excel常用函数公式及技巧搜集5.doc

excel常用函数公式及技巧搜集5 博客分类： ExcelVBA及公式应用 对带有单位的数据如何进行求和 在数据后必须加入单位，到最后还要统计总和，请问该如何自动求和？（例如：A1：2KG，A2：6KG.....，在最后一行自动计算出总KG数）。 =SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))”KG” 对a列动态求和 可以随着a列数据的增加，在“b1”单元格=sum(x)对a列动态求和。? =SUM(OFFSET(A1,0,0,COUNTA(A:A),1)) 动态求和公式 自A列A1单元格到当前行前面一行的单元格求和。 =SUM(INDIRECT(A1:AROW()-1)) 列的跳跃求和 若有20列（只有一行），需没间隔3列求和，该公式如何做？ 假设a1至t1为数据（共有20列），在任意单元格中输入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1)) 按ctrl＋shift＋enter结束即可求出每隔三行之和。 跳行设置：如有12行，需每隔3行求和 =SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12))) 有规律的隔行求和 要求就是在计划、实际、差异三项中对后面的12个月求和。 =SUMPRODUCT(--(MOD(COLUMN(F3:AO3)-CELL(Col,F3)+0,3)=0),F3:AO3) =SUMIF($F$2:$AO$2,C$2,$F3:$AO3) =SUMPRODUCT((MOD(COLUMN($F3:$AO3),3)=MOD(COLUMN(F3),3))*$F3:$AO3) 也可以拖动填充，插入行、列也不影响计算结果。 如何实现奇数行或偶数行求和 假设数据在A1:A100 奇数行:=SUMPRODUCT(MOD(ROW($A$1:$A$100),2)*$A$1:$A$100) 偶数行:=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100)) 奇数行求和 =SUMPRODUCT((A1:A100)*MOD(ROW(A1:A100),2)) 偶数行求和 =SUMPRODUCT((A1:A100)*NOT(MOD(ROW(A1:A100),2))) 单数行求和 隔行求和用什么函数，即：A1+A3+A5+A7+A9…公式如何用。 {=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))} {=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,0))} 统计偶数单元格合计数值 统计F4到F62的偶数单元格合计数值。｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝ 隔行求和公式设置 均为数组公式： =SUM(IF(MOD(ROW(A1:A110),2),A1:A110,0)) =SUM(N(OFFSET($A$1,ROW(1:55)*2-2,,,))) =SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100)) =SUM((MOD(ROW(A1:A100),2)=0)*(A1:A100)) =SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*A1:A100) 隔列将相同项目进行求和 隔列将出勤日和工资分别进行求和 数组公式=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0)) 或； =SUMPRODUCT(--(MOD(ROW(C5:C25),2)0),C5:C25) 隔行或隔列加总 隔2列加总 =SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25) 隔2栏加总 =SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1) 请问如何在一百行内做隔行相加 数组公式 A1+A3+……+A99??? 单 =SUM(N(OFFSET(A1,ROW(1:50)*2-2,))) A2+A4+……+A100?? 双 =SUM(N(OFFSET(A1,ROW(1:50)*2-1,))) 如何将间隔一定的列的数据相加呢 碰到100多列的数据将间隔一定的数据用手工相加太烦了，也容易出错。如果需要相加的数据均有相同的名称（字段），可以用Sumif()来求解，如果没有，就需要用数组公式来解决了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)} ???1、4、7……行相加。 隔列求和（A、B列） =SUM(A:A,B:B) =SUM(A:A,B:B,C:C)?? （统计A、B、C列） 隔列求和的公式 品种及日期 1月1日 1月2日 1月3日 1月4日 1月5日