Excel数据处理与分析 课件 项目3 简单的数据管理工具

项目3

简单实用的数据

管理工具主要内容3．1项目展示3．2项目制作3．3知识点击3．4实战训练3．1项目展示本项目将创建“GT公司销售管理”工作簿，其中包含的工作表有：销售订单表、客户信息表、产品信息表，客户统计表、产品统计表、销售员统计表等，通过对客户、产品、销售员等数据进行多种方式的排序，可以方便、清晰地浏览、检索大量繁杂的数据，效果分别如下图所示。“销售订单表”制作效果“客户统计表”制作效果销售数据的三级分类汇总结果3．2项目制作创建“销售管理”工作簿，制作销售订单表、客户信息表、产品信息表制作客户统计表、产品统计表、销售员统计表多级分类汇总“销售订单表”中的数据任务一：创建“销售管理”工作簿，制作销售订单表、客户信息表、产品信息表

“销售订单表”中的数据引用和计算要求如下：产品名称：根据产品型号，从产品信息表中查找产品名称区域：根据客户名称，从产品信息表中查找客户所在区域单价：从产品信息表中查找产品的单价总金额：销量与单价的积是否已处理：由订单处理日期判断该订单是否已处理业务员、客户电话、客户邮箱、销售员：均由客户信息表中查找（1）新建工作簿。在【文件】选项卡【新建】功能区中，单击【空白工作簿】命令按钮，并命名为：销售管理。（2）制作销售订单表、客户信息表、产品信息表，并录入基础数据，分别如下图所示。任务一：创建“销售管理”工作簿，制作销售订单表、客户信息表、产品信息表

任务一：创建“销售管理”工作簿，制作销售订单表、客户信息表、产品信息表

任务一：创建“销售管理”工作簿，制作销售订单表、客户信息表、产品信息表

（3）销售订单表中数据的引用与计算。在D2单元格中输入公式：=VLOOKUP(C2,产品信息表!A$2:B$14,2,FALSE)；在E2单元格中输入公式：=VLOOKUP($E2,客户信息表!$A$2:$H$26,2,FALSE)；在R2单元格中输入公式：=VLOOKUP(C2,产品信息表!A$2:C$14,3,FALSE)；在I2单元格中输入公式：=G2*H2；任务一：创建“销售管理”工作簿，制作销售订单表、客户信息表、产品信息表

在K2单元格中输入公式：=IF(YEAR(J2)=2017,"√","")；在L2单元格中输入公式：=VLOOKUP($E2,客户信息表!$A$2:$H$26,3,FALSE)；在H2单元格中输入公式：=VLOOKUP($E2,客户信息表!$A$2:$H$26,6,FALSE)；在N2单元格中输入公式：=VLOOKUP($E2,客户信息表!$A$2:$H$26,7,FALSE)；任务一：创建“销售管理”工作簿，制作销售订单表、客户信息表、产品信息表

在O2单元格中输入公式：=VLOOKUP($E2,客户信息表!$A$2:$H$26,8,FALSE)；向下填充公式，数据引用和计算结果如图所示。任务一：创建“销售管理”工作簿，制作销售订单表、客户信息表、产品信息表

任务二：制作客户统计表、产品统计表、销售员统计表（1）打开“销售管理”工作簿，创建新工作表，分别命名为：客户统计表、产品统计表、销售员统计表。（2）客户统计表中数据的引用与计算。在A3单元格中输入公式：=客户信息表!A2；在B3单元格中输入公式：=SUMIFS(销售订单表!$I$2:$I$181,销售订单表!$A$2:$A$181,"#1701*",销售订单表!$E$2:$E$181,$A3)，即由销售订单表中的数据计算一月份北京“和丰公司”购货的总金额。在N3单元格中输入公式：=SUM(B3:M3)；数据引用与计算结果如图所示。（3）在C3～N3中单元格公式的输入。应用自动填充的方法向右复制公式后，修改参数"#1701*"即可。如单元格C2中要将参数"#1701*"修改为"#1702*"即可。自动填充数据后结果如图所示。（4）应用自动填充的方法向下复制步骤（3）中的公式，客户统计表的制作效果如图所示。任务二：制作客户统计表、产品统计表、销售员统计表任务二：制作客户统计表、产品统计表、销售员统计表（5）用同样的方法制作产品统计表、销售员统计表，也可以复制客户统计表，做适当的修改得到产品统计表和销售员统计表。任务二：制作客户统计表、产品统计表、销售员统计表任务三：多级分类汇总“销售订单表”中的数据（1）打开“销售订单表”。（2）按照多关键字进行排序。选中所有数据信息，按照“区域”、“产品名称”、“客户名称”等字段进行排序，如图所示。（3）单击“确定”按钮，排序结果如图所示。任务三：多级分类汇总“销售订单表”中的数据（4）进行一级分类汇总。按照如图所示的分类汇总选择进行汇总。任务三：多级分类汇总“销售订单表”中的数据（5）单击“确定”按钮，按照“区域”一级分类汇总的结果如图所示。任务三：多级分类汇总“销售订单表”中的数据（6）按照右图所示修改二级分类汇总字段。任务三：多级分类汇总“销售订单表”中的数据（7）单击“确定”按钮，汇总结果如图所示。任务三：多级分类汇总“销售订单表”中的数据（8）添加三级分类汇总。再次打开【分类汇总】对话框，选择“客户名称”作为分类字段，汇总方式为“求和”，选定“销量”、“总金额”为汇总项。汇总结果如图所示。任务三：多级分类汇总“销售订单表”中的数据3．3知识点击3．3．1数据的排序3．3．2数据的筛选3．3．3数据的分类汇总3．3．4合并计算3．3．1数据的排序排序是将工作表中无规律的数据按某几列以升序或降序的形式排列起来。Excel允许对字符、数字等关键字数据按升序或降序排序。Excel提供了三级排序，分别为：主要关键字、次要关键字、第三关键字。不同类型的数据的排序规则数据类型：排序规则：数值按数值的大小字母按字母的先后顺序日期按日期的先后顺序汉字按汉语拼音的顺序或按笔画的顺序逻辑值升序时FALSE在前，TRUE在后，降序时相反空格总是排在最后一、简单排序简单排序是按照选中单元格所在列的数据默认进行升序或降序排序。首先，选取工作表中需要排序的所有单元格区域（注意：已经合并了的单元格不要选中）。最后，在【数据】菜单下的【排序和筛选】功能区中，单击【升序】或【降序】排序命令按钮即可。二、按主要关键字排序在排序操作中以某一个属性作为排序依据，该属性即为排序的主要关键字。在同一个工作表中，根据需要可以按不同的关键来排序。

排序对话框

按主要关键字对数据排序时，在【数据】菜单下的【排序和筛选】功能区中，单击【排序】命令按钮，弹出【排序】对话框，如图所示。二、按主要关键字排序在【排序】对话框中可以设置主要关键字、排序依据、次序等参数，还可以添加、删除、复制排序条件。二、按主要关键字排序【例3-1】在“学生成绩汇总表”中以“总分”为关键字，按“降序”排序。（1）打开“三班学生成绩表”，如图所示。二、按主要关键字排序（2）在【数据】选项卡【排序和筛选】功能区中，单击【排序】命令按钮，在弹出的【排序】对话框中设置主要关键字“总分”为排序关键字，如图所示。二、按主要关键字排序（3）单击“确定”按钮，排序结果如图所示。二、按主要关键字排序三、按多个关键字排序按主要关键字进行排序时，不可避免会出现相同的数据信息，通过添加其他条件进行排序，即按多个关键字进行排序，可以再区分这些有重复数据的信息。三、按多个关键字排序【例3-2】在【例3-1】基础上，对三班学生成绩，添加“ps平面图像处理”次关键字进行“降序”排序。（1）打开“三班学生成绩表”。（2）在【数据】选项卡【排序和筛选】功能区中，单击【排序】命令按钮，在弹出的【排序】对话框中单击“添加条件”按钮，在“次要关键字”右侧选择“ps平面图像处理”，“次序”改为“降序”，如图所示。

三、按多个关键字排序（3）单击“确定”按钮，即可按多关键字排序，排序结果如图所示。三、按多个关键字排序

四、自定义排序有时希望按照部门、职称、学历等自定义的序列进行排序。需要先将这些信息定义为序列，然后进行自定义排序。

四、自定义排序在【数据】选项卡【排序和筛选】功能区中，单击【排序】命令按钮，在弹出的【排序】对话框中，在“次序”下选择“自定义序列”，打开【自定义序列】对话框，如图所示。单击“添加”按钮，可以输入自定义序列。3．3．2数据的筛选自动筛选和高级筛选。两者区别：自动筛选是对表格中的数据筛选出来后，数据还在原来的表格上中；高级筛选是把表格中的数据筛选出来后，数据被复制到空白的单元格中。一、自动筛选一般用于简单的条件筛选，只显示符合条件的数据，将不满足条件的数据暂时隐藏起来。常见的字段类型有文本型、日期型、数字型等。自动筛选对应“文本筛选”、“日期筛选”、“数字筛选”等操作。【例3-3】利用“筛选”按钮下拉列表功能自动筛选，在“学生基本信息表”中，对文本字段类型进行自动筛选。（1）打开三班的“学生基本信息表”，如图所示。案例案例（2）单击工作表第5行“标题行”中任意一个单元格，在【数据】选项卡【排序和筛选】功能区中，单击【筛选】按钮，标题行所有字段右侧出现下三角形【筛选】按钮，如图所示。案例（3）单击标题行“性别”单元格右侧的筛选按钮，显示如图所示的筛选功能菜单。案例（4）取消“全选”前的“√”，选中“女”，单击“确定”按钮，筛选结果如图所示。案例案例【例3-4】利用“文本筛选”子菜单，在“学生基本信息表”中，对文本字段类型进行自动筛选。（1）接着【例3-3】的操作，单击“撤销”按钮，取消按照“性别”=“女”自动筛选结果。（2）单击标题行“籍贯”单元格右侧的筛选按钮，并选择筛选菜单下“文本筛选”右侧的子菜单“开头是…”，如图所示。案例（3）打开【自定义自动筛选方式】对话框，在“开头是”右侧文本下拉框中输入“河”，如图所示。案例（4）自定义自动筛选“籍贯”=“河”的结果如图所示。案例二、高级筛选高级筛选能够完成比较复杂的多条件查询,并能将筛选结果复制到其它位置。首先输入高级筛选条件，在要筛选的工作表的空白位置处输入所要筛选的条件。注意：每个筛选条件最好在一列输入，不同筛选条件在不同列；筛选条件的表头标题需要和数据表中表头一致；如果是两个及以上筛选条件，判断筛选条件输入在同一行表示为“与”的关系；判断筛选条件输入在不同的行表示为“或”的关系。案例【例3-5】筛选出“工资表”中“市场部实发工资大于7000”员工信息。（1）打开“工资表”，在空白处输入两个筛选条件，如图所示。案例（2）在【数据】选项卡【排序和筛选】功能区中，单击【高级】命令按钮，弹出【高级筛选】对话框，如右图所示。（3）单击【高级筛选】对话框“列表区域”右侧的单元格引用按钮，打开“高级筛选|列表区域”小窗体，如图所示。（4）选择区域：“$A$3:$I$101”，筛选的列表区域显示在图中。案例（5）单击“高级筛选-列表区域”右下方的单元格引用按钮，返回到【高级筛选】对话框中。（6）单击【高级筛选】对话框“条件区域”右侧的单元格引用按钮，打开“高级筛选-条件区域”选择的小窗体，选择区域：“$K$1:$L$2”，如图所示。案例（7）单击“高级筛选-条件区域”右下方的单元格引用按钮，返回到【高级筛选】对话框中，如图所示。案例（8）单击“确定”按钮，显示同时满足“微积分＞80”和“计算机组成原理＞90”的学生成绩信息，如图所示。案例3．3．3数据的分类汇总分类汇总是对工作表中的数据进行快速统计汇总的方法，使用分类汇总命令可以免去输入大量公式和函数的操作。执行分类汇总命令之前，首先确定哪一列字段数据是需要分类的，并对该列数据进行“排序”操作，保证该列中相同名称的数据紧挨在一起，然后再进行分类汇总。这是分类汇总使用前的重中之重。分类汇总时要在图所示的“分类汇总”对话框中进行选择。分类字段：需要选择在工作表中已排过序，并且有重复记录的名称；汇总方式：选择“求和”、“计数”、“平均值”、“最大值”等汇总方式；选定汇总项：选择的汇总字段项必须是数字类型。3．3．3数据的分类汇总主要内容一、建立分类汇总二、修改当前分类汇总信息一、建立分类汇总【例3-6】在“客户信息表”中，按照“区域”对客户“全年购货”金额进行分类汇总。（1）打开“客户信息表”，如图所示。（2）选择所有数据信息，在【数据】选项卡【排序和筛选】功能区中，单击【排序】命令按钮，打开排序对话框，按照主关键字“区域”进行升序，排序结果如图所示。一、建立分类汇总（3）点击任意单元格，在【数据】选项下的【分级显示】功能区中，单击【分类汇总】按钮，在“分类汇总”对话框中：选择“分类字段”下的“区域”字段，选择“汇总方式”下的“求和”选项；选择“选定汇总项”下的“全年购货（万元）”字段，如图所示。一、建立分类汇总（4）单击“确定”按钮，部分分类汇总结果及明细如图所示。一、建立分类汇总（5）折叠各级分类汇总信息。点击左上角面板中的“2”，折叠“区域”的各条详细记录，只显示按照“区域”汇总的全年购货总计，如图所示界面。一、建立分类汇总二、修改当前分类汇总信息【例3-7】在【例3-6】基础上，查看每个“销售员”的“全年购货”的“平均值”。（1）删除当前所有的分类汇总结果。选择所有单元格区域，打开【分类汇总】对话框，点击【分类汇总】对话框左下角的“全部删除”按钮，如图所示。（2）单击“确定”按钮，工作表恢复到分类汇总前的数据状态，如图所示。一、建立分类汇总（3）重新排序并选择分类字段及汇总方式。按照“销售员”进行升序排序，在【分类汇总】对话框中，选择“销售员”作为分类字段，选择“平均值”作为汇总方式，选择“全年购货（万元）”作为汇总项。（4）单击“确定”按钮，分类汇总结果如图所示。一、建立分类汇总3．3．4合并计算合并计算是对一个或者多个工作表的值进行汇总计算，工作表可以在同一个工作簿中或在不同的工作簿中，但合并计算结果可以在单个输出区域中显示。EXCEL合并计算功能可以求和、求平均值、计数、求最大值、求最小值等一系列合并功能。单表格合并计算多表格合并计算一、单表格合并计算单表格的合并计算，主要适用于在一个工作表中存在重复字段，而这些字段的其它信息需要进行合并求和等的简单合并计算。【例3-8】在销售订单表中，统计每个销售员2017年1月份的销售总量和销售总额。（1）打开“销售订单表”工作表，截取1月份销售记录，并调整列，存放在如图所示的工作表中。一、单表格合并计算（2）选择一个存放统计结果的起始单元格“F1”，在【数据】选项卡下【数据工具】功能区中，单击【合并计算】命令按钮，打开【合并计算】对话框，如图所示。一、单表格合并计算（3）在【合并计算】对话框中，单击“引用位置”下文本框右侧的单元格引用按钮，打开【合并计算-引用位置】小窗体。（4）选择工作表中需要合并计算的区域（选择时一定要把重复字段所在列作为首选列，例如“销售员”），所选区域地址为“$B$1:$D$20”，显示在【合并计算-引用位置】的窗体中，如图所示。一、单表格合并计算一、单表格合并计算（5）单击【合并计算-引用位置】窗体中地址区域右侧的单元格引用按钮，返回到【合并计算】对话框。在【合并计算】对话框中选择“标签位置”下方的“首行”和“最左列”选项，如图所示。一、单表格合并计算（6）单击“确定”按钮，每个销售员2017年1月份的销售总量和销售总额如图所示。默认情况下，合并计算结果显示的区域左上角字段是空着的，这时只需添上“销售员”字段即可。一、单表格合并计算二、多表格合并计算【例3-9】目前华东某公司三个子公司8月份销售情况存放在两个工作簿的不同工作表中，请使用多表格合并计算汇总8月份的销售情况。（1）打开“华东1分公司销售情况表”工作簿，如图所示；打开“华东2、3分公司销售情况表”工作簿，如图所示。二、多表格合并计算二、多表格合并计算（2）新建第四个工作表，用于存放汇总后的数据信息。在“华东1分公司销售情况表”工作簿中新建一个空白工作表，命名为“华东公司8月份销售汇总表”，将鼠标定位到“A2”单元格。（3）在【数据】选项卡下【数据工具】功能区中，单击【合并计算】按钮，打开【合并计算】对话框。在【合并计算】对话框中进行如下操作：二、多表格合并计算①在“函数”下选择“求和”函数；②单击“引用位置”下文本框右侧的单元格引用按钮，出现【合并计算-引用位置】小窗体，鼠标切换至当前“华东1分公司销售情况表”工作簿的sheet1工作表中，选择华东1公司8月份的所有数据区域“Sheet1!$A$2:$C$9”，如图所示。二、多表格合并计算二、多表格合并计算③单击【合并计算-引用位置】小窗体右下侧的单元格引用按钮，返回到【合并计算】对话框。④单击“所有引用位置”右侧的“添加”按钮，步骤②中的地址区域“Sheet1!$A$2:$C$9”添加到“所有引用位置”列表下，如图所示。二、多表格合并计算二、多表格合并计算⑤重复②至④的步骤，将工作簿2中华东2、3公司的所有数据区域，添加到【合并计算】对话框下“所有引用位置”的列表下，选中“标签位置”下面的“首行”和“最左列”，如图所示。二、多表格合并计算⑥单击“确定”按钮，三个子公司8月份销售情况汇总在工作表“华东公司8月份销售汇总表”中，在汇总区域的左上角添加“商品名称”字段，如图所示。二、多表格合并计算（4）单击左边面板的“+”，展开查看各个“商品名称”合并计算结果明细的来源，如图所示。二、多表格合并计算3．4实战训练对“学生成绩”数据进行高级筛选对“薪资管理”数据按多个关键字自定义排序3.4.1对“学生成绩”数据进行高级筛选

任务一：筛选出“学生成绩汇总表”中同时满足“微积分＞80”和“计算机组成原理＞90”的学生成绩信息。任务二：筛选出“学生成绩汇总表”中同时满足“微积分＞80”和“计算机组成原理＞90”，或者满足“总分>350”的学生成绩信息。任务一筛选出“学生成绩汇总表”中同时满足“微积分＞80”和“计算机组成原理＞90”的学生成绩信息。（1）打开三班的“学生成绩汇总表”，在空白处输入两个筛选条件，如图所示。任务一（2）在【数据】选项卡【排序和筛选】功能区中，单击【高级】命令按钮，弹出【高级筛选】对话框，如图所示。任务一（3）单击【高级筛选】对话框“列表区域”右侧的单元格引用按钮，打开“高级筛选-列表区域”小窗体，如图所示。（4）选择区域：“$A$5:$I$54”，筛选的列表区域显示在图中。（5）单击“高级筛选-列表区域”右下方的单元格引用按钮，返回到【高级筛选】对话框中。（6）单击【高级筛选】对话框“条件区域”右侧的单元格引用按钮，打开“高级筛选-条件区域”选择