学Excel速成班心得,SUMIFS帮您按月总结

问题:Excel问题?

自小编加入Excel磨练营速成班至现在已有半个多月了,收获累累,从起头的Excel认识、文件操作、窗口与界面操作、工作表的应用、自定义访问工具栏等等再到函数。函数我们日常都会在耳边说起,但有多少人确实的会用呢?sum这么些函数大家并不素不相识,基本用法我们都会用。前天自身为大家享受的正是sum这么些函数但不是核心用法哦!而是比较复杂累计求和,综合选择,与sumif的连用和境遇文本格式的求和。(1)首先介绍一下怎么用sum函数累计求和,实际非常粗略就在D2单元格中输入”=SUM($C$2:C2)”,然后在下拉填充即可求出累计数。如图1

 (Excel)常用函数公式及操作技巧之六:

难点根源

回答:

皇家赌场网址hj9292 1

汇总总括与总括(一)

明日,朋友传来EXCEL文件,内有两张工作表,一张是依据日期记录的商品销量,另一张是按月计算的模版,请韩先生帮忙总结每个产品的月销量。

化解措施:

(2)SUM综合采用,小编认为普通使用的比较多的机关应当是财务,销售,仓库等机构,可是又有个别许人领悟那些公式了?估算唯有百分之几的人能会用吧!作者在这边举个列子吧!上面图第22中学是某商店从十6月5日到五月20入库A产品明细,在入库的还要也有出库,问5月17日此公司A产品结存多少?

——通过知识共享树立个人品牌。

货物销量记录表:

此间没领会是将颜色填充到“缺货”的日期里,依然将颜色一贯填充直到缺货;我们以第2种意况为例子,如下图效果。

皇家赌场网址hj9292 2

 

皇家赌场网址hj9292 3

皇家赌场网址hj9292 41.在C4单元格内输入公式=SUM(E4:N4),
在D4单元格输入公式=B4-C4,向下填充好公式;选用D4:D6,设置标准格式“小于0”彰显芙蓉红;

(3)与sumif连用时应有怎么设置呢?图3是某商厦1到2月份的入库数量,与出库数量和结存,求的是7到8月份的入库数量多少?

 

按月总结的沙盘:

皇家赌场网址hj9292 52.在O4:O6单元格内输入公式=IF(D4<0,”仓库储存不足,请补仓”,””)。

皇家赌场网址hj9292 6

个调税公式

=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}  为税收扣除数

皇家赌场网址hj9292 7

皇家赌场网址hj9292 83.
增选日期下方E4:N6设置标准格式=SUM($E4:E4)>$B4,设置好颜色;

(4)碰到文本格式无法求和时大家应该如何是好呢?实际也能够用公式实现的如图4

上列公式的简化式 :

=MAX(应纳税所得额*0.05*{1,2,3,4,5,6,7,8,9}
-25*{0,1,5,15,55,135,255,415,615},0)

重点操作

皇家赌场网址hj9292 94.
如下图,只要在相对应的日子下方输入消耗量累积超仓库储存时就会展现并提示了。

皇家赌场网址hj9292 10

算物价的函数

物价的十分之三七作五,二舍八入的尾数处理,做1个函数。就是小数点前边第壹个人要是是1,2的就舍掉,假使是3,4,5,6,7的都变为5,假使是8,9的小数点第3位加1,第二个人就变为0。比如价格是3.3② 、3.31,作最后多少个处理就是3.3;价格是3.3三 、3.3四 、3.3陆 、3.37,做倒数处理正是3.35;价格是3.3八 、3.39,做倒数处理正是3.4。

=CEILING(A1-0.02,0.05)

都以3个人小数 

B2=ROUND(2*A2,1)/2

跨越3人小数 

B2=ROUND(2*ROUNDDOWN(A2,2),1)/2

第贰步:TEXT函数建立救助列

皇家赌场网址hj9292 115.
如是要设置成第三种颜色展现景况,如下图将规范格式中的>改成<就足以了。

sum还有不少用法哦!如快捷行列求和,多表格求和等,小编不一一介绍了。中期大家还有许多公式学习啊!如常用的sumif,vlookup,max,min,average,counta等等公式。没有读书的同桌赶紧报名哦!所以作者引进大家,踏踏实实地做贰次系统的上学。想系统的学习Excel,欢迎参加到大家Excel学习演习营。

机关估测计算应收款滞纳金

务求在加以的应收日期、滞纳金率、当明日期(自动取)的根基上自行测算出应收滞纳金。

学Excel速成班心得,SUMIFS帮您按月总结。解答:

=(DATEDIF(应收日期,NOW(),"d"))*滞纳金率(每天)*应收金额

在日期列前插入协助列,在A2单元格输入公式“=TEXT(B2,”[dbnum1]m月”)”,将日期转换为月份,此月份的格式与总括模板中月份的格式一致。

皇家赌场网址hj9292 12

欢迎添加Excel达人的微信,chujun二〇一三

淘汰率

题材如下:那几个工厂有一千人,前几天抽出十二位来做调查,那11人一天的产量分别为101
102 105 106 98 95 96 104 110 103 (A3-A12)。

壹仟人中间淘汰率为5%,以那12人为正式那么那一千人他们的生育应该为多少才不会被淘汰,看看函数的支持就清楚了哟,重返数组K百分比值点,你要1000人淘汰六人就是一半00=0.5%=0.005,就是您以那十三个抽样调查的数码为标准,只要产量达到规定的标准那些数就不会被淘汰了。(95.45)

公式=PERCENTILE(A3:A12,G1)

结果如下:

  1. 有不知晓的地方能够关切后私信作者。

应用公积金的三个函数

本身公司职工公积金比例为26%
也正是私家和单位各13%,给公积金投缴职员营造了3个函数。直接用基数乘以比例
基数*皇家赌场网址hj9292 ,比例=投缴额, 对于投缴额的须求是:取最相近“投缴额”的偶数。

自作者制作的函数是“学Excel速成班心得,SUMIFS帮您按月总结。=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))

皇家赌场网址hj9292 13

皇家赌场网址hj9292 14皇家赌场网址hj9292 15

何以选用公式将数值转为百分比格式

如用公式将1.289675来得为128.97%,不是用格式来完结的。

=ROUND(B1*100,1)&"%"

第二步:SUMIFS函数

回答:

比高得分公式

=RANK(B4,$B$4:$B$26,1)

按月总计的沙盘,在B2单元格输入公式:“=SUMIFS(记录!$D$2:$D$24,记录!$C$2:$C$24,Sheet1!$A2,记录!$A$2:$A$24,Sheet1!B$1)”,像下向有填充,即可得到全数成品各月的销量。

一 、选中如图所示区域

机关评定奖级

=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2)
=LOOKUP(L179,{0,4,7,12,24},{"一等奖","二等奖","三等奖","纪念奖","纪念奖"})

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

皇家赌场网址hj9292 16

皇家赌场网址hj9292 17

对含有单位的数量咋样开始展览求和

在数量后必须投入单位,到终极还要计算总和,请问该怎样自动求和?(例如:A1:2KG,A2:6KG…..,在最后一行自动测算出总KG数)。

=SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))&”KG”

SUMIFS函数简介

② 、然后依次点击-早先-条件格式-新建规则

对a列动态求和

能够趁机a列数据的充实,在“b1”单元格=sum(x)对a列动态求和。 

=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))

功能:

皇家赌场网址hj9292 18

动态求和公式

自A列A1单元格到当下行前边一行的单元格求和。

=SUM(INDIRECT("A1:A"&ROW()-1))

用来总括其满足多少个条件的百分之百参数的总量。

叁 、点击“使用公式鲜明要设置格式的单元格”,在底下对话框中输入公式:=SUM($D2:D2)<=$C2
,同时点击“格式”,在弹出的界面中选拔想要的颜料,点击分明就消除

列的踊跃求和

若有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)))

语法:

皇家赌场网址hj9292 19

有规律的隔行求和

须求正是在安插、实际、差别三项中对前面包车型客车11个月求和。

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

也得以拖动填充,插入行、列也不影响总计结果。

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2], …)

回答:

什么样落到实处奇数行或偶数行求和

假诺数据在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))}

SUMIFS(求和区域, 条件区域1, 条件1, [标准化区域2,条件2], …)

看示例,意思是想清楚某种产品的入库,出库以及结存景况。并且把结存情况和月供给量想相比,假如低于安全仓库储存就报告警方提示。示例表格是1个二维表格,并不可能促成自动化,所以能够用Excel建立二个数据模型,出入库数据录入完结后,就能见到仓库储存情形,以及是还是不是供给补仓。

总括偶数单元格合计数值

总结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)

参数1:求和区域,必需的,须要求和的单元格区域;

皇家赌场网址hj9292 20

隔列将同一档次进展求和

隔列将出勤日和薪水分别开始展览求和数组公式

=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0))
或;
=SUMPRODUCT(--(MOD(ROW(C5:C25),2)<>0),C5:C25)

参数2:,条件区域1,必需的,用来寻觅条件1的单元格区域;

出入库表格式一样,格式为:

隔行或隔列加总

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

参数3:条件1,必需的,定义规则区域1中单元格符合的标准化;

皇家赌场网址hj9292 21

借问怎么着在一百行内做隔行相加

数组公式

A1+A3+……+A99    单

=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))

A2+A4+……+A100   双

=SUM(N(OFFSET(A1,ROW(1:50)*2-1,)))

自此的参数:条件区域2,条件2, …是足以不难的。

汇总表暗灰黄底纹区域,均为函数自动生成,不供给手动输入,个中当日多少反映的是当天的处境,累计数据体现的是一起出入库的状态。结存的数据为期初+入库累计-出库累计。

怎么样将距离一定的列的多少相加呢

遇上100多列的数目将间隔一定的数码用手工业相加太烦了,也便于失误。借使急需相加的数额均有平等的称呼(字段),能够用Sumif()来求解,假如没有,就需求用数组公式来缓解了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)}   1、4、7……行相加。

SUMIFS函数最多能够输入 127 个区域/条件对。

当日数量用的是多规格求和函数sumifs,累计数据用的是标准化求和函数sumif,函数公式为:

隔列求和(A、B列)

=SUM(A:A,B:B)
=SUM(A:A,B:B,C:C)   (统计A、B、C列)

核心解析

当天入库:=SUMIFS(入库表!$E$2:$E$9,入库表!$D$2:$D$9,汇总表!B4,入库表!$C$2:$C$9,汇总表!$C$2)

隔列求和的公式

品种及日期

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

在本示例中:

总共入库:=SUMIF(入库表!$D$2:$D$9,汇总表!B4,入库表!$E$2:$E$8)

隔列求和

类别

产品代码

 单价

安贞

北辰

长安

长春

合计

库存

销售

库存

销售

库存

销售

库存

销售

库存

销售

皮带

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)

求和区域:记录!$D$2:$D$24;

当天出库:=SUMIFS(出库表!$E$2:$E$9,出库表!$D$2:$D$9,汇总表!B4,出库表!$C$2:$C$9,汇总表!$C$2)

关于隔行、隔列求和的题材

隔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)
均为数组公式。

有八个区域/条件对:

计算出库:=SUMIF(出库表!$D$2:$D$9,汇总表!B4,出库表!$E$2:$E$8)

EXCEL中求两列的应和成分乘积之和

如:a1*b1+a2*b2+b3*b3...的和

=SUM(A1:A3*B1:B3) (数组公式)

=SUMPRODUCT(A1:A10,B1:B10)

原则区域1:记录!$C$2:$C$24,即全体产品区域;

图标集的多寡是用总共结存和月须要量想相比,若是是正数正是石黄箭头,如果是负数正是原野绿箭头。

计算900~一千之内的数值之和

sumif函数的乘除格式为: =sumif($a$1:$a$20,”>1000″)。即重回$a$1:$a$20Chinese Football Association Super League过一千的数值的和,但借使想总计900~一千之内的数值之和,应该如何编写。

请参考:{=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:Sheet1!$A2,即模板表中的“鼠标”;

图标集的行使方法是,选中改数据区域,点击条件格式——图标集,选中个中多个,然后点击编辑规则。设计超过零时展现为蓝色箭头,小于零时展现为鲜红箭头,并勾选只展现图标集。

双标准求和

① 、 求一班女人的个数 :

=SUMPRODUCT((A2:A9=1)*(B2:B9=""女""))

二 、求一班战表的和 : 

=SUMIF(A2:A9,1,C2:C9) "    

③ 、求一班男人成绩的和 : 

=SUMPRODUCT((A2:A9=1)*(B2:B9=""男""),C2:C9) "    

原则区域2:记录!$A$2:$A$24,即补助列月份区域;

皇家赌场网址hj9292 22

 怎样实现如此的口径求和

求型号中含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)

条件2:Sheet1!B$1,即模板表中的“11月”。

经过这么的装置以往,就能够完成输入实现原始数据,就能自行汇总,并显示月供给量和仓库储存量的涉及。当然假如想设置安全仓库储存提醒也得以用函数来落到实处。

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)

       以后您看来的还不是最后结果,请按如下操作   

       一 、把数量区域设置成可筛选     

       ② 、把SEX筛选成”=女”, 把年龄筛选成<20   

       三 、你再看下面的公式结果…     

其中:

皇家赌场网址hj9292 23

去掉当中五个最大值和多个小小值,再求和

借问怎么去掉多个最高分,多少个最低分,剩余人士的分数求和,例如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))

两个规格区域,都使用相对化引用,公式填充时,区域保持不便;

最后索要小心的是:sumifs函数只辛亏Excel高版本中才能运用,图标集也是适用于Excel的高等版本。

去掉四个最高分、最低分,彰显出被去掉的分数

被去掉的分数:

最大五个:=large(data,{1;2})

微小四个:=small(data,{1;2})

条件1“Sheet1!$A2”,使用混合引用,公式向下填充时,行会自动扩张,向右填充时,产品所在A列保持不变;

假诺您必要这些源文件,私信作者,笔者享受给您。若是您不懂什么去用,也可以留言或臧否。期待与你交换。

固定的求和

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

条件2“Sheet1!B$1”,使用混合引用,公式向下填充时,保持月份所在第三行不变,向右填充时,月份所在的列自动改变。

按字体颜色求和

做法:

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)

 

Leave a Comment.