☰

如何根据另一列的条件对一列中的值进行求和？

tag-icon • tag-icon microsoft-excel worksheet-function
如何根据另一列的条件对一列中的值进行求和？

我记了日志，想知道是否存在一个公式，可以根据图像中第三列的值对相邻单元格中的值进行求和。第一个“真”行必须包括“假”行中的所有先前值加上“真”值本身。

在此处输入图片描述。

很长一段时间我都必须手动输入黄色值。

答案1

编辑：公式略微简化9E+307 => 2

这个公式似乎有效：

C3: =IF(AND($A3,NOT($A2)),SUM(INDEX($B:$B,1+LOOKUP(2,1/$A$2:$A2,ROW($B$2:$B2))):B3),"")

输入C3并向下填充。

在此处输入图片描述

算法

  • 当且仅当满足以下条件时才返回结果：
    • 同一行的单元格 =TRUE
    • 前一行单元格 <>TRUE
  • LOOKUP(2,1/$A$2:$A2,ROW($B$2:$B2)))TRUE将返回包含公式位置上方一行的范围中的最后一个单元格的行号
  • 添加1到起始行，以便我们跳过最后一个 TRUE 单元格
  • 使用INDEX我们返回 B 列中与当前 TRUE 和前面的 FALSE 相对应的条目
  • SUM他们

答案2

嗯，看来你的问题相当有趣。

如果您尝试从顶部开始求和，这将很简单。但在 TRUE 之间求和则相当复杂。

以下是我编写的实现该目标的公式：
=IF(ROW()=1,"",IF(AND(A2,NOT(INDIRECT(CONCAT("A",MAX(ROW()-1,1))))),SUM(INDIRECT(CONCAT("B",IF(ISNUMBER(MATCH(2,1/(A$1:INDIRECT(CONCAT("A",ROW()-1))))),MATCH(2,1/(A$1:INDIRECT(CONCAT("A",ROW()-1))))+1,1))):B2),"")) Excel 输出

所以是的...真恶心。

这可能可以通过数组公式来简化，但我个人并不喜欢它们。

此外，这不是一个非常便携的公式，因为您需要编辑列引用，包括文本和引用。所以...

这是一个更粗糙的版本，仅使用间接来使用相对单元格引用：
=IF(ROW()=1,"",IF(AND(INDIRECT(CONCAT("R",ROW(),"C",COLUMN()-2),FALSE),NOT(INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE))),SUM(INDIRECT(CONCAT("R",IF(ISNUMBER(MATCH(2,1/(INDIRECT(CONCAT("R1C",COLUMN()-2),FALSE):INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE)))),MATCH(2,1/(INDIRECT(CONCAT("R1C",COLUMN()-2),FALSE):INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE)))+1,1),"C",COLUMN()-1),FALSE):INDIRECT(CONCAT("R",ROW(),"C",COLUMN()-1),FALSE)),""))

^ 此公式可以放置在任何单元格中（无需编辑），并将根据左侧第 2 列对其左侧的列求和（如我的图片所示）。

说明

好吧，系好安全带，让我们来分解一下。

首先，这个等式中真正的主角是： match(2,1/([range]=TRUE))
这将在选定范围内从下到上进行搜索，并找到第一个 TRUE

现在，我们需要匹配函数要使用的范围。为此，我们使用indirect，它接受一个文本字符串并将其解释为引用。因此，对于每行需要查看的范围，我们说：
=A$1:indirect(concat("A",row()-1))

如果此公式在C5，它将产生参考范围：A$1:A4

现在我们可以找到最近的 TRUE：
=match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))

好的，我们有最新的 TRUE 的行号，让我们总结一下 match+1 和当前行的范围：
=sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))+1)):B1) （B1随着我们填充列，这将发生变化）

好吧，太棒了！我们有一个有效的公式。但我们只希望它在 TRUE 时输出。因此，让我们在前面加上一个 IF 语句，以便""在 A 列为 FALSE 时输出：
=if(A1=false,"",sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))):B1))

太棒了，现在如果我们测试公式，我们会发现我们有一些问题……即在第 2 行。这是因为我们的匹配函数需要范围参数，并且我们A$1:A1在第一行提供 when。所以，让我们添加另一个 IF：
=if(row()=1,if(A1,B1,""),=if(A1=false,"",sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))):B1)))

因此，现在公式可以自行处理第 1 行...但是，我们还可以看到，如果我们碰巧将其设置A1为 FALSE，则第 2 行会出现错误。这是因为函数match在其上方未找到 TRUE。因此，我们需要检查“N/A”，如果是 N/A，则用 1 替换该值（因为我们想要将其加到顶部）。这需要另一个 IF。让我们将其从公式中分离出来以单独查看：
=if(isna(match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))),1,=match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))

太棒了！现在把这些都拼凑起来，你就得到了上面的第一个公式。

注：部分内容已修改，但解释尚未更新，抱歉

对于相对引用版本，只需用间接函数替换所有对A1、、、等A2的硬引用...就好了！B2"B"

总结：

使用match(2,1/[range])和indirect检查最近的 TRUE，然后从那里对当前行求和。此外，处理恼人的边缘情况。

答案3

Excel 365

=IF(AND($B2=FALSE,$B3=TRUE),SUM(INDEX(C:C,MATCH(2,1/($B$1:$B2=$B3))+1):$C3),"")

调整（基于罗恩·罗森菲尔德的答案）

=IF(AND(NOT($B2),$B3),SUM(INDEX(C:C,MATCH(2,1/($B$1:$B2=$B3))+1):$C3),"")

Excel 2007

=IF(AND(NOT($B2),$B3),SUM(INDEX($C$2:$C3,(LOOKUP(2,1/$B$2:$B2,$C$2:$C3))):$C3),"")

如果从上一行开始查找，则可以从公式中消除 1+ 或 +1。$B$2:$B2。

分解：

将公式放在单元格 d3 中下方。

=如果（并且（$B2=FALSE，$B3=TRUE），数学“”）

检查从 FALSE 到 TRUE 的转换。是的，进行数学运算，不输出空白行。

数学= SUM（开始:$C3)

添加 FALSE 和 TRUE 的范围。范围的结束由当前行固定，但我们必须进行一些数学运算才能找到 False 数据的开始。

开始= INDEX(C:C,匹配(2,1/($B$1:$B2=$B3))+1)

神奇时刻。从这一行，找到上一个 TRUE 的行。加 1 到达 FALSE 行。在 C 列生成单元格引用。

在此处输入图片描述

MATCH(2,1/($B$1:$B2=$B3)) - 获取上一个匹配项

MATCH(2,$B$1:$B2=$B3) - 获取下一个匹配项

从Exceljet 如何查找第一个和最后一个匹配项

最后一场比赛未排序的数据

如果您想要最后一个匹配项，但数据未按查找值排序，该怎么办？换句话说，您想应用条件来查找匹配项，而您只想要符合条件的数据中最后一个项目？这实际上是 LOOKUP 函数大放异彩的情况，因为 LOOKUP 可以原生处理数组操作，而无需 Control + Shift + Enter。这意味着我们可以使用简单的逻辑表达式动态构建查找数组来定位我们想要的数据。

=LOOKUP(2,1/(item=F5),price)

相关内容