使用SUMPRODUCT伯爵拥有多个或标准
正如我在许多博客中提到的那样,SUMPRODUCT是一种非常通用的功能,可以用于多种用途。在本文中,我们将看到如何使用此函数对具有多个OR条件的值进行计数。
用倍数或条件计算的公式
= |
Criteria1:这是任何返回TRUE和FALSE数组的条件。
Criteria2:这是您要检查的下一个条件。
同样,您可以根据需要设置任意多个条件。
上面的通用公式经常进行修改,以适合使用多个OR标准进行计数的要求。但是基本公式是这样。首先,我们将通过一个示例了解其工作原理,然后,我们将讨论其他需要稍微修改此公式的方案。
示例:如果使用SUMPRODUCT
计算经销商代码或年份匹配,则对用户进行计数因此,这里有一个销售人员数据集。数据包含许多列。我们需要做的是计算具有代码“ INKA”或年份为“ 2016”的用户数量。确保如果某人同时拥有(代号为“ inka”和2016年),则应计为1。
因此,这里有两个条件。我们使用上述SUMPRODUCT公式:
= |
在这里,代码和年份被命名为范围。
返回7。在数据中,我们有5条INKA代码记录和4条2016年记录。但是有2个记录分别以“ INKA”和2016作为代码和年份。
这些记录计为1。这就是我们得到7的方式。
如何运作?
因此,让我们看一下如何逐步解决该公式,然后我将讨论其工作原理。
第一步,将I3的值(“ INKA”)与代码范围内的每个单元格进行比较。这将返回一个TRUE和FALSE数组。每次比赛都为TRUE。为了节省空间,我没有显示所有的TRUE-FALSE。同样,K3(2016)的值与年份范围内的每个单元格匹配。
在下一步中,我们将这两个数组相加,得到一个新的数值数组。如您所知,在Excel中将TRUE视为1,将FALSE视为0。因此,将TRUE和TRUE相加时,我们得到2,其余的您可以理解。
= |
1⇒`SUMPRODUCT*`(–\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;…}+\{FALSE;FALSE;FALSE;TRUE;TRUE;…})>0 |
2⇒`SUMPRODUCT*`(–\{1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0 |
3⇒`SUMPRODUCT*`(–(\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;…}) |
4⇒`SUMPRODUCT*`(\{1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
5⇒7 |
在下一步中,我们检查数组中哪个值大于0。
这再次将数组转换为真假数组。对于得到的每个0值,False和rest被转换为true。现在,数组中TRUE值的数目就是我们的答案。但是,我们如何计算它们呢?这是如何做。
链接:/ tips-the-double-negatives-in-excel [双负号(-)号]用于将布尔值转换为1和0。
因此,数组中的每个TRUE值都将转换为1,而FALSE则将转换为0。在最后一步,SUMPRODUCT对这个数组求和,我们得到的答案为7。
使用SUMPRODUCT添加更多或标准进行计数因此,如果您需要添加更多或标准进行计数,则只需在函数中使用+号添加标准即可。例如,如果您想在上述公式中添加另一个条件,以便它增加了销售5种以上产品的员工人数。
SUMPRODUCT公式将如下所示:
简单!是不是
但是,假设您要从Code *范围获得两个条件。假设您要计算“ INKB”。那你怎么做呢?一种方法是使用上述技术,但这将是重复的。假设我要从同一范围再添加10个条件。在这种情况下,使用SUMPRODUCT进行计数时这种技术并不明智。
= |
假设我们有这样排列的数据。
标准代码在I2:J2行中。数据的排列在这里很重要。 3 OR条件计数设置的SUMPRODUCT公式为:
_这是当连续写入一个范围中的多个条件时,使用多个条件进行计数的SUMPRODUCT公式。_这将返回正确的答案,即10。
如果您在J3中键入任何年份,该公式也会添加该计数。
= |
当条件在一行中时使用。当条件在同一范围内的一列中时,它将起作用吗?不,不会。
在此示例中,我们有多个要计数的代码,但是这些类型代码写在一栏中。当我们使用上面的SUMPRODUCT公式时,我们收到#N / A错误。我们将不讨论此错误的产生原因,因为这会使本文过长。让我们看看如何使这项工作有效。
为了使该公式生效,您需要将代码标准包装在“ link:/ mathematical-functions-excel-transpose-function [TRANSPOSE function]”中。这将使公式起作用。
当一列中列出标准时,这是用于计算多个或相同范围内的条件的公式。
是的,队友,我希望我足够清楚,这是有道理的。我希望它能满足您在这里的目的。如果此公式不能解决您的问题,请在下面的评论部分中告诉我您的要求。我将竭诚为您提供任何帮助。您可以提及与Excel / VBA有关的任何疑问。然后,继续学习,保持卓越。
= |
相关文章
链接:/ summing-excel-sumproduct-function [如何在Excel中使用SUMPRODUCT函数]:在excel中将多个数组中的值相乘后返回SUM。此功能可用于执行多项任务。这是最通用的功能之一。
link:/ counting-countifs-dynamic-criteria-range [具有动态条件范围的COUNTIFS]:要使用动态条件范围进行计数,我们只需使用INDIRECT函数。此函数可以`link:// counting-countifs-with-or-for-multi-criteria [COUNTIFS With OR for Multiple Criteria]
:对具有多个条件的单元格进行计数,这些单元格使用OR函数匹配。要将OR逻辑放入COUNTIFS函数中,则无需使用OR函数。
在Microsoft Excel中将IF与AND / OR函数一起使用:这些逻辑函数用于进行多个条件计算。对于IF,OR和AND函数用于包含或排除匹配项。
如何在Microsoft Excel中使用OR函数:该函数用于在多个条件中包括所有TRUE值。 link:/ logical-formulas-count-cells-contain-this-or-that [如何计算Excel中Excel中包含此或那个单元格的单元格]’:对于包含此或那个单元格,我们可以使用SUMPRODUCT功能。这是您进行这些计算的方式。
热门文章:
`链接:/ keyboard-formula-shortcuts-50-excel-shortcuts可提高您的生产率[50 Excel快捷方式以提高生产率]更快地完成任务。这50个快捷键将使您在Excel上的工作速度更快。
link:/ vlookup-functions / formulas-and-functions-introduction- [如何使用Excel VLOOKUP函数]
|这是excel中最常用和最受欢迎的功能之一,用于从不同范围和工作表中查找值。 link:/ tips-countif-in-microsoft-excel [如何使用]
link:/ vlookup函数的公式和函数介绍[Excel]
link:/ tips-countif-in-microsoft-excel [COUNTIF函数]
|使用此惊人的功能对条件进行计数。
您无需过滤数据即可计算特定值。 Countif功能对于准备仪表板至关重要。
如何在Excel中使用SUMIF函数这是仪表板的另一个重要功能。这可以帮助您汇总特定条件下的值。