2011年4月30日 星期六

Excel-在LOOKUP中使用兩個條件

在 Excel 中有一個資料(如下圖左),要由這個資料表中查詢對應的數值,並標示其位址,該如何處理?這次要在LOOKUP函數中使用兩個件來進行查詢。

首先定義三個資料範圍:DA:儲存格A3:A26,DB:儲存格B3:B26,DC:儲存格C3:C26。

(1) 查詢資料

儲存格G3:=IFERROR(LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26),"X")

LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26)

=LOOKUP(1,1/{True,False,True,False,False,…}*{Fasle,False,True,True,False,…},{1,2,3,4,5,…})

=LOOKUP(1,1/{0,0,1,0,0,…},{1,2,3,4,5,…})

=LOOKUP(1,{#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!,…},{1,2,3,4,5,…})

=536

使用IFERROR函數,讓查不到結果的儲存格顯示「X」,而不顯示錯誤訊息。

(2) 查詢位址

儲存格H3:{=IFERROR(ADDRESS(SUM(IF((E3=DA)*(F3=DB)*(G3=DC),ROW($3:$26))),3),"")}

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

{SUM(IF((E3=DA)*(F3=DB)*(G3=DC),ROW($3:$26))}

=SUM(IF({True,False,True,False,…}*{Fasle,False,True,True,…}*{False,False,True,False},{3,4,5,6…}))

=SUM(IF({0,0,1,0,…},{3,4,5,6,…}))

=5

使用IFERROR函數,讓查不到結果的儲存格顯示空白,而不顯示錯誤訊息。

複製儲存格G3:H3,往下貼上。

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

LOOKUP函數:http://office.microsoft.com/zh-tw/excel-help/HP010342671.aspx

LOOKUP:從單列、單欄範圍或陣列中傳回一個值。

語法:LOOKUP(lookup_value,  array)

lookup_value:在陣列中搜尋的值。

array:此引數包含文字、數字,或要與lookup_value比較的邏輯值之儲存格範圍。

 

ADDRESS函數:http://office.microsoft.com/zh-tw/excel-help/HP010342163.aspx

 

ADDRESS:在已知指定列和欄號下,取得工作表中儲存格的位址。

語法:ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num:指定要用在儲存格參照中之列號的數值。

column_num:指定要用在儲存格參照中之欄號的數值。

abs_num:指定要傳回之參照類型的數值。(1或省略→絕對儲存格參照,2→列:絕對;欄:相對,3→列:相對;欄:絕對,4相對參照

2 則留言:

  1. 請問三個資料範圍要如何操作達成"定義" ? 感謝!!

    回覆刪除
    回覆
    1. 以公式=IFERROR(LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26),"X")為例,(A3:A26=E3)*(B3:B26=F3)即使用了二個條件,若要三個條件,則仿(A3:A26=E3)*(B3:B26=F3)*(XXXXXX=XX)即可。多個條件亦是如此...

      刪除

好康東東