2013年1月14日 星期一

Excel-查表練習(INDEX, MATCH, OFFSET, VLOOKUP)

網友問到:在 Excel 的工作表 1 中已經輸入了物品名稱和價格,如果想在工作表 2 中輸入工作表 1 內的物品名稱,即可自動顯示該物品的價格(參考下圖),該如何處理?

就用這例子來做查表練習吧!

【準備工作】

(1) 將儲存格A1:A27,定義名稱:物品。

(2) 將儲存格B1:B27,定義名稱:價格。

(3) 將儲存格A1:B27,定義名稱:資料。

(4) 將儲存格B2,定義名稱:首位。

將儲存格定義名稱,在使用時無論公式放在那一個工作表,公式內容都一樣。

 

【輸入公式】

以下三種為查表方式:

(1) 儲存格E2:=INDEX(價格,MATCH(D2,物品,0),1)

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

(3) 儲存格E2:=OFFSET(首位,MATCH(D2,物品,0)-1,,,)

以下二種為使用陣列觀念:

(4) 儲存格E2:=SUMPRODUCT((物品=D2)*價格)

(5) 儲存格E2:{=SUM((物品=D2)*價格)}

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

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

 

【補充資料】

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

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

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

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

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

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

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

 

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

 

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

 

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

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

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

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

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

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

1 則留言:

  1. i would be very appreciated if you can help my question

    Group Weighting January

    Cat 2.51% 7.21%
    Rat 2.48% -5.54%
    Cat 2.51% 5.61%
    Cat 2.50% 10.40%
    Pig 2.47% 8.56%
    Dog 2.49% -4.87%
    Dog 2.50% 0.36%
    Dog 2.50% 7.54%

    if i want to have formula to vloop up all the Cat and sumproduct the "weighting" and the "respective Jan figure"...Cat is 2.51%*7.21%+2.51%*5.61%+2.5%*10.4%

    thanks

    回覆刪除

好康東東