我手头有一个基本问题。在下面的示例表中,有一个文本值列和一个数字值列:
A | 乙 |
---|---|
果汁 | 5 |
花生 | 2 |
核桃 | 3 |
果汁 | 5 |
菠萝 | 8 |
我想对数字-值列中的数字求和,排除同一行中名称-值列中具有相同值的数字(即 juice),总和为 18。显然,每次给出名称时,数字都与名称相对应,这是理所当然的。
如果数字也不同,公式中需要做出哪些改变呢?如下所示:
A | 乙 |
---|---|
果汁 | 5 |
花生 | 2 |
核桃 | 3 |
果汁 | 6 |
菠萝 | 8 |
果汁 | 5 |
在这种情况下,我想排除一个完全重复:果汁-5,并在计算中将其算作一个实例。总和中的答案现在是 24。
编辑:我有一张类似的表格:
下面我根据 A 列中的每个首字母计算数字的总和。例如:
C10
=SUMPRODUCT((A1:A8="B") *(C1:C8))
我想排除所有三个变量的所有重复项(即黄色),将它们算作一个。因此,C10 将是 5,而不是 7。我应该在公式中添加什么?
答案1
您可以使用 SUMPRODUCT 和 COUNTIFS 作为除数。这样,只需将每个数的 1/2juice 5
相加,结果就只有5
。
=SUMPRODUCT(B1:B6/COUNTIFS(A1:A6,A1:A6,B1:B6,B1:B6))
假设有一个动态数组公式UNIQUE:
=SUM(INDEX(UNIQUE(A1:B6),0,2))
这将返回唯一值并对第二列求和。
根据您的编辑:
=SUMPRODUCT(((A1:A6="B") *(C1:C6))/(COUNTIFS(B1:B6,B1:B6,C1:C6,C1:C6,A1:A6,"=B")+(A1:A6<>"B")))
动态数组版本:
=SUM(INDEX(UNIQUE(FILTER(A1:C6,A1:A6="B")),0,3))
答案2
如何使用一个或两个辅助列来检测重复项?
假设您有 A 到 C 中的数据,并且如果所有三个 A 到 C 的值之前都一起出现过,那么辅助列应该为零,如果不是,则为 C 中的值。现在,如果您可以对数据进行排序(自定义排序,首先按 A 进行多级排序,然后按 B 然后按 C 进行多级排序),那么所有重复项将始终位于彼此的正下方。
因此在 D1 中输入(并复制下来)
=IF(AND(A1=A2,B1=B2,C1=C2),0,C2)
现在按照您的示例对 D 列进行求和。
如果无法排序,则必须添加另一个帮助列(例如 E),内容如下:
=A1 & B1 & C1
有了 D1 =C1
, D2 就变成(向下复制)
=IF(ISNUMBER(MATCH(E2,$E$1:E1,0)),0,C2)