Question

Excel数据分析第十七讲（单变量求解)

Answer 1

在本课程中，我们来学习使用EXCEL的单变量求解功能。

在EXCEL中，单变量求解可以根据一定的公式运算结果，倒推出变量。相当于对公式进行逆运算。比如输入公式=(1+0.4%)^12-1，可以直接算出结果约为4.91%。如果给定计算结果是5.4%，其他条件不变，求指数为多少？使用单变量求解可轻松算出指数约为13.18。下面通过两个例子说明一下单变量求解的该如何运用。

第一个例子假设一个奶茶店的固定成本是$45,000，单位成本是$0.45，单价是$3.00。（如下图所示）

问：需要卖多少杯奶茶这家店才能营收平衡（profit = 0)

求解这个问题我们可以借助单变量求解功能，如下图所示，点击 数据 菜单，点击 模拟分析 工具栏的 单变量求解 按钮

系统弹出以下对话框，在 目标单元格 输入或者选择D7单元格， 目标值 输入0， 可变单元格 输入或者选择D2单元格。然后按确定按钮。

注： 在对话框中输入这些单元格或者值的目的是让EXCEL选取合适的销售量（demand）的值，使得这家奶茶店做到营收平衡（profit=0）

最后，系统会跳出如下窗口，同时demand值会变为17647

下面再举一个身边的例子。我们每个人都有贷款的经历，假设现在你准备贷款买房，贷款年限是15年，年利率是6%。现在的问题是你想知道一共贷多少才可以确保每个月还$2000。我们把这个问题里的所有参数都输入到工作簿中，如下图所示。

D3单元格输入的是总月份数（15*12=180），D4单元格输入的是年利率，D5单元格输入的是贷款总金额，D6单元格输入公式 =-PMT(annual_int_rate/12,years,amt._borrowed) ，这个公式可以帮我们计算出每个月的还款金额。

有了这些数据，怎么才能知道我们该贷多少钱才能保证每个月还$2000。求解这个问题同样可以借助单变量求解功能，如下图所示，点击 数据 菜单，点击 模拟分析 工具栏的 单变量求解 按钮

系统弹出以下对话框，在 目标单元格 输入或者选择E6单元格， 目标值 输入2000， 可变单元格 输入或者选择E5单元格。然后按确定按钮。

系统会跳出如下窗口，系统告诉我们当我们贷$237,007.03，每个月还款金额（Monthly payment)为$2000。

以上就是今天的课程，大家有问题请给我留言。