2014年10月22日 星期三

Excel-輸入代號查詢名稱(INDEX,MATCH,OFFSET,VLOOKUP,LOOKUP,ADRESS,INDIRECT,SUMPRODUCT)

網友想要在 Excel 的工作表中,輸入一個已知的代號,以查詢方式顯示一個名稱,該如何處理?

參考下圖,根據代號及名稱的對照表,透過查詢的函數,很容易查出對應的內容。

因為這是網友常會遇到的問題,所以一次將可以用到的函數全部列出說明。因為是函數的基本運算,所以特別說明公式,至於函數的相關說明,請自行藉由文末的超連結檢視微軟提供的資料。

1. 使用 VLOOKUP 和 LOOKUP

(1) 儲存格B2:=VLOOKUP(A2,$D$2:$E$25,2)

(2) 儲存格B2:=LOOKUP(A2,$D$2:$D$25,$E$2:$E$25)

 

2. 使用 MATCH 配合 INDEX/OFFSET/INDIRECT/ADDRESS

MATCH(A2,$D$2:$D$25,0):查詢儲存格A2的內容在儲存格D2:D25中的位置(第幾個)。

(3) 儲存格B2:=INDEX($D$2:$E$25,MATCH(A2,$D$2:$D$25,0),2)

(4) 儲存格B2:=OFFSET($E$2,MATCH(A2,$D$2:$D$25,0)-1,,)

(5) 儲存格B2:=INDIRECT(ADDRESS(MATCH(A2,$D$2:$D$25,0)+1,5))

 

3. 使用 SUMPRODUCT 配合 INDEX/OFFSET/INDIRECT/ADDRESS

SUMPRODUCT((A2=$D$2:$E$25)*ROW($D$2:$D$25)):使用運算方式找出儲存格A2的內容在儲存格D2:D25中的位置(第幾個)。

(6) 儲存格B2:

=INDEX($D$2:$E$25,SUMPRODUCT((A2=$D$2:$E$25)*ROW($D$2:$D$25))-1,2)

(7) 儲存格B2:

=OFFSET($E$2,SUMPRODUCT((A2=$D$2:$E$25)*ROW($D$2:$D$25))-2,,)

(8) 儲存格B2:

=INDIRECT(ADDRESS(SUMPRODUCT((A2=$D$2:$E$25)*ROW($D$2:$D$25)),5))

複製儲存格B2,往下各列貼上。

 

【補充資料】

關於函數的相關說明,請參考微軟網站:

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

LOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010342671.aspx

陣列形式的 LOOKUP :在陣列的第一列或第一欄中搜尋指定的值,然後從陣列最後一列或最後一欄的相同位置中傳回值。

語法:LOOKUP(lookup_value,  array)

lookup_valueLOOKUP 函數在陣列中搜尋的值。

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

注意:陣列中的值必須以遞增順序排列,如果 LOOKUP 函數找不到 lookup_value,就會使用陣列中小於或等於 lookup_value 的最大值。

 

VLOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

 

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

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 引數內的值必須以遞減次序排序。

 

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

 

沒有留言:

張貼留言

好康東東