第2章
基础统计函数

相对引用与绝对引用是函数使用的基础，制作表格时如果要“偷懒”，可以用我们第1章讲到的图钉“$”。

下面认识几个常用的基础统计函数，如图2-1所示。

有一些英文基础的读者会猜出以上函数的意义。这里重点提一下COUNT函数与COUNTA函数，它们都有“计数”的功能，其中COUNT函数只是统计数字的个数，而COUNTA函数可以理解为COUNT ALL，即统计全部（实际上是统计所选单元格区域中所有“非空”单元格的个数）。下面以图2-2中模拟的某个公司销售员的销售业绩为例，来具体介绍各个函数的实际使用方式。

2.1　求和、计数、平均值函数

在工作中最常用的统计方式就是求和、计数、求平均值，下面分别介绍可以完成这些工作的函数。

1.求和函数

如果需要求总销量，该怎么做呢？不同时期有不同处理数据的方法。

在古代，账房先生算总账，肯定是拿出算盘“噼啪”一通，然后得到结果1004。

在现代，我们使用计算器同样可以得到结果1004。

Excel的初学者通常都能观察到Excel的状态栏中有自动求和功能，选中H5：H14单元格区域，就能得到想到的结果，如图2-3所示。状态栏上不仅有求和，还有平均值和计数。

精通Excel函数的人看到需求时，只需在单元格输入一个公式即可完成，下面我们来看一下具体操作。

选中H15单元格，首先输入“=SUM（”，然后选中H5：H14单元格区域，如图2-4所示。

这样就选定了需要求和的单元格区域，最后输入反括号“）”，按【Enter】键，即可得到销量的总和1004，如图2-5所示。

我们用公式计算数据，最终目的是“偷懒”。当计算范围发生变化时，我们只要修改数据源中的几个数值，结果就会随之更新。下面进一步分析这个公式：

其中，SUM函数是执行“求和”命令，求的是H5：H14单元格区域的数值总和。SUM函数的基础用法就这么简单。

2.计数函数

接下来做一个计数，为了看出COUNT与COUNTA函数的差异，我们选择H4：H14单元格区域作为统计标准，在语法上与SUM函数完全一致，如图2-6所示。

在I15单元格中输入公式：

在I16单元格中输入公式：

H4：H14单元格区域，共11个单元格，而COUNT函数得到的结果是10，因为其中H4单元格是文字，并不是数字，所以不在COUNT的统计范围内。

I16单元格中的COUNTA函数得到的结果是11。

那么，结果是11，是否因为H4：H14单元格区域一共有11个单元格？

不是的，COUNTA计算的是“非空”单元格的个数，下面做一个实验来具体说明。

在公式不变的情况下，我们将数据源中的几个数据清除。例如，清除H6、H9、H11单元格的数据，如图2-7所示。可以看到清除后只剩下7个数字和8个“非空”单元格。并且可以看到SUM函数的求和也会随之更新。

3.平均值函数

学习了求和与计数函数的使用，求平均值就很简单了，可以直接用H15单元格的SUM除以I15单元格的COUNT。

其实，Excel中有更合适的工具，在Excel中可以用AVERAGE函数来求平均值，在H16单元格中输入公式“=AVERAGE（H5：H14）”，如图2-8所示，得到平均值100.4。

至此，我们已经学习了4个函数，SUM、COUNT、COUNTA、AVERAGE。使用公式计算的目的是什么呢？偷懒。当数据源发生变化时，所有函数公式的计算结果都会自动更新。

提示

Excel内置的函数，不需要把函数名记得一个字不差，从Excel 2007版开始，只需输入函数的前几个字母，就会有下拉列表显示以此开头的全部函数，并且有该函数的简要解释，如图2-9所示。此时可以使用键盘的上下箭头来选择，按下【Tab】键，整个函数就可以自动补全。

2.2　MAX和MIN函数及设置上下限

在基础统计时，常常需要看一下最大值和最小值，那么用Excel能不能搞定呢？生活中很多电器标注大小时，都可以看到MAX、MIN的字样，那么MAX和MIN是不是也可以用在Excel统计中呢？下面来试一下，如图2-10所示。

在H15单元格中输入公式：

在H16单元格中输入公式：

输入公式后，就能计算出最大值145和最小值57。

通过以上操作，可以发现，MAX和MIN函数与SUM函数的语法是一样的，输入一个函数名，然后选中所要计算的单元格区域，就能得到想要的结果。

但是，这样操作后得到的只是最大值和最小值，功能较为简单，接下来我们看一下用MAX和MIN还能完成什么操作。

公司发奖金的系数通常是与销量挂钩的，为避免某位员工由于销量过多而奖金系数无上限或销量惨淡完全没有奖金，公司会设置一个上下限规则：所有销量超过120的，以120来计算；所有销量低于80的，以80来计算。下面进行具体分析。

以罗贯中的145为例，他超过了120，应该记为120，进一步观察，实际上只涉及了两个数字，145和上限120，这里应选择两个数字中的“最小值”。再以法正的65为例，他的销量低于80，应该记为80，同样也涉及两个数字，65和下限80，这里应选择两个数字中的“最大值”。

有了上述思路，那么怎样把MAX和MIN进行有效地结合呢？

首先在I5单元格中输入公式“=MIN（H5，120）”，这样就解决了所有销量超过上限120的问题；然后修改I5单元格中的公式为“=MAX（MIN（H5，120），80）”，这样销量低于下限80的问题也解决了。

那么这个公式是否正确呢？将I5单元格的公式向下复制到I14单元格，如图2-11所示，结果完全正确。

这时有人会说：“这个思路是明白了，但在工作中用到公式时还是无法准确使用，记不清哪个放括号外面，哪个放括号里面？哪个后面跟上限数值，哪个后面跟下限数值？”下面我们一一解答。

（1）哪个放括号外面，哪个放括号里面？

都可以。实践是检验真理的唯一标准，我们把公式改为“=MIN（MAX（H5，80），120）”，可以看到，结果仍然正确，这是为什么？其实这个公式的思考过程与之前是相反的，先通过公式MAX（H5，80）计算得到两个数字中的最大值，这样对于所有低于80的数字结果均为80，即设定好下限，再通过公式MIN（MAX（H5，80），120）设定上限。

（2）哪个后面跟上限数值，哪个后面跟下限数值？

这里有3种记忆方法。

①上法：理解上文讲的分析思路，当需要时，将思路提炼出来，分析一遍，公式自然就写出来了。

②中法：设定上下限，如果只记得用MAX和MIN的组合，不知道上下限放在哪里，那就在单元格中直接写，例如，输入公式“=MAX（MIN（H5，80），120）”，向下复制，如图2-12所示。复制后会发现，结果不对，怎么办呢？不用急，公式整个结构是正确的，这里把上下限80和120换个位置，公式变成“=MAX（MIN（H5，120），80）”，得到的就是最终结果。

③下法：死记硬背：MAX跟着下限，MIN跟着上限，背下来就能解决问题，但是千万不要记混了。

接下来介绍另外一个函数——中位数MEDIAN。在A1：F1单元格区域随机输入1、9、8、4、6、3这6个数字，如图2-13所示。

在A3单元格中输入公式：

在A4单元格中输入公式：

得到的结果是6和5。中位数是将一串数字按照大小顺序排列，取得中间的那个数字的值。

如果数字的个数是奇数，就取得最中间的那一个。例如，A3单元格的公式，A1：E1从小到大排列是1、4、6、8、9，中间的数字是6。

如果数字的个数是偶数，就取得中间两个数字并计算平均值。例如，A4单元格的公式，A1：F1从小到大排列是1、3、4、6、8、9，中间的数字是4和6，平均值为5。

前面介绍了MAX函数和MIN函数，这里为什么要介绍MEDIAN函数呢？

回到前面的销量表，同样是设置上下限，在I5单元格输入公式“=MEDIAN（H5，80，120）”，然后向下复制，如图2-14所示。

得到的结果完全正确。计算逻辑相当于将每个人的销量与80、120进行比较，然后取得这3个数的中位数，这样就完成上下限的设置了。

2.3　案例：提取销量前三大和前三小的值

前面介绍了最大值和最小值，但在工作中，有时不仅要求“最”值，还要求排行前几的值等。涉及大和小，对应的英语单词有LARGE和SMALL，在Excel中恰好也有这样的两个函数，可以用来求第几大和第几小的值。

这里先介绍一个函数——ROW，在任意单元格中输入公式“=ROW（1：1）”，它返回结果为数字1，如图2-15所示。其中，1：1代表整个工作表的第1行，而ROW函数用来返回相应单元格、区域的行号。也就是说，公式ROW（1：1）能得到第1行的行号1。

然后将公式向下复制，1：1依次变成2：2、3：3、4：4、5：5，这样ROW函数返回的结果依次为1、2、3、4、5，Row函数将在第4章中详细讲解。

现在需要统计排行前三的销量，要怎样处理呢？

在I5单元格输入“=large（H5：H14，”，如图2-16所示，这时观察一个细节，在语法提示上，出现了LARGE（array, k），其中array是所要选择的数据区域或数组，后面的k可以理解为要求第几大的数字。

在最初学习函数的语法时，不要求马上完整地理解它们的含义，随着接触的函数增多，慢慢就会理解了。

下面继续来完善公式：

其中，ROW（1：1）返回的结果就是数字1，这样就得到了H5：H14区域的最大值。这里需要得到前三大的数字，于是将I5单元格的公式向下复制到I7单元格，ROW函数依次返回结果1、2、3，即可得到销量前三的值，如图2-17所示。

仔细观察图2-17，数字145是最大的，这里没错，但第二大的值应该是H10单元格的134，第三大的值才是H6单元格的123，为什么会出现这种情况呢？下面进一步观察。

I6单元格的公式：

I7单元格的公式：

我们发现第一个参数选择的区域，已经不是最初的H5：H14，而变成了H6：H15、H7：H16。这是因为公式在复制时，所选择的区域也随着公式位置的变化发生了变化。那要怎么处理呢？

我们在第1章讲过“图钉”，如果想让第一个参数所引用的区域不变，就要用图钉把这个区域按住，将I5单元格公式中的H5：H14单元格区域选中，然后按【F4】键添加上图钉“$”，最终公式修正为：

再将I5单元格的公式向下复制到I7单元格，结果如图2-18所示，这样就通过一个公式，得到了销量前三的数值。

同理，也可以得到最小的三笔销量，与LARGE函数对应的是SMALL函数。如图2-19所示，在I12单元格中输入公式：

将I12单元格的公式向下复制到I14单元格，就得到了最小的三笔销量。再次提示，只要对公式进行复制，一定要想到“图钉”的问题。

2.4　案例：对销量进行升序和降序排名

对每个人的业绩做一个排名，有专门的函数RANK, RANK源自于英文单词Ranking。

在I5单元格输入“=rank（”，这时看到此函数的语法提示上出现了RANK（number, ref，[order]）的字样，如图2-20所示。

其中第一个参数为number（数字），于是RANK函数的第一个参数是要进行排名的数字，这里选择H5单元格。第二个参数ref（reference的缩写，引用），代表在表格中所引用的区域，这里选择H5：H14单元格区域。

输入反括号“）”，按下【Enter】键结束，最终H5单元格的公式为：

返回结果为1，说明罗贯中的销量145是排名第一的。然后将I5单元格的公式向下复制到I14单元格，如图2-21所示。

图2-21中出现很多排名第一的。仔细观察，I9单元格陆逊对应的第5名，公式为“=RANK（H9，H9：H18）”，第二个参数选择的区域变成了H9：H18，已经不是最初的H5：H14单元格区域了。问题找到了，这里要用“图钉”。这个不起眼的美元符号$，有着不可忽视的作用。

选中I5单元格公式中的参数H5：H14，按【F4】键，公式变为“=RANK（H5，$H$5：$H$14）”，然后将公式向下复制到I14单元格，如图2-22所示，结果完全正确。

如果需要对各个员工的错误数进行排名该怎么操作呢？错误数自然是越少越好，所以排名不是用RANK函数得到的“降序”，而是用“升序”排列。总人数为10，我最开始使用的方法是用数字11减去当前每个人的排名，公式为“=11-RANK（H5，$H$5：$H$14）”，然后得到了升序。这个方法我用了很长时间。当时的Excel 2003版本并没有函数语法提示，而且周围的同事使用Excel的水平也只停留在初级阶段。

直到有一天，计算机中的Excel升级到2007版本，当我再次对员工的错误数进行排名时，输入RANK公式后，发现RANK后面多了一个第三参数[order]！这个参数就是用来指明数学排序的方式的。

继续在I5单元格中输入公式：

提示框中显示“0-降序；1-升序”，如图2-23所示。

完善I5单元格的公式，将第三参数写上数字1：

然后将公式向下复制到I14单元格，如图2-24所示，即可完成升序的排名。

有时候，完善公式只需认真观察细节，就能找到问题所在，要多做尝试，不要害怕出错。

提示

如果输入“=RANK（H5，$H$5：$H$14，”公式后，看不到后面升序、降序的提示，就需要检查一下公式是否有问题。注意，公式中的逗号应该使用英文半角“，”，而不是中文“，”。 wEsUuzErp0qBchdJX4ZOP3++ETtZhWFl67KfKGP5YsqWteNId4cd96mYp3g/J1zc