4.3 Excel公式与函数（ 课件）——《计算机应用基础》同步教学（西南财经大学出版社）

计算机应用基础 1 计算机基础知识 2 Windows 操作系统 3 Word 2010文字编辑 4 Excel 2010电子表格 5 PowerPoint2010演示文稿 6 计算机网络基础 7 Internet 应用 8 计算机安全 9 多媒体技术 11:50:55 计算机应用基础 3 第4章 Excel 2010电子表格 计算机应用基础 计算机应用基础 1 Excel 2010基本知识 2 工作表的建立与编辑 目 录 CONTENTS 第4章 Excel 2010电子表格 制作Excel图表 4 3 Excel公式与函数 5 数据管理与分析 计算机应用基础 4 第4章 Excel 2010电子表格 Excel的主要功能 1．制作电子表格（工作表 ） 2．数据的计算、分析、处理 3. 图表制作 4．数据库管理 计算机应用基础 5 4.3 Excel公式与函数 公式和函数是Excel最常用的功能之一，运用公式和函数可以对单元格中的数据进行计算，当单元格中的数据发生变动时，公式和函数也会自动更新计算结果。 计算机应用基础 6 6 4.3 Excel公式与函数 4.3.1 使用公式计算 1．运算符 Excel中公式是一个运算表达式，由运算符和运算对象构成。 运算对象包括：常量数值、单元格引用、公式和函数。 运算符包括：算术、关系、文本连接和引用四种。 计算机应用基础 7 优先级由高到低次序为：引用运算符、算术运算符（负号、百分比、乘方、乘除、加减）、文本运算符、比较运算符。 7 4.3 Excel公式与函数 4.3.1 使用公式计算 2．输入公式 在编辑栏或单元格中输入公式，必须以“=”或“+”开头。 如计算图中白玲的应发工资，先选定H3单元格，再输入公式“=E3+F3+G3”，单击“ ”按钮或按Enter键完成编辑。在编辑栏显示公式，而在H3单元格中显示计算的结果。 计算机应用基础 8 8 4.3 Excel公式与函数 4.3.2 单元格引用 单元格引用表示单元格在工作表中所处位置的坐标，也就是说，单元格的名称出现在公式中作为变量使用，这种使用方式称为单元格引用。如前例公式中的“E3”、“F3”等。若修改单元格中的内容，引用该单元格的公式的值也要相应改变。 单元格引用分为相对引用、绝对引用和混合引用三种。 计算机应用基础 9 9 4.3 Excel公式与函数 4.3.2 单元格引用 1. 相对引用 直接用“列号+行号”表示单元格地址，如“E3”。单元格相对引用时，若将单元格中的公式复制或自动填充到其它单元格后，新公式中引用单元格的坐标也会相对地发生改变。 2. 绝对引用 分别在列号和行号前面加“$”字符表示单元格地址，如“$D$4”。单元格绝对引用时，若将单元格中的公式复制或自动填充到其它单元格后，新公式中引用的单元格是固定不变的，与公式所在的位置无关。 3. 混合引用 只在行号或者只在列号前加“$”字符表示单元格地址，如“$D4”、“D$4”等。当公式被复制或填充到其它单元格时，加“$”部分为绝对引用，未加“$”部分为相对引用。 计算机应用基础 10 10 4.3 Excel公式与函数 4.3.2 单元格引用 如果需要引用同一工作簿中其它工作表的单元格，则在引用的单元格前加上“工作表名称！”，如公式“=sheet1!A4+sheet2!B6”，表示工作表sheet1的A4单元格和sheet2的B6单元格求和。 若是同一工作簿中连续工作表的区域引用，则引用格式为：“起始引用工作表名称:结束引用工作表名称！”，如公式“=SUM (sheet2: sheet6!E5)”表示将“sheet2”至“sheet6”的所有工作表的E5单元格求和。 计算机应用基础 11 11 4.3 Excel公式与函数 4.3.3 常用函数的使用 函数是一些预定义的公式，通过使用一些被称为参数的特定数值，按特定的规则进行计算。 函数由函数名和参数两个部分构成，基本格式为： 函数名(参数1、参数2 …) 计算机应用基础 12 12 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 1）SUM函数 功能：返回某一单元格区域中所有数字之和。 语法：SUM(number1, number2 ...) 2）AVERAGE函数 功能：返回参数平均值（算术平均）。 语法：AVERAGE(number1, number2 ...) 3）COUNT函数 功能：返回单元格区域中数字项的个数。 语法：COUNT(value1, value2 ...) 计算机应用基础 13 13 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 4）MAX/MIN函数 功能：返回一组数中的最大值/最小值。 语法：MAX/MIN(number1, number2 ...) 说明：以上五个函数，参数表中直接出现的数字、逻辑值及数字文本都将被计算（数字文本转换为等值的数值；逻辑值TRUE转换为1，FALSE转换为0）；若参数为单元格引用，则只有其中的数字会被计算，引用的空白单元格、逻辑值、文本或错误值都将被忽略。 计算机应用基础 14 14 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 【例4-1】有如图所示的一组数据，计算平均值和最小值，计算结果如下： AVERAGE(100,TRUE,"10",50)=40.25 AVERAGE(A1:A4)=75 MIN(100,TRUE,"10",50)=1 MIN(A1:A4)=50 COUNT(A1:A4)=2 COUNT(100,TRUE,"10",50)=4 计算机应用基础 15 15 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 5）SUMIF函数 功能：根据指定条件对若干单元格求和。 语法：SUMIF(range, criteria, sum_range) 计算机应用基础 16 条件判断的单元格区域 被相加求和的条件 需求和的实际单元格区域 16 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 5）SUMIF函数 【例4-2】如图所示的一组数据，在E9单元格中求出“所有讲师的基本工资总和”。 =SUMIF(D3:D8,"讲师",E3:E8) ，结果显示1100。 计算机应用基础 17 17 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 6）COUNTIF函数 功能：计算给定区域内满足特定条件的单元格的数目。 语法：COUNTIF(range, criteria) 计算机应用基础 18 待统计的单元格区域 统计的条件 18 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 6）COUNTIF函数 【例4-3】如图所示数据表。 求讲师的人数，公式为：=COUNTIF(D3:D8,"讲师")，结果2。 求出所有教师中基本工资超过500的人数，公式为：=COUNTIF(E3:E8,">500")，结果3。 计算机应用基础 19 19 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 7）ROUND函数 功能：返回某个数字按指定位数舍入后的数字。 语法：ROUND(number, num_digits) number表示需要进行舍入的数字。 num_digits指定位数，按此位数进行舍入。 如果num_digits大于0，则舍入到指定的小数位。 如果num_digits等于0，则舍入到最接近的整数。 如果num_digits小于0，则在小数点左侧进行舍入。 例： ROUND(3.1415629,3)=3.142； ROUND(-2.5241,0)=-3； ROUND(14.435,-1)=10。 计算机应用基础 20 20 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 8）IF函数 功能：执行真假值判断，根据逻辑测试的真假值返回不同的结果。 语法：IF(logical_test, value_if_true, value_if_false) 计算机应用基础 21 逻辑测试表达式 测试结果为真返回的值 测试结果为假返回的值 21 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 8）IF函数 【例4-4】如图所示数据表，若将奖金划分成350元以上为甲级，否则为乙级，计算“白玲的奖金等级”。 =IF(G3>=350,"甲级","乙级")”，结果为“乙级”。 计算机应用基础 22 22 4.3 Excel公式与函数 4.3.3 常用函数的使用 1．常用函数 9）RANK函数 功能：返回某数字在一列数字中相对于其他数值的大小排名。 语法：RANK(number, ref, order) 【例4-5】如果A1:A5中分别含有数字7、3、3、1和2，则： RANK(A2,A1:A5)=2 RANK(A1,A1:A5,1)=5 说明：该函数对重复数的排位值相同，后续数值的排位为实际排位值。 计算机应用基础 23 需排位的数值 一组数值的数组或引用 指明排位的方式 0或省略，降序 非0，升序 23 4.3 Excel公式与函数 4.3.3 常用函数的使用 2．函数的输入 在Excel 2010中输入函数，有以下常用方法： （1）单击编辑栏上的“插入函数”按钮，弹出“插入函数”对话框，选择要插入的函数。 （2）在“公式”选项卡的“函数库”分组中，单击“插入函数”按钮，打开“插入函数”对话框；或按函数分类进行选择。 计算机应用基础 24 24 4.3 Excel公式与函数 4.3.3 常用函数的使用 2．函数的输入 【例4-6】求图中每位教师的应发工资。 计算机应用基础 25 25 4.4 制作Excel图表 4.4.1 图表类型 将数据转化成图表可以更直观地显示和比较数据。Excel 2010的图表类型有柱形图、折线图、饼图、条形图、面积图、散点图、股价图、曲面图、圆环图、气泡图和雷达图，每种类型的图表还有若干种子图表类型。各种图表都有其特点，用户可以根据需求选择相应的图表类型。 计算机应用基础 26 26 4.4 制作Excel图表 4.4.2 创建图表 在工作簿中创建图表，可分为“嵌入式图表”和“独立图表”两种。 嵌入式图表是图表作为工作表的一部分，即与数据混放在同一个工作表中； 独立图表亦称图表工作表，是工作簿中一个独立的具有工作表名称的独立图表，只包含图表。 计算机应用基础 27 27 4.4 制作Excel图表 4.4.2 创建图表 1．创建独立图表 选择要制作图表的数据区域，按F11键即可快速创建独立图表，默认采用二维柱形图类型。 2．创建嵌入式图表 计算机应用基础 28 28 4.4 制作Excel图表 4.4.3 编辑图表 当图表创建完成后，可以对图表中各个部分进行修改编辑。单击图表，功能区部分出现“图表工具”，下设“设计”、“布局”和“格式”三个选项卡，可以完成图表的编辑。如修改图表类型、图表样式、添加图表标题、坐标轴标题、数据标签设置、图例设置、图表格式设置等。 数据源改变，对应的图表如何变化？！（添加、删除） 计算机应用基础 29 29 4.5 数据管理与分析 　　Excel可以对表格中的数据进行管理与分析。数据管理与分析操作包括数据排序、数据筛选、数据的分类汇总、数据透视表等。 计算机应用基础 30 30 4.5 数据管理与分析 4.5.1 数据排序 数据排序是指对数据表中的一列或多列数据按升序或降序排列。 （1）快速排序：选择待排序列中的任意一个单元格，单击“数据”选项卡→“排序和筛选”分组→“升序 ”或“降序 ”按钮进行排序。 （2）多条件排序：以多个字段作为关键字对数据表进行排序。其排序的原理是，首先以主关键字为排序依据，当主关键字的值相等时，则以次关键字为依据排序，依次类推。 计算机应用基础 31 31 4.5 数据管理与分析 4.5.2 数据筛选 数据筛选是指在数据表中，筛选出符合条件的记录，不符合条件的记录暂时被隐藏起来，一旦筛选条件被取消，这些数据又重新出现。 1．自动筛选 自动筛选适用于简单的筛选条件。选中数据表任意单元格，单击“数据”选项卡→“排序和筛选”分组→“筛选”按钮，此时，单击列标题后出现的三角按钮打开下拉列表即可设置筛选条件。 2．高级筛选 高级筛选适用于复杂的筛选条件。 注意：高级筛选的条件区域应该至少有两行。第一行用来放置列标题；下面的行则放置筛选条件，同一行的条件认为是“与”条件，而不同行的条件认为是“或”条件。 计算机应用基础 32 32 4.5 数据管理与分析 4.5.3 数据分类汇总 分类汇总是指以某一个字段为分类项，对数据列表中的数值字段进行各种统计计算，如求和、计数、求平均值和最大值等。 分类汇总前必须先依据分类字段进行排序。 分类汇总表中，数据是分级显示的。工作表左上角出现的三个按钮 用来控制显示数据级别。 “1”：显示总计结果； “2”：显示各个分类汇总结果及总计结果； “3”：显示所有详细数据。 计算机应用基础 33 33 4.5 数据管理与分析 4.5.4 页面设置与打印 对工作表进行打印前，要先进行页面设置，合理地布局页面；使用打印预览快速浏览页面及查看打印效果；设置打印区域及相关参数。 1．页面设置 页面设置主要包括页面、页眉与页脚、页边距和工作表设置等。 2．打印工作表 单击“文件”选项卡→“打印”命令，显示Excel打印和打印预览窗格。设置打印选项，单击“打印”按钮，即可打印输出工作表。 计算机应用基础 34 34 $$