Excel 高中生
註冊時間: 2003-11-21
文章: 335
 發表於: 星期四 一月 22, 2004 5:38 pm 文章主題: 超棒的地址輸入工具 |
|
| 沙拉油兄新年好，小弟研究您的地址輸入工具，很是欽羨，只是程度太差，無法了解公式的全意，可否請沙拉油兄做一個簡單的例子，如A1可選A'B'C' 當A1選A時，B1可選A-1'A-2'A-3,A1選擇B時，B1則可選B-1,B-2,B-3,麻煩您了！謝謝。 | |
台灣水電工
註冊時間: 2003-07-13
文章: 494
來自: 嘉義縣
| 發表於: 星期日 一月 25, 2004 8:08 pm 文章主題: |
|
| 定義名稱 >> 資料驗證
首先假設下圖資料在Sheet2，它可被看成四個區域，先依序定義名稱（資料驗證要跨越工作表就得定義名稱） 
先將黃色區域定義名稱為天干 再將綠色、粉紅色、灰色區域分別定義名稱為甲、乙、丙（選A1:D3>>插入>>名稱>>建立>>勾選最左欄>>確定） 切換到 Sheet1>>選 A1 儲存格>>資料>>驗證>>儲存格內允許選清單>>來源輸入=天干>>確定 選 B1 儲存格>>資料>>驗證>>儲存格內允許選清單>>來源輸入=indirect($A1)>>確定 完成 小弟也祝您新年快樂！！ |
|
Excel 高中生
註冊時間: 2003-11-21
文章: 335
| 發表於: 星期日 一月 25, 2004 10:48 pm 文章主題: 很棒的示範 |
|
| 謝謝沙兄的示範，真是神手其技，但是小弟有一點不解，那麼簡單就可作出的驗証清單，不能應用在沙兄您的大作-地址輸入工具裡嗎？如若不行，可否請教您地址輸入工具裡offset公式的設定嗎？(長長一大串小弟實在無法搞懂)謝謝！ | |
台灣水電工
註冊時間: 2003-07-13
文章: 494
來自: 嘉義縣
| 發表於: 星期日 一月 25, 2004 11:59 pm 文章主題: |
|||
在那個地址輸入工具當然也可以用上述的方法來作。 差別在於上述的方法是固定型態的名稱定義，而地址輸入工具則是動態的。 但想想若地址輸入工具用了固定型態的名稱定義， 則共得定義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 希望這樣的解釋不會很含糊！ |
|||
Excel 高中生
註冊時間: 2003-11-21
文章: 335
| 發表於: 星期一 一月 26, 2004 10:23 am 文章主題: 沙兄真是有教無類… |
|
| 很感謝沙兄這樣耐心的指導，對小弟這種程度有點像對牛彈琴，實讓小弟慚愧不已，小弟將您的教程copy在桌面，多花點時間研究，總有學會的一天，再說聲謝謝啦，祝身体健康萬事如意！ | |
Excel 國小生
註冊時間: 2004-01-17
文章: 24
來自: 雲林縣
| 發表於: 星期二 一月 27, 2004 5:44 pm 文章主題: |
|
| 沙拉油兄:
我是用定義名稱-資料驗證的方法做,還真累,而且本來想分村里後連路段都加以區分 但是怕excel無法定義那麼多,電腦跑不動. 您那可以活動參照的函數,我看了還是沒能明白,能否請沙拉油兄示一個範例,謝謝!! |
|
台灣水電工
註冊時間: 2003-07-13
文章: 494
來自: 嘉義縣
| 發表於: 星期三 一月 28, 2004 1:46 am 文章主題: |
|
| 看樣子好像滿難的，我可能把這個問題看得太簡單了......
好吧！那麼我們一步一步來吧！ 首先我希望你們有一個活頁簿如下圖： 
這是同一個活頁簿，並且開啟兩個視窗，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 若不懂意思請參考上面所給的連結位址。 打完了 |
|
Excel 國小生
註冊時間: 2004-01-17
文章: 24
來自: 雲林縣
| 發表於: 星期五 一月 30, 2004 6:05 pm 文章主題: |
|
| 沙拉油兄!參照的資料可不可以改為列,因為村里後面還有路段資料,255格不夠用! | |
台灣水電工
註冊時間: 2003-07-13
文章: 494
來自: 嘉義縣
| 發表於: 星期五 一月 30, 2004 6:13 pm 文章主題: |
|
| 可以啊！
不過 offset 你要記得改一下參照的位置。 |
|
Excel 國小生
註冊時間: 2004-01-17
文章: 24
來自: 雲林縣
| 發表於: 星期五 一月 30, 2004 6:20 pm 文章主題: |
|
| 是修改這個ㄇ?
參照到=OFFSET(資料!$A$1,,,COUNTA(資料!$A:$A),) 怎麼改,謝謝! |
|
台灣水電工
註冊時間: 2003-07-13
文章: 494
來自: 嘉義縣
| 發表於: 星期日 二月 01, 2004 3:39 pm 文章主題: |
|
| OFFSET有五個引數，你可以依照所輸入的引數來取得適當的儲存格區域
Offset(開始的儲存格，列位移，欄位移，包含的列數，包含的欄數） 列位移與欄位移沒輸入的話表示沒有位移 包含的列數與包含的欄數沒輸入的話則預設為1 |
|
Excel 國小生
註冊時間: 2004-01-17
文章: 24
來自: 雲林縣
| 發表於: 星期一 二月 02, 2004 5:48 pm 文章主題: |
|
| 瞭解了!謝謝指導!! | |