实训二十Excel的数据处理

1、实训二十Excel的数据处理实训目的掌握自动筛选和高级筛选功能。掌握数据的分类汇总方法和分级显示数据表。掌握数据的合并计算。掌握数据透视表的创建、使用和设置。会利用模拟运算表求解。实训内容及步骤【据筛选打开“实训20”文件夹下的工作簿“数据筛选”。使用“财政支出表”中的数据，筛选出“优抚”大于70万元且“地区”为“胡宁”的各行。使用“工资表1”中的数据，筛选出“科室”名的第二个汉字为“务”的行；使用“抽样调查表”中的数据，筛选出“食品”小于87.35,并且“日常生活用品”大于等于89.30的记录。使用“工资表2”，利用高级筛选的方法，筛选出“教务科”与“财务科”中的“基本工资大于2000元而小

2、于5000”的人员。使用“成绩表”，利用高级筛选的方法，筛选出姓名为姓“石”或姓“杨”的“总评”成绩大于等于70分而小于90分的行。操作步骤：1、打开“实训20”文件夹下的工作簿“数据筛选”。2、单击“财政支出表”工作表的标签，将光标放到工作表有数据的任意位置，单击数据一筛选一自动筛选，单击“优抚”旁边的下拉列表按钮，单击“自定义”按钮，打开图20-1所示的“自定义自动筛选方式”对话框。按下图进行设置后，单击“确定”按钮；单击“地区”旁边的下拉列表按钮，选择“胡宁”。图20-1“自定义自动筛选方式”对话框3、单击“工资表1”工作表的标签，将光标放到工作表有数据的任意位置，单击数据一筛选一自动筛

3、选，单击“科室”旁边的下拉列表按钮，单击“自定义”按钮，打开“自定义自动筛选方式”对话框。按下图进行设置后，单击“确定”按钮。（注意:问号必须是英文的问号）图202“自定义自动筛选方式”对话框4、步骤与前面基本相同，按下面2个图进行设置。图20-3“自定义自动筛选方式”对话框图204“自定义自动筛选方式”对话框5、单击“工资表2”工作表的标签，先按下图所示输入条件；将光标放到工作表有数据的任意位置，单击数据一筛选一高级筛选，按图205进行设置后，单击“确定”按钮。迈MicrosoftExcel一致捉瓶选匚BjX暫文件逻)编?5(E)讽图(丫)插入(!)格式工貝)数窗口帘助QP(9xE10A10

4、0123456789101112131415161718mqI事事务务事事务务务务科人人数教人人数财财财本名业珍洁兰国根桃洪帆平振晓圣建卫达晓圣卫姓哀石杨杨石石石杨杨石1000.1000.100.kilUli.2100.5000.1000.5000.2000.8000.高级辯迭区I懿据区域(X)|$A$1：$H$11玉J务件区M()：|工资滾2!如$13:SE$1：玉J复制到：|工资表2朮曲17TI厂选捋不重复的记录确定|职消方式r在匣有区域品示筛选结果(f)枪筛选结呆复制到其他位査)基本工资基本工资200020005000匚发工资1085.001085.00300.002131.902130

5、.004980.001940.007200.002035.007735.00数字图20-56、单击“成绩表”工作表的标签，先按图20-6所示输入条件；将光标放到工作表有数据的任意位置，单击数据一筛选一高级筛选，按下图进行设置后，单击“确定”按钮。图20-6【据分类汇总1、打开“实训20”文件夹下的工作簿“数据分类汇总”。2、将“工资表”中的数据，以“科室”为分类字段，将“基本工资”、“补助工资”、“应发工资”、“扣款”和“实发工资”进行“求和”分类汇总。3、将“考生登记表”中的数据，以“模块”为分类字段，将“总分”进行“最大值”分类汇总。4、将“部分装饰材料价格表”中的数据，以“商品名称”为分

6、类字段，将“最高价格”和“最低价格”进行“平均值”分类汇总。5、将“销售表”中的数据，先以“销售人员”为分类字段，将“销售数量”进行“求和”分类汇总，然后以“类型”分类字段，将“销售数量”进行“求和”的嵌套式分类汇总。操作步骤：1、打开“实训20”文件夹下的工作簿“数据分类汇总”。2、单击“工资表”工作表的标签，将光标放到工作表“科室”列，单击常用工具栏中的升序或降序按钮身J艮I,使同一个科室的集中在一块区域，将光标放在工作表任意有数据的位置，单击数据一分类汇总，打开图20-7所示的“分类汇总”对话框，按下图进行设置后，单击“确定”按钮。图20-7“分类汇总”对话框3、单击“考生登记表”工作表

7、的标签，将光标放到工作表“模块”列，单击常用工具栏中的升序或降序按钮令IK使同一个模块的集中在一块区域，将光标放在工作表任意有数据的位置，单击数据一分类汇总，打开“分类汇总”对话框，按下图进行设置后，单击“确定”按钮。图20-8“分类汇总”对话框图20-9“分类汇总”对话框4、单击“部分装饰材料价格表”工作表的标签，将光标放到工作表“商品名称”列，单击常用工具栏中的升序或降序按钮令I殳丄，使同一个商品需称”的集中在一块区域，将光标放在工作表任意有数据的位置，单击数据一分类汇总，打开“分类汇总”对话框，按下图进行设置后，单击“确定”按钮。5、单击“销售表”工作表的标签，将光标放在工作表任意有数据

8、的位置，单击数据排序，打开“排序”对话框，按图20-10进行设置。单击数据一分类汇总，打开“分类汇总”对话框，按图20-11进行设置后，单击“确定”按钮；再单击数据一分类汇总,打开“分类汇总”对话框，按下图20-12进行设置后，单击“确定”按钮。图20-10“排序”对话框图20-11“分类汇总”对话框图20-12三、合并计算1打开“实训20”文件夹下的工作簿“合并计算”。使用“课程安排表”工作表中的相关数据，在“课程安排统计表”中进行“求和”合并计算。使用“纽约汇市预测”工作表中的相关数据，在“纽约汇市开盘预测平均值”中进行均值”合并计算。4使用“统计表”工作表的“上半年各车间产品合格情况表”

9、和下半年各车间产品合格情况表”中的数据，在“全年各车间产品合格情况统计表”中进行“求和”合并计算。操作步骤：1、打开“实训20”文件夹下的工作簿“合并计算”。2、单击“课程安排表”工作表的标签，将光标放在“课程名称”的下面，如图20-13；单击数据一合并计算，打开“合并计蛋”对话框，在对话框中选择“函数”为“求和”;单击“引用位置”的“引用”按钮国，选择B3-D27区域的数据，单击“添加”按钮,在“标签位置”中选择“最左列”，如下图所示。图20-133、单击“纽约汇市预测”工作表的标签，将光标放在“价位”的下面，如图20-14；单击数据一合并计算，打开“合并计塁”对话框，在对话框中选择“函数”

10、为“平均值”;单击“引用位置”的“引用”按钮包，选择A3-F8区域的数据，单击“添加”按钮，在“标签位置”中选择“最左列”，如下图所示。图20-144、单击“统计表”工作表的标签，将光标放在“不合格产品（个）”的下面，如图20-15；单击数据合并计算，打开“合并计皐：对话框，在对话框中选择“函数”为“求和”；单击“引用位置”的“引用”按钮国，兰择C3-E10区域的数据，单击“添加”按钮；再单击“引用位置”的“引用”按钮玉，选择C14-E21区域的数据，单击“添加”按钮；此处是纯数据，“标签位置”的“最左列”不能选择打勾，如下图所示。图20-15四、1、打开实训20文件夹下的原文文件夹中的工作簿

11、“数据透视表”；2、以工作表“各季度各车间合格情况表”中的数据，以“产品规格”为分页，以“季度”为行字段，以“车间”为列字段，以“不合格产品”、“合格产品”和“总数”为求和项，从Sheetl工作表的A1单元格起建立数据透视表。3、在透视表结果中查看产品型号为G-05的产品的情况。操作步骤：1、打开实训20文件夹下的原文文件夹中的工作簿“数据透视表”；2、单击工作表“各季度各车间合格情况表”的标签，将光标放在工作表中的任意有数据的单元格，单击数据一数据透视表和数据透视图，打开“数据透视标和数据透视图向导一3步骤之1”对话框，在对话框中选择“数据透视表”（如图20-16所示），单击下一步按钮。打开

12、“数据透视标和数据透视图向导一3步骤之2”对话框，单击“下一步”按钮，打开“数据透视标和数据透视图向导一3步骤之3”对话框，在对话框中单击“布局”按钮，打开“数据透视标和数据透视图向导一布局”对话框，将“产品规格”拖到页，“季度”拖到行，“车间”拖到列，“不合格产品”、“合格产品”和“总数”拖到数据区域（如图20-19所示），在对话框中单击“确定”按钮，返回到“数据透视标和数据透视图向导一3步骤之3”对话框，在“数据透视标和数据透视图向导一3步骤乙3”对话框中选择数据透视表为“现有工作表”，单击“现有工作表”旁边的引用按钮国，单击sheetl工作表的A1单元格，出现图20-20所示的结果，单击

13、“完成”按钮，则在sheetl工作表中显示透视表结果。在结果中单击“产品规格”“全部”旁边的下拉列表按钮，选择型号“G-05”（如图20-21所示）。图20-16数据透视表和数据透视图向导-3步驟之2图20-17图20-18图20-19冈数据透视表显示位置：r新越工作表）席现有工作表（M）|Sheetl!$A$l|51单击完成抹餌创連数据透视表布局）.|选项Q）.|职消|上一步）|厂11完成0）|图20-20图20-21五、数据分析学校总评分数=平时成绩*20%十期中成绩*30%+期末成绩*50%,现在己知期中成绩为70,期末成绩为67,问当该学生的平时成绩分别为50、55、60、65、70、

14、75、80、85、90时，其总评分数为多少？（操作结果参看“实训10”文件夹下的工作簿“数据分析”中的工作表“单变量分析1”或“单变量分析2”）。某个人想存一笔钱，己知其本金是50000元，他想知道当年年利率与年限取不同值，到期的回报是多少？（操作结果参看“实训10”文件夹下的工作簿“数据分析”I4MSheet1/Sheet2/Sheet3/就绪图20-23中的工作表“双变量分析”)。本利和=本金x1+年利率年W12.第1题的操作步骤：新建工作表，按图20-22所示填好有关数据，在D2处填入公式“A2*20%+B2*30%+C2*50%”；在A4与A5单元格中分别填入50、55,然后选中这两个

15、单元格，拖动A5的“填充柄”，填充数据。在D3填入“=D2”。ABCDEFGHI1平吋成绩期中成绩期末成绩总评成绩260706766.5366.5450555660765870975102011185Tir-nlZIBook2图20-22选中A3到D12这长方形区域，单击菜单栏中的“数据”一单击菜单项“模拟运算表”，如图20-23所示，一弹出图20-24所示的对话框，一在“输入引用列的单元格”处输入A2-单击“确定S得到如图20-25所示的结果。3MicrosoftExcel-Book2-|g|x|囲立件代)编揚他)视因住)插入(!)格式(Q)工旦数据)窗口毗)帮助(H)D昌鸟尊X能危宋体hI

16、CIDI平时成绩期中成绩期末成绩总评戍绩60170lEfl66.5R2旦壬固mc裂排序筛逸)分类汇总型有效性(！)123456模拟运算表0).1213505560657075808590茨取外部数据)!更新数据分列g)圄数据逶视表和勿表抿告(P)JjJ输入引用行的单元格:I隹I输入引用列的单元格(C):rafI确走：n取消I图20-24模拟运算表图20-25单变量分析结果第2题的操作步骤：新建工作表，按图20-26填好有关数据，在D2处填入公式“=A2*(1+B2/12)A(C2*12)”。在B列中输入“年利率”的取值。在第四行输入“年限”的不同值。在B4处填入“=D2”。图20-26图20-

17、28双变量分析结果选中B4到G14之间的单元格，单击菜单栏中的“数据”一单击菜单项“模拟运算表”，一弹出图20-27所示的对话框，一在“输入引用行的单元格”处输入C2-在“输入引用列的单元格”处输入B2-单击“确定S得到如图2028所示的结果。图20-27gMicrosoftEccel-教案例子回说|團文件0）编辑）视因辺插入I格式工具辽）数据迦窗口址）帮助I-laiX|IyiIa&躍o|c丁|sasi啦巧i叱国.宋体B崖1%埼专尸自动套用格式窗AA015d=1ABCDEFG三1本金年利率年限本息.2500004.50%562589.79134年限62589.79123455350%51778

18、.3553619.9555527.0457501.9759547.146400%52037.0854157.1556363.5958659.9361049.837450%52296.9954699.5157212.3959840.7262589.798500%52558.0955247.0758073.6161044.7764167.939550%52820.3955799.8858947.4362272.5365785.1910600%53083.895635匚9959834.0363524.4667442.5111650%53348.5956921.4560733.5864801.0269

19、140.87127.00%53614.5057490.3061646.2866102.6970881.26137.50%53881.6358064.6062572.3167429.9672664.72148.00%54149.9858644.4063511.8568783.3174492.29I1一.11lfZJmN1、乂戈变呈分析2/销售数据表/销售数据表排序4|II_IImJI_I_I六、自主操作练习J一、打开文档ks7-l.xls,将该文件以文件名为“A7-l.xls”保存到自己的文件夹中。按下列要求操作。1、使用sheetlI作表中的数据，统计“总分”并计算“各科平均分”，结果分别放在

20、相应的单元格中。2、使用sheet2工作表中的数据，以“总分”为主要关键字，“数学”为次要关键字,升序排序。3、使用sheet3工作表中的数据，筛选出各科分数均大于等于80的记录。4、使用sheet4工作表中的相关数据，在“各班各科成绩表”中进行“平均值”合并计算。5、使用sheets工作表中的数据，以“班级”为分类字段，将各科成绩进行“平均值”分类汇总。6、使用“数据源”工作表中的数据，布局以“班级”为分页，以“日期”为行字段，以“姓名”为列字段，以“迟到”为计数项，从sheet6I作表的Al单元格起建立数据透视表。二、打开文档ks7-2.xls,将该文件以文件名为“A7-2.xls”保存到

21、自己的文件夹中。按下列要求操作。1、使用sheetl工作表的数据，计算“实发工资”，结果放在相应的单元格中。2、使用sheet2工作表中的数据，以“基本工资”为主要关键字，降序排序。3、使用sheet3工作表中的数据，筛选出“部门”为工程部并且“基本工资”大于等于900的记录。4、使用sheet4工作表“利达公司一月份所付工程原料款”和“利达公司二月份所付工程原料款”中的数据，在“利达公司前两个月所付工程原料款”中进行“求和”合并计算。5、使用sheets工作表中的数据，以“部门”为分类字段，将“基本工资”与“实发工资”进行“平均值”分类汇总。6、使用“数据源”工作表中的数据，以“项目工程”为

22、分页，以“原料”为行字段，以日期”为列字段，以“金额”为求和项，从sheet6I作表Al单元格起建立及数据透视表。三、打开文档KS7-3,将该文件以文件名为“A7-3.xls”保存到自己的文件夹中。按下列要求操作。1、使用sheetl工作表中的数据，计算“合格率”，结果分别放在相应的单元格中。2、使用sheet2工作表中的数据，以“总数”为主要关键字，降序排序。3、使用sheet3工作表中的数据，筛选出“合格产品”大于或等于5500的记录，“不合格产品”小于或等于200的记录。4、使用sheet4工作表“上半年各车间产品合格情况表”和“下半年各车间产品合格情况表”中的数据，在“全年各车间产品合格情况统计表”中进行“求和”合并计算。5、使用sheets工作表中的数据，以“产品型号”为分类字段，将“不合格产品”、“合格产品”及“总数”分别进行“求和”分类汇总。6、使用“数据源”工作表中的数据，以“产品规格”为分页，以“季度”为行字段，以“车间”为列字段，以“不合格产品”、“合格产品”和“总数”为求和项，从sheet6工作表的Al单元格起建立数据透视表。四、打开文档KS7-4,将该文件以文件名为“A7-4.xls”保存