乔山办公网我们一直在努力
您的位置:乔山办公网 > excel表格制作 > Excel基础知识-取整函数圈里的哼哈二将-excel取整函数

Excel基础知识-取整函数圈里的哼哈二将-excel取整函数

作者:乔山办公网日期:

返回目录:excel表格制作

​上一篇文章我们一起了解一下ROUND的函数的扩展用法,带着上文的余温,我们一起来看一下数学计算函数中的哼哈二将ROUNDUP和ROUNDDOWN,说起来它们跟ROUND的有些渊源,就是它们不光完美继承了ROUND函数的特性,也有自己特有的本质,UP和DOWN就是它们特有标志,接下来我们就一起剖析剖析它们特有的本质是什么吧!

为了方便描述,我们给ROUNDDOWN和ROUNDUP取个外号叫小U和小D,小U(ROUNDUP)比较爱吹牛,比如她有只有一毛,她就会告诉别人她有一块,而小D(ROUNDDOWN)正好相反,做人比较谨慎,总留有余量,他比如有1.99元,他跟别人说的时候,他总说自己只就1块钱,直到他有的钱超过2元了,才说他有2元;了解这些后,我们一起看看他两的使用上语法结构看如下图:

Excel基础知识-取整函数圈里的哼哈二将

ROUNDUP函数和ROUNDDOWN函数语法结构图

​我们简单的测试部分数据如下图:

Excel基础知识-取整函数圈里的哼哈二将

ROUNDDOWN函数和ROUNDUP函数的测试数据效果图

通过上面的数据,我们对小U和小D有了初步的了解,它俩完美的继承的round函数的特性,比如精度数为N,则的精度数为10的-N次方,若果N为1,则精度基数为0.1,再按照它俩自己的规则进行取舍加减精度基数0.1,下面我们就以N为-1为例,近距离确认一下它俩是不是跟ROUND函数一样好玩呢?我们拭目以待吧!

精度数为-1,则精度基数为10;

小D脾气用数学表达式表示为:>=0且<10,返回0;>=10且<20,返回10;>=20且<30返回20;

小U脾气用数学表达式表示为:=0,返回0,>0且<=10,返回10,>10且<=20则返回20;依次类推,当我们知道这些,就能弥补ROUND函数在判断范围上的不足,ROUND函数能表达的范围特点以5为基数,以10为进阶数,这样的计算出的范围阶段数为,5,15,25,35...N*10+5;为了加深对小D和小U的了解,我们用生活的案例来练习一下!

生活案例

需要将小学四年级学生的成绩进行等级划分,所学的科目有:语文,数学,英语,品德和生活,科学,共5课主科,每科的成绩为90分及以上为A,包括80分及到90分为B,60分到80分为C,并作为三好学生的首要推荐条件,统计每个班级的A较多的前10名学生,如果A一样多,就看B的级别多少,以此类推,每个人有多少个A?(注:当然学科中不能有不及格的情况)

Excel基础知识-取整函数圈里的哼哈二将

四年级03班学生成绩表

普通解法

当我们遇到这个问题的,我们该从何做起呢?当然先整理条件了,将条件成数学表达式:

每科成绩>=90,则评级=A

成绩>=80 且成绩<90 则评级= B

成绩>=60 且成绩<80 则评级=C

先说一下常见的处理方式:IF + 文本函数SUBSTITUTE(Excel基础知识-文本函数实例公式的编写思路和优化方法

(注:答案并不唯一,我说的方法只局限于我们写过相关的函数介绍)

万事从头起,我们先来处理一个语文成绩的等级判断=if(语文>=90,"A",if(语文>=80,"B",if(语文>=60,"C",""),然而在排序混合成绩的排名时,需要比较每个学生的A,B,C的多少?而IF函数并不擅长处理这种多条件判断,这里我们就给每个等级设定一个数值,然后每个人综合成绩再用数值表示,这样比较是不是就容易多了,这种方法还有一个科班的名字叫,加权计算法,是用来解决多条件组合判断的有效测定方法。

我们给A定义个数值为100,B定义一个数值为10,C则定义一个数字为2,设定完这些之后,还需要考虑一个条件,就是这个学生不能有不及格的情况,这个值我们怎么考虑的呢?我们考虑最坏的情况,就是1科不及格,4科全是A的情况,且不能大于等于5科C的情况即10,只要符合这个区间的值都可以,哪我们就给不及格为-500吧,如果我们再继续使用IF来判断,对加权值来计算就异常复杂了呢?这时就需要我们的SUBSTITUTE函数登场了!

如果使用SUBSTITUTE函数解决排名的问题的之前,需要经将每个学生的成绩生成字符串组合,然后统计每个字符的数量,再加权计算得出排名分数。其实生成字符串组合很简单,就是把各科对应的字母拼接在一起,这个字符串的公式=if(c2>=90,"A",if(c2>=80,"B",if(c2>=60,"C","")))&if(d2>=90,"A",if(d2>=80,"B",if(d2>=60,"C","")))&if(e2>=90,"A",if(e2>=80,"B",if(e2>=60,"C","")))&if(f2>=90,"A",if(f2>=80,"B",if(f2>=60,"C","")))&if(g2>=90,"A",if(g2>=80,"B",if(g2>=60,"C",""))),由于公式太长,用String来代替生成成绩序列;

接下来我们该如何求出A,B,C在String中的数量呢?

原理:先求出String的总长度,然后用SUBSTITUTE函数分别将A,B,C替换为空,在总长度减去分别替换后的长度就是A,B,C分别出现次数;

A的出现次数=len(String)-len(substitute(String,"A",""));

B的出现次数=len(String)-len(substitute(String,"B",""));

C的出现次数=len(String)-len(substitute(String,"C",""));

而未及格的科目=5-len(String);

最后汇总一下带有加权值的公式=(len(String)-len(substitute(String,"A","")))*100+(len(String)-len(substitute(String,"B","")))*10+(len(String)-len(substitute(String,"A","")))*2+(5-len(String))*-500;最后再String对应的IF公式代入,完成拖拽填充公式,然后再按从大到小排序,就能找出前10名A比较多的学生名单。

至于统计一个同学多少A,直接用改String的公式为=if(c2>=90,"A","")&if(d2>=90,"A","")&if(e2>=90,"A","")&if(f2>=90,"A","")&if(g2>=90,"A","");当然你也可以通过修改String对应的公式,直接将加权值分别代替A,B,C进行求和计算也行,不过这种处理方式,维护起来比较麻烦,稍微条件变化就需要重新编写公式,非常繁琐,下面就介绍一种易维护公式编写方法。

高阶解法

我们使用lookup+rounddown,不过在实现上线的要求之前,需要创建一个辅助表Grade(分级表)数据结构如下:

Excel基础知识-取整函数圈里的哼哈二将

Grade表的数据示意图

Grade表的数据示意图

​语文权值公式=ifna(lookup(rounddown(c2,-1),Grade!A:A,Greade!C:C),-500)

数学权值公式=ifna(lookup(rounddown(d2,-1),Grade!A:A,Greade!C:C),-500)

英语权值公式=ifna(lookup(rounddown(e2,-1),Grade!A:A,Greade!C:C),-500)

品德与生活权值公式=ifna(lookup(rounddown(f2,-1),Grade!A:A,Greade!C:C),-500)

科学权值公式=ifna(lookup(rounddown(g2,-1),Grade!A:A,Greade!C:C),-500)

综合权值=SUM(IFNA(LOOKUP(ROUNDDOWN(C2,-1),Grade!A:A,Grade!C:C),-500),IFNA(LOOKUP(ROUNDDOWN(D2,-1),Grade!A:A,Grade!C:C),-500),IFNA(LOOKUP(ROUNDDOWN(E2,-1),Grade!A:A,Grade!C:C),-500),IFNA(LOOKUP(ROUNDDOWN(F2,-1),Grade!A:A,Grade!C:C),-500),IFNA(LOOKUP(ROUNDDOWN(G2,-1),Grade!A:A,Grade!C:C),-500)),然后拖拽完成公式填充,进行从大到小排序就能找出相比较A最多的同学啦,你自己观察数据也会发现,其中某写同学的总分数并不低,但却排在后面,这就是加权计算法的魅力,只需我们设定好权值,再复杂的逻辑问题,也能迎刃而解。

最后补上每位同学得A的公式:=IFNA(LOOKUP(ROUNDDOWN(C2,-1),Grade!$A$4,Grade!$B$4),"")&IFNA(LOOKUP(ROUNDDOWN(D2,-1),Grade!$A$4,Grade!$B$4),"")&IFNA(LOOKUP(ROUNDDOWN(E2,-1),Grade!$A$4,Grade!$B$4),"")&IFNA(LOOKUP(ROUNDDOWN(F2,-1),Grade!$A$4,Grade!$B$4),"")&IFNA(LOOKUP(ROUNDDOWN(G2,-1),Grade!$A$4,Grade!$B$4),""),最终的效果如下图:

Excel基础知识-取整函数圈里的哼哈二将

完成公式填充的效果图

​在整个的完成任务的过程中,我们可能有这样的认识,就是越简答的方法,在相对简单的问题上简单直接且有效,当简单的方法遇到逻辑复杂度较高的任务时,就显得力不从心了,没有任何一个方法能解决所有的问题,只有我们不断的学习和深入了解知识点的差异,才会让你在工作中如鱼得水,不过如果你已经工作了,建议你在工作的时候,解决一个问题或者了解某个知识点的时候,尽量的了解深入一些和在时间允许的情况,多尝试用的新的解方法。

至于ROUNDUP函数的用法就作为课后作业,留给你去思考吧!好了,今天的文章就写到这了,希望屏幕前的你读过有所收获,也希望你多多使用这些函数也用法,俗话说得好,熟能生巧一点都不假,也欢迎你的指正文中的不当之处,还是那句话,学习Excel的路上你并不孤单,我们一路同行!期待你把工作中遇到的问题私信我,我看到后会第一时间给你回复!

相关阅读

关键词不能为空
极力推荐

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