(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)

算物价的函数

物价的那个三七作五,二舍八入的尾数处理,做一个函数。就是小数点后面第二位如果是12的就舍掉,如果是34567的都变为5,如果是89的小数点第一位加1,第二位就变为0。比如价格是3.323.31,作尾数处理就是3.3;价格是3.333.343.363.37,做尾数处理就是3.35;价格是3.383.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},{"一等奖","二等奖","三等奖","纪念奖","纪念奖"})

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

在数据后必须加入单位,到最后还要统计总和,请问该如何自动求和?(例如:A12KGA26KG.....,在最后一行自动计算出总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列求和,该公式如何做?

假设a1t1为数据(共有20列),在任意单元格中输入公式:

=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))

ctrlshiftenter结束即可求出每隔三行之和。

跳行设置:如有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)

 

posted @ 2012-02-15 11:06  .NET快速开发框架  阅读(34228)  评论(0编辑  收藏  举报