穿越时间Excel升级之路连载10：Index函数Offset函数提取不重复值

咔片PPT · AI自动生成演示文稿，模板丰富、排版精美讯飞智文 · 一键生成PPT和Word，高效应对学习与办公

跟我一起，穿越时间！

在上一期的连载中，我们通过MATCH、COUNTIF、SUMPRODUCT等函数的应用，实现了判断是否存在重复值以及对不重复值进行计数，今天我们则是要更进一步，一是要对满足条件的不重复值计数，二是要把不重复值提取出来。

今天不仅会涉及上期连载内3个函数的综合应用，更会涉及（Index、Small、IF、Match、Row等5个函数的综合运用。你准备好挑战升级了吗？

如果没有看过前期连载的可以点击头像或链接阅读：

穿越时间·Excel升级之路连载6：神级函数SUMPRODUCT的惊鸿一瞥

穿越时间Excel升级之路连载7：SUMPRODUCT函数挑战Excel条件计数

穿越时间Excel升级之路连载8：SUMPRODUCT函数决胜Excel条件求和

穿越时间Excel升级之路连载9:MATCH函数及SUMPRODUCT不重复值计数

今天我们走到Excel升级之路连载10：Index函数、Offset函数提取不重复值。

一、统计符合条件的不重复值的个数

1、复习一下

在上面的表格中，我们可以看到不同门派的不同销售人员都有成功开单的记录，那么问题一来了：问一共有多少人成功开单了？

分析：这实际上就是求E列身份证号或者D列销售人员姓名有多少个不重复值，属于上期连载讲过的不重复值计数问题。

我们通过公式来统计D列销售人员姓名有多少个不重复值，实际应用中，因为可能有重名现象，通过身份证号或员工工号来进行判断才是最好的做法。

公式：

=COUNT(0/(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1))

按Ctrl+Shift+Enter键计算

=SUMPRODUCT(1/COUNTIF(D2:D18,D2:D18))

按Enter键计算

如果有疑问或者不理解，请详细阅读研究之前的连载。

2、增加一下条件

现在不想直接统计所有人，而是限定条件：

问题二：翠湖山庄有几人成功开单了？

即：要求门派满足“翠湖山庄”，统计D列销售人员不重复值的个数。

可以使用以下公式：

=SUM((IF(C2:C18="翠湖山庄",TRUE,FALSE))*(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1))

按Ctrl+Shift+Enter键计算。

思路分析：

门派是否满足“翠湖山庄”？我们通过IF函数(IF(C2:C18="翠湖山庄",TRUE,FALSE))判断C2:C18，返回由TRUE或FALSE构成的数组，TRUE代表门派是“翠湖山庄”；

D列销售人员是否重复？继续使用MATCH函数(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1)进行判断，返回TRUE或FALSE构成的数组，TRUE代表不重复；

然后两个数组相乘，得到由1或0构成的新数组，其中1就代表门派满足“翠湖山庄”，同时销售人员还不重复；

所以计算出1的个数即可；

我们这里不直接去数1的个数，而是对这个由1或0构成的新数组应用sum函数求和，sum会把所有的1和0相加，其结果等同于1的个数。

上面的公式理解之后，我们可以简化一下：

逻辑运算(C2:C18="翠湖山庄")可以直接代替上面的IF判断：

=SUM((C2:C18="翠湖山庄")*(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1))

上面说到，我没有直接去数1的个数，那如果用countif函数去数1的个数可不可以呢？比如写成：

=COUNTIF((C2:C18="翠湖山庄")*(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1),1)

这就是用countif函数去数最终常量数组{0;1;0;0……}中1的个数，然而Excel会提示错误！

想想原因是为什么？答案：最外层COUNTIF的第一个参数不支持常量数组。

延续刚才的思路分析：

如果我们使用神级函数SUMPRODUCT的话，问题会变得简单一些

=SUMPRODUCT((C2:C18="翠湖山庄")*1,(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1)*1)

=SUMPRODUCT(--(C2:C18="翠湖山庄"),--(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1))

=SUMPRODUCT((C2:C18="翠湖山庄")*(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1))

或者采用SUMPRODUCT和COUNTIF判断重复值的方式：

=SUMPRODUCT((C2:C18="翠湖山庄")*(1/COUNTIF(D2:D18,D2:D18)))

=SUMPRODUCT((C2:C18="翠湖山庄")*1,(1/COUNTIF(D2:D18,D2:D18)))

我们可以看到(C2:C18="翠湖山庄")可以抹去不是“翠湖山庄”的；

(1/COUNTIF(D2:D18,D2:D18))又可以处理重复值，保持最终1个人就计数为1

3、再进一步，增加更多条件试试：

问题三：翠湖山庄中有几人成功开单卖出了瑞晶镜？

答案是3个人，注意，如果只是限定门派是“翠湖山庄”，文创产品是“瑞晶镜”，再对销售人员不重复值计数，三个条件合并，通过这种公式的计算结果会是2，你知道为什么吗？

=SUMPRODUCT((C2:C18="翠湖山庄")*1,(F2:F18="瑞晶镜")*1,(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1)*1)=2

=SUMPRODUCT((C2:C18="翠湖山庄")*(F2:F18="瑞晶镜")*(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1))=2

最后限定人员重复重复时，“琼英”因为不是第一次出现会被判定FALSE，最终不会被计数，所以这里通过这种公式写法是会产生误差的。

小结：

问题二：翠湖山庄有几人成功开单了？

问题三：翠湖山庄中有几人成功开单卖出了瑞晶镜？

两个问题为什么问题二中限定门派的条件和限定人员是否重复的条件一起运算不会产生误差？

而问题三中限定门派的条件、限定文创产品的条件和限定人员是否重复的条件一起运算会产生误差？

关键在于限定门派的条件后，结果肯定会包括人员第一次卖出产品的条目；

而限定文创产品的条件后，结果中可能不是卖出这个产品的销售人员第一次卖出产品的条目，MATCH会认为人员重复得到FALSE，从而乘积时被抹去产生误差。

就像上面，琼英的确卖出瑞晶镜，但不是琼英第一次卖出产品（琼英第一次卖出产品是表格第二行的青花翎），因此(MATCH(D2:D18,D2:D18,0)=ROW(D2:D18)-1)会返回FALSE，认为琼英已经重复了。最后之所以公式结果为2，就是因为琼英没有被计算在内。

二、提取构建不重复值列表

经过上面一部分的分析，我们已经可以根据实际情况计算出符合条件的不重复值得个数，但仅有个数还不够，我还想把不重复值提取出来，注意不是复制粘贴出来，而是通过公式，直接将满足条件的不重复值提取出来，构建新的列表，那要怎么操作呢？

（一）方法一

（Index函数、Small函数、IF函数、Match函数、Row函数综合运用）

1、先来介绍一下需要用到的函数

（1）Index函数

Index函数有两种形式：数组形式和引用形式，我们使用的是数组形式，数组形式非常好理解。

INDEX(array, row_num, [column_num])

INDEX(单元格区域或数组常量,行号, [列号])

返回由行号和列号索引选中的表或数组中元素的值。

举一个例子：

=INDEX(A1:C2,2,3) 表示的就是返回数组A1:C2中第2行第3列的元素的值，结果就是6

=INDEX({1,2,3;4,5,6},2,3)的结果也是6

当然，可以省略行号或列号，这时执行数组运算后可以返回整列或整行。

=INDEX({1,2,3;4,5,6},2)按下Ctrl+Shift+Enter键后，得到数组{4,5,6}

（2）Small函数

Small函数可以用来返回数据集中的第 k 个最小值，使用此函数以返回在数据集内特定相对位置上的值。

SMALL(array,k)

例如=small({1.5,2.5,3.5,4.5,5.5,6.5,7.5,8.5},6)=6.5

表示返回数组{1.5,2.5,3.5,4.5,5.5,6.5,7.5,8.5}中第6个最小值，结果为6.5

当然，数组中的元素没有排序要求，=SMALL({2.5,1.5,3.5,8.5,5.5,6.5,7.5,4.5},6)的结果还是6.5

但是采用数组形式后，small函数可以用来排序，可以把原来的数组排序后输出：

例如

=SMALL({2.5,1.5,3.5,8.5,5.5,6.5,7.5,4.5},{1,2,3,4,5,6,7,8})的结果为

{1.5,2.5,3.5,4.5,5.5,6.5,7.5,8.5}

如果 k ≤ 0 或 k 超过数据点数，则 SMALL函数返回#NUM!错误值

（3）IF函数、MATCH函数、ROW函数之前都介绍过，这里就不再赘述了。

2、提前选择输出区域的做法：

提取所有不重复的销售人员的姓名：

选中AH2:AH18

=INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW($D$2:$D$18)-1))

按Ctrl+Shift+Enter执行数组运算即可

分析：

原理就是先通过match函数找到不重复人员，构成由TRUE或FALSE组成的数组，TRUE为不重复人员；

然后利用if函数，将由TRUE或FALSE组成的数组转化为不重复人员的行号，TRUE转换为行号，FALSE仍然会被处理为FALSE（if函数，判断为真返回第2个参数，判断为假，返回第3个参数，第3个参数省略时，仍返回FALSE）；

然后利用small函数对上面的数组返回第1个最小值，返回第2个最小值……，实质为返回了不重复人员所在的行号，当超出后，会显示#NUM!

最后利用index函数从D列中取值，行号就是上述small函数的返回值数组。

至于出现的ROW(…)-1这种问题，具体减几要根据实际的区域来决定。

上面我们选中的是AH2:AH18，公式可以写为以下两种形式：

=INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW($D$2:$D$18)-1)) 按Ctrl+Shift+Enter计算

或者

=INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW()-1)) 按Ctrl+Shift+Enter计算

ROW()-1表示所选区域左上角第一个单元格的行号-1。

如果选中AH3:AH19，那么公式可以写为：

=INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW($D$2:$D$18)-1)) 按Ctrl+Shift+Enter计算

或

=INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW()-2)) 按Ctrl+Shift+Enter计算

实质是修改small函数的参数，确保从1开始，返回第1个最小值、第2个最小值、第3个最小值……

最后如果再套用iferror函数，即可隐藏掉#NUM!错误值

=IFERROR(INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW($D$2:$D$18)-1)),"以上为全部不重复人员")

或

=IFERROR(INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW($D$2:$D$18)-1)),"")

以上做法输出的结果都是数组，数组是一个整体，不能单独更改其中某一个单元格的值。

2、不提前选择输出区域的做法：

提取所有不重复的销售人员的姓名：

选择一个单元格，例如AO2，输入公式

=INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW()-1))

按Ctrl+Shift+Enter键计算

AO2中会出现一个结果“周婉悦”，然后拖动单元格右下角的填充柄，向下填充，直至出现#NUM!即可完成。

注意此时AO2到AO18的每个单元格都是独立的，他们不是数组。

套用IFERROR之后则更加直观方便。

=IFERROR(INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW()-1)),"")

这里使用ROW()-1作为small的参数（具体减几根据选择的单元格的行号和1的差值确定）。

注意：使用ROW($D$2:$D$18)-1作为small参数的以下写法不行：=INDEX($D$2:$D$18,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW($D$2:$D$18)-1))

拖动则会出错，结果都是“周婉悦”。

（二）方法二

（offset函数、Small函数、IF函数、Match函数、Row函数综合运用）

1、Offset函数是什么？

它使用的是偏移量，以指定的引用为参照系，通过给定偏移量得到新的引用，看一下语法：

OFFSET(reference, rows, cols, [height], [width])

Rows可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）

Cols可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）

[height]，可选，意味着返回引用的行数

[width]，可选，意味着返回引用的列数

举个例，通过一张图直观地理解offset偏移量返回引用的意思。

=OFFSET(C3,2,1) 以C3单元格为参照系，向下2行，向右1行，返回385

就是根据上面这个特性，通过offset函数、Small函数、IF函数、Match函数、Row函数综合运用，也可以提取不重复值。

不过offset函数对数组的处理和Index函数有些差别。

2、提前选择输出区域的做法（该做法不可行）：

提取所有不重复的销售人员的姓名：

提前选择区域K2:K18

=OFFSET($D$1,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW()-1),0)

或

=OFFSET($D$1,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW($D$2:$D$18)-1),0)

按下Ctrl+Shift+Enter键计算，会发现出错了，结果都是#VALUE!

公式的原理和采用Index函数时基本一致，match判断是否重复，if把不重复的转换成行号，small再按第1小、第2小依次输出作为offset从D1单元格向下的偏移量，Offset从D1向右到偏移量设为0，执行数组运算却出现了#VALUE!

原因和offset函数本身对数组参数的处理有关系，这些不展开。

2、不提前选择输出区域的做法：

提取所有不重复的销售人员的姓名：

选择一个单元格，例如L2，输入公式：

=OFFSET($D$1,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW()-1),0)

按下Ctrl+Shift+Enter键计算，得到一个结果“周婉悦”，

然后拖动右下角的填充柄填充，则可以提取出不重复值！

注意：和使用index时类似，以下写法不可行：=OFFSET($D$1,SMALL(IF(MATCH($D$2:$D$18,$D$2:$D$18,0)=ROW($D$2:$D$18)-1,ROW($D$2:$D$18)-1),ROW($D$2:$D$18)-1),0) 这种写法不行！填充出来也都是一个人！