excel在主工作表中汇总多个工作表中满足条件的值
发布时间:2022-02-10 18:24:59
我们可能熟悉使用INDEX、SMALL等在给定单列或单行数组的情况下,返回满足一个或多个条件的值的列表。这是一项标准的公式技术。
我们述了一种方法,给定由多个列组成的单元格区域,从该区域返回由所有非空单元格组成的单个列。可以很容易地验证,在该公式中的单个条件可以扩展到多个条件,因此,我们现在有了从一维数组和二维数组中生成单列列表的方法。
那么,可以更进一步吗?“三维”是经常应用于Excel * 定公式的通用术语,这些公式不仅可以对单列或单行进行操作,也可以对由多列或多行组成的单元格区域进行操作,还可以有效地对多个工作表进行操作。
本文提供了一种方法,在给定一个或多个相同布局的工作表的情况下,可以创建另一个“主”工作表,该工作表仅由满足特定条件的所有工作表中的数据组成。并且,这里不使用VBA,仅使用公式。
假设我们想从下面三个工作表中整理数据:
工作表Sheet1:
工作表Sheet2:
工作表Sheet3:
想要创建一个主工作表Master,其数据来源于上面三个工作表中列D中的值为“Y”的数据:
解决方案
首先,定义下面两个名称:
名称:Sheet3
引用位置:={“Sheet1″,”Sheet2″,”Sheet3”}
名称:Arry1
引用位置:=MMULT(0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets))))),TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”)))
可以根据实际情况,修改工作表列表和数据范围(D2:D10)。
在工作表Master的单元格G1中,输入下面的公式:
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
在工作表Master的单元格A2中输入下面的数组公式:
=IF(ROWS($1:1)>$G$1,””,INDEX(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!A2:F10”),SMALL(IF(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!D2:D10″)=”Y”,ROW(INDIRECT(“1:”&$G$1))),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))),COLUMNS($A:A)))
向下和向右拖放公式至合适的位置。
工作原理
先看看相对简单的单元格G1中的公式,该公式用于确定返回结果的数量:
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
如果不熟悉跨多个工作表使用公式的技术,那么应记下使用INDIRECT的这种公式构造,因为它实际上是我们执行此类计算的唯一方法。上述公式转换为:
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&{“Sheet1″,”Sheet2″,”Sheet3″}&”‘!D2:D10″),”Y”))
然后,将这组代表工作表名称的文本字符串的两端连接,在后面是所使用的工作表区域(D2:D10),在前面用单个撇号连接。尽管在工作表的名称中不包含空格的情况下,并不需要这样,但是这样做将更好更通用。这样,公式转换为:
=SUMPRODUCT(COUNTIF(INDIRECT({“‘Sheet1’!D2:D10″,”‘Sheet2’!D2:D10″,”‘Sheet3’!D2:D10″}),”Y”))
因为COUNTIF函数能够操作三维单元格区域,并且SUMPRODUCT函数提供了必要的强制转换,使得INDIRECT函数返回一组单元格引用,而不仅仅是一个,因此公式转换为:
=SUMPRODUCT({3,2,1})
其中数组的值由3、2、1组成,与工作表Sheet1、Sheet2、Sheet3的列D中包含“Y”的数量一致。该公式的最后结果为:
6
接下来,看看单元格A2中的主公式:
=IF(ROWS($1:1)>$G$1,””,INDEX(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!A2:F10”),SMALL(IF(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!D2:D10″)=”Y”,ROW(INDIRECT(“1:”&$G$1))),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))),COLUMNS($A:A)))
在IF函数中的前半部分很简单,如果拖放的行数超过了可能获得的结果数量,则为空。
在公式中使用了定义的名称Arry1:
=MMULT(0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets))))),TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”)))
这种公式构造可以有效地动态生成汇总小计,并且是使用标准的SUBTOTA/OFFSET函数组合的替代方法。
依次看看传递给MMULT函数的数组。第一个是:
0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets)))))
转换为:
0+(ROW(INDIRECT(“1:”&3))>=TRANSPOSE(ROW(INDIRECT(“1:”&3))))
转换为:
0+({1;2;3}>=TRANSPOSE({1;2;3}))
转换为:
0+({1;2;3}>={1,2,3})
两个正交数组进行比较,一个是3行1列,一个是1行3列,得到一个3行3列的数组,该数组由9个TRUE/FALSE值组成:
0+({TRUE,FALSE,FALSE;TRUE,TRUE,FALSE;TRUE,TRUE,TRUE})
转换为1/0值组成的数组:
{1,0,0;1,1,0;1,1,1}
另外一个传递给MMULT函数的数组是:
TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
转换为:
TRANSPOSE({3,2,1})
得到:
{3;2;1}
因此,MMULT函数变为:
MMULT({1,0,0;1,1,0;1,1,1},{3;2;1})
结果是:
{3;5;6}
使用Arry1的值来替换主公式中的相应部分,先看看公式中的:
INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!A2:F10”)
转换为:
INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,{3;5;6}>=1,0))&”‘!A2:F10”)
转换为:
INDIRECT(“‘”&INDEX(Sheets,1)&”‘!A2:F10”)
转换为:
INDIRECT(“‘”&”Sheet1″&”‘!A2:F10”)
转换为:
INDIRECT(“‘Sheet1’!A2:F10”)
因此,可以看到,对于A2中的公式,将返回Sheet1。例如,如果解构单元格A5中的公式,那么公式中的MATCH构造将如下所示:
MATCH(TRUE,Arry1>=ROWS($1:4),0)
唯一发生变化的是引用ROWS($1:4)而不是ROWS($1:1),结果转换为:
MATCH(TRUE,{3;5;6}>=4,0)
得到2,这样将引用工作表Sheet2。
实际上,该技术的核心为:通过生成动态汇总小计数量的数组,该小计数量由来自每个工作表中符合条件(即在列D中的值为“Y”)的行数组成,然后将公式所在单元格相对行数与该数组相比较,以便有效地确定公式所在行中要指定的工作表。因此,主公式中的子句:
INDEX(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!A2:F10”),SMALL(IF(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!D2:D10″)=”Y”,ROW(INDIRECT(“1:”&$G$1))),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))),COLUMNS($A:A))
可转换为:
INDEX(Sheet1!A2:F10,SMALL(IF(Sheet1!D2:D10=”Y”,ROW(INDIRECT(“1:”&$G$1))),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))),COLUMNS($A:A))
先看看上面公式中的SMALL函数部分:
IF(Sheet1!D2:D10=”Y”,ROW(INDIRECT(“1:”&$G$1)))
转换为:
IF({“Y”;0;”Y”;”Y”;0;0;0;0;0}=”Y”,ROW(INDIRECT(“1:”&6)))
转换为:
IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6})
得到:
{1;FALSE;3;4;FALSE;FALSE;FALSE;FALSE;FALSE}
这里,可以看到数组中的1、3和4对应于工作表Sheet1列D中为“Y”的相对行号。现在要做的就是将该数组传递给SMALL函数并确定参数k,这实际上是整个解决方案中最难的部分,因为与我们将这种构造应用于由单列组成的数组不同(例如,在这种情况下,对于连续行,可以简单地将参数k增加1),而这里必须考虑:当要求Sheet2返回值时,以及要求Sheet3返回值时,该参数将被“重置”为1。为此,这里使用:
IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))
为理解这个公式构造是如何工作的,我们可暂时将其作为独立的数组公式,输入到某单元格中,然后向下拖放以了解其生成的值。实际上,将该公式从A2向下拖至A7,可转换为:
IFERROR(1+{1,2,3,4,5,6}-LOOKUP({1,2,3,4,5,6},1+Arry1),{1,2,3,4,5,6})
转换为:
IFERROR(1+{1,2,3,4,5,6}-LOOKUP({1,2,3,4,5,6},1+{3;5;6}),{1,2,3,4,5,6})
转换为:
IFERROR(1+{1,2,3,4,5,6}-{#N/A,#N/A,#N/A,4,4,6},{1,2,3,4,5,6})
转换为:
=IFERROR({#N/A,#N/A,#N/A,1,2,1},{1,2,3,4,5,6})
得到:
{1,2,3,1,2,1}
正是我们需要的参数k的值,即在工作表Sheet1中匹配第1、第2和第3小的行,在工作表Sheet2中匹配第1和第2小的行,在工作表Sheet3中匹配第1小的行。
现在,回到主公式中的子句:
INDEX(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!A2:F10”),SMALL(IF(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!D2:D10″)=”Y”,ROW(INDIRECT(“1:”&$G$1))),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))),COLUMNS($A:A))
可转换为:
INDEX(Sheet1!A2:F10,SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;FALSE;FALSE},1),COLUMNS($A:A))
转换为:
INDEX(Sheet1!A2:F10,1,COLUMNS($A:A))
COLUMNS($A:A)使得公式向右拖放时,可以为INDEX函数的参数column_num提供合适的值。在单元格A2中,COLUMNS($A:A)的值等于1,因此公式转换为:
INDEX(Sheet1!A2:F10,1,1)
即工作表Sheet1中单元格A2的值。
猜你喜欢
- Word是很多用户都在使用的Word文档编辑软件。有时由于格式要求,需要使word首页不显示页码,从第二页开始显示页码1,这种情况要怎么设置
- 我们在使用Win10系统更新后速度慢怎么办?很多人在Win10系统更新之后速度变慢了,对于这个问题小编也是带来了解决的办法,如果有需要的用户
- Word文档字间距怎么调?当我们使用Word文档编辑好文字之后,为了文档的观看起来的整体美观与舒适性,我们会对其字间距进行调整,那么应该如何
- Word模板可存储样式 、自动图文集 词条、“自动更正”词条、宏 、工具栏 、自定义菜单设置和快捷键 。可加载模板并使之成为共用模板 。加载
- 在Excel中隐藏行或列, 通常可以通过格式菜单中的行或列选项中的隐藏来实现, 或者是选中行号或列标后通过鼠标右键的快捷菜单来完成, 对于分
- 如何通过用Word制做灯笼中国的灯笼又统称为灯彩,起源于1800多年前的西汉时期,每年的农历正月十五元宵节前后,人们都挂起象征团圆意义的红灯
- 有时从网上下载了一些文本,发现其中的标点都是半角西文的。如果是逗号、句号倒也好说,在word中弄个“查找并替换”也就成了,惟独西文中没有前引
- Acrobat怎么使用朗读功能?Acrobat中有朗读功能,该怎么使用朗读功能和停用朗读呢?下面我们就来看看详细的教程,很简单,需要的朋友可
- 许多用户在使用电脑的时候都会查看一些电脑的特殊设置,甚至会针对自己的使用需求修改UEFI电脑的配置。但是不少小伙伴最近在使用电脑的时候却找不
- 我在word中执行“保存”命令进行保存时,Word会自动将文档的保存位置设为系统默认的位置,其实我们可以根据需要指定word文档的保存位置,
- Win10注册表已被管理员禁用怎么办?有用户开启电脑的某些注册表时,电脑却弹出了提示,显示这个注册表被管理员禁用了。那么如何去重新开启注册表
- 我们在使用这款操作系统的过程中,会看到电脑桌面的底部有一个任务栏,在任务栏中显示了很多常用应用的图标。很多朋友也想将联系人图标显示在任务栏中
- win10桌面图标可以设置快捷键,然后可以轻松使用快捷键打开,非常方便快捷,设置简单,但是很多用户没用过,用起来很好。我们来看看详细的设置方
- 使用Excel录入数据的时候我们通常使用下拉列表来限定输入的数据,那么Excel怎么制作下拉列表?下面小编就为大家介绍制作下拉列表方法,不会
- win10邮箱中许多合作伙伴使用的邮箱非常方便。许多新用户可能不熟悉此邮箱,不知道如何设置它。下面是关于如何设置邮箱的详细教程。win10邮
- word除了能进行文字处理,还有翻译的功能,可以将整篇文档进行简繁转换,进行字数统计,对比两个文档中的差别,对内容进行批注,保护文档不被更改
- 小编在这教大家使用WPS文字用“页眉和页脚”功能去除Word文档中的水印废话不多说,直接上GIF图:
- Win10无法设置默认保存位置怎么办?Win10系统下载东西都会有一个默认保存位置,防止用户下载文件之后没有设置保存位置导致文件无法下载或下
- 用了这么多年的Word,你是不是经常还被Word小问题搞定心神不宁呢?本期Word小编与大家分享几个常见的问题。1、不规则单元格的合并如下图
- [1] 鼠标选中文本,右键单击,选择【复制】。 [2] 切换到【开始】选项卡,在【剪贴板】中,单击【粘贴】按钮的下