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

对带有单位的数据如何进行求和

在数据后必须加入单位，到最后还要统计总和，请问该如何自动求和？（例如：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的量:

A	B	C
	型号	数量
1	CRVABC12	100
2	CVABC13	102
3	CVBC12	104
4	CNVBC13	106

=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<LARGE(A$1:A$7,2))*(A$1:A$7>SMALL(A$1:A$7,2))*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,"、",""))

统计人数

见表：

性别	年龄
男	6
女	35
男	3
男	55
男	21
男	53.5
女	55
女	56
男	65
女	45
女	53
男	51

如何计算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(DAY(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:A10),"")),1)}

[2] 次大的连续长次数, 公式为 :

{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),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,SMALL(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,SMALL(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,SMALL(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
对每列字母个数统计:
11	11	11
每列不相同的字母有:
3	2	4
它们分别是:
1	m	B
2	n	C
3	END	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	44	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(INDIRECT("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)

=ROUNDDOWN(A1,0)

求余数的函数

比如：A1=28，A2=（A1÷6）的余数=4，请问这个公式怎么写？ 

解答：=MOD(28,6)

四舍五入公式

=ROUND()

=ROUND($B$1*A1,2)

=ROUND(B1*A1,2)

=round(a1,0)

=round(a1,0)*0.95

对数字进行四舍五入

对于数字进行四舍五入,可以使用INT（取整函数），但由于这个函数的定义是返回实数舍入后的整数值。因此，用INT函数进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。公式应写成：

=INT(B2*100+0.5)/100

如何实现“见分进元”

在我们的工资中，有一项“合同补贴”，只要计算结果出现“分”值就在整数“元”进一位，也就是说3.01元进到4.00元，3.00元不变，整数“元”不变。

=IF((A3-INT(A3))>=0.3,IF((A3-INT(A3))>=0.8,1,0.5),0)+INT(A3)

＝IF(RIGHT(FIXED(A1,2),2)>B1,TRUNC(A2)+1,A2)

说明一下：A1即是要转换的目标；B2输入00（文本格式，必须是00这两个数） 。

＝IF(INT(A1)<>A1,INT(A1)+1,A1)

=ROUNDUP(A1,0)

=CEILING(A9,1)

=INT(A9+1)

四舍五入

如何将Excel 中的数据，希望把千位以下的数进行四舍五入，例如：3245  希望变成3000；3690 希望成为400

=ROUND(C6*D6,2)

=ROUND(A2*0.001,)*1000

=ROUND(A2,-3)

=--FIXED(A2,-3)

=ROUND(A2/1000,0)*1000

如何四舍五入取两位小数

如何四舍五入取两位小数,如2.145为2.15,0.1449为0.14.

=ROUND(A1,2)

根据给定的位数，四舍五入指定的数值

对整数无效。四舍五入B234的数值，变成小数点后一位。

12512.2514     12512.3

=ROUND(B23,1)

四舍六入

=IF(MOD(INT(A1),2)=0,IF(MOD(A1,1)=0.5,INT(A1),INT(A1+0.5)),INT(A1+0.5))

=IF(AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE,IF(INT(A1)/2=INT(INT(A1)/2),INT(A1),ROUND(A1,0)),ROUND(A1,0))

AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE 判断是否为一位小数,且是0.5，如果不符合上术要条件,按普通四舍五入法则处理,否则判断整数部分的奇偶。

=IF(RIGHT(A1,1)*1<5,INT(A1),IF(RIGHT(A1,1)*1>5,INT(A1)+1,IF(MOD(ROUND(A1,),2)=0,ROUND(A1,),ROUNDDOWN(A1,))))

=IF(ROUNDUP(A1*2,)=A1*2,IF(MOD(ROUND(A1,),2)=1,ROUNDDOWN(A1,),ROUNDUP(A1,)),ROUND(A1,))

如何实现2舍3入

做工资时，常遇到：3.2元要舍去0.2元变为3.00元,而3.3元要把0.3元入为0.5元变为3.5元.请教,该如何实现?

=ROUND(A1*2,0)/2

=CEILING(A1,0.5)

=IF((A1-INT(A1))<=0.2,INT(A1),IF((A1-INT(A1))<=0.5,INT(A1)+0.5,IF((A1-INT(A1))<=0.7,INT(A1),INT(A1)+1)))

=CEILING(A1-0.2,0.5)

=FLOOR(A1+0.2,0.5)

怎么设置单元格以千元四舍五入

比如输入123456，显示出来123，000

=CEILING(ROUND(A1/1000,0),1)*1000

=round(a1,-3)

=mround(A1,1000)

ROUND函数的四舍五入不进位的解决方法？

计算一：A2=1345.3  B2=1232.4  C3=A2-B2=112.9   D=0.05  E=ROUND(B2*D2,2)=5.64  （计算结果为5.645，此运算没有进位）。

计算二：A2=1225.4  B2=1112.5  C3=A2-B2=112.9   D=0.05  E=ROUND(B2*D2,2)=5.65（计算结果为5.645，此运算进位）。

以上两式中C3结果都为112.9，而为什么应用ROUND函数后结果却不一样。

请教高手有什么函数能保证四舍五入不会出错。

可将C列先变成文本性数据，再进行后面的运算，以达到计算的目的。

如：C列可改成C1=TRIM(A1-B1)，以此类推，只要是更改成文本性数据就行。

保留一位小数

我需要保留一位小数,不管后面是什么数字,超过5或不超过5,都向前进一位.

例如:329.99-->330.00

329.84----->329.90

329.86----->329.90

=roundup(*,2)或=round(a1+0.04,1)

如何三舍四入

=round(原数值+0.001,2)

另类四舍五入

我用Excle给别人算帐，由于要对上百家收费，找零却是个问题。于是我提出四舍五入，收整元。但是领导不同意，要求收取0.5元。例如：某户为123.41元，就收123.50元;如果是58.72元，就收58.5元。这可难坏了我。经过研究，我发现，可以在设置单元格中，设成分数，以2为分母，可以解决问题。但是打印出来的却是分数不好看，而且求和也不对。请各位高手给予指点。是这样的，如果是57.01元，则省去，即收57.00元；如果是57.31元，则进为57.50元；如果是57.70元，也收57.50元；要是57.80元，则收58.00元。

假设数据在A1

=INT(A1)+IF((A1-INT(A1)<=0.3),0,IF((A1-INT(A1)>0.7),1,0.5))

简化一下：

=INT(A1)+0.5*((A1-INT(A1)>0.3)+(A1-INT(A1)>0.7))

int函数取整数部分，A1-int(A1)取小数部分，根据你的意思：<=0.3按0算，0.3~0.7(含)按0.5算，0.7~0.99……按+1算

则：第一个公式不难理解了

简化公式中：“*((A1-INT(A1)>0.3)+(A1-INT(A1)>0.7))”即(小数部分>0.3)+(小数部分>0.7)

我们知道这是省略if的判断语句，条件为真返回true（也就是1）否在为false（0），那么如果小数<=0.3，则两个条件都为0，即整数部分+0.5*0=整数部分，介于0.3~0.7，则为整数部分+0.5*（1+0），大于0.7肯定也大于0.3啦，则为整数部分+0.5*（1+1）。

请问，如果是由几个分表汇总的总表想如此处理，该如何做。

例：e112位置=SUM(一库入库!G112,二库入库!G112,四库入库!G112,保健酒基地入库!G112,下陆仓库入库!G112)

汇总的结果为100.24，而我要求如果小数为24的话自动视为1累加，否则不便。

就是小数为0.24才加1，否则都舍掉？

若是：=if（sum公式-int（sum公式）=0.24，int（sum公式）+1，sum公式）

想把小数点和后面的数字都去掉，不要四舍五入

比如：        

12.30    变成         12.00

45.32                 45.00

25.38                 25.00

6.54                   6.00

13.02                 13.00

59.68                 59.00

23.62                 23.00

=Rounddown(A1,0)

你要把A1换成你要转换的那个单元格啊，然后拖动就可以了！

我那里用的那个A1只是告诉你一个例子而已，你要根据你的实际情况来修改一下才能用的。

=INT(A1)

=TRUNC(A1,0)

求真正的四舍五入后的数

请教如何在Excel中，求“金额合计”（小数点后二位数）时，所取的数值应是所求单元格中写的数字（四舍五入后的数字），而不是（四舍五入前）的数字。因为只有这样行和列及关联的工作表才能对得上，例如：表上的数值分别是：(1.802/2=0.901)0.90(A1); (1.604/2=0.802)0.80(A2);  (1.406/2=0.703)0.70(A3);（因取小数点后二位）。合计数(A4)表中自己计算和显示是：(0.901+0.802+0.703=2.406)2.41（四舍五入后的数值）。但照表中的数值人工计算却是：(0.9+0.8+0.7=)2.4,有矛盾，还有许多例子，故请教各高手，如何设置公式，使得人工计算结果同表中一致。请指教。十分感谢！

工具》选项》重新计算》以显示精度为准   前打钩

也可以用函数 ROUND() 使结果四舍五入 。如ROUND（算式，2）代表保留两位小数，如ROUND（算式，1）代表保留一位小数。

小数点进位

小数点进位如何把1.4进成2或1.3进成2

=Ceiling(A1,1)

=Roundup(A1,0)

=INT(A1+0.9)

 =int(a1)+1

如何把1.4进成2，而1.2不进位

=ROUND(A1+0.1,0)

个位数归0或者归5

A*B后想得到C的结果值，用什么函数比较好

A          B         C(想得到的数值)

320        1.1               355

1140       1.2               1370

50         1.3               65

16         1.4               25

=FLOOR(A1*B1+5*(MOD(A1*B1,5)<>0),5)

=CEILING(A1*B1,5)