商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件

第7章

商務(wù)數(shù)據(jù)處理分析第7章

商務(wù)數(shù)據(jù)處理分析1Excel提供了非常實(shí)用的數(shù)據(jù)分析工具，利用這些分析工具，可解決數(shù)據(jù)管理中的許多問題，例如財(cái)務(wù)分析工具、統(tǒng)計(jì)分析工具、工程分析工具、規(guī)劃求解工具、方案管理器等等。下面主要介紹財(cái)務(wù)管理與統(tǒng)計(jì)分析中常用的一些數(shù)據(jù)分析工具。Excel提供了非常實(shí)用的數(shù)據(jù)分析工具，利用這些分析工具，可27.1用模擬分析方法求解

1.單變量求解

單變量求解是求解只有一個(gè)變量的方程的根，方程可以是線性方程，也可以是非線性方程。單變量求解工具可以解決許多數(shù)據(jù)管理中涉及到一個(gè)變量的求解問題。[例1]某企業(yè)擬向銀行以7%的年利率借入期限為5年的長(zhǎng)期借款，企業(yè)每年的償還能力為100萬(wàn)元，那么企業(yè)最多總共可貸款多少？

圖7.1貸款總額計(jì)算

圖7.2“單變量求解”對(duì)話框7.1用模擬分析方法求解 1.單變量求解32.模擬運(yùn)算表模擬運(yùn)算表是將工作表中的一個(gè)單元格區(qū)域的數(shù)據(jù)進(jìn)行模擬計(jì)算，測(cè)試使用一個(gè)或兩個(gè)變量對(duì)運(yùn)算結(jié)果的影響。在Excel中，可以構(gòu)造兩種模擬運(yùn)算表：?jiǎn)巫兞磕M運(yùn)算表和多變量模擬運(yùn)算表。（1）單變量模擬運(yùn)算表單變量模擬運(yùn)算表是基于一個(gè)輸入變量，用它來(lái)模擬對(duì)一個(gè)或多個(gè)公式計(jì)算結(jié)果的影響。[例1]企業(yè)向銀行貸款10000元，期限5年，使用“模擬運(yùn)算表”模擬計(jì)算不同的利率對(duì)月還款額的影響，2.模擬運(yùn)算表4圖7.3單變量模擬運(yùn)算表圖7.3單變量模擬運(yùn)算表5圖7.4“模擬運(yùn)算表”對(duì)話框

圖7.5單變量的模擬運(yùn)算表商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件63．

方案管理器在企業(yè)的生產(chǎn)經(jīng)營(yíng)活動(dòng)中，由于市場(chǎng)的不斷變化，企業(yè)的生產(chǎn)銷售受到各種因素的影響，企業(yè)需要估計(jì)這些因素并分析其對(duì)企業(yè)生產(chǎn)銷售的影響。Excel提供了稱為方案的工具來(lái)解決上述問題，利用其提供的方案管理器，可以很方便地對(duì)多種方案進(jìn)行模擬分析。例如，不同的市場(chǎng)狀況、不同的定價(jià)策略等,所可能產(chǎn)生的結(jié)果，也即利潤(rùn)會(huì)怎樣變化。[例1]某企業(yè)生產(chǎn)光盤，現(xiàn)使用方案管理器，假設(shè)生產(chǎn)不同數(shù)量的光盤（例如3000，5000，10000），對(duì)利潤(rùn)的影響。3．方案管理器7已知：在該例中有4個(gè)可變量：?jiǎn)蝺r(jià)、數(shù)量、推銷費(fèi)率和單片成本。利潤(rùn)=銷售金額－成本－費(fèi)用*（1+推銷費(fèi)率）銷售=單價(jià)*數(shù)量費(fèi)用=20000成本=固定成本＋單價(jià)*單片成本固定成本=70000已知：在該例中有4個(gè)可變量：?jiǎn)蝺r(jià)、數(shù)量、推銷費(fèi)率和單片成本。8（1）建立方案步驟如下：建立模型：將數(shù)據(jù)、變量及公式輸入在工作表中，如圖7.9所示。在單元格“B7:B10”中保存著要進(jìn)行模擬的4個(gè)變量,分別是：?jiǎn)蝺r(jià)、數(shù)量、單片成本和推銷費(fèi)率。圖7.9建立模型（1）建立方案9圖7.10“方案管理器”對(duì)話框

圖7.11“編輯方案”對(duì)話框商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件10圖7.12“方案量值”對(duì)話框商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件11（2）顯示方案設(shè)定了各種模擬方案后，任何時(shí)候都可以執(zhí)行方案，察看模擬的結(jié)果。

圖7.13顯示運(yùn)算結(jié)果（按鈕。圖7.13顯示運(yùn)算結(jié)果（2）顯示方案（按鈕。圖7.13顯示運(yùn)算結(jié)果12（3）修改、刪除或增加方案（4）建立方案報(bào)告當(dāng)需要將所有的方案執(zhí)行結(jié)果都顯示出來(lái)時(shí)，可建立方案報(bào)告。圖7.14（a）“方案總結(jié)”對(duì)話框

（b）“方案總結(jié)”工作表（3）修改、刪除或增加方案137.2線性回歸分析回歸分析法，是在掌握大量觀察數(shù)據(jù)的基礎(chǔ)上，利用數(shù)理統(tǒng)計(jì)方法建立因變量與自變量之間的回歸關(guān)系函數(shù)表達(dá)式（稱回歸方程式）。回歸分析中，當(dāng)研究的因果關(guān)系只涉及因變量和一個(gè)自變量時(shí)，叫做一元回歸分析；當(dāng)研究的因果關(guān)系涉及因變量和兩個(gè)或兩個(gè)以上自變量時(shí)，叫做多元回歸分析。回歸分析中，又依據(jù)描述自變量與因變量之間因果關(guān)系的函數(shù)表達(dá)式是線性的還是非線性的，分為線性回歸分析和非線性回歸分析。通常線性回歸分析法是最基本的分析方法，遇到非線性回歸問題可以借助數(shù)學(xué)手段化為線性回歸問題處理。7.2線性回歸分析回歸分析法，是在掌握大量觀察數(shù)據(jù)的基礎(chǔ)14[例1]

對(duì)銷售額進(jìn)行多元回歸分析預(yù)測(cè)，數(shù)據(jù)見圖。[例1]對(duì)銷售額進(jìn)行多元回歸分析預(yù)測(cè)，數(shù)據(jù)見圖。15解：本題可用二元線性回歸分析來(lái)求解：設(shè)定變量：Y=銷售額，X1=電視廣告費(fèi)用，X2=報(bào)紙廣告費(fèi)用方程為：Y=a1X1+a2X2+b通過線性回歸分析確定a1,a2,b的值，從而確定方程。解：本題可用二元線性回歸分析來(lái)求解：161．操作方法與步驟(1)

建立數(shù)據(jù)模型(2)工具→加載宏/“分析工具庫(kù)”復(fù)選框(3)工具→數(shù)據(jù)分析/“回歸”(4）設(shè)置“回歸”對(duì)話框中參數(shù):“Y值輸入?yún)^(qū)”，“X值輸入?yún)^(qū)”，“輸出區(qū)域”(5)根據(jù)結(jié)果得出方程1．操作方法與步驟17圖7.15在EXCEL工作表中建立數(shù)據(jù)模型

圖7.16“回歸”對(duì)話框

中參數(shù)的設(shè)置商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件18由回歸分析結(jié)果可見：回歸方程y=a1*x1+a2*x2+b中，a1=2.2901836209178；a2=1.30098909825998；b=832.300916901311商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件19表7.1 回歸結(jié)果整理4.預(yù)測(cè)未來(lái)的銷售額假設(shè)2002年的電視廣告費(fèi)用預(yù)算為35萬(wàn)元，報(bào)紙廣告費(fèi)用預(yù)算為18萬(wàn)元，則根據(jù)多元線性回歸方程y=2.290183621*x1+1.300989098*x2+832.3009169可計(jì)算出2002年的銷售額為2.290183621*35+1.300989098*18+832.3009169即913.7583萬(wàn)元。多元回歸方程：y=2.290183621*x1+1.300989098*x2+832.3009169標(biāo)準(zhǔn)差：a1=0.304064556a2=0.320701597b=15.73868952判定系數(shù)=0.9191356y估計(jì)值的標(biāo)準(zhǔn)誤差=6.425873026F統(tǒng)計(jì)值=28.37776839自由度=5回歸平方和=2343.540779殘差平方和=206.4592208表7.1 回歸結(jié)果整理多元回歸方程：y=2.29018362207.3規(guī)劃求解

“規(guī)劃”是數(shù)學(xué)概念，它是指運(yùn)用微積分和線性代數(shù)的方法，在滿足一組約束條件的情況下，求出一個(gè)多變量函數(shù)極值的模型。數(shù)學(xué)規(guī)劃是運(yùn)籌學(xué)的一個(gè)分支，主要包括線性規(guī)劃、非線性規(guī)劃、動(dòng)態(tài)規(guī)劃和整數(shù)規(guī)劃等。規(guī)劃求解可求得工作表上某個(gè)單元格（目標(biāo)單元格）中公式的最優(yōu)值。（對(duì)直接或間接與目標(biāo)單元格中公式相關(guān)聯(lián)的一組單元格中的數(shù)值進(jìn)行調(diào)整，最終在目標(biāo)單元格公式中求得期望的結(jié)果）7.3規(guī)劃求解21意義：規(guī)劃求解用于在生產(chǎn)或工作中的一些問題或項(xiàng)目在受多個(gè)因素的制約的前提下如何獲得最佳的結(jié)果。如獲得最大的利潤(rùn)、最小的成本等。注意事項(xiàng)：在創(chuàng)建模型過程中，對(duì)“規(guī)劃求解”模型中的可變單元格數(shù)值應(yīng)用約束條件，而且約束條件可以引用其他影響目標(biāo)單元格公式的單元格。添加規(guī)劃求解命令:單擊文件選項(xiàng)卡→選項(xiàng)→加載項(xiàng)→規(guī)劃求解加載項(xiàng)→轉(zhuǎn)到，在“加載宏“對(duì)話框中選定“規(guī)劃求解”復(fù)選框，然后單擊“確定”按鈕。意義：規(guī)劃求解用于在生產(chǎn)或工作中的一些問題或項(xiàng)目在受多個(gè)因素22

1、求解線性規(guī)劃問題 118例：工廠生產(chǎn)3種玩具：狗、娃娃、大熊貓；3種玩具的凈利潤(rùn)（元）：90、75、503種玩具各用機(jī)工（小時(shí)）：3、4、53種玩具各用手工（小時(shí)）：4、3、2

1、求解線性規(guī)劃問題 11823工廠極限生產(chǎn)能力：機(jī)工：<=400h

手工：<=280h

要求：生產(chǎn)玩具狗的數(shù)量<=50件生產(chǎn)玩具大熊貓數(shù)量>=32件

求滿足以上條件時(shí)，如何安排各玩具的生產(chǎn)數(shù)量以獲得最大利潤(rùn)。

工廠極限生產(chǎn)能力：機(jī)工：<=400h 241．建立數(shù)據(jù)模型:圖7.21建立數(shù)據(jù)模型1．建立數(shù)據(jù)模型:圖7.21建立數(shù)據(jù)模型25（2）進(jìn)行求解單擊數(shù)據(jù)選項(xiàng)卡→規(guī)劃求解，彈出“規(guī)劃求解參數(shù)”對(duì)話框，如圖7.22所示（2）進(jìn)行求解26在建立好所有的規(guī)劃求解參數(shù)后，單擊“求解”，則系統(tǒng)將顯示如圖7.25所示的“規(guī)劃求解結(jié)果”對(duì)話框，選擇“保存規(guī)劃求解結(jié)果”項(xiàng)，單擊“確定”，則求解結(jié)果顯示在工作表上，如圖7.25所示。在建立好所有的規(guī)劃求解參數(shù)后，單擊“求解”，則系統(tǒng)將顯示如圖7.25所示的“規(guī)劃求解結(jié)果”對(duì)話框，選擇“保存規(guī)劃求解結(jié)果”項(xiàng)，單擊“確定”，求解結(jié)果如圖7.25。圖7.25“規(guī)劃求解結(jié)果”對(duì)話框

圖7.26運(yùn)算結(jié)果在建立好所有的規(guī)劃求解參數(shù)后，單擊“求解”，則系統(tǒng)將顯示如圖27

求解的結(jié)果可形成：·

運(yùn)算結(jié)果報(bào)告·

敏感性報(bào)告·

極限值報(bào)告求解的結(jié)果可形成：282、求解方程組的解 （1）建立計(jì)算模型：在工作表中輸入數(shù)據(jù)及公式，如圖7.27所示；圖7.27利用“規(guī)劃求解”工具求解方程組2、求解方程組的解 （1）建立計(jì)算模型：在工作表中輸入數(shù)29（2）單擊數(shù)據(jù)選項(xiàng)卡→規(guī)劃求解，彈出“規(guī)劃求解參數(shù)”對(duì)話框，設(shè)置“規(guī)劃求解參數(shù)”對(duì)話框中的參數(shù)（3）單擊“求解”，即可得到方程組的解，如圖7.28所示。圖7.28求解結(jié)果（2）單擊數(shù)據(jù)選項(xiàng)卡→規(guī)劃求解，彈出“規(guī)劃求解參數(shù)”對(duì)話框，307.4

移動(dòng)平均 移動(dòng)平均法是根據(jù)時(shí)間序列資料，逐項(xiàng)推移，依次計(jì)算移動(dòng)平均，來(lái)反映現(xiàn)象的長(zhǎng)期趨勢(shì)。特別是現(xiàn)象的變量值受周期變動(dòng)和不規(guī)則變動(dòng)的影響，起伏較大，不能明顯地反映現(xiàn)象的變動(dòng)趨勢(shì)時(shí)，運(yùn)用移動(dòng)平均法，消除這些因素的影響，進(jìn)行動(dòng)態(tài)數(shù)據(jù)的修勻，以利于進(jìn)行長(zhǎng)期趨勢(shì)的分析和預(yù)測(cè)。

7.4移動(dòng)平均 31簡(jiǎn)單移動(dòng)平均的計(jì)算公式：設(shè)xi為時(shí)間序列中的某時(shí)間點(diǎn)的觀測(cè)值，其樣本數(shù)為N；每次移動(dòng)地求算術(shù)平均值所采用的觀測(cè)值的個(gè)數(shù)為n（n的取值范圍：2<n<t-1），則在第t時(shí)間點(diǎn)的移動(dòng)平均值Mi式中：Mi——第t時(shí)間點(diǎn)的移動(dòng)平均值，也可當(dāng)作第t+1時(shí)間點(diǎn)的預(yù)測(cè)值，即：yi+1=Mi或yi=Mi-1移動(dòng)平均分析工具及其公式可以基于特定的過去某段時(shí)期中變量的均值，對(duì)未來(lái)值進(jìn)行預(yù)測(cè)簡(jiǎn)單移動(dòng)平均的計(jì)算公式：式中：Mi——第t時(shí)間點(diǎn)的移動(dòng)平均值32[例1]某公司1994至2005年銷售額數(shù)據(jù)如圖所示。進(jìn)行三年移動(dòng)平均，并預(yù)測(cè)2006年銷售額。操作步驟：

（1）建立模型：將原始數(shù)據(jù)輸入到單元格區(qū)域A1:B13，如圖7.29所示；圖7.29建立數(shù)據(jù)模型[例1]某公司1994至2005年銷售額數(shù)據(jù)如圖所示。進(jìn)行33（2）單擊數(shù)據(jù)選項(xiàng)卡→數(shù)據(jù)分析，彈出“數(shù)據(jù)分析”對(duì)話框；設(shè)置“移動(dòng)平均”對(duì)話框，確定。圖7.30“移動(dòng)平均”對(duì)話框（2）單擊數(shù)據(jù)選項(xiàng)卡→數(shù)據(jù)分析，彈出“數(shù)據(jù)分析”對(duì)話框；圖34結(jié)果如圖。單元格區(qū)域D5:D12即為標(biāo)準(zhǔn)誤差。根據(jù)公式y(tǒng)i+1=Mi得2006年的銷售額預(yù)測(cè)應(yīng)為M2005，所以為640。7.31“移動(dòng)平均”的分析結(jié)果結(jié)果如圖。單元格區(qū)域D5:D12即為標(biāo)準(zhǔn)誤差。根據(jù)公式y(tǒng)i+357.5指數(shù)平滑指數(shù)平滑是在移動(dòng)平均的基礎(chǔ)上的進(jìn)一步擴(kuò)展。指數(shù)平滑法是用過去時(shí)間數(shù)列值的加權(quán)平均數(shù)作為趨勢(shì)值，越靠近當(dāng)前時(shí)間的指標(biāo)越具有參考價(jià)值，因此給予更大的權(quán)重，按照這種隨時(shí)間指數(shù)衰減的規(guī)律對(duì)原始數(shù)據(jù)進(jìn)行加權(quán)修勻。所以它是加權(quán)移動(dòng)平均法的一種特殊情形。其基本形式是根據(jù)本期的實(shí)際值Yt和本期的趨勢(shì)值，分別給以不同權(quán)數(shù)α和1–α，計(jì)算加權(quán)平均數(shù)作為下期的趨勢(shì)值。7.5指數(shù)平滑36[例1]利用上題例題的數(shù)據(jù)，用Excel進(jìn)行單指數(shù)平滑進(jìn)行分析。操作步驟：建立數(shù)據(jù)模型；單擊數(shù)據(jù)選項(xiàng)卡→數(shù)據(jù)分析→指數(shù)平滑，“確定”，顯示“指數(shù)平滑”對(duì)話框；設(shè)置“指數(shù)平滑”對(duì)話框圖7.32“指數(shù)平滑”對(duì)話框[例1]利用上題例題的數(shù)據(jù)，用Excel進(jìn)行單指數(shù)平滑進(jìn)行分37商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件38結(jié)果：結(jié)果：397.6相關(guān)分析相關(guān)關(guān)系是指變量之間存在的不完全確定性的關(guān)系。在實(shí)際問題中，許多變量之間的關(guān)系并不是完全確定性的，例如居民家庭消費(fèi)與居民家庭收入這兩個(gè)變量的關(guān)系就不是完全確定的。收入水平相同的家庭，它們的消費(fèi)額往往不同；消費(fèi)額相同的家庭，它們的收入也可能不同。對(duì)現(xiàn)象之間相關(guān)關(guān)系密切程度的研究，稱為相關(guān)分析。對(duì)兩個(gè)變量之間線性相關(guān)程度的度量稱為簡(jiǎn)單相關(guān)系數(shù)---r。7.6相關(guān)分析40簡(jiǎn)單相關(guān)系數(shù)又稱皮爾遜相關(guān)系數(shù)，它描述了兩個(gè)定距變量間聯(lián)系的緊密程度。樣本的簡(jiǎn)單相關(guān)系數(shù)一般用r表示。R的性質(zhì)：

r>0為正相關(guān)，r<0為負(fù)相關(guān)。R=1表明兩個(gè)變量完全線性相關(guān)；

r=0表明兩個(gè)變量完全不線性相關(guān)，但兩個(gè)變量之間有可能存在非線性相關(guān)。

相關(guān)程度劃分:

當(dāng)r>=0.8，高度相關(guān)；

0.5<=r<0.8，視為中度相關(guān)；

0.3<=r<0.5，視為低度相關(guān)；

|r|<0.3時(shí)，視為不相關(guān)。簡(jiǎn)單相關(guān)系數(shù)又稱皮爾遜相關(guān)系數(shù)，它描述了兩個(gè)定距變量間聯(lián)系的41[例]根據(jù)下圖的數(shù)據(jù)，對(duì)家庭月消費(fèi)支出與家庭月收入的數(shù)據(jù)進(jìn)行相關(guān)分析。[例]根據(jù)下圖的數(shù)據(jù)，對(duì)家庭月消費(fèi)支出與家庭月收入的數(shù)據(jù)進(jìn)行42操作步驟：建立數(shù)據(jù)模型；工具→數(shù)據(jù)分析/相關(guān)系數(shù)設(shè)置“相關(guān)系數(shù)”對(duì)話框內(nèi)容操作步驟：43“確定”，輸出結(jié)果如圖所示。分析結(jié)果表明：相關(guān)系數(shù)r=0.979747601，表示家庭月消費(fèi)支出與家庭月收入之間存在高度正相關(guān)關(guān)系。“確定”，輸出結(jié)果如圖所示。分析結(jié)果表明：相關(guān)系數(shù)r=0.944分析結(jié)果表明：相關(guān)系數(shù)r=0.979747601，表示家庭月消費(fèi)支出與家庭月收入之間存在高度正相關(guān)關(guān)系。操作步驟:（1）建立數(shù)據(jù)模型：將數(shù)據(jù)輸入到工作表區(qū)域A1:E16中，如圖8.38所示；分析結(jié)果表明：相關(guān)系數(shù)r=0.979747601，表示家庭月457.7方差分析方差分析(AnalysisofVariance,縮寫為ANOVA)是數(shù)理統(tǒng)計(jì)學(xué)中常用的數(shù)據(jù)處理方法之一，是經(jīng)濟(jì)和科學(xué)研究中分析試驗(yàn)數(shù)據(jù)的一種有效的工具。運(yùn)用數(shù)理的方法對(duì)數(shù)據(jù)進(jìn)行分析，以鑒別各種因素對(duì)研究對(duì)象某些特征值的影響大小和影響方式，這種方法就叫做方差分析。所關(guān)注的對(duì)象的特征稱為指標(biāo)，影響指標(biāo)的各種原因叫做因素，在實(shí)驗(yàn)中因素的各種不同狀態(tài)稱為因素的水平。7.7方差分析467.7.1單因素方差分析單因素方差分析的作用是通過對(duì)某一因素的不同水平進(jìn)行多次觀測(cè)，然后通過統(tǒng)計(jì)分析判斷該因素的不同水平對(duì)考察指標(biāo)的影響是否相同。從理論上講，實(shí)際上是在檢驗(yàn)幾個(gè)等方差正態(tài)總體的等均值假設(shè)。單因素方差分析的基本假設(shè)是各組的均值相等。[例1]為了考察不同的銷售渠道對(duì)總銷售額的貢獻(xiàn)，連續(xù)半年對(duì)不同渠道的業(yè)績(jī)進(jìn)行觀測(cè)，得到一組數(shù)據(jù)如圖所示，要求用方差分析判斷各渠道的作用是否相同。本題是一個(gè)典型的單因素方差分析問題，渠道作為營(yíng)業(yè)業(yè)績(jī)這個(gè)指標(biāo)的一個(gè)主要因素，而不同的渠道可以視做該因素的不同水平。7.7.1單因素方差分析47操作步驟:建立數(shù)據(jù)模型操作步驟:48單擊工具→數(shù)據(jù)分析/方差分析：?jiǎn)我蛩胤讲罘治觥保O(shè)置對(duì)話框的內(nèi)容，“確定”。單擊工具→數(shù)據(jù)分析/方差分析：?jiǎn)我蛩胤讲罘治觥保?9商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件50運(yùn)算結(jié)果說明：運(yùn)算結(jié)果分為概要和方差分析兩部分。概要：返回每組數(shù)據(jù)（代表因素的一個(gè)水平）樣本數(shù)、合計(jì)、均值和方差。方差分析：返回標(biāo)準(zhǔn)的單因素方差分析表，包括離差平方和、自由度、均方、F統(tǒng)計(jì)量、概率值、F臨界值。運(yùn)算結(jié)果說明：51其中的“組間”就是影響銷售額的因素(不同的銷售渠道)，“組內(nèi)”就是誤差，“總計(jì)”就是總和，“差異源”則是方差來(lái)源，“SS”---平方和，“df”---自由度，“MS”---均方，“F”---F比（F統(tǒng)計(jì)量），“P-value”則是原假設(shè)(結(jié)論)成立的概率(這個(gè)數(shù)值越接近0，說明原假設(shè)成立的可能性越小，反之原假設(shè)成立的可能性越大，“Fcrit”為拒絕域的臨界值。其中的“組間”就是影響銷售額的因素(不同的銷售渠道)，“組內(nèi)52分析組內(nèi)和組間離差平方和在總離差平方和中所占的比重，可以直觀的看出各組數(shù)據(jù)對(duì)總體離差的貢獻(xiàn)。將F統(tǒng)計(jì)量的值與臨界值比較，可以判定是否接受等均值的假設(shè)。其中F臨界值是用FINV函數(shù)計(jì)算出來(lái)的。本例中F統(tǒng)計(jì)值是0.848783，遠(yuǎn)遠(yuǎn)小于F臨界值3.098393。所以，接受等均值假設(shè)。即認(rèn)為四種渠道的總體水平?jīng)]有明顯差距。從顯著性分析上也可以看出，概率高達(dá)0.48，遠(yuǎn)遠(yuǎn)大于0.05。分析組內(nèi)和組間離差平方和在總離差平方和中所占的比重，可以直觀537.8Z-檢驗(yàn)Z檢驗(yàn)：是在總體方差已知的條件下兩個(gè)樣本均值之差的檢驗(yàn)。[例1]某企業(yè)管理人員對(duì)采用兩種方法組裝新產(chǎn)品所需的時(shí)間（分鐘）進(jìn)行測(cè)試，隨機(jī)抽取6個(gè)工人，讓他們分別采用兩種方法組裝同一種產(chǎn)品，采用方法A組裝所需的時(shí)間和采用方法B組裝所需的時(shí)間如圖8.54所示。假設(shè)組裝的時(shí)間服從正態(tài)分布，以α=0.05的顯著性水平比較兩種組裝方法是否有差別。

7.8Z-檢驗(yàn)54操作步驟：建立數(shù)據(jù)模型

工具→數(shù)據(jù)分析/Z-檢驗(yàn)：雙樣本平均差檢驗(yàn)”設(shè)置對(duì)話框，確定。操作步驟：工具→數(shù)據(jù)分析/Z-檢驗(yàn)：雙樣本平均差檢驗(yàn)”55商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件56結(jié)果：結(jié)果：57結(jié)果分析：可以根據(jù)P值進(jìn)行判斷，也可以根據(jù)統(tǒng)計(jì)量和臨界值比較進(jìn)行判斷。本例采用的是單尾檢驗(yàn)，其單尾P值為0.17，大于給定的顯著性水平0.05，所以應(yīng)該接受原假設(shè)，即方法A與方法B相比沒有顯著差別若用臨界值判斷，得出的結(jié)論是一樣的，Z值為0.938194，小于臨界值1.644853，由于是右尾檢驗(yàn)，所以也是接受原假設(shè)。結(jié)果分析：58第7章

商務(wù)數(shù)據(jù)處理分析第7章

商務(wù)數(shù)據(jù)處理分析59Excel提供了非常實(shí)用的數(shù)據(jù)分析工具，利用這些分析工具，可解決數(shù)據(jù)管理中的許多問題，例如財(cái)務(wù)分析工具、統(tǒng)計(jì)分析工具、工程分析工具、規(guī)劃求解工具、方案管理器等等。下面主要介紹財(cái)務(wù)管理與統(tǒng)計(jì)分析中常用的一些數(shù)據(jù)分析工具。Excel提供了非常實(shí)用的數(shù)據(jù)分析工具，利用這些分析工具，可607.1用模擬分析方法求解

1.單變量求解

單變量求解是求解只有一個(gè)變量的方程的根，方程可以是線性方程，也可以是非線性方程。單變量求解工具可以解決許多數(shù)據(jù)管理中涉及到一個(gè)變量的求解問題。[例1]某企業(yè)擬向銀行以7%的年利率借入期限為5年的長(zhǎng)期借款，企業(yè)每年的償還能力為100萬(wàn)元，那么企業(yè)最多總共可貸款多少？

圖7.1貸款總額計(jì)算

圖7.2“單變量求解”對(duì)話框7.1用模擬分析方法求解 1.單變量求解612.模擬運(yùn)算表模擬運(yùn)算表是將工作表中的一個(gè)單元格區(qū)域的數(shù)據(jù)進(jìn)行模擬計(jì)算，測(cè)試使用一個(gè)或兩個(gè)變量對(duì)運(yùn)算結(jié)果的影響。在Excel中，可以構(gòu)造兩種模擬運(yùn)算表：?jiǎn)巫兞磕M運(yùn)算表和多變量模擬運(yùn)算表。（1）單變量模擬運(yùn)算表單變量模擬運(yùn)算表是基于一個(gè)輸入變量，用它來(lái)模擬對(duì)一個(gè)或多個(gè)公式計(jì)算結(jié)果的影響。[例1]企業(yè)向銀行貸款10000元，期限5年，使用“模擬運(yùn)算表”模擬計(jì)算不同的利率對(duì)月還款額的影響，2.模擬運(yùn)算表62圖7.3單變量模擬運(yùn)算表圖7.3單變量模擬運(yùn)算表63圖7.4“模擬運(yùn)算表”對(duì)話框

圖7.5單變量的模擬運(yùn)算表商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件643．

方案管理器在企業(yè)的生產(chǎn)經(jīng)營(yíng)活動(dòng)中，由于市場(chǎng)的不斷變化，企業(yè)的生產(chǎn)銷售受到各種因素的影響，企業(yè)需要估計(jì)這些因素并分析其對(duì)企業(yè)生產(chǎn)銷售的影響。Excel提供了稱為方案的工具來(lái)解決上述問題，利用其提供的方案管理器，可以很方便地對(duì)多種方案進(jìn)行模擬分析。例如，不同的市場(chǎng)狀況、不同的定價(jià)策略等,所可能產(chǎn)生的結(jié)果，也即利潤(rùn)會(huì)怎樣變化。[例1]某企業(yè)生產(chǎn)光盤，現(xiàn)使用方案管理器，假設(shè)生產(chǎn)不同數(shù)量的光盤（例如3000，5000，10000），對(duì)利潤(rùn)的影響。3．方案管理器65已知：在該例中有4個(gè)可變量：?jiǎn)蝺r(jià)、數(shù)量、推銷費(fèi)率和單片成本。利潤(rùn)=銷售金額－成本－費(fèi)用*（1+推銷費(fèi)率）銷售=單價(jià)*數(shù)量費(fèi)用=20000成本=固定成本＋單價(jià)*單片成本固定成本=70000已知：在該例中有4個(gè)可變量：?jiǎn)蝺r(jià)、數(shù)量、推銷費(fèi)率和單片成本。66（1）建立方案步驟如下：建立模型：將數(shù)據(jù)、變量及公式輸入在工作表中，如圖7.9所示。在單元格“B7:B10”中保存著要進(jìn)行模擬的4個(gè)變量,分別是：?jiǎn)蝺r(jià)、數(shù)量、單片成本和推銷費(fèi)率。圖7.9建立模型（1）建立方案67圖7.10“方案管理器”對(duì)話框

圖7.11“編輯方案”對(duì)話框商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件68圖7.12“方案量值”對(duì)話框商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件69（2）顯示方案設(shè)定了各種模擬方案后，任何時(shí)候都可以執(zhí)行方案，察看模擬的結(jié)果。

圖7.13顯示運(yùn)算結(jié)果（按鈕。圖7.13顯示運(yùn)算結(jié)果（2）顯示方案（按鈕。圖7.13顯示運(yùn)算結(jié)果70（3）修改、刪除或增加方案（4）建立方案報(bào)告當(dāng)需要將所有的方案執(zhí)行結(jié)果都顯示出來(lái)時(shí)，可建立方案報(bào)告。圖7.14（a）“方案總結(jié)”對(duì)話框

（b）“方案總結(jié)”工作表（3）修改、刪除或增加方案717.2線性回歸分析回歸分析法，是在掌握大量觀察數(shù)據(jù)的基礎(chǔ)上，利用數(shù)理統(tǒng)計(jì)方法建立因變量與自變量之間的回歸關(guān)系函數(shù)表達(dá)式（稱回歸方程式）?；貧w分析中，當(dāng)研究的因果關(guān)系只涉及因變量和一個(gè)自變量時(shí)，叫做一元回歸分析；當(dāng)研究的因果關(guān)系涉及因變量和兩個(gè)或兩個(gè)以上自變量時(shí)，叫做多元回歸分析。回歸分析中，又依據(jù)描述自變量與因變量之間因果關(guān)系的函數(shù)表達(dá)式是線性的還是非線性的，分為線性回歸分析和非線性回歸分析。通常線性回歸分析法是最基本的分析方法，遇到非線性回歸問題可以借助數(shù)學(xué)手段化為線性回歸問題處理。7.2線性回歸分析回歸分析法，是在掌握大量觀察數(shù)據(jù)的基礎(chǔ)72[例1]

對(duì)銷售額進(jìn)行多元回歸分析預(yù)測(cè)，數(shù)據(jù)見圖。[例1]對(duì)銷售額進(jìn)行多元回歸分析預(yù)測(cè)，數(shù)據(jù)見圖。73解：本題可用二元線性回歸分析來(lái)求解：設(shè)定變量：Y=銷售額，X1=電視廣告費(fèi)用，X2=報(bào)紙廣告費(fèi)用方程為：Y=a1X1+a2X2+b通過線性回歸分析確定a1,a2,b的值，從而確定方程。解：本題可用二元線性回歸分析來(lái)求解：741．操作方法與步驟(1)

建立數(shù)據(jù)模型(2)工具→加載宏/“分析工具庫(kù)”復(fù)選框(3)工具→數(shù)據(jù)分析/“回歸”(4）設(shè)置“回歸”對(duì)話框中參數(shù):“Y值輸入?yún)^(qū)”，“X值輸入?yún)^(qū)”，“輸出區(qū)域”(5)根據(jù)結(jié)果得出方程1．操作方法與步驟75圖7.15在EXCEL工作表中建立數(shù)據(jù)模型

圖7.16“回歸”對(duì)話框

中參數(shù)的設(shè)置商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件76由回歸分析結(jié)果可見：回歸方程y=a1*x1+a2*x2+b中，a1=2.2901836209178；a2=1.30098909825998；b=832.300916901311商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件77表7.1 回歸結(jié)果整理4.預(yù)測(cè)未來(lái)的銷售額假設(shè)2002年的電視廣告費(fèi)用預(yù)算為35萬(wàn)元，報(bào)紙廣告費(fèi)用預(yù)算為18萬(wàn)元，則根據(jù)多元線性回歸方程y=2.290183621*x1+1.300989098*x2+832.3009169可計(jì)算出2002年的銷售額為2.290183621*35+1.300989098*18+832.3009169即913.7583萬(wàn)元。多元回歸方程：y=2.290183621*x1+1.300989098*x2+832.3009169標(biāo)準(zhǔn)差：a1=0.304064556a2=0.320701597b=15.73868952判定系數(shù)=0.9191356y估計(jì)值的標(biāo)準(zhǔn)誤差=6.425873026F統(tǒng)計(jì)值=28.37776839自由度=5回歸平方和=2343.540779殘差平方和=206.4592208表7.1 回歸結(jié)果整理多元回歸方程：y=2.29018362787.3規(guī)劃求解

“規(guī)劃”是數(shù)學(xué)概念，它是指運(yùn)用微積分和線性代數(shù)的方法，在滿足一組約束條件的情況下，求出一個(gè)多變量函數(shù)極值的模型。數(shù)學(xué)規(guī)劃是運(yùn)籌學(xué)的一個(gè)分支，主要包括線性規(guī)劃、非線性規(guī)劃、動(dòng)態(tài)規(guī)劃和整數(shù)規(guī)劃等。規(guī)劃求解可求得工作表上某個(gè)單元格（目標(biāo)單元格）中公式的最優(yōu)值。（對(duì)直接或間接與目標(biāo)單元格中公式相關(guān)聯(lián)的一組單元格中的數(shù)值進(jìn)行調(diào)整，最終在目標(biāo)單元格公式中求得期望的結(jié)果）7.3規(guī)劃求解79意義：規(guī)劃求解用于在生產(chǎn)或工作中的一些問題或項(xiàng)目在受多個(gè)因素的制約的前提下如何獲得最佳的結(jié)果。如獲得最大的利潤(rùn)、最小的成本等。注意事項(xiàng)：在創(chuàng)建模型過程中，對(duì)“規(guī)劃求解”模型中的可變單元格數(shù)值應(yīng)用約束條件，而且約束條件可以引用其他影響目標(biāo)單元格公式的單元格。添加規(guī)劃求解命令:單擊文件選項(xiàng)卡→選項(xiàng)→加載項(xiàng)→規(guī)劃求解加載項(xiàng)→轉(zhuǎn)到，在“加載宏“對(duì)話框中選定“規(guī)劃求解”復(fù)選框，然后單擊“確定”按鈕。意義：規(guī)劃求解用于在生產(chǎn)或工作中的一些問題或項(xiàng)目在受多個(gè)因素80

1、求解線性規(guī)劃問題 118例：工廠生產(chǎn)3種玩具：狗、娃娃、大熊貓；3種玩具的凈利潤(rùn)（元）：90、75、503種玩具各用機(jī)工（小時(shí)）：3、4、53種玩具各用手工（小時(shí)）：4、3、2

1、求解線性規(guī)劃問題 11881工廠極限生產(chǎn)能力：機(jī)工：<=400h

手工：<=280h

要求：生產(chǎn)玩具狗的數(shù)量<=50件生產(chǎn)玩具大熊貓數(shù)量>=32件

求滿足以上條件時(shí)，如何安排各玩具的生產(chǎn)數(shù)量以獲得最大利潤(rùn)。

工廠極限生產(chǎn)能力：機(jī)工：<=400h 821．建立數(shù)據(jù)模型:圖7.21建立數(shù)據(jù)模型1．建立數(shù)據(jù)模型:圖7.21建立數(shù)據(jù)模型83（2）進(jìn)行求解單擊數(shù)據(jù)選項(xiàng)卡→規(guī)劃求解，彈出“規(guī)劃求解參數(shù)”對(duì)話框，如圖7.22所示（2）進(jìn)行求解84在建立好所有的規(guī)劃求解參數(shù)后，單擊“求解”，則系統(tǒng)將顯示如圖7.25所示的“規(guī)劃求解結(jié)果”對(duì)話框，選擇“保存規(guī)劃求解結(jié)果”項(xiàng)，單擊“確定”，則求解結(jié)果顯示在工作表上，如圖7.25所示。在建立好所有的規(guī)劃求解參數(shù)后，單擊“求解”，則系統(tǒng)將顯示如圖7.25所示的“規(guī)劃求解結(jié)果”對(duì)話框，選擇“保存規(guī)劃求解結(jié)果”項(xiàng)，單擊“確定”，求解結(jié)果如圖7.25。圖7.25“規(guī)劃求解結(jié)果”對(duì)話框

圖7.26運(yùn)算結(jié)果在建立好所有的規(guī)劃求解參數(shù)后，單擊“求解”，則系統(tǒng)將顯示如圖85

求解的結(jié)果可形成：·

運(yùn)算結(jié)果報(bào)告·

敏感性報(bào)告·

極限值報(bào)告求解的結(jié)果可形成：862、求解方程組的解 （1）建立計(jì)算模型：在工作表中輸入數(shù)據(jù)及公式，如圖7.27所示；圖7.27利用“規(guī)劃求解”工具求解方程組2、求解方程組的解 （1）建立計(jì)算模型：在工作表中輸入數(shù)87（2）單擊數(shù)據(jù)選項(xiàng)卡→規(guī)劃求解，彈出“規(guī)劃求解參數(shù)”對(duì)話框，設(shè)置“規(guī)劃求解參數(shù)”對(duì)話框中的參數(shù)（3）單擊“求解”，即可得到方程組的解，如圖7.28所示。圖7.28求解結(jié)果（2）單擊數(shù)據(jù)選項(xiàng)卡→規(guī)劃求解，彈出“規(guī)劃求解參數(shù)”對(duì)話框，887.4

移動(dòng)平均 移動(dòng)平均法是根據(jù)時(shí)間序列資料，逐項(xiàng)推移，依次計(jì)算移動(dòng)平均，來(lái)反映現(xiàn)象的長(zhǎng)期趨勢(shì)。特別是現(xiàn)象的變量值受周期變動(dòng)和不規(guī)則變動(dòng)的影響，起伏較大，不能明顯地反映現(xiàn)象的變動(dòng)趨勢(shì)時(shí)，運(yùn)用移動(dòng)平均法，消除這些因素的影響，進(jìn)行動(dòng)態(tài)數(shù)據(jù)的修勻，以利于進(jìn)行長(zhǎng)期趨勢(shì)的分析和預(yù)測(cè)。

7.4移動(dòng)平均 89簡(jiǎn)單移動(dòng)平均的計(jì)算公式：設(shè)xi為時(shí)間序列中的某時(shí)間點(diǎn)的觀測(cè)值，其樣本數(shù)為N；每次移動(dòng)地求算術(shù)平均值所采用的觀測(cè)值的個(gè)數(shù)為n（n的取值范圍：2<n<t-1），則在第t時(shí)間點(diǎn)的移動(dòng)平均值Mi式中：Mi——第t時(shí)間點(diǎn)的移動(dòng)平均值，也可當(dāng)作第t+1時(shí)間點(diǎn)的預(yù)測(cè)值，即：yi+1=Mi或yi=Mi-1移動(dòng)平均分析工具及其公式可以基于特定的過去某段時(shí)期中變量的均值，對(duì)未來(lái)值進(jìn)行預(yù)測(cè)簡(jiǎn)單移動(dòng)平均的計(jì)算公式：式中：Mi——第t時(shí)間點(diǎn)的移動(dòng)平均值90[例1]某公司1994至2005年銷售額數(shù)據(jù)如圖所示。進(jìn)行三年移動(dòng)平均，并預(yù)測(cè)2006年銷售額。操作步驟：

（1）建立模型：將原始數(shù)據(jù)輸入到單元格區(qū)域A1:B13，如圖7.29所示；圖7.29建立數(shù)據(jù)模型[例1]某公司1994至2005年銷售額數(shù)據(jù)如圖所示。進(jìn)行91（2）單擊數(shù)據(jù)選項(xiàng)卡→數(shù)據(jù)分析，彈出“數(shù)據(jù)分析”對(duì)話框；設(shè)置“移動(dòng)平均”對(duì)話框，確定。圖7.30“移動(dòng)平均”對(duì)話框（2）單擊數(shù)據(jù)選項(xiàng)卡→數(shù)據(jù)分析，彈出“數(shù)據(jù)分析”對(duì)話框；圖92結(jié)果如圖。單元格區(qū)域D5:D12即為標(biāo)準(zhǔn)誤差。根據(jù)公式y(tǒng)i+1=Mi得2006年的銷售額預(yù)測(cè)應(yīng)為M2005，所以為640。7.31“移動(dòng)平均”的分析結(jié)果結(jié)果如圖。單元格區(qū)域D5:D12即為標(biāo)準(zhǔn)誤差。根據(jù)公式y(tǒng)i+937.5指數(shù)平滑指數(shù)平滑是在移動(dòng)平均的基礎(chǔ)上的進(jìn)一步擴(kuò)展。指數(shù)平滑法是用過去時(shí)間數(shù)列值的加權(quán)平均數(shù)作為趨勢(shì)值，越靠近當(dāng)前時(shí)間的指標(biāo)越具有參考價(jià)值，因此給予更大的權(quán)重，按照這種隨時(shí)間指數(shù)衰減的規(guī)律對(duì)原始數(shù)據(jù)進(jìn)行加權(quán)修勻。所以它是加權(quán)移動(dòng)平均法的一種特殊情形。其基本形式是根據(jù)本期的實(shí)際值Yt和本期的趨勢(shì)值，分別給以不同權(quán)數(shù)α和1–α，計(jì)算加權(quán)平均數(shù)作為下期的趨勢(shì)值。7.5指數(shù)平滑94[例1]利用上題例題的數(shù)據(jù)，用Excel進(jìn)行單指數(shù)平滑進(jìn)行分析。操作步驟：建立數(shù)據(jù)模型；單擊數(shù)據(jù)選項(xiàng)卡→數(shù)據(jù)分析→指數(shù)平滑，“確定”，顯示“指數(shù)平滑”對(duì)話框；設(shè)置“指數(shù)平滑”對(duì)話框圖7.32“指數(shù)平滑”對(duì)話框[例1]利用上題例題的數(shù)據(jù)，用Excel進(jìn)行單指數(shù)平滑進(jìn)行分95商務(wù)數(shù)據(jù)處理與分析第7章-商務(wù)數(shù)據(jù)分析課件96結(jié)果：結(jié)果：977.6相關(guān)分析相關(guān)關(guān)系是指變量之間存在的不完全確定性的關(guān)系。在實(shí)際問題中，許多變量之間的關(guān)系并不是完全確定性的，例如居民家庭消費(fèi)與居民家庭收入這兩個(gè)變量的關(guān)系就不是完全確定的。收入水平相同的家庭，它們的消費(fèi)額往往不同；消費(fèi)額相同的家庭，它們的收入也可能不同。對(duì)現(xiàn)象之間相關(guān)關(guān)系密切程度的研究，稱為相關(guān)分析。對(duì)兩個(gè)變量之間線性相關(guān)程度的度量稱為簡(jiǎn)單相關(guān)系數(shù)---r。7.6相關(guān)分析98簡(jiǎn)單相關(guān)系數(shù)又稱皮爾遜相關(guān)系數(shù)，它描述了兩個(gè)定距變量間聯(lián)系的緊密程度。樣本的簡(jiǎn)單相關(guān)系數(shù)一般用r表示。R的性質(zhì)：

r>0為正相關(guān)，r<0為負(fù)相關(guān)。R=1表明兩個(gè)變量完全線性相關(guān)；

r=0表明兩個(gè)變量完全不線性相關(guān)，但兩個(gè)變量之間有可能存在非線性相關(guān)。

相關(guān)程度劃分:

當(dāng)r>=0.8，高度相關(guān)；

0.5<=r<0.8，視為中度相關(guān)；

0.3<=r<0.5，視為低度相關(guān)；

|r|<0.3時(shí)，視為不相關(guān)。簡(jiǎn)單相關(guān)系數(shù)又稱皮爾遜相關(guān)系數(shù)，它描述了兩個(gè)定距變量間聯(lián)系的99[例]根據(jù)下圖的數(shù)據(jù)，對(duì)家庭月消費(fèi)支出與家庭月收入的數(shù)據(jù)進(jìn)行相關(guān)分析。[例]根據(jù)下圖的數(shù)據(jù)，對(duì)家庭月消費(fèi)支出與家庭月收入的數(shù)據(jù)進(jìn)行100操作步驟：建立數(shù)據(jù)模型；工具→數(shù)據(jù)分析/相關(guān)系數(shù)設(shè)置“相關(guān)系數(shù)”對(duì)話框內(nèi)容操作步驟：101“確定”，輸出結(jié)果如圖所示。分析結(jié)果表明：相關(guān)系數(shù)r=0.979747601，表示家庭月消費(fèi)支出與家庭月收入之間存在高度正相關(guān)關(guān)系?！按_定”，輸出結(jié)果如圖所示。分析結(jié)果表明：相關(guān)系數(shù)r=0.9102分析結(jié)果表明：相關(guān)系數(shù)r=0.979747601，表示家庭月消費(fèi)支出與家庭月收入之間存在高度正相關(guān)關(guān)系。操作步驟:（1）建立數(shù)據(jù)模型：將數(shù)據(jù)輸入到工作表區(qū)域A1:E16中，如圖8.38所示；分析結(jié)果表明：相關(guān)系數(shù)r=0.979747601，表示家庭月1037.7方差分析方差分析(AnalysisofVariance,縮寫為ANOVA)是數(shù)理統(tǒng)計(jì)學(xué)中常用的數(shù)據(jù)處理方法之一，是經(jīng)濟(jì)和科學(xué)研究中分析試驗(yàn)數(shù)據(jù)的一