2012年1月8日 星期日

Excel-查詢後修正部分資料

在 Excel 中有一個價格資料表(如下圖左),因為有了新的價目表,其中只有部分的價格做了調整(如下圖右),如何製作新的價目表呢?其中沒有調價的項目維持原價,而有調價項目以新價格取代。

【準備工作】

選取儲存格G1:H12,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:調價項目和新價格。接著,定義名稱:資料,範圍為儲存格G1:H12。

 

【輸入公式】

儲存格D2:=IFERROR(INDEX(資料,MATCH(B2,調價項目,0)+1,2),C2)

MATCH(B2,調價項目,0):查出B欄中的項目位在新價目中的「調價項目」欄位中的第幾列,如果查不到資料,則會傳回錯誤值。

INDEX(資料,MATCH(B2,調價項目,0)+1,2):將 MATCH 函數查得的數字代入 INDEX 函數,查詢在「資料」中的第 2 個欄位(新價格)的值,其中「+1」是因為在 INDEX 函數中的查詢欄位必須要具有欄位名稱。

IFERROR 函數用以判斷如果傳回錯誤訊息,則表示在新價目表中沒有該項目,即沒有更新價目,所以用原價格表示。

儲存格E2:=IF(ISERROR(MATCH(B2,調價項目,0)),"","新調價")

參考上述說明,在備註欄中如果傳回錯誤訊息,則顯示空白,否則顯示「新調價」。

複製儲存格D2:E2,往下各列複製。

 

【補充資料】

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

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

 

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

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

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

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

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

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

 

1 則留言:

  1. 大大你好,
    路過看到你這篇文章,
    讓我對index()&match()有了應用的思考方向了.
    只看EXCEL F1的說明好難搞明白它們的用途...

    另外,
    小弟對於「查詢後修正部份資料」提供另一個公式給你參考,
    VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    lookup_value:搜尋目標值
    table_array:搜尋範圍
    col_index_num:找到的目標列,要傳回的欄號.
    range_lookup:邏輯判斷,搜尋目標值的對照程度,
    一般我都用FALSE(完全符合)

    VLOOKUP的功能是在指定的搜尋範圍的首欄尋找指定目標值,
    並傳回指定目標值那一列,第N欄的值.

    另一個與其功能雷同的為 HLOOKUP,
    只是改為在指定的搜尋範圍的首列尋找指定目標值.

    ------------------------
    借用大大你提供的範例資料:
    只要 定義名稱:資料,範圍為儲存格G1:H12

    [公式]
    儲存格D2:=IFERROR(VLOOKUP(B2,資料,2,FALSE),C2)

    查出B欄中的項目位在新價目中的首欄「調價項目」中的第幾列,並直接調第2欄的「新價格」傳回,如果查不到資料,則會傳回錯誤值。


    儲存格E2:=IF(EXACT(C2,D2),"","新調價")

    這邊我是偷吃步,僅比對C欄「價格」與D欄「調整後」有無差異來作判斷標準.

    回覆刪除

檢視其他文章

好康東東