Excel Data Analysis 简明教程

Advanced Data Analysis - What-If Analysis

What-if analysis 是更改单元格中的值的过程,以查看这些更改如何影响工作表上公式的结果。你可以在一个或多个公式中使用多组不同的值来探索所有不同的结果。

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. You can use several different sets of values in one or more formulas to explore all the various results.

what-if 分析在进行数据分析时在许多情况下很有用。例如 −

What-if analysis is useful in many situations while doing data analysis. For example −

  1. You can propose different budgets based on revenue.

  2. You can predict the future values based on the given historical values.

  3. If you are expecting a certain value as the result from a formula, you can find different sets of input values that produce the desired result.

Excel 为你提供了可以根据你的数据分析需求使用的以下 what-if 分析工具 −

Excel provides you with the following What-if analysis tools that can be used based on your data analysis needs −

  1. Data Tables

  2. Scenario Manager

  3. Goal Seek

数据透视表和场景采用输入值集并向前预测,以确定可能的结果。目标寻求与数据透视表和场景不同,因为它采用一个结果并向后预测以确定产生该结果的可能输入值。

Data Tables and Scenarios take sets of input values and project forward to determine possible results. Goal seek differs from Data Tables and Scenarios in that it takes a result and projects backwards to determine possible input values that produce that result.

在本章中,你将了解可以使用 what-if 分析工具的可能情况。有关这些工具的用法详细信息,请参阅本教程后面的章节。

In this chapter, you will understand the possible situations where you can use the Whatif Analysis tools. For details on usage of these tools, refer to the later chapters in this tutorial.

Data Tables

Data Table 是一个单元格范围,你可以在其中更改一些单元格中的值,并提出解决问题的不同答案。例如,你可能想通过分析不同的贷款金额和利率来了解你能负担得起多少房屋贷款。你可以将这些不同的值与数据表中的 PMT 函数放在一起并获得所需结果。

A Data Table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem. For example, you might want to know how much loan you can afford for a home by analyzing different loan amounts and interest rates. You can put these different values along with the PMT function in a Data Table and get the desired result.

数据表仅适用于 one or two variables ,但它可以接受这些变量的许多不同值。

A Data Table works only with one or two variables, but it can accept many different values for those variables.

有关数据表的详细信息,请参阅本教程中的章节 – What-If Analysis with Data Tables

For the details on Data Tables, refer to the chapter – What-If Analysis with Data Tables in this tutorial.

Scenario Manager

场景是 Excel 保存并可以在工作表的单元格中自动替换的一组值。

A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet.

主要功能 −

The key features are −

  1. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

  2. A scenario can have multiple variables, but it can accommodate only up to 32 values.

  3. You can also create a scenario summary report, which combines all the scenarios on one worksheet. For example, you can create several different budget scenarios that compare various possible income levels and expenses, and then create a report that lets you compare the scenarios side-by-side.

  4. Scenario Manager is a dialog box that allows you to save the values as a scenario and name the scenario.

有关方案的详细信息,请参阅本教程中的第 What-If Analysis with Scenario Manager 章。

For the details on Scenarios, refer to the chapter – What-If Analysis with Scenario Manager in this tutorial.

Goal Seek

如果您知道从公式中想要的结果,但无法确定公式需要什么输入值才能得到该结果,那么目标寻值将很有用。例如,如果您想借一笔贷款,并且您知道贷款金额、贷款期限以及可以支付的等额每月分期付款 (EMI),那么可以使用目标寻值来找到可以获得该贷款的利率。

Goal Seek is useful if you know the result that you want from a formula, but not sure what input value the formula needs, to get that result. For example, if you want to borrow a loan and you know the loan amount, tenure of loan and the EMI that you can pay, you can use Goal Seek to find the interest rate at which you can avail the loan.

目标寻值只能用于一个变量输入值。如果您有多个输入值变量,可以使用求解器加载项。

Goal Seek can be used only with one variable input value. If you have more than one variable for input values, you can use the Solver add-in.

有关如何使用目标寻值,请参阅本教程中的第 What-If Analysis with Goal Seek 章。

For details on the usage of Goal Seek, refer to the chapter – What-If Analysis with Goal Seek in this tutorial.

Solver

求解器作为加载项随 Excel 提供。您可以使用求解器找到工作表上称为目标单元格的单元格中公式的最佳值。

Solver comes with Excel as an add-in. You can use Solver to find an optimal value for a formula in a cell called the target cell on a worksheet.

求解器使用与目标单元格中的公式相关的单元格组。求解器调整您指定的可调整单元格中的值,以获得目标单元格公式指定的预期结果。

Solver works with a group of cells that are related to the formula in the target cell. Solver adjusts the values in the adjustable cells that you specify, to produce the result that you specify, from the target cell formula.

有关如何使用 Excel 求解器加载项,请参阅本教程中的第 Optimization with Excel Solver 章。

For the details on the usage of Excel Solver add-in, refer to the chapter - Optimization with Excel Solver in this tutorial.