作者aotsukirin (小鈴)
看板Chat82gether
標題[心得] 主購：對帳
時間Mon Oct 28 14:48:47 2013
哈囉我又來了
最近洽特好熱鬧
剛好有看有人說什麼用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
1F：推 rbitmbasitw:推推！！！ 36.231.97.8 10/28 14:59
2F：推 maochih:推！推文檢核很好用，這次的分享也好棒!!! 59.125.77.25 10/28 15:10
3F：推 daiting:好文推:) 223.142.31.218 10/28 15:11
4F：推 rainbowbaby:我也用過這個 但我後來發現用依序對會 114.46.96.37 10/28 15:21
5F：→ rainbowbaby:更快 可能是習慣上的問題QQ 114.46.96.37 10/28 15:22
6F：推 jamlykate:推倒你(大誤 61.230.121.124 10/28 15:27
7F：推 Queenpee:推推推!!! 111.252.174.26 10/28 15:29
8F：推 pig0216:推! 210.60.122.200 10/28 15:29
9F：推 tlx:推推!清楚明瞭的教學! 219.71.208.137 10/28 15:32
10F：推 pig0216:原來可以直接RIGHT,我後五碼有削頭去尾 210.60.122.200 10/28 15:34
12F：→ pig0216:因為完全不會EXCEL 當時是谷歌教的 弄很久 210.60.122.200 10/28 15:40
13F：→ pig0216:懶人包就是好用 讚! 210.60.122.200 10/28 15:44
14F：→ djpro:感謝分享 下次試試 27.52.237.224 10/28 15:45
15F：推 everyna:推，但我手工對帳(存簿 悲劇) 1.162.91.231 10/28 16:15
16F：推 sunrainemily:推推XD 很少開到大團需要這樣對(誤 220.134.57.129 10/28 16:23
17F：推 sparrow0806:推，但我手工對存簿帳款QQ 140.112.25.105 10/28 16:26
其實小團我也是手工對帳的:)
※ 編輯: aotsukirin 來自: 60.251.181.240 (10/28 16:28)
18F：推 sparrow0806:其實是因為沒有網路銀行， 140.112.25.105 10/28 16:44
19F：→ sparrow0806:開大團對存摺帳款會對到死QQ 140.112.25.105 10/28 16:45
20F：→ sparrow0806:20人就可以搞死我了，根本只敢開地方 140.112.25.105 10/28 16:45
21F：推 zhilyan:推 看洽特長知識~~XD111.249.188.126 10/28 16:51
22F：推 daiting:right函數很有趣耶，之前是用資料檢核用線 42.77.70.24 10/28 17:41
23F：→ daiting:段分隔，這也滿好用的~推薦 42.77.70.24 10/28 17:42
24F：推 MaxBigRice:可以再搭配MID跟Len還有Search 114.25.90.137 10/28 17:46
25F：→ MaxBigRice:或是將資料排序後用if做整理 114.25.90.137 10/28 17:46
26F：推 tieny:推推!雖然沒在開團但又新學了一個公式好開心 140.112.25.105 10/28 18:49
27F：推 error123:先M起來 完全看不懂T_T 42.73.111.97 10/28 19:04
28F：推 snows94520:推推，謝謝分享，非常有幫助>///< 123.193.43.108 10/28 20:41
29F：推 ninaching:推！之前都一個個對，會累到不想開團了X 123.110.96.5 10/28 21:12
30F：推 lojve:推!! 超方便懶人包 :) 118.161.60.125 10/28 23:34
31F：推 guessjo:我也都一個一個對 Orz 改天來研究一下111.240.104.116 10/28 23:52
32F：推 duck0823:愛死你了~~~~~啾 1.174.239.15 10/29 12:03
33F：推 angela810805:推~~~考試完再來研究好了QQ 101.8.252.141 10/29 15:35
34F：推 crazy1019:推教學範本和懶人包～ 114.45.185.231 10/30 12:40
※ 編輯: aotsukirin 來自: 60.251.181.240 (12/11 15:22)
35F：推 bluetea: 最近不小心開了百人團，還好有懶人包~ 61.70.79.8 05/15 13:24
36F：→ bluetea: 感謝您熱心分享^^Y 61.70.79.8 05/15 13:25
37F：推 sukidase: 懶人包感恩!!! 推推140.129.136.215 06/11 10:53
38F：推 seek: 感謝分享 114.38.2.85 05/24 16:13