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却可以识别嵌套的矩阵公式

,