1

职场实例

今天群里小伙伴咨询了这样的一个Excel技巧类问题：

如何返回符合指定条件的所有记录？我们来看一下具体的案例实况。

如下图所示：

左侧为业务员每日的销售量明细数据，A列为日期列，B列为姓名列，C列为销售量数据列。我们想要实现这样的效果：通过切换E2单元格内的销售日期，可以在下方调出此日期内所有的业务员及其销售量数据，即我们今天讲到的返回符合指定条件的所有记录问题。

2

解题思路

今天这个问题我们需要用到：

IFERROR+INDEX+AGGREGATE+ROW四个函数嵌套搭配使用来解决，公式都是基础类常用函数，组合原理非常的简单，下面我们就来详细的分步骤讲解一下！

首先我们在E2单元格输入下面函数：

=ROW($2:$10)

回车后，再次进入公式编辑状态，全选公式按下F9键查看返回结果：

={2;3;4;5;6;7;8;9;10}

目的是用ROW函数以数组的存储形式返回2~10行的各行行号。

我们继续完善E2单元格内的函数：

=ROW($2:$10)/($A$2:$A$10=$E$2)

回车后，再次进入公式编辑状态，全选公式按下F9键查看返回结果：

={#DIV/0!;#DIV/0!;4;#DIV/0!;#DIV/0!;7;#DIV/0!;#DIV/0!;#DIV/0!}

以数据源中的行号ROW($2:$10)除以指定条件($A$2:$A$10=$E$2)，当A2:A10单元格区域中的日期等于E2单元格指定的日期时返回对应的行号，否则返回错误值#DIV/0!，目的是得到一个包含行号和错误值的内存数组结果。

我们继续完善E2单元格内的函数：

=AGGREGATE(15,6,ROW($2:$10)/($A$2:$A$10=$E$2),ROW(A1))

回车后，再次进入公式编辑状态，全选公式按下F9键查看返回结果：

={4}

AGGREGATE函数第一参数、第二参数、第四参数分别使用15、6和ROW(A1)，表示使用SMALL函数的计算规则，在该内存数组中忽略错误值依次提取出第1至第N个最小行号。

我们看到E5单元格：

{#DIV/0!;#DIV/0!;4;#DIV/0!;#DIV/0!;7;#DIV/0!;#DIV/0!;#DIV/0!}该内存中忽略错误值提取出第1个最小行号是4。

AGGREGATE函数用法与SUBTOTAL函数类似，但在功能上比SUBTOTAL函数更加强大，不仅可以实现诸如SUM、SMALL、COUNT、LARGE、MAX等19个函数的功能，而且还可以忽略隐藏行、错误值、空值等，并且支持常量数组。

AGGREGATE函数用于返回列表或数据库中的合计。

AGGREGATE函数可将不同的聚合函数应用于列表或数据库，并提供忽略隐藏行和错误值的选项。

AGGREGATE函数有两种语法结构有2种：

①引用形式：

AGGREGATE(function_num，options，ref1，[ref2], …)

②数组形式：

AGGREGATE(function_num，options，array，[k])

参数说明：

function_num：

一个介于 1 到 19 之间的数字，指定要使用的函数。本例中用数字“9”代表SUM求和函数。

options：

一个数值，决定在函数的计算区域内要忽略哪些值。本例中用数字“7”代表“忽略隐藏行和错误值”。

ref1：

函数的第一个数值参数，这些函数具有要计算聚合值的多个数值参数。ref1 可以是一个数组或数组公式，也可以是对要为其计算聚合值的单元格区域的引用。

ref2，…：

选填，要计算聚合值的 2 至 253 个数值参数。ref2 是某些函数必需的第二个参数。例如SMALL函数 SMALL(array,k)，第二参数k为返回值在数组或数据单元格区域中的位置（从小到大排）。

我们再次观察看到E6单元格：

=AGGREGATE(15,6,ROW($2:$10)/($A$2:$A$10=$E$2),ROW(A2))

={7}

{#DIV/0!;#DIV/0!;4;#DIV/0!;#DIV/0!;7;#DIV/0!;#DIV/0!;#DIV/0!}该内存中忽略错误值提取出第2个最小行号是7。

我们继续完善E2单元格内的函数：

=INDEX(B:B,AGGREGATE(15,6,ROW($2:$10)/($A$2:$A$10=$E$2),ROW(A1)))

使用INDEX函数，根据AGGREGATE函数的的计算结果，在B列中提取出对应位置的内容。

当公式向下复制的行数超过符合指定条件的记录数时，AGGREGATE函数会返回错误值#NUM!，最后使用IFERROR函数，将错误值显示为空文本“”，使单元格中看起来为空白：

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($2:$10)/($A$2:$A$10=$E$2),ROW(A1))),"")

我们最终得到了如下的效果：