当前所在位置: 首页 > 儿童教育 > 正文

excel技巧:在主工作表中汇总多个工作表中满足条件的值

2023-01-26 餐饮美食网 【 字体:

我们可能熟悉使用INDEX、SMALL等在给定单列或单行数组的情况下,返回满足一个或多个条件的值的列表。这是一项标准的公式技术。

在《Excel公式练习32:将包含空单元格的多行多列单元格区域转换成单独的列并去掉空单元格》中,我们述了一种方法,给定由多个列组成的单元格区域,从该区域返回由所有非空单元格组成的单个列。可以很容易地验证,在该公式中的单个条件可以扩展到多个条件,因此,我们现在有了从一维数组和二维数组中生成单列列表的方法。

那么,可以更进一步吗?“三维”是经常应用于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的值。

阅读全文
相关推荐

如何自制蛋挞

如何自制蛋挞
1、倒入低筋面、黄油和水,揉成面团状,藏一小时后取出,抹上黄油卷起,包上保鲜膜冷藏半小时。2、取出,将面团切成一厘米的面团,放入模具内,用力按压,中间稍薄,外缘要比模具高,将蛋挞液倒入蛋挞皮中,放入烤箱里烤30分钟即可。

肉火烧面怎么和面

肉火烧面怎么和面
1、首先用温水把酵母融开,加入面粉,用手把面粉揉成非常软的面团,可以放一点熬好的猪油在面团里面。2、然后放在温暖的地方,盖上保鲜膜发酵至2倍大,发好的面团用手插入不回缩,就说明面团发酵好了。

奶茶几分糖好喝

奶茶几分糖好喝
1、不同的人对奶茶的口感要求是不一样的,一般来说,奶茶有三分糖、五分糖、七分糖三种不同的口味。2、女生一般都喜欢喝甜一点的,而男生则喜欢喝不那么甜的,七分糖的奶茶最好喝。

过桥米线是哪里的?

过桥米线是哪里的?
过桥米线是来自云南省滇南地区的一种有名特色小吃。过桥米线最早是在清朝就已经出现,距离现在至少有一百多年的历史啦!起源于建水县东城外锁龙桥西侧的鸡市街头处,有建水的特产草芽、地椒作配料,风味独特而远近闻名。深受广大美食爱好者的喜欢,不少人为此专门长途跋涉,只为尝

面霜和乳液的区别

面霜和乳液的区别
面霜和乳液的区别,相较而言乳液的水分含量要比面霜高,乳液质地要轻薄一些,乳液主要作用是保湿,滋润可以隔离外界干燥的气候,面霜既可保湿,又可美白,还能抗衰老,乳液的吸收快一些,而面霜吸收比较慢一些,因为液体的吸收速度都比较快。

怎么摘隐形眼镜

怎么摘隐形眼镜
在摘隐形眼镜之前,先用洗手液将手清洗干净,以免将细菌带入眼睛内,对着镜子,用右手中指轻拉眼睛下眼睑,左手中指轻拉眼睛上眼睑,让黑色眼球暴露在空气中,用右手食指和拇指轻触镜片的两边缘部分,向中间推使镜片拱起,再用两手指轻轻捏出镜片即可。

粉饼和散粉的区别

粉饼和散粉的区别
粉饼是呈压缩固体状态,多呈圆形或者方形,散粉则是细腻的粉末状,粉饼遮瑕力会比较强一些,可以湿用做粉底,或者用来补妆,而散粉则是定妆的效果,粉饼通常用在底妆的第一步,而散粉通常用在底妆最后一步。

冷烫和热烫的区别

冷烫和热烫的区别
冷烫和热烫的区别:冷烫对头发的要求是要在保温状态下才能给卷有个好的效果,而热烫是在干和湿的情况下都行,热烫烫出来的头发比较自然明显而有弹力,冷烫的头发风干后基本看不出来,并且发质会有点干。

高品质香水如何鉴别?用三步就可以解决

高品质香水如何鉴别?用三步就可以解决
1、看色泽以天然香料调制而成的高级香水,都有它本来的颜色,且大都是琥珀色或褐色,看起来很像宝石,比如,从茉莉、玫瑰或水仙等天然鲜花中所萃取的精油都呈黄色、褐白或绿褐色;此外,香水中所添加的魅惑香气物性香料也是褐色,苔类中的橡树苔是绿色,从树根或树根脂类中萃取的

如何去除黑眼圈

如何去除黑眼圈
去除黑眼圈首先可以用热毛巾敷眼,然后再用冷毛巾敷,十分钟就能让黑眼圈淡化的不那么严重,其次将去壳后的鸡蛋用无菌纱布包裹住,敷于眼部轻轻转动,可以急救去除黑眼圈,另外抹完眼霜后,用双手顺时针按摩,可以促进血液循环消除黑眼圈。
本文Tag