EXCEL中带你体验不一样的极值与均值 设置上限下限 图文

2019年8月11日16:08:42 评论 871 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

在读书时经常听到:第一名成绩某某,最后一名成绩某某。平均成绩某某。其实这些说白了就是最大值、最小值与平均值。

如图一:是一份各销售人员销售金额汇总表。

Excel中带你体验不一样的极值与均值

图一

问题1 获取最大销售额

=MAX(B2:B8)

问题2 获取最小销售额

=MIN(B2:B8)

问题3 许多公司都会根据销售额来计算销售提成。按销售额的2%计算,最大不得超过3000,最低不得低于1000。否者销售员跑光了。

=MIN(3000,MAX(1000,B2*2%))

首先将销售额乘以2%与1000比较,使用MAX函数获取最大值,当2%销售额低于1000时取1000,即给销售提成设置了下限。

将MAX函数返回的值与3000比较,使用MIN函数提取最小值,当MAX超过3000时取3000,

即给销售提成设置了上限。通过这样设置就限制了提成在1000~3000。

针对问题3,Excel提供了一个可以返回中间值的函数MEDIAN.

=MEDIAN(1000,B2*2%,3000),完美搞定。

问题4 获取平均销售额

=AVERAGE(B2:B8)

问题5 去除最大小值求平均值,在比赛中经常会用到。

=(SUM(B2:B8)-MAX(B2:B8)-MIN(B2:B8))/COUNT(B2:B8)-2)

后来我针对这问题我想了想有点繁琐,其实Excel提供了一个去除首尾函数TRIMMEAN:

=TRIMMEAN(区域,比例)

比如有10个值,去除1个最大值,1个最小值,比例为0.2.如果是去除2个最大值,2个最小值,比例为0.4.也就是说,去除的总数除以实际的数就是比例。

问题6 获取第二销售额

=LARGE(B2:B8,2)

问题7 获取倒数第二销售额

=SMALL(B2:B8,2))

这两个函数语法一样,第一参数为区域,第二参数是第几个的意思。很多的时候,我们需要按销售额降序排序,这时可以用如下公式:

=LARGE(B$2:B$8,ROW(A1))

ROW函数可以获取1到N的序号。如果升序排序,此时将LARGE函数换成SMALL函数即可。

欢迎评论,转发。让知识广泛传播。

本文已取得今日头条原作者【老余Excel】授权 禁止转载 原文链接为https://www.toutiao.com/item/6669187107182871052/

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

发表评论

您必须才能发表评论!