学会“深入浅出”招式,函数公式水平提升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换成实际要提取内容的数据区域,这样就可以了。(比实际数据多选一行,可以屏蔽错误,原理以后咱们再讲哈)

(0)

相关推荐