2011年6月21日 星期二

Excel-練習各種查表函數(INDEX,LOOKUP,OFFSET,MATCH)

在 Excel 中根據一個班級基本表,在一個申請夜讀的報表中,自動查詢得到班級基本資料(如下圖)。試著使用INDEX、LOOKUP、VLOOKUP、OFFSET、MATCH、INDIRECT等函數來練習查表。

先定義一些名稱:

班級:儲存格A2:A29;導師:儲存格B2:B29;人數:儲存格C2:C29,資料:儲存格A1:C29。

以下各式都可以得到正確的結果,將儲存格F2和儲存格G2複製後,往下各列貼上。

(1) 使用LOOKUP函數

儲存格F2:=LOOKUP(E2,班級,導師)

儲存格G2:=LOOKUP(E2,班級,人數)

(2) 使用VLOOKUP函數

儲存格F2:=VLOOKUP(E2,資料,2)

儲存格G2:=VLOOKUP(E2,資料,3)

(3) 使用INDEX函數+MATCH函數

儲存格F2:=INDEX(資料,MATCH(E2,班級,0)+1,2)

儲存格G2:=INDEX(資料,MATCH(E2,班級,0)+1,3)

(4) 使用OFFSET函數+MATCH函數

儲存格F2:=OFFSET($A$2,MATCH(E2,班級,0)-1,1,,)

儲存格G2:=OFFSET($A$2,MATCH(E2,班級,0)-1,2,,)

(5) 使用INDIRECT函數+MATCH函數

儲存格F2:=INDIRECT("B" &MATCH(E2,班級,0)+1)

儲存格G2:=INDIRECT("C" &MATCH(E2,班級,0)+1)

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

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應該要尋找完全符合還是大約符合的值。

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 必須是正數。

 

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

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

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

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

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

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

7 則留言:

  1. 您好

    經常閱讀您的文章,為了練習起見,不知道您的例子中,excel資料可否提供可複製的表格,因為圖片較難練習,感謝~

    回覆刪除
    回覆
    1. 您的建議不錯,我儘量試試。

      刪除
  2. 不好意思,我想請問一個排序的問題,謝謝~

    有A欄與B欄兩排資料,B欄資料不是每個欄位都有,若有的話都是以B或S開頭,我想把B欄有資料且為S開頭時,把A欄的資料擷取到在C欄重新排列該如何做?也就是說我該如何將有 B欄有Sell 的A欄重新在C欄排列呢?謝謝~
    A B C
    1 Sell 1
    2 4
    3 Buy 6
    4 Sell
    5
    6 Sell

    回覆刪除
    回覆
    1. 可參考 http://isvincent.blogspot.tw/2012/10/excel.html

      刪除
  3. 新年快樂!有問題想請教大師:

    來源檔有 圖號 栓號 種類 地址, 日誌有 圖號 栓號 種類, 我想讓日誌的資料去比對來源檔並且把對應的地址貼在日誌上該如何用? 請大師指點,感恩

    回覆刪除
  4. 您好, 我是你blog的常客, 看你的blog, 讓學到了很多, 十分感謝.
    因工作需要, 我時常會用到excel formula, 有一情況想請問一下.
    我有以下excel:
    https://docs.google.com/spreadsheet/ccc?key=0AlHkZZh3V78ddFJLMkFKdWlCOC1ZSW1oak1mWTc4V0E
    一個name可有多個ref, 我想在D1輸入要尋找的name, D2以下就會出現所有屬於該name的ref.

    我曾用index+match, 但只能找出第一條record. 請問有什麼方法可以做到?

    回覆刪除
    回覆
    1. 請參考: http://isvincent.blogspot.tw/2013/01/excel_14.html 為您所做的說明的說明。

      刪除

好康東東