“非连续"公式中的范围指定 [英] "Non-contiguous" range specification in formula
问题描述
[2018-08-01:查看帖子底部附近的新材料.]
我刚刚遇到了一个 QnA
可能最重要的含义/后果如下:
- 无论有多少子范围"、不同/重叠或其他方式，您都不能重复求和，前提是这些子范围以冒号分隔.例如，下面给出了与前 2 个数字完全相同的解决方案:
然而，对于大多数实际应用，需要多子集引用(特别是在求和时)是罕见和奇怪的.当使用用逗号分隔的各种子集时，我可以设想这样的事情.
一般来说，公式化需要简洁/简单(为了可审计性、可审查性、验证、计算速度、减少错误等).
我相信，在使用图 1 中简单直接的引用无法实现的情况下，编造/理解在数据表内(或跨)引用多个子集的场景将是非常具有挑战性的.
>事实上，它需要高度定制的练习/结构来证明图 3 中所示的总和之类的东西的合理性，并且作为一名几乎每天都在审查 excel 模型的长期服务经理，我当然建议不要构建一个以这种方式引用的求和.
我考虑的越多，这种方式的多重引用就会阻碍功能.例如，使用图 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(如果我没有混淆 mod 除数/操作数:).PS - 此时我意识到我错误地复制了您的值，并且无意中将行标签作为列包含在内.因此，99 基于与您显示的结果一致的正确"值.
当然，我可以取左 & 的总和.右侧(没有任何这样的条件，或者有一些也将满足的条件，例如 mod(a,b) <0)，其中每一半用逗号分隔.当然，这将产生与对整个区域求和(如图 1-3 所示)完全相同的总体结果.
因此，当用逗号分隔时，Excel 将其视为与图 1-3 完全相同，但它仅针对冒号"分隔的单元格的每个子集"执行此操作.在这里，重复/重叠或省略的范围确实重要/有所作为.
当涉及到冒号或逗号时，没有奇怪"的引用形式，有好的/最佳实践，但随着无限不同的可能性/数据表结构、倡议和用途/模型等/毫无疑问将some 用于复杂形式的简单求和(用冒号分隔时)；我认为这些很少见/相差甚远，如果可以避免，建议不要进行复杂的设置.
但是，我认为具有 2/3 或更多子集以逗号分隔的类似构造具有更大的应用/用途.事实上，这有效地实现了与状态栏消息相同的结果(汇总统计选择为显示"，即在右键单击并选择它之后):
[2018-08-01: See new material near bottom of post.]
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)
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
The formula results are:
=SUM(A1:F5) is 315
=SUM(A1:A5:C1:C5:E1:E2:F3:F4) is 315
=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.
So, my questions:
- Is that odd range reference of any real use?
- is it really a valid sort of range reference?
[New material 2018-08-01]
@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)
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.
When separated by a comma, simpler is better. Avoid referencing subsets within the same rectangular block that Excel would create if you only selected the upper left and bottom right of the region in question.
No, there are not too many practical exercises that warrant this additional complexity (when referencing cells separated by a colon only). I read a comment/proposed response by someone who suggested there is use, depending on the data structure. I advise that individual seek a better format/ layout of data before using an overly complex referencing regime/strategy
RE: separation by comma: Yes, there are numerous practical and valid exercises that are being managed in suitable fashion
Background / detail
You have asked two questions: i.e. pertaining to 1) validity of 'odd referencing' and 2) potential use (paraphrased).
Re: validity - going back to basics, when you sum the whole are you are simply referencing the upper most left and bottom most right cells - Excel automatically creates a 'perimeter' to encompass any and all cells separated by a colon. This perimeter is always constructed to form a rectangle (the square being a special type of rectangle). You can actually 'force' Excel to keep these cells separated initially, i.e. the first and second summations are identical (parametrically, and in terms of their result):
Probably the most important implication/consequence of this is as follows:
- You cannot double-sum no matter how many 'sub-ranges', disparate / overlapping or otherwise, PROVIDED these are separated by a colon. For instance, the following gives exactly the same solution as the first 2 figures:
However, for most practical applications, it would be rare and odd to require multi-subset referencing (especially when it comes to summation). I could envisage something like this when using various subsets that are separated with a comma.
Generally, parsimony/simplicity in formulation is required (for auditability, reviewability, validation, computationally speed, error mitigation, and so on).
I believe it would be quite challenging to concoct / fathom a scenario where referencing several subsets within (or across) tables of data served a cause that couldn't be achieved using the simple, straightforward referencing per Figure 1).
In fact, it would require a highly bespoke exercise/structure to justify something like the summation shown in Figure 3, and as a long-serving manager who reviews excel models almost on a daily basis, I would certainly advise against constructing a summation that referenced in this manner.
The more that I consider, it, multiple referencing of this manner impedes functionality. For instance, using the referencing style in Figures 1-2 allows one to take summation across multiple (adjacent) sheets (which I'm not a big fan of because any sheet that's placed between Sheets 4 and 2 will be included in the summation, which can lead to errors):
=SUM(Sheet4:Sheet2!A1:F5)
Where Sheets 2, 3, and 4 contain identical values to those you provided, which yields 945 (=315 x 3) as one would expect. Doing this for version 3 results in #Value! (not even Excel likes it!).
Now, in terms of separation by commas, this is an entirely different thing but involves similar 'mechanics' under the hood. Excel will only 'perimeter-fence' cells separated by a colon, and it will create several 'ring-fenced' rectangular perimeters, one for each group of (colon-separated) cells that are separated by a comma.
This 'setup' has a number of practical uses: it allows non-contiguous groups of cells to be summed; different conditions can be placed on each e.g. here's a function that sums all even numbers on the left hand side, and all odd numbers on the right:
=SUM(A1:C5*(MOD(A1:C5,2)=0),D1:F5*(MOD(D1:F5,2)<>0))
This yields 99 (if I haven't mixed up the mod divisor/operand:). PS - at this point I realised I copied your values incorrectly and inadvertently included the row labels as a column. So the 99 is based upon the 'correct' values which reconciles to results you've shown.
Of course, I could take the sum of the left & right side (without any such conditions, or with some condition that will also be satisfied, e.g. mod(a,b) < 0), where each half is separated by a comma. Of course this would yield the exact same overall result as summing the entire region like shown in Figures 1-3.
So, when separated by a comma, Excel treats this exactly the same as Figure 1-3 BUT it only does so in respect of each 'subset' of 'colon' separated cells. Here, duplication/overlapping or omitted ranges do count / make a difference.
There is no 'odd' form of referencing when it comes to colon or comma, there is good/best practice, but with the infinite different possibilities/data table constructs, initiatives and uses/models etc/ there will undoubtedly be some use for complicated forms of straightforward summations (when separated by a colon); I see these as being rare/far-between and advise against a complicated set up if it can be avoided.
However, I see greater application / purpose for a similar construct with 2/3 or more subsets separated by a comma. In fact, this effectively achieves the same outcome as the status bar message (with summation statistics selected to 'display', i.e. after right-clicking and selecting it):
这篇关于“非连续"公式中的范围指定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
