打造稱手的審計數據處理工具

太自紅

數據分析在審計工作中發揮著十分重要的作用，當前多數審計人員已掌握了在excel和數據庫中進行篩選比對的技能。然而現實工作環境下，審計人員面對數據，遇到最大的問題是數據格式不規范，量大難處理，如何高效處理數據考驗著計算機審計人員的能力和水平。本文將介紹筆者和同事在工作實踐中，不斷總結編寫了的一系列數據處理工具和方法，高效解決了數據收集、清洗、轉換、分拆問題，極大地提高了審計工作效率。

一、多種形式的數據收集合并。

電子表格的收集合并是工作中時常遇到的一個問題，審計現場收集的電子數據資料，因為年度和地區的不同，數據常常分散存儲在多個工作簿和工作表中，為了實現對數據的統一分析必須將數據進行歸集合并和整理。常見有以下幾種形式的合并整理：1.從選擇的工作簿中，逐個把工作表數據復制到合并工作簿的不同工作表下。2.從選擇的工作簿中，把所有工作表數據復制到合并工作簿的同一工作表下。3.預設需要提取的數據列，從工作簿中提取相關列數據后，復制到合并工作簿同一工作表下。上述工作在數據量小的情況下可以手工操作，但面對成百上千張數據表時，將變成一個十分繁重的工作。針對此種需求我們設計了比較通用的工作簿合并工具（圖1），實現了幾百張數據表可在幾秒時間內完成合并歸集。

二、根據內容分割符拆分多行的問題。

數據的格式規范決定了數據能否在數據庫中參與比對，工作常會遇到這樣的情況，被審單位為了自身工作方便，提供的資料中，一些關鍵信息使用標點符號隔開的形式合并存儲。審計中為了實現對關鍵信息的比對，需要對關鍵數據進行提取分離。如圖2中提供的產權登記信息，原數據以產權號為單位，共有產權人用標點符合隔開來組合存儲，審計對數據進行關聯分析時，產權人證件號作為一個重要信息需要做圖2中的拆分轉換才能夠參與比對。

上述轉換是數據清洗過程中非常必要的操作，EXCEL現有工具雖然提供了數據分列功能，但分列后的數據仍需要大量繁瑣整理，才能實現上圖轉換效果。為此我們用VBA編寫了分拆整理工具（圖3），在工具交互窗口下輸入需要分拆的列和分隔符號以及分拆后需要保持文本格式的數據列，最終完成上述的分拆轉換操作。

三、批量視圖構建完成數據庫的漢化翻譯。

被審計單位提供業務數據庫后，分析組一個重要工作是對數據進行翻譯轉換使數據庫具有可讀性，通過對照數據字典進行表名和字段名的轉換。創建漢化視圖是轉換的一種主要方式，實際工作中如果依據數據字典逐條編寫視圖語句，不僅耗時且難于保障轉換的準確性。最有效的方式是把數據字典按表名、中文表名、列名、中文列名四個要素規范整理后，運用工具批量生成視圖創建語句，圖4為批量生成視圖創建語句的vba代碼。

四、數據入庫后的規范清洗和靈活運用。

入庫的數據一方面需要做專門的清洗轉換，另一方面還需要靈活處理運用。對一些特殊的數據處理，數據庫提供的系統函數難于滿足，因此定義一系列處理函數顯得十分必要，我們根據工作需要定義了以下一些函數：1.身份證號的處理函數，身份證號是重要關聯比對字段，對它的處理能極大地提升數據質量。針對身份證號的函數有合法性檢測、字符串全半角轉換、隱藏的非法字符（一些既不是空格肉眼也難于觀察到的字符）清除、15位升級18位。2.字符串相似度匹配函數，一些數據無法用like 運算符關聯比對，如在使用公司名稱關聯數據時，由于數據來源的不同，公司名稱有的簡稱有的全稱，like運算符無法表達關系，可以通過計算兩個字符串的相似度來做關聯，確定相似度在一定范圍內的數據為關聯結果，然而SQl數據庫并沒有提供字符相似度計算函數，所以在結合了單個字符的匹配性和位置權重后我設計了字符串相似度函數。3.索引的批量自動建立問題，索引的建立能夠讓數據庫的比對更加高效，在索引構建時由于一些關聯字段雖然在不同數據表中但名稱一致，為了工作的快捷設計了批量構建索引的函數，極大地提高了數據的管理水平。

五、數據疑點庫的批量分發問題。

數據集中分析，疑點分散核查是當前審計的一種重要工作方式，省市審計機關對大量數據集中分析后，將多個疑點數據保存到數據庫下，分散核查時再將數據按地區按種類導出，分發到各核查小組。而手工操作將是一個繁重的工作，為此我們設計了數據庫審計疑點批量分拆導出工具（圖5），在工具中輸入數據庫連接參數連向疑點數據庫，疑點列表獲取菜單幫助選擇需要導出的數據表，導出范圍配置界面填寫好需要導出的地區，計算機根據上述配置，從數據庫自動抽取指定的地區和指定表，并以地區為單位生成工作簿，從而分拆出各個地區的疑點數據。

六、審計現場數據任意拆分的問題。

在現場組織審計工作中，電子表格的數據分拆也是時常遇到的問題，如審計組根據工作安排，需把工作簿數據按照數據內容中的審計地區和資金類別，拆分成不同的電子工作簿，交各組員開展相關工作。當地區和資金種類繁多時，從電子表格中篩選數據然后復制到不同工作表成了一項繁重工作。為簡化此項工作，我們設計了靈活的工作簿拆分工具（圖6），拆分工具能夠實現對工作簿按照自定的屬性組合（不同列中內容項組合）完成拆分，考慮了多表頭工作表情況還增加了表頭終止號選項，同時為實現把具有相同屬性的不同工作表拆分到同一工作簿下，還設計了相應的勾選項和屬性組合編號。

數據審計的道路上我們將面臨各種挑戰，只有在工作實踐中認真總結規律，充分發揮知識技能的作用，不斷創新技術方法，才能更好的迎接挑戰，以問題為導向形成實用的經驗技能，甚至固化成一勞永逸的數據處理工具，是每位計算機審計人員努力的一個方向。