Excel之Vlookup多層次料號分類 - 會計

Excel之多層次Vlookup料號分類

網誌圖文版：

http://www.b88104069.com/archives/1750

有讀者來信問我Vlookup，我想Excel除了Sum之外，Vlookup是會計人最好的
朋友了，值得寫一篇專文分享。

實務上常常遇到必須依照編碼原則，帶出存貨料號的性質分類，便於進一步
彙總整理，舉例如下：

一、首先，這是簡化的入庫明細表，為了方便說明，諸如品名單位等欄位被
省略了，而且筆數大大減少，實務上系統跑出來的報表，可能比這個肥大到
好幾偣。

二、存貨編碼原則！每間像樣的公司，都會這麼一份像樣的文件，這是公司
所有存貨料號的基本大法，非常重要，不怎麼機密，如果手上沒有，記得趕
快找到一份存檔。如圖所示，它是一份簡化的編碼原則。第一碼是會科，第
二碼是依據各個會科、各別展開的性質分類，後面三碼是流水號，沒有特別
意義的流水編號，總共加起來，存貨有五碼。

三、既然第一碼是會科：「=LEFT(C2,1)」，左邊算來取第一碼。關於LEFT這
個函數，既然是左邊，肯定也有右邊跟中間，就是Right跟Mid函數，道理用
法是一樣的，有需要可以從函數百寶袋掏出來。

四、我是函數狂，但Excel並非函數的天下，特別是微軟每次Office改版升級
，從1997、2003、2007，感覺工具命令越來越多，其實很多任務不需要函數
，也能完成。例如抓出第一碼資料這檔事，古老的「資料剖析」便能辦到。

五、想要剖析的資料沒有特別的「分隔符號」，我們也只是單純要抓出某一
碼，所以選擇「固定宽度」即可。

六、說明寫得很清楚：若要建立分欄線，按一下，清除分欄線，連按兩下，
移動分欄線，按住並拖曳。我們要的很簡單，就是在第一碼的位置一下，OK
。

七、「目標儲存格」的部份，預設是下一欄，D欄，但工作表上D欄已經有資
料了，所以要手動改成空白的F欄，避免資料被覆蓋了。

八、剖析出來的結果，神奇吧！在某些場合，特別是系統跑出來報表慘不忍
睹的時候，絶對不要忘了，還有「資料剖析」這個大絶招。

九、回到函數的世界，主角是Vlookup，微軟打官腔的文字介紹：「在表格陣
列的第一欄中搜尋某個數值，並傳回該表格陣列中同一列之其他欄中的數值
。」如果不是把那段話當文言文研究，正常人應該有看沒有懂。所以還是回
到我們的簡單例子，輸入公式：「=VLOOKUP(D2,分類!A:B,2,0)」，以逗號分
隔的四個引數之中，「D2」是查找條件，「分類！A:B」是查找範圍，「2」
表示傳回範圍裡的第二欄資料，「0」是查找方式，這個我慣用零，表示必須
完全相符。以這個例子而言，D2=A，所以Excel會在分類工作表的A欄中，找
到第一個完全相符的A，也就是該工作表第A欄第二行的儲存格(內容為A)，然
後傳回A:B這個範圍同樣列數的第二欄資料，也就第B欄第二行的儲存格(內容
為製成品)，將公式往下拉，Excel會依此類推傳回所查找到的資料。

十、我習慣將所有公式塞到同一格裡，這樣雖然函數組合看起來噁心，但報
表列印出來賞心悅目：「=VLOOKUP(LEFT(C2,1),分類!A:B,2,0)」。

十一、接下來是這篇文章的重頭戲。存貨第一碼有三種會科分類，根據會科
不同，又有各別的性質分類。以製成品而言，有一層櫃、二層櫃、三層櫃的
區別，假設現在比較單純，我們只抓出製成品的性質分類，其它的不用，這
樣就只要加入一個若P則Q的If判斷式，輸入公式：「
=IF(LEFT(C2,1)="A",VLOOKUP(VALUE(MID(C2,2,1)),分類!C:D,2,0),"非成品
")」表示如果存貨第一碼是A(製成品)，讓Excel依照存貨第二碼的值
[VALUE(MID(C2,2,1))]，傳回製成品的性質分類，否則的話（如果存貨第一
碼並非A），就顯示「"非成品"」，結果如圖所示。

十二、IF函數可以多層次判斷，所以能夠若P則Q則R則S則T一直套用下去，簡
單的公式結構為IF(P,Q,IF(R,S,T))，在這篇文章的例子，想得到各個存貨料
的性質分類，最終公式：「
=IF(LEFT(C2,1)="A"[P],VLOOKUP(VALUE(MID(C2,2,1)),分類
!C:D,2,0)[Q],IF(LEFT(C2,1)="B"[R],VLOOKUP(VALUE(MID(C2,2,1)),分類
!E:F,2,0)[S],VLOOKUP(VALUE(MID(C2,2,1)),分類!G:H,2,0))[T])」，公式
實在太長了，擷圖裝不下，有興趣可以參考這裡貼上的文字，公式裡面有標
註[紅字]，那個並非公式內容，只是特別補充的說明。總之最後的結果，有
圖有真相，就是我想要的存貨分類，夠漂亮吧！

--

會計人的Excel小教室：

https://www.facebook.com/acctexcel


--