2018年5月11日 星期五

Excel-多條件查詢(SUMPRODUCT,OFFSET)

在 Excel 中,你會使用 VLOOKUP 函數來執行查詢的工作,但是如果是要查詢多個條件時,VLOOKUP 函數無法滿足這個需求,該如何處理?
以下圖為例,如果要以雙條件在下圖左的資料表中查詢,如何設計公式?
Excel-多條件查詢(SUMPRODUCT,OFFSET)

【公式設計與解析】
選取儲存格A1:F14,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:單號、序號、料號、數量、進貨單號、倉庫。
輸入公式,儲存格H8:
=OFFSET($F$1,SUMPRODUCT((單號=H2)*(料號=H4)*ROW(倉庫))-1,0)
(1) SUMPRODUCT((單號=H2)*(料號=H4)*ROW(倉庫))
在 SUMPRODUCT 函數使用雙條件:
條件一:單號=H2:單號和儲存格H2相同者
條件二:料號=H4:料號和儲存格H4相同者
在 SUMPRODUCT 函數中,(單號=H2)*(料號=H4)*ROW(倉庫)會傳回合於條件者的列號。
(2) OFFSET($F$1,第(1)式-1,0)
將第(1)式傳回的列號代入 OFFSET 函數,取得對應的儲存格內容。
如果你要增加條件,則在 SUMPRODUCT 函數中擴增條件,其餘不用變更。

沒有留言:

張貼留言

檢視其他文章

好康東東