標題:
[分享]
定義名稱的迷思
[打印本頁]
作者:
Hsieh
時間:
2010-8-9 22:40
標題:
定義名稱的迷思
本帖最後由 Hsieh 於 2011-9-3 21:19 編輯
近來發現很多問題在於定義名稱的觀念
定義名稱的用途很廣，舉凡公式的簡化、跨工作表驗證、條件格式化、動態範圍的取得等等
用到定義名稱的地方很廣，但是在作定義名稱時經常無法達到自己想要的答案。
在此就本人學習經驗與大家分享，若有錯誤或不足之處還請各位前輩不吝指教，更希望能拋磚引玉，
請各位先進提出經驗與大家分享。
學習定義名稱，儲存格參照的觀念必須確實建立。
甚麼是參照?參照簡單的說就是儲存格與儲存格之間的對應關係
EXCEL說明中有一段結構化參照的元件
如附件參照.doc[attach]2316[/attach]
這段說明闡述工作表的資料表各個元件意義(這部分請自行下載檔案或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[attach]7709[/attach]
作者:
HUNGCHILIN
時間:
2010-8-9 23:51
本帖最後由 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表對定義非常依賴
作者:
et5323
時間:
2010-9-20 16:42
標題:
讓名稱返回當前工作表的一個技巧
本帖最後由 et5323 於 2010-9-20 16:57 編輯
在Sheet1裏定義一個名稱：rng，引用位置：= $A$1，那麽在別的工作表裏使用這個名稱：=rng，返回的還是Sheet1裏A1的值，而不是當前工作表裏A1的值。這裏有個技巧：引用位置：= !$A$1，即在前面加個“!”，則名稱返回的是當前工作表裏A1的值。有兩個問題要注意：
1．這種方式不能用在工作表的公式裏面，只能用在名稱裏；
2．具又“易失性”，即A1有變化，名稱rng不會反映出來。解決辦法加T(NOW())：=!$A$1&T(NOW())，這時rng返回的是字符串，若要轉換為數值：= Value(!$A$1&T(NOW()))
作者:
Hsieh
時間:
2010-9-20 19:56
本帖最後由 Hsieh 於 2010-9-23 13:47 編輯
回復
1#
et5323
這就是所謂的公用定義
很好的心得分享
用t(now())使成為易失性公式
這招用得很妙
感謝分享
作者:
oobird
時間:
2010-9-20 23:26
哈哈，受教了！
作者:
zz5151353
時間:
2010-9-22 23:12
好技巧
多謝 et5323 兄提供分享 !!!
作者:
gong
時間:
2010-9-25 21:08
學習了
作者:
HUNGCHILIN
時間:
2010-9-30 00:13
很不錯的idea
這篇最有價值的部份在 T(NOW()) 這個函數應用在定義
謝謝
作者:
ryu0417
時間:
2010-11-15 15:39
看起來似乎有點複雜...
但是又多學習到了新的東西
作者:
tador
時間:
2010-11-21 22:06
完全不懂，努力學習中~~~
作者:
jcman
時間:
2011-3-29 11:43
學到新東西了,雖然看的眼花撩亂,謝謝
作者:
absggyy
時間:
2011-5-10 11:45
獲益良多 來試試看 感謝分享
作者:
Happkkevin
時間:
2011-6-26 04:55
本帖最後由 Happkkevin 於 2011-6-26 05:04 編輯
還在研究這篇文，但權限還無法下載附件，研究文字敘述中
但關於絕對參照位址 與相對參照位址，是VBA 基本概念與 函數很重要的元素
在函數使用，我常常用到，分享個應該常用到但初學者可能常不知的快速鍵
選取欲更改的對象，如函數中的A1，不須輸入$，使用F4鍵快速變換 於$A$1、A$1、$A1、A1、$A$1、、、
感謝分享關於名稱應用，於使用函數可以階層分析後，將共用者以名稱導出簡化函數與便於解讀
只是看到前輩的使用，我還有很多可以精進的地方，受教不少，感恩
作者:
ky2599y
時間:
2011-7-13 16:00
感謝分享，讓我的EXCEL功力大增
作者:
freeffly
時間:
2011-8-11 16:11
讓名稱返回當前工作表的一個技巧
這個技巧還沒看到之前真的困擾我好久
謝謝分享
作者:
cdw549
時間:
2011-8-26 13:05
真的有點複雜，
好好來學習學習。
謝謝您的分享。
作者:
infoverdad
時間:
2011-9-3 11:06
本帖最後由 infoverdad 於 2011-9-3 11:13 編輯
回復
1#
Hsieh
謝謝版大無私的分享讓我們受益良多!!
但附件中的公式以絕對位址$H$1的地方是否須稍作調整(改成$H1)?
1.工作表"Original" rr=MATCH(Orginal!$H
$
1,Original!$A:$A,0) =>
工作表"revised" rr=MATCH(revised!$H
1
,revised!$A:$A,0)
2. j1資料驗證準則【來源】
=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)))) 改成
=OFFSET($A$1,MATCH($H1,$A:$A,0)-1,1,,COUNTA(INDIRECT("B"&MATCH($H
1
,$A:$A,0)&":F"&MATCH($H
1
,$A:$A,0))))
[attach]7681[/attach]
作者:
Hsieh
時間:
2011-9-3 21:21
回復
17#
infoverdad
多謝指正，沒錯列應該使用相對參照，才會隨著同列變化清單
附件已更新
作者:
dr.step
時間:
2011-9-7 12:20
T(NOW())....
這好似說.窮則變.變則通的道理
作者:
elsajminmickey
時間:
2011-9-13 22:55
霧裡看花
該來去買書翻一番
還停留在使用簡單函數的階段
作者:
frankieyim
時間:
2011-9-16 10:47
看起來似乎有點複雜...
但是又多學習到了新的東西
作者:
lady_kikoko
時間:
2011-9-17 12:03
有那麼一點點概念了, 3q
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
