使用Excel进行数据整理和图表显示的实验优化

1）实验目的

①掌握利用Excel的“排序”命令对数据进行预处理的方法。

②掌握利用Excel的“自动筛选”和“高级筛选”命令对数据进行预处理。

③掌握利用Excel的“FREQUENCY”工具进行数据分组整理的方法。

④掌握利用Excel中“图表向导”和“直方图”工具制作频数和频率组合图（直方图与折线图的组合）的方法。

2）实验资料与要求

（1）资料

某企业50名销售人员的月销量见表3.20。

表3.20　某企业50名销售人员的月销量

（2）要求

①用Excel对原始数据按照从小到大进行排序。

②用Excel从原始数据中筛选出月销量低于150台的数据。

③用Excel以组距为30台进行等距分组，并整理成频数分布表。

④用Excel绘制出频数和频率的组合图（直方图和折线图的组合）。

⑤用Excel分别绘制气泡图/雷达图/箱线图。

3）实验步骤与结果

（1）输入数据

在A1中输入标题“原始数据”，然后把50名销售人员的月销量资料输入工作表A2：A51单元格中。

（2）用Excel进行数据排序

第1步：在B1中输入标题“排序结果”；输入排序数据。将原始数据复制到B2：B51。

第2步：选择待排序的数据区域：B2：B51。

第3步：执行排序命令。选择“数据”菜单中的“排序”命令，在弹出的“排序”对话框中进行设置，如图3.23所示。

第4步：显示排序结果。单击“确定”按钮后，即可得到排序结果，如图3.23所示的B列。

（3）用Excel进行数据筛选

第1步：输入筛选条件。在C1中输入标题“筛选条件”，在C2中输入“原始数据”，在C3中输入“＜150”。

第2步：执行高级筛选命令。选择“数据”菜单中的“筛选”命令的“高级筛选”子命令，在弹出的“高级筛选”对话框中进行设置，如图3.24所示。

图3.23　排序对话框

图3.24　高级筛选对话框

第3步：显示筛选结果。单击“确定”按钮，即可得到筛选结果，见表3.21。

表3.21　排序和筛选结果

（4）用Excel进行统计分组

有两种方法：一是利用“FREQUENCY”函数；二是利用“直方图”工具。

①利用“FREQUENCY”函数进行统计分组的操作方法

第1步：输入分组条件。在E1中输入列标题“分组”，在E2：E8中输入分组区间；在F1中输入列标题“组上限”，在F2：F8中输入各组上限。

第2步：调用“FREQUENCY”函数。选定G2：G8单元格，插入FREQUENCY函数，输入所需参数，如图3.25所示。

图3.25　插入FREQUENCY函数对话框

第3步：显示分组结果。按“Ctrl＋Shift＋Enter”组合键，结果如图3.26所示。

图3.26　FREQUENCY函数对话框

②利用“直方图”工具进行统计分组的操作方法

第1步：调用直方图工具。在工具菜单中单击“数据分析”选项，从其对话框的分析工具列表中选择“直方图”，在弹出的“直方图”对话框中进行设置，如图3.27所示。

图3.27　插入直方图对话框

第2步：显示分组结果。单击“确定”按钮，即可得输出结果。过程如图3.28所示，输出结果如图3.29所示。

图3.28　直方图对话框

应当注意，图3.29实际上是一个柱形图，而不是直方图，若要把它变成直方图，可按以下操作：

a.用鼠标双击任一条柱子，打开“数据系列格式”对话框。

b.在对话框中选择选项标签，把分类间距改为0。

c.按确定后即可得到直方图，如图3.30所示。

（5）用Excel作频数和频率的组合图

用Excel绘制频数和频率的组合图右两种方式：一是利用“图表向导”；二是利用“直方图”工具。

利用“直方图”工具绘制频数和累积频率组合图的方法在前面已介绍，下面介绍利用“图表向导”绘制频数和频率组合图（直方图和折线图的组合）的方法。

图3.29　分组结果和直方图及累积频率

图3.30　调整后的直方图和累积频率

第1步：列出频数和频率分布表，见表3.22。

表3.22　频数和频率分布表

第2步：生成频数和频率柱形图。

①选择“插入”菜单栏中的“图表”命令，或单击“常用工具栏”中的“图表向导”按钮，弹出“图表类型”对话框，选择柱形图。过程如图3.31所示。

图3.31　选择图标类型对话框

②单击“下一步”按钮，打开源数据对话框，在数据区域选项卡中输入数据区域：G2：H8，在系列选项卡中输入分类（X）轴标志：E2：E8；又单击“下一步”按钮，填写“图表选项”，再单击“下一步”按钮，指定“图表位置”；最后，单击“完成”按钮，即可得到频数和频率柱形图，如图3.32所示。

图3.32　频数和频率的柱形图

第3步：改变频率系列的图表类型并为其添加次坐标轴。首先，选择频率系列，打开“图表类型”对话框，将频率系列改变成折线图；然后打开“数据系列格式”对话框，切换到坐标轴选项卡，选择次坐标轴；最后，单击“确定”按钮，结果如图3.33所示。

第4步：将频数系列的柱形图变成直方图。选择频数系列，打开“数据系列格式”对话框，切换到选项卡，将分类间距设置为0，即可得到频数和频率得组合图。

第5步：加工修饰图表。设置标题/坐标轴格式等，最终结果如图3.34所示。

图3.33　频数和频率的柱形⁃折线图

图3.34　频数和频率组合图

【学习指导与小结】

数据的图表展示是应用统计的基本技能。本章首先介绍数据的预处理方法，然后介绍不同类型数据的整理与图示方法，最后介绍图表的合理使用问题。本章各节的主要内容和学习要点如表3.23所示。

表3.23　本章各节的主要内容和学习要点

续表

注：“加粗”部分为重点学习要点，应当重点学习并掌握。

【常用术语】

频数　频数分布　比例　比率　累积频数　数据分组　组距分组　组距　组中值　直方图　茎叶图　箱线图

【上机操作】(www.xing528.com)

肥皂公司间的竞争^[3]

自1879年宝洁公司推出象牙牌香皂以来，它一直是美国首屈一指的香皂制造商。但是，在1991年末，它的竞争者联合利华取代了宝洁行业老大的位置，抢占了16亿个人香皂市场31.5％的市场份额，而宝洁公司的市场份额为30.5％。自从联合利华在1895年推出Lifebuoy香皂进入香皂市场以来，它一直在关注宝洁的动向。在1999年，联合利华推出它的新产品Lever2000适合整个家庭使用。由于香皂市场被细分为儿童用香皂、男士香皂及女士香皂，这就为该产品的推出创造了条件。联合利华认为开发家庭中每一个成员都能使用的香皂会有市场。消费者对此反应也非常强烈。在1991年，Lever2000的销售额就达到了1.13亿美元，在个人香皂的销售战中，联合利华第一次走在了宝洁的前面。虽然宝洁公司香皂的销售数量还是位居第一，但是联合利华的产品定价更高，因此它的总销售额最大。

不用说，宝洁公司很快就对Lever2000的成功作出了反应。宝洁公司选择了几个的战略，包括为被视为男用香皂的舒肤佳重新定位。

最终，宝洁公司面对挑战，推出了玉兰油滋润沐浴香皂。在该产品推向全国销售的第一年，用于媒体宣传的资金为2400万美元。新的沐浴香皂极其成功，帮助宝洁公司重新夺回了失去的市价份额。

1999年美国前10大日用香皂的销售额见表3.24。这些香皂都是由4大厂商所生产的：联合利华、宝洁、Dial及高露洁⁃棕榄。

表3.24　4大厂商香皂销售额分布表

1983年，香皂市场份额分布为：宝洁占37.1％，联合利华占24％，Dial占15％，高露洁⁃棕榄占6.5％，其他厂商占17.4％。到了1991年，香皂市场份额分布为：联合利华占31.5％，宝洁占30％，Dial占19％，高露洁⁃棕榄占8％，其他厂商占11％。

【讨论】

①假设你正在为宝洁公司出一份宝洁公司及其他公司1983年、1991年和1999年的市场份额报告。利用Excel绘出每一年的个人香皂市场的市场份额图。假设1999年所有其他厂商的销售额为1.19亿美元，研究图形中关于不同公司的市场份额，你能观察到什么？特别是与以前年度相比，宝洁公司的表现如何？

②假设宝洁公司每周大约卖出2000万块香皂，但需求并不稳定，该公司的生产经理想更好地掌握该年度香皂销售的分布情况。假设下面给出的以百万块为单位的销售数字为该年中香皂每周的销售数量。用柱形图表示这些数据，从图中你能得到什么有助于生产（及销售）人员的信息？

17.1　19.6　15.4　17.4　15.0　18.5　20.6　18.4　20.0　20.9

19.3　18.2　14.7　17.1　12.2　19.9　18.7　20.4　20.3　15.5

16.8　19.1　20.4　15.4　20.3　17.5　17.0　18.3　13.6　39.8

20.7　21.3　22.5　21.4　23.4　23.1　22.8　21.4　21.0　25.2

26.3　23.9　30.6　25.2　26.2　26.9　32.8　26.3　26.6　24.3

26.2　23.8

【思考与练习】

一、思考题

1.数据的预处理包括哪些内容？

2.数值型数据分组整理的方法有哪些？简述组距分组的步骤。

3.常用的统计图有哪些？应该如何使用？

4.简述统计表的结构和设计统计表应该注意的问题。

二、练习题

1.某行业管理局所属40个企业2010年的产品销售收入数据（单位：万元）如下：

152　124　129　116　100　103　92　95　127　104

105　119　114　115　87　103　118　142　135　125

117　108　105　110　107　137　120　136　117　108

97　88　123　115　119　138　112　146　113　126

要求：对上面的数据进行适当分组，编制次数分布表，并计算出累计频数和累计频率。

按规定，销售收入在125万元以上为先进企业，115万～125万元为良好企业，105万～115万元为一般企业，105万元以下为落后企业，按先进企业/良好企业/一般企业/落后企业进行分组。

2.2001—2011年我国的国内生产总值数据见表3.25。

表3.25　2001—2011年我国的国内生产总值数据

续表

资料来源：中华人民共和国国家统计局官方网站：http：//www.stats.gov.cn/。

其中，在2011年的国内生产总值中，第一产业为47486.21亿元，第二产业为220412.8亿元，第三产业为204982.5亿元。

要求：（1）根据2001—2011年的国内生产总值数据，绘制线图。

（2）根据2011年的国内生产总值及构成数据，绘制饼图。

3.已知某班级50名学生的《统计学》考试成绩资料如下：

50　70　80　69　79　99　51　71　81　68　78

99　54　72　83　67　77　97　55　72　84　58

73　85　66　76　95　65　75　91　59　74　86

61　74　86　61　74　86　61　74　86　64　75

90　63　75　88　62　74

要求：

（1）试根据上述资料进行数据分组并编制频数分布表。

（2）在频数分布表中计算出向上和向下累积频数。

（3）根据所编制的频数分布表编制茎叶图和直方图，根据直方图说明学生考试成绩分布属于哪一种类型。

【注释】

[1]中国共产党第七届中央委员会第二次全体会议发言，毛泽东。

[2]大卫·R.安德森，等.商务与经济统计学精要［M］.陆成来，等，译.大连：东北财经大学出版社，2000：25.

[3]肯·布莱克，等.以Excel为决策工具的商务与经济统计［M］.张久琴，等，译.北京：机械工业出版社，2003：36.