使用Excel PowerQuery进行数据整理和查询分析的方法和步骤

在进行大数据审计时，审计人员第一步就是将采集到的各种数据源进行整合，便于后续分析，但在进行数据整理时经常会出现许多困难：一是数据源分散，比如采集的有数据库文件、文本文件、PDF文件等，整合成一种格式比较困难，对计算机技术要求较高；二是数据内容不规范，调整成适合关系数据库的模式耗时耗力；三是数据容量大，当电脑配置不高时，加载缓慢，容易卡顿；四是重复操作频繁，当同样格式的数据较多时，经常要做重复工作。

Excel自带的免费插件PowerQuery可很好的解决上述问题，它是用于数据获取和预处理的强大工具。它的优势：一是可连接上百种数据源，所有数据源可在同一可视化界面下操作；二是操作简单，无需使用SQL语句或编程也能进行数据处理和查询操作；三是支持大批量和大容量数据处理，运行速度快；四是记录所有操作，当数据源发生变化，只需刷新即可。非专业技术人员通过拖拽、点击即可完成数据处理。

具体操作方法和步骤举例演示如下：

一是审计数据整理

以林业专项资金审计采集到的某市A县2020年至2023年生态护林员补贴人员名单数据为例，用PowerQuery可视化操作进行数据表整理，生成符合关系查询模式的内容。

数据表《A县2020-2023护林员享受补贴人员名单》含有大量的空列和合并单元格，不符合关系查询结构。鉴于后续还要处理其它县（市、区）的相关数据表，所以选择数据源时以“文件夹”模式导入，可有效避免重复操作：

进入PowerQuery编辑器，点击「新建源」 → 「文件」 → 「文件夹」，选择“\某市2020-2023护林员享受补贴人员名单”文件夹，此文件夹内目前只有一个表，即《A县2020-2023护林员享受补贴人员名单》数据表，点击「合并并转换数据」，进行数据整理。

操作步骤：删除前两列 → 选中“区县”至“工作区域”前四列，点击「转换」 → 「向下填充」覆盖null值，然后删除前两行； 点击「将第一行用作标题」，下一步将所有的“金额”列修改列名为对应的年份数值，删除空列；然后选中所有的“年份”列，点击「逆透视列」整理完成。

后续可直接将其他县林业局发过来的数据表拷贝至“\某市2020-2023护林员享受补贴人员名单”文件夹内，然后在PowerQuery编辑器内点击「主页」 → 「刷新预览」，就可自动重复操作，完成所有数据表的整理：

如上图所示，PowerQuery在数据整理方面高效快捷，遇到数据源发生改变时，点击“刷新”即可，减少重复操作。

二是审计数据查询

以医院审计中“分解住院”审计事项为例，用PowerQuery可视化操作进行查询分析，生成疑点结果。

分解住院指的是医疗机构将原本可以一次完成的住院治疗，人为拆分成多次住院，从而得到增加收入，规避医保限额等“好处”。对患者而言，会增加经济负担、延误连续治疗；对医疗行业而言，破坏了诊疗规范和医保秩序。

操作思路：将住院费用表进行自关联，筛选出同一患者入院时间和出院时间相差3天以内的信息。

操作步骤：选择数据源「SQL Server数据库」→《XX医院2022年住院收费数据库》 → 《住院收费表》：

1、根据“门诊住院号”标识一个人单次住院的唯一性，便可按每一个人单次住院进行分组，提取其单次住院的入院日期和出院日期：

点击「分组依据」 → 「高级」，按“身份证号”、“住院号”分组，→ 新列名：“入院时间”；操作：“最小值”；柱：“费用时间”，点击「添加聚合」→ 新列名：“出院时间”；操作：“最大值”；柱：“费用时间”。

2、按照每个人身份证号进行自我关联，将每个人不同的住院信息连接到一起：

选中《住院收费表》，点击「主页」 → 「合并查询」，因为要自连接，所以需要合并的表依然选择《住院收费表》(当前)，分别选中两个表的“身份证号”字段，点击「联结种类」 → 「内部(仅限匹配行)」。合并成功后，点击新表头「展开分组的行」。

3、剔除住院号相同的信息：

点击「添加列」 → 「条件列」→ 列名：“门诊住院号”；运算符：“等于”；值(列)：“分组的行.门诊住院号”；输出：“相同住院号”；ELSE：“不同住院号”。生成新的条件列后，「筛选」 “不同住院号”。

4、将后一次住院的入院日期减去前一次住院的出院日期：

按住Ctrl键，依次选中列“分组的行.入院时间”和“出院时间”，点击「添加列」 → 「日期」 → 「减去天数」，得到两次住院间隔天数。

5、筛选出结果为0、1、2天的信息（0代表当天出院又当天入院、1代表第二天重新入院、2代表隔天后入院）：

在新生成的“减法”结果列（相邻住院间隔天数）中，筛选「数字筛选器」 → 「介于」，选择大于等于0，小于等于2的范围。

最终得到同一病人两次住院间隔小于3天的分解住院疑点，然后采取进一步核实措施。

Power Query 通过低门槛自动化处理，使审计人员从数据整理中和复杂SQL代码编写中解放，使其可以将更多的精力聚焦于风险判断和结论推导上。尤其在大数据方面，该工具优势远超传统手动处理或单一数据库软件，是大数据审计场景下的核心提效工具。

（临汾市审计局 肖鉴光）