这个函数比 COUNTIF 好用,助你高效工作

原文标题:《这个函数比 COUNTIF 好用 3 倍,助你高效工作,到点下班!》

许久不见的同学老王突然找到我。

他是一名人民教师,每次测验都想快速了解班上学生的成绩状况:

低于 60 分的有多少人,60-80 分的有多少人,80-90 分的有多少人,90 分以上又有多少人?

如下图,根据 C 列成绩,统计各个等级的人数。

如果是你,会怎么做?

有一定基础的同学,第一反应可能是用 COUNTIF 函数。

那我们先来看看 COUNTIF 的做法。

COUNTIF 函数法

使用 COUNTIF 函数,我们需要在 G 列输入以下公式:

❶ G3 单元格输入公式,求成绩低于 60 的人数。

=COUNTIF(C:C,"<60")

❷ G4 单元格输入公式,求成绩大于等于 60,小于 80 分的人数。

=COUNTIFS(C:C,">59",C:C,"<=79")

❸ G5 单元格输入公式,求成绩大于等于 80,小于 90 分的人数。

=COUNTIFS(C:C,">79",C:C,"<=89")

❹ G6 单元格输入公式,求成绩大于等于 90 分的人数。

=COUNTIF(C:C,">89")

很明显,用 COUNTIF 函数,我们需要一个个编写公式,好麻烦啊!

下面必须拿出我的「绝招」了 ——FREQUENCY 函数。

只需一个公式,就能完成分段统计人数。

接下来我们来看看它的做法吧。

FREQUENCY 函数法

选中 G3:G6 单元格,在公式栏中输入公式:

=FREQUENCY(C3:C18,{59,79,89})

按下【CTRL+SHIFT+ENTER】三键进行数组运算,结果就全都出来啦。

是不是超级简单!

现在,我们知道 FREQUENCY 函数的做法了,但是它究竟是啥?

看起来好复杂啊!别担心!接下来我们就来讲解它的语法规则。

基本语法如下:

=FREQUENCY(Data_array, Bins_array)

=FREQUENCY (统计的区域,分组的界点值)

FREQUENCY 函数有两个参数,它是用来计算数值在某个区域内的出现频率,然后返回一个垂直的数组。

我们来看看前面案例的公式:

=FREQUENCY(C3:C18,{59,79,89})

公式我们可以理解为:

需要统计的数值在单元格区域 C3:C18,它作为第 1 参数:数值分布在一个数值轴上;

再根据数组中 {59,79,89} 这 3 个数值作为分段点,为第 2 参数:它们将数值轴上的数据分隔成 4 个区间。

做成图示如下:

最后,FREQUENCY 函数会帮我们数一数各个区间范围的数值个数,返回一个垂直的数组即:{2;11;1;2}

是不是很简单!

当我们需要对数值分段统计个数时,用 FREQUENCY 函数解决更高效、更简洁。

你以为 FREQUENCY 函数只能用在统计个数?那你就错了!

由于 FREQUENCY 函数还忽略逻辑值;它的 「分组的界点值」可以是乱序,允许重复。

当该分段点首次出现时返回其统计的个数,其后重复出现的分段点返回的是 0,最后 1 个返回大于分段点最大值的个数。

下面做一个简单示例来验证这一特性:

所以,它还可以:

❶ 计算不重复值的个数

❷ 统计最大连续次数

❸ 统计文本分布频率

❹ 查找最接近目标值的数值

……

举个简单的例子,它可以做中国式排名。

即无论有几个并列第 1 名,之后的排名仍然是第 2 名,也就是并列排名不占用名次。

在 D3 单元格输入如下公式,然后按【CTRL+SHIFT+ENTER】三键,下拉填充。

=SUM(--(FREQUENCY(C$3:C$11,IF(C$3:C$11>=C3,C$3:C$11))>0))

简单解析一下:

IF (C$3:C$11>=C3,C$3:C$11),如果 C$3:C$11 区域的值大于等于 C3,则返回对应的数值,否则返回逻辑值 False;

利用 FREQUENCY 忽略逻辑值的特性,将大于等于 C3 单元格的值作为分段点;

因为重复出现的分段点返回的是 0,所以返回的结果的非零数就是大于等于 C3 的不重复个数;

最后用 SUM 函数求非零的个数,就是 C3 在 C$3:C$11 的排名。

小结

下面对 FREQUENCY 函数做个小结:

❶ 当我们需要对数值分段统计个数时,可以使用 FREQUENCY 函数;

❷FREQUENCY 返回的结果为数组,需要用数组公式的形式输入。

按【CTRL+SHIFT+ENTER】三键;

❸ 「分组的界点值」中的每一分段点,是以左开右闭的规则来分组。

如公式:

=FREQUENCY(C3:C18,{59,79,89})

分别统计的范围为:数值≤59,59 <数值≤79,79< 数值≤89,数值> 89。

❹ FREQUENCY 函数忽略空白、文本、逻辑值,它的「分组的界点值」可以是乱序,允许分段点重复。

❺ 对于「分组的界点值」中重复出现的分段点数据,只在该分段点首次出现时返回其统计的个数;

其后重复出现的分段点返回的是 0,最后 1 个返回大于分段最大值的个数。

由于文章的篇幅有限,今天我们暂且学习下 Frequency 函数的基本用法,有关它的更多用法,我们后续继续探讨。

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:赵骄阳 & 小爽,编辑:竺兰

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

文章价值:
人打分
有价值还可以无价值
置顶评论
    热门评论
      文章发布时间太久,仅显示热门评论
      全部评论
      一大波评论正在路上
        取消发送
        软媒旗下人气应用

        如点击保存海报无效,请长按图片进行保存分享