浅谈Excel公式函数在审计实务中的运用

刘熔芬

提高审计效率起着事半功倍的效果。但就目前而言，很多审计人员对Excel软件的公式函数仅局限于一些简单的求和、计算平均数以及基本的条件公式函数。笔者曾就职于“四大”，本文中，笔者将会结合自己的经验介绍一些在实务中运用不太普遍，但对提高审计效率大有裨益的Excel公式函数，也请各位同行不吝赐教。

【关键词】Excel软件 公式函数 审计实务运用

Excel功能之强大让人叹为观止，我无数次地给同行以及学生说过的一句话是：“只有你想不到，没有Excel做不到”。Excel提供的大量的内置函数从本质上来说是一些预定义的公式，这些函数使用参数按预先定义好的顺序或结构进行计算。用户可以直接应用这些函数对活动工作表的某个区域内的数值进行系列计算。在实务中，我们只需要掌握部分函数的使用方法，便可事半功倍。笔者将会介绍几个较为实用的函数。

一、LEFT、MID、RIGHT、LEN和FIND函数

数据的整理工作在审计实务中占了很大比重，因为大部分从客户财务系统中导出的数据格式并不全都是规范有序的，那审计师就需要利用各种Excel函数从中提取审计所需要的数据信息。

（一）函数语法

1. LEFT（text， num_chars）：从字符串的最左端位置提取指定数量的字符；

2. MID（text， start_num， num_chars）：从字符串中间的任意位置提取指定数量的字符；

3. RIGHT（text，num_chars ）：从字符串的最右端位置提取指定数量的字符。

其中，text 表示要提取字符的字符串位置；start_num表示开始提取字符串的位置；num_chars 表示需要提取的字符数，忽略时为1。LEFT、MID和RIGHT 函数的运用基本一致，区别在于一个从左开始提取字符串， 一个从中间任意位置开始提取字符串，一个从右开始提取字符串。

4. LEN（text）：返回字符串中的字符个数，即计算字符串的长度。

5. FIND（find_text，within_text，s tart_num）：查找其他文本字符串（within_text） 内的文本字符串（find_text），并从within_text 的首字符开始返回find_text 的起始位置编号。其中，“find_text”表示要查找的字符串；“within_text”表示要在其中进行搜索的字符串，“start_num”表示起始搜索位置，在within_text 中第一个字符的位置为1，忽略时，start_num= 1。

（二）在审计中的运用实例

实务中经常会有财务人员将存货数量直接填列在摘要栏内，表1列示了类似情况，如果逐个摘录摘要文本中的数量信息并手工录入，既耗时且还容易出错，但若审计员了解上述一组提取数据的函数，提取出存货数量就会迎刃而解。

由于文本中每笔交易数量的位数不同，所以我们不能直接用RIGHT函数提取摘要栏内的数量信息。假设我们需要提取第一笔摘要栏“电源模块6ES73071EA000AA0：257”中的数量信息“257”， 则复合函数公式为“=RIGHT（B3，LEN（B3）-FIND（“：”，B3，1）”。拆分各公式后具体介绍如下：

LEN（B3）： 测量该字符串长度，结果为24；

FIND（“：”，B3，1）： 查找冒号在该字符串中所处位置， 结果为21；

RIGHT（B3，3）： 通过LEN 和FIND函数组合运用，得出需要在B3单元格中从右返回3个字符（即24-21= 3）， 结果为“257”。

熟练掌握后， 同理还可以提取处于最左边或者中间任意位置的数量信息。

二、VLOOKUP、HLOOKUP函数

VLOOKUP函数和HLOOKUP函数是用户在查找数据时使用频率非常高的Excel函数。利用这两个函数可以实现一些简单的数据查询。

（一）函数语法

1. VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

2. HLOOKUP（lookup_value，table_array，row_index_num，range_lookup）：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

其中，lookup_value ： 需要在数据表首列进行搜索的值。

table_array： 需要搜索数据的信息表。

col_index_num：满足条件的单元格在数组区域table_array中的列序号。

row_index_num：满足条件的单元格在数组区域table_array中的行序号。

range_lookup ： 在查找时， 是否需要精确匹配。如果为FALSE，则大致匹配，如果为TRUE 或忽略，则精确匹配（并区分全/ 半角）。在实务中， 一般选择大致匹配， 即“FALSE”。

VLOOKUP函数和HLOOKUP函数的语法非常相似，功能基本相同。主要区别是VLOOKUP主要用于搜索用户查找范围中的首列中满足条件的数据，并根据指定的列号返回对应的值，唯一的区别在于VLOOKUP函数按列进行查询，而HLOOKUP函数按行查询。

（二）在审计中的运用实例

在进行审计工作时，往往会需要以前年度的对比数，比如说按客户分类的应收账款余额就需要列示出该客户上年末的对比数，通过两期对比数，我们可以判断有无客户余额变化情况、有无新增客户等情况。表2、表3分别给出了某公司截至2013年12月31日以及2012年12月31日按客户分类的应收账款余额。endprint

首先，在“2013年应收账款余额”工作表的D2单元格内粘贴“2012年12月31日”字符串， 作为行标题。然后， 在“2013年”工作表D3单元格内使用VLOOKUP 函数， 公式为“=VLOOKUP（A3，'2012年应收账款明细账'！$A$3：$C$7，3，FALSE）”，依次类推，其中“$”符号是为了绝对引用和相对引用单元格信息，以便通过鼠标的单元格拖放功能， 让电脑自动生成D列内其他行次的单元格信息。在表2 和表3 的基础上， 经上述操作并略作整理， 便可得到表4 所示结果。其中，因为海克斯康测量技术是2013年度新增的一家客户，所以其在2012年12月31日没有余额，公式自动返回“#N/A”。

三、COUNTIF、SUMIF、SUBTOTAL函数

（一）函数语法

1. COUNTIF（range，criteria）：主要用于有目的地统计指定范围内满足特地条件的数据个数。其中，range必须是对单元格区域的直接引用或由引用函数产生的间接引用；crieria是定义的特点条件。

2. SUMIF（range，criteria，[sum_range]）：主要用于按指定条件在查找区域进行查找，并返回查找区域内满足条件数值的和。SUMIF的前两个参数和COUNTIF函数完全一致，如果不输入数据求和区域sum_range，则SUMIF函数会对查找区域range自动求和。

3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于筛选下的数据统计，是Excel中唯一一个可以只统计可见单元格的函数。其中：function_num参数使该函数具备求和、计数、求平均值等等功能，在审计实务中，运用最多的function_num是9，即对可见单元格求和。

（二）在审计中的运用实例

表5是某公司按产品分类的主营业务收入明细账，在审计实务中，审计人员可能需要统计出每类产品的销量以及销售金额。这里就可以用到COUNTIF函数以及SUMIF函数。

首先编制表6列出所需要整理的信息，在E3单元格使用COUNTIF函数，公式为“=COUNTIF（$A$3：$A$13，D3）”，在F3单元格使用SUMIF函数，公式为“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次类推，让电脑自动生成E列以及F列内其他行次的单元格信息，经上述操作之后便可得出如下表6。

当然，上述功能也可以通过筛选之后利用SUBTOTAL函数对可见单元格进行求和处理完成。

本文所介绍的几个公式函数仅仅是Excel庞大公式函数中的“冰山一角”，在既定的审计程序目标和有限的审计工作时间内，若想不断地提高工作效率，就需要审计人员不断地去探索和完善。

【参考文献】

[1]张宇. 浅谈Excel软件在审计实务中的运用[J].会计之友，2008（10）.

[2]孙良文.基于 Excel 的审计数据挖掘方法与路径研究[J.]财会通讯，2011（12）.

[3]Excel Home. Excel应用大全[M] . 北京：人民邮电出版社，2008.endprint

首先，在“2013年应收账款余额”工作表的D2单元格内粘贴“2012年12月31日”字符串， 作为行标题。然后， 在“2013年”工作表D3单元格内使用VLOOKUP 函数， 公式为“=VLOOKUP（A3，'2012年应收账款明细账'！$A$3：$C$7，3，FALSE）”，依次类推，其中“$”符号是为了绝对引用和相对引用单元格信息，以便通过鼠标的单元格拖放功能， 让电脑自动生成D列内其他行次的单元格信息。在表2 和表3 的基础上， 经上述操作并略作整理， 便可得到表4 所示结果。其中，因为海克斯康测量技术是2013年度新增的一家客户，所以其在2012年12月31日没有余额，公式自动返回“#N/A”。

三、COUNTIF、SUMIF、SUBTOTAL函数

（一）函数语法

1. COUNTIF（range，criteria）：主要用于有目的地统计指定范围内满足特地条件的数据个数。其中，range必须是对单元格区域的直接引用或由引用函数产生的间接引用；crieria是定义的特点条件。

2. SUMIF（range，criteria，[sum_range]）：主要用于按指定条件在查找区域进行查找，并返回查找区域内满足条件数值的和。SUMIF的前两个参数和COUNTIF函数完全一致，如果不输入数据求和区域sum_range，则SUMIF函数会对查找区域range自动求和。

3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于筛选下的数据统计，是Excel中唯一一个可以只统计可见单元格的函数。其中：function_num参数使该函数具备求和、计数、求平均值等等功能，在审计实务中，运用最多的function_num是9，即对可见单元格求和。

（二）在审计中的运用实例

表5是某公司按产品分类的主营业务收入明细账，在审计实务中，审计人员可能需要统计出每类产品的销量以及销售金额。这里就可以用到COUNTIF函数以及SUMIF函数。

首先编制表6列出所需要整理的信息，在E3单元格使用COUNTIF函数，公式为“=COUNTIF（$A$3：$A$13，D3）”，在F3单元格使用SUMIF函数，公式为“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次类推，让电脑自动生成E列以及F列内其他行次的单元格信息，经上述操作之后便可得出如下表6。

当然，上述功能也可以通过筛选之后利用SUBTOTAL函数对可见单元格进行求和处理完成。

本文所介绍的几个公式函数仅仅是Excel庞大公式函数中的“冰山一角”，在既定的审计程序目标和有限的审计工作时间内，若想不断地提高工作效率，就需要审计人员不断地去探索和完善。

【参考文献】

[1]张宇. 浅谈Excel软件在审计实务中的运用[J].会计之友，2008（10）.

[2]孙良文.基于 Excel 的审计数据挖掘方法与路径研究[J.]财会通讯，2011（12）.

[3]Excel Home. Excel应用大全[M] . 北京：人民邮电出版社，2008.endprint

首先，在“2013年应收账款余额”工作表的D2单元格内粘贴“2012年12月31日”字符串， 作为行标题。然后， 在“2013年”工作表D3单元格内使用VLOOKUP 函数， 公式为“=VLOOKUP（A3，'2012年应收账款明细账'！$A$3：$C$7，3，FALSE）”，依次类推，其中“$”符号是为了绝对引用和相对引用单元格信息，以便通过鼠标的单元格拖放功能， 让电脑自动生成D列内其他行次的单元格信息。在表2 和表3 的基础上， 经上述操作并略作整理， 便可得到表4 所示结果。其中，因为海克斯康测量技术是2013年度新增的一家客户，所以其在2012年12月31日没有余额，公式自动返回“#N/A”。

三、COUNTIF、SUMIF、SUBTOTAL函数

（一）函数语法

1. COUNTIF（range，criteria）：主要用于有目的地统计指定范围内满足特地条件的数据个数。其中，range必须是对单元格区域的直接引用或由引用函数产生的间接引用；crieria是定义的特点条件。

2. SUMIF（range，criteria，[sum_range]）：主要用于按指定条件在查找区域进行查找，并返回查找区域内满足条件数值的和。SUMIF的前两个参数和COUNTIF函数完全一致，如果不输入数据求和区域sum_range，则SUMIF函数会对查找区域range自动求和。

3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于筛选下的数据统计，是Excel中唯一一个可以只统计可见单元格的函数。其中：function_num参数使该函数具备求和、计数、求平均值等等功能，在审计实务中，运用最多的function_num是9，即对可见单元格求和。

（二）在审计中的运用实例

表5是某公司按产品分类的主营业务收入明细账，在审计实务中，审计人员可能需要统计出每类产品的销量以及销售金额。这里就可以用到COUNTIF函数以及SUMIF函数。

首先编制表6列出所需要整理的信息，在E3单元格使用COUNTIF函数，公式为“=COUNTIF（$A$3：$A$13，D3）”，在F3单元格使用SUMIF函数，公式为“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次类推，让电脑自动生成E列以及F列内其他行次的单元格信息，经上述操作之后便可得出如下表6。

当然，上述功能也可以通过筛选之后利用SUBTOTAL函数对可见单元格进行求和处理完成。

本文所介绍的几个公式函数仅仅是Excel庞大公式函数中的“冰山一角”，在既定的审计程序目标和有限的审计工作时间内，若想不断地提高工作效率，就需要审计人员不断地去探索和完善。

【参考文献】

[1]张宇. 浅谈Excel软件在审计实务中的运用[J].会计之友，2008（10）.

[2]孙良文.基于 Excel 的审计数据挖掘方法与路径研究[J.]财会通讯，2011（12）.

[3]Excel Home. Excel应用大全[M] . 北京：人民邮电出版社，2008.endprint