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

一、原始資料為橫式

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

參考以下步驟來設計:

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

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

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

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

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

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

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

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

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

 

二、原始資料為是直式

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

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

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

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

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

Excel-二層下拉式清單的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)

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)

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

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

Excel-二層下拉式清單的2種設計方式(橫式資料和直式資料)(資料驗證)v

    全站熱搜

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