Excel 函数极值与均值应用以销售金额汇总表为例展开探讨

在读书的时候经常听到：第一名成绩××，最后一名成绩××，平均成绩××。其实这些说白了就是最大小值跟平均值。

如图5-25所示，是一份各销售人员销售金额汇总表。

图5-25 销售数据汇总表

         问题1 获取最大销售额。

=MAX(B2:B8)

         问题2 获取最小销售额。

=MIN(B2:B8)

         问题3 获取销售提成，按销售额的1%计算，最大不得超过3000，最小不得低于1000。

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

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

         将MAX函数返回的值与3000比较，使用MIN函数提取最小值，当MAX超过3000时取3000，即给提成设置了上限。

         通过设置上下限，限制了提成在1000~3000之间。

         这样说起来有点绕，其实Excel提供了一个可以返回中间值的函数MEDIAN。

=MEDIAN(1000,B2*1%,3000)

         问题4 获取平均销售额。

=AVERAGE(B2:B8)

         问题5 去除最大小值求平均值，在体育比赛中经常会出现。

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

         直接求和然后依次减去最大值、最小值，然后除以总数-2个，这样就获取平均值。不过这样挺繁琐的，其实Excel提供了一个去除首尾的函数TRIMMEAN。

=TRIMMEAN(B2:B8,2/COUNT(B2:B8))

         函数语法

=TRIMMEAN(区域,比例)

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

         问题6 获取第二大销售额。

=LARGE(B2:B8,2)

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

=SMALL(B2:B8,2)

         这两个函数语法一样，第一参数为区域，第二参数为N。N就是第几个的意思。很多时候我们需要的是将销售额从大到小进行降序排序，这时可以用：

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

         ROW函数可以获取1到N的序号。如果需要从小到大升序排序，将LARGE函数换成SMALL函数即可。