☰

Excel 公式用于对某一列中的值求和，直到达到指定的值，但只对相邻列中具有指定值的数字求和

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 公式用于对某一列中的值求和，直到达到指定的值，但只对相邻列中具有指定值的数字求和

我目前有一个数据集，其中有 2 个工作表，一个工作表包含一个名称列表，旁边有一个较大的值。第二个工作表上有一个名称列表，旁边有一个值，但这些值要小得多。

第二页上的值的一些组合等于第一页上的值的总和，并且我希望在第一页的每一行旁边写一个公式，该公式将查找第二张表上的名称并将第二张表上的所有值相加（假设它们旁边有正确的名称）以得出第一张表上名称旁边的值。

我在下面提供了一些示例数据，希望可以减少混淆；

示例数据

希望这是有道理的，但很高兴澄清任何事情，本质上是在 D3,4,5,6 中寻找一个公式，将 H3:H18 中的值相加，前提是它们旁边有相应的名称，但只能达到每行 C 列中的值，例如，将 H3、H4 和 H5 加到等于 D3，但不包括 H6，因为它超过了总数。

答案1

如果我理解正确，并且在阅读了 OP 中的评论之后，以下公式应该足以实现所需的输出。

下面显示的公式与名为的辅助函数一起使用MS365并使用它，如果需要使用一个动态数组公式返回整个输出，那么可以使用。LAMBDA()SCAN()MAKEARRAY()

在此处输入图片描述

• 单元格中使用的公式D3

=LET(
     α, FILTER($H$3:$H$18,(B3=$G$3:$G$18)),
     δ, TEXTJOIN(", ",,FILTER(α,SCAN(0,α,LAMBDA(x,y,x+y))<=C3)),
     HSTACK(δ,SUM(--TEXTSPLIT(δ,", "))))
  • αSheet 2-->通过匹配NameinSheet 1和Namein返回值范围Sheet 2
  • δ--> 通过LAMBDA()使用函数进行迭代来返回一个分隔列表，以根据每个名称SCAN()获取运行总数，并使用提取小于等于即的那些。αFILTER()C3ValueSheet 1
  • 最后，使用TEXTSPLIT()来拆分上述变量并求和，以及合并在一起形成一个1x2数组来HSTACK()返回输出。

上面的公式需要向下填充，而下面的公式将会溢出整个数组。

在此处输入图片描述

• 单元格中使用的公式D3

=MAKEARRAY(ROWS(B3:C6),2, LAMBDA(r,c,
 INDEX(LET(α, FILTER(H3:H18,(INDEX(B3:B6,r)=G3:G18)),
           δ, TEXTJOIN(", ",,FILTER(α,SCAN(0,α,LAMBDA(x,y,x+y))<=INDEX(C3:C6,r))),
           HSTACK(δ,SUM(--TEXTSPLIT(δ,", ")))),c)))

相关内容