60%的人不会这两种求和方法,不信来试试!

1.求每月所有产品的总数量

下图是某公司1~6月四种产品的销售数据,每种产品都有两列,单价一列,数量一列。求每月所有产品的总数量。以1月份为例,就是C4+E4+G4+I4。

这是个隔列求和的问题,方法有挺多的。

第1种,用常用的sumif,在J4单元格输入下面的公式,向下填充。

=SUMIF(B$3:I$3,"数量",B4:I4)

如果B3:I3的内容是"数量",那么对相应的B4:I4的值求和。

第2种,用sum,在J4单元格输入下面的公式,按ctrl+shift+enter。

=SUM((B$3:I$3="数量")*B4:I4)

还是用的条件求和,只不过换了一种写法。

第3种,根据列号的奇偶性来求,用mod函数。在J4单元格输入下面的公式,向下填充。

=SUMPRODUCT(MOD(COLUMN(B:I),2)*B4:I4)

MOD(COLUMN(B:I),2)这部分用列号除以2求余数,如果列数是奇数,求余的结果为1;如果列数是偶数,求余的结果为0。最后的结果为{0,1,0,1,0,1,0,1}。也就是"单价"对应的是0,"数量"对应的是1。

第4种,offset的多维引用,在J4单元格输入下面的公式,向下填充。

=SUM(N(OFFSET(A4,,{2,4,6,8})))

以A4为基点,分别向右偏移2,4,6,8列,得到C4、E4、G4、I4组成的横向三维引用。用n函数降维得到{11,77,21,51},也就是每种产品的数量,最后用sum求和。

2.求每月所有产品的总金额

现在求每月所有产品的总金额,还是以1月份为例,就是B16*C16+D16*E16+F16*G16+H16*I16。如果有很多数据,要一直这么加下去吗?

这个题我们可以用错位相乘的方法来完成,也可以用offset的多维引用来完成。

第1种,sumproduct错位相乘法,在J16单元格输入下面的公式,向下填充。

=SUMPRODUCT((B$15:H$15="单价")*B16:H16*C16:I16)

首先要注意公式中的3个区域,分别是B15:H15、B16:H16、C16:I16,如下图①所示。其中B16:H16和C16:I16是错位对齐的,这很重要。

然后判断B15:H15是否等于"单价",如果成立返回true,否则返回false。如上图②所示。最后将这3部分相乘,得到的结果如③所示。是true的返回单价和数量的乘积,是false的返回0。

第2种,也可以用mod来判断奇偶列,还是错位相乘的方法。公式为:

=SUMPRODUCT(MOD(COLUMN(A:G),2)*B16:H16*C16:I16)

第3种,offset的多维引用,公式如下:

=SUM(N(OFFSET(A16,,{1,3,5,7}))*N(OFFSET(A16,,{2,4,6,8})))

第1个offset的多维引用得到了各产品的单价,结果为{7,9.2,4.7,2.9};第2个offset的多维引用得到了各产品的数量,结果为{11,77,21,51}。然后让它们对应相乘,最后sum求和。

第4种,offset的4维引用,mmult以及对数函数。公式如下:

=SUM(EXP(MMULT(LN(N(OFFSET(A16,,{1,2;3,4;5,6;7,8}))),{1;1})))

OFFSET(A16,,{1,2;3,4;5,6;7,8})这部分得到了4维引用,用n降维得到了{7,11;9.2,77;4.7,21;2.9,51}。也就是对各产品的单价和数量重组,得到了4行2列的二维数组。如下图①所示。

EXP(MMULT(LN(N(OFFSET(A16,,{1,2;3,4;5,6;7,8}))),{1;1}))这部分对上图二维数组的每一行分别相乘,得到对应的积,结果如②所示。最后用sum函数对所有的积求和。
链接:

https://pan.baidu.com/s/15eAHCd2HONLQKbaozVmSbw

提取码:5m73
(0)

相关推荐