第2章 会计报表
会计报表是企业财务报告的主要部分，是企业向外传递会计信息的主要手段。会计报表根据日常会计核算资料定期编制，是综合反映企业某一特定时期财务状况和某一会计期间经营成果、现金流量的总结性书面文件。现在的会计报表是企业的会计人员根据一定时期（例如月、季、年）的会计记录，按照既定的格式和种类编制的系统的报告文件。随着企业经营活动的扩展，会计报表的使用者对会计信息的需求在不断增加，仅仅依靠几张会计报表提供的信息已经不能满足或不能直接满足他们的需求，因此需要通过报表以外的附注和说明提供更多的信息。在本章中我们不仅要学习一些典型会计报表的相关知识，更重要的是学习使用ExceI 2016软件制作这些会计报表的方法。
2.1 现金流量表
现金流量表是财务报表的3个基本报告之一，表达的是在某一固定期间（通常是每月或每季）一家机构的现金（包含银行存款）的增减变动情况。
现金流量表主要是反映资产负债表中各个项目对现金流量的影响，并根据其用途划分为经营、投资及融资3个活动分类。它的编制具有很大的意义。首先，它弥补了资产负债信息量的不足；其次，便于从现金流量的角度对企业进行考核；最后，现金流量表还有助于了解企业筹措现金、生成现金的能力。
2.1.1 编制现金流量表
步骤 01：启动Excel 2016软件后。新建一个空白工作簿。打开新建的空白工作簿，双击工作表的名称标签，使其处于编辑状态，将其名称设置为“现金流量表”，如图2-1所示。
图2-1 修改表格名称
步骤 02：在表格中选择B列，在主页功能区切换到“开始”选项卡下，在“单元格”选项组中单击“格式”下拉按钮，在弹出的下拉菜单中选择“列宽”命令，如图2-2所示。
图2-2 选择列宽命令
步骤 03：在弹出的“列宽”对话框中，将“列宽”设置为32，并单击“确定”按钮，如图2-3所示。
图2-3 设置列宽
步骤 04：使用同样的方法，将C～F列的“列宽”设置为18，完成后的效果如图2-4所示。
图2-4 设置列宽后的效果
步骤 05：选择第2行单元格区域，在数字2的位置右击，在弹出的快捷菜单中选择“行高”选项，如图2-5所示。
图2-5 选择“行高”选项
步骤 06：在弹出的“行高”对话框中，将“行高”设置为43，单击“确定”按钮，如图2-6所示。
图2-6 设置行高
步骤 07：使用同样方法将第3行的“行高”设置为13.5，第4行的“行高”设置为25，第5～37行的“行高”设置为20，如图2-7所示。
图2-7 设置行高后的效果
步骤 08：选择B2:F2单元格区域，在主页功能区切换到“开始”选项卡，在“对齐方式”组中单击“合并后居中”按钮，将其合并居中，如图2-8所示，效果如图2-9所示。
图2-8 选择“合并后居中”
图2-9 合并后居中效果图
步骤 09：在上一步合并单元格中配合空格键输入表名“现金流量表”，在主页面中功能区切换到“开始”选项卡，在“字体”选项组中，将“字体”设置为“宋体-方正超大字符集”，字号设置为26，如图2-10所示。
图2-10 输入文字
步骤 10：使用前面讲过的方法将E3:F3单元格进行合并，并在合并的单元格中输入文字“年度：2016”，将“字体”设置为“宋体-方正超大字符集”，字号设置为11，如图2-11所示。
图2-11 输入文本
步骤 11：在B4:F4单元格中结合空格键分别输入文字“项目名称”“第一季度”“第二季度”“第三季度”“第四季度”，将“字体”设置为“宋体-方正超大字符集”，字号设置为14，并单击“加粗”按钮，效果如图2-12所示。
图2-12 输入文字
步骤 12：在表格中选择B4:F37单元格区域，在主页功能区中切换到“开始”选项卡，在“字体”选项组中单击“边框设置”右侧的下三角按钮，在弹出的下拉菜单中选择“其他边框”命令，如图2-13所示。
图2-13 选择“其他边框”命令
步骤 13：在弹出的“设置单元格格式”对话框中，切换到“边框”选项卡下，选择如图2-14所示的线条样式，并单击“外边框”按钮。
图2-14 设置外边框
步骤 14：继续选择线条样式，然后单击“内部”按钮，单击“确定”按钮，如图2-15所示。
图2-15 设置内部边框
步骤 15：设置边框后的效果如图2-16所示。
图2-16 设置边框效果
步骤 16：使用前面介绍的方法分别对B5:F5、B16:F16、B27:F27单元格进行合并，且在合并的单元格中分别输入文字“一、经营活动产生的现金流量”“二、投资活动产生的现金流量”“筹资活动产生的现金流量”，并将“字体”设置为“方正大标宋简体”，字号设置为12，将“填充颜色”设置为“黄色”，将“字体颜色”设置为“红色”，完成后的效果如图2-17所示。
图2-17 预览效果图
步骤 17：在B列单元格中输入文字，并将字体改为“微软雅黑”，“字号”设置为9，如图2-18所示。
图2-18 输入文字并设置
步骤 18：在单元格中输入数据，如图2-19所示。
图2-19 输入数据
步骤 19：在C9单元格中输入公式“=SUM（C6:C8）”，按Enter键完成公式的输入，如图2-20所示。
图2-20 输入公式
步骤 20：在单元格中选择C9单元格，将光标置于该单元格的右下角，按住鼠标左键向右拖动至F9单元格，复制公式，效果如图2-21所示。
图2-21 复制公式
步骤21：在C14单元格中输入公式“=SUM（C10:C13）”，按Enter键完成公式的输入，将光标置于该单元格的右下角，按住鼠标左键向右拖动至F14单元格，复制公式，如图2-22所示。
图2-22 输入公式并复制
步骤 22：使用与上面相同的方法，分别在C15单元格中输入公式“=C9-C14”，在C21单元格中输入公式“=SUM(C17:C20)”，在C25单元格中输入公式“=SUM (C22:C24)”，在C26单元格中输入公式“=C21-C25”，在C31单元格中输入公式“=SUM (C28:C30)”，在C35单元格中输入公式“=SUM(C32:C34)”，在C36单元格中输入公式“=C31-C35”，在C37单元格中输入公式“=C15+C26+C36”，并以此对公式进行复制，完成后的效果如图2-23所示。
图2-23 输入公式并复制
步骤 23：在表格中选择带有数据的单元格，在主页功能区中切换到“开始”选项卡下，在“数字”选项组中将“数字模式”设置为“会计专用”，如图2-24所示，效果如图2-25所示。
图2-24 设置数字模式
图2-25 设置数字模式效果
步骤 24：选择含有“现金流入小计”“现金流出小计”及“现金及现金等价物增加净额”的数字行，在主页功能区中切换到“开始”选项卡下，将填充颜色设置为“蓝-灰，文字2，淡色60%”，如图2-26所示，最终完成后的效果如图2-27所示。
图2-26 设置填充颜色
图2-27 最终完成后的效果
2.1.2 在不同的视图下现金流量表的查看方式
Excel 2016提供了多种视图方式，主要包括普通视图、页面布局视图、分页预览视图和自定义视图，用户可以根据需要在不同的视图方式下查看表格，通常默认的视图方式为普通视图方式。
1．普通视图
普通视图是Excel 2016默认的视图方式，适合于对表格进行设计和编辑。
操作步骤如下：在主页功能区中切换到“视图”选项卡下，在“工作簿视图”组中便可以看到表格默认的视图方式为“普通”视图，如图2-28所示。
图2-28 普通视图
2．页面布局视图
在“页面布局”视图中，既能对表格进行编辑修改，也能查看和修改页边距、页眉和页脚。同时，“页面布局”视图中还会显示水平和垂直标尺，这对于测量和对齐对象十分有用。
操作步骤如下：我们将主页面功能区切换到“视图”选项卡下，在“工作簿视图”组中单击“页面布局”按钮，表格就会以页面布局视图的方式来呈现表格的内容，如图2-29所示。
图2-29 页面布局视图
3．分页预览视图
在分页预览视图中可以调整当前工作表的分页符，还可以调整打印区域的大小以及编辑工作表。
操作步骤如下：将主页面功能区切换到“视图”选项卡下，在“工作簿视图”组中单击“分页预览”按钮，就可以切换到分页预览视图，如图2-30所示。
图2-30 分页预览视图
4．自定义视图
如果某些用户对上述视图都不喜欢，在Excel 2016中，还可以根据自己的习惯来自定义视图方式。具体操作步骤如下所示。
步骤 01：隐藏“现金流量表”工作表中的C列，在“工作簿视图”组中单击“自定义视图”按钮，如图2-31所示。
图2-31 选择自定义视图
步骤 02：在弹出的“视图管理器”对话框中单击“添加”按钮，如图2-32所示。
图2-32 单击“添加”按钮
步骤 03：在“添加视图”对话框中的“名称”框中输入“自定义视图1”，其余保留默认设置，单击“确定”按钮，如图2-33所示。
图2-33 自定义名称
步骤 04：取消隐藏列C，然后在“工作簿视图”组中单击“自定义视图”按钮，如图2-34所示。
图2-34 取消隐藏列
步骤 05：在打开的“视图管理器”对话框中的“视图”列表中选择“自定义视图1”，然后单击“显示”按钮，如图2-35所示。
图2-35 显示自定义视图1
步骤 06：此时工作表按照“自定义视图1”显示，会自动隐藏C列，如图2-36所示。
图2-36 自定义视图1效果图
小技巧：删除自定义视图
如果要删除自定义视图方式，请先在“工作簿视图”组中单击“自定义视图”按钮，打开“视图管理器”对话框，在“视图”列表中选择要删除的视图名称，然后单击“删除”按钮，在弹出的对话框中单击“是”按钮即可删除自定义视图，如图2-37所示。
图2-37 删除自定义视图
2.1.3 设置工作表的显示比例
用户还可以根据需要调整工作表的显示比例。在默认的方式下，工作簿的显示比例为100%，用户可以根据需要缩放显示比例。
1．通过“显示比例”对话框设置显示比例
步骤 01：在主页面功能区中切换到“视图”选项卡下，在“显示比例”组中单击“显示比例”按钮，如图2-38所示。
图2-38 选择显示比例
步骤 02：在“显示比例”对话框中单击选中“75%”单选按钮，单击“确定”按钮，如图2-39所示。
图2-39 选择显示比例
步骤 03：此时工作表按75%缩小显示，如图2-40所示。
图2-40 按75%比例显示
步骤 04：在“显示比例”组单击“100%”按钮，则可快速恢复为100%比例显示效果，如图2-41所示。
2．缩放到选定区域
在Excel 2016中，还可以根据选定区域的内容和窗口的大小来自动调整显示比例，以确保在窗口中仅显示且正好显示选定区域的内容。
图2-41100%显示比例
步骤 01：选择单元格区域B5:F11，在主页面功能区中切换到“视图”选项卡，在“显示比例”组中单击“缩放到选定区域”按钮，如图2-42所示。
图2-42 选择缩放到选定区域
步骤 02：此时Excel会自动调整显示比例，整个窗口中只显示选定的区域，如图2-43所示。
图2-43 自动调整比例以显示选定区域
步骤 03：选择当前窗口不能完全显示出来的单元格区域B5:F19，在“显示比例”组中单击“缩放到选定区域”按钮，如图2-44所示。
图2-44 选择缩放到选定区域
步骤 04：若当前窗口不能完全显示出选定的区域，Excel会自动缩小显示比例以显示所有选定区域，如图2-45所示。
图2-45 自动调整比例以显示选定区域
3．通过拖动“显示比例”滑块自由设置显示比例
用户还可以直接拖动Excel窗口状态栏右下角的“显示比例”滑块来自由设置显示比例，向左拖动缩小比例显示，向右拖动增大比例显示，如图2-46所示。
图2-46 拖动滑块设置显示比例
2.1.4 拆分和冻结窗口
当工作表中的数据量比较庞大时，可以通过拆分和冻结窗口来显示和比较工作表中不同区域的数据。例如，要比较查看现金流量表中“经营活动”和“投资活动”各自的现金流量情况，就可以拆分窗口来比较。如果希望现金流量表中的标题行和左侧列一直显示在屏幕上，则可以通过冻结窗口来实现。接下来我们为了讲解拆分和冻结窗口的实际应用，将“第2章现金流量表”中合并的B5:F5、B16:F16、B27:F27单元格区域拆分成单个的单元格。接下来我们就来学习一下怎么拆分单元格。
1．拆分窗口
步骤 01：选择要折分位置的单元格，如单元格D16，在“视图”选项卡中的“窗口”组中单击“拆分”按钮，如图2-47所示。
图2-47 选择拆分按钮
步骤 02：此时，Excel会从单元格C15的位置将窗口拆分为4个窗口，每个窗口都有单独的滚动条，用户可以拖动滚动条以查看不同的区域，如图2-48所示。
图2-48 拆分为4个窗口
步骤 03：用鼠标指向水平和垂直拆分框的交叉位置，当指针变为双向黑色箭头时，拖动鼠标可更改拆分框的位置，如图2-49所示。如果要取消拆分窗口，只需要再次单击“窗口”组中的“拆分”按钮即可。
图2-49 更改拆分框的位置
2.2 资产负债表
资产负债表作为会计、商业会计或簿记实务中的财务报表之一，与购销损益账、现金流量表、股东权益变动表并列为企业四大常用财务报表。本节我们重点介绍如何用Excel 2016来编制符合用户需要的资产负债表。
2.2.1 编制资产负债表
资产负债表是反映企业某一特定日期（如月末或年末）财务状况的会计报表，也称财务状况表。它根据“资产=负债+所有者权益”的会计等式，按照一定的分类标准和一定的顺序，把企业在一定时期的资产、负债和所有者权益项目予以适当排列，按照一定的要求编制而成。下面我们具体来介绍资产负债表编制的具体操作步骤。
步骤 01：启动Excel 2016软件后新建一个空白的工作表格，选择A1:D1单元格，在主页面功能区切换到“开始”选项卡下，单击“对齐方式”组中的“合并后居中”按钮，选择E1:F1单元格，单击“合并后居中”按钮，如图2-50所示。
图2-50 合并单元格
步骤 02：选择A、E列单元格并右击，在弹出的快捷菜单中选择“列宽”命令，在弹出的对话框中将列宽设置为13，如图2-51所示。
图2-51 设置列宽
步骤 03：选择第1、7行单元格并右击，在弹出的快捷菜单中选择“行高”命令，在弹出的对话框中将“行高”设置为21，如图2-52所示。
图2-52 设置行高
步骤 04：按照上述方法，将2～6、8～12行的单元格的“行高”设置为15。然后选择E7:H7单元格区域，在主页功能区切换到“开始”选项卡，在“对齐方式”组中单击“合并后居中”按钮，并在合并后的单元格内分别输入文字“试算表”“损益表”“资产负债表”，将“字体”设置为“宋体”，字号设置为16，完成后的效果如图2-53所示。
图2-53 设置单元格并输入文字
步骤 05：在A2:F2单元格区域中输入文字，将“字体”设置为“宋体”，将“字号”设置为11，在“对齐方式”组中单击“居中”按钮，完成的效果如图2-54所示。
图2-54 输入文字
步骤 06：使用同样的方法，在如图2-55所示的单元格内输入文字，选择A1:H21单元格区域，在“开始”选项卡中单击“对齐方式”组中的“居中”按钮，如图2-55所示。
图2-55 输入文字并居中
步骤 07：选择F3单元格，在编辑栏中输入公式“=SUM(D4:D5)”，按Enter键完成公式运算，效果如图2-56所示。
图2-56 输入公式并完成运算
步骤 08：选择F4单元格，在编辑栏中输入公式“=SUM（B7:B13）”，按Enter键完成操作，如图2-57所示。
图2-57 在F4中输入公式
步骤 09：在F5单元格中输入公式“=SUM(B14:B20)”，如图2-58所示。在F6单元格中输入公式“=F3-F4-F5”，如图2-59所示。
图2-58 在F5单元格中输入公式
图2-59 在F6单元格中输入公式
步骤 10：在H10单元格中输入公式“=$F$6”，在B21单元格中输入公式“=SUM (B3:B20)”，在D21单元格内输入公式“=SUM(D3:D20)”，在F21单元格内输入公式“=SUM(F9:F20)”，在H21单元格内输入公式“=SUM(H9:H20)”，完成后的效果如图2-60所示。
图2-60 输入公式
步骤 11：在Sheet1上双击鼠标，输入文字“资产负债表”，完成资产负债表的制作，如图2-61所示。
图2-61 更改工作表名称
2.2.2 相关函数介绍
1．MAX函数的语法和功能
说明：返回一个最大数值。
语法：MAX(number1, [number2], ...)
MAX函数语法具有下列参数：number1, [number2], ..．表示number1是必需的，后续数字是可选的。
注意事项：可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果逻辑值和文本不能忽略，请使用函数MAXA来代替。此外，如果参数不包含数字，函数MAX返回0。
示例：我们以负债表中的数值为例。在F13单元格中输入公式“=MAX(F9:F12)”，按Enter键输出结果，可得到数值为220000，它表述的意思为在F9:F12单元格区域中，最大的一个值为220000，如图2-62所示。
图2-62 输入公式
2．ABS函数的语法和功能
说明：返回数字的绝对值。一个数字的绝对值是该数字不带其符号的形式。正数和0返回数字本身，负数返回数字的相反数。
语法：ABS(number)
ABS函数语法具有以下参数：number是必需的参数，需要计算其绝对值的实数。
注意事项：参数必须为数值类型，即数字、文本格式的数字或逻辑值。如果是文本，则返回错误值#VALUE!。
示例：这里我们新建一个空白表格以计算温差为例来解释ABS函数的应用。在D2单元格中输入公式“=ABS(C2-B2)”，按Enter键输出结果，可得到数值2，它表达的意思是两地温差的“绝对值”，如图2-63所示。
图2-63 两地温差
2.2.3 设置万元显示格式
在Excel单元格里，如果表格中的数值太大，想直接以万元为单位进行显示，该怎么设置单元格式呢？这里我们以资产负债表的设置为例。
步骤 01：按住Ctrl键，选择“数值”列，分别是B列、D列、F列、H列，右键鼠标，在弹出的对话框中选择“设置单元格格式”选项，如图2-64所示。
图2-64 选择设置单元格格式
步骤 02：在弹出的“设置单元格格式”对话框中切换到“数字”选项卡，在“分类”选项下选择“自定义”弹出“自定义类型”页面，在“类型”文本框中输入“0! .!00000”，单击“确定”按钮，如图2-65所示，设置后的效果如图2-66所示。
图2-65 自定义类型
图2-66 效果预览图
2.3 科目余额表的计算
前两节我们已经细致地学习了现金流量表和资产负债表的制作，对于单元格的格式设置以及文本和数值的输入与设置大家肯定都掌握了。那么接下来的科目余额表，学习起来就相对简单多了，我们将会重点讲解它的计算步骤。
步骤01：新建一个工作表“科目余额表”，按照前面学习的方法将“科目余额表”的文本、数字输入并进行设置，并对单元格格式进行设置，效果如图2-67所示。
图2-67 设置格式效果图
步骤 02：根据“资产类会计科目期末余额=期初余额+借方发生额-贷方发生额”这个公式，单击选中单元格C3，然后输入运算符“+”，选择单元格D3，然后输入运算符“-”，最后选择单元格E3，按Enter键输出结果，如图2-68所示。
图2-68 输入公式
步骤 03：拖动单元格G3右下角的填充柄向下复制公式至单元格G13，计算后的效果如图2-69所示。
图2-69 复制公式
步骤 04：根据“负债和权益类会计科目期末余额=期初余额+贷方发生额-借方发生额”的公式，在单元格G14中输入公式“=C14+E14-D14”，如图2-70所示。
图2-70 输入公式
步骤 05：按下Enter键后，拖动单元格G14右下角的填充柄向下复制公式至单元格G23，计算出负债和权益类科目的期末余额，如图2-71所示。
图2-71 预览计算结果
2.4 利润表
利润表又称损益表，它是反映企业在一定时期（月份或年度）经营成果的报表，是企业的定期报表之一。利润表是根据“收入-费用=利润”的会计平衡公式和收入与费用相配比原则编制的。利润表中主要包括五大项目，分别是主营业务收入、主营业务利润、营业利润、利润总额和净利润，通过表项目中的加减关系，最后计算出净利润。
2.4.1 编制利润表
和科目余额表一样，我们只重点介绍利润表的计算方法。但这次我们介绍的计算方法是引用其他表格数据。需要说明的是，编制利润报表需要参照用前面的方法计算出的“科目余额表”中各科目的余额。
步骤 01：打开“利润表．xlsx”工作簿，其中包括“科目余额表”“资产负债表”“利润表”和“现金流量表”，如图2-72所示。
图2-72 打开利润表工作簿
步骤 02：单击“利润表”工作表标签，在单元格C5中输入公式“=科目余额表！K4”，从科目余额工作表中获取“营业收入”，如图2-73所示。
图2-73 引用“营业收入”数据
步骤 03：在单元格C6中输入公式“=科目余额表！J5”，按下Enter键后向下复制公式至单元格C11，得到如图2-74所示的数据。
图2-74 引用营业成本等数据
步骤 04：在单元格C13中输入公式“=科目余额表！K11”，如图2-75所示。
图2-75 引用“投资收益”数据
步骤 05：在单元格C15中输入公式“=C5-C6-C7-C8-C9-C10-C11+C12+C13”，计算结果如图2-76所示。
图2-76 计算营业利润
步骤 06：在单元格C16中输入公式“=科目余额表！K12”，计算结果如图2-77所示。
图2-77 引用“营业外收入”数据
步骤 07：在单元格C17中输入公式“=科目余额表！J13”，计算结果如图2-78所示。
图2-78 引用“营业外支出”数据
步骤 08：在单元格C19中输入公式“=C15+C16-C17”，按下Enter键后，计算结果如图2-79所示。
图2-79 计算利润总额
步骤 09：在单元格C20中输入公式“=科目余额表！J14”，引用得到的数据，如图2-80所示。
图2-80 引用“所得税费用”数据
步骤 10：在单元格C21中输入公式“=C19-C20”，计算结果如图2-81所示。编制利润表的任务就完成了，效果如图2-82所示。
图2-81 计算净利润
图2-82 利润表预览图
2.4.2 创建费用统计图表
统计图表也可称为统计图或趋势图，以统计图的方式，如柱形图、曲线图、饼图、点图、面积图、雷达图等，呈现某事物或某信息数据的发展趋势。利用图表可以充分地表现出文字无法展现的效果，因此在报表中使用图表来说明问题也是常见的一种表达方式。下面我们就来介绍如何创建费用统计图表。
步骤 01：打开“利润表．xlsx”工作簿，将页面切换到“利润表”工作表，选择A4:C24单元格区域，在主页面功能区切换到“插入”选项卡，在“图表”组中单击“饼图”下拉按钮，在弹出的下拉框中选择“三维饼图”选项，如图2-83所示。
图2-83 选择图表类型
步骤 02：选择图表，单击“图表元素”按钮，在展开的对话框中勾选“图表标题”和“图例”选项，并将文本标题命名为“本期金额”，费用统计图表的创建就完成了，效果如图2-84所示。
图2-84 创建图表效果图
2.5 综合运用：销售收入、成本、费用和税金综合分析
企业从事商品交易，在得到商品收入的同时，也会产生销售成本、销售费用，并需要按国家法律法规交纳销售税金。企业销售收入主要有三大流向：成本、费用和税金。企业对三大开支的负担能力决定了企业的盈利能力。现有的财务软件都是以固定的模式设计好的，有时它不能满足用户的需求，但Excel 2016的数据分析功能非常强大，许多专业的财务软件也难以媲美。本节接下来就以某企业在某年内的销售收入、成本、费用和税金数据为例，综合运用Excel 2016对这些数据进行分析。
2.5.1 单项产品销售收入与成本分析
已知企业各个产品在某一年中每月的销售收入、成本和销售数量，现需要分析各产品的单价、单位成本以及销售成本率等指标。现以A产品为例，分析方法总共分为两部分，具体分析如下。
1．设置公式计算销售成本率
销售成本率也称为主营业务成本率，它是用来衡量企业成本、费用消化能力的指标之一。销售成本率是指销售成本与销售收入的百分比，反映每100元收入中收回成本的比例。
步骤 01：打开“产品销售收入、成本、费用和税金数据表．xlsx”工作簿，在单元格C7中输入公式“=IF(C6=0,0, C4/C6)”，按下Enter键后，向右复制公式至单元格N7，得到如图2-85所示的计算结果。
图2-85 计算销售单价
步骤 02：在单元格C8中输入公式“=IF(C6=0,0, C5/C6)”，按下Enter键后，向右复制公式至单元格N8，得到如图2-86所示的计算结果。
图2-86 计算单位成本
步骤 03：在单元格C9中输入公式“=IF(C4=0,0, C5/C4)”，然后复制公式至单元格N9，得到如图2-87所示的数据。
图2-87 计算销售成本率
步骤 04：使用类似的方法计算出B产品和C产品各月的销售单价、单位成本和销售成本率，如图2-88所示。
图2-88 计算其他产品的数据
2．创建图表分析收入与成本
除了计算出销售成本率外，还可以使用图表来更加形象直观地反映单项产品的销售收入、成本、数量之间的对比、单价与成本的对比，以及销售成本率的趋势等。
（1）创建收入、成本和数量对比折线图
步骤 01：在主页面功能区切换到“插入”选项卡下，在“图表”组中单击“折线图”下三角按钮，从下拉列表中选择“带数据标记的折线图”，如图2-89所示。
图2-89 选择图表类型
步骤 02：在“图表工具-设计”选项卡中的“数据”组中单击“选择数据”按钮，如图2-90所示。
图2-90 单击“选择数据”按钮
步骤 03：在“选择数据源”对话框中单击“图表数据区域”右侧的单元格引用按钮，选择单元格区域B3:N6，然后单击“确定”按钮，如图2-91所示。
图2-91 选择图表区域
步骤 04：此时，以选定的数据区域创建的默认的图表效果如图2-92所示。由于3个数据系列绘制在同一个坐标轴中，而销量数据比其他两个系列的值小得多，因此，该图中不能明确地反映各月销量的比较情况。要想反映出销售数量的对比，还需要进行下面的操作。
图2-92 图表默认效果
步骤 05：在“图表布局”组中单击“添加图表元素”下三角按钮，从下拉列表中单击“图例”展开按钮，在展开的下拉框中选择“底部”选项，如图2-93所示。
图2-93 更改图例位置
步骤 06：在“图表工具-格式”选项卡下单击“当前所选内容”下拉三角按钮，在下拉框中选择图表中的“销售数量”系列，单击“设置所选内容格式”选项，如图2-94所示。
图2-94 单击“设置所选内容格式”选项
步骤 07：在弹出的“设置数据系列格式”对话框中单击选中“次坐标轴”单选按钮，如图2-95所示。
图2-95 选择次坐标轴
步骤 08：单击选择“销售数量”系列，右击，从弹出的快捷菜单中选择“更改系列图表类型”选项，如图2-96所示。
图2-96 单击“更改系列图表类型”选项
步骤 09：在“更改图表类型”对话框中选择“组合”选项，将“销售数量”的图表类型设置为“簇状柱形图”，单击“确定”按钮，如图2-97所示。
图2-97 选择簇状柱形图
步骤 10：更改后的图表效果如图2-98所示，折线图类型的两个数系列“销售收入”和“销售成本”绘制在主要纵坐标轴上，而柱形图类型的“销售数量”系列绘制在次坐标轴上。
图2-98 更改类型后的图表效果
步骤 11：在“图表布局”组中单击“添加图表元素”下三角按钮，从下拉列表中单击“图表标题”展开按钮，在展开的下拉框中选择“图表上方”选项，如图2-99所示。
图2-99 选择图表标题位置
步骤 12：在图表标题文本框中输入“收入、成本和销量对比图”，设置后的效果如图2-100所示。
图2-100 图表最终效果图
（2）创建销售单价和单位成本对比图
步骤 01：按住Ctrl键，单击拖动上面创建的图表创建一个副本，然后双击复制的图表标题，输入新标题“单价与单位成本比较图表”，如图2-101所示。
图2-101 复制图表并修改标题
步骤 02：单击“图表工具-设计”选项卡下“数据”组中的“选择数据”按钮，如图2-102所示。
图2-102 单击“选择数据”按钮
步骤 03：在“选择数据源”对话框中单击“图表数据区域”右侧的按钮，选择单元格区域B3:N3、B7:N8，然后单击“确定”按钮，如图2-103所示，图表效果如图2-104所示。
图2-103 选择图表数据区域
图2-104 图表效果图
（3）创建销售成本率趋势图表
步骤 01：在主页面功能区切换到“插入”选项卡下，在“图表”组中单击“折线图”下三角按钮，从下拉列表中选择“带数据标记的折线图”，如图2-105所示。
图2-105 选择折线图类型
步骤 02：打开“选择数据源”对话框，单击“添加”按钮，如图2-106所示。
图2-106 单击“添加”按钮
步骤 03：随后打开“编辑数据系列”对话框，选择“系列名称”引用单元格为B9,“系列值”引用单元格为C9:N9，然后单击“确定”按钮，如图2-107所示。
图2-107 编辑系列名称和系列值
步骤 04：在“选择数据源”对话框中的“水平（分类）轴标签”区域单击“编辑”按钮，如图2-108所示。
图2-108 单击“编辑”按钮
步骤 05：在“轴标签”对话框中单击单元格引用按钮，选择单元格区域C3:N3，然后单击“确定”按钮，如图2-109所示。
图2-109 选择轴标签区域
步骤 06：返回“选择数据源”对话框，更改后的轴标签会显示在“水平（分类）轴标签”列表中，单击“确定”按钮，如图2-110所示。
图2-110 更改后的轴标签
步骤 07：此时得到图表效果如图2-111所示。
图2-111 图表效果图
2.5.2 本年销售收入、成本、费用和税金分析
上一节针对公司某一项产品单独分析了销售收入与成本，对于一个企业来说，除了按产品单独核算外，还需要对所有产品的收入、成本、费用及税金进行分析。本节将对公司本年度整体销售成本、销售费用、销售税金与销售收入的相关性进行分析。
1．创建表格并设置公式计算
创建一个用于统计本年度销售收入、成本、费用和税金的表格，并根据已知数据计算出各月的销售收入、成本、费用和税金，然后计算出销售成本率、销售费用销售税金率、销售税金率，操作步骤如下所示。
步骤 01：将工作表标签Sheet2更改为“本年成本、费用、税金与收入相关分析”，在工作表中创建如图2-112所示的分析表。
图2-112 创建表格
步骤 02：在单元格B4中输入公式“=数据表！C4+数据表！C11+数据表！C18”，按下Enter键后，向右复制公式至单元格M4，如图2-113所示。
图2-113 设置公式计算销售收入
步骤 03：向下拖动单元格M4右下角的填充柄至单元格M7，求出销售成本、费用和税金，如图2-114所示。
图2-114 复制公式
步骤 04：在单元格B8中输入公式“=B5/B4”，计算销售成本率，按下Enter键后，复制至N8，如图2-115所示。
图2-115 计算销售成本率
步骤 05：在单元格B9中输入公式“=B6/B4”，计算销售费用率，按下Enter键后，复制公式至N9，得到如图2-116所示的数据。
图2-116 计算销售费用率
步骤 06：在单元格B10中输入公式“=B7/B4”，计算销售税金率，按下“Enter”键后，复制公式至N10，得到如图2-117所示的数据。
图2-117 计算销售税金率
步骤 07：在单元格N4中输入公式“=SUM(B4:M4)”，计算合计按下Enter键后，复制公式至单元格N7，得到表格最终的数据如图2-118所示。
图2-118 计算合计
2．销售收入与销售成本相关分析
销售收入与销售成本的分析是企业发展计算所得利润的最初阶段，正确地利用Excel对其进行分析对一个企业来讲至关重要，操作步骤如下。
步骤 01：在工作表中选择单元格区域A3:M4，在“插入”选项卡的“图表”组中单击“折线图”下三角按钮，从展开的下拉列表中单击“折线图”子类型，如图2-119所示。
图2-119 选择折线图类型
步骤 02：Excel会根据用户选择的数据区域，以默认的样式创建所选择的图表类型，如图2-120所示。
图2-120 默认的图表类型
步骤 03：打开“选择数据源”对话框，选择“月份”系列，单击“删除”按钮，如图2-121所示。
图2-121 删除系列
步骤 04：在图表中添加标题“收入、成本比较图表”，得到的图表最终效果如图2-122所示。
图2-122 图表最终效果
步骤 05：复制“收入、成本比较图表”，然后将副本图表的标题更改为“销售成本率变化趋势”，如图2-123所示。
图2-123 复制图表并修改标题
步骤 06：打开“选择数据源”对话框，单击“图表数据区域”右侧的单元格引用按钮，选择单元格区域A8:M8，然后单击“确定”按钮，如图2-124所示。更改图表数据源后的图表效果如图2-125所示。
图2-124 更改图表数据源
图2-125 更改后图表效果
步骤07：选择单元格区域B31:C31，输入公式“=LINEST(B4:M4, B5:M5)”，按下Ctrl+Shift+Enter组合键，生成数组公式，返回结果如图2-126所示。
图2-126 设置LINEST函数进行最佳直线拟合
步骤 08：在合并单元格B32中输入公式
“=CONCATENATE("Y=", TEXT(B31, "0.0000"), "X+", TEXT(C31, "0.0000") )”，按下Enter键后，返回回归函数表达式，如图2-127所示。
图2-127 返回回归函数表达式
步骤 09：在单元格B33中输入公式“=CONCATENATE("r=", TEXT(CORREL(B4:M4, B5:M5), "0.0000"))”，按下Enter键后，单元格中显示相关系数“r=0.3744”，如图2-128所示。
图2-128 计算相关系数
步骤 10：在单元格C33中输入公式“=IF(CORREL(B4:M4, B5:M5)<0.5, "异常", "")”，判定相关性是否正常，按下Enter键后，单元格中显示“异常”，如图2-129所示。
图2-129 判定相关性是否正常
3．销售收入与销售费用相关分析
上面分析了销售收入与销售成本之间的相关性，接下来分析销售收入与销售费用之间的相关性，操作步骤如下所示。
步骤 01：复制上一节中创建的“收入、成本比较图表”，并将副本图表的标题更改为“收入、费用比较图表”，如图2-130所示。
图2-130 复制图表并修改标题
步骤 02：打开“选择数据源”对话框，选择“销售成本”数据系列，单击“删除”按钮，如图2-131所示。
图2-131 删除数据系列
步骤03：此时“选择数据源”对话框中只有“销售收入”一个数据系列。单击“添加”按钮，如图2-132所示。
图2-132 单击“添加”按钮
步骤 04：打开“编辑数据系列”对话框，单击“系列名称”框右侧的单元格引用按钮，选择“销售费用”文本所在单元格A6，单击“系列值”框右侧的单元格引用按钮，选择单元格B6:M6，然后单击“确定”按钮，如图2-133所示。
图2-133“编辑数据系列”对话框
步骤 05：返回“选择数据源”对话框，单击“确定”按钮，如图2-134所示。
图2-134 单击“确定”按钮
步骤 06：返回工作表中，得到的销售收入与费用比较图表，如图2-135所示。
图2-135 收入、费用比较图表效果
步骤 07：复制“销售成本率变化趋势”图表，然后打开“选择数据源”对话框，将副本图表的数据区域更改为A9:M9，单击“确定”按钮，如图2-136所示。销售成本率变化趋势图最终效果如图2-137所示。
图2-136 更改图表数据源
图2-137 销售成本率变化趋势图表
步骤 08：选择单元格区域B54:C54，输入数组公式“=LINEST (B4:M4, B6: M6)”，得到如图2-138所示的计算结果。
图2-138 设置公式返回函数参数
步骤 09：在单元格B55中输入公式“=CONCATENATE("Y=", TEXT(B54, "0.0000"),"X+", TEXT(C54, "0.0000") )”，返回销售收入与销售费用的函数表达式，如图2-139所示。
图2-139 设置公式返回函数表达式
步骤10：在单元格B56中输入公式“=CONCATENATE("r=", TEXT(CORREL(B4:M 4, B6:M6), "0.0000"))”，按下Enter键后，公式计算相关系数为“r=0.9284”，如图2-140所示。
图2-140 设置公式计算相关系数
步骤 11：在单元格C56中输入公式“=IF(CORREL(B4:M4, B6:M6)<0.5, "异常", "")”，按下Enter键后，公式结果返回为空，说明该相关系数在正常范围内，如图2-141所示。
图2-141 设置公式判定相关系数
我们已经介绍了销售收入与销售成本相关分析、销售收入与销售费用相关分析的具体步骤，我们不难从中发现一些有趣的计算规律，我们可以利用这些规律，对表格中任意两个变量之间的相关关系进行分析。
2.5.3 销售收入与成本年度对比分析
为了体现企业经营策略变动所发生的效果，有时需要将相邻的年度之间的数据进行对比分析。例如，将本年度的销售收入、销售成本、销售成本率与上年度的销售收入、销售成本、销售成本率进行对比分析。
1．创建分析表格
首先创建年度分析表格，表格中列标志包含“销售收入”“销售成本”“销售成本率”的本年数和上年数，操作步骤如下所示。
步骤 01：将工作表标签Sheet2更改为“销售收入与成本年度比较分析”，在该工作表中创建“销售收入与成本年度对比分析”表格，并输入已知的上年的“销售收入”“销售成本”数据，如图2-142所示。
图2-142 新建表格并输入上年数据
注意：第7行为合计，指的是求和项，用SUM函数得出，下文含有合计项的可直接计算得出，不再重复过程。
步骤 02：在单元格D4中输入公式“=C4/B4”，按下Enter键后，向下复制公式至单元格D7，得到如图2-143所示的数据。
图2-143 计算上年销售成本率
步骤 03：在单元格E4中输入公式“=数据表！O4”，单元格E5中输入公式“=数据表！O11”，单元格E6中输入公式“=数据表！O18”，得到的数据如图2-144所示。
图2-144 引用本年销售收入
步骤 04：在单元格F4中输入公式“=数据表！O5”，单元格F5中输入公式“=数据表！O12”，单元格F6中输入公式“=数据表！O19”，得到如图2-145所示的数据。
图2-145 引用本年销售成本
步骤 05：在单元格G4中输入公式“=F4/E4”，计算销售成本率，按下Enter键后，向下复制公式至单元格G7，得到如图2-146所示的数据。
图2-146 计算销售成本率
2．创建结构图表
创建结构图表能够帮助我们更好地分析数据。下面我们以创建销售收入结构图表为例，具体讲解一下创建结构图表的过程。
步骤 01：选择单元格区域A4:B6，在主页面功能区中切换到“插入”选项卡下，在“图表”组中单击“饼图”下三角按钮，从展开的下拉列表中选择“三维饼图”，如图2-147所示。Excel创建的默认的三维饼图效果如图2-148所示。
图2-147 选择图表类型
图2-148 图表默认效果
步骤 02：在“图表工具-设计”选项卡中的“图表布局”组中选择带有图表标题、百分比数据标志和图例的布局样式，这里选择“布局6”，如图2-149所示。
图2-149 更改图表布局
步骤 03：更改图表布局后的图表效果如图2-150所示，从图表中的数据标志可以得知，在上年的销售收入结构中，A产品的销售收入占上年整个销售收入的9%, B产品占76%, C产品占15%。
图2-150 图表效果
3．创建本年与上年销售成本率比较图表
通过比较上年销售成本率与本年销售成本率的变化，可以得知企业在该年度的成本控制效果。那么怎么创建本年与上年销售成本率比较图表呢？具体操作如下。
步骤 01：在主页面功能区中切换到“插入”选项卡下，在“图表”组中单击“折线图”下三角按钮，从展开的下拉列表中选择“带数据标记的折线图”子类型，如图2-151所示。
图2-151 选择图表类型
步骤 02：在“图表工具-设计”选项卡的“数据”组中单击“选择数据”按钮，打开“选择数据源”对话框，单击“添加”按钮，如图2-152所示。
图2-152 单击“添加”按钮
步骤 03：打开“编辑数据系列”对话框，在“系列名称”框中直接输入“销售成本率（上年数）”，单击“系列值”单元格引用按钮，选择单元格区域D4:D6，然后单击“确定”按钮，如图2-153所示。
图2-153 编辑数据系列
步骤 04：返回“选择数据源”对话框，再次单击“添加”按钮打开“编辑数据系列”对话框。在“系列名称”框中输入“销售成本率（本年数）”，单击“系列值”单元格引用按钮，选择单元格区域G4:G6，然后单击“确定”按钮，如图2-154所示。
图2-154 添加“销售成本率”系列
步骤 05：返回“选择数据源”对话框，此时添加的系列会显示在“图例项（系列）”列表中。在“水平（分类）轴标签”区域中单击“编辑”按钮，如图2-155所示。
图2-155 单击“编辑”按钮
步骤 06：在打开的“轴标签”对话框中，单击“轴标签区域”单元格引用按钮，选择单元格区域A4:A6，然后单击“确定”按钮，如图2-156所示。
图2-156 选择轴标签区域
步骤 07：更改轴标签后返回“选择数据源”对话框，单击“确定”按钮，如图2-157所示。
图2-157 单击“确定”按钮
步骤 08：图表最终效果如图2-158所示。从图表中可以明显看出，本年销售成本率低于上年的销售成本率，说明本年的成本控制比上一年做得好。
图2-158 图表最终效果