Excel如何資料剖析後vlookup查找




最近在檢視管理月報,發現有數字不太對勁,仔細OOXX核對,抓到兩隻不同系統程式下的報表,明明內容皆是本月應付匯總,兩者總金額卻不知道少了哪根筋,硬是有差異,通常這種情況我會使出Excel天下第一招Vlookup,可是有個麻煩,其中一個程式供應商欄位是把代碼和名稱併在一起,另外一個程式的代碼名稱卻是分開,這就造成Vlookup查找的困難,以下分享兩個作法:

一、如圖所示,系統跑出來報表,自作聰明把供應商欄位黏在一起。

供應商欄位把代碼和名稱黏在一起了!

二、直覺想到「資料剖析」,將滑鼠移到資料工具區塊,Excel開始囉嗦這隻自帶程式的用法。

資料剖析程式說明

三、不管是資優生上數學課、小朋友事務所查帳、新會計結算成本,聽老師講、看前輩做再多都沒用,自己操作一次才叫上手,Excel也是如此,直接點選「資料剖析」實戰最快:步驟3之1,這裡因為代碼和名稱中間隔著空格,所以是「分隔符號」。

資料剖析步驟3之1

四、步驟三之二,「分隔符號」選擇「空格」,「連續分隔符號視為單一處理」已經預設打勾省得麻煩,下面可以「預覽分隔結果」,確實如我們所願!

資料剖析步驟3之2

五、最後一個步驟,「欄位的資料格式」預設「一般」,「目標儲存格」還能選擇要在哪裡生成分割後的資料。

資料剖析步驟3之3

六、噹噹噹,本來的A欄被剖開成AB欄,格式再整理下成為DE欄,OK啦!這是我要的。

順利把資料剖成兩半!

七、我是函數狂人,遇到什麼都想套上函數,仔細觀察資料規則,於A欄右邊輸入公式:「=LEFT(A2,6)」,表示A2左邊數來取六位,公式一拉,供應商代碼完美呈現。

以LEFT函數替代資料剖析

八、供應商名稱比較消耗腦細胞,於供應商代碼右邊輸入公式:「=RIGHT(A2,LEN(A2)-8)」,表示A2右邊數來取位,因為A2的規則是6位供應商代碼加兩個空格再加供應商名稱,所以右邊要取A2總長度扣掉8的位數。

以RIGHT和LEN函數替代資料剖析

 

如此,順利以函數套出和「資料剖析」同樣的效果。喜歡套函數,是因為有些例行性報表如果是每周每月都要編製,只要把編製流程寫成函數,日後便可下系統報表拉公式,自動化秒編管理報表。以前我就試過將每個禮拜的資金預估,成功編寫函數,本來至少一個小時完成的活,濃縮成每周五下午花個十分鐘完事交差,輕輕鬆鬆準時下班,這才是真Excel達人的境界呀!






當前文章延伸閱讀:
VLOOKUP