Excel Indirect函數(shù)用法的7個(gè)實(shí)例，含跨表跨文檔引用、與Match、Address與Row組合實(shí)現(xiàn)多表和批量提取數(shù)據(jù)

在 Excel 中，Indirect函數(shù)用于用于返回由文本字符串指定的單元格引用。它的引用類(lèi)型有兩種，一種為如 A1 樣式的引用，另一種為如R1C1 樣式的引用，它們由邏輯值真和假設(shè)置。它引用的單元格可以是同表的，也可以是同文檔或跨文檔的，后兩種情況為跨表引用。

Indirect函數(shù)常與 Address、Row、Column、Match 函數(shù)組合使用，例如 Indirect + Row + Address + Column 函數(shù)組合實(shí)現(xiàn)將多個(gè)表格中指定行數(shù)據(jù)提取到一個(gè)表格，Indirect + Match + Column 函數(shù)組合實(shí)現(xiàn)按條件批量提取數(shù)據(jù)。

一、Excel Indirect函數(shù)的語(yǔ)法

1、表達(dá)式：INDIRECT(Ref_Text, [A1])

中文表達(dá)式：INDIRECT(引用文本, [引用類(lèi)型])

2、說(shuō)明：

A、Ref_Text 既可以是內(nèi)部引用（在本工作簿中引用，即在同一 Sheet 中引用或在一個(gè) Sheet 引用另一個(gè) Sheet）又可以是外部引用（對(duì)另一工作簿的引用，即在一個(gè)文檔引用另一個(gè)文檔）；值得注意的是，外部引用時(shí)，被引用的工作簿必須打開(kāi)，否則Indirect函數(shù)將返回引用錯(cuò)誤 #Ref!。另外，Excel Web App 不支持外部引用。

B、如果 Ref_Text 引用的單元格區(qū)域超出 Excel 允許的最大行數(shù) 1048576 或最大列數(shù) 16384（XFD），Indirect函數(shù)也返回引用錯(cuò)誤#REF!；但 Excel 早期版本會(huì)忽略這一限制并且返回一個(gè)值。

C、A1 有兩個(gè)可選值，分別為 True（或省略）和 False；若為 True，則解釋為 A1 樣式的引用；若為 False，則解釋為 R1C1 樣式的引用，R 表示“行”，C 表示“列”，R1C1 表示對(duì)第一行與第一列對(duì)應(yīng)單元格的引用，即 A1。無(wú)論哪種引用方式，如果引用單元格不存在，Indirect函數(shù)都將返回錯(cuò)誤。

二、Excel Indirect函數(shù)的使用方法及實(shí)例

（一）引用類(lèi)型 A1 為 True（或省略）的實(shí)例

1、選中 A9 單元格，輸入公式 =Indirect(B5)，按回車(chē)，返回引用錯(cuò)誤 #REF!；雙擊 B9，把公式 =INDIRECT("B"&5) 復(fù)制到 B9，按回車(chē)，返回 B5 中的姓名“黃子辛”；雙擊 B9，在 5 后輸入“,true”，按回車(chē)，也返回“黃子辛”；操作過(guò)程步驟，如圖1所示：

圖1

2、公式說(shuō)明：

A、公式 =Indirect(B5) 之所以會(huì)返回引用錯(cuò)誤，是因?yàn)镮ndirect函數(shù)的第一個(gè)參數(shù)必須為文本，而 B5 是對(duì)單元格的引用。

B、公式 =INDIRECT("B"&5) 中，"B"&5 為文本，意思是用 & 把字符 B 與 5 連接起來(lái)，即 "B5"；由于 "B5" 為文本而不是對(duì) B5 單元格的引用，因此Indirect函數(shù)能返回正確的值。

C、公式 =INDIRECT("B"&5) 與 =INDIRECT("B"&5,true) 返回一樣的值，說(shuō)明Indirect函數(shù)省略第二個(gè)參數(shù)與把它設(shè)置為 True 一樣；另外，True 也可以用 1 代替，即公式  =INDIRECT("B"&5,true) 可改為 =INDIRECT("B"&5,1)。

（二）引用類(lèi)型 A1 為 False 的實(shí)例

1、雙擊 B9 單元格，把公式 =INDIRECT("R"&5&"C"&2,FALSE) 復(fù)制到 B9，按回車(chē)，返回 B5 中的姓名“黃子辛”；雙擊 B9，把 FALSE 改為 0，按回車(chē)，也返回一樣的結(jié)果；操作過(guò)程步驟，如圖2所示：

圖2

2、公式說(shuō)明：

公式中 "R"&5 表示第五行，"C"&2 表示第二列，"R"&5&"C"&2 連接起來(lái)為 "R5C2"，表示第五行與第二列對(duì)應(yīng)的單元格；FALSE 表示以R1C1 樣式引用，0 可以替代 FALSE；公式的意思是：返回第 5 行與第 2 列對(duì)應(yīng)的單元格引用，即返回 B5 中的姓名。

（三）內(nèi)部引用（同表與跨表引用）的實(shí)例

1、同表引用；當(dāng)前工作表為“水果銷(xiāo)量表1”，雙擊 A10 單元格，把公式 =INDIRECT("D"&4) 復(fù)制到 A10，按回車(chē)，返回 D4 中的數(shù)值 2050?？绫硪茫偃缭凇八N(xiāo)量表1”引用“水果銷(xiāo)量表2”；雙擊 B10，把公式 =INDIRECT("水果銷(xiāo)量表2!D"&4) 復(fù)制到 B10，按回車(chē)，返回“水果銷(xiāo)量表2”中的 D4 中的數(shù)值 1588；操作過(guò)程步驟，如圖3所示：

圖3

2、公式說(shuō)明：

同表引用與跨表引用的區(qū)別為：跨表引用只需在要引用的單元格前加工作表名稱(chēng)和半角感嘆號(hào)（即 !），如演示中的 "水果銷(xiāo)量表2" 為工作表名稱(chēng)，它的后面為感嘆號(hào)（!），D4 為引用單元格。

（四）外部引用（跨文檔引用）的實(shí)例

1、假如要在文檔“服裝銷(xiāo)量1.xlsx”中的“1月”工作表引用“服裝銷(xiāo)量2.xlsx”中的同一工作表中的“黑色T恤”銷(xiāo)量。雙擊 E6 單元格，把公式 =INDIRECT("[服裝銷(xiāo)量2.xlsx]1月!D6") 復(fù)制到 E6，按回車(chē)，返回 750；操作過(guò)程步驟，如圖4所示：

圖4

2、公式說(shuō)明：

公式 =INDIRECT("[服裝銷(xiāo)量2.xlsx]1月!D6") 中，[服裝銷(xiāo)量2.xlsx] 為文檔名稱(chēng)，注意要加擴(kuò)展名“.xlsx”；“1月”為工作表名，D6 為引用單元格，工作表名與引用單元格之間同樣要加感嘆號(hào)（!）。

（五）引用單元格為 Excel 允許的最大行數(shù) 1048576 或列數(shù) 16384（XFD）的實(shí)例

1、雙擊 C2 單元格，把公式 =INDIRECT("A"&1048576) 復(fù)制到 C2，按回車(chē)，返回 Excel 允許的最大行數(shù) 1048576；雙擊 C2，把 后面的6 改為 7，按回車(chē)，返回引用錯(cuò)誤 #REF!。雙擊 C3，把公式 =INDIRECT("XFD"&1) 復(fù)制到 C3，按回車(chē)，返回 Excel 允許的最大列數(shù) 16384；雙擊 C2，把 XFD 改為 XFE，按回車(chē)，也返回引用錯(cuò)誤 #REF!；按 Ctrl + 向右方向鍵，移到最后一列，XFD 列后已無(wú)列。操作過(guò)程步驟，如圖5所示：

圖5

2、公式說(shuō)明：

從演示可知，當(dāng)把最后一行 1048576 改為 1048577，公式返回引用錯(cuò)誤，說(shuō)明 1048576 已是最后一行；同理把最后一列 XFD 改為 XFE，公式也返回引用錯(cuò)誤，說(shuō)明 XFD 也是最后一列。

三、Excel Indirect函數(shù)的應(yīng)用實(shí)例

（一）Indirect + Row + Address + Column 函數(shù)組合實(shí)現(xiàn)將多個(gè)表格中指定行的數(shù)據(jù)提取到一個(gè)表格

1、假如要把服裝銷(xiāo)量表“1月、2月和3月”中的第四行提取到總表。當(dāng)前工作表為“總表”，雙擊 A2 單元格，把公式 =INDIRECT(ROW(A1)&"月!"&ADDRESS(ROW($A$4),COLUMN(B$1))) 復(fù)制到 A2，按回車(chē)，返回工作表“1月”的第四行第二列的服裝名稱(chēng)“白色修身T恤”；選中A2，把鼠標(biāo)移到 A2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)楹谏犹?hào)后，按住左鍵并往右拖，一直拖到 C2（提取數(shù)據(jù)表格的最后一列），則提取了表格“1月”的第四行數(shù)據(jù)；再往下拖（要提取數(shù)據(jù)的表格還剩幾個(gè)拖幾行），則又提取工作表“2月和3月”的第四行數(shù)據(jù)；切換到被提取數(shù)據(jù)的工作表，可以看到提取的數(shù)據(jù)正確；操作過(guò)程步驟，如圖6所示：

圖6

2、公式 =INDIRECT(ROW(A1)&"月!"&ADDRESS(ROW($A$4),COLUMN(B$1))) 說(shuō)明：

A、ROW(A1) 用于返回 A1 的行號(hào) 1，然后與“月”字用 & 連接合成工作表名稱(chēng)“1月”；當(dāng)往下拖時(shí)，A1 會(huì)變?yōu)?A2，A3 等，Row(A2) 返回行號(hào) 2，再與“月”字合成工作表“2月”，其它的以此類(lèi)推。

B、$A$4 表示絕對(duì)引用 A4，往下拖時(shí)，A4 不會(huì)變 A5、A6 等；往右拖時(shí)，A4 不會(huì)變 B4、C4 等；這樣確保 ROW($A$4) 始終返回第 4 行。

C、COLUMN(B$1) 用于返回 B1 的列號(hào) 2，B$1 為相對(duì)引用列絕對(duì)引用行，以確保往右拖時(shí)，B1 變?yōu)?C1、D1 等，從而達(dá)到取不同列的數(shù)據(jù)的目的。

D、則 ADDRESS(ROW($A$4),COLUMN(B$1)) 變?yōu)?ADDRESS(4,2)，接著以文本方式且以絕對(duì)引用返回第四行與第二列對(duì)應(yīng)的單元格，即 "$B$4"，因?yàn)?Address 函數(shù)省略了第三個(gè)參數(shù)“引用類(lèi)型”，默認(rèn)以絕對(duì)引用返回。

E、則公式變?yōu)?=INDIRECT(1&"月!"&"$B$4")，進(jìn)一步計(jì)算，把字符串連接起來(lái)，則公式變?yōu)?=INDIRECT("1月!$B$4")，最后返回對(duì)工作表“1月”中 B4 單元的引用，即返回 B4 中的服裝名稱(chēng)。

（二）Indirect + Match + Column 函數(shù)組合實(shí)現(xiàn)按條件批量提取數(shù)據(jù)

1、假如要從一個(gè)表格中提取任意指定幾個(gè)月份的銷(xiāo)量，每個(gè)月的銷(xiāo)量占一行。雙擊 B13 單元格，把公式 =INDIRECT("r"&MATCH($A13,$A$1:$A$10,)&"c"&COLUMN(),0) 復(fù)制到 B13，按回車(chē)，返回“T恤”“1月”的銷(xiāo)量 567；選中 B13，用往右拖的方法返回“1月”剩余的數(shù)據(jù)，再用往下拖的方法提取“4月、5月和8月”的銷(xiāo)量；操作過(guò)程步驟，如圖7所示：

圖7

2、公式 =INDIRECT("r"&MATCH($A13,$A$1:$A$10,)&"c"&COLUMN(),0) 說(shuō)明：

A、MATCH($A13,$A$1:$A$10,) 用于返回 A13（即“1月”）在 A1:A10 中位置，結(jié)果為 2；$A13 之所以用列絕對(duì)引用行相對(duì)引用，是因?yàn)橥彝蠒r(shí)要求它不變?yōu)?B13、往下拖時(shí)要變?yōu)?A14，這樣實(shí)現(xiàn)取到“A14 中的4月、A15 中的5月”等等；$A$1:$A$10 之所以列行都用絕對(duì)引用，是因?yàn)橥峦蠒r(shí)始終要確保在 A1:A10 中查找，因?yàn)橛媒^對(duì)引用，往下拖，A1 不為會(huì) A2、A10 也不會(huì)變 A11。另外，Match函數(shù)省略了最一個(gè)參數(shù)，默認(rèn)取 0，表示精確匹配。

B、COLUMN() 用于返回公式所在列號(hào)，當(dāng)公式在 B13 時(shí)，它返回 2；當(dāng)公式有 C13 時(shí)，它返回 3，其它的以此類(lèi)推。

C、則公式變?yōu)?=INDIRECT("r"&2&"c"&2,0)，進(jìn)一步計(jì)算把各字符連接起來(lái)，公式變?yōu)?=INDIRECT("r2c2",0)，公式中，r 表示行號(hào)，c 表示列號(hào)，0 表示以 r1c1 樣式引用，最后公式返回第 2 行與第 2 列對(duì)應(yīng)的單元格引用，即返回 B2 中的數(shù)值 567。