EXCEL在審計(jì)中的運(yùn)用暨審計(jì)工作對(duì)EXCEL最基本的要求.doc

EXCEL在審計(jì)中需要我們掌握哪些技能？(聽(tīng)會(huì)律哥講審計(jì)故事學(xué)EXCEL！給初學(xué)者！) 在我談?wù)搶徲?jì)理論及其實(shí)務(wù)之前，我想談一個(gè)很重要的東西，工具性的東西，那就是EXCEL，這個(gè)東西只要讀過(guò)大學(xué)的人都學(xué)過(guò)，但是都是半桶水，準(zhǔn)確的說(shuō)我們99%的人連半桶水都不是，我們實(shí)際中還沒(méi)有用到EXCEL的2%的功能！即使在這2%里面很多人都還在黑暗中探索，談EXCEL色變！包括我自己要不是提前去實(shí)習(xí)了，畢業(yè)的時(shí)候可能跟大多數(shù)人一樣，連最基本的操作都不會(huì)。總結(jié)下應(yīng)該有兩個(gè)原因，一是，曾經(jīng)學(xué)過(guò)，但是用的比較少，慢慢的就忘記，大學(xué)里的考試也就那么回事，要掛科的東西都不著急，更不要說(shuō)，考試過(guò)了的EXCEL了，二是，可能很多人沒(méi)有自己的電腦，這個(gè)就用起來(lái)很不方便。 以前的都過(guò)去了，那么從現(xiàn)在開(kāi)始，這個(gè)東西你就得學(xué)了，EXCEL是做會(huì)計(jì)和審計(jì)的一個(gè)重要的工具，非常重要，（即使現(xiàn)在做審計(jì)、會(huì)計(jì)用了軟件，但是他的作用沒(méi)有絲毫減輕）就像你要用筷子吃飯一樣，我還沒(méi)有發(fā)現(xiàn)一個(gè)比較全的特別是注重會(huì)計(jì)、審計(jì)實(shí)務(wù)相結(jié)合的函數(shù)運(yùn)用的介紹，有些就是一把在里面這里貼一點(diǎn)那里貼點(diǎn)，有些根本就用不著，多了你現(xiàn)在記了也沒(méi)有用，不怕EXC傷害你的自尊心就拿一本書(shū)做枕頭開(kāi)始記吧，看能學(xué)會(huì)幾個(gè)。我記得剛?cè)ナ聞?wù)所實(shí)習(xí)的時(shí)候，有個(gè)銀行賬號(hào)讓我輸入，輸著輸著就傻了，里面全是變成科學(xué)計(jì)數(shù)法了。我納悶了半天，沒(méi)有搞定還是問(wèn)了同事，為了減少這種尷尬，我決定把我做審計(jì)過(guò)程中，比較常用的函數(shù)和技巧總結(jié)下來(lái)（OFFICE2003版為例,套07版就很簡(jiǎn)單了），但是現(xiàn)在可能不是很完整，我會(huì)陸續(xù)完善，每一個(gè)都將以案例的形式出現(xiàn)，并且所有案例都是在會(huì)律哥本人親自實(shí)驗(yàn)之后寫(xiě)出來(lái)的請(qǐng)大家放心使用： 1、單引號(hào)的作用，這個(gè)作用就是幫你搞定我上面的那個(gè)問(wèn)題，一般一個(gè)單元格里面只能輸入11個(gè)數(shù)字，那么像你填寫(xiě)身份證號(hào)碼，或者單位銀行存款賬號(hào)的時(shí)候，就會(huì)多出來(lái)成科學(xué)計(jì)數(shù)法，這種方式可以解決，還有就是你再填寫(xiě)之前，記得是填寫(xiě)之前，選擇 “格式”-“單元格”-“數(shù)字”-“分類”里選好“文本”格式解決,填寫(xiě)之后再選沒(méi)有用了。 2、DATEDIF的作用，測(cè)算固定資產(chǎn)折舊，通常，在財(cái)務(wù)軟件里面可以直接輸入折舊自動(dòng)計(jì)算，但是如果某一年你調(diào)了折舊額，或者是錄錯(cuò)了固定資產(chǎn)原值，這個(gè)改的話就比較麻煩，特別是一些制造企業(yè)，固定資產(chǎn)很多，你要手改很麻煩，所以通常你會(huì)自己做一個(gè)EXCEL工作表，每個(gè)月算折舊，做審計(jì)的話，就更加不要說(shuō)了，測(cè)算固定資產(chǎn)折舊這個(gè)程序，沒(méi)有其他的證據(jù)的情況下，肯定是需要審計(jì)師重新算一次的。 如果要算這個(gè)機(jī)器從購(gòu)買(mǎi)日（假設(shè)購(gòu)買(mǎi)日就開(kāi)始使用）到你的審計(jì)截止日需要計(jì)提幾個(gè)月的折舊，這個(gè)公式怎么用，好像在03版的“插入”-“函數(shù)”里面找不到，那我們就在你要計(jì)算的那個(gè)單元格里輸入=DATEDIF(A1,A2,M)，注意這里的AI是你實(shí)際使用開(kāi)始日，A2是你的審計(jì)截止日（報(bào)表截止日），這里的M,（記得要打英文的引號(hào)）就是你要算的月份。需要強(qiáng)調(diào)的是這個(gè)公式很爭(zhēng)氣！很給力！已經(jīng)幫我們考慮了本月新增使用的固定資產(chǎn)，本月不計(jì)提折舊這個(gè)因素，所以你直接用就可以。 好，現(xiàn)在對(duì)這個(gè)公式進(jìn)行一下變形，那我如果要求天數(shù)呢？比如我測(cè)算借款利息的時(shí)候，我可不可以用這個(gè)函數(shù)呢？可以！變形為=DATEDIF(A1,A2,D)，那要求年數(shù)呢？可以！變形為=DATEDIF(A1,A2,Y)，大家應(yīng)該看出道理來(lái)了，其實(shí)就是變的后面的“”里的字母，他們分別是年月日的英文首寫(xiě)。 特殊情況，有的時(shí)候我們很無(wú)聊，就是算下，歷史上的事件到今天總共多少天了，比如2001年7月13日，到今天，你又忘記今天是多少號(hào)了，怎么辦？用 =DATEDIF(A1,today（）,D)，即A2你就用today（）替換，today（）就是表示今天的意思，可以在電子表格里輸入=today（）感受一下！ 特別特別特別強(qiáng)調(diào)：這個(gè)世界上的人都是痛并快樂(lè)著的，這個(gè)公式也不例外，有他快樂(lè)的一面，也有他的痛的一面，通常我們的審計(jì)截止日（財(cái)務(wù)報(bào)表日）不全是12月31日，有可能是月報(bào)，季度報(bào)，半年報(bào)，他痛的一面就展現(xiàn)出來(lái)了。 大家可以在EXC里做這么一個(gè)實(shí)驗(yàn)，用DATEDIF公式算，2010-12-31，到2011-3-31，答案是幾？計(jì)算2010-12-31到2011-6-30，答案是幾？到2011-9-30,2011-12-31呢？你會(huì)發(fā)現(xiàn)一個(gè)有意思的東西，在計(jì)算到2011-6-30,2011-9-30的時(shí)候用DATEDIF算的時(shí)候，少了一個(gè)月，道理就是在與：6-30,9-30，日期數(shù)30都比12-31里的31小，正式因?yàn)檫@個(gè)原因的存在DATEDIF這個(gè)函數(shù)變的不完美，但是我們很慶幸的這只能算是一個(gè)瑕疵，實(shí)際上，月末最后一天入賬固定資產(chǎn)的可能性很小，一般的企業(yè)，28號(hào)以前就要關(guān)賬，有的報(bào)表都要出了。但是做審計(jì)，或者做財(cái)務(wù)的時(shí)候，如果你的報(bào)表截止日不是以31號(hào)結(jié)尾的，那么你就要看看，固定資產(chǎn)明臺(tái)帳里面有沒(méi)有是帶31號(hào)日期入賬的，比如7-31,8-31，等等都是有影響的，萬(wàn)一有這樣子的情況，沒(méi)有關(guān)系，在EXC表的右邊加一個(gè)備注列，比如有31號(hào)入賬的后面標(biāo)注為1，然后篩選出來(lái)，月份數(shù)后面加1個(gè)月就可以了，也不麻煩。3、求和函數(shù)（SUM,SUMIF,SUMPRODUCT）求和的公式在EXCEL中是用的最多的，主要有以上三種，為了便于三者的舉例子，以銀行借款為例說(shuō)明，在以下EXCEL表中，基礎(chǔ)數(shù)據(jù)列示如下：（單位：萬(wàn)元） 序號(hào) A B C1 光大銀行長(zhǎng)期借款 100 2 工商銀行 短期借款 503光大銀行 短期借款404 工商銀行長(zhǎng)期借款 50 5光大銀行短期借款 60 6 C6 如果求該公司期末短期借款余額合計(jì)，很簡(jiǎn)單，在C6里輸入=SUM(C1：C5)就可以了，記得中間是冒號(hào)，不是逗號(hào)，如果是=SUM(C1，C5）那么就變成C1+C5，不是C1至C5的合計(jì)數(shù)了。 如果要你求光大銀行的期末借款余額是多少？上面只舉5個(gè)銀行賬號(hào)，光大只有3個(gè)賬號(hào)，我見(jiàn)過(guò)的最多的銀行賬號(hào)有97個(gè)的，那要是手動(dòng)是相當(dāng)麻煩的，5個(gè)你可以手動(dòng)算，那么97個(gè)呢？當(dāng)然你可以用篩選的方式做，把名字都統(tǒng)一起來(lái)，也可以，一個(gè)個(gè)的篩選，如果它公司有20家銀行你要篩選20次，如果發(fā)現(xiàn)最后總數(shù)不對(duì)，你又得重新篩一次，很麻煩，效果也不好。 這時(shí)就可以用SUMIF 這個(gè)函數(shù)，SUMIF函數(shù)可對(duì)滿足某一條件的單元格區(qū)域求和，那你可以在C6單元格里面輸入 =SUMIF(A1：A5，光大銀行， C1：C5)，簡(jiǎn)單解釋下就是，你要把A1到A5中，含光大銀行的特征值選擇出來(lái)，然后對(duì)其在C1至C5的系列中求和。這個(gè)公式中依次為提供邏輯判斷依據(jù)的單元格區(qū)域，區(qū)域中的特征值，實(shí)際求和的單元格區(qū)域。 知道這個(gè)公式的用法后，其實(shí)它還可以運(yùn)用在：比如職工薪酬的分配上，管理人員薪酬，在建工程人員薪酬，銷售人員的薪酬，薪酬的分配；還有就是累計(jì)折舊的攤銷，可能有很多很多固定資產(chǎn)，攤銷后進(jìn)入不同的成本費(fèi)用，要你計(jì)算出折舊計(jì)提到管理費(fèi)用的金額，這個(gè)公式也是很友好的，用的最多的就是，我們?cè)谧鰧徲?jì)調(diào)整的時(shí)候，可能會(huì)對(duì)同一個(gè)會(huì)計(jì)科目，借、貸方調(diào)整很多次，特別是未達(dá)賬項(xiàng)的時(shí)候，或者在做試算平衡表的時(shí)候，如果沒(méi)有這個(gè)公式，你用手動(dòng)是很吃虧的。 最難的就是第三個(gè)求和公式了，要你求光大銀行短期借款的期末余額合計(jì)數(shù)，實(shí)際上現(xiàn)在就有兩個(gè)特征值了，這個(gè)時(shí)候就可以輸入 =SUMPRODUCT(A1:A5=光大銀行)*(B1:B5=短期借款)*C1:C5)這個(gè)公式實(shí)際上是基于 SUMPRODUCT(條件1)*(條件2)* (條件3) *(條件n)*某區(qū)域)這個(gè)語(yǔ)法，里面你隨便加條件，我們的這里只有兩個(gè)條件，就是A1:A5=光大銀行，B1:B5=短期借款，如果以后碰見(jiàn)有多條件的了，只要按照這個(gè)套就是，注意：英文的雙引號(hào)，外面的那個(gè)大括號(hào)不要忘記，最后的那個(gè)“某區(qū)域”不要單獨(dú)用括號(hào)括起來(lái)。 4、if函數(shù) if函數(shù)是個(gè)判斷的函數(shù)，比如，翻譯出來(lái)就是“（如果是這么樣，那么怎么樣，否則呢”）比如在上列中，我隨便輸入=if（C1C2，1，O）,這個(gè)公式翻譯過(guò)來(lái)就是（如果C1C2，那么顯示為1，否則顯示0），這個(gè)函數(shù)的一個(gè)很大運(yùn)用可以做很多判斷，比如我們做報(bào)表的時(shí)候經(jīng)常要碰見(jiàn)的負(fù)值重分類，這時(shí)候就可以用了，“（如果是=0，那么是1，0）”，然后你把等于0的篩選出來(lái)，就是要重分類的科目余額了。 5、LEFT、RIGHT函數(shù)， 這個(gè)兩個(gè)函數(shù)一般的是用的很少，它主要是用來(lái)取數(shù)字的，但是在審計(jì)中運(yùn)用的比較多，比如，有一家企業(yè)的業(yè)務(wù)員很多，經(jīng)常借支差旅費(fèi)，財(cái)務(wù)人員又沒(méi)有在其他應(yīng)收款的帳套下設(shè)置到個(gè)人的明細(xì)科目，如：其他應(yīng)收款-李某，其他應(yīng)收款-張某，但是在摘要里注明了是誰(shuí)誰(shuí)借了錢(qián)，比如記錄“李某借支差旅費(fèi)”，“張某某借支業(yè)務(wù)招待費(fèi)”，那如果到月末了，領(lǐng)導(dǎo)要你說(shuō)出李某、張某在公司借了多少錢(qián)，這個(gè)時(shí)候你應(yīng)該怎么辦？顯然如果業(yè)務(wù)量大的話經(jīng)常借支，你不可能一筆筆的算，這個(gè)時(shí)候LEFT函數(shù)就可以幫你忙了。你首先從財(cái)務(wù)軟件中，導(dǎo)出其他應(yīng)收款的序時(shí)賬，并在EXC中整理如下：在D列輸入公式 A B C D 備注比如 摘要 借方 貸方1 張某借支招待費(fèi) 1000=left（A1,2） 顯示出張某的名字 2李某某借支招待費(fèi)200 =left（A2,2） 顯示出李某的名字3 張某還借支 400=left（A3,2） 顯示出張某的名字4張某借支差旅費(fèi)500=left（A4,2） 顯示出張某的名字 然后你再刷選出張某，如果沒(méi)有期初數(shù)，你借方合計(jì)減去貸方合計(jì)，就可以了，如果有期初數(shù)，你在把上期的賬倒出來(lái)，再這么算一次，直到算到張某最開(kāi)始借的那筆錢(qián)。要注意的是，括號(hào)里面的2是取的兩個(gè)字節(jié)，我們D2單元格里實(shí)際上沒(méi)有把“李某某”的名字顯示完整，只顯示兩個(gè)字節(jié)，“李某”，如果你要把所有人的名字都顯示出來(lái)，那么一開(kāi)始的時(shí)候你用公式=left（A1,3），這樣子名字中是3個(gè)字都出來(lái)了，但是三個(gè)字以下的多了一個(gè)字，這時(shí)你把不多的人篩選開(kāi)，對(duì)多的那些人，再做一次取數(shù)，這時(shí)取左邊兩位就可以了。 right的道理也是跟left一樣，只是right從右邊取而已，你需要的東西是在右邊。比如中華人民共和國(guó)北京市、中華人民共和國(guó)上海市，你只要后面那三位就可以用right函數(shù)！6、VLOOKUP函數(shù)， VLOOKUP函數(shù)是一個(gè)非常重要的查詢函數(shù)，在審計(jì)中運(yùn)用的非常廣泛！它的語(yǔ)法是 =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)解釋下：參數(shù)簡(jiǎn)單說(shuō)明輸入數(shù)據(jù)類型lookup_value要查找的值數(shù)值、引用或文本字符串table_array要查找的區(qū)域 數(shù)據(jù)表區(qū)域col_index_num返回?cái)?shù)據(jù)在區(qū)域的第幾列數(shù) 正整數(shù)range_lookup精確匹配 TRUE（或不填） /FALSE 這個(gè)函數(shù)的運(yùn)用，我舉個(gè)例子，比如每個(gè)月給公司的員工發(fā)工資的時(shí)候，我們要做一張工資表，假如公司的員工的薪酬由基本工資和獎(jiǎng)金構(gòu)成，問(wèn)題就出來(lái)了，通常獎(jiǎng)金和基本工資不是一個(gè)人做的，做在了兩張表里了，一張是基本工資表，一張是獎(jiǎng)金表，并且這兩張表里面的員工的順序全部對(duì)不上，也就是說(shuō)你不能從獎(jiǎng)金表里直接貼數(shù)據(jù)來(lái)加在基本工資里面構(gòu)成應(yīng)發(fā)工資總額。如果一個(gè)企業(yè)上萬(wàn)員工，你手動(dòng)幾乎不可能！這個(gè)時(shí)候VLOOKUP伸出了援助之手！ lookup_value 你可以設(shè)置你要找的特征值，像如果沒(méi)有同名的部門(mén)就可以以名字作為特征值，那么企業(yè)你最好以員工號(hào)，反正要是唯一的特征！table_array你就填寫(xiě)你獎(jiǎng)金表里有數(shù)字的那個(gè)區(qū)域，col_index_num 返回?cái)?shù)據(jù)在區(qū)域的第幾列數(shù)，就是填你獎(jiǎng)金那張表里，獎(jiǎng)金金額所在的列數(shù)，這個(gè)時(shí)候要注意，這個(gè)是相對(duì)列數(shù)，不是絕對(duì)列數(shù)，比如，你的獎(jiǎng)金表里，B列是員工名稱 C列是金額，那你只能填相對(duì)列數(shù)2，不能填絕對(duì)列數(shù)3，不然的話，VLOOKUP找不到，range_lookup為一邏輯值，指明函數(shù) VLOOKUP 查找時(shí)是精確匹配，還是近似匹配，一般你填個(gè)1就可以。 VLOOKUP實(shí)際上運(yùn)用的最廣泛的地方，是算賬齡，比如你做審計(jì)對(duì)于應(yīng)收款項(xiàng)的期初余額進(jìn)行審計(jì)時(shí)，或者做財(cái)務(wù)，清理往來(lái)款項(xiàng)時(shí)，要一直往前面追，通常我們的帳套是按年度的，每一年都有一個(gè)應(yīng)收款項(xiàng)明細(xì)表，這個(gè)時(shí)候就要運(yùn)用這個(gè)函數(shù)把他們歸集在一張表上，你才可能對(duì)其計(jì)算賬齡，測(cè)算壞賬。7、數(shù)據(jù)透視表和數(shù)據(jù)透視圖的作用， 它是一個(gè)非常強(qiáng)大的工具，特別是對(duì)于我們財(cái)務(wù)人員來(lái)說(shuō)，比如我上面列舉的例子3，求光大銀行的短期借款合計(jì)，那么要你求所有銀行的短期借款合計(jì)呢？例5里只要是求出張某的借支余額，那么要你求出所有的員工的借支余額呢？相當(dāng)于分類求和的，但是數(shù)量特別多，用簡(jiǎn)單的分類匯總無(wú)法有效實(shí)現(xiàn)的時(shí)候，那么可以在數(shù)據(jù)-數(shù)據(jù)透視表和數(shù)據(jù)透視圖中完成，在布局里面設(shè)置你要求內(nèi)容，比如求和，求個(gè)數(shù)，等等。那么這個(gè)數(shù)據(jù)透視表和數(shù)據(jù)透視圖，主要運(yùn)用于往來(lái)科目的匯總，比如在實(shí)務(wù)中，應(yīng)付賬款科目下面可能有兩個(gè)二級(jí)科目，分別是應(yīng)付賬款-暫估，應(yīng)付賬款-來(lái)票，然后下面再設(shè)置三級(jí)子目，到某某單位，那么企業(yè)之所以這樣分，主要是出于對(duì)進(jìn)項(xiàng)稅的考慮，很清楚的就可以看到某一個(gè)單位，比如A單位，我要應(yīng)付A單位多少錢(qián)，其中來(lái)票了的要付多少，暫估的要付多少，這樣的核算，便于對(duì)每一個(gè)供應(yīng)商的管理，但是不便于從編制報(bào)表的角度去做，比如這么多單位，你要編制一張科目余額表，特別是做審計(jì)的時(shí)候要你對(duì)一些供應(yīng)商往來(lái)發(fā)函，怎么辦？每一個(gè)單位都在兩個(gè)二級(jí)子目下有往來(lái)款，這時(shí)你不可能一個(gè)個(gè)單位在兩個(gè)明細(xì)中加總，如果是一個(gè)制造企業(yè)，零件采購(gòu)非常大，供應(yīng)商過(guò)萬(wàn)，所以這個(gè)時(shí)候你就要用到數(shù)據(jù)透視表盒數(shù)據(jù)透視圖，這里要注意一家單位在兩個(gè)子目下，名稱要完全一致，不然也是加不完整的，但是總數(shù)不會(huì)錯(cuò)。 8、COUN