Excel在审计中的实用技巧
祁莉(江苏省睢宁县审计局)
【发布时间:2011年05月10日】
字号:【大】 【中】 【小】
    随着计算机技术的广泛运用,审计人员已经逐步从繁杂的手工劳动中解脱出来。审计人员如果能够正确、灵活地使用Excel,则能大大减少日常工作中复杂的手工劳动量,提高审计效率。 本人结合工作实际总结了一些使用方法,与大家一起分享。
    一、设计使用电子表格应把握以下几点原则
    (一)表格的设计要符合审计项目实施方案的要求。审计项目实施方案规定了整个审计项目实施的依据和总体要求,所以表格的设计就要紧贴审计项目实施方案的内容和要求进行。
    (二)表格的设计要简约实用。对于审计项目实施方案中规定的审计或审计调查的内容,表格设计一定要做到简约实用,对需要的数据项目进行采集,而有些不需要的数据项目尽量不要涉及,能使用二维表格完成的就不要使用三维表格,这样做既方便了表格填报者理解表格内容,也对表格回收后的统计工作带来极大便利。
    (三)表格数据项目的勾稽关系要合理。表格的设计最重要的一个方面就是其表内和表间的勾稽关系,在设计表格之初就应该充分考虑到表格需要的数据项目之间是否存在数理逻辑关系,在设计表格时要将这些关系在表格中体现或在表格附注中标明,这样既方便了表格填报者理解和正确填写表格,也为审计人员判断表格数据的合理性提供了重要依据。
    (四)表格设计要使用合适的计量单位,便于汇总。表格采集的数据项目不是单纯的数字,而是代表了一定的实际意义,例如货币数量、产量、工程量等,规范这些数据就要求我们选取合适的计量单位,根据审前调查和审计项目实施方案的要求,选取适当的计量单位会使得取得的数据更加有效,便于汇总。
    二、使用方法简述
    (一)基本技巧
    1、通过自动设置小数点快速输入小数。审计人员经常要输入大量的小数(通常是2位小数),可以用“自动设置小数点”功能来快速输入小数。
    执行“工具→选项”命令,打开“选项”对话框,切换到“编辑”标签下,选中“自动设置小数点”选项,然后在“位数”右侧的方框中输入数字“2”,确定返回,再输入数字,系统自动设置2位小数。
        注意:①此时,输入的数字要包含“2”位小数,如果没有小数也要用“0”补齐。例如,要输入“23、34.56”时,请输入“2300、3456”。②如果把“位数”设置为负数(如“-2”),则将输入的数字扩大“100倍”,例如,输入“123、12.45”,确认后显示为“12300、1245”。
    2、在Excel中将文本转换为数字的方法。审计人员有时从被审单位财务软件后台数据库中的数据库文件导入到Excel中,Excel可能会将其中的某些数字识别为文本。这将导致某些函数(如 SUM 和 AVERAGE)忽略这些单元格中的数值。除了要转换的数字外,这些文本串还可能包含真正的文本字符。那么如何将文本转换为数字?  
    (1)在“工具”菜单上,单击“选项”。
    (2)单击“错误检查”选项卡。
    (3)单击以选中“启用后台错误检查”复选框。
    (4)单击以选中“数字以文本形式存储”复选框。
    (5)单击以选中您要打开的其他规则。
    (6)单击“确定”。
    (7)单击包含错误指示器的单元格。
    (8)单击单元格旁边的错误按钮,然后单击“转换为数字”。
    3、Excel中快速输入有相同特征的数据。在输入一些相同特征数据,比如会计科目代码、身份证号、材料入库号等,都是前面几位相同,只是后面的数字不一样。
    有简单的方法,只输后面几位,前面相同的几位让计算机自动填充呢?下面以10位数的材料入库号(前面都是252303)为例给大家讲讲。
        方法1:
    假如要输入的数据放在A列,从A2单元格开始在下面的单元格输入入库号后面几位数字,所有的数据输入完毕后,在B2单元格中输入公式“=252303&&A2”然后回车,这样B2单元格的数据在A2的基础上就自动加上了252303。
    鼠标放到B2位置,双击单元格的填充柄(或者向下拉填充柄),瞬间B列全部加上了252303,至此所有的数据都改好了。
        方法2:
        (1) 选定要输入共同特征数据的单元格区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”命令,打开“单元格格式”对话框(也可依次选择“格式→单元格”菜单命令打开)。
        (2) 选择“数字”选项卡,选中“分类”下面的“自定义”选项,然后在“类型”下面的文本框中输入2523030000(注意:后面有几位不同的数据就补几个0),单击〔确定〕按钮即可。
        (3) 在单元格中只需输入后几位数字,如“2523034589”只要输入“4589”,系统就会自动在数据前面添加“252303”。
    也可以先输入数字,再选中单元格区域设定数据格式,可以得到相同的效果。
    4、根据身份证号提取出生日期。假定身份证号在A1单元格
     方法1:   =IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"错误身份证号"))
     方法2:
    =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1
    5、同时在多个工作表中输入相同的内容。如果同一工作簿内包含多个相似的工作表,无需分别为这些工作表输入相同的文字信息。先按住Shift键,点击工作表的名字选中所有工作表,然后在第一个工作表中填写所有工作表相同的内容,其余工作表中也会自动出现这些内容。
    6、轻松选中一大块有内容的单元。如果单纯用鼠标选择,可不是一件轻松的事情,可能会漏选,也可能因为某个单元的内容超出边界而多选了空白的单元。你可以先选中一小块包含内容的单元,然后按Ctrl+Shift+8,Excel自动把选中区域扩展到周围所有非空单元。
        (二)实务技巧
    1、利用Excel编制审计工作底稿
    审计工作要产生许多的审计工作底稿,其中不少审计工作底稿如固定资产与累计折旧分类汇总表、生产成本与销售成本倒轧表、应收账款函证结果汇总表、应收账款账龄分析表等都可以用表格的形式编制。
    (1)整理并设计各表格。审计人员先要整理出可以用表格列示的审计工作底稿,并设计好表格的格式和内容。如右表为固定资产与累计折旧分类汇总表的部分格式与内容。
    (2)建立各表格审计工作底稿的基本模本。对每个设计好的表格,首先在Excel的工作表中填好其汉字表名、副标题、表尾、表栏头、表中固定文字和可通过计算得到的项目的计算公式,并把这些汉字和计算公式的单元格设置为写保护,以防在使用时被无意地破坏。完成上述工作后,各表格以易于识别的文件名分别存储,成为各表格的基本模本。
    例如,用Excel建立固定资产与累计折旧分类汇总表基本模本的步骤为:①在Excel的工作表中建立表的结构,并填好表名、表栏头和固定项目等;②在工作表中填列可通过计算得到的项目的计算公式,此例中有关计算公式为:E5单元格=B5+C5-D5,K5单元格=H5+I5-J5,B10单元格=SUM(B5∶B9);③相同关系的计算公式可通过复制得到,此例中可把E5的公式复制到E6∶E9,K5的公式复制到K6∶K9,B10的公式复制到C10∶K10;④表格与计算公式设置好后,把有汉字和计算公式的单元格设置为写保护(把不需要的区域设置为不锁定,然后设置全表格为写保护);⑤把已建好的表格模本以便于记忆的文件名(例如GDZCSJ.XLS)存盘,也可以把各表格模本存放在同一个文件的不同工作表中,各工作表以便于记忆的名字命名,以备审计时使用。
    (3)审计时调用并完成相应的审计工作底稿。上述工作做好后,只要打开相应的模本文件,在需要输入数据的单元格内填入被审计单位相应的数据即可完成表格的编制,从而得到需要的审计工作底稿。
    2、利用Excel编制试算工作底稿与调整后的会计报表 。审计人员在完成了各项审计工作后,要根据发现的问题及其重要程度确定要调整的项目,并作出调整分录。一般审计人员要先编制试算工作底稿,然后根据试算工作底稿编制调整后的会计报表。这项工作在报表金额较大、调整项目较多的情况下,要做很多枯燥而又必须保证计算正确的填列工作。如果利用Excel,就可以辅助审计人员大大加快编表速度,而且可以提高计算的准确性。利用Excel编制试算工作底稿与调整后的会计报表的具体方法如下:
    (1)建立试算工作底稿和各报表的基本模本。每个表格先填列好表头、表尾、固定项目和可通过计算得到的项目的计算公式,并把它们设置为写保护,要输入的单元格暂时留空。试算工作底稿各项目按各报表项目依次排列,调整前、调整数和调整后均包括借方和贷方两栏,调整后数据根据调整前数据和调整数计算得到。因为经调整的会计报表是根据试算工作底稿编制的,因此可利用Excel的链接功能把试算工作底稿各项目调整后数据链接到会计报表的相应项目而无需人工输入。
    (2)利用模本完成试算工作底稿和各经审会计报表的编制。基本模本建好后可反复使用,每次要编制某被审计单位的试算工作底稿和经审会计报表时,只要打开模本文件,在试算工作底稿中输入调整前与调整数,通过计算公式得到的数据会自动填列到表中。因为表中计算关系已确定且输入差错能相互抵销的几率很小,只要检查试算工作底稿的调整前、调整数和调整后借贷两方合计数是否相等,报表应有的勾稽关系是否满足,马上就能发现输入的错误。由于报表与试算工作底稿建立了数据链接的关系,一旦试算工作底稿编制完成,报表的编制即自动完成。这有效地保证了试算工作底稿与经审会计报表数据的一致性。
    3、利用Excel编制集团公司的经审合并报表。对集团公司进行审计时,审计人员的一项重要而繁琐的工作就是编制经审合并报表。其主要工作是:①汇总各公司相应的会计报表;②对需要互相抵销的项目逐一编制抵销分录进行调整;③根据调整结果编制经审合并报表。
    利用Excel辅助编制经审合并报表的工作步骤为:①分别建立合并工作底稿和经审合并报表的基本模本;②利用模本完成合并工作底稿和经审合并报表的编制。
    由于合并工作底稿中母子公司报表汇总数要由各公司的经审会计报表数汇总得到,所以,编制合并工作底稿时,可先利用Excel的“合并计算”功能,将各公司审计后编制的试算工作底稿汇总,生成汇总试算工作底稿。然后,打开合并工作底稿和经审合并报表模本文件,把汇总试算工作底稿中调整后数据复制到合并工作底稿母子公司报表汇总栏。接着,把各合并抵销分录汇总后输入到合并工作底稿合并调整栏,计算机自动计算并完成经审合并报表的编制。
        4、利用Excel进行分析性复核。分析性复核可以帮助审计人员发现异常变动项目,确定审计重点,以便采取适当的审计方法以提高审计效率。利用Excel不仅能减轻审计人员大量的抄写和计算工作,还能使审计工作更准确、更直观。其工作步骤为:①根据要求设计相应的分析性复核表格,按上述方法建立其基本模本;②打开相应的模本文件,根据被审计单位情况输入有关数据后自动完成分析性复核表格的编制。
    在审计实践中,经常会遇到一些很不规范的账务处理,这增加了审计的难度和风险。如一些单位由于效益不好,应收账款长期不作清理。要快速清理应收账款,可以用“账龄分析模板”。该模板是在Excel的工作簿里选择一个按标准的会计平衡式账页设计的工作表,取名为“平衡账”,它分借方、贷方、余额三栏。可按客户的名称,将其应收账款借方发生额输入到借方,收回账款的金额输入到贷方。根据实际情况,可以变为一借一贷,一借多贷,一贷多借,及时得到欠款余额。但应注意的是,一定要正确输入发货和收到货款的时间。在同一工作簿的另一名为“账龄分析”的工作表中建立账龄分析模本,利用电子表格的引用功能,将来源于会计平衡式账页的余额数据,根据发货和货款回笼日期,将不同的客户代入计算,得出账龄分析情况,然后输出结果。它既可以得出企业总的账龄分析结果,又可以得出某一个客户的分析结果。
    在审计涉及多年的手工账时,通常的报表只有当年和上年同期数的对比,而不能将审计期的多年报表总括反映。这时利用软件建立的“多年报表分析模板”就派上了用场,它不仅可以将若干报表实际数对比,而且可以根据委托人的需要,将多年预算数加进来分析,还能根据账簿,增加报表尚没有明细列出的分析项目,并制成趋势图,使企业的发展状况一目了然。
    5、审计银行存款未达款项的应用
    步骤一:建立工作簿及工作表。在Excel中新建一个工作簿,假定取名为“DZ.xls”。双击工作表标签中的Sheet1,将其重命名为“YH”,保存单位银行存款日记账数据;将Sheet2工作表重命名为“DW”,保存银行对账单数据。
    步骤二:输入单位银行存款日记账数据。进入YH工作表,分别在A1到H1单元格中输入“核对单据号”、“凭证日期”、“凭证编号”、“摘要”、“借方发生额”、“贷方发生额”“、”余额“、”核对符号“8个字段。将单位银行存款日记账的内容输入到工作表YH中。
    表1中假定上期无未达账项,如有可将上期的未达账项视为本期发生,一并输入到当期的发生额中,以把双方的期初余额调整一致;在其他单元格中输入当期的相应内容。
    步骤三:计算日记账余额。在G3单元中,输入公式“=G2+E3-F3”,回车后,在G3单元格中计算出发生的第一笔业务后的余额,然后用填充柄向下拖动进行填充,可计算出每笔业务发生后的余额及期末余额。
    步骤四:输入银行对账单数据。进入DW工作表,输入期初余额,余额在贷方,银行记账与单位记账方向相反,其他单元格填上本期银行对账单数据;在F3单元格中输入公式“=F2-D3+E3”,并用填充柄拖动填充计算出其他业务发生后的余额。
    步骤五:定义数据清单。进入YH工作表,选定工作表当期发生额的行,选择菜单“插入”→“名称”中的“定义”命令,将工作表选定的单元格定义为“YHSJ”。同样,将DW工作表中的当期发生额数据定义为“DWSJ”。
    步骤六:单位银行日记账自动与银行对账单核对。在YH工作表H3单元格中输入公式“=IF(AND(VLOOKUP(A3.DZ.xls!DWSJ,5,FALSE)=E3,VLOOKUP(A3,DZ.xls!DWSJ,4,FALSE)=F3),”T“,”N“)”(注意输入公式时要使用半角方式);将H3单元格的内容用填充柄向下填充至当期的最后一笔发生额为止。
    在H3单元格中,我们利用了Excel的查找函数“VLOOKUP”、联接函数“AND”、逻辑判断函数“IF”3个函数的嵌套公式,该公式表明,用该“YH工作表”的A3单元格特征代码的“核对单据号”到被定义为“DWSJ”的“DW工作表”A列中查找是否存在该特征号码,如不存在,则在“核对符号”栏表示为出错符号“#N/A”;如存在这一特征代码,则用“AND”函数在“DW工作表”中寻找该特征代码行的第五列即“ 贷方发生额”的金额,与“YH工作表”的“E3”单元格即“借方发生额”比较,以及“DW工作表”中此行的第四列“借方发生额”与“YH工作表”的“F3”单元格“贷方发生额”比较,如两个金额不相等,则在“核对单据号”栏出现“N”符号,如金额相等,则在“核对单据号”栏出现“T”符号。
    步骤七:银行对账单自动与单位银行日记账核对。在DW工作表G3单元格中输入公式“=IF(AND(VLOOKUP(A3,DZ.xls!YHSJ,5,FALSE)=E3,VLOOKUP(A3,DZ.xls!YHSJ,4,FALSE)=D3),”T“,”N“)”;将G3单元格的内容用填充柄向下填充至当期的最后一笔发生额为止。
    经过上述操作,所有核对单据号相符、借贷方发生额一致的业务在“核对符号”栏目均注明“T”符号;借、贷发生额不相等的将会注明“N”符号,一般系一方或双方的记账错误引起的;而标明了“#N/A”符号的则为无对应的“核对单据号”,为未达账项。
    步骤八:列出记账错误和确认未达账项。在YH工作表中,单击工作表的“全选”按钮,选择“数据”→“筛选”→“自动筛选”。此时,数据清单顶端的字段名变成了下拉式列表,选择“N”项,即可得到企业和银行记账不一致的数据记录;选择“#N/A”,即可得到所有单位已记账而银行未记账的未达账项全部明细记录。(祁莉)



【关闭】    【打印】