巧用excel工具完成部门预算执行汇总比对工作

随着大数据越来越多地介入审计分析中，大量的审计数据形成各种的数据库，但是如何在浩瀚的数据中形成疑点模型，并将疑点模型准确地应用到实际审计中，一直是审计人员致力探索分析的途径。

江汉区全区职能部门共计190家，本次审计通过大数据分析，建立疑点审计模型12个，在利用SQL数据库取数建模分析后，将这些数据疑点快速整理、关联，形成有效的审计数据分析，单用SQL语句可能很难实现，而利用传统的excel数据整理分析，可能会取得更好的分析效果。以下以江汉区审计局数据建模分析后，疑点数据核实前，数据的整理与关联为例，说明利用excel工具达成节约时间成本的方法：

一、基础数据处理技巧

1、疑点表单表整理。观察整理审计模型疑点表，由于疑点表数据格式基本不相同，在汇总时按审计需要选择重点的字段进行整理，一般重点字段包括：“预算单位代码”、“预算单位名称”、“摘要”、“辅助项目”、“金额”等字段，在表格处理时一般需要添加“疑点模型名称”进行标记，并在理解每个单表的内容后初步选择、排序、标准化，以避免后面快速提取后无法进行合并分析操作。

2、VB快速提取合并分项表。因为本次模型只有12张表格，所以可以进行手动粘贴，但是有时候提取数据表格可能涉及上百张时，直接张贴肯定不行，可以采取VB宏运行程序，在excel里面有简单的VB编程，启用编程后，按照以下的代码可以自动进行提取：

3、汇总表二次标准化整理。在提取数据后表格后，通常以“疑点模型名称”为排序，进行整理。在这个过程中，由于二次标准化整理处理中，容易出现各种不可预期的错误，最好在表格第一列添加字段“序号”，以便在后期处理时出现错误可以通过“序号”数据重排序等方式进行还原查找源数据。在这个阶段需要保证待处理数据无合并单元格、数据字段标准化、无空格等，而当遇见由于取消合并单元格造成的空格时，快捷键“Ctrl+G”定位空置，按住“Ctrl”后先按“↑”再按“=”，可以进行快速填充，在公式操作之后需进行选择性粘贴，取消格式。一般来说，在进行数据表格整理时，只要进行公式类运算后，都需要将运算完毕的格式取消，以避免在后续操作过程中，因为顺序等变动造成不可预期的数据错误变更。

以上三步是处理基础数据的基本表格操作，是进行数据分析的基础。数据标准化、可对比性是表格数据处理的准备工作，在表格标准化处理后，进行汇总比对分析。

1. 审计分析数据处理技巧

1、数据透视分析技巧的应用。在数据处理分析中，“比对数据唯一性”是很多后续分析操作的基本要求，采用去重方式容易丢失数据，这时采用数据透视表就显得很重要了。

（1）三维透视表的应用。三维透视表的应用多是终端应用，通常是需要进行两个关键点数据来定位，其主要目的是在于直观的观察数据分析的结果。如文章开头提出的，我们需要直观的了解哪些单位有哪些疑点，疑点分布如何，来进行系统分工时，采用如下图右边标记的透视排列的方式，就可以得到左边的三维透视表格：

（2）二维透视表应用。二维透视表往往不会是数据分析的终端，二维透视表的主要目的在于完成“比对数据唯一性”，为后续数据分析打基础，如下文介绍的“vlookup”公式就需要在唯一性数据模式下进行操作，所以选择合适的透视表摆放很重要，当需要处理的数据属性比较多的时候，一般选择的以下表格式摆放的方式会更容易操作：

2、excel公式分析技巧的应用

（1）匹配比对公式“VLOOKUP”的应用。“VLOOKUP”公式是excel数据分析中，使用最多的公式之一，在“表格数据唯一化”后，就可以使用“VLOOKUP”公式进行对应数据的匹配工作，熟练使用“VLOOKUP”公式，可以增加数据匹配的精准度,在使用该函数时，实现精准匹配，就要求查找值与数列表的比对值格式及名称完全一致。进行匹配后，如果结果表中出现过多的“#N/A”标记，往往就需要观察是否存在格式不一致的情况，比防说目标数据中文本前有“空格”等原因，快速取消“空格”的方式可以用快捷“替换”功能。

（2）excel函数的应用。在数据处理中，“if”函数、“left”“right”数据字段提取函数、sumif函数等均可能在分析中出现，每一个函数的巧妙应用，都可以对数据的分析起到简化的作用，基于理解数据的基础上，采用数据的相关应用，是excel函数的基本要求。

三、数据的审计运用技巧

1、excel数据快速拆分应用。审计分析后的数据往往很多，为了信息的安全，不可能全部将数据发放出去，这时候，在应用数据时，直接一一切分，涉及内容多时，容易遗漏或者多粘贴，采用VB分表也是一个方法。

2、excel邮件合并取证应用。

在基于大数据分析下，熟练地使用excel进行数据的末端处理，是非常有必要的，尤其是按照取证语言逻辑进行表格的设计，设计符合要求的取证模板，再采用excel邮件合并方式，将大量一样的取证进行自动生成，从而大量节省重复性工作，同时也减少人为比对的差异率。

在大数据审计工作中，往往思维的建立更重要，而表格化的形成恰恰就是逻辑思维的直观表示，同时通过数表的陈列分析，也可以验证逻辑建模的正确与否。而Excel的处理因为不需要过多的编程技巧，更直观地显现结果等优点，可以更容易在数据分析的后端取得成效。（江汉区审计局 彭晓 孟歆)

巧用excel工具完成部门预算执行汇总比对工作

随着大数据越来越多地介入审计分析中，大量的审计数据形成各种的数据库，但是如何在浩瀚的数据中形成疑点模型，并将疑点模型准确地应用到实际审计中，一直是审计人员致力探索分析的途径。

江汉区全区职能部门共计190家，本次审计通过大数据分析，建立疑点审计模型12个，在利用SQL数据库取数建模分析后，将这些数据疑点快速整理、关联，形成有效的审计数据分析，单用SQL语句可能很难实现，而利用传统的excel数据整理分析，可能会取得更好的分析效果。以下以江汉区审计局数据建模分析后，疑点数据核实前，数据的整理与关联为例，说明利用excel工具达成节约时间成本的方法：

一、基础数据处理技巧

1、疑点表单表整理。观察整理审计模型疑点表，由于疑点表数据格式基本不相同，在汇总时按审计需要选择重点的字段进行整理，一般重点字段包括：“预算单位代码”、“预算单位名称”、“摘要”、“辅助项目”、“金额”等字段，在表格处理时一般需要添加“疑点模型名称”进行标记，并在理解每个单表的内容后初步选择、排序、标准化，以避免后面快速提取后无法进行合并分析操作。

2、VB快速提取合并分项表。因为本次模型只有12张表格，所以可以进行手动粘贴，但是有时候提取数据表格可能涉及上百张时，直接张贴肯定不行，可以采取VB宏运行程序，在excel里面有简单的VB编程，启用编程后，按照以下的代码可以自动进行提取：

3、汇总表二次标准化整理。在提取数据后表格后，通常以“疑点模型名称”为排序，进行整理。在这个过程中，由于二次标准化整理处理中，容易出现各种不可预期的错误，最好在表格第一列添加字段“序号”，以便在后期处理时出现错误可以通过“序号”数据重排序等方式进行还原查找源数据。在这个阶段需要保证待处理数据无合并单元格、数据字段标准化、无空格等，而当遇见由于取消合并单元格造成的空格时，快捷键“Ctrl+G”定位空置，按住“Ctrl”后先按“↑”再按“=”，可以进行快速填充，在公式操作之后需进行选择性粘贴，取消格式。一般来说，在进行数据表格整理时，只要进行公式类运算后，都需要将运算完毕的格式取消，以避免在后续操作过程中，因为顺序等变动造成不可预期的数据错误变更。

以上三步是处理基础数据的基本表格操作，是进行数据分析的基础。数据标准化、可对比性是表格数据处理的准备工作，在表格标准化处理后，进行汇总比对分析。

1. 审计分析数据处理技巧

1、数据透视分析技巧的应用。在数据处理分析中，“比对数据唯一性”是很多后续分析操作的基本要求，采用去重方式容易丢失数据，这时采用数据透视表就显得很重要了。

（1）三维透视表的应用。三维透视表的应用多是终端应用，通常是需要进行两个关键点数据来定位，其主要目的是在于直观的观察数据分析的结果。如文章开头提出的，我们需要直观的了解哪些单位有哪些疑点，疑点分布如何，来进行系统分工时，采用如下图右边标记的透视排列的方式，就可以得到左边的三维透视表格：

（2）二维透视表应用。二维透视表往往不会是数据分析的终端，二维透视表的主要目的在于完成“比对数据唯一性”，为后续数据分析打基础，如下文介绍的“vlookup”公式就需要在唯一性数据模式下进行操作，所以选择合适的透视表摆放很重要，当需要处理的数据属性比较多的时候，一般选择的以下表格式摆放的方式会更容易操作：

2、excel公式分析技巧的应用

（1）匹配比对公式“VLOOKUP”的应用。“VLOOKUP”公式是excel数据分析中，使用最多的公式之一，在“表格数据唯一化”后，就可以使用“VLOOKUP”公式进行对应数据的匹配工作，熟练使用“VLOOKUP”公式，可以增加数据匹配的精准度,在使用该函数时，实现精准匹配，就要求查找值与数列表的比对值格式及名称完全一致。进行匹配后，如果结果表中出现过多的“#N/A”标记，往往就需要观察是否存在格式不一致的情况，比防说目标数据中文本前有“空格”等原因，快速取消“空格”的方式可以用快捷“替换”功能。

（2）excel函数的应用。在数据处理中，“if”函数、“left”“right”数据字段提取函数、sumif函数等均可能在分析中出现，每一个函数的巧妙应用，都可以对数据的分析起到简化的作用，基于理解数据的基础上，采用数据的相关应用，是excel函数的基本要求。

三、数据的审计运用技巧

1、excel数据快速拆分应用。审计分析后的数据往往很多，为了信息的安全，不可能全部将数据发放出去，这时候，在应用数据时，直接一一切分，涉及内容多时，容易遗漏或者多粘贴，采用VB分表也是一个方法。

2、excel邮件合并取证应用。

在基于大数据分析下，熟练地使用excel进行数据的末端处理，是非常有必要的，尤其是按照取证语言逻辑进行表格的设计，设计符合要求的取证模板，再采用excel邮件合并方式，将大量一样的取证进行自动生成，从而大量节省重复性工作，同时也减少人为比对的差异率。

在大数据审计工作中，往往思维的建立更重要，而表格化的形成恰恰就是逻辑思维的直观表示，同时通过数表的陈列分析，也可以验证逻辑建模的正确与否。而Excel的处理因为不需要过多的编程技巧，更直观地显现结果等优点，可以更容易在数据分析的后端取得成效。（江汉区审计局 彭晓 孟歆)