上机练习二课件

上机练习二CONTENTS

任务描述

CONTENTS

任务描述

CONTENTS

任务描述

CONTENTS

任务描述

CONTENTS

任务分析

通过分析该任务可知，完成该任务，需要掌握以下几点：（1）能够创建表格，并能够对表格进行格式设置。（2）会使用相关的函数和公式对数据进行统计。（3）能够使用Excel2010提供的图表功能绘制折线图。（4）会对数据进行筛选，并能够进行条件查找。CONTENTS

任务实现

（1）建立表格文件，在A1单元格中输入“班级考试成绩表”，选择A1：L1单元格区域，对其进行合并居中，并设置其中文字字体为“隶书”，文字大小为36。（2）在A2：L2单元格区域中输入表头信息，设置其为居中对齐，设置文字格式为“宋体，14，加粗”，单元格填充背景设置为“浅蓝”。（3）在A3单元格中输入学号“‘10301”，向下拖动鼠标至A23单元格，实现学号的自动填充，然后填写其他各科成绩。（4）选中K3单元格，输入公式“=SUM（C3:J3）”，求出第一名学生的总分，然后拖拽鼠标复制公式至K23单元格，完成所有总分的计算。CONTENTS

任务实现

（5）选中L3单元格，输入公式“=RANK（K3$K$3:$K$23）”，求出第一名学生的名次，然后复制公式至L23单元格，完成所有学生名次的统计。说明：此处在统计学生名次的时候，没有使用常用的排序操作，而是使用了RANK函数，该函数的基本功能是返回一个数字在数字列表中的排位，其基本格式为：RANK（数字，引用区域）。此案例操作中，就是求一个单元格的数值，在一个区域中（在总分数据区域内）的排序位置，此处使用的是绝对引用。CONTENTS

任务实现

（6）在A3：L23单元格区域中隔行设置填充背景，依次为“淡蓝”和“无填充颜色”。（7）设置A3：K23单元格区域的文字格式为“宋体，14”，并居中显示，设置L3：L23单元格区域的文字格式为“宋体，14，倾斜”。（8）选择C3：J23单元格区域，切换到“开始”选项卡，在“样式”组中单击“条件格式”下拉按钮，在弹出的下拉列表中选择“突出显示单元格规则”→“介于”选项，在弹出的“介于”对话框中，分别将“介于59.9到0”设置为“浅红填充色深红色文本”，将“介于80到100”设置为“自定义格式”，如图4-3所示。接着在弹出的“设置单元格格式”对话框“字体”选项卡中，选择字形为“加粗”。CONTENTS

任务实现

CONTENTS

任务实现（9）选择A2：L23单元格区域，切换到“开始”选项卡，在“字体”组中单击“边框”下拉按钮，在下拉列表中选择“线条颜色”为“浅蓝”后，再次单击“边框”下拉按钮，在弹出的下拉列表中选择“所有框线”选项，即为表格设置浅蓝色内外边框。（10）选中O4单元格，输入“成绩分析”，从Q4单元格至X4单元格依次输入各个科目名称，其文字格式设置为“宋体，14，加粗”，填充背景设置为“紫色，强调文字颜色4，淡色40%”，居中显示文字，从O5单元格至O9单元格依次输入“平均分”“最高分”“最低分”“及格率”和“优秀率”，其文字设置为“宋体，14”，居中显示文字。（11）依次选择O4：P4、O5：P5、O6：P6、O7：P7、O8：P8和O9：P9单元格区域，将它们设置为“合并后居中”。（12）选中Q5单元格，输入公式“=AVERAGE（C$3:C$23）”，计算语文科目的平均分，然后拖拽鼠标复制公式至X4，计算其他科目的平均分。CONTENTS

任务实现（13）选中Q6单元格，输入公式“=MAX（C$3:C$23）”，统计语文科目的最高分，然后拖拽鼠标复制公式至X6，统计其他科目的最高分。（14）选中Q7单元格，输入公式“=MIN（C$3:C$23）”，统计语文科目的最低分，然后拖拽鼠标复制公式至X7，统计其他科目的最低分。（15）选中Q8单元格，输入公式“=COUNTIF（C$3:C$23，">=60"）/COUNTIF（C$3:C$23，">0"）”，计算语文科目的及格率，然后拖拽鼠标复制公式至X8，计算其他科目的及格率。（16）选中Q9单元格，输入公式“=COUNTIF（C$3:C$23，">=80"）/COUNTIF（C$3:C$23，">0"）”，计算语文科目的优秀率，然后拖拽鼠标复制公式至X9，计算其他科目的优秀率。（17）从O5：X9单元格区域隔行设置填充背景，依次为“紫色，强调文字颜色4，淡色80%”和“无填充颜色”。CONTENTS

任务实现（18）选择Q5：X5单元格区域，切换到“开始”选项卡，单击“数字”组的组按钮，在弹出的“设置单元格格式”对话框中，切换到“数字”选项卡，在“分类”列表框中选择“数值”选项，在右侧窗格中设置小数位数为2位。（19）选择Q8：X9单元格区域，按照（9）的方法，在“数字”选项卡的“分类”列表框中选择“百分比”选项，然后设置小数位数为2位。（20）选择Q5：X9单元格区域，设置其中字体为“宋体，12”，并居中显示。（21）选择O4：X9单元格区域，按照步骤1中的（9），为表格设置内外边框颜色为“紫色，强调文字颜色4，淡色80%”。CONTENTS

任务实现（22）选中O11单元格，输入“各科各分数段人数”，然后选择O11：X11单元格区域，进行“合并及居中”，其中文字格式设置为“宋体，14，加粗”。选中O12单元格，输入“分数段”，从Q12单元格至X12单元格，依次输入各个科目名称，其文字格式设置为“宋体，14，加粗”，填充背景设置为“水绿色，强调文字颜色5，淡色40%”，居中显示文字，从O13单元格至O21单元格依次输入“满分”“90-99”“80-89”等分段信息，其文字设置为“宋体，14，加粗”，居中显示文字。（23）依次选择O13：P13、O14：P14、O15：P15、……、O21：P21单元格区域，设置“合并及居中”。（24）从O13：X21单元格区域隔行设置填充背景，依次为“水绿色，强调文字颜色5，淡色80%”和“无填充颜色”。（25）从单元格N11至单元格N21，依次输入100、99.9、89.9、79.9、69.9、59.9、49.9、39.9和29.9，作为FREQUENCY函数的分段数据，如图4-4所示。CONTENTS

任务实现

CONTENTS

任务实现

（26）选择Q13：Q21单元格区域，切换到“公式”选项卡，在“函数库”组中单击“插入函数”按钮，弹出“插入函数”对话框，在“或选择类别”下拉列表框中选择“全部”选项，在“选择函数”列表框中选择FREQUENCY选项，然后单击“确定”按钮，弹出“函数参数”对话框，在Data_array文本框中输入C3：C23，在Bins_array文本框中输入N13：N21，将数据区域改为混合引用C$3：C$23，将分段区域改为绝对引用$N$13：$N$21，如图4-5所示。CONTENTS

任务实现

单击“确定”按钮后，在编辑栏上按下Ctrl+Shift+Enter组合键，保证公式以数组的形式输入（公式两端会自动加上“{}”，此时不可用手工添加），公式输入之后按Enter键，统计结果就会自动显示在区域内，如图4-6所示。然后在区域右下角按住鼠标左键，拖拽复制公式至整个统计区域，完成统计操作。CONTENTS

任务实现

说明：此处操作用到了FREQUENCY函数，该函数的主要功能是计算数值在某个区域内的出现频率，然后返回一个垂直数组，其主要格式如下：FREQUENCY（data_array，bins_array）其中，data_array是一个数组或对一组数值的引用，为它计算频率；Bins_array是一个区间数组或对区间的引用，该区间用于对data_array中的数值进行分组。因为该函数返回一组数据，因此，本步操作选取了一个区域输入公式。（27）选择O12：X21单元格区域，为表格设置内外边框颜色为“水绿色，强调文字颜色5，淡色40%”。（28）选择Q13：X21单元格区域，按照步骤1中（8）的方法，在“大于”对话框中，设置为大于“0”的单元格设置格为“黄填充色深黄色文本”。（29）将鼠标指针置于第N列边界处，拖动鼠标缩小列宽，直至将其隐藏。将表格数据居中显示。CONTENTS

任务实现

（30）选择B2：E23单元格区域，切换到“插入”选项卡，在“图表”组中单击“折线图”下拉按钮，在弹出的下拉列表中选择“带数据标记的折线图”选项，即可新建图表。（31）切换到“图表工具-布局”选项卡，在“标签”组中单击“图表标题”下拉按钮，在弹出的下拉列表中选择“图表上方”选项，图表中会显示“图表标题”编辑框，在标题框中输入标题文字“语数外成绩分析图”，设置“主要横坐标轴标题”为“坐标轴下方标题”，输入内容为“姓名”，设置“主要纵坐标轴标题”为“竖排标题”，输入内容为“成绩”。（32）切换到“图表工具-布局”选项卡，在“坐标轴”组中单击“网格线”下拉按钮，在弹出的下拉列表中选择“主要横网格线”→“无”选项。（33）双击“数值轴”（Y轴），系统显示“设置坐标轴格式”对话框，在该对话框的左侧窗格中选择单击“坐标轴选项”选项，在右侧窗格中分别设置“最大值”为“固定，100”、设置“主要刻度单位”为“固定，10”，单击“关闭”按钮，如图4-7所示。CONTENTS

任务实现

CONTENTS

任务实现

（34）通过浮动工具栏，可以将图例中的文字设置为9号字，适当调整图表和图例到一定位置和大小，使分类轴文字能够水平显示。（35）单击表格区域，切换到“开始”选项卡，在“编辑”组中单击“排序和筛选”下拉按钮，在弹出的下拉列表中选择“筛选”选项，表格的表头项目上将会出现“筛选”下拉按钮。单击对应的“筛选