公式与函数的使用

【任务目标】

(1) 复习Excel 2010中公式和函数的使用知识。

(2) 掌握Excel 2010中公式和常用函数的使用方法。

(3) 掌握排序方法。

【任务内容】

完成任务一制作的表格中相关数据。

【实训步骤】

步骤一: 计算总分、 平均分、 各科最高分、 各科最低分

(1) 单击 “总分” 列下面的第一个单元格， 即H3， 使该单元格成为活动单元格。

(2) 单击 “公式” 面板 “自动求和” 列表中的 “求和” 命令， 进入公式编辑状态， 将其中的B3更改为C3， 即公式为 “＝SUM (C3: G3)”， 如图4-12所示。 按下回车键， 得到第一个同学的总分。

图4-12 编辑公式

(3) 拖动H3左下角的手柄， 移动到最后一位同学对应的单元格， 松开鼠标得到所有同学的总分。

(4) 单击 “公式” 面板 “自动求和” 列表中的 “平均值”， 进入平均值函数编辑状态，将其中的参数B3: H3改为C3: G3， 得到公式 “＝AVERAGE (C3: G3)”， 按下回车键，得到第一个同学的平均分。 同样通过拖动该单元格右下角的手柄将其拖到最后一个同学记录， 得到所有同学的平均分。

(5) 选择平均分一列所有单元格， 右击， 选择 “设置单元格格式” 命令， 在打开的“设置单元格格式” 对话框中， 通过 “数字” 选项卡选择 “分类” 为 “数值”， “小数位数”为 “2”， 如图4-13所示， 单击 “确定” 按钮。

图4-13 设置数值格式

(6) 选择 “各科最高分” 行 “语文” 列下面的单元格， 选择 “自动求和” 下拉列表中的 “最大值” 函数， 将其参数更改为 “语文” 成绩所占有的单元格， 如C3: C15， 得到公式 “＝MAX (C3: C15)”， 按下回车键， 得到语文最高分。 通过向右拖动手柄经过所有科目所在的列， 得到各科最高分。 同样方法得到最低分。

步骤二: 利用 “排序” 排定名次

(1) 按下第一个同学所在的行最左边的行标签， 拖动鼠标指针 (或者单击第一个同学所在行的行标签， 按下 【Shift】 键， 然后单击最后一个同学所在的行标签) 选择所有同学信息行， 如图4-14所示。

图4-14 选择同学信息行

(2) 选择 “数据” 面板中的 “排序” 命令， 打开如图4-15所示的 “排序” 对话框，设置 “主要关键字” 为 “总分”， “排序依据” 为 “数值”， 右边的排序方式为 “降序”， 单击 “确定” 按钮， 则总分最高的同学移动到最上面一行。

图4-15 “排序” 对话框

(3) 在总分最高同学对应的 “名次” 列中输入 “1”， 然后按下 【Ctrl】 键的同时拖动该单元格手柄到最后一位同学， 依次递增填充 “名次” 单元格。

(4) 重复 (1)， 然后打开 “排序” 对话框， 设置 “主要关键字” 为 “学号”， 排序方式为 “升序”， 将同学重新按照学号排列。

步骤三: 计算 “总分差距”

总分差距即为当前同学的总分减去全班的最高总分。

(1) 单击选择 “总分差距” 列下的第一个单元格， 即K3， 直接在英文状态下输入 “＝H3-$ H $ 16”， 其中 “H3” 表示第一位同学的总分， “$ H $ 16” 表示对所有总分的最高分的绝对引用。 按下回车键， 得到该同学总分与全班最高总分的差。

(2) 拖动该单元格 (K3) 右下角的移动手柄到最后一个同学对应的 “总分差距” 单元格， 松开鼠标得到所有同学的总分差距。

步骤四: 计算及格率与成绩等级

(1) 单击 “各科及格率” 右边的单元格， 即 “语文” 列与 “各科及格率” 行交叉的单元格 (C18)。

(2) 选择 “公式” 面板中的 “插入函数” 选项， 打开 “插入函数” 对话框， 如图4-16所示。 在 “或选择类别” 中选择 “统计”， 在下面的 “选择函数” 列表中单击“COUNTIF”， 单击 “确定” 按钮。

图4-16 “插入函数” 对话框

(3) 在 “函数参数” 对话框中单击 “Range” 编辑框右边的， 用鼠标从C3拖动到C15， 按下回车键， 然后在 “Criteria” 右边的编辑框中输入 “＞ ＝60”， 如图4-17所示。单击 “确定” 按钮， 则在C18单元格中会显示 “语文” 的及格人数。

图4-17 “函数参数” 对话框

(4) 双击C18单元格进入编辑状态， 在该公式后面添加 “/13”， 即最后该单元格的公式为 “＝COUNTIF (C3: C15，＂ ＞＝60＂) /13”。

(5) 单击 “开始” 面板中 “数字” 工具栏中的 “百分比样式” 按钮， 并单击两次“增加小数位数” 按钮， 该单元格显示为100.00％。 向右拖动该单元格移动手柄到最右边的课程， 即 “化学” 对应的列。

(6) 单击C18单元格， 按下 【Ctrl】＋【C】 键， 单击C19单元格， 按下 【Ctrl】＋【V】键， 双击C19， 并将其修改为 “＝COUNTIF (C3: C15，＂ ＞＝90＂) /13”， 按下回车键。 同样拖动其移动手柄到最后课程对应的列。

(7) 利用名次的前后判断成绩的等级。 单击选择 “成绩等级” 列下面的第一个单元格，即L3， 输入公式 “＝IF (J3＜3，＂ 优秀＂， IF (AND (J3＜6， J3≥3)，＂ 良好＂， IF (AND (J3＜10， J3≥6，＂ 一般＂， ＂ 差＂)))”， 按下回车键。 然后拖动其移动手柄到最后一位同学对应的 “成绩等级” 列。 如图4-18所示。

图4-18 计算结果

【思考与练习】

完成如图4-19所示的表格。

图4-19 考试成绩单