首页 > Excel专区 > Excel教程 >

excel里如何使用SUMPRODUCT进行跨工作表统计数量-

Excel教程 2022-03-10 21:23:19

在Excel表中,您可以在列中的任意两个条件上进行过滤。但是在单独的列表中过滤任意数量的项目不是很好吗?如果在计算列中使用Excel的SUMPRODUCT函数,则可以。Excel 2007引入了强大的表格功能,如下图所示。表格使您可以轻松地对数据进行排序和过滤。

但是,过滤能力至少有两个问题。首先,您最多只能使用两个条件来过滤任何列。其次,每次更换过滤器时,大约需要六步。因此,更换过滤器并不是一个快速的过程。如果您只需将一个或多个过滤器复制并粘贴到一系列单元格中,甚至使用公式来更改过滤器,就会容易得多。该图说明了解决这些问题的方法。在图中,“表的项目”列只是电子表格的A列中的文本项目的列表。D列(不属于表)显示了Items列的搜索条件列表。如果公式B在列A的相邻项目中的SearchFor列表中找到任何项目,则该公式将返回TRUE。在更新列表SearchFor,您只需按下F9键(如果你手动计算模式设置),然后如果你,如果你想要的物品只希望找到的项目,或FALSE筛选TRUE找到列 未找到。SUMPRODUCT公式这是我在上面的B列中使用的 SUMPRODUCT公式:B3:= SUMPRODUCT(NOT(ISERROR(SEARCH(SearchFor,[@ Items])))+ 0)> 0如果您以前没有使用过Excel Tables,则“ [@Items]”参数对您来说会很奇怪。该参数说:“从Items列的当前行返回值。” 也就是说,它在单元格B3中的工作方式与在此处使用单元格引用A3相同:B3:= SUMPRODUCT(NOT(ISERROR(SEARCH(SearchFor,A3)))+ 0)> 0实际上,当您在单元格B3中键入此公式,但单击而不是在单元格A3中键入时,Excel会返回“ [@Items]”。因此,在输入公式时,您实际上不需要了解Table语法。但是,还有一个问题需要更长的解释:这个SUMPRODUCT公式如何起作用?回答此问题的最佳方法是遵循我最初用于创建公式的路径。为此,让我们使用单元格B4,当该单元格中的公式正常工作时,该单元格应返回TRUE …

步骤1:设置SEARCH功能。单元格B4中的SEARCH公式返回#VALUE!错误,因为它试图将多个搜索结果返回到一个单元格中。(换句话说,它正在尝试为SearchFor列表中的每个项目返回一个结果。)要查看这些结果是什么,请单击公式栏中的任意位置,然后按F9键以计算公式。当您按F9键时,您会得到以下结果:

SEARCH函数返回#VALUE! 当找不到商品时。在这里,Excel在“运动外套”中搜索“鞋子”,但失败了。它搜索“ b?w”并失败;它搜索“ c * s”并成功,返回“ 8”,然后搜索三组破折号,但失败了。因此,它返回了显示的错误值数组,中间夹着8个。在公式栏中计算结果后,就像我在这里所做的那样,按Esc键返回原始公式。我们的下一步是将这些错误值转换为FALSE值,并将“ 8”转换为TRUE值。为此,我们用NOT(ISERROR(…))包围SEARCH函数,从而为我们提供…

步骤2:将错误设置为FALSE,将成功设置为TRUE。因为我们要对结果求和,所以需要将那些TRUE和FALSE转换为1和0(一和零)。最简单的方法是在公式中添加零。(这是有效的,因为TRUE + 0 = 1且FALSE + 0 =0。)这给我们:B4:= NOT(ISERROR(SEARCH(SearchFor,[@ Items])))+ 0测试编辑栏中的更改,我们现在看到:

步骤3:将TRUE设为1,将FALSE设为0。现在,我们需要将这些结果加起来。如果总数大于零,我们知道至少有一个过滤器起作用。我们通过再次扩展公式来添加结果…B4:= SUMPRODUCT(NOT(ISERROR(SEARCH(SearchFor,[@ Items])))+ 0)> 0这是公式的最终版本。现在,我们可以将其复制到表的其余部分,从而得到以下结果:

步骤4:如果所有1的总和大于零,则返回TRUE。我们在这里使用SUMPRODUCT的原因有两个。首先,即使公式中没有乘法(也没有“乘积”),此函数也会对数组中的值求和。因此,它可以满足我们的需求。其次,它省去了我们需要数组输入公式的其他版本的麻烦。如果您不介意在表中使用数组,则可以对这个较短的公式进行数组输入以得到相同的结果:B4:= OR(NOT(ISERROR(SEARCH(SearchFor,[@ Items]))))使用OR函数之所以有效,是因为如果OR的任何参数为TRUE,则OR返回TRUE,这就是我们想要的。但是不幸的是,我们必须告诉OR将其参数视为数组,这就是为什么我们必须对表达式进行数组输入的原因。关于SearchFor列表的简要说明如果SearchFor列表包含任何空单元格,则整个Found列将返回TRUE。因此,我们需要仔细定义此列表。一种选择是使用动态范围名称仅定义我们正在使用的搜索词列表。但是我不会在这里讨论这种方法。另一个选择是输入一些您知道列表不包含的搜索项,例如上面D列中显示的三组破折号。然后,当您要将其他搜索词添加到列表中时,只需用实际要使用的搜索文本替换一组破折号即可。但是,不要包含太多破折号,因为列表中的每个其他项都需要花费额外的时间来计算。因此,如果A列中的列表很长,则可能会明显降低计算速度。


标签: 公式单元格数组返回excel教程

office教程网 Copyright © 2016-2020 http://www.office9.cn. Some Rights Reserved. 苏ICP备20040415号-9