Excel高级培训课程22

1、Excel高级培训课程22第二讲：公 式 与 函 数 一、基本常识：一、基本常识： 1、什么是公式： 在在Excel中公式是一系列表达式的组合，其中包中公式是一系列表达式的组合，其中包 括函数，运算符和单元格区域。用到公式时必须括函数，运算符和单元格区域。用到公式时必须 先输入标志符先输入标志符=、+，其中最简单的公式就是复，其中最简单的公式就是复 制单元格（如制单元格（如=A8然后按回车）然后按回车） 2、什么是函数： 最通俗的理解就是：最通俗的理解就是：Excel软件内部自带的一些现软件内部自带的一些现 成的特殊公式，是为了完成某项任务而编写的一套成的特殊公式，是为了完成某项任务而编写的一

2、套 内部程序，需要是可直接拿来使用。他通过一些叫内部程序，需要是可直接拿来使用。他通过一些叫 做参数的设置可完成一些手工操作无法完成的工作。做参数的设置可完成一些手工操作无法完成的工作。3、Excel中的计算： 无论是公式还是函数，他们都是为了完成某一项任务而服务，因此，他们在使用时，有其共性，一个是都带有=号，二是属于计算。在Excel中，常用的有以下几种计算方式，分别是： （1）算术运算：+、-、*、/（）、%、(方) （2）文本连接运算：&（将多个单元格的内容连在一起 放到一个单元格中，文本，数字，符号都可以。） （3）比较或逻辑运算：、=、=（大于或等于） =（小于或等于）、（

3、不等 于）第二讲：公 式 与 函 数 第二讲：公 式 与 函 数（4）综合运算：手动计算与函数综合，运算方式 综合，函数与函数综合（嵌套函数）4、Excel中函数的限制： （1）参数个数：2003版30个，2007版255个。 （2）公式长度：2003版1024个字符，2007版以上 8192个字符。 （3）数字计算精度：15位第二讲：公 式 与 函 数4、Excel中改变运算优先级： 在先要计算的公式中加上（）：如=（2+6）*（5-1）-10 结果为结果为2222二、调用函数的方法：二、调用函数的方法： （1）单击编辑栏中）单击编辑栏中Fx（插入函数）按钮（插入函数）按钮 （2）快捷按键：

4、）快捷按键：shift+F3 (3) 2007版公式选项卡，版公式选项卡，Fx插入函数按钮。插入函数按钮。第二讲：公 式 与 函 数三、修改公式：三、修改公式： 1、更改计算方式：如将+改为*等。 方法是：选中有内容公式的单元格在编辑栏中选中计算符输入新的计算符即可。 2、更改单元格：如将B5改为F30。 3、更改单元格区域方式：如将A1：H50改为A1，E3，F48等 其中，英文下的逗号表示和的关系，几个单元格相加的和。 冒号表示到的关系，即从某个单元格到某个单元格。是一个 矩形区域。 4、更改单元格引用方式： （1）相对引用：计算结果同行数值相对应。 （2）绝对引用：将某一个单元格或区域锁

5、定。计算时希望公式引用 的单元格不被更改。 第二讲：公 式 与 函 数（3 3）混合引用，即相对引用和绝对引用搭配使用。其中，包括行）混合引用，即相对引用和绝对引用搭配使用。其中，包括行 相对，列相对，行绝对，列绝对和完全绝对。相对，列相对，行绝对，列绝对和完全绝对。 转换的方法为，在输入公式时，将光标定位到编辑栏引用的转换的方法为，在输入公式时，将光标定位到编辑栏引用的 单元格中，按单元格中，按F4F4键（可以在各个引用方式间切换）。键（可以在各个引用方式间切换）。5 5、更改函数：如本来是求和计算，现在根据需要要变成统计计算。、更改函数：如本来是求和计算，现在根据需要要变成统计计算。 方法

6、：选中已计算好的单元格方法：选中已计算好的单元格光标定位在编辑栏中光标定位在编辑栏中选选 中函数名如中函数名如SUMSUM输入新的函数名如输入新的函数名如COUNTIFCOUNTIF。第二讲：公 式 与 函 数四、常用的函数演示：四、常用的函数演示： （一）数学与三角函数：（一）数学与三角函数： 1、INT函数：将数值取整到整数（小数），只有一个参数。函数：将数值取整到整数（小数），只有一个参数。 选取数字所在单元格选取数字所在单元格回车使用格式：回车使用格式：INT(number)=int(A1)参数说明：参数说明：number表示需要取整的数值或包含数值的引用单元格。表示需要取整的数值或包

7、含数值的引用单元格。 2、ROUND四舍五入函数：两个参数。参数四舍五入函数：两个参数。参数1，选中数值所在，选中数值所在单元格；参数单元格；参数2，输入，输入 舍去的位数。舍去的位数。=ROUND(a1,1) 3、TRUNC截取数值函数（一般指的是小数），有截取数值函数（一般指的是小数），有2个参数。个参数。参数参数1，选中数值所在单元格；参数，选中数值所在单元格；参数2，输入，输入 截取的位数，如输入截取的位数，如输入0为为整数。整数。TRUNC(number,num_digits)参数说明：参数说明：number：表示需要截尾取整的数字。：表示需要截尾取整的数字。num_digits：表

8、示取整精度的数：表示取整精度的数字，默认值为字，默认值为0。 4、MOD返回两数的余数返回两数的余数 ：有：有2个参数。个参数。 参数参数1，选被除数数值所在单元格；参数，选被除数数值所在单元格；参数2，选除数所在单元格，选除数所在单元格。使用格式：。使用格式：MOD(number,divisor)参数说明：参数说明：number代表被除数；代表被除数；divisor代表除数。输入公式：代表除数。输入公式：=MOD(13,4)， 第二讲：公 式 与 函 数5、SUMIF单条件求和函数单条件求和函数 ：有：有3个参数。个参数。 参数参数1，选第一条件所在单元格区域，如计算四川省销售总额，选第一条

9、件所在单元格区域，如计算四川省销售总额 选四川所在单元格区域；参数选四川所在单元格区域；参数2，选第一条件，如四川；，选第一条件，如四川；参数参数3，选求和所在单元格区域，如销售额数值所在区域。使用格式，选求和所在单元格区域，如销售额数值所在区域。使用格式：SUMIF（Range,Criteria,Sum_Range）参数说明：参数说明：Range代表条件判断的单元格区域；代表条件判断的单元格区域；Criteria为指定条为指定条件表达式；件表达式；Sum_Range代表需要计算的数值所在的单元格区域代表需要计算的数值所在的单元格区域。输入公式：输入公式：=SUMIF(C2:C63,男男,D2

10、:D63)6、SUMIFS多条件求和函数：有多条件求和函数：有255个参数，但常用的有个参数，但常用的有5个等。个等。 如本节案例企业员工工资本月实发表格，求市场如本节案例企业员工工资本月实发表格，求市场1部甘部甘 倩琦销售总额：倩琦销售总额： 调用调用sumifs函数后，参数函数后，参数1，选求和数值所在区域；参数，选求和数值所在区域；参数2, 第第1条件所在区域（即所属部门单元格区域）；参数条件所在区域（即所属部门单元格区域）；参数3，选第，选第1 条件所在单元格（即任意市场部条件所在单元格（即任意市场部1单元格）；第单元格）；第4参数，第参数，第2条条 件所在区域（即销售员单元格区域）；

11、第件所在区域（即销售员单元格区域）；第5参数，选第参数，选第2条件条件 所在单元格（即甘倩琦）。所在单元格（即甘倩琦）。第二讲：公 式 与 函 数 （二）统计函数：（二）统计函数： 1、counta：统计选中的区域中非空单元格数目，：统计选中的区域中非空单元格数目，1个参数。个参数。 选中要统计的单元格区域。输入公式选中要统计的单元格区域。输入公式=COUNTA(A1:A7) 2、countif：单条件统计函数，有：单条件统计函数，有2个参数。个参数。 参数参数1，选条件所在单元格区域；参数，选条件所在单元格区域；参数2，选条件所在任意单元格或输，选条件所在任意单元格或输入条件。使用格式：入条

12、件。使用格式：COUNTIF(Range,Criteria) 参数说明：参数说明：Range代表要统计的单元格区域；代表要统计的单元格区域；Criteria表示指定的条件表示指定的条件表达式。输入公式：表达式。输入公式：=COUNTIF(B1:B13,=80) 3、countifs：多条件统计函数，有：多条件统计函数，有255个参数，但常用的有个参数，但常用的有 6个，本小结案例产品销售统计表个，本小结案例产品销售统计表 ，求市场，求市场1部部 销售额大于销售额大于4000的甘倩琦有几个？的甘倩琦有几个？ 参数参数1，选第，选第1条件单元格所在区域如条件单元格所在区域如E3：E56；参数；参数

13、2,第第 1条件所在单元格即市场部条件所在单元格即市场部1；参数；参数4，第，第2条件单元格所在区域条件单元格所在区域 如如F3：F56；参数；参数4，选任意条件所在单元格或输入条，选任意条件所在单元格或输入条4000 参数参数5，第，第3条件单元格所在区域即条件单元格所在区域即D3:D56；参数；参数6，第，第3条件所在任意单元格条件所在任意单元格即甘倩琦。即甘倩琦。语法语法COUNTIFS(range1, criteria1,range2, criteria2)第二讲：公 式 与 函 数4、rank：统计排名函数，有：统计排名函数，有3个参数。个参数。 参数参数1，选要排名的函数区域开始所

14、在单元格；参数，选要排名的函数区域开始所在单元格；参数2，选排名数值所单，选排名数值所单元格区域（一般为绝对引用）；参数元格区域（一般为绝对引用）；参数3，升序还是降序，一般为升序，升序还是降序，一般为升序，所以该参数省略不输。，所以该参数省略不输。使用格式：使用格式：RANK（Number,ref,ord）参数说明）参数说明：Number代表需要排序的数值；代表需要排序的数值；ref代表排序数值所处的单元格区域；代表排序数值所处的单元格区域；order代表代表排序方式参数输入公式：排序方式参数输入公式：=RANK(B2,$B$2:$B$31,0)（三）文本函数：（三）文本函数： 1、LEN：

15、统计字符串函数，有：统计字符串函数，有1个参数选中要统计字符的单元格。个参数选中要统计字符的单元格。主要功能：统计文本字符串中字符数目。主要功能：统计文本字符串中字符数目。使用格式：使用格式：LEN(text) 参数说明参数说明：text表示要统计的文本字符串。输入公式：表示要统计的文本字符串。输入公式：=LEN(A40) 2、LEFT：从左边开始提取字符串函数，有：从左边开始提取字符串函数，有2个参数。个参数。 参数参数1，选中提取字符串的单元格；参数，选中提取字符串的单元格；参数2，输入提取的个数。，输入提取的个数。使用格式：使用格式：LEFT(text,num_chars)参数说明：参数

16、说明：text代表要截字符的字符串；代表要截字符的字符串；num_chars代表给定的截取数目输入公式：代表给定的截取数目输入公式：=LEFT(A38,3) 3、RIGHT：从右侧提取字符串函数，有：从右侧提取字符串函数，有2个参数个参数参数参数1，选中提选中提取字符串的单元格；参数取字符串的单元格；参数2，输入提取，输入提取 的个数。的个数。使用格式：使用格式：RIGHT(text,num_chars) 参数说明：参数说明：text代表要截字符的字符串；代表要截字符的字符串；num_chars代表给定的截取数目。输入公式：代表给定的截取数目。输入公式：=RIGHT(A65,3) 第二讲：公

17、式 与 函 数4、MID：从任意字符开始提取字符，有：从任意字符开始提取字符，有3个参数。参数个参数。参数1，选中提取选中提取字符串的单元格；参数字符串的单元格；参数2，从哪开始提取（输入数字）；参数，从哪开始提取（输入数字）；参数3，提几位（输数字）。提几位（输数字）。使用格式：使用格式：MID(text,start_num,num_chars)参参数说明：数说明：text代表一个文本字符串；代表一个文本字符串；start_num表示指定的起始位置表示指定的起始位置num_chars表示要截取的数目。输入公式：表示要截取的数目。输入公式：=MID(A47,4,3) 5、TEXT：将数字转换为

18、文本，有：将数字转换为文本，有1个参数。选中要转换数字单元格个参数。选中要转换数字单元格。使用格式：使用格式：TEXT(value,format_text) 参数说明：参数说明：value代表需要转换的代表需要转换的数值或引用的单元格；数值或引用的单元格；format_text为指定文字形式的数字格式。输入公式：为指定文字形式的数字格式。输入公式：=TEXT(B68, $0.00)6、VALUE：将文本型数字转换成数字格式，有：将文本型数字转换成数字格式，有2个参数。个参数。 参数参数1，选要转换的文本所在单元格；参数，选要转换的文本所在单元格；参数2，设置转换，设置转换 后的格式。一般选择元

19、单元格，以保证转换后是原来的格式。后的格式。一般选择元单元格，以保证转换后是原来的格式。使使用格式：用格式：VALUE(text) 参数说明：参数说明：text代表需要转换文本型字符串数值。公式代表需要转换文本型字符串数值。公式=VALUE（A1）7、CONCATENATE：文本连接函数，将多个单元格的字符连在起：文本连接函数，将多个单元格的字符连在起，有，有255参数。参数参数。参数1，选要连接的第，选要连接的第1单元格，第单元格，第2单元格，单元格，使用格式：使用格式：CONCATENATE(Text1，Text) 参数说明：参数说明：Text1、Text2为需要连接的字符文本或引用的单元

20、格。为需要连接的字符文本或引用的单元格。 应用举例：在应用举例：在C14单元格中单元格中输入公式：输入公式：=CONCATENATE(A14,B14,.com)，确认后，即可将，确认后，即可将A14单元格中字符、单元格中字符、B14单元格中的字符和单元格中的字符和.com连接成一个整体，显示在连接成一个整体，显示在C14单元格中。单元格中。 第二讲：公 式 与 函 数（四）逻辑函数：（四）逻辑函数： 1、AND：与的含义，判断：与的含义，判断2个条件是否同时满足，都满足时返回个条件是否同时满足，都满足时返回 想要实现的内容，有想要实现的内容，有1个不满足时，返回假值。有个不满足时，返回假值。有

21、255个参数个参数 参数参数1，输入条件，输入条件1，参数，参数2，输入条件，输入条件2， 注：用这个参注：用这个参数前，一定要先给定条件。使用格式：数前，一定要先给定条件。使用格式：AND(logical1,logical2, .) 参数说明：参数说明：Logical1,Logical2,Logical3：表示待测试：表示待测试的条件值或表达式，最多这的条件值或表达式，最多这30个。输入公式：个。输入公式：=AND(A5=60,B5=60) 2、OR：或的含义，判断所有条件，有一个满足为要返回的内容：或的含义，判断所有条件，有一个满足为要返回的内容 都不满足时为假值。都不满足时为假值。 本节

22、案例，打开本节案例，打开AND函数表格。函数表格。 在在C2单元格输入单元格输入=AND(A2=男男,B260)*1000 =OR(A2=女女,B260)*1000当所有当所有参数值均为逻辑参数值均为逻辑“假（假（FALSE）”时返回函数结果逻辑时返回函数结果逻辑“假（假（FALSE）”，否则都返回逻辑，否则都返回逻辑“真（真（TRUE）”。 第三讲：剩余函数与排序，筛选，分类汇总。一、其他常用函数：一、其他常用函数： （一）日期函数：（一）日期函数：1、TODAY：返回能自动更新的系统当前日期，无参数。：返回能自动更新的系统当前日期，无参数。TODAY() 2、NOW：返回能自动更新的系统当

23、前时间，无参数。：返回能自动更新的系统当前时间，无参数。NOW() 3、DATE：将分散的日期连接在一起，有：将分散的日期连接在一起，有3个参数。参数个参数。参数1，选年所，选年所在单元格；参数在单元格；参数2，选月所在单元格，选月所在单元格参数参数3，选日所在单元格。使用格式：，选日所在单元格。使用格式：DATE(year,month,day) 参数说明：参数说明：year为指定的年份数值（小于为指定的年份数值（小于9999）；）；month为指定为指定的月份数值（可以大于的月份数值（可以大于12）；）；day为指定的天数。为指定的天数。 输入公式：输入公式：=DATE(2003,13,35

24、)，确认后，显示出，确认后，显示出2004-2-4 4、YEAR：返回日期中的年，一般与：返回日期中的年，一般与today函数合用，有函数合用，有1参数选参数选中要截取的日期所在单元格。给出系统日期使用格式中要截取的日期所在单元格。给出系统日期使用格式TODAY()输入公式：输入公式：=TODAY()=YEAR(2015-5-6)结果是结果是2015 5、MONTH：返回日期中的月，有：返回日期中的月，有1个参数。个参数。=MONTH（2015-5-6）结果是）结果是5 选中要截取的日期所在单元格。选中要截取的日期所在单元格。第三讲：剩余函数与排序，筛选，分类汇总。6、DAY：返回日期中的日，

25、有：返回日期中的日，有1个参数。个参数。 选中要截取的日期所在单元格。选中要截取的日期所在单元格。=DAN（2015-5-6）结果）结果是是6（二）查找与引用函数：（二）查找与引用函数： 1、LOOKUP：在数据区域的第一行：在数据区域的第一行第一列中查找指定的值，第一列中查找指定的值，然后返回最后一行然后返回最后一行一列中同行一列中同行同列相对应的数值，常用的有同列相对应的数值，常用的有2个参数。输入个参数。输入=LOOKUP(E2,B2:B8,C2:C8) 参数参数1，用谁找，用谁找，选条件所在单元格；参数，选条件所在单元格；参数2，到哪找，选数，到哪找，选数 据区域单元格。据区域单元格。

26、 打开本节案例企业员工考评管理系统表，利用编号找总成绩。打开本节案例企业员工考评管理系统表，利用编号找总成绩。 选中员工技能考勤表，利用编号选中员工技能考勤表，利用编号HSR1010找它的总成绩是：找它的总成绩是： 先在其他单元格输入先在其他单元格输入HSR1010单击单击Fx按钮按钮类别为查找类别为查找 与引用与引用选择选择LOOCUP确定确定选择第选择第2项项确定确定参数参数1选选 HSR1010所在单元格所在单元格参数参数2选选A3G20（要绝对引用）（要绝对引用）第三讲：剩余函数与排序，筛选，分类汇总。2、HLOOKUP函数：按行查找函数，有函数：按行查找函数，有4个参数。个参数。 参

27、数参数1，用谁找；参数，用谁找；参数2，到哪找；参数，到哪找；参数3，找到后，返回第，找到后，返回第 几行；参数几行；参数4，精确找，还是模糊找。精确找输入，精确找，还是模糊找。精确找输入0,不精确找不精确找，输入，输入1，但得需要排序。一般都是精确找，所有可以省略不输，但得需要排序。一般都是精确找，所有可以省略不输 打开本小节案例企业员工考评管理系统表，选中员工业绩考评表打开本小节案例企业员工考评管理系统表，选中员工业绩考评表 选中选中D3单元格单元格输入公式输入公式=HLOOKUP(C3,员工业绩奖励标准员工业绩奖励标准!$B$2:$F$4,3)*C3，本例省略了最后一个参数。，本例省略了

28、最后一个参数。3、VLOOKUP函数：按列查找函数，有函数：按列查找函数，有4个参数。个参数。 参数参数1，用谁找；参数，用谁找；参数2，到哪找（一般为绝对引用）；参数，到哪找（一般为绝对引用）；参数3 找到后，返回第几列；参数找到后，返回第几列；参数4，精确找，还是模糊找。精确找，精确找，还是模糊找。精确找 输入输入0，不精确找输入，不精确找输入1。 打开本小节案例企业员工考评管理系统表，选中员工年终考评打开本小节案例企业员工考评管理系统表，选中员工年终考评 表选中表选中3单元格单元格输入公式输入公式=VLOOKUP(A3,员工工作态度考员工工作态度考 评评!$A$3:$H$20,8)，本例

29、省略了最后一个参数。，本例省略了最后一个参数。 第三讲：剩余函数与排序，筛选，分类汇总。注意：使用注意：使用vlookup函数，要几点要求，否则系统将报错。函数，要几点要求，否则系统将报错。 1、查找的内容即用谁找时那一项必须在数据区域的首列、查找的内容即用谁找时那一项必须在数据区域的首列 2、参数一定要输全。、参数一定要输全。 3、没有特殊要求，一般都用绝对引用。、没有特殊要求，一般都用绝对引用。 如果想查找时，目标内容不是首列，可以把列位置倒换以下。如如果想查找时，目标内容不是首列，可以把列位置倒换以下。如用姓名查找编号，可将姓名和编号列倒换位置。用姓名查找编号，可将姓名和编号列倒换位置。

30、（三）函数综合使用案例即函数的嵌套使用。（三）函数综合使用案例即函数的嵌套使用。 1、根据员工身份证号提取出生年月日：、根据员工身份证号提取出生年月日： 打开本节案例企业员工档案管理表打开本节案例企业员工档案管理表选中选中D3单元格，输入公式单元格，输入公式=IF(LEN(C3)=15,CONCATENATE(19,MID(C3,7,2),年年,MID(C3,9,2),月月,MID(C3,11,2),日日),CONCATENATE(MID(C3,7,4),年年,MID(C3,11,2),月月,MID(C3,13,2),日日) 如果都是如果都是18位的，公式没有这么复杂。位的，公式没有这么复杂。

31、 第三讲：剩余函数与排序，筛选，分类汇总。2、根据员工身份证号判断性别为男女：、根据员工身份证号判断性别为男女： 打开本节案例企业员工档案管理表打开本节案例企业员工档案管理表选选E3单元格输入公式单元格输入公式 =IF(LEN(C3)=15,IF(MOD(MID(C3,15,1),2)=1,“男男, 女女),IF(MOD(MID(C3,17,1),2)=1,男男,女女) 如果身份证都是如果身份证都是18位的，公式没这么复杂。位的，公式没这么复杂。3、计算工龄：、计算工龄： 打开本节案例企业员工档案管理表打开本节案例企业员工档案管理表选中选中G3单元格，输入公式单元格，输入公式 =YEAR(TO

32、DAY()-YEAR(F3)第三讲：剩余函数与排序，筛选，分类汇总。二、排序：二、排序： 1、按行排序： 选中要排序的单元格区域开始选项卡排序和筛选按钮自定义排序确定选择选项按行排序确定然后依次设置即可。 2、2007新增按颜色排序： 打开本节案例这样的排序你用过吗表选中按颜色排 序工资表按要求操作选中B2：B12开始排 序和筛选自定义排序主要关键字为销售额排序 依据为单元格颜色次序中先选一个颜色在顶端 确定。第三讲：剩余函数与排序，筛选，分类汇总。2、按自定义序列排序： 打开本节案例这样的排序你用过吗表选中按自定义序列排序工作表先添加已经输好的序列单击office按钮Excel选项常用单击编

33、辑自定义列表单击红色折叠按钮选中B3:B11单元格单击导入确定 确定开始排序与筛选自定义排序选项次序为自定义序列选中已经添加的序列确定确定3、恢复排序前的顺序： 加入一个辅助列，输入自然数1,2,3，排序后，在排序时关键是为辅助列，次序为升序确定。第三讲：剩余函数与排序，筛选，分类汇总。4、各行插入空行：制作工资条 加入一个辅助列先在有内容的区域输入1,3,5等，然后再他下方相等的单元格中输入2,4,6,8等给辅助列 升序排序删除辅助列选中有第1行有内容区域的单元格复制选中有内容的区域，包含空行F5单击定位条件空值确定粘贴。 第三讲：剩余函数与排序，筛选，分类汇总。三、高级筛选（多条件筛选）三、高级筛选（多条件筛选） 1、高级筛选中的与和或的关系：、高级筛选中的与和或的关系： 当条件在同行中写，表示与的关系，即多个条件当条件在同行中写，表示与的关系，即多个条件同时满足；当条件错行写，表示或的关系，即满足其中同时满足；当条件错行写，表示或的关系，即满足其中一个即可。一个即可。 2、筛选不重复值：、筛选不重复值： 选中