数据透视表在审计中的运用技巧

潘荣涛

[摘要]数据透视是一种快速汇总大量数据的交互式方法，在审计实务中，利用数据透视表可深入分析数据信息，揭示大数据背后的真相，为审计发现问题提供实证。

[关键词]数据透视    Excel    审计技巧    大数据

审计工作离不开信息系统，离不开大数据。各种数据展现在审计人员面前的都是一张张载有诸多信息的Excel表，精通Excel运用尤其是数据透视表的运用，会事半功倍。如Excel 2007版，可编辑数据量是2的15次方即32768行，当数据超过32000行时，可以把后面的行剪切到另一张Sheet中，这样透视功能就不会受到影响。而Excel 2010版数据系列中的数据点数目仅受内存限制，编辑功能大大增强。

一、判断唯一性指标是否重复

数据表中的唯一性指标，如身份证号、订单号、入库单号、时间序列号、系统自动生成序列号等，这些指标如果重复出现，说明实际业务可能存在异常。

（一）一年内同一人有无多次领取补贴费用

如果从几万个身份证号中逐一筛选、比较，工作量太大，容易出差错。判断同一人有无多领补贴，从Excel表中检索身份证号有无重复即可。如果用公式查找，假如有一万个身份证号，将身份证号从A列复制到B列，再在C列插入空白列，运用公式Countif（$B$2：$B$10001，A2），可以找出。由于行数多，运算量大，重复使用公式编辑，电脑常会出现卡顿、延迟状态。这时可以使用透视表，在Excel表中增加一辅助列，列内输入数值1，对应的含义是发生了1次，建立透视表，对身份证号对应的辅助列1进行求和。每个身份证如果只发生了1次，则求和值为1，否则就大于1，再对求和项筛选，重复出现的身份证号立马现身。

（二）同一订单号有无重复出现，相近物资有无串项

正常一个订单号对应一项物资。通过上述方法，透视订单号辅助值，求和项大于1的订单号肯定重复出现。

在透视表的列标签中选择规格、型号、价格相近的物资，进行筛选，记录在一张订单上的物资可能串项，或者行为者利用价格不同有意制造舞弊。

（三）入库单号有无重复

用上述方法对入库单号透视求和，如果大于1，说明物资重复入库。原因可能是系统错误，需要冲红抵消，如果不冲红，则虚增了物料库存。

入库单重复也可能是人为编制入库单，虚假入库，再根据入库单开具发票，领取物资款。

（四）同一时间点有无发生多笔业务

系统中的时间通常记录到秒。对于一般业务量的企业来讲，每一笔业务的发生时间对应某一秒，同一秒内发生多笔业务的概率较低。建立时间透视表，辅助值求和大于1的，则是同一时间点发生了多笔业务。原因可能是业务人员在同一时间点将已经准备好的表格传入系统，但这不符合业务逻辑，需要规避;也可能是操作人员利用系统缺陷或可乘之机，人为制造、调整对自己有利的业务事项，编造虚假业务，给公司造成损害。

（五）系统自动生成的序列号是否唯一

一般情况下，系统自动生成的序列号是唯一的。如果出现重复的序列号，应具体问题具体分析：如果仅是序列号重复，则是系统出现了差错;如果重复序列号记录的两项业务完全相同，则为业务发生一次，实际结算时系统中出现了两次。

二、对变量分类求和，分析变量背后的规律

不断变化的指标，比如销售订单，其对应的产品数量、价格和销售区域同时也在变化。同理，医疗对象在不断变化，适用的药物、价格、治疗期、疗效等也在变化。变化的消费人群对应变化的消费价格、数量、场所、人数等。

比如，要实现同一订单号内不同价格物资的分类求和，如果在Excel中求和，选择同一单号，求和的数据只有一条。而要实现同一订单号下不同价格的所有物资单号的求和，利用透视表求和列表功能就能实现。

建立订单、价格、产品的透视表，将价格、产品放在透视表的列标签栏，对辅助值求和，去掉列标签上的空白页，透视表显示的就是价格不同产品在同一订单上的记录。分类汇总的意义在于细分市场，分析同一订单提货不同产品的数量及其中蕴含的内在规律，进而分析消费习惯、物流方式、销售渠道。

针对医疗对象的不断变化，基于适用的药物、价格、治疗期、疗效等内容，建立医疗对象透视表，在列标签内输入药物、价格、治疗期、疗效等内容，分类求和，求和结果可以显示药物名称、价格、时间等对不同医疗对象的有效区间，得出统计结论，为医疗手段提供佐证。

针对变化的消费人群，基于消费价格、数量、场所、人数等内容，建立消费人群透视表，在列标签内输入价格、数量、场所、人数等内容，进行分类求和，结果反映的是不同人群的消费习惯，为制定销售政策提供依据。

三、分析市场窜货行为，评估销售政策及市场管控

同一产品在市场上出现窜货，是由于产品出厂价格差异，厂家对不同销售区域采取了不同的价格政策。一般是强势市场出厂价格高，弱势市场价格低，公司给予弱势市场经销商价格补偿，鼓励其拓展市场。由于市场存在价差及市场管控不力，造成出厂价低的产品流向强势市场而获利，扰乱市场秩序。

对弱势市场区域，分区域、客户、产品进行透视求和，对回退的包装物求和，乘以回退率，比较两者差异，就是流出市场的产品数量。对強势市场区域，分区域、客户、产品进行透视求和，对回退的包装物求和乘以回退率，比较两者差异，就是流入市场的产品数量。通过对比，可以评估公司的销售政策，找出解决问题的方法。

四、检索有奖销售兑奖结果是否存在舞弊

有奖销售主要分为零星小奖和大奖。

零星小奖主要将市场投放量与回退量进行比较（投放量=回退量+市场灭失量）。如果作为整体统计，肯定是投放量大于回退量，发现不了问题。可以将投放的区域充分细分，对每一个细分点透视求和，再作比较，查看有没有不合逻辑的地方。

对于大奖，一般有兑奖码，兑奖码设有兑奖期限，逾期不予兑付。检查兑奖码有没有重复项，核查是否存在重复兑奖或逾期兑付情况。

五、查找付款期限及二次转包，规范物资采购行为

一是查找付款期限的不同。采购物资付款一般分为票到付款，以及票到7天、10天、30天、45天付款多种情况。设置辅助列输入值1，建立供应商透视表，对供应商辅助列求和，即该供应商当年发生的业务笔数。在列标签内输入付款条件，不同付款条件下发生的笔数即跃然纸上，不同供应商付款期限不同，要追溯是否符合合同约定，以及招标、谈判过程是否有损公司利益;同一供应商存在的不同付款期限问题，背后可能是负责采购付款人员操纵付款期限，出现了内部舞弊行为。

二是查找供应商的二次转包问题。建立供应商透视表，在列标签内输入发货地点、发货单位、运输距离、运输费用等内容，寻找同一供应商存在不同发货地点问题。可能是供应商对货物进行了二次转包，公司可以寻求更经济的采购方式，维护公司利益。

以上列举了数据透视表在Excel中的运用技巧，以帮助审计人员在浩如烟海的数据中理清思路，迅速发现问题。当然，不同公司、不同系统、不同数据库内容差别很大，但透视表的逻辑是相通的，所以要不断学习业务，熟悉每项数据背后的逻辑和含义。只有业务清晰，数据透视才能有的放矢。

[作者单位：华润雪花啤酒（安徽）有限公司，邮政编码：230031，电子邮箱：1255407026@qq.com]