[心得] 主購：對帳

[aotsukirin (小鈴)]

哈囉我又來了

最近洽特好熱鬧

剛好有看有人說什麼用VLOOKUP對帳
又有人推文說不會的

所以我又很雞婆來分享一下0.0/

上次有介紹過推文檢查懶人包 #1I4pKwHk (Chat82gether)
有興趣可以看一下:)

覺得我很笨有更有效率的方法的大大
也請多分享
但是不要鞭我T^T

-----------------------------------------------------------------------------

回到正題

我們只要用兩個簡單的公式
就可以直接篩選末五碼對帳

我怕我太笨不會講解
所以我用了一個範本，大家看著範本一邊看我講XD
http://ppt.cc/Dsm5

首先看"匯款回條"的Sheet
因為VLOOKUP是要針對帳號末五碼去抓數字
但是一般填寫匯款回條會把PTT ID放在第一欄
所以我們手動移動一下欄位，把末五碼的欄位移到最前面
這樣匯款回條的基本準備工作就完成了

再來看"郵局交易明細"的Sheet
整理一下僅留下需要的欄位
以郵局為例，對方的匯款帳號會顯示在"備註"的位置
所以我們主要留下"存款金額"和"備註"兩個欄位

黃底部分是我們要自己手動製作的地方
首先匯款帳號落落長，VLOOKUP抓不出來啦怎麼辦

介紹一個好用的公式 RIGHT
顧名思義就是從右邊抓字，因為帳號末五碼就是從右邊算起來五碼
所以使用這個公式
用法 =RIGHT(帳號的儲存格,5) 5就是總共取五碼的意思

延伸應用的另一個公式 LEFT
顧名思義就是從左邊抓字
合購常見的應用就是
例如你請團員填寫EZ店號/店名
但是你要輸到EZ網站上的時候，無法整個複製貼上這樣搜尋不到
你可以輸入 =LEFT(店號/店名的儲存格,5) 因為店號清一色五碼
再直接複製套公式的那個儲存格就可以囉~

或是例如你要把使用迷你袋和輕鬆袋的團員分開
可以使用 =LEFT(EZ店名,1) 然後排序
顯示"O"和"萊"的就歸類在迷你袋，顯示"全"的就是輕鬆袋
是不是很簡單呢?

這兩個公式其實意外的好用

好!繼續!

接下來使用VLOOKUP去抓這個末五碼的匯款團員和回條金額

=VLOOKUP(末五碼的儲存格,匯款回條Sheet整個明細圈起來,3,FALSE)

看不懂我在寫啥的可以直接看我的範本，點那個儲存格就可以看到公式
為什麼是這樣寫呢?
VLOOKUP有4個值要填寫，分別以逗號隔開

第一個值是你要比對的值，我們這次是要比對末五碼，所以選取末五碼的儲存格

第二個值是你要比對的範圍，我們要比對找出交易明細和匯款回條相同的末五碼
所以第二個值我們要圈選整個匯款回條的明細(末五碼記得放第一欄)

第三個值是你要顯示的欄位，我們要顯示PTT的ID
請看匯款回條的表格依序是末五碼/金額/PTT ID，PTT ID在第三欄
所以我們打上3

第四個是邏輯值，是寫上你要比對的方法
TRUE(或空白不填寫)表示找出一個最接近的值(近似值)
FALSE表示要比對完全符合的值
以對帳來說，末五碼當然要完全一樣，所以我們填入FALSE

回條金額欄位比照辦理，第三個值要填入2(因為金額在第二欄)

最後對減就完成對帳了，不管是金額有問題，還是末五碼有問題的都清清楚楚
(如果無摺局號填錯地方，也會出現錯誤，無摺填法請參考彩虹大教學文章#1Hscj6RK)
正常情況下，只會有非常小部分的團員會出現錯誤
所以針對有問題的地方人工對帳就好囉
(不過我回條寫末五碼...卻常常有人給我填4碼/6碼/甚至全部帳號的= =)
總之有問題就人工對囉，當然你也可以在匯款回條用RIGHT公式重新抓末五碼
降低問題單機率

教學就到這邊

如果...如果你真的看不懂
小鈴太笨講解不好讓你還是不懂

沒關係!這次我一樣準備好了懶人包
上限是500人
(之前有板友寫信給我說200人不夠，但是真的有人在使用我的懶人包讓我好感動T^T)

http://ppt.cc/2n0X 
歡迎下載!!
只要照著說明把資料貼上去
連誰沒匯款都幫你抓出來了!!!

方便吧...我根本是專作懶人包的專家(超大誤)

-----
2013/11/6 01:41更新
有人反應貼上不會正確顯示的問題
建議複製到記事本再轉複製到懶人包上
或是選擇性貼上值，才不會因為變成文字顯示不出來唷
-----
檔案皆已修正成可選取式鎖定
可以複製貼上囉
(2013/12/11更新)

如果這個檔案出了任何問題
請再告訴我

今天的分享就到這邊
下臺一鞠躬m(_ _)m

PS.上個班卻偷懶花了好多時間打這篇XD

--
看完後請記得給原po一個鼓勵喔～
歡迎分享看過之後的感想
不知道推什麼的話 ^把下面亮白色的文字直接複製貼上按enter就可以了
X1洽特就溫馨 分享謝謝你!

--
某天，老公不知道發什麼神經，在FB打了某句動態

於是...他得到了他個人紀錄有史以來最多的讚

http://ppt.cc/Drhl     (￣□￣|||)a

--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 60.251.181.240
推 rbitmbasitw:推推！！！
推 maochih:推！推文檢核很好用，這次的分享也好棒!!!
推 daiting:好文推:)
推 rainbowbaby:我也用過這個 但我後來發現用依序對會
→ rainbowbaby:更快 可能是習慣上的問題QQ
推 jamlykate:推倒你(大誤
推 Queenpee:推推推!!!
推 pig0216:推!
推 tlx:推推!清楚明瞭的教學!
推 pig0216:原來可以直接RIGHT,我後五碼有削頭去尾
→ pig0216:http://ppt.cc/3CZi 不知道當時在想什麼
→ pig0216:因為完全不會EXCEL 當時是谷歌教的 弄很久
→ pig0216:懶人包就是好用  讚!
→ djpro:感謝分享 下次試試
推 everyna:推，但我手工對帳(存簿 悲劇)
推 sunrainemily:推推XD 很少開到大團需要這樣對(誤
推 sparrow0806:推，但我手工對存簿帳款QQ
其實小團我也是手工對帳的:)
※ 編輯: aotsukirin      來自: 60.251.181.240       (10/28 16:28)
推 sparrow0806:其實是因為沒有網路銀行，
→ sparrow0806:開大團對存摺帳款會對到死QQ
→ sparrow0806:20人就可以搞死我了，根本只敢開地方
推 zhilyan:推 看洽特長知識~~XD
推 daiting:right函數很有趣耶，之前是用資料檢核用線
→ daiting:段分隔，這也滿好用的~推薦
推 MaxBigRice:可以再搭配MID跟Len還有Search
→ MaxBigRice:或是將資料排序後用if做整理
推 tieny:推推!雖然沒在開團但又新學了一個公式好開心
推 error123:先M起來 完全看不懂T_T
推 snows94520:推推，謝謝分享，非常有幫助>///<
推 ninaching:推！之前都一個個對，會累到不想開團了X
推 lojve:推!! 超方便懶人包 :)
推 guessjo:我也都一個一個對 Orz 改天來研究一下
推 duck0823:愛死你了~~~~~啾
推 angela810805:推~~~考試完再來研究好了QQ
推 crazy1019:推教學範本和懶人包～
※ 編輯: aotsukirin      來自: 60.251.181.240       (12/11 15:22)
推 bluetea: 最近不小心開了百人團，還好有懶人包~
→ bluetea: 感謝您熱心分享^^Y
推 sukidase: 懶人包感恩!!! 推推
推 seek: 感謝分享