有網友根據上一篇文章: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,往下各列貼上。
【補充資料】
關於詳細函數說明,請參考微軟網站:
INDEX:http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx
INDEX:傳回表格或範圍內的某個值或值的參照。 |
語法:INDEX(array, row_num, [column_num]) Array:儲存格範圍或陣列常數。 Row_num:選取陣列中傳回值的列。 Column_num:選取陣列中傳回值的欄。 |
IFERROR:http://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!。 |
留言列表