二维码 购物车
部落窝在线教育欢迎您!

同样是countifs函数,为什么同事却使得比你好?原因在这里!

 

作者:苗旭来源:部落窝教育发布时间:2019-08-09 10:17:37点击:5121

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!相信很多小伙伴都会遇到这样一个问题,为什么同一个的函数,别人却使的比我好?为什么这些奇妙的用法我就想不到呢?就拿countifs来说,它用于计算多个区域中满足给定条件的单元格的数量,但同时也可以将一维表转换成二维表,计算数据是第几次重复等等,而这一切仅仅只需要在COUNTIFS函数里加点“钱”,就可以实现。是怎么回事呢?一起来看看吧~



1、统计单列中数据出现的次数



早上刚上班,客服部的小美就跑来找我了。

 

“苗老师,你原来有教过我excel删除重复项的功能,但是我今天碰到了个问题,要求统计出下表内各客户的到访次数,该怎么办呢?”

 

1

 

别急,今天就教你一个计数函数,它就是COUNTIFS,可以用于单、多条件计数。在2007版本以前,并没有这个函数,当时只有用于单条件计数的COUNTIF函数。而现在用的excel版本大多是2007以后的,所以我们现在直接学习COUNTIFS就行。

 

回到正题,这个需求很简单,只要在C2单元格里输入公式:=COUNTIFS(A:A,A2)就可以得到结果。

 

这个函数有两部分组成,一个是条件所在的区域,一个是需要计数的条件,下拉之后,就能得到各客户在这此列中出现的次数,如图2所示。

 

2

 

太棒了,这样一下,就得到我想要的东西了。 



2、统计在单列中的数据是第几次出现



苗老师,我被领导批评了。

 

怎么了,昨天的工作不是做好了吗?

 

我理解错领导的意思了,他是想知道这些到访用户每次是第几次来访。原来那个COUNTIFS只能判断出每个客户总共来了几次,你快教教别的函数。

 

原来是这样,没听清领导的需求吃大亏了吧,其实不用换函数,就用这个函数就行,只要往函数里‘加钱’。

 

什么,加钱是什么意思?

 

这里我就要介绍一个符号了——“$”,就是美元符号。它在函数里有着固定区域或是固定单元格的作用。一般情况下,我们在将公式填充到多个单元格中时就会用到它。如果不用“$”符号就会发生一些错误,如GIF3所示。

 

GIF3

 

你可以在动图里看到,项目B和项目C的百分比明显是错误的,因为在下拉公式的时候,分子和分母同时发生了变化。这时候,我们加入“$”符号,就可以解决此类问题。如图GIF4

 

GIF4

 

我在“B2”的2前面加上了“$”符号,在下拉的时候,分母就保持不变了。

 

苗老师,你说的这么开心,和我刚才说的问题有什么关系呀?

 

哦哦哦,说多了,我们绕回来,我们这时候就把这个特性应用到COUNTIFS上,看GIF5,认真看区域部分哦。

 

GIF5

 

这是什么原理?

 

我们把计数区域限制了一个范围,而这个计数范围会随着公式下拉不断扩大,这样一来需要统计的数量就会逐渐增多了。而原来的公式,它的计数范围是被固定住的最大范围,所以它统计出来的结果就是各客户到访的总次数了。



3、利用COUNTIFS把一维表制作成二维表


 

苗老师,这个COUNTIFS这么神奇,你快教教我还有没有别的用法。

 

今天有空,我就再教你一个。用COUNTIFS把一维表转换为二维表的办法。

 

一维表?二维表?这是什么意思?

 

一维表一般指用一行来存放一条完整的数据,比如你这张人员到访表,就是一张一维表,每人每次一条记录。

 

那么二维表呢?一般指用行和列分别记录两个不同的维度,多用于统计表,如图6这就是一个二维表。

 

6

 

那我们现在举一个简单的例子。如图7是一个公司某月的订单明细,要求统计出销售人员在各地区完成的订单数量。

 

7

 

我们需要做一个这样的表,如图8

 

8

 

当然数据透视表也能达到目的,但我们今天主要说说COUNTIFS

 

我们可以看到这个表有两个维度,一个是销售人员维度,一个是地区维度,我们需要用到这两个条件。

 

B2单元格输入=COUNTIFS(C:C,F2,B:B,G1)如图9所示。

 

9

 

C:C作为第一个条件区域,F2是第一个条件,B:B是第二个条件区域,G1是第二个条件,以此类推,最终计算出在多个区域中满足所有条件的单元格个数。但是在这里我们将公式横拉、下拉时,却出现了问题。如GIF10

 

GIF10

 

除了得出北京地区李丽的订单数量外,其他的结果都是0。其实问题的关键还是在“$”符号上,和问题2是一样的道理,只是这里的情况会更复杂一些。那我们来具体看看这里要怎么加“$”符号。

 

首先,两个条件区域肯定是不能变的,都要加上“$”符号,=COUNTIFS($C:$C,F2,$B:$B,G1)

 

但是这样还是不能解决问题,因为在下拉的时候,我们需要条件1的行号变,列号不变;在横拉的时候,我们要求条件2的列号变,行号不变。小心别被绕晕了,我们看一个GIF就明白了。

 

GIF11

 

不熟悉引用的小伙伴还可以看下我们往期的教程《绝对引用混合引用都不懂?难怪你总是公式填充错误!》。多看几遍,你就能马上掌握“$”和多条件计数的用法。

 

今天的教程就到这里了,我们下期再见~


本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

引用的用法《绝对引用混合引用都不懂?难怪你总是公式填充错误!

统计非重复数①《1分钟搞定不重复数统计》

统计非重复数②《她跟多少人打过电话?这是典型的非重复统计,有三种方法!