我试图找出一个公式来计算每个工人每月和每个项目的工作时间总和。我很确定XLOOKUP、AND/IF和SUBTotal可能很有用,但我不确定如何将它们组合在一起。以下是确切的数据。每个雇员的查阅值(A列)、每个雇员的每个项目的查阅值(B列)、每个时间表期间的查阅值(C列)格式:2022/06/03,然后计算每个员工每月每个项目的总工时(D列)提前感谢!我尝试了XLOOKUP公式和公式的日期SUBTOTAL公式的小时。
bnl4lu3b1#
Sumifs解决了您的问题
=SUMIFS($D$2:$D$12,$A$2:$A$12,$A15,$B$2:$B$12,$B15,$C$2:$C$12,">="&DATE(2022,7,1);$C$2:$C$12,"<="&DATE(2022,7,31))
iq0todco2#
也许您也可以尝试使用**SUMPRODUCT()**
·单元格C20中使用的公式
C20
=SUMPRODUCT((A20=$A$2:$A$13)*(B20=$B$2:$B$13)*(TEXT($C$2:$C$13,"mm/yyyy")="06/2022")*($D$2:$D$13))
注意:使用如下单元格引用使其动态化
=SUMPRODUCT(($A20=$A$2:$A$13)*($B20=$B$2:$B$13)*(TEXT($C$2:$C$13,"mm/yyyy")=TEXT(C$18,"mm/yyyy"))*($D$2:$D$13))
7vhp5slm3#
此方法使用O365函数来产生数组解决方案。参照日期会在储存格H2中产生为1x3数组,并格式化为日期,如下所示:mmm-yy(与计算无关,仅用于可视化):
H2
1x3
mmm-yy
=EDATE(DATE(2022,6,1),SEQUENCE(1,3,0))
然后在H3中放入以下公式并向右展开,无需向下展开:
H3
=LET(set, $A$2:$D$13, lkup, $F$3:$G$8, SOM, H2, projects, INDEX(set,,1), employes, INDEX(set,,2),timesheets, INDEX(set,,3), hours, INDEX(set,,4), MAP(INDEX(lkup,,1), INDEX(lkup,,2), LAMBDA(prj,empl, LET(EOM, EOMONTH(SOM,0), SUM(FILTER(hours, (projects =prj) * (employes = empl) * (timesheets >= SOM) * (timesheets <= EOM),0)) ))) )
其中LET用于**阅读 * 和排版。
LET
注意:您也可以使用下列其中一个选项来取代SUM/FILTER:
SUM/FILTER
SUMPRODUCT(hours, (projects =prj) * (employes = empl) * (timesheets >= SOM) * (timesheets <= EOM))
或
SUMPRODUCT(hours, N(projects =prj), N(employes = empl), N(timesheets >= SOM), N(timesheets <= EOM))
或者仅仅是SUM:
SUM
SUM(hours * (projects =prj) * (employes = empl) * (timesheets >= SOM) * (timesheets <= EOM))
这是偏好的问题。输出如下:
如果你想知道是否有可能避免向右扩展公式,这样我们就可以在一个公式中得到整个结果,是的,这是可能的。
备选方案1:使用DROP/REDUCE/HSTACK:
DROP/REDUCE/HSTACK
=LET(set, A2:D13, lkup, F3:G8, SOMs, H2:J2, projects, INDEX(set,,1), employes, INDEX(set,,2),timesheets, INDEX(set,,3), hours, INDEX(set,,4), DROP(REDUCE(0,SOMs, LAMBDA(acc, SOM, HSTACK(acc, LET(EOM, EOMONTH(SOM,0), MAP(INDEX(lkup,,1), INDEX(lkup,,2), LAMBDA(prj, empl, SUM(hours * (projects =prj) * (employes = empl) * (timesheets >= SOM) * (timesheets <= EOM)) )))))),,1) )
备选方案2:使用TEXTSPLIT/TEXTJOIN:
TEXTSPLIT/TEXTJOIN
=LET(set, A2:D13, lkup, F3:G8, SOMs, H2:J2, projects, INDEX(set,,1), employes, INDEX(set,,2),timesheets, INDEX(set,,3), hours, INDEX(set,,4), byC, BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0), mp, MAP(INDEX(lkup,,1), INDEX(lkup,,2), LAMBDA(prj, empl, SUM(hours * (projects =prj) * (employes = empl) * (timesheets >= SOM) * (timesheets <= EOM)) )), TEXTJOIN(",",, mp) ))), TRANSPOSE(1*TEXTSPLIT(TEXTJOIN(";",, byC), ",", ";")) )
注:在这两个备选项下,不需要在范围中使用$-符号。
$
3条答案
按热度按时间bnl4lu3b1#
Sumifs解决了您的问题
iq0todco2#
也许您也可以尝试使用**SUMPRODUCT()**
·单元格
C20
中使用的公式注意:使用如下单元格引用使其动态化
·单元格
C20
中使用的公式7vhp5slm3#
此方法使用O365函数来产生数组解决方案。参照日期会在储存格
H2
中产生为1x3
数组,并格式化为日期,如下所示:mmm-yy
(与计算无关,仅用于可视化):然后在
H3
中放入以下公式并向右展开,无需向下展开:其中
LET
用于**阅读 * 和排版。注意:您也可以使用下列其中一个选项来取代
SUM/FILTER
:或
或者仅仅是
SUM
:这是偏好的问题。
输出如下:
如果你想知道是否有可能避免向右扩展公式,这样我们就可以在一个公式中得到整个结果,是的,这是可能的。
备选方案1:使用
DROP/REDUCE/HSTACK
:备选方案2:使用
TEXTSPLIT/TEXTJOIN
:注:在这两个备选项下,不需要在范围中使用
$
-符号。