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

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

1、常用Excel函数及公式应用水电七局锦屏二工区 常用Excel函数及公式应用 内部小资料 目录 第一节 说明 . 1 第二节 IF函数 . 2 第三节 AND/OR/NOT函数 . 6 第四节 FIND函数 . 11 第五节 LEFT/RIGHT函数 . 14 第六节 函数中的优先级顺序 . 16 第七节 SUM/SUMIF/SUMIFS函数 . 17 第八节 COUNT/COUNTA/COUNTIF/COUNTIFS函数 . 25 第九节 Vlookup函数 . 27 第十节 小结 . 30 第一节 说明 这里不用重复Excel的基本操作, 只是把一些常用的基本信息罗列一下： 1、 Exce

2、l2003的文件扩展名是.xls; Excel2007的是.xlsx； 2、 Excel2003如果需要打开2007的文件, 必须安装微软的补丁(但是有些颜色/样式无法兼容); 2007是可以兼容2003的文件格式； 3、Excel2007在外观上和2003有非常大的区别, 熟悉2003的要适应2007需要一段过程, 如果您找不到菜单在哪里就请参阅微软提供的Flash ； 4、 Excel2003有65536行, Excel2007能容纳100W行, 能够满足我们大部分的要求, 但是建议您还是不要放太多数据, 否则效率非常低； 5、 Excel的函数都是由等号(=)开始后面跟函数的名字 ； 6

3、、 Excel的函数在键入的时候会自动显示参数类型/提示, 能够帮助我们了解函数的用法 ； 7、Excel输入身份证等长数字时会自动把后面几位变成0， 那是因为Excel不能显示太长的整数；所以您可以把单元格变成文本格式, 或着在身份证前加单引号 (e.g.: 250204200803241016) ； 8、Excel函数本身是不区分大小写的, 当然参数中如果有字符(串)是分的； 9、函数当中的参数如果是字符串的一定要加双引号(这个比较容易忘记) ； 10、要学习函数, 就是要把更多的重复或者机械的工作交给函数，希望以下内容对大家有帮助。 1 第二节 IF函数 第一个函数要讲的是函数If. 这

4、个函数很常用, 用它来判断一个表达式或者单元格的内容是否你所想要的结果, 如果是就执行某个动作, 如果不是就执行另外一个动作. 所以, If函数有三个参数: = if ( 参数1, 参数2, 参数3 ) 补充一下: Excel写函数的时候都是以 = 开头, 这个就是告诉Excel现在开始写函数咯! 然后函数当中的参数都是以逗号隔开! 参数1是一个逻辑表达式, 什么是逻辑表达式呢? 比如: Spider-Man是不是人? 这个就是一个逻辑表达式, 它的结果是Yes或者No. 在逻辑表达式中, Yes就是TRUE (真), No就是FALSE (假) (同时,数字0是FALSE, 其他数字是TRU

5、E)。 也就是说, 你的第一个参数的结果是TRUE或者非0的数字, 则If函数执行参数2, 否则执行参数3。 现在举几个例子来说明: 例子1: 如下图, A列是员工的名字, B列是员工的年龄. 某天, 你老板说:喂, 把那些超过30岁的都找出来。 如果真的只有下面4个员工当然好办, 眼睛一看就出来了。但是如果你在富士康几十万的员工名单你怎么找? 所以我们在C列加上30 Years Old。 然后开始判断。 我们要怎么判断呢? 很简单, 就是判断B列的每个单元格是否大约30就可以了(参数1), 然后如果大于30就显示大于30岁(参数2), 否则显示没到30, 幼齿哦(参数3)。 所以函数就是(在

6、C2单元格中): =if(B230,大于30岁,没到30, 幼齿哦) , 结果如下图: 参数1: B230 参数2: 大于30岁 参数3: 没到30, 幼齿哦 2 因为在C2单元格中, B2的值是35, 和30比较的时候它是大于30的, 所以表达式 B230 的结果是 TRUE. 即执行参数2. 因为在C3单元格中, B3的值是28, 和30比较的时候它是小于30的, 所以表达式 B330 的结果是 FALSE. 即执行参数3. 这样, 我们就很容易定位到我们需要的数据中。 例子2: 如下图, A列为名字, B列是一月份的Bonus, C列是二月份的Bonus. 请找出两个月Bonus总和超过

7、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的结果是32

8、00, 和2000比较的时候它是大于2000的, 所以表达式 (B3+C3)2000 的结果是 TRUE. 即执行参数2。 3 注解: 在Excel中, 加减乘除分别是: 加 (+); 减 (-); 乘 (*); 除 (/). 例子3: 上面两个例子比较简单, 都是直接利用IF函数来判断某一个表达式. 但是通常我们在工作中会遇到比较复杂的情况, 比如我们把例子2改一下: 要求找出在一月份Bonus超过1000同时在二月份超过1500的人. 这个时候如果我们只用IF函数来完成： =if(B21000,if(C21500,符合条件,不符合条件),不符合条件) 参数1: B21000 参数2: if

9、(C21500,符合条件,不符合条件) 参数2.1: C21500 参数2.2: 符合条件 参数2.3: 不符合条件 参数3: 不符合条件 这个函数是什么意思呢? 我们根据题目需要分两步走. 第一步, 判断B2是否大于1000, 如果不是大于1000, 就直接显示不符合条件 (因为一月份都不符合了, 就算二月份符合也没有用, 不是我们要找的); 对于大于1000的, 我们再做一次IF函数计算, 这个时候的前提已经是一月份大于1000了, 所以我们要做的判断是二月份是否大于1500(蓝色的判断), 如果大于1500则显示符合条件, 否则也显示不符合条件. (这个就是所谓的函数嵌套) 4 通过这种

10、方式, 我们就可以同时判断多个单元格(或者多个表达式)的情况. 总结: IF函数就是判断参数1是否为TRUE(或者FALSE), (表达式成立或者计算结果非0则为TRUE), 如果是TRUE则执行参数2, 如果FALSE则执行参数3. 希望这样能够帮助大家足够清楚的理解IF函数的用法。 5 第三节 AND/OR/NOT函数 现在我们来介绍三个逻辑函数: AND (与) ; OR (或) ; NOT (非)。 我们讲IF函数的时候, 讲到IF的第一个参数是逻辑值, 也就是TRUE或者FALSE. 今天我们学习的这三个函数就是判断逻辑值的函数, 得出的值还是逻辑值. 我们下面一个个来解释: 最简单

11、的一个 NOT (非) : 非 就是不是. 如果我们有一个逻辑 我吃过早饭了, 那么 NOT(我吃过早饭了) 的结果就是 我没有吃过早饭. (当然Excel没有办法这么智能判断中文句子, 这个只是例子, 大家不要Copy到Excel然后骂我骗人, 呵呵). 所以, NOT(TRUE) = FALSE ; NOT(FALSE) = TRUE (Clear?). NOT函数只有一个参数(参数可以是一个值也可以是一个表达式). 上面的例子中: A2=(32) (翻译成中文就是3比2大吗? , 所以结果是TRUE) A3=NOT(32) (加了一个NOT, 就把原来的结果倒了个, 结果是FALSE)

12、A5=10; A6=15 A8 =(A6-A70) (翻译成中文就是A6减去A7的结果大于0不? , (10-15)=-5当然比零小, 所以结果是FALSE) A9=NOT(A6-A70) (结果是TRUE) 2. 第二个 AND (与) : AND(与) 就是并且的意思. AND函数有两个(或以上)参数, AND的功能就是取这几个参数的交集. 我们要记住的是, 只要参数中有任何一个6 的值是FALSE, 那么AND函数的值就是FALSE; 当且仅当所有参数结果都是TRUE的时候, AND函数的值才是TRUE. 例子: 先将A1到A3单元格赋值: A1=ABC, A2=XYZ, A3=123

13、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函数中, 前两个参数的结果是T

14、RUE, 但是第三个参数的结果是FALSE, 所以整个函数的结果是FALSE. (这时候大家有可能会问: 我在单元格里面输入123, 它就自动是一百二十三啊, 我怎么输入才能是字符串123呢? 这个时候我们不能输入 123, 如果这样的话, Excel会当作前后两个双引号加上字符串123; 其实很简单, 我们在数字123前面加上一个单引号就可以了。 这样数字123就变成字符串123了(肉眼看上去是一样的). 这个还有什么用呢? 比如你输入身份证或者银行卡的时候, 数字太长Excel会自动截断后面的数字同时变成科学计数法, 这个时候我们只要在前面加一个单引号就搞定了(e.g. 350206200

15、803241016, 这样输入Excel就会把输入当成是字符串, 大家可以在Excel里面试一试就明白了). 第三个是 OR (或) : OR其实和AND的用法和参数都一样, 区别是AND只要有一个参数是FALSE则结果是FALSE; OR函数只要有一个参数是TRUE则结果就是TRUE. 例子: 我们继续用上面AND例子中的数字: 7 A1=ABC, A2=XYZ, A3=123, 那么: A5 = OR(A1=ABCD,A2=XYZW,A3=123) 结果是TRUE, 大家看到第一和第二个参数都是FALSE, 但是第三个参数是TRUE, 所以结果是TRUE; A6 = OR(A1=ABCD,

16、A2=XYZW,A3=1234) 结果是FALSE, 大家看到三个参数都是FALSE, 所以结果是FALSE (一个TRUE都没有); 关于AND / OR / NOT 的基础用法我们已经介绍了, 相信大家对这三个函数有了一定的认识. 但是我们在实际的应用中一般都不会这么简单, 通常都需要多种判断的结合。 例子1: 如下图所示数据, 列出了Team A 和Team B 中每个Agent某天的电话量和邮件处理量. 我们的问题是: 请找出电话和邮件量都多于20个的Agent。 分析题目 (就像我们在学校考试一样, 拿到题目第一就要审题): 要找出电话和邮件量都多于20的Agent, 也就是说 电话

17、量要大于20 并且 邮件量也要大于20: 电话量要大于20 用公式表达就是 (C220) 邮件量要大于20 用公式表达就是 (D220) 那么: 电话量要大于20 并且 邮件量也要大于20 就是: E2 = AND(C220,D220) (然后我们用之前说的把鼠标移到E2单元格的右下角变成粗体十字的时候双击. 这样我们就得到下面的结果: 8 这样看其实已经知道结果了。但是比较难看，那我们就用前面讲的IF函数来美化一下: 如果电话和邮件量都多于20个, 则显示Good, 其他显示Normal. 怎么写? E2 = IF(AND(C220,D220),Good, Normal) (再次强调, 字符

18、串要用引号, 这个大家容易忘记) 结果如下: 例子2：我们现在把题目再弄复杂一些, 我们要找出 电话和邮件量都多于20个或者邮件量超过30个, 同时名字不叫John的Agent. 继续审题: 要求1: 电话和邮件量都多于20个 这个我们前面已经做了: AND(C220, D220) 要求2: 邮件量超过30个 : (D230) 要求3: 名字不叫John : (B2John) 不等于在Excel里面是 或者还可以怎样写? 我们前面学了NOT, 所以也可以写成 (NOT(B2=John) 对于字符串的比较Excel是区分大小写的, 这个大家要注意. 接下来怎么做? 接下来就是Transforme

19、rs变形金刚开始合体了. 看看我们的题目: 有一个或者 和 一个同时. 我们把或者翻译成OR, 把同时翻译成AND. 那么函数就变成 =AND(OR(要求1, 要求2),要求3). 把上面的式子都套上去再用IF 美化一下就变成: 9 =IF(AND(OR(AND(C220, D220), (D230), NOT(B2=John),符合条件,不符合条件) 这样就得出了我们想要的结果 (如下图): 当我们在写嵌套函数的时候(就是一个函数套另外一个函数), 刚开始时一般都比较难适应, 这个时候我们的建议是先把函数分开在不同的单元格里面, 然后最后Copy到一起, 然后再慢慢学习两个函数的嵌套, 三个

20、函数的嵌套这样就会习惯了。 Excel会自动在嵌套函数中把相对应的括号标上不同的颜色(如下图), 这样你在检查你的公式的时候就比较容易定位。 再反黑看看: 10 第四节 FIND函数 接下来我们要介绍的函数是: Find, 相信了解的朋友都知道, Find 在英文中是找, 发现的意思. 因为Excel是老美做的, 所以在Excel里面, Find 函数就是一个查找函数。 Find 函数一共有3个参数, 如下: = Find (你要找的字符或者字符串, 在哪里找, 从的几位开始找) - 返回的值是你要找的字符或者字符串第一次出现的位数。 需要强调的是: Find函数是区分大小写的! 举个简单的例

21、子: 我要到日本找饭岛爱, 从东京开始找!。 那么饭岛爱就是参数1; 日本就是参数2; 东京就是参数3; 地下就是返回的结果。具体的说: 参数1是你要找的字符或者字符串, 比如 A, Good 或者某个单元格 如 A1, B3 等 。 参数2是你要在哪里找, 比如你要在 who are you 里面找 u, 那么参数2就是 who are you. 和参数1一样, 可以是你自己输入的内容, 也可以是某个单元格的引用. 参数3是你要从参数2的的几位开始找, 象上面说的, 你如果想从who are you的第一个空格开始找, 那么参数3就是4. 这里要注意的是, 参数3只是让我们从某个位置开始找,

22、 但是返回的值仍然是从第一位开始计算: 比如说: =find(u, who are you) 的结果是11; =find(u, who are you, 4) 的结果也是11。 (你是不是要说: 那么第三个参数到底有什么用? 返回的都是一样的? 其实是有用的, 因为Find找的是第一个符合参数1的字符(或者字符串)的位置, 如果你要找第二个, 第三个(几乎没有人找第三个)的时候就要用到这个功能了) 下面我们开始举例子: 例子1: 假设 A5 = 给国内的朋友汇钱就用Taobao, 给国外的朋友汇钱就用PayPal 这么一个字符串. 我们要找PayPal在第几个字符? 很简单: A4 = Fin

23、d (PayPal, A1) - 结果是29 ; 我们试一下找paypal: A5 = Find (paypal, A1) - 结果是 #VALUE! 出错了, 11 说明Find是区分大小写的！ 例子2: 可以看到在A1的字符串中, 逗号后面有一个空格, Find怎么找空格呢? 是用 还是 ? 我们来试验一下: A4 = FIND( ,A1) - 结果是18 A5 = FIND(,A1) - 结果是1 A6 = FIND(,A1,10) - 结果是10 所以我们的结论是: 1. 找空格的时候应该用的是 , 就是双引号中间加一个空格; 2. 双引号中间没有任何字符的时候其实是空字符, 所以查找

24、的结果是参数3或者当参数3没有的时候结果是1. (一般不会做这种事情, 所以大家知道一下就可以, 没有必要太在意) 例子3:我们要找A1单元格中的第二个朋友 在哪里。 审题：要找到第二个朋友, 肯定要用到第三个参数, 所以也不难, 从第一个朋友后面开始找就可以了: A4 = FIND(朋友,A1,7) 结果是23 。是很简单. 但是这样是用眼睛看的. 我们前面已经说了, 学习函数的时候就尽量不要用眼睛算, 不然函数就没有意义了。 所以, 上面的函数得到了正确的结果, 但是不是我们想要的. 我们再想一步, 我们知道要在第一个朋友后面找, 那第一个朋友的后面应该是几呢? (这个几是不是就是参数3?

25、). 答案是肯定的, 我们所需要的参数3就是找到第一个朋友然后加212 就可以了(因为朋友是2个字符). 找第一个朋友的函数是: =Find(朋友, A1). 把它加到我们刚才写的函数替换掉数字7就得到了: A5 = FIND(朋友,A1,FIND(朋友, A1)+2) - 结果是23. 这样就得到我们所需要的答案. 例子4:我们要知道第一个朋友和第二个朋友之间有多少个字符, 如果字符数多于10同时在两个朋友之间有空格的话则显示PayPal, 否则显示Taobao. (假设此句子内只有一个空格) 看起来很复杂, 其实就是一些简单函数的组合。1. 我们要知道两个朋友之间有多少个字符? 那么就是例

26、子3里面的, 把第二个朋友的位置减去第一个朋友的位置, 再减去2 (因为朋友还有两个字符); 要判断是否超过10, 那么用一个判断符号 就可以了。 (注释: 大于就用 , 小于就用 , 等于就用 =, 不等于就用 ) 2. 我们要知道两个朋友中间有没有空格, 那么就查找空格在什么位置, 这个位置是否大于第一个朋友的位置, 同时小于第二个朋友的位置。 3. 同时满足条件1和2, 用And函数 4. 如果xxx就yyy, 否则zzz ，就用If语句啊. 好了, 那我们来一个一个写： 1. A4 = (FIND(朋友,A1,FIND(朋友,A1)+2)-FIND(朋友,A1)-2)10 - 结果是T

27、RUE 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) 13 这个例子比较复杂, 但是大家一定要学会, 因为经常分析的时候需要我们写多个函数的嵌套, 否则你会浪费很多列的无用数据(数据复杂了容易把Data搞得很乱, 最后自己都迷糊了)。 第五节 LEFT/

28、RIGHT函数 接下来我们要讲的函数是: Left / Right, Left 就是我们说的左; Right 就是我们说的右, 这两个函数是字符函数. 所以大家很容易理解。 Left 就是从一个字符串的左边截取一段字符串； Right就是从一个字符串的右边截取一段字符串(或一个字符)。(这里说一下, Excel的函数中的参数绝大多数都可以是自己输入的内容, 也可以是单元格的引用) 。 我们先介绍Left (Right其实是一样的用法): = Left (要截取的字符串, 需要截取多少个字符) 比如: 我们要在 I will kick your ass if you kiss my ass 中截

29、取前面的15个字符. 那么函数是 (我们在A1单元格中输入这段字符串, 在A3中输入函数): A3 =LEFT(A1,15) - 结果是I will kick you (用肉眼算一下, 对了, 的确是15个字符) 使用Left函数要注意以下几点: - Left函数的第二个参数必须是大于等于零的数字(=0) (如果输入负数会出错) - 如果第二个参数大于第一个参数的字符数, 否则显示第一个参数的整体 - 在Left函数中, 第二个参数其实是可以省略的, 默认为1, 也就是说 = 14 left(A1) 的结果是字符I。 即截取1个字符。 但是为了方便记忆, 大家还是记住需要这个参数吧, 也不差这

30、么几个字, 而且养成好习惯. (在这个例子中, 大家可以看到所有的字符都计算在内, 空格也是按字符计算的) 和Find函数一样, Left也有LeftB函数, 对于双字节的字符是有用的, 大家可以看Excel的帮助。 Right函数的用法是一样的, 只是它是从右边开始截取. 比如: A5 = RIGHT(A1,15) - 结果是you kiss my ass 要提醒大家的是,经常我们的原数据(不管是从SQL还是Oracle导出)在字符串后面都会带一些空格, 从肉眼看看不到, 但是用Right函数的时候就经常拿不到我们想要的东西, 大家要注意单元格内的实际内容. (这里可以推荐大家用Trim函数

31、, 她可以去除前后的空格, 不过对有些数据没有效果) 现在我们开始举例: 例子1: 我们需要截取A1单元格中的if这个字符串, 怎么写. 分析一下, if在左起第21位, 然后往右边2位字符. 但是我们还没有学习从中间截取的函数, 怎么办呢? 这个时候, 我们可以先从左边截取23 (21+2) 位 (即I will kick your ass if), 然后在结果中再从右边截取2位, 就可以得到我们要的结果了. 所以函数就是: A3 =RIGHT(LEFT(A1,23),2) 结果如下 用这种方法, 我们就可以截取到我们想要在字符串的中间截取的内容。 例子2: 我们要截取A1单元格中的kick

32、字符串后面的8位字符。 继续审题: 要找到kick字符串后面的8位字符, 就要先把kick后面的所有字符提取出来, 然后再用Left函数从左边取8位. 15 需要把kick后面的所有字符提取出来, 就要先用Right函数截取, 那么第二个参数是多少呢? 需要知道: 第二个参数是多少, 就需要找到kick在的几位(还记得前面的FIND函数吧), 然后用A1字符串的总长度减去kick的位置再减3就得到了。 (这里我们当我们已经知道总长度是39) 知道了整个的逻辑, 我们就一步步从下往上写: 需要找到kick在的几位: A3 = FIND(kick,A1) 那么kick后面的字符串就是: A4 =

33、RIGHT(A1,39-FIND(kick,A1)-3) 最后找这个字符串的左边8位: A5 = LEFT(RIGHT(A1,39-FIND(kick,A1)-3),8) 结果如下: 第六节 函数中的优先级顺序 当我们运用多个函数计算的时候, 往往会遇到比较复杂的情况, 在一长串的表达式中, 我们不单单要注意函数的规则, 还要考虑函数中运算符的优先级别, 否则结果可能大相径庭。 1. 函数里面的括号的作用 每个函数都对应一个括号也仅有一个括号, 如果有多个括号一定是我们自己输入参数时的需要产生的. 比如: = If (参数1, 参数2, 参数3) 函数中的每个参数是用逗号 (,) 隔开的, 也

34、就是说参数和参数之间有明显的区分, 不必对每个参数再加括号, 比如下面几个表达式是一样的: = If (参数1, 参数2, 参数3) = If (参数1), (参数2), (参数3) 16 = If (参数1), (参数2), (参数3) 函数不会出错, 但是会自动把你加的多余的括号略去. (具体能加多少个多于的括号我也不知道, 大家只要记住对于每个参数的最外围是没有必要加括号的。 但是我们什么时候要在参数内加括号呢? 这个就涉及到运算符的优先级以及个人的爱好了。 2. 运算符的优先级 关于这个话题, 如果您要详细了解, 请查看这里微软的权威解释. 懒得看就看下面我们摘录的简化版本: 1 先乘

35、除后加减； 2 加减乘除的优先级比比较运算符(大于,小于, 等于.)高 3 相同级别的从左到右计算 3. 个人爱好 比如: =1+2&3+4, 如果我真的写这样的公式, 我一定是写成: =(1+2)&(3+4), 这样不管是自己还是用户都比较容易理解, 虽然结果一样。 第七节 SUM/SUMIF/SUMIFS函数 在数据处理中, 我们经常要对数据进行计算, 现在我们就要讲一些加总函数: SUM/SUMIF/SUMIFS. 其实, 我们在Excel中, 如果选中一系列的数字, Excel会在界面的右下角帮我们自动计算这些数字的总和(当然如果你右击那个Sum=15, 你还有更多的选项, 比如计算数

36、量, 计算平均数等) 下面我们先来介绍函数Sum的用法: 17 Sum函数的参数为1-30个. 这1-30个参数可以是一个数字, 也可以是一个数组 (估计Excel7已经升级为127个, 不过这个对我们没有什么大用处, 奉劝您不要用这么多个参数, 否则出错几率成倍增加) 例子1: 下面A2到A10分别是一些数字/字符型数字/逻辑值/NA/字符. 我们先来计算A2/3/4三个单元格的总和, 下面两个表达都是正确的, 当然如果是连续的单元格当然用数组会方便得多. =SUM(A2,A3,A4) =SUM(A2:A4) 18 注: 什么是数组? (我们暂且用数组来代表一连串单元格的组合 ) 其实在Ex

37、cel中数组的应用很多, 这里我们先了解最简单的, 数组就是一系列的连续单元格, 比如A1到A10, A1到Z1, A1到Z10. 数组的写法是在头尾两个单元格中间加一个冒号. 也就是: A1到A10: A1/A2/A3 . A10 写成: A1:A10 A1到Z1: A1/B1/C1 . Z1 写成: A1:Z1 A1到Z10: A1/A2.A10/B1/B2/.B10 .Z1/Z2/.Z10 一共260个单元格 写成: A1:Z10 如果我们把公式写成下面这样会得出什么结论呢? A16 = SUM(A2:A8) 结果还是6, 说明Sum函数把A4-A8的字符数字和逻辑值都忽略了(什么是字符

38、数字? 就是我们前面说的, 在数字前面加一个单引号, 使得数字变成字符) A17 = SUM(A2:A10) 因为A9是NA, 所以就出错了. 19 所以我们的结论是: 当Sum函数的参数是单元格 的时候会自动屏蔽非数字的单元格。 为什么我们要说当引用单元格的时候呢? 因为当我们把字符数字或者逻辑值写入Sum函数中的某个参数(而不是单元格引用)的时候, Sum函数会把相应的内容转换成数组然后一起加起来. 比如: A20 =SUM(5,5,TRUE) 第一个参数是字符5 (5),Sum函数把其转换成数字5; 第二个参数就是数字5, 没有问题; 第三个参数是逻辑值TRUE, 我们前面说了, TRU

39、E是1, FALSE是0, 所以整个函数其实就是 5+5+1, 所以结果在A20中显示11. 这个看上去好像没有什么用,其实只能说很少用, 但是既然它存在, 必定是有点用处的, 我们来举个例子: 例子2: 下面是某公司员工列表, A列为员工名字, B列为员工在公司的工作年限, C列为该员工的年龄。 公司规定: 员工入职时的年假天数为10天, 每服务一年多加一天, 同时为了照顾年龄大的员工, 公司还规定, 40岁或者以上的员工可以比其他员工多享受1天年假。 请在D列中注明每个员工应该享受的假期。 20 好了, 其实这个题目很简单, 就是一个加法: 首先: 初始的年假天数为10天, 所以Sum的第

40、一个参数我们可以用10 另外: 每服务一年多加一天, 所以第二个参数我们就用B列的值减1就可以 最后: 年龄大于等于40的可以多一天, 这时候, 我们可以用IF函数来判断C列的值, 如果是大于等于40就多加1, 如果小于40就不加。 所以整个函数就是: D2 = SUM(10,B2-1,IF(C2=40,1,0) 结果如下: 好, 这样我们得出了我们要的结果, 但是能不能再简单一点呢? 刚才我们说了, 在Sum函数中的参数可以是逻辑值, TRUE代表1, 所以我们的函数也可以写成这样. (为了比较, 我把源数据拷贝了一份) E2 = SUM(10,B2-1,C2=40) 大家可以看到结果是一样

41、的. 原因很简单, C33=40这个比较函数直接给出了我们要的结果, 如果大于等于40, 结果就是TRUE(也就是1), 如果小于则是FALSE(0). 这样看起来是不是更简单呢? 希望大家都明白了Sum函数的用法, 下面我们开始讲Sumif。 Sumif函数比Sum函数多一个IF. 所以可以联想一下, 其实它就是在Sum的基础上再加上一个判断功能。 21 = Sumif (参数1, 参数2, 参数3) 参数1是用来判断的数组 参数2是判断的条件 参数3是执行求和(sum)的数组, 如果忽略则参数3等于参数1； 同时参数3和参数1是一一对应的, 也就是说如果参数1是从上往下的系列, 则参数3也

42、是从上往下的. 如果参数1是从左往右的系列, 则参数3也是从左往右的系列。如果参数3比参数1包含更多的单元格, 则多于的单元格将被忽略. 如果参数3比参数1包含更少的单元格, 则函数会自动往下/右延伸至单元格数和参数1相同为止。 (讲的有点复杂, 看下例子3就明白了 ) 例子3: 如下图, A1:B4 和 D1:G2 是一些数字系列。 A7计算的是A1:A4中, 判断这4个单元格的内容是否大于等于3, 如果是的话就将参数3求和,因为参数3为空, 所以默认为参数3就是参数1, 也就是3+4=7. A8和A7的区别是我把参数1当作参数3写进去了, 大家可以看到结果和A56是一样的. 也就说明了当参

43、数3缺省时函数自动将参数1默认为参数3. A9中, 我们把参数3设置成B50:B53, 和参数1有相同的单元格数(都是4个单元格). 所以函数的执行过程时: - 判断A1是否大于等于3, 结果时否, 所以忽略B1 - 判断A2是否大于等于3, 结果时否, 所以忽略B2 - 判断A3是否大于等于3, 结果时是, 所以结果加上B3 - 判断A4是否大于等于3, 结果时是, 所以结果加上B4 - 所以最后的记过是B3+B4=7+8=15 A10中,我们把参数3换了一下, 把B1:B4换成B1:B3, 这样大家可以发现, 其实参数3比参数1少了一个单元格, 这个时候函数会自动向下延伸, 把B4也包含进来. 所以结果和A9的一样。 同样的逻辑我们也可以解释A12-A15之间的结果, 区别就是一个是计算从上到下的系列; 一个是计算从左到右的系列. 22 介绍了Su