2012年1月18日 星期三

Excel-多條件的查詢(INDEX+MATCH+陣列)

根據 Excel 中的資料表(參考下圖),想要根據三個條件(零件編號、外形、尺寸)來求得售價,利用 INDEX 函數應是不錯的選擇。由於條件有三個,所以得藉助陣列公式才能求得結果。

【準備工作】

1. 選取儲存格A1:D19,按一下 Ctrl+Shift+F3 鍵,建立名稱:零件編號、外形、尺寸。

2. 選取儲存格A2:D19,建立名稱:資料。

【公式說明】

儲存格D23:{=INDEX(資料,MATCH(1,(零件編號=A22)*(外形=B22)*(尺寸=C22),0),4)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(零件編號=A22)*(外形=B22)*(尺寸=C22):公式中的「*」乃將三個條件做 AND 運算,條件完全成立時會傳回 True (運算時視為1),反之傳回 False (運算時視為0)。

MATCH(1,(零件編號=A22)*(外形=B22)*(尺寸=C22),0):找尋「1」(符合條件者)在第幾列。

透過 INDEX 函數查詢在第4欄中對應的結果。

如果查詢不到任何符合條件的資料會傳回錯誤值,稍加修改公式,利用 IFERROR 函式,讓其傳回「無此零件」訊息。修改如下:

儲存格D24:{=IFERROR(INDEX(資料,MATCH(1,(零件編號=A22)*(外形=B22)*(尺寸=C22),0),4),"無此零件")}

使用資料驗證方式設計下拉式清單,可以讓輸入資料時不會因為輸入錯誤而查詢不到資料。

 

【補充資料】

關於詳細函數說明,請參考微軟網站: 

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

 

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

IFERRORhttp://office.microsoft.com/zh-tw/excel-help/HA010342587.aspx

IFERROR:如果公式計算錯誤,會傳回指定的值;否則,會傳回公式的結果。

語法:IFERROR(value, value_if_error)

value:檢查此引數是否有錯誤。

value_if_error:公式計算錯誤時要傳回的值。

使用 IFERROR 函數,可以捕捉並處理公式中的錯誤。會評估下列錯誤類型:#N/A#VALUE!#REF!#DIV/0!#NUM!#NAME? #NULL!

 

1 則留言:

  1. 手邊有個多重條件判斷計算獎金
    搞得我一頭霧水,能否請你指點,檔案要如何提供?

    Rita

    回覆刪除

檢視其他文章

好康東東