※ 請使用蝦皮購物帳號密碼進行登入
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)為正常,因為我們還沒輸入商品及購買數量

完成公式如下

完成後就可以開始輸入A欄(姓名)、B欄(購買商品)、C欄(購買數量),輸入完後,自動更新欄位為:D欄(真實庫存)、H欄(已賣出)、I欄(剩餘庫存)

最後附上完成後公式如下:

文章出處:菜鳥救星 意如老師 Excel教學:實作篇-查詢商品即時庫存


favorite2chat1visibility3,160

Loading