如何从动态命名范围中抵消并在SUMPRODUCT中使用该范围? [英] How to OFFSET from a dynamic named range and use that range in SUMPRODUCT?

问题描述

情况:

尝试将动态命名范围的OFFSET传递给SUMPRODUCT时得到#Value!.

I am getting #Value! when trying to pass the OFFSET of a dynamic named range to SUMPRODUCT.

设置:

我在Sheet1的A2:B4范围内有以下数据.

I have the following data in range A2:B4 of Sheet1.

| TextA | 1 |
|-------|---|
| TextA | 2 |
|-------|---|
| TextB | 3 |

我已经使用公式创建了一个动态命名范围textRange，该范围是A列中的值:

I have created a dynamic named range, textRange, of the values in column A with the formula:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1048576),1)

如图所示:

注意:textRange将提取所有必需的行.可以假定它是从连续填充的范围拉出的，即textRange覆盖了所有需要的行.

Note: textRange will pick up all required rows. It can be assumed to be pulled from a continuously populated range i.e. textRange covers all the rows required.

目标:

我现在想使用Column函数从中Offset来获得相同的第1列，即B2:B4；如果A列中的相应文本以"A"结尾，则求和该范围内的值.

I now want to Offset from this, using the Column function, to get the same rows 1 column across i.e. B2:B4; and then sum the values in this range if the corresponding text in column A ends with "A".

预期结果为3.

过程:

1)我正在使用以下公式来构建偏移范围:

1) I am using the following formula to construct the offset range:

OFFSET(A2,0,COLUMN(B1)-1,COUNTA(textRange),1)

偏移量范围可以是A之后的任何列，因此我使用Column函数，因此可以将公式拖到感兴趣的返回范围.

The offset range could be any column after A, hence my use of Column function so can drag formula across to return range of interest.

2)然后，我将此偏移量范围传递给SUMPRODUCT，并在相应的Column A行的最后一个字母为"A"的情况下求和.

2) I then pass this Offset range to SUMPRODUCT and sum it's value if the corresponding Column A row has "A" as it's last letter i.e.

=SUMPRODUCT(OFFSET(A2,0,COLUMN(B1)-1,COUNTA(textRange),1),--(RIGHT(textRange,1)="A"))

结果:

预期结果为3，但当前为#Value!

The expected result would be 3 but is currently #Value!

问题:

我做错了什么?我猜这是因为我越过了范围.

What I am doing wrong? I am guessing it is because of how I am passing the range.

请求的解决方案:

我愿意以任何其他方式获得相同的结果.但是，在跨列拖动时，公式必须更新动态范围的偏移量"，并且必须对新的行集执行条件求和.

I am open to any other way of achieving the same result. However, the formula must update the Offset for the dynamic range, when dragged across columns, and must perform the conditional sum on the new set of rows.

参考:

https://chandoo.org/forum/线程/使用偏移量的求和乘积.960/

推荐答案

#Value！错误似乎是由于Column(B1)生成了一个单单元格数组，您可以查看是否使用Evaluate Formula跟踪它.

The #Value! error seems to be coming from the fact that Column(B1) produces a single-cell array as you can see if you trace through it with Evaluate Formula

这肯定是一个奇怪的例子，但是我猜测OFFSET会尝试返回一个范围数组，该范围只能由几个函数处理-N()，SUBTOTAL和INDEX.

It's certainly a strange one but I'm guessing that OFFSET would then try to return an array of ranges, which can only be dealt with by a few functions - N(), SUBTOTAL and INDEX.

您可以通过像这样对数组求和来修复它

You can fix it by SUM-ing the array like this

=SUMPRODUCT(OFFSET($A2,0,SUM(COLUMN(B1)-1),COUNTA(TextRange),1),--(RIGHT(TextRange,1)="A"))

或将偏移量包装在索引中

or wrapping the OFFSET in an INDEX

=SUMPRODUCT(INDEX(OFFSET($A2,0,COLUMN(B1)-1,COUNTA(TextRange),1),0,1),--(RIGHT(TextRange,1)="A"))

这篇关于如何从动态命名范围中抵消并在SUMPRODUCT中使用该范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！