在工作中，我们有时会遇到将汇总表中的数据提取到其他工作表的情形。当然我们可以利用Excel的筛选功能，将满足条件的数据筛选后复制粘贴到其他工作表。但是当汇总表中的数据有新增或修改时，我们就要重复筛选复制粘贴操作。本次教程将向大家介绍通过函数提取满足条件的数据，并且当数据更新时，可以自动更新提取的数据，而不必重复操作。

01

问题描述

如下图所示，“成绩汇总表”为1班和2班的语文及数学成绩汇总表。现在希望完成以下要求：

1、将“成绩汇总表”中1班语文成绩自动提取到工作表“1班语文成绩”；

2、将“成绩汇总表”中1班数学成绩自动提取到工作表“1班数学成绩”；

3、将“成绩汇总表”中2班语文成绩自动提取到工作表“2班语文成绩”；

4、将“成绩汇总表”中2班数学成绩自动提取到工作表“2班数学成绩”；

5、当在“成绩汇总表”中新增1班和2班其他同学的语文及数学成绩，新增的成绩记录能自动提取到对应的分表中。

02

解决方法

1、定义名称。将“成绩汇总表”中的单元格区域定义名称，定义的名称如下图所示。

为了完成要求5，即当成绩汇总表新增数据时，各分表能自动提取到新增的数据，定义的名称所引用位置远超过当前的数据区域。比如名称“班级”引用的单元格区域是A2:A10000，远超过当前“班级”列的数据区域A2:A17。

2、在“1班语文成绩”工作表A2单元格输入以下公式：

=IFERROR(INDEX(成绩汇总,SMALL(IF((班级="1班")*(科目="语文"),ROW(班级)-1),ROW(A1)),COLUMN(A1)),"")

由于该公式是数组公式，因此需按Ctrl+Shift+Enter完成公式的输入。

在A2单元格输入公式后，拖动填充柄将公式向右向下复制到其他单元格。提取的数据结果如下图所示：

3、将该公式分别复制到其他分表中，并相应修改if函数中的判断条件。

在“1班数学成绩”分表的A2单元格公式为

=IFERROR(INDEX(成绩汇总,SMALL(IF((班级="1班")*(科目="数学"),ROW(班级)-1),ROW(A1)),COLUMN(A1)),"")

在“2班语文成绩”分表的A2单元格公式为

=IFERROR(INDEX(成绩汇总,SMALL(IF((班级="2班")*(科目="语文"),ROW(班级)-1),ROW(A1)),COLUMN(A1)),"")

在“2班数学成绩”分表的A2单元格公式为

=IFERROR(INDEX(成绩汇总,SMALL(IF((班级="2班")*(科目="数学"),ROW(班级)-1),ROW(A1)),COLUMN(A1)),"")

03

公式解析

公式解析（以分表“1班语文成绩”A2单元格的公式进行说明）

①IF函数用于获得满足条件的数据所在的行号。IF((班级="1班")*(科目="语文"),ROW(班级)-1)表示在“成绩汇总表”中，当“班级”为“1班”，“科目”为“语文”时，返回数据所在行号-1，否则返回FALSE。该IF公式生成的结果为

{False;False;3;False;False;6;False;False;False;False;False;12;False;False;15;False}。

②ROW函数用于返回满足判断条件的数据所在行号，之所以减1，是为了获得该数据在定义的名称“成绩汇总”（单元格区域A2:A10000）中所在的行号。比如“1班”的“亚瑟”在工作表中位于第4行数据，ROW函数的结果是4，但是该行数据相对于定义的名称“成绩汇总”则位于第3行。

③SMALL函数用于返回IF函数生成的数组的第k个最小值。比如ROW(A1)=1,则SMALL(IF((班级="1班")*(科目="语文"),ROW(班级)-1),ROW(A1))用于返回IF函数结果的第1个最小值，即3。

④INDEX函数用于返回指定行列交叉处单元格的值，指定的行由公式IF+ROW+SMALL确定，指定的列由COLUMN函数确定。

⑤IFERROR函数用于屏蔽错误值。当所有符合条件的结果均已查找并返回到对应分表时，返回空值。

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。