☰

如果值符合条件，则对范围求和，其中范围是使用相交运算符生成的

tag-icon • tag-icon microsoft-excel worksheet-function
如果值符合条件，则对范围求和，其中范围是使用相交运算符生成的

在我的特定情况下，我尝试仅对位于我指定范围交集内的正数求和。但是，相交运算符似乎不适用于 SUMIF 函数。

=SUMIF(Master_Categories Remaining,">0")

我认为这行不通，因为相交运算符会生成单元格列表而不是范围。还有其他方法可以实现这一点吗？

编辑：样本数据集

Master_Categories|Remaining
cat 1            |1
                 |2
cat 2            |-1
                 |-2
                 |3
cat 3            |3
                 |4
cat 4            |-3
                 |-4
cat 5            |5
                 |5

Master_Categories =!$1:$1,!$3:$3,!$6:$6,!$8:$8,!$10:$10
Remaining =!$B:$B

评估公式误差

在此处输入图片描述

答案1

该公式在其内部步骤中生成一对单行矩阵，其中包含每个范围内单元格的值。

目前还没有采取任何措施将它们转换成可以相加的东西。

首先是 Master_Categories 命名范围。这个想法似乎是，如果在匹配的 Master_Categories 单元格中有条目，则仅对剩余项求和。公式中的方法让人想起标准的“制作一个 {1,1,1,0,1,0,0,0,1} 样式矩阵”，但并未完成从矩阵中返回的文本转换为 TRUE/FALSE 条目并最终转换为 1 和 0 的所有步骤。在这种情况下，联合运算符所做的只是生成一个#NULL!。

因此，创建该部分所需的矩阵，并强制在没有文本时的值为 0：

=NOT(ISBLANK(Master_Categories))*1

成功了。ISBLANK()测试空白是否产生与所需相反的结果，然后NOT()将其反转。最后一步是将文本 TRUE/FALSE 值转换为 1 和 0。对它们执行一个简单的、没有其他影响的算术运算（“ *1 ”或“ +0 ”）即可实现这一点。

然后是剩余部分。对该部分使用以下内容：

=((Remaining>0)*1)*Remaining

第一位测试每个值是否与“>0”相等，返回 TRUE/FALSE 值，这些值由“乘以 1”部分转换为 1 和 0。此部分的最后一部分取 1 和 0 的结果矩阵，并将其乘以 Remaining 中的实际值矩阵。这会删除 <0 项，因为它们在 1 和 0 中的值是 0，而零乘以任何值都是 0。然后，您将得到一个 Remaining 的实际值矩阵，这些值是 >0 和 0。

然后“ SUM()”一切（不是SUMIF()因为你已经选择了）：它将两个矩阵相乘（第一部分中的 1/0，第二部分中的实际列值和 0），因此第一部分中的 1“收集”剩余的值并将它们相加，最终给出你的答案。

它必须是一个 CSE 公式：

{=SUM(NOT(ISBLANK(Master_Categories))*(Remaining>0)*Remaining*1)}

所以...基本上，测试*测试*测试（乘法是算术中的逻辑AND，加法是逻辑OR）得到感兴趣的值然后Sum()完成它。

附注：

您可以在命名范围内完成每个部分的所有操作（使用前面显示的精确单独部分以及代替其中每个名称的文字范围），而不仅仅是定义它们的单元格范围。那么公式可以是：

{=SUM(Master_Categories * Remaining)}

你可能会更喜欢这个。（仍然必须是 CSE 公式。）

最后一点：编辑公式 ( F2)，突出显示部分（通常从“内向外”），然后按F9查看 Excel 如何评估它们，通常会显示与公式评估器显示的完全不同的图片。显然，它们大致相同，但可以细化到您想要的程度。它可能非常有用。

编辑以稍微简化公式。很多时候，看似随意，将数组更改为 1 和 0 会失败，而无需用括号括起来，而且我一直认为每个矩阵都需要一个“*1”（或“+0”）才能更改，但在查看是否可以删除多余的括号时，我进行了实验，发现整个乘法字符串的单个“*1”同样有效。不过仍然是 CSE……诱人的是，当整个内部公式归结为F9'ed最终矩阵时，SUM()无需 CSE 即可得到答案，但我找不到让它用部分来做到这一点的方法。

相关内容