學校統計學生資料,需先選「科室」,再根據科室顯示對應「班級」。利用「定義名稱」+ INDIRECT 函數,可輕鬆實現二級聯動下拉選單,避免龐大單一選單的操作困擾。
一、建立基礎資料表(工作表命名為「基礎資料」)
- A~D 欄分別輸入四個科室的班級清單,首列為科室名稱(如「微機科」、「網管科」...)。
- E 欄輸入所有科室名稱(E1=「科室」,E2 以下列出各科室)。
二、定義名稱(關鍵步驟)
- 選取 A 欄區域 → 「公式」索引標籤 → 「定義名稱」群組 → 「定義名稱」 → 名稱輸入
微機科(對應科室名)
。
- 同樣方式將 B、C、D 欄分別定義為對應科室名稱。
- 選取 E 欄科室清單區域 → 定義名稱為
科室。
原理:各科室名稱成為「名稱」,參考該科室班級清單區域。E 欄名稱為「科室」,參考所有科室名稱清單。
三、設定二級聯動下拉選單
1. 第一層:科室下拉選單(B2:B6)
- 選取 B2:B6 → 「資料」 → 「資料驗證」 → 「設定」 → 「允許」選「序列」。
- 「來源」 輸入:
=科室(引用 E 欄科室清單)
。
2. 第二層:班級下拉選單(C2:C6)
- 選取 C2:C6 → 「資料驗證」 → 「序列」。
- 「來源」 輸入:
=INDIRECT($B2)
。
關鍵解析:
$B2:鎖定欄($B),不鎖定列(2)。每列根據該列 B 欄選取的科室名稱,動態引用對應的定義名稱區域。
INDIRECT(參考文字):將文字字串轉換為實際儲存格參考。B2 選「微機科」→ INDIRECT("微機科") 解析為 A 欄班級區域。
效果展示
點選 B 欄下拉選科室 → C 欄下拉自動顯示該科室班級清單
。資料錄入快速且準確。
擴充應用
- 三級聯動:縣市 → 鄉鎮 → 村里(需定義更多名稱)。
- 搭配
VLOOKUP / XLOOKUP 自動帶入其他欄位(如科室代碼、班級導師)。