常用Excel函数及公式应用.docx

常用Excel函数及公式应用.docx

《常用Excel函数及公式应用.docx》由会员分享，可在线阅读，更多相关《常用Excel函数及公式应用.docx（26页珍藏版）》请在冰豆网上搜索。

常用Excel函数及公式应用

水电七局锦屏二工区

常用Excel函数及公式应用

内部小资料

第一节说明

这里不用重复Excel的基本操作,只是把一些常用的基本信息罗列一下：

1、Excel2003的文件扩展名是".xls";Excel2007的是".xlsx"；

2、Excel2003如果需要打开2007的文件,必须安装微软的补丁（但是有些颜色/样式无法兼容）;2007是可以兼容2003的文件格式；

3、Excel2007在外观上和2003有非常大的区别,熟悉2003的要适应2007需要一段过程,如果您找不到菜单在哪里就请参阅微软提供的Flash；

4、Excel2003有65536行,Excel2007能容纳100W行,能够满足我们大部分的要求,但是建议您还是不要放太多数据,否则效率非常低；

5、Excel的函数都是由等号（=）开始后面跟函数的名字；

6、Excel的函数在键入的时候会自动显示参数类型/提示,能够帮助我们了解函数的用法；

7、Excel输入身份证等长数字时会自动把后面几位变成0，那是因为Excel不能显示太长的整数；所以您可以把单元格变成文本格式,或着在身份证前加单引号（e.g.:

‘250204200803241016）；

8、Excel函数本身是不区分大小写的,当然参数中如果有字符（串）是分的；

9、函数当中的参数如果是字符串的一定要加双引号（这个比较容易忘记）；

10、要学习函数,就是要把更多的重复或者机械的工作交给函数，希望以下内容对大家有帮助。

第二节IF函数

第一个函数要讲的是函数"If".这个函数很常用,用它来判断一个表达式或者单元格的内容是否你所想要的结果,如果是就执行某个动作,如果不是就执行另外一个动作.

所以,If函数有三个参数:

=if（参数1,参数2,参数3）

[补充一下:

Excel写函数的时候都是以"="开头,这个就是告诉Excel现在开始写函数咯!

然后函数当中的参数都是以逗号隔开!

]

"参数1"是一个"逻辑表达式",什么是逻辑表达式呢?

比如:

"Spider-Man是不是人?

"这个就是一个逻辑表达式,它的结果是"Yes"或者"No".在逻辑表达式中,"Yes"就是TRUE（真）,"No"就是FALSE（假）（同时,数字0是FALSE,其他数字是TRUE）。

也就是说,你的第一个参数的结果是"TRUE"或者"非0的数字",则If函数执行"参数2",否则执行"参数3"。

现在举几个例子来说明:

例子1:

如下图,A列是员工的名字,B列是员工的年龄.某天,你老板说:

"喂,把那些超过30岁的都找出来"。

如果真的只有下面4个员工当然好办,眼睛一看就出来了。

但是如果你在富士康几十万的员工名单你怎么找?

所以我们在C列加上">30YearsOld"。

然后开始判断。

我们要怎么判断呢?

很简单,就是判断B列的每个单元格是否大约30就可以了（参数1）,然后如果大于30就显示"大于30岁"（参数2）,否则显示"没到30,幼齿哦"（参数3）。

所以函数就是（在C2单元格中）:

=if（B2>30,"大于30岁","没到30,幼齿哦"）,结果如下图:

参数1:

B2>30

参数2:

"大于30岁"

参数3:

"没到30,幼齿哦"

因为在C2单元格中,B2的值是35,和30比较的时候它是大于30的,所以表达式B2>30的结果是TRUE.即执行参数2.

因为在C3单元格中,B3的值是28,和30比较的时候它是小于30的,所以表达式B3>30的结果是FALSE.即执行参数3.

这样,我们就很容易定位到我们需要的数据中。

例子2:

如下图,A列为名字,B列是一月份的Bonus,C列是二月份的Bonus.请找出两个月Bonus总和超过2000的人.

所以我们的逻辑是什么呢?

根据题目:

就是B列的Bonus数加上C列的Bonus数（参数1）,如果结果大于2000则就是我们要找的人,否则不是.所以函数就是:

=if（（B2+C2）>2000,"超过2000咯!

","比2000少啊!

"）同样我们把函数往下拉,这样结果就出来了:

参数1:

（B2+C2）>2000

参数2:

"超过2000咯!

"

参数3:

"比2000少啊!

"

因为在D2单元格中,B2+C2的结果是1900,和2000比较的时候它是小于2000的,所以表达式（B2+C2）>2000的结果是FALSE.即执行参数3。

因为在D3单元格中,B3+C3的结果是3200,和2000比较的时候它是大于2000的,所以表达式（B3+C3）>2000的结果是TRUE.即执行参数2。

注解:

在Excel中,加减乘除分别是:

加（+）;减（-）;乘（*）;除（/）.

例子3:

上面两个例子比较简单,都是直接利用IF函数来判断某一个表达式.但是通常我们在工作中会遇到比较复杂的情况,比如我们把例子2改一下:

要求找出在一月份Bonus超过1000同时在二月份超过1500的人.

这个时候如果我们只用IF函数来完成：

=if（B2>1000,if（C2>1500,"符合条件","不符合条件"）,"不符合条件"）

参数1:

B2>1000

参数2:

if（C2>1500,"符合条件","不符合条件"）

参数2.1:

C2>1500

参数2.2:

"符合条件"

参数2.3:

"不符合条件"

参数3:

"不符合条件"

这个函数是什么意思呢?

我们根据题目需要分两步走.第一步,判断B2是否大于1000,如果不是大于1000,就直接显示"不符合条件"（因为一月份都不符合了,就算二月份符合也没有用,不是我们要找的）;对于大于1000的,我们再做一次IF函数计算,这个时候的前提已经是一月份大于1000了,所以我们要做的判断是二月份是否大于1500（蓝色的判断）,如果大于1500则显示"符合条件",否则也显示"不符合条件".（这个就是所谓的函数嵌套）

通过这种方式,我们就可以同时判断多个单元格（或者多个表达式）的情况.

总结:

IF函数就是判断"参数1"是否为"TRUE"（或者"FALSE"）,（表达式成立或者计算结果非0则为TRUE）,如果是TRUE则执行参数2,如果FALSE则执行参数3.

希望这样能够帮助大家足够清楚的理解IF函数的用法。

第三节AND/OR/NOT函数

现在我们来介绍三个逻辑函数:

AND（与）;OR（或）;NOT（非）。

我们讲IF函数的时候,讲到IF的第一个参数是逻辑值,也就是TRUE或者FALSE.今天我们学习的这三个函数就是判断逻辑值的函数,得出的值还是逻辑值.我们下面一个个来解释:

最简单的一个NOT（非）:

"非"就是"不是".如果我们有一个逻辑"我吃过早饭了",那么NOT（"我吃过早饭了"）的结果就是"我没有吃过早饭".（当然Excel没有办法这么智能判断中文句子,这个只是例子,大家不要Copy到Excel然后骂我骗人,呵呵）.所以,

NOT（TRUE）=FALSE;NOT（FALSE）=TRUE（Clear?

）.

NOT函数只有一个参数（参数可以是一个值也可以是一个表达式）.

上面的例子中:

"A2"=（3>2）（翻译成中文就是"3比2大吗?

",所以结果是TRUE）

"A3"=NOT（3>2）（加了一个NOT,就把原来的结果倒了个,结果是FALSE）

[A5=10;A6=15]

"A8"=（A6-A7>0）（翻译成中文就是"A6减去A7的结果大于0不?

",（10-15）=-5当然比零小,所以结果是FALSE）

"A9"=NOT（A6-A7>0）（结果是TRUE）

2.第二个AND（与）:

"AND（与）"就是"并且"的意思.AND函数有两个（或以上）参数,AND的功能就是取这几个参数的交集.我们要记住的是,只要参数中有任何一个的值是FALSE,那么AND函数的值就是FALSE;当且仅当所有参数结果都是TRUE的时候,AND函数的值才是TRUE.

例子:

先将A1到A3单元格赋值:

[A1="ABC",A2="XYZ",A3=123]

"A5=AND（A1="ABC",A2="XYZ",A3=123）"结果是"TRUE",因为三个等式都是成立的.那我来考考大家下面两个的计算结果是什么?

No.1:

"A6"=NOT（AND（A1="ABC",A2="XYZ",A3=123））

No.2:

"A7"=AND（A1="ABC",A2="XYZ",A3="123"）

第一个很简单吧,就是把原来的结果再"非"一下,那NOT（TRUE）当然就是FALSE咯;

第二个呢?

结果为什么是FALSE?

大家注意了,上面写的是（A3=123）,没有加引号,也就是说123是数字一百二十三,而不是是字符串"123".所以在第二个AND函数中,前两个参数的结果是TRUE,但是第三个参数的结果是FALSE,所以整个函数的结果是FALSE.（这时候大家有可能会问:

我在单元格里面输入123,它就自动是一百二十三啊,我怎么输入才能是字符串"123"呢?

这个时候我们不能输入"123",如果这样的话,Excel会当作前后两个双引号加上字符串123;其实很简单,我们在数字123前面加上一个单引号（’）就可以了。

这样数字123就变成字符串123了（肉眼看上去是一样的）.这个还有什么用呢?

比如你输入身份证或者银行卡的时候,数字太长Excel会自动截断后面的数字同时变成科学计数法,这个时候我们只要在前面加一个单引号就搞定了（e.g.–‘350206200803241016,这样输入Excel就会把输入当成是字符串,大家可以在Excel里面试一试就明白了）.

第三个是OR（或）:

OR其实和AND的用法和参数都一样,区别是"AND只要有一个参数是FALSE则结果是FALSE";"OR函数只要有一个参数是TRUE则结果就是TRUE".

例子:

我们继续用上面AND例子中的数字:

[A1="ABC",A2="XYZ",A3=123],那么:

A5=OR（A1="ABCD",A2="XYZW",A3=123）结果是TRUE,大家看到第一和第二个参数都是FALSE,但是第三个参数是TRUE,所以结果是TRUE;

A6=OR（A1="ABCD",A2="XYZW",A3=1234）结果是FALSE,大家看到三个参数都是FALSE,所以结果是FALSE（一个TRUE都没有）;

关于AND/OR/NOT的基础用法我们已经介绍了,相信大家对这三个函数有了一定的认识.但是我们在实际的应用中一般都不会这么简单,通常都需要多种判断的结合。

例子1:

如下图所示数据,列出了TeamA和TeamB中每个Agent某天的电话量和邮件处理量.我们的问题是:

请找出电话和邮件量都多于20个的Agent。

分析题目（就像我们在学校考试一样,拿到题目第一就要审题）:

要找出电话和邮件量都多于20的Agent,也就是说"电话量要大于20"并且"邮件量也要大于20":

"电话量要大于20"用公式表达就是（C2>20）

"邮件量要大于20"用公式表达就是（D2>20）

那么:

"电话量要大于20"并且"邮件量也要大于20"就是:

E2=AND（C2>20,D2>20）（然后我们用之前说的把鼠标移到E2单元格的右下角变成粗体十字的时候双击.这样我们就得到下面的结果:

这样看其实已经知道结果了。

但是比较难看，那我们就用前面讲的IF函数来"美化"一下:

如果"电话和邮件量都多于20个",则显示"Good",其他显示"Normal".怎么写?

E2=IF（AND（C2>20,D2>20）,"Good","Normal"）（再次强调,字符串要用引号,这个大家容易忘记）结果如下:

例子2：

我们现在把题目再弄复杂一些,我们要找出"电话和邮件量都多于20个"或者"邮件量超过30个",同时"名字不叫"John"的Agent.继续审题:

要求1:

"电话和邮件量都多于20个"这个我们前面已经做了:

AND（C2>20,D2>20）

要求2:

"邮件量超过30个":

（D2>30）

要求3:

"名字不叫John":

（B2<>"John"）[不等于在Excel里面是"<>"]或者还可以怎样写?

我们前面学了NOT,所以也可以写成（NOT（B2="John"））[对于字符串的比较Excel是区分大小写的,这个大家要注意].接下来怎么做?

接下来就是Transformers变形金刚开始合体了.

看看我们的题目:

有一个"或者"和一个"同时".我们把"或者"翻译成"OR",把"同时"翻译成"AND".那么函数就变成=AND（OR（要求1,要求2）,要求3）.把上面的式子都套上去再用IF"美化"一下就变成:

=IF（AND（OR（AND（C2>20,D2>20）,（D2>30））,NOT（B2="John"））,"符合条件","不符合条件"）这样就得出了我们想要的结果（如下图）:

当我们在写嵌套函数的时候（就是一个函数套另外一个函数）,刚开始时一般都比较难适应,这个时候我们的建议是先把函数分开在不同的单元格里面,然后最后Copy到一起,然后再慢慢学习两个函数的嵌套,三个函数的嵌套…这样就会习惯了。

Excel会自动在嵌套函数中把相对应的括号标上不同的颜色（如下图）,这样你在检查你的公式的时候就比较容易定位。

再反黑看看:

第四节FIND函数

接下来我们要介绍的函数是:

Find,相信了解的朋友都知道,"Find"在英文中是"找,发现"的意思.因为Excel是老美做的,所以在Excel里面,"Find"函数就是一个查找函数。

"Find"函数一共有3个参数,如下:

=Find（你要找的字符或者字符串,在哪里找,从的几位开始找）-----返回的值是你要找的字符或者字符串第一次出现的位数。

需要强调的是:

Find函数是区分大小写的!

举个简单的例子:

"我要到日本找饭岛爱,从东京开始找!

"。

那么"饭岛爱"就是参数1;日本就是参数2;东京就是参数3;"地下"就是返回的结果。

具体的说:

参数1是你要找的字符或者字符串,比如"A","Good"或者某个单元格如"A1","B3"等。

参数2是你要在哪里找,比如你要在"whoareyou"里面找"u",那么参数2就是"whoareyou".和参数1一样,可以是你自己输入的内容,也可以是某个单元格的引用.

参数3是你要从参数2的的几位开始找,象上面说的,你如果想从"whoareyou"的第一个空格开始找,那么参数3就是4.这里要注意的是,参数3只是让我们从某个位置开始找,但是返回的值仍然是从第一位开始计算:

比如说:

=find（"u","whoareyou"）的结果是11;=find（"u","whoareyou",4）的结果也是11。

（你是不是要说:

那么第三个参数到底有什么用?

返回的都是一样的?

其实是有用的,因为Find找的是第一个符合参数1的字符（或者字符串）的位置,如果你要找第二个,第三个（几乎没有人找第三个）的时候就要用到这个功能了）

下面我们开始举例子:

例子1:

假设"A5"="给国内的朋友汇钱就用Taobao,给国外的朋友汇钱就用PayPal"这么一个字符串.我们要找"PayPal"在第几个字符?

很简单:

A4=Find（"PayPal",A1）--结果是29;

我们试一下找"paypal":

A5=Find（"paypal",A1）--结果是#VALUE!

出错了,说明Find是区分大小写的！

例子2:

可以看到在A1的字符串中,逗号后面有一个空格,Find怎么找空格呢?

是用""还是""?

我们来试验一下:

A4=FIND（"",A1）--结果是18

A5=FIND（"",A1）--结果是1

A6=FIND（"",A1,10）--结果是10

所以我们的结论是:

1.找空格的时候应该用的是"",就是双引号中间加一个空格;

2.双引号中间没有任何字符的时候其实是空字符,所以查找的结果是参数3或者当参数3没有的时候结果是1.（一般不会做这种事情,所以大家知道一下就可以,没有必要太在意）

例子3:

我们要找A1单元格中的第二个"朋友"在哪里。

审题：

要找到第二个朋友,肯定要用到第三个参数,所以也不难,从第一个"朋友"后面开始找就可以了:

A4=FIND（"朋友",A1,7）结果是23。

是很简单.但是这样是用眼睛看的.我们前面已经说了,学习函数的时候就尽量不要用眼睛算,不然函数就没有意义了。

所以,上面的函数得到了正确的结果,但是不是我们想要的.我们再想一步,我们知道要在第一个"朋友"后面找,那"第一个朋友的后面"应该是几呢?

（这个"几"是不是就是参数3?

）.答案是肯定的,我们所需要的参数3就是找到第一个"朋友"然后加2就可以了（因为"朋友"是2个字符）.找第一个朋友的函数是:

=Find（"朋友",A1）.把它加到我们刚才写的函数替换掉数字7就得到了:

A5=FIND（"朋友",A1,FIND（"朋友",A1）+2）--结果是23.这样就得到我们所需要的答案.

例子4:

我们要知道第一个"朋友"和第二个"朋友"之间有多少个字符,如果字符数多于10同时在两个"朋友"之间有空格的话则显示"PayPal",否则显示"Taobao".（假设此句子内只有一个空格）

看起来很复杂,其实就是一些简单函数的组合。

1.我们要知道两个朋友之间有多少个字符?

那么就是例子3里面的,把第二个"朋友"的位置减去第一个"朋友"的位置,再减去2（因为"朋友"还有两个字符）;要判断是否超过10,那么用一个判断符号">"就可以了。

（注释:

大于就用">",小于就用"<",等于就用"=",不等于就用"<>"）

2.我们要知道两个朋友中间有没有空格,那么就查找空格在什么位置,这个位置是否大于第一个"朋友"的位置,同时小于第二个"朋友"的位置。

3.同时满足条件1和2,用And函数

4.如果xxx就yyy,否则zzz，就用If语句啊.

好了,那我们来一个一个写：

1.A4=（FIND（"朋友",A1,FIND（"朋友",A1）+2）-FIND（"朋友",A1）-2）>10--结果是TRUE

2.A5=AND（FIND（"",A1）FIND（"朋友",A1））--结果是TRUE

所以整个函数就是:

=if（and（1,2）,"PayPal","Taobao"）,把1,2套进去就是:

A6==IF（AND（（FIND（"朋友",A1,FIND（"朋友",A1）+2）-FIND（"朋友",A1）-2）>10,AND（FIND（"",A1）FIND（"朋友",A1）））,"PayPal","Taobao"）

这个例子比较复杂,但是大家一定要学会,因为经常分析的时候需要我们写多个函数的嵌套,否则你会浪费很多列的无用数据（数据复杂了容易把Data搞得很乱,最后自己都迷糊了）。

第五节LEFT/RIGHT函数

接下来我们要讲的函数是:

Left/Right,"Left"就是我们说的"左";"Right"就是我们说的"右",这两个函数是字符函数.所以大家很容易理解。

"Left"就是从一个字符串的左边截取一段字符串；"Right"就是从一个字符串的右边截取一段字符串（或一个字符）。

（这里说一下,Excel的函数中的参数绝大多数都可以是自己输入的内容,也可以是单元格的引用）。

我们先介绍"Left"（"Right"其实是一样的用法）:

=Left（要截取的字符串,需要截取多少个字符）

比如:

我们要在"Iwillkickyourassifyoukissmyass"中截取前面的15个字符.那么函数是（我们在A1单元格中输入这段字符串,在A3中输入函数）:

A3=LEFT（A1,15）--结果是"Iwillkickyou"（用肉眼算一下,对了,的确是15个字符）

使用Left函数要注意以下几点:

-Left函数的第二个参数必须是大于等于零的数字（>=0）（如果输入负数会出错）

-如果第二个参数大于第一个参数的字符数,否则显示第一个参数的整体

-在Left函数中,第二个参数其实是可以省略的,默认为1,也就是说=left（A1）的结果是字符"I"。

即截取1个字符。

但是为了方便记忆,大家还是记住需要这个参数吧,也不差这么几个字,而且养成好习惯.（在这个例子中,大家可以看到所有的字符都计算在内,空格也是按字符计算的）和Find函数一样,Left也有LeftB函数,对于双字节的字符是有用的,大家可以看Excel的帮助。

"Right"函数的用法是一样的,只是它是从右边开始截取.比如:

A5=RIGHT（A1,15）--结果是"youkissmyass"

要提醒大家的是,经常我们的原数据（不管是从SQL还是Oracle导出）在字符串后面都会带一些空格,从肉眼看看不到,但是用Right函数的时候就经常拿不到我们想要的东西,大家要注意单元格内的实际内容.（这里可以推荐大家用Trim函数,她可以去除前后的空格,不过对有些数据没有效果）

现在我们开始举例:

例子1:

我们需要截取A1单元格中的"if"这个字符串,怎么写.分析一下,"if"在左起第21位,然后往右边2位字符.但是我们还没有学习从中间截取的函数,怎么办呢?

这个时候,我们可以先从左边截取23（21+2）位（即"Iwillkickyourassif"）,然后在结果中再从右边截取2位,就可以得到我们要的结果了.所以函数就是:

A3=RIGHT（LEFT（A1,23）,2）结果如下

用这种方法,我们就可以截取到我们想要在字符串的中间截取的内容。

例子2:

我们要截取A1单元格中的"kick"字符串后面的8位字符。

继续审题:

要找到"kick"字符串后面的8