INDIRECT函數用法的那些事?終於湊齊了!

大家好，花葉草木深，有事找廖晨，我就是那個愛聊Excel的小胖子，閒言少敘，文接INDIRECT函數的用法你都知道？這是簡單用法了解一下！，上文聊到INDIRECT函數使用中，參數是字符串，在使用拼接法生成引用範圍時，遇到在生成類似「A2:H1」字符串時比較費勁，我們有什麼好的方法解決它嗎？真誠奉上3種方法僅供參考：

第一種方法：開啟INDIRECT函數的R1C1引用樣式模式，在生成字符串之前，我們簡單回憶一下R1C1規則：

1.先行後列，不同與A1的先列後行；2.行號用R+數字，列表用C+數字；比如：$A$2用R1C1樣式的寫法就是R2C1，在字符串生成中，數字是比較好生成的；比如在A2:H2存儲一些數據，從尾刪除或增加一個數據導致引用範圍總數減少，增加，若想引用範圍隨數據的增減而增減，就需要用公式=indirect(「R2C1:R2C」&COUNTA(1:1))來生成,若數據沒有變化時等價公式為=$A$2:$H$2；

不知你有沒有發現？R1C1樣式在生成的絕對引用的範圍比較擅長，但在公式拖拽的時候，引用範圍卻固定，這在很多情況下並不是我們想要的，但要用R1C1引用樣式生成相對引用，又需要花一定時間來多學一種引用規則，我覺的這並不值得，哪我們就回到問題上，在生成動態引用字符串上，我們相對容易的是生成數字，而缺少的是列標跟數字的對應關係，哪我們就來創建對應關係好了，可以用字符串提取法。

R1C1相對引用示意圖（圖1）

第二種：我們定義一個名稱strings，字符串「ABCDEFGHIJKLMNOPQRSTUVWXYZ」,然後用MID函數按列號位置讀取字符的字符，每次讀取一個字母，比如A2:H2的相對引用，=indirect(「A2:」&mid(strings,COUNTA(1:1),1)&」2」),它的缺陷就是範圍最大列標為Z,如果想涵蓋所有的列標，就需要涉及到算法思維了，可能有點深，初學的可以跳過看第三種方法。

列標總數並不是一成不變的，在2007版的Excel中，列標由ZZ增加到XFD(16384)，即最多有3位字母，將ABZ換算成十進位數字為1*262+2*26+26=754=COLUMN(ABZ1)，而我們現在要做的就是將COLUMN函數獲取的列號反編譯成字母；不過我們在變字母前需前求出對應字母對應位置序號:

第3位字母序號：列表<=26,則模擬公式：MOD(列標,26)+(mod(列標,26)=0)*26

第2位字母序號，列標>26且<=702,則,則模擬公式:MOD(INT((列標-(MOD(列標,26)+(mod(列標,26)=0)*26))/26),26)+(MOD(INT((列標-(MOD(列標,26)+(mod(列標,26)=0)*26))/26),26)=0)*26

化簡一下：MOD(INT((列標-MOD(列標,26)/26)-(mod(列標,26)=0),26)+ (MOD(INT((列標-MOD(列標,26)/26)-(mod(列標,26)=0),26)=0)*26

第1位字母序號，列標>26*26+26=702(列標:ZZ)，,則模擬公式=INT((列標-(第2個字母序號)*26-(第1字母序號)/676)

以上式子中求解出來的是字母在字符串中的位置，再代入=mid(strings,字母序號,1)；就能得出對應字母；在拼接時，不建議使用如下方式：

IF(列標>702,3位字母拼接,if(列標>26,2位字母拼接,1個字母))

這個公式邏輯上容易理解，但第1，2字母位置序號的公式使用了2次，導致公式比較冗餘，也增加輸錯的概率，並且遇到錯誤排查比較困難，推薦的使用方法如下：

IF(列標>702,第3個字母,」」)&IF(列標>26,第2位字母,」」)&第1位字母

不論使用哪一種公式，最後都要做容錯處理，以上公式都已經過驗證，可以在案例中直接使用，不過對於初學者來說，在理解上稍微有點難，因為它的難度水平是工程師級別，就在2019年，華為在招聘工程師的算法面試中，有過類似的一道題：26進位算法，其原理相同，只不過在Excel字母列標中，並沒有0的出現，需要將0替換為26而已。

定義名稱的操作步驟示意圖(圖2）

第三種：函數法，因為有個專門生成區域字符串的函數ADDRESS,這裡簡單介紹一下它的用法，共有5個參數，3個為選填內容，

1參行號，2參列，3參引用類型（4種：1（$A$1，絕對引用），2（A$1,行絕對引用）3（$A1 列絕對引用）4（A1 相對引用）） 4參引用樣式 TURE(默認)為A1,FALSE為R1C1, 5參工作表名稱

A1用ADDRESS寫就是=ADDRESS(1,1,4)是不是簡單許多呢，生成動態引用字符串A2:H2 =INDIRECT(「A2:」&ADDRESS(2,COUNTA(1:1),4)；

提示：在某個單元格直接寫這個公式會返回錯誤#VALUE!

是不是有人疑惑了，為什麼用一個函數能解決的方法，非要費哪個勁，自己寫個算法幹什麼？

其實很簡單，因為寫算法人的工資是只會用函數人工資的最少是2-3倍，在學習的時候，儘量往深了學，學習算法，一些函數的原理並嘗試自己通過簡單函數的組合實現一樣的功能，這樣有助於你形成編程思維，如果在工作中，如果沒有特殊要求，有函數誰不用啊。說完了INDRECT函數在一個工作表中的用法，我們接下來就來看看如何玩轉跨工作表訪問。

跨表訪問

跨表訪問有個通用公式就是：『工作表』!單元格引用（可翻閱：小白講Excel:跨工作表引用怎麼用？這是規則了解一下！）

看著公式我們就一起回顧一下跨表訪問的規則：

單引號：當工作表名稱中以數字開頭或包含空格或某些特殊字符時，必須用單引號包裹名稱，否則可以省略提示：

正確做法通常都寫，這樣就能保證所寫的引用公式100%有效)；INDIRECT不能識別引用符號逗號和空格的原因是因為會工作表名稱符號衝突，即便花力氣實現，可用性也不高多工作表引用：工作表引用表達式不光可以是一個工作表引用，還支持為多個工作表，而且是連續工作表：

連續工作表：以冒號為分隔符，比如訪問sheet1,sheet2,sheet3中的b2:b5,表達式為=『sheet1:sheet2』!b2:b5，如果上述3個表可以寫成：』*』!b2:b5跨表訪問主要用在跨工作表匯總數據，而與indirect函數的結合則是解決根據已給的內容來匯總數據，比如匯總某個員工的半年銷售額，我司有銷售員8名，每個工作表以銷售員姓名為名稱，內面內容結構相同，A:D；商品類型，商品名稱，銷售日期，金額，要求製作一個匯總表，A:B為姓名和銷售額。

如果你直接這麼做：A2輸入員工姓名，B2則輸入公式=sum(indirect(「』」&A2&」』!D:D」)，然後就填充了剩餘的公式，然後就交差了，這樣做，會讓上司對你這個人的評價大大減分，因為在細節上處理的不夠好，要做好細節，從下面兩點入手：

因為公司員工有可能出現變動，可能增加也可能減少，公式不能只填充8個，大致填充到12個(1.5倍，翻看上文)，所以通用公式需要做為空處理，判斷控制有兩種方法：ISBLANK或=「「；ISBLANK判斷的空是指單元格真的沒有任何內容，而=」「判斷是單元格最後的結果不能空，這裡使用=」「，公式調整為=if(A2=」」,」」, sum(indirect(「』」&A2&」』!D:D」));沒有對應姓名的表，造成的原因有兩個，一個錄入錯誤，一個真的沒有，所以容錯提示要突出這兩點，最終公式為=IFERROR(IF(A2="","",SUM(INDIRECT("'"&A2&"'!D:D"))),"請確保姓名無誤和工作表存在")。

案例操作步驟示意圖（圖3）

有人說，為什麼需要做兩步，一步容錯就可以啦！還是哪句話，容錯並不是一味的掩蓋錯誤，而容錯的目的有2：

1.讓操作者更容易，更準確的找出問題的原因，2.讓錯誤編碼顯示的更加友好；而要做到準確反映出錯誤原因，就需要分析錯誤，拆分錯誤情況，才能做好容錯，當然如果錯誤過多無法全部提示，則選重點的寫提示就好，至於看起來友好，就是比較低端的需求，做了不能代表水平如何，但不做就是水平不高了。本著容錯第1目的，而單元格為空又是所有單元格默認狀態，就需要單獨處理了。

可能有人還說：不就是個容錯嗎？至於嗎？用得著這麼細扣嗎？

有時間多學其他的函數不好嗎？如果你只想做個表格操作員，或者叫表格工兵，哪這麼做沒有問題，如果你想做工程師級別的，就必須考慮這些問題，你做的表格必須滿足一個條件，即便只會簡單電腦操作的人，也能輕鬆上手，排除日常的錯誤原因，否則你就別幹別的了，他們會一直找你，讓你沒有時間幹別的，/(ㄒoㄒ)/~~。

跨工作簿

跨工作簿引用就是公式：『路徑[工作簿名]工作表』!單元格引用的具體使用，使用時需要注意2點：

工作簿的名稱一定要用中括號[包裹；假如工作簿A引用工作簿B的內容，在B打開時引用不需要完整路徑，如果關閉B,則會自動增加完整路徑；當A關閉後再打開，如果B沒有打開，則彈出安全警告，你選擇【啟用內容】來更新連結，不過有部分函數並不支持訪問關閉狀態的下的工作簿；由於第2點的影響，會給使用它的公式和項目帶來很大的不確定性，當然你也可以總結出哪些函數能用 ，哪些函數不能用，來規避不確定性，不過這已經影響了它的使用範圍，哪就別說跟INDIRECT函數的配合使用了，所以我們下面的內容，只作了解之用；兩者的配合使用十分簡單，只要注意規則中的不可少符號，舉個簡單例子了解一下：

對一個2.XLSX工作簿中所有工作表中A1求和？

公式為=SUM(INDIRECT("'[2.XLSX]*'!A1"))，而且它僅是一個例子，沒有太多應用意義。

今天文章是INDIRECT函數相關內容的結尾篇，這就是我所知道的INDIRECT函數所有的內容，毫無保留的都告訴你了，希望你從中有所收穫，最後給你分享一個我們在學Excel時遊戲叫：函數接龍，規則：先定一個範圍，然後每個人說一個此類函數的一個有效用法為過，說不上來的人需要表演一個節目，如果你有和你一樣愛玩Excel，不妨玩一下，既能玩，還能鞏固知識！好了今天的內容就到這了，喜歡我就關注我吧，花葉草木深，學習要認真！我就我，一個愛玩Excel的胖小夥-廖晨，期待你的留言和私信！