任务二制作年度收支汇总表_Excel在经济管理中的应用-QQ阅读男生历史网

上QQ阅读APP看书，第一时间看更新

任务二　制作年度收支汇总表

1.制作全年收支汇总报表

（1）年度收支汇总表可利用月收支明细表复制而来，再删除第21行（“记事本”标题所在行），清除所有公式，保留12列，并将“日期”改为“月份”，如图1.5所示。关于编辑单元格内容的方法见附录A知识与操作技巧1。

图1.5　年度收支汇总表列表结构

（2）引用各月汇总数据。在“年度收支汇总表”C3单元格中输入“=”，再选取“1”表中的AH3，即C3='1'！AH3（其中的“'1'！”是引用的工作表名），确定后向下填充至C20单元格。用同样的方法完成其他各月。这种方法与“粘贴链接”的效果一致，即选择“1”表中的AH3：AH20单元格区域，执行复制操作后，再对“年度收支汇总表”的C3单元格右击，在弹出的快捷菜单中选择“选择性粘贴”命令，在对话框中分别单击“粘贴链接”和“确定”按钮。

关于使复制粘贴的信息能随数据源自动更新的方法见附录A知识与操作技巧200。也可以在1月份引用公式建立好的基础上，运用填充公式、替换表名的方式快速完成。

如图1.6所示，选择C3：N20单元格区域，按<Ctrl+R>组合键，即完成公式的向右填充，再查找和替换其中的表名。关于快速查找和替换的方法见附录A知识与操作技巧23。为保持向右填充时列号不发生相对变化，在公式填充前，应将C3单元格公式中的AH3改为“$AH3”（列绝对、行相对的引用方式），这样向下填充时行号保持相对，向右填充时列号不变（在公式中选择AH，连续按<F4>键可在四种引用方式之间循环切换）。关于引用的类别及快速切换引用的方法见附录A知识与操作技巧68。

图1.6　填充公式及替换表格

（3）设置格式等。选择C3：N20单元格区域，设置字号为8、千位分隔符、不显示“0”和网格线。

2.建立汇总表与月表联系

通过超链接建立各月表与汇总表之间的联系，可以方便地在各表之间查看数据。关于设置、编辑、取消超链接的方法见附录A知识与操作技巧28～30。

（1）建立各月表与汇总表的联系。将各月明细表标题指向“年度汇总”表。例如，选择“1”表A1单元格，按<Ctrl+K>组合键，弹出“插入超链接”对话框，选择链接到“本文档中的位置”，在下拉列表中选择“年度收支汇总表”，单击“确定”按钮，如图1.7所示。由于各月表链接的目标均为“年度收支汇总表”，因此可以用填充工作组的方法完成其他表的设置。

图1.7　建立“1”表与“年度收支汇总表”联系

（2）建立汇总表与各月表的联系。将“年度收支汇总表”中的各月标志分别指向相应月明细表。例如，选择“年度收支汇总表”中的C2单元格后右击，在弹出的快捷菜单中选择“超链接”命令，或按<Ctrl+K>组合键，弹出“插入超链接”对话框，选择“本文档中的位置”，在“或在此文档中选择一个位置”列表框中选择“1”工作表，单击“确定”按钮，如图1.8所示。用同样的方法分别完成年度汇总表中的各月份与相应月表的超链接。由于链接的目标不同，因此必须逐一设置。

图1.8　建立汇总表与月表的联系

（3）格式设置。单击有超链接的单元格时，将自动打开目标，要对这些单元格进行格式设置，可利用箭头键选择要编辑的第一个单元格，按<Shift>键，移动箭头键选择要编辑的最后一个单元格（或者在名称框中输入有关区域的地址）。关于选择已设置超链接的单元格或对象的方法见附录A知识与操作技巧194。

3.设置标题栏让年份可选

（1）将标题分解为两部分：其右端选择G1：N1单元格区域，合并单元格，并设置向左对齐，输入“年度收支汇总表”；其左端合并E1、F1两个单元格（用于选择输入年份），并设置向右对齐。选择标题行设置适当字体、字号和颜色等格式。

（2）输入年份序列。在工作表空白处如B22、B23单元格，分别输入年份数字，如“2016”“2017”，选择B22、B23单元格，拉动填充柄向下拖动，填充至适当位置时松手。

（3）设置年份下拉选择。选中E1单元格，选择“数据”选项卡“数据工具”组中的“数据验证”命令。在弹出的对话框中选择“序列”，并在“来源”中选择预置的年份区域（B22：B62单元格区域），默认选择“忽略空值”和“提供下拉箭头”复选框，单击“确定”按钮。以后用户可在E1单元格提供的下拉列表中选择年份，如图1.9所示。（注意序列内容一旦清除，则下拉列表中将无序列可选，故建议将序列所在的B22：B62行隐藏）。关于快速隐藏和取消隐藏行、列的方法见附录A知识与操作技巧205～207。

图1.9　建立年份输入可选的下拉列表

4.制作项目年度统计图表

制作收支统计图表可以直观地显示各项目经费使用情况，并通过文字提示显示各类项目占收支比例及理财信息。

（1）建立项目年度统计表。在“年度收支汇总表”中P：R列建立收支汇总统计表，如图1.10所示。其中标题行P1：R1单元格区域合并居中，并输入公式为：=E1&"年度收支统计表"，使用时只要在A1单元格中选择当年的年份，如“2016”，则统计表的标题自动显示“2016年度收支统计表”。P2：R2单元格区域为收入部分的列表标题，P7：R7单元格区域为支出部分的列表标题，P3：P6、P8：P20单元格区域分别引用B4：B7、B8：B20单元格区域的项目名称。

（2）计算各项目汇总金额。对照图1.10可知：工资项的总和Q3=SUM（C4：N4），将Q3单元格中的公式向下填充至Q6单元格，可分别获得其他各对应收入项目的合计。同样，餐饮项的总和Q8=SUM（C8：N8），将Q8单元格中的公式向下填充至Q19单元格，可分别获得其他各对应支出项目的合计。最后一项现金余额则引用12月份的现金余额，即Q20=N20。

（3）添加大写人民币。在“收入合计”与“支出合计”右边的单元格R6和R18中分别输入：R6=Q6、R18=Q18，将R6和R18单元格分别设置为“中文大写数字”。关于将数值转换为中文大写数字的方法见附录A知识与操作技巧9。

（4）计算各收（支）项目占总收（支）的百分比。由图1.10可知，计算工资收入占总收入比例的公式为R3=Q3/Q6，其他项目所占的比例分别为R4=Q4/Q6、R5=Q5/Q6，若采取将R3单元格中的公式向下填充的方法，则应将R3单元格中的公式修改为R3=Q3/Q$6（以确保向下复制时Q6的行号不变）。在分母Q6为零时，以上公式会出现报错信息。为此，可将公式改为：R3=IF（Q$6=0，""，Q3/Q$6）。关于不显示公式错误信息的方法见附录A知识与操作技巧203。

图1.10　收支统计表结构

用同样的方法计算餐饮占总支出的比例为：R8=Q8/Q18，若采取将R8单元格中的公式向下填充，则应将R8单元格中的公式修改为R8=Q8/Q$18。为避免分母Q18单元格为零值时公式显示报错信息，可将R8单元格中的公式修改为：=IF（Q$18=0，""，Q8/Q$18）。以上公式查看如图1.11所示。

（5）设置百分比格式。按住<Ctrl>键，分别选择R3：R6、R8：R18单元格区域，单击“开始”选项卡“数字”组中的、按钮，设置格式为百分比，保留1位小数。也可在“设置单元格格式”对话框中设置。

（6）制作收（支）统计图。按住<Ctrl>键，分别选取P2：P5，R2：R5单元格区域（汇总表中的收入部分的列表），单击“插入”选项卡“图表”组右下角的“对话框启动器”按钮，弹出“插入图表”对话框，在对话框中选择想要的图表类型，制作收入比例图；若对某对象不满意，可右击该对象，在弹出的快捷菜单中选择相应的命令进行细节设置。用同样的方法，分别选取P7：P17，R7：R17单元格区域（汇总表中的支出部分的列表）制作支出比例图。关于图表美化编辑的方法见附录A知识与操作技巧177～180。