Pages

Subscribe:

Ads 468x60px

Labels

2013年8月24日 星期六

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

沒有留言:

張貼留言