Ecel数据分析教程 第5章上机题及课后习题

上机题5

1、使用统计函数分析航空公司售票速度

有顾客反映某家航空公司售票处售票的速度太慢。为此，航空公司收集了解

100位顾客购票所花费时间的样本数据（单位：分钟）结果如图所示：

ABCDEFGHI

1绿梦航空公司售票速度调查（单位：J分钟）

2

32.31.03.50.71.01.30.81.02.409

4工11.50.28.21.75.21.63.95.423

56.匚2.62.82.43.93.81.60.31.111

63.匚1.14.31.40.20.32.72.74.140

73.15.50.93.34.221.72.21.03.334

84.63.64.50.51.20.73.54.82.609

97.46.91.64.12.15.85.01.73.863

103.20.62.13.77.81.90.81.31.435

1111.08.67.52.02.02.01.22.96.510

124.62.01.25.82.92.02.96.60.715

航空公司认为，为一位顾客办理一次售票业务所需的时间在五分钟之内就是

合理的。上面的数据是否支持航空公司的说法，顾客提出的意见是否合理，请你

对上面的数据进行适当的分析，回答下列问题。

（1）对数据进行等距分组，整理成频数分布表，并绘制频数分布图（直方

图、折线图、饼图）。

（2）根据分组后的数据，计算中位数、众数、算术平均数和标准差。

（3）分析顾客提出的意见是否合理，为什么？

（4）使用哪一个平均指标来分析上述问题比较合理？

答：（I）在原数据旁边空一列处（本例为L列），按2.5的步长建立起一个

等差数列，作为分组依据；

在M3单元格输入公式

=L2+0.1&&L3

并向下填充到M12单元格，这个数据系列不参与计算，但却是将来作图时

用来作为坐标轴标签的；

然后选中N3:N12单元格，在编辑栏输入函数

=FREQUENCY（A3:J12,L3:L12）

后按“Ctrl+Shifl+Enter”组合键，计算出各分组出现的频数；

这一步也可以不用FREQUENCY函数，而是分别用countif来统计各组数据

个数。这样做的好处是数据获得过程看得更清楚。对于小于2.5和大于25的，

直接使用countif语句来统计：

=COUNTIF（A3:J12,"<2.5"）

但对于介于两个组限之间的数据个数,则无法直接使用countif语句来统计，

必须与sum函数配合：

=SUM（COUNTIF（A3:J12,{">2.5",">5"}）*{1,-1}）

用两个分别得到了大于2.5和大于5的数据个数，再分别乘以1和

再求和，由于大于2.5的数据的个数肯定比大于5的数据个数多，分别乘以1和

-1后，前者成为正数而后者成了负数，所以求和的结果是正数减去负数，从而得

到所需的数据个数。

最后选中M2:N12区域后插入柱形图。结果如下图所示：

LMN0PQR

绿梦航空公司售票速度

2.50.1-2.550

5.02.6-533调查（单位：分钟）

7.55.1-7.51260

10.07.6-103

50

12.510.1-12.5I

15.012.6-15040

17.515.1-17.5030

20.017.6-20020

22.520.1-22.5I10

25.0,22.6-250

（2）用相应的函数进行计算后，结果如卜.图所示:

ABC]D|EFG1Hlij

1球梦航空公司售票速度调查（单位：分钟）

2

32.31.03.50.71.01.30.81.02.40.9

124.62.01.25.82.92.02.96.60.71.5

13

14最大值

±21.7算术平均值3.17

小值

15最

±0.2中位数2.5MEDIAN（A3:J12）

验组

经

16距

±2.51MODE（A3:J12）

验组

经投数

数10标雇差2.86STDEV（A3:J12）

从表中我们可以得到中位数为2.5、众数为I、平均数为3.17、标准差为2.864。

（3）合理。虽然他的平均数是3.17<5属于正常范围，但是依旧有将近20%

的购票时间>5分钟属于超过正常范围，那就是速度太慢了。平均数不能代表一

切。

所以顾客提出的理由是正确的购票太慢的现象确实存在。

（4）平均数比较合理，它能较好的反映购票的大概时间。比较有代表性。

2、利用移动平均趋势剔除季节变动因素

如图所示是某商品5年的分季节的销售数据，如何利用移动平均趋势剔除季

节变动？

ABc

1茶企业近3丝分季销售额

2

3三二季节销与软

4201511750

522203

63225S

rf4204"

s201611762

922SS6

1032346

1141700

12201711974

1322220

32465

41657

201811941

22593

32565

42024

201911716

22395

32359

41546

首先分别在D列和E列算出四项移动平均和二项移正平均（即趋势），然后

在F6使用公式“=C6/E6”并填充至UF7:F21o

SUMX✓A=C21/E21

ABCDF

2

3

4年份季节销售额四项移动平均趋势（二项移正平均）剔除长期趋势

4201517501

622208'2065.75

32258r2068.752067.251.092272342

7

42047r2238.252153.50.950545623

8

11762r2385.252311.750.762193144

92016

22886r2298.52341.8751.232345877

1032846r2351.523251.224086022

1111700'21852268.250.749476469

12r

2017119742089.752137.3750.923562781

13r

14222020792084.3751.065067466

r

1524652070.752074.8751.188023375

161657r21642117.3750.782572761

17

20181941r21892176.50.891798759

18r

1925932280.752234.8751.160243862

202565r2224.52252.6251.13867155

2024r21752199.750.920104557

20192248.25.2211.625.0.775900073

212395r2128.7512188.目1.0943568651

32858

411546

重新排列F6:F21中的数字，使之每行按年、每列按季节排列:

SUMX=D9*$B$10

ABCDE

1某企业近5年分季销售额

2

3年份"季节1234

420151.0922723420.950545623

520160.7621931441.2323458771.2240860220.749476469

620170.9235627811.0650674661.1880233750.782572761

720180.8917987591.1602438621.138671550.920104557

820190.7759000731.094356865.

9平均0.83836369.1.138003517,"1.160763322T0.850674853

10调整系数1.003057977.

回季节比率0.8409273871.1414835061.1643129110.853276197

先在B9:F9区域分别用公式

=AVERAGE(B5:B8)

=AVERAGE(C5:C8)

=AVERAGE(D4:D7)

=AVERAGE(E4:E7)

求出每季的平均数；再在BIO单元格用公式

=VERAGE(B9:E9)

求出调整系数(即总平均值)，最后在B11单元用公式

=B9*$B$10

求出1季节的季节比率并填充到区域。

3、规划求解实例1:钢管切割问题

某物流配送中心从钢管厂进货，需要将钢管按照用户的要求切割后进行配送,

从钢管厂进货时得到的原料钢管都是7.4米长，而用户分别需要2.9米长、2.1米

长和1.5米长的钢管各100根，应如何下料使原材料最节省？

第一步：问题分析

首先，应当确定哪些切割方案是可行的，所谓一个切割方案，是指按照用户

需要在原料钢管上安排切割的一种组合，例如，我们可以将7.4米长的钢管截下

2.9米的一根、1.5米的三根，所剩料头为0；或者截两根2.9米的、一根1.5米

的，所剩料头为0.1米，可行的切割方案是很多的。其次，应当确定哪些切割方

案是合理的，通常假设一个合理的切割方案的余料应该很小，至少不应该大于或

等于客户需要的钢管的最小尺寸，在这种合理性假设下，可以选择的切割方案一

共有4种，如下图所示：

1方案1方案2方案3方案4[

2.9米1201

2.1米0022

1.5米3120

合计7.47.37.27.1

料头00.10.20.3

于是，问题转化为在满足用户需要的条件下，按照哪些种合理的方案使原材

料最为节省。而所谓节省，切割后剩余的料头和最小或切割原料钢管的总根数最

少。

第二步：模型建立

假设四种方案切割的钢管数分别为：Xi，X2,X3,X4

决策目标：切割后剩余的料头最小，设切割后剩余的料头和为丫

则目标函数为：

MinY=OX1+O.IX2+O.2X3+O.3X4

约束条件为：

XI+2X2+X4=100

2X3+2X4=100

3XI+X2+2X3=100

XI，X2，X3,XQO

上述实际问题就变成这样个数学问题，求解满足约束条件的X”X2,X3,

X4使丫达到最小值，这是一个规划求解问题，建立完数学模型后，如果利用手

工求解是非常复杂的，我们可以利用Excel中的“规划求解”工具进行求解。

第三步：数据准备

利用Excel具体求解步骤如下：

（1）启动Excel,新建一张工作表。

（2）按上表的形式将具体数据输入到工作表中，如下图所示：

（3）并且在B2单元格中输入公式

=B8*B9+C8*C9+D8*D9+E8*E9

即规划求解的“目标函数”，也可以用

=SUMPRODUCT（B8:E8,B9:E9）

如下图所示：

（5）拖动F4单元格右下角的“填充柄”将公式复制到F5和F6两个单元

格中，注意$B$9,$C$9,$D$9,$E$9四个单元格是绝对引用，在公式复制过程

中保持不变，而单元的相对引用，在公式复制过程将发生相应的改变。

（6）在“工具”菜单中选择“规划求解”，在随后出现“规划求解参数”对

话框中，在“设置目标单元格”中输入目标函数所在的单元格，既$8$2,在“等

于”选择项中选择“最小值”。

（7）在“可变单元格”中输入代表变量Xi、X2、X3和X4所在的区域$B$9:$E$9。

（8）输入“规划求解”的“约束条件”，方法是单击“添加”按钮来添加“约

束条件”，出现“添加约束”对话框，在“单元格引用位置"输入$F$4单元格；

然后选择“=”；在“约束值”框中输入100：如下图所示：

然后单击“添加”按钮，类似地输入下一个“约束条件”，最后单击“确定”

按钮。注意不要忘记输入Xi，X2，X3,X420约束条件。

（9）单击“规划求解参数”对话框中的“求解”按钮，计算机开始求解并

将最终结果显示在存放变量的单元格中，如下图所示：

B2▼笈=B8*B9+C8*C9+D8*D9+E8*E9

ABCDEF

1

|_____16]

2展期帙一

3方案1方案2方案3方案4

42.9121100

52.122100

61.5312100

7合计7.47.37.27.1

科女

800.10.20.3

9各方案所需的根数0403020

但也可能是下图所示结果:

1------------------------------

ABCDEF

1最少料头16

2

3方案1方案2方案3方案4

42.9121100

52.122100

61.5312100

7合计7.47.37.27.1

8科美00.10.20.3

9各方案所需要的根数10.030.020.030.0

1。

最终计算结果是：“方案2”40根；“方案3”30根；“方案4”20根。所剩

料头和为16米。

但也可能是：“方案1”10根;“方案2”30根；“方案3”20根；“方案4”

30根。所剩料头和仍为16米。

4、规划求解实例2：最佳购买方案问题

现有资金20万元，准备购买冬季御寒衣物一批，在批发市场调查后得到的

最低批发价如图所示：

商品名称单价数量金额

鞋4501450

帽1201120

毛衣2001200

围巾80180

外套5001500

请问应该怎么购买才能刚好用掉20万元(注：确保每种产品都有，且鞋不

能低于20双)。

答：

D7-=A=SUM（D2：D6）|

ABCDE

1商品名称单价数量金额降费

2鞋450-1450一200000

3帽1201120

4丰衣2001200

5围巾80180

6外奏500

________1------500,1

13501

8顺求解装

9

设置目标:①$D$7|

10

11到：O最大值(M)O最小值史@目标值:0)200000

12通过更改可变单元格:回

13$C$2:$C$6

14

遵守约束：(U)

15

$c$2：$c$6=

16

$C$2:$C$6>=1

17$C$3>=20

课后习题5

1、数据表中给出了某厂12个车间加工同一产品所需时间的全部数据，如下

图所示。

时间（时）

12

15

18

19

20

14

15

17

16

13

1211

（1）使用函数计算该厂12个车间加工这一产品所需时间的算术平均值、几

何平均值、调和平均值、众数、中位数、方差、标准差、偏度以及峰度。

（2）使用数据分析工具对该数据进行描述性统计分析，并将结果与（1）中

计算的结果进行对比，判断两者是否相同。

2、简单平均法和趋势平均法练习：以下数据表给出了1995〜2018年间某厂

用电数的全部数据：

A___B

1用电数（万度）

2_199545.8

3.18650.5675

4_199758.1484

□19M65.8551

6198一”

7200080

S200133.1

?_200285

10200391

11200498.2

122005105.6

13_2006113.4

142007119.9

15_2008119.2

162009120.2

17_2010121.3

2011125

二92012117.7

202013133

2L2014166.5

222015178.5

232016101.3

242017209.8

252018226.9

262019

要求:

（1）利用简单移动平均法预测2019年的用电数，并确定误差，已知步长为

3»

（2）利用趋势移动平均法预测2019年的用电数，并确定误差。

［解题：］简单移动平均法预测结果如下：

SUM=SQRT(SUMXMY2(B23:B25,C24：C26)/3)

ABCDEF

用电数（预测值预测误差

199545.8

199650.5675#N/A#N/A

1997S8.14B4#N/A#N/A

199865.8551r51.5053#N/A

199975.3’53匚二#N/A

200080r66.4345F7.77767

200183.1r73.7184r7.67691

200285,784667r6.61456

200391r82.7'4.30507

2004触.2r86.3667'3.64361

2005105.6r91.4'4.83281

2006113.4’98.2667'6.36358

2007119.9r105.733r7.27543

2008119.2'112.867r7.31726

二二二120.2’117.5r6.04811

172010121.3’119.767’4.12912

182011125r120.223’1.18541

2012117.7’122.167r1.76572

2013133'121.323r2.73049

2014166.5r125.233'5.21376

:139,067'16.5043

2015(178.5|

20161181.3''159.33?.:19.8340

!1

2017209.81178.767'20.632

2018226.9:193.2r16.33

1

2018'209.33r15.719,____

趋势移动平均法预测结果如下:

C30▼fx=C28+C29*1

ABD

用电数（万度）

1一次移动平均二次移动平均

218545.8

319g650.5675#N/A

4199758.1484#N/A

5199865.855151.5053

618975.3’58」乳33333#N/A

720008066.434559.71004444

r

9200183.173.7183666766.1144

9200285’70,4666666773.20651111

1020038182.773.62334444

11200498.2，86.3666666732.34444444

122005105.691.436.32222222

132006113.4’98.2666666792.01111111

142007119.9，105.733333393.46666667

152008119.2’112.9666667105.6555556

162009120.2117.5112.0666667

172010121.3'110.7666667116.7444444

182011125’120.2333333119.1666667

192012117.7r122.1666667120.7222222

202013133r121.3333333121.2444444

212014166.5，125.2333333122.9111111

222015178.5r138.066666712S.5444444

232016191.3T150.3333333141.2111111

2420172O9.S’178.7666667159.0555556

252018226.9183.2177.1

262010’209.333333193.7666667

27

29a,=2*C26-D26224.9

29^=(27(3-1))*(026-026)15.56666667

30认+bj240.4666667I

此题也可用回归计算趋势移动平均:

用电数（万度）

199545.8SU36£ARYOUTPUT

199650.5675

415J199758.1484回归疑计

199865.8551Multiple0.95246

617J199975.3RSauare0.90713

200080Adjusted0.90296

8I标准误差

9J200183.115.2039

10I200285观测值24

一200391

111

2004Q8.2方差分析

2005105.6dfSSMSFxificanceF

2006113.4回三分析149705.549705.5215.0277.7E-13

2007119.Q差差225035.5231.159

2008119.2总计2354791

2009120.2

2010121.3Coefficien标准误差tStatP-valuewO-rer95^Jpper95吓俄95.0cV限95.饮

2011125Intercep-13076899.593-14.5359.2E-13-14941-11210-14941-11210

20121177XVariab6.574360.44S3414.6€3S7.7E-135.644567.504165.644567.50416

2013133

2014166.5

2015178.5

2016191.3201阚测值

2017209.8198.066

2018226.9

2019

3、鸡兔同笼问题。鸡兔同笼是中国古代著名趣题之一，据《孙子算经》记

载：今有鸡兔同笼，上有三十五头，下有九十四足，问鸡兔各几何？

［解题：］将其模型化后输入到Excel表格中，如下图所示：

IB7✓AII=B4*2+B5*4

AL_BCD