题目及操作步骤参考.doc

EXCEL 2003 案例精选一、在当前文件夹中，已有文件：原始文件.xls。按下列要求操作，并将结果存盘。1. 使用REPLACE函数，对Sheet1中的员工代码进行升级，要求：a升级方式：在PA或PB后面加上0；b将升级后的员工代码结果填入表中的“升级员工代码”列中。2使用时间函数，对Sheet1中员工的“年龄”和“工龄”进行计算，并将结果填入到表中的“年龄”列和“工龄”列中。3使用统计函数，对Sheet1中的数据，根据以下统计条件进行如下统计：a统计男性员工的人数，结果填入N3单元格中；b统计高级工程师人数，结果填入N4单元格中；c统计工龄大于等于10的人数，结果填入N5单元格中。4使用逻辑函数，判断员工是否有资格评“高级工程师”。评选条件为：工龄大于20，且为工程师的员工。5将Sheet1复制到Sheet2中，并对Sheet2进行高级筛选，要求：a筛选条件为：“性别”-男，“年龄”30，“工龄”=10，“职称”-助工；b将结果保存在Sheet2中。6. 根据Sheet1中的数据，创建一张数据透视图Chart1，要求： a. 显示工厂中各个职称的人数； b. x坐标设置为“职称”； c. 计数项为职称； d. 将对应的数据透视表保存在Sheet3中。操作步骤：打开当前文件夹中的“原始文件.xls”文件；1、 在工作表Sheet1中，单击C2单元格，输入公式：=REPLACE(B2:B65,3,0,0)，并按Enter键确认； 拖动填充柄完成其他单元格的计算。2、（1） 在工作表Sheet1中，单击F2单元格，输入公式：=YEAR(TODAY()-YEAR(E2)，并按Enter键确认； 拖动填充柄完成其他单元格的计算。 （2） 在工作表Sheet1中，单击H2单元格，输入公式：=YEAR(TODAY()-YEAR(G2) ，并按Enter键确认； 拖动填充柄完成其他单元格的计算。3、（1）在工作表Sheet1中，单击N3单元格，输入公式：=COUNTIF(D2:D65,男) ，并按Enter键确认； （2）在工作表Sheet1中，单击N4单元格，输入公式：=COUNTIF(I2:I65,高级工程师)，并按Enter键确认。 （3）在工作表Sheet1中，单击N5单元格，输入公式：=COUNTIF(H2:H65,=10)，并按Enter键确认。4、在工作表Sheet1中，单击K2单元格，输入公式：=AND(H2=20,I2=工程师)，并按Enter键确认。操作结果如图3-1、3-2所示。图3-1 第1、2、4题操作结果图3-2 第3题操作结果5、（1）选定Sheet1，将其复制到Sheet2中，单击Sheet2，在B68：E69数据区中建立条件区域：性别年龄工龄职称男3010助工 （2）单击“数据”“筛选”中的“高级筛选”命令项，打开“高级筛选”对话框，按如图3-3所示进行操作。图3-3 “高级筛选”对话框操作结果如图3-4所示。图3-4 “高级筛选”筛选结果6、选择Sheet1工作表，并单击工作表中任一单元格。 单击“数据”“数据透视表和数据透视图”，打开“数据透视表和数据透视图向导3步骤之1”对话框，如图3-5所示，选择“数据透视表”，单击“下一步”；图3-5数据透视表和数据透视图向导3步骤之1”对话框在“数据透视表和数据透视图向导3步骤之2”对话框中，选择数据透视表的数据区域（默认为全部数据），单击“下一步”，如图3-6所示；图3-6数据透视表和数据透视图向导3步骤之2”对话框在“数据透视表和数据透视图向导3步骤之3”对话框中，选择“现有工作表”，如图3-7所示，同时单击Sheet3表标签，选择A1单元格；图3-7数据透视表和数据透视图向导3步骤之3”对话框在“数据透视表和数据透视图向导3步骤之3”对话框中，单击“布局”按钮，打开“数据透视表和数据透视图向导布局”对话框，如图3-8所示；图3-8数据透视表和数据透视图向导布局”对话框 在图3-8所示的对话框中，将字段“职称”分别拖到“行”区域和“数据”区域中，如图3-9所示，单击“确定”返回“布局”对话框；图3-9字段布局结果 在“布局”对话框中，单击“完成”按钮完成操作，操作结果如图3-10所示。图3-10统计各类职称人数的数据透视表Excel案例应用-2食品公司销售分析 2.2.1 问题描述 某食品公司主要经营各类干货的零售和批发业务，各类干货均有零售指导价，即销售单价，批发价根据购买数量的多少有相应的折扣。该公司销售部使用 Excel软件对销售情况进行管理，每月将销售数据记录在销售记录表中，如图 3-11 所示。请你帮助公司销售部对各类干货的销售记录进行计算和统计，完成下列操作： 1. 对 Sheet1 中销售记录表的“销售单价”和“折扣”列进行自动填充。 2. 利用公式，计算 Sheet1中销售记录表的“销售金额”列。 3. 统计各种商品的销售总数量和销售总金额，将结果保存在 Sheet1 中的“销售统计表”中。 4. 对 Sheet2 中的销售记录表根据“销售地区”升序和“销售金额”降序排序。 5. 将 Sheet2 复制到 Sheet3，并对 Sheet3 中的销售记录表进行自动筛选，找出“销售数量大于等于 400”的商品销售记录。 6. 将 Sheet2 复制到 Sheet4，并对 Sheet4 中的销售记录表进行高级筛选，找出“销售数量大于平均销售数量”的商品销售记录。 7. 将 Sheet2 复制到 Sheet5，并对 Sheet5 中的销售记录表进行分类汇总，汇总各地区不同商品的销售数量。8. 根据 Sheet2，分别在 Sheet6 和 Chart1中新建数据透视表和图，统计各地区不同商品的销售总金额。图3-11食品公司销售记录表2.2.2 解决思路 1. 销售记录表中各数据列的计算 (1) “销售单价”列的计算。可根据“销售价格表”中的“销售单价”列，使用 VLOOKUP函数，自动填充到“销售记录表”中的“销售单价”列中。 (2) “折扣”列的计算。可根据“销售折扣表”中的折扣率，使用 IF 函数，将其折扣率自动填充到“销售记录表”中的“折扣”列中。 (3) “销售金额”列的计算。可使用公式来计算，计算方法为： 销售金额=(销售单价*销售数量)*(1-折扣)。 2. 销售统计 可以使用 SUMIF 函数，统计出各种商品的销售总数量和销售总金额，并将结果保存在Sheet1 中的“销售统计表”中。 3. 数据排序在 Sheet2 中，可以使用数据的排序功能，首要关键字选择“销售地区”，次要关键字选择“销售金额”，数据顺序分别选择降序和升序。 4. 数据筛选 (1) 查找“销售数量大于等于 400”的商品销售记录。在 Sheet3 中，可以使用自动筛选的自定义筛选方式，输入“销售数量=400”筛选条件，得到结果。 (2) 查找“销售数量大于平均销售数量”的商品销售记录。在 Sheet4 中，利用高级筛选的计算条件功能，建立条件区域进行高级筛选，得到结果。 5. 各地区不同商品的销售数量汇总 在 Sheet5 中，可以使用数据的分类汇总功能，分类字段选择“销售地区”，汇总方式选择“求和”，汇总项选择“数量”，就可以汇总得到各地区不同商品的销售总数量。 6. 各地区不同商品的销售总金额统计 可以使用数据的数据透视表和数据透视图功能，在 Sheet6 中新建数据透视表和数据透视图，选择“销售地区”作为行，选择“商品名称”作为列，选择“销售金额”作为数据，就可以统计出各地区不同商品的销售总金额。选择生成数据透视图（及数据透视表）即可生成数据透视图和表。 2.2.3 知识要点1.Excel 常用函数 本案例中需要使用 VLOOKUP、IF、SUMIF 等常用函数进行数据列的计算。 (1) VLOOKUP 函数，是 Excel 最常用、最有效的查找函数，可以在一个表格或指定的区域中查找某一指定的值，并由此返回该值对应当前行中指定列的数值。 (2) IF 函数，可以用来对某一条件执行的真假值进行判断，根据逻辑计算的真假值，返回不同结果。如果结果为真，则返回一个真，如果为假，则返回另一值。 (3) SUMIF 函数，根据指定条件对指定的单元格区域求和。 2.数据排序 数据排序是按一定的规则对数据进行整理和排列，为进一步处理数据做好准备。Excel 2003 提供了多种对数据列表进行排序的方法，既可以按升序或降序进行排序，也可以按用户自定义的方式进行排序。 (1) 普通排序：可以选择主要关键字、次要关键字、第三关键字按升序或降序对数据列表进行排序。 (2) 自定义排序：可以按用户自定义的顺序进行排序，需要先建立一个自定义序列。 3.数据的分类汇总 分类汇总是对数据列表指定的行或列中的数据进行汇总统计，统计的内容可以由用户指定，通过折叠或展开行、列数据和汇总结果，从汇总和明细两种角度显示数据，可以快捷的创建各种汇总报告。分类汇总为分析汇总数据提供了非常灵活有用的方式，它可以完成以下工作。 (1) 显示一组数据的分类汇总及总和。 (2) 显示多组数据的分类汇总及总和。 (3) 在分组数据上完成不同的计算，如求和、统计个数、求平均值（或最大值、最小值）、求总体方差等。 4.数据筛选数据筛选是一种用于查找数据的快速方法，筛选将数据列表中所有不满足条件的记录暂时隐藏起来，只显示满足条件的数据行，以供用户浏览和分析。Excel 提供了自动和高级两种筛选数据的方式。 (1) 自动筛选。筛选条件可以是： a. 全部。显示出工作表中的所有数据，相当于不进行筛选。 b. 前 10 个。该选项表示只显示数据列表中的前若干个数据行，不一定就是 10 个，个数可以修改。 c. 自定义。该选项表示自己可以自定义筛选条件。 d. 单个值。选择其中的某项内容，Excel 就会以所选内容对数据列表进行筛选。 (2) 高级筛选：对于筛选条件较多的情况，可以使用高级筛选功能来处理。使用高级筛选功能，必须先建立一个条件区域，用来指定筛选条件。条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据列表中的字段名必须一致，条件区域的其它行则输入筛选条件。 4.数据透视表和数据透视图 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，不仅能够改变行和列以查看数据源的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。数据透视图则是一个动态的图表，它可以将创建的数据透视表以图表的形式显示出来。 (1) 数据透视表：创建好数据透视表之后，可以根据需要对它的布局、数据项、数据汇总方式与显示方式、格式等进行修改。 (2) 数据透视图：在数据透视图生成之后，也可以修改它的布局、隐藏或显示数据项、汇总方式和数据显示方式等等，对数据透视图的操作会同时修改对应的数据透视表。2.2.4 方案步骤 1. 销售记录表中数据列的计算 在计算这些数据列时，我们可以先在某单元格输入单值公式，再使用填充柄完成该列中其余单元格的计算。 (1) “销售单价”列的计算。 在销售记录表中单击单元格 F11 ，输入计算公式：“=VLOOKUP(A11,$L$3:$M$9,2,FALSE)” ，按回车键。该公式的含义为：在单元格区域“L3:M9”即销售价格表中查找单元格 A11 中某商品名称所对应的销售单价。第 3个参数 2表示返回销售价格表中的第 2 列。 使用填充柄“+”下拉至单元格 F40，即可完成所有商品销售单价的计算。 (2) “折扣”列的计算。 在销售记录表中单击单元格 G11 ，输入计算公式：“=IF(E11=$A$6,$B$6,IF(E11=$A$5,$B$5,IF(E11=$A$4,$B$4,$B$3)”，按回车键。该公式的含义为：如果单元格 E11 中商品销售数量大于等于销售折扣表中单元格 A6 所对应的数量，则返回单元格 B6 中的折扣率值；否则再将 E11 与 A5 比较，如果大于等于则返回 B5的值；否则再将 E11 与A4 比较，如果大于等于则返回 B4，否则返回 B3。 使用填充柄“+”下拉至单元格 G40，即可完成所有商品销售折扣的计算。(3) “销售金额”列的计算。 在销售记录表中单击单元格 H11，输入计算公式：“=(E11*F11)*(1-G11)”，按回车键。 使用填充柄“+”下拉至单元格 H40，即可完成所有商品销售折扣的计算。 在完成了这些数据列的计算之后，销售记录表的结果如图 3-12 所示。图3-12销售记录表计算结果2. 销售统计 在销售统计表中单击单元格 M14 ，输入计算公式：“=SUMIF($A$11:$A$40,L14,$E$11:$E$40)”，按回车键。然后使用填充柄“+”下拉至单元格 M20，即可完成所有商品总销售数量的计算。该计算公式的含义为：返回单元格 L14 中的商品在销售记录表中对应的所有销售记录中的数量累加值。 用同样方法，在单元格N14输入计算公式：“=SUMIF($A$11:$A$40,L14,$H$11:$H$40)”，按回车键。然后使用填充柄“+”下拉至单元格 N20，即可完成所有商品总销售金额的计算。 在完成了这些数据列的计算之后，销售统计表的结果如图 3-13 所示。图3-13销售统计表计算结果3. 数据排序 选中 Sheet2 中销售记录表的任一单元格，单击菜单栏中的“数据”“排序”，弹出如图 3-14 所示的“排序”对话框。这里我们在“首要关键字”下拉列表中选择“销售地区”，并将“升序”单选钮选中，“次要关键字”则选择“销售金额”，并设置为“降序”，然后单击“确定”按钮，得到排序后的结果，如图 3-15 所示。图3-14排序对话框图3-15销售统计表排序结果4. 数据筛选 (1) 查找“销售数量大于等于 400”的商品销售记录。 首先将 Sheet2 复制到 Sheet3 中，然后选中 Sheet3 中销售记录表的任一单元格，单击菜单栏中的“数据”“筛选”“自动筛选”，数据列表中标题行的各列中将分别显示出一个下拉按钮。单击“数量”列的下拉按钮，在弹出的下拉列表中选择“”，弹出“自定义自动筛选方式”，如图 3-16 所示，单击“确定”按钮，得到筛选后的结果，如图 3-17 所示。图3-16“自定义自动筛选方式”对话框图3-17自动筛选结果(2) 查找“销售数量大于平均销售数量”的商品销售记录。 首先将 Sheet2 复制到 Sheet4 中，然后在 Sheet4 中创建一个条件区域，输入高级筛选的计算条件。 建立计算条件须满足下列 3 条原则。l 计算条件中的标题可以是任何文本或空白，不能与数据列表中的任一列标相同。 l 必须以绝对引用的方式引用数据列表外的单元格。 l 必须以相对引用的方式引用数据列表内的单元格。 我们依次在单元格 J1（或任一空白单元格）中输入列标：“平均值计算公式”，单元格J2 中输入平均值计算公式“=AVERAGE(E3:E32)”，单元格 J9 中输入列标：“大于平均销售数量”其值需满足上述的第 1 条原则，单元格 J10 中输入计算条件公式“=E3$J$2” ，输入该公式须满足上述的第 2、3 条规则，E3 是数据列表中的单元格，因此只能使用相对引用的方式。J2 包含平均值公式，是数据列表之外的单元格，只能采用绝对引用的方式。 建立好计算条件之后，选中 Sheet4中销售记录表的任一单元格，单击菜单栏中的“数据”“筛选”“高级筛选”，弹出如图3-3所示的“高级筛选对话框” ，在列表区域中输入“A2:H32”（如果前面单击了销售记录表的任一单元格，系统会自动填入，否则需手工输入），条件区域中输入“J9:J10”，单击“确定”按钮，得到筛选后的结果，如图 3-18所示。 图3-18“高级筛选”对话框 5. 各地区不同商品的销售数量汇总 首先将 Sheet2 复制到 Sheet5 中，然后在 Sheet5 中单击销售记录表的任一单元格，选择“数据”“分类汇总”菜单项，打开如图 3-19 所示的“分类汇总”对话框。 依次在“分类字段”下拉列表中选择“销售地区”作为分类字段，“汇总方式”下拉列表中选择“求和”作为汇总方式，“选定汇总项”列表中选择“数量”作为汇总项，单击“确定”按钮得到分类汇总后的结果，如图 3-20 所示。图3-19“分类汇总”对话框图3-20分类汇总结果6. 各地区不同商品的销售总金额统计 选中 Sheet2 销售记录表中的任一单元格，单击菜单栏中的“数据”“数据透视表和数据透视图”，弹出“数据透视表和数据透视图向导”对话框。该向导包括3 个步骤，第 1个步骤是数据源和报表类型的指定，在“所需创建的报表类型”中选择“数据透视图(及数据透视表)”单选钮，单击“下一步”按钮进入第 2 个步骤，这个步骤是数据源区域的选定，系统会自动选定“Sheet2!$A$2:$H$32”单元格区域，单击“下一步”按钮，进入第 3 个步骤。 单击“布局”按钮打开布局对话框，对话框的右半部分列出了数据源中的所有字段，可以将这些字段按钮拖放到左半部分图中的行、列、页和数据上。我们把“销售地区”字段拖放到“行”中，把“商品名称”字段拖放到“列”中，把“销售金额”字段拖放到“数据”中，如图 3-21 所示。 设置完毕后，单击“确定”按钮返回到“布局”对话框，然后单击“布局”对话框中的“确定”按钮返回到“步骤 3”对话框，单击“步骤 3”对话框中的“完成”按钮，系统自动生成数据透视表和图，并分别保存在