前言:
当前小伙伴们对“mysqlfunction返回”大约比较关注,我们都想要了解一些“mysqlfunction返回”的相关资讯。那么小编也在网络上汇集了一些有关“mysqlfunction返回””的相关文章,希望姐妹们能喜欢,看官们一起来了解一下吧!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))),"")
我们最终得到了如下的效果:
标签: #mysqlfunction返回