☰

根据两个表中的多个条件计算总销售额

tag-icon • tag-icon microsoft-excel worksheet-function
根据两个表中的多个条件计算总销售额

我目前正在与表格1并尝试计算总销售额[C 列]具有相同产品ID并匹配库存单位和销售单位表 2。我尝试使用 SUMIFS 公式，如下所示：

=SUMIFS(Table2!E:E, Table2!A:A, Table1!A1, Table2!C:C, Table2!D:D)

但是，它似乎没有给我想要的结果。有人能帮我解决这个问题吗？我非常感谢你的帮助。谢谢！

表 1（预期结果）：

表格1

表 2：

表 2

答案1

如果我理解正确，那么SUMIFS()正确的函数就不是能够获得所需输出的函数了，而是使用屏幕截图中所示的以下命令：

在此处输入图片描述

• 单元格中使用的公式C2并向Sheet Named Table1下填充：

=LET(
     _extract, FILTER(Table2!C$2:E$19,(Table2!A$2:A$19=Table1!A2)*(Table1!B2=Table2!B$2:B$19),""),
     SUM(--(INDEX(_extract,,1)=INDEX(_extract,,2))*TAKE(_extract,,-1)))
  • 使用FILTER()函数提取标题为的数据范围Stock Unit，Sales Unit并Sales基于ID和Name
  • 接下来，使用函数进行布尔运算，对从上面提取的和数组SUM()进行比较，对相应数组的乘积求和。Sales UnitStock Unit

此外，如果您正在使用，MS365那么您可以使用一个公式溢出整个输出，因此无需在此处填写：

=MAP(A2:A7,B2:B7,LAMBDA(x,y,
  LET(z, FILTER(Table2!C:E,(Table2!A:A=x)*(Table2!B:B=y),""), 
  SUM(--(INDEX(z,,1)=INDEX(z,,2))*TAKE(z,,-1)))))
  • 使用MAP()函数迭代每个单元格的值来执行自定义LAMBDA()计算。
  • LAMBDA()在使用中LET()传递一个函数来根据和FILTER()提取所需的三个列IDName
  • 接下来，对此处相应数组的乘积进行求和，与Stock Unit-->INDEX(z,,1)与Sales Unit-->进行比较INDEX(z,,2)，返回TRUE和FALSE，然后将其乘以和，Sales相加后即可得到所需的输出。

一种简单的方法是使用SUMIFS()将Sales Unit列作为Criteria Range和Stock Units将列作为Criteria，但是这取决于您的偏好：

在此处输入图片描述

=SUMPRODUCT(
    SUMIFS(
        Table2!E:E,
        Table2!A:A, Table1!A2,
        Table2!B:B, Table1!B2,
        Table2!D:D, UNIQUE(
            Table2!C:C
        )
    )
)

这是另外一种替代方法，但建议采用这种方法，因为它会降低 Excel 的效率。

=SUM(FILTER(Table2!E:E,(IFNA(XMATCH(Table2!D:D,UNIQUE(Table2!C:C)),0))*(A2=Table2!A:A)*(Table2!B:B=Table1!B2),0))

相关内容