如果公式及其范围求和的Excel问题

Question

我的工作表中有这个设置:

  A        B     C           D          E        F
1 11-apr   1pm   2pm         START      END      TOTAL
2          2pm   3pm         11-apr     14-apr   8,50 h
3          6pm   7pm         15-apr     25-apr   ....
4          7pm   8pm
5 14-apr   10am  11am
6          12am  12:30am
7          2pm   3pm
8          3pm   5pm
9 15-apr   1pm   3pm
10         6pm   7pm
...

我需要执行以下操作:

以 D2 和 E2 为起点，计算 4 月 11 日至 4 月 14 日之间每天花费的小时数，并将总数写在 F2 中。
我无法修改我的工作表，并且我不想创建隐藏单元格来引用值，因为我不想弄乱我的 Excel 工作表。

我正在寻找的是，因为我有一些编程技能，所以在 A:A 单元格之间进行一个 for 循环，并从“11-apr”(在本例中为 A1)所在的行开始，直到我找到“4 月 14 日”并在第二天单元格之前停止(所以在 A8 处)。我也更喜欢不制作宏，因为它需要在 D 和 E 列方面是动态的。
A中的日期是随机的，所以没有办法提供第二天的到来。

我问你这是否可以在单元格 F2 中的一个公式中实现。

我所做的是寻找引用。 “11-apr”和“14-apr”的行，然后在数组公式中找到哪些单元格不为空(如 INT(A:A<>"") )并使用返回的数组我想从“11-apr”行直到next-non-empty-cell-after-"14-apr"-row 减 1(直到 "15-apr" row - 1 )，即数组中的下一个非 0(非 FALSE)值 AFTER 行 "14-apr""减 1。我将很多公式与 MATCH、INDEX、LARGE 结合起来获得第 n 个元素但没有用。

希望我足够清楚，在此先感谢!

附言我在 Excel for Mac 上，以防万一知道!

Answer 1

这些非 volatile 公式数组基于 @OverflowStacker 方法来识别 Target Range ，他们使用 INDEX而不是 INDIRECT :

同时按 CTRL + SHIFT + ENTER 输入公式数组，您将看到公式包裹在 { 之间和 }如果输入正确。

这篇文章验证了提供的日期:

=IF( Start > End, "!Err",

这是 FormulaArray用于根据 Start 获得小时的加法和 End提供时间:

= SUM(
INDEX( Column.End , MATCH( MIN( IF( Range.Dates >= Start, Range.Dates ) ), Column.Dates , 0 ) ) :
INDEX( Column.End , MATCH( MIN( IF( Range.Dates > End, Range.Dates ) ), Column.Dates , 0 ) - 1 ),
-INDEX( Column.Start , MATCH( MIN( IF( Range.Dates >= Start, Range.Dates ) ), Column.Dates , 0 ) ) :
INDEX( Column.Start , MATCH( MIN( IF( Range.Dates > End, Range.Dates ) ), Column.Dates , 0 ) - 1 ) ) )

Start , End和 Total时间可以表示为 TimeSerial或作为 Double (例如 12:30 AM 或 0.5 分别)

下表显示了根据所需时间格式使用的公式:



应用公式:

表 1 显示的次数为 TimeSerial

一个。总计为 TimeSerial : 输入 ArrayFormula在 I7然后向下复制(单元格编号格式为 [hh]:mm )

= IF( $G7 > $H7, "!Err", SUM(
  INDEX($E:$E, MATCH( MIN( IF( $C$7:$C$27 >= $G7, $C$7:$C$27 ) ),$C:$C, 0 ) ) :
  INDEX($E:$E, MATCH( MIN( IF( $C$7:$C$27 > $H7, $C$7:$C$27 ) ),$C:$C, 0 ) - 1 ),
- INDEX($D:$D, MATCH( MIN( IF( $C$7:$C$27 >= $G7, $C$7:$C$27 ) ),$C:$C, 0 ) ) :
  INDEX($D:$D, MATCH( MIN( IF( $C$7:$C$27 > $H7, $C$7:$C$27 ) ),$C:$C, 0 ) - 1 ) ) )  

湾。总计为 Double : 输入 ArrayFormula在 J7然后向下复制(单元格编号格式为 0.?? "Hrs." )

= IF( $G7 > $H7, "!Err", 24 * SUM(
  INDEX($E:$E, MATCH( MIN( IF( $C$7:$C$27 >= $G7, $C$7:$C$27 ) ),$C:$C, 0 ) ) :
  INDEX($E:$E, MATCH( MIN( IF( $C$7:$C$27 > $H7, $C$7:$C$27 ) ),$C:$C, 0 ) - 1 ),
- INDEX($D:$D, MATCH( MIN( IF( $C$7:$C$27 >= $G7, $C$7:$C$27 ) ),$C:$C, 0 ) ) :
  INDEX($D:$D, MATCH( MIN( IF( $C$7:$C$27 > $H7, $C$7:$C$27 ) ),$C:$C, 0 ) - 1 ) ) )  

表 1 显示的次数为 Double

一个。总计为 Double : 输入 ArrayFormula在 S7然后向下复制(单元格编号格式为 0.?? "Hrs." )

= IF( $Q7 > $R7, "!Err", SUM(
  INDEX($O:$O, MATCH( MIN( IF( $M$7:$M$27 >= $Q7, $M$7:$M$27 ) ),$M:$M, 0 ) ) :
  INDEX($O:$O, MATCH( MIN( IF( $M$7:$M$27 > $R7, $M$7:$M$27 ) ),$M:$M, 0 ) - 1 ),
- INDEX($N:$N, MATCH( MIN( IF( $M$7:$M$27 >= $Q7, $M$7:$M$27 ) ),$M:$M, 0 ) ) :
  INDEX($N:$N, MATCH( MIN( IF( $M$7:$M$27 > $R7, $M$7:$M$27 ) ),$M:$M, 0 ) - 1 ) ) )  

湾。总计为 TimeSerial : 输入 ArrayFormula在 T7然后向下复制(单元格编号格式为 [hh]:mm )

= IF( $Q7 > $R7, "!Err", 1/24 * SUM(
  INDEX($O:$O, MATCH( MIN( IF( $M$7:$M$27 >= $Q7, $M$7:$M$27 ) ),$M:$M, 0 ) ) :
  INDEX($O:$O, MATCH( MIN( IF( $M$7:$M$27 > $R7, $M$7:$M$27 ) ),$M:$M, 0 ) - 1 ),
- INDEX($N:$N, MATCH( MIN( IF( $M$7:$M$27 >= $Q7, $M$7:$M$27 ) ),$M:$M, 0 ) ) :
  INDEX($N:$N, MATCH( MIN( IF( $M$7:$M$27 > $R7, $M$7:$M$27 ) ),$M:$M, 0 ) - 1 ) ) )