1、模块二 Excel 2010项目八 分析处理数据1.Excel 2010中的排序、筛选、分类汇总、数据透视表。2.Excel 2010中的数据有效性、合并计算、分列。掌握知识点任务1 排序排序是将工作表中的数据按照一定的规律进行排列，方便用户对数据进行分析。1.简单排序简单排序适用于根据一个条件对数据进行排序，即按某一列的数据进行排序。方法：（1）选择数据表中要排序的那一列中任一单元格。（2）在“数据”选项卡“排序和筛选”工作组中，单击“升序”或“降序”按钮，如图2-8-1所示。2.自定义排序若要一次性根据多个条件对数据进行排序，应在“排序”对话框中自行设置排序的关键字、排序依据、排序次序等，
2、即自定义排序。（1）方法：选择数据表中任一单元格。在“数据”选项卡，“排序和筛选”工作组中单击“排序”按钮。在弹出的对话框中，设置主要关键字、排序依据、次序。如果有多个关键字，单击“添加条件”按钮，对次要关键字进行设置，如图2-8-2所示。单击“确定”按钮。（2）排序规则（以升序为例）：文本：按照所选文本首字母AZ顺序排列，如果第一个字母相同，则按第二个字母排列。数值：从最小的负数到最大的正数进行排列。日期：从最早的日期到最晚的日期进行排列。逻辑值：FALSE排在TRUE之前。空白单元格：总是排在数据的最后。任务2 筛选筛选是将符合条件的记录显示出来，不符合条件的记录隐藏。筛选分为自动筛选和高
3、级筛选两种。如果对一个字段进行筛选通常用自动筛选；若对多个字段筛选，可以用自动筛选做多次；若对多个字段一次性进行筛选，通常使用高级筛选。1.自动筛选（1）方法：选择数据表中任一单元格。在“数据”选项卡“排序和筛选”工作组中，单击“筛选”按钮。单击要筛选的字段名后的下拉按钮。在弹出的下拉列表中选择筛选条件。（2）说明：单击“数据”选项卡“排序和筛选”工作组中的“筛选”按钮后，每个字段名后会出现一个下拉按钮，如图2-8-4所示。单击下拉按钮，会出现一个下拉列表，如图2-8-5所示。再次单击“数据”选项卡“排序和筛选”工作组中的“筛选”按钮，会取消自动筛选。2.高级筛选（1）方法：在数据表外建立条件
4、区域。单击数据表中任一单元格。在“数据”选项卡“排序和筛选”工作组中，单击“高级”按钮。在弹出的对话框中，输入（或用在工作表中选择的方法代替输入）列表区域、条件区域，选择筛选结果显示位置，如图2-8-6所示。单击“确定”按钮。（2）说明：输入条件时，同行为“与”的关系，如图2-8-7所示；不同行为“或”的关系，如图2-8-8所示。若要取消高级筛选结果，还原到原数据表，则需单击“数据”选项卡“排序和筛选”工作组中的“清除”按钮。任务3 分类汇总分类汇总是将数据表中的数据先分类再进行汇总，所以分类汇总之前要对数据表中的分类字段进行排序。1.方法：（1）按分类字段排序。（2）选择数据表中任一单元格。
5、（3）在“数据”选项卡“分组显示”工作组中，单击“分类汇总”按钮。（4）在弹出的对话框中，选择分数字段、汇总方式、汇总项等。（5）单击“确定”按钮。2.说明：选择数据表中任一单元格后，如图2-8-9所示，再次打开“分类汇总”对话框，单击左下角的“全部删除”按钮，可以删除当前分类汇总，如图2-8-10所示。去掉“分类汇总”对话框中“替换当前分类汇总”复选框中的钩可实现嵌套分类汇总。分类汇总后，Excel将分级显示数据列表，单击数据表左侧1、2、3可看到不同的汇总结果。单击+、可以展开、折叠明细数据行，如图2-8-11和图2-8-12所示。任务4 数据透视表数据透视表具有强大的数据处理能力，能够快
6、速汇总、分析、浏览和显示数据，对原始数据进行多维度展现。1.创建数据透视表（1）选择要创建数据透视表的单元格区域。单击“插入”选项卡，“表格”工作组中的“数据透视表”下拉按钮，选择“数据透视表”命令。（2）在弹出的对话框中，选择数据源，选择数据透视表存放位置，如图2-8-13所示，单击“确定”按钮。（3）在弹出的“数据透视表字段列表”任务窗格中，选择要添加到报表的字段，设置行标签、列标签、数值等，如图2-8-14所示。2.更改计算类型（1）在右侧任务窗格中，单击“数值”框中要更改字段后下拉按钮，选择“值字段设置”。（2）在弹出的对话框中，选择“值汇总方式”，选择要更改的计算类型，如图2-8-1
7、5所示。（3）单击“确定”按钮。3.更改数据透视表字段在右侧任务窗格中选择要添加或减少的字段。4.更改数据透视表样式（1）选择数据透视表。（2）在“设计”选项卡“数据透视表样式”工作组中，单击“其他”按钮，如图2-8-16所示。（3）选择一种样式。5.更改数据透视表部分选项右击选择“数据透视表选项”对话框，可以设置数据透视表的布局、格式（如图2-8-17所示）、显示（如图2-8-18所示）等。任务5 数据有效性1.作用：设置数据有效性，可以防止在单元格中输入无效数据。2.实例：将语文、数学成绩设置在090之间。（1）选择“语文”“数学”列要输入分数的单元格区域。（2）单击“数据”选项卡，在“数
8、据工具”工作组中，单击“数据有效性”按钮，选择“数据有效性”。（3）在弹出的对话框中，单击“设置”选项卡，有效性条件设置为允许“整数”，数据的最小值设为0，最大值设置为90，如图2-8-19所示。（4）单击“输入信息”选项卡，输入信息标题、输入信息内容，如图2-8-20所示。（5）单击“出错警告”选项卡，选择出错警告的样式及标题，在错误信息中输入“请输入0至90的数值”，如图2-8-21所示。（6）单击“确定”按钮。此时，如在“语文”“数学”列，输入的数据超出090的范围，就会弹出出错信息，如图2-8-22所示。任务6 合并计算1.作用：合并计算可以将多个区域的值合并到一个新区域中。2.实例：
9、打开“excel1.6.xlsx”，在“第一仓库”“第二仓库”“第三仓库”工作表中分别存放着各种商品的型号和数量，现需要在“合并计算”工作表中计算出三个仓库各型号商品的数量总和，如图2-8-23所示。（1）在“合并计算”工作表中单击A1单元格。（2）单击“数据”选项卡，在“数据工具”工作组中单击“合并计算”命令。（3）在弹出的对话框中，如图2-8-24所示：根据题意函数选“求和”；单击“引用位置”拾取按钮，选择第一仓库要合并的数据区域，单击“添加”按钮，依次选择以添加第二仓库、第三仓库数据区域；标签位置勾选“首行”和“最左列”；勾选“创建指向源数据的链接”（当源数据发生变化时，合并计算结果也随
10、之变化）。（4）单击“确定”按钮。单击合并计算工作表左侧“+”。单击“+”可展开显示源数据信息；单击“”可折叠源数据信息，如图2-8-25所示。任务7 分列1.作用：在Excel中可以利用数据工具中的分列功能将一列的数据分成两列显示。2.实例：打开“excel1.7.xlsx”，将A1:A5单元格的文本以“”为分界线分成歌名和歌手两列显示，如图2-8-26所示。（1）选择A1:A5单元格区域。（2）在“数据”选项卡“数据工具”工作组中单击“分列”按钮，如图2-8-27所示。（3）在弹出的对话框中单击“下一步”，如图2-8-28所示。（4）在弹出的对话框中，勾选“其他”，输入“-”，单击“下一步
11、”，如图2-8-29所示。（5）在弹出的对话框中，分列线左右列数据格式均设置为“文本”，单击“完成”按钮，如图2-8-30所示,效果如图2-8-31所示。习题八打开“习题八.xlsx”,完成以下操作：（1）将工作表“排序1”按“总分”降序排列。（2）将工作表“排序1”按“语文”成绩降序排列，若“语文”成绩相同，则按“数学”成绩降序排列（标题行不参与排序）。（3）将工作表“排序2”主要关键字按销售部门甲、乙、丙、丁顺序排序，次要关键字按品名的上衣、下装、袜子顺序排列。（4）工作表“排序1”：显示数据表中所有女生的记录。（5）工作表“排序1”：显示数据表中所有姓“陈”同学的记录。（6）工作表“排序
12、1”：显示数学成绩在90分及以上同学的记录。（7）工作表“排序1”：降序显示“总分”最高的前10项。（8）工作表“排序1”：显示语文、数学、英语成绩均在80分以上同学的记录，筛选结果显示在原有区域。（9）工作表“排序1”：显示语文或数学或英语成绩在80分以上同学的记录，筛选结果复制到以I6为起始单元格的单元格区域中。（10）工作表“排序1”：数据表中有班级序号、姓名、性别、语文、数学、英语、总分字段，请利用分类汇总按“性别”统计各科成绩的平均分。（11）工作表“分类汇总”部分数据如图2-8-32所示。要求：先汇总数据表中男、女生“余额”的平均值，再嵌套汇总出“生活费支出合计”和“余额”的最小值。（12）工作表“数据透视表”：利用数据透视表显示各专业语文、数学、英语的平均值（保留1位小数），结果存放在以I2为起始单元格的区域，并添加文总字段。最终效果图如图2-8-33所示。（13）工作表“分列”：将A2:A9的班级序号分两列显示，A2:A9显示班级“1502”，B2:B9显示每位同学的序号。
