EXCEL中带日期和求和公式Xlookup

vatpfxk5  于 2022-11-18  发布在  其他
关注(0)|答案(3)|浏览(221)

我试图找出一个公式来计算每个工人每月和每个项目的工作时间总和。我很确定XLOOKUP、AND/IF和SUBTotal可能很有用,但我不确定如何将它们组合在一起。以下是确切的数据。
每个雇员的查阅值(A列)、每个雇员的每个项目的查阅值(B列)、每个时间表期间的查阅值(C列)格式:2022/06/03,然后计算每个员工每月每个项目的总工时(D列)
提前感谢!
我尝试了XLOOKUP公式和公式的日期SUBTOTAL公式的小时。

bnl4lu3b

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))

iq0todco

iq0todco2#

也许您也可以尝试使用**SUMPRODUCT()**

·单元格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))

注意:使用如下单元格引用使其动态化

·单元格C20中使用的公式

=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))
7vhp5slm

7vhp5slm3#

此方法使用O365函数来产生数组解决方案。参照日期会在储存格H2中产生为1x3数组,并格式化为日期,如下所示:mmm-yy(与计算无关,仅用于可视化):

=EDATE(DATE(2022,6,1),SEQUENCE(1,3,0))

然后在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用于**阅读 * 和排版。

注意:您也可以使用下列其中一个选项来取代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(hours * (projects =prj) * (employes = empl) * (timesheets >= SOM) 
  * (timesheets <= EOM))

这是偏好的问题。
输出如下:

如果你想知道是否有可能避免向右扩展公式,这样我们就可以在一个公式中得到整个结果,是的,这是可能的。

备选方案1:使用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

=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), ",", ";"))
)

:在这两个备选项下,不需要在范围中使用$-符号。

相关问题