学会“深入浅出”招式，函数公式水平提升27.5%
小伙伴们好啊，今天Kevin和大家一起学习一个Excel函数公式中的常用招数——深入浅出。
先来看下面这个表格，要计算从一月份开始，到当前月份的累计销量：
C2单元格可以输入以下公式，然后下拉：
=SUM($B$2:B2)
这就是一个最基础的深入浅出用法，其中的$B$2:B2部分，第一个B2使用了绝对引用，第二个B2使用了相对引用，在公式下拉时会依次变成$B$2:B3、$B$2:B4、$B$2:B5……这样逐步扩大的求和范围。最后得到的结果，就是从B2单元格开始，到公式所在行的B列这个范围之和了。
这种自动扩展的引用区域技巧，在日常公式中经常会用到。接下来咱们就再说几个有代表性的应用场景。
1、判断数据是否重复出现
如下图，要统计B列的姓名是否为重复出现。
C2使用的公式为：
=IF(COUNTIF($B$2:B2,B2)>1,'重复','')
COUNTIF函数使用动态扩展区域$B$2:B2作为统计范围，计算B列员工姓名在这个区域中出现的次数，如果出现的次数大于1，就是重复。
以B2为例，北原爱子首次出现，C3单元格中的公式为：
=IF(COUNTIF($B$2:B3,B3)>1,'重复','')
结果就是1，也就是不重复了。
而到了C9单元格，公式为：
=IF(COUNTIF($B$2:B9,B9)>1,'重复','')
在$B$2:B9这个区域中，B9单元格的北原爱子出现了两次。所以$B$2:B3,B3)>1的条件成立，也就是说B9是重复出现的。
2、按部门添加序号
如下图，要根据B列的部门填写序号，每个部门都要从1开始排序。
A2单元格公式为：
=B2&-COUNTIF($B$2:B2,B2)
这个公式中，COUNTIF函数以$B$2:B2作为动态扩展的统计区域，计算B列的部门出现的次数。
如果该部门是首次出现，结果就是1，如果是第二次出现，结果就是2……
最终的统计结果，就可以看做是部门的序号了。
3、不允许录入重复数据
如果把COUNTIF函数的这种用法，结合数据验证功能，就可以拒绝录入重复的数据，如果要输入大量的员工姓名，这种方法特别实用。
数据验证中的公式为：
=COUNTIF($D$2:D2,D2)=1
数据验证可以根据预先指定的条件，对输入的内容进行自动判断，拒绝不符合条件的内容输入。
实际使用的时候，公式中的D2需要换成实际选中数据区域的首个单元格，比如你选中的区域是A2:A20，公式就写成：
=COUNTIF($A$2:A2,A2)=1
4、必须连续输入，不允许有空单元格
在员工信息表中，要求各个记录必须是连续输入的，如果输入的不完整或是输入后又删除了记录，Excel就不允许在下面继续输入了：
数据验证的公式为
=COUNTBLANK($D$2:D2)=0
COUNTBLANK用于统计数据范围中空单元格的个数。这里约束的条件就是空单元格数量为0。
同样，使用的时候要注意把公式中的D2换成你所选区域的活动单元格地址。
如果把以上几个在数据验证中的公式应用到条件格式当中，还可以对不符合条件的数据进行突出标记，公式原理是一样的，咱们就不再赘述了。有兴趣的小伙伴，可以看看这篇稍微复杂点的应用方法：
5、提取不重复姓名
如下图，要根据A列的员工姓名，提取出不重复的人员名单：
C2单元格公式为：
=INDEX(A:A,1+MATCH(0,COUNTIF($C$1:C1,$A$2:$A$14),))&''
注意这里是一个数组公式，输入后要按Ctrl+Shift+回车才可以哦。
这个公式比较复杂一点，需要有一定的函数功底才能理解，咱们简单说说计算过程，小伙伴们有个了解就好：
1、公式中的COUNTIF($C$1:C1,$A$2:$A$14)部分，在公式所在单元格上方的区域中，用COUNTIF函数依次统计$A$2:$A$14单元格区域中每个元素的个数。返回一个由0和1构成的内存数组，其中0表示是首次出现的数据，1表示对应位置的姓名在公式上方出现过，也就是重复出现的姓名。
2、当公式向下复制时，参数$C$1:C1依次变$C$1:C2、$C$1:C3、……
也就是前面的公式结果，会被继续作为后面公式的参数进行排除。
3、然后用MATCH函数在COUNTIF函数返回的数组中查找0的位置，也就是首次出现的数据所在位置。由于数据表的标题行占了1行，所以将这个数字再加1。
4、最后利用INDEX函数，根据MATCH函数的结果返回A列对应位置的姓名。
够复杂吧？不过不用担心，实际使用的时候，可以先学会套用，也就是把公式中的$C$1:C1，换成实际公式所在单元格的前一个单元格地址，把$A$2:$A$14换成实际要提取内容的数据区域，这样就可以了。（比实际数据多选一行，可以屏蔽错误，原理以后咱们再讲哈）