第6章 计算Excel表格数据

第6章计算Excel表格数据学习目标学习要点◎公式的输入与编辑◎函数的输入与插入◎函数的嵌套◎常见函数的使用学习目标◎了解Excel中的公式和函数◎掌握单元格引用的含义◎掌握公式的输入与编辑方法◎掌握函数的输入与插入方法◎了解嵌套函数的使用◎熟悉常用函数的应用方法主要内容使用公式计算数据1使用函数计算数据2常见函数应用3课堂练习4使用公式计算数据输入公式单元格引用认识Excel公式编辑公式课堂案例1——计算员工工资表数据认识Excel公式什么是Excel公式在Excel中，公式遵循一个特定的语法或次序：最前面是等号“=”，后面是参与计算的元素和运算符。每个元素可以是常量数值、单元格，或引用单元格区域、标志、名称，如下图所示。单元格引用相对引用Excel2010中默认情况下使用的都是相对引用，在相对引用中，被引用单元格的位置与公式所在单元格的位置相关联，当公式所在单元格的位置改变时，其引用的单元格的位置也会发生相应变化，如下图所示。单元格引用绝对引用选择需进行绝对引用单元格编辑框中的公式内容，按【F4】键将公式转换为绝对引用，如下图所示。单元格引用混合引用混合引用是指一个单元格地址的引用中，同时存在相对引用与绝对引用。如果公式所在单元格的位置改变，则公式中相对引用地址也会随之改变，而绝对引用地址保持不变，如下图所示。单元格引用引用同一工作簿中不同工作表的单元格如果要引用同一工作簿中其他工作表的单元格或单元格区域，可在公式输入状态下，单击需引用对象所在的工作表标签，拖曳鼠标选择目标对象，按【Enter】键即可。其格式为：工作表名称！单元格地址。输入公式选择要输入公式的单元格，在编辑框中输入“=”，进入公式输入状态，然后在英文状态下逐次输入公式所需的内容，完成后按【Enter】键或单击编辑框左侧的“输入”按钮即可。编辑公式修改公式移动公式复制公式删除公式将文本插入点定位到编辑框的公式中，按修改数据的方法修改公式内容，按【Enter】键或【Ctrl+Enter】组合键确认修改选择公式所在的单元格，按【Ctrl+C】组合键，选择目标单元格，按【Ctrl+V】组合键选择公式所在的单元格，拖曳该单元格的边框至目标单元格。移动公式后，公式内容不会发生改变选择公式所在的单元格，按【Delete】键LOGO课堂案例1——计算员工工资表数据利用公式计算每位员工的工资数据，然后计算所有员工的工资总和与平均工资数据，完成后的参考效果如下图所示。使用函数计算数据使用嵌套函数输入与插入函数认识Excel函数课堂案例2——计算销量汇总表数据认识Excel函数什么是Excel函数函数是Excel中一些预定好的公式，可以通过设置参数来执行计算操作，其参数可以是数字、文本、单元格引用或其他的公式、函数等。在描述函数时会涉及到语法结构，其语法结构为：“=函数名(参数1,参数2,…)”，如下图所示。输入与插入函数选择需插入函数的单元格，在编辑框左侧单击“插入函数”按钮，或在【公式】→【函数库】组中单击“插入函数”按钮。打开“插入函数”对话框，在“或选择类别”下拉列表框中选择函数类别，如“数学与三角函数”选项，在“选择函数”列表框中选择某种函数，单击确定按钮。打开“函数参数”对话框，将文本插入点定位到其中某个参数文本框，拖曳鼠标选择工作表中参与计算的单元格或单元格区域（也可直接输入参数内容），单击确定按钮即可插入函数，如下图所示。使用嵌套函数将文本插入点定位到编辑框的函数中某个需创建参数的位置，单击名称框右侧的下拉按钮，在打开的下拉列表中选择需插入的嵌套函数选项，然后在打开的“函数参数”对话框中设置嵌套函数的参数，并确认设置，如下图所示。课堂案例2——计算销量汇总表数据通过输入的方式，利用求和函数SUM计算各产品的销量总和数据，然后以插入的方式利用求平均值函数AVERAGE计算所有产品的平均销量，完成后的参考效果如下图所示。常见函数应用COUNT函数MAX/MIN函数AVERAGE函数IF函数INT函数SUM函数课堂案例3——计算销量统计表数据SUM函数使用SUM函数的注意事项(1)参数的数量范围为1~30个。(2)若参数均为数值，则直接返回计算结果，如SUM(10,20)，将返回“30”。(3)若参数中包含文本数字和逻辑值，则会将文本数字判断为对应的数值，将逻辑值TURE判断为“1”。如SUM(10,20,TRUE)将返回“31”。(4)若参数为引用的单元格或单元格区域的地址，则只计算单元格或单元格区域中为数字的参数，其他如空白单元格、文本、逻辑值、错误值都将被忽略。AVERAGE函数AVERAGE函数为求平均值函数，属于统计函数，能返回所有参数的算术平均值，其语法格式为AVERAGE(Number1,Number2,Number3,…)。MAX/MIN函数MAX函数为最大值函数、MIN函数为最小值函数，均属于统计函数，能分别返回所有参数的最大值或最小值，其语法格式为MAX(Number1,Number2,Number3,…)或MIN(Number1,Number2,Number3,…)。COUNT函数使用COUNT函数的注意事项(1)如果参数为数字、日期或代表数字的文本（如用引号引起的数字，如1），则将被计算在内。(2)逻辑值和直接输入到参数列表中代表数字的文本将被计算在内。(3)如果参数为错误值或不能转换为数字的文本，不会被计算在内。(4)如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计算数组或引用中的空单元格、逻辑值、文本、错误值。IF函数使用IF函数的注意事项(1)Logical_test为IF函数的第一参数，作用是IF函数判断的参照条件。(2)Value_if_true为IF函数的第二参数，表示当IF函数判断Logical_test成立时返回的值。(3)Value_if_false为IF函数的第三参数，表示当IF函数判断Logical_test不成立时返回的值。(4)第二参数可以省略，此时若应该返回第二参数的值时，则返回“0”。(5)第三参数可以省略，此时若应该返回第三参数的值，则有两种情况：一是若第三参数前面的“,”一起省略，则将返回TRUE；二是若“,”未省略，则将返回“0”。INT函数使用INT函数的注意事项(1)此函数只会返回小于或等于它的整数。如INT(2.9)则将返回“2”；INT(-8.6)则将返回“-9”。(2)此函数的参数可以为单元格引用。即INT(A3)，根据A3单元格的数据进行取整。课堂案例3——计算销量统计表数据利用多种函数计算销量统计表中的数据，包括统计数量、最高/最低销售数量、销售总价、销售平均价、销售情况等，完成后的参考效果如下图所示。课堂练习计算员工实发工资数据计算员工项目提成数据计算员工项目提成数据练习目标本练习的目标是计算两名员工的销售数据，其中将主要涉及到公式的应用、填充，以及引用不同工作表中的单元格等操作。本练习完成后的参考效果如下图所示。计算员工项目提成数据操作思路计算员工实发工资数据练习目标本练习的目标是利用公式与函数计算员工工资的相关数据，其中将主要涉及函数与嵌套函数的使用。本练习完成后的参考效果如下图所示。计算员工实发工资数据操作思路课后习题（1）在产品库存表中利用公式计算每种产品的本月库存量，然后利用合适的函数计算所有产品平均库存量、最小库存量、最大库存量，完成后的参考效果如下图所示。课后习题（2）在培训成绩表中分别计算各员工的培训成绩总分，然后利用函数进行等级判断，标准为大于85分者，等级为“优秀”，等于或低于85分者，等级为“一般”，完成后的参考效果如下图所示。