电子表格培训

1、电子表格在审计中的应用授课教师：李娅QQ:1103279210Phonemail:rzq_数据审计思路一：勾稽关系的复核：将具有内在联系的数据，按照勾稽关系进行相互核对，达到验证被审计单位业务处理是否正确，有无人为违规调整等目的。ABNXY?是否相等财务数据财务数据业务数据业务数据文本文本TXT电子表格电子表格XLSX数据库数据数据库数据。医保审计数据案例验证思路：判断所有医药销售记录是否遵循了药品数量、药品单价与药费之间应有的勾稽关系。实践：借款凭证表.xlsx：请检查借款凭证表中是否存在借款金额不等于本凭证累计收回贷款、本凭证贷款余额、核销金额之和的记录。医保药品

2、审计.xlsx文件中的医保中心销售记录工作表：请检查应收药费、个人交费和医保比例之间是否符合应有的勾稽关系，并使用WPS电子表格工具进行验证。 身份证号码数据案例 根据数据中的身份证号码，验证出生日期和性别，查看是否有身份证号码与出生日期和性别不符的情况 身份证号码构成原则：（见下表） 该验证过程用到电子表格的公式函数功能，可以在参考书或网络上学习如下函数的用法： LEN() / LENB() MID() / MIDB() LEFT() / LEFTB() RIGHT() / RIGHTB() IF() TRIM() 实践： 身份证号码审计.xlsx中提供的数据，根据讲解案例实现的思路，试填充

3、该电子表格 根据身份证号码中得到的出生日期和性别，查看是否有身份证与出生日期和性别不符的情况数据审计思路二： 合规性验证：按照方针政策、法律、法规、内部控制等，对被审计单位的数据进行检查，达到确定业务处理是否合法、合规的目的。法律法律法规法规政策政策条款条款。建立模型建立模型工具描述工具描述验证验证车购税=计税价格*10%B1单元格中输入：=A1*0.1车量购置税案例 根据交通部规定，国产车车辆购置税应按发票价的10%计征；后来为杜绝因一辆车开具多张票据而造成少征车购税的现象，交通部又颁布了车购税最低征收额标准，明确只要发票价的10%小于最低征收额的，就按最低征收额征收车购税。审计思路： 根据

4、车购税的条款，建立数据模型应征金额MAX(发票价0.1，最低征收额 )违规金额ABS（应征金额实际征收额 ） 使用电子表格对模型进行计算，得到应收车购税 将应收车购税与实际征收车购税相比对，发现是否有违规征收的情况 若存在不符情况，统计出不合规的征收费（少征、多征两种情况） 实践： 车辆购置税.xlsx：按照交通部规定，对车购税是否按照标准征收进行审查，若有不合规的征收费用，统计应征收的费用与实际征收费用的差额。 提示：该案例中试用如下函数MAX()、ABS()，SUMIF()、SUBTOTAL().函数功能请参考网络或者教材附：SUBTOTAL()第一个参数说明101 AVERAGE（平均值

5、）102 COUNT （数值计数）103 COUNTA （包涵文本计数）104 MAX （最大值）105 MIN （最小值）106 PRODUCT (所有数的积) 107 STDEV （估算样本的标准偏差，反映了偏离相对于平均值的离散程度） 108 STDEVP （返回整个样本总体的标准偏差。它反映了样本总体相对于平均值的离散程度） 109 SUM （求和） 110 VAR （估算基于给定样本的方差） 个人所得税案例个人所得税现行方法应纳个人所得税税额=应纳税所得额适用税率-速算扣除数扣除标准3500元/月（工资、薪金所得适用）个税计算7级标准（请见下表） 审计思路 按照对个人所得税的现行方法

6、，对其进行模型的建立 实践 对个人所得税.xlsx的计税工资，根据模型，试用IF()函数的嵌套实现个人所得税的计算 可以有多种方法实现，个人所得税(x).xlsx工作表中，提供了另外两种实现方法，根据学员各人的情况，选择掌握 实践 公积金缴存案例 审计数据：公积金缴存案例.xlsx文件中的公积金缴存表 审计思路：根据公积金缴存规定，建立公积缴存模型注意考虑到各种违规情况 该案例数据中，公积金的缴存比例最高12%，当地的平均工资为1400，要求：公积金缴存不能高于当地工资的3倍，试用WPS电子表格实现违规数据的查找和统计数据审计思路三： 审计数据关联性的验证：在不同的数据系统中，相关数据衔接是否

7、严密，逻辑关系是否一致，避免主观认为计算机处理不会存在问题 个人住房贷款案例 根据中国人民银行个人住房贷款管理办法的要求，个人住房公积金贷款放款前必须办理抵押登记手续。在审计时，审计人员从房产中心取得了住房公积金个人贷款房产抵押表，这张表中的抵押人就是办理了抵押登记手续的贷款人；审计人员还从住房公积金管理分中心取得了个人贷款信息表，这张表中的人员就是所有取得贷款的人。由此可知，将两边数据进行比对，发现问题线索。 审计思路： 个人贷款信息中的贷款人应该是房产抵押表中的抵押人 验证两张表中信息的关联性，若不存在，说明两表数据存在不一致性 实践 根据个人住房贷款审计.xlsx提供数据，验证个人贷款信

8、息中的贷款人是否出现在房产抵押表的抵押人中 提示：该案例中试用如下函数VLOOKUP()函数功能请参考网络或者教材 实践 专业系统数据与财务软件数据核对案例 审计数据：财务软件引出与专业软件引出核对.xlsx文件 审计背景：随着信息化的发展，被审计单位往往都具有各自行业的业务软件，业务软件与财务软件往往采用同时记账的方式，对于两个系统导出数据的核对，也可以作为审计业务中的一个方向。 财务软件引出与专业软件引出核对.xlsx文件中包含ERP账面数和财务账面数两个工作表，试对两个引出的数据表进行核对，查找是否存在差异。如果存在差异，还要查出是哪个编码的商品出现了差异。 练习 对工程项目统计.xls

9、x提供的数据，核实各类型项目的支出情况 VLOOKUP()函数的应用 练习 个人所得税案例 试用VLOOKUP()函数实现个人所得税的计算数据审计思路四： 分析性复核：根据审计人员的经验，对数据的某些特征进行统计分析，将可能有问题的分析结果作为审计线索进行验证 销售收入真实性审计案例 商品销售明细表中显示了商品销售相关的业务数据，为了与财务数据的结果相比对，通常要对其进行各种统计。 根据需求，对以下业务数据进行统计 1）对所有商品的销售金额进行统计 2）各类商品的销售金额进行统计 3）查看单位中通购买商品糖果的金额 审计思路 根据审计目的的需求，统计出相应业务数据 与相关财务数据相比对，判断是

10、否有不合规的数据修改 实践 对销售收入真实性审计.xlsx中商品销售明细表中的数据进行如下三种数据的统计 1）对所有商品的销售金额进行统计 2）各类商品的销售金额进行统计 3）查看单位中通购买商品糖果的金额 提示：该案例中试用如下函数SUM()，SUMIF()函数功能请参考网络或者教材练习 审计人员需要了解某单位“现金”科目总体情况，试用电子表格展现各月“现金”科目的会计月份、科目编码、科目名称、借方发生额、贷方发生额等字段 打开某企业凭证表.xlsx a）统计各月份现金科目的贷方发生额 b）使用图表展示各月份的贷方发生额，将不正常的月份确定为审计疑点 c）检索出贷方发生额最高的月份的现金科目

11、凭证信息 d）检索结果按贷方金额进行降序排列后，第一条记录即是当月现金支出最多的一笔业务，将此笔业务作为疑点保存。 图表展现参考下图 预算经费超支审计案例对预算经费超支审计数据进行各种汇总，应用到电子表格的排序、分类汇总、图表、数据透视表等数据分析方法和形式查看显示，发现审计疑点。 审计过程 打开预算经费超支审计.xlsx文件中的预算经费超支明细表工作表，计算超支超支额和超支率。 将预算经费支出明细表工作表复制一份，命名为“各部门汇总统计”，在“各部门汇总统计”工作表中统计各部门的实际支出数、年初预算数、超支额、超支率。 将汇总结果拷贝到新的工作表“超支部门情况表”中，自动筛选出超支率0的数据

12、，并复制成两张新的工作表，分别为“超支部门的超支率图表”和“超支较严重部门统计”。 在“超支部门的超支率图表”工作表中，创建超支率图表。 在“超支较严重部门统计”工作表中，利用高级筛选统计超支较严重部门（超支额在100000以上，或超支率在各超支部门超支率的平均值以上）数据采集和清理 数据采集 文本数据的采集 a)固定长度 b)分隔符 c)长文本字符数据 d)日期型的数据 数据库数据的采集 a)链接 b)部分数据的采集 实践练习 将贷款利率定长.txt文本文件中的所有数据采集到WPS电子表格中。 将长文本数据.txt文本文件中的所有数据采集到WPS电子表格中。 将销售数据库.accdb数据库文件的销售记录表中东南部地区销量在500以上的数据，采集到WPS电子表格中，且结果依据销量升序排序。 数据清理 1.导入数据过程的清理（日期型） 2.数据格式的转换 a)长数字的显示、设置文本型代码、小数点对齐（单元格自定义格式） b)将公式的值变成实际数值（选择性粘贴