Excel常用函数总括,等级鉴定那么些事

问题:如何用Excel中的IF函数判断学生的大成是或不是杰出?

难题源于

Excel常用函数总计

2016-10-28 Kevin 叼着奶瓶撩妹

  1. VLOOKUP函数

 

周边情势

题材讲述:将下图中G列的数目依照学生的人名填写到D列。

皇家赌场网址hj9292 1

公式解析:

=VLOOKUP(A2,$F$2:$G$16,2,FALSE)

Excel常用函数总括,等级鉴定那么些事。参数1:必要查询的数据

参数2:钦定询问的区域,必须包涵查找值和重临值,且第三列必须是查找值

参数3:$F$2:$G$16区域的第①例

参数4:钦命的搜寻方法,TRUE表示模糊 查找,FALSE表示精确查找

 

解法之二

上海教室中的难点也可应用INDEX+MATCH消除,如下如图所示。

皇家赌场网址hj9292 2
公式解析:

=INDEX($G$2:$G$16,MATCH(A2,$F$2:$F$16,0))

先用MATCH函数在$F$2:$F$16区域查找A2的职位,在使用INDEX引用$G$2:$G$16区域该地方上的数量

 

 

归来多列查询结果

难点讲述:将下图中C、D列的多寡根学生的姓名分别填充到对应的G、H列。

皇家赌场网址hj9292 3

公式解析:

=VLOOKUP($F2,$A$2:$D$16,COLUMN(C:C),FALSE)

COLUMN(C:C),重回C列的列号

 

逆向查找

题材讲述:

将下图中A列学生的真名根据对应的学号填充到E列。

皇家赌场网址hj9292 4
公式解析:

=VLOOKUP(D2,IF({1,0},$B$2:$B$16,$A$2:$A$16),2,FALSE)

 

多规格查找

题材讲述:

下图中依据姓名和班级将C列数据填充到G列。

皇家赌场网址hj9292 5
公式解析:

=VLOOKUP(E2&F2,IF({1,0},$B$2:$B$16&$A$2:$A$16,$C$2:$C$16),2,FALSE)

需使用Ctrl+Shift+Enter键

 

  1. IF函数

 

皇家赌场网址hj9292 6

公式解析:

=IF(A2>=60,”及格”,”不及格”)

=IF(A2>=90,”优秀”,IF(A2>=80,”良好”,IF(A2>=60,”及格”,”不及格”)))

 

皇家赌场网址hj9292 7
公式解析:

=IF(AND(A2>=295,A2<=305),”合格”,”不合格”)

等价于这么些公式:

=IF(OR(A2<295,A2>305),”不合格”,”合格”)

 

  1. SUMIF函数

单条件求和

皇家赌场网址hj9292 8

公式解析:

=SUMIF(A$2:A$10,”>=”&C2)

参数1:求和区域

参数2:求和标准

 

 

皇家赌场网址hj9292 9
公式解析:

=SUMIF($B$2:$B$10,$F2,C$2:C$10)

 

参数1:条件区域

参数2:求和准星

参数3:求和区域

 

多规格求和

皇家赌场网址hj9292 10
公式解析:

 

=SUMIFS(D$2:D$12,$B$2:$B$12,$H2,$C$2:$C$12,$G2)

参数1:求和区域

皇家赌场网址hj9292 ,参数2:条件1区域; 参数3:求和标准化1

参数4:条件2区域; 参数5:求和规则2

Excel常用函数总括,等级鉴定那么些事。听闻可以写很1二十七个求和条件。

 

  1. COUNTIF函数

单条件计数

皇家赌场网址hj9292 11

公式解析:

=COUNTIF(B$2:B$16,D2)

参数1:计数区域

参数2:计数条件

 

多规格计数

皇家赌场网址hj9292 12

公式解析:

=COUNTIFS(B$2:B$16,E2,C$2:C$16,”>=”&F2)

参数1:计数区域1; 参数2:计数条件1

参数3:计数区域2; 参数4:计数条件2

 

  1. AVERAGEIF函数

单条件求平均值

皇家赌场网址hj9292 13

公式解析:

=AVERAGEIF(B$2:B$16,$E2,C$2:C$16)

 

参数1:条件区域1

参数2:求值条件

参数3:求值区域

 

多规格求平均值

皇家赌场网址hj9292 14

公式解析:

 

=AVERAGEIFS(C$2:C$16,C$2:C$16,”>=”&F2,B$2:B$16,$E2)

 

参数1:求值区域

参数2:条件1区域; 参数3:条件1; 

参数4:条件2区域; 参数5:条件2;

 

  1. 字符串函数

 

一而再字符串

皇家赌场网址hj9292 15

公式解析:

=A2&”-“&B2

也能够选择那一个公式:

=CONCATENATE(A3,”-“,B3)

 

截取字符串

皇家赌场网址hj9292 16

公式解析:

 

=LEFT(A2,3)

也可用那几个公式:

=LEFT(A2,FIND(“-“,A2)-1)

截取姓名可用这几个公式:

=RIGHT(A2,LEN(A2)-FIND(“-“,A2))

 

🌻🌻🌻IF函数的底蕴用法

回答:其一很简短,只要嵌套IF函数,就能活动判断划分出成绩等级,上边笔者简单介绍一下降成进程:

等级评定是EXCEL数据管理常常蒙受的一种多少分析方法,一般是付诸分化等级评判的规范数据区间,依照某项数据,批量变更每行的等级。

✅假若(内容为True(度量准则),则进行某个操作(再次来到逻辑值TRUE),不然就推行别的操作(不然重回逻辑值FALSE));

1.为了更好的证实难点,那里本人新建了一个Excel表格,输入的测试数据如下,那里是自由填写的:

韩先生以下边一组数据为例,给大家总计一下品级评定的八种常的法子:

🍉男:标记为60,女:标记为55

皇家赌场网址hj9292 17

皇家赌场网址hj9292 18

🌵=IF(B2=”男”,60,55)

2.随即大家点击等级上边第3个单元格,输入IF判断函数“=IF(B2>=90,”杰出”,IF(B2>=80,”突出”,IF(B2>=70,”中等”,IF(B2>=60,”及格”,”不及格”))))”,那里划分了伍个级次,分别是“特出”,“卓绝”,“中等”,“及格”和“不及格”,点击“Enter”,就能断定出第③个战绩的级差,如下:

一般,在可比标准的报表中,不提倡添加帮衬列,那韩老师就不加协理列,直接写结果。

🌻🌻🌻IF函数单条件判断:

皇家赌场网址hj9292 19

摄像讲解

已知员工的在职状态(实习/转正)和工龄(是还是不是干满1年),要依据职员和工人是不是是试用期和工龄是还是不是满意3年来测算职员和工人的奖金。

3.终极,从刚刚的单元格依次下拉,全数的成绩就会自动判断等级,如下:

EXCEL等级鉴定_腾讯摄像

🌵=IF (B2=”试用期”,100,IF(B3>=1,200,150))

皇家赌场网址hj9292 20

主要操作

【注】

从那之后,大家就达成了使用IF函数自动判断学生成绩等级。总的来说,整个进度很不难,也简单,只要您有一些的Excel知识,多熟识几次操作,相当的慢就能控制的,网上也有有关课程和资料,感兴趣的话,能够搜一下,希望以上分享的情节约财富对您抱有帮忙啊,也欢迎大家评论、留言。

IF函数

多原则判断时,要小心括号的岗位,右括号都在最后,而且有多少个IF就输入多少个右括号(是检查IF语句是还是不是完好的好办法);

回答:

在C2单元格输入公式:“=IF(B2<60,”但是关”,IF(B2<70,”合格”,IF(B2<85,”卓越”,”优异”)))”。

🌻🌻🌻IF函数单条件多层级嵌套条件判断:

用IF函数判断学生的成正是不是优质是很简短的,曾写过一篇小说《玩转学生战绩分析》不仅能交付答案,还是可以让名师们做更具体的分析,在次发出来,希望能给同事们有着帮助。
皇家赌场网址hj9292 21

因为有各个不一致的等级,所以利用IF的三重嵌套。

🌵=IF(C2=100,”满分”,IF(C2>=90,”优秀”,IF(C2>=80,”良好”,IF(C2>=60,”及格”,”不及格”))))

我们教育工笔者,让学员考试是日常的事。而每二遍考试成绩出来后是或不是需求举行数量解析呢?后天介绍用IF函数和LOOKUP函数等对学员战绩举行等级划分和总计的形式。

皇家赌场网址hj9292 22

❗️❗️

一、什么是IF函数

IF函数及规范函数,基本语句是IF(logical_test, [value_if_true],
[value_if_false]),其中logical_test是逻辑检验的表明式;value_if_true是检验结果为实在重回值,value_if_false是检查结果为否的重临值。意思是只要满意什么条件,则赶回某值,不然再次来到另一值。

VLOOKUP函数

IF函数能够判断中文条件:文字加上双引号即可

二 、IF函数的着力用法

以下表为例,当C3中的分数在八十九分(总分150)以上为合格,九十几分以下不及格,在D3单元格输入公式=if(c3>=90,“及格”,“不及格”),并拖动公式填充到D列的其它单元。
皇家赌场网址hj9292 23

在D2单元格输入公式:“=VLOOKUP(B2,{0,”不合格”;60,”合格”;70,”特出”;85,”卓绝”},2)”。

🌻🌻🌻IF函数或涉及多规格判断:

③ 、IF函数的嵌套用法

一般来说图,假如大家将培养划分为“优良”(1叁十分以上)、“较非凡”(1拾8分上述)、“突出”(10陆分以上)、”合格”(捌拾捌分以上)、“不沾边”(八十七分以下),当中还恐怕出现有同学缺考的图景,于是在D3中输入公=if(c3>=135,”非凡”,if(c3>=120,”较卓越”,if(c3>=105,”突出”,if(c3>=90,”合格”,if(c3=””,”缺考”,”不过关”))))),尤其说爱他美(Aptamil)下缺考的状态,缺考则该单元格不填(空值),而不是填0。拖动公式填充,则每人同学的等次就出来了。
皇家赌场网址hj9292 24

皇家赌场网址hj9292 25

✅想要录入A部门和B部门的职工能够获得奖金200元

肆 、用LOOKUP函数划等次

当大家有时候等次分别较多时,用IF函数就比较麻烦了,那时可以用LOOKUP函数来代替。如下图,在I列和J列中分出分数段和阶段,在D3中输入=LOOKUP(C3,$I$3:$I$7,$J$3:$J$7),注意“$”符号是指的相对引用,此时若是不相对引号,在拖动公式填充时对应的单元格就会下移,就丰裕了。还有一特别注意的是LOOKUP函数是向后12分,所以在个别时肯定要从低分到高。
皇家赌场网址hj9292 26

里头,{0,”不合格”;60,”合格”;70,”突出”;85,”卓越”}是形成一新的多寡区域:

🌵=IF((B2=”A”)+(B2=”B”),200,0)

五 、IF函数的回顾用法

当大家有同学缺考时(有空值),能够应用IF和LOOKUP的一块,在D3中输入=if(C3=””,”缺考”,LOOKUP(C3,$I$3:$I$7,$J$3:$J$7))。
皇家赌场网址hj9292 27

皇家赌场网址hj9292 28

✅工龄满一年或一般KPI得分抢先85的职工能够取得奖金200元

⑥ 、按比例划定等次

以下图为例,共有29名同学的实际业绩(C3:C31),按A等百分之二十,B等十分之三,C等百分之三十,D等十分之二来划定等次。

(一)即便用IF函数,先扩张一列,计算出各类分数排行在总额中的比例,用公式=RANK(C3,C$3:C$31)/(COUNTA(C$3:C%31),在那之中=RANK(C3,C$3:C$31)是分数在全数分数中的排行,COUNTA(C$3:C%31)是总计区域内有着不含空值的单元格个数(当然为里明亮有二十八个数据,可不要这么些函数,直接/29),由于公式须要下拉开始展览填充,所以行区域的行号要用相对引用($)。
皇家赌场网址hj9292 29
再按前面IF函数的嵌套用法实行等次的划定。
皇家赌场网址hj9292 30
(二)直接用LOOKUP函数,输入:=LOOKUP(PERAV4CENTRANK(C:C,C3),{0,0.201,0.501,0.801},{“D”,”C”,”B”,”A”}),此公式中PE景逸SUVCENTRANK(C:C,C3)意思是C3数额在C列中的全数C列数据中的百分比排行,此处引用了整套列,也足以像前面一样用C$3:C$31为个区域。因为PE君越CENTRANK排名是降序的,即首先名是1,所今后边的数组中A占伍分一,也正是0.8现在的,B占三成,也正是0.5之后的,依次类推。
皇家赌场网址hj9292 31

LOOKUP函数

🌵=IF((B2>=1)*(B3>=85),200,0)

柒 、各等次数据计算

当大家划定了等次后,可能学供给总括每一个等次的学习者数,则足以用公式:=COUNTIF(E$3:E$31,G3),含义是在H3中执会调查总结局计E3:E31区域内等于G3的数额,区域是不变的,所以相对引用,但G3在下拉填充时是索要变成G4的,所以相对引用。
皇家赌场网址hj9292 32

在E2单元格输入公式:“=LOOKUP(B2,{0,60,70,85},{“不及格”;”合格”;”突出”;”特出”})”。

✅男性60退休,女性55退休

越多教育意见、教学经验、学习财富、教育技术,敬请关切“清劲风教育”。 皇家赌场网址hj9292 33

回答:

用IF函数来做判断并不复杂,只要有一套对应的等级规则,然后用相应的逻辑嵌套就行了。

皇家赌场网址hj9292 34

🌵=IF((B2=”男”)*(C3>=60)+(B2=”女”)*(C3>=55),”退休”,“”)

1. IF函数嵌套判断

皇家赌场网址hj9292 35

=IF(B2>=90,”优秀”,IF(B2>=70,”良好”,IF(B2>=60,”及格”,”不及格”)))

CHOOSE+MATCH函数

🌻🌻🌻IF函数依照标准求和:

2. IFS函数判定

#Excel2019 新增了1个函数IFS,相对于IF函数的嵌套,IFS函数使用更直观。

一直不多重嵌套,不简单失误。

在Office2019版本和Office365本子都可用。

皇家赌场网址hj9292 36

=IFS(B2>=90,”优秀”,B2>=70,”良好”,B2>=60,”及格”,B2<60,”不及格”)

在F2单元格输入公式:“=CHOOSE(MATCH(B2,{0,60,70,85},1),”不沾边”,”合格”,”非凡”,”非凡”)”。

班里女子高校友的实际业绩之和

3. LOOKUP函数寻找匹配

漏洞分外多查找,LOOKUP函数才是的确的王者。

然而在写LOOKUP函数的时候,需求先在“登记表”中添加二个扶助列。

并且救助列的数值必须升序排列。

因为LOOKUP函数实行搜索的时候使用的是“二分法”的查找方法。

事实上您也不须求搞懂什么叫“二分法”是怎么鬼,只要求记得被搜寻区域必须升序排列就行了。

皇家赌场网址hj9292 37

=LOOKUP(B2,H:H,G:G)

绝对之下,LOOKUP函数是或不是要简明多了啊。

多如牛毛人知道VLOOKUP很强大,其实LOOKUP函数才是实在的王者啊!

style=”font-weight: bold;”>小编是微软求证MCT,微软MOS认证大师。关切笔者,一起玩Office。

回答:能够接纳if函数举行多规格判断

公式:=IF(B2>=90,”优秀”,IF(B2>=80,”良好”,IF(B2>=60,”及格”,”不及格”)))

皇家赌场网址hj9292 38

在那大家以小王的叁10分为例,在第二层中规格是破绽百出的,函数会回到if函数的不当班值日,而if函数的荒唐值嵌套了第1个if函数,所以函数会一而再判断,第四个尺码也是不对的,函数会进去第多少个if函数实行判定,我们得以见见第多个规格也是谬误的,所以小王的大成为不及格

有关越多if函数的用法可以参照小编事先写的稿子

回答:=IF(A2>=90,”优”,IF(A2>=80,”良好”,IF(A2>=60,”及格”,”不及格”)))

皇家赌场网址hj9292 39

上面的是间接出结果,对于新手幸免混乱有个简单的多规格堆叠法

如图

皇家赌场网址hj9292 40

规律是那样的

1,先用1层的if,判断,比如B2单元格判断,是或不是当先86分,若是是,那么为优良,假设不是,则属于其余景况,那里权且不急着填,直接把另一种结果指向C2。

2,在C2单元格判断,是还是不是当先柒十四分,假如是那么属于杰出,不是则指向D3

3,D2单元格用于判断是还是不是合格

4,把合并B2 C2
D2单元格中的公式。先复制D第22中学的公式,不含if前边的=号,然后替换掉C2单元格中的D2那么些值,同理替换掉B2单元格中的C2。最终得出了总体的公式。

那东西用语录表达会略复杂些,实操起来应当做两次就精通了。这么做的裨益是多规格判断不便于失误。

回答:公式为:IF(B3>=90,”优”,IF(B3>=80,”良”,IF(B3>=60,”及格”,”不及格”)))

皇家赌场网址hj9292 41

回答:用if函数嵌套即可:

皇家赌场网址hj9292 42

皇家赌场网址hj9292 43

=SUM(IF(B2:B8=”女”,C2:C8))   点击control+shift+enter
(那一个地点关系到数组的概念,后续介绍) 获得结果。

中间:MATCH(B2,{0,60,70,85},1),是同盟比B2小的最大值在数组{0,60,70,85}中的位次。

班里女子学校友的平均战表

那CHOOSE函数就足以简写为:“=CHOOSE(位次,”不合格”,”合格”,”突出”,”卓绝”)”,位次是几,那就回到”不沾边”,”合格”,”杰出”,”杰出”中的第多少个阶段。

=AVERAGE(IF(B2:B8=”女”,C2:C8))  点击control+shift+enter
(那一个地方同上关系到数组的概念,后续介绍) 得到结果。

比如说,借使B2多少是66,那比66小的最大数据便是60,60在数组中的是第三位,那遵照CHOOSE函数,66对应的正是“合格”。

IF函数排除错误值汇总

**INDEX+MATCH函数

✅总结销售值总和,出去错误的值

在G2单元格输入公式:“=INDEX({“然而关”;”合格”;”优良”;”特出”},MATCH(B2,{0,60,70,80},1))”。

皇家赌场网址hj9292 44

皇家赌场网址hj9292 45

=SUM(IF(ISE昂科威ROKoleos(E2:E8),0,E2:E8))        点击control +shift+enter获得结果
 

总结

🌻🌻🌻IF函数提取数据并转换报表结构

如上的两种艺术,同样适合于计算个税、业绩提成等地点。

步骤:

1.用IF函数判断数据的绝对地点,分别领到性别、年龄、分数到分裂的列;

皇家赌场网址hj9292 46

F2:=IF(D2=””,””,E2)G2:=IF(D2=””,””,E3) H2:=IF(D2=””,””,E4)

选拔了单元格的冲突引用,实现了人名所在地方和内需领取数额的撼动调用,利用IF完成标准化判断,仅领到符合条件的数额。

2.将公式的结果转化为值;

3.筛选并剔除空白行记录;

皇家赌场网址hj9292 47

4.修饰并提取无用的前缀;

皇家赌场网址hj9292 48

control+H是替换的火速键

皇家赌场网址hj9292 49

皇家赌场网址hj9292 50

小结:1.IF函数断定数据地点并提取;

2.查找替换的快捷键control+H;

3.替换的小技巧:(*:);

未完待续~~~~

穿插:

📍INDEX:

✅接二连三区域中index函数的公式格式是=INDEX(array,row_num,column_num)

里面array表示大家要引用的区域;

row_num表示要引用的行数;

column_num表示要引用的列数;

终极的结果正是引用出区域内行列交叉处的剧情。

✅在excel中检索某类别数据数组中的第几一点都不大数的时候会用到small函数,公式格式=SMALL(array,k)

array 查找的数据区域;

k 为回去的数码在数组或数额区域里的职责(从小到大);

相关文章

Leave a Comment.