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

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:A"&ROW()-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日余额进出进出进出进出进出A 1 1 2 5 3 2 7 9 8 1 3=SUMIF($B$2:$K$2,"进",B3:K3)-SUMIF($B$2:$K$2,"出",B3:K3) =SUM(SUMIF(B$2:K$2,{"进","出"},B3:K3)*{1,-1})隔列求和类别成品代码单价安贞北辰长安长春合计库存销售库存销售库存销售库存销售库存销售皮带V19201 270.00 1 2 1 2 1 2 1 2库存合计=SUMIF($D$3:$BS$3,"库存",$D$4:$BT$4),销售合计=SUMIF($D$3:$BS$3,"销售",$D$4:$BT$4)=SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)关于隔行、隔列求和的问题隔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)均为数组公式。

EXCEL中求两列的对应元素乘积之和如:a1*b1+a2*b2+b3*b3...的和=SUM(A1:A3*B1:B3) (数组公式)=SUMPRODUCT(A1:A10,B1:B10)计算900~1000之间的数值之和sumif函数的计算格式为: =sumif($a$1:$a$20,">1000")。

即返回$a$1:$a$20中大于1000的数值的和,但如果想计算900~1000之间的数值之和,应该如何编写。

请参考：{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}或{=SUM((900<A1:A20)*(A1:A20<1000)*A1:A20)}2、=SUMIF(A1:A20,">900")-SUMIF(A1:A20,">1000")双条件求和1、求一班女生的个数 :=SUMPRODUCT((A2:A9=1)*(B2:B9=""女""))2、求一班成绩的和 :=SUMIF(A2:A9,1,C2:C9)"3、求一班男生成绩的和 :=SUMPRODUCT((A2:A9=1)*(B2:B9=""男""),C2:C9) "如何实现这样的条件求和求型号中含BC但不含ABC的量:=SUMIF($A$2:$A$12,"*"&"bc"&"*",$B$2:$B$12)-SUMIF($A$2:$A$12,"*"&"abc "&"*",$B$2:$B$12)=SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12 )))*B2:B12)A1:A10数字显为文本格式时，如何求和=SUMPRODUCT(A1:A10)求和所有本范例所使用的数据都为引用以下绿色区域，并定义为对应的标题。

Name Sex Age Position Salary张无忌男26 主角10000韦小宝男16 主角13000灭绝女55 配角3000周芷若女22 主角8000鳌拜男62 普通演员2000仪琳女18 配角5000岳灵珊女19 配角4500令狐冲男27 主角15000性空男88 普通演员2200东方不败不详45 主角9000A 求所有演员工资总额71700 =SUM($G$7:$G$16)简单求和B 求男演员工资总额42200 =SUMIF($D$7:$D$16,"男",$G$7:$G$16)单条件求和.1C 求年龄在20岁以下的演员工资22500 =SUMIF($E$7:$E$16,"<20",$G$7:$G$16)单条件求和.2D 求主角和配角的工资(不是普通演员)67500 =SUMIF($F$7:$F$16,"*角",$G$7:$G$16)单条件求和.3E 求20岁以下女演员工资9500 {=SUM(($D$7:$D$16="女")*($E$7:$E$16<20)*$G$7:$G$16)}多条件求和-同时满足条件F 求男性或主角的工资59200 {=SUM(IF(($D$7:$D$16="男")+($F$7:$F$16="主角"),$G$7:$G$16))}多条件求和-只须满足条件之一G 求男性非主角或主角非男性的工资(即除男主角外的男性和主角)g.1 21200 {=SUM(IF(($D$7:$D$16="男")-($F$7:$F$16="主角"),$G$7:$G$16))}g.2 21200 {=SUM(IF(($F$7:$F$16="主角")-($D$7:$D$16="男"),$G$7:$G$16))}多条件求和-只满足条件之一而不能同时满足H 啊~~~你不知道什么是数组函数啊，可是你有时候也要用多条件求和？不要紧，教你用另外的方法：SUBTOTAL求20岁以下女演员工资71700 =SUBTOTAL(9,$G$7:$G$16)现在你看到的还不是最后结果，请按如下操作1、把数据区域设置成可筛选2、把SEX筛选成"=女", 把年龄筛选成<203、你再看上面的公式结果…去掉其中两个最大值和两个最小值，再求和请问如何去掉两个最高分，两个最低分，剩余人员的分数求和，例如A1-A7中的7个分，去掉两个最高分，两个最低分，剩余人员的分数求和。