excel如何满足多个工作表中某个条件的值

时间：2021-09-28 来源：互联网编辑：宝哥软件园浏览：次

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

我们讨论了一种方法，给定一个由多列组成的单元格区域，从中返回一个由所有非空单元格组成的单列。很容易验证这个公式中的一个条件可以推广到多个条件。因此，我们现在有了一种从一维数组和二维数组生成单列列表的方法。

那么，我们能走得更远吗？“三维”是一个通用术语，常用于Excel中的特定公式。这些公式不仅可以对单列或单行进行操作，还可以对多列或多行组成的单元格区域进行操作，还可以对多张工作表进行有效操作。

本文提供了一种方法，给定一个或多个具有相同布局的工作表，创建另一个“主”工作表，该工作表仅由满足特定条件的所有工作表中的数据组成。此外，仅使用公式代替VBA。

假设我们想从以下三个工作表中整理数据：

工作表1:

图1

工作表2:

图2。

工作表3:

图3。

您要创建一个主工作表，其数据来自上面三个工作表的d列中值为“y”的数据：

图4

解决办法

首先，定义以下两个名称：

名称：Sheet3。

参考位置：={“sheet 1”、“sheet2”、“sheet 3”}

名称：Arry1。

引用位置：=mmult (0(行(间接的(“1:”Counta(Sheets))))=转置(行(间接的(“1:”Counta(Sheets)))))，转置(Countif(间接的(“””Sheets))。d 2: D10)" Y "))

可以根据实际情况修改工作表列表和数据范围(D2:D10)。

在工作表母版的G1单元格中，输入以下公式：

=SUMPRODUCT(COUNTIF)(间接(“‘Sheets’”)！d 2: D10)“Y”))

在工作表母版的单元格A2中输入以下数组公式：

=IF(ROWS($1:1)$G$1，""，INDEX(INDIRECT)(' " INDEX(Sheets，MATCH(TRUE，Arry1=ROWS($1:1)，0))" '！A2:F10)，" SMALL(IF(间接)(' " INDEX(Sheets，MATCH(TRUE，Arry1=ROWS($1:1)，0))" '！d 2:d 10)=“Y”、ROW(间接)(“1:”$ G $ 1))、IFERROR(1 ROWS($1:1)-LOOKUP(ROWS($ 1:1)、1 Arry1)、ROWS($ 1:1)))、COLUMNS($A:A))

并将公式向右下方拖放到适当的位置。

操作原理

看看单元格G1中相对简单的公式，它用于确定返回结果的数量：

=SUMPRODUCT(COUNTIF)(间接(“‘Sheets’”)！d 2: D10)“Y”))

如果您不熟悉在多个工作表中使用公式的技术，您应该使用INternet写下这个公式构造，因为这实际上是我们执行此类计算的唯一方法。上述公式转换为：

=SUMPRODUCT(COUNTIF(INDIRECT(“”{“sheet 1””、“Sheet2”、“sheet 3”}”)！d 2: D10)“Y”))

然后，连接代表工作表名称的文本字符串的两端，后面是工作表区域(D2:D10)，前面用单个撇号连接。虽然当工作表的名称不包含空格时，这是不必要的，但它会更好，更通用。这样，公式转换为：

=SUMPRODUCT(COUNTIF)(间接({“”sheet 1！d 2:d 10”,“sheet 2 ”!d 2:d 10”,“sheet 3 ”!D2:D10})，“Y”))

由于COUNTIF函数可以操作三维单元格区域，而SUMPRODUCT函数提供了必要的强制转换，因此间接函数返回一组单元格引用，而不仅仅是一个，公式转换为：

=SUMPRODUCT({3，2，1})

数组的值由3、2和1组成，这与工作表Sheet1、Sheet2和Sheet3的d列中的数字“y”一致。这个公式的最终结果是：

六

接下来，看看单元格A2中的主公式：

IF函数的前半部分很简单，如果拖放的行数超过了可能的结果数，那么它就是空的。

公式中使用了一个已定义的名称Arry1:

=MMULT(0 (ROW(间接)(“1:”COUNTA(Sheets)))=转置(ROW(间接)(“1:”COUNTA(Sheets))))、转置(COUNTIF(间接)(“‘Sheets’”)！d 2: D10)" Y "))

这种公式构造可以有效地动态生成汇总小计，使用subota/OFFSET函数的标准组合是一种替代方法。

依次看传递给MMULT函数的数组。首先是：

0(行(间接)(“1:”COUNTA(Sheets)))=转置(行(间接)(“1:”COUNTA(Sheets))))

转换为：

0(行(间接)(“1:”3))=转置(行(间接)(“1:”3)))

转换为：

0 ({1;2;3 }=转置({ 1；2;3}))

转换为：

0 ({1;2;3}={1,2,3})

比较两个正交数组，一个是3行1列，另一个是1行3列，得到一个3行3列的数组，由9个真/假值组成：

0({真，假，假；真、真、假；真，真，真})

转换为1/0值的数组：

{1,0,0;1,1,0;1,1,1}

传递给MMULT函数的另一个数组是：

转置(COUNTIF)(间接(“‘Sheets’”)！d 2: D10)“Y”))

转换为：

转置({3，2，1})

获取：

{3;2;1}

因此，MMULT功能变为：

MMULT({1，0，0；1,1,0;1,1,1},{3;2;1})

结果是：

{3;5;6}

用Arry1的值替换主公式中对应的部分，首先看一下：

间接(“‘INDEX(Sheets，MATCH(TRUE，Arry1=ROWS($1:1)，0))”！A2:F10”)

转换为：

间接(“”)索引(工作表，匹配(真，{ 3；5;6}=1,0))"'!A2:F10”)

转换为：

间接(“‘索引(工作表，1)”)！A2:F10”)

转换为：

间接(“”“Sheet1”“”！A2:F10”)

转换为：

间接(“Sheet1！A2:F10”)

因此，您可以看到对于A2中的公式，将返回Sheet1。例如，如果解构单元格A5中的公式，公式中的MATCH构造将如下所示：

匹配(真，数组1=行($1:4)，0)

唯一的变化是引用ROWS($1:4)而不是rows ($133601)，结果转换为：

匹配(真，{ 3；5;6}=4,0)

获取2，它将引用工作表第2页。

其实这项技术的核心是：通过生成一个动态汇总小计数量的数组，该数组由每个工作表中的限定行组成(即d列中的值为“y”)，然后将公式所在单元格的相对行与数组进行比较，就可以有效地确定公式所在行中要指定的工作表。因此，主公式中的子句：

INDEX(间接(“”)INDEX(Sheets，MATCH(TRUE，Arry1=ROWS($1:1)，0))”！A2:F10)，" SMALL(IF(间接)(' " INDEX(Sheets，MATCH(TRUE，Arry1=ROWS($1:1)，0))" '！d 2:d 10)=“Y”、ROW(间接)(“1:”$ G $ 1))、IFERROR(1 ROWS($1:1)-LOOKUP(ROWS($ 1:1)、1 Arry1)、ROWS($ 1:1))、COLUMNS($A:A))

可转换为：

INDEX(Sheet1！A2:F10，小型(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(间接(" 1:"$G$1)))

转换为：

IF({“Y”；0;“Y”；“Y”；0;0;0;0;0 }=“Y”，ROW(间接(“1:”6))

转换为：

中频({真；假；真；真；假；假；假；假；FALSE}，{ 1；2;3;4;5;6})

获取：

{1;假；3;4;假；假；假；假；FALSE}

在这里，您可以看到数组中的1、3和4对应于工作表1的d列中“y”的相对行号。我们现在需要做的是将数组传递给SMALL函数，并确定参数k，这实际上是整个解决方案中最困难的部分，因为它不同于将这种结构应用于由单列组成的数组(例如，在这种情况下，对于连续的行，参数k可以简单地增加1)，这里必须考虑的是，当Sheet2需要返回值，Sheet3需要返回值时，参数将被“重置”为1。为此，请在此处使用：

iforr(1 ROWS($1:1)-LOOKUP(ROWS($ 1:1)，1 Arry1)，ROWS($ 1:1))

为了理解这个公式构造是如何工作的，我们可以暂时将它作为一个独立的数组公式使用，将其输入到一个单元格中，然后将其拖放下来以理解生成的值。实际上，将公式从A2向下拖动到A7可以转换为：

iforr(1 {1，2，3，4，5，6}-LOOKUP({1，2，3，4，5，6}，1 Arry1)，{ 1，2，3，4，5，6})

转换为：

iferor(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}-{ #不适用，#不适用，#不适用，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的值，即匹配工作表1中的第1、2、3小行，匹配工作表2中的第1、2小行，匹配工作表3中的第1小行。