返回列表 上一主題 發帖

[分享] 定義名稱的迷思

[分享] 定義名稱的迷思

本帖最後由 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)
學海無涯_不恥下問

本帖最後由 HUNGCHILIN 於 2010-8-10 00:01 編輯

而定義可分成
隱藏與非隱藏
依開啟與使用SHEET還可再分成自動啟用與非自動啟用兩種
依使用中SHEET還可再分成私用與公用
依使用中區域還可再分成絕對相對與共用三種

各方面都可再更深入研究
會有更大的收獲

上述這文在精英發過一些
在4.0宏表專帖#20帖中
http://www.officefans.net/cdb/viewthread.php?tid=59862&extra=&page=1
以前4.0表對定義非常依賴
Hung-Chi Lin/林宏吉
HUNGCHILIN/林宏吉的OFFICE專欄

TOP

讓名稱返回當前工作表的一個技巧

本帖最後由 et5323 於 2010-9-20 16:57 編輯

在Sheet1堜w義一個名稱:rng,引用位置:= $A$1,那麽在別的工作表堥洏帠o個名稱:=rng,返回的還是Sheet1釷1的值,而不是當前工作表釷1的值。這埵陪荍犍屆G引用位置:= !$A$1,即在前面加個“!”,則名稱返回的是當前工作表釷1的值。有兩個問題要注意:
1.這種方式不能用在工作表的公式堶情A只能用在名稱堙F
2.具又“易失性”,即A1有變化,名稱rng不會反映出來。解決辦法加T(NOW()):=!$A$1&T(NOW()),這時rng返回的是字符串,若要轉換為數值:= Value(!$A$1&T(NOW()))

Book1.rar (4.34 KB)

1

評分人數

TOP

本帖最後由 Hsieh 於 2010-9-23 13:47 編輯

回復 1# et5323
這就是所謂的公用定義
很好的心得分享
用t(now())使成為易失性公式
這招用得很妙
感謝分享
學海無涯_不恥下問

TOP

哈哈,受教了!

TOP

好技巧
多謝 et5323 兄提供分享 !!!
300 字節以內
不支持自定義 Discuz! 代碼

TOP

學習了
知之為知之,不知為不知,誠實也!

TOP

很不錯的idea
這篇最有價值的部份在 T(NOW()) 這個函數應用在定義
謝謝
Hung-Chi Lin/林宏吉
HUNGCHILIN/林宏吉的OFFICE專欄

TOP

看起來似乎有點複雜...
但是又多學習到了新的東西

TOP

完全不懂,努力學習中~~~

TOP

        靜思自在 : 我們最大的敵人不是別人.可能是自己。
返回列表 上一主題