EXCEL/Numbers常用公式 - 经典案例

EXCEL/Numbers公式求和有多种“花式求和”,大家可以根据自己的需要选取。

1.隔列求和

实际=SUMPRODUCT((MOD(COLUMN($F54:$K54),2)=0)*($F54:$K54))

计划=SUMPRODUCT((MOD(COLUMN($F54:$K54),2)<>0)*($F54:$K54))

案例:

numbers表格怎么自动填充整列函数(EXCELNumbers常用公式-)(1)

实际=SUMIF($F$53:$K$53,L$53,$F54:$K54)

计划=SUMIF($F$53:$K$53,M$53,$F54:$K54)

=SUMPRODUCT(ISEVEN(COLUMN($F65:$K65))*($F65:$K65))

=SUMPRODUCT(ISODD(COLUMN($F65:$K65))*($F65:$K65))

Remark:

eg.

在C28单元格中输入以下公式:

=SUMPRODUCT(ISEVEN(ROW(2:27))*C2:C27)

公式释义:

ROW(2:27):提取2到27行的行号,结果为2到27的一组数字(2;3;4:.;27);

ISEVEN(.…):iseven 函数的作用是判断上述数组中的数字是否为偶数,生成一组结果为true和false 的数组;

SUMPRODUCT(..*C2:C27):对上述数组和C2:C27区域的数值进行乘积求和;只有第一个数组中为true的值与C列数值相乘才保留原结果,false相乘为0:最后对乘积结果求和,也就是将所有偶数列的值相加.

说明:

如果 number 不是数字类型,则ISEVEN返回 #VALUE!错误值。

既然有iseven,那么当然也有对应的 isodd 函数。与iseven相对,isodd的作用是判断参数是否为奇数,如果是则返回true,不是则返回false.

2.自动挑选符合条件的数据求和

=SUMIFS($E$91:$E$99,I91,$G$91:$G$99)

numbers表格怎么自动填充整列函数(EXCELNumbers常用公式-)(2)

=SUMPRODUCT(($E$91:$E$99=I97)*($G$91:$G$99))

numbers表格怎么自动填充整列函数(EXCELNumbers常用公式-)(3)

注:不只局限于2个条件的求和,3个4个5个···条件的求和也适用

3.单条件模糊求和

包含A的求和=SUMIF($E$103:$E$108,"*A*",$G$103:$G$108)或=SUMIF($E$103:$E$108,"*"&I102&"*",$G$103:$G$108)

以A开头的求和=SUMIF($E$103:$E$108,"A*",$G$103:$G$108)或=SUMIF($E$103:$E$108,I103&"*",$G$103:$G$108)

以A结束的求和=SUMIF($E$103:$E$108,"*A",$G$103:$G$108))或=SUMIF($E$103:$E$108,"*"&I104,$G$103:$G$108)

eg.

numbers表格怎么自动填充整列函数(EXCELNumbers常用公式-)(4)

注:此处不能用sumproduct公式,因为不识别模糊格式“*”

4.多条件模糊求和

包含“电视”且是郑州地区的求和= SUMIFS($G$113:$G$118,$E$113:$E$118,"*"&I113&"*",$F$113:$F$118,J113)

注:如果是以“电视”开头或以“电视”结尾的数量求和,请参照3

numbers表格怎么自动填充整列函数(EXCELNumbers常用公式-)(5)

5.按日期和地区求和(多条件求和)

‘= SUMPRODUCT((MONTH($E$123:$E$131)=J$122)*($F$123:$F$131=$I123)*($G$123:$G$131))

numbers表格怎么自动填充整列函数(EXCELNumbers常用公式-)(6)

注: sumif公式在此处不能用。因为sumif不能嵌套矩阵公式,eg. month(E92:E100),sunif智能识别一个范围eg.E92:E100。不同的是sumproduct却可以识别嵌套的矩阵公式

,