學不完．教不停．用不盡

如果在 Excel 中要設計二層的下拉式選單，可以配合使用「資料驗證」功能來設計，當原始資料為橫式或是直式時，設計的方式並不相同。

一、原始資料為橫式

參考下圖右側，原始資料是橫式呈現。

參考以下步驟來設計：

1. 將儲存格D1:J1定義名稱：商品類別1。

2. 選取儲存格D1:D4，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：上衣。

依此方法，定義名稱：長褲、短褲、長裙、短裙、外套、帽子。

3. 選取儲存格A2，設定資料驗證來產生下拉式選單。

4. 在[資料驗證]對話框中設定：

儲存格內允許：清單；來源：=商品類別1(已定義的名稱)。

5. 依儲存格A2，在儲存格B2要顯示其下的商品編號。

6.在[資料驗證]對話框中設定：

儲存格內允許：清單；來源：=INDIRECT(A2)。

透過 INDIRECT 函數將儲存格A2中的文字轉換為位址(先前已定義儲存格範圍)。

 

二、原始資料為是直式

觀察下圖，原始資料是直式呈現，而且同商品已經集合在一起了。這樣就可以少掉許多定義名稱的步驟。

1. 將儲存格D2:D8定義名稱：商品類別2。將儲存格E2:E21定義名稱：商品清單。

2. 選取儲存格A2，設定資料驗證來產生下拉式選單。

3. 在[資料驗證]對話框中設定：

儲存格內允許：清單；來源：=商品類別2(已定義的名稱)。

4. 定義名稱「商品編號」，其參照設定為：(假設工作表名稱：DATA2)

=OFFSET(DATA2!F1,MATCH(DATA2!A2,商品清單,0),,COUNTIF(商品清單,DATA2!A2),)

MATCH(DATA2!A2,商品清單,0)：使用 MATCH 函數查詢儲存格A2在商品清單中的第幾列。

COUNTIF(商品清單,DATA2!A2)：使用 COUNTIF 函數計算儲存格A2在商品清單中共有幾個。

將幾上二式代入 OFFSET 函數，取得儲存格A2在商品清單中的儲存格範圍。(以短褲為例，傳回F7:F9)

5. 選取儲存格B2，設定資料驗證來產生下拉式選單。

6. 在[資料驗證]對話框中設定：

儲存格內允許：清單；來源：=商品編號(已定義的名稱)。