24周年

財稅實務 高薪就業(yè) 學歷教育
APP下載
APP下載新用戶掃碼下載
立享專屬優(yōu)惠

安卓版本:8.7.20 蘋果版本:8.7.20

開發(fā)者:北京正保會計科技有限公司

應用涉及權限:查看權限>

APP隱私政策:查看政策>

HD版本上線:點擊下載>

淺談EXCEL軟件在審計實務中的運用

來源: 張宇 編輯: 2009/03/03 09:15:46  字體:

  【摘要】 本文以審計實務為背景,通過介紹Excel與Word軟件的銜接、共享工作簿、公式函數(shù)和隨機數(shù)發(fā)生器的運用,以實現(xiàn)提高審計工作效率、解決實際困難的目的,達到事半功倍的效果,對于目前的審計實務工作具有一定的參考應用價值。

  【關鍵詞】 Excel軟件;審計實務運用;公式函數(shù);隨機數(shù)發(fā)生器

  談到Excel軟件,大家可能都十分熟悉,因為它是審計工作的好幫手,其使用頻率遠遠超過了其他辦公類軟件。隨著審計工作電算化程度的不斷提高,無紙化的辦公模式必將成為未來的發(fā)展趨勢。但僅就目前而言,我們在日常審計工作中經常使用的Excel軟件功能通常還局限在加減乘除的簡單運算,常使用的也僅是SUM、AVERAGE、IF等一些較為簡單的公式函數(shù)。筆者將在本文中介紹一些平時使用率相對較少,而一旦掌握后將大幅提高審計工作效率的Excel功能。

  一、Excel與Word軟件的超銜接

  在出具審計報告時,若需修改word版財務會計報告附注,每位審計工作者一定十分頭疼。手工修改既繁瑣又容易出錯。不但要花費大量時間,還增加了校對的工作量。那么,是否能夠在Excel審定數(shù)據(jù)確定后,就自動生成Word版的財務會計報告附注呢?筆者認為,通過運用Excel的自動運算功能來避免手工計算的錯誤,同時,通過Excel與Word軟件之間建立數(shù)據(jù)銜接引用,可大幅度地簡化財務會計報告附注的修改過程,提高審計的工作效率。其實,自 Microsoft Office 2002版開始,已增加了Excel與Word軟件的數(shù)據(jù)銜接功能。當在Word報告附注中粘貼Excel數(shù)據(jù)表格時,其右下腳會出現(xiàn)選擇性粘貼菜單按鈕,只需選中“保留源格式并銜接到Excel”即可。如圖1所示。

  運用該方法制作的表格,當被選中時,背景色呈灰色。若單擊鼠標右鍵,列示的菜單條中會增加“更新銜接”的功能。通過該“更新銜接”功能,就能實現(xiàn)Excel與Word的數(shù)據(jù)更新銜接,如圖2所示。

  系統(tǒng)的默認銜接狀態(tài)是“自動銜接”到Excel,當Word文件中銜接至Excel的表格較多時,通常打開該文件速度會較慢。上市公司的財務會計報告附注表單信息量往往較大,這一點就顯得尤為明顯。所以,筆者建議使用“手動銜接”設置(單擊鼠標右鍵,彈出如圖2的菜單條,選中“銜接的 工作表 對象”→“銜接…”),彈出“銜接”菜單界面,如圖3所示。

  我們在“所選銜接的更新方式”中將默認的“自動更新”變更為“手動更新”方式。這樣Word文檔與Excel文件并非時刻保持數(shù)據(jù)更新,不必占用 “寶貴”的內存,可提高文檔的操作運行速度。

  熟練掌握該方法后,除了財務會計報告附注外,盡職調查、資產評估等業(yè)務,凡是在Word文檔中需要摘抄EXCEL數(shù)據(jù)的工作都將有其用武之地。目前,實務中出具審計報告的大致工作流程是:編制審計底稿→合并報表→編寫審計報告。報告附注中有大量數(shù)據(jù),通過采用上述銜接方法后可大幅度地縮短工作時間。同時,Word文檔與Excel文件進行銜接的準備工作,可不必安排在審計完成階段進行,完全可以提前進行準備。通過對現(xiàn)有工作流程的再造,將原先摘抄數(shù)據(jù)的工作時間前置,不僅提高了審計工作效率,還為緊張的年審工作爭取了寶貴的時間。

  二、共享工作簿

  對審定單體報表進行合并工作,是每位審計項目負責人都十分熟悉的。當母公司的下屬子公司較多時,合并工作往往需要多位審計員一起分工配合完成。實務中,通常按照各人所分配的工作分頭進行,當某人需要修改部分內容時,往往需要更新所有人手中的Excel文件。當分工的人員較多時,項目負責人需要對每位審計員手中的文檔更新工作進行時刻監(jiān)控,否則就容易造成不同的更新內容存儲在不同的文件中,經多次修改后容易出現(xiàn)混亂的現(xiàn)象,最后甚至項目負責人都難以區(qū)分哪份文檔系“最終稿”。

  為防止更新內容混亂,解決上述問題的辦法通常是采用串聯(lián)式的工序分配方法。但是,審計工作通常存在一定的時間限制要求,所以,實務中大家往往只能采用并聯(lián)式的工序分配方法,即“分頭進行、同時開工”。那么,是否有避免并聯(lián)式作業(yè)產生混亂情況的辦法?

  筆者向大家介紹Excel軟件中的“共享工作簿”功能。由于并聯(lián)式作業(yè)系“分頭進行、同時開工”,我們可讓合并審計組成員連接在一個局域網中(當然,隨著電腦配置的不斷提高,無限網卡也已成為大多數(shù)電腦的基本配置,構建一個無線局域網已不再是難事)。由項目負責人打開一個Excel合并報表附注文件,然后單擊菜單欄中的“工具”→“共享工作簿…”,并在彈出的“共享工作簿…”菜單界面中,選中“允許多用戶同時編輯,同時允許工作簿合并”單選框。這時,局域網內的其他成員就可以同時編輯該合并文件了。由于,所有的更新內容系保存在一個相同的Excel文件中,電腦將累計保存局域網中每位審計員對該文件的信息修改內容,以保證該文件永遠是“最終稿”。當不同的審計員對同一單元格內容進行修改時,該單元格右上方將出現(xiàn)最近次修改者名字和修改時間,以提示審計員對需重復修改信息的確認,以保證修改內容的“最終性”。

  三、Excel軟件的公式函數(shù)

  如果您瀏覽過Excel的函數(shù)菜單,是否曾驚愕于其龐大的函數(shù)功能?其實,我們只需掌握其中部分函數(shù)的運用方法,就足以滿足日常審計工作中的大多數(shù)需求。除了SUM、AVERAGE、IF等常用函數(shù)外,筆者向大家介紹幾個較實用的函數(shù),掌握后可達到事半功倍的效果。

 ?。ㄒ唬¬ALUE函數(shù)

  1. 用途。

  VALUE函數(shù)的用途是將代表數(shù)字的文本字符串轉換成數(shù)字。實務中,我們常遇到從某些財務軟件引出的財務數(shù)據(jù)信息系文本型字符串,如:Orical軟件。雖然其導出后的數(shù)據(jù)表示的系數(shù)值信息,但由于是文本型字符串,所以無法直接進行運算,給審計工作帶來了諸多不便。通過使用VALUE函數(shù),可以將該文本型字符串轉換成數(shù)值型。

  2. 函數(shù)語法VALUE(text)。

  其中:text為帶引號的文本或對需要進行文本轉換的單元格的引用。比如需要將A1單元格中的文本型字符串轉換成數(shù)值型,則公式“=VALUE(A1)”即可。

  值得介紹的是,筆者發(fā)現(xiàn)在Excel中存在個小BUG。當我們選中文本型字符串單元格后,如果按CTRL+F,查找“. ”替換為“. ”的話,即可將原先帶小數(shù)點的文本型字符串轉換為數(shù)值型字符串,大大地簡化操作步驟。但是,當文本型字符串所代表的數(shù)值信息系整數(shù)時(即無小數(shù)點時),該方法則不適用。此外,運用菜單欄“數(shù)據(jù)”→“分列”的功能也可以達到該效果。

  (二)LEFT、RIGHT、LEN和FIND函數(shù)

  LEFT、RIGHT、LEN和FIND函數(shù)在這里的用途是提取會計賬簿摘要欄內填寫的數(shù)量信息。在審計實務中,常會遇見財務人員在三欄式賬簿摘要欄內填寫存貨數(shù)量的情況。當審計員擬取得數(shù)量金額式的存貨賬簿時,客戶卻提供這樣的“數(shù)量金額”式賬簿,“可遠觀,而不可褻玩焉”,讓人哭笑不得。在此,筆者介紹一套“組合拳”給大家,可方便地提取類似賬簿中的數(shù)量信息,生成數(shù)量金額式的電子賬簿,使審計員可大顯身手。具體運用如下。

  1. 函數(shù)語法LEFT(text,num_chars)。

  作用:基于所指定的字符數(shù)返回文本字符串中的第一個或前幾個字符。

  2. 函數(shù)語法RIGHT(text,num_chars)。

  作用:根據(jù)所指定的字符數(shù)返回文本字符串中最后一個或多個字符。其中,text表示要提取字符的字符串位置;num_chars表示,需要提取的字符數(shù),忽略時為1。LEFT和RIGHT函數(shù)的運用基本一致,區(qū)別在于一個從左開始提取字符串,一個從右開始提取字符串。

  3. 函數(shù)語法LEN(text)。

  作用:返回文本字符串中字符個數(shù)。其中,text表示要查找其長度的文本,空格將作為字符進行計數(shù)。

  4. 函數(shù)語法FIND(find_text,within_text,start_num)。

  作用:查找其他文本字符串 (within_text) 內的文本字符串 (find_text),并從 within_text 的首字符開始返回 find_text 的起始位置編號。其中,“find_text”表示要查找的字符串;“within_text”表示要在其中進行搜索的字符串。“start_num”表示起始搜索位置,在within_text中第一個字符的位置為1,忽略時,start_num=1。

  下面用示例說明操作。表1系某公司的部分存貨明細賬,財務已將數(shù)量記錄在摘要欄內。

  但是,由于每筆交易數(shù)量的位數(shù)不同,所以我們無法直接用RIGHT函數(shù)提取摘要欄內的數(shù)量信息。假設我們需要提取第一筆摘要欄“網絡終端購光端機:8M 120 000 ”中的數(shù)量信息“120 000”,則復合函數(shù)公式為“ =RIGHT(F2,LEN(F2)-FIND(" ",F(xiàn)2,1)) ”。拆分各公式后具體介紹如下:

  LEN(F2):測量該字符串長度,結果為19(包括空格);

  FIND(" ",F2,1):查找空格在該字符串中所處位置,結果為12;

  RIGHT(F2,7):通過LEN和FIND函數(shù)組合運用,得出需要在F7單元格中從右返回7個字符(即19-12=7),結果為“120 000”。

  因為我們系在文本型字符串中引用字符,所以得到的結果也是文本型的數(shù)值信息。我們可通過運用上述介紹的VALUE函數(shù)將其轉換成數(shù)值型字符串。同理,第二、三筆的交易均可通過編寫的復合函數(shù)由電腦自動完成。熟練掌握后,同理還可以提取處于中間位置的數(shù)量信息(提示:在上述公式基礎上再嵌套LEFT函數(shù)即可)。

  (三)VLOOKUP和CONCATENATE函數(shù)

  1. VLOOKUP 函數(shù)。

  用途:搜索表區(qū)域內首列滿足條件的數(shù)值,確定待檢索單元格在區(qū)域中的行序號,再進一步返回選定單元格的值。

  實務中,該函數(shù)的運用率很高,我們經常碰到所需的信息被存儲在不同的工作表中。比如,按照先進先出法編制應收賬款賬齡明細表時,客戶2007年度的往來增減變動明細在某個工作表內,而2006年度的往來增減變動明細卻在另外一個工作表內。如果需要分析5年賬齡,則需要收集5個工作表。由于各客戶在各工作表內所處的行次并不完全相同,為查找該客戶在不同年度內的增減變動信息,需要審計員在不同的工作表內人工查找行號位置。尤其當客戶明細較多時,查找工作量將非常巨大,并不十分經濟。為此,通過運用VLOOKUP函數(shù)便可快捷地實現(xiàn)該目標。有關客戶資料見表2、表3。

  表2為“2007年客戶明細余額”工作表,其中包含公司2007年度末各客戶應收賬款余額數(shù)據(jù)。而表3為“2006年客戶明細余額”工作表,其中包含該些客戶的2006年末余額數(shù)據(jù)和相應的銷售業(yè)務員信息。但是,由于客戶在兩個工作表中所處行次并不相同,所以人工查找粘貼的方法并不經濟(客戶數(shù)量較多時,尤為明顯)。具體運用如下。

  (1)函數(shù)語法VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。

  作用:在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值,并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。其中:

  lookup_value: 需要在數(shù)據(jù)表首列進行搜索的值。本例中,系A列客戶的名稱。

  table_array:需要搜索數(shù)據(jù)的信息表。本例中為“2006年客戶明細余額”工作表中A2至C5單元格范圍。

  col_index_num:滿足條件的單元格在數(shù)組區(qū)域table_array中的列序號。在本例中,需在“2007年客戶明細余額”工作表C列中返回“2006年客戶明細余額”工作表中的B列2006年末余額信息,故屬表3中的第2列信息,應填寫“2”。同理,表“2007年客戶明細余額”工作表中D列需返回“2006年客戶明細余額”工作表C列中的銷售員信息,故屬表3中的第3列信息,應填寫“3”。

  range_lookup:在查找時,是否需要精確匹配。如果為FALSE,則大致匹配,如果為TRUE或忽略,則精確匹配(并區(qū)分全/半角)。在實務中,一般選擇大致匹配,即“FALSE”。

 ?。?)具體操作程序。

  首先,在“2007年客戶明細余額”工作表的C1和D1單元格內分別粘貼“2006-12-31”和“銷售業(yè)務員”字符串,作為行標題。然后,在“2007年客戶明細余額”工作表C2單元格內使用VLOOKUP函數(shù),公式為“=VLOOKUP($A2,2006年客戶明細余額!$A$2:$C$5,2,FALSE)”,D2單元格的函數(shù)公式為“=VLOOKUP($A2,2006年客戶明細余額!$A$2:$C$5,3,FALSE)”。其中“$”符號是為了絕對引用和相對引用單元格信息,以便通過鼠標的單元格拖放功能,讓電腦自動生成C列和D列內其他行次的單元格信息。在表2和表3的基礎上,經上述操作并略作整理,便可得到表4所示結果。

  2.CONCATENATE 函數(shù)。

  用途:將多個文本字符串合并成一個。

  上述筆者已介紹了VLOOKUP函數(shù)的運用,但在實務中不同的工作表之間并非均存在唯一性的關鍵字符串(如上例為“客戶”)。那么,我們就需要將不同單元格內的信息進行合并,使其生成唯一的一個字符串。例如:在編制服裝企業(yè)存貨賬齡分析表時,獲取的明細清單內各件衣服的類別、款式、顏色、尺寸均不具有唯一性特點,可見表5。

  為了使用VLOOKUP函數(shù),我們需要自己構建一個唯一性的字符串。在本例中,我們可先在首列中插入一列,標題可稱作為“品名”,然后使用CONCATENATE函數(shù),構建唯一性的字符串。CONCATENATE函數(shù)運用如下:

  函數(shù)語法:CONCATENATE(text1,text2,…)。

  作用:將幾個文本字符串合并為一個文本字符串。其中,text1, text2, ... 為 1 到 30 個將要合并成單個文本項的文本項。這些文本項可以是文本字符串、數(shù)字或對單個單元格的引用。本例單元格A2的公式函數(shù)為“ =CONCATENATE(B2,"/",C2,"/",D2,"/",E2) ”。其中,“/”是為了以后篩選查找方便,不用也可。

  四、Excel軟件的“隨機數(shù)發(fā)生器”

  實務中,審計員通常會選擇那些金額較大,或者發(fā)生頻繁的交易作為抽樣樣本,并習慣以這些特定項目的偏差或錯報來推斷總體。按照2006年度頒布的《中國注冊會計師審計準則第1314號——審計抽樣和其他選取測試項目的方法》的相關規(guī)定,對特定項目實施審計程序的結果不能推斷至整個總體,選取特定項目并不構成審計抽樣。審計抽樣包括統(tǒng)計抽樣和非統(tǒng)計抽樣。若使用統(tǒng)計抽樣,則必須通過隨機選取樣本。那么,如何才能做到隨機抽樣呢?CPA審計教材中就曾提到過“隨機數(shù)表”;“四大”會計師事務所也曾專門開發(fā)隨機數(shù)發(fā)生器軟件,用于審計程序的隨機抽樣。那么,我們是否能夠利用Excel軟件來制作一張“隨機數(shù)表”,并使該表能夠滿足審計三級復核的要求呢?筆者給大家介紹一下Excel軟件中的“隨機數(shù)發(fā)生器”的運用。

  假設有一份存貨產成品明細清單,樣本規(guī)模為1 000項,我們已為每項產成品按其順序賦予了1至1 000的序號?,F(xiàn)我們需隨機抽取其中20個樣本量并執(zhí)行審計程序。我們通過選擇菜單欄“工具”→“數(shù)據(jù)分析”(如果您沒有找到“數(shù)據(jù)分析”選擇項,可能您的Excel中尚未安裝該功能模塊。請選擇“工具”→“加載宏…”,并安裝“分析工具庫”即可)。在“數(shù)據(jù)分析”菜單界面中,選擇“隨機數(shù)發(fā)生器”,如圖4所示。

 

  選中“隨機數(shù)發(fā)生器”項目后,單擊確定按鈕后,將出現(xiàn)如7圖5的界面。

  變量個數(shù):表示在指定輸出表中數(shù)值列的個數(shù)。即,我們需要的隨機數(shù)的組數(shù),審計實務中一般需要1組即可。

  隨機數(shù)個數(shù):在此輸入要查看的數(shù)據(jù)點個數(shù)。即在1 000個樣本規(guī)模中,需要抽取的樣本個數(shù),本例我們需抽取20個隨機樣本。

  分布:在下拉菜單中選擇用于創(chuàng)建隨機數(shù)的分布方法。如:均勻、正態(tài)、柏努利、二項式、泊松、模式、離散。在實務中,我們一般只需要在樣本清單中產生隨機序列號碼即可,所以通常選擇“均勻分布”。均勻分布,系以下限和上限來表征。其變量是通過對區(qū)域中的所有數(shù)值進行等概率抽取而得到的。

  參數(shù):在此輸入用于表征選定分布的數(shù)值。因本例的樣本規(guī)模為1 000個,所以輸入1至1000即可。

  隨機數(shù)基數(shù):在此輸入用來構造隨機數(shù)的可選數(shù)值,可在以后重新使用該數(shù)值來生成相同的隨機數(shù)。通過隨機數(shù)基數(shù),審計底稿內核老師可據(jù)此產生相同的隨機數(shù),以復核審計員所抽取樣本正確與否。此外,因隨機數(shù)基數(shù)不同,將產生不同的隨機數(shù)。審計人員可能會以此來逃避對某些抽樣樣本的審計工作,而選擇其他較為方便的樣本實施審計程序。為預防該情況的發(fā)生,隨機數(shù)基數(shù)一般應由項目負責人確定。

  輸出區(qū)域、新工作表組、新工作簿:按照審計員要求,可將產生的隨機數(shù)組列示在相應的EXCEL單元格中。

  通過上述操作步驟,Excel會在審計員制訂的輸出區(qū)域內生成相應的隨機數(shù)組。經適當整理后,如圖6所示。

  然后,審計員可結合運用本文介紹的VLOOKUP函數(shù),快捷地制作出一份存貨產成品隨機抽樣清單。

  古人云:“工欲善其事,必先利其器”。在既定的審計程序目標和有限的審計工作時間內,若想不斷地提高工作效率,就需要我們不斷地去完善和探索新的工作方式。Excel軟件具備強大的表格處理功能,筆者相信,本文所介紹的內容還僅僅是其“冰山一角”,隨著我們對Excel軟件運用的不斷深入,其必將使審計工作事半功倍,增添無窮的樂趣。

責任編輯:冠

實務學習指南

回到頂部
折疊
網站地圖

Copyright © 2000 - m.galtzs.cn All Rights Reserved. 北京正保會計科技有限公司 版權所有

京B2-20200959 京ICP備20012371號-7 出版物經營許可證 京公網安備 11010802044457號