[教學] 比對資料進階題 – 比對庫存成本/訂單單價/異常問題顯示(VLOOKUP+IFERROR+IF)

待解之EXCEL問題

目前有個Excel檔案是這樣的,如下圖,summary分頁是總表,用來顯示各PN(料號)產品對應的庫存成本及訂單出貨單價,然後在說明處顯示正常或異常之問題。

接著我們看到本Excel中其他頁籤,首先是庫存stock分頁

接著看到訂單order分頁

解題

帶入庫存價格

首先,summary的B欄我們就用IFERROR 以及 VLOOKUP函數來比對並抓取stock分頁的價格資料。

先在B2欄位設定好以下公式,設定完成B2後,用滑鼠移到B2格子右下角會出現加號,按著拖曳到最下面B10欄位。

公式解釋:
=IFERROR(VLOOKUP(此行的料號, stock分頁的料號+價格區域欄位, 顯示第二行數值, 0), "如果VLOOKUP運算出來是錯誤則顯示這文字") 

完整公式(以第一格B2來示範):
=IFERROR(VLOOKUP([@PN], stock!A:B, 2, 0), "NO")

*備註:[@PN]也可以用A2來取代,只是[@PN]不用隨著換行變更,但A2需要隨著換行變成A3, A4, A5...。

小技巧:設定公式的時候,可先輸入等於, 公式及前方的括號後,例如=VLOOKUP(,就可以用滑鼠去按你要選擇的格子或區域(選擇一片區域就是按住滑鼠左鍵拖曳到你要的區域都反白之後再放開滑鼠),格子對應的編號就會自動輸入公式欄位中,而不用用鍵盤一個個輸入喔。

帶入訂單價格

接著我們要把order分頁的價格帶入summary分頁的C列欄位中。

公式解釋:
=IFERROR(VLOOKUP(此行的料號, order分頁的料號+價格區域欄位, 顯示第二行數值, 0), "如果VLOOKUP運算出來是錯誤則顯示這文字") 

完整公式(以第一格B2來示範):
=IFERROR(VLOOKUP([@PN], order!B:C, 2, 0), "NO")

設定說明的判斷公式

說明區塊我們希望若賺錢或打平的項目顯示ok、若庫存無價格資料或無訂單資料要顯示相對應的文字、若是虧本賠錢賣的品項也要顯示出來讓主管與業務知道。

這邊公式稍微長一點,但其實整體結構並不複雜,這邊我們使用巣狀的IF函數來進行判斷與顯示相對應的文字。

公式結構:
=IF(條件, TRUE的顯示文字, FALSE的顯示文字或繼續執行的下一個公式)
=IF(條件1, 顯示文字1, IF(條件2, 顯示文字2, IF(條件3, 顯示文字3, 顯示文字4)))

公式解釋:
=IF(成本欄位為NO, 則顯示"無此PN單價", 若不是NO則繼續執行公式IF(訂單出貨價欄位為NO, 則顯示"無訂單資料", 若不是NO則繼續執行公式IF(若成本大於售價, 則顯示"賠錢", 若非以上各列出之各情況則顯示"ok")))

完整公式(以第一格B2來示範):
 =IF([@成本]="NO", "無此PN單價", IF([@訂單出貨價]="NO", "無訂單資料", IF([@成本]>[@訂單出貨價], "賠錢", "ok"))) 

完成!

好啦,然後把在D2框框右下角加號用滑鼠拖曳到最後一欄位,就大功告成囉!!


發佈留言