若是您是在一家小公司當人資、會計,有時候想利用銀行的轉帳方式,卻因為轉換成媒體格式不會,讓你一筆一筆寫匯款單而苦惱。或是,求懂得寫媒體轉帳格式的朋友,卻看朋友忙別的事,而你不想再三央求拜託他人情況下,其實,你是可以學會此工具的,本文就是說明轉帳格式是很簡單的,不要怕仔細看會每一步驟,你就學會了。
一、轉帳格式
這是讓人亳無頭緒的一串文字,下圖就使一堆人心感畏懼,簡直是眼花瞭亂來形容。
分析上圖情形:
1.各筆資料長度一致,不可能沒有不規則的長度發生
2.各筆資料僅限文數字內容,沒有其他圖片等內容
3.有些資料內容很雷同,如NSC2010000等情形
因此,這些內容是有規則的,若你不知道規則,需要向銀行索取,才能知道資料如何轉化。
二、轉帳規則
當你向銀行索取轉帳內容,它寫的內容有些是有看沒有懂,此時你需要去問銀行,把它記錄下來,寫成以下圖示,方便日後轉化參考用。
三、手上資料
這是你平日所做的資料表,如員工薪轉帳戶、廠商匯款帳號、股東股利匯入帳戶...等,有時仍有不完整資料,你必要要到更完整內容,否則仍是匯不了款的。
有了完整資料後,你最好是上下筆檢查看看,內容呈現規則能一致性。例如:台北市、北市,要求一致內容為「台北市」,不要一時偷懶造成轉化判斷時的困難。以下是我整理的內容,供大家參考。
上述內容有沒有注意到,數字以文字格式呈現,這是因為銀行別、分行別資料是文字呈現,不是數量形式,所以小心設定好格式,以免資料少0而發生錯誤
四、分析轉帳規格內容
就用上圖格式來分析
1.系統序號:是以流水號形式產生,所以此欄資料可以用下拉式自動產生資料,或是利用向下填滿工具,以數列方法產生資料。
2.系統銀行:是固定為01710,所以直接複製貼上就可以完成此欄資料了。
3.類別:是固定為8100000,比照系統銀行資料方式來產生。
4.銀行,此為你手上資料要轉化欄位,待會再逐一解說。
5.分行:如4.銀行一樣。
6.帳號:如4.銀行一樣。
7.金額:如4.銀行一樣。
8.匯出公司別碼:這是要向銀行申請,銀行會給予代碼,也是固定內容,所以直接複製貼上就可以完成此欄資料了。
9.身分證號:此為你手上資料,直接轉化。
10.14位零字元及預留31個空白:此為固定內容,直接用複製產生即可。
因此,真正要進行轉化的資料有4.銀行、5.分行、6.帳號、7.金額,其他欄位資料,是可以很簡單完成。
看懂了轉帳格式之後,再回頭看下圖,你會很清楚最左側12文數字是系統序號,接下來5個文數字是系統銀行.....,規格了解了,看此資料就有上頭緒了。
五、進行轉化作業
真正要進行轉化的資料有4.銀行、5.分行、6.帳號、7.金額,但如何去進行呢?我來逐一說明:
1.銀行與分行
這二欄資料有共同點是,最大長度各為3、4,有些手上資料是不足長度,是需要左側需零,每筆資料才可以完整長度內容。為使內容視同數字,請將儲存格式均設為文字,否則左側0均視同消失。
我是利用LEN()函數來判斷手上資料長度是多少,因此,以B2公式值,我先用LEN(A2),來判斷手上資料長度是多少。而判斷長度之後,相對的是,需要多少0來補足,故我用了4-LEN(A2)來判斷(分行總長度是4,若是銀行總長度需要改成3)。最後,將B1儲位之一串0的字串中,利用LEFT()來取多少0出來,故公式為LEFT($B$1,(4-LEN(A2)))),其中$B$1為絕對儲存格,方便我下拉來產生公式。 所以,產生結果(B2:B14)內容,有的是00、0、空字元。
產生補0之欄位(B欄),接下來是與原本分行資料串成均為4位字元的內容(銀行則為3位字元),此技巧可利用&或是函數CONCATENATE(),原本資料在右側,左側補0之公式,以B2&A2串成,結果在C欄呈現均為長度4位字元。
2.帳號
原本帳號內容有-之內容,如下圖,去除-符號,不是逐一修改。此時,利用資料剖析方式,將分隔符號之其他項,設定成-。
之後,再將各欄位內容,逐一設定成文字格式,再按「完成」鍵。
經過了資料剖析方式,所產生內容如下圖,不就是沒有-的資料了(E欄至I欄)。
E欄至I欄之帳號資料,利用&或是函數CONCATENATE(),串成單一欄位(J欄),如下圖,公式為E2&F2&G2&H2&I2。
由於帳號長度為14碼,因此,要先判斷J欄之長度多少,公式為LEN(J2);若需了解補多少個0,公式就為14-(LEN(J2));因此,我們最終目的是串成14字元內容,不足14要補0,故K檔內容是補多少個0,公式為LEFT($B$1,(14-(LEN(J2)))),其中,$B$1是我事先做好有14字元長度0字串,不足多少來$B$1取多少0出來補。
最後是利用&或是函數CONCATENATE(),將J欄及K欄串成都是14字元長度內容,公式為K2&J2,完成內容為L欄。
3.金額
在手上原始資料之金額是數字格式,因此,匯入此資料,需要轉化成文字格式。若用「'」方式改成文字的話,會使資料多了「'」,因此,是不是有更好的方法來解決數字轉成文字呢?
我的方法是利用資料剖析就可以完成此轉換,不必寫一堆函數,步驟如下圖,首先將貨款欄圈選,再進入資料剖析,選擇「分隔符號」。
第二步驟就是按下「完成」鍵就好,再將儲存格式改為文字,就馬上就轉換文字格式了(如下圖)。
將貨款數字格式轉換為文字格式後,就可以利用LEN()函數算出貨款文字串有幾位,因此,M2儲存格之貨款文字長度公式是LEN(M2),但是,銀行要求貨款文字串總長為14(含小數二位),不足14位元要在貨款字串前補零,即12位整數+2位小數之文字字串形式。本案例之貨款均無小數問題,所以用12-LEN(M2),就知道左側補多少0,另在右側均補2位0,就是匯出貨款了。
4.其他欄位及整位欄位
其他欄位就依銀行格式要求製作,做成銀行要求的內容,細分成各項欄位,如下圖,剩下整併及生成文字檔工作了。
合併各欄位文字串方式也與之前步驟一樣,直接用&或是函數CONCATENATE(),整併如下圖,最後再利用複製及選擇性貼上/值二個步驟,轉換成欲匯出轉帳的銀行要求格式了。
六、轉換成欲匯出轉帳的銀行要求格式
將整併欄位直接選取及複製,開啟記事本,再貼上匯出內容,如下圖,此為銀行要求匯款格式,再依銀行要永檔名來命名,就可以交給銀行轉帳匯款。
七、結尾
剛接觸銀行轉帳的朋友,不要被密密麻麻文字嚇跑,先去了解銀行要求格式,再去檢視手上資料是否欠缺及格式要求是否不同,再逐一欄位進行,切莫手忙腳亂,這不會太難轉換工作,也不用到處求人幫忙,且有機密的資料也不會被其他人看見,要好好學起來,才節省人力及時間。
留言列表