☰

使用特定标题和其他标准对值进行求和

tag-icon • tag-icon microsoft-excel worksheet-function
使用特定标题和其他标准对值进行求和

我有以下公式：

SUMPRODUCT((Chart!$J$2:$BE$1000=H$2)*(Chart!$A$2:$A$1000=$A3)*(Chart!$C$2:$C$1000="FE")*(Chart!$H$2:$H$1000="YES")*(Chart!$G$2:$G$1000))

其中：

  • (Chart!$J$2:$BE$1000=H$2)：在一系列列中查找特定月份。
  • (Chart!$A$2:$A$1000=$A3)：在一列中查找客户的特定昵称。
  • (Chart!$C$2:$C$1000="FE")：在一列中查找交易的特定名称。
  • (Chart!$H$2:$H$1000="YES")：在一列中查找将要续订的交易。
  • (Chart!$G$2:$G$1000)：这些是需要求和的金额。

该公式效果很好，但我需要能够对一系列列求和，而不仅仅是G2:G1000。G2:G1000我需要对列范围内的值求和$J$2:$EBE$1000，而不是对标题中具有特定名称的列（即MONTHLY_VOLUME）求和。

我试过这两个公式：

SUMPRODUCT((Chart!$J$2:$BE$1000=H$2)*(Chart!$A$2:$A$1000=$A3)*(Chart!$C$2:$C$1000="FE")*(Chart!$H$2:$H$1000="YES")*SUMIF(Chart!$J$1:$BE$1," * MONTHLY_VOLUME * ",Chart!$J$2:$BE$1000))

和

SUMPRODUCT((Chart!$J$2:$BE$1000=H$2)*(Chart!$A$2:$A$1000=$A3)*(Chart!$C$2:$C$1000="FE")*(Chart!$HD$2:$H$1000="YES")*INDEX(Chart!$J$2:$BE$1000,,MATCH(" * MONTHLY_VOLUME * ",Chart!$J$1:$BE$1,0)))

这两个公式都给出了相同的结果，但结果是错误的。这些值太高了，我猜这是因为这两个公式的最后一部分，无论是SUMIF或INDEX缺少一些参数，使它们能够与先前的标准一起工作，而不是仅仅抓住一切。

那么，这真的可行吗？如果可行，我该如何更改这些公式以使其起作用？

这是工作表： https://drive.google.com/file/d/1cMZaKJIMam7NZOr-6LL8OdLwXJTZ2bco/view?usp=sharing

答案1

因此，在问题的第一部分（您已经解决了）中，您生成了一个掩码列向量，例如（0、0、1、0、1），然后将其逐个单元格与数量列向量相乘，例如（10、20、30、40、50），然后使用 sumproduct 将向量元素相加（在此示例中为 80）。

扩展您的问题，您现在想要识别一个或多个 monthly_volume (MV) 列，并根据上面的掩码向量将它们加在一起。例如 SUMPRODUCT((0, 0, 1, 0, 1) * ((10, 20, 30, 40, 50) + (60, 70, 80, 90, 100))) = 260

让我们使用一些矩阵乘法。假设您有 n 行数据和 m 列，其中包含 1 个或多个 MV 列。首先获取一个柱子MV 列的矢量掩码：([1 xm]' = [mx 1])

TRANSPOSE((Chart!$J$1:$BE$1 = " * MONTHLY_VOLUME * ")*1)

然后将其与数据矩阵相乘，得到一个由 MV 列和组成的单列向量 ([nxm] * [mx 1] = [nx 1])

MMULTI(IFERROR(VALUE($J$2:$EBE$1000),0), TRANSPOSE((Chart!$J$1:$BE$1 = " * MONTHLY_VOLUME * ")*1)

现在在原始公式中使用它：

SUMPRODUCT((Chart!$J$2:$BE$1000=H$2)*(Chart!$A$2:$A$1000=$A3)*(Chart!$C$2:$C$1000="FE")*(Chart!$H$2:$H$1000="YES")*(MMULTI(IFERROR(VALUE($J$2:$EBE$1000),0), TRANSPOSE((Chart!$J$1:$BE$1 = " * MONTHLY_VOLUME * ")*1)))

记住使用 CTRL+SHIFT+ENTER 输入公式（作为数组公式）

相关内容