117
大家好！大家好！见面喝酒少不了！你不喝，我不喝，国家造酒往哪搁？你不醉，我不醉，国家马路谁来
睡？
两个重要的统计表CHAPTER7
·
第7章两个重要的统计表
财务制作统计表，那是家常便饭。定期的、临时的、小型的、巨大
的……只有经过整理统计，那些数据才能清晰反映出每年、每月、每个单
位的情况，例如本章要讲解的收费统计表。还有财务人员常用的账龄统计
表，经过Excel处理后，能直观地反映出每个往来单位的账龄。这对于及时
观察往来账的诉讼时效，避免给企业造成损失由很大的作用。下面，我们
就来对这两种统计表的制作进行讲解。
本章要点：1.填充序列、SUMPRODUCT函数
2.IF函数、AND函数
收费统计表效果图
账龄统计表效果图
118
财务管理必会Excel应用100例办公室之
·
7.1
收费统计表
对于收费，相信财务都会有详细的记录，包含了缴费时间、缴费单位、金额等信
息。但这一条条记录对于分析判断起不了多大作用。但如果将这些信息进行归类整理，
制作有针对性的收费统计表就不一样了。有了Excel，我们不用手工整理计算，自动生成
统计表即可。
收费统计表效果图
步骤01制作收费记录表
启动Excel2007创建一个新的工作簿，将Sheet1改名为“收费登记表”，并保存。在
A1:E1单元格区域输入标题。然后录入日常收费记录，适当调整列宽保证单元格中内容完
整显示。设置边框线、字体、字号和文本居中显示等，并取消网格线的显示。
如何快速输入数据序列？
119
两个重要的统计表CHAPTER7
·
步骤02创建收费统计表
将Sheet2工作表改名为“收费统计表”，并保存。输入标题，对B1:C1、D1:E1和F1:
G1单元格区域进行合并居中操作，并适当调整列宽。
步骤03填充序列
选中A3单元格，输入“1”，将光标放在A3单元格的右下角，当光标变成黑十字形
状时，按住鼠标左键不放，向下拖拽光标到相应位置松开。单击右下角自动填充的下拉
小三角，在弹出菜单中选择“填充序列”。这时，A列单元格区域就按序列自动生成了
一排数字。同样的操作，却得到不同的结果，个中细节需要表哥、表姐自己体会。熟练
使用可以让Excel带来更多的方便。
步骤04设置单元格格式
选中B3:G14单元格区域，单击鼠标右键弹出快捷菜单，在“设置单元格格式”中，
选择“货币”，而在“货币符号”下拉列表中选择“无”，点击“确定”完成设置。
在第一个单元格内输入起始数据，在下一个单元格内输入第二个数据，选定这两个单元格，将光标指向单元格
右下方的填充柄，沿着要填充的方向拖动填充柄，拖过的单元格中会自动按Excel内部规定的序列进行填充。
120
财务管理必会Excel应用100例办公室之
·
步骤05编制单位1的2008年收费公式
选中B3单元格，在编辑栏中输入公式：“=SUMPRODUCT((收费登记
表!$C$2:$C$100=$A3)(收费登记表!$D$2:$D$100=LOOKUP(2,1/($B$1:B$1<>""),$B$1:
B$1))(收费登记表!$B$2:$B$100=B$2)收费登记表!$E$2:$E$100)”，按回车键确认。使
用拖拽的方法完成该列公式的复制。
步骤06横向复制2009年收费公式
选中B3单元格，将光标放在单元格的右下角，当光标变成黑十字形状时，按住鼠标
认识单元格的保护功能
121
两个重要的统计表CHAPTER7
·
左键不放，朝右拖动光标到C3单元格松开，就能完成C3单元格的公式复制。使用拖拽的
方法，完成C列公式的复制。
我们前面的公式复制都是在同列中进行的，对于不同列横向的公式复制，方法不过
时大同小异。
步骤07编制单位2的收费公式
选中D3单元格，在编辑栏中输入公式：“=SUMPRODUCT((收费登记
表!$C$2:$C$100=$A3)(收费登记表!$D$2:$D$100=LOOKUP(2,1/($B$1:D$1<>""),$B$1:
D$1))(收费登记表!$B$2:$B$100=D$2)收费登记表!$E$2:$E$100)”，按回车键确定。将
该公式复制到E3单元格，选中D:E3单元格区域，使用公式复制的方法完成D4:E14单元格
区域公式的复制。
步骤08编制单位3的收费公式
选中F3单元格，在编辑栏中输入公式：“=SUMPRODUCT((收费登记
表!$C$2:$C$100=$A3)(收费登记表!$D$2:$D$100=LOOKUP(2,1/($B$1:F$1<>""),$B$1:
F$1))(收费登记表!$B$2:$B$100=F$2)收费登记表!$E$2:$E$100)，按回车键确认。使用
上步骤的方法，完成F3:G14单元格区域的公式。
工作簿（表）被保护之后，还可对工作表中某些单元格区域的重要数据进行保护，起到双重保护的功能。选
定需保护的单元格区域，进入“审阅”选项卡，在“更改”组中选择“保护工作表”命令。
122
财务管理必会Excel应用100例办公室之
·
步骤09合计
选中A15单元格，输入“合计“。在B15:G15单元格区域的各个单元格中制作各列求
和公式。
步骤10完善表格
设置表头填充色，设置字体、字号和边框线，取消网格线和零值的显示。
7.2
账龄统计表
无论是对内还是对外，企业都需要进行账龄分析。特别是法律健全的今天，各企业
对应收账款的账龄数应更为关心。因为账龄一旦超过诉讼时效，就不再受法律保护，财
务人员必须及时创建账龄分析表，提醒相关决策者。
本节将示范如何通过Excel直观反映每个账户的账龄，并计算出每个账龄区间总额。
如何使单元格中的颜色和底纹不打印出来？
123
两个重要的统计表CHAPTER7
·
账龄统计表效果图
步骤01新建工作表
启动Excel2007新建一个工作簿，将Sheet1改名为“往来账龄分析”，并保存。选中
A2单元格，输入“截止时间：”，在B2输入“2009-1-1”，A3:I3输入标题，设置文字
居中显示，然后适当调整单元格列宽，保证单元格内容完整显示。在A4:A18输入单位名
称，选中A19单元格，输入“合计”。
步骤02录入“期末金额”并求和
进入“页面布局”选项卡，打开“页面设置”组，在“工作表”标签中，找到“打印”栏，选择“单元格单
色打印”选项。
124
财务管理必会Excel应用100例办公室之
·
在B4:B18单元格区域输入“期末金额”，选中B19单元格，点击“编辑”中的Σ符
号，自动生成求和公式：“=SUM(B4:B18)”，按回车键确认。
步骤03设置日期格式并录入数据
选中C4:C18单元格区域，点击鼠标右键弹出快捷菜单，在“设置单元格格式”中，
在“数字“中选择”日期，在“类型”中选择“2001-03-14”，点击确定完成设置。然后
录入最后一笔交易的日期。
步骤04设置自定义格式
选中D1:H2单元格区域，在右键菜单中选择“设置单元格格式”，然后选择“自定
义”，在“类型”文本框中输入“0"天"”，点击确定按钮完成设置。
步骤05输入数据
在C1单元格输入“上限值天数”，在C2单元格输入“下限值天数”，然后在D1:H2
单元格区域录入天数数字，然后表格中就自动在数字后加个“天”，如图所示。
工作表保护的口令忘记了怎么办？
125
两个重要的统计表CHAPTER7
·
步骤06编制“金额”公式
选中D4单元格，在编辑栏中输入公式：“=IF(AND($B$2-$C4>=D$2,$B$2-
$C4
知识点：AND函数
AND函数是Excel逻辑函数中较为常用的函数之一，当所有参数的逻辑值为真时它返
回TRUE，只要有一个参数的逻辑值为假即返回FALSE。AND函数一般不单独使用，而
是作为嵌套函数与IF函数一起使用。由AND函数返回的值作为IF函数的条件判断依据，
最后返回不同的结果。
函数语法
AND(logical1,logical2,...)
logical1,logical2,...表示待检测的1到30个条件值，各个条件值可以为TRUE或
FALSE。
如果你想使用一个保护了的工作表，但口令又忘记了，有办法吗？有。选定工作表，选择“编辑”\“复
制”、“粘贴”，将其拷贝到一个新的工作薄中（注意：一定要是新工作簿），即可。
126
大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家
财务管理必会Excel应用100例办公室之
·
函数说明
参数必须是逻辑值TRUE或FALSE，或者包含逻辑值的数组或引用。如果数组或引用
参数中包含文本或空白单元格，这些值将被忽略。如果指定的单元格区域内包括非逻辑
值，AND将返回错误值#VALUE!。
步骤07横向、纵向复制公式
选中D4单元格，将光标移到单元格右下角，当光标变成黑十字形状时，按住鼠标左
键不放，朝右拖拽到H4单元格松开，完成横向公式的复制。选中D4:H4单元格区域，将
光标放在H4单元格右下角，用同样的方法向下拖拽完成纵向单元格区域的公式复制。
步骤08编制“合计”
选中I4单元格，在编辑栏输入求和公式：“=SUM(D4:H4)”，按回车键确认。然后
完成I5:I19和D19:H19单元格区域完成求和公式。
步骤09完善表格
对表格的字体、字号和边框线和填充色进行设置，并取消网格线和零值的显示。
|
|
