“不连续的"是指不连续的.公式中的范围规格 [英] "Non-contiguous" range specification in formula
问题描述
[2018-08-01:请参阅帖子底部附近的新材料.]
[2018-08-01: See new material near bottom of post.]
我刚刚遇到了QnA 此处，其中包括一个函数引用示例使用我以前从未见过的规格的范围.我试了一下，发现使用这种类型的引用时，大多数Excel函数都会返回一个值:
I just ran across a Q-n-A here that included an example of a function referencing a range using a specification I had not seen before. I played around with it a little and discovered that most Excel functions will return a value when using this kind of reference:
=SUM(A1:A5:C1:C5:E1:E2:F3:F4)
请注意其中的所有:".
Notice all the ":"s in there.
使用光标在编辑栏中，突出显示那些不连续的单元格，就好像一样，这些单元格将被总计.但是，当我进行实验时，我发现它等效于以下公式:
With the cursor in the formula bar, those non-contiguous cells are highlighted, as if only those cells would be totaled. However, when I experimented, I discovered that it is equivalent to this formula:
=SUM(A1:F5)
但是，这个公式可以达到我的期望:
However, this formula does what I expect:
=SUM(A1:A5,C1:C5,E1:E2,F3:F4)
示例:我将范围A1:F5定义为:
Example: I have the range A1:F5 defined as:
-- 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:F5) is 315
= SUM(A1:A5:C1:C5:E1:E2:F3:F4)为315
=SUM(A1:A5:C1:C5:E1:E2:F3:F4) is 315
= SUM(A1:A5，C1:C5，E1:E2，F3:F4)为117
=SUM(A1:A5,C1:C5,E1:E2,F3:F4) is 117
这说明了我的观点，即前两个似乎是等效的，而定义不连续范围的第三个正确"方法给出了不同的结果(但我希望如此).
This illustrates my point that the first two seem to be equivalent, and the third "correct" way of defining a discontiguous range gives a different result (but what I would expect).
我在互联网上和其他地方四处张望，没有找到相关的讨论.
I looked around here and elsewhere on the Internet and found no relevant discussion.
所以，我的问题:
- 该奇数范围引用是否有任何实际用途?
- 这真的是一种有效的范围参考吗?
[新材料2018-08-01]
[New material 2018-08-01]
@ YowE3K想知道如果数量奇数的单元格会发生什么情况.这是我的示例:
@YowE3K wondered what would happen with an odd number of cells. Here is my example:
-- A B C
1 1 1 1
2 1 1 1
3 1 1 1
4 1 1 1 现在，在一个遥远的单元格中，我输入以下公式:
Now, in a faraway cell, I enter this formula:
=SUM(A1:B3:$C$2)
然后，如果我拖动公式，它会产生有趣的结果，基本上是3行2列范围扩展到始终包含固定单元格的矩形范围.在此示例中，您可以获得的最小总和为3.尝试一下.
Then if I drag the formula around, it creates interesting results, basically the 3-row, 2-column range extended to a rectangular range that always includes the fixed cell. The smallest sum you can ever get in this example is 3. Try it and see.
这显示出一些有用的东西，尽管我还没有提出具体的用途.
This shows a glimmer of something useful, though I haven't come up with a concrete use for it yet.
推荐答案
我发现一种可能的用法是INDIRECT()可以与此语法结合使用.例如:
One possible use I have found is that INDIRECT() can be used in conjunction with this syntax. For example:
=SUM(INDIRECT("a"&H11&":a"&I11):A1:C1:C4:D4:F1)
其中单元格H11和T11的值等于该行.这允许具有最小范围的可变范围.
where cells H11 and T11 have a value equal to the row. This allows for a variable range that has a minimum range.
例如，如果我想要一个可以根据用户输入进行更新的范围，但包含A列和A列； B和第1行& 2我可能会使用以下内容:
If, as an example, I wanted to have a range that would update based on user input but included columns A & B and rows 1 & 2 I might use the following:
=SUM(INDIRECT(H11&":"&I11):A1:A2:A1:B1)
感谢分享，如果以后有机会在实时公式中使用它，我会进行更新.
Thanks for sharing, I'll update if I find an opportunity to use it in a live formula in the future.
这篇关于“不连续的"是指不连续的.公式中的范围规格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
