在 Excel 中的某一列中添加值直到达到某个点并重复

在 Excel 中的某一列中添加值直到达到某个点并重复

我有几千个与提供的图片中显示的值类似的值。

我试图对 K 列中的时间值求和,但前提是它们在 M 列中具有“标志”的值,并且只有连续的值。

例如,单元格 M467-M477 均被标记,加在一起等于 ~0.017 小时。

然后忽略单元格 M478-M480,然后将单元格 M481-M483 相加等于 ~0.0036 小时,依此类推。 有什么自动化 Excel 公式可以做到这一点?

电子表格值的示例

![在此处输入图片描述

答案1

带有辅助列的选项。

在此处输入图片描述

Q2 中的公式是

=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))

在 R2 中

=IF(Q3="",Q2,"")

抄下来。

答案2

如果希望总和位于每组的第一行,则可以使用以下公式:

=IF(AND(M2="Flag",M1<>"Flag"),SUM(K2:INDEX(K2:INDEX(K:K,MATCH(1E+99,K:K)+1),MATCH(TRUE,INDEX(M2:INDEX(M:M,MATCH(1E+99,K:K)+1)="",),0)-1)),"")

具体来说:

两者INDEX(K:K,MATCH(1E+99,K:K)+1)找到 K 列中最后一个包含数字的单元格,并将其设置为引用范围的范围。这样,公式就是动态的,随着新行的添加,公式会自动调整。

然后在 Sum 中,我们从同一行的单元格开始,K2 是相对的,随着公式向下移动,引用也向下移动。范围内的结束单元格使用另一个 INDEX/MATCH 进行设置。

这次,我们要查找公式所在行中的第一个单元格,该单元格在 M 列中为空。我们将该行 -1 设置为 SUM() 的最后一行。

IF() 仅使我们想执行的任何求和运算和其余运算都成为空字符串。

在此处输入图片描述


如果您希望它位于组的底部,那么使用以下数组公式:

=IF(AND(M2="Flag",M3=""),SUM(INDEX($K$1:K2,IFERROR(MATCH(2,IF($M$1:M2="",1)),1)+1):K2),"")

作为数组公式,退出编辑模式时必须使用 Ctrl-Shift-Enter 进行确认。如果操作正确,Excel 会将{}公式放在周围。

这次,我们寻找当前行上方 M 中的最后一个空白处来设置第一个。

如果正下方的行有“标志”,则 IFERROR 将处理标题行。

在此处输入图片描述

相关内容