excel函数技巧：如何快速汇总销售合计项

一年的销售数据整理完了除了要看到每个人的销售合计之外老板今年还要看到图中这两项合计

销量最高的三个月合计是指汇总每人一年中销量最高的三个月的数据。

超过平均值的销售合计是指汇总超过总平均值的月份销量。

两项合计需要一个一个去比较后再求和吗若一个个比较用时一个小时也不能算好。这可难住了小表姐。

其实这两项求和不难下面就给大家分享这两种求和的套路。

1.汇总销量最高的三个月的数据

这个问题首先是找出销售额排名前三的数据然后再将数据进行求和就行了。

我们都知道在EXCEL中求最大值用MAX求最小值用MIN除此之外还有两个非常实用的求最大、最小值的函数——LARGE函数和SMALL函数。

LARGE函数可以在指定的数据区域中返回指定的第几大的值。

格式LARGE数据区域第几大的值

例如=LARGE(B2:M2,3)会返回B2:M2区域中第三大的值。

除此以外这个函数还可以找到数据区域中若干个最大值例如找前三名的值可以将第二个参数写成常量数组的格式

=LARGE(B2:M2,)

为了大家看起来更直观我们将前三名的值用颜色标注

在上图O2单元格只能看到销量最高的1月的数据是公式出问题了吗

公式使用常量数组后结果虽是一组数据但在单元格中只能显示出这组数据中的第一个值。要想看到每个数据可以在编辑栏选中公式后按F9键

可以看到前三名的值都出现了。

注意使用F9之后不要按回车键要按ESC键返回。

SMALL函数与LARGE函数的用法是完全一样的我们也可以利用SMALL函数得到最小的几个值。

找到了最高的三个值我们再使用SUM函数求和即可=SUM(LARGE(B2:M2,))

下面来看第二个问题超过平均值的销售合计。

2．超过平均值的销售合计

这项合计涉及到平均值就一定要用到求平均值的AVERAGE函数。使用AVERAGE(B2:M17) 可以得到每月的平均值

现在问题就明朗了许多其实就是条件求和。说到条件求和当然少不了SUMIF函数。再来复习一下这个函数的基本用法吧

格式SUMIF条件区域条件求和区域

函数的格式很简单但在这个例子中对于SUM函数第二参数的写法是个难点也许你会这样写公式=SUMIF(B2:M2,>AVERAGE(B2:M17),B2:M2)得到的结果是这样的

那这样写=SUMIF(B2:M2,”>AVERAGE(B2:M17)”,B2:M2)

倒是不报错了可结果不对啊变成0了……

好吧不卖关子了正确的写法是这样的=SUMIF(B2:M2,">"&AVERAGE(B2:M17),B2:M2)

在这个公式中第二参数用到了比较运算符大于号“>”,还有平均值函数AVERAGE(B2:M17)。在SUMIF中运算符需要加引号如果是与一个具体的数值比较的话这样写是没有问题的=SUMIF(B2:M2,">20424",B2:M2)。但当要比较的对象是一个函数时那就不行了因为函数是不能加引号的。此时需要用文本连接符&连接函数因此第二参数的正确写法就是">"&AVERAGE(B2:M17)。

同时SUMIF函数还有个特性当求和区域与条件区域相同时第三参数可以省略这个公式最终可以写成=SUMIF(B2:M2,">"&AVERAGE($B$2:$M$17))AVERAGE函数中的数据区域要绝对引用

最后来总结一下今天的收获

1使用LARGE和SMALL函数可以返回指定数量的最大值或最小值。函数的第二参数使用常量数组时常量数组的大括号需手动输入而不能三键产生。

2使用SUMIF函数时如果第二参数同时出现运算符和函数运算符要加引号再使用&连接该函数。当条件区域和求和区域相同时可以省略第三参数。