Excel在审计实务中的应用举例

Excel在审计实务中的应用举例基础运用函数公式实用技巧练习1基础运用-常用快捷键及应用举例熟练掌握复制：Ctrl+C；粘贴：Ctrl+V剪切：Ctrl+X；快速求和：Alt+=查找：Ctrl+F或Shift+F5打印：Ctrl+P定位：Ctrl+G或F5替换：Ctrl+H单元格中换行：Alt+Enter按F1，输入“快捷键”，即可查看excel自带的所有快捷键，可根据需要自行学习2基础运用-常用快捷键及应用举例快速选中多个单元格：Ctrl+Shift+箭头适用于：Excel表格中需要选中某一列（行）的值，该列（行）数据很多，采取下拉方式耗时较长，可以在需要选中的列中选择第一个单元格（也可多个，意味着选中多列），按Ctrl+Shift+下箭头，即可选中。选择最后一个单元格则换为上箭头，选中行的原理相同。该方法只能选中有值得单元格列（行），遇到空格自动终止。其他方法：在Excel目标区域输入区输入需选中的单元格范围，如A2：A10000，按下回车键即可。移到数据边缘：Ctrl+箭头；移动到工作表开头/末尾：Ctrl+Home/End3基础运用-常用快捷键及应用举例重复上一次操作：F4或Ctrl+Y；撤销上一次操作：Ctrl+Z；自动求和：Alt+等号；设置单元格格式：Ctrl+1；设置百分比格式：Ctrl+Shift+%；应用年月日格式表示：Ctrl+Shift+#；将12456345.23567格式调整为12,456,345.24：Ctrl+Shift+！，实现使用千位符且负数用负号-表示的数字格式，再单击开始-增加小数点两次添加两位小数，如右图所示4基础运用-常用快捷键及应用举例插入新工作表：Shift+F11或Shift+Alt+F1；移动到上一张工作表：Ctrl+Pageup；移动到下一张工作表：Ctrl+Pagedown；选中多张工作表：Ctrl+工作表；选中连续多张工作表：首张工作表+Shift+末张工作表；打√：Fn+F8调出数字键盘，Alt+414205基础运用-常用快捷键及应用举例自定义快捷键用途：将经常使用的固定步骤自定义为快捷键，避免重复操作，提高工作效率；方法：录制宏、使用VisualBasic编辑器；示例1：快速将数字区域调整格式为外边框粗、内边框细，字体为NEWTIMESROME，加上千位符，保留两位小数，负数以负号显示，垂直居中，水平居右。6基础运用-常用快捷键及应用举例接示例1：将左表到右表的调整步骤固化为快捷键方法：选定数字区域，视图-宏-录制宏，设置快捷键为Ctrl+Shift+Q（可根据个人喜好设置），操作一遍达到需实现的效果，结束宏录制。下次需调整数据区域实现该种效果时，只需选中区域，按下Ctrl+Shift+Q（即设置的快捷键）即可。7基础运用-常用快捷键及应用举例示例2：将选择性粘贴固化为快捷键Ctrl+Z适用性：审计中需要频繁将特定区域选择性粘贴为数值，使用鼠标操作非常缓慢，快捷键过于复杂，自定义快捷键方便快速操作。选择性粘贴需变换粘贴位置，录制宏不能实现需要的自定义快捷键功能，可使用VisualBasic编辑器编写代码，因原理较为复杂，不再赘述，可按下列方法实现固化。方法：关闭Excel，将文档（见附件）复制到C:\DocumentsandSettings\用户名\ApplicationData\Microsoft\Excel\XLSTART下，即可实现Ctrl+Z为选择性粘贴-数值，简化了操作步骤。8基础运用-基础操作举例快速求和（表格见附件“示例-表一”）适用范围：需要求和单元格较多、且需要求和的行数并不完全相同的表格，不能复制Sum函数直接使用，单项输入Sum函数求和速度缓慢且易遗漏。操作方法：如右图所示，选定B3：B15，F5定位-空值，确定后Alt+等号确定后实现自动求和。9基础运用-基础操作举例为数字自动添加单位示例：如右表所示，为B列数据添加单位“个”方法1：选定B3：B15，开始-数字-自定义，在类型中输入#.00”个”，确定即可；方法2：如右下图所示，在C列输入对应的单位“个”，在D3输入公式=B3&B4，确定，再将公式下拉，选择性粘贴为数值后，辅助列C可删除。10基础运用-基础操作举例数字分段显示示例：将2014928显示为2014-9-28，开始-数字-自定义，在类型中输入0000-0-00；将2014928显示为2014年9月28日，开始-数字-自定义，在类型中输入0000年0月00日。可选中数据区域批量操作。锁定和隐藏公式，使工作簿不能编辑选定需锁定的区域，审阅-保护工作表-输入密码，则不能对此区域进行编辑。保护工作簿如不希望他人整体移动工作簿，只能看，审阅-保护工作簿-保护结构和窗口-输入密码；如只希望特定的人查看，另存为-工具-常规选项，可设置打开权限密码、修改权限密码或设置为只读。11基础运用-基础操作举例数据透视表（表格见附件“示例-表2”）适用范围：数据的快速归类汇总，审计工作中可用于根据存货盘点表编制代保管存货发函清单、根据调整分录汇总表编制试算平衡表、根据序时账汇总各科目各月发生额等。操作步骤：插入-数据透视表-选择表区域-放置于新工作表-根据需要添加字段。示例：以存货盘点表编制发函清单为例。利用存货盘点表对所有代储库点进行发函，需先编制发函清单，库点较多时使用透视表功能可保证快速、准确。12基础运用-基础操作举例操作步骤：1、插入透视表：插入-透视表-选择表区域为!$A$3:$H$162，确定后，选择添加的字段，将存储库点、性质、品种设置为列标签，盘点日、报表日数量拖到数值区域，将出现新的表格。13基础运用-基础操作举例步骤2、对新表格根据需要进行调整：如图，新表格式并不能满足作为发函清单的需要，在A5、A6单元格点击右键去掉分类汇总的对勾，并在字段设置-布局和打印-在同一列中显示下一字段的标签去掉对勾，将透视表选择性粘贴-数值到其他区域，对A、B列分别进行填充（定位-空值-Ctrl+Enter），对C列筛选为空值的行删除，即可直接作为发函清单了。14基础运用-基础操作举例定位填充：接上例，详细说明定位填充，如表所示。（表格见附件“示例-表3”）步骤：将A5复制到A6，选中A6：B40，F5或Ctrl+G定位-空值，按下等号A6单元格，Ctrl+Enter完成A、B列的填充。（思考：为何要先将A5复制到A6？）15基础运用-基础操作举例高级筛选：将符合多个特定条件的内容快速筛选出来，适用于凭证抽查。示例：以凭证抽查为例，见附件“示例-表4-凭证查询”，以抽取预收账款贷方发生额前十名为例。步骤：在D行筛选-开头是，输入2203（预收账款科目代码），F行点击降序排列，在A、B行选择前十行（注意还要复制表头）复制到一张空表中，将原表的筛选模式去掉（或者操作前将表格复制为原表），将鼠标移至空白单元格，筛选-高级-列表区域选择“凭证查询!凭证查询!$A$2:$F$892”（即筛选的数据来源），条件区域选择“高级筛选!$A$1:$B$11”（即需要匹配的条件），点击确定即可得到预收账款贷方发生额前十名的凭证。16基础运用-基础操作举例邮件合并：适用于按统一模板填制的多张文档，如录取通知书、请柬等，在审计中如果被询证方数量特别巨大时可用于快速编制询证函，因往来询证函已开发出模板，不再需要使用邮件合并功能。对于代储库点特别多的企业编制代保管询证函依然适用，该功能仅能在word中使用，考虑到用途比较广泛，再次一并介绍。（表格见附件“示例-表5”）示例：如表所示，共需编制15张询证函，一一复制较为缓慢，使用邮件合并功能可以实现快速编制询证函。17基础运用-基础操作举例操作步骤：邮件-开始邮件合并-信函-使用当前文档-使用新列表-浏览-选择发函清单，勾选需要插入的行-撰写信函选择其他项目，在word文档中需要插入的地方选择相应的区域插入，如被询证单位名称处选择F2插入，将编号、性质、品种、数量均按对应区域插入，预览信函，将格式调整好，完成合并-编辑单个信函，即可查看所有询证函。邮件合并中添加千位符：全部插入完成后，按Alt+F9，在需要加千位符的域代码后加入千位格式码，如原来的域为{MERGEFIELD“F5“}，加入格式码后为{MERGEFIELD”F5”\#“#,##0”}，上面为整数格式。下面为保留2位小数，{MERGEFIELDF5\##,##0.00}加入格式码后，你要按Alt+F9，再按F9更新域才能看到结果。18基础运用-基础操作举例为Word中的数字添加千位符方法一：查找([0-9])([0-9]{3})([!][0-9年])，在查找选项中勾选使用通配符，替换为\1,\2\3。可以为带有小数位的数字添加千位符，年份不变。方法二：打开WORD，Alt+F11，打开VisualBasic编辑器，打开VisualBasic，在右边的窗口中复制代码，代码详见附件。此方法会为所有数字添加千位符，年份数字也会发生变化。19函数公式VLOOKUP函数（表格见附件“示例-表6-VLOOKUP”）=vlookup（A1，$B$1：$D$20,5,0）A1为需要匹配的单元格，$B$1：$D$20为与需要匹配单元格相匹配的区域，5为相匹配的区域中德第几列，0为精确匹配。用途：用于引用具有相同项的两张表格中的值；比较两张表格，具有相同项的返还相应值，无相同项的返回N/A#。示例：在B3单元格中输入公式=VLOOKUP(A3,$D$3:$E$107,2,0)，注意将D3:E107用F4锁定为绝对引用，下拉公式即可比对出对应项目的成本，使用成本项目查找出对应的收入同理。20函数公式If函数（表格见附件“示例-表7”）=IF(A4=C4，0，1)，表示如果满足条件A4=C4，则返回0，否则返回1。条件可以使等式也可是不等式，可以根据需要设置。公式可以多重嵌套，也可与其他函数结合使用，如IF(E41=B41,IF(C41=F39,D41,0),0)，表示需满足两个条件才返回相应的值。示例1：将表格与标准表格核对，使用公式=IF(A4=C4,“一致”,”不一致“)，如果一致则返回一致，不一致则返回不一致。示例2：使用多重嵌套公式=IF($F$29=VLOOKUP(E30,$B$30:$C$36,2,0),VLOOKUP(E30,$B$30:$D$36,3,0),0)，因两表各项目顺序不一致，只使用if函数难以达到目的，嵌套vlookup函数并返回匹配项，完成表格填写。21函数公式Sumif函数（表格见附件“示例-表8”）单条件汇总=SUMIF(B9:B20,B25,C9:C20)，B9:B20为范围（range），即需要求和的项目区域；B25为标准（criteria），即需要求和的项目名称；C9:C20为需要求和的范围（sum-range），即需要求和的数字区域。用途：适用于由明细账到总账的过程，无需筛选加总一次性统计加总，非常方便。示例：在A列右侧插入列，将A列数据分列，列示在B列，在需填列数据的单元格输入=SUMIF($A$3:$A$54,D3,$B$3:$B$54)，再下拉即可。22函数公式Sumif函数条件汇总（表格见附件“示例-表1”）=SUMIFS(B3:B15,B3:B15,“=550000”,B3:B15,“=1000000”)，表示汇总B3:B15区域数量为55万至100万的项目之和。综合计算求和（表格见附件“示例-表1”）示例：=SUMPRODUCT($B$26:$G$26,B27:G27)，将对应单元格乘数相加。避开单元格中的错误值求和（表格见附件“示例-表1”）示例：=SUMIF(B35:B37,9.99E+307)23函数公式Len、Left、Right函数（表格见附件“示例-表9”）可用于不规则文字与数字的分离，如果每行文字长度都一样，可以用数据分列分离出来。A2为原始数据，B2=len(A2)为返回文本字符串的字符数，C2=lenb(A2)为返回文本字符串的字节数。D2中（C2-B2），C2与B2之差就是汉字的个数(23-16