今天用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函数在统计中的应用

这篇文章记录一些Oracle的小技巧,以供参考。

一、函数篇

1. TRUNC, ROUND

trunc一个普遍的用法是对数值的截取,如

SQL> SELECT TRUNC(3.1415926,3) FROM dual;

TRUNC(3.1415926,3)
------------------
3.141

TRUNC函数其实还支持对日期的截断,如

SQL> SELECT TRUNC(SYSDATE,'mm') FROM dual;

TRUNC(SYS
---------
01-MAY-13

TRUNC的第二个参数支持多个精度,可以截断到时分,年季月等等。它的语法如下:



其中的fmt的日期格式部分可以参见这里

同理地,ROUND函数也有类似的用法。

2. DECODE

由于在Oracle里 "NULL =  NULL" 不为真也不为假,所以做表的关联时,如果想让NULL值和NULL也关联上就需要这样写:

WHERE (column = :bind_variable OR (column IS NULL AND :bind_variable IS NULL))

其实有一种更简洁的写法,就是用上DECODE函数:

DECODE(column,:bind_variable,1) = 1

(这里方法取自Thomas Kyte《Oracle Database 9i/10g/11g 编程艺术》6.2.2)