(Excel)常用函数公式及操作技巧之六:汇总计算与统计(一)
(Excel)常用函数公式及操作技巧之六:
汇总计算与统计(一)
——通过知识共享树立个人品牌。
个调税公式
=MAX(($A1-1900)*{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} -{0,0,25,125,375,1375,3375,6375,10375,15375}) {0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} 为税率 {0,0,25,125,375,1375,3375,6375,10375,15375} 为税收扣除数
上列公式的简化式 :
=MAX(应纳税所得额*0.05*{1,2,3,4,5,6,7,8,9} -25*{0,1,5,15,55,135,255,415,615},0)
算物价的函数
物价的那个三七作五,二舍八入的尾数处理,做一个函数。就是小数点后面第二位如果是1,2的就舍掉,如果是3,4,5,6,7的都变为5,如果是8,9的小数点第一位加1,第二位就变为0。比如价格是3.32、3.31,作尾数处理就是3.3;价格是3.33、3.34、3.36、3.37,做尾数处理就是3.35;价格是3.38、3.39,做尾数处理就是3.4。
=CEILING(A1-0.02,0.05)
都是二位小数
B2=ROUND(2*A2,1)/2
超过二位小数
B2=ROUND(2*ROUNDDOWN(A2,2),1)/2
自动计算应收款滞纳金
要求在给定的应收日期、滞纳金率、当前日期(自动取)的基础上自动计算出应收滞纳金。
解答:
=(DATEDIF(应收日期,NOW(),"d"))*滞纳金率(每天)*应收金额
淘汰率
题目如下:这个工厂有1000人,今天抽出十人来做调查,这十人一天的产量分别为101 102 105 106 98 95 96 104 110 103 (A3-A12)。
1000人当中淘汰率为5%,以这十人为标准那么这1000人他们的生产应该为多少才不会被淘汰,看看函数的帮助就知道了呀,返回数组K百分比值点,你要1000人淘汰5人就是5/1000=0.5%=0.005,就是你以这10个抽样调查的数据为基准,只要产量达到这个数就不会被淘汰了。(95.45)
公式=PERCENTILE(A3:A12,G1)
应用公积金的一个函数
我公司职工公积金比例为26% 也就是个人和单位各13%,给公积金投缴人员制作了一个函数。直接用基数乘以比例 基数*比例=投缴额, 对于投缴额的要求是:取最接近“投缴额”的偶数。
我制作的函数是“=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),CEILING(A1*B1,2))”
注:A1=基数 B1=投缴比例
也可以改成这样
=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),INT(A1*B1)+1) 或=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),EVEN(A1*B1))
如何利用公式将数值转为百分比格式
如用公式将1.289675显示为128.97%,不是用格式来达到的。
=ROUND(B1*100,1)&"%"
比高得分公式
=RANK(B4,$B$4:$B$26,1)
自动评定奖级
=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2) =LOOKUP(L179,{0,4,7,12,24},{"一等奖","二等奖","三等奖","纪念奖","纪念奖"})
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
对带有单位的数据如何进行求和
在数据后必须加入单位,到最后还要统计总和,请问该如何自动求和?(例如: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)
作者:
RDIF
出处:
http://www.cnblogs.com/huyong/
Email:
406590790@qq.com
QQ:
406590790
微信:
13005007127(同手机号)
框架官网:
http://www.guosisoft.com/
http://www.rdiframework.net/
框架其他博客:
http://blog.csdn.net/chinahuyong
http://www.cnblogs.com/huyong
国思RDIF开发框架
,
给用户和开发者最佳的.Net框架平台方案,为企业快速构建跨平台、企业级的应用提供强大支持。
关于作者:系统架构师、信息系统项目管理师、DBA。专注于微软平台项目架构、管理和企业解决方案,多年项目开发与管理经验,曾多次组织并开发多个大型项目,在面向对象、面向服务以及数据库领域有一定的造诣。现主要从事基于
RDIF
框架的技术开发、咨询工作,主要服务于金融、医疗卫生、铁路、电信、物流、物联网、制造、零售等行业。
如有问题或建议,请多多赐教!
本文版权归作者和CNBLOGS博客共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,如有问题,可以通过微信、邮箱、QQ等联系我,非常感谢。