今天用Excel时碰到一个问题就是要求类似于SQL里的这么一个语句的功能:

 SELECT SUM(ISNULL(Column01,Column02)) FROM Table01;

Excel中的数据如下图,目的就是当B列值为空时用A列对应行上的值替换,这样求得前5行数的和,也即下图中红框内数的和。

excel-fun_sumproduct01

这种多条件求和可以用SUMPRODUCT函数。

先来看看SUMPRODUCT的基础语法:

SUMPRODUCT(array1, [array2], [array3], ...)

其中填入的各array维度数必须一致,该函数的结果就是将各array对应下标的数相乘最后求和,即如下:

\sum_{j=1}^n{\prod_{i=1}^k {A_i[j]}}


这是SUMPRODUCT的基础用法,它还可以多条件求和

SUMPRODUCT((条件A1)*(条件A2)*(条件A3)*...*(条件An)*(数据列1),(条件B1)*(条件B2)*(条件B3)*...*(条件Bn)*(数据列2),...)

这样在数据列前加上条件限制就只会选出满足条件的数据来计算,但是要注意,不满足条件的位置会变成0。

上面的公式可以退化成这样:

SUMPRODUCT((条件A1)*(条件A2)*(条件A3)*...*(条件An))

这样就变成了统计满足条件的数据的个数了。

下面这个例子就是统计年龄为11岁的男生的个数:

excel-fun_sumproduct02

最后回到我们原来的问题上来,用下面这个公式就可以搞定了:

SUMPRODUCT((B1:B5="")*(A1:A5))+SUM(B1:B5)

excel-fun_sumproduct03

推荐阅读:用EXCEL的sumproduct函数做条件统计

SUMIF、COUNTIF和SUMPRODUCT函数在统计中的应用

在使用Excel的柱状图时会遇到个别柱子特别长的情况,如果只是简单的呈现,既会让这几个“鹤立鸡群”的柱子显得突兀,也让其他柱子的差别看得不明显,如下图。

BreakY-BarChart-01

本文主要是采用此文的方法,本文所用数据也取于

方法一

一般地可以弄成这样:

BreakY-BarChart-02

这种图的做法就是先将原图拉长,然后分别截出长图的上端和下端部分,将上下端拼接起来,中间的过度部分就需要手工去调整(如上图,柱子中间的白色部分就是插入矩形旋转得到的,Y轴的截断部分是直接插入了一个图片)。

但是有几个问题,这里一些地方需要手动调节,多做几个图就不方便。

这种方法甚至可以做成这个链接中的这样:[分享] 可自定义断轴刻度并自动变化的截断图(断层图,Brokencolumns)

方法二

用对数坐标,这种方法最简单方便,就是有时候不是太直观。用这种方法需要考虑到看官的水平。

方法三

这种是本文要主要介绍的方法。做出来的图叫面板图(Panel Chart),效果如下。

BreakY-BarChart-03

这种图将柱状图分为上下两部分,上半部分是全貌,而下半部分只是低端部分。

制作过程如下图演示: