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)」，格式設定文字色彩為紅色。(某一儲存格套用色彩)

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

vincent

學不完．教不停．用不盡

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

學不完．教不停．用不盡