審計(jì)工作中常用EXCLE函數(shù)整理.

1、審計(jì)中常用函數(shù)公式在審計(jì)工作中常用函數(shù)及實(shí)用技巧，此文件對(duì)于初學(xué)者很有幫助。常用函數(shù)公式有VALUE、LEFT、RIGHT、LEN和FIND 、MID、SUMIF、VLOOKUP、CONCATENATE（類(lèi)似&）、IF、ROUND、TRIM、SUBTOTAL、1、 連字符“&” CONCATENATE在實(shí)際運(yùn)用EXCEL進(jìn)行審計(jì)工作的時(shí)候，我們?yōu)榱四茉趦蓚€(gè)數(shù)據(jù)庫(kù)之間找一個(gè)合適的比較標(biāo)準(zhǔn)，有時(shí)需要將兩個(gè)或以上的單元格連接起來(lái)。這時(shí)，我們可以用字符“&”將兩個(gè)或以上的單元格連接起來(lái)。例子：我們想統(tǒng)計(jì)一下美元采購(gòu)價(jià)格為12的材料A的采購(gòu)數(shù)量。這時(shí)，我們可以將單元格“A6”與單

2、元格“C6”連接起來(lái)再分類(lèi)匯總即可(如下表2)。表2ABCDE F1美元匯率28.127345材料幣別價(jià)格人民幣價(jià)格采購(gòu)數(shù)量6材料AUSD12.00=C6*$A$21,200.00=A6&"-"&C67材料BUSD15.00=C7*$A$21,300.00=A7&"-"&C78材料CUSD11.00=C8*$A$21,320.00=A8&"-"&C89材料AUSD12.00=C9*$A$21,100.00=A9&"-"&C9注意：用連字符“&”

3、計(jì)算出的結(jié)果是文本型字符，也就是文本格式，不能用來(lái)加、減、乘、除等數(shù)學(xué)運(yùn)算。如果文本型字符是數(shù)字，那么我們可以用函數(shù)value( )將其轉(zhuǎn)換為數(shù)值型字符，然后才能進(jìn)行數(shù)學(xué)運(yùn)算。（函數(shù)value( )的用法見(jiàn)下面）2、CONCATENATE函數(shù)功能：將多個(gè)文本字符串合并成一個(gè)。實(shí)務(wù)中，不同的工作簿之間并非時(shí)刻存在唯一的關(guān)鍵字符串（如上例為“客戶(hù)名稱(chēng)”）。那么，我們就需要將不同單元格內(nèi)的信息進(jìn)行合并，使其生成唯一的一個(gè)字符串。例如：在編制服裝企業(yè)存貨賬齡分析表時(shí)，由于獲取的明細(xì)清單內(nèi)各件衣服的類(lèi)別、款式、顏色、尺寸均不具有唯一性特點(diǎn)，如下“表四”所示：為了使用VLOOKUP函數(shù)，我們需要自己構(gòu)建一

4、個(gè)唯一性的字符串。在本例中，我們可先在首列中插入一列，標(biāo)題可稱(chēng)作為“品名”，然后使用CONCATENATE函數(shù)，創(chuàng)建唯一性的字符串，公式介紹如下：ABCDE1品名類(lèi)別款式顏色尺寸2女裝/休閑服/紅/中號(hào)女裝休閑服紅中號(hào)公式：=CONCATENATE(text1,text2,text3,text4,text29,text30)該函數(shù)，共可合并30個(gè)不同單元格內(nèi)的字符串，在本例中的運(yùn)用如下：=CONCATENATE(B2,"/",C2,"/",D2,"/",E2)（其中“/”，是為了便于檢查的需要，不用也可）注：常用語(yǔ)連接時(shí)間日期。3、v

5、alue( )在審計(jì)實(shí)務(wù)中，我們常碰到某些財(cái)務(wù)軟件導(dǎo)出的財(cái)務(wù)數(shù)據(jù)是文本型字符串，無(wú)法進(jìn)行計(jì)算，如果遇到這種情況，使用VALUE函數(shù)，可以將該文本型字符串轉(zhuǎn)換成數(shù)字型語(yǔ)法：=value(text)功能：將代表數(shù)字的文本字符串轉(zhuǎn)換成數(shù)字說(shuō)明：TEXT，表示需要轉(zhuǎn)換的文本型單元格位置，比如我們需要將A1單元格中的文本型字符串轉(zhuǎn)換成數(shù)值型，則公式VALUE(A1)即可。（注：EXCEL中存在個(gè)小BUG，當(dāng)我們選中文本型字符串范圍后，如果按CTRLF，查找“.”替換為“.”的話(huà)，可將原先帶小數(shù)點(diǎn)的文本型字符串全部轉(zhuǎn)換為數(shù)值型字符串，可以大大地簡(jiǎn)化操作步驟。但是，當(dāng)文本型字符串所代表的數(shù)字信息系整數(shù)時(shí)（即

6、不存在小數(shù)點(diǎn)時(shí)），該方法則不適用。）注：原數(shù)據(jù)乘1或選中單元格，點(diǎn)擊嘆號(hào)“轉(zhuǎn)換為數(shù)值”4、 去除空格鍵函數(shù)-trim( )我們?cè)趯?dǎo)出ERP數(shù)據(jù)庫(kù)中的數(shù)據(jù)時(shí)，由于ERP數(shù)據(jù)庫(kù)中規(guī)定了字符的長(zhǎng)度，所以在導(dǎo)出數(shù)據(jù)時(shí)，會(huì)造成有些字符后面帶有空格鍵字符，影響我們數(shù)據(jù)統(tǒng)計(jì)的準(zhǔn)確性。為此，我們需要掌握一個(gè)可以除去文本以外空格鍵字符的函數(shù)。語(yǔ)法：trim(text)說(shuō)明：trim( )函數(shù)可把文本前后兩邊的空格鍵去掉（注：不能去掉文本中間的空格鍵）。函數(shù)的使用方法和函數(shù)value（）一樣。5、 取字符串或數(shù)值長(zhǎng)度函數(shù)-len( )我們介紹這個(gè)函數(shù)是為了配合下面截取字符串函數(shù)的使用而特別提出的。語(yǔ)法：len(t

7、ext)說(shuō)明：這個(gè)函數(shù)返回的數(shù)值是字符串的個(gè)數(shù)。函數(shù)的使用方法和函數(shù)value（）一樣。6、 將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本-TEXT()語(yǔ)法: TEXT(value,format_text)Value  為數(shù)值、計(jì)算結(jié)果為數(shù)字值的公式，或?qū)Π瑪?shù)字值的單元格的引用。Format_text    為“單元格格式”對(duì)話(huà)框中“數(shù)字”選項(xiàng)卡上“分類(lèi)”框中的文本形式的數(shù)字格式。說(shuō)明· Format_text 不能包含星號(hào) (*)。· 通過(guò)“格式”菜單調(diào)用“單元格”命令，然后在“數(shù)字”選項(xiàng)卡上設(shè)置單元格的格式，只會(huì)更改單元格的格式而不會(huì)影

8、響其中的數(shù)值。使用函數(shù) TEXT 可以將數(shù)值轉(zhuǎn)換為帶格式的文本，而其結(jié)果將不再作為數(shù)字參與計(jì)算。示例1. 創(chuàng)建空白工作簿或工作表。 2. 請(qǐng)?jiān)凇皫椭敝黝}中選取示例。不要選取行或列標(biāo)題。 從幫助中選取示例。3. 按 Ctrl+C。 4. 在工作表中，選中單元格 A1，再按 Ctrl+V。 5. 若要在查看結(jié)果和查看返回結(jié)果的公式之間切換，請(qǐng)按 Ctrl+（重音符），或在“工具”菜單上，指向“公式審核”，再單擊“公式審核模式”。 123AB銷(xiāo)售人員銷(xiāo)售Buchanan2800Dodsworth40%公式說(shuō)明（結(jié)果）=A2&" sold "&TEXT(B2, &

9、quot;$0.00")&" worth of units."將上面內(nèi)容合并為一個(gè)短語(yǔ) (Buchanan sold $2800.00 worth of units.)=A3&" sold "&TEXT(B3,"0%")&" of the total sales."將上面內(nèi)容合并為一個(gè)短語(yǔ) (Dodsworth sold 40% of the total sales.)7、 截取字符串函數(shù)-right( ),left( ),mid( )我們從ERP里導(dǎo)出數(shù)據(jù)之后，數(shù)據(jù)錄入員

10、所錄入的數(shù)據(jù)不一定和我們所要的一模一樣，但其中可能包含了我們所要的信息，這樣，我們就需要把其中的信息提取出來(lái)。我們可以用截取字符串函數(shù)來(lái)幫助我們完成工作。語(yǔ)法：左截取字符串函數(shù)：left(text, number )右截取字符串函數(shù)：right(text, number )中間截取字符串函數(shù)：mid(text, start_num, number )說(shuō)明：Text是指函數(shù)操作的對(duì)象，也就是包含所要提取字符的文本Number是要提取字符的數(shù)量Start_num 是指開(kāi)始提取字符的起始位置但在實(shí)際操作中，常將right（）函數(shù)或left（）函數(shù)與len（）函數(shù)結(jié)合起來(lái)使用，達(dá)到快速提取我們需要的信

11、息的目的。在表4中，我們假定A列中前面的是分公司代碼，后面是采購(gòu)單號(hào)。我們現(xiàn)在要把所有的采購(gòu)單號(hào)取出來(lái)分析，可以這樣處理：表4ABCDE F1A1 PO0512112=right(a1,len(a1)-3)2A1 PO0512001=right(a2,len(a2)-3)3B1 PO200411010A=right(a3,len(a3)-3) 4B1 PO200512121=right(a4,len(a4)-3)5C1 PO200503141C=right(a5,len(a5)-3)8、SUBTOTAL函數(shù)日常中最常見(jiàn)到的是在分類(lèi)匯總中， 返回列表或數(shù)據(jù)庫(kù)中的分類(lèi)匯總。通常，使用“數(shù)據(jù)”菜單中

12、的“分類(lèi)匯總”命令可以容易地創(chuàng)建帶有分類(lèi)匯總的列表。一旦創(chuàng)建了分類(lèi)匯總，就可以通過(guò)編輯 SUBTOTAL 函數(shù)對(duì)該列表進(jìn)行修改。語(yǔ)法:SUBTOTAL(function_num,ref1,ref2, .)Function_num 為 1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數(shù)字，指定使用何種函數(shù)在列表中進(jìn)行分類(lèi)匯總計(jì)算。ref1refn參數(shù)為要對(duì)其進(jìn)行分類(lèi)匯總計(jì)算的第1至29個(gè)命名區(qū)域或引用。必須是對(duì)單元格區(qū)域的引用。Function_num （包含隱藏值）為1到11之間的自然數(shù)，用來(lái)指定分類(lèi)匯總計(jì)算使用的函數(shù)值相當(dāng)于函數(shù)1AVERAGE2COUNT3COUNTA4

13、MAX5MIN6PRODUCT7STDEV8STDEVP9SUM10VAR11VARPFunction_num （忽略隱藏值） 為101到111之間的自然數(shù)值相當(dāng)于函數(shù)101AVERAGE102COUNT103COUNTA104MAX105MIN106PRODUCT107STDEV108STDEVP109SUM110VAR111VARP注意：101到111僅在Office 2003、2007及更新的版本中能用。處理隱藏?cái)?shù)據(jù)時(shí)使用。9、VLOOKUP ( )語(yǔ)法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)說(shuō)明：look

14、up_value：指需要在table_array區(qū)域中第一列查找的值；table_array：指需要在其中查找數(shù)據(jù)的表格；col_index_num：指在table_array區(qū)域中對(duì)應(yīng)匹配值所返回的值所在的列數(shù)；range_lookup：這是一個(gè)邏輯值（ture或false）,如果填ture是近似匹配，而false則是精確匹配。這個(gè)函數(shù)的主要用途是將存放在另外一張表格的信息相對(duì)應(yīng)地提取到一張表格上。我們舉個(gè)簡(jiǎn)單的例子（見(jiàn)表5），把“物料信息表”中的物料名稱(chēng)和單位相應(yīng)地取到“物料進(jìn)倉(cāng)明細(xì)表”中。表5ABCDE1材料信息表2物料編號(hào)物料名稱(chēng)單位3N001材料A張4N002材料B公斤5N003材料

15、C公斤67物料進(jìn)倉(cāng)明細(xì)表8物料編號(hào)物料名稱(chēng)單位進(jìn)倉(cāng)時(shí)間進(jìn)倉(cāng)數(shù)量9N001=vlookup(a9,$a$2:$c$5,2,false)=vlookup(a9,$a$2:$c$5,3,false)2005-9-31200010N002=vlookup(a10,$a$2:$c$5,2,false)=vlookup(a10,$a$2:$c$5,3,false)2005-10-14120011N001=vlookup(a11,$a$2:$c$5,2,false)=vlookup(a11,$a$2:$c$5,3,false)2005-10-21240012N003=vlookup(a12,$a$2:$c$

16、5,2,false)=vlookup(a12,$a$2:$c$5,3,false)2005-11-12250013N002=vlookup(a13,$a$2:$c$5,2,false)=vlookup(a13,$a$2:$c$5,3,false)2005-12-21950小提示：在公式中引用其他單元格時(shí)，可以直接將光標(biāo)移動(dòng)到目標(biāo)單元格或用光標(biāo)選取引用范圍，再輸入分格符“，”即可。 另外，要改變單元格的引用方式，在輸入完單元格按F4。table_array區(qū)域可以定義成名稱(chēng),使用名稱(chēng)來(lái)表達(dá).10 、sumif( )語(yǔ)法：SUMIF(range, criteria, sum_range)說(shuō)明：ra

17、nge：為用于條件判斷的范圍；criteria：用于判斷的標(biāo)準(zhǔn)；sum_range：實(shí)際求和的范圍。我們?cè)谶\(yùn)用該公式求和時(shí)要注意，range和sum_range是一一對(duì)應(yīng)的關(guān)系，如果他們的對(duì)應(yīng)關(guān)系錯(cuò)了，求出的結(jié)果也不一定正確。我們還是以表5中的“物料進(jìn)倉(cāng)明細(xì)表”為例子，用sumif（）分類(lèi)匯總物料出倉(cāng)數(shù)量，見(jiàn)表6表6ABCDE1材料信息表2物料編號(hào)物料名稱(chēng)單位進(jìn)倉(cāng)總數(shù)3N001材料A張=sumif($a$8:$a$13,a3,$e$8:$e$13)4N002材料B公斤=sumif($a$8:$a$13,a4,$e$8:$e$13)5N003材料C公斤=sumif($a$8:$a$13,a5,$

18、e$8:$e$13)67物料進(jìn)倉(cāng)明細(xì)表8物料編號(hào)物料名稱(chēng)單位進(jìn)倉(cāng)時(shí)間進(jìn)倉(cāng)數(shù)量9N001材料A張2005-9-31200010N002材料B公斤2005-10-14120011N001材料A張2005-10-21240012N003材料C公斤2005-11-12250013N002材料B公斤2005-12-21950注:常用于往來(lái)雙掛賬。11、其他的一些函數(shù)我們?cè)趯?shí)際運(yùn)用EXCEL審計(jì)的過(guò)程中，還常常用到month( ), year( )、now( )等函數(shù)。這些函數(shù)簡(jiǎn)單實(shí)用，常常和其他函數(shù)組合起來(lái)使用。宏所謂宏，就是用VBA（Visual Base Application）語(yǔ)言編寫(xiě)的一段程序。

19、如果我們?cè)趯徲?jì)的過(guò)程中，能夠用運(yùn)用宏來(lái)輔助審計(jì)工作，那將會(huì)大大地提高我們的工作效率。VBA語(yǔ)言是VB語(yǔ)言的一個(gè)分支，如果我們有一種數(shù)據(jù)庫(kù)計(jì)算機(jī)語(yǔ)言作為基礎(chǔ)，那么學(xué)好VBA語(yǔ)言并不難。注：不常用，太繁瑣。使用技巧絕對(duì)引用和相引用在使用EXCEL函數(shù)時(shí)，我們常要引用某個(gè)單元格的數(shù)據(jù)。這時(shí)，我們就需要了解絕對(duì)引用和相對(duì)引用的區(qū)別和作用。定義：相對(duì)引用，隨著引用單元格的位置變化，被引用單元格位置也是在變化的是相對(duì)引用；絕對(duì)引用（$），隨著引用單元格位置的變化，被引用單元格位置不變化的就是絕對(duì)引用（$）。區(qū)別：相對(duì)引用和絕對(duì)引用的區(qū)別在于當(dāng)引用單元格被復(fù)制到其他地方時(shí)，被引用單元格的位置變與不變的區(qū)別。

20、例子： 如下表（表1）所示，在單元格“A2”中存放著美元匯率信息，那么我們可將表1中的美元價(jià)格轉(zhuǎn)換為人民幣價(jià)格，即：對(duì)于材料A，我們可以將單元格“C6”與單元格“A2”相乘得出材料A的人民幣價(jià)格。我們?cè)凇癉6”中絕對(duì)引用單元格“A2”，相對(duì)引用單元格“C6”。在“D6”中輸入“=C6*$A$2”，然后將單元格“D6”復(fù)制到剩余兩個(gè)需要求人民幣價(jià)格的單元格上，就可以很方便地求出結(jié)果了。表1ABCDE1美元匯率28.127345材料幣別價(jià)格人民幣價(jià)格采購(gòu)數(shù)量6材料AUSD12=C6*$A$212007材料BUSD15=C7*$A$213008材料CUSD11=C8*$A$213209材料AUSD1

21、2=C9*$A$21100Excel在審計(jì)往來(lái)賬款賬齡時(shí)的運(yùn)用 往來(lái)賬款(應(yīng)收賬款、其他應(yīng)收款、應(yīng)付賬款等)的賬齡審計(jì)，是財(cái)務(wù)會(huì)計(jì)報(bào)表的基礎(chǔ)審計(jì)工作，企業(yè)往來(lái)款項(xiàng)的明細(xì)賬戶(hù)動(dòng)輒成百上千，賬齡審計(jì)頗為繁瑣。而利用Excel進(jìn)行往來(lái)賬款賬齡審計(jì)卻非常簡(jiǎn)單方便。利用Excel進(jìn)行賬齡審計(jì)，需要企業(yè)提供往來(lái)款項(xiàng)、分客戶(hù)明細(xì)賬戶(hù)編制的“年初數(shù)、本年累計(jì)借方發(fā)生額、本年累計(jì)貸方發(fā)生額、年末余額”格式的會(huì)計(jì)數(shù)據(jù)資料。目前越來(lái)越多的企業(yè)采用會(huì)計(jì)軟件進(jìn)行核算，該資料可以利用會(huì)計(jì)軟件提供的“數(shù)據(jù)導(dǎo)出”功能導(dǎo)出為Excel格式獲得。將獲取資料置于一個(gè)Excel工作簿內(nèi)。運(yùn)用Excel的數(shù)據(jù)排序功能將年末余額為負(fù)數(shù)的

22、數(shù)據(jù)(需要報(bào)表重分類(lèi))剔除后，就可以在A、B、C、D單元格中設(shè)定函數(shù)公式。一是賬齡1年以?xún)?nèi)的函數(shù)為：AIF(本年貸方發(fā)生額年初余額，年末余額，本年借方發(fā)生額)。二是接著利用Excel的數(shù)據(jù)排序功能，篩選出(年末余額一賬齡1年以?xún)?nèi)金額)0的明細(xì)賬戶(hù)，獲取其上年“年初數(shù)、本年累計(jì)借方發(fā)生額、本年累計(jì)貸方發(fā)生額、年末余額”格式的會(huì)計(jì)數(shù)據(jù)資料。在上表“單位名稱(chēng)”列左側(cè)增加列，用以添加上年數(shù)據(jù)。賬齡12年的函數(shù)為：BIF(本年貸方發(fā)生額十上年貸方發(fā)生額)：上年度年初余額，年末余額一賬齡1年以?xún)?nèi)的金額A，上年度借方發(fā)生額)。三是篩選出(年末余額一賬齡)1年以?xún)?nèi)金額一賬齡12年的金額)0的明細(xì)賬戶(hù)，獲取其更

23、前一個(gè)年度的數(shù)據(jù)。賬齡23年的函數(shù)為：C=IF(本年貸方發(fā)生額上年貸方發(fā)生額更上一年的貸方發(fā)生額)更上一個(gè)年度年初余額，年末余額賬齡1年以?xún)?nèi)的金額A-賬齡1-2年的金額B，更上一個(gè)年度的借方發(fā)生額)。四是賬齡3年以上的函數(shù)為：n：年末余額一賬齡1年以?xún)?nèi)的金額A賬齡1-2年的金額B賬齡2-3年的金額C。輸入完畢后，模板將自動(dòng)計(jì)算年末賬齡，方便準(zhǔn)確。注：審計(jì)年度新增往來(lái)單位可能穿插在內(nèi)，與上一年度在順序上不匹配，就會(huì)需要到SUMIF。所以此方法更為繁瑣，更簡(jiǎn)便的方法為：用鼎信諾取連續(xù)三年的數(shù)據(jù)，系統(tǒng)自動(dòng)生成賬齡。用時(shí)目測(cè)1小時(shí)6個(gè)往來(lái)。Excel在審計(jì)固定資產(chǎn)折舊時(shí)的運(yùn)用 Excel為注冊(cè)會(huì)計(jì)師提

24、供了年數(shù)總和法(SYD)、雙倍余額遞減法(DDB)、直線(xiàn)法(SLN)等多種計(jì)算折舊方法。筆者以年數(shù)總和法為例予以介紹。例如，甲公司的一項(xiàng)固定資產(chǎn)原值為200000元，預(yù)計(jì)殘值為20000元，使用年限為5年，采用年數(shù)總和法計(jì)算折舊。為審計(jì)該項(xiàng)折舊計(jì)提是否正確，可用以下方法：打開(kāi)Excel，點(diǎn)擊工具條上的“FX”函數(shù)按鈕，彈出“粘貼函數(shù)”對(duì)話(huà)框，其中分左右兩欄：左欄為函數(shù)分類(lèi)，右欄為函數(shù)名。在函數(shù)分類(lèi)欄“常用函數(shù)”對(duì)應(yīng)的“函數(shù)名”中選定SYD，然后單擊“確定”按鈕，就可彈出這就對(duì)話(huà)框，其中有四項(xiàng)參數(shù)：cost,salvage,life,per。在cost中輸入成本200000，在salvage中輸

25、入殘值20000，在life中輸入使用年限5，在per中輸入1，就可求得第一年的折舊額為60000元。依次把per中的數(shù)值換為2，3，4，5，就可求出以后各年的折舊額，非常方便準(zhǔn)確。(SLNDDB)同理。注：此為年折舊。Excel在審計(jì)相互引用數(shù)據(jù)的運(yùn)用 由于會(huì)計(jì)報(bào)表各科目之間的勾稽關(guān)系，一個(gè)科目金額的變動(dòng)常常會(huì)引起其他相關(guān)科目的連鎖變動(dòng)。這種情況常常導(dǎo)致修改審計(jì)工作底稿，一方面增加了工作量，另一方面也使工作底稿容易變得凌亂不堪，費(fèi)時(shí)費(fèi)力。Execl在這方面也發(fā)揮了不小的作用。例如，甲公司某年度營(yíng)業(yè)收入為100000元，營(yíng)業(yè)稅稅率為5，城建稅稅率為5，教育費(fèi)附加費(fèi)4，則營(yíng)業(yè)稅金及附加計(jì)算如下：營(yíng)業(yè)稅為100000×55000(元)，城建稅為5000×525