贊贊小屋

Excel如何vlookup多層次存貨料號分類

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

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

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

入庫明細表

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

存貨編碼原則

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

LEFT這個函數

四、我是函數狂,但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.books.com.tw/products/0010734874

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1