淺談Excel公式函數(shù)在審計(jì)實(shí)務(wù)中的運(yùn)用

劉熔芬

提高審計(jì)效率起著事半功倍的效果。但就目前而言，很多審計(jì)人員對Excel軟件的公式函數(shù)僅局限于一些簡單的求和、計(jì)算平均數(shù)以及基本的條件公式函數(shù)。筆者曾就職于“四大”，本文中，筆者將會(huì)結(jié)合自己的經(jīng)驗(yàn)介紹一些在實(shí)務(wù)中運(yùn)用不太普遍，但對提高審計(jì)效率大有裨益的Excel公式函數(shù)，也請各位同行不吝賜教。

【關(guān)鍵詞】Excel軟件 公式函數(shù) 審計(jì)實(shí)務(wù)運(yùn)用

Excel功能之強(qiáng)大讓人嘆為觀止，我無數(shù)次地給同行以及學(xué)生說過的一句話是：“只有你想不到，沒有Excel做不到”。Excel提供的大量的內(nèi)置函數(shù)從本質(zhì)上來說是一些預(yù)定義的公式，這些函數(shù)使用參數(shù)按預(yù)先定義好的順序或結(jié)構(gòu)進(jìn)行計(jì)算。用戶可以直接應(yīng)用這些函數(shù)對活動(dòng)工作表的某個(gè)區(qū)域內(nèi)的數(shù)值進(jìn)行系列計(jì)算。在實(shí)務(wù)中，我們只需要掌握部分函數(shù)的使用方法，便可事半功倍。筆者將會(huì)介紹幾個(gè)較為實(shí)用的函數(shù)。

一、LEFT、MID、RIGHT、LEN和FIND函數(shù)

數(shù)據(jù)的整理工作在審計(jì)實(shí)務(wù)中占了很大比重，因?yàn)榇蟛糠謴目蛻糌?cái)務(wù)系統(tǒng)中導(dǎo)出的數(shù)據(jù)格式并不全都是規(guī)范有序的，那審計(jì)師就需要利用各種Excel函數(shù)從中提取審計(jì)所需要的數(shù)據(jù)信息。

（一）函數(shù)語法

1. LEFT（text， num_chars）：從字符串的最左端位置提取指定數(shù)量的字符；

2. MID（text， start_num， num_chars）：從字符串中間的任意位置提取指定數(shù)量的字符；

3. RIGHT（text，num_chars ）：從字符串的最右端位置提取指定數(shù)量的字符。

其中，text 表示要提取字符的字符串位置；start_num表示開始提取字符串的位置；num_chars 表示需要提取的字符數(shù)，忽略時(shí)為1。LEFT、MID和RIGHT 函數(shù)的運(yùn)用基本一致，區(qū)別在于一個(gè)從左開始提取字符串， 一個(gè)從中間任意位置開始提取字符串，一個(gè)從右開始提取字符串。

4. LEN（text）：返回字符串中的字符個(gè)數(shù)，即計(jì)算字符串的長度。

5. FIND（find_text，within_text，s tart_num）：查找其他文本字符串（within_text） 內(nèi)的文本字符串（find_text），并從within_text 的首字符開始返回find_text 的起始位置編號(hào)。其中，“find_text”表示要查找的字符串；“within_text”表示要在其中進(jìn)行搜索的字符串，“start_num”表示起始搜索位置，在within_text 中第一個(gè)字符的位置為1，忽略時(shí)，start_num= 1。

（二）在審計(jì)中的運(yùn)用實(shí)例

實(shí)務(wù)中經(jīng)常會(huì)有財(cái)務(wù)人員將存貨數(shù)量直接填列在摘要欄內(nèi)，表1列示了類似情況，如果逐個(gè)摘錄摘要文本中的數(shù)量信息并手工錄入，既耗時(shí)且還容易出錯(cuò)，但若審計(jì)員了解上述一組提取數(shù)據(jù)的函數(shù)，提取出存貨數(shù)量就會(huì)迎刃而解。

由于文本中每筆交易數(shù)量的位數(shù)不同，所以我們不能直接用RIGHT函數(shù)提取摘要欄內(nèi)的數(shù)量信息。假設(shè)我們需要提取第一筆摘要欄“電源模塊6ES73071EA000AA0：257”中的數(shù)量信息“257”， 則復(fù)合函數(shù)公式為“=RIGHT（B3，LEN（B3）-FIND（“：”，B3，1）”。拆分各公式后具體介紹如下：

LEN（B3）： 測量該字符串長度，結(jié)果為24；

FIND（“：”，B3，1）： 查找冒號(hào)在該字符串中所處位置， 結(jié)果為21；

RIGHT（B3，3）： 通過LEN 和FIND函數(shù)組合運(yùn)用，得出需要在B3單元格中從右返回3個(gè)字符（即24-21= 3）， 結(jié)果為“257”。

熟練掌握后， 同理還可以提取處于最左邊或者中間任意位置的數(shù)量信息。

二、VLOOKUP、HLOOKUP函數(shù)

VLOOKUP函數(shù)和HLOOKUP函數(shù)是用戶在查找數(shù)據(jù)時(shí)使用頻率非常高的Excel函數(shù)。利用這兩個(gè)函數(shù)可以實(shí)現(xiàn)一些簡單的數(shù)據(jù)查詢。

（一）函數(shù)語法

1. VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。

2. HLOOKUP（lookup_value，table_array，row_index_num，range_lookup）：在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。

其中，lookup_value ： 需要在數(shù)據(jù)表首列進(jìn)行搜索的值。

table_array： 需要搜索數(shù)據(jù)的信息表。

col_index_num：滿足條件的單元格在數(shù)組區(qū)域table_array中的列序號(hào)。

row_index_num：滿足條件的單元格在數(shù)組區(qū)域table_array中的行序號(hào)。

range_lookup ： 在查找時(shí)， 是否需要精確匹配。如果為FALSE，則大致匹配，如果為TRUE 或忽略，則精確匹配（并區(qū)分全/ 半角）。在實(shí)務(wù)中， 一般選擇大致匹配， 即“FALSE”。

VLOOKUP函數(shù)和HLOOKUP函數(shù)的語法非常相似，功能基本相同。主要區(qū)別是VLOOKUP主要用于搜索用戶查找范圍中的首列中滿足條件的數(shù)據(jù)，并根據(jù)指定的列號(hào)返回對應(yīng)的值，唯一的區(qū)別在于VLOOKUP函數(shù)按列進(jìn)行查詢，而HLOOKUP函數(shù)按行查詢。

（二）在審計(jì)中的運(yùn)用實(shí)例

在進(jìn)行審計(jì)工作時(shí)，往往會(huì)需要以前年度的對比數(shù)，比如說按客戶分類的應(yīng)收賬款余額就需要列示出該客戶上年末的對比數(shù)，通過兩期對比數(shù)，我們可以判斷有無客戶余額變化情況、有無新增客戶等情況。表2、表3分別給出了某公司截至2013年12月31日以及2012年12月31日按客戶分類的應(yīng)收賬款余額。endprint

首先，在“2013年應(yīng)收賬款余額”工作表的D2單元格內(nèi)粘貼“2012年12月31日”字符串， 作為行標(biāo)題。然后， 在“2013年”工作表D3單元格內(nèi)使用VLOOKUP 函數(shù)， 公式為“=VLOOKUP（A3，'2012年應(yīng)收賬款明細(xì)賬'！$A$3：$C$7，3，F(xiàn)ALSE）”，依次類推，其中“$”符號(hào)是為了絕對引用和相對引用單元格信息，以便通過鼠標(biāo)的單元格拖放功能， 讓電腦自動(dòng)生成D列內(nèi)其他行次的單元格信息。在表2 和表3 的基礎(chǔ)上， 經(jīng)上述操作并略作整理， 便可得到表4 所示結(jié)果。其中，因?yàn)楹？怂箍禍y量技術(shù)是2013年度新增的一家客戶，所以其在2012年12月31日沒有余額，公式自動(dòng)返回“#N/A”。

三、COUNTIF、SUMIF、SUBTOTAL函數(shù)

（一）函數(shù)語法

1. COUNTIF（range，criteria）：主要用于有目的地統(tǒng)計(jì)指定范圍內(nèi)滿足特地條件的數(shù)據(jù)個(gè)數(shù)。其中，range必須是對單元格區(qū)域的直接引用或由引用函數(shù)產(chǎn)生的間接引用；crieria是定義的特點(diǎn)條件。

2. SUMIF（range，criteria，[sum_range]）：主要用于按指定條件在查找區(qū)域進(jìn)行查找，并返回查找區(qū)域內(nèi)滿足條件數(shù)值的和。SUMIF的前兩個(gè)參數(shù)和COUNTIF函數(shù)完全一致，如果不輸入數(shù)據(jù)求和區(qū)域sum_range，則SUMIF函數(shù)會(huì)對查找區(qū)域range自動(dòng)求和。

3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于篩選下的數(shù)據(jù)統(tǒng)計(jì)，是Excel中唯一一個(gè)可以只統(tǒng)計(jì)可見單元格的函數(shù)。其中：function_num參數(shù)使該函數(shù)具備求和、計(jì)數(shù)、求平均值等等功能，在審計(jì)實(shí)務(wù)中，運(yùn)用最多的function_num是9，即對可見單元格求和。

（二）在審計(jì)中的運(yùn)用實(shí)例

表5是某公司按產(chǎn)品分類的主營業(yè)務(wù)收入明細(xì)賬，在審計(jì)實(shí)務(wù)中，審計(jì)人員可能需要統(tǒng)計(jì)出每類產(chǎn)品的銷量以及銷售金額。這里就可以用到COUNTIF函數(shù)以及SUMIF函數(shù)。

首先編制表6列出所需要整理的信息，在E3單元格使用COUNTIF函數(shù)，公式為“=COUNTIF（$A$3：$A$13，D3）”，在F3單元格使用SUMIF函數(shù)，公式為“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次類推，讓電腦自動(dòng)生成E列以及F列內(nèi)其他行次的單元格信息，經(jīng)上述操作之后便可得出如下表6。

當(dāng)然，上述功能也可以通過篩選之后利用SUBTOTAL函數(shù)對可見單元格進(jìn)行求和處理完成。

本文所介紹的幾個(gè)公式函數(shù)僅僅是Excel龐大公式函數(shù)中的“冰山一角”，在既定的審計(jì)程序目標(biāo)和有限的審計(jì)工作時(shí)間內(nèi)，若想不斷地提高工作效率，就需要審計(jì)人員不斷地去探索和完善。

【參考文獻(xiàn)】

[1]張宇. 淺談Excel軟件在審計(jì)實(shí)務(wù)中的運(yùn)用[J].會(huì)計(jì)之友，2008（10）.

[2]孫良文.基于 Excel 的審計(jì)數(shù)據(jù)挖掘方法與路徑研究[J.]財(cái)會(huì)通訊，2011（12）.

[3]Excel Home. Excel應(yīng)用大全[M] . 北京：人民郵電出版社，2008.endprint

首先，在“2013年應(yīng)收賬款余額”工作表的D2單元格內(nèi)粘貼“2012年12月31日”字符串， 作為行標(biāo)題。然后， 在“2013年”工作表D3單元格內(nèi)使用VLOOKUP 函數(shù)， 公式為“=VLOOKUP（A3，'2012年應(yīng)收賬款明細(xì)賬'！$A$3：$C$7，3，F(xiàn)ALSE）”，依次類推，其中“$”符號(hào)是為了絕對引用和相對引用單元格信息，以便通過鼠標(biāo)的單元格拖放功能， 讓電腦自動(dòng)生成D列內(nèi)其他行次的單元格信息。在表2 和表3 的基礎(chǔ)上， 經(jīng)上述操作并略作整理， 便可得到表4 所示結(jié)果。其中，因?yàn)楹？怂箍禍y量技術(shù)是2013年度新增的一家客戶，所以其在2012年12月31日沒有余額，公式自動(dòng)返回“#N/A”。

三、COUNTIF、SUMIF、SUBTOTAL函數(shù)

（一）函數(shù)語法

1. COUNTIF（range，criteria）：主要用于有目的地統(tǒng)計(jì)指定范圍內(nèi)滿足特地條件的數(shù)據(jù)個(gè)數(shù)。其中，range必須是對單元格區(qū)域的直接引用或由引用函數(shù)產(chǎn)生的間接引用；crieria是定義的特點(diǎn)條件。

2. SUMIF（range，criteria，[sum_range]）：主要用于按指定條件在查找區(qū)域進(jìn)行查找，并返回查找區(qū)域內(nèi)滿足條件數(shù)值的和。SUMIF的前兩個(gè)參數(shù)和COUNTIF函數(shù)完全一致，如果不輸入數(shù)據(jù)求和區(qū)域sum_range，則SUMIF函數(shù)會(huì)對查找區(qū)域range自動(dòng)求和。

3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于篩選下的數(shù)據(jù)統(tǒng)計(jì)，是Excel中唯一一個(gè)可以只統(tǒng)計(jì)可見單元格的函數(shù)。其中：function_num參數(shù)使該函數(shù)具備求和、計(jì)數(shù)、求平均值等等功能，在審計(jì)實(shí)務(wù)中，運(yùn)用最多的function_num是9，即對可見單元格求和。

（二）在審計(jì)中的運(yùn)用實(shí)例

表5是某公司按產(chǎn)品分類的主營業(yè)務(wù)收入明細(xì)賬，在審計(jì)實(shí)務(wù)中，審計(jì)人員可能需要統(tǒng)計(jì)出每類產(chǎn)品的銷量以及銷售金額。這里就可以用到COUNTIF函數(shù)以及SUMIF函數(shù)。

首先編制表6列出所需要整理的信息，在E3單元格使用COUNTIF函數(shù)，公式為“=COUNTIF（$A$3：$A$13，D3）”，在F3單元格使用SUMIF函數(shù)，公式為“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次類推，讓電腦自動(dòng)生成E列以及F列內(nèi)其他行次的單元格信息，經(jīng)上述操作之后便可得出如下表6。

當(dāng)然，上述功能也可以通過篩選之后利用SUBTOTAL函數(shù)對可見單元格進(jìn)行求和處理完成。

本文所介紹的幾個(gè)公式函數(shù)僅僅是Excel龐大公式函數(shù)中的“冰山一角”，在既定的審計(jì)程序目標(biāo)和有限的審計(jì)工作時(shí)間內(nèi)，若想不斷地提高工作效率，就需要審計(jì)人員不斷地去探索和完善。

【參考文獻(xiàn)】

[1]張宇. 淺談Excel軟件在審計(jì)實(shí)務(wù)中的運(yùn)用[J].會(huì)計(jì)之友，2008（10）.

[2]孫良文.基于 Excel 的審計(jì)數(shù)據(jù)挖掘方法與路徑研究[J.]財(cái)會(huì)通訊，2011（12）.

[3]Excel Home. Excel應(yīng)用大全[M] . 北京：人民郵電出版社，2008.endprint

首先，在“2013年應(yīng)收賬款余額”工作表的D2單元格內(nèi)粘貼“2012年12月31日”字符串， 作為行標(biāo)題。然后， 在“2013年”工作表D3單元格內(nèi)使用VLOOKUP 函數(shù)， 公式為“=VLOOKUP（A3，'2012年應(yīng)收賬款明細(xì)賬'！$A$3：$C$7，3，F(xiàn)ALSE）”，依次類推，其中“$”符號(hào)是為了絕對引用和相對引用單元格信息，以便通過鼠標(biāo)的單元格拖放功能， 讓電腦自動(dòng)生成D列內(nèi)其他行次的單元格信息。在表2 和表3 的基礎(chǔ)上， 經(jīng)上述操作并略作整理， 便可得到表4 所示結(jié)果。其中，因?yàn)楹？怂箍禍y量技術(shù)是2013年度新增的一家客戶，所以其在2012年12月31日沒有余額，公式自動(dòng)返回“#N/A”。

三、COUNTIF、SUMIF、SUBTOTAL函數(shù)

（一）函數(shù)語法

1. COUNTIF（range，criteria）：主要用于有目的地統(tǒng)計(jì)指定范圍內(nèi)滿足特地條件的數(shù)據(jù)個(gè)數(shù)。其中，range必須是對單元格區(qū)域的直接引用或由引用函數(shù)產(chǎn)生的間接引用；crieria是定義的特點(diǎn)條件。

2. SUMIF（range，criteria，[sum_range]）：主要用于按指定條件在查找區(qū)域進(jìn)行查找，并返回查找區(qū)域內(nèi)滿足條件數(shù)值的和。SUMIF的前兩個(gè)參數(shù)和COUNTIF函數(shù)完全一致，如果不輸入數(shù)據(jù)求和區(qū)域sum_range，則SUMIF函數(shù)會(huì)對查找區(qū)域range自動(dòng)求和。

3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于篩選下的數(shù)據(jù)統(tǒng)計(jì)，是Excel中唯一一個(gè)可以只統(tǒng)計(jì)可見單元格的函數(shù)。其中：function_num參數(shù)使該函數(shù)具備求和、計(jì)數(shù)、求平均值等等功能，在審計(jì)實(shí)務(wù)中，運(yùn)用最多的function_num是9，即對可見單元格求和。

（二）在審計(jì)中的運(yùn)用實(shí)例

表5是某公司按產(chǎn)品分類的主營業(yè)務(wù)收入明細(xì)賬，在審計(jì)實(shí)務(wù)中，審計(jì)人員可能需要統(tǒng)計(jì)出每類產(chǎn)品的銷量以及銷售金額。這里就可以用到COUNTIF函數(shù)以及SUMIF函數(shù)。

首先編制表6列出所需要整理的信息，在E3單元格使用COUNTIF函數(shù)，公式為“=COUNTIF（$A$3：$A$13，D3）”，在F3單元格使用SUMIF函數(shù)，公式為“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次類推，讓電腦自動(dòng)生成E列以及F列內(nèi)其他行次的單元格信息，經(jīng)上述操作之后便可得出如下表6。

當(dāng)然，上述功能也可以通過篩選之后利用SUBTOTAL函數(shù)對可見單元格進(jìn)行求和處理完成。

本文所介紹的幾個(gè)公式函數(shù)僅僅是Excel龐大公式函數(shù)中的“冰山一角”，在既定的審計(jì)程序目標(biāo)和有限的審計(jì)工作時(shí)間內(nèi)，若想不斷地提高工作效率，就需要審計(jì)人員不斷地去探索和完善。

【參考文獻(xiàn)】

[1]張宇. 淺談Excel軟件在審計(jì)實(shí)務(wù)中的運(yùn)用[J].會(huì)計(jì)之友，2008（10）.

[2]孫良文.基于 Excel 的審計(jì)數(shù)據(jù)挖掘方法與路徑研究[J.]財(cái)會(huì)通訊，2011（12）.

[3]Excel Home. Excel應(yīng)用大全[M] . 北京：人民郵電出版社，2008.endprint