審計(jì)中常用函數(shù)公式在審計(jì)工作中常用函數(shù)及實(shí)用技巧，此文件對(duì)于初學(xué)者很有幫助。常用函數(shù)公式有VALUE、LEFT、RIGHT、LEN和FIND、MID、SUMIF、VLOOKUP、CONCATENATE（類(lèi)似&）、IF、ROUND、TRIM、SUBTOTAL、連字符“&”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”與單元格“C6”連接起來(lái)再分類(lèi)匯總即可(如下表2)。表2ABCDEF1美元匯率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注意：用連字符“&”計(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)鍵字符串（如上例為“客戶名稱”）。那么，我們就需要將不同單元格內(nèi)的信息進(jìn)行合并，使其生成唯一的一個(gè)字符串。例如：在編制服裝企業(yè)存貨賬齡分析表時(shí)，由于獲取的明細(xì)清單內(nèi)各件衣服的類(lèi)別、款式、顏色、尺寸均不具有唯一性特點(diǎn)，如下“表四”所示：為了使用VLOOKUP函數(shù)，我們需要自己構(gòu)建一個(gè)唯一性的字符串。在本例中，我們可先在首列中插入一列，標(biāo)題可稱作為“品名”，然后使用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、value()在審計(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)我們選中文本型字符串范圍后，如果按CTRL＋F，查找“.”替換為“.”的話，可將原先帶小數(shù)點(diǎn)的文本型字符串全部轉(zhuǎn)換為數(shù)值型字符串，可以大大地簡(jiǎn)化操作步驟。但是，當(dāng)文本型字符串所代表的數(shù)字信息系整數(shù)時(shí)（即不存在小數(shù)點(diǎn)時(shí)），該方法則不適用。）注：原數(shù)據(jù)乘1或選中單元格，點(diǎn)擊嘆號(hào)“轉(zhuǎn)換為數(shù)值”去除空格鍵函數(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（）一樣。取字符串或數(shù)值長(zhǎng)度函數(shù)-len()我們介紹這個(gè)函數(shù)是為了配合下面截取字符串函數(shù)的使用而特別提出的。104MAX105MIN106PRODUCT107STDEV108STDEVP109SUM110VAR111VARP注意：101到111僅在Office2003、2007及更新的版本中能用。處理隱藏?cái)?shù)據(jù)時(shí)使用。9、VLOOKUP()語(yǔ)法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)說(shuō)明：lookup_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），把“物料信息表”中的物料名稱和單位相應(yīng)地取到“物料進(jìn)倉(cāng)明細(xì)表”中。表5ABCDE1材料信息表2物料編號(hào)物料名稱單位3N001材料A張4N002材料B公斤5N003材料C公斤67物料進(jìn)倉(cāng)明細(xì)表8物料編號(hào)物料名稱單位進(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-3120．0010N002=vlookup(a10,$a$2:$c$5,2,false)=vlookup(a10,$a$2:$c$5,3,false)2005-10-1412．0011N001=vlookup(a11,$a$2:$c$5,2,false)=vlookup(a11,$a$2:$c$5,3,false)2005-10-2124．0012N003=vlookup(a12,$a$2:$c$5,2,false)=vlookup(a12,$a$2:$c$5,3,false)2005-11-1225．0013N002=vlookup(a13,$a$2:$c$5,2,false)=vlookup(a13,$a$2:$c$5,3,false)2005-12-219．50小提示：在公式中引用其他單元格時(shí)，可以直接將光標(biāo)移動(dòng)到目標(biāo)單元格或用光標(biāo)選取引用范圍，再輸入分格符“，”即可。另外，要改變單元格的引用方式，在輸入完單元格按F4。table_array區(qū)域可以定義成名稱,使用名稱來(lái)表達(dá).10、sumif()語(yǔ)法：SUMIF(range,criteria,sum_range)說(shuō)明：range：為用于條件判斷的范圍；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)物料名稱單位進(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,$e$8:$e$13)67物料進(jìn)倉(cāng)明細(xì)表8物料編號(hào)物料名稱單位進(jìn)倉(cāng)時(shí)間進(jìn)倉(cāng)數(shù)量9N001材料A張2005-9-3120．0010N002材料B公斤2005-10-1412．0011N001材料A張2005-10-2124．0012N003材料C公斤2005-11-1225．0013N002材料B公斤2005-12-219．50注:常用于往來(lái)雙掛賬。11、其他的一些函數(shù)我們?cè)趯?shí)際運(yùn)用EXCEL審計(jì)的過(guò)程中，還常常用到month(),year()、now()等函數(shù)。這些函數(shù)簡(jiǎn)單實(shí)用，常常和其他函數(shù)組合起來(lái)使用。宏所謂宏，就是用VBA（VisualBaseApplication）語(yǔ)言編寫(xiě)的一段程序。如果我們?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ū)別。例子：如下表（表1）所示，在單元格“A2”中存放著美元匯率信息，那么我們可將表1中的美元價(jià)格轉(zhuǎn)換為人民幣價(jià)格，即：對(duì)于材料A，我們可以將單元格“C6”與單元格“A2”相乘得出材料A的人民幣價(jià)格。我們?cè)凇癉6”中絕對(duì)引用單元格“A2”，相對(duì)引用單元格“C6”。在“D6”中輸入“=C6*$A$2”，然后將單元格“D6”表1ABCDE1美元匯率28.127345材料幣別價(jià)格人民幣價(jià)格采購(gòu)數(shù)量6材料AUSD12=C6*$A$212007材料BUSD15=C7*$A$213008材料CUSD11=C8*$A$213209材料AUSD12=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ì)賬戶動(dòng)輒成百上千，賬齡審計(jì)頗為繁瑣。而利用Excel進(jìn)行往來(lái)賬款賬齡審計(jì)卻非常簡(jiǎn)單方便。利用Excel進(jìn)行賬齡審計(jì)，需要企業(yè)提供往來(lái)款項(xiàng)、分客戶明細(xì)賬戶編制的“年初數(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ù)的數(shù)據(jù)(需要報(bào)表重分類(lèi))剔除后，就可以在A、B、C、D單元格中設(shè)定函數(shù)公式。一是賬齡1年以內(nèi)的函數(shù)為：A＝IF(本年貸方發(fā)生額＞＝年初余額，年末余額，本年借方發(fā)生額)。二是接著利用Excel的數(shù)據(jù)排序功能，篩選出(年末余額一賬齡1年以內(nèi)金額)＞0的明細(xì)賬戶，獲取其上年“年初數(shù)、本年累計(jì)借方發(fā)生額、本年累計(jì)貸方發(fā)生額、年末余額”格式的會(huì)計(jì)數(shù)據(jù)資料。在上表“單位名稱”列左側(cè)增加列，用以添加上年數(shù)據(jù)。賬齡1～2年的函數(shù)為：B＝IF(本年貸方發(fā)生額十上年貸方發(fā)生額)＞：上年度年初余額，年末余額一賬齡1年以內(nèi)的金額A，上年度借方發(fā)生額)。三是篩選出(年末余額一賬齡)1年以內(nèi)金額一賬齡1～2年的金額)＞0的明細(xì)賬戶，獲取其更前一個(gè)年度的數(shù)據(jù)。賬齡2～3年的函數(shù)為：C=IF((本年貸方發(fā)生額＋上年貸方發(fā)生額＋更上一年的貸方發(fā)生額)＞＝更上一個(gè)年度年初余額，年末余額—賬齡1年以內(nèi)的金額A-賬齡1-2年的金額B，更上一個(gè)年度的借方發(fā)生額)。四是賬齡3年以上的函數(shù)為：n：年末余額一賬齡1年以內(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ì)師提供了年數(shù)總和法(SYD)、雙倍余額遞減法(DDB)、直線法(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ì)話框，其中分左右兩欄：左欄為函數(shù)分類(lèi)，右欄為函數(shù)名。在函數(shù)分類(lèi)欄“常用函數(shù)”對(duì)應(yīng)的“函數(shù)名”中選定SYD，然后單擊“確定”按鈕，就可彈出這就對(duì)話框，其中有四項(xiàng)參數(shù)：cost,salvage,life,per。在cost中輸入成本200000，在salvage中輸入殘值20000，在life中輸入使用年限5，在per中輸入1，就可求得第一年的折舊額為60000元。依次把per中的數(shù)值換為2，3，4，5，就可求出以后各年的折舊額，非常方便準(zhǔn)確。(SLN\DDB)同理。注：此為年折舊。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×5％＝5000(元)，城建稅為5000×5％＝250(元)，教育費(fèi)附加為5000×4％＝200(元)。假設(shè)“營(yíng)業(yè)收入”工作底稿和“營(yíng)業(yè)稅金及附加”工作底稿都用Excel編制。當(dāng)甲公司某年度營(yíng)業(yè)收入審定數(shù)為150000元時(shí)，相應(yīng)的營(yíng)業(yè)稅金及附加也要重新計(jì)算，工作量隨之加大，此時(shí)可在Excel中通過(guò)建立“鏈接”的辦法來(lái)解決這一問(wèn)題。具體如下：在營(yíng)業(yè)收入工作底稿中，將營(yíng)業(yè)收入100000元改為150