实训二十Excel 的数据处理Word格式文档下载.docx
- 文档编号:21904358
- 上传时间:2023-02-01
- 格式:DOCX
- 页数:16
- 大小:703.91KB
实训二十Excel 的数据处理Word格式文档下载.docx
《实训二十Excel 的数据处理Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《实训二十Excel 的数据处理Word格式文档下载.docx(16页珍藏版)》请在冰豆网上搜索。
5.使用“工资表2”,利用高级筛选的方法,筛选出“教务科”与“财务科”中的“基本工资大于2000元而小于5000”的人员。
6.使用“成绩表”,利用高级筛选的方法,筛选出姓名为姓“石”或姓“杨”的“总评”成绩大于等于70分而小于90分的行。
操作步骤:
1、打开“实训20”文件夹下的工作簿“数据筛选”。
2、单击“财政支出表”工作表的标签,将光标放到工作表有数据的任意位置,单击数据—筛选—自动筛选,单击“优抚”旁边的下拉列表按钮,单击“自定义”按钮,打开图20-1所示的“自定义自动筛选方式”对话框。
按下图进行设置后,单击“确定”按钮;
单击“地区”旁边的下拉列表按钮,选择“胡宁”。
图20-1“自定义自动筛选方式”对话框
3、单击“工资表1”工作表的标签,将光标放到工作表有数据的任意位置,单击数据—筛选—自动筛选,单击“科室”旁边的下拉列表按钮,单击“自定义”按钮,打开“自定义自动筛选方式”对话框。
按下图进行设置后,单击“确定”按钮。
(注意:
问号必须是英文的问号)
图20-2“自定义自动筛选方式”对话框
4、步骤与前面基本相同,按下面2个图进行设置。
图20-3“自定义自动筛选方式”对话框
图20-4“自定义自动筛选方式”对话框
5、单击“工资表2”工作表的标签,先按下图所示输入条件;
将光标放到工作表有数据的任意位置,单击数据—筛选—高级筛选,按图20-5进行设置后,单击“确定”按钮。
图20-5
6、单击“成绩表”工作表的标签,先按图20-6所示输入条件;
将光标放到工作表有数据的任意位置,单击数据—筛选—高级筛选,按下图进行设置后,单击“确定”按钮。
图20-6
二、数据分类汇总
1、打开“实训20”文件夹下的工作簿“数据分类汇总”。
2、将“工资表”中的数据,以“科室”为分类字段,将“基本工资”、“补助工资”、“应发工资”、“扣款”和“实发工资”进行“求和”分类汇总。
3、将“考生登记表”中的数据,以“模块”为分类字段,将“总分”进行“最大值”分类汇总。
4、将“部分装饰材料价格表”中的数据,以“商品名称”为分类字段,将“最高价格”和“最低价格”进行“平均值”分类汇总。
5、将“销售表”中的数据,先以“销售人员”为分类字段,将“销售数量”进行“求和”分类汇总,然后以“类型”分类字段,将“销售数量”进行“求和”的嵌套式分类汇总。
2、单击“工资表”工作表的标签,将光标放到工作表“科室”列,单击常用工具栏中的升序或降序按钮
,使同一个科室的集中在一块区域,将光标放在工作表任意有数据的位置,单击数据—分类汇总,打开图20-7所示的“分类汇总”对话框,按下图进行设置后,单击“确定”按钮。
图20-7“分类汇总”对话框
3、单击“考生登记表”工作表的标签,将光标放到工作表“模块”列,单击常用工具栏中的升序或降序按钮
,使同一个模块的集中在一块区域,将光标放在工作表任意有数据的位置,单击数据—分类汇总,打开“分类汇总”对话框,按下图进行设置后,单击“确定”按钮。
图20-8“分类汇总”对话框图20-9“分类汇总”对话框
4、单击“部分装饰材料价格表”工作表的标签,将光标放到工作表“商品名称”列,单击常用工具栏中的升序或降序按钮
,使同一个“商品名称”的集中在一块区域,将光标放在工作表任意有数据的位置,单击数据—分类汇总,打开“分类汇总”对话框,按下图进行设置后,单击“确定”按钮。
5、单击“销售表”工作表的标签,将光标放在工作表任意有数据的位置,单击数据排序,打开“排序”对话框,按图20-10进行设置。
单击数据—分类汇总,打开“分类汇总”对话框,按图20-11进行设置后,单击“确定”按钮;
再单击数据—分类汇总,打开“分类汇总”对话框,按下图20-12进行设置后,单击“确定”按钮。
图20-10“排序”对话框图20-11“分类汇总”对话框图20-12
三、合并计算
1.打开“实训20”文件夹下的工作簿“合并计算”。
2.使用“课程安排表”工作表中的相关数据,在“课程安排统计表”中进行“求和”合并计算。
3.使用“纽约汇市预测”工作表中的相关数据,在“纽约汇市开盘预测平均值”中进行“均值”合并计算。
4.使用“统计表”工作表的“上半年各车间产品合格情况表”和“下半年各车间产品合格情况表”中的数据,在“全年各车间产品合格情况统计表”中进行“求和”合并计算。
1、打开“实训20”文件夹下的工作簿“合并计算”。
2、单击“课程安排表”工作表的标签,将光标放在“课程名称”的下面,如图20-13;
单击数据—合并计算,打开“合并计算”对话框,在对话框中选择“函数”为“求和”;
单击“引用位置”的“引用”按钮
,选择B3-D27区域的数据,单击“添加”按钮,在“标签位置”中选择“最左列”,如下图所示。
图20-13
3、单击“纽约汇市预测”工作表的标签,将光标放在“价位”的下面,如图20-14;
单击数据—合并计算,打开“合并计算”对话框,在对话框中选择“函数”为“平均值”;
,选择A3-F8区域的数据,单击“添加”按钮,在“标签位置”中选择“最左列”,如下图所示。
图20-14
4、单击“统计表”工作表的标签,将光标放在“不合格产品(个)”的下面,如图20-15;
,选择C3-E10区域的数据,单击“添加”按钮;
再单击“引用位置”的“引用”按钮
,选择C14-E21区域的数据,单击“添加”按钮;
此处是纯数据,“标签位置”的“最左列”不能选择打勾,如下图所示。
图20-15
四、数据透视表和数据透视图
1、打开实训20文件夹下的原文文件夹中的工作簿“数据透视表”;
2、以工作表“各季度各车间合格情况表”中的数据,以“产品规格”为分页,以“季度”为行字段,以“车间”为列字段,以“不合格产品”、“合格产品”和“总数”为求和项,从Sheet1工作表的A1单元格起建立数据透视表。
3、在透视表结果中查看产品型号为G-05的产品的情况。
2、单击工作表“各季度各车间合格情况表”的标签,将光标放在工作表中的任意有数据的单元格,单击数据—数据透视表和数据透视图,打开“数据透视标和数据透视图向导—3步骤之1”对话框,在对话框中选择“数据透视表”(如图20-16所示),单击下一步按钮。
打开“数据透视标和数据透视图向导—3步骤之2”对话框,单击“下一步”按钮,打开“数据透视标和数据透视图向导—3步骤之3”对话框,在对话框中单击“布局”按钮,打开“数据透视标和数据透视图向导—布局”对话框,将“产品规格”拖到页,“季度”拖到行,“车间”拖到列,“不合格产品”、“合格产品”和“总数”拖到数据区域(如图20-19所示),在对话框中单击“确定”按钮,返回到“数据透视标和数据透视图向导—3步骤之3”对话框,在“数据透视标和数据透视图向导—3步骤之3”对话框中选择数据透视表为“现有工作表”,单击“现有工作表”旁边的引用按钮
,单击sheet1工作表的A1单元格,出现图20-20所示的结果,单击“完成”按钮,则在sheet1工作表中显示透视表结果。
在结果中单击“产品规格”“全部”旁边的下拉列表按钮,选择型号“G-05”(如图20-21所示)。
图20-16
图20-17
图20-18
图20-19
图20-20
图20-21
五、数据分析
1.学校总评分数=平时成绩*20%+期中成绩*30%+期末成绩*50%,现在已知期中成绩为70,期末成绩为67,问当该学生的平时成绩分别为50、55、60、65、70、75、80、85、90时,其总评分数为多少?
(操作结果参看“实训10”文件夹下的工作簿“数据分析”中的工作表“单变量分析1”或“单变量分析2”)。
2.某个人想存一笔钱,已知其本金是50000元,他想知道当年年利率与年限取不同值,到期的回报是多少?
(操作结果参看“实训10”文件夹下的工作簿“数据分析”中的工作表“双变量分析”)。
第1题的操作步骤:
1新建工作表,按图20-22所示填好有关数据,在D2处填入公式“A2*20%+B2*30%+C2*50%”;
2在A4与A5单元格中分别填入50、55,然后选中这两个单元格,拖动A5的“填充柄”,填充数据。
3在D3填入“=D2”。
图20-22
4选中A3到D12这长方形区域,单击菜单栏中的“数据”→单击菜单项“模拟运算表”,如图20-23所示,→弹出图20-24所示的对话框,→在“输入引用列的单元格”处输入A2→单击“确定”,得到如图20-25所示的结果。
图20-23
图20-24模拟运算表
图20-25单变量分析结果
第2题的操作步骤:
1新建工作表,按图20-26填好有关数据,在D2处填入公式“=A2*(1+B2/12)^(C2*12)”。
2在B列中输入“年利率”的取值。
3在第四行输入“年限”的不同值。
4在B4处填入“=D2”。
图20-26
5选中B4到G14之间的单元格,单击菜单栏中的“数据”→单击菜单项“模拟运算表”,→弹出图20-27所示的对话框,→在“输入引用行的单元格”处输入C2→在“输入引用列的单元格”处输入B2→单击“确定”,得到如图20-28所示的结果。
图20-27
图20-28双变量分析结果
六、自主操作练习题
一、打开文档ks7-1.xls,将该文件以文件名为“A7-1.xls”保存到自己的文件夹中。
按下列要求操作。
1、使用sheet1工作表中的数据,统计“总分”并计算“各科平均分”,结果分别放在相应的单元格中。
2、使用sheet2工作表中的数据,以“总分”为主要关键字,“数学”为次要关键字,升序排序。
3、使用sheet3工作表中的数据,筛选出各科分数均大于等于80的记录。
4、使用sheet4工作表中的相关数据,在“各班各科成绩表”中进行“平均值”合并计算。
5、使用sheet5工作表中的数据,以“班级”为分类字段,将各科成绩进行“平均值”分类汇总。
6、使用“数据源”工作表中的数据,布局以“班级”为分页,以“日期”为行字段,以“姓名”为列字段,以“迟到”为计数项,从sheet6工作表的A1单元格起建立数据透视表。
二、打开文档ks7-2.xls,将该文件以文件名为“A7-2.xls”保存到自己的文件夹中。
1、使用sheet1工作表的数据,计算“实发工资”,结果放在相应的单元格中。
2、使用sheet2工作表中的数据,以“基本工资”为主要关键字,降序排序。
3、使用sheet3工作表中的数据,筛选出“部门”为工程部并且“基本工资”大于等于900的记录。
4、使用sheet4工作表“利达公司一月份所付工程原料款”和“利达公司二月份所付工程原料款”中的数据,在“利达公司前两个月所付工程原料款”中进行“求和”合并计算。
5、使用sheet5工作表中的数据,以“部门”为分类字段,将“基本工资”与“实发工资”进行“平均值”分类汇总。
6、使用“数据源”工作表中的数据,以“项目工程”为分页,以“原料”为行字段,以“日期”为列字段,以“金额”为求和项,从sheet6工作表A1单元格起建立及数据透视表。
三、打开文档KS7-3,将该文件以文件名为“A7-3.xls”保存到自己的文件夹中。
1、使用sheet1工作表中的数据,计算“合格率”,结果分别放在相应的单元格中。
2、使用sheet2工作表中的数据,以“总数”为主要关键字,降序排序。
3、使用sheet3工作表中的数据,筛选出“合格产品”大于或等于5500的记录,“不合格产品”小于或等于200的记录。
4、使用sheet4工作表“上半年各车间产品合格情况表”和“下半年各车间产品合格情况表”中的数据,在“全年各车间产品合格情况统计表”中进行“求和”合并计算。
5、使用sheet5工作表中的数据,以“产品型号”为分类字段,将“不合格产品”、“合格产品”及“总数”分别进行“求和”分类汇总。
6、使用“数据源”工作表中的数据,以“产品规格”为分页,以“季度”为行字段,以“车间”为列字段,以“不合格产品”、“合格产品”和“总数”为求和项,从sheet6工作表的A1单元格起建立数据透视表。
四、打开文档KS7-4,将该文件以文件名为“A7-4.xls”保存到自己的文件夹中。
1、使用sheet1工作表中的数据,计算三个月“总计”,结果分别放在相应的单元格中。
2、使用sheet2工作表中的数据,以“一月”为主要关键字,升序排序。
3、使用sheet3工作表中的数据,筛选出“二月”大于或等于80000的记录。
4、使用sheet4工作表“家家惠超市上半年各连锁店销售情况表”和“家家惠超市下半年各连锁店销售情况表”中的数据,在“家家惠超市全年各连锁店销售情况表”中进行“求和”合并计算。
5、使用sheet5工作表中的数据,以“销售区间”为分类字段,将各月销售额分别进行“求和”分类汇总。
6、使用“数据源”工作表中的数据,以“销售区间”为分页,以“类别”为行字段,以“月份”为列字段,以“销售额”为求和项,从sheet6工作表的A1单元格起建立数据透视表。
五、打开文档KS7-7,将该文件以文件名为“A7-7.xls”保存到自己的文件夹中。
1、使用sheet1工作表中的数据,计算三个月“最小值”和“每季度总计”,结果分别放在相应的单元格中。
2、使用sheet2工作表中的数据,以“第四季度”为主要关键字,升序排序。
3、使用sheet3工作表中的数据,筛选出“第三季度”和“第四季度”大于或等于50000的记录。
4、使用sheet4工作表“第一季度各家电城彩电销售情况表”和“第二季度各家电城彩电销售情况表”中的数据,在“上半年各家电城彩电销售情况表”中进行“求和”合并计算。
5、使用sheet5工作表中的数据,以“品牌”为分类字段,将四个季度的销售量分别进行“平均值”分类汇总。
6、使用“数据源”工作表中的数据,以“经销商”为分页,以“品牌”为行字段,以“型号”为列字段,以“数量”为求和项,从sheet6工作表的A1单元格起建立数据透视表。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实训二十 Excel 的数据处理 数据处理