我有一个数据集,我需要用三个不同的标准对所有产品的销售值求和。问题是,有许多产品的这些标准重叠,但我不想加倍(或三次)计数(我没有使用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|九千|九千|
4条答案
按热度按时间w1e3prcc1#
依次使用
FILTER()
和UNIQUE()
,并对这些唯一值求和。sbdsn5lh2#
我想加一个助手栏,你可以填-
假设列
D
为ID Number
其工作原理是
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行(手动填充,因为它不能重复)5sxhfpxr3#
有两种方法可以做到这一点,而且两种方法都有:
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。
68bkxrlz4#
这里只展示一个脚本示例(如这里所暗示的),Python + Pandas这样的工具可以很容易地为您完成这一任务(注意,重复的索引3被删除,但原始索引排序保持不变)
删除重复的制造商行示例
总过滤
相关文件
.drop_duplicates()
behavior