有位老師問到:

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

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

 

【公式設計與解析】

關於這個問題,因為老師想要將各個月的課程內容放在不同的工作表,所以依老師的想法設計公式。

1. 安排工作表

以 10月、11月、12月為例:

(1) 將每不同月份都以相同格式安排,只是其中的內容不同而已。

(2) 依序將工作表名稱命名為:10月、11月、12月

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

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

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

 

2. 設計查詢公式

(1) 在儲存格B1輸入月份

因為儲存格B1輸入的內容要與工作表名稱一致,建議使用「資料驗證」方式來建立下拉式清單以方便選取。

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

(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 欄內容(時間、地點和攜帶物品)。

arrow
arrow
    全站熱搜

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