excel万金油按日期查询(EXCEL中的万金油公式)

在职场应用中,EXCEL有一些函数或公式的固定用法是需要我们来掌握的,我来为大家科普一下关于excel万金油按日期查询?下面希望有你要的答案,我们一起来看看吧!

excel万金油按日期查询(EXCEL中的万金油公式)

excel万金油按日期查询

在职场应用中,EXCEL有一些函数或公式的固定用法是需要我们来掌握的。

今天给大家带来的是被称为"万金油"的公式,如果你还没掌握它,都不好意思说会EXCEL。它确实给我们工作带来很大的便利,所以是我们必须要掌握的公式之一。

这个公式是一个嵌套公式,它是INDEX SMALL IF组合

在详解VLOOKUP函数一对多查找,思维转变,难题不再难中讲解了VLOOKUP函数的一对多查找,如果掌握了INDEX SMALL IF组合,用辅助列的做法会让我们嫌弃的。

下面我们来看套对组合的两个典型应用,学习一下这套组合的具体用法。


一、一对多查找

如下图,我们要查找某个部门的员工分别有哪几个人?

在F3单元格输入公式:

=IFERROR(INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$E$3,ROW($1:$8),4^8),ROW(1:1))),"")

CTRL SHIFT ENTER三键结束公式输入,双击向下填充。

公式解析:

IF($A$2:$A$9=$E$3,ROW($1:$8),4^8)

第1个参数是来判断$A$2:$A$9中的内容是否和A3单元格的值相等,相等的话返回TRUE,不相等的话返回FALSE,组成8行1列的数组{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}。

ROW函数是返回一个引用的行号,例如ROW(A1)返回1,ROW(A2)返回2,ROW(1:1)是对一行的引用,返回1,ROW(1:8)是1至8行的引用,返回是一个8行1列的数组{1;2;3;4;5;6;7;8}。

4^8它在这里主要是代表尽可能大的数。4^8是4的8次幂,结果是65536,它是EXCEL2003最大的行数,2007开始最大行数是4的10次幂,也就是1048576。INDEX函数应用来说,它不可能超过EXCEL最大行数,所以在EXCEL2003版本才会出现4^8这种用法。4^8熟练掌握EXCEL比较早的EXCEL大神的用法,版本的升级但是习惯没变,其他人在学习的时候也沿用了这种用法,本例中我们把4^8改为9或者更大的数,一样是可行的。

整个公式来说,如果第1个参数为TRUE时,返回对应的行号,否则返回65536,它们组成8行1列的数组{65536;65536;3;65536;5;65536;65536;8}。

SMALL(IF($A$2:$A$9=$E$3,ROW($1:$8),4^8),ROW(1:1))

SMALL函数是在一列数值中从小到大的顺序返回第N大的值,它的用法:

SMALL(数值区域或数组,返回第几大的值)

IF($A$2:$A$9=$E$3,ROW($1:$8),4^8)返回8行1列的数组,然后我们通过ROW函数作为SMALL函数的第2个参数,向下拖动公式时,分别返回第1个参数中第1大、第2大……的值。

INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$E$3,ROW($1:$8),4^8),ROW(1:1)))

INDEX函数在这里是它的基本用法,它是返回某个区域中第N行第M列的值,它的用法:

INDEX(要查找的区域,返回第N行的值,返回第M列的值)

这个公式中,SMALL公式的结果是INDEX函数的第2个参数,也就是要查找B2:B9区域中第N行的值,本例中的公式我们向下拖动时,分别要查找第3、5、8、65536、65536、65536行的值。

如果INDEX第2个参数或第3个参数大于查找区域中最大的行数或列数时,则返回错误值#REF。

IFERROR函数由两个参数组成,如果第1个参数是错误值时,则返回第2个参数的值,本例中如果INDEX函数出现错误值,我们要求返回空值("")。

对于初步掌握这个组合的人来说,一些细节还是需要注意的,熟练掌握的人可以略过且随意使用:

    是INDEX函数第1个参数的行的范围和IF函数第1个参数的行的范围尽量要保持一致,如A2:A9和B2:B9都是2至9行,虽然范围不一致有IFERROR函数会把错误值处理,但是我们一开始要保持良好的习惯。

    IF函数第2个参数对行数的引用,是第一个参数数据区域范围的总行数,如本例中IF函数第1个参数数据区域范围A2:A9,我们用ROW(1:8),如果是A3:A10,那么我们需要用ROW(1:9)。

    4^8可以改为尽可能大的任意一个数,这个数值只要大于IF函数第1个的最大行数即可。当然是保证在前述那一条规范的基础上,如果第一条不规范或者怕自己使用错误,可以用4^8或4^10。

    一定要注意绝对引用、相对引用和混合引用的应用。

    一定要注意按CTRL SHIFT ENTER三键结束公式输入。


二、查找不重复值

查找不重复值也是INDEX SMALL IF组合的一种经典用法,如下图我们要查找有哪几个部门。

在E2单元格中输入公式:

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(MATCH($A$2:$A$9,$A$2:$A$9,0)=ROW($1:$8),ROW($1:$8),4^8),ROW(1:1))),"")

CTRL SHIFT ENTER三键结束公式输入,双击向下填充。

公式解析:

MATCH($A$2:$A$9,$A$2:$A$9,0)=ROW($1:$8)

MATCH函数是返回值在指定区域中的位置,在这里我们只讲用到的它的一个用法:

MATCH(要找谁,在哪找,0)

如果查找区域中有多个相同的内容,只返回自上而下找到的第1个内容的位置。

MATCH($A$2:$A$9,$A$2:$A$9,0)是分别找A2:A9每个单元格中的数据在A2:A9的位置,返回8行1列的结果{1;2;3;1;3;2;2;3}。

MATCH($A$2:$A$9,$A$2:$A$9,0)=ROW($1:$8)是MATCH函数返回的结果与8行1列的序号序列对比,如果相等返回TRUE,如果不相等返回FALSE,形成8行1列的结果{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}。

其它内容不再解析,和前述的解析一样,最终结果是只返回第1次出现的部门名称,最终生成不重复的部门名称。

INDEX SMALL IF组合是职场中非常实用的一个公式组合,没掌握的可以用固定的用法套用进去,然后慢慢理解它的运行原理,直到把它完全掌握。

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页