excel 使用多个条件但避免重复的Sumif(和w/通配符)

50pmv0ei 于 2023-02-17 发布在其他

关注(0)|答案(4)|浏览(356)

我有一个数据集，我需要用三个不同的标准对所有产品的销售值求和。问题是，有许多产品的这些标准重叠，但我不想加倍（或三次）计数（我没有使用sumifs公式，因为它是在有多个条件必须符合时使用的--"AND"--在本例中，我需要"OR"）。我需要使用通配符，因为要搜索的文本伴有其他文本（幸运的是，我的关键字每个单元格只出现一次）。我在这里创建了一个非常基本的数据集作为示例，因为我无法上传我们的敏感数据（请原谅我对汽车缺乏了解）。假设我想把所有萨博、白色汽车和2022年生产的汽车的价值相加（这些中的任何一个，而不是全部--换句话说，OR而不是AND），但是我不希望任何相同汽车的价值被计算两次。

左上角显示"制造商"的单元格是A1。如果我使用求和公式，将所有萨博、白色汽车和2022年制造的汽车加在一起，我将重复计算汽车JCT835、VUT613和OCY365。
但有趣的是，在某些情况下，同一辆车（参见ID号）在数据中出现多次（重复值请参见红色突出显示的单元格），在这种情况下，我不希望销售数据重复计算。在实际的数据集中，ID号将是相同的，但同一行中的其他单元格将不会--它们将包含公司名称一次，但包含其他文本，这些文本将是不同的，这就是为什么我尝试使用通配符（但是销售数据总是相同的数字）我所做的是创建一个新列，将销售总额除以该项的示例数，所以总的总数保持不变（F列）。单元格F2的公式为：= E2/计数（美元D$2：美元D$18，D2）
您能提供的任何帮助将不胜感激!!
我一直在考虑创建另一个helper列，然后可能创建一些sumif公式来减去重复项，但对我来说有点高级。我在此示例电子表格的早期迭代中尝试了它，它工作正常，直到为了实验起见，我又添加了两行（屏幕截图和底部表格中的最后两行），第一行无关紧要，但第二个是（唯一）白色2022 Saab，值为9000。虽然结果应为114，000，但公式返回了96，000。换句话说，对于一个巨大的数据集（我正在处理的数据集），它不起作用，因为会出现双重和三重计数的示例数量太多，而我不知道如何使用Sumifs进行排序。
=和积（F2：F18 *（（A2：A18 ="萨博"）+（B2：B18 ="白色"）+（C2：C18 = 2022））-（和积（F2：F18，A2：A18，"萨博"，B2：B18，"白色"）+和积（F2：F18，B2：B18，"白色"，C2：C18，2022）+和积（F2：F18，A2：A18，"萨博"，C2：C18，2022）+和积（F2：F18，A2：A18，"萨博"，B2：B18，"白色"，C2：C18，2022））
最后，这是复制的表（从Office 365）。第一个单元格（"Manufacturer"）是A1。为了让大家都清楚，答案应该是：5500 + 5500 + 12000 + 12000 + 9000 + 9000（所有萨博）+13000 +10000（所有尚未统计的白色汽车）+13000 +17000 +8000（尚未统计的2022年起的汽车）= 114000。
| 制造商|颜色|年份|识别号|价值|数值（考虑重复）|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 随机文本丰田|灰色随机文本|小行星2020|ABH749|一万|一万|
| 随机文本Saab|白色随机文本|小行星2021|JCT835|一万一千|5500|
| 雷诺随机文本|随机白色文本|小行星2020|GUK498|一万三千|一万三千|
| Saab随机文本|随机文本白色|小行星2021|JCT835|一万一千|5500|
| 随机文本Audi|黑色随机文本|小行星2020|IUP140|小行星18000|小行星18000|
| 随机Saab文本|黄色随机文本|小行星2022|WTC659|小行星12|小行星12|
| 丰田随机文本|随机文本红色|二○二三|PUN261|一万三千|一万三千|
| 随机文本雷诺|随机红色文本|小行星2022|紫外线976|一万三千|一万三千|
| 随机文本丰田|蓝色随机文本|小行星2021|雷诺710|一万四千|一万四千|
| Audi随机文本|绿色随机文本|小行星2022|VKB396|小行星17|小行星17|
| 随机文本Saab|随机文本蓝色|小行星2022|OCY356|小行星12|小行星12|
| Saab随机文本|随机文本白色|小行星2020|VUT613|九千|九千|
| 大众随机文本|随机红色文本|小行星2022|NVB989|八千|八千|
| 随机文本平面|随机灰度文本|小行星2021|GHB932|六千|六千|
| 随机雷诺文本|随机白色文本|小行星2022|MDA232型|一万|一万|
| 随机大众文本|随机文本灰色|小行星2021|GUB838|七千|七千|
| 随机Saab文本|随机文本白色|小行星2022|VUG879|九千|九千|

excel

来源：https://stackoverflow.com/questions/75424166/sumif-with-multiple-criteria-but-avoiding-duplicates-and-w-wildcards

4条答案

按热度按时间

w1e3prcc1#

依次使用FILTER()和UNIQUE()，并对这些唯一值求和。

=SUM(TAKE(UNIQUE(FILTER(A2:E6,(A2:A6="Saab")*(B2:B6="White")*(C2:C6=2022))),,-1))

赞(0）回复(0）举报 2023-02-17

sbdsn5lh2#

我想加一个助手栏，你可以填-
假设列D为ID Number

=IF(ISERROR(MATCH(D2,D1:D$1,0)),E2,0)

其工作原理是

MATCH()如果D中的ID Number在同一行的单元格 * 上方 * 包含同一行的单元格，则返回相对索引或ERROR（不匹配）
ISERROR()返回True（发现重复项）或False（不匹配）
IF()出现错误，请使用Value中的同一行，否则0

注意事项

如果两个ID Number对的Value不相同，将使用第一个，忽略后面的示例

这可以通过添加 * 另一个 * 列来修复，该列只是附加了ID Number和Value，以生成新的密钥=D2&E2（尽管这可能会被非常特殊的情况进一步愚弄，但似乎不太可能）

如果另一个ID Number就是ID Number，则从D3开始，跳过第2行（手动填充，因为它不能重复）

赞(0）回复(0）举报 2023-02-17

5sxhfpxr3#

我只回答你问题的第一部分，而不是重复的部分。你提到了SUMIF（），这让我相信你并不熟悉提供多个条件的内置SUMIFS（）函数。*

有两种方法可以做到这一点，而且两种方法都有：

不要假设您拥有Excel O365
不需要帮助器列
假设您仍在以某种方式处理重复ID
SUMIFS（）方法

这是“简单按钮”。我不知道你的具体列和范围，所以我将使用一般术语，你可以在你的电子表格中用具体范围替换它们：
=SUMIFS( valueRange, colorRange, "White", modelRange, "Saab", yearRange, 2002)

求和乘积（）方法

我经常使用SUMPRODUCT方法而不是SUMIFS方法，因为我已经习惯了它，而且它很健壮--它允许AND和OR标准的任意组合：
=SUMPRODUCT( (colorRange="White) * (modelRange="Saab") * (yearRange=2002) * valueRange )
SUMPRODUCT（）方法需要一些练习才能掌握，但它是一种非常有价值的高级Excel技能，如果您经常做这类工作，那么学习曲线是非常值得的。一旦您习惯了SUMPRODUCT方法，您可能会在大多数时间停止使用SUMIFS。

赞(0）回复(0）举报 2023-02-17

68bkxrlz4#

这里只展示一个脚本示例（如这里所暗示的），Python + Pandas这样的工具可以很容易地为您完成这一任务（注意，重复的索引3被删除，但原始索引排序保持不变）

>>> import pandas as pd
>>> df = pd.read_csv("data_75424166.txt", sep="\t")  # copied from post
>>> df.drop_duplicates()  # defaults to all rows
   Manufacturer   Color  Year ID Number  Value
0        Toyota    Gray  2020    ABH749  10000
1          Saab   White  2021    JCT835  11000
2       Renault   White  2020    GUK498  13000
4          Audi   Black  2020    IUP140  18000
5          Saab  Yellow  2022    WTC659  12000
6        Toyota     Red  2023    PUN261  13000
7       Renault     Red  2022    UVD976  13000
8        Toyota    Blue  2021    REW710  14000
9          Audi   Green  2022    VKB396  17000
10         Saab    Blue  2022    OCY356  12000
11         Saab   White  2020    VUT613   9000
12   Volkswagen     Red  2022    NVB989   8000
13         Fiat    Gray  2021    GHB932   6000
14      Renault   White  2022    MDA232  10000

删除重复的制造商行示例

>>> df.drop_duplicates(["Manufacturer"])
   Manufacturer  Color  Year ID Number  Value
0        Toyota   Gray  2020    ABH749  10000
1          Saab  White  2021    JCT835  11000
2       Renault  White  2020    GUK498  13000
4          Audi  Black  2020    IUP140  18000
12   Volkswagen    Red  2022    NVB989   8000
13         Fiat   Gray  2021    GHB932   6000

总过滤

>>> df.drop_duplicates(["ID Number"]).loc[(df["Manufacturer"] == "Saab") | (df["Color"] == "White") | (df["Year"] == 2022) ]
   Manufacturer   Color  Year ID Number  Value
1          Saab   White  2021    JCT835  11000
2       Renault   White  2020    GUK498  13000
5          Saab  Yellow  2022    WTC659  12000
7       Renault     Red  2022    UVD976  13000
9          Audi   Green  2022    VKB396  17000
10         Saab    Blue  2022    OCY356  12000
11         Saab   White  2020    VUT613   9000
12   Volkswagen     Red  2022    NVB989   8000
14      Renault   White  2022    MDA232  10000
>>> df.drop_duplicates(["ID Number"]).loc[(df["Manufacturer"] == "Saab") | (df["Color"] == "White") | (df["Year"] == 2022) ]["Value"].sum()
105000

excel 使用多个条件但避免重复的Sumif(和w/通配符)

4条答案

相关问题

热门标签

最新问答