917221-Excel基础与应用--7

EXCEL基础及应用第七章企业年度收支预算表案例说明知识点案例制作123知识拓展47.1案例说明案例分析设计思路AB一、案例分析制作“年度收支预算表”主要是为了方便企业进行一年的收支预算统计，使企业的相关部门能够及时了解企业的运营状况。为了减少计算上的错误，除了一些基本数据需要用户输入外，其他的统计数据（如销售收入合计、毛利、纯利等）都将通过Excel提供的公式和函数计算求得。采用自动化的方式，让用户只需要输入年份的起始时间，就可以得到该年度中每月的表头信息，极大的方便了电子表格的再利用。本例制作出的“年度收支预算表”如图7-1所示。二、设计思路根据“年度收支预算表”中需要包含的数据，通过分析为了保证数据尽量少的重复输入，收支预算表中用一年的每个月作为列标题，然后每种产品名以及各项收支的名称作为每行标题，这样就不用重复输入产品销售以及收支的相关数据。为了方便对各项数据统计查看，把需要处理的数据分为产品销售收入、产品销售成本和费用三部分输入，并且把这三部分中需要输入数据的单元格用不同的底纹颜色进行区别，这样便于浏览表格中的数据。7.2知识点自动计算列标题中的月份填写并计算销售收入ABCD计算各销售收入所占百分比填写并计算销售成本部分填写并计算毛利、费用、纯利部分E一、自动计算列标题中的月份通过收支预算表中的起始财年自动计算出该财年对应列标题中的每个月份，需要使用YEAR()、MONTH()、DATE()函数，起始财年有所更改，列标题中的日期会自动更改。二、填写并计算销售收入每个月的销售收入数据需要人工输入，但是每个月的销售收入合计可以利用Excel中提供的求和函数方便快速地计算，每种产品的年度销售收入总和则利用公式进行累加计算。三、计算各销售收入所占百分比各种产品的销售收入预计会占总销售收入的百分比的计算需要采用Excel中的IF()函数结合公式以及单元格的绝对引用进行计算。四、填写并计算销售成本部分每个月的销售成本合计使用SUM()函数求出，然后利用IF()函数计算出每个月各产品销售成本占销售收入的百分比，计算出每个月销售成本合计占销售收入合计的百分比、每种产品的年度销售成本总和占年度销售收入的百分比。五、填写并计算毛利、费用、纯利部分每个月的销售收入合计减去销售成本合计的差额就是每个月的毛利，每个月的毛利减去每个月的费用合计就是每个月的纯利。7.3案例制作制作工作表标题和表头自动计算列标题中的月份ABCD各项数据的输入填写并计算销售收入填写并计算销售成本E写并计算毛利、开支、纯利FG美化工作表一、制作工作表标题和表头制作“年度收支预算表”的标题和表头的操作步骤如下：（1）创建一个新的Excel工作簿，选中A1单元格，输入“年度收支预算表”，“字体”为“黑体”，“字号”为28号，“字形”为“加粗”。（2）选中工作表的第一行，将该行行高设置为“36”。（3）选定A1:E1,在菜单栏选择【格式】|【单元格】命令，打开【单元格格式】对话框。（4）选择【对齐】，在“文本对齐方式”中的【水平对齐】下拉列表框中选择“常规”，【垂直对齐】下拉列表框中选择“居中”，在【文本控制】选项区域中选择“合并单元格”。（5）单击【确定】按钮结束单元格合并设置。（6）选中A2单元格，输入“××电器设备有限公司”，设“字体”为“楷体”，“字号”为“12”号，“字形”为“加粗”。（7）合并A2:D2，并设置“水平对齐”为“常规”，“垂直对齐”为“居中”。（8）打开【单元格格式】对话框，选择【图案】，在【单元格底纹】中选择“浅黄色”。（9）选中A4单元格，输入“财年始于：”，设置“字体”为“宋体”，“字号”为“10”号，“字形”为“加粗”。（10）合并B4:C4，设其“字体”为“Aria”，“字号”为“10”号，“水平对齐”为“居中”，“垂直对齐”为“靠下”，底纹为“浅青绿”色，输入日期“2008-1-1”，如图7-4所示。图7-4输入起始日期（11）打开【单元格格式】对话框，选择“数字”选项卡，从“分类”中选“日期”，“区域设置”选“中文（中国）”，“类型”选“2001年3月”日期显示类型，如图7-5所示。设置完毕，单击【确定】返回工作表，单元格中的日期格式已经更改为设置的显示方式了，如图7-6所示。图7-5设置日期显示格式图7-6更改日期显示格式二、自动计算列标题中的月份列标题中月份的自动计算，操作步骤如下：（1）选中B5，输入公式“=B4”，然后单击回车。（2）打开【单元格格式】对话框，选择“数字”选项卡，从“分类”中选“自定义”，“类型”中选“yyyy-mm”。（4）设此单元格“字体”为“Arial”，“字号”为“9”号，“字形”为“加粗”，“水平对齐”为“居中”，同时适当调整列宽。（5）选定C5，输入百分号“%”，然后按照步骤（4）设置单元格格式。1月份的列标题就制作完成了，其他月份的列标题以及年度列标题的制作要和1月份的一样。（6）同时选中B5和C5，将B5和C5中的数据和格式复制粘贴到D5和E5单元格中。（7）使用鼠标左键单击E5右下角出现的【粘贴选项】按钮，选择“保留源列宽”选项，D5和E5单元格的格式就设置好了。（8）更改D5单元格中的公式。选中D5单元格，输入公式“=DATE(YEAR(B4)，MONTH(B4)+1，1)”，按回车键后正确的结果就显示在D5单元格中了，（9）使用相同的方法，制作出所有的月份，在制作的过程中需要改变的是DATE函数中的年份和月份参数，所有日期的计算公式如表7-1所示。表7-1日期计算公式单元格月份输入公式B5一月份=B4D5二月份=DATE(YEAR(B4)，MONTH(B4)+1，1)F5三月份=DATE(YEAR(D5)，MONTH(D5)+1，1)H5四月份=DATE(YEAR(F5)，MONTH(F5)+1，1)J5五月份=DATE(YEAR(H5)，MONTH(H5)+1，1)L5六月份=DATE(YEAR(J5)，MONTH(J5)+1，1)N5七月份=DATE(YEAR(L5)，MONTH(L5)+1，1)P5八月份=DATE(YEAR(N5)，MONTH(N5)+1，1)R5九月份=DATE(YEAR(P5)，MONTH(P5)+1，1)T5十月份=DATE(YEAR(R5)，MONTH(R5)+1，1)V5十一月份=DATE(YEAR(T5)，MONTH(T5)+1，1)X5十二月份=DATE(YEAR(V5)，MONTH(V5)+1，1)(10)最后制作“年度”列标题。先使用复制、粘贴的方式将Z5和AA5的格式设置好，然后将Z5的内容改为“年度”。选中所有列标题，将单元格底纹设置为“黑色”、字体设置为“白色”，完成后如图7-10所示。图7-10改变底纹和字体颜色通过这样制作列标题可以实现当“财年始于”对应的日期发生更改的时候，列标题中的日期会随着自动更改。如图7-11所示。图7-11自动更改日期三、各项数据的输入输入产品销售的各项数据步骤如下：（1）从A6开始，按列依次输入“销售收入”、各产品名称、“销售收入合计”，从A16开始，按列依次输入“销售成本”、各产品名称、“销售成本合计”、“毛利”，这里的产品名称要与销售收入下的产品名称相同，并且顺序相同。（2）在A28中，输入“费用”，从A29单元格开始按列依次输入“工资”、“交通”、“商务”、“办公”、“房租”、“差旅”、“水电”、“电话”、“福利”、“运输”、“税金”、“费用合计”、“纯利”等项目。（3）从B7开始，按列依次输入各产品1月对应的预计销售收入、销售成本、各项费用值，然后选中1月份所有已输入的数据，格式设为“货币”，“小数位数”为“0”，“货币符号”为“无”，“负数”为“-1,234”样式。（4）各产品2~12月对应的预计销售收入、销售成本、各项费用值的也依次输入，然后将表中1月份对应的列单元格区域【复制】，通过选择【编辑】|【选择性粘贴】，粘贴到2~12月份列标题下的单元格区域，在【粘贴】选项区域中选择“格式”选项，如图7-13所示。图7-13选择性粘贴四、填写并计算销售收入填写并计算销售收入操作步骤如下。（1）选中A6、A14、A16、A24、A26、A28、A40、A42，设置“字体”为“宋体”，“字号”为“9”号，“字形”为“加粗”，【水平对齐】为“常规”，【垂直对齐】为“靠下”，在【文本控制】中选“自动换行”。（2）选中B7：B14，单击工具栏中的【自动求和】按钮，计算出的1月份销售收入合计在B14单元格中显示。（3）其他月份对应的销售收入合计也可以使用此方法计算，还可以在公式栏或单元格中直接输入“=SUM（B7:B14）”使用SUM函数来进行计算。（4）选中C7单元格，设置格式为“自定义”“0.0”样式。（5）在C7中输入公式“=IF（B$14=0,”-“,(B7*100)/B$14）”,输入完毕后按回车键。（6）把鼠标指针移到C7的右下角，按住左键向下拖动到最后一种产品的单元格时松开鼠标，这时Excel会为选中的单元格自动计算百分比和复制格式。（7）选中C7，把C7中的公式复制粘贴到E7，然后将鼠标移动到E7右下角拖动，将同一月份其他产品的销售百分比计算出来，使用同样的方法把其他月份的求百分比的公式也完成。（8）选中Z7单元格，然后输入公式“=B7+D7+F7+H7+J7+L7+N7+P7+R7+T7+V7+X7”，按回车键，第一种产品的各月销售收入累加的结果就显示在Z7单元格中。（9）选中Z7单元格，把鼠标指针移到Z7单元格的右下角，按住左键向下拖动，把其他产品的年度销售收入合计都计算出来，百分比的计算方法和前面的步骤相同，计算完成后的结果如图7-18所示。图7-18完成销售收入计算（10）把需要人工输入的单元格区域的底纹颜色设置为“淡蓝色”。五、填写并计算销售成本操作步骤如下:（1）选中B17：B24，单击工具栏中的【自动求和】按钮，结果将在B24中显示。（2）其他月份对应的销售成本合计也使用此方法计算。（3）选中C17单元格，设置格式为“自定义”“0.0”样式。（4）在C17中输入公式“=IF（B7=0,”-“,(B17*100)/B7）”,计算出销售成本占销售收入的百分比。（5）选中C17，使用鼠标拖动的方法，完成其他产品以及销售成本合计的相关计算。（6）选中Z17单元格,然后输入公式“=B17+D17+F17+H17+J17+L17+N17+P17+R17+T17+V17+X17”，输完后按回车键，第一种产品的各月销售成本累加的结果就显示在Z17单元格中。（7）选中Z17单元格，使用鼠标拖动的方法，完成其他产品的年度销售成本合计的计算，再计算各种产品的年度销售成本合计占年度销售收入合计的百分比。（8）把需要人工输入的单元格区域的底纹颜色设置为“浅黄色”。六、写并计算毛利、开支、纯利1.毛利部分的计算毛利就是“销售收入合计”减去“销售成本合计”的差额。（1）在B26中输入公式“=B14-B24”,计算出1月份的毛利，在C26中输入“=IF(B14=0，-，(B26*100)/B14)”，计算出一月份的毛利占1月份销售收入合计的百分比。（2）选中B26和C26，鼠标指针指向右下角变成“＋”字形时，按行的方向拖动鼠标将其他月份相应数据计算出来填入。图7-20计算毛利2.日常开支部分的计算（1）选中B29:B40，单击工具栏中的【自动求和】按钮。（2）选中C29，输入公式“=IF（B$14=0,-,(B29*100)/$B$14）”，计算出一月份该项费用占本月销售收入合计的百分比。（3）选中B29和C29，使用鼠标先按行的方向拖动计算出其它月份该项费用占销售收入合计的百分比，分别选中B29:AA29中对应每个月求百分比的单元格，又分别按列的方向使用鼠标拖动方式计算相应的百分比。（4）为需要手工输入数据的单元格设置底纹颜色为“浅青绿”。3.纯利部分的计算纯利就是用“毛利”减去“费用合计”的差额。（1）在B42中输入公式“=B26-B40”计算“纯利”，在C42中输入公式“=IF(B14=0,-,