多列求和差异在一个单一公式中?

huangapple go评论59阅读模式
英文:

Multiple columns sum difference in a single formula?

问题

在Excel中,我正在制作一张类似时间卡的电子表格,其中列A是费用代码,列B是上班时间,列C是下班时间。

然后,我想制作一个表格,其中:

第1列包含各种劳工代码(我使用了唯一筛选器)
第2列包含每个代码下工作的小时总和。

所以,结果应该如下所示:

多列求和差异在一个单一公式中?

我使用的B13公式是:

=UNIQUE(FILTER(A3:A10,A3:A10<>""))

C13:C16等可以使用“HH:MM”或小数小时表示。对我来说无所谓。

我想做到这一点,避免使用辅助列和VBA。

谢谢你的帮助!

我尝试了许多组合的SUM、SUMIF、MATCH、FILTER,但都没有成功。

英文:

Working a spreadsheet timecard of sorts in excel where i have column A is the charge code, column B is clock-in time, and column C is clock out time.

I'd like to then produce a table where:

Column 1 has the various labor codes (i used unique filter)
Column 2 has the sum of the hours worked under each code.

So, the result should look like this:

多列求和差异在一个单一公式中?

B13 formula i'm using is:

=UNIQUE(FILTER(A3:A10,A3:A10&lt;&gt;&quot;&quot;))

C13:C16..etc can be in either HH:MM or decimal hour notation. doesn't matter to me.

I'd like to do this avoiding helper columns and VBA

Thank you for your help!

I've tried numerous mixtures of sum, sumif, match, filter and it isnt working out

答案1

得分: 1

你可以尝试以下方法使用 MAP( )

多列求和差异在一个单一公式中?


• 在单元格 B10 中使用的公式

=LET(
  _code,A3:A8,
  _timeIn,B3:B8,
  _timeOut,C3:C8,
  _uCode,UNIQUE(_code),
  VSTACK({"CODE","小时"},HSTACK(_uCode,
  MAP(_uCode,LAMBDA(x,SUM(--(_code=x)*(_timeOut-_timeIn))))))
)

或者,不使用任何 LAMBDA( ) 辅助函数:

多列求和差异在一个单一公式中?


• 在单元格 B10 中使用的公式

=LET(
 _code,A3:A8,
 _timeIn,B3:B8,
 _timeOut,C3:C8,
 _uCode,UNIQUE(_code),
 VSTACK({"CODE","小时"},
 HSTACK(_uCode,
 MMULT(N(_uCode=TOROW(_code)),(_timeOut-_timeIn)))))

或者,更简单的方法是使用 MMULT( )UNIQUE( )HSTACK( )

多列求和差异在一个单一公式中?


• 在单元格 B10 中使用的公式

=VSTACK({"CODE","小时"},
 UNIQUE(HSTACK(A3:A8,
 MMULT(N(A3:A8=TOROW(A3:A8)),(C3:C8-B3:B8)))))

注意:Hours 列的单元格设置格式为 [h]:mm;@

英文:

You could try something along the lines using <kbd>MAP( )</kbd>

多列求和差异在一个单一公式中?


• Formula used in cell B10

=LET(
  _code,A3:A8,
  _timeIn,B3:B8,
  _timeOut,C3:C8,
  _uCode,UNIQUE(_code),
  VSTACK({&quot;CODE&quot;,&quot;Hours&quot;},HSTACK(_uCode,
  MAP(_uCode,LAMBDA(x,SUM(--(_code=x)*(_timeOut-_timeIn))))))
)

Or, without using any <kbd>LAMBDA( )</kbd> helper functions

多列求和差异在一个单一公式中?


• Formula used in cell B10

=LET(
 _code,A3:A8,
 _timeIn,B3:B8,
 _timeOut,C3:C8,
 _uCode,UNIQUE(_code),
 VSTACK({&quot;CODE&quot;,&quot;Hours&quot;},
 HSTACK(_uCode,
 MMULT(N(_uCode=TOROW(_code)),(_timeOut-_timeIn)))))

Or, more easier way using <kbd>MMULT( )</kbd> <kbd>UNIQUE( )</kbd> <kbd>HSTACK( )</kbd>

多列求和差异在一个单一公式中?


• Formula used in cell B10

=VSTACK({&quot;CODE&quot;,&quot;Hours&quot;},
 UNIQUE(HSTACK(A3:A8,
 MMULT(N(A3:A8=TOROW(A3:A8)),(C3:C8-B3:B8)))))

Note: Format the cells for Hours Column as [h]:mm;@


huangapple
  • 本文由 发表于 2023年6月15日 19:25:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/76481974.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定