Excel中多条件统计函数COUNTIFS和FREQUENCY的应用

王侃

摘要：Excel是Office软件中的一个重要组件，即电子表格软件。Excel函数则是Excel中的内置函数，Excel函数包含统计函数，数学函数，逻辑函数等十一大类，正确应用Excel函数可以提高工作效率。该文主要以函数COUNTIFS和FREQUENCY为例来探讨Excel统计函数的应用。

关键词：Excel;COUNTIFS函数;FREQUENCY函数

中图分类号：TP311        文献标识码：A        文章編号：1009-3044（2018）31-0210-02

Excel是Microsoft公司出品的办公套装软件的一个重要组件，可以用它来进行数据计算和生成各类统计图表等，且界面清晰直观，使得Excel至面世以来便成为最流行的个人计算机数据处理软件，在各行各业如教务教学、行政管理，财务统计、保险金融等被广泛地应用。Excel中共包含十一类函数，随着版本的不断更新，函数数目也逐步增加。使用Excel的函数就是使用它预定义的公式，可以按特定的顺序或结构来执行计算。如我们在使用Excel电子表格来统计处理数据的时候，时常会用到统计函数如COUNTIF函数、COUNTIFS函数和FREQUENCY函数等来按需自动统计处理表单数据，之前常用COUNTIF函数来统计满足某个条件的单元格的数量，但若条件不单一，则要多次使用COUNTIF函数才能完成统计，烦琐且容易出错。本文通过探讨COUNTIFS函数和FREQUENCY函数的应用来完成多条件的数据统计。

1 COUNTIFS函数

1.1功能

COUNTIFS函数用来统计一组给定条件所指定的单元格数目，条件可以为一个也可为多个。

1.2语法

COUNTIFS（条件区域1，条件1，[条件区域2，条件2]，…）。

1.3参数

1）条件区域1：必须项。是要为特定条件进行匹配查询的第一个区域。

2）条件1：必须项。其形式可以是数字、表达式、单元格引用或者文本，它定义了要统计的单元格的范围。 例如，条件可以表示为 75、"<=65"、A6、"中国江苏"或 "2018"。

3）条件区域2，条件2， ...：可选项。为第二个条件区域和第二个条件，以此类推。 最多能有一百二十七个条件区域和条件对。第二个及之后的每个条件匹配查询区域都需与参数条件区域1具有相同的行数和列数，但这些区域无须彼此相邻。

1.4注意事项

1）COUNTIFS函数的用法与COUNTIF函数类似，但它既能统计单个条件的计数，此时相当于COUNTIF函数的应用;同时又能实现统计多个条件的计数。

2）如果条件中需要引用单元格，正确写法是“符号条件”&单元格地址。

3）如果条件参数是对空值的引用，COUNTIFS将该单元格的值视为0。

4）条件中可以使用星号（*）和问号（？）这两个文本通配符。星号代表任意字符串，也可以代表零个字符，问号则代表任意一个字符。如果要查找实际的星号或问号，则必须在字符前输入波形符 （～）。

2 FREQUENCY函数

2.1 功能

FREQUENCY函数即频率函数，可以计算某个区域中某值的出现频率，并返回一个垂直数组。例如，使用函数FREQUENCY可以统计计算分数区域中每个分数段出现的次数。由于REQUENCY返回的是一个数组，所以输入时也必须为数组公式的形式。

2.2 语法

FREQUENCY（数组数据，数据接收区域）。

2.3 参数

1）数组数据：必需项。需要对其进行计算频率的一个数组或对一组数值的引用，即数据源。如果数组数据中不包含任何数值，则FREQUENCY函数返回一个零数组。

2）数据接收区域：必需项。要将数组数据中的数值分组插入到一个区间数组或对区间的引用，即统计区间的分段点。 如果数组接收区域中不含有任何数值，则FREQUENCY函数的返回值与数组数据中元素的个数相同。

2.4注意事项

1）FREQUENCY函数的参数设定完之后必须以数组公式的形式输入。

2）数组返回中的元素个数比数组数据中的元素多一个。返回的数组中的额外元素返回超过最大间隔以上的任何值的计数。例如，要对60以下、60-85及85以上这三个区域进行计数时，我们只需设置60和85两个值，即可确保将FREQUENC函数输入到结果的三个单元格。额外的单元格将返回数组数据中大于第二个间隔值的值的数量。

3）函数FREQUENCY会自动忽略非数值的数据。

4）对于返回结果为数组的公式，必须以数组公式的形式输入。

3 应用解析

以学生成绩统计表为例，对于教师，这是期末考试结束后必不可少的工作之一，利用Excel函数进行统计既快捷方便，又不易出错，极大提高了统计的准确率。

示例：求出以下四门学科各分数段的人数，如图1所示。

3.1利用COUNTIFS 函数求解

以统计课程高等数学各分数段人数为例，步骤如下：

1）先求90分以上或60分以下的人数，在单元格C21中，输入公式“=COUNTIFS（C2：C17，">=90"）”，即可求出90分及以上的人数，如图2所示;同理60分以下的人数可以在C25单元格内输入公式“=COUNTIFS（C2：C17，"<60"）”求出。此时的统计人数均只有一个条件，使用COUNTIFS 函数相当于COUNTIF函数的功能。

2）再求80分段的人数，在单元格C22中输入公式“=COUNTIFS（C2：C17，"<90"，C2：C17，">=80"）”，如图3所示;

3）用同样的方法求出70分段的人数，在单元格C23中输入公式“=COUNTIFS（C2：C17，"<80"，C2：C17，">=70"）”;

4）再用同样的方法求出60分段的人数，在单元格C24中输入公式“=COUNTIFS（C2：C17，"<70"，C2：C17，">=60"）”。

其他各门课的统计均可参照以上COUNTIFS函数的使用方法。

3.2利用FREQUENCY函数求解

以统计课程大学英语各分数段人数为例，步骤如下：

1）首先确定间隔分值，定义区间数组，在单元格H21：H25内分别输入“100，89，79，69，59”，区间分割值含义如图4所示;

2）然后在单元格D21内输入公式“=FREQUENCY（D2：D17，H21：H25）”，如图5所示。此时要注意的是，由于FREQUENCY函数的结果是返回一个数组，要想得到数组的结果，则必须以数组公式的形式输入，方法是按下<Ctrl+Shift+Enter>组合键来确定，得到公式显示为“{=FREQUENCY（D2：D17，H21：H25）}”，特别提醒此处的大括号手动输入是无效的;

3）选用单元格D21右下角的智能填充柄，向下拖动填充单元格D22：D25即可得出其他分数段的统计人数。

4 结束语

通过以上案例我们可以看出，使用COUNTIFS函数和FREQUENCY函数均能完成对成绩分数段的统计，其中COUNTIFS函数在多条件的情况下比COUNTIF函数的输入公式要简洁一些，且该函数比较容易理解，但每个分数段需要分别输入公式，还是比较烦琐，效率并不高。而使用FREQUENCY函数是专门用于数据频度分析的函数，能快速方便地完成统计，但输入时需要注意必須以数组公式的形式输入。

总之通过对以上两个函数的应用实例剖析，我们能了解到COUNTIFS函数和FREQUENCY函数的作用及使用方法，在实际应用时根据需要选择相应函数即可。

参考文献：

[1] 神龙工作室.Excel高效办公.行政与人力资源管理[M].北京：人民邮电出版社，2006.

[2] 胡国民.《办公软件高级应用》课堂教学之我见[J].办公自动化，2013（2）：56-58.

[3] 教育部考试中心.全国计算机等级考试二级教程——MS Office高级应用[M].北京：高等教育出版社，2015.