如何用excel函数实现多条件计算

Question

如图，如何实现在统计区间内人员工时。求大神帮帮忙
各位大大注意下，条件不光有人员，还有统计期间（如：7月份）
图例只是为了方便描述做的表，实际应用中人数 日期 上班类型都很多

Answer 1

条件很多哦！

B11输入

=SUMPRODUCT((MONTH(A$2:A$5)=--LEFT(B$8,LEN(B$8)-1))*(OFFSET($A$2,,MATCH(A11,B$1:D$1,),4)=F$2))*G$2+SUMPRODUCT((MONTH(A$2:A$5)=--LEFT(B$8,LEN(B$8)-1))*(OFFSET($A$2,,MATCH(A11,B$1:D$1,),4)=F$3))*G$3+SUMPRODUCT((MONTH(A$2:A$5)=--LEFT(B$8,LEN(B$8)-1))*(OFFSET($A$2,,MATCH(A11,B$1:D$1,),4)=F$4))*G$4+SUMPRODUCT((MONTH(A$2:A$5)=--LEFT(B$8,LEN(B$8)-1))*(OFFSET($A$2,,MATCH(A11,B$1:D$1,),4)=F$5))*G$5

回车并向下填充。

追问

弱弱的问下
这个有些看不懂OFFSET($A$2,,MATCH(A11,B$1:D$1,),4)

弱弱的问下
这个有些看不懂OFFSET($A$2,,MATCH(A11,B$1:D$1,),4)

追答

OFFSET($A$2,,MATCH(A11,B$1:D$1,),4)——偏移函数，语法：OFFSET(引用区域,偏上下,偏左右,范围高,范围宽)。
MATCH(A11,B$1:D$1,)——匹配函数，语法：MATCH(查找条件,查找区域,参数)。
这是偏移函数的第3个参数“偏左右”。你要统计是在A11起的姓名，而这些姓名的数据是在第一行B1~D1（或许更多），且下面A11起的姓名排列跟上面B1~D1的顺序不一定都相同，故用匹配函数MATCH()，以A11作为查找条件，查找在B1~D1区域里面是第几个，以便给偏移函数做左右偏移。
如：MATCH(A11,B$1:D$1,)=1，即从$A$2往右偏移1（即B2）。
OFFSET()末尾参数4，为你现在有日期的是4行，即引用范围为B2、B3、B4、B5共4行的区域。
其实，可以用计数函数COUNT(A:A)，以动态统计A列有数字的单元格个数（即有日期的行数）。改为：
OFFSET($A$2,,MATCH(A11,B$1:D$1,),COUNT(A:A))
或
OFFSET($A$2,,MATCH(A11,B$1:D$1,),COUNT(A$2:A$100))
但你提供的抓图范围空间太小（A8已经是别的内容了，并且下面又有别的内容，如果有数字，就会被统计在内），故我就干脆写“4”啦。

Answer 2

好热闹啊，我也凑个：B11

=SUMPRODUCT((MONTH(A$2:A$5)=--LEFT(B$8,LEN(B$8)-1))*SUMIF(F:F,OFFSET($A$2,,MATCH(A11,B$1:D$1,),4),G:G))

下拉填充

下列数据请自行调整

A2:A5：日期数据

Offset函数中的4，总数据行

B1:D1：人员

F:F,G:G 班次

Answer 3

在B11单元格中输入公式
=SUM(LOOKUP(INDIRECT("R2C"&MATCH(A11,$1:$1,0)&":R5C"&MATCH(A11,$1:$1,0),FALSE),$F$1:$F$5,$G$1:$G$5))
然后按下enter+shift+ctrl把公式设为数组公式结束公式编辑，最后下拉填充公式。

Answer 4

在H列中填h2=if(B2="夜班",10,if(B2="上午班",5,if(b2="下午班",4,if(b3="全天班",8))))然后下拉李，赵的依次改成c，d让后再工时哪里输工时=sum（h2,h4）我只想的到这种办法

Answer 5

在B11单元格输入以下公式，然后向下填充公式
=COUNTIF(OFFSET($B$1,1,ROW(A1)-1,2),F2)*G2+COUNTIF(OFFSET($B$1,1,ROW(A1)-1,2),F3)*G3+COUNTIF(OFFSET($B$1,1,ROW(A1)-1,2),F4)*G4+COUNTIF(OFFSET($B$1,1,ROW(A1)-1,2),F5)*G5

追问

谢谢，但是时间因素没加进去吧 如果统计区间变成8月呢

追答

对汇总区的格式稍作调整后，在B11单元格输入以下公式，然后向右填充公式

=SUMPRODUCT((B2:B4=$F2)*(MONTH($A2:$A4)=--LEFT($B8))*1)*$G2+SUMPRODUCT((B2:B4=$F3)*(MONTH($A2:$A4)=--LEFT($B8))*1)*$G3+SUMPRODUCT((B2:B4=$F4)*(MONTH($A2:$A4)=--LEFT($B8))*1)*$G4+SUMPRODUCT((B2:B4=$F5)*(MONTH($A2:$A4)=--LEFT($B8))*1)*$G5

 

详见附图附件