[心得] Excel之Vlookup多層次料號分類

[moodyblue ()]

    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


--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 183.206.183.199
※ 文章網址: https://www.ptt.cc/bbs/Accounting/M.1428782850.A.EAE.html
※ 編輯: moodyblue (192.154.200.68), 04/12/2015 04:12:51
推 Joyce203: 推！
推 comicduo: 推！
推 may189: 推
推 iverson5566: 推