2016年4月24日 星期日

Excel-建立名稱配合INDIRECT執行查表工作

有網友想要了解 INDIRECT 函數的應用,特別用以下的例子來說明。
其中工作表是一個成績的清單,包含了姓名和五個科目的成績。我們要來利用這個成績表,製作一個可以查詢不同姓名對照的各科成績。
Excel-建立名稱配合INDIRECT執行查表工作

【公式設計與解析】
我們要使用 INDIRECT 函數前先定義一些用的到的名稱,當你的名稱定義的愈仔細,則相對可以讓公式變的較簡潔。
首先,選取儲存格B1:G25,按 Ctrl+Shift+F3 鍵,勾選:
頂端列:定義名稱『姓名、國文、英文、數學、社會、自然』
最左欄:定義名稱『陳郁婷、劉維力、李雨潔、 ... 、周于廷、賴詩柔』。
到名稱管理員中,即可以看到所定義的名稱內容:
image
當需要的名稱都定義好了,接著輸入公式:
(1) 查詢各科成績
儲存格J2:=INDEX(INDIRECT($J$1),ROW(1:1))
複製儲存格J2,貼至儲存格J2:J6。
INDIRECT($J$1):利用 INDIRECT 函數將儲存格J1的內容轉換為儲存格位址,本例儲存格J1為『陳郁婷』,所以 INDIRECT("陳郁婷") = { "91","77","92","48","67" }
ROW(1:1) 在公式向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ ... ROW(5:5)=5。
最後利用 INDEX 的查詢功能,找出第 1 個元素:91、第 2 個元素:77、 ... 、第 5 個元素:67。

(2) 查詢各科平均分數
儲存格K2:=AVERAGE(INDIRECT(I2))
INDIRECT(I2):利用 INDIRECT 函數將儲存格I2的內容轉換為儲存格位址,本例儲存格I2為『國文』,所以 INDIRECT("國文") = { "91","68","57", ... , "71","40" }
再透過 AVERAGE 函數將整個陣列內容予以平均。
複製儲存格K2,貼至儲存格K2:K6。

【參考資源】
如果想要查詢以前寫過和 INDIRECST 函數有關的文章,請參考:點選這裡

【延伸練習】
如果你不使用 INDIRECT 函數,則可以改用以下的公式:
儲存格J2:=VLOOKUP($J$1,$B$2:$G$25,ROW(2:2),FALSE)
或是
儲存格J2:=INDEX($C$2:$G$25,MATCH($J$1,$B$2:$B$25,0),ROW(1:1))

沒有留言:

張貼留言

檢視其他文章

好康東東