2015年9月9日 星期三

Excel-製作二層的下拉式清單來輸入資料(INDIRECT,資料驗證)

網友問到要在 Excel 中設計一個二層的下拉式清單,方便來輸入資料。如下圖的範例中,已經有「季別、月份、星期、天干、地支」等五類的資料,每一類中各個一些資料項目。如何能根據選取的不同類別,在「項目」中顯示不同的項目清單?
大多數網友應該都是不想寫程式,想要直接使用公式等方式來達到這個效果。建議藉助「資料驗證」功能和 INDIRECT 函數來搭配,也可以做到這個效果。

【設計與解析】
1. 先為每個資料類別定義一個名稱,例如:
(1)選取儲存格D1:D5,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:季別。
(2)選取儲存格E1:E13,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:星期。
(3)選取儲存格F1:F8,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:天干。
(4)選取儲存格G1:G13,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:地支。

2.設定類別選項的下拉式清單。
(1) 選取儲存格B1,在[資料/資料工具]功能表中設定「資料驗證」。
(2) 設定資料驗證準則,儲存格內允許:清單,來源:$D$1:$H$1。
儲存格D1:H1分別為類別的名稱,作為下拉式清單的內容。

3.設定項目選項的下拉式清單。
(1) 選取儲存格B2,在[資料/資料工具]功能表中設定「資料驗證」。
(2) 設定資料驗證準則,儲存格內允許:清單,來源:=INDIRECT($B$1)。
將儲存格B1的內容透過 INDIRECT 函數轉換為儲存格範圍,作為下拉式清單的內容。

【延伸思考】
請問:如果要依此例,設計第三層的下拉式清單,該如何處理?

沒有留言:

張貼留言

好康東東