網友想要在 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,往下各列貼上。
【補充資料】
關於函數的相關說明,請參考微軟網站:
INDIRECT:http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx
INDIRECT:傳回文字串所指定的參照位址。 |
語法:INDIRECT(ref_text,[a1]) ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。 a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。 |
LOOKUP:http://office.microsoft.com/zh-tw/excel-help/HP010342671.aspx
陣列形式的 LOOKUP :在陣列的第一列或第一欄中搜尋指定的值,然後從陣列最後一列或最後一欄的相同位置中傳回值。 |
語法:LOOKUP(lookup_value, array) lookup_value:LOOKUP 函數在陣列中搜尋的值。 array:此引數包含文字、數字,或您要與 lookup_value 比較的邏輯值之儲存格範圍。 注意:陣列中的值必須以遞增順序排列,如果 LOOKUP 函數找不到 lookup_value,就會使用陣列中小於或等於 lookup_value 的最大值。 |
VLOOKUP:http://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_num:table_array 引數中必須傳回相符值的欄號。 range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。 |
SUMPRODUCT:http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx
SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。 |
語法:SUMPRODUCT(array1, [array2], [array3], ...) array1:要求對應元素乘積和的第一個陣列引數。 array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |
MATCH:http://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 引數內的值必須以遞減次序排序。 |
OFFSET:http://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 必須是正數。 |
留言列表