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

挖数网精选
挖数网精选
挖数网精选
355
文章
0
评论
2020-05-0102:05:00 评论 462 1618字
摘要

今天要分享一个简单的平均值问题,但是合并单元使这个简单的问题难度倍增,现在用下面这个案例操作看看大神与菜鸟角度做法吧!希望看过这篇文章之后,你能有所收获。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

你怎么看?

二. 大神思路

1. 四个函数组合搞定

先分享大神的公式:

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

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

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

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

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

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

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

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

  • 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)

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

MATCH用的妙,IF用的也妙!

关于MATCH,有两点说明:

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

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

关于IF,补充一点:

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

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

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

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

三. 小 结

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

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

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

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

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

End.

作者:Excel高效应用

来源:商业新知

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

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: