专题二 使用公式和函数考点（运算符号、单元格引用）（讲义）-高职分类考试《计算机基础》专题复习讲练测（宁夏）

专题一：计算表格数据——使用公式和函数考点（运算符号、单元格引用） [考纲要求] 1、掌握在单元格中输入公式及利用填充柄复制公式的方法。要输入公式，必须先输入“=”，然后在其后输入表达式。 2、掌握使用常用函数计算工作表数据的方法。 3、 能够利用工作表成组功能，在多个工作表的相同位置输入和编辑相同数据。 4、能够根据需要自定义单元格区域数据格式。 [课时安排] 4课时 [知识梳理] EXCEL强大得计算功能主要依赖于其公式和函数，利用它们可以对表格中得数据进行各种计算和处理。 一、认识公式和函数 1、公式 公式由运算符和参与运算的操作数组成。 运算符可以是算术运算符、比较运算符、文本运算符和引用运算符 操作数可以是常量、单元格引用和函数等。 要输入公式必须先输入“=”，然后再在其后输入运算符和操作数，否则EXCEL会将输入得内容作为文本型数据处理 图7-47a:求A2单元格与B5单元格之积再除以B6单元格后加100的值。 图7-47b：使用函数average求A2:B7单元格区域的平均值，并将求出的平均值乘以A4单元格后再除以3。 2、函数 函数是预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数组成，其中函数名表示将执行的操作（如求平均值函数average），参数表示函数将使用的值的单元格地址。 二、公式中的运算符 运算符是用来对公式中的元素进行运算而固定的特殊符号。在Excel中包含四种类型的运算符，算术运算、比较运算、文本运算和引用运算。 1、算术运算符。 算术运算符有6个，如表7-1所示，其作用是完成基本的数学运算并产生数字结果。 2、比较运算符。 比较运算符有6个，如表7-2所示，它们的作用是比较两个值，并得出一个逻辑值，即TRUE真或FALSE假。 3、文本运算符。 使用文本运算符“&”（与号）可将两个或多个文本值串起来产生一个连续的文本值。例如：输入“祝你”&“快乐、开心！”会生成“祝你快乐、开心！”。 4、引用运算符。 引用运算符有3个，如表7-3所示，他们的作用是对单元格区域中的数据进行合并计算。 三、单元格引用 单元格引用，用来指明公式中所使用的数据的位置。它可以是一个单元格地址，也可以是单元格区域。 1、相同或不同工作簿、工作表中的引用 对于同一工作表中的单元格引用，直接输入单元格或单元格区域地址即可。 在当前工作表中，引用同一工作簿不同工作表中的单元格的表示方法为， 工作表名称！单元格（或单元格区域）地址 例如，sheet!F8:F16表示引用sheet2工作表中F8:F16单元格区域的数据。 在当前工作表中引用不同工作簿的单元格的表示方式为 【工作簿名称.xlsx】工作表名称！单元格（或单元格区域）地址 注意：引用某个单元格区域时，应先输入单元格区域起始位置的单元格地址，然后输入引用运算符，再输入单元格区域结束位置的单元格地址。 2、相对引用、绝对引用和混合引用 相对引用：相对引用是excel默认的单元格引用方式，他直接用单元格的列标和行号表示单元格，如B5，或用引用运算符表示单元格区域，如B5：D15。在移动或复制公式时，系统会根据移动的位置自动调整公式中引用单元格地址。 绝对引用：绝对引用是指在单元格的列标和行号前面都加上“$”符号，如$B$5。不论将公式复制或移动到什么位置，绝对引用的单元格地址都不会改变。 混合引用：混合引用是指引用中既包含绝对引用又包含相对引用，如A$1或$A1等，用于表示列变行不变或列不变行变的引用。 四、实际操作 任务一 计算水费开支表数据 情景引入：小陈是房东，他的租客有10个人，已经连续两个月没交水电费了。为此，他统计了所有户主这两个月的电费和水费，准备上门收缴。 制作思路：输入工作表基本数据——使用公式计算工作表数据——计算水电费合计值 用电量=本月表底—上月表底 电费=用电量*电费单价 电费合计=每户电费之和 水费同理 任务二 计算学生成绩一览表数据并判定级别 情景引入：老师让小郭制作学生成绩一览表，然后计算学生的总分，科目的最高分、平均分，最后根据比例系数计算学生的综合分，并根据综合分判断成绩是否及格。 制作思路：新建工作簿后首先在工作表中输入基本数据并格式化——依次计算总分、最高分、平均分和综合分——根据综合分判断成绩是否及格 【操作讲解】 计算总分、最高分和平均分 步骤1 单击G4单元格后单击“开始”选项卡上“编辑”组中的“自动求和”按钮，按【Enter】键得到排到第1位学生的总分。 步骤2 向下拖动G4单元格右下角的填充柄至G13单元格后释放鼠标，得到其他学生的总分。 … “自动求和”列表中选择“最大值”“平均分”后，按“Enter”键，拖动填充。 [知识点讲解] =MAX(数据1，数据2…数据N) =MIN(数据1，数据2…数据N) =SUM(数据1，数据2…数据N) =AVERAGE(数据1，数据2…数据N) 步骤3 计算综合分并判断成绩 =C4*$C$16+D4*$D$16+E4*$E$16+F4*$F$16 向下拖动 步骤4 假设综合分大于等于60分为及格，否则为不及格，在I4单元格输入 =IF（H4>=60, “及格”，“不及格”） 知识点讲解 =IF（条件，“返回内容”,“不满足返回内容”） 任务三 计算员工考核表数据 情景引入：又到年末，公司让小张统计员工各个季度的出勤量、工作态度和工作能力数据，并据此计算季度和年度考核数据，然后按年度总成绩排名，并根据排名计算奖金。 计算的员工考核表数据 制作思路：新建工作簿后首先制作出勤量统计表、绩效表、季度考核表和年度考核——然后利用函数及通过直接引用单元格计算各季度考核数据——接着计算年度考核数据——最后在年度考核表中按年度总成绩排名，并根据排名计算奖金 【操作讲解】制作出勤量统计表、绩效表及其他季度和年度考核表 新建工作簿 重命名工作表 自动换行【Alt+Enter】 设置单元格格式：输入编号 工作表成组：单击一个工作表按住shift同时单击其他工作表 更改一个工作表格式，其他工作表一同变化，输入相同内容 取消成组，右键单击标签，取消成组 【操作讲解】使用公式和函数计算季度考核表数据 步骤1 计算“第一季度考核表”数据。分别在C3，D3，E3，F3单元格输入公式，计算出编号为“0001”的员工第一季度的出勤量、工作态度、工作能力和季度总成绩表数据，其中“季度总成绩表”列中数据的计算依据是“出勤量的20%、工作态度的30%、工作能力的50%之和”。 [知识点讲解] 【提示】INDEX函数用于返回表格或区域中的数值或对数值的引用。 =INDEX(区域，行号，列号) 步骤2 向下拖动C3:F3单元格区域右下角的填充柄至F11单元格后释放鼠标，计算出其他员工第一季度考核数据。这是第一种计算方法。 步骤3 计算“第二季度考核表”。在“第二季度考核表”的C3，D3，E3，F3单元格中输入公式，计算出编号为“0001”的员工第二季度的出勤量、工作态度、工作能力和季度总成绩表数据。 【提示】输入前3个公式时，可先输入“=”，然后单击要引用的工作表标签，接着单击其中要引用的单元格。 步骤4 向下拖动C3:F3单元格区域右下角的填充柄至F11单元格后释放鼠标，计算出其他员工第二季度考核数据。这是第二种计算方法。 【操作讲解】使用公式和函数计算年度考核表数据 步骤1 计算员工的年度考核数据。分别在“年度考核表”的C3，D3，E3，F3单元格中输入公式，计算出计算编号为“0001”员工的年度考核数据。 步骤2 向下拖动C3:F3单元格右下角的填充柄到单元格F11后释放鼠标，计算其他员工的年度考核数据。 步骤3 根据年度总成绩进行排名。在G3单元格输入然后向下拖动G3单元格右下角的填充柄至G11单元格后释放鼠标，计算出所有员工的年度总成绩排名。 知识点讲解 【提示】RANK函数的功能是返回一个数字在数字列表中的排位。 =RANK(排序数值，排序区域，0或者非0)0降序，非零升序 步骤4 根据排名确定奖金额标准。在F14:G17单元格区域输入排名和奖金标准数据。 步骤5 在H3单元格输入公式，计算编号为0001的员工的奖金额。然后向下拖动H3单元格右下角的填充柄到H11单元格后释放鼠标，计算出其他员工的奖金额。 [知识点讲解] =LOOKUP(查找的值，查找区域，显示的值) [常考函数类型] [限时练习] 1.在Excel中,要在同一工作簿中把工作表sheet3移动到sheet1前面,应 。 A.单击工作表sheet3 标签,并沿着标签行拖动到sheet1 前 B.单击工作表sheet3 标签,并按住Ctrl 键沿着标签行拖动到sheet1前 C.单击工作表 sheet3标签,并选“编辑”菜单的“复制”命令,然后单击工作表sheet1标签,再选“编辑”菜单的“粘贴”命令 D.单击工作表sheet3 标签,并选“编辑”菜单的“剪切”命令,然后单击工作表sheet1标签,再选“编辑”菜单的“粘贴”命令 答案：A 2.Excel工作表最多可有 列。 A.65535 B. 256 C.255 D.128 答案：B 3.在Excel中,给当前单元格输入数值型数据时,默认为 A.居中 B.左对齐 C.右对齐 D.随机 答案：C 4.在Excel工作表单元格中,输入下列表达式 是错误的。 A. =(15-A1)/3 B. = A2/C1 C. SUM(A2:A4)/2 D. =A2+A3+D4 答案：C 5.当向Excel工作表单元格输入公式时,使用单元格地址D$2引用D列2行单元格,该单元格的引用称为()。 A.交叉地址引用 B.混合地址引用 C.相对地址引用 D.绝对地址引用 答案：B 6.Excel 工作簿文件的缺省类型是() A .TXT B .XLS C.DOC D.WKS 答案：B 7.在Excel工作表中,不正确的单元格地址是 A.C6 B.CC$66 答案：B 8.在Excel工作表中,在某单元格内输入数值123,不正确的输入形式是 A.123 B .= 123 C. +123 D .* 123 答案:D 9. Excel工作表中可以进行智能填充时,鼠标的形状为 A. 空心粗十字 B.向左上方箭头 C.实心细十字 D.向右上方箭头 答案：C 10.在Excel工作表中,正确的Excel公式形式为 A .= B3*Sheet3!A2 B. =B3*Sheet3$A2 C .= B3*Sheet3:A2 D. =B3*Sheet3%A2 答案：A 11.在Excel工作表中,单元格D5中有公式“=2+C4",删除第A列后C5单元格中的公式为 A.=2+B4 B .= 2+B4 C.SAB$2+C4 答案：A 12.Excel工作表中,某单元格数据为日期型“一九00年一月十六日”,单击“编辑”菜单下“清除”选项的“格式”命令,单元格的内容为 A .16 B .=17 C.1916 D.1917 答案：A 13.在Excel工作薄中,有关移动和复制工作表的说法,正确的是 A.工作表只能在所在工作薄内移动,不能复制 B.工作表只能在所在工作薄内复制,不能移动 C.工作表可以移动到其它工作薄内,不能复制到其它工作薄内 D.工作表可以移动到其它工作薄内,也可以复制到其它工作薄内 答案：D 14.在Excel中,日期型数据“2003年4月23日”的正确输入形式是 A .23-4-2003 B .23.4.2003 C .23,4,2003 D .23:4:2003 答案：A 15.在Excel工作表中,单元格区域D2:E4所包含的单元格个数是 A .5 B.6 C.7 D.8 答案：B 16.在Excel工作表中,选定某单元格,单击“编辑”菜单下的“删除”选项,不可能完成的操作是 A.删除该行 B.右侧单元格左移 C.删除该列 D.左侧单元格右移 答案：D 17.在Excel工作表的某单元格内输入数字字符串“456”,正确的输入方式是 A.456 B. '456 C. =456 D." 456" 答案：B 18.在Excel 中,关于工作表及为其建立的嵌入式图表的说法,正确的是_ A．删除工作表中的数据,图表中的数据系列不会删除 B．增加工作表中的数据,图表中的数据系列不会增加 C．修改工作表中的数据,图表中的数据系列不会修改 D．以上三项不正确 答案：D 19.在Excel工作表中,单元格C4中有公式“=A3+5“,在第三行之前插入一 行之后,单元格C5中的公式为_ A .= A4+6 B .= A4+5 C .= A3+6 D .= A3+5 答案：A 20.若在数值单元格中出现一连串的“###”符号,希望正常显示则需要 A.重新输入数据 B.调整单元格的宽度 C.删除这些符号 D.删除该单元格 答案：B 21.一个单元格内容的最大长度为_个字符。 A.64 B.128 C.225 D.256 答案：D 22.执行“插入→工作表”菜单命令,每次可以插入_个工作表。 A.1 B.2 C.3 D.4 答案：A 23.假设B1为文字"100”,B2为数字"3”,则COUNT(B1:B2)等于 A.103 B.100 C.3 D.1 答案：D 24.为了区别“数字”与"数字字符串”数据,Excel要求在输入项前添加_符号来确认。 A. " B.' C. # D. @ 答案：B 25. 在同一个工作簿中区分不同工作表的单元格,要在地址前面增加_来标识。 A.单元格地址 B.公式 C.工作表名称 D.工作簿名称 答案：C 26. 自定义序列可以通过_来建立。 A.执行“格式一自动套用格式”菜单命令 B.执行“数据一排序”菜单命令 C.执行“工具一选项”菜单命令 D.执行“编辑→填充”菜单命令 答案：C 27. 准备在一个单元格内输入一个公式,应先键入_先导符号。 A.$ B. > C .< D. = 答案:D 28.利用鼠标拖放移动数据时,若出现“是否替换目标单元格内容?”的提示框,则说明 A.目标区域尚为空白 B.不能用鼠标拖放进行数据移动 C.目标区域己经有数据存在 D.数据不能移动 答案：C 29.设置单元格中数据居中对齐方式的简便操作方法是_ A.单击格式工具栏"跨列居中”按钮 B.选定单元格区域,单击格式工具栏”跨列居中”按钮 C.选定单元格区域,单击格式工具栏“居中”按钮 D.单击格式工具栏“居中”按钮 答案：C 30. 当在某单元格内输入一个公式并确认后,单元格内容显示为#REF!,它表示 A.公式引用了无效的单元格 B.某个参数不正确 C.公式被零除 D.单元格太小 答案:A 操作题： 1、 计算员工人事工资管理表数据。打开配套素材“员工人事工资管理表”工作簿，计算年龄和工龄数据。 2、 计算成绩评定表数据。打开配套素材“成绩评定表”工作簿，以“平均分”判断考试成绩级别。 [内容小结] 学科网（北京）股份有限公司 $$