作者:乔山办公网日期:
返回目录:excel表格制作
有如下数据,需要分别对一个月的上班、请假、加班、旷工时间进行汇总:
可以使用数组公式,如:
D4单元格可以设置公式:=SUMPRODUCT((MOD(COLUMN(I4:EB4)-9,4)=0)*I4:EB4)
D5单元格可以设置公式:=SUMPRODUCT((MOD(COLUMN(J4:EC4)-10,4)=0)*J4:EC4)
其它公式可以类似设置:
上班 | 请假 |
=SUMPRODUCT((MOD(COLUMN(I4:EB4)-9,4)=0)*I4:EB4) | =SUMPRODUCT((MOD(COLUMN(J4:EC4)-10,4)=0)*J4:EC4) |
=SUMPRODUCT((MOD(COLUMN(I5:EB5)-9,4)=0)*I5:EB5) | =SUMPRODUCT((MOD(COLUMN(J5:EC5)-10,4)=0)*J5:EC5) |
=SUMPRODUCT((MOD(COLUMN(I6:EB6)-9,4)=0)*I6:EB6) | =SUMPRODUCT((MOD(COLUMN(J6:EC6)-10,4)=0)*J6:EC6) |
=SUMPRODUCT((MOD(COLUMN(I7:EB7)-9,4)=0)*I7:EB7) | =SUMPRODUCT((MOD(COLUMN(J7:EC7)-10,4)=0)*J7:EC7) |
=SUMPRODUCT((MOD(COLUMN(I8:EB8)-9,4)=0)*I8:EB8) | =SUMPRODUCT((MOD(COLUMN(J8:EC8)-10,4)=0)*J8:EC8) |
=SUMPRODUCT((MOD(COLUMN(I9:EB9)-9,4)=0)*I9:EB9) | =SUMPRODUCT((MOD(COLUMN(J9:EC9)-10,4)=0)*J9:EC9) |
=SUMPRODUCT((MOD(COLUMN(I10:EB10)-9,4)=0)*I10:EB10) | =SUMPRODUCT((MOD(COLUMN(J10:EC10)-10,4)=0)*J10:EC10) |
=SUMPRODUCT((MOD(COLUMN(I11:EB11)-9,4)=0)*I11:EB11) | =SUMPRODUCT((MOD(COLUMN(J11:EC11)-10,4)=0)*J11:EC11) |
其中的-9、-10是表示需要开始计算的列号离第一列的列数;
其中的4表示数据每隔4列需要相加。
由Column函数求出某列是第几列;
Mod函数求出除4的余数,通过逻辑等式确定是否逻辑值是真还是假;逻辑值为真是在数组中代表1,假时代表0;
Sumproduct函数可以对两个数组中按顺序对应的数组元素值相乘,得到一个乘值数组,乘值数组元素再求和。
最终的运算结果如下: