【Excel函式】如何將符合條件的行提取到另一工作表並自動更新

在工作中，我們有時會遇到將彙總表中的資料提取到其他工作表的情形。當然我們可以利用Excel的篩選功能，將滿足條件的資料篩選後複製貼上到其他工作表。但是當彙總表中的資料有新增或修改時，我們就要重複篩選複製貼上操作。本次教程將向大家介紹透過函式提取滿足條件的資料，並且當資料更新時，可以自動更新提取的資料，而不必重複操作。

一、問題描述

如下圖所示，“成績彙總表”為1班和2班的語文及數學成績彙總表。現在希望完成以下要求：

1、將“成績彙總表”中1班語文成績自動提取到工作表“1班語文成績”；

2、將“成績彙總表”中1班數學成績自動提取到工作表“1班數學成績”；

3、將“成績彙總表”中2班語文成績自動提取到工作表“2班語文成績”；

4、將“成績彙總表”中2班數學成績自動提取到工作表“2班數學成績”；

5、後續在“成績彙總表”中新增1班和2班其他同學的語文及數學成績，新增的成績記錄能自動提取到對應的分表中。

二、解決方法

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）），“”）

三、公式解析

公式解析（以分表“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函式用於遮蔽錯誤值。當所有符合條件的結果均已查詢並返回到對應分表時，返回空值。