乔山办公网我们一直在努力
您的位置:乔山办公网 > excel表格制作 > excel if模糊匹配

excel if模糊匹配

作者:乔山办公网日期:

返回目录:excel表格制作

如何用Excel中的IF函数判断学生的成绩是否优秀?


用IF函数判断学生的成绩是否优质是很简单的,曾写过一篇文章《玩转学生成绩分析》不仅能给出答案,还能让老师们做更具体的分析,在次发出来,希望能给同仁们有所帮助。


我们老师,让学生考试是经常的事。而每一次考试成绩出来后是不是需要进行数据分析呢?今天介绍用IF函数和LOOKUP函数等对学生成绩进行等级划分和统计的方法。

一、什么是IF函数

IF函数及条件函数,基本语句是IF(logical_test, [value_if_true], [value_if_false]),其中logical_test是逻辑检验的表达式;value_if_true是检验结果为真的返回值,value_if_false是检验结果为否的返回值。意思是如果满意什么条件,则返回某值,否则返回另一值。

二、IF函数的基本用法

以下表为例,当C3中的分数在90分(总分150)以上为及格,90分 以下不及格,在D3单元格输入公式=if(c3>=90,“及格”,“不及格”)【注意:所有符号都是英文符号】,并拖动公式填充到D列的其它单元。


三、IF函数的嵌套用法

如下图,假如我们将成绩划分为“优秀”(135分以上)、“较优秀”(120分以上)、“良好”(105分以上)、"合格"(90分以上)、“不合格”(90分以下),其中还可能出现有同学缺考的情况,于是在D3中输入公=if(c3>=135,"优秀",if(c3>=120,"较优秀",if(c3>=105,"良好",if(c3>=90,"合格",if(c3="","缺考","不合格"))))),特别说明一下缺考的情况,缺考则该单元格不填(空值),而不是填0。拖动公式填充,则每位同学的等次就出来了。


四、用LOOKUP函数划等次

当我们有时候等次分级较多时,用IF函数就比较麻烦了,这时可以用LOOKUP函数来代替。如下图,在I列和J列中分出分数段和等级,在D3中输入=LOOKUP(C3,$I$3:$I$7,$J$3:$J$7),注意“$"符号是指的绝对引用,此时如果不绝对引号,在拖动公式填充时对应的单元格就会下移,就不行了。还有一特别注意的是LOOKUP函数是向后兼容,所以在分级时一定要从低分到高。


五、IF函数的综合用法

当我们有同学缺考时(有空值),可以使用IF和LOOKUP的联合,在D3中输入=if(C3="","缺考",LOOKUP(C3,$I$3:$I$7,$J$3:$J$7))。


六、按比例划定等次

以下图为例,共有29名同学的成绩(C3:C31),按A等20%,B等30%,C等30%,D等20%来划定等次。

(一)如果用IF函数,先增加一列,计算出每个分数排名在总数中的比例,用公式=RANK(C3,C$3:C$31)/(COUNTA(C$3:C%31),其中=RANK(C3,C$3:C$31)是分数在所有分数中的排名,COUNTA(C$3:C%31)是统计区域内所有不含空值的单元格个数(当然为里知道有29个数据,可不用这个函数,直接/29),由于公式需要下拉进行填充,所以行区域的行号要用绝对引用($)。


再按前面IF函数的嵌套用法进行等次的划定。

(二)直接用LOOKUP函数,输入:=LOOKUP(PERCENTRANK(C:C,C3),{0,0.201,0.501,0.801},{"D","C","B","A"}),此公式中PERCENTRANK(C:C,C3)意思是C3数据在C列中的所有C列数据中的百分比排名,此处引用了整个列,也可以像前面一样用C$3:C$31为个区域。因为PERCENTRANK排名是降序的,即第一名是1,所以后面的数组中A占20%,也就是0.8以后的,B占30%,也就是0.5以后的,依次类推。

七、各等次数据统计

当我们划定了等次后,可能学需要统计每个等次的学生数,则可以用公式:=COUNTIF(E$3:E$31,G3),含义是在H3中统计E3:E31区域内等于G3的数量,区域是不变的,所以绝对引用,但G3在下拉填充时是需要变为G4的,所以相对引用。

更多教育观点、教学经验、学习资源、教育技术,敬请关注“轻风教育”。

Excel中如何进行多条件模糊求和?


【本文示例文件获取方法见文末】

我们在工作中经常会遇到条件求和的情况,有时候不止一个条件,甚至还有模糊条件,这时候,SUMIFS函数就可以派上大用场!今天,技巧君就来讲一下SUMIFS函数的用法!

这个问题是典型的多条件模糊求和,国籍惯例,先上效果:

下面我们来详细学习一下SUMIFS函数

SUMIFS函数是什么

功能:对满足多个条件的单元格的数值求和

语法:SUMIFS(求和区,条件区1,条件1,[条件区2,条件2]…...)

什么时候用SUMIFS

一般情况下,对于多条件求和 我们会使用SUMIFS函数 ;那么,什么是条件求和呢?我们先来理解一下“条件”的含义。

例如,将某家公司的员工作为研究对象,一个公司会有多个部门,每个员工可能来自全国各地,员工性别各异......每个员工都会有多个不同的属性,如果,我们要求江西人事部员工的总工资,那么这个问题就含有多个限定条件籍贯属性条件为 江西,部门条件为 人事部,求和项为 工资,这就是一个典型的多条件求和问题,像这种情况就很适合用SUMIFS函数!

SUMIFS函数运用示例

1、求蜀国人事部员工的总工资(基础应用)

=SUMIFS(D2:D10,C2:C10,F3,B2:B10,G3)

2、求工资3000~5000员工的总工资(条件区和求和区重合)

=SUMIFS(D2:D10,D2:D10,F7,D2:D10,G7)

3、求广东销售1部总业绩(模糊条件求和)

=SUMIFS(D14:D18,B14:B18,"*广东*",C14:C18,"销售1部")

这里的条件“*广东*”运用了通配符“*”,它代表任意数量的任意字符,这个条件的含义是:包含“广东”两个字的任意字符

以上就是常见的几种SUMIFS函数的基础用法!欢迎留言评论一起探讨哦!

示例文件获取

点击右上角红色按钮 关注EXCEL精选技巧,然后私信发送【SUMIFS函数】即可获取

关注EXCEL精选技巧,每天学习3分钟,坚持一个月,你将大不同!

本文标签:

相关阅读

  • excel if模糊匹配

  • 乔山办公网excel表格制作
  • 如何用Excel中的IF函数判断学生的成绩是否优秀? 用IF函数判断学生的成绩是否优质是很简单的,曾写过一篇文章《玩转学生成绩分析》不仅能给出答案,还能让老师们做更具体的分析,
关键词不能为空
极力推荐

ppt怎么做_excel表格制作_office365_word文档_365办公网