2020-12-23 14:37:08

Excel教學：實作篇－查詢商品即時庫存

圖片來源：pexels

最終結果

使用者填入為橘色區域，自動化計算為綠色區域

任務1：事前準備

1.1準備商品庫存表（填入商品、庫存）

1.2準備讓使用者輸入（姓名、購買商品、數量）

任務2：認識SUMIF()函數

1-1有條件的加總

1-2計算該商品總共賣出了幾個

任務3：計算剩餘庫存

任務4：自動查詢目前庫存

任務1：事前準備

1-1準備商品庫存表

先填入所有的商品名稱跟庫存數量

我們要做的是自動計算的地方（已賣出、剩餘庫存）

1-2準備讓使用者輸入的表格

（讓使用者輸入客戶姓名、要購買的商品跟數量）我們要做的是自動計算目前庫存量（真實庫存）

任務2：認識SUMIF()函數

2-1有條件的加總

要計算出該商品的數量總共賣了幾個，所以加總還必須帶有條件的加總，只計算所有的A商品賣出了多少或者是所有的B商品賣出了多少等等。

SUM()為加總，而SUMIF()代表有條件式的加總，我們的範例是要找出”該商品”共賣了幾個，所以屬有條件的加總，所以會使用到SUMIF() 的函數。

先快速的認識一下SUMIF() 函數的用法

SUMIF() 有三個引數（參數）

參數1：要搜尋加總的範圍

例如：讓使用者輸入商品的這一欄

參數2：要搜尋的值

例如：剛剛輸入的範圍內，只要找到要加總的商品欄位，如“B商品”。

參數3：要加總的範圍

例如：所有商品購買數量的範圍

所以SUMIF()的函數為：

=SUMIF(“要搜尋的範圍”,”要搜尋的值”,”要加總的範圍”)

2-2計算該商品總共賣出了幾個

接下來要讓H欄（已賣出）自動化更新賣出數量，點選到H2輸入公式

第一個參數為搜尋的範圍為B欄的商品，所以先點選B欄=SUMIF(B:B)

因為B欄位固定欄位，所以再加上錢字號$ （快速鍵 F4 按到 $B:$B ）

第二個參數為要搜尋的值，例如：”商品A”、”商品B”，所以點一下F2儲存格，F為固定欄位，所以（快速鍵F4按到$F2）

等等往下複製即可=SUMIF($B:$B,$F2)

接下來第三個參數為要加總的範圍為C欄的購買數量，因為C欄位固定欄位，所以再加上錢字號$（快速鍵F4按到$C:$C）

完成公式如下：

=SUMIF($B:$B,$F2,$C:$C)

任務3：計算剩餘庫存

目前有了已賣出的數量，就可以直接用（目前庫存－已賣出的數量）來計算剩餘庫存量。

點選I2儲存格輸入：=G2-H2，往下複製即完成

最後往下複製即可完成”剩餘庫存”

剩餘庫存（公式如下）

任務4：自動查詢目前庫存

我們的庫存表已經設定完畢（如上圖），接下來就要來處理即時更新庫存的D欄位（如下圖）。

點選D2輸入公式：
=VLOOKUP()
第一個參數為搜尋值B2這個欄位
=VLOOKUP(B2)
第二個參數為對照表（剛剛做好的庫存表F1:I6），因為是固定的所以點選（快速鍵F4按到$F$1:$I$6）
=VLOOKUP(B2,$F$1:$I$6)
第三個參數為要查詢對照表的第幾欄，剩餘庫存是第4欄，所以輸入4
完整公式如下:
=VLOOKUP(B2,$F$1:$I$6,4)

往下複製（出現#N/A）為正常，因為我們還沒輸入商品及購買數量