Excel如何实现间隔任意行取数求和? | 您所在的位置:网站首页 › excel横向求和函数求和 › Excel如何实现间隔任意行取数求和? |
声明:原文首发于个人公众号:cxd1301,转载请注明。 最近接到客户求助,需要汇总分单据的计划数和执行数,在单据整理后,其实是有规律的间隔行求和或平均。 最基础的需求大致如下: 目标是汇总出上半年计划数,执行数。 进一步需求是:由于不同人录入,文字描述可能不一致。 方法一:点选,或手工输入 以C14为例,=sum(C2,C4,C6,C8,C10),手工输入,或者按住cmmand键,一个个点选。少量数据下,这种方法可以,但数量多的情况下,这种方法显然是不靠谱的。 方法二:用数据透视表 选中数据区域后,以名称为行,以列求和为值,建立一个数据透视表即可。如果列里的名称一致情况下,这种方法是可行的,速度也很快,鼠标点一点就行,值的汇总方式很丰富,还不需要编写任何公式。 方法三:用名称匹配统计 如果不想用数据透视表,要直接用公式生成,可以这么实现: C14为例=SUMPRODUCT(($B$2:$B$13=$B14)*C$2:C$13) 不考虑锁定引用的$,单元格的公示是:=SUMPRODUCT((B2:B13=B14)*C2:C13) 解释:B2:B13=B14是判断B2到B13里的值是否和B14相同,如果相同是1,不同是0,结果是1,0,1,0,1....然后用sumproduct实现与C2:C13的值进行对应乘积再求和。 多说一句,$ 符号是用来做引用锁定的,放在列(字母)前,是锁定列,放在行(数字)前,是锁定行,这种锁定是拖动公式填充的时候,不会变化。 例如:$B14,是锁定了B列,那拖动填充公式时,填充的是B15、B16,行号会变化,但列不动; C$13,是锁定了13行,那拖动填充公式时,填充的是D13、E13,列会变化,但行不动; $B$2,是既锁定行又锁定列,也就是固定单元格了,拖动填充公式时,不会出现变化,$B$2:$B$13,就是永远取B列的2到13行,不会变化。 方法四:奇数行或偶数行求和 方法二和方法三都是基于B列有相同的内容,进行同内容汇总。 但如果名称不一致,例如有些名称多了一些空格,或者多了个单位什么的,这种方法就不行了。比如不同人填写的内容可能略有差异,表达的意思一样,但Excel不会智能识别。 如果要取出其中的奇数行或偶数行进行求和,怎么处理呢? 首先取奇数行和偶数行,就需要先知道行数,通过ROW()来提取; 其次要判定奇数和偶数,是除以2,看余数是0还是1,用MOD(X,2); 最后还是用sumproduct进行结果计算。 取B2到B13的行号,结果为2-13,考虑表头1行,所以row(B2:B13)-1,再用2求余数,结果就是一串1,0,1,0,1....,后面就简单了,用这串数和C2:C13笛卡尔积(对应位置相乘再求和),就可以了。 如果说不熟悉sumproduct,要用sum也是可以的,计算公式如下: =SUM((MOD(ROW($B$2:$B$13)-1,2)=1)*(C$2:C$13)) 注意,这里因为需要采用向量计算,不能直接回车,需要用Command+shift+enter(IOS系统),windows系统应该是Ctrl+shift+enter。 方法五:任意间隔行求和 假设现在要取1、4、7、10....每间隔3行取数,求和,怎么办呢?参照方法4:=SUMPRODUCT(IF(MOD(ROW($B$2:$B$13)-1,3)=1,1),C$2:C$13) 通过取行号(有表头-1)和3求余数,如果是1,则为1,否则为0,然后再和C2:C13进行笛卡尔积。 这里一样要用Command+shift+enter(IOS系统),不能直接enter,否则报错。查看公式的时候是有{}的,这个大括号不是手工输入的。 类似的要做到任意行,就只要修改mod中的除数,就可以了。 比如要1、n+1、2n+1、3n+1.....行求和,就mod中的3,改成n就行。 |
CopyRight 2018-2019 实验室设备网 版权所有 |