Excel达人必学：用INDIRECT搞定复杂报表！

Excel达人必学：用INDIRECT搞定复杂报表！

引用

CSDN

等

9

来源

1.

https://blog.csdn.net/FANSHEN2022/article/details/127182811

2.

https://jingyan.baidu.com/article/1612d50079d1e5a30f1eeed0.html

3.

https://blog.csdn.net/ldq_sd/article/details/118958187

4.

https://blog.csdn.net/fucong920618717/article/details/116697363

5.

https://www.ithome.com/0/699/013.htm

6.

http://www.360doc.com/content/24/0911/08/78825344_1133695951.shtml

7.

https://learn.microsoft.com/zh-cn/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions

8.

http://www.itblw.com/graphic/66df0d1109e9de3d0e387f3576615079.html

9.

https://docs.pingcode.com/baike/4139142

在Excel中，INDIRECT函数是一个强大的工具，能够将文本字符串转换为实际的单元格引用。这个功能看似简单，但在处理复杂数据场景时却能发挥意想不到的作用。本文将详细介绍INDIRECT函数在实际工作中的高级应用，帮助你提升数据处理效率。

跨表自动求和

在处理多个结构相同的工作表时，INDIRECT函数可以与SHEET函数结合使用，实现跨表数据的自动汇总。例如，如果你需要汇总每个月的销售额，可以使用以下公式：

=IF(SHEET()=1,B2,INDIRECT(SHEET()-1&"月!C2") +B2)

• SHEET()函数用于获取当前工作表的编号。
• IF函数判断是否为第一个工作表，如果是则直接引用B2单元格的值。
• INDIRECT函数动态引用前一个工作表的C2单元格值，并与当前工作表的B2值相加。

提取区域内的非空单元格

在需要从数据区域中提取非空单元格内容时，INDIRECT函数可以与TEXT、SMALL和IF函数组合使用。以下公式可以实现这一目标：

=INDIRECT(TEXT(SMALL(IF($A$1:$E$40<>"",ROW($1:$40)/1% + COLUMN(A:E)),ROW(A1)),"r0c00"))

这个公式的解析如下：

• IF函数检查A1到E40范围内的每个单元格是否非空，返回TRUE或FALSE。
• ROW和COLUMN函数生成一个二维数组，表示每个单元格的行号和列号。
• SMALL函数从数组中提取第N小的值，依次返回101, 102, 103等。
• TEXT函数将提取的数值转换为R1C1格式的文本地址。
• INDIRECT函数将文本地址转换为实际的单元格引用，并返回该单元格的内容。

提取多列数据中的非重复值

当你需要从多个列中提取非重复数据时，INDIRECT函数可以与COUNTIF和TEXT函数结合使用。以下公式可以实现A到C列的去重：

=INDIRECT(TEXT(MIN(IF(COUNTIF($E$1:E1,$A$2:$C$7)=0,ROW($2:$7)*100+COLUMN($A:$C))),"R0C00"),0)&""

这个公式的解析如下：

• COUNTIF函数检查区域内是否存在与E列中已出现的值不重复的单元格。
• ROW和COLUMN函数生成一个唯一的数值矩阵，确保每个单元格都有唯一的标识符。
• IF函数对上述TRUE或FALSE的数组进行条件判断，返回符合条件的唯一标识符。
• MIN函数从符合条件的标识符中提取最小值。
• TEXT函数将标识符转换为R1C1格式的文本地址。
• INDIRECT函数将文本地址转换为实际的单元格引用，并返回单元格内容。

快速交换两列数据

在需要快速交换两列数据时，INDIRECT函数可以与TEXT、MMULT和CELL函数组合使用。以下公式用于交换A列和B列的数据：

=CELL("contents", (INDIRECT(TEXT(MMULT(ROW(1:5)/1% + COLUMN(A:B), 1 - MUNIT(2)), "r0c00"))))

这个公式的解析如下：

• ROW和COLUMN函数生成一个5×2的二维数组，表示行列组合。
• MMULT函数进行矩阵乘法，将列位置交换。
• TEXT函数将交换后的数组转换为R1C1格式的文本地址。
• INDIRECT函数将文本地址转换为实际单元格引用。
• CELL函数返回所引用单元格的内容。

文本数据动态重组

借助INDIRECT函数结合TEXTJOIN和TEXT等函数，你可以动态生成和重组文本数据。例如，以下公式可以生成一个连续数列并进行格式化：

=TEXTJOIN(" ",0,TEXT(LEFT(B2,3) +TRANSPOSE(ROW(INDIRECT("1:"&A2)) -1), "000"))

这个公式的解析如下：

• LEFT函数提取B2单元格中的前三个字符。
• ROW和INDIRECT函数生成一个连续的行号序列。
• TRANSPOSE函数将行号序列转换为列向量。
• TEXT函数对数值进行格式化。
• TEXTJOIN函数将结果连接成一个字符串。

动态跨表引用

在处理多个工作表的数据汇总时，INDIRECT函数可以实现动态的跨表引用。例如，如果你有多个名为“十里河”、“饮马井”、“大洋路”和“方庄”的工作表，需要汇总它们的H列合计数，可以使用以下公式：

=SUM(INDIRECT("'"&B3&"'!H:H"))

这个公式中，INDIRECT函数的参数由三部分组成：

• 单引号'用于标识工作表名称。
• &符号用于连接字符串和单元格引用。
• !H:H表示引用工作表的H列。

性能影响和注意事项

虽然INDIRECT函数功能强大，但在使用时也需要注意以下几点：

1. 性能影响：INDIRECT是一个易失性函数，每次工作簿重新计算时都会重新计算，大量使用会影响性能。
2. 引用有效性：文本字符串必须符合有效的单元格地址格式，否则会返回#REF!错误。
3. 跨工作簿引用：引用其他工作簿时，目标工作簿必须处于打开状态，否则会返回错误。
4. 结构化引用：INDIRECT不支持Excel表的结构化引用（如Table1[列1]）。

通过灵活运用INDIRECT函数，结合其他Excel函数，你可以轻松解决许多复杂的数据处理问题。但也要注意合理使用，避免过度依赖导致性能下降。希望这些技巧能帮助你提升工作效率，让你在Excel操作中游刃有余。