35
大家好！大家好！见面喝酒少不了！你不喝，我不喝，国家造酒往哪搁？你不醉，我不醉，国家马路谁来
睡？你不醉，我不醉，国家马路谁来睡？
凭证与凭证汇总表CHAPTER2
·
第2章凭证与凭证汇总表
一说到从事财务工作的人，总让人联系到旧时账房先生的形象。账
本、算盘、笔、眼镜，在灯下仔细一笔一笔整理核对记录，然后装订成
册，一天天，一年年，累积成无数的厚重账本。账房先生的工作，财务工
作人员仍在重复着，但可以不再用笔、用算盘，也不用为了一个陈年的数
据而搬动厚重的账本。电子凭证最大的好处不是省了很多的纸张，而是查
阅方便。输入查询条件，很快就能得到想要的数据。但不管电脑给财务人
员带来多大的方便，财务工作仍离不开日复一日的数据记录，凭证汇总和
制作各种账表，只是有了电脑的帮助，工作可以变得更轻松，只要你善于
利用Excel。
本章要点：1.数据有效性、COUNTIF函数、冻结窗格
2.自动筛选、工作表的保护
凭证明细表效果图凭证汇总表效果图
36
财务管理必会Excel应用100例办公室之
·
2.1
科目代码录入
有些工作注定是繁琐而没有创造性的重复劳动，比如科目代码的录入工作等。但这
些工作就像基本功，只有基本功练好了，才有了学习高深武功的资本。
凭证时记录会计信息的重要载体，是生成对外报表的数据基础。汇总凭证是财务工
作中的一个重要环节，重大的财务报表也是从这些源头上来。录入、汇总、分析、规
划，一步步形成了完善的核算体系。
科目代码效果图
步骤01新建工作表
EXCEL文档损坏该怎么办？
37
凭证与凭证汇总表CHAPTER2
·
启动Excel自动新建一个工作簿，保存并命名。双击工作表标签，将工作表改名
为“科目代码”，按回车键确定。单击鼠标右键弹出快捷菜单，为工作表标签设置
一个颜色。
步骤02设置数据有效性
选中A2单元格，点击“数据”→“数据工
具”→“数据有效性”，弹出“数据有效性”对
话框，“设置”→“允许”→在下拉列表中选择
“自定义”。在“公式”文本框中输入公式：
“=COUNTIF(A:A,A2)=1”，点击确定完成设置。
知识点：COUNTIF函数
COUNTIF函数用来返回计算区域中满足给定条件的单元格的个数。
函数语法
COUNTIF(range,criteria)
range：为需要计算其中满足条件的单元格数目的单元格区域。
criteria：为确定哪些单元格倍计算在内的条件，其形式可以为数字、表达式和文本。
函数说明
Excel提供其他的函数，可用来基于条件分析数据。例如要计算基于一个文本字符串
或某个范围内的一个数值的总和，可以使用SUMIF函数。若要使公式返回两个基于条件
的值之一，例如某个指定销售量的销售红利，则可使用IF函数。
在本例中，是指在A列中查找和A2单元格中数字相同的单元格个数为1，即在A列中
没有与A2单元格中的数字相同的单元格。
步骤03用格式刷复制设置
介绍一款很不错的Excel文档恢复工具：ExcelRecovery。
38
财务管理必会Excel应用100例办公室之
·
选中A1单元格，单击“开始”→剪贴板上的格式刷按钮，此时，光标起了变化。按
住Shift键不放，单击A100单元格，第一列格式复制完成，光标回复常态。
步骤04输入数据、完善表格
这里可没有窍门或技巧，老老实实根据公司的科目输入数据吧。数据录入完成，简
单设置字体、字号和边框线即可。
步骤05冻结窗口
根据每个公司情况的不同，科目的数量也不同。在进行数据录入的时候，财务人员
经常会遇到这样的情况：不管多大的显示器，都无法完整显示表格，给录入工作带来些
许不便利。录入时间一长，还可能出错。这个时候可以用到Excel冻结窗格的功能。在本
例中就遇到这种情况，这时，选中A2单元格，点击“视图”→“窗口”→“冻结窗格”
→“冻结首行”。现在，我们下拉表格操作时，第一行始终固定不动，非常方便查看。
如何实现EXCEL开方运算？
39
凭证与凭证汇总表CHAPTER2
·
2.2
凭证明细表
创建好科目的代码之后，就要开始创建凭证明细表了。
凭证明细表效果图
2.2.1
设置科目代码的录入方式
步骤01新建工作表
单击工作表标签Sheet2，改名为“凭证明细”，然后在A1:G1单元格区域输入标题名称。
步骤02定义科目代码名称
单击“科目代码”工作表标签进入“科目代码”工作表，选中A2:A100单元格区
域，然后单击菜单“公式”→“定义名称”→弹出“新建名称”对话框，在“名称”中
例如将8开3次方，可以用这个公式，在单元格中输入“=8^(1/3)”
40
财务管理必会Excel应用100例办公室之
·
输入“data”，然后单击确定按钮。这样就将“科目代木”工作表中的A2:A100单元格区
域的名称定义为“data”。
步骤03设置数据有效性
单击“凭证明细”工作表标签，选中C2:C204单元格区域，点击“数据”→“数据工
具”→“数据有效性”，在弹出的“数据有效性”对话框，在“设置”→“允许”→选
择“序列”。在“来源”中输入“=data”，单击确定按钮。
步骤04单元格边的下拉列表
此时，单击C2单元格，在单元格的右侧会出现一个下拉按钮，鼠标单击下拉按钮会
弹出一个下拉列表，列表中的内容是“科目代码”工作表中的科目代码，用户可以用鼠
标选择输入内容。
步骤05输入数据
在A2:E204单元格区域输入数据，遇到数字过长，可适当调整列宽，让单元格中的数
据完整显示。
如何查找工作表中的链接？
41
凭证与凭证汇总表CHAPTER2
·
2.2.2
编制一、二级科目代码
步骤01输入一级科目代码
选中F2单元格，在编辑框输入公式：“=VLOOKUP(C2,科目代码!$A$2:$C$171,
2,FALSE)”，按回车键确认。选中F2单元格，将光标放在单元格的右下角，当光标
变成黑十字形状时按住左键不放，向下拖拽到相应位置后松开左键，即可完成该列
的公式复制。
步骤02输入二级科目代码
选中G2单元格，输入公式：“=VLOOKUP(C2,科目代码!$A$2:$C$171,3,FALSE)”，
按回车键确认。选中G2单元格，将光标放到右下角，当光标变成黑十字形状，按住鼠标
左键不放，向下拉动鼠标到相应位置，松开左键即可完成该列公式的复制。
使用“Ctrl+~”或“编辑→链接”。
42
财务管理必会Excel应用100例办公室之
·
知识点：本例公式说明
一级科目代码公式：是指在“科目代码”工作表的A2:C171单元格区域的A列中查找
与“凭证明细”工作表中C2单元格中内容相同的单元格，然后返回对应B列中的内容到
当前单元格。
二级科目代码公式：是指在“科目代码”工作表的A2:C171单元格区域的A列中查找
与“凭证明细”工作表中C2单元格中相同的单元格，然后返回对应C列中的内容到当前
单元格。
2.2.3
自动筛选
步骤01设置自动筛选
如何让空单元格自动填为0？
43
凭证与凭证汇总表CHAPTER2
·
选择A1:G1单元格区域，点击“数据”→“筛选”，此时，在A1:G1单元格区域的每
个单元格的右侧会出现一个小三角的下拉按钮。
步骤02进行筛选查询
单击A1单元格右侧的下拉按钮，然后选择“6”，“凭证明细”工作表中就会筛选
出“序号”为6的记录。搭配其他几项进行筛选查询，可以更加精确地查找到符合条件的
记录。
步骤03美化表格
对表格的字体、字号、边框线等进行简单设置，为方便显示A1:G1单元格区域适当
加宽。为方便浏览，可使用冻结窗格的功能。
选中需更改的区域，打开查找对话框，将空替换为0。
44
财务管理必会Excel应用100例办公室之
·
2.3
凭证汇总表
前面一系列工作，其目的就是为了编制凭证汇总表，只有经过数据汇总，才能给出
一个明确的数据，成为规划决策的依据。这里我们选择一些重要的项目为大家讲解汇总
表的制作，当然，在实际凭证汇总中，每个项目都是不能省略的。
凭证汇总表效果图
步骤01新建工作表
在“凭证明细表“工作簿中，将Sheet3改名为“凭证汇总”，在A1单元格输入“凭
证汇总表”，在A3：C3中分别输入标题。选中B2单元格，在右键菜单中选择“设置单元
格格式”→“日期”→“2001年3月”。然后输入表格的时间“2009-5-7”。在C2单元格
输入“编号：第号至第号”。
步骤02输入科目名称
在A4:A19单元格区域中输入科目名称，在A20单元格输入“合计”。
用Excel做多页的表格时，怎样像Word的表格那样做一个标题，即每页的第一行（或几行）是一样的。但是
不是用页眉来完成？
45
凭证与凭证汇总表CHAPTER2
·
步骤03设置货币格式
选中B4:C19单元格区域，点击鼠
标右键弹出快捷菜单，选择“设置单
元格格式”→“数字”→“分类”→
“货币”，将“小数位数”的值设置
为“2”，“货币符号”选择“无”，
“负数”选择“-1,234.10”，然后点
击确定按钮完成单元格区域设置。
步骤04编制借方金额汇总公式
选中B4单元格，在编辑栏中输入公式：“=SUMIF(凭证明细!$F:$F,$A4,凭证明细!D:
在EXCEL的“页面布局”选项卡的“页面设置”组中，单击“页面设置”对话框启动器，在“工作表”选项
卡中选择打印标题。可进行顶端或左端标题设置，通过按下折叠对话框按钮后，用鼠标划定范围即可。
46
财务管理必会Excel应用100例办公室之
·
D)”，按回车键确认。将光标移动到B4单元格右下角，当光标变为黑十字形状时，按住
鼠标左键不放，向下拖动鼠标到B19松开，即可完成公式的复制。适当调整B列列宽，使
B列内容能够完全显示出来。
步骤05编制贷方金额汇总公式
选中C4单元格，在编辑栏中输入公式：“=SUMIF(凭证明细!$F:$F,$A4,凭证明细!E:
E)”，用上面的方法，拖动完成C5:C19单元格区域公式的复制。
步骤06编制金额合计
选中B20单元格，点击“编辑”中的Σ符号，自动生成求和公式，用同样的方法完成
C20的金额合计。
在Excel中如何设置加权平均？
47
凭证与凭证汇总表CHAPTER2
·
步骤07完善表格
表格中的0很影响表格的美观，可以采用前面说过的方法取消零值的显示。点击
Office按钮→Excel选项→高级→此工作表的显示选项→去掉复选项“在具有零值的单元
格中显示零”→确定。
设置工作表的字体、字号和边框线，根据个人爱好对表格进行美化。还可以在“页
面布局”→工作表选项→网格线→去掉“查看”复选框中的勾→确定。这样就取消显示
网格线了。
步骤08工作表的保护
每个表哥、表姐都明白财务报表对于财务工作的重要
性，每个数据都是严谨不可随意更改的。很多人拥有查阅
报表的权利，但却没有修改数据的权利。做财务工作的人
都知道，哪怕是一个数字，都足以改变全盘。那么，怎样
保证工作表不被随意改动呢？除了对自己电脑的加密保护
外，工作表同样可以设置密码保护。
在“审阅”→更改→保护工作表→在“取消工作表保
护时使用的密码”，现在，想要修改工作表，系统都会弹出一个警告窗口。如果需要修
改数据，就要先撤销工作表保护，需要输入事先设定的那个密码。当然，一定要记住这
个密码，否则自己也无法修改工作表中的数据了。我们还可以设置保护工作簿，对于一
个系统的表格，这样的保护措施是必要的。
在Excel中可设置公式解决（其实就是一个除法算式），分母是各个量值之和，分子是相应的各个数量之和，
它的结果就是这些量值的加权平均值。
|
|
