题目及操作步骤参考.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，输入计算公式：
“=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条原则。
●计算条件中的标题可以是任何文本或空白，不能与数据列表中的任一列标相同。
●必须以绝对引用的方式引用数据列表外的单元格。
●必须以相对引用的方式引用数据列表内的单元格。
我们依次在单元格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”对话框中的“完成”按钮，系统自动生成数据透视表和图，并分别保存在Sheet6和Chart1中，如图3-22和3-23所示。
图3-21“布局”对话框
图3-22生成的数据透视表
图3-23生成的数据透视图
2.2.5总结
在本案例的学习中，我们需要掌握以下内容：
1.利用自定义公式与常用函数自动计算部分数据列的值，在本案例中我们主要使用
了VLOOKUP、IF、SUMIF等函数，这些函数在实际应用中使用非常频繁，必须要熟练掌握。
2.利用数据的排序对数据进行快速排序。
数据排序可以对数据进行整理和排列，为
进一步处理数据做好准备，比如对数据进行分类汇总之前必须按照分类汇总字段进行排序。
3.利用计算条件对数据进行高级筛选。
自定义筛选只能完成条件简单的数据筛选，
如果筛选的条件比较复杂，自定义筛选就会显得比较麻烦。
对于筛选条件较多的情况，可以使用高级筛选功能来处理。
在本案例中我们使用计算条件建立了复杂的非常数条件，从而筛选出大于平均销售数量的商品销售记录。
4.利用数据的分类汇总功能对数据进行统计。
在学习过程中，要注意在创建分类汇
总之前，必须要保证要进行分类汇总的数据区域是一个连续的数据区域，而且每个数据列都有列标题；还必须对要进行分类汇总的列排序。
这个排序的列标题称为分类汇总关键字，分类汇总时只能指定排序后的列标题为汇总关键字。
习题
1.在“销售价格表”中加上一列“成本价”，输入相应数据；在“销售记录表”中
增加两列“成本价”与“销售利润”，使用合适的函数自动填充“成本价”列，使用合适
的公式计算“销售利润”。
2.使用数据的排序功能，对所有商品的销售利润进行降序排序。
3.使用数据的自动筛选功能，找出利润最大的商品销售记录。
4.使用数据的高级筛选功能，找出销售金额大于平均销售金额的商品销售记录。
5.使用数据的分类汇总功能，汇总各地区不同商品的平均利润。
6.使用数据透视表和数据透视图，统计各地区不同商品的销售总利润。
升级会员 