EXCEL中带有日期和和公式的Xlookup

Question

我试图找出一个公式，以找出每个工人每月工作时间和每个项目的工作时数之和。我很确定XLOOKUP和/IF以及SUBTotal可能是有用的，但我不太清楚如何将它们全部结合起来。这是准确的数据。

每个雇员的查找值(A栏)，每个雇员的项目查找值(B栏)，每个时间表期间的查找值(C栏)格式: 2022/06/03，然后将每个项目每个月的总工时之和(D栏)

提前感谢！

我尝试了XLOOKUP公式和小时的日期小计公式。

​

​

Answer 1

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

​

​

Answer 2

也许您也可以尝试使用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))

Answer 3

这种方法使用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), ",", ";"))
)

注意事项：在这两种选择下，不需要在范围内使用$-notation。