隐式交叉点：在Excel公式中使用 @ sign

Excel 的隐式交集运算符 @ 符号详解

Excel 的隐式交集功能已存在多年，但鲜为人知。直到 Excel 365 中不再将其作为默认行为后，才引发了许多疑问。本教程旨在解释 Excel 公式中“@”符号（称为隐式交集运算符）的含义。

您可能觉得对自己的工作簿了如指掌，然后公式开头突然出现一个“@”字符。这是什么意思？它究竟是如何工作的？简而言之，它是一个隐式交集运算符，它禁用公式的新默认数组行为，并指示 Excel 返回单个值。更多详情，请继续阅读。

Excel 隐式交集

Excel 中的隐式交集是指将多个值简化为单个值。通常情况下，当向公式提供数组或范围时，该公式应该在一个单元格中只输出一个值。

Excel 2019 - 2000 中的隐式交集

在传统 Excel 中，隐式交集是默认行为。它在后台为所有公式默默执行。隐式交集逻辑如下：

• 如果公式产生单个值，则返回该值（实际上，在这种情况下，隐式交集什么也不做）。
• 对于范围，使用与公式位于同一行或列的单元格中的值。
• 对于数组，使用左上角的值。

例如，当乘以两列数字时，Excel 只选择与公式位于同一行中每列的一个数字，并且只在一个单元格（我们的例子中为 D2）中输出结果：

=B2:B5*C2:C5

要乘以其他单元格中的数字，您需要向下复制公式。

要禁用隐式交集，必须使用 Ctrl Shift Enter 输入数组公式（这就是传统数组公式有时被称为 CSE 公式 的原因）。这明确地告诉 Excel 处理作为范围或数组输入的多个值。

在我们的例子中，选择单元格 D2:D5，键入上述公式，然后按 Ctrl Shift Enter 键一起确认。完成后，公式将用大括号 { } 括起来，表示这是一个数组公式。结果，每一行中的数字都会立即相乘：

Excel 365 中的隐式交集

动态数组的引入改变了 Excel 365 中所有公式的默认行为。现在，任何可能产生多个结果的公式都会自动将其溢出到工作表中。这使得隐式交集变得不必要，并且不再默认触发。因此，Excel 365 有时被称为 动态数组 Excel 或 DA Excel。

在这里，整个范围使用仅在最顶部的单元格 (D2) 中输入的常规公式相乘：

=B2:B5*C2:C5

结果是一个包含 4 个单元格的溢出范围：

如果希望公式只返回一个值，则需要显式地启用隐式交集。为此，他们引入了一个特殊的运算符，在下一节中，您将找到有关它的完整详细信息。

隐式交集运算符 - Excel 公式中的 @ 符号

隐式交集运算符是在 Excel 365 中引入的，用于防止默认的动态数组行为。如果希望公式只返回一个值，请在函数名称之前（或公式内某个范围或数组之前）加上“@”，它将像在动态版本之前的常规非数组公式一样运行。

例如：

=@B2:B5*@C2:C5

注意：隐式交集运算符仅在 Microsoft 365 订阅中受支持。如果您尝试在旧版本中添加“@”符号，则在完成公式后它将被静默删除。

为什么会在旧公式中添加 @？

在 Excel 365 中，打开在旧版本中创建的工作簿时，您可能会注意到某些公式中添加了“@”字符。在大多数情况下，这样做是为了强制公式的行为与在创建它的原始版本中的行为相同。换句话说，如果公式在旧版本中返回单个值，但在 Excel 365 中会返回多个结果，则会在其前面自动加上“@”以禁用数组行为。

通常，交集运算符插入在可能返回多单元格数组或范围的函数之前，例如 OFFSET、INDEX 或用户定义函数。

例如，在动态 Excel 之前的 Excel 中编写的以下公式：

=INDEX(B2:C5,,F1)

在动态数组 Excel 中将采用以下形式：

=@INDEX(B2:C5,,F1)

原因是，如果没有“@”运算符，公式将返回 C2:C5 中的所有值，因为省略了 INDEX 函数的 row_num 参数。为了确保在所有版本中的一致性，先前不明显的隐式交集变得显而易见。请比较结果：

如果可以处理数组并输出单个结果的函数（例如 SUM、COUNT、AVERAGE 等）中嵌套了具有潜在多单元格输出的函数，则没有理由触发隐式交集，并且该公式将按原样传输到动态 Excel 中，而无需添加“@”符号。例如：

=AVERAGE(INDEX(B2:C5,,F1))

可以从公式中删除 @ 符号吗？

当然可以。Excel 只执行一次公式转换。如果“@”运算符破坏或负面改变了公式的行为，您可以手动删除“@”，它在保存工作簿后将不会再次出现。

删除隐式交集运算符的后果是什么？根据“@”符号后面的公式部分返回的内容，可能有三种可能的结果：

• 如果返回单个值，则不会发生任何更改。
• 如果返回数组，它将溢出到相邻单元格。
• 如果返回数组，但没有足够的空单元格来显示所有值，则会发生 #SPILL 错误。

为什么在 Excel 365 中使用 @ 运算符？

如前所述，Excel for Microsoft 365 默认情况下将所有公式都视为数组公式，而无需像在以前的版本中那样按 Ctrl Shift Enter。如果要禁用数组行为，则插入隐式交集运算符。

示例 1. 消除 #SPILL! 错误

一个非常常见的场景是防止或修复 #SPILL 错误。如果您觉得方便引用整列（这通常不是一个好主意，因为它会降低 Excel 的速度），但是这样的公式在动态版本之前的版本中可以正常工作：

=VLOOKUP(A:A, D:E, 2, FALSE)

在动态 Excel 中，它将导致 #SPILL 错误，因为没有足够的空间来显示近 105 万个结果。

在查找值参数之前添加“@”可以解决此问题：

=VLOOKUP(@A:A, D:E, 2, FALSE)

当 Excel 预见公式可能会溢出工作表的边缘时，它会建议进行更正，明智的做法是接受它：

示例 2. 使公式在旧版 Excel 中正常工作

如果您使用的是 Microsoft 365 订阅并与坚持使用旧版本的人共享文件，则必须确保对每个人而言公式行为的一致性。

假设您在 Excel 365 中编写了此动态数组公式：

=B2:B5*C2:C5

在旧版本中，它将转换为旧版 CSE 数组公式：

{=B2:B5*C2:C5}

并且在这两种情况下都会返回一系列值：

如果希望只输出一个结果，请在每个表达式之前添加“@”，然后根据需要将公式复制到尽可能多的单元格（要保持范围不变，请记住使用绝对单元格引用锁定它们）：

=@$B$2:$B$5*@$C$2:$C$5

在旧版 Excel 中，“@”符号将被自动删除，公式将采用正常外观：

=$B$2:$B$5*$C$2:$C$5

不应做的是在一个公式中混合隐式交集和数组计算！例如，如果您尝试在 Excel 365 中输入以下内容：

=@B2:B5*C2:C5

您将收到通知，此公式在旧版本中不受支持：

如果您拒绝建议的变体，则混合公式将被提交并会提供一些结果（尽管它可能不是您预期的结果）。但是，当您在动态 Excel 之前的 Excel 中打开该公式时，将出现 _xlfn.SINGLE 函数而不是隐式交集运算符：

=_xlfn.SINGLE(B2:B5)*C2:C5

当旧版 Excel 计算此公式时，将返回 #NAME! 错误。

Excel 表格中的隐式交集

一般来说，表格中的隐式交集行为与您的 Excel 版本一致。

在Excel 2019 及更早版本中，您可以引用整列，公式将解析为当前行中的单个单元格。

例如，此公式成功地将“价格”和“数量”列中的数字相乘：

=[Price]*[Qty.]

即使它引用了整列，动态 Excel 之前的 Excel 仍然在行级别操作单个值。

在Excel 365中，这种方法将不起作用，因为隐式交集默认情况下是关闭的。因为公式返回多个值，并且 Excel 无法将它们全部放入表格中，所以它会建议在列名前加 @ 符号：

如果您有使用表格引用的经验，此语法应该很熟悉 - @ 符号表示公式将处理同一行中“价格”和“数量”列的值。

=[@Price]*[@[Qty.]]

它将在所有版本中都能很好地工作：

提示：如果您打算使用动态数组函数来计算表格的数据，请确保将公式放在表格之外，因为在表格内不支持动态数组。

这就是 Excel 中隐式交集的工作方式。感谢您的阅读，希望下周在我们的博客上再见！

以上是隐式交叉点：在Excel公式中使用 @ sign的详细内容。更多信息请关注PHP中文网其他相关文章！