EXCEL BI在计算机审计中的应用

李琦

山东财经大学燕山学院，山东济南，271199

0 引言

大数据时代背景下，传统的审计技术也面临着挑战。面对信息化程度不断提高的审计对象，以手工查账为主的传统审计已经不能满足审计人员的需求，计算机辅助审计技术应运而生[1]。审计人员针对不同的审计目标和工作需求，运用相关分析软件，寻找相关证据及线索，对被审计单位实施有效监督，从而实现持续性审计。Excel作为一种通用性分析软件，不仅可以分析财务数据，还可以融合非财务信息进行综合分析，在审计中被广泛应用。因此，本文对计算机审计的发展具有一定的实用价值。

1 计算机审计相关概念

1.1 计算机审计

根据审计的侧重点不同，计算机审计可以分为两种层次：电子数据审计和信息系统审计[2]。电子数据审计，审计的重点在于数据，是为达到相应的审计目标，对被审计单位的相关数据进行采集、处理和分析，进而发现其中的问题，获取审计证据。信息系统审计，审计的重点在于存储数据的系统，即被审计单位采用的信息系统能否对资产进行有效保护、维护数据的完整。

1.2 计算机辅助审计

计算机辅助审计，是指在进行审计工作时，运用计算机相关技术，帮助审计人员执行或者完成审计程序和审计任务。同计算机审计一样，计算机辅助审计也分为两种层次。

面向数据的计算机辅助审计技术，是指对数据进行采集、处理和分析所使用的计算机软件、方法或者工具。按照是否专门嵌入审计模块，可分为专业的审计数据分析软件，如悦审、ACL；以及通用类分析软件，如Excel、SQL等。

面向系统的计算机辅助审计技术，是利用一定的技术方法，对被审计单位的信息处理系统进行检查，验证系统是否存在风险。采用的方法有集成测试、测试数据、平行模拟等[3]。

2 Excel BI相关概述

本文以面向数据的计算机辅助审计技术Excel BI为重点，详细介绍其在计算机审计中的应用。Excel BI是以通用性分析软件Excel为基础，结合Excel软件内置的Power Query、Power Pivot、Power BI插件，进行交互式数据处理的技术。需要注意的是，本文中所介绍的方法须基于Excel 2016及以上版本，否则无法使用上述插件。

2.1 Excel

Excel作为Microsoft Windows的一个组件，近年来已经被广泛运用到财务会计中，会计人员利用其中的公式及相关的计算功能编制各种报表等财务数据[4]。而其对数据强大的处理能力往往被忽视，作为一种通用性分析工具，Excel支持多种类型数据的处理[5]。

2.2 Power Query

Power Query编辑器，用于数据的预处理，即对数据进行清洗整理、内容或格式转换的一种技术。

2.3 Power Pivot

在审计工作中，Power Pivot主要用于数据分析，其配有强大的DAX函数，可以对多张表构建关系模型，进行多个维度的数据分析。

2.4 Power BI

Power BI是一种可视化分析软件，可以将数据分析的结果进行可视化展示。

3 Excel BI在计算机审计中的应用

Excel作为一种数据处理软件，其强大的数据分析能力也为审计提供便利。本文将从数据采集、数据预处理、数据分析、可视化分析四个方面进行介绍[6]。

3.1 审计数据采集

审计数据采集是指审计人员为了完成审计任务，按照审计需求从被审计单位的信息系统或者其他来源获得相关电子数据的过程[7]。

各单位存储数据的方式多样，Excel的“数据”选项卡下，支持多种格式的数据采集：可以直接采集Excel、文本、XML等文件，或连接SQL Server、Access等数据库获取数据；还支持使用ODBC接口直接访问被审计单位数据库进行数据采集。

除上述操作外，Excel内置的Power Query提供了更为丰富的采集方式，不仅可以采集上述类型的数据，还可以进行网页数据、大数据平台的数据采集。同时可以直接选择“从文件夹”直接采集文件夹数据。

3.2 审计数据预处理

采集来的数据往往存在以下问题：基于审计目的和选择范围不同，可能会导致数据冗余和无效；在存储过程中，因为范式分解可能造成信息分裂；不同格式、多种数据来源的数据无法放在一起直接分析。为了解决这些问题，在对数据进行正式分析前，需进行预处理。

数据预处理是指对于采集来的数据，进行清洗、转换、和验证等操作，将其转化为可以理解、直接利用的数据的过程。

3.2.1 数据清洗

数据清洗，是利用相关技术对错误的、不完整及重复的数据进行检测修正的过程。Excel中可以直接通过删除冗余行和列，清理掉垃圾数据；通过替换值来将不完整的数据进行补充替换；或通过函数的使用，将不规范的数据进行筛选删除，例如用LEN()、MID()、DATEVALUE()函数判断身份证号码是否正确。

3.2.2 数据转换

Excel中，数据转换有两种层次。①数据表格式的转换，将数据转化为对应的数据格式，例如文本型、数值型数据；②数据表内容的转换，即将不同字段的具体经济含义进行标识，例如将DC标识为借贷方向。对于数据表格式的转换，修改字段类型即可实现。对于内容的转换，如果只涉及一张表的内容转换，可以直接进行重命名；但如果涉及多张表之间的内容相互对照转换，则可以使用VLOOKUP()函数或Power Query中的表连接迅速匹配。

3.2.3 数据验证

从数据的采集，到数据的预处理，数据验证要贯穿始终。数据验证有两个目的：①为了确保数据清洗和转换的目标顺利实现；②确认数据预处理的相关操作并未产生新错误。例如，利用Power Query的分组依据，检验各个会计期间的借贷方发生额是否平衡。

3.3 审计数据分析

审计数据分析是指在对审计数据采集、预处理的基础上，运用查询、统计、挖掘等分析技术对数据展开分析，从中发现错误，为审计工作提供强有力的审计证据，从而实现审计目标。

按照分析思路和方法的不同，审计数据分析又可以分为常规审计数据分析及多维审计数据分析两个层次。

3.3.1 常规审计数据分析

常规审计数据分析，是指运用常规性分析方法进行分析。在分析时，根据数据的不同构成层次，分别采用不同的分析模型：以全部数据为分析对象的系统分析；以不同类型的数据为对象的类别分析；以各个数据为对象的个体分析。

（1）系统分析。系统分析，把全部数据看作整体，进行描述性统计、排序、比率分析、趋势分析等常规性分析。系统分析需先在Excel中加载“分析数据库”。

（2）类别分析。类别分析，是将数据划分为字符型、数值型和日期型三类，分别采用不同的分析方法。

字符型数据，常用的分析方法是分类汇总，即对字符型分类变量（代码表中的各个维度变量，如性别、部门）进行计数、最值等汇总操作。Excel中的分类汇总、SUMIF()函数、数据透视表、Power Query中的分组依据都可实现分类汇总。其中，Excel的分类汇总，只能对单个字段进行操作；数据透视表可以利用行和列，实现两个字段的分类汇总，但是三个及以上的字段无法操作；而Power Query中的分组依据能够同时实现多个字段的分类汇总。

数值型数据，常用的分析方法是分层分析。是指将连续的数值划分为不同的区间，再观察不同的区间中数据的分布情况。VLOOKUP()函数、数据透视表、Power Query中的添加条件列都可实现数值的分层。其中，VLOOKUP()及Power Query可以自定义分层的间距，而数据透视表只能够对数据进行等距离划分。

日期型数据，常用的分析方法是账龄分析。账龄分析是指把一定的时间段划分为若干间隔，分析不同时间间隔中各个数据的表现。DATEDIF()函数及数据透视表可直接实现账龄分析，或者在Power Query中的添加辅助列，先计算出时间间隔，再对其进行数值分层分析。

（3）个体分析。个体分析是前两种分析的延伸，帮助审计人员筛选线索，精确地获得审计证据。个体分析相关方法主要有查询分析、重号分析和断号分析。

查询分析通常与审计业务规则相联系，即通过相关方法检验数据是否为有效值、是否符合业务特定的数据模式约束。例如，数据表中的借贷方向DC=“0”，则表示该借贷方向是无效值。Excel中，审计人员可以使用筛选功能进行查询分析[7]，但只能对同一张表中的数据进行筛选。若想同时对多张表的数据进行查询，可利用Power Query的合并查询先将多张表进行关联，再进行筛选。

重号分析，用来检查同一字段在表中是否存在重复现象，例如检查相同编号的发票是否重复记账，判断企业是否存在利用同一张发票重复报销的情况。使用COUNTIF()函数，可快速计算出同一字段出现的次数[8]；或者选择标注重复值，将重复的字段突出显示；也可以利用Power Query的合并查询，将多张表进行连接，进而查询多张表中重复的信息。

断号分析，主要用于检查某些内容在数据中记录是否连续。根据断号的信息，例如支票或者发票号码不连续，说明可能存在的舞弊行为。MATCH()函数和COUNTIF()函数都可以快速实现断号分析；除此之外，在Power Query中添加从0和从1的索引列，利用合并查询实现表的自连接，也可确定出缺失的内容。

3.3.2 多维审计数据分析

多维审计数据分析，是指分析人员从多个不同的观察角度对企业的信息进行快速、一致、交互性地存取，从而对数据有更深入的了解。

Excel中，通常使用Power Pivot插件进行多维数据分析，其拥有处理海量数据的能力。Power Pivot最大的特点在于构建关系模型，同时配有强大的DAX函数，有效展开数据分析。在进行数据分析时，Excel及其插件互相搭配使用，具体流程如下。

（1）启动Power Pivot。在初次使用时，需先将Power Pivot加载至Excel界面。

（2）数据导入。可以采用三种方法：①从Excel添加到数据模型；②从Power Query中上载；③在Power Pivot界面直接导入。

（3）生成日期表。根据导入的数据可以在Power Pivot中直接自动生成；又可先在Excel中创建日期表，再加载至Power Pivot。

（4）数据整理。在建模之前，需对数据内容和格式修改。如果数据处理较复杂，一般先导入Power Query编辑器进行处理，再将处理好的数据上载至Power Pivot中建模。

（5）构建关系模型。Power Pivot的建模实质是根据不同表中相同的字段列，构建起各表间的关系模型。建模后，无需再对多张表格进行合并，利用构建的关系模型，即可对多张表同时进行不同维度的分析。

（6）多维数据分析。建模后，Power Pivot运用DAX（数据分析表达式）函数进行数据分析。DAX函数是一种公式语言，分析人员可以根据需要使用DAX函数自行定义计算列和度量值。例如，创建本期的修理费度量值，修理费总额:=SUM([修理费发生额])。DAX函数除了包括Excel中的常用函数，还拥有上下文函数、筛选器函数和时间智能函数等高级函数。

（7）形成数据透视表。多维数据分析结束后，通常搭配数据透视表列示分析结果。

3.4 可视化分析

完成数据分析后，结合计算机图形学、图像处理等技术，将分析结果呈现出图形、图像、视频等内容进行展示，这一过程即可视化分析。与数据结果的直接列示相比，可视化分析更为直观地将数据结果通过图形图表呈现，便于理解，同时支持用户对数据进行交互处理。

Excel中，通常使用Power BI进行可视化分析。在进行数据分析时，通常先用Power Query进行数据处理，再用Power Pivot进行数据建模，最后使用Power BI进行可视化展示。

4 结语

随着计算机技术的发展，审计工作越发依赖计算机。在审计工作中，运用计算机辅助能够有效简化审计流程，节省审计时间。而Excel BI的应用，可以贯穿审计数据采集、预处理、数据分析及可视化整个过程，不仅可以提升审计工作的效率，在一定程度上还可以有效降低审计风险，确保审计工作的正确性，从而促进审计事业的可持续发展。