excel里如何使用SUMPRODUCT进行跨工作表统计数量-
发布时间:2023-11-27 14:23:10
在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列中的列表很长,则可能会明显降低计算速度。


猜你喜欢
- 在Win10系统中打开一个文件夹时,底部会出现一条状态栏,如果你觉得状态栏占用了一行显示空间,可以设置不让其显示,下面是具体的设置步骤,需要
- 最近有用户反应,自己在使用Win10电脑是出现了桌面上找不到鼠标箭头的情况,Win10桌面上没有鼠标箭头怎么解决?遇到相同问题的小伙伴们就一
- 习惯用windows系统的用户认为苹果mac系统不好上手,但mac os x远比大家想象中的还要易用很多,那么苹果mac系统怎么用呢习惯用w
- 很多用户通过映射网络磁盘,来解决开机时手动输入访问局域网进行共享文件的问题,那么winxp系统如何映射网络磁盘?今天系哦啊吧就为大家分享关于
- Sketch是当前Mac上最强大和知名的移动APP和网站设计工具,尤其是在移动应用设计方面,Sketch的优点在于使用简单,学习曲线低,并且
- Word域代码特殊指令该怎么用?今天我们就来看看word中创建分数和积分的方法,很简单,一学就会,下面的公式和用法都很详细,需要的朋友可以参
- 很多用户发现,自己在更新了win11系统版本后,打开edge浏览器一直更新无法打开,提示错误代码0x800704ec,本文就为大家带来了详细
- 我们常常用Excel来绘制数据表格,进行复杂的数据的统计。可是对于新手来说,陌生的Excel看起来很复杂,不知道该从哪里入手。接下来,小编就
- 其实【钢笔工具】没有想象中的难,只是你没有足够的了解它。下边小编就为大家详细的介绍一下钢笔工具的使用方法,需要的朋友可以参考下很多学习初学p
- 在日常使用电脑的过程中,有时会出现以下问题,最近有网友遇到了win7系统注册表损坏无法启动的情况,win7系统注册表损坏无法启动怎么办?开始
- Win11怎么下载安卓安卓应用APP?以前如果想要在Windows系统电脑上运行安卓应用的话,需要先下载安卓模拟器,不过现在不用这么麻烦了,
- 第1步,打开Word2013文档窗口,选中需要应用样式的段落或文本。在“开始”功能区的“样式”分组中单击“其他”按钮,如图1所示。 &nbs
- 今日,苹果发布了 macOS 11.0.1 Big Sur 修订版,该版本可用于此前未安装 macOS 11.0.1 更新的部分 Mac,赶
- 电脑使用久了总是会遇到一些问题,其中最常见的就是出现系统提示了,相信不少用户都曾遇到过系统提示GWXUX已停止工作,那么当你遇到Win7出现
- WPS公式中的分式和根式怎么输入?wps中输入的公式需要插入分式和根式,该怎么插入呢?下面我们就来看看详细的教程,需要的朋友可以参考下今天我
- 客户反应,windows黑屏未能启动,提示由于系统注册表文件丢失或损坏,因此windows无法加载,错误代码:0xc000014c,电脑故障
- 我相信许多计算机用户都不知道在win7系统中有阻止媒体共享的功能。此策略可以阻止Windows Media Player中的媒体共享,即,防
- 在Win7中,备份分为两种:备份整个系统和备份系统设置/创建系统修复盘。其中,系统备份是最彻底的备份,可将将整个系统集成到一个映像文件中,在
- 打开Word窗口时,会看到窗口上方有一排标尺,依次为2、4、6……38,它是以字符为单位,在宋体五号字的格式下,每行可输入约38个中文字符。
- NVIDIA英伟达公司刚刚发布全新的Win10显卡驱动355.60版,距上一个版本353.54只有半个月动。355.60版显卡驱动同样通过了