如何将 sumif 与通配符一起使用?
SUMIF 是我最喜欢的 Excel 函数之一,我相信它也是您的。但一直让我担心的一件事是,只有当标准与值完美匹配时,我们才能添加值。
假设你的名字是约翰·马丁。
在这种情况下,如果我们想使用您的名字添加值,我们应该有您的全名。而且,如果我们只有您的名字,我们就无法使用它。
是的,这是一个问题。但是,我也有一个解决方案。您还记得Excel 通配符吗?是的你是对的。
如果我们在 SUMIF 中使用通配符,我们可以使用部分标准对值求和。好消息是我们可以在 SUMIF/SUMIFS 中使用所有三个字符(星号 [*]、问号 [?]、波形符 [~])。
今天在这篇文章中,我想与您分享一种使用 SUMIF 和通配符的简单方法,并通过示例解释了这三个字符的用法。
如果您想了解有关通配符的更多信息,您将从本指南中学习,并确保从此处下载此数据文件以进行后续操作。
现在让我们开始……
1. 星号 [*] 与 SUMIF
星号是最流行的通配符。而且它是可以与 SUMIF 完美配合使用的一种。
星号:快速介绍
您可以使用星号来表示条件末尾或开头处缺少的一个或多个字符。
正如我所说,如果我们以 John 作为条件,并且需要对 John Martin 这个名字的值求和,我们可以将文本“John”与星号组合起来以呈现文本的其余部分。用星号表示文本的其余部分。
示例:SUMIF + 星号
这里我有一个更好的例子让你了解asterisk如何与SUMIF一起使用。
下表中有一列附有产品名称和发票号码,或者您可以说它是每个产品的一个发票号码。
现在,我们需要从该表中创建每个产品的总计。但如果您看到的话,每个产品名称都是唯一的,因为我们有一个发票号码。
从这里开始,即使我们创建了数据透视表,我们也无法获得每个产品的总计。所以是时候使用星号了,公式是:
=SUMIF(invoice_column,product_name&"*",amount_column)
在上面的公式中,我们将每个产品的产品名称与星号通配符总和值组合在一起。
因此,当 SUMIF 与发票列条件匹配时,它会采用星号之前的字符并替换其余字符。
简单来说,它忽略单元格产品名称后面的所有字符并返回金额列的总和。
这样,即使我们在列中没有合适的产品名称,我们也可以获得每个产品的总计。
2.问号[?]与SUMIF
问号是星号之后的下一个重要字符。 SUMIF/SUMIFS 可以帮助您创建部分文本条件。
问号:快速介绍
您可以使用问号来表示文本字符串中的单个未知字符。
我的名字是“普尼特”。但是如果有人把它写成 PUNNET 和 PUNIIT 那样会发生什么呢?我无法对与这两个或任何其他相关的值求和。
如果我使用 PUN??T(两个问号),它可以帮助我对两个标准的值求和。
示例:SUMIF + 问号
通常,与星号相比,问号的使用并不常见,但即便如此,学习它也很重要,并且可以在某些特定情况下使用。
看看下面的数据(再次,我使用我的名字)。如果你仔细观察,你会发现我的名字在名单上出现了好几次。
但需要注意的是,名字和姓氏之间不是有空格,而是有不同的字符。
如果你想总结那里的所有奖金,你不能只提到我的名字。
即使该名称存在于多个单元格中,但每个单元格都是唯一的,可以解决此问题并将所有奖金加起来,您可以使用下面的公式。
=SUMIF(name_column,"Puneet?Gogia",amount_column)
现在,您在名字和姓氏之间使用了问号。问题是,当您使用问号时,它只会用可用字符之一替换该字符。
当 SUMIF 使用您指定的条件时,它仅匹配名字和姓氏,并忽略您使用问号的同一位置的匹配值中的字符。
所以关键点是:问号代表文本字符串中的单个字符。
3. 波形符 [~] 与 SUMIFS
我不知道您是否需要使用波浪号作为通配符。但是,如果您的工作包括处理最脏的数据,那么有一天波浪号可能会派上用场。
波形符:快速介绍
简而言之,波形符会覆盖通配符(星号和问号)的效果。
例如,如果您尝试使用将星号或问号视为真实字符的文本来添加值,SUMIF 会将它们视为通配符而不是真实字符。
示例:SUMIF + 波形符
在这里,我们使用与上例相同的数据,在该数据中,您再次看到我的名字。但这个时间名称已经具有了通用特征。
根据此数据,您需要将带有问号的名称的奖金值相加。看一下下面的示例,我们只是使用标准来累加奖金值。
目的:如果您查看公式结果,它会显示所有奖金值的总和(名字和全名)。
SUMIF 将条件中的这个问号视为通配符,并返回条件中文本为“Puneet”的奖金值的总和。正如我所说,我们需要使用带星号的波形符来获取值的总和。所以公式是:
=SUMIF(name_column,"Puneet*~",amount_column)
因此,当您在星号旁边使用波形符时,SUMIF 会将其视为真实字符而不是通配符。
问题解决了。您已获得正确的值总和。
结论
我喜欢在 SUMIF/SUMIFS 中使用通配符的原因是它可以节省时间,并且您可以添加值而无需对原始值进行任何初始更改。
只要记住:
- 如果您不确定数据的全文字符串,可以使用星号。
- 并且,如果要替换特定数量的字符,可以使用问号。
- 但是,就像我说的,您的工作包括处理大部分脏数据,因此波形符可能会派上用场。
我希望这个技巧能帮助您更好地掌握公式。您可以从这里学习一些最令人惊奇的 Excel 公式。
现在告诉我一件事。
您以前使用过带通配符的 SUMIF 吗?
在评论部分与我分享您的想法,我很乐意听取您的意见。并且不要忘记与您的朋友分享这个技巧。