EXCEL 下拉式選單 - 多階式

EXCEL的下拉選單，大家都知道用資料驗證>>清單，這樣作下拉選單，又快速又輕鬆
之前介紹過一個二階式的下拉選單，這樣可以更輕鬆的輸入資料
而更有許多人在使用上，需要三階、四階選單，甚而更多階的下拉選單
通常參照這些清單位置，會以定義名稱來處理，這裡介紹不須定義名稱，多階式下拉選單的簡便作法

假設下圖為我們所需要輸入的表格:


每一個欄位的下拉選單，都須依照上一個選項而決定內容
而首先，我們必須先建立一個下拉選單對應的資料庫，如下圖


我們必須了解的是，EXCEL驗證清單，是不允許參照其他工作表的
所以比較簡單的作法，是將資料放在同一個工作表中，而針對對應的驗證清單，以不同的公式帶出
如下圖


各欄驗證清單公式如下

A2
=OFFSET($H$1,1,0,COUNTA(H:H)-1,1)

B2
=OFFSET(INDIRECT("H"&MATCH($A2,$H:$H,0)),0,1,1,4)

C2
=OFFSET(INDIRECT("N"&MATCH($B2,$N:$N,0)),0,1,1,3)

D2
=OFFSET(INDIRECT("S"&MATCH($C2,$S:$S,0)),0,1,1,3)

E2
=OFFSET(INDIRECT("X"&MATCH($D2,$X:$X,0)),0,1,1,4)

完成第2列的設定後，在將儲存格往下複製即可~
這樣，馬上就完成一個多階式的下拉選單了
可以參考以下附檔
http://www.FunP.Net/28199


當然，B2:E2 簡便一點，設一個公式就好
在B2的資料驗證清單，利用以下公式
=OFFSET(INDIRECT("R"&MATCH(A2,OFFSET($A$1,,MATCH(A$1,$H$1:$AB$1,)+6,100,1),0)&"C"&MATCH(A$1,$H$1:$AB$1,)+7,),0,1,1,4)

再將B2複製到B2:E10  這樣也可以囉~
可以參考以下附檔
http://www.FunP.Net/574188


因為多階式清單的資料庫，通常很多資料，與輸入表單放在同一個工作表，總會有很混亂的感覺
因此通常會將多階式清單的資料庫放在其他的工作表，再利用定義名稱設定來引用
這裡介紹一個不用定義名稱的方法，但是公式可會複雜多了~
假設輸入表單在"MENU"工作表，表單資料在"DATA"工作表，如下圖






則
MENU!A2驗證公式為
=INDIRECT("DATA!A2:A10")

B2
=OFFSET(INDIRECT("DATA!A1"),MATCH(A2,INDIRECT("DATA!A:A"),)-1,1,1,3)

C2
=OFFSET(INDIRECT("DATA!G1"),MATCH(B2,INDIRECT("DATA!G:G"),)-1,1,1,3)

D2
=OFFSET(INDIRECT("DATA!L1"),MATCH(C2,INDIRECT("DATA!L:L"),)-1,1,1,3)

E2
=OFFSET(INDIRECT("DATA!Q1"),MATCH(D2,INDIRECT("DATA!Q:Q"),)-1,1,1,3)

與之前一樣，第二列設訂完成後，再往下複製即可完成~
可以下載以下附件參考~
http://www.FunP.Net/122712


如果嫌棄這樣逐一設定太麻煩，B2:E2 也可以用一個公式來設定，
不過這得挑戰您對公式函數的運用能力了
B2:E10 
=OFFSET(INDIRECT("DATA!A1"),MATCH(A2,OFFSET(INDIRECT("DATA!A1"),,MATCH(A$1,INDIRECT("DATA!$1:$1"),)-1,100,1),)-1,MATCH(A$1,INDIRECT("DATA!$1:$1"),),1,3)



參考檔案~
http://www.FunP.Net/412720

以上介紹為不使用定義名稱時，多階式下拉選單作法
其重點多在公式函數的運用，當驗證清單跨工作表時，常常必須使用定義名稱，來簡化公式
以後有機會在來介紹包含定義名稱的多階式下拉選單吧~

http://tw.myblog.yahoo.com/vincent-excel/article?mid=2217&sc=1