![在 SUMIFS 中包含空值和非空值的标准](https://linux22.com/image/1588485/%E5%9C%A8%20SUMIFS%20%E4%B8%AD%E5%8C%85%E5%90%AB%E7%A9%BA%E5%80%BC%E5%92%8C%E9%9D%9E%E7%A9%BA%E5%80%BC%E7%9A%84%E6%A0%87%E5%87%86.png)
总结:
我想改变配方
=SUMIFS(sumRange, criteriaRange1, criterium1, criteriaRange2, criterium2),
这样,如果满足某个条件,它就会忽略标准 2。假设有一个单元格(“控制单元格”),当必须忽略标准 2 时,该单元格的值为 FALSE,而当必须应用标准 2 时,该单元格的值为 TRUE。
我希望将公式改为如下形式:
=SUMIFS(sumRange, criteriaRange1, criterium1, criteriaRange2, IF(controlCell = FALSE, X, criterium2))
=SUMIFS(sumRange, criteriaRange1, criterium1, IF(controlCell = FALSE, Y, criteriaRange2), criterium2)
=SUMIFS(sumRange, criteriaRange1, criterium1, IF(controlCell = FALSE, Y, criteriaRange2), IF(controlCell = FALSE, X, criterium2))
并且想知道要使用什么值或字符串文字X
,或者要使用什么文字Y
,以实现这一点。
我目前最喜欢的解决方案是:(1)在表中添加一列,用 填充=TRUE
,然后(2)引用此列Y
,并使用TRUE
。X
这可行,但缺点是我需要添加一列(我不想弄乱输入数据)
长读:
我有一张大表,我想计算某一列的总和。我只想包含符合特定条件的行 - 这是典型的情况SUMIFS
。
假设这是一张表,名为inv
:
如果我想知道我有多少颗红葡萄,无论大小,我都可以这样做
=SUMIFS(inv[Qty];inv[prd];"Grapes";inv[col];"red")
并得到正确答案10
。
到目前为止,一切都很好。
现在,我想知道我有多少个苹果,无论大小和颜色。
通常情况下,这很容易,
=SUMIFS(inv[Qty];inv[prd];"Apple")
给出正确答案28
。
但由于我的限制,我必须col
在公式中包含 -column。如果您想知道原因,我会在下面添加它,但对于我的问题的解决方案,它是无关紧要的。我需要知道的是,我可以在此列上放置什么条件,以便它接受此列中具有任何(或没有)值的行。(请注意,并非所有苹果行都为颜色设置了值)。
我已尝试过:
=SUMIFS(inv[Qty];inv[prd];"Apple";inv[col];"")
=SUMIFS(inv[Qty];inv[prd];"Apple";inv[col];"*")
=SUMIFS(inv[Qty];inv[prd];"Apple";inv[col];"<>")
第一个使用""
作为标准,结果给出了错误的答案12
,因为它只选取了没有颜色的行。而其他使用"*"
和"<>"
作为标准,结果给出了错误的答案16
,因为它们只选取了有一种颜色。
有人知道我可以使用什么作为标准,以便它同时包含空行和非空行吗?
有兴趣者请注意:
好的,为什么我需要以SUMIFS
这种方式使用公式,即以inv[col]
列作为条件范围?
- 首先,实际上我的表格比这个要长得多,而且我需要做很多事情
SUMIFS
。因此,还有一个额外的“摘要”表格,这里的例子是
最后一行颜色列中的空值意味着颜色应被忽略作为标准。如您所见,使用我当前使用的公式(下面会提到),我在该行中得到了一个错误的值,因为我想要得到28
。
因为汇总表也很长,所以我在最后一列输入的公式需要统一,即对于这个汇总表的每一行都相同。
在这个小例子中,看起来合乎逻辑的是
=IF([@color]="";SUMIFS( inv[Qty];inv[prd];[@product]);SUMIFS(inv[Qty];inv[prd];[@product];inv[col];[@color]))
。
但是,这在我的实际案例中也不是一种选择,因为库存表也相当宽。我有很多列与本例中的颜色列处于相同的情况。(并且每个额外的列都会使公式需要自定义公式的情况数量增加一倍SUMIFS
。)
- 只是为了向你展示我目前拥有的东西:
=SUMIFS(inv[Qty];inv[prd];[@product];inv[col];IF([@color]="";"*";[@color]))
该方法不起作用,因为它只接受非空行,如上所示。我想要此公式的变体,为此,我只需要知道要替换什么"*"
才能使所有行都符合条件。
答案1
这是另一种解决方案SUMPRODUCT
,它仍然比更复杂SUMIFS
,但我觉得这是一个进步:
=SUMPRODUCT(inv[qty]*(inv[prd]=F2)*IF(ISERROR(SEARCH(G2,"#"&inv[col]&"#")),0,1))
这是一个数组公式,因此输入后需要按 CTRL + SHIFT + ENTER。
笔记:
- 默认情况下,搜索颜色会查找所有包含条件的条目(例如“黄色”条件也会与“黄红色”匹配)
- 可以通过使用
#
术语来查找确切的术语(例如#yellow#
) - 空条件将匹配所有内容,用于
##
仅搜索空值
当然,配方可以进行微调。
答案2
解决此问题的一种方法可能是SUMPRODUCT
:
=SUMPRODUCT(Table1[Qty],--(Table1[prd]="Apple"),((--(Table1[col]=""))+(--(Table1[col]<>""))))
[col]
我们将所有等于""
和不等于的实例相加,这样就计算出了所有的苹果,无论颜色如何
答案3
下面显示的方法将获得表中所示颜色的水果和无色的水果的总和。
怎么运行的:
- 表范围是
A1:C12
。 - 摘要数据范围是
A15:B20
。 在单元格中输入此公式
C15
并向下填充以获得和, 使用TABLE 的参数。=IF(B15="", SUMIFS(FruitTAB[Qty],FruitTAB[Fruit Name],A15,FruitTAB[Color],""),SUMIFS(FruitTAB[Qty],FruitTAB[Fruit Name],A15,FruitTAB[Color],B15))
如果您的数据是:您可以使用此公式进入SUM
单元格D15
并填写:not a TABLE
=IF(B15="", SUMIFS($C$2:$C$12,$A$2:$A$12,A15,$B$2:$B$12,""),SUMIFS($C$2:$C$12,$A$2:$A$12,A15,$B$2:$B$12,B15))
注意:
- 水果标签,是表名。
为了社区的利益,我建议在非表格范围中使用公式
D15
。,您和读者可以跳过它。根据需要调整公式中的单元格引用。