计算机的世界就是0和1的世界,这符合了中国易经的思想,世界充满了变数,但是归根结底无非是两种,阴阳、是非、男女等等,所以我们先从EXCEL的IF函数说起,今天小编就来聊一聊关于excel技巧新手必看?接下来我们就一起去研究一下吧!

excel技巧新手必看(EXCEL经验分享七)

excel技巧新手必看

计算机的世界就是0和1的世界,这符合了中国易经的思想,世界充满了变数,但是归根结底无非是两种,阴阳、是非、男女等等,所以我们先从EXCEL的IF函数说起。

人生就是选择和判断

可以这么说,不仅仅是在EXCEL中,所有的编程语言用的最多的就是IF,或者说就是不停的根据条件去判断。因此IF函数第一个参数就是条件,这条件不是TRUE就是FALSE,然后的两个参数分别是当条件为TRUE的时候返回一个值,当条件为FALSE的时候返回另一个值。

而在我们实际工作中,我们要判断的条件可能不是一个,是多个。条件与条件之间也是有层次的,可能需要同时满足,也可能只需要满足其中一个或几个,还可能先判断了条件1才去判断条件2,所以IF函数很多时候我们会继续套用IF。当IF函数第一个参数条件得到了TRUE的结果,那么他会执行第二个参数,而第二个参数又是一个IF函数。如果IF套用的太多,那就太影响阅读了。所以这里隆重推出AND和OR函数。

满足条件的可能性无非也是两种,同时满足和只满足部分,同时满足,我们叫且,部分满足我们叫或。AND和OR函数的用法,都是在中间添加条件参数,随便你写多少个条件,条件与条件之间用英文的逗号隔开。那么AND函数无论有多少个条件都必须满足就返回TRUE,只要有一个不满足则返回FALSE,而OR函数无论多少个条件只要满足一个就返回FALSE。

我们来举个例子。我做了一份学生成绩表,成绩科目有语文、数学、英语、物理、化学、政治、历史,每一科的分数都是0-100分。我们根据分数来判断这个学生是理科生还是文科生,还是全能的全科生。条件规定如下:

全部全科成绩都大于等于80分的就是全科生;语文、英语、政治、历史大于等于80分的就是文科生;数学、物理、化学大于等于80分的就是理科生。为了简便我把文科生或理科生放在一起,称之为文或理科生。

或许你会问了,如果这个学生是全科生,那他还是文科生或者理科生吗?这就是我刚才说的条件是有层次的,从全科生的角度去理解,那这个学生即是文科生就是理科生,不过在这里我规定,如果是全科生了,就不会在文或理科生那一列显示结果。所以一个学生会出现三个结果,全科生、文或理科生、啥都不是。

再来看条件。全科生是所有学科分数大于等于80分,因此每个学科判断分数条件之间是AND,即是且;文或理科生是把文科和理科分别进行判断,只满足其中一个即可,所以文科还是理科条件之间的关系是OR,即是或,同时文科或者理科内部判断的条件之间又是一个AND。所以文或理科生的函数一定是一个OR函数里面嵌套了两个AND函数。

整个函数一定是一个大大的IF函数,那我们先判断哪个条件呢?对于多条件来说,先判读全部满足或者全部不满足的情况,然后再进行分支。因此,我们先判断他是不是全科生,然后判断文或理科生,最后就是啥都不是的学生了。

标题列排列从A列到I列依次为学生姓名 语文、数学、英语、物理、化学、政治、历史、优等生,数据从第二行到第九行,一共8个学生。文科就是语文、英语、政治、历史,理科就是数学、物理、化学。既然先判断是否为理科生,所以我们从I2单元格开始编辑公式。

如果你编辑公式还不是很熟练,那就从里面往外编辑。所有学科分数都大于等80分,我们就用AND函数包裹所有条件,得到函数:

=AND(B2>=80,C2>=80,D2>=80,E2>=80,F2>=80,G2>=80,H2>=80),你得到结果不是TRUE就是FALSE。

然后我们再套一个IF函数,并且第二个参数即结果如果是TRUE返回”全科生”,得到函数:

=IF(AND(B2>=80,C2>=80,D2>=80,E2>=80,F2>=80,G2>=80,H2>=80),"全科生")

这个函数少了一个FALSE情况下返回的结果,但EXCEL并没有报错吧,其他EXCEL已经有了智能判断,你即便没写FALSE的参数,如果这个函数真的是FALSE的情况他就返回FALSE。

接着我们就要编辑第三个参数了,文科生或理科生的判断。文科生,即所有文科成绩全部大于等于80分,所以公式就是

AND(B2>=80,D2>=80,G2>=80,H2>=80)

理科生,即所有理科成绩全部大于等于90分,所以公式就是

AND(C2>=80,E2>=80,F2>=80)

文科生和理科生之间是只要满足一个即可,所以他们之间是OR的关系,公式就是

OR(AND(B2>=80,D2>=80,G2>=80,H2>=80),AND(C2>=80,E2>=80,F2>=80))

好了,我们只需要在做一个IF判断,满足文或理科生的条件返回 “是”,如果不满足,那么这个学生就啥都不是了,那么返回空白。

这个公式很长,但其实仔细想想并不复杂,你只要想通其中的条件层次关系就行了。至于编辑公式,可以把全科生涉及的单元格全部复制过来,粘贴到判断文或理科生的条件内,然后分别删除文科或者理科涉及的单元格,这样就加快了编辑公式的速度。

我们得到了一个很长的公式

=IF(AND(B2>=80,C2>=80,D2>=80,E2>=80,F2>=80,G2>=80,H2>=80),"全科生",IF(OR(AND(B2>=80,D2>=80,G2>=80,H2>=80),AND(C2>=80,E2>=80,F2>=80)),"文或理科生",""))

下拉公式即可得到所有的结果了。

我差不多用了所有的篇章写了IF函数,是因为我觉得这个条件判断或者说逻辑判断是锻炼脑子的。如果你的函数在用法上有错误,EXCEL当即会有错误提示,而如果条件判断出现错误,EXCEL是不会报错的,他只会忠实的执行你写的函数并返回结果。所以说函数可以熟能生巧,逻辑判断完全是个人脑子里面的思考了。我有时候会遇到条件多、复杂的情况,写出来的条件判断就会错误,从而导致结果是错误的,这个时候我会花挺多的时间在脑子里不停的思考,这样能锻炼自己的脑子。我还发现一个有意思的事情,经过长期的观察,我发觉女性对于IF函数的使用不如男性,可能印证了那句话,女人是感性的男人是理性的。

和判断相关的函数

因为我们经常要用到判断,而判断之后会有一番操作,或者反过来对经过一番操作的结果来判断,所以EXCEL将判断和操作整合在一起内置了很多函数。这些函数虽然多,但理解起来一点都不难。主要分为两大类,一类是统计判断函数,另一类是结果判断函数。

统计判断函数,其实就是统计函数加上判断,统计函数就是我们常用的求和(SUM),计数(COUNT),平均(AVERAGE)等等数理统计函数,这些函数后面一般都可以加上IF,成为一个统计判断函数,这样我们就有SUMIF条件求和,COUNTIF条件计数,AVERAGEIF条件平均等等了。微软毕竟是一个伟大的公司,他觉得用户们对于条件判断不止一个,所以在IF后面加了个S,搞成了英文的复数形式,那就有了SUMIFS,COUNTIFS,AVERAGEIFS等等多条件统计函数了。在此,我再次的强调,学好英文对于掌握函数的帮助非常大。

单条件的统计判断函数的参数有三个,第一个是判断条件所在的范围,第二个是判断条件,第三个是求和范围。而多条件统计判断函数的参数则是顺序有些变化,因为是多条件,所以参数理论上可以非常多,但第一个是求和的范围,第二个是条件1的范围,第三个是条件1的条件内容,第三个是条件2的范围,第四个是条件2的条件内容,然后以此类推多条件。

重点说下条件内容如何写。所有的条件都用英文的双引号包裹起来,比如”>=100”,意思就是对符合大于等于100的条件的单元格进行求和。如果你逻辑符号就是那些大于小于等于符号不写,那EXCEL就默认为等于。知识点来了,如果你需要对是否包含某个字符做判断的如何写?假如你需要判断是否包含中国两个字,那么条件就这样写 “*中国*”,用星号来表示其他所有字符然后中间写上中国即可。

下面我就利用刚才学生成绩的例子来演示一下SUMIF函数,依次是对所有优等生是文或理科生的学生的语文成绩求和、所有物理成绩大于90分的学生的英文成绩求和,所有优等生中包含 科 这个字的学生的数学成绩求和。

结果判断函数基本上都是IS开头的函数,IS的意思就是 是,所以他通过结果来判断是否,也很好理解。大家在公式输入栏中输入 =IS的时候,EXCEL会列出所有包含IS的函数,然后一个个往下查看,EXCEL会给出这个函数作用的提示,这些都不难。

这其中比较常用的是ISERR或者ISERROR函数,ERROR就是错误的意思,就是用来判断公式是否出错。有时候我们批量拉函数,会出现错误的结果,比如VLOOKUP函数你不能保证都能匹配到结果,所以套上ISERROR函数并且配合IF,那么如果是错误你就让单元格显示空白,否则则是显示正常内容,那么公式下拉后就不会出现错误的代码,从而影响查看了。