注意：最長位數到 Double 的上限，1 開頭是 16 位數，其他開頭是 15 位數，超過此長度的話，請改用字串格式，也就是 '1234567890123456790
2014年3月7日 星期五
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
之前介紹過一個二階式的下拉選單，這樣可以更輕鬆的輸入資料
而更有許多人在使用上，需要三階、四階選單，甚而更多階的下拉選單
通常參照這些清單位置，會以定義名稱來處理，這裡介紹不須定義名稱，多階式下拉選單的簡便作法
假設下圖為我們所需要輸入的表格:
每一個欄位的下拉選單，都須依照上一個選項而決定內容
而首先，我們必須先建立一個下拉選單對應的資料庫，如下圖
我們必須了解的是，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
火星人的天空之城: Excel-使用多層下拉式清單結構輸入資料(進階)
火星人的天空之城: Excel-使用多層下拉式清單結構輸入資料(進階): 前一篇文章提到：使用多層下拉式清單結構輸入資料(基礎) 參考網址： http://isvincent.blogspot.com/2010/06/excel_25.html 這次要來練習：不需要每個年級都定義一個名稱，而是要以整個基本資料表為單位來查詢。 ...
火星人的天空之城: Excel-使用多層下拉式清單結構輸入資料(基礎)
火星人的天空之城: Excel-使用多層下拉式清單結構輸入資料(基礎): 在Excel中輸入資料，如果完全以Key In方式輸入，除了耗費較多時間之外，還可能打錯字。所以如果能以選取方式來輸入資料，應該可以改善這些問題。解決這個問題，利用資料驗證、INDIRECT函數和定義名稱，可以很簡單的解決。 假設要有三個年級，每個年級的班級名稱都不相同，規則...
2013年5月29日 星期三
2013年5月22日 星期三
2013年5月17日 星期五
訂閱：
文章 (Atom)