Excel假设分析工具数据表的使用方法

　　此“数据表”并非泛指的数据表格，而是Excel假设分析的工具之一。“数据表”的主要功能是通过引用行或者引用列为公式中的变量赋值，生成一个数据表格，用户可以通过生成的数据表格查看和比较不同赋值生成的不同结果。

　　“数据表”分为“单变量数据表”和“双变量数据表”，分别说明如下：

　　1、单变量数据表

　　我们用一个计算不同贷款年限月还款金额的例子说明单变量数据表的使用方法。

　　

 

　　单元格B1:B3保存常量信息；单元格A6:A20是引用列，列出了不同贷款年限，将为公式的变量赋值；单元格B6:B20将显示不同贷款年限的月还款金额。

　　首先在单元格B6键入公式“=PMT($B$2/12,A6*12,$B$1-$B$3)”。

　　PMT函数的功能是返回固定利率下，每期等额还款金额，其语法为：PMT(rate,nper,pv,[fv],[type])，其中rate表示利率，nper表示还款期的总期数，pv表示贷款本金。rate和nper的单位必须保持一致，譬如都以月或者年为周期。贷款本金输入正值时返回负值，输入负值时返回正值。

　　

 

　　然后鼠标选中单元格区域A6:B20，点击“数据”选项卡，点击“假设分析”按钮，选择“数据表”。

　　

 

　　在弹出的“数据表”对话框中，“输入引用列单元格”选择地址A6，意思是公式中的地址引用A6是变量，分别用选择区域的第一列即引用列的数据赋值。

　　

 

　　确定之后，单元格B7:B20自动填充，生成一个数据表。

　　单元格B6的公式不变，从编辑栏可以看到单元格B7:B20带了一个大括号，是一个区域数组形式，不能单独修改。

　　

 

　　单变量数据表也可以理解为只有引用行单元格或者只有引用列单元格的数据表，如果贷款年限是以行的形式显示，则只在“输入引用行单元格”选择地址B5，其余方法和步骤相同。

　　

 

　　2、双变量数据表

　　我们可以利用双变量数据表快速生成一个九九乘法表。

　　首先创建数字1-9的行和列作为引用行和引用列，两者的交叉点空出来录入公式“=A1&"x"&B1&"="&A1*B1”。注意，双变量数据表的公式必须放置在引用行和引用列的交叉点上。

　　

 

　　然后选择区域A2:J11，打开“数据表”对话框，“输入引用行单元格”选择B1，“输入引用列单元格”选择A1。表示公式中的A1和B1分别用引用列和引用行的相应数据赋值。

　　

 

　　确定之后，空白区域自动填充，生成双变量数据表。

　　

 

　　如果想显示成阶梯状的九九乘法表，可以先将引用行和列的字体颜色设置为白色。然后选择数据表区域，点击“开始”选项卡，点击“条件格式”按钮，选择“新建规则”。

　　

 

　　在“新建格式规则”对话框中，选择“使用公式确定要设置格式的单元格”，然后在编辑规则的对话框键入公式“=COLUMN()>ROW()-1”。函数COLUMN和ROW返回参数中单元格的列号和行号，没有参数时返回公式所在单元格的列号和行号。

　　（关于条件格式，请参阅Excel条件格式工具的使用技巧（二）Excel条件格式工具的使用技巧（一））

　　

 

　　设置条件格式后的九九乘法表效果如下。

　　

 

　　数据表的用法，你学会了吗？

《Excel假设分析工具数据表的使用方法》由 性价比高的手机原创提供，转载请注明 https://m.baijing8.cn/jiqiao/7651.html