2018年5月27日 星期日

Excel-多條件查詢(INDEX,INDIRECT,SUMPRODUCT)

在 Excel 中的資料表(如下圖)中,如何執行多個條件的查詢?
例如:給予類別、項目和重量等三個變項,要查詢對應的數值,該如何設計公式?
Excel-多條件查詢(INDEX,INDIRECT,SUMPRODUCT)

【公式設計與解析】
首先,定義名稱:
選取儲存格A1:G20,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:類別、項目、100kgs、200kgs、300kgs、400kgs、500kgs。
(特別注意:其中數字為首的名稱會被加上「_」,例如:100kgs→_100kgs。)
接著,輸入公式:
儲存格J4:=INDEX(INDIRECT("_"&J3),SUMPRODUCT((類別=J1)*(項目=J2)*
ROW(項目))-1,0)
假設多條件的查詢結果具唯一性。
(1) SUMPRODUCT((類別=J1)*(項目=J2)*ROW(項目))
利用 SUMPRODUCT 函數求得同時符合「類別」和「項目」二個條件者,傳回其在項目陣列中的位置(傳回一個數字)。
(2) INDIRECT("_"&J3)
利用 INDIRECT 函數將儲存各J3的內容轉換為儲存格範圍。(因為名稱定義時,數字為首的名稱會被加上「_」,例如:100kgs→_100kgs。)
(3) INDEX(第(2)式,第(1)式-1,0)
再利用 INDEX 函數查表得到對應的結果。

沒有留言:

張貼留言

檢視其他文章

好康東東