三种隐藏函数公式(函数公式二指禅)

三种隐藏函数公式(函数公式二指禅)(1)

小伙伴们好啊,今天老祝和大家一起学习一个Excel函数公式中的常用招数——咱们暂且称之为“半封闭”。

先来看下面这个表格,要计算从一月份开始,到当前月份的累计销量:

三种隐藏函数公式(函数公式二指禅)(2)

C2单元格可以输入以下公式,然后下拉:

=SUM($B$2:B2)

三种隐藏函数公式(函数公式二指禅)(3)

这就是一个最基础的“半封闭”用法,其中的$B$2:B2部分,第一个B2使用了绝对引用,第二个B2使用了相对引用,在公式下拉时会依次变成$B$2:B3、$B$2:B4、$B$2:B5……这样逐步扩大的求和范围。最后得到的结果,就是从B2单元格开始,到公式所在行的B列这个范围之和了。

这种自动扩展的引用区域技巧,在日常公式中经常会用到,接下来咱们就列举几个有代表性的应用。

1、判断数据是否重复出现

如下图,要统计B列的姓名是否为重复出现。

三种隐藏函数公式(函数公式二指禅)(4)

C2使用的公式为:

=IF(COUNTIF($B$2:B2,B2)>1,"重复","")

COUNTIF函数使用动态扩展的区域$B$2:B2作为统计范围,计算B列员工姓名在这个区域中出现的次数,如果出现的次数大于1,就是重复。

以B2为例,令狐冲首次出现,C2单元格公式中的COUNTIF计算结果为1,也就是不重复:

=COUNTIF($B$2:B2,B2)

而到了C8单元格,COUNTIF公式的引用区域变化为$B$2:B8:

=COUNTIF($B$2:B8,B8)

在$B$2:B8这个区域中,令狐冲出现了两次,也就是说B8是重复出现的。


2、按部门添加序号

如下图,要根据B列的部门填写序号,每个部门都要从1开始排序。

三种隐藏函数公式(函数公式二指禅)(5)

A2单元格公式为:

=B2&-COUNTIF($B$2:B2,B2)

这个公式中,COUNTIF函数以$B$2:B2作为动态扩展的统计区域,计算B列的部门出现的次数。

如果该部门是首次出现,结果就是1,如果是第二次出现,结果就是2……

最终的统计结果,就可以看做是部门的序号了。

3、必须连续输入,不允许有空单元格

使用数据验证功能,还可以限制必须连续输入。如果输入的不完整或是输入后又删除了记录,Excel就不允许在下面继续输入了:

三种隐藏函数公式(函数公式二指禅)(6)

数据验证的公式为

=COUNTBLANK($D$2:D2)=0

COUNTBLANK用于统计数据范围中空单元格的个数。这里约束的条件就是空单元格数量为0。

同样,使用的时候要注意把公式中的D2换成你所选区域的活动单元格地址。

好了,今天咱们分享的内容就是这些吧,祝各位小伙伴一天好心情!


图文制作:祝洪忠

,

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

    分享
    投诉
    首页