贊贊小屋

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類

Excel資料處理時常常要因應管理需求新增欄位。本文以入庫明細表為例,介紹VLOOKUP、IF、LEFT、MID、VALUE等函數綜合應用,依照編碼原則新增會科及存貨分類欄位。

公司收入來源是銷售存貨,因此存貨可說是公司最重要資產,對於存貨管理的第一步,就是依照一定編碼原則,為所有存貨編列料號。財務部成本會計人員,對於存貨編碼原則,必須非常熟悉,實務工作上,無論是內部管理或者外部審計,常常需要以存貨分類進行分析。可是,有時候原始報表只有料號,沒有分類,必須自行依照料號帶出分類,接著才能彙總整理。以下介紹VLOOKUP函數在這個過程中的妙用:

一、入庫明細表

為了方便說明,諸如品名單位等欄位被省略了,而且筆數大大減少,實務上系統跑出來的存貨報表,通常筆數會非常多。

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類 VLOOKUP 第1張

二、存貨編碼原則

第一碼是會科,第二碼則是依據各會科分別展開的性質分類,後面三碼是沒有特別意義的流水編號,總共存貨有五碼。

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類 VLOOKUP 第2張

三、LEFT函數

既然第一碼是會科,利用LEFT函數,取左邊算來取第一碼,公式為:「=LEFT(B2,1)」。除了LEFT是取左邊字元的函數應用之外,還有RIGHT及MID函數,用法類似,視情況需要可以使用不同的函數。

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類 VLOOKUP 第3張

四、VLOOKUP函數

輸入公式:「=VLOOKUP(D2,分類!$A$2:$B$4,2,0)」。Excel便會依照分類原則,傳回存貨會科。這裡的搜尋範圍輸入「分類!$A$2:$B$4」和「分類!A:B」,兩者同樣效果。「分類!$A$2:$B$4」是將範圍鎖住,非常精準,最節省Excel運算資源,「分類!A:B」會搜尋整個工作表兩個欄位,非常耗費資源,可是在某些場合,例如欄位資料會持續更新增補,卻是相當方便。

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類 VLOOKUP 第4張

五、函數公式合併

將取第一碼和查找分類的公式合併:「=VLOOKUP(LEFT(C2,1),分類!$A$2:$B$4,2,0)」,如此公式較為複雜,但報表看起來簡潔。

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類 VLOOKUP 第5張

六、IF函數

接下來更進一步。存貨第一碼有三種會科分類,而根據會科不同,又有各自的性質分類。以製成品而言,有一層櫃、二層櫃、三層櫃的區別,假設現在情況單純,只須顯示製成品的性質分類,其它不用。如此只須一個若P則Q的判斷式,輸入公式:「=IF(LEFT(C2,1)=”A”,VLOOKUP(VALUE(MID(C2,2,1)),分類!$C$2:$D$4,2,0),”非成品”)」表示如果存貨第一碼是A(製成品)(IF(LEFT(C2,1)=”A”)),讓Excel依照存貨第二碼的值(VALUE(MID(C2,2,1))),傳回製成品的性質分類,否則的話(存貨第一碼並非A),顯示「”非成品”」,結果如圖所示。

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類 VLOOKUP 第6張

七、IF巢狀公式

IF函數可以多層次判斷,所以能夠若P則Q則R則S則T套用下去,簡單的公式結構為IF(P,Q,IF(R,S,T))。在這一章節的範例中,想得到各個存貨料號的性質分類,最終公式為:「=IF(LEFT(C2,1)=”A”,VLOOKUP(VALUE(MID(C2,2,1)),分類!$C$2:$D$4,2,0),IF(LEFT(C2,1)=”B”,VLOOKUP(VALUE(MID(C2,2,1)),分類!$E$2:$F$4,2,0),VLOOKUP(VALUE(MID(C2,2,1)),分類!$G$2:$H$4,2,0)))」

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類 VLOOKUP 第7張

VLOOKUP執行核對與查找任務

前面兩節在核對差異時,都是利用VLOOKUP搜尋不到的情況,然而究其本質,VLOOKUP函數原始功能是依照特定條件,搜尋查找後傳回相對應的值,重點是找到了什麼,而不是沒有找到,這一節便是重點介紹如何運用vlookup的查找功能。實際操作之後,應該能夠體會VLOOKUP達到的是橋樑作用,在兩份資料之間,就共同具備的屬性,將數據串起來,使得原來的報表更加完整。掌握了這個特性,便可以善加利用,而且會發現,VLOOKUP函數在會計工作上具有非常大的實用性。

本文內容取自《會計人的Excel小教室(增訂版)》,書本仍然是最好的學習方法,省下一張電影票的錢,今天就買本書吧!

博客來網路書店網址:

Excel以IF函數判斷編碼原則,VLOOKUP查找存貨料號分類 VLOOKUP 第8張