Excel 中的数据表:创建一变量和二变量数据表
当您有一个依赖于多个变量的复杂公式并且想要了解更改这些输入如何有效地影响结果时,Excel 中的假设分析数据表是一个强大的工具。它使您可以快速浏览所有可能的结果。以下是有关如何在 Excel 中创建数据表的分步指南。此外,我们将讨论有效使用数据表的一些关键点,并演示其他操作,例如删除、编辑和重新计算数据表。
Excel中的数据表是什么?
在 Excel 中,数据表是假设分析工具之一,可让您试验公式的不同输入值并观察公式输出的变化。该工具对于探索各种场景和进行敏感性分析非常有用,特别是当公式依赖于多个变量时。
- 数据表 允许您测试公式中的不同输入值,并查看这些值的变化如何影响输出。它对于敏感性分析、情景规划和财务建模特别有用。
- Excel表格 用于管理和分析相关数据。它是结构化的数据范围,您可以在其中轻松排序、筛选和执行其他操作。数据表更多的是基于不同的输入来探索各种结果,而 Excel 表格则是为了有效地管理和分析数据集。
Excel中有两种类型的数据表:
一变量数据表:这使您可以分析一个变量的不同值将如何影响公式。您可以采用面向行或面向列的格式设置单变量数据表,具体取决于您是要跨行还是跨列改变输入。
二变量数据表:此类型允许您查看更改两个不同变量对公式结果的影响。在双变量数据表中,您可以改变行和列上的值。
面向列的数据表
当您想要测试单个变量的不同值如何影响公式的输出(变量值在一列中列出)时,面向列的数据表非常有用。让我们考虑一个简单的财务示例:
假设您正在考虑一笔 50,000 美元的贷款,您计划在 3 年(相当于 36 个月)内偿还。为了根据您的工资评估每月还款额是可以承受的,您有兴趣探索不同的利率将如何影响您每月需要支付的金额。
第 1 步:设置基本公式
为了计算还款额,这里我将利息设置为5%。在单元格 B4 中,输入 PMT 公式,它根据利率、期数和贷款金额计算每月还款额。看截图:
= -PMT($B$1/12, $B$2*12, $B$3)
步骤 2:在一栏中列出利率
在一栏中列出您要测试的不同利率。例如,在一列中以 4% 为增量列出从 11% 到 1% 的值,并在右侧至少留出一个空白列作为结果。看截图:
第三步:创建数据表
- 在单元格 E2 中,输入以下公式: = B4.
备注: B4 是主公式所在的单元格,您希望看到该公式的结果随着利率的变化而变化。 - 选择包含公式、利率列表和结果相邻单元格的范围(例如,选择 D2 到 E10)。看截图:
- 转到功能区,单击 时间 选项卡,然后单击 假设分析 > 数据表,请参见屏幕截图:
- 在 数据表 对话框,点击 列 输入单元格 框(因为我们使用一列作为输入值),然后选择公式中引用的变量单元格。在此示例中,我们选择包含利率的 B1。最后,点击 OK 按钮,请参见屏幕截图:
- Excel 将自动用相应的结果填充每个变量值(不同利率)旁边的空白单元格。看截图:
- 根据您的需要将所需的数字格式应用于结果(货币)。现在,列式数据表已成功创建,您可以快速查看结果来评估当利率变化时您可以承受哪些每月还款额。看截图:
面向行的数据表
在 Excel 中创建面向行的数据表需要对数据进行排列,以便变量值跨行列出,而不是沿列列出。以上面的例子为参考,我们继续按照步骤完成Excel中行式数据表的创建。
步骤 1:连续列出利率
将变量值(利率)放在一行中,确保左侧至少有一个空列用于公式,下方至少有一个空行用于结果,请参见屏幕截图:
第三步:创建数据表
- 在单元格 A9 中,输入以下公式: = B4.
- 选择包含公式、利率列表和结果相邻单元格的范围(例如,选择 A8 到 I9)。然后,单击 时间 > 假设分析 > 数据表.
- 在 数据表 对话框,点击 行输入单元格 框(因为我们使用一行作为输入值),然后选择公式中引用的变量单元格。在此示例中,我们选择包含利率的 B1。最后,点击 OK 按钮,请参见屏幕截图:
- 根据您的需要将所需的数字格式应用于结果(货币)。现在,面向行的数据表已创建,请参见屏幕截图:
一变量数据表的多个公式
在 Excel 中创建包含多个公式的单变量数据表可让您了解更改单个输入如何同时影响多个不同的公式。在上面的示例中,如果您想查看还款利率和总利息的利率变化,该怎么办?设置方法如下:
第 1 步:添加新公式来计算总利息
在单元格 B5 中,输入以下公式来计算总利息:
=B4*B2*12-B3
步骤2:整理数据表的源数据
在一列中,列出您要测试的不同利率,并在右侧至少留出两个空白列作为结果。看截图:
第三步:创建数据表:
- 在单元格 E2 中,输入以下公式: = B4 创建对原始数据中还款计算的引用。
- 在单元格 F2 中,输入以下公式: = B5 创建对原始数据的总兴趣的引用。
- 选择包含公式、利率列表和结果相邻单元格的范围(例如,选择 D2 到 F10)。然后,单击 时间 > 假设分析 > 数据表.
- 在 数据表 对话框,点击 列输入单元格 框(因为我们使用一列作为输入值),然后选择公式中引用的变量单元格。在此示例中,我们选择包含利率的 B1。最后,点击 OK 按钮,请参见屏幕截图:
- 根据您的需要将所需的数字格式应用于结果(货币)。您可以根据不同的变量值查看每个公式的结果。
创建二变量数据表
Excel 中的双变量数据表显示两组变量值的不同组合对公式结果的影响,演示公式的两个输入的更改如何同时影响其结果。
在这里,我画了一个简单的草图,以帮助您更好地理解二变量数据表的外观和结构。
在创建一变量数据表的示例的基础上,现在让我们继续学习如何在 Excel 中创建二变量数据表。
在下面的数据集中,我们有利率、贷款期限和贷款金额,并使用以下公式计算了每月还款额: =-PMT($B$1/12, $B$2*12, $B$3) 以及。在这里,我们将重点关注数据中的两个关键变量:利率和贷款金额,以观察这两个因素的变化如何同时影响还款金额。
第 1 步:设置两个变化变量
- 在一栏中列出您要测试的不同利率。看截图:
- 在一行中,在列值上方输入不同的贷款金额值(从公式单元格右侧的一个单元格开始),请参阅屏幕截图:
第三步:创建数据表
- 将公式放在列出变量值的行和列的交叉点处。在这种情况下,我将输入这个公式: = B4 进入单元格 E2。看截图:
- 选择包括贷款金额、利率、公式单元格以及将显示结果的单元格的范围。
- 然后,单击 时间 > 假设分析 > 数据表。在数据表对话框中:
- 在 行输入单元格 框中,选择对行中变量值的输入单元格的单元格引用(在此示例中,B3 包含贷款金额)。
- 在 列输入单元格l 框中,选择对列中变量值的输入单元格的单元格引用(B1 包含利率)。
- 然后,单击 OK 按钮。
- 现在,Excel 将使用每种贷款金额和利率组合的结果填充数据表。它可以直接了解贷款金额和利率的不同组合如何影响每月还款额,使其成为财务规划和分析的宝贵工具。
- 最后,您应该根据需要将所需的数字格式应用于结果(货币)。
使用数据表的要点
- 新创建的数据表必须与原始数据位于同一工作表中。
- 数据表的输出取决于源数据集中的公式单元格,对此公式单元格的任何更改都会自动更新输出。
- 一旦使用数据表计算出值,就无法撤消 按Ctrl + Z。但是,您可以手动选择所有值并将其删除。
- 数据表生成数组公式,因此表中的各个单元格无法更改或删除。
编辑数据表结果
事实上,无法直接编辑数据表中的各个单元格,因为它们包含 Excel 自动生成的数组公式。
要进行更改,您通常需要删除现有数据表,然后创建一个包含所需修改的新数据表。这涉及调整基本公式或输入值,然后再次设置数据表以反映这些更改。
手动重新计算数据表
通常,每次进行更改时,Excel 都会重新计算所有打开的工作簿中的所有公式。如果包含大量变量值和复杂公式的大型数据表导致 Excel 工作簿速度变慢。
为了避免每次工作簿中发生更改时 Excel 自动对所有数据表执行计算,您可以将计算模式从自动切换为手动。请按以下步骤操作:
去 公式 标签,然后单击 计算选项 > 自动(数据表除外),请参见屏幕截图:
修改此设置后,当您重新计算整个工作簿时,Excel 将不再自动更新数据表中的计算。
如果您需要手动更新数据表,只需选择显示结果的单元格(包含 TABLE() 公式的单元格),然后按 F9 键。
通过遵循本指南中概述的步骤并记住要点,您可以有效地利用数据表来满足您的数据分析需求。如果您有兴趣探索更多 Excel 提示和技巧,我们的网站提供了数千个教程, 请点击此处访问它们。 感谢您的阅读,我们期待在未来为您提供更多有用的信息!
最佳办公生产力工具
🤖 | Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数... |
热门特色: 查找、突出显示或识别重复项 | 删除空白行 | 合并列或单元格而不丢失数据 | 不使用公式进行四舍五入 ... | |
超级查询: 多条件VLookup | 多值VLookup | 跨多个工作表的 VLookup | 模糊查询 .... | |
高级下拉列表: 快速创建下拉列表 | 依赖下拉列表 | 多选下拉列表 .... | |
列管理器: 添加特定数量的列 | 移动列 | 切换隐藏列的可见性状态 | 比较范围和列 ... | |
特色功能: 网格焦点 | 设计图 | 大方程式酒吧 | 工作簿和工作表管理器 | 资源库 (自动文本) | 日期选择器 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊过滤器 (过滤粗体/斜体/删除线...)... | |
前 15 个工具集: 12 文本 工具 (添加文本, 删除字符,...) | 50+ 图表 类型 (甘特图,...) | 40+ 实用 公式 (根据生日计算年龄,...) | 19 插入 工具 (插入二维码, 从路径插入图片,...) | 12 转化 工具 (小写金额转大写, 货币兑换,...) | 7 合并与拆分 工具 (高级组合行, 分裂细胞,...) | ... 和更多 |
使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...
Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松
- 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
- 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
- 每天将您的工作效率提高50%,并减少数百次鼠标单击!