excel如何在多个工作表满足条件的值
发布时间:2023-05-18 14:02:41
我们可能熟悉使用INDEX、SMALL等在给定单列或单行数组的情况下,返回满足一个或多个条件的值的列表。这是一项标准的公式技术。
我们之前讲述了一种方法,给定由多个列组成的单元格区域,从该区域返回由所有非空单元格组成的单个列。可以很容易地验证,在该公式中的单个条件可以扩展到多个条件,因此,我们现在有了从一维数组和二维数组中生成单列列表的方法。
那么,可以更进一步吗?“三维”是经常应用于Excel * 定公式的通用术语,这些公式不仅可以对单列或单行进行操作,也可以对由多列或多行组成的单元格区域进行操作,还可以有效地对多个工作表进行操作。
本文提供了一种方法,在给定一个或多个相同布局的工作表的情况下,可以创建另一个“主”工作表,该工作表仅由满足特定条件的所有工作表中的数据组成。并且,这里不使用VBA,仅使用公式。
假设我们想从下面三个工作表中整理数据:
工作表Sheet1:
图1
工作表Sheet2:
图2
工作表Sheet3:
图3
想要创建一个主工作表Master,其数据来源于上面三个工作表中列D中的值为“Y”的数据:
图4
解决方案
首先,定义下面两个名称:
名称: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的值。
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- Excel数据透视表技巧——列表法创建动态源透视表
- 你是否因为别人发你一份乱糟糟的数据表格而恼羞成怒,或是因为数据格式的问题被领导责骂而心烦意乱?一份好的数据表格不仅仅要保证数据的准确性及其完
- 有用户在使用Win10的过程中遇到了explorer.exe的情况,这种情况是可以进行修复的,那么Win10explorer.exe怎么修复
- 网络的便利就是它的四通八达属性,只要我们之间建立了连接,就可以互相访问,那么远程连接可以让我们控制远在千里之外的电脑,又方便我们远程处理问题
- 接下来一起来感受一下,到底有哪些解决办法吧!网上的方法:方法一:HPB235钢筋符号:在word中先输入E000,然后按住ALT键,同时按下
- 如何使用Word 2019中的字段添加动态元素?Word使您可以将动态元素添加到文档中。与通常编写的文本不同,动态文本会更改以反映许多因素。
- 选中一部分数据时候,excel的状态栏总会显示一部分的数据统计信息,例如数据项本数量,平均值等等。那如何让状态栏显示更多的内容呢?下面小编就
- 在用Word编辑的数学试卷中,会有大量的公式存在。如果在文档编辑完成后,需要重新调整字号的大小,那么文档中的这些公式怎么办呢?通常情况下,W
- 其实在Word中字符的间距是很多人都特别不注重的,但是却又是十分重要的,因为如果设置的不好的,排版就会十分的难看。所以小编在这里就要来教一教
- 在使用Excel的过程中,我们往往先把表格根据要求做好,这时单元格的列宽已经固定好了,但有的列字符数目不等。比如在录入家庭住址时,有的家庭住
- 今天的word 2013教程是中英文的在线翻译的方法。英语现在很普遍了,如果你的文档需要把中文翻译成英语或者你的文档需要把英语翻译成中文,那
- 大家在使用Excel 处理数据时,有时单元格会显示出"#VALUE!”这样的信息,打印时不太整齐美观大家在使用Excel 处理数据
- 相信对Word文档比较熟悉的小伙伴都知道,我们在文档中用两个英文状态下的*括住文本,其中的文本就会加粗显示,但是有时候会发现没有效果,那小伙
- 我们在word 2013文档中创建SmartArt图形后,可以根据需要对文档中的SmartArt图形格式进行设置,具体操作如下:1、显示/隐
- 最近有朋友问我了在使用word过程中,有时会出现word下划线,怎么去掉这个下划线呢?今天小编就为大家整理了解决方法,一起看看吧word下划
- Excel作为办公软件中常用软件,其快捷键也颇多,那么excel的快捷键有哪些?如何使用这些快捷键?下面小编就为大家详细介绍一下,一起来看看
- 当我们使用win10操作系统的时候,一些同伴希望安装最新的操作系统win101903。因此,除了在系统设置中进行更新和安全的直接升级外,该版
- Word九宫格图片怎么做呢?很多用户对此还不是很清楚,小编这里就给大家带来有关Word九宫格图片怎么做的回答,希望能够帮助大家了解。1、首先
- Excel2010怎么制作柱形图如果不允许添加辅助列,单独用簇状柱形图或堆积柱形图无法完成,本文介绍一种不用辅助列在双坐标轴中并列显示两种子
- word2010中怎么使用画笔修改表格线条样式?word2010中想要修改表格的线条,该怎么使用画笔修改呢?下面我们就来看看使用画笔工具修改