1.求每月所有产品的总数量
这是个隔列求和的问题,方法有挺多的。
=SUMIF(B$3:I$3,"数量",B4:I4)
如果B3:I3的内容是"数量",那么对相应的B4:I4的值求和。
=SUM((B$3:I$3="数量")*B4:I4)
还是用的条件求和,只不过换了一种写法。
=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的多维引用来完成。
=SUMPRODUCT((B$15:H$15="单价")*B16:H16*C16:I16)
然后判断B15:H15是否等于"单价",如果成立返回true,否则返回false。如上图②所示。最后将这3部分相乘,得到的结果如③所示。是true的返回单价和数量的乘积,是false的返回0。
=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求和。
=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列的二维数组。如下图①所示。
https://pan.baidu.com/s/15eAHCd2HONLQKbaozVmSbw
联系客服