大家好，我是愛聊Excel 小胖子廖晨，今天我們就要說說關於生成動態引用範圍的相關的函式，動態引用範圍其實在我們日常工作還是蠻常見，比如：級聯下拉列表，智慧工資條，隨機點名器等都有過它的身影，那麼問題來了，生成動態引用所需要的掌握的函式有哪些？常用的函式有INDIRECT，ADDRESS，OFFSET，我們今天先來了解一個簡單上手快的函式：
INDIRECT函式
功能
：將文字字串輸出成具體單元格或單元格區域的引用
語法結構
：INDIRECT（字串，引用樣式編碼）
字串
：使用一對雙引號
”包裹
的字元組合，純數字可省略包裹符號；
引用樣式編碼
：2組值，TURE/1或FALSE/0，
TURE/1
：預設值，可以省略，為A1樣式，系統預設的引用樣式，
FALSE/0
：R1C1引用樣式，在A1引用樣式下也可以直接使用R1C1引用樣式規則的字串來輸出成單元格或區域引用的；
注
：字串必須符合對應引用樣式規則，否則返回#REF！；
通常我們在呼叫一個單元格A2的資料，都會在目標單元格直接輸入=A2，如果INDIRECT函式實現的話，就會有更多玩法，先從最簡單的方法開始，比如=INDIRECT（“A2”）或=INDIRECT（“A”&2）。
什麼！明明很簡單，不就是第一個公式的事嗎？你卻要搞的這麼複雜？其實你仔細回想一下，我們的學習過程難道不就是一個簡單到複雜，再從複雜到簡單的過程嗎？第一種是簡單，它簡單的就是例子而已，你學會它並不能給你在日後的工作提供任何幫助，而第二個就不同了，它卻是我們日常最常用的原型之一，而且還有個學名叫拼接法。
拼接法
：
拼接的內容並沒有侷限，它可以是函式，字串，純數字，那麼問題來了，它可以用來做什麼呢？我們透過一個面試題來近距離的接觸一下拼接法吧。
面試題1：現A2:A8上分別為孫策，張飛，李忠，單田芳，梅蘭芳，張紹藍，琳娜，將資料轉置為從單元格C2開始的行儲存資料且增加黑色邊框，請寫出可行的操作方法？（10分）
看到這道題，是不是很多人就覺得這題太簡單了，複製貼上就能搞定，再設定單元格邊框就行了，於是就寫了如下操作步驟：
選中A2：A8，然後ctrl+c或滑鼠右鍵，彈出選單選複製命令，
選擇C2，ctrl+v，在貼上選項中，點選轉置按鈕或直接滑鼠右鍵，彈出的選單命令中選擇【貼上選項】中選【轉置】命令
選中C2：I2，點選
[開始]
選單下邊框下拉按鈕，選擇
[所有框線(A)]
。
indirect面試題操作步驟示意圖（圖1）
如果你真的就是這麼寫的，不出意外，你也就能得2-3分，哪麼怎麼做才能得更高的分呢？
在名稱欄輸入：C2：Z2回車或滑鼠託選C2：Z2單元格，接著直接輸入=INDIRECT（“A”&COLUMN（B2）），然後CTRL+enter，完成公式填充。
點選【開始】選單下的條件格式，選擇【新建規則】，彈窗內選擇“使用公式確定要設定格式的單元格”，然後在公式輸入=C2<>0，點選格式，設定為黑色邊框，點選兩次確定；
重複第2步驟，公式中輸入=C2=0，點選格式，選【數字】選項卡，點選自定義，在型別中輸入；；；後點擊兩次確定；
indirect面試題解法操作步驟示意圖（2
其實面試中，越簡單的題，越能暴露你的職業素養，表面上看兩種做法都能解決問題，但卻存在著質的區別，第一種做法對工作沒有質減少，只是存在量的變化，只是簡單的機械的使用操作技巧，一但資料有修改，增加或刪除，都需要重複操作，而第二種的做法，對實際工作有著質的減少，即使出現資料修改，增加刪除的操作，不需重複操作，就能自動完成資料的更新和增減，但它也有缺陷就是不能完全避免拓充公式，只能在一定的範圍內減少修改公式的操作，因為過量的填充公式會造成檔案的體積增大，開啟檔案的速度變慢，需要我們做的就是找到填充公式量的平衡點，需要注意的有2點：
平時需要注意總結和彙總資料的變化率是多少，通常我在製作時，填充公式的量一般為整體是的1。2-1。5倍，資料量越大的時候，就越接近1。2；
如果修改次數是個位數，還需要結合自己的技術儲備和工作中留給你完成任務的時間，來選擇操作方法，並非絕對的那種方法就是最好的；
如果時間充足，知識即便欠缺，可以在網路中補足，也要儘量嘗試用第2種方法，因為它確實在給以後的工作做減法，減法做的多了，想加班都難啊！
介紹完單個單元格的引用，下面我們看看如何生成一個引用區域，在說用法之前，我們先溫習與引用區域相關的3個引用運算子號：
逗號，冒號，空格；
逗號
：聯合運算子，可以將多個單元格引用或引用區域合併成一個；比如：A2，C3，D5
冒號
：範圍運算子，生成兩個單元格之間所有單元格的引用區域；比如：A2：C3
空格(單個)
：交叉運算子，生成兩個引用區域共同包含的引用區域；比如：A2：D5 C3：F9
具體詳情可以翻看之前的文章：
小白講Excel：表格引用樣式的哪些秘密？你都知道嗎？
哪麼問題來了，我們可以像這樣：=INDIRECT（“A2，C3，D5”），=INDIRECT（“A2：D5 C3：F9”）生成引用區域嗎？要是能這樣用是不是就太方便了！
不過想象總是很美好，現實卻不盡人意啊，因為INDIRECT函式的文字字串並不能識別
逗號
和
空格
為引用運算子，所以它直接生成引用區域是一個單元格引用或連續的引用區域，不過你也不用擔心，我們依然有技巧可以完美規避它的這個缺陷，來個面試題剖析一下。
面試題2：一工作表： B2:D2分別為銷售部，營銷部，設計部；B3:B12,C3:C9,D3:D10為各部門的員工，在G2:H2分別為部門，員工；要求製作級聯二級選單，在G3勾選對應部門，H3勾選對應的員工。（10分）
難點
：如何將每個部門對應上所有該部門的員工列表上，即員工名單所在的引用區域，而一個名稱對應引用範圍用什麼呢？沒有錯，就是自定義名稱，解決這個點，剩下就是操作技巧上的事，有的多兩步，少兩步對得分影響不大，也就0。2-0。5分的事。
操作步驟：
選擇G3，點選［資料］選單下的驗證，在資料驗證視窗，允許選序列，來源，選擇部門所在的區域或者直接錄入=B2：D2，確定；
選中每列有資料的單元格：B2：B12，然後按住CTRL鍵，接著選中C2：C9，D2：D10，，點選［公式］選單下的［根據內容建立定義的名稱］，彈窗中選擇首行，確定；
單獨選中B2：B12；然後CTRL+T，勾選包含標題選項，確定，依法對C2：C9，D2：D10也採用同樣的樣式；
CTRL+F3，點選［新建（N）］按鈕，彈窗中輸入名稱：menu，在
引用位置
輸入公式=INDIRECT（G3），點選確定；
選中H3，重複第1步操作，來源錄入=menu，確定；
indirect函式面試題操作示意圖3
可能有人要問，為什麼要做第3步，是不是可以省略呢？當然不行，第3步是用智慧表格的功能來實現部門人員增加時，下拉列表也會跟著自動增加，這也是你有程式設計思想的體現，可是加分項啊，不過這樣也只是能得及格分6-7分，因為透過上面的步驟我們得到並非嚴格意義的級聯選單，存在以下缺點：
1.
智慧表格能解決下拉列表自動增加，卻不能解決刪除後列表自動減少，會留下空白選項
2.
下拉列表並不是嚴格意義上的級聯下拉選單，因為2級選單選擇過選項後，再修改1級選單，2級選單依然會保留上次操作的值，並不能實時更新。
第1個缺點可以用透過EVALUATE和OFFSET函式解決，可得8分，至於不論怎麼操作都可以實現級聯變化，就需要透過函式組合的方式建立的下拉選單（滿分），我會在後面文章中具體揭曉級聯下拉選單的各類製作方法以及INDIRECT存在的缺陷，透過的上面例子，我們發現可以是用名稱字串作為INDIRECT函式的引數，就能使用名稱定義的引用範圍，就能彌補不能識別逗號和空格引用符號的不足了。
拼接法最要是的如何生成動態引用字串，生成同列如“A1：A13”，”D12：D36”等類似的引用字串比較容易，但在動態生成”A1：H1”這樣的字串時就會有些吃力，哪有什麼更好的方法來生成這樣的字串嗎？有人說用R1C1引用樣式，沒錯確實是解決方法之一，但並不是好的解決方法，由於文章篇幅的原因，所有的解決方法和INDIRECT函式其他用法留在下文分解，希望你能從文中有所收穫，也歡迎你留言指正文中不當之處，我會看到後第一時間回覆，喜歡我就關注吧，我是一個愛聊Excel的小胖子廖晨。葉草花木深，學習要認真。何以術業精？有事找廖晨。