資料來源 http://oilonline.myweb.hinet.net/example/excel/address.zip
定義名稱 >> 資料驗證
首先假設下圖資料在Sheet2，它可被看成四個區域，先依序定義名稱（資料驗證要跨越工作表就得定義名稱） 
先將黃色區域定義名稱為天干
再將綠色、粉紅色、灰色區域分別定義名稱為甲、乙、丙（選A1:D3>>插入>>名稱>>建立>>勾選最左欄>>確定）
切換到 Sheet1>>選 A1 儲存格>>資料>>驗證>>儲存格內允許選清單>>來源輸入=天干>>確定
選 B1 儲存格>>資料>>驗證>>儲存格內允許選清單>>來源輸入=indirect($A1)>>確定
完成
在那個地址輸入工具當然也可以用上述的方法來作。
差別在於上述的方法是固定型態的名稱定義，而地址輸入工具則是動態的。
但想想若地址輸入工具用了固定型態的名稱定義，
則共得定義1個中華民國+25個縣市+369個鄉鎮市區＝共得定義395個固定的名稱。(369個鄉鎮市區，我是參考這裡的，不確定對不對！）
Excel的名稱定義能定義幾個雖然受限制於記憶體，
但是若我在那個檔案內定義了395個名稱，那......我實在是無顏面對江東父老了。
395個名稱實在太多了也太亂了一點。
先說說那個檔案裡面有什麼？
資料>>驗證（這我想您應該已經知道了）參考這裡！
絕對參照與相對參照（這個好像很多人都肯定知道）參考這裡！
定義名稱（這個好像沒什麼人喜歡用）參考這裡！
什麼不知道呢？動態名稱不知道吧？！我想
那就來解釋動態名稱吧！
在該檔案的動態名稱當中用了幾個不知道的函數，分別是 OFFSET、MATCH、COUNTA
MATCH 則是在儲存格陣列中找指定的資料，找到後會傳回該資料是該儲存格陣列中的第幾個？
關於 MATCH 函數詳細資訊
Excel XP版本參考 mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office10\1028\xlmain10.chm::/html/xlfctMATCH.htm
Excel 2000版本參考 mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office\1028\xlmain9.chm::/html/xlfctMATCH.htm
OFFSET 這可能就是關鍵了
OFFSET它可以傳回一個經過位移後的指定儲存格的資料，也可以傳回一個儲存格區域。
而動態名稱就是利用了它傳回一個儲存格區域給該名稱。
在地址輸入工具裡面的動態名稱裡面的 OFFSET 經過了 COUNTA 的計算、MATCH 的比對後傳給了OFFSET 正確的個數與儲存格位址
於是該使用 OFFSET 的名稱正確的得到了 OFFSET 所傳回的儲存格區域。
關於 OFFSET 函數詳細資訊
Excel XP版本參考 mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office10\1028\xlmain10.chm::/html/xlfctOFFSET.htm
Excel 2000版本參考 mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office\1028\xlmain9.chm::/html/xlfctOFFSET.htm
看樣子好像滿難的，我可能把這個問題看得太簡單了......
好吧！那麼我們一步一步來吧！
首先我希望你們有一個活頁簿如下圖： 
這是同一個活頁簿，並且開啟兩個視窗，Book1:1 及 Book1:2
（用視窗>>新增視窗可以你要開幾個視窗就幾個，並請再按一下視窗>>重排視窗>>選擇垂直並排）
好了，現在你的 Excel 看起來如上圖了嗎？左邊是輸入資料表右邊是資料資料表
開始來定義名稱了.........
<一> 首先先切換到 Book1:1 視窗，也就是在資料資料表
選 A1 >> 插入 >> 名稱 >> 定義
現有名稱輸入第一層
參照到輸入=OFFSET(資料!$A$1,,,COUNTA(資料!$A:$A),)
然後按新增
定義第一個動態名稱完成了，名稱就是第一層
現在確定一下名稱內的資料是不是正確，先將定義名稱對話方塊移出 Excel 視窗，不要讓它檔到右邊那個資料資料表就行了，
現在點選一下定義名稱對話方塊內的名稱第一層再點選一下參照到內的公式，
這時候你應該會看到右邊那個資料資料表裡的A1:A4的外框會有一條虛線在旋轉，
確定名稱第一層的資料無誤後就可以關閉定義名稱對話方塊了。
<二> 對輸入資料表的A2使用資料驗證
切換到 Book1:2 視窗，也就是在輸入資料表
選 A2 >> 資料 >> 驗證 >> 儲存格內允許選清單，來源輸入=第一層 >> 確定
現在A2應該可以選擇了，先選一個資料，就選台北市好了。
<三> 再來我們要定義第二及第三個名稱（本來是可以定義兩個名稱就好了，為了方便理解我就分成三個好了）
選B2 >> 插入 >> 名稱 >> 定義
現有名稱輸入 buf ，參照到輸入=OFFSET(資料!$A$1,MATCH(輸入!$A2,第一層,0)-1,1)
注意這個公式裡面的 MATCH(輸入!$A2,第一層,0) 用的是列相對，如果你的輸入資料表的第3、4、5.........列都要用資料驗證的下拉式選單的話，那就得注意名稱定義時，公式若參照到輸入資料表的儲存格時該儲存格位址就得用列相對。
當你用步驟<一>方法確定名稱所參照到的儲存格時，你應該會看到虛線是落在資料資料表的 B1 儲存格，
這個名稱的作用是要找到下一個名稱的資料的起始點，並簡短下一個名稱的公式的長度。
現在要定義第三個名稱了，定義視窗對話方塊應該還沒關掉吧？！如果關了的話請切換到輸入資料表然後選B2 >> 插入 >> 名稱 >> 定義
現有名稱輸入第二層，參照到輸入=OFFSET(buf,,,,COUNTA(OFFSET(buf,,,,255)))
解釋一下 OFFSET(buf,,,,COUNTA(OFFSET(buf,,,,255)))
COUNTA(OFFSET(buf,,,,255)) 是要計算從 buf 所參照的儲存格向右包含的 1 列 255 欄的儲存格（也就是 B1:IV1）共有幾個儲存格內有資料。這個例子會傳回 5
為什麼是255呢？Excel 有256欄，理論上名稱第二層所傳回的儲存格區域會從B欄開始，所以256-A欄（第一欄）＝255。
但是這個名稱可能會因你在資料資料表的A欄插入欄而造成這個名稱的參照出現錯誤，所以你若擔心會參照出錯的話，可以把 255 改成 256-COLUMN(buf)+1 ，至於函數 COLUMN 的意思在這裡就不解釋了。
所以在本例中的COUNTA(OFFSET(buf,,,,255)) 會傳回5
而外層的 offset 就會變成是 OFFSET(buf,,,,5) 也就是傳回從資料資料表的B1向右包含的 1 列 5欄，當你用步驟<一>方法確定名稱所參照到的儲存格時，你應該會看到虛線是落在資料資料表的 B1:F1 儲存格
名稱定義結束了，我們總共定義了三個名稱，應該知道意思了吧？！
步驟四我看我順便講一下好了，以免有人不知道我們在討論沙瞇碗糕。
<四> 對輸入資料表的B2使用資料驗證
切換到 Book1:2 視窗，也就是在輸入資料表
選 B2 >> 資料 >> 驗證 >> 儲存格內允許選清單，來源輸入=第二層 >> 確定
現在B2也應該可以選擇了，選擇的條件是依照A2的縣市選該縣市下的鄉鎮市區...................
這裡只討論兩層，第三、四、五.....層的作法跟第二層的作法基本上是一樣的
還有若你想省略那個名稱 buf ，你可以直接把 buf 的公式複製到名稱第二層內的公式內有出現 buf 的地方，然後就可以刪除 buf 了。
函數 OFFSET 若不懂意思請參考上面所給的連結位址。
打完了
留言列表
