2011年4月12日 星期二

Excel-Index+Match練習

這次要來練習利用 Index 和 Match 函數來查表的功能。

(1) 建立基本表

儲存格A2:=CHAR(ROW(1:1)+64),複製儲存格A2至儲存格A2:A11。

利用COLUMN(1:1)+64來產生65,66,67 …,再代入CHAR( )產生A,B,C …。

儲存格B1:=CHAR(COLUMN(A:A)+80),複製儲存格B1至儲存格B1:K1。

利用COLUMN(A:A)+80來產生81,82,83…,再代入CHAR( )產生Q,R,S …。

儲存格B2:=B$1&$A2,複製儲存格B2至儲存格B2:K11。

(2) 設定使用清單選取欄列項目

儲存格E14設定資料驗證準則為-儲存格允許:清單;來源:=$B$1:$K$1

儲存格F14設定資料驗證準則為-儲存格允許:清單;來源:=$A$2:$A$11

如此可以儲存格以清單方式挑選想要的項目。

(3) 進行查表

儲存格G14:=INDEX(A1:K11,MATCH(F14,A1:A11),MATCH(E14,A1:K1))

公式中的MATCH(F14,A1:A11)為找出儲存格F14(列)中的內容,位於儲存格A1:A11中的第幾個。

公式中的MATCH(E14,A1:K1)為找出儲存格E14(欄)中的內容,位於儲存格A1:K11中的第幾個。

將以上的兩個數值代入INDEX( )中,可以交叉找到儲存格A1:K11中的某一個儲存格。

(4) 設定儲存格底色

如果想要凸顯某一欄和某一列及其交會儲存格的色彩,可以藉助設定格式化條件來完成。

選取儲存格$A$2:$K$11,設定公式「=$A2=$F$14」,格式設定背景色彩為微深綠色。(某一列套用色彩)

選取儲存格$B$1:$K$11,設定公式「=B$1=$E$14」,格式設定背景色彩為微深綠色。(某一欄套用色彩)

選取儲存格$A$1:$K$11,設定公式「=AND($A1=$F$14,A$1=$E$14)」,格式設定文字色彩為紅色。(某一儲存格套用色彩)

完成之後,你就可以使用清單方式選取欄和列的名稱,立即查表得結果,並且顯示該欄、該列及交會的儲存格不同色彩。

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

CHAR:http://office.microsoft.com/zh-tw/excel-help/HP010342259.aspx

COLUMN:http://office.microsoft.com/zh-tw/excel-help/HP010342277.aspx

ROW:http://office.microsoft.com/zh-tw/excel-help/HP010342861.aspx

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

MATCH:http://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

沒有留言:

張貼留言

好康東東