在 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:選取陣列中傳回值的欄。

 

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()