2012年1月19日 星期四

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

有網友根據上一篇文章:Excel-多條件的查詢(INDEX+MATCH+陣列) http://isvincent.blogspot.com/2012/01/excel-indexmatch.html

問到如果要查詢的資料不止一筆時,公式該如何寫?參考下圖,要由外形和尺寸這兩個條件來找符合的零件編號及其售價。

【準備工作】

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

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

【公式說明】

儲存格F4:{=IFERROR(INDEX(資料,SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),1),"")}

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

(外形=$G$1)*(尺寸=$G$2):公式中的「*」乃將二個條件做 AND 運算,條件完全成立時會傳回 True ,反之傳回 False

IF((外形=$G$1)*(尺寸=$G$2),ROW($2:$19)):因為資料共有 18 筆,所以將上式合乎條件者,傳回對應的列號(1~18)。

SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),1):因為ROW(1:1)=1,所以可以取出合於條件者的最小的列號,當往下複製時產生ROW(2:2)=2,會取出合於條件者的第 2 小的列號,以此類推。

透過 INDEX 函數,將此式求得的列號來查詢第 1 欄中的資料。由於往下複製公式時,可能因為找不到資料而傳回錯誤值,所以利用 IFERROR 函數,將錯誤訊息轉換為空字串。

同理,

儲存格G4:{=IFERROR(INDEX(資料,SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),4),"")}

透過 INDEX 函數,將此式結果來查詢第 4 欄的結果。

複製儲存格F4:G4,往下各列貼上。

 

【補充資料】

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

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

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

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

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

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

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

 

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!

沒有留言:

張貼留言

檢視其他文章

好康東東