信息技术 课件 单元2 Excel 2016电子表格处理

单元2Excel2016电子表格处理汇报内容02项目5道克图文公司员工考核数据处理03目录项目6开支明细表数据处理01项目4数据快速录入及计算项目6开支明细表数据处理学习目标课前自学课中学习课后提升学习目标1．知识目标1）掌握设置单元格边框、行高、列宽、对齐方式的操作方法。2）掌握创建表格并设置表格样式的操作方法。3）掌握数据验证的作用和操作方法。4）掌握运用条件格式筛选数据的操作方法。5）掌握自定义多条件筛选数据的操作方法。6）掌握多关键字排序的操作方法。7）掌握数据透视表的作用和创建方法。8）掌握图表的常用类型和选取数据创建图表的操作方法。2．能力目标1）能够熟练对工作表进行美化。2）能够根据需要选取相应数据制作报表。3．素养目标1）通过对实际问题的解决，培养学生的信息素养。2）体会成功的快乐，激进学习兴趣，提高自主学习能力。3）具备协调沟通的团队精神。课前自学（一）格式化表格

Excel表格提供了丰富的格式化命令，利用这些命令，可以对工作表内的数据及外观进行修饰，制作出符合日常习惯又美观的表格。

单元格或单元格区域的格式化主要包含六部分：数字、对齐、字体、边框、图案和保护，如图所示，为“设置单元格格式”对话框中包含的六部分。（二）数据处理数据验证

数据验证是Excel提供的用于检验数据有效性的功能。在Excel中，为了避免在输入数据时出现过多错误，可以通过在单元格中设置数据有效性以防止输入无效数据，或者在录入无效数据时自动发出警告，从而保证数据输入的准确性，提高工作效率。

对于学生成绩，有效值为0-100的数值，比如成绩为90，误输入为900，若已经为学生成绩列设置数据验证，则此时会出现警告提示，保证数据有效性。数据验证在“数据”项卡下的“数据工具”组中，如图所示。

有一点需要注意，验证功能只对新数据有效，在已经填好数据的单元格增加验证功能没有作用。数据验证是在当单元格的值发生变化时触发的，如果原有的值不符合标准，同时该值没有发生变化，就不会触发验证功能。所以，如果决定使用验证功能，最好在未填写数据时就设置好，而不是等数据都填写后再设置。条件格式Excel提供的条件格式功能可以迅速为满足某些条件的单元格或单元格区域设定某项格式，条件格式会基于设定的条件来自动更改单元格区域的外观，可以突出显示所关注的单元格或单元格区域、强调异常值、使用数据条、色阶和图标集来直观地显示数据。如图所示。（1）突出显示单元格规则

用比较运算符限定数据范围，对该数据范围内的单元格设定格式。

规则选项有大于、小于、介于、等于、文本包含、发生日期、重复值和其他规则。

例如：查找高数科目成绩大于85的值，如图所示。（2）项目选取规则

对选定单元格区域中的前若干个最高值或后若干个最低值及高于或低于该区域平均值的单元格设定特殊格式。

规则有前10项、前10%、最后10项、最后10%、高于平均值、低于平均值和其他规则。

例如：查找高数科目成绩前十名的值，如图所示。（3）数据条

数据条的长度代表单元格中的值。数据条越长，值越高；数据条越短，值越低。

最常用的数据条有两种样式：渐变填充和实心填充。

例如：高数科目成绩是渐变填充效果，体育科目成绩是实心填充效果，如图所示。（4）色阶

使用两种或者三种颜色的渐变效果直观地比较单元格区域中数据，用来显示数据的变化。一般情况下，颜色的深浅表示值得高低。

例如：用“绿-白色阶”表示高数科目的成绩值。成绩越高，颜色越绿，成绩越低，颜色越白。如图所示。（5）图标集

可以使用图标集对数据进行注释，每个图标代表一个值得范围。

预设了方向、形状、标记、等级四种图标集。

例如：用三色交通灯来表示高数科目的成绩。如图所示。排序

对数据进行排序有助于快速直观地组织并查找所需数据，可以对一列或多列中的数据文本、数值、日期和时间按升序或者降序的方式进行排序。还可以按照自定义序列、格式（包括单元格颜色、字体颜色等）进行排序。（1）快速简单排序按“总分”列进行升序排序。单击“总分”数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组→“升序”按钮，则按总分成绩从小到大排序；若选择“降序”，则按总分成绩从大到小排序，如图2-63所示。（2）复杂多条件排序按“高数”成绩升序，高数成绩相同的，按照“英语”成绩降序排序。单击数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组中的“排序”按钮→打开“排序”对话框→在“主要关键字”中选择“高数”、“数值”、“升序”→单击“添加条件”→在“次要关键字”中选择“英语”、“数值”、“降序”→单击”确定”按钮，如图所示。（3）按照字体颜色排序将“高数”成绩相同的行，字体颜色设置为红色，并将其所在行设置在数据表的顶端。单击数据列中的任意一个单元格，选择“数据”选项卡→“排序和筛选”组中的“排序”按钮→打开“排序”对话框→在“主要关键字”中选择“高数”、“排序依据”选择“字体颜色”、“次序”选择“红色”“在顶端”→单击“确定”按钮，如图2-65所示。筛选

通过筛选功能可以快速的从数据列表中查找符合条件的数据或者排除不符合条件的数据。对数据列表中的数据进行筛选后，就会仅显示满足指定条件的行，不满足条件的行就会被隐藏。更方便用户查看数据。首先单击内容数据表的任一单元格，选择“数据”选项卡→“排序和筛选”组→“筛选”按钮，此时，字段名右侧会出现一个下拉箭头。单击需要筛选字段的下拉箭头，设置筛选条件或自定义自动筛选方式。例如：筛选出“计算机”成绩大于85的数据行，操作步骤如图所示。筛选结果若取消筛选，则再单击“筛选”按钮取消选中即可。数据透视表

数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。建好数据透视表后，可以对其中的字段重新安排，以便从不同角度查看数据。数据透视表能够将筛选、排序、分类汇总等操作依次完成，并生成汇总报表，生成数据透视表的关键步骤如下页图2-68所示。图2-68将性别、班级、姓名、总分4个字段分别拖入到相应区域，生成的数据透视表如图所示。将“性别”筛选为男，列标签改为“班级”，行标签改为“姓名”，效果如图所示。迷你图迷你图是放入单个单元格中的小型图，每个迷你图代表所选内容中的一行数据。选中Q3单元格，选择“插入”选项卡→“迷你图”组→“折线图”按钮，打开创建迷你图对话框，数据范围选择F3:N3，位置Q3，单击确定。拖动Q3右下角的填充柄，可以将迷你图填充到最后。迷你图的样式及颜色，高点、低点、首点、尾点等的设置，在“迷你图工具--设计”上下文选项卡下进行。图表相对于迷你图，图表作为表格中的嵌入对象，类型更丰富、创建更灵活、功能更全面、作用更强大。图表是工作表数据的图形化表示，通过图表，数据信息变得一目了然，更加详细地表示数据的大小、变化、走向、趋势及数据间的差异等，更方便进行数据的比较和分析。在Excel中，图表由多个部分组成，这些组成部分被称为图表元素。一个完整的图表通常由图表区、绘图区、图表标题、图例等组成。最常用的图表类型有：柱形图，折线图，饼图，条形图，面积图等。图表的基本组成：图表区，绘图区，横坐标轴和纵坐标轴，图例，图标标题，坐标轴标题，数据标签等。课中学习任务2-4：“开支明细表”整理与分析（一）任务介绍

小张是一名参加工作不久的大学生，平时习惯使用Excel表格来记录每月的个人开支情况。在2021年底，他将每个月各类支出的明细数据录入了文件名为“开支明细表.xlsx”的Excel工作簿文档中。请根据下列要求帮助小赵对明细表进行整理和分析：1）在工作表“小张的美好生活”的第一行添加表标题“小张2021年开支明细表”，并通过合并单元格，放于整个表的上端、居中。2）将工作表应用一种主题，并增大字号，适当加大行高列宽，设置居中对齐方式，除表标题“小张2021年开支明细表”外为工作表分别增加恰当的边框和底纹以使工作表更加美观。3）将每月各类支出及总支出对应的单元格数据类型都设为“货币”类型，无小数、有人民币货币符号。4）通过函数计算每个月的总支出、各个类别月均支出、每月平均总支出；并按每个月总支出升序对工作表进行排序。5）利用“条件格式”功能：将月单项开支金额中大于1000元的数据所在单元格以不同的字体颜色与填充颜色突出显示；将月总支出额中大于月均总支出110%的数据所在单元格以另一种颜色显示，所用颜色深浅以不遮挡数据为宜。6）在“年月”与“服装服饰”列之间插入新列“季度”，数据根据月份由函数生成，例如：1至3月对应“1季度”、4至6月对应“2季度”……7）复制工作表“小张的美好生活”，将副本放置到原表右侧；改变该副本表标签的颜色，并重命名为“按季度汇总”；删除“月均开销”对应行。8）通过分类汇总功能，按季度升序求出每个季度各类开支的月均支出金额。9）在“按季度汇总”工作表后面新建名为“折线图”的工作表，在该工作表中以分类汇总结果为基础，创建一个带数据标记的折线图，水平轴标签为各类开支，对各类开支的季度平均支出进行比较，给每类开支的最高季度月均支出值添加数据标签。课后提升（一）巩固提升

实训任务2-5：“股票走势数据表”处理

小张就职于一家金融公司，需要对股票走势进行分析计算。打开“股票走势.xlsx”文件，对“股票走势”数据表按以下要求操作。1）设置标题，文字为黑体、20磅、粗体，并在A1:N1区域中合并居中，行高30。2）在J3单元格中，使用MIN函数求出该股票今日最低价，并将该公式向下填充J4:J7区域，在K3单元格中，使用MAX函数中求出该股票今日最高价，并将该公式向下填充K4:K7区域，在L3单元格中，利用公式计算今日涨跌。今日涨跌=最高价-最低价，并将该公式向下填充M4:M7区域，在M3单元格中，利用公式计算今日涨幅，今日涨幅=今日涨跌/昨收盘，并将该公式向下填充M4:M7区域。对今日涨幅列数据进行降序排序。3）在K9单元格中，使用AVERAGE函数求出今日平均涨幅。4）设置A2:N7区域中行的行高设置为30，并为该区表格设置所有框线，在N3单元格中插入折线图迷你图，并向下填充N4:N7区域。5）设置A1:N7区域的单元格对齐方式为水平方向居中、垂直方向居中。6）以B2:C7和I2:I7区域数据为数据源，在当前表中生成一个二维簇状柱形图，图表设计布局为“布局1”，图表标题为“股票价格变化图”，为图表添加图表元素“数据标签”。最终效果如图所示。（二）拓展提升

实训任务2-6：“产品销售表”统计分析

销售部助理小王需要根据2012年和2013年的图书产品销售情况进行统计分析，以便制订新一年的销售计划和工作任务。现在，请按照如下需求，在文档“产品销售表.xlsx”中完成以下工作并保存。1）在“销售订单”工作表的“图书编号”列中，使用VLOOKUP函数填充所对应“图书名称”的“图书编号”，“图书名称”和“图书编号”的对照关系请参考“图书编目表”工作表。

2）将“销售订单”工作表的“订单编号”列按照数值升序方式排序，并将所有重复的订单编号数值标记为紫色（标准色）字体，然后将其排列在销售订单列表区域的顶端。3）在“2013年图书销售分析”工作表中，统计2013年各类图书在每月的销售量，并将统计结果填充在所对应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。4）在“2013年图书销售分析”工作表中的N4:N11单元格中，插入用于统计销售趋势的迷你折线图，各单元格中迷你图的