图片 11

excel函数浅解,汇总计算与总括

By admin in 办公软件 on 2019年11月19日

问题:怎么实现在B2输入排量,在C8自动显示车船税的金额?(二次费改后的金额)谢谢赐教!

查找函数

  • vlookup
![](https://upload-images.jianshu.io/upload_images/5329960-0da2309f02f4fb81.png)

vlookup

<p>vlookup有四个参数,如上表所示

  • 第一个是参照值,就是两个表相关联的地方
  • 第二个是一个表,你要给函数一个查找范围
  • 第三个就是一个数字,代表表里面第几列是你要查找的数据
  • 第四个是布尔值,用0精确匹配

图片 1

示例vlookup.png

  • 求全年总计,工号相对
    在I4处输入=vlookup(H4)//查找100003
  • 输入表A2:F12//在这个表里面查询
    <b>注意绝对引用</b>
    确定表的范围后按<b>F4</b>,否则往下填充的时候表的范围会改变,按后变成$A$2:$F$12(美元符号)
  • 第六列是<b>我们给的表</b>的数据全年总计
    第三个参数为6
  • 第四个不讲,默认为0

输入=vlookup(H4,$A$2:$F$12,6,0)
<p>好了,其实函数也给了我们提示,看英文

  • lookup_value 要找的值
  • table_array 表格数组
  • col_index_num 第几列的索引的数字值
  • 不用讲了,直接0

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

回答:

求和函数

  • sumifs

图片 2

sumifs.png

<p>5个参数

  • sum_range 需要求和的实际单元格
  • critica_range1 计算关联条件的第一个区域
  • critica1 第一个区域满足条件
  • critica_range2 计算关联条件的第二个区域
  • critica2 第二个区域满足条件

图片 3

示例sumifs.png

<p>求男生语文成绩的和

  • 对语文成绩求和,语文成绩范围为C2:C8
  • 要求性别为男生
    在性别里面 B2:B8
    条件为男生 文本”男” 记得引号,且在英文标点下
    =sumifs(c2:c8,b2:b8,”男”)

<p>同理,求语文和数学得分都大于等于90分的学生总分之和
在G4单元格输入公式=SUMIFS(F2:F8,C2:C8,”>=90″,D2:D8,”>=90″)
求和总分,语文里面//大于90,数学里面//大于90


  • sumprounduct
    <p>语法:SUMPRODUCT(array1,array2,array3, …)
    Array1,array2,array3, … 为 2 到 30
    个数组,其相应元素需要进行相乘并求和。///相乘再相加
    <p>条件求和:求成都仓库的出货量
    函数关系式:=SUMPRODUCT((C13:C19=”成都”)*(D13:D19))
    求解的是成都仓库的出货总量。

图片 4

sumproduct.png


汇总计算与统计(一)

大家好,我是@EXCEL实例视频网站长@欢迎关注我的头条,每天都会分享Excel实战技巧!

排序函数

  • rank

图片 5

rank.png

<p>假如A列从A1单元格起,依次有数据80、98、65、79、65。
<p>在B1中编辑公式
=RANK(A1,$A$1:$A$5,0)
从B1单元格起依次返回值为2、1、4、3、4。
<p>在C1中编辑公式
=RANK(A1,$A$1:$A$5,1)

此时从C1单元格起依次返回的值是4、5、1、3、1

  • 需要排名的数值
  • 需要排名的范围
  • 排序方式,0为正序,1为倒序

这个比较简单,不做详细探讨

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


取整函数

  • round

图片 6

round.png

四舍五入,没什么可说的
<b>注意第二个参数为小数位数,其余见图

 

这个是典型的多条件取数,阶梯条件,你首先要重新整理条件为标准格式,按我下面方法操作即可

字符串函数

  • left

图片 7

left.png

<p>LEFT( string, n )
<p>可以很方便的去取文本的值,n是取几个,left是从左边去取,right是从右边去取
<p>如果A1=安徽省蚌埠市固镇县杨庙乡,则公式“=LEFT(A1,FIND(“省”,A1))”返回安徽省。
<p>还有mid函数

图片 8

middle.png

图片 9

mid.png

注意字符串函数的妙用

 

EXCEL–多条件取值终极解法SUMPRODUCT高级应用

上次介绍了一个lookup进行多条件判断取值的解法,不过今天突然发现那个解法有个严重的缺陷,就是只能解a<=A<b,就是当条件都是大于等于较小值且小于较大值的情况,今天又有人问了一个a<A<=b
及大于较小值而小于等于较大值的多条件
那个公式在节点上就判断不准了,我又实在不想用if函数去做,嵌套一大堆得if
看着都累,先后试验了vlookup,Hlookup,match,发现它们的内在原理都是一样的
没办法只好请出SUMPRODUCT大神了

实例如下

输入500>=a1>0 的数的时候,B1显示为A1*0.1

输入2000>=A1>500 B1显示为A1*0.2

输入5000>=A1>2000 B1显示为A1*0.3

输入20000>=A1>5000 B1显示为A1*0.4

有人会说 这还不简单那 几个if就可以了 恭喜你 答对了! 但是我现在要做的是
如果有N个以上这样的条件 你还能if么,那公式写出来只怕累死你
而且if只能嵌套7层的,其实我最想做的是将公式简化
简化成容易理解容易使用的形式 废话少说 开始分析

1、本问题核心是A1*X,X的取值根据A1的范围变化,好了 核心就是求X

2、抽出A1条件和X对应结果 简单得出以下数列,

123>0<=5000.1>500<=20000.2>2000<=50000.3>5000<=200000.4

到现在 事情就变得比较简单的 这个问题抽象成数学逻辑就变成了

对1,2列数据进行逻辑与运算 其结果再乘以3列数据
由于只有一行的数据会符合要求 而其他行注定会等于0
所以对每行的运算结果求和 就是最终的X取值了

说了一大堆理论 估计很多人看不懂 我表达能力实在有限啊 看不明白的就略过吧

上面的结论 最适合的公式就是SUMPRODUCT了 这个函数其本质就是数组求和
实际上可以分解成sum函数的数组公式
但是它的优点就是用普通公式完成了数组公式的效果
在这里它最大的作用就是进行逻辑与运算和数组求和 公式其实挺简单

纯数字版的:

=SUMPRODUCT((A1>{0,500,2000,5000})*(A1<={500,2000,5000,20000})*{0.1,0.2,0.3,0.4})

别看这觉得吓人 其实你几个括号的值和上面的数列一对比 就很简单了

实际使用的时候 可以用单元格区域代替{}例如

00.15000.220000.350000.420000

这是F9:G13的单元格区域 公式就相应改为

=SUMPRODUCT((A1>$F$9:$F$12)*(A1<=$F$10:$F$13)*($G$9:$G$12))

这里 当你的条件更多时 一样可以套用这个公式 只要把条件区域做相应改变即可

到此为止 这就算出了对应X的值 剩下的就是在公式前面加上A1*即可了

实例截图如下

图片 10

SUMPRODUCT实乃excel函数中大神 经常可以用它来完成一些不可思议的任务

回答:

图片 11

不知道怎么图被吃了一张…

回答:

用vlookup匹配应该可以解决问题。

说实在的,需要的话私信,发表格给我,方便交流问题。这样抛出几张图,录入数据的时间都需要好长。

欢迎关注我的头条号,如果有excel方面的问题,可以私信交流,为你答疑解惑。

日期函数

<p>Month
函数指返回一个Variant(Integer),其值为1到12之间的整数,表示一年中的某月
<p>mintue()
<p>hour()

<p>先介绍到这里,聪明的你学会了吗?

个调税公式

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

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图
Copyright @ 2010-2020 澳门新葡亰官网app 版权所有