Excel函数太多太难学?先学个万能的呢

阶梯条件,(二次费改后的金额)谢谢赐教,p&gt,输入=vlookup(H4,——通过知识共享树立个人品牌,不想学习太多的函数公式,那么也一定要学习的函数就是-SUMPRODUCT

澳门新莆京线上娱乐 11

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

求和函数

  • sumifs

澳门新莆京线上娱乐 1

sumifs.png

<p>5个参数

  • sum_range 需须求和的实际上单元格
  • critica_range1 计算关联条件的第一个区域
  • critica1 第叁个区域满意条件
  • critica_range2 计算关联条件的首个区域
  • critica2 第一个区域知足条件

澳门新莆京线上娱乐 2

示例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 到 贰16个数组,其相应成分须求举办相乘并求和。///相乘再相加
    <p>条件求和:求巴拿马城饭馆的生产能力
    函数关系式:=SUMPRODUCT((C13:C19=”蒙Trey”)*(D13:D19))
    求解的是路易港仓房的出货总数。

澳门新莆京线上娱乐 3

sumproduct.png


对含有单位的数量怎么样进展求和

在数码后必须投入单位,到最后还要总结总和,请问该怎么着自动求和?(举例:A1:2KG,A2:6KG…..,在结尾大器晚成行自动测算出总KG数卡塔 尔(阿拉伯语:قطر‎。

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

7、分组排行

上面这些例子,”小组”这一列中有八个例外的值,“少年老成组”和“二组”。未来大家要指向“出售额”按分裂的组开展排行。

D3单元格中公式为:

=SUMPRODUCT(($B$3:$B$10=B3)*($C$3:$C$10>C3))+1

($B$3:$B$10=B3)不表明了

($C$3:$C$10>C3)纵然拿装有的“出卖额”跟C3(这里是争持征引,向下复制时会形成C4,C5,……卡塔尔比,比它大的会回到TRUE。

而是这样一来排第风姿潇洒的数,是从未有过比它大的,会重返0。所以大家在公式的最后面有个“+1”,那就改成大家想要的“排名”的模范了。

写了如此多,一贯疑似自言自语,

否则大家也来点相互作用?

在斟酌区告诉笔者,

您最想上学的Excel知识是什么?

-END-


排序函数

  • rank

澳门新莆京线上娱乐 4

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>在C第11中学编辑公式
=RANK(A1,$A$1:$A$5,1)

那时候从C1单元格起依次再次回到的值是4、5、1、3、1

  • 急需排行的数值
  • 内需排行的界定
  • 排序格局,0为正序,1为倒序

以此比较轻巧,不做详细探求

动态求和公式

自A列A1单元格到当下行前面风度翩翩行的单元格求和。

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

3、多规格求和

上边求和的例子,跟计数的差别就在于,最后多乘三个数组。

D13单元格中的公式为:

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80)*($D$3:$D$11))

也便是将刚刚的列再与“战表”那一列,D3:D11开展相乘,然后在展开加和,便可获取。

其一是非凡的多规格取数,阶梯条件,你首先要重新收拾条件为正规格式,按作者上边方法操作就可以

日期函数

<p>Month
函数指重临三个Variant(Integer),其值为1到12里面的整数,表示一年中的某月
<p>mintue()
<p>hour()

<p>先介绍到那边,聪明的你学会了吧?

电动评定奖级

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

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

4、加权平均值(权重已知)

加权平均值的用法也很普及。比方期末成绩由下边3片段组成,每一片段的权重分别为十分三,十分三,二分之一。

E4单元格中公式为:

=SUMPRODUCT(B4:D4,B$3:D$3)

与以前例子分化的是,这一次是分化行之间先相乘,然后再把列进行相加。

B$3:D$3用了“混合援引”,目标是为了保险公式向下复制时,这一行数据的引用不会发生变化。

回答:

追寻函数

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

vlookup

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

  • 首先个是参照值,就是多少个表相关联的地点
  • 其次个是叁个表,你要给函数贰个搜寻范围
  • 其八个就是一个数字,代表表里面第几列是你要物色的多寡
  • 第八个是布尔值,用0精确相称

澳门新莆京线上娱乐 5

示例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

去掉在那之中八个最大值和三个细微值,再求和

试问怎么去掉三个最高分,多个最低分,剩余名员的分数求和,譬如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))

重重敌人感到Excel函数太多了,何况每一个都有两样的选用条件,根本记不复苏。

意气风发经您也可能有平等的心得,不想学习太多的函数公式,那么也应当要读书的函数便是-SUMPRODUCT。

无论是条件求和、条件计数、加权平均,那个常用的总结计算它都能做赢得!还在为函数太多记不住发愁呢?快来学习它呢~~

前日大纲:

① SUMPRODUCT幼功运用

② SUMPRODUCT多规格计数

③ SUMPRODUCT多规格求和

④ 总计加权平均值(权重已知)

⑤ 总结加权平均值(权重未知)

⑥ 交叉查询

⑦ 分组排行

SUMPRODUCT函数的干活规律为:

在加以的几组数组中,将数组间对应的因素先相乘(PRODUCT),后相加(SUM)。唯独就凭这几个相乘、相加的测算,就能够完毕无数意义。

语法

SUMPRODUCT(array1, [array2], [array3], …)

其中:

array1 必得,它是呼应成分需求开展相乘并求和的第一个数组参数。

array2, array3,… 为可选。能够是 2 到 252个数组参数,其对应成分要求开展相乘并求和。

数组参数必得具备同样的维数。 不然,函数 SUMPRODUCT 将赶回 #VALUE!
错误值 #REF!。

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*即可了

实例截图如下

澳门新莆京线上娱乐 6

SUMPRODUCT实在是excel函数中山高校神 平常能够用它来达成都部队分语无伦次的职分

回答:

澳门新莆京线上娱乐 7

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

回答:

用vlookup般配应该能够解决问题。

说其实的,需求的话私信,发布格给自己,方便交换难题。那样抛出几张图,录入数据的时日都亟待好长。

接待关心自己的头条号,如若有excel方面包车型地铁标题,能够私信交换,为你答应解惑。

字符串函数

  • left

澳门新莆京线上娱乐 8

left.png

<p>LEFT( string, n )
<p>可以很有益的去取文本的值,n是取多少个,left是从左侧去取,right是从侧边去取
<p>借使A1=云南省新乡市铜官区杨庙乡,则公式“=LEFT(A1,FIND(“省”,A1))”再次来到山东省。
<p>还有mid函数

澳门新莆京线上娱乐 9

middle.png

澳门新莆京线上娱乐 10

mid.png

小心字符串函数的妙用

隔行求和公式设置

均为数组公式:

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

5、加权平均值(权重未知)

此处的权重未知,指的是还未有一个显然的比重,唯有叁个绝对的数码。

如此的状态也很好处理,我们只需用跟上个例子相近的进度,让“数量”和“费用”分别相乘后再相加,然后在除以“数量”的和就能够。

图中A9单元格中的公式为:

=SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

大家好,笔者是@EXCEL实例录制网址长@迎接关心自个儿的头条,每一日都会分享Excel实战技能!

澳门新莆京线上娱乐,取整函数

  • round

澳门新莆京线上娱乐 11

round.png

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

列的弹跳求和

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

2、多规格计数

求成绩超乎80分的女孩子的多少:

大家总共有八个标准:

① 成绩,也就是D列的数,要大于80;

② 性别,也就是C列,为“女”。

D13单元格中的公式为:

=SUMPRODUCT(($C$3:$C$11=$C$3)*葡京彩票平台,($D$3:$D$11>80))

公式中,

$C$3:$C$11=$C$3意味着,C3到C11区域等于C3,也正是“女”。相符条件的,会回来“TRUE”(也就是1卡塔 尔(英语:State of Qatar),不切合条件的,再次来到“FALSE”(也正是0卡塔尔.

$D$3:$D$11>80,同理,那多少个单元格“战表”大于80的,再次回到“TRUE”(1卡塔尔国,不切合条件的,重返“FALSE”(0卡塔 尔(英语:State of Qatar)。

然后五个数组分别相乘。

1*0=0

0*0=0

1*1=1

就拿到上面第3列的结果。

接下来在将这一列结果开展相加,就收获2.

(此总结进程,上边多少个例证很相同,就不再赘述卡塔 尔(英语:State of Qatar)

算物价的函数

物价的三分之一七作五,二舍八入的尾数管理,做一个函数。正是小数点前边首个人即使是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

1、底子运用

SUMPRODUCT函数的最非凡应用:

上边例子中有单价和多少,我们渴求具有成品的总共价值。

当中A12单元格中的公式为:

=SUMPRODUCT(B3:B9,C3:C9)

公式所起到的坚守,就好像自身在边上米黄虚框中评释的意气风发致,先将B3到B9中的每贰个数,分别与C3到C9中的数相乘。然后再将这几个乘积实行加和,就拿走了大家要的结果。

隔列求和(A、B列卡塔 尔(阿拉伯语:قطر‎

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

6、交叉查询

陆陆续续查询用在那处可以说又是焕发青木正作了。

平日大家耳濡目染的化解格局是用INDEX+MATCH函数来形成。

可是,万能的SUMPRODUCT也同等能行。

下图中,大家渴求“超市3”“青龙果”的价格。

D13单元格中的公式为:

=SUMPRODUCT(($B$4:$B$9=B13)*($C$3:$G$3=C13)*($C$4:$G$9))

前两段分别看很好明白,正是判别是不是切合条件,切合的回来”TRUE”,不契合的回来”FALSE”。

然则,它俩分别是三个列数组和一个行数组,相乘后拿到的是三个二维的矩阵。

($B$4:$B$9=B13)*($C$3:$G$3=C13)

如下图:

然后,再把($C$4:$G$9),本条区域的每三个单元格,分别与地方的结果相乘。

提起底在将拥有的数相加,就拿走9.8.

计算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″)

对a列动态求和

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

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