EXCEL在财务中的应用（第五版）课件 钭志斌 项目7-12 数据分析工具的应用-Excel VBA编程基础

项目七数据分析工具的应用Excel在财务中的应用目录CONTENTS01任务一单变量求解的应用02任务二规划求解的应用03任务三方案分析的应用04任务四数据透视表的应用05任务五数据透视表的计算06课后实训（附参考答案）01任务一单变量求解的应用学习目标与任务描述任务一·单变量求解的应用知识目标●理解单变量求解的基本原理与作用，能够根据目标值的计算公式利用单变量求解方式进行逆运算，求出其他影响因素的值。●能够在财务决策中应用单变量求解方式，提高计算效率，为决策分析服务。素养目标●增强解决实际问题的能力，学会用工具反向求解目标值。●培养勤于思考、乐于探索的学习品质，激发对数据分析工具的深度兴趣。任务描述（学习资料）中原公司职工张三的销售奖金是全年销售额的0.2%，前3个季度的销售额分别为120000元、180000元、150000元。张三想知道第4季度的销售额为多少时，才能保证年终奖金为1500元。单变量求解的基本原理任务一·单变量求解的应用什么是单变量求解“单变量求解”是一组命令的组成部分，这些命令有时也称作假设分析工具。如果已知单个公式的预期结果，而用于确定此公式结果的输入值未知，则可使用单变量求解功能。简单地说，就是知道一个公式的结果，求公式中包含的某个未知单元格的值。单变量求解可以帮助求解“如果……怎样”的条件问题，即从结果反推并改变一个变量。举例说明

一、定义计算公式任务一·单变量求解的应用启动Excel，按图7-1所示格式建立一张工作表。在B8单元格中输入下列公式，用以计算年终奖金：=SUM(B4:B7)*B2图7-1单变量求解单元格识别二、确定目标值，选定可变单元格任务一·单变量求解的应用执行“数据”→“模拟分析”→“单变量求解”命令，打开“单变量求解”对话框。在对话框的“目标单元格”中选择输入“$B$8”，在“目标值”中输入“1500”，在“可变单元格”中选择输入“$B$7”，如图7-2所示。图7-2单变量求解单元格定义操作提示7-1对话框单元格定义●目标单元格中必须已录入设置好的计算公式；●可变单元格为需要计算求解的单元格，该单元格中无公式。三、确定计算结果任务一·单变量求解的应用单击“确定”按钮，则在可变单元格中自动填入显示的结果，如图7-3所示。也可以选择“取消”按钮，不填入计算结果。图7-3“单变量求解状态”对话框同步训练7-1：年终奖发放方式选择任务一·单变量求解的应用同步训练7-1接任务一资料：若张三年终奖并入当年综合所得计税与单独计税的税后所得存在差异，请用单变量求解测算两种方式的税后年终奖。参考答案：经单变量求解测算，两种方式税后所得差异对应年终奖临界点约为76.67万元。图7-4单变量求解结果02任务二规划求解的应用学习目标与任务描述任务二·规划求解的应用知识目标●理解规划求解的基本原理与作用，能够利用规划求解方式根据目标值的计算公式自动求出影响目标值的其他各因素的最优值。●能够在财务决策中应用规划求解方式，提高计算效率，为决策分析服务。素养目标●理解解决问题与方法之间的关系，掌握多约束条件下最优方案的设计。●增强系统优化意识，培养在复杂条件下寻找最优解的综合能力。任务描述（学习资料）已知西部公司生产甲和乙两种产品，甲产品的销售单价为150元，乙产品的销售单价为200元；甲产品的单位变动生产成本为70元，乙产品的单位变动生产成本为120元。利用规划求解方式，求甲产品和乙产品的数量为多少时，公司的毛利额最大？相关资源限制条件及产品消耗定额如图7-5所示。图7-5相关资源限制条件及产品消耗定额规划求解的基本原理任务二·规划求解的应用什么是规划求解单变量求解仅可解决存在一个变量的问题，而规划求解则可以求解更多变量。规划求解是假设分析的组成部分，可以用于解决复杂的方程式值及各类线性或非线性的有约束的优化问题。规划求解是Excel的一个可选安装模块，必须在系统安装了规划求解工具后，才能使用它。在财务决策中的应用在财务决策中涉及很多优化的问题，这些问题都可以采用规划求解工具来解决：利润最大化成本最小化投资组合最优化一、建立规划求解模型任务二·规划求解的应用在使用规划求解之前，首先应建立规划求解模型，以确保规划求解设置和运算的正确性。建立规划求解模型包括以下内容：（1）确定可变单元格可变单元格是Excel中可以进行更改或调整以优化目标单元格的单元格。例如测算各产品产销数量为多少时才能实现利润最大，产销数量所在的单元格便是可变单元格。图7-5中，B11:C11单元格区域为可变单元格区域。（2）确定目标单元格目标单元格与可变单元格数值的关系可以通过计算公式建立。例如“毛利总额＝（单位售价－单位变动成本）×预计产销量”。图7-5中，B12单元格为目标单元格。（3）确定约束条件可以根据已知条件进行设置，例如各产品的消耗总工时不能超过机器工时总数等。

模型的公式设置与约束条件任务二·规划求解的应用单元格公式设置：B10

=B8-B9；C10=C8-C9：计算两种产品的单位毛利额B12

=SUMPRODUCT(B10:C10,B11:C11)目标单元格，计算毛利总额D5

=SUMPRODUCT(B5:C5,B11:C11)计算产品消耗的总工时D6

=SUMPRODUCT(B6:C6,B11:C11)计算产品消耗的材料总量D7

=SUMPRODUCT(B7:C7,B11:C11)计算产品消耗的能源总量注：B12公式与表达式“=B10*B11+C10*C11”含义相同。约束条件

知识链接7-1SUMPRODUCT函数任务二·规划求解的应用知识链接7-1SUMPRODUCT函数该函数是指在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。其语法为：=SUMPRODUCT(array1,[array2],[array3],…)●array1，array2，array3，…为2到255个数组，其相应元素需要进行相乘并求和；●数组参数必须具有相同的维数，否则函数SUMPRODUCT将返回错误值#VALUE!；●函数SUMPRODUCT将非数值型的数组元素作为零处理。本例应用本例B12单元格的=SUMPRODUCT(B10:C10,B11:C11)与表达式=B10*B11+C10*C11含义相同。二、加载规划求解命令任务二·规划求解的应用（1）在“Excel选项”对话框内，单击选择“加载项”选项，然后单击右侧的“转到”按钮，如图7-6所示。（2）在“加载项”对话框内，选择“规划求解加载项”，然后单击“确定”按钮，如图7-7所示。通过上述设置，即可在“数据”选项卡的“分析”选项组中添加“规划求解”命令。图7-6“加载项”对话框图7-7“加载宏”对话框三、利用规划求解命令求解任务二·规划求解的应用（1）设置目标单元格“$B$12”，选中“最大值”单选按钮，计算毛利额的最大值。（2）设置可变单元格：将$B$11:$C$11单元格区域（产销量）设置为可变单元格。（3）设置约束条件：单击“添加”按钮，在“添加约束”对话框中逐个添加约束条件（图7-9），添加完毕后单击“确定”按钮返回。图7-8“规划求解参数”对话框图7-9添加或修改约束条件操作提示7-2设置约束条件为“整数”任务二·规划求解的应用操作提示7-2设置约束条件为“整数”有时候，需要将可变单元格的约束条件设置为“整数”，如产品生产的件数。本例要求将产销量（即B11:C11单元格中的数值）设置为整数：单击“添加”，在“添加约束”对话框中定义“单元格引用位置”为$B$11:$C$11单元格区域，运算符号选择“int”，此时约束值自动变成“整数”，如图7-10所示。图7-10设置约束条件为“整数”（4）规划求解与求解结果任务二·规划求解的应用单击“求解”按钮，出现“规划求解结果”对话框，选择“保存规划求解结果”单选框，然后单击“确定”按钮，即可得出最优解，如图7-11所示。图7-11规划求解结果求解结论只生产50件甲产品，同时停产乙产品，就能实现最大毛利额4000元。此时材料消耗、能源消耗都还有剩余。同步训练7-2增加约束条件后的规划求解任务二·规划求解的应用同步训练7-2如果我们必须完成10件乙产品的生产任务，若其他条件不变，那么该如何进行求解呢？参考答案：X2的最小值为10件；求解后，甲产品生产35件，最大毛利额为3600元。图7-12增加乙产品约束条件03任务三方案分析的应用方案分析的应用：任务目标与任务描述任务三·方案分析的应用知识目标掌握方案分析的应用原理，根据不同的预期建立不同的方案；掌握方案的建立、修改和删除方法，并能显示方案摘要报告。素养目标培养多方案比较分析与权衡取舍的能力；树立依据数据结果科学决策的职业意识。任务描述江南公司年产D产品2,500件，产品售价120元，单位变动成本60元，固定成本50,000元。公司拟对影响目标利润的四项因素分别拟订三种方案，计算每种方案的目标利润，并提供方案比较报告。影响因素方案一方案二方案三产品售价-10%-8%-6%单位变动生产成本0%-2%-2%预计产销量30%25%20%固定成本0(不变)0(不变)1%方案分析的应用：基本原理与表格设计任务三·方案分析的应用市场环境的变化会使影响目标利润的各因素随之变动。利用Excel的方案管理器，可以随时显示各方案的执行结果，并自动建立方案摘要报告，为管理层的权衡取舍提供直接依据。一、设计方案分析表格按表7-1建立方案分析表格，在B9单元格输入目标利润计算公式：目标利润=(产品售价-单位变动生产成本)×产销量-固定成本=(B5-B6)*B7-B8图7-13D产品方案分析表格定义方案分析器中各单元格的名称任务三·方案分析的应用二、定义各单元格的名称为便于识别，将方案分析表中可变单元格定义为直观名称：C5——售价变动率；C6——变动成本变动率；C7——产销量变动率；C8——固定成本变动率。方法：单击"公式"选项卡→"定义名称"。在C9单元格输入如下公式，得到不同方案下的目标利润：=(B5*(1+售价变动率)-B6*(1+变动成本变动率))*B7*(1+产销量变动率)-B8*(1+固定成本变动率)建立方案并输入可变单元格的值任务三·方案分析的应用三、建立方案单击"数据"选项卡→"预测"组→"模拟分析"→方案管理器（图7-15）；在打开的对话框中单击"添加"，在"编辑方案"对话框中输入方案名，并指定可变单元格为$C$5:$C$8（图7-16）。四、输入可变单元格的值在"方案变量值"对话框中按表7-1输入各方案的变动值（图7-17）；返回方案管理器后，可对已有方案进行修改、删除或继续添加（图7-18）。图7-15方案管理器图7-16编辑方案图7-17方案变量值图7-18方案列表显示各方案的执行结果任务三·方案分析的应用五、显示方案在方案管理器中选择需要查看的方案名称，单击"显示"按钮，工作表中即按该方案的取值自动重算，得到相应的目标利润。下图分别为方案二（目标利润111,250.00元）和方案三（目标利润111,500.00元）的显示结果。图7-19方案二显示结果图7-20方案三显示结果建立方案摘要报告任务三·方案分析的应用六、建立方案摘要报告在方案管理器中单击"摘要"按钮，在"方案摘要"对话框中选择报表类型为"方案摘要"，结果单元格为$C$9（目标利润），单击"确定"后自动生成"方案摘要"工作表（图7-21）。摘要报告解读（图7-22）：当前值方案：111,500.00元；方案一：106,000.00元；方案二：111,250.00元；方案三：111,500.00元。管理层可据此直接比较各方案对目标利润的影响。图7-21方案摘要对话框图7-22方案摘要报告04任务四数据透视表的应用数据透视表的应用：任务目标与任务描述任务四·数据透视表的应用知识目标能描述数据透视表的作用与结构，运用求和、计数、平均值、最大值、最小值等方式进行数值汇总；能按指定字段对数据进行分类查询与统计；能识别数据格式、表格样式不规范对数据透视表的影响。素养目标培养对业务数据进行分类汇总的结构化思维；增强从原始单据中整合信息、服务管理决策的意识。任务描述田源良品公司"生产订单"工作表记录了各客户、各产品的订单明细。公司要求利用数据透视表完成以下统计：（1）每种产品、每个客户的订单数量与总价汇总表；（2）按客户列示的每种产品订单汇总表；（3）指定客户的各产品订单情况查询。数据透视表：功能与基本原理任务四·数据透视表的应用数据透视表是一种交互式的报表，可以快速分类汇总、比较大量的数据，并可以随时选择其中页、行和列中的不同元素，以快速查看源数据的不同统计结果。数据透视表有机地综合了数据排序、筛选、分类汇总等数据分析的功能，用户只要用鼠标拖动字段，就可以生成各种类型的报表，是Excel中最常用、功能最全面的数据分析工具之一。交互式鼠标拖动字段即可重组报表，行列位置随时互换快速汇总对大量数据按分类即时完成求和、计数等统计多维分析筛选、行、列、数值四个区域自由组合视角数据透视表的结构任务四·数据透视表的应用一、数据透视表的结构报表筛选按指定项筛选整个报表列标签字段值横向展开为列行标签字段值纵向展开为行数值求和、计数、平均值、最大值、最小值、标准差、方差等图7-23数据透视表字段窗格图7-24值字段设置创建数据透视表：检查数据格式任务四·数据透视表的应用（一）检查数据格式数据格式不规范将直接影响数据透视表的汇总结果。创建前应先检查并修正：选中"入库数量"列首个单元格H5，按Ctrl+Shift+↓选中整列，在错误提示中选择"转换为数字"；"订单数量"列按同样方法处理，确保参与汇总的字段均为数值格式。图7-25将文本格式转换为数字格式创建数据透视表：插入并指定位置任务四·数据透视表的应用（二）创建数据透视表单击"插入"选项卡→"数据透视表"；在对话框中检查表/区域是否为订单明细数据区域；选择"现有工作表"，将数据透视表放置在本表L4单元格；单击"确定"，生成空白数据透视表及字段窗格。图7-26创建数据透视表结构设计1：每种产品每个客户的订单汇总任务四·数据透视表的应用1．每种产品每个客户的订单数量与总价汇总将产品名称字段拖至"行标签"区域，订单数量、总价字段拖至"数值"区域，客户编码字段拖至"报表筛选"区域，即生成按产品汇总、可按客户筛选的订单汇总表。图7-27字段布局图7-28每种产品每个客户订单汇总表结构设计2：各产品下按客户的订单汇总任务四·数据透视表的应用2．按产品列示的各客户订单汇总将客户编码字段拖至"行标签"区域，并排在产品名称之后，数据透视表即按"产品→客户"两个层级展开，显示每种产品下各客户的订单数量与总价。图7-29字段布局图7-30各产品各客户订单汇总表结构设计3：各客户下按产品的订单汇总任务四·数据透视表的应用3．按客户列示的各产品订单汇总在行标签区域中，将客户编码拖放在产品名称之前，报表即改为"客户→产品"结构（图中BJBA等为客户编码），可按客户查看其订购的各种产品。图7-31字段布局图7-32各客户各产品订单汇总表结构设计4：其他结构组合任务四·数据透视表的应用4．其他结构设计行标签与列标签可以相互调换位置，也可以按需将字段拖放至不同区域进行组合，从而从不同视角观察同一组订单数据。图7-33行列标签互换图7-34结构示例一图7-35结构示例二同步训练7-3：数据清洗与透视表刷新任务四·数据透视表的应用同步训练7-3在"结构设计1"的数据透视表中，按客户编码CZHS筛选，查询该客户各产品的订单数量汇总。参考答案：筛选结果若发现CZHS编码出现重复行，说明编码前后可能存在空格——需先进行数据清洗：按Ctrl+F打开"查找和替换"，查找内容输入一个半角空格，"全部替换"后回到透视表右击选择"刷新"，再重新筛选即得正确结果。图7-36编码重复现象图7-37查找替换空格图7-38刷新后的正确结果生成数据透视图任务四·数据透视表的应用三、生成数据透视图选中数据透视表中的任一单元格，单击"插入"选项卡→"图表"组→数据透视图，选择柱形图类型，即生成与数据透视表联动的数据透视图；图表随客户编码、产品名称的组合筛选同步变化。图7-39插入数据透视图图7-40数据透视图效果05任务五数据透视表的计算数据透视表的计算：任务目标与任务描述任务五·数据透视表的计算知识目标掌握在数据透视表中增加计算字段的方法；能运用筛选器对数据进行分类查询统计；能运用条件格式增强报表的可读性。素养目标培养对数据进行深加工、挖掘数据价值的能力；树立数据作为重要资产、服务经营分析的意识。任务描述田源良品公司1季度的采购订单与生产入库单均已登记。公司要求：统计各客户的采购数量与生产入库数量差异，在数据透视表中增设"生产差异"计算字段，并用数据条条件格式将差异直观地标注出来。数据透视表的计算：基本原理与数据整理任务五·数据透视表的计算数据透视表生成之后，可以对数据进行排序、筛选、重新编排版式，这就是其编辑功能；还可以在透视表中增设字段、对汇总结果进行辅助计算，这就是其计算功能。两者结合，使数据透视表成为完整的数据分析工具。一、进行数据整理创建透视表前先检查数据格式：凡以文本形式存储的数字（单元格左上角带绿色三角标记），应通过错误提示中的"转换为数字"统一转为数值格式，避免汇总结果失真。本任务中"订单数量""入库数量"两列均需检查处理。筛选器的应用任务五·数据透视表的计算二、筛选器的应用将数据透视表放置在本表L4单元格，客户编码拖至"报表筛选"，产品名称拖至"行标签"，订单数量、入库数量拖至"数值"（图7-41）；在筛选器中选择KC客户，即可单独查看该客户各产品的订单与入库汇总（图7-42）。图7-41字段布局图7-42筛选KC客户的结果增设"生产差异"计算字段任务五·数据透视表的计算三、增设生产差异字段（1/2）选中数据透视表任一单元格，单击"数据透视表分析"选项卡→"字段、项目和集"→"计算字段"（图7-43）；在"插入计算字段"对话框中输入名称"生产差异"、公式=入库数量-订单数量（图7-44）。图7-43插入计算字段图7-44设置名称与公式查看"生产差异"字段的设置结果任务五·数据透视表的计算三、增设生产差异字段（2/2）设置完成后，数据透视表字段窗格中新增"生产差异"字段（图7-45）；勾选该字段后，透视表末列自动增加"求和项：生产差异"列（图7-46），直观地反映出KC客户各产品订单数量与入库数量的差异。图7-45字段窗格新增字段图7-46新增"求和项：生产差异"列设置条件格式：数据条任务五·数据透视表的计算四、设置条件格式选中"生产差异"列的数据区域O5:O7，单击"开始"选项卡→"条件格式"→"数据条"，选择一种数据条样式（图7-47）；设置后单元格内增加数据条，条的长度代表数值大小（图7-48）。如需清除，可在"条件格式"中选择"清除规则"。图7-47选择数据条样式图7-48数据条效果06课后实训及参考答案课后实训：判断题（附参考答案）课后实训·附参考答案一、判断题（√／×）为参考答案1．简单来说，单变量求解就是已知公式的计算结果，求公式中某个未知单元格的值。（√）2．已知算式z=3x+4y+1，当z=20、y=2时，可以使用单变量求解功能计算x的值。（√）3．执行"数据"→"模拟分析"→"单变量求解"命令，可以打开"单变量求解"对话框。（√）4．在单变量求解或规划求解过程中，需要确定目标单元格，目标单元格中必须已有设置好的计算公式。（√）5．在单变量求解或规划求解过程中，需要确定可变单元格，可变单元格为需要计算求解的单元格，该单元格中应事先设置好相应的计算公式。（×）6．单变量求解仅可解决单变量问题，而规划求解可处理多变量问题。（√）7．规划求解是Excel的一个可选安装模块，只有在系统安装了规划求解工具后，我们才能使用它。（√）8．财务决策中涉及很多优化的问题，如利润最大化、成本最小化、投资组合最优化等，这些问题都可以采用规划求解工具来解决。（√）课后实训：判断题（附参考答案）课后实训·附参考答案一、判断题（√／×）为参考答案9．规划求解的过程包括确定目标单元格、可变单元格、目标值与可变单元格之间的关系和约束条件等。（√）10．一般而言，目标单元格与可变单元格的数值关系可以通过计算公式建立。（√）11．执行"数据"→"规划求解"命令，可以打开"规划求解参数"对话框。（√）12．利用Excel提供的方案管理器工具，我们可以很方便地建立各种方案，随时显示各方案的执行结果。（√）13．表达式"=SUMPRODUCT(B10:C10,B11:C11)"与"=B10*B11+C10*C11"的含义一样。（√）14．SUMPRODUCT函数是指在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。（√）15．使用SUMPRODUCT(array1,array2,array3,…)函数时，式中array1、array2、array3数组参数必须具有相同的维数，否则函数将返回错误值#VALUE!。（√）课后实训：函数应用1（附参考答案）课后实训·附参考答案二、函数应用

影响利润的各因素当前数值p的最小值b的最大值q的最小值a的最大值销售单价/元90p=?909090单位变动成本/元6060b=?6060产销量/件200020002000q=?2000固定成本/元50000500005000050000a=?目标利润/元100000000表7-2E产品单变量求解表参考答案：p的最小值＝85元；b的最大值＝65元；q的最小值＝1,667件；a的最大值＝60,000元。将实训结果以"××××（学号）71.xls"命名保存。课后实训：函数应用2、3（附参考答案）课后实训·附参考答案二、函数应用2．东方公司同时生产甲、乙两种产品（表7-3）。问：公司应将各种产品的单位变动成本控制在什么水平，才能确保实现200,000元的目标利润？项目甲产品乙产品预计销售量/件7801200单位变动成本/元??预计最高单价/元150200固定成本总额/元5000050000单位消耗工时/时812机器工时总数/时2500025000表7-3甲、乙产品资料参考答案：第2题（规划求解）：以单位变动成本为可变单元格、目标利润200,000元为约束，解得甲产品单位变动成本应控制在40.74元，乙产品应控制在62.68元。第3题（规划求解）：在工时约束（8×780+12×乙≤25,000）下求解，乙产品至少应销售1,173件。提示：目标单元格为目标利润公式，可变单元格分别为单位变动成本、乙产品销售量。课后实训：函数应用2、3（附参考答案）课后实训·附参考答案二、函数应用3．若单位变动成本甲40元、乙60元（表7-4），问：企业至少要销售多少件乙产品，才能确保实现200,000元的目标利润？项目甲产品乙产品预计销售量/件780?单位变动成本/元4060预计最高单价/元150200固定成本总额/元5000050000单位消耗工时/时812机器工时总数/时2500025000表7-4甲、乙产品资料参考答案：第2题（规划求解）：以单位变动成本为可变单元格、目标利润200,000元为约束，解得甲产品单位变动成本应控制在40.74元，乙产品应控制在62.68元。第3题（规划求解）：在工时约束（8×780+12×乙≤25,000）下求解，乙产品至少应销售1,173件。提示：目标单元格为目标利润公式，可变单元格分别为单位变动成本、乙产品销售量。课后实训：函数应用4、5（附参考答案）课后实训·附参考答案二、函数应用4．资料如表7-5（销售量待定，单位变动成本甲40元、乙60元）。问：公司如何安排产品生产，才能确保实现利润最大化？项目甲产品乙产品预计销售量/件??单位变动成本/元4060预计最高单价/元150200固定成本总额/元5000050000单位消耗工时/时812机器工时总数/时2500025000表7-5甲、乙产品资料参考答案：第4题（利润最大）：甲产品单位工时贡献(150-40)/8=13.75元，高于乙产品(200-60)/12≈11.67元，全部工时投入甲产品——生产甲产品3,125件，停产乙产品。第5题（保本）：解(150-40)×甲+(200-60)×乙=50,000且两产品均生产，得甲产品172件、乙产品222件。将实训2—实训4的结果以"××××（学号）72.xls"命名保存。课后实训：函数应用4、5（附参考答案）课后实训·附参考答案二、函数应用5．资料如表7-6。问：若甲、乙两种产品必须都生产，公司将如何安排产品生产才能保本？项目甲产品乙产品预计销售量/件??单位变动成本/元4060预计最高单价/元150200固定成本总额/元5000050000单位消耗工时/时812机器工时总数/时2500025000表7-6甲、乙产品资料参考答案：第4题（利润最大）：甲产品单位工时贡献(150-40)/8=13.75元，高于乙产品(200-60)/12≈11.67元，全部工时投入甲产品——生产甲产品3,125件，停产乙产品。第5题（保本）：解(150-40)×甲+(200-60)×乙=50,000且两产品均生产，得甲产品172件、乙产品222件。将实训2—实训4的结果以"××××（学号）72.xls"命名保存。项目八资金需求量的预测Excel在财务中的应用参考教材：《Excel在财务中的应用》高等教育出版社（第五版）目录CONTENTS01任务一用销售百分比法预测资金需求量02任务二用回归分析法预测资金需求量03任务三用高低点法预测资金需求量04课后实训（附参考答案）01任务一用销售百分比法预测资金需求量任务目标与任务描述任务一·用销售百分比法预测资金需求量知识目标掌握IF函数及数据有效性在实务中的应用技巧，能在Excel中制作下拉式菜单，能制作一个通用的销售百分比法资金需求量预测模型；掌握Excel的公式输入方法，能正确运用"+""-""*""/"等运算符进行相关数据的计算。素养目标培养前瞻性管理思维，增强独立思考、自主钻研的学习能力；掌握举一反三的知识迁移能力，将所学方法用于财务决策。任务描述江南公司的资产负债表、利润表等相关资料如表8-1、表8-2所示，请采用销售百分比法预测该公司2027年的资金需求量。其他资料：销售增长率按营业收入三年平均增长率确定；营业净利率按近三年平均营业净利率确定；固定资产投资金额按投资计划表金额495万元确定；股利支付率假定为30%。销售百分比法的基本原理任务一·用销售百分比法预测资金需求量销售百分比法，是在分析资产负债表有关项目与销售额关系的基础上，根据市场调查和销售预测资料，确定资产、负债和所有者权益有关项目占销售收入的百分比，并依此推算流动资金需求量的方法。（1）预计销售增长率根据历史数据，预计销售收入增长率。（2）区分敏感项目敏感项目随销售额同步变动（如库存现金、应收账款、存货、应付账款等）；非敏感项目与销售额无紧密变动关系（如长期负债、实收资本等）。（3）计算需要增加的资金需增加的资金=敏感资产销售百分比×新增销售额-敏感负债销售百分比×新增销售额。（4）计算内部留存收益增加额内部留存收益增加额=预计销售额×预计销售净利率×（1-股利支付率）。（5）计算外部融资需求外部融资需求=需要增加的资金-内部留存收益增加额。

搜集整理企业近年来的销售、盈利资料任务一·用销售百分比法预测资金需求量二、Excel模型的设计：（一）搜集整理资料搜集整理企业近年来的营业收入、净利润、营业净利率、股利支付率等资料，作为资金需求预测的基本数据（图8-1）。在B20:D20区域计算各年度营业净利率，并用SUMIF函数在B36单元格录入公式，汇总2027年度固定资产投资额（图8-2）。=SUMIF(B24:B34,B35,C24:C34)图8-1历年来销售、盈利情况资料图8-2固定资产投资额条件求和知识链接8-1SUMIF函数任务一·用销售百分比法预测资金需求量知识链接8-1SUMIF函数使用SUMIF函数可以对区域中符合指定条件的值求和。其语法为：SUMIF(range,criteria,[sum_range])range：用于条件计算的单元格区域，区域中的单元格必须是数字或名称、数组或包含数字的引用，空值和文本值将被忽略；criteria：确定对哪些单元格求和的条件，形式可以为数字、表达式、单元格引用、文本或函数，如32、">32"、B5、"苹果"或TODAY()；任何文本条件或含有逻辑、数学符号的条件必须使用双引号括起来，条件为数字则无须双引号；sum_range：求和的实际单元格区域，可省略；省略时对range区域中符合应用条件的单元格求和。criteria中可使用通配符：问号（?）匹配任意单个字符，星号（*）匹配任意一串字符；若要查找实际的问号或星号，请在该字符前键入波形符（~）。需要多条件求和时，可以使用SUMIFS函数实现。设计资产负债表敏感性分析选择表任务一·用销售百分比法预测资金需求量（二）设计资产负债表敏感性分析选择表敏感项目需要随时调整，应在资产负债表中增加一列作为敏感项目调整列，由财务人员分析选择"是"或"否"填列（图8-3）。选择C4:C54单元格区域，执行"数据"→"数据工具"→"数据验证"：在"允许"框中选择"序列"，"来源"框中输入"是，否"（逗号为英文输入法状态下的逗号），单击"确定"（图8-4），即完成敏感项目的选择录入。图8-3敏感性分析选择表图8-4数据有效性的设置知识链接8-2数据验证任务一·用销售百分比法预测资金需求量知识链接8-2数据验证数据验证是对单元格或单元格区域输入的数据从内容到数量上进行限制：符合条件的数据允许输入，不符合条件的数据禁止输入。这样可以依靠系统检查数据的有效性，避免错误的数据录入。数据验证功能可以在尚未输入数据时预先设置，以保证输入数据的正确性。允许输入符合条件的数据正常录入，如下拉序列中的"是/否"禁止输入不符合条件的数据被系统拒绝并提示预先设置在录入数据前先行设置，从源头保证数据正确计算敏感项目的销售百分比任务一·用销售百分比法预测资金需求量（三）计算敏感项目销售百分比确定敏感项目后，根据"敏感项目占销售收入的百分比=（基期敏感项目数额÷基期销售额）×100%"计算各项目销售百分比。在D6单元格中输入：=IF(C6="是",B6/利润表!$B$3,"")公式含义：如果C6单元格的值是"是"，则执行资产负债表中敏感项目金额（B6）除以"利润表"工作表中营业收入（B3）的操作；如果C6不是"是"，则D6显示为空。采用销售百分比法预测资金需求，是假定预计年度敏感项目金额占销售收入的比重不变。式中采用绝对引用（$B$3），公式填充复制到其他单元格时营业收入数据始终保持不变。上述公式计算的是货币资金项目占上年销售收入的比重；将公式向下填充，即可完成所有报表项目销售百分比的计算。计算敏感性资产、负债的销售百分比之和任务一·用销售百分比法预测资金需求量（四）分别计算敏感性资产和敏感性负债之和对各敏感项目的销售百分比自动求和，计算结果保留4位小数，以减少尾数误差带来的影响（图8-5、图8-6）。D28：=ROUND(SUM(D5:D26),4)D54：=ROUND(SUM(D31:D53),4)结果解读：敏感资产销售百分比合计53.28%；敏感负债销售百分比合计34.83%。图8-5敏感资产的销售百分比图8-6敏感负债的销售百分比按销售百分比法预测资金需求量任务一·用销售百分比法预测资金需求量操作提示8-1用SUMIF函数条件求和D28单元格也可用SUMIF函数实现：敏感项目为"是"时对D5:D26区域的销售百分比求和，即用"=SUMIF(C5:C26,"是",D5:D26)"代替"=SUM(D5:D26)"。公司每增加销售收入100元，需要增加53.28元的资产，同时增加34.83元的商业信用（属自发性负债筹资），最终需净增加资金18.45元（53.28-34.83）。在B39单元格录入"=(B3/E3)^(1/3)-1"，完成营业收入三年平均增长率的计算（32.03%）。为提高模板通用性，表中所有数据均采用链接方式，便于动态更新（图8-7）。图8-7按销售百分比法预测资金需要量计算新增资金需求与外部融资需求任务一·用销售百分比法预测资金需求量（五）需要增加的资金B48：=B42*(B44-B45)因销售增长新增资金=29000×32.03%×(53.28%-34.83%)≈1,713万元引用固定资产投资B49：=B36引用2027年固定资产投资新增资金495万元新增资金总需求B50：=B48+B492027年新增资金需求=1,713+495=2,208万元（六）内部留存收益B52：=INT(B43*B40*(1-B41))38288×6.67%×(1-30%)≈1,789万元（七）外部融资需求B53：=B50-B522,208-1,789=419万元结论：依据基期报表项目占销售收入的比例关系和项目投资计划，2027年度新增资金需求为2,208万元，其中内部留存收益可提供1,789万元，公司需向外部筹资419万元。至此，一个简易的销售百分比法资金需求预测模型制作完成。知识链接8-3INT函数向下取整任务一·用销售百分比法预测资金需求量知识链接8-3INT函数向下取整INT函数将数字向下舍入到最接近的整数。其语法为：INT(number)，式中number为需要进行向下舍入取整的实数。例如：INT(8.9)=8，INT(-8.9)=-9。本任务中用INT函数对新增留存收益等计算结果取整。操作提示8-2数据验证的作用如果修改资产负债表中的敏感项目（"是/否"选择），最终的计算结果是否会发生变化？由于模型中各计算环节均通过公式与敏感性分析选择列联动，调整任一项目的"是/否"选择后，销售百分比合计、新增资金需求与外部融资需求都会自动重算——这正是数据验证设置在通用预测模型中发挥的作用。02任务二用回归分析法预测资金需求量任务目标与任务描述任务二·用回归分析法预测资金需求量知识目标能调用Excel分析工具库中的工具用于财务实务决策；能正确使用分析工具库中的回归分析法，正确设置Y值与X值，能根据计算结果编写分析项目的回归公式。素养目标培养统计分析思维，理解数据规律，揭示数据背后的因果关系；增强用数据说话的实证意识，提高依据数据进行客观、公正、科学分析的能力；坚持守正创新，在传统方法基础上探索更精准的预测工具。任务描述中南公司2022—2026年度的资金占用与销售收入如表8-3所示。假设2027年的预计销售收入为700万元，请用回归分析法预测中南公司2027年的资金需求量。年度业务量x/万元资金占用y/万元20225001002023520110202448012020255401252026690130表8-3资金占用与销售收入回归分析法的基本原理任务二·用回归分析法预测资金需求量

由n组观测值构成方程组，可求出固定资金a与单位变动资金b：

年度业务量x资金占用yx·yx²202250010050000250000202352011057200270400202448012057600230400202554012567500291600202669013089700476100合计Σx=2730Σy=585Σxy=322000Σx²=1518500表8-4中南公司回归分析计算表（万元）利用Excel分析工具库：加载分析工具库任务二·用回归分析法预测资金需求量二、利用Excel分析工具库进行公式的设置（1）加载宏：单击Office按钮进入Excel选项，单击"加载项"，选择"分析工具库"（图8-8）。（2）调用分析工具库：管理Excel加载项，单击"转到"按钮，进入"加载宏"选项界面，勾选"分析工具库"（图8-9、图8-10）。图8-8加载分析工具库图8-9转到分析工具库图8-10"加载宏"选项界面选择回归分析并设置计算区域任务二·用回归分析法预测资金需求量（3）选择回归分析方法：单击"数据"选项卡，选择"数据分析"功能，在"数据分析"对话框中选择"回归"，单击"确定"（图8-11）。（4）设置因变量和自变量计算区域：Y值输入区域选择$B$3:$F$3（资金占用金额，因变量），X值输入区域选择$B$2:$F$2（业务量，自变量），输出区域选择$A$6（图8-12）。图8-11选择回归分析法图8-12自变量与因变量区域的选择输入编辑资金预测的线性回归方程任务二·用回归分析法预测资金需求量（5）编辑资金预测的线性回归方程：在系统输出的回归结果中，根据B26、B27单元格的系数（a值为固定资金需求量，b值为单位变动资金需求量），写出资金预测的线性回归方程（图8-13）：

假设2027年预计销售收入为700万元：2027年资金需求量＝66.3503+0.0928×700≈131.31万元图8-13根据输出结果编制资金需求公式同步训练8-1：回归分析方程式的确定任务二·用回归分析法预测资金需求量同步训练8-1回归分析法不仅可以用来预测资金需求量，也可以用于分解混合成本。假设江东机械厂1—5月机器工作小时与维修成本的变动情况如表8-5所示，请利用回归分析法写出维修费的混合成本预测公式。月份1月2月3月4月5月业务量x/千机器小时68569维修费y/元120130100125140表8-5机器工作小时与维修成本变动情况

03任务三用高低点法预测资金需求量任务目标与任务描述任务三·用高低点法预测资金需求量知识目标掌握MAX、MIN函数的使用，能利用该函数在一组数据中找到最大值与最小值；理解HLOOKUP函数的使用，能在数据表首行按指定条件查找，并精确返回指定行数的值；掌握CONCATENATE函数的使用，能将多个单元格中的数字或文本合并成一个简要的字符串。素养目标增强方法论意识，根据实际条件选择恰当的预测工具；理解极端值与常态的关系，培养去伪存真、去粗取精的分析能力。任务描述中南公司2022—2026年度的资金占用情况与销售收入之间的关系如表8-6所示。假设2027年的预计销售收入为700万元，请用高低点法预测中南公司2027年的资金需求量。年度业务量x/万元资金占用y/万元202250010020235201102024（低点）48012020255401252026（高点）690130表8-6资金占用情况与销售收入高低点法的含义任务三·用高低点法预测资金需求量一、高低点法的含义

方法特点：简便易算，只要有两个不同时期的业务量和资金占用情况即可求解，使用较为广泛；只根据最高、最低两组数据，不考虑其他业务量变化，计算结果往往不够精确；选用的历史数据应能代表业务活动的正常情况，不应含有异常状态下的数据；求得的公式只适用于相关范围（本例为业务量480万～690万元），超出相关范围即不适用。编制资金需求预测的线性公式任务三·用高低点法预测资金需求量二、编制资金需求预测的线性公式

图8-14高低点法预测资金需求模型公式说明：B8=MAX(B3:F3)确定业务量高点；B9=MIN(B3:F3)确定业务量低点；B10/B11=HLOOKUP(…)精确查找高、低点对应的资金占用额；B15=ROUND((B10-B11)/(B8-B9),4)计算单位变动资金b；B16=ROUND(B10-B8*B15,4)计算固定资金a；B17=CONCATENATE("y=",B16,"+",B15,"x")合成资金预测公式。知识链接：MAX、MIN与HLOOKUP函数任务三·用高低点法预测资金需求量知识链接8-4MAX与MIN函数MAX(number1,number2,…)返回一组数值中的最大值；MIN(number1,number2,…)返回一组数值中的最小值。参数为1到255个要找出最大值或最小值的数字，可以是数字或包含数字的名称、数组或引用。知识链接8-5HLOOKUP函数当比较值位于数据表的首行、要查找下方给定行中的数据时，使用HLOOKUP函数（H代表"行"；比较值位于左侧列时用VLOOKUP）。语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)lookup_value：需要在数据表第一行中查找的数值，可为数值、引用或文本字符串；table_array：用于查找数据的数据表，其第一行可为文本、数字或逻辑值；row_index_num：待返回匹配值的行序号（为1返回第一行，为2返回第二行，以此类推）；range_lookup：逻辑值，TRUE或省略返回近似匹配值；FALSE查找精确匹配值，找不到则返回错误值#N/A。同步训练8-2：用高低点法分解混合成本任务三·用高低点法预测资金需求量同步训练8-2高低点法不仅可用于预测资金需求，也可用于分解混合成本。请根据江东机械厂1—5月机器工作小时和维修成本的变动情况（表8-5），将图8-14模型中A3:F4区域的数据修改为表8-5的数据，验证其混合成本预测公式是否为y=50+10x。月份1月2月3月4月5月业务量x/千机器小时68569维修费y/元120130100125140表8-5机器工作小时与维修成本变动情况

04课后实训及参考答案课后实训：函数基础·判断正误（附参考答案）课后实训·附参考答案一、函数基础（判断正误）（√／×）为参考答案1．如果A3:A12单元格区域包含数字，则公式"=AVERAGE(A3:A12)"将返回这些数字的平均值。（√）2．使用AVERAGE函数时，如果区域或单元格引用的参数包含文本、逻辑值或空单元格，这些值将被忽略，但包含零值的单元格将被计算在内。（√）3．若只对符合某些条件的值计算平均值，请使用AVERAGEIF函数或AVERAGEIFS函数。（√）4．如果单元格区域A3:A12包含数字，则公式"=INT(AVERAGE(A3:A12))"将返回这些数字的平均值的整数。（√）5．INT函数将数字向下舍入到最接近的整数。（√）6．表达式"=INT(8.9)"将8.9向下舍入到最接近的整数，其结果为8。（√）7．表达式"=INT(-8.9)"将-8.9向下舍入到最接近的整数，其结果为-9。（√）8．表达式"=SUMIF(A1:A12,">5")"对数据区域中大于5的值进行求和。（√）课后实训：函数基础·判断正误（附参考答案）课后实训·附参考答案一、函数基础（判断正误）（√／×）为参考答案9．表达式"=(27/1)^(1/3)-1"的计算结果为2。（√）10．表达式"=SUMIF(A:A,">5",C:C)"表示，若A列单元格的值">5"，则对相应C列的数值进行累计求和。（√）11．表达式"=IF(5>9,"这是不可能的","这是真的吗？")"，其值将显示"这是真的吗？"。（√）12．表达式"=A1"表示对A1单元格的引用，将该公式复制到A3单元格，则引用A3单元格的值。（√）13．表达式"=A$1"表示对A1单元格的绝对行引用，将该公式复制到A3单元格，则引用A1单元格的值。（√）14．表达式"=$A1"表示对A1单元格的绝对列引用，将该公式复制到B3单元格，则引用A3单元格的值。（√）15．表达式"=$A$1"表示对A1单元格的绝对引用，将该公式复制到A3单元格，则仍引用A1单元格的值。（√）课后实训：函数应用1（附参考答案）课后实训·附参考答案二、函数应用1．东北公司拟采用销售百分比法预测2027年资金需求量，项目敏感性分析如表8-7、表8-8所示（摘要）。其他资料：销售增长率20%，近年来平均销售净利率10%，平均股利支付率40%。表8-7资产负债表（摘要）20252026敏感性货币资金10005169是交易性金融资产09000是应收票据及应收账款700018954是存货1260010283是应付票据及应付账款73907959是应交税费04659是表8-8利润表（摘要）20252026营业收入180000216000净利润2002520369单位：万元——参考答案：（1）按表中敏感性分析，用销售百分比法测算，2027年需向外融资9,395万元。（2）若存货、交易性金融资产改为非敏感项目（数据验证改选"否"，模型自动重算），2027年需向外融资13,251万元。将实训结果以"××××（学号）81.xls"命名保存。课后实训：函数应用2（附参考答案）课后实训·附参考答案二、函数应用2．南方公司是一家高负债企业，收入与筹资活动密切相关。公司近年来营业收入与筹资活动现金流入如表8-9所示，要求采用回归分析法预测其2027年的筹资额。年份历年营业收入x/元筹资活动现金流入y/元2023638006310229202476672255351720251055885256042202617848201692373表8-9南方公司近年营业收入与筹资活动现金流入

课后实训：函数应用3（附参考答案）课后实训·附参考答案二、函数应用3．假设江南公司流动资产项目中，除"一年内到期的非流动资产"与"其他流动资产"为非敏感资产外其余均为敏感资产；长期资产项目中除"固定资产"为敏感资产外其余均为非敏感资产。要求在D列录入单元格"是，否"的有效性设置（图8-15），并完成：（1）在C列计算各项资产占总资产的比重，在C29计算敏感资产累计比重；（2）利用IF函数在C列计算各敏感资产的比重，并在C29计算累计比重。参考答案：敏感性资产占资产比重累计为88.96%。C29可用SUMIF条件求和：=SUMIF(D7:D27,"是",C7:C27)；用IF函数逐项判断（=IF(D7="是",B7/资产总计,"")）后求和，结果一致。图8-15江南公司的敏感资产比重分析课后实训：函数应用4（附参考答案）课后实训·附参考答案二、函数应用4．江南公司经营铅笔、毛笔、圆珠笔、钢笔的销售，销售部门有A001、A002、A003、B001、B002五位销售人员，季末销售清单如图8-16所示。请利用SUMIF、SUMIFS函数及数据有效性设置制作销售情况查询表，实现：（1）按工号查询累计销售量；（2）按商品查询累计销售量；（3）对大于等于0、大于等于100的销售量分别累计汇总；（4）按工号和商品组合查询累计销售量。图8-16SUMIF与SUMIFS函数的应用参考答案：（1）（2）单条件查询用SUMIF函数配合数据有效性下拉选择（工号或商品）；（3）汇总条件分别设置为">=0"、">=100"；（4）多条件查询用SUMIFS函数，F14单元格公式为：=SUMIFS(C:C,A:A,D14,B:B,E14)其中D14为工号查询条件、E14为商品查询条件，均通过数据有效性下拉菜单选择。课后实训：函数应用5（附参考答案）课后实训·附参考答案二、函数应用5．江南公司生产部的人员情况如图8-17所示，月基本工资标准为：博士9,000元；硕士8,000元；本科6,500元；专科5,000元；高中3,000元；高中以下2,000元。请利用IF函数及嵌套公式在C列实现基本工资的自动录入。图8-17江南公司生产部的人员情况参考答案：C3单元格公式（向下填充）：=IF(B3="博士",9000,IF(B3="硕士",8000,IF(B3="本科",6500,IF(B3="专科",5000,IF(B3="高中",3000,2000)))))嵌套IF按学历层次依次判断，均不满足时按"高中以下"返回2,000元。项目九财务报表的分析Excel在财务中的应用参考教材：《Excel在财务中的应用》高等教育出版社（第五版）目录CONTENTS01任务一单元格名称的定义与修改02任务二基本财务比率的计算03任务三结构百分比财务报表的制作04任务四比较财务报表的制作05任务五雷达图的制作与阅读06任务六制作动态图表（一）07任务七制作动态图表（二）08任务八制作动态图表（三）09课后实训（附参考答案）01任务一单元格名称的定义与修改任务目标与任务描述任务一·单元格名称的定义与修改知识目标掌握单元格名称和单元格区域名称的定义、修改、删除等基本操作；能利用单元格名称进行公式的设置，使公式更易于理解和维护。素养目标培养规范命名意识，理解标准化管理对团队协作的重要性；增强数据管理素养，建立清晰的财务数据架构思维。任务描述为了便于在计算财务比率时能直接识别相应的数据源，利于公式的理解与维护，要求对江南公司财务报表中的各期数据根据其对应的报表项目进行名称定义，为制作财务报表分析模型做准备（报表数据参见项目八表8-1、表8-2）。一、定义单元格名称任务一·单元格名称的定义与修改很难直接理解公式“=E5/E6”的含义，若表示为“=流动资产/流动负债”，就能理解其意指流动比率。定义单元格名称可使公式更易于理解和维护。（1）在编辑栏“名称”框中定义选择B4:F4单元格区域，在编辑栏的“名称”框中输入“货币资金”，则该区域的名称被定义为“货币资金”，便于以后在公式中引用识别。也可以选择某一单元格进行名称设置。图9-1通过编辑栏的“名称”框定义单元格名称（2）右键选择“定义名称”选择B5:F5单元格区域，右键单击后选择“定义名称”菜单栏，出现“新建名称”窗口，对话框中自动显示所选单元格左侧的名称，如“交易性金融资产”。图9-2“新建名称”窗口（3）根据所选内容创建名称任务一·单元格名称的定义与修改操作提示9-1单元格名称的使用范围“范围”有工作簿与工作表（Sheet1、Sheet2等）可供选择：若选择工作簿，则该名称对本工作簿中的所有工作表均可识别，但对其他工作簿不可识别；若选择工作表，则只能在当前工作表中使用。系统默认引用位置为绝对引用方式，实务中可根据需要修改为混合引用方式，以增加名称在公式设置方面的灵活性。按上述方法逐一定义名称费时费力。执行“公式”选项卡“定义的名称”组中的“根据所选内容创建”命令，能基于单元格区域的现有行和列标签批量创建名称：选择A2:F52单元格区域，单击“根据所选内容创建”，在对话框中选择“首行”和“最左列”，单击“确定”。图9-3根据所选内容创建单元格名称图9-4以首行和最左列作为单元格名称二、删除、修改单元格名称任务一·单元格名称的定义与修改有的报表项目并不参与计算，或与日常使用习惯不符（如“流动资产”“流动负债”“所有者权益”仅是分类名称，没有实际数据，却又与财务指标计算要素同名），为使财务分析模板更具通用性，需要对已定义的名称进行删除或修改。单击“公式”→“名称管理器”，选择需要删除或修改的名称，单击“删除”或“编辑”即可。图9-5已定义好的单元格名称图9-6删除或修改不符合要求的名称图9-7根据需要对名称进行修改或删除如“流动资产合计”可通过“编辑”修改为“流动资产”；“流动负债”没有相应数据且与指标同名，可修改或删除（若不修改，设置公式时须选择“流动负债合计”，会降低模板可读性）。修改时要确保单元格名称的唯一性。利润表名称定义与同步训练任务一·单元格名称的定义与修改同理，对利润表的单元格数据进行同样的操作，定义与修改单元格名称使其符合工作需要，例如“减：营业成本”可以修改为“减_营业成本”。图9-8定义与修改利润表中的单元格名称同步训练9-1单元格名称的定义与编辑请对课后实训专项训练1中资产负债表的各年数据进行如下操作：（1）以“根据所选内容创建”命令创建单元格名称，并将“负债合计”修改为“负债”、“流动负债合计”修改为“流动负债”、“资产合计”修改为“资产”；（2）只保留“存货”“负债”“流动负债”“流动资产”“资产”的单元格名称，将其他名称全部删除；（3）检查所建立的名称是否适用整个工作簿。图9-9单元格名称的定义与编辑（训练结果）02任务二基本财务比率的计算任务二基本财务比率的计算02任务二知识目标●理解财务分析模型设计的基本要求，能够引用事先定义好名称的单元格进行财务比率指标计算公式的编辑。●掌握AVERAGE函数的运用，能够利用该函数正确计算所选数据的平均值。●掌握三年营业收入平均增长率等同类计算公式的录入，能够对开多次方根的计算公式进行编辑。素养目标●理解财务比率背后的经营实质，培养透过数据看本质的能力。●增强客观公正意识，能依据数据进行科学分析而不主观臆断。图9-10基本财务指标计算模型任务描述根据江南公司已完成定义的报表项目名称，编辑常用的财务比率指标计算公式，制作一张财务指标计算表。二、编辑财务指标的计算公式02任务二之前已经对单元格进行了名称定义，因此编辑计算公式时，可直接根据公式的内容输入名称。本例仅讲述偿债能力指标公式的编辑，其余指标的设置自行完成。流动比率（B4单元格）：=流动资产/流动负债利息保障倍数（B8单元格）：=（所得税费用+净利润+财务费用）/财务费用采用名称方式可使公式编辑更加方便，也便于公式的审核。再将相应的公式复制到其他各列单元格中，即可得出各期指标的计算结果。图9-11引用已定义好的单元格名称编辑计算公式数组公式与AVERAGE函数02任务二操作提示9-2以数组公式完成公式录入●图9-11中各年度的流动比率均等于各年的流动资产除以流动负债，也可采用数组公式录入：选择B4:F4单元格区域，输入“=流动资产/流动负债”，然后按Ctrl+Shift+Enter组合键锁定数组公式，即可计算出各年度的流动比率。●Excel将在公式两边自动加上花括号“{}”。注意：不要自己键入花括号，否则Excel会认为输入的是一个正文标签。●对于某些指标需要引用的数据，如果未曾定义或不便定义名称，则仍需按照通常的引用方式引用数据计算，如图9-11中平均资产负债率的计算。知识链接9-1AVERAGE函数●该函数可以对一个或多个值执行运算，并返回一个或多个值的平均值（算术平均值）。●语法：AVERAGE(number1,number2,...)，参数可以是数字、单元格引用或单元格区域，最多可包含255个。●对单元格中的数值求平均值时，应牢记空单元格与含零值单元格的区别：空单元格不计算在内，但含零值单元格会计算在内。三、特殊的计算公式编辑02任务二（1）涉及平均值的财务指标计算。财务指标中有大量指标需要计算平均值，如“总资产周转率＝营业收入/平均资产总额”。可采用嵌套方式编辑：选择B14单元格，输入“=”，选择“利润表”工作表中的B3单元格引用营业收入，键入“/”，再调用AVERAGE函数，数据范围选择资产负债表中的B23:C23单元格区域，引用期初、期末资产数据计算平均值；随后采用填充方式将公式复制到其他各列。=利润表!B3/AVERAGE(资产负债表!B23:C23)图9-12总资产周转率计算公式的录入三、特殊的计算公式编辑02任务二（2）涉及开根号的财务指标计算。计算三年营业收入平均增长率时会涉及开立方根的计算。三年前营业收入总额指企业三年前的营业收入总额，如评价2026年的绩效状况，则指2023年的营业收入总额。

表9-1江南公司各年度的营业收入单位：元年度2026年度2025年度2024年度2023年度营业收入29000180001600012600=(29000/12600)^(1/3)-1计算结果约为32.03%，即江南公司近三年的营业收入平均增长率为32.03%。若开n次方根，则在公式中录入“^(1/n)”即可。图9-13Excel立方根公式的编辑录入图9-13中的B38单元格即采用这种方式实现计算，式中“利润表!B3”引用了2026年度的数据，“利润表!E3”则引用了2023年度的数据。同步训练9-202任务二同步训练9-2以引用单元格名称方式进行财务指标计算请完成项目九课后实训专项训练1资料中表9-4的各种财务指标计算。利润表、资产负债表中参与计算的各期数据一律通过引用单元格名称方式完成，可按下列计算公式进行相应的单元格名称定义：资产负债率＝负债/资产×100%流动比率＝流动资产/流动负债资产周转率（按各期资产计算）＝营业收入/资产存货周转率（按存货平均值计算）＝营业收入/AVERAGE(期初存货,期末存货)资产增长率＝(期末资产总计-期初资产总计)/期初资产总计×100%净资产增长率＝(期末净资产-期初净资产)/期初净资产×100%营业净利率＝净利润/营业收入×100%营业成本率＝营业成本/营业收入×100%提示：期初数与期末数需要一一进行单元格名称定义；公式录入能采用数组方式录入的，就尽量采用数组方式录入，以提高录入效率。图9-14东北公司财务指标计算公式及其计算结果03任务三结构百分比财务报表的制作任务三结构百分比财务报表的制作03任务三知识目标●掌握相对引用与绝对引用的区别与应用，能利用各种引用方式制作结构百分比报表和趋势百分比报表。●掌握条件格式的使用，能利用条件格式的设置突出显示需要的数据。●掌握IF函数的使用，能利用该函数设置简单智能化的计算公式。●掌握Ctrl+Shift+%快捷键的应用。素养目标●理解结构分析对把握全局的重要性，培养整体观念。●增强比例思维，理解部分与整体的辩证关系。任务描述请根据江南公司的财务数据，制作结构百分比财务报表，对大于15%的比率能自动以“浅红色填充”方式显示；在利润表中，若项目分子为零，则自动显示为空格，不参与计算。一、结构百分比财务报表制作的基本原理03任务三结构百分比分析是在财务报表比较的基础上发展而来的。它以财务报表中的某个总体指标作为基准100%，再计算其各组成项目占该总体指标的百分比，对各个项目百分比的增减变动进行比较分析，以此来判断有关财务活动的变化趋势。例如，分析利润表项目构成时，以营业收入作为基准100%；分析资产负债表项目构成时，以资产为基准100%。这种比较方法可以用于发现存在显著变化的项目，为进一步分析指明方向。在采用比较分析法时，必须注意以下问题：（1）用于进行对比的各个时期的指标，在计算口径上必须一致。（2）需要剔除偶发性项目的影响，使分析的数据能反映正常的经营状况。（3）应用例外原则，应对某项有显著变动的指标作重点分析，分析其产生的原因，以便采取对策，趋利避害。制作结构百分比财务报表的手工计算工作量过大，此时可以借助Excel强大的计算功能来实现。二、结构百分比财务报表的制作03任务三（1）复制财务报表格式。打开财务报表工作簿，选择“利润表”工作表，单击鼠标右键，执行“移动或复制工作表”命令，选择“建立副本”，单击“确定”；将新建“利润表（2）”工作表标签重命名为“利润表结构百分比分析”，将其中的数据全部删除，并对格式进行适当调整。图9-15复制财务报表格式（2）编辑结构百分比计算公式。进行利润表结构百分比分析时，将营业收入作为100%，其他项目均除以营业收入计算结构百分比。首先在B3单元格中输入公式，式中采用绝对行（B$3）引用方式，目的是将分母固定在第3行，始终以营业收入作为分母；然后向下填充复制到B4:B20，再选中B3:B20区域将公式填充复制到C3:F20。=利润表!B3/利润表!B$3图9-16利润表结构百分比分析二、结构百分比财务报表的制作03任务三如果计算的结果没有以百分比显示，可选择数据区域，按Ctrl+Shift+%组合键，将所选的单元格数据转换为百分比形式。图9-17编辑结构百分比计算公式（3）采用条件格式设置单元格格式。选择B3:F20单元格区域，执行“开始”→“条件格式”→“突出显示单元格规则”→“大于”命令，将比率大于15%的所有单元格设置为“浅红色填充”显示，以提醒使用者注意。图9-18条件格式的设置若要取消单元格条件格式，则执行“开始”→“条件格式”→“清除规则”→“清除整个工作表的规则”命令即可。图9-19取消单元格条件格式二、结构百分比财务报表的制作03任务三（4）使用IF函数，使分子为零的项目不参与计算。计算完成的报表中存在0%这样的数据，影响报表查看，需要让分子为零的单元格不参与计算，自动显示为空格，IF函数可以实现这项功能。知识链接9-2IF函数在指定条件下，该函数根据计算结果为TRUE或FALSE，返回不同的结果。语法：IF(logical_test,value_if_true,value_if_false)。logical_test表示计算结果为TRUE或FALSE的任意值或表达式；value_if_true是logical_test为TRUE时返回的值；value_if_false是logical_test为FALSE时返回的值。=IF(利润表!B3=0,"",利