数据透视表

数据透视表 玩转数据透视表 —— 一个运用Excel进行数据审计的例子 杨顺年 Microsoft Office Excel是组织和分析数据的理想工具之一。在AO系统部署以前～审计人员大多已经熟悉Excel的基本功能～审计署在设计AO时也充分考虑了AO系统与Excel分析结果的相互利用问题。在AO中几乎所有的数据分析结果都可以导出到Excel表中～同样也可以把Excel表导入AO系统。审计人员在工作中如果能够熟练运用AO和Excel～充分发挥两个软件在数据处理方面各自的优势～使它们起到相辅相成的作用～无疑将给审计工作带来极大的帮助。 本文是我用Excel的数据透视表功能对一个审计案例进行的模拟分析～希望能够通过这个案例开拓审计人员的思路。由于篇幅所限～本文并没有涉及数据透视表的高级功能以及Execl与AO系统的交互问题～留待今后继续讨论。 数据透视表,Pivot Table,是Excel软件极具特色也是其中功能最强大的数据分析工具。通过数据透视表～我们不必写入复杂的公式就可以使用向导创建一个交互式的表格来自动提取、组织和汇总数据。通过鼠标拖放把数据移来移去就可以从不同的视角观察、比较、分析数据～从而揭示数据包含的深层次信息。使用数据透视表的过程就如同转动万花筒一样～稍微转动即可看到不同的图案。这不禁让人想到～在运用计算机进行数据审计时～如果我们能够通过数据透视表把电子数据进行一番梳理～是不是可以更快地找到头绪～理出审计思路呢, 图一是一张商品进销存结转表～ 清单 安全隐患排查清单下载最新工程量清单计量规则下载程序清单下载家私清单下载送货清单下载 中包括入账日期、凭证号、发票号、单位代码、单位名称、类别、商品代码、数量、单价、金额等列～时间跨度为一年。在对某烟厂销售收入真实性的审计过程中～审计小组取得了这张表。 这是一张很‚平庸‛的数据清单。审计人员拿到这样一份清单远不如拿到‚某某统计表‛感觉愉快。清单中虽然只是记录了企业的进销存流水账～但却有上万行数据～且显得很杂乱～很少有人能够从如此纷乱冗长的数据中立刻找到审计方向。好在审计小组里都是很有经验的行家～经过讨论后决定用数据透视表对数据进行分析处理。在一番数据处理、 分析、推断与核实之后审计人员很快发现了数据清单中隐藏的问题。 1 图一 首先～审计人员在Excel表中创建了数据。您可以将现有的电子表格或数据库中的数据导入到新 Excel 工作表中来创建数据,图一是已经创建好数据的Excel表。为了下步能够运用数据透视表～创建数据表时要满足下列条件: ,(删除所有空行或空列。例如:用来分隔数据块的空行应该删除。 ,(删除所有自动小计。 ？(确保第一行包含各列的描述性标题。 ,(确保各列只包含一种类型的数据～例如～一列中全是文本～另一列中全是数值,标题例外,。 其次～审计人员启动‚数据透视表和数据透视图向导‛开始对数据进行分析。由于图一的数据来自数据库中～所以天然地满足了上述条件～可以直接用于创建数据透视表,其实图一的清单并非‚平庸‛～而是严格符合数据库设计规范的,。 ,(在 Excel表中从要根据其创建数据透视表的表格数据区内选择一个单元格。 ,(在‚数据‛菜单上～单击‚数据透视表和数据透视图‛启动‚数据透视表和数据透视图向导‛,如图二,。 ？(在‚数据透视表和数据透视图向导 - 3 步骤之 1‛的‚请指定待分析数据的数据源类型:‛下～单击‚Microsoft Office Excel 列表或数据库‛。 ,(在‚所需创建的报表类型:‛下～单击‚数据透视图,及数据透视表,‛。 ,(单击‚完成‛。 2 图二 步骤四中我们选择创建‚数据透视图,及数据透视表,‛是为了从图表上更直观地观察数据变化趋势～实际操作时～我们大多数情况只选择‚数据透视表‛。由于步骤一时～我们在表格数据区内选择了一个单元格～因此步骤五我们直接单击‚完成‛～省去了单击‚下一步‛选择数据区的麻烦。 完成上述操作后～Excel会在您的工作簿中插入一个新工作表,sheet1,～该新工作表包含报表布局区域,即‚数据透视表‛工具栏,和‚数据透视表字段列表‛,如图三,～同时也会插入一个新图表,chart1,。 图三 由于sheet1和chart1是联动的～所以我们只对sheet1进行操作。鼠标单击选中sheet1后进行如下操作: ,.将‚入账日期‛字段从‚数据透视表字段列表‛拖动到数据透视表的‚将行字段拖至此处‛区域,将‚数量‛字段拖动到数据透视表的‚请将数据项拖至此处‛区域,将‚类别‛字段拖动到数据透视表的‚请将页字段拖至此处‛区域,拖放示意如图四～结果如图五,。不要担心会将字段放错位置 ～因为任何时 3 候您都可以随意移动它们。如果对拖放结果不满意～你还可以把字段拖回‚数据透视表字段列表‛。 图四 图五 图六 2. 右键单击单元格‚计数项:数量‛～选择‚字段设置‛菜单,如图六,。在弹出的窗口中将‚汇总方式‛改为‚求和‛～点‚确定‛。 3. 点击‚类别‛下拉选择框～将类别设为‚2‛,如图七,。这里类别‚2‛表示产品销售～类别‚4‛表示上年结转等等。 4. 通过上述步骤我们已经生成了每日的销售汇总数据～但是按日汇总数据后数据仍然太多不便于观察～最好是按月汇总。右键点击单元格‚入账日期‛～选择菜单‚组及显示明细数据‛、‚组合‛,如图八,～在弹出窗口中选择‚步长‛值为‚月‛～然后点击确定,如图九,。图十是我们得到的结果～其中左边是sheet1中显示的数据透视表～右边是chart1中显示的数据透视图。 4 图七 图八 图九 图十 通过上述步骤～我们生成了按月汇总的产品销售数据表和柱状图。从柱状图中可以看到～企业1，10月份销售数量都保持在3至4万箱左右～11月份达6万箱～而到了12月份销售数量异常增长为7万多箱～显然应将12月份的销售情况作为审计的重点内容。 继续通过数据透视表进行分析～ 1. 取消按月汇总。方法是右键点击单元格‚入账日期‛～选择菜单‚组及显示明细数据‛、‚取消组合‛。 2. 单击单元格‚入账日期‛的下拉按钮,如图十一,～把‚全部显示‛前面的勾去掉。拖动滚动条～勾选所有12月的日期,如图十二,～单击‚确定‛。 3. 将‚单位名称‛字段从‚数据透视表字段列表‛拖动到数据透视表的‚将行字段拖至此处‛区域～并放在‚入账日期‛的右边,将‚商品代码‛字段拖动到数据透视表的‚将列字段拖至此处‛区域。如图十三～本步骤生成了12月份各单位日购货情况明细表～行为单位明细～列为商品明细。 5 图十一 图十二 图十三 通过观察发现:‚入账日期‛为1998年12月29日～代码为‚011220‛～单位购买了代码为‚03‛的商品～数量为4700箱。通过检查该笔业务的原始附件发现～一是该笔业务没有相应的购货 合同 劳动合同范本免费下载装修合同范本免费下载租赁合同免费下载房屋买卖合同下载劳务合同范本下载 ～二是提货单显示该笔销售的4700箱产品竟在一天内全部由购货方用卡车提货运走。审计人员认为这不符合常规～因为按照该产品的体积和重量～一辆卡车一次才能装几十箱产品～也就是这4700箱产品要一天提完货的话需要几百辆卡车～显然是不太可能的。至此被审计单位不得不承认～为了完成利税指标～制造已经销售的假象～而将该批4700箱产品在企业外另租仓库保管～形成账外资产。通过实地查看仓库～共统计出被审计单位账外资产6400多万元。 在本例中我们看到～审计人员并没有施展什么绝招就查出了问题。审计过程中审计人员所做的不过是对数据进行分类、汇总以及反复尝试。虽然简单但却有效的办法使得数据就如同听到了号令～从一群乌合之众集合成了整齐的队列～数6 字间的关系便一目了然了。通过反复尝试～审计人员对问题的理解不断加深～数据包含的深层含义便慢慢浮现出来。当然～分类也是一件有技巧的工作～怎么分类以及用什么关键字进行分类以往主要是靠经验。而象本例这样一份涉及上万数据但看上去很普通的数据清单～恐怕依靠经验也是无能为力的。审计人员很自然地想到用数据透视表～是因为数据透视表具备操作直观方便～可以反复尝试的优良特性恰好可以帮助审计人员快速实现分类的目地。虽然反复运用Excel的‚分类汇总‛功能也能实现本例的审计目的。但是‚分类汇总‛毕竟是一种二维工具～不象数据透视表那样能够直观、立体地展示数据～且建立和取消‚分类汇总‛都比较繁琐～操作上明显不如数据透视表方便～不利于对数据进行反复地分类尝试。 最后～ 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf 一下使用数据透视表的窍门:在开始时检查源数据～问一问自己需要知道什么从而确定需要使用的字段～以及记住将字段拖到行区域以便按行显示等等。同时记住‚不要担心会以‘错误’的方式设置布局:‛因为创建数据透视表的过程就是将素材移来移去并查看效果的过程。将素材移动任意多次是很简单的事情。这一操作称为‚更改布局‛～通常只用几秒就可以完成～而且是整个过程不可缺少的一部分,这有点像要将一幅图在多面墙上试过之后才知道放在哪里效果会最好。 数据透视表的功能很多～一些高级功能大家可以查看Excel帮助文件或者访问微软公司的官方网站,office.microsoft.com,。本文使用的案例数据来源于审计署计算机审计中级培训班的教学案例～原案例是通过‚审计数据采集分析软件3.0‛进行操作的～AO系统部署后～通过AO系统也可以实现类似的审计过程～但是这两种方法都要用到SQL语言～对于缺乏数据库知识的审计人员来说～掌握起来有一定困难。本文用数据透视表对原案例进行了重写～更利于审计人员掌握其中的分析方法。 由于篇幅有限～本文只讨论了数据透视表的一些基本功能～目的在于向审计人员介绍数据透视表这个优秀的分析工具～希望能够帮助审计人员在审计工作中起到减少手工劳动、开拓审计思路的作用。 二〇〇八年三月三日 7