excel常用函数公式及技巧搜集5
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 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 主角9000 A 求所有演员工资总额 71700 =SUM($G$7:$G$16) 简单求和 B 求男演员工资总额 42200 =SUMIF($D$7:$D$16,"男",$G$7:$G$16) 单条件求和.1 C 求年龄在20岁以下的演员工资 22500 =SUMIF($E$7:$E$16,"<20",$G$7:$G$16) 单条件求和.2 D 求主角和配角的工资(不是普通演员) 67500 =SUMIF($F$7:$F$16,"*角",$G$7:$G$16) 单条件求和.3 E 求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筛选成"=女", 把年龄筛选成<20 3、你再看上面的公式结果… 去掉其中两个最大值和两个最小值，再求和 请问如何去掉两个最高分，两个最低分，剩余人员的分数求和，例如A1-A7中的7个分，去掉两个最高分，两个最低分，剩余人员的分数求和。 =SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2) =SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2})) =TRIMMEAN(A1:A7,4/7)*(7-4) =SUMPRODUCT(LARGE(A1:A7,ROW(A1:A7))*(ROW(1:7)>2)*(ROW(1:7)<6)) =SUMPRODUCT((A$1:A$7 =SUM (A!:A7)-LARGE(A!:A7,1)-LARGE(A!:A7,2)-SMALL(A!:A7,1)-SMALL(A!:A7,2) 将此函数横着使用（A1-G1） =TRIMMEAN(A1:G1,4/7)*(7-4) =SUMPRODUCT(LARGE(A1:G1,COLUMN(A1:G1))*(COLUMN(A:G)>2)*(COLUMN(A:G)< 6)) 去掉两个最高分、最低分，显示出被去掉的分数 被去掉的分数： 最大两个：=large(data,{1;2}) 最小两个：=small(data,{1;2}) 永恒的求和 1、=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以对A列数值自动求和。 2、=SUM(INDIRECT("R2C:R[-1]C",FALSE)) 3、=SUM(INDIRECT("A2:A"&ROW()-1)) =SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))) 按字体颜色求和 做法： G3={SUM(IF(($A$2:$A$19=E3)*($B$2:$B$19=F3),$C$2:$C$19))} G4:G11公式为G3公式下拖. ～～～～～～～～～～～～～～～～～～～～～～～～～～～～～ 如何分班统计男女人数 男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) =SUMPRODUCT(($B$2:$B$446=E3)*($C$2:$C$446=F$1)) =SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) ｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝ ｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝ 女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1)) 合计=COUNTIF($B$2:$B$446,E2) 统计数值大于等于80的单元格数目 在C17单元格中输入公式： =COUNTIF(B1:B13,">=80") 确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。 计算出A1里有几个abc A1: abc-ded-abc-def-abc-ded-ded-abc , 如何计算出A1里有几个abc 公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc") 有条件统计 如何统计当A1<=15时，统计B列中<=8.5的累加值和个数，而>15时不进行统计？ 个数： =IF(A1>15,"",COUNTIF(B2:B10,"<=8.5")) 累加值（求和）： =IF(A1>15,"",SUMIF(B2:B10,"<=8.5")) 如何统计各年龄段的数量 需分别统计20岁以下、21-30岁、31-40岁、41-50岁、50岁以上年龄段的数量。 根据“出生日期”用以下公式，得到“自动显示年龄”。 先将F列的出生日期设置为“1976年5月”格式，在G列公式为： =DATEDIF(F2,TODAY(),"Y")（周岁，自动显示年龄） =YEAR(TODAY())-YEAR(F2) 再根据年龄段：20岁以下、21-30岁、31-40岁、41-50岁、50岁以上，用以下公式，求出不同年龄段人数。 在J2公式为： =SUMPRODUCT(($G$2:$G$34>$H1)*($G$2:$G$34<=$H2)*($C$2:$C$34=J$1))｛=SUM(($G$2:$G$34<=VALUE(MID(I2,1,2)))*1)｝ 或数组公式： ｛=SUM(($G$2:$G$34<=VALUE(MID(I3,4,2)))*1)-SUM($J$2:J2)｝ 如何计算20-50岁的人数? =COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50") =SUMPRODUCT((C3:C17>=20)*(C3:C17<=50)) =FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19) ｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝ 如何统计40-50岁的人的个数 ＝countif(a:a,">40")-countif(a:a,">50") ＝SUM(COUNTIF(a:a,">"&{40,50})*{1,-1}) 数组公式{=sum((a1:a7>40)*(a1:a7<50))} ＝SUMPRODUCT((A1:A7>40)*(A1:A7<50)) 要统计出7岁的女生人数 =COUNTIF(D2:D12,D2) =SUMPRODUCT((B2:B12="女")*(D2:D12=7)) 统计人数 =COUNTA(A:A) =COUNTIF(A:A,"> ") 如何统计A1:A10，D1:D10中的人数？ =COUNTA(A1:A10,D1:D10) 如何让EXCEL自动从头统计到当前单元格 情况如下： C列要根据A列的内容来统计B列的数据，范围从A1：An，即当A 列中An有数据时，Cn自动根据An的值，统计B1：Bn的数据。 ｛=SUM(INDIRECT("B1:B" & LARGE((A1:A65535<>"")*(ROW(A1:A65535)),1)))｝ 统计人数 建议提建议人员姓名提建议人数 建议1 王、李、赵、孙、钱、胡 6 建议2 张、王、李、赵、孙、钱、胡7 建议3 张、王、李、孙、钱、胡 6 =LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1 =LEN(SUBSTITUTE(B2,"、","")) 统计人数 见表： 如何计算20-50岁的人数? =COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50") =SUMPRODUCT((C3:C17>=20)*(C3:C17<=50)) =FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19) ｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝ 如何计算男20-50岁的人数? =SUMPRODUCT((B3:B17="男")*(C3:C17>=20)*(C3:C17<=50)) 求各分数段人数 90—100 =COUNTIF(B2:B43,">=90") 80—89 =COUNTIF(B2:B43,">=80")-COUNTIF(B2:B43,">=90") 70—79 =COUNTIF(B2:B43,">=70")-COUNTIF(B2:B43,">=80") 60—69 =COUNTIF(B2:B43,">=60")-COUNTIF(B2:B43,">=70") 50—59 =COUNTIF(B2:B43,">=50")-COUNTIF(B2:B43,">=60") 有什么方法统计偶数 例如：A1到E1有5个数如何统计着五个数中有几个是偶数 A B C D E F 1 50 15 8 11 15 3 在F1中的3要用什么公式能统计出来 统计偶数的个数 ｛=COUNT(1/MOD(A1:E1-1,2))｝ ｛=Sum(Mod(a1:e1+1,2))｝ 将偶数转化成奇数，再求奇数的个数。 请在编辑栏中选择部分公式按F9观察每一步的计算过程。 {=SUM(--((A1:F1)/2=INT((A1:F1)/2)))} 算是一法,长了点 =SUMPRODUCT((MOD(A1:E1,2)=0)*1) =SUMPRODUCT(1-MOD(A1:E1,2)) 如何显示 如果D2>20那E2就显示$200、如果D2>30那E2就显示$300依此类推 解答：=INT(D2/10)*100，当然，你的单元格格式设置成$格式就可以了。否则用，="$"&INT(D2/10)*100 则该单元格成字符型。当然，你也可以用IF函数，但它有7层的限制。= IF (D2>30, "300",IF(D2>20,"200")) 工资统计中的问题 问题：表一和表二中的职工姓名相同,但不在同一个位置上。怎样用公式求出表一中职工在表二中对应的工资、奖金和值班费的总额。要求，不能用表二中先加入一列，然后求和，再用公式导入表一的方法。我想知道能否在表一中用一个公式就可实现，而表二不动。 =SUMPRODUCT((表二!$B$3:$B$42=A3)*(表二!$C$3:$E$42)+(表 二!$G$3:$G$42=A3)*(表二!$H$3:$J$42)) =IF(COUNTIF(表二!$B$3:$B$42,A3),SUMPRODUCT(VLOOKUP(A3,表 二!$B$3:$E$42,{2;3;4},)),SUMPRODUCT(VLOOKUP(A3,表 二!$G$3:$J$42,{2;3;4},))) =IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0)),SUM(OFFSET(表 二!$G$2,MATCH(A3,表二!$G$3:$G$42,0),1,,3)),SUM(OFFSET(表 二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,,3))) =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),SUM(INDIRECT("表 二!H"&MATCH(A3,表二!$G$3:$G$42,0)+2&":J"&MATCH(A3,表 二!$G$3:$G$42,0)+2)),SUM(INDIRECT("表二!C"&MATCH(A3,表 二!$B$3:$B$42,0)+2&":J"&MATCH(A3,表二!$B$3:$B$42,0)+2))) =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),VLOOKUP(A3,表 二!$G$3:$J$42,4,0),VLOOKUP(A3,表二!$B$3:$F$42,4,0)) 统计数据问题一例 如果我想统计50个数据中大于某个值的数据个数，（这个值是在使用时才输入某个单元格的），请问用什么函数。如数据单元格为A1：E10，值的单元格为A11。 1、使用下面的数组公式：{=SUM(IF($A$1:$E$10>$A$11,1))} 2、输入以下函数：=COUNTIF(A1：E10,">"&A11) 根据给定的条件，对数据进行合计 实例：姓名件数（姓名在B307-B313中；件数在C307-C313中） 李六 12 王武 50 李六 18 陈丰 187 李六 49 王武 135 陈丰 1584 目的：对上面三个人的件数分别进行统计分析 步骤：李六的： =SUMIF(B307:B313,B323,C307:C313) 王武的： =SUMIF(B307:B313,C323,C307:C313) 陈丰的： =SUMIF(B307:B313,D323,C307:C313) 姓名：李六王武陈丰（分别在B323、C323、D323单元格中）结果： 79 185 1771 十列数据合计成一列 =SUM(OFFSET($1,(ROW()-2)*10+1,,10,1)) 统计汉字字符个数 中国 A1中"中国",A2中"人民",A3中是空白,A4中是"幸福 ",A5,A6中是空白 人民258 258 幸福 247大家好 中国147 函数结果说明 =SUMPRODUCT(LENB(ASC(A1:A6))-LEN(A1:A6)) 11 仅统计汉字字符个数 =SUMPRODUCT(LEN(A1:A6)) 23 如果还混杂有其它字符 关于取数 购进日期付款期 7月5日2007-8-25 6月5日2007-7-25 7月18日2007-9-15 7月26日2007-9-15 注：我想在B列的付款期中得到这样的结果： 付款期=（购进日期+45天），但我们的付款期只有每月15和25号，如果按购进日期加上45天后不正好是付款日，那就得再往后延到最近的一个付款日，也就是15或25号。 ｛=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70)))｝ ｛=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70),999999))｝ ｛=MIN(IF((DAY(A2+ROW($45:$67))=15)+(DAY(A2+ROW($45:$67))=25),A2+ROW ($45:$67)))｝ =IF(DAY(A2+45)<15,TEXT(A2+60-DAY(A2+45),"mm月dd日 "),TEXT(A2+70-DAY(A2+45),"mm月dd日")) =DATE(YEAR(A2+45),IF(DAY(A2+45)>25,MONTH(A2+45)+1,MONTH(A2+45)),IF(D AY(A2+45)<=15,15,IF(DAY(A2+45)<=25,25,15))) 统计单元格内不为空的格数 如下图，怎么自动统计单元格内的“√”，而空白的单元格则不计入内？ =counta(a2:a31),下拉 =countif(a2:a31,"√") =COUNTIF(a2:a31,"<>") 自动将销量前十名的产品代号及销量填入表中 如：产品代号在“B”列，销量在“C”列 =INDIRECT("b"&MATCH(ROW(A1),$D$2:$D$20,0)+1) =INDIRECT("c"&MATCH(ROW(A1),$D$2:$D$20,0)+1) 统计最大的连续次数 如图，请问如何编写公式求出A1到A10单元格中数字4连在一起的次数，本例中答案应为3（A1到A3）和2（A9到A10）。 [1] A1到A10单元格中, 数字4连在一起, 最大的连续次数, 公式为 : {=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A1 0),"")),1)} [2] 次大的连续长次数, 公式为 : {=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A1 0),"")),2)} 3个“不重复”个数统计 =SUM(--IF(MATCH(B$2:B$21,B$2:B$21,0)=ROW(B$2:B$21)-1,B$2:B$21>B2))+1 =SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1 =SUM(--(FREQUENCY(IF(B$2:B$21>B2,B$2:B$21),B$2:B$21)>0))+1 在一列有重复的姓名中，如何统计出具体有几人 如果第一个张三在A1单元格，在B1处输入： =IF(COUNTIF($A$1:A1,A1)>1,"",A1) 向下复制即可 用数组公式也可以解决呀:假设你要统计A1到A100可以这样: =sum(1/countif(a1:a100,a1:a100),然后按住crtl,shift,和回车就可以了。 计数的问题 这个例子主要是计数的问题：共有三列数据，分别统计每列字母的个数、每列有几个不同的字母，最后把它们分别列出来。对每列字母个数统计，字符用COUNTA()，数字可以用COUNT()和COUNTA()。公式分别为： =COUNT(A2:A12) =COUNTA(B2:B12) =COUNTA(C2:C12) 每列不相同的字母，公式分别为： ｛=SUM(1/COUNTIF(A$2:A$12,A$2:A$12))｝ ｛=SUM(1/COUNTIF(B$2:B$12,B$2:B$12))｝ ｛=SUM(1/COUNTIF(C$2:C$12,C$2:C$12))｝ 分别列出来，公式分别为： ｛=IF(SUM(1/COUNTIF(A$2:A$12,A$2:A$12))>=ROW(A1),INDEX(A$2:A$12,SMAL L(IF(ROW(A$2:A$12)-1=MATCH(A$2:A$12,A$2:A$12,0),ROW(A$2:A$12)-1,"0") ,ROW(A1))),"END")｝ ｛=IF(SUM(1/COUNTIF(B$2:B$12,B$2:B$12))>=ROW(B1),INDEX(B$2:B$12,SMAL L(IF(ROW(B$2:B$12)-1=MATCH(B$2:B$12,B$2:B$12,0),ROW(B$2:B$12)-1,"0") ,ROW(B1))),"END")｝ ｛=IF(SUM(1/COUNTIF(C$2:C$12,C$2:C$12))>=ROW(C1),INDEX(C$2:C$12,SMAL L(IF(ROW(C$2:C$12)-1=MATCH(C$2:C$12,C$2:C$12,0),ROW(C$2:C$12)-1,"0") ,ROW(C1))),"END")｝ 列1列2列3 1 m B 2 n B 3 m C 1 n D 1 m A 2 m B 3 n C 2 n D 1 m A 2 n A 1 m B 对每列字母个数统计: 111111 每列不相同的字母有: 324 它们分别是: 1m B 2n C 3END D END A END 如何分班统计男女人数 姓名班别性别 高健丽 1 女 蔡美燕 2 女 张玉玫 3 女 蔡文文 4 女 陈娇娇 5 女 吴振宇 1 男 周婷婷 6 女 肖欣 6 女 梁丽宝 5 女 邱晓雯 4 女 李春梅 3 女 龙玉桦 2 女 阮梅英 1 女 梁光昕 2 男 班别男女总人数 1 29 45 74 2 30 44 74 3 30 4 4 74 4 31 43 74 5 30 44 74 6 30 45 75 ……… 男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) 女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1)) 男｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝ 女｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$G$1))｝ 男｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝ 女｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)｝ 增加d列，输入公式：=B2&C2，合并数据后再利用countif公式对D列统计。 =COUNTIF($B$2:$B$446,E2) 在几百几千个数据中发现重复项 我的意思不是查找功能,那个我会用,比如有几百个人的名字输入单元格中,但我面对那么多名字真无法短时间内看出谁重复了,该如何办? 假设判断区域为A1:D10,格式/条件格式，选公式(不是数值)，输入： =COUNTIF($A$1:$D$10,A1)>1 然后在格式中设置一个字体或图案颜色,确定,这样重复数据就变成了有色单元格。 统计互不相同的数据个数 例如,在 3 * 3 的区域中统计互不相同的数据个数, 1 2 3 3 2 1 1 2 0 结果应为 4 (4 个互不相同的数据) 数组公式=sum(1/countif(a1:c3,a1:c3)) 还可以公式: =COUNT(IF(FREQUENCY(A1:C3,A1:C3),1)) 多个工作表的单元格合并计算 =Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4) 单个单元格中字符统计 假设 A1单元格中有数据"sdfsfjksfhweofiefondsfljsdfisdofjei" 如何用公式统计出A1单元格中有多个不重复的字符? =SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1)) 数组公式=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),A1)),,1)) 这个公式只适用单元中的字符为小写字母,给个通用点的 =SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIR ECT("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2))))) =SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1)) 数据区包含某一字符的项的总和,该用什么公式 =sumif(a:a,"*"&"某一字符"&"*",数据区) 函数如何实现分组编码 对数值进行分组编码 =A2&TEXT(COUNTIF($A$2:A2,A2),"00") ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 【数值取整及进位】 取整数函数 907.5；1034.2；1500要改变为908；1035；1500公式为： =CEILING(A1,1) 907；1034；1500要改变为910；1040；1500公式为： =CEILING(A1,10) 如果要保留到百位数，即改变为1000；1100；1500公式为： =CEILING(A1,100) 数值取整 在单元格中要取整数（只取整数不用考虑四舍五入）用什么函数呀？例如：10/4只要显示2就可以了！要考虑负数的因数呢？例如：(-10/4)要显示－2而不是－3？怎么办？ =TRUNC(A1,0) excel常用函数公式介绍 excel常用函数公式介绍1：MODE函数应用 1MODE函数是比较简单也是使用最为普遍的函数，它是众数值，可以求出在异地区域或者范围内出现频率最多的某个数值。 2例如求整个班级的普遍身高，这时候我们就可以运用到了MODE 函数了 3先打开插入函数的选项，之后可以直接搜索MODE函数，找到求众数的函数公式 4之后打开MODE函数后就会出现一个函数的窗口了，我们将所要求的范围输入进Number1选项里面，或者是直接圈选区域 5之后只要按确定就可以得出普遍身高这一个众数值了 excel常用函数公式介绍2：IF函数应用 1IF函数常用于对一些数据的进行划分比较，例如对一个班级身高进行评测 2这里假设我们要对身高的标准要求是在170，对于170以及170之上的在备注标明为合格，其他的一律为不合格。这时候我们就要用到IF函数这样可以快捷标注好备注内容。先将光标点击在第一个备注栏下方 3之后还是一样打开函数参数，在里面直接搜索IF函数后打开 4打开IF函数后，我们先将条件填写在第一个填写栏中， D3>=170，之后在下面的当条件满足时为合格，不满足是则为不合格 5接着点击确定就可以得到备注了，这里因为身高不到170，所以备注里就是不合格的选项 6接着我们只要将第一栏的函数直接复制到以下所以的选项栏中就可以了 excel常用函数公式介绍3：RANK函数应用 2这里我们就用RANK函数来排列以下一个班级的身高状况 3老规矩先是要将光标放于排名栏下面第一个选项中，之后我们打开函数参数 4找到RANK函数后，我们因为选项的数字在D3单元格所以我们就填写D3就可了，之后在范围栏中选定好，这里要注意的是必须加上$不然之后复制函数后结果会出错 5之后直接点击确定就可以了，这时候就会生成排名了。之后我们还是一样直接复制函数黏贴到下方选项栏就可以了。 电子表格常用函数公式 1、自动排序函数： =RANK(第1数坐标，$第1数纵坐标$横坐标：$最后数纵坐标$横坐标，升降序号1降0升) 例如：=RANK(X3,$X$3:$X$155,0) 说明：从X3 到X 155自动排序 2、多位数中间取部分连续数值： =MID（该多位数所在位置坐标，所取多位数的第一个数字的排列位数，所取数值的总个数） 例如：612730************在B4坐标位置，取中间出生年月日，共8位数 =MID(B4，7，8) =19820711 说明：B4指该数据的位置坐标，7指从第7位开始取值，8指一共取8个数字 3、若在所取的数值中间添加其他字样， 例如：612730************在B4坐标位置，取中间出生年、月、日，要求****年**月**日格式 =MID(B4，7，4)&〝年〞&MID(B4，11，2) &〝月〞& MID(B4，13，2) &〝月〞& =1982年07月11日 说明：B4指该数据的位置坐标，7、11指开始取值的第一位数排序号，4、2指所取数值个数，引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板：首先利用Word制作一个奖状模板并保存为“奖状.doc”，将其中班级、姓名、获奖类别先空出，确保打印输出后的格式与奖状纸相符（如图1所示）。 第二步用Excel建立获奖数据库：在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”，格式如图2所示。 第三步关联数据库与奖状：打开“奖状.doc”，依次选择视图→工具栏→邮件合并，在新出现的工具栏中选择“打开数据源”，并选择“奖状数据.xls”，打开后选择相应的工作簿，默认为sheet1，并按确定。将鼠标定位到需要插入班级的地方，单击“插入域”，在弹出的对话框中选择“班级”，并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印：选择“查看合并数据”，然后用前后箭头就可以浏览合并数据后的效果，选择“合并到新文档”可以生成一个包含所有奖状的Word文档，这时就可以批量打印了。 15个常用的Excel函数公式，拿来即用 1、查找重复内容 =IF(COUNTIF(A:A,A2)>1,"重复","") 2、重复内容首次出现时不提示 =IF(COUNTIF(A$2:A2,A2)>1,"重复","") 3、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","") 4、根据出生年月计算年龄 =DATEDIF(A2,TODAY(),"y") 5、根据身份证号码提取出生年月 =--TEXT(MID(A2,7,8),"0-00-00") 6、根据身份证号码提取性别 =IF(MOD(MID(A2,15,3),2),"男","女") 7、几个常用的汇总公式 A列求和：=SUM(A:A) A列最小值：=MIN(A:A) A列最大值：=MAX (A:A) A列平均值：=AVERAGE(A:A) A列数值个数：=COUNT(A:A) 8、成绩排名 =RANK.EQ(A2,A$2:A$7) 9、中国式排名（相同成绩不占用名次） =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 10、90分以上的人数 =COUNTIF(B1:B7,">90") 11、各分数段的人数 同时选中E2:E5，输入以下公式，按Shift+Ctrl+Enter =FREQUENCY(B2:B7,{70;80;90}) 12、按条件统计平均值 =AVERAGEIF(B2:B7,"男",C2:C7) 13、多条件统计平均值 =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售") Excel常用函数公式大全 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格； 常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。 2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。 2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。 四、求和公式 1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法 3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符* 5、多表相同位置求和 公式：b2 =SUM(Sheet1:Sheet19!B2) 说明：在表中间删除或添加表后，公式结果会自动更新。 6、按日期和产品求和 EXCEL常用函数大全（做表不求人！） 2013-12-03 00:00 我们在使用Excel制作表格整理数据的时候，常常要用到它的函数功能来自动统计处理表格中的数据。这里整理了Excel中使用频率最高的函数的功能、使用方法，以及这些函数在实际应用中的实例剖析，并配有详细的介绍。 1、ABS函数 函数名称：ABS 主要功能：求出相应数字的绝对值。 使用格式：ABS(number) 参数说明：number代表需要求绝对值的数值或引用的单元格。 应用举例：如果在B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。 特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！”。 2、AND函数 函数名称：AND 主要功能：返回逻辑值：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 使用格式：AND(logical1,logical2, ...) 参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多这30个。 应用举例：在C5单元格输入公式：=AND(A5>=60,B5>=60)，确认。如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。 国美提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。 EXCEL常用函数公式大全及举例 一、相关概念 （一）函数语法 由函数名+括号+参数组成 例：求和函数：SUM（A1,B2,…) 。参数与参数之间用逗号“,”隔开（二）运算符 1. 公式运算符：加（+）、减（-）、乘（*）、除（/）、百分号（%）、乘幂（＾） 2. 比较运算符：大与（>）、小于（<）、等于（=）、小于等于（<=）、大于等于（>=）、不等于（<>） 3. 引用运算符：区域运算符（：）、联合运算符（,） （三）单元格的相对引用与绝对引用 例： A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 （一）数学函数 1. 求和 =SUM（数值1,数值2,……） 2. 条件求和 =SUMIF（查找的范围,条件(即对象),要求和的范围） 例：（1）=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思：对第A1栏至A4栏中，大于等于200的数值对应的第B1列至B4列中数值求和 （2）=SUMIF(A1:A4,”<300”,C1:C4) 函数意思：对第A1栏至A4栏中，小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数 =COUNT（数值1,数值2,……） 例：（1） =COUNT(A1:A4) 函数意思：第A1栏至A4栏求个数（2） =COUNT(A1:C4) 函数意思：第A1栏至C4栏求个数 4. 条件求个数 =COUNTIF（范围,条件） 例：（1） =COUNTIF(A1:A4，”<>200”) 函数意思：第A1栏至A4栏中不等于200的栏求个数 （2）=COUNTIF(A1:C4,”>=1000”) 函数意思：第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数 =AVERAGE（数值1,数值2,……） 例：（1） =AVERAGE(A1,B2) （2） =AVERAGE(A1:A4) 6. 四舍五入函数 =ROUND(数值,保留的小数位数) 7. 排位函数 =RANK（数值,范围,序别） 1-升序 0-降序 例：（1） =RANK(A1,A1:A4,1) 函数意思：第A1栏在A1栏至A4栏中按升序排序，返回排名值。 （2） =RANK(A1,A1:A4,0) 函数意思：第A1栏在A1栏至A4栏中按降序排序，返回排名值。 8. 乘积函数 =PRODUCT（数值1,数值2,……） 9. 取绝对值 =ABS(数字) 10. 取整 =INT(数字) （二）逻辑函数 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式： =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格； （5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格； （6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格； （7）=COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格； excel公式笔记 一、vlookup 1.查找A列中第一个以”厦门”开头的记录对应B列的值。 =vlookup(H厦门性A:B20) 其中第一个参数为要寻找的文本，第二个参数为一个区域，第二个参数的第一列为要寻找的区域,第三个参数的2表示第二个参数的第二列显示出来,第四个参数的0表示精确查找。 二、countif 1.统计Al:A10区域中型号为" 2.5m*3m"的记录个数。 =countif(Al:A10,,,2.5m~*3m H) 在excel常用函数中,支持通配符的主要有vlookup、hlookup> match> sum讦、courttif、search> searchB,而find、findB> subsitute 不支持通配符。*表示任意字符,？表示单个字符解除字符的通配性。 2.统计Al:A10中不重复数的个数。 =SUMPRODUCT((1/COUNTIF(A1:A10,A1:A10))) 3?求小于60的数据有多少 二count(A2:AKVv6(T) sumproduct 1?求购物总花费,A列表示购买数量月列表示购买单价 =sumproduct(A2:A8,B2:B8) 意思为A2*B2+A3*B3oooo +A8*B8 2.求二班有多少学生学习了数学 =sumproduct((A2:A10=H~ B,,)*(B2:B10=H数学“)) 表示二班的数学有几个 3.求二班数学分数总和 =sumproduct((A2:A10=M Z：B M)*(B2:B10=H数学H)*(C2:C1O)) 4.统计“技术部”考试成绩为0的个数(缺考除外) =sumproduct((B2:B9=H技术部,,)*(E2:E9=0)*(E2:E9o,,H)) excel会将空值看成0,所以在统计成绩为0的考生时，需要把成绩为空的考牛去除。 excel常用公式函数大全 1．求和函数SUM 语法：SUM（number1，number2，．．．）。 参数：number1、number2．．．为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。 注意：参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。如果参数为数组或引用，只有其中的数字参与计算，数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。 应用实例一：跨表求和 使用SUM函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域进行求和，可以采用以下方法：选中Excel XP“插入函数”对话框中的函数，“确定”后打开“函数参数”对话框。切换至第一个工作表，鼠标单击“number1”框后选中需要求和的区域。如果同一工作表中的其他区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。 应用实例二：SUM函数中的加减混合运算 财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。按照规定，工作表中的这些项目没有输入负号。这时可以构造“=SUM（B2:B6，C2:C9，-D2，-E2）”这样的公式。其中B2:B6，C2:C9引用是收入，而D2、E2为支出。由于Excel不允许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出正确结果。即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:B6，C2:C9，-D2，-D3，D4）”这样的形式。 应用实例三：及格人数统计 假如B1:B50区域存放学生性别，C1:C50单元格存放某班学生的考试成绩，要想统计考试成绩及格的女生人数。可以使用公式“=SUM（IF（B1:B50=″女″，IF（C1:C50>=60，1，0）））”，由于它是一个数组公式，输入结束后必须按住Ctrl+Shift键回车。公式两边会自动添加上大括号，在编辑栏显示为“{=SUM（IF （B1:B50=″女″，IF（C1:C50>=60，1，0）））}”，这是使用数组公式必不可少的步骤。 2.平均值函数AVERAGE 语法：AVERAGE（number1，number2，．．．）。 参数：number1、number2．．．是需要计算平均值的1～30个参数。 注意：参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。如果需要将参数中的零排除在外，则要使用特殊设计的公式，下面的介绍。 应用实例一：跨表计算平均值 EXCEL的常用计算公式大全 一、单组数据加减乘除运算： ①单组数据求加和公式：=（A1+B1） 举例：单元格A1：B1区域依次输入了数据10和5，计算：在C1中输入 =A1+B1 后点击键盘“Enter（确定）”键后，该单元格就自动显示10与5的和15。 ②单组数据求减差公式：=（A1-B1） 举例：在C1中输入 =A1-B1 即求10与5的差值5，电脑操作方法同上； ③单组数据求乘法公式：=（A1*B1） 举例：在C1中输入 =A1*B1 即求10与5的积值50，电脑操作方法同上； ④单组数据求乘法公式：=（A1/B1） 举例：在C1中输入 =A1/B1 即求10与5的商值2，电脑操作方法同上； ⑤其它应用： 在D1中输入 =A1^3 即求5的立方（三次方）； 在E1中输入 =B1^（1/3）即求10的立方根 小结：在单元格输入的含等号的运算式，Excel中称之为公式，都是数学里面的基本运算，只不过在计算机上有的运算符号发生了改变——“×”与“*”同、“÷”与“/”同、“^”与“乘方”相同，开方作为乘方的逆运算，把乘方中和指数使用成分数就成了数的开方运算。这些符号是按住电脑键盘“Shift”键同时按住键盘第二排相对应的数字符号即可显示。如果同一列的其它单元格都需利用刚才的公式计算，只需要先用鼠标左键点击一下刚才已做好公式的单元格，将鼠标移至该单元格的右下角，带出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可，即可完成公司自动复制，自动计算。 二、多组数据加减乘除运算： ①多组数据求加和公式：(常用) 举例说明：=SUM（A1:A10）,表示同一列纵向从A1到A10的所有数据相加； =SUM（A1:J1）,表示不同列横向从A1到J1的所有第一行数据相加； ②多组数据求乘积公式：（较常用） 举例说明：=PRODUCT（A1:J1）表示不同列从A1到J1的所有第一行数据相乘； =PRODUCT（A1:A10）表示同列从A1到A10的所有的该列数据相乘； ③多组数据求相减公式：(很少用) 举例说明：=A1-SUM（A2:A10）表示同一列纵向从A1到A10的所有该列数据相减； =A1-SUM（B1:J1）表示不同列横向从A1到J1的所有第一行数据相减； ④多组数据求除商公式：(极少用) 举例说明：=A1/PRODUCT（B1:J1）表示不同列从A1到J1的所有第一行数据相除； =A1/PRODUCT（A2:A10）表示同列从A1到A10的所有的该列数据相除； 三、其它应用函数代表： ①平均函数 =AVERAGE（:）；②最大值函数 =MAX （:）；③最小值函数 =MIN （:）； ④统计函数 =COUNTIF（:）：举例：Countif ( A1：B5，”>60”) 说明：统计分数大于60分的人数，注意，条件要加双引号，在英文状态下输入。 工作中最常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。 2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。 2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。 四、求和公式 1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法 3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符* Excel函数公式 在会计同事电脑中，保保经常看到海量的Excel表格，员工基本信息、提成计算、考勤统计、合同管理.... 看来再完备的会计系统也取代不了Excel表格的作用。 于是，小呀尽可能多的收集会计工作中的Excel公式，所以就有了这篇本平台史上最全的Excel公式+数据分析技巧集。 员工信息表公式 1、计算性别(F列) =IF(MOD(MID(E3,17,1),2),"男","女") 2、出生年月(G列) =TEXT(MID(E3,7,8),"0-00-00") 3、年龄公式(H列) =DATEDIF(G3,TODAY,"y") 4、退休日期(I列) =TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa") 5、籍贯(M列) =VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,) 注：附带示例中有地址库代码表 6、社会工龄(T列) =DATEDIF(S3,NOW,"y") 7、公司工龄(W列) =DATEDIF(V3,NOW,"y")&"年"&DATEDIF(V3,NOW,"ym")&"月"&DATEDIF(V3,NOW,"md")&"天" 8、合同续签日期(Y列) =DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1 9、合同到期日期(Z列) =TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY,"[<0]过期0天;[<30]即将到期0天;还早") 10、工龄工资(AA列) =MIN(700,DATEDIF($V3,NOW,"y")*50) 11、生肖(AB列) =MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1) 1、本月工作日天数(AG列) =NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),) 2、调休天数公式(AI列) =COUNTIF(B9:AE9,"调") 3、扣钱公式(AO列) 婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元 =SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10}) 1、本科学历人数 =COUNTIF(D:D,"本科") 2、办公室本科学历人数 =COUNTIFS(A:A,"办公室",D:D,"本科") 3、30~40岁总人数 =COUNTIFS(F:F,">=30",F:F,"<40") 1、提成比率计算 =VLOOKUP(B3,$C$12:$E$21,3) 电子表格常用函数公式 1．去掉最高最低分函数公式： =SUM（所求单元格‘注：可选中拖动’）—MAX（所选单元格‘注：可选中拖动’）—MIN（所求单元格‘注：可选中拖动’） （说明：“SUM”是求和函数，“MAX”表示最大值，“MIN”表示最小值。） 2．去掉多个最高分和多个最低分函数公式： =SUM（所求单元格）—large(所求单元格，1)—large(所求单元格，2) —large(所求单元格，3)—small(所求单元格，1) —small(所求单元格，2) —small(所求单元格，3) （说明：数字123分别表示第一大第二大第三大和第一小第二小第三小，依次类推） 3．计数函数公式： count 4．求及格人数函数公式：（”>=60”用英文输入法） =countif(所求单元格，”>=60”) 5．求不及格人数函数公式：（”<60”用英文输入法） =countif(所求单元格，”<60”) 6．求分数段函数公式：（“所求单元格”后的内容用英文输入法）90以上：=countif(所求单元格，”>=90”) 80——89：=countif(所求单元格，”>=80”)—countif(所求单元格，”<=90”) 70——79：=countif(所求单元格，”>=70”)—countif(所求单元格，”<=80”) 60——69：=countif(所求单元格，”>=60”)—countif(所求单元格，”<=70”) 50——59：=countif(所求单元格，”>=50”)—countif(所求单元格，”<=60”) 49分以下：=countif(所求单元格，”<=49”) 7．判断函数公式： =if(B2,>=60,”及格”，”不及格”) （说明：“B2”是要判断的目标值，即单元格） 8．数据采集函数公式： =vlookup(A2,成绩统计表，2，FALSE) （说明：“成绩统计表”选中原表拖动，“2”表示采集的列数） 公式是单个或多个函数的结合运用。 AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。条件判断 AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 工作中最常用的excel函数公式大全，帮你整理齐了，拿来即用 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。 2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。 2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。 四、求和公式 1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法 3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符* 5、多表相同位置求和 公式：b2 =SUM(Sheet1:Sheet19!B2) 说明：在表中间删除或添加表后，公式结果会自动更新。 6、按日期和产品求和 公式：F2 常 用 函 数 公式及技巧 目录 从身份证号码中提取出生年月日.............................................................................................. - 1 - 从身份证号码中提取出性别...................................................................................................... - 1 - 从身份证号码中进行年龄判断.................................................................................................. - 2 - 按身份证号号码计算至今天年龄.............................................................................................. - 2 - 按身份证号分男女年龄段.......................................................................................................... - 2 - 根据出生年月计算年龄.............................................................................................................. - 3 - 根据出生年月推算生肖.............................................................................................................. - 3 - 如何求出一个人到某指定日期的周岁？.................................................................................. - 3 - 计算距离退休年龄的公式.......................................................................................................... - 3 - 求工齡.......................................................................................................................................... - 3 - 计算工龄...................................................................................................................................... - 4 - 年龄及工龄计算.......................................................................................................................... - 4 - 自动显示当前日期公式.............................................................................................................. - 5 - 如何在单元格中自动填入当前日期.......................................................................................... - 5 - 如何判断某日是否星期天.......................................................................................................... - 5 - 某个日期是星期几...................................................................................................................... - 5 - 什么函数可以显示当前星期...................................................................................................... - 5 - 求本月天数.................................................................................................................................. - 5 - 用公式算出除去当月星期六、星期日以外的天数.................................................................. - 6 - 显示昨天的日期.......................................................................................................................... - 6 - 关于取日期.................................................................................................................................. - 6 - 如何对日期进行上、中、下旬区分.......................................................................................... - 6 - 如何获取一个月的最大天数...................................................................................................... - 6 - 日期格式转换公式...................................................................................................................... - 6 - 把198405转换成1984.05 .......................................................................................................... - 7 - 象22怎样转换成22日？转成当年当月的日子...................................................................... - 8 - 将“2006年5月”转换成“2006年05月”................................................................................... - 8 - 将“1968年6月12日”转换为“1968/6/12”格式........................................................................ - 8 - 将“1968年6月12日”转换为“1968-6-12”格式 ....................................................................... - 8 - 将1993-12-28的日期格式转换成1993年12月 ..................................................................... - 8 - 将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式 ........................................ - 8 - 要将“99.08.15” 格式转换成“1999.08.15”如何做..................................................................... - 8 - 要保持2005/8/6格式 ................................................................................................................. - 8 - 将“二〇〇三年十二月二十五日”转为“2003-12-25”格式， .................................................... - 9 - 日期格式转换.............................................................................................................................. - 9 - 日期格式转换问题...................................................................................................................... - 9 - 要想自动取得“编制日期：XXXX年X月X日” .................................................................... - 9 - 自动排序.................................................................................................................................... - 10 - 按奇偶数排序............................................................................................................................ - 10 - 自动生成序号............................................................................................................................ - 10 - 如何自动标示A栏中的数字大小排序？............................................................................... - 10 - 如何设置自动排序.................................................................................................................... - 10 -excel常用函数公式介绍
电子表格常用函数公式
15个常用的Excel函数公式
Excel常用函数公式大全(实用)
常用excel函数公式大全
EXCEL常用函数大全
EXCEL常用函数公式大全与举例
(完整版)excel基本常用函数公式大全
excel常用函数公式(有实例有分析有重点).docx
excel常用公式函数大全
Excel常用的函数计算公式大全(一看就会)
(完整版)工作中最常用的excel函数公式大全
Excel函数公式
电子表格常用函数公式
工作中最常用地excel函数公式大全
Excel常用函数公式及技巧