通常我们习惯直接使用AVERAGE函数进行数据的平均值计算，其实该函数可以与其他函数组合运用，来满足更复杂的平均值计算。

实例：如图数据所示，要求在不进行排序的情况下计算出销量前5名销售的平均销量。

思考：如果需求改为“要求在不进行排序的情况下计算出销量末尾5名销售的平均销量”，该如何实现？

注意：需按Ctrl+Shift+Enter三键输入数组公式，才能得到想要的结果。

下面数据是一份销售报表，由于星期六、星期日为休息日，无销售额发生，因此金额栏中的值为0（用“-”代替）。
需求1：分别计算星期一至星期五的平均销售额，用于分析星期一至星期五的平均销售额变动情况。

思考：如果上述问题使用AVERAGE函数实现，要怎么做？
答案：在O4单元格输入数组公式：{=AVERAGE(IF($H$3:$H$30=M4,$I$3:$I$30))}，然后向下填充公式，公式结合IF函数用逻辑判断H3到H30单元格区域的内容是否与M4单元格的内容相等，然后返回I3到I30单元格区域中属于M4单元格的值，最后用AVERAGE函数求平均值。

需求2：计算2014年5月1日至28日之间有效工作日的日均销售额。

思路：在N12单元格输入公式：=AVERAGEIF(I3:I30,">0")，这里的有效工作日是指非周六周日，因此只需要计算金额列非0单元格的平均值即可。

注意：当AVERAGEIF函数省略第三参数时，将使用第一参数同时作为条件判断与数值计算的区域。

AVERAGEIF函数：AVERAGEIFS(求平均值区域，条件区域1，条件1，[条件区域2，条件2]，[条件区域3，条件3]，……)

需求3：沿用1.2的数据，要求计算中旬有效工作日的平均销售额。

注意：AVERAGEIFS函数的条件区域和条件值组成一组条件参数，最多允许设置127组条件参数（一般不会设置这么多条件）。

COUNTIF函数和COUNTIFS函数可以统计满足一定条件的单元格个数，条件参数中可以使用比较运算符和通配符。上面展示了COUNTIF函数常用的公式用法，这些公式同样适用于COUNTIFS函数，在仅有一个条件参数的情况下，两者的运算结果完全相同。
设置COUNTIF函数或COUNTIFS函数的条件参数时，需要注意以下一些情况：

• 判断条件 “<>”：这个条件参数表示不等于 ”真空”，“真空”表示单元格内没有任何数据，是真正的空单元格，设置这个判断条件可统计非真空单元格的个数；
• 判断条件 “><”：这个条件参数仅表示统计大于 “<”符号的文本，注意区分 “<>”和 “><”的区别；
• 判断条件 “=”：这个条件参数表示等于 “真空”，可用于统计真正空单元格的个数；
• 判断条件“”：这个条件参数表示包含真空单元格及空文本，其中的 “空文本”一般是指由公式计算得到的结果；
• 判断条件 “*”：这个条件参数代表所有文本，包括空格以及空文本，但不包含真空单元格，也不包含数值、逻辑值、错误值等数据单元格；
• 判断条件 “<>”””：这个条件参数的含义并不代表“不等于空文本”，而仅仅只表示不等于单个双引号 (“ )；
• 判断条件 “?*”：这个条件参数表示统计所有单元格长度不为0的文本单元

2.2 单字段多条件计数

需求1：统计销售量大于等于1000且小于1300的记录个数。

思路：该需求属于单个字段列多个条件的类型，可使用COUNTIFS函数进行统计，其参数中每两个参数形成一组关联条件区域和条件表达式（最大可以包含127组条件），因此在K15单元格输入公式： =COUNTIFS(L2:L10,">=1000",L2:L10,"<1300") *得到结果。

得到结果；也可以在COUNTIF函数中运用数组参数作为计数条件，然后与数组相乘取得求和运算中的正负符号，最后用SUM函数求和得到差额。在K17单元格输入公式：=SUM(COUNTIF(L2:L10,">="&{})*{1,-1}) 得到结果。

2.3 多字段多条件计数

需求2：沿用2.2的数据，统计工号以A或E开头的员工的汽油销售笔数。
思路：该需求属于多字段（工号和商品）多条件的类型，可直接使用COUNTIFS函数实现，分别统计工号以 “A”开头的员工的“汽油”销售笔数以及工号以

扩展：上述需求也可以运用COUNTIFS函数对“工号”和“商品”两个字段进行多字段多条件计数，条件为模糊条件，需要运用通配符“*”代表任意字符，对于“工号”字段条件，运用数组解决逻辑“或”的关系，计算结果为数组{2,2}，最后用SUM函数求和。在K23单元格输入公式：=SUM(COUNTIFS(J2:J10,{“A*”,“E*”},K2:K10,"*汽油"))

3.1 检查数据填写的完整性

COUNTA函数可以返回单元格区域非空单元格个数；COUNTBLANK函数可以统计指定单元格区域中空白单元格的个数。
实例：如上表所示，固定资产清单中存在一些缺项，运用COUNTA和COUNTBLANK函数确认固定资产清单是否填写完毕。

要求：运用COUNTA和COUNTBLANK函数，从“内容是否完整”和“是否存在空值”两个不同的角度实现数据检查的功能。

（1）用COUNTA函数统计当前行中的字段填写个数，如果非空单元格个数与第二行中的标题个数相同，则返回空值（表示已填写完整），如果有缺项，则返回“缺项”。在K3单元格输入公式：=IF(COUNTA(A3:I3)=COUNTA($A$2:$I$2),"",“缺项”) ，然后向下填充得到结果。
（2）用COUNTBLANK函数统计当前行中是否存在空值，如果计算结果大于0，即有空值，表示填写未完成，公式返回“缺项”，否则返回空文本。在L3单元格输入公式：=IF(COUNTBLANK($A3:$I3),“缺项”,"")，然后向下填充得到结果。

3.2 空与非空的判断

运用COUNTA和COUNTBLANK函数进行数据统计时，需要注意数据表中空值和非空值的判断。
实例：如图所示，从表面上看，员工信息表中的性别字段均无内容，但实际上已被设置了6种真假空的情况。

（1）用COUNTA函数统计当前行中的字段填写个数，如果非空单元格的个数为4，则返回空值（表示已填写完整），如果有缺项，则返回“缺项”。在U3单元格输入公式：=IF(COUNTA($O3:$R3)=4,"",“缺项”) ，然后向下填充得到结果。
（2）用COUNTBLANK函数统计当前行中是否存在空值，如果计算结果大于0，即有空值，表示填写未完成，公式返回“缺项”，否则返回空文本。在V3单元格输入公式：=IF(COUNTBLANK($O3:$R3),“缺项”,"")，然后向下填充得到结果。

（1）COUNTA函数返回包含文本、假空单元格、逻辑值或错误值的结果，只有真空单元格不被计数，其参数可以是引用，也可以内存数组（指按Ctrl+shift+enter得到的结果）。
（2）而COUNTBLANK函数则返回单元格区域中单元格为空单元格或公式计算结果为空文本的个数，其参数只能是单元格引用，不能是内存数组。

SUMPRODUCT函数将给定的几组数组中数组间对应的元素相乘，并返回乘积之和。利用这一特性，可以用该函数进行多条件求和、计数以及其他相关的数值计算。

需求1：根据上面数据明细，按品种、站点对“金额”进行分类汇总。
（1）构造数组1，用于判断数据表中的区域单元格引用是否为对应单元格给定的油品品种；构造数组2，用于数据表中的区域单元格引用是否为对应单元格给定的站点名称。两个数据条件相乘，数据均满足这两个条件时返回1，如果有一个或一个以上不满足，则返回0。
（2）SUMPRODUCT函数将两个条件数组乘积再与目标计算字段“金额”所在的单元格区域进行数据相乘，并将乘积求和，得到计算结果。

（1）用SUMPRODUCT函数进行多条件计算，要求条件单元格引用区域与目标计算字段单元格引用的数据区域大小相同。
（2）用SUMPRODUCT函数进行多条件求和计算，也可以使用如下公式：=SUMPRODUCT((条件数组1)*(条件数组2)*……*(条件数组n)*(求和数据区域))。
即多个“条件数组”与“求和数据区域”直接连乘。为了避免目标计算字段数据中存在空格或其他文本项造成计算出现#VALUE!的情况，建议使用通用格式：

需求2：沿用4.1的数据，统计0号轻柴油单笔加油量在2000以上的笔数。

需求3：根据对应日期的经营总额进行排序

（1）用“对应日期的经营总额”在“全部日期经营总额单元格区域”中进行比较，当“对应日期的经营总额”<“全部日期经营总额单元格区域值”成立时，返回TRUE，否则返回FALSE，所以比较结果返回一个由TRUE和FALSE构成的逻辑数组，然后将逻辑数组乘以1，得到一个由0和1构成的新数组。
（2）SUMPRODUCT再对由0和1构成的新数组求和，表示在“全部日期经营总额单元格数据区域”中比“对应日期经营总额”大的数据个数，所以“对应日期经营总额”在“全部日期经营总额单元格数据区域”内排列的位次，应该是比“对应日期经营总额”大的数据个数加1。
（3）再将公式向下复制，则依次对“全部日期经营总额单元格数据区域”中每一个数据重复进行上述判断求和，从而排除数据区域中每一个数据的位次。如果区域中有相同的数，则计算结果也是相同的，因而排出来的位次也相同。

5.1 单字段多条件求和

需求：根据评分表、评分规则、系数表计算出各油站的月综合得分，以此考核油站的当月工作情况。

（1）转换等级为对应分值：利用SUMIF函数与COUNTIF函数类似“单字段多条件计数”的原理，使用C3:E11单元格的评分等级进行条件求和，转换为对应分值，计算结果为与C3:E11对应的9行3列的数组；
（2）计算评分项目系数：将SUMIF得到的评分分值乘以每个项目对应的系数，得到实际得分值。由于系数为3行1列，而SUMIF的结果为3列，因此使用TRANSPOSE函数进行转置。
（3）条件求和：将SUMIF与系数相乘的结果再乘以加油站名称判断，得到对应加油站的考评分项的分值。
（4）最后用SUM函数求和，得到对应加油站的综合得分。

5.2 多字段多条件求和

需求：计算7月份工号以G或P开头的员工汽油的销售量。

解析：该需求涉及了三个字段的条件：日期、工号、商品

公式运用SUMIFS函数进行多字段多条件求和，其中日期条件使用IF函数和MONTH函数，计算得到月份为“7月”的一组数组条件，而“工号”和“商品”两个字段条件为模糊条件，需要运用通配符“*”进行模糊匹配，对于“工号”字段条件，运用横向数组解决逻辑“或”的关系，最后用SUM函数求和，得到数组计算结果。

注意：SUMIFS和SUMIF的参数顺序不同，SUMIFS中将求和区域作为第一参数，而在SUMIF中则是第三参数，使用时注意区分。

5.3 二维区域条件求和

SUMIF函数除了可以在一维数据区域中进行条件求和外，还可以在二维数据表中进行条件求和。
需求1：统计所有“康师傅”方便面的销售量合计。

需求2：统计所有工号为A开头的销售量合计。

思路：在条件区域中查找满足条件的值，如在T3:Z11数据区域中查找值为“康师傅”的单元格，然后根据满足条件值，在第三参数所指定的目标区域中求和运算。

注意：使用SUMIF函数进行二维区域条件求和时，要求条件区域与求和区域需要尺寸相同，且相对位置要保持一一对应，这样才能得到正确结果，否则将不能得到正确结果。

• data_array：参数data_array为一数组或对一组数组的引用，用于计算频率。如果data_array中不包含任何数值，函数FREQUENCY返回零数组。

（1）该函数的两个参数均支持数组和单元格区域的引用，当第二参数为二维数组或引用时，采用“先行后列”的方式进行统计。
（2）分段点若有重复，只在首次分段点统计数值出现的个数，其余分段点返回0。
（3）函数会忽略文本、逻辑值和空单元格，只对数值进行统计。

6.2 分段统计数值结果

根据规则，在H2:H6单元格区域设置各分数段的分段点，然后同时选中I2:I6单元格区域，输入多单元格数组，公式如下：
需要注意这里的花括号{}不是输入内容，而是编写完等号及左边的公式后按Ctrl+shift+enter键得到。

FREQUENCY函数返回的元素个数会比bins_array参数中的元素个数多1个，多出来的元素表示超出最大间隔的数值个数。

此外，在按间隔统计时，FREQUENCY函数是按包括间隔上限，但不包括下限进行统计（即左闭右开）。

根据该函数的这些特征，设计公式时，需要在给出的间隔区间数据基础上进行必要修正，才能得到正确的结果：

• 间隔区间要少取一个，取H2:H5数据区域，而不是表中显示的H2:H6;
• 在给出的间隔区间上限值的基础上减去一个较小的值0.001，调整间隔区间上下限的开闭区间关系。

6.3 计算连续相同值的最多个数

需求：根据上图的NBA篮球赛连胜场次统计数据中，计算两个球队各自的比赛最长连胜（得3分）的场次。

该数组公式中的IF公式分别表示满足条件的数据赋值“列号”，其他赋值FALSE，忽略逻辑值后，以列号用作间隔区间，对第一参数中的连续列号进行分段统计个数，最后通过MAX函数取得最大值。

7.1 使用RANK函数对学生成绩排名

**RANK函数语法：RANK(number,ref,order) ，其中Number 为需要找到排位的数字，Ref 为数字列表数组或对数字列表的引用（Ref 中的非数值型参数将被忽略），Order 为一数字，指明排位的方式。 **

• 如果 order 为 0（零）或省略，WPS表格对数字的排位是基于 ref 为按照降序排列的列表。
• 如果 order 不为零，WPS表格对数字的排位是基于 ref 为按照升序排列的列表。

需求：根据考试成绩表，按总分分别进行班级内部排名和全年级排名。

班级内部排名：在I3单元格输出如下公式:=RANK(H3,$H$3:$H$7),然后向下拖动公式。二班的排名类似，只需将调整排名数据。
年级内排名：全年级成绩区域包含一班和二班两个数据区域，中间不连续。RANK函数可以忽略引用区域中的非数值参数，而该函数的第二参数引用区域可以为单一数据区域，也可以是联合属于区域，因此公式可以为=RANK(H3,($H$3:$H$7,$H$10:$H$15))（联合区域）或者 =RANK(H3,$H$3:$H$15)（完整区域）。

注意：RANK函数重复数的排名相同，但重复数会影响后续数值的排名，从出现跳跃式名次。

• RANK.EQ函数：返回一列数字的数字排位。 其大小与列表中其他值相关；如果多个值具有相同的排位，则返回该组值的最高排位。
• RANK.AVG函数：返回一列数字的数字排位：数字的排位是其大小与列表中其他值的比值；如果多个值具有相同的排位，则将返回平均排位。

（1）可以对数据进行升序或降序排名；
（2）排名范围只能是单元格引用，不支持数组引用；

说明：RANK.EQ函数和RANK.AVG函数在相同数值中出现不同排名是由其返回值决定的。

PERCENTRANK 函数用于返回特定数值在一个数据组中的百分比排位，利用该函数可以对目标数据按一定数量比例进行分级。
（1）Array：定义相对位置的数组或数字区域。
（2）x：数组中需要得到其排位的值。
（3）Significance：可选，一个用来标识返回的百分比值的有效位数的值。如果省略，函数PERCENTRANK保留三位小数。

（3）若数组里没有与x相匹配的值,则PERCENTRANK 将进行插值以返回正确的百分比排位
需求：按各公司营业额的大小排序，排名前20%的公司评定为A级单位，在级别列填入评定结果。

思路：在C2单元格中输入如下公式，并填充至C15单元格：
该公式使用PERCENTRANK函数计算B2单元格的值在B2:B15单元格区域的数据组中的百分比排位，保留2位小数后的结果为0.92（B2:B15单元格区域中小于B2单元格值得个数有12个，大于B2单元格值的个数有1个，百分比排位计算过程为12/(1+12)≈0.92），最后用IF函数判断大于或等于0.8时为“A级单位”。

TRIMMEAN 函数返回数据集的内部平均值:先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。
（1）array 为需要进行整理并求平均值的数组或数值区域。
（2）percent 为计算时所要除去的数据点的比例，例如，如果 percent = 0.3，在 20 个数据点的集合中，就要除去 6 个数据点 (20 x 0.3)：头部除去3个，尾部除去 3 个。

• 函数 TRIMMEAN 将除去的数据点数目向下舍入为最接近的 2 的倍数。如果 percent = 0.1，30 个数据点的 10% 等于 3 个数据点。函数 TRIMMEAN 将对称地在数据集的头部和尾部各除去一个数据。

需求：根据上面的评分表，要求在最终得分列计算“去掉一个最高分和一个最低分”后的平均得分。

注意：当存在多个相同极值（最大值或最小值）时，TRIMMEAN函数只会按要求各剔除其中一个，然后求平均值

众数是指一组数值中出现频率最高（次数最多）的数值。
需求：根据上面的选手评分表计算出每个选手得分频率最高的分值。

思路：在J2单元格输入公式=MODE(B2:I2)，然后向下填充。

扩展：MODE函数还可以在数值和字符混合的数据表中计算出现频率最高的内容。
如上面数据表所示，计算出现频率最高的内容，P1单元格的公式如下：
MODE函数只能对数值进行计算，要在数值和字符混合的数据表中计算出现频率最高的内容，首先需要用MATCH函数将数据表中的值全部转为数值，其计算结果表示每一个数据在整个数据表中出现的位次，再用MODE函数从中计算出频率最高的位次，最后用INDEX函数，在数据表中查找对应定位的值，从而得到出现频率最高的值。

注意*：当数据表中的多个数字出现最高频率相同时，则MODE函数按照先列后行、从上到下的原则返回第1个出现频率最高的数值。

以上分享的干货均来自书籍《Excel2013实战技巧精粹》，更多技巧可自行查阅原书。

当您发现懂得网上存在涉嫌侵犯您合法权益的内容时，您可以通过以下方式向懂得网提出诉求。

请将侵权链接、举证材料及说明，发送至邮箱：。我们将在收到邮件的7个工作日处理您的请求。