[心得] EXCEL技巧之對帳(網路銀行適用)

看板Chat82gether (轉讓合購)作者HOWARDSHEN (效率->省時間->省錢)時間13年前 (2012/02/05 12:25)推噓13(13推 0噓 8→)

留言21則, 14人參與討論串1/1

希望對一些大團的主購能有所幫助，增加作業效率，減少錯誤率，加速整團的速度！！　昨天看到J大四百多人內褲團，有感而發，如果要對起帳來...金恐怖@@ 因此發表這一篇，給各位主購參考：優點：快速知道誰有匯款、誰沒有。缺點：如果團員格式錯誤會對不到。或是有些網路銀行會在帳號處打***(馬賽克) 可能要花更多技巧來快速對帳。 ======================================================================== 【對帳VLOOKUP函數】看到函數先不用擔心，excel裡面很多計算公式都稱為函數，其實如果了解，這兩個幾乎是相等的東西....(不談太深入的數學了) 回到一開始的表格，現在必須先做一件事情，就是把ID、總金額各別列出： │ Ａ │ Ｂ　│ Ｃ │　Ｄ │ ─┼─────┼────┼────┼────┼ 1│ ptt ID │ 總金額 │匯款金額│ 金額差 │ ─┼─────┼────┼────┼────┼ 2│HOWARDSHEN│ 558 │ ─┼─────┼────┼────┼────┼ 3│ PTTA │ 358 │ ─┼─────┼────┼────┼────┼ 4│ PTTB │ 258 │ ─┼─────┼────┼────┼────┼ 5│ PTTC │ 358 │ ─┼─────┼────┼────┼────┼ 6│ PTTD │ 1064 │ ─┼─────┼────┼────┼────┼ <網路銀行資料：(郵局為例)> 把往銀資料複製到excel上... 左邊放帳號，右邊是金額 (隨便打的，如果相符純屬巧合) 如果出現亂碼，例如I2欄的帳號變成2.14258E+12，請點選右鍵，找到儲存格格式，找左列的的"文字"，然後確認。最好一開始就先設定好~ │ I │ J │ ┼───────┼──┼ 1│02142581965014│358 ┼───────┼──┼ 2│00325740059188│285 ┼───────┼──┼ 3│02358400008004│10 可是這樣一般對不到，因為主購都是要求填上匯款後五碼(我是要求後七碼) 因此，必須要動些手腳。選取I欄，然後找到EXCEL最上方一排的"資料"，選"資料剖析" ，跑出一個方塊，選第二個"固定寬度"，然後下方的"預覽分欄結果"去把豎線移到帳號中間，然後下一步，欄位的資料格式請選"文字"。就會變成這樣： │ I │ J │ K ┼────┼────┼──┼ 1│0214258 │1965014 │358 ┼────┼────┼──┼ 2│0032574 │59188 │285 ┼────┼────┼──┼ 3│0235840 │8004 │10 等等就是對J欄後七碼(有零會消失，所以才要填後七碼，避免剩下個位數)，與K欄金額。 <團員回條：> │ Ｅ │ Ｆ　│ Ｇ │ Ｈ │ ─┼─────┼────┼────┼───┼ 1│ ptt ID │匯款金額│ 後七碼 │實匯款│ ─┼─────┼────┼────┼───┼ 2│ PTTD │ 1064 │1508564 │ #N/A │ ─┼─────┼────┼────┼───┼ 3│ PTTA │ 358 │1965014 │ 358 │ ─┼─────┼────┼────┼───┼ 4│ PTTB │ 258 │59188 │ 285 │ ─┼─────┼────┼────┼───┼ 對帳的第一步驟：先確認團員回條和網銀相符(真的有入款) 函數簡介：=VLOOKUP(比對來源,比對目標,顯示內容欄位,正確度) ※解釋： 1) 比對來源一般是一整欄資料，像是整團的明細表中的ID。 2) 比對目標就是大海撈針(誤)，就像是回條中的資料，一般是一個範圍。 3) 顯示內容欄位就是你可以設定，當你比對成功時，要顯示"2)比對目標" 中的哪一欄，例如你對到帳，可以顯示網銀中的實際匯款金額等等。 4) 正確度 FALSE是完全正確。如果是TRUE是部分正確。不過我只會前者，後者都會出現怪怪的結果.... 在H2輸入 =VLOOKUP(G2,$J$1:$K$3,2,FALSE) ，ENTER。然後一樣下拉，整排就會如你現在看到的，實匯款(H2)就會出現從網銀顯示的金額。也就是K欄的資料。 #N/A表示查無此款項。你可以用匯款金額-實匯款，去看看有沒有人多匯或少匯。例如PTTB板友就多匯了285-258=27元如果都對款完畢。可以先把#N/A的那一欄刪除。進入到下一個步驟... │ Ｅ │ Ｆ　│ Ｇ │ Ｈ │ ─┼─────┼────┼────┼───┼ 1│ ptt ID │匯款金額│ 後七碼 │實匯款│ ─┼─────┼────┼────┼───┼ 2│ PTTA │ 358 │1965014 │ 358 │ ─┼─────┼────┼────┼───┼ 3│ PTTB │ 258 │59188 │ 285 │ ─┼─────┼────┼────┼───┼ 對帳的第二步驟：接下來要把有匯款的人KEY IN到一開始的明細表，可以在C2欄輸入 =VLOOKUP(A2,$E$2:$H$3,4,FALSE) ，ENTER。然後一樣下拉，整排就會如你現在看到的，匯款金額(C2)就會出現從H欄複製來的金額。一開始的明細表變成如下： │ Ａ │ Ｂ　│ Ｃ │　Ｄ │ ─┼─────┼────┼────┼────┼ 1│ ptt ID │ 總金額 │匯款金額│ 金額差 │ ─┼─────┼────┼────┼────┼ 2│HOWARDSHEN│ 558 │ ─┼─────┼────┼────┼────┼ 3│ PTTA │ 358 │ 358 ─┼─────┼────┼────┼────┼ 4│ PTTB │ 258 │ 285 27 ─┼─────┼────┼────┼────┼ 5│ PTTC │ 358 │ ─┼─────┼────┼────┼────┼ 6│ PTTD │ 1064 │ ─┼─────┼────┼────┼────┼ 這樣應該都了解吧~"~ 所以團員們~回條填錯就........... -- 看完後請記得給原po一個鼓勵喔～歡迎分享看過之後的感想不知道推什麼的話 ^把下面亮白色的文字直接複製貼上按enter就可以了 X1洽特就溫馨分享謝謝你! -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 114.42.172.253 ※ 編輯: HOWARDSHEN 來自: 114.42.172.253 (02/05 12:28)

推

waitend

02/05 12:27, , 1^F

02/05 12:27, 1^F

推

fox198712

02/05 12:35, , 2^F

02/05 12:35, 2^F

推

chsyi

02/05 12:35, , 3^F

02/05 12:35, 3^F