对带有单位的数据如何进行求和
在数据后必须加入单位,到最后还要统计总和,请问该如何自动求和?(例如: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)
更多推荐
excel常用函数公式及技巧搜集5
发布评论