Ecel數(shù)據(jù)分析教程 第5章上機題及課后習(xí)題

上機題5

1、使用統(tǒng)計函數(shù)分析航空公司售票速度

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

100位顧客購票所花費時間的樣本數(shù)據(jù)（單位：分鐘）結(jié)果如圖所示：

ABCDEFGHI

1綠夢航空公司售票速度調(diào)查（單位：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

航空公司認(rèn)為，為一位顧客辦理一次售票業(yè)務(wù)所需的時間在五分鐘之內(nèi)就是

合理的。上面的數(shù)據(jù)是否支持航空公司的說法，顧客提出的意見是否合理，請你

對上面的數(shù)據(jù)進行適當(dāng)?shù)姆治?，回答下列問題。

（1）對數(shù)據(jù)進行等距分組，整理成頻數(shù)分布表，并繪制頻數(shù)分布圖（直方

圖、折線圖、餅圖）。

（2）根據(jù)分組后的數(shù)據(jù)，計算中位數(shù)、眾數(shù)、算術(shù)平均數(shù)和標(biāo)準(zhǔn)差。

（3）分析顧客提出的意見是否合理，為什么？

（4）使用哪一個平均指標(biāo)來分析上述問題比較合理？

答：（I）在原數(shù)據(jù)旁邊空一列處（本例為L列），按2.5的步長建立起一個

等差數(shù)列，作為分組依據(jù)；

在M3單元格輸入公式

=L2+0.1&&L3

并向下填充到M12單元格，這個數(shù)據(jù)系列不參與計算，但卻是將來作圖時

用來作為坐標(biāo)軸標(biāo)簽的；

然后選中N3:N12單元格，在編輯欄輸入函數(shù)

=FREQUENCY（A3:J12,L3:L12）

后按“Ctrl+Shifl+Enter”組合鍵，計算出各分組出現(xiàn)的頻數(shù)；

這一步也可以不用FREQUENCY函數(shù)，而是分別用countif來統(tǒng)計各組數(shù)據(jù)

個數(shù)。這樣做的好處是數(shù)據(jù)獲得過程看得更清楚。對于小于2.5和大于25的，

直接使用countif語句來統(tǒng)計：

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

但對于介于兩個組限之間的數(shù)據(jù)個數(shù),則無法直接使用countif語句來統(tǒng)計，

必須與sum函數(shù)配合：

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

用兩個分別得到了大于2.5和大于5的數(shù)據(jù)個數(shù)，再分別乘以1和

再求和，由于大于2.5的數(shù)據(jù)的個數(shù)肯定比大于5的數(shù)據(jù)個數(shù)多，分別乘以1和

-1后，前者成為正數(shù)而后者成了負(fù)數(shù)，所以求和的結(jié)果是正數(shù)減去負(fù)數(shù)，從而得

到所需的數(shù)據(jù)個數(shù)。

最后選中M2:N12區(qū)域后插入柱形圖。結(jié)果如下圖所示：

LMN0PQR

綠夢航空公司售票速度

2.50.1-2.550

5.02.6-533調(diào)查（單位：分鐘）

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）用相應(yīng)的函數(shù)進行計算后，結(jié)果如卜.圖所示:

ABC]D|EFG1Hlij

1球夢航空公司售票速度調(diào)查（單位：分鐘）

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算術(shù)平均值3.17

小值

15最

±0.2中位數(shù)2.5MEDIAN（A3:J12）

驗組

經(jīng)

16距

±2.51MODE（A3:J12）

驗組

經(jīng)投數(shù)

數(shù)10標(biāo)雇差2.86STDEV（A3:J12）

從表中我們可以得到中位數(shù)為2.5、眾數(shù)為I、平均數(shù)為3.17、標(biāo)準(zhǔn)差為2.864。

（3）合理。雖然他的平均數(shù)是3.17<5屬于正常范圍，但是依舊有將近20%

的購票時間>5分鐘屬于超過正常范圍，那就是速度太慢了。平均數(shù)不能代表一

切。

所以顧客提出的理由是正確的購票太慢的現(xiàn)象確實存在。

（4）平均數(shù)比較合理，它能較好的反映購票的大概時間。比較有代表性。

2、利用移動平均趨勢剔除季節(jié)變動因素

如圖所示是某商品5年的分季節(jié)的銷售數(shù)據(jù)，如何利用移動平均趨勢剔除季

節(jié)變動？

ABc

1茶企業(yè)近3絲分季銷售額

2

3三二季節(jié)銷與軟

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年份季節(jié)銷售額四項移動平均趨勢（二項移正平均）剔除長期趨勢

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中的數(shù)字，使之每行按年、每列按季節(jié)排列:

SUMX=D9*$B$10

ABCDE

1某企業(yè)近5年分季銷售額

2

3年份"季節(jié)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調(diào)整系數(shù)1.003057977.

回季節(jié)比率0.8409273871.1414835061.1643129110.853276197

先在B9:F9區(qū)域分別用公式

=AVERAGE(B5:B8)

=AVERAGE(C5:C8)

=AVERAGE(D4:D7)

=AVERAGE(E4:E7)

求出每季的平均數(shù)；再在BIO單元格用公式

=VERAGE(B9:E9)

求出調(diào)整系數(shù)(即總平均值)，最后在B11單元用公式

=B9*$B$10

求出1季節(jié)的季節(jié)比率并填充到區(qū)域。

3、規(guī)劃求解實例1:鋼管切割問題

某物流配送中心從鋼管廠進貨，需要將鋼管按照用戶的要求切割后進行配送,

從鋼管廠進貨時得到的原料鋼管都是7.4米長，而用戶分別需要2.9米長、2.1米

長和1.5米長的鋼管各100根，應(yīng)如何下料使原材料最節(jié)?。?/p>

第一步：問題分析

首先，應(yīng)當(dāng)確定哪些切割方案是可行的，所謂一個切割方案，是指按照用戶

需要在原料鋼管上安排切割的一種組合，例如，我們可以將7.4米長的鋼管截下

2.9米的一根、1.5米的三根，所剩料頭為0；或者截兩根2.9米的、一根1.5米

的，所剩料頭為0.1米，可行的切割方案是很多的。其次，應(yīng)當(dāng)確定哪些切割方

案是合理的，通常假設(shè)一個合理的切割方案的余料應(yīng)該很小，至少不應(yīng)該大于或

等于客戶需要的鋼管的最小尺寸，在這種合理性假設(shè)下，可以選擇的切割方案一

共有4種，如下圖所示：

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

2.9米1201

2.1米0022

1.5米3120

合計7.47.37.27.1

料頭00.10.20.3

于是，問題轉(zhuǎn)化為在滿足用戶需要的條件下，按照哪些種合理的方案使原材

料最為節(jié)省。而所謂節(jié)省，切割后剩余的料頭和最小或切割原料鋼管的總根數(shù)最

少。

第二步：模型建立

假設(shè)四種方案切割的鋼管數(shù)分別為：Xi，X2,X3,X4

決策目標(biāo)：切割后剩余的料頭最小，設(shè)切割后剩余的料頭和為丫

則目標(biāo)函數(shù)為：

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

約束條件為：

XI+2X2+X4=100

2X3+2X4=100

3XI+X2+2X3=100

XI，X2，X3,XQO

上述實際問題就變成這樣個數(shù)學(xué)問題，求解滿足約束條件的X”X2,X3,

X4使丫達到最小值，這是一個規(guī)劃求解問題，建立完數(shù)學(xué)模型后，如果利用手

工求解是非常復(fù)雜的，我們可以利用Excel中的“規(guī)劃求解”工具進行求解。

第三步：數(shù)據(jù)準(zhǔn)備

利用Excel具體求解步驟如下：

（1）啟動Excel,新建一張工作表。

（2）按上表的形式將具體數(shù)據(jù)輸入到工作表中，如下圖所示：

（3）并且在B2單元格中輸入公式

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

即規(guī)劃求解的“目標(biāo)函數(shù)”，也可以用

=SUMPRODUCT（B8:E8,B9:E9）

如下圖所示：

（5）拖動F4單元格右下角的“填充柄”將公式復(fù)制到F5和F6兩個單元

格中，注意$B$9,$C$9,$D$9,$E$9四個單元格是絕對引用，在公式復(fù)制過程

中保持不變，而單元的相對引用，在公式復(fù)制過程將發(fā)生相應(yīng)的改變。

（6）在“工具”菜單中選擇“規(guī)劃求解”，在隨后出現(xiàn)“規(guī)劃求解參數(shù)”對

話框中，在“設(shè)置目標(biāo)單元格”中輸入目標(biāo)函數(shù)所在的單元格，既$8$2,在“等

于”選擇項中選擇“最小值”。

（7）在“可變單元格”中輸入代表變量Xi、X2、X3和X4所在的區(qū)域$B$9:$E$9。

（8）輸入“規(guī)劃求解”的“約束條件”，方法是單擊“添加”按鈕來添加“約

束條件”，出現(xiàn)“添加約束”對話框，在“單元格引用位置"輸入$F$4單元格；

然后選擇“=”；在“約束值”框中輸入100：如下圖所示：

然后單擊“添加”按鈕，類似地輸入下一個“約束條件”，最后單擊“確定”

按鈕。注意不要忘記輸入Xi，X2，X3,X420約束條件。

（9）單擊“規(guī)劃求解參數(shù)”對話框中的“求解”按鈕，計算機開始求解并

將最終結(jié)果顯示在存放變量的單元格中，如下圖所示：

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各方案所需的根數(shù)0403020

但也可能是下圖所示結(jié)果:

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各方案所需要的根數(shù)10.030.020.030.0

1。

最終計算結(jié)果是：“方案2”40根；“方案3”30根；“方案4”20根。所剩

料頭和為16米。

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

30根。所剩料頭和仍為16米。

4、規(guī)劃求解實例2：最佳購買方案問題

現(xiàn)有資金20萬元，準(zhǔn)備購買冬季御寒衣物一批，在批發(fā)市場調(diào)查后得到的

最低批發(fā)價如圖所示：

商品名稱單價數(shù)量金額

鞋4501450

帽1201120

毛衣2001200

圍巾80180

外套5001500

請問應(yīng)該怎么購買才能剛好用掉20萬元(注：確保每種產(chǎn)品都有，且鞋不

能低于20雙)。

答：

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

ABCDE

1商品名稱單價數(shù)量金額降費

2鞋450-1450一200000

3帽1201120

4豐衣2001200

5圍巾80180

6外奏500

________1------500,1

13501

8順求解裝

9

設(shè)置目標(biāo):①$D$7|

10

11到：O最大值(M)O最小值史@目標(biā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

課后習(xí)題5

1、數(shù)據(jù)表中給出了某廠12個車間加工同一產(chǎn)品所需時間的全部數(shù)據(jù)，如下

圖所示。

時間（時）

12

15

18

19

20

14

15

17

16

13

1211

（1）使用函數(shù)計算該廠12個車間加工這一產(chǎn)品所需時間的算術(shù)平均值、幾

何平均值、調(diào)和平均值、眾數(shù)、中位數(shù)、方差、標(biāo)準(zhǔn)差、偏度以及峰度。

（2）使用數(shù)據(jù)分析工具對該數(shù)據(jù)進行描述性統(tǒng)計分析，并將結(jié)果與（1）中

計算的結(jié)果進行對比，判斷兩者是否相同。

2、簡單平均法和趨勢平均法練習(xí)：以下數(shù)據(jù)表給出了1995?2018年間某廠

用電數(shù)的全部數(shù)據(jù)：

A___B

1用電數(shù)（萬度）

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）利用簡單移動平均法預(yù)測2019年的用電數(shù)，并確定誤差，已知步長為

3?

（2）利用趨勢移動平均法預(yù)測2019年的用電數(shù)，并確定誤差。

［解題：］簡單移動平均法預(yù)測結(jié)果如下：

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

ABCDEF

用電數(shù)（預(yù)測值預(yù)測誤差

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,____

趨勢移動平均法預(yù)測結(jié)果如下:

C30▼fx=C28+C29*1

ABD

用電數(shù)（萬度）

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認(rèn)+bj240.4666667I

此題也可用回歸計算趨勢移動平均:

用電數(shù)（萬度）

199545.8SU36￡ARYOUTPUT

199650.5675

415J199758.1484回歸疑計

199865.8551Multiple0.95246

617J199975.3RSauare0.90713

200080Adjusted0.90296

8I標(biāo)準(zhǔn)誤差

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標(biāo)準(zhǔn)誤差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、雞兔同籠問題。雞兔同籠是中國古代著名趣題之一，據(jù)《孫子算經(jīng)》記

載：今有雞兔同籠，上有三十五頭，下有九十四足，問雞兔各幾何？

［解題：］將其模型化后輸入到Excel表格中，如下圖所示：

IB7?AII=B4*2+B5*4

AL_BCD