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组合是职场中非常实用的一个公式组合，没掌握的可以用固定的用法套用进去，然后慢慢理解它的运行原理，直到把它完全掌握。