2015年9月29日 星期二

Excel-跨工作表查詢資料(VLOOKUP,INDIRECT,COLUMN)

有位老師問到:
如果在 Excel 中每一個月有不同內容的多個工作表,其內容是某些課程的時間、地點和攜帶物品的明細表。如何能在輸入某個月份時,即能列出該月份的各個課程之時間、地點和攜帶物品,以方便提醒每位小朋友呢?

【公式設計與解析】
關於這個問題,因為老師想要將各個月的課程內容放在不同的工作表,所以依老師的想法設計公式。
1. 安排工作表
以 10月、11月、12月為例:
(1) 將每不同月份都以相同格式安排,只是其中的內容不同而已。
(2) 依序將工作表名稱命名為:10月、11月、12月

2. 設計查詢公式
(1) 在儲存格B1輸入月份
因為儲存格B1輸入的內容要與工作表名稱一致,建議使用「資料驗證」方式來建立下拉式清單以方便選取。
(2) 輸入公式
儲存格C1:
=VLOOKUP($B3,INDIRECT($B$1&"!$A$2:$D$4"),COLUMN(B:B),FALSE)
複製儲存格C1,貼至儲存格C1:E10。
INDIRECT($B$1&"!$A$2:$D$4"):透過 INDIRECT 函數將儲存格B1的內容轉換為工作表名稱,而參照不同工作表的格式為:「'工作表名稱'!儲存格範圍」。
COLUMN(B:B):當公式向右複製時,COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4→...。用以在 VLOOKUP 函數中顯示儲存格A2:D4的第 2, 3, 4 欄內容(時間、地點和攜帶物品)。

沒有留言:

張貼留言

檢視其他文章

好康東東