本帖最後由 Hsieh 於 2011-9-3 21:19 編輯
近來發現很多問題在於定義名稱的觀念
定義名稱的用途很廣，舉凡公式的簡化、跨工作表驗證、條件格式化、動態範圍的取得等等
用到定義名稱的地方很廣，但是在作定義名稱時經常無法達到自己想要的答案。
在此就本人學習經驗與大家分享，若有錯誤或不足之處還請各位前輩不吝指教，更希望能拋磚引玉，
請各位先進提出經驗與大家分享。
學習定義名稱，儲存格參照的觀念必須確實建立。
甚麼是參照?參照簡單的說就是儲存格與儲存格之間的對應關係
EXCEL說明中有一段結構化參照的元件
如附件參照.doc
 參照.rar (43.48 KB)
這段說明闡述工作表的資料表各個元件意義(這部分請自行下載檔案或EXCEL說明以關鍵字"參照"搜尋)
內有簡單範例請閱讀後若有不明之處再提出討論
認識資料表結構後，我們就開始來討論絕對參照與相對參照
這在一般設定公式或定義名稱時是極為重要的觀念
相信大家都知道在公式輸入時若加入$這時此儲存格位址即為絕對參照位址
如在B1輸入公式=$A$1
當你將B1公式向下拖曳複製時，你會發現B2，B3，B4....得到的公式一樣是=$A$1
這就是說在B欄公式中此A1是不隨著B欄列位改變而改變，他始終都會得到A1儲存格的值
若在B1輸入公式=$A1向下拖曳複製時，你會發現B2，B3，B4....得到的公式一樣是=$A2，=$A3，=$A4....
也就是說隨著列位的增加其參照到A欄的列位也跟著增加
若在B1輸入公式=$A1向右拖曳複製時，你會發現B1，C1，D1....得到的公式一樣是=$A1，=$A1，=$A1....
也就是說隨著欄位的增加其參照到A欄的欄位是不變的
所以可見=$A1這公式不論向右或向下複製，對到的欄位是不變的永遠是A欄
但是列位會因為公式所在位置而改變，所以$A1表是對A欄是絕對參照但列位因公式所在位置不同而改變所以列位是相對參照
有了參照的觀念及結構化參照元件的基礎了解，相信對於定義名稱的意義會有一點基本觀念了，誠如老夏與阿吉所說定義種類及使用對象不勝枚舉，小弟所學有限就針對目前常用到的儲存格物件的定義加以說明，因為這類的定義目前在使用上最為廣泛，由其是用在驗證以及條件格式化的設定極其重要，因為驗證公式及條件格式化公式是不允許跨工作表參照的，所以在下這些公式時為了要參照不同工作表時就必須使用定義名稱來達成，有時候驗證條件及格式條件又是一範圍儲存格，取決於另一範圍儲存格的計算結果來判斷其邏輯值，這時的參照其實是變動的，以下就以幾個實例來解釋定義名稱中的參照關係，以方便大家對驗證公式與格式條件公式的了解。
以最常被問到的二階層驗證選單為例:
我在Sheet1的A欄輸入第一階層清單內容A1:A3分別輸入A，B，C並定義該範圍名稱為List_1，做為第二層清單的依據
B1:D1輸入甲 乙 丙
B2:E2輸入子 丑 寅 卯
B3:F3輸入金 銀 銅 鐵 錫
做為第二層清單
H1做為輸入第一層清單的位置，其驗證清單公式就是=List_1
I1做為輸入第二層清單的位置，因為I1的清單來源我們是要依據H1所選內容來參照到A1:F3，的哪一列資料做為清單內容所以這時候如果要把公式一次寫在I1的驗證清單公式就成了
=OFFSET($A$1,MATCH($H$1,$A:$A,0)-1,1,,COUNTA(INDIRECT("B"&MATCH($H$1,$A:$A,0)&":F"&MATCH($H$1,$A:$A,0))))
這樣冗長的公式日後要修正或閱讀會令人眼花撩亂，這時候我們如果把這公式分解成幾部分，將這幾部份都定義成一個名稱，那麼日後我們要修正或閱讀時就輕而易舉了，而起這樣的公式如果要在其他工作表當成驗證清單公式時，將會出現不得參照其他工作表的錯誤，所以定義名稱是必須且實用的，現在我們先以單為H1參照所做的I欄清單內容公式作定義，也就是說在I欄所有存存格都是依照H1內容做為參考，不因列位不同而參照不同的H欄位儲存格，首先我們想到的是:清單範圍是一儲存格範圍，那麼有哪些函數的傳回值是儲存格範圍呢?第一個直接想到的就是INDIRECT和OFFSET這2個函數，那麼這2個函數要傳回範圍時就必須得到基準儲存格、位移、欄列大小這3個重要的參數值，所以我們就想辦法把上述的公式分解出我們想要的值做成定義,再將參數帶入函數即可得到我們想要的東西了
以上公式是我想到用OFFSET函數來傳回其範圍，
OFFSET(reference,rows,cols,height,width)傳回一儲存格範圍
reference就是基準點
rows就是位移列數
cols就是位移欄數
height就是範圍總列數
width就是範圍總欄數
所以
=OFFSET($A$1,MATCH($H$1,$A:$A,0)-1,1,,COUNTA(INDIRECT("B"&MATCH($H$1,$A:$A,0)&":F"&MATCH($H$1,$A:$A,0))))中
reference就是$A$1
rows就是MATCH($H$1,$A:$A,0)-1
cols就是1
height省略代表預設值1
width就是COUNTA(INDIRECT("B"&MATCH($H$1,$A:$A,0)&":F"&MATCH($H$1,$A:$A,0)))
當中rows跟width的公式中有一個相同的地方MATCH($H$1,$A:$A,0)
這就是在A欄中找到H1的位置
那麼我們就把這共同的公式定義一個名稱
所以rr=MATCH(Sheet1!$H$1,Sheet1!$A:$A,0)
你在任一儲存格輸入公式=rr得到的值將是H1在A欄中的列號
所以再把rr帶入OFFSET的公式
就得到=OFFSET($A$1,rr-1,1,,COUNTA(INDIRECT("B"&rr&":F"&rr)))
把這公式定義名稱為List
你將會發現不管你在Sheet1的哪一個儲存格做驗證清單公式=List
他都將會是以Sheet1的H1做判斷依據來找到第幾列做為清單內容，
如檔案二階選單_1
二階選單_1.rar (143.53 KB)
|
發表於 2010-8-9 22:40
| 
