分类：Excel2019函数公式大全

下面为D11:D14单元格区域添加并设置数值调节按钮，从而帮助实现数据的动态分析。具体操作步骤如下。

STEP01：选择工作表中的任意单元格，如D11单元格，切换至“开发工具”选项卡，单击“控件”组中的“插入”下三角按钮，在展开的下拉列表中单击“表单控件”列表中的“数值调节钮（窗体控件）”按钮，如图25-17所示。

图25-17　单击“数值调节钮（窗体控件）”按钮

STEP02：此时，鼠标指针会变成十字形状，将鼠标定位至D11单元格内，在D11单元格中绘制一个数值调节钮，并使用调节钮四周的调节句柄适当改变其大小，如图25-18所示。

STEP03：选中数值调节钮，单击鼠标右键，在弹出的隐藏菜单中选择“设置控件格式”选项，打开“设置对象格式”对话框，如图25-19所示。

图25-18　绘制数据调节钮

图25-19　选择“设置控件格式”选项

STEP04：打开“设置对象格式”对话框后，切换至“控制”选项卡，在“最小值”调节框中设置“最小值”为“0”，在“最大值”调节框中设置“最大值”为“400”，在“步长”调节框中设置“步长”为“1”，然后将单元格链接地址设置为“$D$11”单元格，并勾选下方的“三维阴影”复选框，最后单击“确定”按钮即可完成控件格式的设置，如图25-20所示。

STEP05：使用同样的方法在D12、D13、D14单元格中绘制数值调节钮并设置控件格式，将其单元格链接分别设置为“$D$12”单元格、“$D$13”单元格、“$D$14”单元格，设置完成后的结果如图25-21所示。

图25-20　设置对象格式

图25-21　绘制其他按钮

接下来在需要的单元格中输入公式，以计算盈亏临界销售量、盈亏临界销售额、利润等。具体的操作步骤如下所示。

STEP01：选中C15单元格，在编辑栏中输入公式“=(C11-C12)*C13-C14”，然后按“Enter”键返回，即可计算出目标利润，如图25-8所示。

图25-8　计算目标利润

STEP02：选中E11单元格，在编辑栏中输入公式“=D11/100-100%”，然后按“Enter”键返回，即可计算出销售单价的变动百分比，如图25-9所示。

STEP03：选中E11单元格，将鼠标光标移至单元格右下角，使用填充柄工具向下复制公式至E14单元格，通过自动填充功能计算出其他因素对应的变动百分比，如图25-10所示。

图25-9　计算变动百分比

图25-10　复制公式

STEP04：选中E15单元格，在编辑栏中输入公式“=(F15-C15)/C15”，然后按“Enter”键返回，即可计算出目标利润的变动百分比，如图25-11所示。

STEP05：选中F11单元格，在编辑栏中输入公式“=C11*(1+E11)”，然后按“Enter”键返回，即可计算出销售单价变动后的数值，如图25-12所示。

图25-11　计算目标利润的变动百分比

图25-12　计算销售单价变动后的数值

STEP06：选中F11单元格，将鼠标光标移至单元格右下角，使用填充柄工具向下复制公式至F14单元格，通过自动填充功能计算出其他因素对应的变动后数值，如图25-13所示。

STEP07：选中F15单元格，在编辑栏中输入公式“=(F11-F12)*F13-F14”，然后按“Enter”键返回，即可计算出目标利润变动后的数值，如图25-14所示。

图25-13　计算其他因素对应的变动后数值

图25-14　计算目标利润变动后的数值

STEP08：选中G11单元格，在编辑栏中输入公式“=E15/E11”，然后按“Enter”键返回，即可计算出销售单价的敏感系数，如图25-15所示。

STEP09：选中G11单元格，将鼠标光标移至单元格右下角，使用填充柄工具向下复制公式至G14单元格，通过自动填充功能计算出其他因素对应的敏感系数，如图25-16所示。

D11:D14单元格区域中的数据将来要通过微调按钮调整出来，E11:E14单元格区域中的数据由D11:D14单元格区域中的数据转换而来。

图25-15　计算销售单价的敏感系数

图25-16　计算其他因素对应的敏感系数

在以上表格中，有些单元格区域的格式需要按设计要求再定义一下，设置为数值格式或百分比格式。下面介绍具体的操作步骤。

STEP01：选中C3:C8单元格区域，单击鼠标右键，在弹出的隐藏菜单中选择“设置单元格格式”选项，打开“设置单元格格式”对话框，如图25-3所示。

图25-3　选择“设置单元格格式”选项

STEP02：切换至“数字”选项卡，在“分类”列表框中选择“数值”选项，并将“小数位数”设置为“2”，然后单击“确定”按钮，完成该单元格区域格式的设置，如图25-4所示。

STEP03：选中C11:C15单元格区域，单击鼠标右键，在弹出的隐藏菜单中选择“设置单元格格式”选项，打开如图25-5所示的“设置单元格格式”对话框。切换至“数字”选项卡，在“分类”列表框中选择“数值”选项，并将“小数位数”设置为“2”，然后单击“确定”按钮，完成该单元格区域格式的设置。

图25-4　设置单元格格式

图25-5　设置C11:C15单元格区域数字格式

STEP04：选中E11:E15单元格区域，单击鼠标右键，在弹出的隐藏菜单中选择“设置单元格格式”选项，打开如图25-6所示的“设置单元格格式”对话框。切换至“数字”选项卡，在“分类”列表框中选择“百分比”选项，并将“小数位数”设置为“0”，然后单击“确定”按钮，完成该单元格区域格式的设置。

STEP05：选中F11:F15单元格区域，用与上面类似的方法将单元格区域的格式设置为“数值”格式，小数位数设置为“2”。

STEP06：选中G11:G15单元格区域，单击鼠标右键，在弹出的隐藏菜单中选择“设置单元格格式”选项，打开如图25-7所示的“设置单元格格式”对话框。切换至“数字”选项卡，在“分类”列表框中选择“数值”选项，并将“小数位数”设置为“0”，然后单击“确定”按钮，完成该单元格区域格式的设置。

图25-6　设置E11:E15单元格区域数字格式

图25-7　设置G11:G15单元格区域数字格式

在进行本量利分析之前，首先创建一个用于练习的本量利分析基本模型。具体的操作步骤如下。

STEP01：首先新建一个空白工作簿，将其重命名为“本量利分析.xlsx”，并将“Sheet1”工作表重命名为“本量利分析”，如图25-1所示。该工作表主要用于制作本量利分析基本模型。

STEP02：在“本量利分析”工作表中输入所需文本、数据，并适当设置文本和表格的格式。这里将字体设置为“宋体”，设置字号为“11”，并设置标题处单元格的背景颜色，在“本量利分析模型”标题处设置背景颜色为“红色，个性色2”，在“盈亏临界分析”标题处和“因素变动分析”标题处设置背景颜色为“蓝色”，然后为表格依次添加内边框和外边框。用户可根据实际情况对文本和表格格式进行设置，设置后的效果如图25-2所示。

图25-1　新建工作表

图25-2　输入所需文本与数据并设置格式