[E2-04]名稱管理器

想當年，我也曾是走路自帶T台的風流少年，幻想通過文字走向人生巔峰。

我給自己想好了行走文壇的筆名就叫做「西門吹風」（嚴肅的「西門」複姓加上市井的「吹風」場景，完美的一塌糊塗，解構又重構本來就是我們作家最熱衷的事情），EXCEL算個毛，那隻不過是我用來記賬的工具而已。

一個月在EXCEL上記一筆，最多十二行就夠，每月收支兩個單元格的事情，大致記賬內容我認為是這樣的：

第一個月，收入50萬，支出3000（嗯，我去吃了個人均3000的自助餐，一個人去的）

第二個月，收入60萬，支出0（嗯，狂熱的粉絲把我抬過去又吃了一頓自助，不用我買單）第三個月，收入70萬，支出1元（從我們家客廳騎摩拜單車去卧室睡覺，距離比較遠，超過半小時了，收了我1元，可恨）

第四個月，收入0，支出8000元（護士加大電擊量把我電醒了，8000是治療費）

好吧，又是夢一場，除了「西門吹風」這個妖嬈的名字以及EXCEL這個妖嬈的工具以外，由於護士電擊電壓太大，我已記不清別的什麼。

於是，我今天能和你聊的只是EXCEL+名稱這件事情，它叫做「名稱管理器」。

1.基本概念

「名稱管理器」貴為四大神器之一，且需要單獨一章講解，自然有其過人之處。為此，我們先理清幾個基本問題。

何謂名稱？在EXCEL語境下，名稱是一種特殊的公式，同樣以「=」的方式或者作為參數的方式被使用。名稱可以由常量、常量數組、單元格引用、單元格區域引用、函數與公式等元素組成，已定義的名稱可以被其他名稱嵌套使用，也可以被其他公式調用。

你在辦公室格子間可能叫做vivian、tony總監、pony、jackMa等等，春節回到你八百年魂牽夢繞的老家你的名字又變成了「翠花、狗剩、花藤、小雲」等等，這些無一例外都是名稱，EXCEL里的名稱也基本是這個意思，無非起名對象是單元格或數據對象而不是人的區別而已。

需要注意的是，EXCEL名稱命名也有一些限制，不能以數字開頭、不能以R、C、r、c作為名稱名、不能使用除下劃線、點號、和反斜線以外的其它符號、字元不能超過255個。可以使用漢字，使用英文的話字母不區分大小寫。

使用「名稱」能帶來如下好處:

1. 方便輸入(輸入「部門」比輸入F4:F11方便)。
2. 方便閱讀(閱讀「單價」比A2更清楚)。
3. 方便修改(僅僅修改名稱對應的內容，所有引用名稱的地方全部自動修改)。
4. 有利於簡化公式。
5. 可代替單元格區域存儲常量數據(常量或常量數組可直接命名為名稱，無需對應到單元格)。
6. 突破數據驗證序列只能是單列數據的限制。
7. 解決數據驗證和條件格式無法使用常量數組、交叉引用的問題。
8. 暫時沒想到第8個好處，7個也差不多可以說服你了吧。

2.名稱管理器

既然名稱有這麼多好處，該如何創建和使用呢？有三個辦法。1. 使用名稱框快速創建看下圖說話。首先，選中單個或者連續多個或者不連續多個單元格；然後在下圖左上方那個籃框就是名稱框的地方手工輸入任何一個名稱符號要求的名稱如「狗剩」；這就完成了名稱的創建。

怎麼知道創建是否成功呢？兩個辦法，一是滑鼠點選其他工作表或單元格之後，只要在名稱框中下拉選中「狗剩」，就能看到上圖這種單元格被選中的效果。二是打開[公式-名稱管理器]，能看到類似下圖的一行:

容我再交代幾句:

• 名稱的作用域默認是整個工作簿。每個名稱的作用域可以通過上圖名稱管理器的「範圍」列查看。
• 名稱對單元格區域的引用模式默認是是絕對引用方式，見上圖下方「引用位置」部分的地址表示。
• 請相信微軟研發工程師的智商，儘管上述兩個默認都是可以修改的，但我強烈建議你不要這麼做。
• 名稱與引用區域之間的對應關係是彈性自適應的。比如你在上圖的單元格區域的中間部分刪除或者增加內容，該名稱對應的區域是自動擴大或者縮小的。但是如果你在第一行前面插入或者在最後一行後面追加內容，名稱對應範圍不會自動擴展。請知悉。
• 上一條這種彈性伸縮如果不是你想要的(比如你希望引用區域永遠是固定的B3到B13區域)，這時候你應該使用INDIRECT函數。
• 順便給個TIPS，關於名稱框的部分，如果你有大量的選擇需求，比如選中一千行內容，有一個途徑是在名稱框中直接輸入類似「A1:A1000」後回車即可完成選中，效率比較高。

2. 使用「定義名稱」新建我們也可以使用「定義名稱」入口來直接新建，該功能入口在「名稱管理器」按鈕右邊，注意該入口是下拉菜單，既可以新建名稱，也可以應用名稱(就是把定義好的名稱用在其他地方)，新建功能適合新建不依賴於單元格的常量數據或者常量數組，比如我們新建一個名稱為「單價」，值為12.99的名稱。

我們應用公式列印值，如下圖，可以看到輸出是沒有問題的:

至於應用名稱，和快捷鍵 F3是一樣的，可以在輸入公式過程中通過該功能選擇對應名稱，當然手工輸入也是沒有問題的。

3. 根據所選內容批量創建

這個有點兒意思，我們先看下圖:

我們現在需要一次性建立三個名稱，分別對應第一行的列標題，比如我們的名稱「客戶名稱」對應的單元格區域是從「滄州市運河區祥發便利店」到「徐水縣立安家電商店」區域，另兩個名稱類似。

怎麼做呢，先選中整個表格單元格區域包括標題欄區域，然後點擊[公式-根據所選內容創建]，位置就在名稱管理器旁邊。

可以看到結果:

已經一次性創建成功了。

這裡提醒一個有意思的細節，如果在A工作表上創建了若干名稱，那麼在不同工作簿上複製該表時，涉及該工作表A的所有名稱都會被帶過去(不管範圍是工作表還是工作簿級別的，不管是常量還是引用)；如果在同一個工作簿中複製表A，則除了常量名稱以外，工作表級別和工作簿級別的名稱依然會被複制。

上述現象可以通過名稱管理器進行驗證。同樣的，如果要對名稱進行修改刪除查看等操作，都可以通過名稱管理器完成。

3.案例:二級聯動菜單

二級聯動菜單是一個特別經典的例子，把名稱和INDIRECT函數都串起來了，在將案例之前，我們作為預備知識，先簡單說說INDIRECT函數。

INDIRECT函數是一個特別有趣的函數，我打賭你會愛上它。他的功能很簡單，甩給他一個地址字元串(如「A1」)，它返回該地址對應的內容或者內容數組，如果這個這個地址上存儲的內容恰好是名稱管理器已有的名稱，則返回該名稱對應的內容。有點抽象，我們舉個例子。

看下圖，我們先在J1單元格輸入一串字元就叫做「我是普通內容字元串」，然後我們在J2單元格輸入公式 =INDIRECT("J1")，注意參數有英文引號。這個時候J2的內容對應的是參數「J1」這個地址上對應的內容，也就是說我們給的是一個地址字元串「J1」，返回的是該字元串對應地址的對應內容。

現在我們在K1單元格輸入內容「我是被名稱管理器定義的內容」，然後通過創建名稱的第一種方法，直接在左上角名稱編輯框給K2單元格定義一個名字，叫做「我是普通內容字元串」。對的，我是故意重複的，注意，這個時候，字元串「我是普通內容字元串」有兩重身份，一方面它是J1單元格的內容，一方面它還是名稱管理器的某個名稱，對應的內容指向K1。

我們在K2單元格輸入公式 =INDIRECT(J1) ，注意參數沒有引號，這個時候結果是什麼？

明白了嗎？INDIRECT接受兩種輸入，一種是地址字元串(帶引號)，此時返回地址對應內容；一種是地址(不帶引號)，此時返回該地址對應內容作為名稱的引用內容(有點繞，結合例子想想)。有好奇寶寶問了，如果我沒有定義名稱，但又使用不帶引號的INDIRECT函數，會怎麼樣？答案是會出現 #REF! 錯誤，見下圖:

好了，有了預備知識，我們看案例數據。

如上圖，右邊是數據源，意思是水果列包含下面四行水果，後面類似，我們想實現的效果在左側表格，我們希望選定一級菜單後，二級菜單下拉後選擇的內容根據一級菜單內容自動改變。

比如如果一級菜單選擇了「蔬菜」，那麼同一行二級菜單下拉以後只能看到「白菜、菠菜、捲心菜、蘿蔔」這四個選項。就是所謂的聯動二級菜單。我們這麼實現:

1. 根據上文提到的方法，我們通過右表批量創建三個名稱分別為「水果、蔬菜、零食」，對應區域分別是對應的下面單元格內容。
2. 選中左側表一級菜單下面的三個空白單元格，點擊[數據-數據驗證]按鈕，下拉選擇「數據驗證」。然後按照下圖設置。

1. 選擇左側二級菜單空白單元格部分，同樣點擊數據驗證，進行如下設置(公式部分為=INDIRECT(B3) )，公式的B3這個地址怎麼確定？看下圖，它指的是選中二級空白單元格，反白的那個單元格對應的同行一級菜單單元格地址。

這樣，我們就實現了聯動菜單，你做到了嗎？

擴展思維

僅僅就需求實現來講，除了 INDIRECT 函數以外，我們還有更具靈活性的方法，感謝答疑社群成員 @MiSS_Sha提供的思路(已獲得授權)，具體實現是這樣的。上述步驟一直到第三步，依然是一樣的，唯一的不同是，原來輸入的公式=INDIRECT(B3) 改為如下公式:

=OFFSET($E$2,1,MATCH(B10,$E$2:$G$2,0)-1,COUNTA(OFFSET($E:$E,,MATCH(B10,$E$2:$G$2,0)-1,,))-1,1)

這種方式帶來的好處:

• 無需手工定義名稱，實際上不再使用名稱。
• 自適應數據源變化，公式可以動態載入數據源對應區域。

剛學公式的同學估計看上面那個故事有些吃力，沒關係，等全部公式學習完畢再回頭看應該就懂了。

全文完。

關注公眾號「怒馬說」，第一時間閱讀EXCEL乾貨。