第五章Excel公式与函数ppt课件.ppt

《第五章Excel公式与函数ppt课件.ppt》由会员分享，可在线阅读，更多相关《第五章Excel公式与函数ppt课件.ppt（62页珍藏版）》请在三一文库上搜索。

1、项目6 Excel公式与函数,项目6 Excel公式与函数,项目提出,以“公式与函数”工作簿为项目。其中“公式”工作表的内容如图6-1所示。,图6-1 “公式”表的内容,项目6 Excel公式与函数,项目提出,(1) 怎样完成公式“总评成绩”、“及格否”、“偏差”的输入？ (2) 在公式与函数中，怎样判断单元地址的引用类型？ (3) 对公式给出的错误信息，如何分析，如何改正公式？ (4) 各种类型的函数又如何使用？各参数的范围又如何确定？,项目分析,所有公式都是以“=”开始的表达式，通过公式的运算后，公式所在的单元格显示是公式的值，公式的本身在编辑栏中显示。 函数存在于公式中，是公式的重要组成

2、部分，每个函数由函数名及函数参数组成，经函数运算后，得到一个或几个函数的值。,项目6 Excel公式与函数,项目导航,通过本项目的学习，应掌握的内容主要有： (1) 公式的表达形式是以等于号开始，后接表达式的等式，表达式中包括常量，单元格地址(相当于变量)、各种运算符(算术、文本、关系、引用)、函数等对象。 (2) 在公式中，引用单元格地址有3种方式，相对引用、绝对引用和混合引用。 (3) 函数是数据处理必不可少的计算工具，依靠函数，可以获取新的数据，函数主要有数字函数、文本函数、统计函数和逻辑函数等。 (4) 单元格公式输入有两种情形，一是直接输入，对熟练者适用；二是公式向导输入，对初学者适

3、用。,项目6 Excel公式与函数,项目导航,本项目的重点内容为： (1) 公式的建立。 (2) 函数的使用。 (3) 地址的引用。 Excel公式与函数的操作内容导航见表6-1。,表6-1 公式函数操作导航,6.1 单元格公式,6.1.1 输入公式,任务1,在“公式”工作表中，输入D4单元格计算公式(总评=平时成绩30%+期末成绩70%)。,操作步骤,选中D4单元格，输入公式“=B4*30%+C4*70%”，按Enter键，或者单击编辑栏左侧的“确定” 按钮。 D4单元格中显示计算结果，编辑栏中显示当前单元格的公式，如图6-2所示。,6.1 单元格公式,6.1.1 输入公式,图6-2 输入公

4、式,6.1 单元格公式,6.1.1 输入公式,操作技巧,(1) 在公式输入过程中，公式中出现单元格引用时，可用鼠标直接单击单元格获取单元格地址，比直接输入更方便。 (2) 双击公式所在的单元格，单元格处于编辑状态，可以看到被该公式引用的所有单元格或单元格区域将以不同的颜色显示在公式单元格中，并在相应的单元格或单元格区域显示相同颜色的边框，便于用户检查并修改单元格或单元格区域的引用。,6.1 单元格公式,6.1.2 运算符,1算术运算符,表6-2 算术运算符的说明,6.1 单元格公式,6.1.2 运算符,2文本连接运算符,任务2,在C10、C11单元格中，连接同行左边两个单元格内容。,操作步骤,

5、选择C10单元格，输入公式“=A10 & B10”；选择C11单元格，输入公式“=A11 & B11”，计算结果如图6-3所示。,图6-3 计算结果,6.1 单元格公式,6.1.2 运算符,3关系运算符,表6-3 关系运算符的说明,比较运算符的运算优先级均相同，使用比较运算符的参数一般为数值型，“=”或“”也可用于字符型与逻辑型。,6.1 单元格公式,6.1.2 运算符,3关系运算符,任务2,输入E4单元格计算公式，当总评大于等于60分时，为及格，用TRUE表示，否则用FALSE表示。,操作步骤,选择E4单元格，输入“=D4=60”，显示结果为TRUE。,6.1 单元格公式,6.1.2 运算符

6、,4引用运算符,一个引用单元格地址表示公式中要使用的单元格的数值，相当于一个变量，当被引用单元格数值改变时，公式计算结果也同步改变。,表6-4 引用运算符的说明,6.1 单元格公式,6.1.2 运算符,4引用运算符,操作技巧,联合运算符中，运算符左右区域中如果有重叠区域，则参与计算两次，具体运算次序为：如果公式中同时用到多个运算符，Excel将按如下所示的顺序进行运算：“-”(负号)、“%”、“”、“*”和“/”、“+”和“-”(减号)、“&”、比较运算符(=、=、)，如果公式包含相同优先级的运算符，则从左到右进行运算。 如果要改变运算过程的顺序，可将公式中要先计算的部分用括号括起来。例如，公

7、式“=(B4+C4)/(D4-F4)”，首先计算B4+C4，然后计算D4-F4，再相除。,6.2 单元格引用,6.2.1 相对引用,任务4,复制D4单元格公式到D5:D7，查看相对引用地址的变化。,操作步骤,向下拖动填充柄，完成公式的复制。,操作技巧,当公式复制到D5时，公式变为“=B5*30%+C5*70%”，这是因为从D4到D5，列没有变化，而行向下偏移了一行，所以公式中单元格的地址列标没变，行号自动增1，以维持公式所在单元格与被引用的单元格之间的相互位置不变。,6.2 单元格引用,6.2.2 绝对引用,任务5,在F4单元格中输入计算公式“=D4-$D$8” ，并将公式复制到F5:F7单元

8、格，查看绝对地址。偏差等于总分与期望值之差。,操作步骤,选择F4单元格，输入公式“=D4-$D$8”，并向下填充到F7单元格。,6.2 单元格引用,6.2.2 绝对引用,操作技巧,(1) 编辑地址引用类型时，可用鼠标直接获取单元格地址，再连续按F4键，循环改变引用的类型，而不必手工输入“$”号。 (2) 由于公式需要复制到F5:F7时，且公式为相应总评与固定单元格D8之差，故D8单元格应为绝对地址。即不管公式复制到任何单元格，引用的永远是D8单元格中的数据。,6.2 单元格引用,6.2.3 混合引用,1引用类型的判断,(1) 公式填充到同一行或者同一列。以单元格或者单元格区域为对象，判断单元格

9、的引用类型，引用类型为绝对引用或者相对引用。,任务6,总分与偏差公式引用类型的判断。,操作步骤,1）前期准备。把单元格公式以“公式”形式显示，选择“工具”|“选项”命令，在“选项”对话框的“视图”选项卡中的“窗口选项”区域中，选中“公式”复选框，这时表中的公式将以公式形式显示。,6.2 单元格引用,6.2.3 混合引用,1引用类型的判断,操作步骤,2）输入主公式与辅助公式。按相对引用方法，输入D4单元格公式，作为主公式，在相邻的单元格D5中，以相同的顺序与方法输入D5单元格公式，作为辅助公式。 同理输入F4、F5单元格主公式与辅助公式，如图6-4所示。,图6-4 同行或同列单元格引用类型的判断

10、,6.2 单元格引用,6.2.3 混合引用,1引用类型的判断,操作步骤,3）判断。比较主公式与辅助公式，在相同的对应位置，如果不是同一个单元格或者单元区域，则为相对引用，否则为绝对引用。 D4、D5单元格公式中，没有相同的地址，单元格的引用为相对引用。 在F4、F5两对公式中，D8相同，说明在D4单元格主公式中，D8为绝对引用，重新编辑主公式，在绝对引用的列、行前加“$”，修改后的主公式为“D4-$D$8”。 4）后期工作。删除辅助公式，取消选中“视图”选项卡中的“公式”复选框。,6.2 单元格引用,6.2.3 混合引用,1引用类型的判断,任务7,选择“九九乘法表”工作表，进行公式的引用类型的

11、判断，并制作“九九乘法表”。,(2) 公式填充到不同行或列。以单元格的行或者列为对象，判断单元格的引用，单元格引用为绝对引用或者混合引用。,操作步骤,1）前期准备。把单元格公式以“公式”形式显示。,6.2 单元格引用,6.2.3 混合引用,1引用类型的判断,操作步骤,2）输入主公式与辅助公式。按相对引用方法，输入I3单元格公式，作为主公式，在右下角单元格J4中，以相同的顺序与方法输入单元格公式，作为辅助公式，要注意主公式与辅助公式所在的单元格位于不同行与列。如图6-5所示。,图6-5 不同行列引用类型的判断,6.2 单元格引用,6.2.3 混合引用,1引用类型的判断,操作步骤,3）判断。从左至

12、右，比较I3、J4单元格主公式与辅助公式中的行或者列，在相同的对应位置，不同的行或列为相对引用，相同的行或列为绝对引用，在绝对引用前加“$”符号。 主公式修改为“=$H*I$2”。 4）后期工作。删除辅助公式，取消选中“视图”选项卡中的“公式”复选框。将主公式复制到I3:Q11区域，完成“九九乘法表”的制作。,6.2 单元格引用,6.2.3 混合引用,2公式的移动,当公式移动后，所引用单元地址还是原来的地址，不会改变。,3单元格或单元格区域引用的移动,公式所引用的单元格或单元格区域(不论是相对引用还是绝对引用、混合引用)移动后，则公式会自动作相应的改变，引用变为单元格或单元格区域移动后的新地址

13、，维持公式的返回值不变。,6.3 错 误 信 息,表6-5 Excel 2003错误提示信息,6.3 错 误 信 息,单击出现错误提示信息的单元格(但#除外)，将出现一个黄色菱形里的感叹号，单击该感叹号，会出现用于检查、修正错误的操作命令，如图6-6所示。,图6-6 被零除错误信息,6.3 错 误 信 息,例如，字符型数值单元格，虽然提示可能有错误，但可以根据实际情况忽略错误，如图6-7所示。,图6-7 字符型数值提示信息,6.4 函 数,6.4.1 函数的输入方法,函数的一般格式为：函数名(，)。,1直接输入,直接输入，就像输入其他普通的数据一样，但如果函数单独使用，要先输入等号“=”。直接

14、输入函数需要对函数比较了解，在输入过程中会有相应的提示。,2利用函数向导输入,利用编辑栏旁边的“插入函数” 按钮，再根据对话框的提示输入。这种方法比较直观，建议使用本方法。特别适应参数较多的或嵌套使用的函数。,6.4 函 数,6.4.2 数学函数,1绝对值函数,格式：ABS(number) 功能：返回number参数的绝对值。,2取整函数,格式：INT(number) 功能：将number参数向左取整为最接近或相等的整数。 在A2单元格中输入公式：=INT(1234.5)，其值为1234。 在A3单元格中输入公式：=INT(-456.78)，其值为-157。,6.4 函 数,6.4.2 数学函

15、数,3截尾函数,格式：TRUNC(number,num_digits) 功能：TRUNC函数是将number参数按num_digits参数的位数直接截取，不四舍五入。 如果num_digits等于0，则截取小数取整数部分，这时num_digits参数可省略。 如果num_digits大于0，则截取到指定的小数位。 如果num_digits小于0，则在小数点左侧进行截取。,6.4 函 数,6.4.2 数学函数,4四舍五入函数,格式：ROUND(number,num_digits) 功能：ROUND函数是对number参数按num_digits参数的位数进行四舍五入，注意此参数不能省略，没有默认值

16、。 如果num_digits等于0，则四舍五入到最接近的整数。 如果num_digits小于0，则在小数点左侧进行四舍五入。,5求余函数,格式：MOD(number, divisor) 功能：返回number参数除以divisor参数的余数，结果的正负号与除数相同。,6.4 函 数,6.4.2 数学函数,6平方根函数,格式：SQRT(number) 功能：计算number的平方根。,7圆周率函数,格式：PI( ) 功能：返回圆周率的数值，是个无参函数。,8随机函数,格式：RAND( ) 功能：随机产生一个大于等于0及小于1的数，注意产生的随机数可以等于0，但不会等于1。每次打开工作簿时都会更新

17、结果。,6.4 函 数,6.4.2 数学函数,任务8,打开“公式函数”工作簿，选择“数学函数”表，计算函数的值。随机生成一个a与b之间的整数的公式为 =INT(RAND( )*(b-a+1)+a。,操作步骤,各数学函数的计算如图6-8所示。,图6-8 数学函数的计算,6.4 函 数,6.4.2 数学函数,操作技巧,随机生成一个a与b之间的整数的公式为 =INT(RAND( )*(b-a+1)+a。,6.4.3 统计函数,格式：SUM(number1,number2,) 功能：返回所有参数number1、number2、的和。,1求和函数,6.4 函 数,6.4.3 统计函数,操作技巧,当参数为

18、逻辑常量值，则FALSE转化为0，TRUE转化为1；数值型字符转换为数字。但是如果参数为单元格引用，则仅计算数值型的数据，不计算空白单元格、逻辑值、字符型的数值。,格式：SUMIF(range,criteria,sum_range) 功能：根据指定的条件，对指定的单元格区域求和。,2条件求和函数,6.4 函 数,6.4.3 统计函数,格式：AVERAGE(number1,number2,) 功能：求算术平均值。,3平均值函数,格式：COUNT(value1,value2,)与COUNTA(value1,value2,) 功能：都是用于统计个数。COUNT函数是统计引用参数内数值型的单元格个数；

19、而COUNTA函数统计非空单元格的个数，不考虑单元格是什么数据类型。,4计数函数,6.4 函 数,6.4.3 统计函数,操作技巧,如果单元格内有空格，则不是非空单元格。,格式：COUNTIF(range,criteria) 功能：计算在range范围内符合criteria条件的单元格数目，criteria参数必须能在range范围内匹配，否则返回值为0。,5条件计数函数,操作技巧,条件计数只有COUNTIF，而没有COUNTAIF，统计满足条件的非空单元格的个数。,6.4 函 数,6.4.3 统计函数,格式：MAX(number1,number2,)与MIN(number1,number2,)

20、 功能：MAX函数是返回参数中的最大值，而MIN函数则是返回参数中的最小值。,6最大值与最小值函数,6.4 函 数,6.4.3 统计函数,任务9,打开“公式函数”工作簿，选择“统计函数”表，计算函数的值。,操作步骤,各统计函数的计算如图6-9所示,图6-9 统计函数的计算,6.4 函 数,6.4.3 统计函数,操作步骤,计算基本工资平均值函数输入如下： (1) 选择单元格C12，单击工具栏上的“插入函数”按钮，弹出“插入函数”对话框。如图6-10所示，在“或选择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择AVERAGE，单击“确定”按钮。,图6-10 “插入函数”对话框,6.4

21、函 数,6.4.3 统计函数,操作步骤,(2) 弹出“函数参数”对话框，单击“Number1”参数框右侧的“折叠”按钮，选择计算平均值的单元格区域C2:C8，再单击“折叠”按钮，单击“确定”按钮，如图6-11所示。,图6-11 “函数参数”对话框,6.4 函 数,6.4.3 统计函数,操作技巧,(1) 函数分为“财务”、“日期与时间”、“数学与三角函数”、“统计”、“查找与引用”、“数据库”、“文本”、“编辑”和“信息”九大类。而“常用函数”指的是本计算机最近使用过的函数，并非是工作中的常用函数。 (2) 在输入公式时，注意将输入法设置为英文输入法，不要设为中文输入法。 (3) 要清楚函数的名

22、称及其作用，不要将英文拼错，一些英文拼写相近的函数不要混淆，如COUNT与COUNTA。 (4) 相似功能的函数，要理解其使用的场合。例如，SUM是求和；SUMIF是条件求和，其条件仅涉及数据库的一个字段；DSUM是数据库求和，条件涉及数据库的两个及以上字段时，使用该函数。,6.4 函 数,6.4.3 统计函数,操作技巧,(5) 要明白函数的参数及其数据类型。要留意插入函数时弹出的“函数参数”对话框中的参数说明，参数引用的是单元格还是区域。 (6) 要清楚函数的返回值是什么数据类型、什么意义，尤其是要注意文本函数。 (7) 在编辑栏输入包含函数的公式时，注意不要用鼠标单击其他无关的单元格，否则

23、容易出错。编辑完成后按Enter键退出。,6.4 函 数,6.4.4 日期时间函数,1年月日转换为日期函数,格式：DATE(year,month,day) 功能：返回指定日期的序列数。,2时分秒转换为时间函数,格式：TIME(hour,minute,second) 功能：函数的结果hour时、minute 分、second秒时刻对应的小数值。如果所在的单元格数字格式设为“常规”，则结果显示为时间格式。,6.4 函 数,6.4.4 日期时间函数,3日期转换为年月日,格式：YAER(serial_number)、MONTH(serial_number)与DAY(serial_number) 功能：

24、分别返回序列数的年、月、日的数值。,4日间转换为时分秒,格式：HOUR(serial_number)、MINUTE(serial_number)与SECOND(serial_number) 功能：分别返回序列数的时、分、秒的数值。,6.4 函 数,6.4.4 日期时间函数,5系统日期时间函数,格式：NOW( ) 功能：返回当前日期和时间。如果预先将所在的单元格数字格式设为“常规”，则显示为所对应的序列号，小数点左边的数表示日期，右边的数表示时间。,6系统日期函数,格式：TODAY( ) 功能：返回当前日期。如果预先将所在的单元格的格式设置为“常规“，则结果显示为序列号。如果没有对单元格作任何设

25、置，则显示形式为日期。,6.4 函 数,6.4.4 日期时间函数,任务10,打开“公式函数”工作簿，选择“日期时间函数”表，计算函数的值。,操作步骤,各日期时间函数的计算如图6-12所示。,图6-12 日期时间函数的计算算,6.4 函 数,6.4.5 文本函数,1左截取函数和右截取函数,格式：LEFT(text,num_chars)，RIGHT(text,num_chars) 功能：都是对text参数截取子字符串，LEFT函数是从左起截取num_chars个字符，而RIGHT函数是从右起截取num_chars个字符，省略num_chars参数，则默认为1。,2中间截取函数,格式：MID(tex

26、t,start_num,num_chars) 功能：返回字符串参数text中从start_num位置开始的num_chars个字符。,6.4 函 数,6.4.5 文本函数,3文本的长度,格式：LEN(text) 功能：返回文本字符串中的字符个数。,6.4 函 数,6.4.5 文本函数,任务11,打开“公式函数”工作簿，选择“文本函数”表，计算函数的值。,操作步骤,各文本函数计算如图6-13所示。,图6-13 各文本函数的计算,6.4 函 数,6.4.5 文本函数,操作技巧,文本长度不区分中英文，每个字符计算为1，包括空格。,6.4.6 逻辑函数,1逻辑非函数,格式：NOT(logical) 功

27、能：对参数求相反的逻辑值，即如果参数值为FALSE，则NOT函数返回TRUE；如果参数值为TRUE，则NOT函数返回FALSE。,6.4 函 数,6.4.6 逻辑函数,2逻辑与函数,格式：AND(logical,logical2,) 功能：AND函数，在所有参数中，只要有一个参数的逻辑值为FALSE，则结果为FALSE；如果所有参数的逻辑值都为TRUE，结果才为TRUE。,3逻辑或函数,格式：OR(logical1,logical2,) 功能：OR函数，在所有参数中，只要有一个参数的逻辑值为TRUE，则结果为TRUE；如果所有参数的逻辑值都为FALSE，结果才为FALSE。,6.4 函 数,6

28、.4.6 逻辑函数,4条件函数,格式：IF(logical_test,value_if_true,value_if_false) 功能：Logical_test参数是一个结果为TRUE或FALSE的表达式，如果其结果为TRUE，则该函数返回value_if_true；否则返回value_if_false。,图6-14 条件函数流程图,6.4 函 数,6.4.6 逻辑函数,任务12,打开“公式函数.xls”工作簿，选择“逻辑函数”表，计算函数的值，如图6-15所示。,图6-15 逻辑表计算,6.4 函 数,6.4.6 逻辑函数,操作步骤,(1) 计算补考否。D2单元格公式为：“=OR(B2=80

29、,C2=80)” (3) 计算英语等级。等级计算分析如下： 根据条件，画出流程图，如图6-16所示。,图6-16 具体二重条件函数流程图,6.4 函 数,6.4.6 逻辑函数,使用函数向导输入公式的操作步骤如下：,(1) 选择单元格C2，选择“插入”|“函数”命令，在“插入函数”对话框的“选择函数”选项区域中选择IF函数，单击“确定”按钮；或者在C2单元格中直接输入=IF( )，单击“插入函数”按钮，弹出“函数参数”对话框，如图6-17所示。,图6-17 “函数参数”对话框,6.4 函 数,6.4.6 逻辑函数,使用函数向导输入公式的操作步骤如下：,(2) 选中Logical_test文本框，

30、单击B2单元格再在输入框中输入“=80”，选中Value_true文本框，直接输入“优”，可以不输入双引号，系统自动添加；选中Value_if_false文本框，直接输入IF( )。,(3) 单击编辑栏中内嵌的IF( )函数名，即外层IF函数的第三个参数的位置，进入内层的IF函数“函数参数”输入对话框，输入对应参数。,(4) 返回外层的IF函数，只需在“编辑栏”中单击外层IF函数的名的位置。,6.4 函 数,6.4.6 逻辑函数,使用函数向导输入公式的操作步骤如下：,(5) 如果又要进入内层IF函数，再单击内层IF函数名。,(6) 检查无误后，按“确定”按钮或单击编辑栏上的按钮结束公式的输入。

31、,操作技巧,公式输入以后，发现有误，又希望用向导修改，这时可选择公式所在的单元格，直接单击“插入函数”按钮，再次打开“函数参数”对话框。修改后，按“确定”按钮结束修改。对于其他内嵌的函数也可按照此方法输入，做到举一反三。,6.4 函 数,6.4.7 频率分布函数,格式：FREQUENCY(data_array,bins_array) 功能：按照bins_array参数设置的间隔，计算data_array参数所在数据的频率分布。,任务13,打开“公式函数”工作簿，选择“频率分布函数”表，统计“计算机”分数在以下分数段的人数，60分以下(即不及格)、6069、7079、8089、9099、100分

32、。,6.4 函 数,6.4.7 频率分布函数,操作步骤,(1) 在H3:H7区域内依次输入间隔点59、69、79、89、99，注意不是60、70、80、90、100，因为每个间隔点的含义是小于或等于，如果设置为60而不是59的话，会将60分当作不及格统计，如图6-18所示。,图6-18 FREQUENCY函数应用,6.4 函 数,6.4.7 频率分布函数,操作步骤,(2) 选择I3:I8区域(比分隔区域向下多一个单元格)，在编辑栏输入“=FREQUENCY (E3:E17,H3:H8)” (3) 按Ctrl+Shift+Enter组合键。结果显示在I3:I8区域，其中I8单元格指的是99分以上的学生人数。,Thank You !,