我有一个SUMIFS
,它可以处理下拉列表和复选框中包含的值。下拉列表中的值用作AND标准(即,求和一列,条件是下拉列表中该值与下拉列表中那个值(,但当我想将值包括在复选框中时,事情会变得棘手,因为复选框是基于用户输入的动态值。从本质上讲,复选框中的值需要用作OR标准(即,如果选择框1,则基于该值求和一列;如果选择框2,则基于此值求和该列,也以下拉列表中的值为条件(。
在线示例显示,您可以通过以下操作实现OR标准的sumifs:
{=SUM(SUMIFS(sumrange,
criteriarange0,"x",
criteriarange1,{"1","2","3"},
criteriarange2,{"A";"B"}
))}
其中,"x"
是下拉列表中的值,
如果勾选了三个相应的复选框,则{"1","2","3"}
是结果值,1,2,3、A、B都是布尔值,因为如果不勾选复选框,它们可以从值更改为0(因此存在组合矩阵(。
我的问题是,如何处理3个或更多充当or条件的多个数组,这些数组在公式中被引用为单元格引用?类似这样的东西:
{=SUM(SUMIFS(sumrange,
criteriarange0,"x",
criteriarange1,value_range1,
criteriarange2,value_range2,
criteriarange3,value_range3
))}
我发现了一些涉及SUMPRODUCT
和ISNUMBER(MATCH()
公式的东西,但无法使其发挥作用。此外,该表为500000行x 100列,因此公式需要高效。
以下是我目前拥有的带有注释的粗略公式:
={SUM(
SUMIFS(sumrange,$A:$A, $A1, $B:$B, $B1, $C:$C, $C1...,
求出一个以单元格$A1、$B1、$C1中的值为条件的范围。这些是AND标准。
$E:$E,$AA10:$AC10,
'包括$AA10:$AC10中的动态值,其值为:5年、10年、15年。这些是OR标准。
$F:$F,{$AA11;$AC11},
还包括不同单元格范围的这些动态值:"5天、10天、15天"。这些是OR标准。
$G:$G,{$AA12;$AJ12})
)}
还包括这些多个动态值,这些值也是OR标准:"5个苹果,10个梨,15个香蕉"。
可以选择动态值的任何组合。
您可以将表示最后一个条件的两个单独的SUMIFS产生的总和相加。
=SUM(SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, "a")),
SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, "b")))
最后两个条件不能与第二个条件在同一布局中使用。这会在数组的循环计算过程中产生混淆和误判。但是,如果您可以将最后一个标准视为处于不同的布局中作为第二个标准,则计算周期应该会产生正确的结果。TRANSPORTSE将更改最后一个条件数组的"方向",从而更改其计算方式。
=SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, TRANSPOSE({"a","b"})))
这产生了与第一个公式相同的结果。
要添加第三个基于OR的标准数组,您需要将这两种方法结合起来。
=SUM(SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, TRANSPOSE({"a","b"}), E:E, "m")),
SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, TRANSPOSE({"a","b"}), E:E, "n")),
SUM(SUMIFS(A:A, B:B, "x", C:C, {1,2,3}, D:D, TRANSPOSE({"a","b"}), E:E, "o")))
样本数据:
nbsp nbsp A列用随机数填充
nbsp B列填充=CHAR(RANDBWEEN(87,89((
nbsp nbsp C列填充=RANDBWEEN(0,5(
nbsp nbsp D列填充=CHAR(RANDBWEEN(65,67((