水电七局锦屏二工区
常用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、 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列加上">30 Years Old"。然后开始判断。
我们要怎么判断呢? 很简单, 就是判断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. –‘350206************, 这样输入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: 如下图所示数据, 列出了Team A 和Team B 中每个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是你要在哪里找, 比如你要在 "who are you" 里面找 "u", 那么参数2就是 "who are you". 和参数1一样, 可以是你自己输入的内容, 也可以是某个单元格的引用.
参数3是你要从参数2的的几位开始找, 象上面说的, 你如果想从"who are you"的第一个空格开始找, 那么参数3就是4. 这里要注意的是, 参数3只是让我们从某个位置开始找, 但是返回的值仍然是从第一位开始计算: 比如说: =find("u", "who are you") 的结果是11; =find("u", "who are you", 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)
所以整个函数就是: = if (and(1,2),"PayPal","Taobao"), 把1,2套进去就是: A6 = =IF(AND((FIND("朋友",A1,FIND("朋友",A1)+2)-FIND("朋友",A1)-2)>10,AND(FIND(" ",A1)
这个例子比较复杂, 但是大家一定要学会, 因为经常分析的时候需要我们写多个函数的嵌套, 否则你会浪费很多列的无用数据(数据复杂了容易把Data搞得很乱, 最后自己都迷糊了)。
第五节 LEFT/RIGHT函数
接下来我们要讲的函数是: Left / Right, "Left" 就是我们说的"左"; "Right" 就是我们说的"右", 这两个函数是字符函数. 所以大家很容易理解。
"Left" 就是从一个字符串的左边截取一段字符串； "Right"就是从一个字符串的右边截取一段字符串(或一个字符)。(这里说一下, Excel的函数中的参数绝大多数都可以是自己输入的内容, 也可以是单元格的引用) 。
我们先介绍"Left" ("Right"其实是一样的用法):
= Left (要截取的字符串, 需要截取多少个字符)
比如: 我们要在 "I will kick your ass if you kiss my ass" 中截取前面的15个字符. 那么函数是 (我们在A1单元格中输入这段字符串, 在A3中输入函数): A3 =LEFT(A1,15) -- 结果是"I will kick you" (用肉眼算一下, 对了, 的确是15个字符)
使用Left函数要注意以下几点:
- Left函数的第二个参数必须是大于等于零的数字(>=0) (如果输入负数会出错) - 如果第二个参数大于第一个参数的字符数, 否则显示第一个参数的整体
- 在Left函数中, 第二个参数其实是可以省略的, 默认为1, 也就是说 =
left(A1) 的结果是字符"I"。即截取1个字符。但是为了方便记忆, 大家还是记住需要这个参数吧, 也不差这么几个字, 而且养成好习惯. (在这个例子中, 大家可以看到所有的字符都计算在内, 空格也是按字符计算的) 和Find函数一样, Left也有LeftB函数, 对于双字节的字符是有用的, 大家可以看Excel的帮助。
"Right"函数的用法是一样的, 只是它是从右边开始截取. 比如:
A5 = RIGHT(A1,15) -- 结果是"you kiss my ass"
要提醒大家的是,经常我们的原数据(不管是从SQL还是Oracle导出)在字符串后面都会带一些空格, 从肉眼看看不到, 但是用Right函数的时候就经常拿不到我们想要的东西, 大家要注意单元格内的实际内容. (这里可以推荐大家用Trim函数, 她可以去除前后的空格, 不过对有些数据没有效果)
现在我们开始举例:
例子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"字符串后面的8位字符。
继续审题: 要找到"kick"字符串后面的8位字符, 就要先把"kick"后面的所有字符提取出来, 然后再用"Left"函数从左边取8位.
需要把"kick"后面的所有字符提取出来, 就要先用Right函数截取, 那么第二个参数是多少呢?
需要知道: 第二个参数是多少, 就需要找到"kick"在的几位(还记得前面的FIND 函数吧), 然后用A1字符串的总长度减去"kick"的位置再减3就得到了。 (这里我们当我们已经知道总长度是39)
知道了整个的逻辑, 我们就一步步从下往上写:
需要找到"kick"在的几位: A3 = FIND("kick",A1)
那么"kick"后面的字符串就是: A4 = RIGHT(A1,39-FIND("kick",A1)-3)
最后找这个字符串的左边8位: A5 = LEFT(RIGHT(A1,39-FIND("kick",A1)-3),8) 结果如下:
第六节函数中的优先级顺序
当我们运用多个函数计算的时候, 往往会遇到比较复杂的情况, 在一长串的表达式中, 我们不单单要注意函数的规则, 还要考虑函数中运算符的优先级别, 否则结果可能大相径庭。
1. 函数里面的括号的作用
每个函数都对应一个括号也仅有一个括号, 如果有多个括号一定是我们自己输入参数时的需要产生的. 比如:
= If (参数1, 参数2, 参数3)
函数中的每个参数是用逗号 (,) 隔开的, 也就是说参数和参数之间有明显的区分, 不必对每个参数再加括号, 比如下面几个表达式是一样的:
= If (参数1, 参数2, 参数3)
= If ((参数1), (参数2), (参数3))
= If (((参数1)), ((参数2)), ((参数3)))
函数不会出错, 但是会自动把你加的多余的括号略去. (具体能加多少个多于的括号我也不知道, 大家只要记住对于每个参数的最外围是没有必要加括号的。
但是我们什么时候要在参数内加括号呢? 这个就涉及到运算符的优先级以及个人的爱好了。
2. 运算符的优先级
关于这个话题, 如果您要详细了解, 请查看这里微软的权威解释. 懒得看就看下面我们摘录的简化版本:
○1先乘除后加减；
○2加减乘除的优先级比比较运算符(大于,小于, 等于....)高
○3相同级别的从左到右计算
3. 个人爱好
比如: =1+2&3+4, 如果我真的写这样的公式, 我一定是写成: =(1+2)&(3+4), 这样不管是自己还是用户都比较容易理解, 虽然结果一样。
第七节 SUM/SUMIF/SUMIFS函数
在数据处理中, 我们经常要对数据进行计算, 现在我们就要讲一些加总函数: SUM/SUMIF/SUMIFS.
其实, 我们在Excel中, 如果选中一系列的数字, Excel会在界面的右下角帮我们自动计算这些数字的总和(当然如果你右击那个Sum=15, 你还有更多的选项, 比如计算数量, 计算平均数等)
下面我们先来介绍函数"Sum"的用法:
Sum函数的参数为1-30个. 这1-30个参数可以是一个数字, 也可以是一个数组(估计Excel7已经升级为127个, 不过这个对我们没有什么大用处, 奉劝您不要用这么多个参数, 否则出错几率成倍增加)
例子1:下面A2到A10分别是一些数字/字符型数字/逻辑值/NA/字符.
我们先来计算A2/3/4三个单元格的总和, 下面两个表达都是正确的, 当然如果是连续的单元格当然用数组会方便得多.
=SUM(A2,A3,A4)
=SUM(A2:A4)
excel常用函数公式介绍 excel常用函数公式介绍1：MODE函数应用 1MODE函数是比较简单也是使用最为普遍的函数，它是众数值，可以求出在异地区域或者范围内出现频率最多的某个数值。 2例如求整个班级的普遍身高，这时候我们就可以运用到了MODE 函数了 3先打开插入函数的选项，之后可以直接搜索MODE函数，找到求众数的函数公式 4之后打开MODE函数后就会出现一个函数的窗口了，我们将所要求的范围输入进Number1选项里面，或者是直接圈选区域 5之后只要按确定就可以得出普遍身高这一个众数值了 excel常用函数公式介绍2：IF函数应用 1IF函数常用于对一些数据的进行划分比较，例如对一个班级身高进行评测 2这里假设我们要对身高的标准要求是在170，对于170以及170之上的在备注标明为合格，其他的一律为不合格。这时候我们就要用到IF函数这样可以快捷标注好备注内容。先将光标点击在第一个备注栏下方 3之后还是一样打开函数参数，在里面直接搜索IF函数后打开 4打开IF函数后，我们先将条件填写在第一个填写栏中， D3>=170，之后在下面的当条件满足时为合格，不满足是则为不合格 5接着点击确定就可以得到备注了，这里因为身高不到170，所以备注里就是不合格的选项 6接着我们只要将第一栏的函数直接复制到以下所以的选项栏中就可以了
excel常用函数公式介绍3：RANK函数应用 2这里我们就用RANK函数来排列以下一个班级的身高状况 3老规矩先是要将光标放于排名栏下面第一个选项中，之后我们打开函数参数 4找到RANK函数后，我们因为选项的数字在D3单元格所以我们就填写D3就可了，之后在范围栏中选定好，这里要注意的是必须加上$不然之后复制函数后结果会出错 5之后直接点击确定就可以了，这时候就会生成排名了。之后我们还是一样直接复制函数黏贴到下方选项栏就可以了。
电子表格常用函数公式 1、自动排序函数： =RANK(第1数坐标，$第1数纵坐标$横坐标：$最后数纵坐标$横坐标，升降序号1降0升) 例如：=RANK(X3,$X$3:$X$155,0) 说明：从X3 到X 155自动排序 2、多位数中间取部分连续数值： =MID（该多位数所在位置坐标，所取多位数的第一个数字的排列位数，所取数值的总个数） 例如：612730************在B4坐标位置，取中间出生年月日，共8位数 =MID(B4，7，8) =19820711 说明：B4指该数据的位置坐标，7指从第7位开始取值，8指一共取8个数字 3、若在所取的数值中间添加其他字样， 例如：612730************在B4坐标位置，取中间出生年、月、日，要求****年**月**日格式 =MID(B4，7，4)&〝年〞&MID(B4，11，2) &〝月〞& MID(B4，13，2) &〝月〞&
=1982年07月11日 说明：B4指该数据的位置坐标，7、11指开始取值的第一位数排序号，4、2指所取数值个数，引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板：首先利用Word制作一个奖状模板并保存为“奖状.doc”，将其中班级、姓名、获奖类别先空出，确保打印输出后的格式与奖状纸相符（如图1所示）。 第二步用Excel建立获奖数据库：在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”，格式如图2所示。 第三步关联数据库与奖状：打开“奖状.doc”，依次选择视图→工具栏→邮件合并，在新出现的工具栏中选择“打开数据源”，并选择“奖状数据.xls”，打开后选择相应的工作簿，默认为sheet1，并按确定。将鼠标定位到需要插入班级的地方，单击“插入域”，在弹出的对话框中选择“班级”，并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印：选择“查看合并数据”，然后用前后箭头就可以浏览合并数据后的效果，选择“合并到新文档”可以生成一个包含所有奖状的Word文档，这时就可以批量打印了。
常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。
2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。
2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。 四、求和公式
1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法
3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。
4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符* 5、多表相同位置求和 公式：b2 =SUM(Sheet1:Sheet19!B2) 说明：在表中间删除或添加表后，公式结果会自动更新。 6、按日期和产品求和
15个常用的Excel函数公式，拿来即用 1、查找重复内容 =IF(COUNTIF(A:A,A2)>1,"重复","") 2、重复内容首次出现时不提示 =IF(COUNTIF(A$2:A2,A2)>1,"重复","") 3、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","")
4、根据出生年月计算年龄 =DATEDIF(A2,TODAY(),"y") 5、根据身份证号码提取出生年月 =--TEXT(MID(A2,7,8),"0-00-00") 6、根据身份证号码提取性别 =IF(MOD(MID(A2,15,3),2),"男","女") 7、几个常用的汇总公式 A列求和：=SUM(A:A)
A列最小值：=MIN(A:A) A列最大值：=MAX (A:A) A列平均值：=AVERAGE(A:A) A列数值个数：=COUNT(A:A) 8、成绩排名 =RANK.EQ(A2,A$2:A$7) 9、中国式排名（相同成绩不占用名次） =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 10、90分以上的人数
=COUNTIF(B1:B7,">90") 11、各分数段的人数 同时选中E2:E5，输入以下公式，按Shift+Ctrl+Enter =FREQUENCY(B2:B7,{70;80;90}) 12、按条件统计平均值 =AVERAGEIF(B2:B7,"男",C2:C7) 13、多条件统计平均值 =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")
Excel常用函数公式大全 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；
《Excel中公式与函数的使用》教案 保山中医药高等专科学校孔春丽 教学内容： 1、单元格引用 2、输入和编辑公式 3、插入和使用常用函数 教学目标: １、知识目标: 理解excel中公式与函数、相对地址与绝对地址的概念。 ２、技能目标: 掌握公式、常用函数以及自动求和按钮的使用，并能运用其解决一些实际问题，提高应用能力。 ３、情感目标: 让学生亲身体验excel强大的运算功能，通过系统学习，培养学生科学、严谨的求学态度，和不断探究新知识的欲望。 教学重点与难点： 根据大纲，依据本节课的教学目标，确定本节课的教学重点为： 1、公式的使用 2、excel中相对地址与绝对地址的引用 3、常用函数的使用 教学方法： 1、任务驱动法、设问法 2、自学法、图示说明法、探究法、演示讲解法 3、归纳总结法 教学过程： 一、创设意境，导入新课（2分钟）（讲解法） 导入：excel的主要功能，制作表格，运算和分析数据，可以为决策提供理论依据，前面我们已经讲解了单元格的格式、单元格地址等概念，这一节将一起来完成公式和函数的应用。 新课程：Excel中公式和函数的使用 二、新课教学（总计38分钟）（讲解法、提问法、示范法） 1、单元格的引用（12分钟） 在公式中使用单元格引用的作用是引用一个单元格或一组单元格的内容，这样可以使用工作表不同部分的数据进行所期望的计算。在Excel 2003中，可以使用相对引用，绝对引用及混合引用来表示单元格的位置。所以，在创建的公式中必须正确使用单元格的引用的类型。 （1）单元格的相对引用（5分钟） 下面以计算2门学科的总成绩为例。如下图所示，将D2单元格成为活动单元格，在编辑栏中输入“＝A2+B2+C2”，回车后D2单元格将得到图示的结果。
EXCEL常用函数大全（做表不求人！） 2013-12-03 00:00 我们在使用Excel制作表格整理数据的时候，常常要用到它的函数功能来自动统计处理表格中的数据。这里整理了Excel中使用频率最高的函数的功能、使用方法，以及这些函数在实际应用中的实例剖析，并配有详细的介绍。 1、ABS函数 函数名称：ABS 主要功能：求出相应数字的绝对值。 使用格式：ABS(number) 参数说明：number代表需要求绝对值的数值或引用的单元格。 应用举例：如果在B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。 特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！”。
2、AND函数 函数名称：AND 主要功能：返回逻辑值：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 使用格式：AND(logical1,logical2, ...) 参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多这30个。 应用举例：在C5单元格输入公式：=AND(A5>=60,B5>=60)，确认。如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。 国美提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。
EXCEL常用函数公式大全及举例 一、相关概念 （一）函数语法 由函数名+括号+参数组成 例：求和函数：SUM（A1,B2,…) 。参数与参数之间用逗号“,”隔开（二）运算符 1. 公式运算符：加（+）、减（-）、乘（*）、除（/）、百分号（%）、乘幂（＾） 2. 比较运算符：大与（>）、小于（<）、等于（=）、小于等于（<=）、大于等于（>=）、不等于（<>） 3. 引用运算符：区域运算符（：）、联合运算符（,） （三）单元格的相对引用与绝对引用 例： A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 （一）数学函数 1. 求和 =SUM（数值1,数值2,……） 2. 条件求和 =SUMIF（查找的范围,条件(即对象),要求和的范围） 例：（1）=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思：对第A1栏至A4栏中，大于等于200的数值对应的第B1列至B4列中数值求和 （2）=SUMIF(A1:A4,”<300”,C1:C4)
函数意思：对第A1栏至A4栏中，小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数 =COUNT（数值1,数值2,……） 例：（1） =COUNT(A1:A4) 函数意思：第A1栏至A4栏求个数（2） =COUNT(A1:C4) 函数意思：第A1栏至C4栏求个数 4. 条件求个数 =COUNTIF（范围,条件） 例：（1） =COUNTIF(A1:A4，”<>200”) 函数意思：第A1栏至A4栏中不等于200的栏求个数 （2）=COUNTIF(A1:C4,”>=1000”) 函数意思：第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数 =AVERAGE（数值1,数值2,……） 例：（1） =AVERAGE(A1,B2) （2） =AVERAGE(A1:A4) 6. 四舍五入函数 =ROUND(数值,保留的小数位数) 7. 排位函数 =RANK（数值,范围,序别） 1-升序 0-降序 例：（1） =RANK(A1,A1:A4,1) 函数意思：第A1栏在A1栏至A4栏中按升序排序，返回排名值。 （2） =RANK(A1,A1:A4,0) 函数意思：第A1栏在A1栏至A4栏中按降序排序，返回排名值。 8. 乘积函数 =PRODUCT（数值1,数值2,……） 9. 取绝对值 =ABS(数字) 10. 取整 =INT(数字) （二）逻辑函数
EXCEL2003公式·函数应用大全 1、SUMPRODUCT函数：该函数的功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。例如：如图1，如果想计算B 3：C6和C3：E6这两组区域的值，可以用以下公式：“=Sumproduct(B3:C6,D3:E6)”。 图1 2、ABS函数：如果在A1、B1单元格中分别输入120、90，那么如果要求A1与B1之 间的差的绝对值，可以在C1单元格中输入以下公式：“=ABS(A1-B1)”。 3、IF函数：如图2，如果C3单元格的数据大于D3单元格，则在E3单元格显示“完成任务，超出：”，否则显示“未完成任务，差额：”，可以在E3单元格中输入以下公式：“=IF(C3>D3, “完成任务，超出：”,”未完成任务，差额：””。 图2 4、Ceiling函数：该数值向上舍入基础的倍数。如图3，在C3单元格中输入以下公 式：“=CEILING(B3,C3)”；而“=FLOOR(B3,C3)”则是向下舍入。 图3 5、GCD函数：该函数计算最大公约数。如图4，如果要计算B3：D3这一区域中3个数字的最大公约数，可以在E3单元格中输入以下公式：“=GCD(B3,C3,D3)”。 图4 6、INT函数：该函数是向下舍入取整函数。如图5，如果要计算显示器和机箱的购 买数量，可以在E3单元格中输入以下公式：“=INT(D3/C3)”。 图5 7、LCM函数：该函数是计算最小公倍数。如图6，如果要计算B3：D3这一区域中3个数字的最小公倍数，可以在E3单元格中输入以下公式：“=LCM(B3,C3,D3)”。 图6 8、LN函数：该函数是计算自然对数，公式为：“=LN(B3)”。 9、LOG函数：该函数是计算指定底数的对数，公式为：“=LOG10(B3)”。 10、MOD函数：该函数是计算两数相除的余数。如图7，判断C3能否被B3整除，可 以在D4单元格中输入以下公式：“=IF(MOD(B3,C3)=0,"是","否")”。 图7 11、PI函数：使用此函数可以返回数字3.979，即数学常量PI，可精确到小数点后 14位。如图8，计算球体的面积，可以在C4单元格中输入以下公式： “=PI()*(B3^2)*4)”；计算球体的体积，可以在D4单元格中输入以下公式：“= (B3^3)*（4* PI())）/3”。 图8 12、POWER函数：此函数用来计算乘幂。如图9，首先在单元中输入底数和指数，然 后在D3中输入以下公式：“=POWER(B3,C3)”。 图9 13、PRODUCT函数：此函数可以对所有的以参数形式给出的数字相乘，并返回乘积。例如：某企业2005年度贷款金额为100000元，利率为1.5%，贷款期限为12个月。如图10所示，直接在单元格E4中输入以下公式：“ =PRODUCT(B4,C4,D4)”。 图10 14、RADIANS函数：此函数是用来将弧度转换为角度的。可以在C3单元格中输入以 下公式：“=RADIANS (B3)”。 15、RAND函数：此函数可以返回大于等于0及小于1的均匀分布随机数，每次计算工作表时都将返回一个新的数值。如果要使用函数RAND生成一个随机数，并且使之不随单元格的计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按[F9]键，将公式永久性地改为随机数。例如：在全班50名同学中以随机方式抽出20名进行调查，如图11，在单元格中输入开始以及结束，然后在单元格B4中输入 以下公式：“=1+RAND()*49”。 图11 16、ROUND函数：此函数为四舍五入函数。如图12，例如：将数字“12.3456”按照指定的位数进行四舍五入，可以在D3单元格中输入以下公式：“=ROUND(B3,C3)”。 17、ROUNDDOWN函数：此函数为向下舍入函数。例如：出租车的计费标准是：起步价为5元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。输入不同的公里数，如图13所示，然后计算其费用。可以在C3单 元格中输入以下公式： “=IF(B3<=10,5+ROUNDDOWN(B3,0)*2,20+ROUNDDOWN((B3-10)*2,0)*2)”。 图13 18、ROUNDUP函数：此函数为向上舍入函数。例如：现在网吧的管理一般是采用向上舍入法，不满一个单元按照一个单位计算。现假设每30分钟计价0.5元，请计算如图14中所示的上网所花费的费用。1）计算上网天数：首先在单元格C3中输入以下公式：“=B3-A3”；2）计算上网分钟数：上网分钟数实际上就等于上网天数乘以60再乘以24，所以应在单元格D3中输入以下公式：“=C3*60*24”；3）计算计费时间：本例中规定每30分钟计费一次，不满30分钟以30分钟计价，所以应在单元格E3中输入以下公式：“=ROUNDUP(D3/30,0)”；4）计算上网费用：在单元格 G3中输入以下公式：“=E3*F3”。
1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式： =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分；
7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格； （5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格； （6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格； （7）=COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；
excel公式笔记 一、vlookup 1.查找A列中第一个以”厦门”开头的记录对应B列的值。 =vlookup(H厦门性A:B20) 其中第一个参数为要寻找的文本，第二个参数为一个区域，第二个参数的第一列为要寻找的区域,第三个参数的2表示第二个参数的第二列显示出来,第四个参数的0表示精确查找。 二、countif 1.统计Al:A10区域中型号为" 2.5m*3m"的记录个数。 =countif(Al:A10,,,2.5m~*3m H) 在excel常用函数中,支持通配符的主要有vlookup、hlookup> match> sum讦、courttif、search> searchB,而find、findB> subsitute 不支持通配符。*表示任意字符,？表示单个字符解除字符的通配性。 2.统计Al:A10中不重复数的个数。 =SUMPRODUCT((1/COUNTIF(A1:A10,A1:A10)))
3?求小于60的数据有多少 二count(A2:AKVv6(T) sumproduct 1?求购物总花费,A列表示购买数量月列表示购买单价 =sumproduct(A2:A8,B2:B8) 意思为A2*B2+A3*B3oooo +A8*B8 2.求二班有多少学生学习了数学 =sumproduct((A2:A10=H~ B,,)*(B2:B10=H数学“)) 表示二班的数学有几个 3.求二班数学分数总和 =sumproduct((A2:A10=M Z：B M)*(B2:B10=H数学H)*(C2:C1O)) 4.统计“技术部”考试成绩为0的个数(缺考除外) =sumproduct((B2:B9=H技术部,,)*(E2:E9=0)*(E2:E9o,,H)) excel会将空值看成0,所以在统计成绩为0的考生时，需要把成绩为空的考牛去除。
excel常用公式函数大全 1．求和函数SUM 语法：SUM（number1，number2，．．．）。 参数：number1、number2．．．为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。 注意：参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。如果参数为数组或引用，只有其中的数字参与计算，数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。 应用实例一：跨表求和 使用SUM函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域进行求和，可以采用以下方法：选中Excel XP“插入函数”对话框中的函数，“确定”后打开“函数参数”对话框。切换至第一个工作表，鼠标单击“number1”框后选中需要求和的区域。如果同一工作表中的其他区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。 应用实例二：SUM函数中的加减混合运算 财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。按照规定，工作表中的这些项目没有输入负号。这时可以构造“=SUM（B2:B6，C2:C9，-D2，-E2）”这样的公式。其中B2:B6，C2:C9引用是收入，而D2、E2为支出。由于Excel不允许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出正确结果。即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:B6，C2:C9，-D2，-D3，D4）”这样的形式。 应用实例三：及格人数统计 假如B1:B50区域存放学生性别，C1:C50单元格存放某班学生的考试成绩，要想统计考试成绩及格的女生人数。可以使用公式“=SUM（IF（B1:B50=″女″，IF（C1:C50>=60，1，0）））”，由于它是一个数组公式，输入结束后必须按住Ctrl+Shift键回车。公式两边会自动添加上大括号，在编辑栏显示为“{=SUM（IF （B1:B50=″女″，IF（C1:C50>=60，1，0）））}”，这是使用数组公式必不可少的步骤。 2.平均值函数AVERAGE 语法：AVERAGE（number1，number2，．．．）。 参数：number1、number2．．．是需要计算平均值的1～30个参数。 注意：参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。如果需要将参数中的零排除在外，则要使用特殊设计的公式，下面的介绍。 应用实例一：跨表计算平均值
EXCEL的常用计算公式大全 一、单组数据加减乘除运算： ①单组数据求加和公式：=（A1+B1） 举例：单元格A1：B1区域依次输入了数据10和5，计算：在C1中输入 =A1+B1 后点击键盘“Enter（确定）”键后，该单元格就自动显示10与5的和15。 ②单组数据求减差公式：=（A1-B1） 举例：在C1中输入 =A1-B1 即求10与5的差值5，电脑操作方法同上； ③单组数据求乘法公式：=（A1*B1） 举例：在C1中输入 =A1*B1 即求10与5的积值50，电脑操作方法同上； ④单组数据求乘法公式：=（A1/B1） 举例：在C1中输入 =A1/B1 即求10与5的商值2，电脑操作方法同上； ⑤其它应用： 在D1中输入 =A1^3 即求5的立方（三次方）； 在E1中输入 =B1^（1/3）即求10的立方根 小结：在单元格输入的含等号的运算式，Excel中称之为公式，都是数学里面的基本运算，只不过在计算机上有的运算符号发生了改变——“×”与“*”同、“÷”与“/”同、“^”与“乘方”相同，开方作为乘方的逆运算，把乘方中和指数使用成分数就成了数的开方运算。这些符号是按住电脑键盘“Shift”键同时按住键盘第二排相对应的数字符号即可显示。如果同一列的其它单元格都需利用刚才的公式计算，只需要先用鼠标左键点击一下刚才已做好公式的单元格，将鼠标移至该单元格的右下角，带出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可，即可完成公司自动复制，自动计算。 二、多组数据加减乘除运算： ①多组数据求加和公式：(常用) 举例说明：=SUM（A1:A10）,表示同一列纵向从A1到A10的所有数据相加； =SUM（A1:J1）,表示不同列横向从A1到J1的所有第一行数据相加； ②多组数据求乘积公式：（较常用） 举例说明：=PRODUCT（A1:J1）表示不同列从A1到J1的所有第一行数据相乘； =PRODUCT（A1:A10）表示同列从A1到A10的所有的该列数据相乘； ③多组数据求相减公式：(很少用) 举例说明：=A1-SUM（A2:A10）表示同一列纵向从A1到A10的所有该列数据相减； =A1-SUM（B1:J1）表示不同列横向从A1到J1的所有第一行数据相减； ④多组数据求除商公式：(极少用) 举例说明：=A1/PRODUCT（B1:J1）表示不同列从A1到J1的所有第一行数据相除； =A1/PRODUCT（A2:A10）表示同列从A1到A10的所有的该列数据相除； 三、其它应用函数代表： ①平均函数 =AVERAGE（:）；②最大值函数 =MAX （:）；③最小值函数 =MIN （:）； ④统计函数 =COUNTIF（:）：举例：Countif ( A1：B5，”>60”) 说明：统计分数大于60分的人数，注意，条件要加双引号，在英文状态下输入。
EXCEL公式及函数的高级应用 公式和函数是Excel 最基本、最重要的应用工具，是Excel 的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。 1.1 数组公式及其应用 数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。在数组公式中使用两组或多组数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。数组公式中的每个数组参数必须有相同数量的行和列。 1.1.1 数组公式的输入、编辑及删除一．数组公式的输入 数组公式的输入步骤如下： (1 )选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。 (2 )输入数组公式。 (3 )同时按“ Crtl+Shift+Enter ”组合键，则Excel 自动在公式的两边加上大括号{ } 。 特别要注意的是，第( 3)步相当重要，只有输入公式后同时按“ Crtl+Shift+Enter ”组合键，系统才会把公式视为一个数组公式。否则，如果只按Enter 键，则输入的只是一个简单的公式，也只在选中的单元格区域的第 1 个单元格显示出一个计算结果。 在数组公式中，通常都使用单元格区域引用，但也可以直接
键入数值数组，这样键入的数值数组被称为数组常量。当不想 在工作表中按单元格逐个输入数值时，可以使用这种方法。 如果要生成数组常量，必须按如下操作： （1）直接在公式中输入数值，并用大括号“{ } ”括起来。 （2）不同列的数值用逗号“ , ”分开。 （3）不同行的数值用分号“ ; ”分开。 ★ 输入数组常量的方法： 例如，要在单元格A1:D1中分别输入10，20，30和40这4个 数值，则可采用下述的步骤： （1）选取单元格区域A1:D1，如图2-1 所示。 图1-1 选取单元格区域A1:D1 （2）在公式编辑栏中输入数组公式“={10,20,30,40} ”，如图2-2 所示。
工作中最常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。
2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2
=COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。 2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。
四、求和公式 1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 说明：如果标题行没有规则用第2个公式
2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法 3、单条件模糊求和
公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符*
EXCEL2003函数大全 张小峰编 鲁东大学计算机科学与技术学院 2008年11月
EXCEL函数大全 目录 一、数据库和清单管理函数 (1) 二、日期和时间函数 (5) 三、财务函数 (10) 四、信息函数 (15) 五、逻辑运算符 (20) 六、查找与引用 (23) 七、数学与三角函数 (32) 八、统计函数 (52) 九、文本函数 (82)
一、数据库和清单管理函数 1、DA VERAGE 【参数】返回数据库或数据清单中满足指定条件的列中数值的平均值。 【语法】DA VERAGE(database，field，criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 2、DCOUNT 【参数】返回数据库或数据清单的指定字段中，满足给定条件并且包含数字的单元格数目。 【语法】DCOUNT(database，field，criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 3、DCOUNTA 【参数】返回数据库或数据清单指定字段中满足给定条件的非空单元格数目。【语法】DCOUNTA(database，field，criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 4、DGET 【参数】从数据清单或数据库中提取符合指定条件的单个值。 【语法】DGET(database，field，criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数
Excel中公式与函数的应用 一、教学目标：1、知识目标：掌握excel的公式组成格式。理解函数的概念，掌握常见函数如（sum,average）的使用。2、能力目标：掌握使用函数（sum,average）计算所给数据的求和，求平均值，并且能够根据工作需要修改函数参数，最后达到能够利用所学知识与技能来解决现实生活中所遇到的问题。3、情感目标：故事情境的导入，激发了学生学习excel电子表格的强烈欲望，在逐一问题得到解决中，感受学习excel 电子表格必要性和重要性。在任务的驱动下，激活学生自主学习意识，在任务的完成过程中体会成功的喜悦，并在具体的任务中感受助人为乐的快乐与充实。二、教学重点、难点：1、重点：公式格式的输入，sum、average 函数的插入和使用。2、难点：公式格式的修改，函数参数的正确使用以及修改。三、教学方法：引导操作，自主探究，任务驱动，互助学习四、教学素材准备： excel电子表格版的学生成绩单。五、教学过程1、情境引入：（1）、刘老师是位有着28年教学经验的老教师，在这28年当中，都担任班主任，工作尽心尽责，深受学生、校领导、家长的好评！然而，随着科学技术的发展，学校从今年起开始步入无纸化办公，面对计算机的使用操作，刘老师感觉心有余而力不足，毕竟老了.如今刘老师要分析学生第一次月考成绩，面对excel电子表格，她向以往填纸制表格一样，用计算器逐个计算，然后再填入表格中，用时大概两个小时。对于这项工作，如果你会操作电子表格，只需两分钟左右就可以解决。同学们，你们想拥有这种能力吗？愿意帮刘老师的大忙吗？（2）、刘老师要处理的
excel电子表格。（3）、通过观察刘老师要处理的excel电子表格，让学生明确要学习的内容与目的，——引出本节课的学习目标。2、明确学习目标（1）、了解公式的概念，掌握公式格式，并使用公式对数据进行处理。（2）、了解函数的概念，掌握常用函数的使用如：求和函数 sum，求平均值函数 average。（3）、能够根据工作需要修改函数参数，最后达到能够利用所学知识与技能来解决现实生活中所遇到的问题。3、新课教学（1）、教学活动之一公式的概念——公式是excel电子表格中进行数值计算的等式。公式的组成格式： =表达式。表达式可包含：有运算符、单元格、常量、函数等。例如： =b2+6， =b2+c2+d2, =sum(参数)在预设置的电子表格——“练兵场1”进行探究，首先通过引导操作，让学生掌握公式的组成及自定义公式的使用，再把时间留给学生，通过自主探究，最终掌握最基本公式组成格式及自定义公式的使用，最后利用自定义公式计算10名学生成绩的总分、平均分。假如：对于某项工作， 一、教学目标：1、知识目标：掌握excel的公式组成格式。理解函数的概念，掌握常见函数如（sum,average）的使用。2、能力目标：掌握使用函数（sum,average）计算所给数据的求和，求平均值，并且能够根据工作需要修改函数参数，最后达到能够利用所学知识与技能来解决现实生活中所遇到的问题。3、情感目标：故事情境的导入，激发了学生学习excel电子表格的强烈欲望，在逐一问题得到解决中，感受学习excel 电子表格必要性和重要性。在任务的驱动下，激活学生自主学习意识，在任务的完成过程中体会成功的喜悦，并在具体的任务中感受助人为乐的快乐与充实。二、教学重点、难点：1、重点：公式格式的输入，sum、average
学习好资料
欢迎下载
excel 公式应用大全
1、SUMPRODUCT 函数：该函数的功能是在给定的几组数组中将数组间对 应的元素相乘并返回乘积之和。例如：如图 1，如果想计算 B3：C6 和 C3：E6 这两组区域的值，可以用以下公式：“=Sumproduct(B3:C6,D3:E6)”。
图1 2、ABS 函数：如果在 A1、B1 单元格中分别输入 120、90，那么如果要求 A1 与 B1 之间的差的绝对值，可以在 C1 单元格中输入以下公式：“=ABS(A1-B 1)”。 3、IF 函数：如图 2，如果 C3 单元格的数据大于 D3 单元格，则在 E3 单元 格显示“完成任务，超出：”，否则显示“未完成任务，差额：”，可以在 E3 单元格 中输入以下公式：“=IF(C3>D3, “完成任务，超出：”,”未完成任务，差额：””。
图2 4、Ceiling 函数：该数值向上舍入基础的倍数。如图 3，在 C3 单元格中输 入以下公式：“=CEILING(B3,C3)”；而“=FLOOR(B3,C3)”则是向下舍入。
学习好资料
欢迎下载
图3
5、GCD 函数：该函数计算最大公约数。如图 4，如果要计算 B3：D3 这一 区域中 3 个数字的最大公约数，可以在 E3 单元格中输入以下公式：“=GCD(B3, C3,D3)”。
图4
6、INT 函数：该函数是向下舍入取整函数。如图 5，如果要计算显示器和 机箱的购买数量，可以在 E3 单元格中输入以下公式：“=INT(D3/C3)”。
图5
7、LCM 函数：该函数是计算最小公倍数。如图 6，如果要计算 B3：D3 这 一区域中 3 个数字的最小公倍数，可以在 E3 单元格中输入以下公式：“=LCM(B 3,C3,D3)”。
图6
8、LN 函数：该函数是计算自然对数，公式为：“=LN(B3)”。
9、LOG 函数：该函数是计算指定底数的对数，公式为：“=LOG10(B3)”。
10、MOD 函数：该函数是计算两数相除的余数。如图 7，判断 C3 能否被 B 3 整除，可以在 D4 单元格中输入以下公式：“=IF(MOD(B3,C3)=0,"是","否")”。