第四节 审计数据转换
本节主要介绍审计数据转换的内容和相关操作。
一、审计数据转换的内容
审计数据转换包括两方面的内容，一是将采集到的不同类型的数据转换为常用的数据格式，即数据表格式转化；二是明确地标识出每张表以及每个字段的经济含义，即数据表内容的转换。
（一）数据表格式转换
在我国政府审计的工作中，审计人员通常是根据采集到的不同格式的数据文件，分别运用多种方法将其装载到审计人员使用的SQL Server数据库中，并用SQL进行处理和分析。对于民间审计和内部审计的审计人员而言，SQL仍具有一定难度，更趋向应用审计软件或Excel进行分析。此部分操作与第三章数据采集操作类似，不再进行详细介绍。
（二）数据表内容转换
审计数据分析过程中，会遇到迥然相异的多个信息系统，例如不同财务软件有不同的数据存储规则：
1.同一字段在不同的应用中具有不同的数据类型。例如，字段“借贷方”在A应用中的类型是“字符型”，取值为“ Credit/Debit”，在B应用中的类型为“数值型”，取值为“0/1”，在C应用中的类型为“布尔型”，取值为“True/False”，在U8中没有该字段标志，因借贷金额是分别存放在2个字段中。
2.同一字段在不同的应用中具有不同的名字。例如，发生额在A应用中的字段是“balance”，在B应用中的名称为“bal”，而在C应用中又变成了“currbal”；
3.同名字段具有不同的含义。例如，字段“月折旧额”在A应用中表示为用直线折旧法提取的月折旧额，在B应用中表示为用加速折旧法提取的月折旧额；
4.同一信息在不同的应用中有不同的格式。例如，字段“日期”在A应用中的格式为“YYYY－MM－DD”，在B应用中的格式为“MM／DD／YYYY”，在C应用中的格式又为“DDMMYY”。
5.同一信息在不同的应用中有不同的表达方式。例如，对于借贷方发生额的记录，在A应用中设计为“借方发生额”与“贷方发生额”两个字段，在B应用中设计为“借贷方标志”与“借贷方发生额”两个字段。
二、审计数据转换的方法
常用的数据表内容转换方法有：
1.字段汉化。字段汉化就是明确标出每张表、每个字段的经济含义。通常，从被审计单位采集到的数据其字段名称不用汉语标注，而是采用系统内部的英文字段或英文简称表示，不便于审计人员对各字段经济含义的把握。
2. 数据类型转换。由于每次采集时的设备及条件所限，使得每次所采集的信息不一定满足分析的要求，需要对某些字段进行数据类型转换。数据转换的前提是类型相容，类型相容指的是一种类型数据的值域可以通过常用的转换函数映射到另一种类型的值域上，这种映射不会丢失数据的精确度。
3.代码转换。从被审计单位采集的原始数据库后含有大量的原始代码，例如，U8中的会计科目、部门、职工、客户、供应商等信息在凭证表中只是用代码来表示，甚至记账方向和部分较规范、常用的摘要也用代码来表示，不便于审计人员分析使用。因此，必须把这些代码转换为审计人员易理解、有意义的字段。
4.金额格式转换。不同财务软件存储凭证借贷的方式不完全一致，例如，对于借贷方发生额的记录，在A应用中设计为“借方发生额”与“贷方发生额”两个字段，在B应用中设计为“借贷方标志”与“借贷方发生额”两个字段，需要统一转换成为审计标准表中的格式。
5.数据长宽格式转换。有时被称为数据塑型。
三、审计数据转换的操作
（一）字段汉化
（二）数据类型转换
Excel的数据类型有：文本类型、数值类型、日期类型、逻辑类型。文本类型就是平常所输入的汉字、空格、英文字母；数值类型：数值型就是百分数、会计、科学计数等；逻辑类型就是True和False；日期类型就是常见的日期、时间等。注意， Excel把1900-01-01认为是第1天，即日期存储的是自1900-01-01那天后的第几天，实际上存储为整数类型。Excel的类型转换方法有多种，PowerQuery类型转换更为方便、直接。
Excel的字段类型转换
（1）利用分列功能转换
（2）强制类型转换，利用*1，或 “&”实现数值与字符型的转换
（3）Excel字符型转为日期型。Excel默认的日期型格式有“yyyy/mm/dd”、“yyyy-mm-dd”两种形式。Excel中字符型转换为日期型有以下常见方法：①通过“分列”功能实现在“分列向导”中的【列数据格式】选择“日期”型；②利用TEXT（）函数，将数据转为“yyyy-mm-dd”格式，如【例4.3】示例；③利用“&”字符串连接符，将年月日用“-”或“/”连接，转为Excel默认的日期格式；④利用DATE（）函数，将字符型的年、月、日参数转为日期格式。
2.PowerQuery的类型转换
（三）代码转换（重点）
VLOOKUP函数
VLOOKUP函数在Excel数据分析中占有重要地位。该函数功能是在表格或数值数组的首列查找指定的数值，并由此返回指定列处与该数值同一行的数值，主要语法参数如下：
VLOOKUP (lookup_value,table_array, col_index_num, range_lookup)
2.Power Query 的【合并查询】
Power Query“合并查询”中上面的表为左表，下面的表为右表。
左外部：左表的所有行，右表的匹配行。此例使用左外部连接，“科目余额表”中所有行都将保留，没有匹配项的行匹配空值（null）。
右外部：右表的所有行，左表的匹配行。若此例使用右外部连接，“科目代码表”中所有行都将保留，没有匹配项的行匹配空值（null）。
完全外部：保留两张表中的所有行，左右表互相匹配。若此例使用完全外部连接，“科目代码表”和“科目余额表”中所有行都保留并互相匹配，没有匹配项的行匹配空值（null）。
内部：内部=左外部（右外部）去空，仅留下匹配的行，没有匹配项的行不显示。若此例使用内部连接，“科目余额表”中有匹配项将保留，没有匹配项的行将删除。
左反：左反=左外部取空，显示左表中没有匹配项的行，不显示有匹配项的行。若此例使用左反连接，“科目余额表”中没有匹配项将保留，有匹配项的行将删除。
右反：右反=右外部取空，显示右表中没有匹配项的行，不显示有匹配项的行。若此例使用右反连接，“科目代码表”中没有匹配项将保留，有匹配项的行将删除。
U8中的会计数据主表由凭证表(gl_accvouch)、余额表（gl_accsum）、辅助余额表（gl_accass）这3张表构成。这3张审计中间表的审计数据转换操作，主要是代码转换，参照上图，实现这些表的科目代码、部门编码、职员编码、客户编码、供应商编码的转换，并建议科目代码实现科目名称全称的转换。
（四）金额格式转换（针对凭证，账簿的金额）
本部分的金额格式转换主要针对会计数据中凭证表的借贷方金额转换。
利用Excel实现凭证借贷金额格式的转换。数据源于“第四章/章节数据/ PZFILE金额转换操作.xlsx”。在此凭证表中，“JE”、“DC”列分别为 “金额”、“借贷方标志”列，其中“1”表示借方，“-1”表示贷方，将其转换为“借方发生额”与“贷方发生额”两个字段，操作如下：
添加“借方发生额”与“贷方发生额”两列，公式分别为“=IF(J2=1,I2,"")”、“=IF(J2=-1,I2,"")”，如图所示，实现金额格式的转换。
（五）长宽格式转换，利用.Power Query 的透视/逆透视。
长宽格式转换类似于Excel透视表的透视功能。在PowerQuery中就有【透视】和【逆透视】支持此类型的转换，远比Excel方便。
本节主要介绍审计数据转换的内容和相关操作。
一、审计数据转换的内容
审计数据转换包括两方面的内容，一是将采集到的不同类型的数据转换为常用的数据格式，即数据表格式转化；二是明确地标识出每张表以及每个字段的经济含义，即数据表内容的转换。
（一）数据表格式转换
在我国政府审计的工作中，审计人员通常是根据采集到的不同格式的数据文件，分别运用多种方法将其装载到审计人员使用的SQL Server数据库中，并用SQL进行处理和分析。对于民间审计和内部审计的审计人员而言，SQL仍具有一定难度，更趋向应用审计软件或Excel进行分析。此部分操作与第三章数据采集操作类似，不再进行详细介绍。
（二）数据表内容转换
审计数据分析过程中，会遇到迥然相异的多个信息系统，例如不同财务软件有不同的数据存储规则：
1.同一字段在不同的应用中具有不同的数据类型。例如，字段“借贷方”在A应用中的类型是“字符型”，取值为“ Credit/Debit”，在B应用中的类型为“数值型”，取值为“0/1”，在C应用中的类型为“布尔型”，取值为“True/False”，在U8中没有该字段标志，因借贷金额是分别存放在2个字段中。
2.同一字段在不同的应用中具有不同的名字。例如，发生额在A应用中的字段是“balance”，在B应用中的名称为“bal”，而在C应用中又变成了“currbal”；
3.同名字段具有不同的含义。例如，字段“月折旧额”在A应用中表示为用直线折旧法提取的月折旧额，在B应用中表示为用加速折旧法提取的月折旧额；
4.同一信息在不同的应用中有不同的格式。例如，字段“日期”在A应用中的格式为“YYYY－MM－DD”，在B应用中的格式为“MM／DD／YYYY”，在C应用中的格式又为“DDMMYY”。
5.同一信息在不同的应用中有不同的表达方式。例如，对于借贷方发生额的记录，在A应用中设计为“借方发生额”与“贷方发生额”两个字段，在B应用中设计为“借贷方标志”与“借贷方发生额”两个字段。
二、审计数据转换的方法
常用的数据表内容转换方法有：
1.字段汉化。字段汉化就是明确标出每张表、每个字段的经济含义。通常，从被审计单位采集到的数据其字段名称不用汉语标注，而是采用系统内部的英文字段或英文简称表示，不便于审计人员对各字段经济含义的把握。
2. 数据类型转换。由于每次采集时的设备及条件所限，使得每次所采集的信息不一定满足分析的要求，需要对某些字段进行数据类型转换。数据转换的前提是类型相容，类型相容指的是一种类型数据的值域可以通过常用的转换函数映射到另一种类型的值域上，这种映射不会丢失数据的精确度。
3.代码转换。从被审计单位采集的原始数据库后含有大量的原始代码，例如，U8中的会计科目、部门、职工、客户、供应商等信息在凭证表中只是用代码来表示，甚至记账方向和部分较规范、常用的摘要也用代码来表示，不便于审计人员分析使用。因此，必须把这些代码转换为审计人员易理解、有意义的字段。
4.金额格式转换。不同财务软件存储凭证借贷的方式不完全一致，例如，对于借贷方发生额的记录，在A应用中设计为“借方发生额”与“贷方发生额”两个字段，在B应用中设计为“借贷方标志”与“借贷方发生额”两个字段，需要统一转换成为审计标准表中的格式。
5.数据长宽格式转换。有时被称为数据塑型。
三、审计数据转换的操作
（一）字段汉化
（二）数据类型转换
Excel的数据类型有：文本类型、数值类型、日期类型、逻辑类型。文本类型就是平常所输入的汉字、空格、英文字母；数值类型：数值型就是百分数、会计、科学计数等；逻辑类型就是True和False；日期类型就是常见的日期、时间等。注意， Excel把1900-01-01认为是第1天，即日期存储的是自1900-01-01那天后的第几天，实际上存储为整数类型。Excel的类型转换方法有多种，PowerQuery类型转换更为方便、直接。
Excel的字段类型转换
（1）利用分列功能转换
（2）强制类型转换，利用*1，或 “&”实现数值与字符型的转换
（3）Excel字符型转为日期型。Excel默认的日期型格式有“yyyy/mm/dd”、“yyyy-mm-dd”两种形式。Excel中字符型转换为日期型有以下常见方法：①通过“分列”功能实现在“分列向导”中的【列数据格式】选择“日期”型；②利用TEXT（）函数，将数据转为“yyyy-mm-dd”格式，如【例4.3】示例；③利用“&”字符串连接符，将年月日用“-”或“/”连接，转为Excel默认的日期格式；④利用DATE（）函数，将字符型的年、月、日参数转为日期格式。
2.PowerQuery的类型转换
（三）代码转换（重点）
VLOOKUP函数
VLOOKUP函数在Excel数据分析中占有重要地位。该函数功能是在表格或数值数组的首列查找指定的数值，并由此返回指定列处与该数值同一行的数值，主要语法参数如下：
VLOOKUP (lookup_value,table_array, col_index_num, range_lookup)
2.Power Query 的【合并查询】
Power Query“合并查询”中上面的表为左表，下面的表为右表。
左外部：左表的所有行，右表的匹配行。此例使用左外部连接，“科目余额表”中所有行都将保留，没有匹配项的行匹配空值（null）。
右外部：右表的所有行，左表的匹配行。若此例使用右外部连接，“科目代码表”中所有行都将保留，没有匹配项的行匹配空值（null）。
完全外部：保留两张表中的所有行，左右表互相匹配。若此例使用完全外部连接，“科目代码表”和“科目余额表”中所有行都保留并互相匹配，没有匹配项的行匹配空值（null）。
内部：内部=左外部（右外部）去空，仅留下匹配的行，没有匹配项的行不显示。若此例使用内部连接，“科目余额表”中有匹配项将保留，没有匹配项的行将删除。
左反：左反=左外部取空，显示左表中没有匹配项的行，不显示有匹配项的行。若此例使用左反连接，“科目余额表”中没有匹配项将保留，有匹配项的行将删除。
右反：右反=右外部取空，显示右表中没有匹配项的行，不显示有匹配项的行。若此例使用右反连接，“科目代码表”中没有匹配项将保留，有匹配项的行将删除。
U8中的会计数据主表由凭证表(gl_accvouch)、余额表（gl_accsum）、辅助余额表（gl_accass）这3张表构成。这3张审计中间表的审计数据转换操作，主要是代码转换，参照上图，实现这些表的科目代码、部门编码、职员编码、客户编码、供应商编码的转换，并建议科目代码实现科目名称全称的转换。
（四）金额格式转换（针对凭证，账簿的金额）
本部分的金额格式转换主要针对会计数据中凭证表的借贷方金额转换。
利用Excel实现凭证借贷金额格式的转换。数据源于“第四章/章节数据/ PZFILE金额转换操作.xlsx”。在此凭证表中，“JE”、“DC”列分别为 “金额”、“借贷方标志”列，其中“1”表示借方，“-1”表示贷方，将其转换为“借方发生额”与“贷方发生额”两个字段，操作如下：
添加“借方发生额”与“贷方发生额”两列，公式分别为“=IF(J2=1,I2,"")”、“=IF(J2=-1,I2,"")”，如图所示，实现金额格式的转换。
（五）长宽格式转换，利用.Power Query 的透视/逆透视。
长宽格式转换类似于Excel透视表的透视功能。在PowerQuery中就有【透视】和【逆透视】支持此类型的转换，远比Excel方便。
