2.5使用公式和函数（课件）-《信息技术》同步教学（大连理工大学出版社）

项目2 WPS Office 2019表格处理 工作情境 电子表格处理是信息化办公的重要组成部分，在数据分析和处理中发挥着重要的作用，广泛应用于财务、管理、统计、金融等领域。WPS Office 2019 表格是一款优秀的电子表格制作软件，不仅能满足日常办公的需要，还可以通过函数实现专业的数据处理。它支持超过900多个函数计算，具有条件表达式、排序、自动填充、多条件筛选、统计图表等丰富的功能。WPS表格的Docer（稻壳儿）表格模板提供了大量常用的工作表模板，可以帮助用户快速地创建各类工作表格，高效地实现多种数据计算功能。WPS表格能够输出PDF格式文档，或另存为其他格式文档，并兼容Microsoft Office Excel文件格式，方便文件的交流和共享。 项目2工作任务 数据的输入、工作表和工作簿的基本操作 公式和函数的使用 数据统计和管理 图表的创建和应用 数据透视表和数据透视图 页面设置与打印输出 4 5 6 1 3 2 项目2知识目标 理解WPS表格的数据、单元格、工作表和工作簿的概念和格式 理解WPS表格的单元格引用、公式和函数 理解图表 理解数据透视表和数据透视图 4 5 6 1 3 2 项目2技能目标 了解电子表格的应用场景 熟练掌握WPS表格的数据输入、工作表和工作簿的基本操作 熟练掌握单元格格式编辑，数据排序、筛选及分类汇总等操作 熟练掌握公式和常用函数的使用 了解常见的图表类型，掌握图表的建立与编辑 掌握数据透视表的创建与更新操作，能利用数据透视表创建数据透视图 掌握WPS表格的页面设置、打印及PDF输出 4 5 6 1 3 2 项目2重点和难点 重点 难点 不同类型数据的输入方法 单元格的引用、公式和函数的应用 数据的统计和分析 图表的应用 数据透视表和透视图的应用 项目2任务安排 任务2 掌握工作簿的基本操作 任务1 认识WPS Office 2019表格 任务3 掌握单元格的基本操作 任务4 管理工作表 任务5 使用公式和函数 任务7 制作图表 任务8 制作数据透视表和数据透视图 任务9 页面布局和打印输出 任务6 数据管理 任务2-5 使用公式和函数 使用公式 子任务2-5-1 任务2-5 使用公式和函数 子任务2-5-1 使用公式 1．公式的格式与录入 公式是WPS表格的计算式，以等号“=”开头，用一个或多个运算符将常量、单元格地址、函数等连接起来的有意义的表达式。如图所示，各办公用品的总价=数量*单价（公式中的乘号用“*”表示），因此G3=D3*F3。 录入公式时要先选定要输入公式的单元格，在编辑栏中输入“=”（会在编辑栏和单元格中同时显示），接着依次输入公式中各个字符（涉及到单元格引用的，可以直接单击相应的单元格，其地址会自动填入到编辑栏的光标位置），输入完毕后单击编辑栏的确认按钮“√”或按Enter键，公式计算结果会显示在单元格中。 修改公式时，可以双击单元格或在编辑栏进行，修改完毕后，单击确认按钮“√”或按Enter键结束。 操作演示 任务2-5 使用公式和函数 子任务2-5-1 使用公式 2．运算符 在WPS表格中，用运算符把常量、单元格地址、函数及括号等连接起来就构成了表达式。常用的运算符除了加、减、乘、除等算术运算符外，还有字符连接符、关系运算符和引用运算符等。运算符在计算时具有优先级，优先级高的先算，比如先乘除，后加减。表中按运算符优先级从高到低列出了常用的运算符及其功能。 运算符 功能 示例 : 冒号，区域运算符，产生对包括在两个引用之间的所有单元格的引用 如：A1值为1，A2值为2 =SUM(A1:A2)的值为3 注：SUM()为求和函数，将在函数部分作详细介绍   空格，交叉运算符产生对两个引用共有的单元格的引用 如：A1值为1，A2值为2，B2值为3，则 =SUM(A1:A2 A2:B2)的值为2 , 逗号，联合运算符，将多个引用合并成一个引用 如：A1值为1，A2值为2，B2值为3，则 =SUM(A1:A2,A2:B2)的值为8 - 负号 -8，-D1 % 百分比 20% 即0.2 ^ 乘方 2^3 即23，值为8 *和 / 乘和除 C1*5，D1/3 +和– 加和减 C2+50，D1-B1 =，< > 等于，不等于 4=5的值为FALSE，4<>5的值为TRUE >，>= 大于，大于等于 5>4的值为TRUE，5>=4的值为TRUE <，<= 小于，小于等于 5<4的值为FALSE，5<=4的值为FALSE & 将两个文本值连接成一个新的文本值 "WPS"&"2019"的值为"WPS2019" 任务2-5 使用公式和函数 子任务2-5-1 使用公式 3．公式的种类 （1）算术公式：其值为数值的公式。 例如，=5*4/2^2-A1，其中A1的值是12，结果是-7。 （2）文本公式：其值为文本数据的公式。 例如：=E2&"2019"，其中B2的值是“WPS”，结果为“WPS2019”。 （3）比较公式（关系式）：其值为逻辑值TRUE（真）或FALSE（假）的公式。 例如：=5>4，结果为TRUE；=5<4，结果为FALSE。 任务2-5 使用公式和函数 子任务2-5-1 使用公式 4．相对引用 当把一个含有单元格或单元格区域地址的公式复制到新的位置时，公式中的单元格地址或单元格区域会随着相对位置的改变而改变，公式的值将会依据改变后的单元格或单元格区域的值重新计算。例如，在图中，G3的值是通过公式“=D3*F3”计算得出的，D3和F3相对G3的位置，简单来说，就是G3左侧的第三个单元格乘以左侧第一个单元格。 如果将公式“=D3*F3”复制到G4单元格，G4到G3的位置变化规律会同样作用到D3和F3上，使公式中的D3和F3变化为D4和F4，也就是说，公式“=D3*F3”复制到G4单元格后，公式会变为“=D4*F4”，这种自动变化也正好和G4单元格值的计算公式相同。 操作演示 任务2-5 使用公式和函数 子任务2-5-1 使用公式 5．绝对引用 在左图中，B5的计算公式为“=B4/E2”，即本分数段人数除以班级总人数，结果是正确的，但如果将这个公式复制到C5，按照上述“相对引用”的变化规则，C5的计算公式为“=C4/F2”，如右图所示，显然分母是错误的，无法得到正确的结果。同样将B5的公式复制到D5、E5，结果都是错误的。其出错的原因是分母作为班级人数应是不变的，也就是说，在复制公式时分母的值都应是对E2单元格的绝对引用。 任务2-5 使用公式和函数 子任务2-5-1 使用公式 5．绝对引用 如果希望公式复制后引用的单元格或单元格区域的地址不发生变化，那么就必须采用绝对引用。所谓绝对引用，是指在公式中的单元格地址或单元格区域的地址不会随着公式引用位置的改变而发生改变。其格式是在行号或列标的前面加上一个“$”符号就可以将它改为绝对引用的地址。 例如，在图中，将B5的公式改为“=B4/$E$2”，复制公式后，所有“占比”的计算结果都正确了。 操作演示 任务2-5 使用公式和函数 子任务2-5-1 使用公式 6．混合引用 如果把单元格或单元格区域的地址表示为部分是相对引用，部分是绝对引用，如行号为相对引用、列标为绝对引用，或者行号为绝对引用、列标为相对引用，这种引用称为混合引用。例如，单元格地址“=$B3”和“=A$5”，前者表示保持列不发生变化，而行会随着公式行位置的变化而变化，后者表示保持行不发生变化，而列标会随着公式列位置的变化而变化。 操作演示 任务2-5 使用公式和函数 子任务2-5-1 使用公式 7. 跨工作表引用单元格或单元格区域 跨工作表引用是指在当前工作表中引用其他工作表内的单元格或单元格区域。引用格式是：工作表名!单元格引用。 例如：在Sheet2中引用Sheet1表中的B2单元格，就可以在Sheet2的公式中用“Sheet1!B2”表示。 如果引用的单元格区域，比如在Sheet2中引用Sheet1表中的B2:E5的单元格区域，可以在Sheet2的公式中用“Sheet1!B2:E5”表示。 操作演示 任务2-5 使用公式和函数 子任务2-5-1 使用公式 8. 三维引用 三维引用是指引用连续的工作表中同一位置的单元格或单元格区域。就像多张工作表整齐叠在一起的三维立体，用刻刀从上往下刻穿后在每张纸上都留下刻痕的效果。 引用格式是：工作表名称1:工作表名称2!单元格引用 例如：在Sheet5中要引用Sheet1～Sheet3表中的B2单元格，就可以在Sheet5的公式中用“Sheet1:Sheet3!B2”表示。 操作演示 任务2-5 使用公式和函数 使用函数 子任务2-5-2 任务2-5 使用公式和函数 子任务2-5-2 使用函数 WPS表格内置的函数是预先定义的执行计算、分析等处理数据任务的特殊公式。它包括了财务、日期与时间、数学和三角函数、统计、查找与引用、数据库、文本、逻辑等多个方面。熟练地使用函数可以有效提高数据处理速度。 1．函数结构 函数由函数名和相应的参数组成，其格式为：函数名([参数1], [参数2],……) 例如： SUM(A1:A2)函数，SUM为函数名，A1:A2为参数，是一个单元格区域的引用。SUM()函数是一个求和函数，其功能是将各参数求和。 函数名及其功能由系统规定，用户不能改变，参数放在函数名后的圆括号内；参数可以是一个或多个，多个参数之间用逗号分隔。参数的类型可以是数值、名称、数组或是包含数值的引用。 也有个别函数没有参数，称为无参函数。对于无参函数，函数名后面的圆括号不能够省略。例如，NOW()函数就没有参数，它返回的是系统内部时钟的当前日期与时间。 任务2-5 使用公式和函数 子任务2-5-2 使用函数 2．输入函数 当用户对某个函数名及使用很熟悉时，可以像输入公式那样直接输入函数。 通常可以使用函数向导来引导输入，其操作步骤如下： ① 选定需输入函数的单元格，例如D22。 ② 在编辑栏输入“=”，随着函数名字母的逐个输入，系统会逐步给出更精确的提示，如图所示，直到显示出要使用的函数后，直接从提示框中双击该函数，该函数会录入到编辑栏中，供编辑公式使用。在选择函数的时候还可以看到该函数的功能及使用技巧的视频介绍。 操作演示 任务2-5 使用公式和函数 子任务2-5-2 使用函数 2．输入函数 或者在编辑栏中输入“=”后，单击左侧的“ ”图标，弹出“插入函数”对话框，如左图所示。通过查找或分类选择到要用的函数后，双击该函数，进行参数设置框，如右图中可以直接输入计算范围；也可以单击数值框右侧的“区域选择”按钮，从工作表中选择计算范围，选择完成后按Enter键，最后单击“确定”按钮。 操作演示 任务2-5 使用公式和函数 子任务2-5-2 使用函数 3．自动求和 系统提供了自动求和的功能，是位于在“开始”选项卡功能区的“求和”命令。利用它可以对工作表中所选定的单元格进行自动求和，它实际上相当于SUM求和函数，但比插入函数更方便。如果单击下三角按钮，还可以求平均值、计数、最大值、最小值和插入其他函数。如图所示。 任务2-5 使用公式和函数 子任务2-5-2 使用函数 3．自动求和 在右表中，通过复制公式的方式将所有记录的“总价”计算出来。选定G22单元格，单击上一页图中的“求和”选项，系统会自动填入公式并判断出求和区域，如图所示。 这里的求和区域“G3:G21”是正确的（如果不正确，这时也可以手工修改），单击“√”图标或按Enter键，G22单元格中会显示求和结果。 操作演示 任务2-5 使用公式和函数 子任务2-5-2 使用函数 3．自动求和 除了以上方法外，还有两种情况需要说明： ① 单行或单列相邻单元格的求和：先选定要求和的单元格行或单元格列，然后单击 “求和”按钮，求和结果将自动放在选定行的右方或选定列的下方单元格中。 例如，对单元格区域A1:A4进行求和，首先选定单元格区域A1:A4，单击“求和”按钮，求和结果会显示在A5中。 ② 多行多列相邻单元格的求和：先选定需要求和的多行多列单元格区域，单击“求和”按钮，求和结果会显示在每列底部的对应单元格中。 任务2-5 使用公式和函数 子任务2-5-2 使用函数 4. 常用函数 WPS表格中内置的函数很多，表中列出了最常用的八个函数。 函数格式 功能介绍 负号 -8，-D1 百分比 20% 即0.2 SUM(参数1,[参数2]…) 返回所有参数的和 AVERAGE(参数1,[参数2]…) 返回所有参数的平均值 MAX(参数1,[参数2]…) 返回所有参数中的最大值 MIN(参数1,[参数2]…) 返回所有参数中的最小值 COUNT(参数1,[参数2]…) 返回所有参数中数值型数据的个数 ROUND(参数1,参数2) 将参数1四舍五入，保留参数2位小数 INT(参数) 返回一个不大于参数的最大整数值（即取整） ABS(参数) 返回参数的绝对值 任务2-5 使用公式和函数 子任务2-5-2 使用函数 5. 函数嵌套 函数嵌套是指把一个函数作为另外一个函数的参数来使用，以完成更为复杂的计算需求。WPS表格中函数最多可以有65级嵌套。 例如：ROUND(AVERAGE(MAX(A1:D5),MIN(A1:D5)),2) 这是一个三重的函数嵌套，意思是将A1:D5区域的最大值和最小值求平均值，对这个平均值四舍五入，保留两位小数。 $$