【任务实操】

一、使用常用函数，求“总计”、“平均值”“最大值”“最小值”。

（1）打开“部门信息表（函数）.xlsx”，选择“部门业绩”工作表，如图4-17所示。

图4-17　部门业绩表

（2）求总计项。在F3单元格插入SUM函数，函数设置为“=SUM（B3：E3）”，利用填充句柄复制函数至F4：F6单元格。

（3）求最大值项。在G3单元格插入MAX函数，函数设置为“=MAX（B3：E3）”，利用填充句柄复制函数至G4：G6单元格。

（4）求最小值项。在H3单元格插入MIN函数，函数设置为“=MIN（B3：E3）”，利用填充句柄复制函数至H4：H6单元格。

（5）求平均值项。在B7单元格插入AVERAGE函数，函数设置为“=AVERAGE（B3：B6）”，利用填充句柄复制函数至C7：F7单元格。

二、使用函数，求“总销售量”“误差值”“实际销售量排名”

（1）打开“部门信息表（函数）.xlsx，选择“部门销售统计表”工作表，如图4-18所示。

图4-18　部门销售统计表

（2）求各组总销售量项。F6单元格要求的是第1组的总销售量，涉及条件及求和操作，用SUM IF函数。在F6单元格插入SUM IF函数，注意部分参数相对引用的设置，其参数设置如图4-19所示。复制函数到F7：F9单元格，检查函数参数设置是否正确。

图4-19　SUM IF函数设置

（3）求各组误差值项。求误差值需用ABS函数。在G6单元格插入ABS函数，输入为“=ABS（E6-F6）”，并利用填充句柄将公式复制至G7：G10。

（4）求各组实际销售量排名项。求排名需用RANK函数。在H6单元格插入RANK函数，注意部分参数相对引用的设置，参数设置如图4-20所示。复制函数到H7：H9单元格，检查函数设置是否正确。

图4-20　RANK函数设置

三、使用函数，统计满足指定条件的人数

（1）打开“部门信息表（函数）.xlsx”，选择“部门学历统计表”工作表，如图4-21所示。

图4-21　部门信息表（函数）

（2）求各学历“人数”，既涉及条件要求，也涉及人数统计，用COUNTIF函数。在H5单元格插入COUNTIF函数，注意部分参数相对引用的设置及修改，各参数设置如图4-22所示，利用填充句柄将公式复制至H6：H8单元格。

图4-22　COUNTIF函数参数设置

（3）求“部门总人数”，计数函数COUNT。在H10单元格插入COUNT函数，输入为“=COUNT（A3：A16）”。

四、使用条件函数，根据不同情况显示不同信息内容

（1）打开“部门信息表（函数）.xlsx”，选择“部门考勤”工作表，如图4-23所示。

图4-23　部门考勤

（2）求各职工扣分。在F3单元格输入“=C3*（-1）+D3*（-2）+E3*（-4）”，并利用填充句柄将公式复制至F4：F16单元格。

（3）按要求为各职工在备注栏给出相关信息。涉及三种结果，需要两个IF函数。

在G3单元格插入第一个IF函数，第一、二个参数设置如图4-24所示；在第三个参数嵌套第二个IF函数，其参数设置如图4-25所示。

（4）按Ctrl+S，保存整个工作表。

图4-24　IF函数第一、二参数设置

图4-25　IF函数嵌套

【温馨提示】

1.单元格地址

（1）单个单元格地址：工作表中的一个单元格地址由对应单元格的“列号+行号”组成，如A2代表第A列第2行所在单元格。

（2）多个连续单元格地址：即为一个单元区域，由该区域“左上角的单元格地址：右下角单元格地址”组成，如B2：D4表示如图4-26所示选取区域。

2.运算符

运算符说明对运算对象所进行的操作。常用的运算符主要有算术运算符、文本连接运算符、比较运算符及引用运算符。

（1）算术运算符：用于完成基本的数学运算。常用的有加（+）、减（-）、乘（*）、除（/）等，如图4-27所示。

图4-26　选择区域

图4-27　算术运算符

（2）文本连接运算符：用于连接一个或多个字符串。常见的有&，如“ice”&“cream”，结果为“icecream”。

（3）比较运算符：用于比较两个数值的大小关系。常见的有等于、大于、小于、大于等于、小于等于、不等于，如图4-28所示。

（4）引用运算符：用于对单元格区域的合并计算。

图4-28　比较运算符

图4-29　输入公式

3.公式的基本操作

（1）输入公式。选择需要输入公式的单元格，直接输入“=”符号，然后按要求输入公式内容，按Enter键，即可显示公式运算结果，如图4-29所示。

（2）显示公式。正常情况下，单元格显示公式运算结果，公式本身显示在编辑栏中。用户可单击“公式”选项卡的“公式审核”功能组中的“显示公式”按钮，即可在单元格中显示公式，如图4-30所示。

图4-30　单元格中显示公式

（3）复制公式。公式的复制有两种方法：一种是利用填充句柄直接填充，另一种是直接复制、粘贴。(www.daowen.com)

（4）单元格地址的引用。公式中单元格地址的引用分为相对引用、绝对引用、混合引用3种。

①相对引用：复制或移动公式时，公式中引用的单元格地址会随着位置变化自动改变，其表示方式直接为列号+行号，如B3。

②绝对引用：复制或移动公式时，公式中该引用的单元格地址不会随着位置变化自动改变，其表示方式为在列号和行号前都加“$”，如$B$3。

③混合引用：既有相对引用又有绝对引用。复制或移动公式时，如$B2，则表示B列绝对不变，行号随着改变；如B$2，则表示B列随之改变，2行绝对不变。

4.函数的结构，函数主要由函数名+函数参数组成

函数名，代表函数的用途，如SUM为求和、AVERAGE为求平均值、MAX为求最大值、MIN为求最小值等。

函数参数，可以是常量、公式或者其他函数，函数指定的参数必须是有效的。参数如LEFT左，RIGHT右，ABOVE上，BELOW下；A3：A8；C4；等等。

以SUM函数为例，函数格式如图4-31所示。

5.函数的插入

（1）在“开始”选项卡的“编辑”功能组中的“自动求和”按钮的下拉列表中有常用的函数可供选择和插入，如图4-32所示。

图4-31　SUM函数格式

图4-32　“开始”选项卡中选择函数

（2）在“公式”选项卡中，有Excel2016中内置的各类函数可供选择和插入，如图4-33所示。

图4-33　“公式”选项卡中选择公式

（3）单击编辑栏旁边的按钮，选择需要的函数。

6.常用函数解析

（1）SUM函数（求和），SUM（Number1，Number2，Number3，…）。

例如，SUM（A3，A4：A8，10），表示将A3单元格中的数据、A4：A8单元格中的数据与数字10相加。其函数参数设置如图4-34所示。

图4-34　SUM函数参数设置

（2）AVERAGE函数（求平均值），AVERAGE（Number 1，Number 2，Number 3，…），其参数设置与SUM函数类似。

（3）MAX、MIN函数（求最大值、最小值），MAX（Number1，Number2，Number3，…），MIN（Number1，Number2，Number3，…）。

例如，MAX（3，A4：A8），表示求数字3与A4：A8单元格中数据的最大值。其函数参数设置如图4-35所示。

图4-35　MAX函数参数设置

（4）RANK函数（求排名），RANK（Number，Ref，Order）。其中，Number表示参与排名的值，Ref表示排名的数值区域，Order表示排名的顺序，一般为0或省略表示降序，非1表示升序。

注意：一般使用RANK函数都会涉及复制函数，因此Ref参数要添加$，为绝对引用或者相对引用，因为排名的数值区域是不变的。

（5）IF函数（条件函数），判断是否满足某条件，满足则返回一个值，不满足则返回另一个值。IF（Logical_test，Value_if_true，Value_if_false），其中，参数Logical_test表示条件，为一个逻辑判断表达式；参数Value_if_true表示条件成立（为真）时显示的内容，参数Value_if_false表示条件不成立（为假）时显示的内容。

例如，判断体育成绩是否大于等于90，条件满足则显示“继续锻炼”，条件不满足则显示“加强锻炼”。其参数设置如图4-36所示。

图4-36　IF函数参数设置

（6）COUNT函数（计数函数），COUNT（Value1，Value2，…），参数为单元格区域，统计区域内有多少个。

例如，计算A1：A5区域中有多少个1，其参数设置如图4-37所示。

图4-37　COUNT函数参数设置

（7）COUNTIF函数（计算指定区域内符合条件要求的单元格个数）。COUNTIF（Range，Criteria），Range表示统计区域，Criteria表示条件。

例如，计算A1：A8区域内数字1的个数，其参数设置如图4-38所示。

图4-38　COUNTIF函数参数设置

（8）SUM IF函数（计算指定区域内符合条件的单元格的和）。SUM IF（Range，Criteria，Sum_range），Range表示条件统计区域，Criteria表示条件，Sum_range表示实际求和区域。

例如，计算女生总分，其参数设置如图4-39所示。

图4-39　SUM IF函数参数设置

（9）ABS函数（求绝对值）。

例如，求A与B的差值，其参数设置如图4-40所示。

图4-40　ABS函数参数设置

【知识拓展】

（1）常见使用到绝对引用的函数有：Rank、Countif、Sum if。

（2）IF函数的嵌套：若有两种结果，则只需一个IF函数；若有三种结果，则需两个IF函数；若有四种结果，则需三个IF函数。

（3）填充句柄：将光标移动到单元格右下角时，光标变为实心黑十字指针，如，此时按下鼠标左键或右键拖动都可进行数据填充。用左键填充时，松开鼠标后，会显示“自动填充选项”，点击可选择“复制单元格”“仅填充格式”“不带格式填充”和“快速填充”。用右键填充时，则弹出菜单选项供选择。

【课堂练习】

打开“班级成绩表（练习）.xlsx”，完成下列要求。

（1）使用SUM、AVERAGE、MAX、MIN函数计算班级各同学的总分、平均分，计算各科最高分、最低分。

（2）使用IF函数评定“成绩等级”，“平均分”>=85的为“优”，<85且>=75的为“良”，<75且>=60的为“中”，<60的为“差”。

（3）使用RANK函数根据各个同学的总分进行降序排名。

（4）使用COUNTIF函数计算各科及格人数、不及格人数，计算男生人数、女生人数。

（5）使用SUM IF函数计算各科男生总分、女生总分、男生平均分、女生平均分。

（6）打开“Sheet2”工作表，运用函数计算单元格E2和F3的值。然后运用填充的方式计算出其他同学的“总分”和“平均分”，不改变原来单元格的格式。