题目及操作步骤参考.docx

题目及操作步骤参考.docx

《题目及操作步骤参考.docx》由会员分享，可在线阅读，更多相关《题目及操作步骤参考.docx（17页珍藏版）》请在冰豆网上搜索。

题目及操作步骤参考

EXCEL2003案例精选

一、在当前文件夹中，已有文件：

原始文件.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数据区中建立条件区域：

性别

年龄

工龄

职称

男

>30

>10

助工

（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.数据排序

数据排序是按一定的规则对数据进行整理和排列，为进一步处理数据做好准备。

Excel2003提供了多种对数据列表进行排序的方法，既可以按升序或降序进行排序，也可以按用户自定义的方式进行排序。

（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，输入计算公式