EXCEL在審計中的運用暨審計工作對EXCEL最基本的要求.doc

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