Excel间接功能 - 基本用途和公式示例

此Excel间接教程解释了该函数的语法，基本用途，并提供了许多公式示例，这些公式示例演示了如何在Excel中使用间接。

Microsoft Excel中存在很多功能，有些功能易于理解，另一些需要长长的学习曲线，而前者比后者更频繁地使用。但是，Excel间接是其中之一。此Excel函数不会执行任何计算，也不会评估任何条件或逻辑测试。

那么，Excel中的间接函数是什么？我该使用什么？这是一个非常好的问题，希望您在阅读本教程后的几分钟内将获得一个全面的答案。

• Excel间接函数 - 语法和基本用途
• Excel间接公式示例
  • 从单元格值中创建间接参考
  • 来自单元格值和文本的参考
  • 间接命名范围
  • 创建对另一张表的Excel动态参考
  • 对另一个工作簿进行动态引用
  • 锁定单元格参考
  • 间接使用其他功能（行，地址，vlookup）
  • 间接和Excel数据验证
• 间接公式中的可能错误和问题

Excel间接函数 - 语法和基本用途

顾名思义，Excel间接用于间接参考细胞，范围，其他床单或工作簿。换句话说，间接函数使您可以创建动态单元格或范围参考，而不是对它们进行硬编码。结果，您可以在不更改公式本身的情况下更改公式中的参考。此外，当在工作表中插入某些新行或列或删除任何现有的新行或列时，这些间接引用不会改变。

从一个示例中，这可能更容易理解。但是，要能够编写一个公式，即使是最简单的公式，您也需要知道该功能的论点，对吗？因此，让我们先快速查看Excel间接语法。

间接函数语法

Excel中的间接函数返回文本字符串中的单元格引用。它有两个参数，第一个是必需的，第二个是可选的：

间接（ref_text，[A1]）

ref_text-是单元格引用，或以文本字符串或命名范围的形式对单元格的引用。

A1-是一个逻辑值，它指定ref_text参数中包含哪种类型的参考：

• 如果true或省略，则将ref_text解释为A1式单元格引用。
• 如果为false，则将ref_text视为R1C1参考。

尽管R1C1参考类型可能在某些情况下很有用，但您可能会大部分时间都使用熟悉的A1参考。无论如何，本教程中几乎所有间接公式都将使用A1参考，因此我们将省略第二个参数。

间接功能的基本用途

要了解该函数的见解，让我们编写一个简单的公式，以演示您在Excel中使用间接的方式。

假设，您在单元格A1中有3号，并且在单元格C1中具有文本A1 。现在，将公式=INDIRECT(C1)放入任何其他单元格中，看看会发生什么：

• 间接函数是指单元C1中的值，即A1。
• 该函数被路由到单元A1，其中选择值返回的值，即数字3。

因此，在此示例中，间接函数实际上所做的是将文本字符串转换为单元格引用。

如果您认为这仍然几乎没有实践意义，请忍受我，我将向您展示更多的公式，以揭示Excel间接功能的真正力量。

如何在Excel-公式示例中使用间接

如上面示例所示，您可以使用Excel间接函数作为通常的文本字符串将一个单元格的地址放入另一个单元格中，并通过引用2 ^nd来获取1 ^ST单元格的值。但是，这个微不足道的例子不过是暗示间接功能的提示。

使用实际数据时，间接函数可以将任何文本字符串变成参考，包括您使用其他单元格的值以及其他Excel公式返回的结果构建的非常复杂的字符串。但是，让我们不要将购物车放在马上，然后一次穿过几个Excel间接公式。

从单元格值中创建间接参考

如您所记住的那样，Excel间接功能允许A1和R1C1参考样式。通常，您不能一次在单个表中使用两种样式，只能通过文件>选项>“公式” > R1C1复选框在两个参考类型之间切换。这就是为什么Excel用户很少考虑使用R1C1作为替代参考方法的原因。

在间接公式中，您可以在同一表上使用任何一个参考类型。在我们进一步移动之前，您可能想知道A1和R1C1参考样式之间的区别。

A1样式是Excel中通常的参考类型，它是指列，然后是行号。例如，B2指在B和第2列的交点处的单元格。

R1C1样式是相反的参考类型 - 行之后是列，它确实需要一些时间来习惯：）例如，R4C1指的是在纸中的第4行，第1列中的单元格A4。如果信件之后没有数字，则您指的是同一行或列。

现在，让我们看看间接函数如何处理A1和R1C1参考：

正如您在上面的屏幕截图中看到的那样，三个不同的间接公式返回相同的结果。你已经知道为什么吗？我敢打赌你有:)

• 单元格中的公式： =INDIRECT(C1)

这是最简单的。该公式指的是单元格C1，获取其值-Text String A2 ，将其转换为单元格引用，转到单元格A2并返回其值，即222。

• 单元格中的公式： =INDIRECT(C3,FALSE)

FALSE在第二^个参数中表明，应将转介值（C3）视为R1C1单元格引用，即行号，然后是列号。因此，我们的间接公式将细胞C3（R2C1）中的值解释为第2行和第1列（即单元a2）的连词处的单元格。

从单元格值和文本中创建间接引用

与我们从单元格值中创建引用的方式类似，您可以将文本字符串和一个单元格引用在间接公式中，并与串联操作员（＆）捆绑在一起。

在下面的示例中，公式：=间接（“ B”＆C2）基于以下逻辑链从单元格返回一个值：

间接函数将ref_text参数 - 文本B中的元素与单元格C2中的值为b中的值相连，单元格中的值为数字2，它引用了单元格B2->该公式为单元格B2并返回其值，并返回其值，为数字10。

将间接函数与命名范围

除了从单元格和文本值中进行参考外，您还可以获取Excel间接函数以参考命名范围。

假设您的工作表中有以下命名范围：

• 苹果-B2：B6
• 香蕉-C2：C6
• 柠檬-D2：D6

要创建对上述任何命名范围的Excel动态引用，只需在某个单元格中输入其名称，例如G1，然后从间接公式=INDIRECT(G1)中引用该单元格。

现在，您可以进一步迈出一步，并将此间接公式嵌入其他Excel函数中，以计算给定命名范围内的值的总和和平均值，或在RAGE内找到最大 /最小值：

• =SUM(INDIRECT(G1))
• =AVERAGE(INDIRECT(G1))
• =MAX(INDIRECT(G1))
• =MIN(INDIRECT(G1))

既然您已经有了如何在Excel中使用间接函数的一般想法，我们可以尝试更强大的公式。

间接公式，动态地参考另一个工作表

Excel间接函数的有用性不仅限于构建“动态”细胞引用。您也可以利用它来指代其他工作表“飞行”中的单元格，这就是方法。

假设您在表1中有一些重要的数据，并且要在表2中将这些数据提取。以下屏幕截图演示了Excel间接公式如何处理此任务：

让我们分解您在屏幕截图中看到的公式并理解。

如您所知，引用Excel中另一张表格的通常方法是编写表格的名称，后跟感叹号和单元 /范围参考，例如SheetName！ranga 。由于表名称通常包含一个空间，因此您最好将其包装（名称，而不是一个space :)以防止错误，例如“我的工作台！” $ a $ 1 。

而现在，您要做的就是在一个单元格中输入表名称，在另一个单元格中输入单元格地址，将它们在文本字符串中加入，然后将字符串馈送到间接函数。请记住，在文本字符串中，您必须将每个元素包装在每个元素中，而只需双引号或数字，并使用串联操作员（＆）将所有元素链接在一起。

鉴于以上，我们得到以下模式：

间接（“'”＆ Sheet的名称＆“'！”和单元格从中获取数据）

返回我们的示例，您将表格的名称放在单元格A1中，然后在B列中键入单元格，如上面的屏幕截图所示。结果，您将获得以下公式：

INDIRECT("'" & $A$1 & "'!" & B1)

另外，请注意，如果您将公式复制到多个单元格中，则必须使用绝对单元格引用（如$ a $ 1）锁定对表格名称的参考。

笔记

• 如果包含2^张纸的名称和单元格地址的任何一个单元格（上述公式中的A1和B1）为空，则您的间接公式将返回错误。为了防止这种情况，您可以将间接函数包裹在IF函数中：

  IF(OR($A$1="",B1=""), "", INDIRECT("'" & $A$1 & "'!" & B1))

• 对于指代正确工作表的间接公式，应打开引用表格，否则公式将返回#REF错误。为避免错误，您可以使用IFERROR函数，该功能将显示一个空字符串，无论发生什么错误：

  IFERROR(INDIRECT("'" & $A$1 & "'!" &B1), "")

创建对另一个工作簿的Excel动态参考

指的是另一个Excel工作簿的间接公式基于对另一个电子表格的参考。您只需要指定工作簿的名称，就是表格名称和单元格地址的添加即可。

为了使事情变得更容易，让我们首先以通常的方式对另一本书进行引用（如果您的书和/或表格名称包含空格，则添加撇号）：'[book_name.xlsx] sheet_name'！

假设书籍名称在单元格A2中，则表格名称在B2中，并且单元地址在C2中，我们会得到以下公式：

=INDIRECT("'[" & $A$2 & "]" & $B$2 & "'!" & C2)

由于您不希望包含本书和表格的名称的单元格在将公式复制到其他单元格时会更改，因此您分别使用绝对单元格引用，分别使用$ a $ 2和$ b $ 2锁定它们。

现在，您可以使用以下模式轻松地编写自己对另一个Excel工作簿的动态引用：

=间接（“''[＆ book Name ＆“＆”]”＆ Sheet Name ＆“'！”＆单元格地址）

笔记。您的公式的工作簿应始终打开，否则间接函数将引发#REF错误。像往常一样，IFERROR功能可以帮助您避免它：

=IFERROR(INDIRECT("'[" & A2 & "]" & $A$1 & "'!" & B1), "")

使用Excel间接函数锁定单元格参考

通常，Microsoft Excel在表格中插入新的或删除现有的行或列时会更改单元格引用。为了防止这种情况的发生，您可以使用间接函数与任何情况下应保持完整的单元格参考。

为了说明差异，请执行以下操作：

1. 在任何单元格中输入任何值，例如，在单元格A1中的数字20。
2. 以不同的方式参考其他两个单元格的A1： =A1和=INDIRECT("A1")
3. 在第1行上方插入一个新行。

看看会发生什么？等于逻辑运算符的单元格仍然返回20，因为它的公式已自动更改为= a2。具有间接公式的单元格现在返回0，因为插入新行时没有更改公式，并且仍然是指当前空的单元格A1：

演示之后，您可能会印象是间接函数更令人讨厌，而不是帮助。好吧，让我们以另一种方式尝试。

假设，您想总结单元格A2：A5中的值，并且可以使用总和函数轻松执行此操作：

=SUM(A2:A5)

但是，无论删除或插入多少行，您都希望该公式保持不变。最明显的解决方案 - 绝对参考的使用 - 无济于事。为了确保，在某个单元格中输入公式=SUM($A$2:$A$5) ，插入一个新行，例如第3行，…找到转换为=SUM($A$2:$A$6) 。

当然，在大多数情况下，这种由Microsoft Excel提供的礼物都可以正常工作。但是，可能会有一些情况，而您不希望该公式自动更改。解决方案是使用间接函数，例如：

=SUM(INDIRECT("A2:A5"))

由于Excel将“ A1：A5”视为单纯的文本字符串而不是范围参考，因此当您插入或删除一行时，它不会进行任何更改。

与其他Excel功能一起使用间接

除了总和外，间接功能经常与其他Excel函数（例如行，列，地址，vlookup，sumif）一起使用。

示例1。间接和行函数

通常，行函数在Excel中使用以返回值数组。例如，您可以使用以下数组公式（请记住，需要按Ctrl Shift Enter ）来返回范围A1：A10：A10：A10：A10：A10：A10：

=AVERAGE(SMALL(A1:A10,ROW(1:3)))

但是，如果您在工作表中，第1行之间的任何地方插入新行，则行函数中的范围将更改为行（1：4），公式将返回4个最小数字的平均值，而不是3个。

为了防止这种情况发生，无论插入或删除多少行：行函数中的嵌套间接和数组公式始终保持正确：

=AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:3"))))

这是另外几个与大函数结合使用间接和行的示例：如何在范围内总和最大数字。

示例2。间接和地址功能

您可以将Excel间接与地址函数一起使用，以便在某个单元格中获得一个值。

您可能还记得，地址功能在Excel中使用，以通过行和列号获得单元格。例如，公式=ADDRESS(1,3)返回字符串$ c $ 1，因为C1是^第1条和^第3列的交点处的单元格。

要创建间接的单元格引用，您只需将地址函数嵌入这样的间接公式：

=INDIRECT(ADDRESS(1,3))

当然，这个微不足道的公式仅展示了该技术。以下是一些可能非常有用的示例：

• 间接地址公式 - 如何切换行和列。
• vlookup和间接 - 如何动态从不同的床单中获取数据。
• 与索引 /匹配的间接 - 如何使对案例敏感的Vlookup公式达到完美。
• Excel间接和Countif-如何在非连接范围或细胞选择上使用Countif函数。

在Excel中使用间接与数据验证

您可以将Excel间接函数与数据验证一起使用，以创建级联下拉列表，这些下拉列表根据第一个下拉列表中选择的用户选择的值显示不同的选择。

简单的依赖下拉列表确实很容易制作。它只需要几个命名范围来存储下拉菜单的项目和一个简单=INDIRECT(A2)公式，其中A2是显示您的第一个下拉列表的单元格。

为了使更复杂的3级菜单或带有多词条目的下拉菜单，您将需要具有嵌套替代功能的更复杂的间接公式。

有关如何与Excel数据验证使用间接的详细分步指南，请查看本教程：如何在Excel中列入依赖的下拉列表。

Excel间接功能 - 可能的错误和问题

如上面示例所示，在处理单元格和范围参考时，间接函数非常有用。但是，并非所有Excel用户都热切地拥抱它，主要是因为在Excel公式中广泛使用间接使用导致缺乏透明度。间接函数很难审查，因为它所指的单元不是公式中使用的值的最终位置，这确实令人困惑，尤其是在使用大型复杂公式时。

除上述内容外，与任何其他Excel功能一样，如果您滥用该函数的论点，间接可能会出现错误。这是最典型错误的列表：

Excel间接#ref！错误

最常见的是，间接函数返回#ref！在三种情况下错误：

1. ref_text不是有效的单元格引用。如果您的间接公式中的ref_text参数不是有效的单元格引用，则该公式将导致#REF！错误值。为避免可能的问题，请检查间接函数的论点。
2. 范围限制已超过。如果您间接公式的ref_Text参数是指超出1,048,576的行限制或16,384的列限制的一系列单元格，则您还将在Excel 2007，2010和Excel 2013中获得#REF错误。
3. 引用的表或工作簿已关闭。如果您的间接公式是指另一个Excel工作簿或工作表，则必须打开其他工作簿 /电子表格，否则间接返回#REF！错误。

Excel间接#Name？错误

这是最明显的情况，这意味着该函数的名称中存在一些错误，这使我们进入了下一个:)

在非英语环境中使用间接功能

您可能很想知道，间接函数的英文名称已翻译成14种语言，包括：

丹麦 - Indirekte 芬兰-Epäsuora 德语 - indirekt 匈牙利人-Indirekt 意大利人 - indiretto

挪威人 - Indirekte 波兰-Adr.pośr 西班牙语-Indirecto 瑞典-Indirekt 土耳其 - 多莱利

如果您有兴趣获取完整列表，请查看此页面。

非英语本地化的一个常见问题不是间接函数的名称，而是列表分离器的区域设置不同。在北美和其他一些国家 /地区的标准Windows配置中，默认列表分隔机是一个逗号。在欧洲国家，逗号保留为小数符号，列表分隔符设置为半隆。

结果，当在两个不同的Excel Locales之间复制公式时，您可能会收到错误消息“我们发现了该公式的问题…… ”，因为公式中使用的列表分离器与计算机上设置的列表不同。如果将本教程中的某些间接公式复制到Excel中时遇到此错误，只需将所有逗号（，）替换为Semicolons（;）即可修复它。

要检查机器上设置哪个列表分隔符和十进制符号，请打开控制面板，然后转到“区域和语言”>其他设置。

希望本教程能够阐明在Excel中使用间接。现在，您知道了它的优势和局限性，现在该对其进行镜头并了解间接功能如何简化您的Excel任务了。谢谢您的阅读！

以上是Excel间接功能 - 基本用途和公式示例的详细内容。更多信息请关注PHP中文网其他相关文章！