財(cái)務(wù)資源巧編EXCEL票據(jù)程序顯奇效以及應(yīng)用注(doc )

1、巧編excel票據(jù)程序顯奇效實(shí)施票據(jù)審計(jì)，掌握票據(jù)收費(fèi)類型、確定收入總額、確定票據(jù)是否全部入賬、有無重復(fù)入賬、有無外購票據(jù)、有無未入賬票據(jù)、有無票據(jù)存根聯(lián)與記賬聯(lián)金額不符、有無私留票據(jù)不交審計(jì)組校驗(yàn)、有無重大違紀(jì)違規(guī)情況發(fā)生等情況，長期以來，因其牽涉面廣、工作量大、任務(wù)繁重、方法落后等因素，成為廣大審計(jì)工作者想做而難做的一塊骨頭。為了更好地解決這個(gè)難點(diǎn)，啃動(dòng)這個(gè)骨頭，南京市大廠區(qū)審計(jì)局，充分應(yīng)用excel直觀簡便、易學(xué)易用、功能強(qiáng)大的特點(diǎn)，為審計(jì)工作服務(wù)，編制出了功能豐常實(shí)用的集票據(jù)清理、收入?yún)R總、自動(dòng)較驗(yàn)、問題分析于一體的多功能票據(jù)審核統(tǒng)計(jì)實(shí)用程序模塊，并形成了excel模板，實(shí)施對(duì)票據(jù)的全

2、方位自動(dòng)操作，收到了非常好的效果。在專案審計(jì)中，該局一次性清理校驗(yàn)審計(jì)專案對(duì)象五年中使用的票據(jù)74本（計(jì)3670張），共查出未入賬票據(jù)18本（計(jì)185張），未入賬金額441萬元，并將涉案人員和審計(jì)結(jié)果移送紀(jì)檢、檢察機(jī)關(guān)作進(jìn)一步處理，現(xiàn)介紹這一方法供大家參考。一、設(shè)計(jì)思想通過對(duì)票據(jù)存根聯(lián)與記賬聯(lián)這二個(gè)最基本資料的輸入（不必一一對(duì)應(yīng)），來達(dá)到自動(dòng)統(tǒng)計(jì)開票總額，分別匯總收入與往來，自動(dòng)列出已開票未入賬票據(jù)的金額和票據(jù)號(hào)碼，查出有無重復(fù)入賬的情況，查出有無票據(jù)存根作廢中的缺頁反映在入賬憑證中的情況，查出有無私留票據(jù)存根未交審計(jì)組校驗(yàn)而在賬簿、憑證中有部分單據(jù)反映卻反映不全的情況等，從而以最快的速度，找

3、出突破口，完成審計(jì)任務(wù)。二、設(shè)計(jì)表頭項(xiàng)（文件標(biāo)題和制表日期占第1、2行）行數(shù)abcde3票據(jù)存根聯(lián)基本情況及分析4票據(jù)號(hào)碼開票人是否作廢存根金額開票時(shí)間56合計(jì)fghij（續(xù)）票據(jù)存根聯(lián)基本情況及分析錄入校驗(yàn)作廢號(hào)碼存根聯(lián)備注已開未登號(hào)碼已開未登金額klmnop票據(jù)記賬聯(lián)基本情況及分析登賬票號(hào)登賬金額憑證號(hào)碼存根聯(lián)記賬聯(lián)收入往來合計(jì)qrst（續(xù)）票據(jù)記賬聯(lián)基本情況及分析票據(jù)登入憑證次數(shù)登入憑證的作廢票號(hào)碼私留票據(jù)未交入賬票據(jù)說明三、具體運(yùn)用靈活運(yùn)用excel的絕對(duì)、相對(duì)地址，豐富的求和函數(shù)、條件函數(shù)和數(shù)組公式，并加以綜合匯集，以實(shí)現(xiàn)票據(jù)程序模塊的全部功能。程序?qū)⑵睋?jù)分成二大塊：一是票據(jù)存根聯(lián)基

4、本情況及分析；二是票據(jù)記賬聯(lián)基本情況及分析。通過對(duì)票據(jù)這二塊數(shù)字的錄入，并根據(jù)程序反映的問題，運(yùn)用excel提供的排序功能（建議采用倒排序），生成多種“問題表”，以供進(jìn)一步核實(shí)查詢和定性處理。各項(xiàng)內(nèi)容具體功用和編制公式說明如下：（一）票據(jù)存根聯(lián)基本情況及分析1、票據(jù)號(hào)碼（a列）。指要求被審計(jì)單位在指定時(shí)間內(nèi)全部上交供審計(jì)組繳驗(yàn)的票據(jù)存根聯(lián)。屬必輸項(xiàng)目。本列支持無序錄入，可允許多人錄入數(shù)字后粘貼或疊加過來，排列次序不限，但發(fā)票號(hào)碼要與金額相對(duì)應(yīng)。2、開票人（b列）。指票據(jù)存根聯(lián)的開票落款人。非必輸項(xiàng)目。但建議錄入，對(duì)有問題票據(jù)可迅速分清責(zé)任。3、是否作廢（c列）。以三聯(lián)齊全為準(zhǔn)，對(duì)三聯(lián)不全的，以

5、開給對(duì)方的發(fā)票聯(lián)有無注明“作廢”字樣為準(zhǔn)。本列對(duì)作廢票據(jù)屬必輸項(xiàng)目。作廢的填“是”，不作廢的不填。4、存根金額（d列）。指發(fā)票存根聯(lián)金額，以元為單位，保留二位小數(shù)。屬必輸項(xiàng)目。5、開票時(shí)間（e列）。指發(fā)票存根聯(lián)開具的時(shí)間。非必輸項(xiàng)目。但建議錄入，特別是在票據(jù)數(shù)量很多或時(shí)間跨度較大的情況下，可方便查找。6、錄入校驗(yàn)（f列）。指對(duì)錄入人員錄錯(cuò)票據(jù)、產(chǎn)生票據(jù)重號(hào)的情況進(jìn)行校驗(yàn)的提示。本列由系統(tǒng)自動(dòng)完成，不需錄入，如重號(hào)則顯示“重號(hào)”字樣。公式：=if（countif（a$7：a$5006，a7）=2，“重號(hào)”，0）7、作廢號(hào)碼（g列）。本列由系統(tǒng)自動(dòng)完成，不需錄入。公式：=if（c7=“是”，a7，

6、0） 為加強(qiáng)視覺效果，建議將本單元格（g7）字體加上“刪除線”格式。8、存根聯(lián)備注（h列）。非必輸項(xiàng)目。但對(duì)有疑問的票據(jù)或系統(tǒng)提示有問題的票據(jù)，建議補(bǔ)錄，以便快速定位查找。9、已開未登號(hào)碼（i列）。指已開具但未記入憑證、賬簿的發(fā)票號(hào)碼。本列由系統(tǒng)自動(dòng)生成，不需錄入。公式：=if（c7=“是”，0，if（iserror（vlookup（a7，k$7：k$5006，1，false）=a7），a7，0）10、已開未登金額（j列）。指已開具但未記入憑證、賬簿的發(fā)票金額。本列由系統(tǒng)自動(dòng)生成，不需錄入。公式：=if（i7=0，0，d7）（二）票據(jù)記賬聯(lián)基本情況及分析1、登賬票號(hào)（k列）。指已登入憑證或賬薄

7、的發(fā)票號(hào)碼。屬必輸項(xiàng)目。本列支持無序錄入，允許多人錄入數(shù)字后粘貼或疊加過來，排列次序不限，但發(fā)票號(hào)碼要與金額相對(duì)應(yīng)。2、登賬金額（收入）（l列 ）。審計(jì)人員判別屬單位收入性項(xiàng)目的填在此列。本列屬必輸項(xiàng)目。3、登賬金額（往來）（m列）。審計(jì)人員判別票據(jù)屬單位銀行賬戶之間的非收入性往來劃款的填在此列。屬必輸入項(xiàng)目。4、登賬金額（合計(jì)）（n列）。是登賬金額（收入）與登賬金額（往來）之和，本列由系統(tǒng)自動(dòng)生成，不必錄入。公式：=sum（l7：m7）5、憑證號(hào)碼（o列）。指記賬憑證號(hào)。建議格式：年/月/憑證號(hào)，年份最好填四位數(shù)，以方便排序。強(qiáng)烈建議輸入，以方便排序、查找。6、存根聯(lián)記賬聯(lián)（p列）。即發(fā)票的

8、存根聯(lián)金額大于記賬聯(lián)金額的差額，由系統(tǒng)自動(dòng)生成，不需錄入。公式： =if（iserror（vlookup（k7，a$7：a$5006，1，false）=k7），0，（vlookup（k7，a$7：d$5006，4，false）-n7）7、票據(jù)登入憑證次數(shù)（q列）。指票據(jù)有無重復(fù)登入憑證的現(xiàn)象。如被審計(jì)單位借口票據(jù)遺失而用復(fù)印件記賬造成與原件記賬重復(fù)等，程序可自動(dòng)反映出4次以上的重復(fù)登賬。本列由系統(tǒng)自動(dòng)生成，不需錄入。公式：=if（k7=0，0，if（countif（k$7：k$5006，k7）=1，0，（if（countif（k$7：k$5006，k7）=2，“登了2次”，if（countif

9、（k$7：k$5006，k7）=3，“登了3次”，if（countif（k$7：k$5006，k7）=4，“登了4次”，“4次以上”）8、登入憑證的作廢票號(hào)碼（r列）。指票據(jù)存根聯(lián)上注有“作廢”字樣，但三聯(lián)不全，審計(jì)人員根據(jù)發(fā)票內(nèi)容，對(duì)此已做了作廢錄入處理，但被審計(jì)單位卻將其中的一聯(lián)作了收入憑證入賬。本列由系統(tǒng)自動(dòng)生成，不需錄入。公式：=if（k7=0，0，if（iserror（vlookup（k7，g$7：g$5006，1，false）=k7），0，k7）9、私留票據(jù)未交（s列）。指被審計(jì)單位管理票據(jù)的人員，未在審計(jì)規(guī)定的時(shí)間內(nèi)交出全部票據(jù)供審計(jì)組校驗(yàn)，而卻在被審計(jì)單位的記賬憑證和賬簿上有部

10、分票據(jù)已反映入賬，但卻反映不全的那部分漏交票據(jù)情況。此類票據(jù)疑點(diǎn)最多、問題可能較大。本列也由系統(tǒng)自動(dòng)生成，不需錄入。公式： =if（k7=0，0，if（iserror（vlookup（k7，a$7：a$5006，1，false）=k7），“私留票據(jù)未交”，0）10、入賬票據(jù)說明（t列）。非必輸項(xiàng)目。但對(duì)有疑問的入賬票據(jù)聯(lián)或系統(tǒng)提示有問題的票據(jù)，建議補(bǔ)錄說明內(nèi)容，以快速查找問題。四、特點(diǎn)1、適用性廣。以廣大審計(jì)人員普遍采用的excel表格方式出現(xiàn)，易學(xué)易用。但錄入時(shí)的工作量較大。2、錄入簡便。只需錄入票據(jù)存根聯(lián)號(hào)碼和金額與票據(jù)記賬聯(lián)號(hào)碼和金額即可，可將任務(wù)分配給參加審計(jì)的所有人員同時(shí)錄入，以便節(jié)

11、約時(shí)間，提高效率。如先錄入票據(jù)存根聯(lián)號(hào)碼和金額，則在錄入票據(jù)記賬聯(lián)號(hào)碼后，系統(tǒng)會(huì)在同一行次的存根聯(lián)記賬聯(lián)欄自動(dòng)提供應(yīng)記賬金額，相互較對(duì)，及時(shí)糾錯(cuò)，十分便捷。3、自動(dòng)校驗(yàn)。系統(tǒng)提供相同票號(hào)的票據(jù)存根聯(lián)與記賬聯(lián)自動(dòng)校銷功能，自動(dòng)反映已開未登票據(jù)號(hào)碼和金額，自動(dòng)反映發(fā)票存根聯(lián)金額與記賬聯(lián)金額不符及差額情況，自動(dòng)反映重復(fù)登賬情況和私留票據(jù)未交情況及作廢票據(jù)入賬等情況。4、自動(dòng)統(tǒng)計(jì)。系統(tǒng)提供收入和往來款項(xiàng)自動(dòng)匯總功能，可迅速摸清被審計(jì)單位全部家底。5、自動(dòng)顯示有問題內(nèi)容。以excel表格的直觀方式，迅速顯示出被審計(jì)單位在票據(jù)管理方面的許多問題，以便審計(jì)組在較短期間內(nèi)做出判斷，查清事實(shí)。五、 編制程序時(shí)注

12、意事項(xiàng)1、本文各公式，除中文文字外，一律以西文半角方式錄入，公式中不允許有空格。2、本程序15行為表頭內(nèi)容。第6行為總合計(jì)，第6行各單元格公式如下：d6=sum（d7：d5006）； j6=sum（j7：j5006）； l6=sum（l7：l5006）； m6=sum（m7：m5006）； n6=sum（n7：n5006）； p6=sum（p7：p5006）。自第7行起為審計(jì)錄入內(nèi)容，本文只介紹了第7行公式，第7行以下，以第7行為基數(shù)，用excel的單元格填充柄填充即可，并以100本票據(jù)量為例，程序控制從第7行起至5006行止。超出數(shù)量需重新修改公式，否則統(tǒng)計(jì)有誤。3、建議用excel自身提供的工作表工具，關(guān)閉“0”數(shù)字顯示，以獲得良好的界面效果和輸出效果；建議將審計(jì)數(shù)據(jù)按列設(shè)置成不同的顏色，便于區(qū)分和查看；為避免顯示超寬，建議將各單元格內(nèi)容設(shè)為“縮小字體填充”。4、建議將程序另存為excel的模板類型文件（文件后綴名為 .xlt），以方便審計(jì)人員重復(fù)調(diào)用，彼此共享，方便存取。六、 使用程序時(shí)注意事項(xiàng)1、本程序?yàn)殚_放式系統(tǒng)，建議在使用時(shí)對(duì)工作表的公式，用excel自身提供的工作表保護(hù)功能進(jìn)行公式保護(hù)，以防在錄入時(shí)誤刪，使審計(jì)數(shù)據(jù)在運(yùn)行時(shí)出錯(cuò)；但在審計(jì)錄入完畢后，進(jìn)行排序、篩選前，如工作表處于保護(hù)狀態(tài)，則應(yīng)先解除保護(hù)，再進(jìn)行排序、篩選。2、在先