excel公式取决于不同列中的动态值

Question

我试图创建一个excel公式使用SUM和SUMIF，但无法找到如何。

我有第一列(A)，这是一项工作的总时间，然后是每一行每天在该任务中花费的时间( B、C、.栏)。

对于每一天( B、C、.栏)，公式只返回A列中与当天已完成的任务有关的值之和:一行内所有单元格的总和等于或超过分配任务的时间。

• 一个12小时任务的示例：

    A   B  C  D  E    
    12  4  6  2  0

使用公式：

    A   B  C  D  E
    12  4  6  2  0    
    0   0  0  12 0

其中12显示在D栏中，因为4+6+2=12(A栏)

• 第二个例子(3个任务)：

    A   B  C  D  E    
    10  9  0  1  0    
    21  8  8  5  0    
    5   0  0  3  2 

使用公式：

    A   B  C  D  E    
    10  9  0  1  0    
    21  8  8  5  0
    5   0  0  3  2
    0   0  0  31 5

其中：

31( D) =10(任务1在当天完成)+21(任务2也在当天完成)

5(日E) =任务3当天完成

尝试了这个公式(用于第二天)：

 SUMIF(B1:B3,">=A1:A3",A1:A3)

(如果行p到列B中的单元格(在本例中仅为B)中的单元格比迭代的单元格为>=，则这些值在A列中之和。

对于C栏，应该是，

SUMIF(C1:C3 + B1:B3,">=A1:A3",A1:A3)

上面的例子没有奏效(第一次返回零，第二次是无效公式)，有什么想法吗？

谢谢。

Answer 1

用户ServerS给出的公式工作得很好：

Col B：

=IF(SUM(B2)=A2,A2,0)+IF(SUM(B3)=A3,A3,0)+IF(SUM(B4)=A4,A4,0)+IF(SUM(B5)=A5,A5,0)

C中校：

=IF(SUM(B2:C2)=A2,A2,0)+IF(SUM(B3:C3)=A3,A3,0)+IF(SUM(B4:C4)=A4,A4,0)+IF(SUM(B5:C5)=A5,A5,0)

Col

=IF(SUM(B2:D2)=A2,A2,0)+IF(SUM(B3:D3)=A3,A3,0)+IF(SUM(B4:D4)=A4,A4,0)+IF(SUM(B5:D5)=A5,A5,0)

但是，有两个不便之处:如果添加了新行，则需要对其进行调整，并包含另一个IF()。如果不可能将公式传播到相邻单元格，则最好有一个泛型和，因为它会将公式的一部分更改为"=A2，A2,0“，改为"=A3，A3,0”，需要保持不变。

如果可能的话，任何其他改进这一点的想法都会受到赞赏。

Answer 2

您可以避免在You产品中使用IF。此方法允许用于插入所需的任何行。确保范围是正确的(例如A2:A5，最后一行使用5)。我会这么做：

在B栏：

=SOMMEPROD(($A$2:$A$5)*($A$2:$A$5=(B2:B5)))

在C栏：

=SUMPRODUCT(($A$2:$A$5)*($A$2:$A$5=(B2:B5+C2:C5)))-B6

在D栏中

=SUMPRODUCT(($A$2:$A$5)*($A$2:$A$5=(B2:B5+C2:C5+D2:D5)))-C6-B6

E栏中

=SUMPRODUCT(($A$2:$A$5)*($A$2:$A$5=(B2:B5+C2:C5+D2:D5+E2:E5)))-D6-C6-B6

​

​