Excel函数公式常用技巧——混合引用

先来看下面这个表格，要计算从一月份开始，到当前月份的累计销量：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

C2单元格可以输入以下公式，然后下拉：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

=SUM($B$2:B2)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

这就是一个最基础的“半封闭”用法，其中的$B$2:B2部分，第一个B2使用了绝对引用，第二个B2使用了相对引用，在公式下拉时会依次变成$B$2:B3、$B$2:B4、$B$2:B5……这样逐步扩大的求和范围。最后得到的结果，就是从B2单元格开始，到公式所在行的B列这个范围之和了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

这种自动扩展的引用区域技巧，在日常公式中经常会用到，接下来咱们就列举几个有代表性的应用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

1、判断数据是否重复出现文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

如下图，要统计B列的姓名是否为重复出现。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

C2使用的公式为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

=IF(COUNTIF($B$2:B2,B2)>1,"重复","")文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

COUNTIF函数使用动态扩展的区域$B$2:B2作为统计范围，计算B列员工姓名在这个区域中出现的次数，如果出现的次数大于1，就是重复。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

以B2为例，令狐冲首次出现，C2单元格公式中的COUNTIF计算结果为1，表示该姓名在$B$2:B2这个区域中没有重复出现：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

=COUNTIF($B$2:B2,B2)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

而到了C8单元格，COUNTIF公式的引用区域变化为$B$2:B8：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

=COUNTIF($B$2:B8,B8)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

在$B$2:B8这个区域中，令狐冲出现了两次，也就是说B8是重复出现的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

2、按部门添加序号文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

如下图，要根据B列的部门填写序号，每个部门都要从1开始排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

=B2&-COUNTIF($B$2:B2,B2)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

这个公式中，COUNTIF函数以$B$2:B2作为动态扩展的统计区域，计算B列的部门出现的次数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

如果该部门是首次出现，结果就是1，如果是第二次出现，结果就是2……文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

最终的统计结果，就可以看做是部门的序号了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

3、不允许录入重复数据文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

如果把COUNTIF函数的这种用法，与数据验证功能相结合，就可以实现拒绝录入重复数据。如果要输入大量的员工姓名，这种方法特别实用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

数据验证中的公式为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

=COUNTIF($D$2:D2,D2)=1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

数据验证可以根据预先指定的条件，对输入的内容进行自动判断，拒绝不符合条件的内容输入。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

实际使用的时候，公式中的D2需要换成实际选中数据区域的首个单元格，比如你选中的区域是A2:A20，公式就写成：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

=COUNTIF($A$2:A2,A2)=1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

4、必须连续输入，不允许有空单元格文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

使用数据验证功能，还可以限制必须连续输入。如果输入的不完整或是输入后又删除了记录，Excel就不允许在下面继续输入了：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

数据验证的公式为文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

=COUNTBLANK($D$2:D2)=0文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

COUNTBLANK用于统计数据范围中空单元格的个数。这里约束的条件就是空单元格数量为0。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html

同样，使用的时候要注意把公式中的D2换成你所选区域的活动单元格地址。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/21860.html