大家好，我是愛聊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)]。
如果你真的就是這麼寫的，不出意外，你也就能得2-3分，哪麼怎麼做才能得更高的分呢？
在名稱欄輸入：C2:Z2回車或滑鼠託選C2:Z2單元格,接著直接輸入=INDIRECT("A"&COLUMN(B2))，然後CTRL+enter,完成公式填充。點擊【開始】菜單下的條件格式，選擇【新建規則】,彈窗內選擇「使用公式確定要設置格式的單元格」,然後在公式輸入=C2<>0,點擊格式，設置為黑色邊框，點擊兩次確定；重複第2步驟，公式中輸入=C2=0,點擊格式，選【數字】選項卡，點擊自定義，在類型中輸入;;;後點擊兩次確定；
其實面試中，越簡單的題，越能暴露你的職業素養，表面上看兩種做法都能解決問題，但卻存在著質的區別，第一種做法對工作沒有質減少，只是存在量的變化，只是簡單的機械的使用操作技巧，一但數據有修改，增加或刪除，都需要重複操作，而第二種的做法，對實際工作有著質的減少，即使出現數據修改，增加刪除的操作，不需重複操作，就能自動完成數據的更新和增減，但它也有缺陷就是不能完全避免拓充公式，只能在一定的範圍內減少修改公式的操作，因為過量的填充公式會造成文件的體積增大，打開文件的速度變慢，需要我們做的就是找到填充公式量的平衡點，需要注意的有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,確定;
可能有人要問，為什麼要做第3步，是不是可以省略呢？當然不行，第3步是用智能表格的功能來實現部門人員增加時，下拉列表也會跟著自動增加，這也是你有編程思想的體現，可是加分項啊，不過這樣也只是能得及格分6-7分，因為通過上面的步驟我們得到並非嚴格意義的級聯菜單，存在以下缺點：
1.智能表格能解決下拉列表自動增加，卻不能解決刪除後列表自動減少，會留下空白選項
2.下拉列表並不是嚴格意義上的級聯下拉菜單，因為2級菜單選擇過選項後，再修改1級菜單，2級菜單依然會保留上次操作的值，並不能實時更新。
第1個缺點可以用通過EVALUATE和OFFSET函數解決，可得8分，至於不論怎麼操作都可以實現級聯變化，就需要通過函數組合的方式創建的下拉菜單(滿分)，我會在後面文章中具體揭曉級聯下拉菜單的各類製作方法以及INDIRECT存在的缺陷，通過的上面例子，我們發現可以是用名稱字符串作為INDIRECT函數的參數，就能使用名稱定義的引用範圍，就能彌補不能識別逗號和空格引用符號的不足了。
拼接法最要是的如何生成動態引用字符串，生成同列如「A1:A13」,」D12:D36」等類似的引用字符串比較容易，但在動態生成」A1:H1」這樣的字符串時就會有些吃力，哪有什麼更好的方法來生成這樣的字符串嗎？有人說用R1C1引用樣式，沒錯確實是解決方法之一，但並不是好的解決方法，由於文章篇幅的原因，所有的解決方法和INDIRECT函數其他用法留在下文分解，希望你能從文中有所收穫，也歡迎你留言指正文中不當之處，我會看到後第一時間回復，喜歡我就關注吧，我是一個愛聊Excel的小胖子廖晨。葉草花木深，學習要認真。何以術業精？有事找廖晨。