Excel技巧 | 平均值合并单元这么难，菜鸟与大神做法有何不同？

今天要分享一个简单的平均值问题，但是合并单元使这个简单的问题难度倍增，现在用下面这个案例操作看看大神与菜鸟角度做法吧！这里简单模拟了一个数据，如图所示：

结果是怎么来的似乎很容易就看出来了，按照B列的合并单元格所占的行数，对数据进行平均。

但是如何得到C列的结果，好像很容易，但是又有点无从下手……那就让我们先从菜鸟的思路开始吧！

一. 菜鸟思路：3个辅助列搞定

首先出现的是使用了3个辅助列得到结果的方法：

第一个辅助列用的公式是=COUNT($B$2:B2)

得到的结果是对B列的数据进行计数，至于这个辅助列有什么用，继续往下看。

第二个辅助列用的公式是=COUNTIF($D$2:$D$16,D2)

得到的结果是对D列也就是第一个辅助列中每个数字进行计数，再结合B列数据分析的话，这个辅助列的结果正好就是合并单元格所占的行数。

解决问题的方法似乎已经呼之欲出了。

第三个辅助列用的公式是=IF(B2=0,F1,B2)

得到的结果就是把B列的数字变成去掉合并的效果，到了这一步，所需结果已经很明显了！

所需结果的公式为=F2/E2

点评：这种解题思路非常清晰，关键是全部使用了最基础最常用的函数，纯菜鸟，无技巧的解题方法，可以给个五星好评。

你怎么看？

二. 大神思路

1. 四个函数组合搞定

先分享大神的公式：

=IF(B2,B2/IFNA(MATCH(1=1,B3:B10<>"",),COUNTA(A2:A10)),C1)

这是一个数组公式，需要按着Ctrl、shift和回车键完成输入。

要搞明白这个公式，关键是IF第二参数中分母的这一段：

IFNA(MATCH(1=1,B3:B10<>"",),COUNTA(A2:A10))

下面单独来看看这一段的结果。

公式中有这么几个地方需要说明：

• IFNA函数部分版本没有这个函数，可以用IFERROR函数代替，两个函数的用法一致，区别是IFNA只对#N/A这种错误值进行判断

• MATCH中的1=1，完整的写法是TRUE，因为后面的 B3:B10<>""得到的是一组逻辑值，要实现匹配就需要对数据类型作统一

• MATCH这部分也可以写成MATCH(1,--(B3:B10<>""),)，道理还是一样的，查找值是数字时，就需要把逻辑值转为数字

• 公式中的范围 B3:B10并没有锁定，这样会随着公式下拉时对应发生下移，这个范围包含了8个单元格，这个数字只要大于数据源中的最大合并单元格占用的行数即可

点评：这个解法也是很妙的，不但充分利用了数据中的特点，而且对相关函数的细节展现的淋漓尽致，细细琢磨后肯定对函数会有进一步的理解。

如果没有下一个公式的话，这个公式也是可以给到五星好评的。

2. 两个函数就搞定了

再来看大神的第二种解法：

=IF(B2,B2/MATCH(,--(B3:$B$17=""),-1),C1)

MATCH用的妙，IF用的也妙！

关于MATCH，有两点说明：

• 省略第一参数表示查找值为0

• 第三参数-1表示默认降序的模糊匹配

关于IF，补充一点：

可以直接用数字作为条件，当数字不为零是等同于TRUE，数字为零或者单元格为空时等同于FALSE。

关于数据区域，需要以实际数据的下面一个单元格作为结束，并且锁定结尾。

点评：除了赞叹还是赞叹，公式的乐趣尽在其中了。

我是想不到这样写，但是我可以给五星好评，你不反对吧！

三. 小 结

站在解决问题的角度来说，最快能想到的方法就是最高效的。

站在学习的角度来说，多一种思路就能多学到很多知识。

站在看热闹的角度来说，反正大神的公式都看不懂，喜欢长的还是喜欢短的就看自己吧。

菜鸟的辅助列方法虽然简单，但是不说出来真的还不一定能想到这样用，就模仿的容易程度来说，辅助列无疑是多数人的首选，通过多做题，多练习，积累经验，遇到问题肯定会有自己的思路。

不用辅助列的公式解法固然精彩，但是需要掌握的知识点非常多，对于公式的运用也需要更加多的磨练才能自如。

End.

作者：Excel高效应用

来源：商业新知

本文为转载分享，如侵权请联系后台删除