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

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

引用

CSDN

1.

https://blog.csdn.net/2401_83316378/article/details/139654352

在Excel中，我们经常需要在主工作表中汇总多个工作表中满足特定条件的值。本文将介绍一种仅使用公式（不使用VBA）的方法，实现这一需求。

问题背景

假设我们有三个工作表（Sheet1、Sheet2、Sheet3），每个工作表都有相同的数据布局。我们的目标是在一个主工作表（Master）中汇总所有工作表中列D值为"Y"的数据。

示例数据

以下是三个工作表的数据示例：

工作表Sheet1：

图1：工作表Sheet1

工作表Sheet2：

图2：工作表Sheet2

工作表Sheet3：

图3：工作表Sheet3

解决方案

定义名称

首先，我们需要定义两个名称：

• 名称：Sheets
  引用位置：={“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中输入以下数组公式（需要按Ctrl+Shift+Enter确认）：

=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单元格公式解析

单元格G1中的公式用于确定返回结果的数量：

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))

这个公式通过INDIRECT函数动态生成多个工作表的引用，并使用COUNTIF函数统计每个工作表中列D值为"Y"的单元格数量，最后使用SUMPRODUCT函数求和。

A2单元格主公式解析

主公式的核心是通过动态生成的汇总小计数组（Arry1）来确定当前行应该引用哪个工作表的数据。具体步骤如下：

1. 使用MMULT函数生成动态汇总小计数组：

   =MMULT(0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets))))),TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”)))

2. 使用INDEX和MATCH函数确定当前行应该引用的工作表：

   INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))

3. 使用SMALL函数和IF函数筛选出满足条件的行号：

   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)))

4. 最后使用INDEX函数返回具体的数据单元格。

通过上述步骤，我们可以实现跨多个工作表的数据汇总，且不需要使用VBA代码。