Excel-多条件的OR运算(SUMIFS,SUMPRODUCT,WEEKDAY)

爱必应

在多条件的运算中,有些要执行逻辑 AND 运算,有些要执行逻辑 OR 运算。以下的例子中,是要求取符合多个被选取的星期几者的小计。(参考下图)

Excel-多条件的OR运算(SUMIFS,SUMPRODUCT,WEEKDAY)

 

【公式设计与解析】

选取A栏至D栏中的资料,按 Ctrl+Shift+F3 键,勾选「顶端列」,定义名称:人员、日期、星期、数量。

 

1. 利用 SUMIF 函数加总多个结果

单元格F5:=SUMIFS(数量,人员,F$4,星期,”星期一”)+SUMIFS(数量,人员,F$4,星期,
“星期三”)+SUMIFS(数量,人员,F$4,星期,”星期五”)

如果使用三个 SUMIF 函数,分别求取固定条件:「星期一、星期三、星期三」的结果,但是该结果没有弹性,条件更改时,要修改参数。

 

2. 利用阵列公式加总多个结果

{=SUM(IF(人员=F$4,IF(星期={“星期一”,”星期三”,”星期五”},数量),””))}

这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会自动加上「{}」。

如果利用阵列公式,可简化公式长度。其中公式「SUM(IF(…」和函数「SUMIF」的观念相同。

 

3. 利用 SUMPRODUCT 函数加总多个结果

单元格F5:=SUMPRODUCT((WEEKDAY(日期,2)=($F$2:$L$2=”V”)*COLUMN
($A:$G))*(人员=F$4)*数量)

如果利用 SUMPRODUCT 函数,则可以达到较为弹性的方式来计算每个人员选取不同星期几的小计。

条件:WEEKDAY(日期,2)=($F$2:$L$2=”V”)*COLUMN($A:$G)

WEEKDAY(日期,2):传回数字 1~7 代表星期一 ~星期日。

Excel-多条件的OR运算(SUMIFS,SUMPRODUCT,WEEKDAY)

其传回顺序,恰好对应图中的单元格F1:L1。

Excel-多条件的OR运算(SUMIFS,SUMPRODUCT,WEEKDAY)

COLUMN($A:$G):代表 1~7 的阵列。

($F$2:$L$2=”V”)*COLUMN($A:$G):在阵列中会传回 $F$2:$L$2=”V” 条件成立者对应的COUMN 数。

 

【延伸练习】

如果要将第2式改为像第3式是只计算有勾选的项目,该如何处理公式?

参考答案:

单元格F5:{=SUM(IF(人员=F$4,IF(WEEKDAY(日期,2)=($F$2:$L$2=”V”)*COLUMN
($A:$G),数量),””))}

这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会自动加上「{}」。

本文链接:,转发请注明来源!

发表评论