公式中的“非连续”范围指定

Question

[2018-08-01：请参见帖子底部新材料。]

我刚刚遇到了一个Q-n-A 这里，其中包括一个函数引用范围的示例，使用了一种我之前没有见过的规范。我尝试了一下，并发现大多数Excel函数在使用此类引用时将返回一个值：

=SUM(A1:A5:C1:C5:E1:E2:F3:F4)

注意其中的所有“:”。

当光标在公式栏中时，这些不连续的单元格会被突出显示，好像只有这些单元格会被合计。然而，当我进行实验时，我发现它等同于这个公式：

=SUM(A1:F5)

然而，这个公式确实达到了我的预期：

=SUM(A1:A5,C1:C5,E1:E2,F3:F4)

示例：我定义了范围A1:F5：

--  A   B   C   D   E   F
1   1   2   3   4   5   6
2   2   4   6   8   10  12
3   3   6   9   12  15  18
4   4   8   12  16  20  24
5   5   10  15  20  25  30 

这个公式的结果是:

=SUM(A1:F5)为315

=SUM(A1:A5:C1:C5:E1:E2:F3:F4)为315

=SUM(A1:A5,C1:C5,E1:E2,F3:F4)为117

这表明了我的观点，前两个看起来是等价的，而第三种“正确”的离散范围定义方式给出了不同的结果（但这是我所期望的）。

我在这里和互联网上四处查找，没有找到相关的讨论。

因此，我的问题是：

1. 那种奇怪的范围引用有任何实际用途吗？
2. 它确实是一种有效的范围引用吗？

---

[新材料2018-08-01]

@YowE3K想知道一个奇数单元格会发生什么情况。以下是我的示例：

--  A   B   C
1   1   1   1
2   1   1   1
3   1   1   1
4   1   1   1   

现在，在一个遥远的单元格中，我输入了这个公式：

=SUM(A1:B3:$C$2)

那么，如果我拖动这个公式，它会产生有趣的结果，基本上是将3行2列范围扩展为一个矩形范围，该范围始终包括固定单元格。在此示例中，您可以获得的最小总和为3。尝试一下。

这显示出了一些有用的迹象，尽管我还没有想出具体的用途。

- vknowles

---

我猜它很有用，因为它自动给出了多区域范围的边界框（手动在公式中计算会很棘手）。否则我也没有看到过它。 - GSerg

@GSerg - 在我的原始问题中（链接在我的OP上方），有一个有趣的讨论。 - vknowles

我想不出那种语法有很多用途，但是也许在某些地方会有用，比如=SUM(A1:A10:A$9)（其中一个单元格是绝对的，但其他单元格是相对范围）。 - YowE3K

@YowE3K - 我喜欢那个想法。我设置了一个示例工作表并将试玩它。当你复制公式时，它会产生有趣的模式。 - vknowles

我在“SUM(A1:B3:$C$2)”上使用了“Evaluate Formula”，Excel将其转换为“SUM($A$1:$C$3)”。“Evaluate Formula”有两个要点：1）范围被转换为一个矩形，其尺寸可容纳所有引用的单元格；2）新引用将始终包括具有绝对引用的单元格。这个有趣的属性是相对引用部分有效地定义了一个“最小尺寸”的维度。当你拖动公式时，它可以扩展，始终包括绝对引用，但当相对和绝对引用相交时，将保持最小尺寸。 - Ken Love

Answer 1

1. 当用逗号分隔时，越简单越好。避免引用同一矩形块内的子集，如果您只选择了所涉及区域的左上角和右下角，Excel会创建该块。

2. 不，这里没有太多实际练习需要额外的复杂性（仅引用由冒号分隔的单元格）。我读到一个人的评论/提议回答，建议根据数据结构的情况使用它。我建议那个人在使用过于复杂的引用体制/策略之前寻求更好的数据格式/布局。

3. 关于逗号分隔：是的，有大量实际的有效练习正在得到适当的管理。

背景/详细信息

您提出了两个问题：即与1）'奇怪的引用'的有效性和2）潜在用途相关的问题（改述）。

关于有效性 - 回到基础，当您求和整个区域时，您只是引用最左上角和最右下角的单元格 - Excel会自动创建一个“周长”来包含由冒号隔开的任何单元格。该周长始终构建为矩形（正方形是矩形的一种特殊类型）。实际上，您可以“强制”Excel最初保持这些单元格分开，即第一次和第二次求和在参数上和结果上都是相同的：

这样做的最重要的含义/后果可能是：

• 无论有多少“子范围”，不管它们是分离的还是重叠的，只要它们用冒号分隔开来，你都不能进行双重求和。例如，以下内容给出的解决方案与前两个图形完全相同：

然而，对于大多数实际应用来说，要求多子集引用（特别是在求和方面）是罕见且奇怪的。我可以想象在使用用逗号分隔的各种子集时会出现这样的情况。

通常需要在公式中保持简洁性/简单性（以进行审计、审核、验证、计算速度、错误缓解等）。

我相信很难构思一个情景，在数据表格内或跨越多个表格引用多个子集有助于实现某种目的，而无法使用图1所示的简单、直接的引用方式来实现。

事实上，为了证明类似图3所示的总和，需要进行高度定制的练习/结构，并且作为一名长期审查电子表格模型的经理，我肯定不建议构建这种引用方式的总和。

越考虑它，这种多重引用方式会妨碍功能。例如，使用图1-2中的引用样式允许对多个（相邻）工作表进行求和（我不太喜欢这种方法，因为任何放置在第4张和第2张之间的工作表都将被包括在总和中，这可能会导致错误）。

=SUM(Sheet4:Sheet2!A1:F5)

当第2、3和4个表格包含与您提供的相同的值时，结果为945（=315 x 3），这是可以预期的。但将此操作应用于版本3会导致#Value!（即使Excel也不喜欢它！）。

现在，就逗号分隔而言，这是完全不同的事情，但在内部涉及类似的“机制”。 Excel仅会“围栏”由冒号分隔的单元格，并且它将创建几个“环形围栏”的矩形周界，每个由逗号分隔的单元格组都有一个。

这种“设置”具有许多实际用途：它允许对非连续的单元格组进行求和；可以对每个条件进行不同的放置，例如，下面是一个函数，它将左侧所有偶数和右侧所有奇数进行求和：

=SUM(A1:C5*(MOD(A1:C5,2)=0),D1:F5*(MOD(D1:F5,2)<>0))

这将产生99（如果我没有混淆模数除数/操作数的话：）。PS-此时我意识到我错误地复制了您的值，并无意中将行标签包括在列中。因此，99基于“正确”的值，这与您显示的结果相符。

当然，我可以对左侧和右侧进行求和（没有任何条件，或者有一些也将被满足的条件，例如mod（a，b）＜0），其中每半部分由逗号分隔。当然，这将产生与图1-3中所示的整个区域求和完全相同的结果。

因此，当用逗号分隔时，Excel会像图1-3中那样对待每个“子集”中的“冒号”分隔单元格。在这里，重复/重叠或省略的范围确实计数/有所不同。

在冒号或逗号引用方面，没有“奇怪”的形式，只有好/最佳实践，但随着无限不同的可能性/数据表结构、倡议和用途/模型等，毫无疑问会存在某些使用复杂形式的简单总结（当由冒号分隔时）; 我认为这些是罕见的/少见的，并建议如果可以避免，则不要进行复杂的设置。

然而，我看到更多的应用/目的是将2/3个或更多子集用逗号分隔的类似结构。 实际上，这有效地实现了与状态栏消息相同的结果（选择要“显示”的汇总统计数据，即右键单击并选择它后）：

- JB-007

Answer 2

这个奇怪的区域引用有什么真正的用途吗？-- 是的。根据你所使用的数据结构，你可能没有在表格中添加额外行/列的特权。

这真的是有效的区域引用吗？-- 既然能工作，那就是有效的。无论它是否被正确记录下来。祝贺你的发现！我也在学习新的东西。（：

- p._phidot_

---

我不太明白 #1。你是在说，也许用户没有访问 A1:F5 单元格的权限，但是使用 A1:A5:C1:C5:E1:E2:F3:F4 可以解决这个问题吗？你能举个例子吗？谢谢！ - vknowles

我的意思是，通常（按照我的标准），您将奇数范围的数据收集到1列中>然后对该列执行操作。它可以是sum() index() average()甚至是index()＆match()。但在某些情况下（通常是公司），拥有许多用户/编辑器相同的文件。添加/更改行/列不是一个选项...那么定义奇怪的单元格范围可能会有所帮助。顺便说一句，我仍然喜欢使用适当的A1：A5，C1：C5，E1：E2，F3：F4方式来定义奇怪的区域。 - p._phidot_

很抱歉，这并没有涵盖到问题的本质。请查看我对原始问题所作的编辑。因为您提出的“正确”方法提供了不同的结果，我仍然不知道使用“不正确”的方法是否有任何好处。 - vknowles

对我来说，这似乎是一个很好的混淆 Excel 代码的候选人。我的帽子向你致敬，因为你在“解释”Excel 文件上做了五星级算法分析，我印象深刻。然而，在我看来，使用“不当”的范围定义并没有获得额外的好处。祝你好运，希望能找到更好的答案。(: - p._phidot_

Answer 3

我发现INDIRECT()可以与此语法结合使用，有一个可能的用途。例如：

=SUM(INDIRECT("a"&H11&":a"&I11):A1:C1:C4:D4:F1)

其中单元格H11和T11的值等于行号。这允许使用具有最小范围的可变范围。

例如，如果我想要一个基于用户输入但包括列A和B以及行1和2的范围，我可以使用以下公式：

=SUM(INDIRECT(H11&":"&I11):A1:A2:A1:B1)

感谢分享，如果我将来找到在实际公式中使用它的机会，我会更新。

- I like Excel very much