年度稽核計畫是稽核人員在12月底前需要完成上傳的工作,許多稽核人員用word或excel表格做好了,卻煩惱如何將word或excel表格轉成上傳txt格式,使多數稽核人員只好一個字一個字比對文字,浪費不少時間。這也是我寫本文章目的,造福稽核人員了。
首先,我找一家上市櫃公司之年度稽核計畫為例,若是你是稽核朋友就拿手上之文件來做做看。大多年度稽核計畫是以下表格內容。以word或excel格式居多。
而官方要求上傳TXT格式內容如下,分成表頭及稽核項目二部份。(如下圖示)
表頭欄位說明
{[(表頭)]}:為固定之文字,必須輸入,為每筆稽核計畫項目之控制行,
本行有格式一及格式二兩種,請依下列說明擇一使用,並刪除不適用部分。
a. 公司代號,格式一:4 bytes,上市、上櫃、興櫃、及一般公開發行公司,公司代號4碼者適用,
格式二:6 bytes,證券期貨周邊事業,公司代號6碼者適用。
b. 091:年度,必須輸入3byte之民國年。
c. 0910101:預定稽核期間-起始日,必須輸入7 byte,例:0880101為民國88年1月1日。
d. 0910201:預定稽核期間-結束日,必須輸入7 byte,說明如上。
e. 000001 :稽核項目編號,最多10 bytes (英文字母/數字/符號為1 byte)。
# ,且不可有「'」(上單引號)、....等特殊符號。
稽核項目:每行最多 78 bytes,
# 行數無限制,若有多行內容,必須於每行開頭加"@",以示區別。
@xx(行數無限制,每行最多 78 bytes)
因此,我整理此文件用EXCEL處理,好處是可以用函數、資料輸入等方式,加速處理此類文字轉化作業。excel格式會以以下方式來呈現,而word格式朋友先將表格轉成excel表格,切記,表頭留最上方就好,表底就不用copy上來。
由於剛才格式有太多沒用內容,故整理表頭需要的公司上市櫃代號6174,年度102,分別在A、B欄貼入。另外,空格的欄位也是多餘的,故一併刪除不用,整理後的內容如下。
觀察「預定稽核期間」格式有文字內容,此與上傳格式不符故需要轉化成數字內容,於是,我將此範圍內容,貼至記事本,貼上內容如下,並存成TXT格式,方便EXCEL匯入資料用。
EXCEL2007之資料匯入方式為:工具列的「資料」/「從文字檔」,進入選定文字檔,請選剛才的文字檔,進入「匯入字串精靈」步驟,此時,請用「固定寬度」,目的是將年月分成二欄,尤其年度固定寬度,比較好切割。
切割畫面如下,此時,用滑鼠標示切割欄位情形。其中,匯入EXCEL格式,均用文字格式,若用通用格式,102會成了數字,比較不好處理。
切割之後,EXCEL所匯入之「預定稽核期間」成了三欄了。
依上傳格式規定,月份為2字元,但1月份只有1字元,也需要刪除「月」文字,我就用IF、LEN及CONCATENATE等函數來整理月份資料,就可以轉化月份成2位元數字格式。
另外,朋友「預定稽核期間」是有日期區隔的,如下圖。
轉化方法相同,貼至記事本,格式如下。
匯入此文字,需改為分隔符號,才可以將「~」符號移除。
移除「~」符號,請利用下圖方式移除。
將匯入欄位格式,一定要「文字」格式,否則會成數字/日期等格式,資料就不是我們要的。
匯入EXCEL之欄位內容如下。
此時,多了礙眼的「/」符號,如上一移除「~」方式,貼至記事本。
再匯入EXCEL,此時,因為有二欄,會多一TAB符號,故「分隔符號」用以下畫面來設定。
轉化日期成了四欄資料了。
再將日期欄位轉化月份/日期均為2字位格式。此時轉化函數,一樣用IF、LEN及CONCATENATE等函數來整理。
整理好的內容如下。
參考上傳格式,我依格式順序調整如下圖,增加表項、稽核項目,並將「預定稽核期間」轉化成年月日共6字元形式。轉化完成之欄位,請用複製,再用選擇性貼上,就沒有函數了。
接下來整理上下二列格式,因為,涉及比較難的內容,用分圖分步驟解說,首先,增加最前面的欄位。
在A1處,輸入1,圈選A1:A2。
圈選好了之後,先按「SHIFT」,再滑鼠點外框右下角有一正方小黑框二下,就發現複製成以下畫面。
在選定A1:A48範圍,再按「F5」鍵,進入「到」畫面,請選「特殊」鍵。
進入「特殊目標」畫面,請先選「空格」,再「確定」。
此時,EXCEL會將A1:A48的空格標示成灰色。
在A2上方按滑鼠右鍵,跳出小工具列,選「插入」。
此時,插入「整列」。
這時候,會多一些空白列出來。
再來,選A欄,按「F5」/「特殊」,進入「特殊目標」,再選「常數」,再按確定。
此時,A欄有數字被選定。
一樣用按滑鼠右鍵,跳出小工具列,選「插入」/「插入整列」。
得到間隔空列式的格式,再刪了A欄及第一列,至I欄用函數彙整上傳格式第一列及第二列文字。
彙整好了,一樣用選擇性貼上值,移除函數,刪了不要各欄,留要上傳的內容。
再選定欲上傳範圍,貼至上傳格式欄之-------------------以下內容,再檢查文字過長部份,就可以上傳了。