Excel數(shù)據(jù)處理與分析 課件 項(xiàng)目5 功能強(qiáng)悍的數(shù)據(jù)分析工具

項(xiàng)目5

應(yīng)有盡有的數(shù)據(jù)

分析工具5．1項(xiàng)目展示企業(yè)廣告費(fèi)用與銷售額關(guān)系的回歸分析

某企業(yè)希望確定其廣告費(fèi)x與銷售額y之間的關(guān)系，以制定本企業(yè)下一年的營銷計(jì)劃。本節(jié)將通過企業(yè)的廣告費(fèi)用與銷售額的歷史數(shù)據(jù)，來進(jìn)行回歸分析，如右圖所示：5．1項(xiàng)目展示企業(yè)廣告費(fèi)用與銷售額關(guān)系的回歸分析

進(jìn)行一系列操作后，得出的回歸分析結(jié)果，如下圖所示，并由此結(jié)果可以得出回歸方程是y=ax+b。其中a=8.068297,b=433.9947，也即是y=8.068297x+433.9947。并由回歸方程和廣告費(fèi)用x的值，即可預(yù)測出銷售額y的值。5.2項(xiàng)目制作任務(wù)一：

散點(diǎn)圖判斷變量關(guān)系（簡單線性）任務(wù)二：求回歸系數(shù)，建立回歸方程任務(wù)三：回歸方程的分析檢驗(yàn)任務(wù)四：預(yù)測任務(wù)一

（1）在企業(yè)歷史數(shù)據(jù)表中，選中B2:C19數(shù)據(jù)區(qū)域。（2）在【插入】選項(xiàng)卡【圖表】功能區(qū)，單擊【散點(diǎn)圖】的第一個圖標(biāo)類型，即可繪制銷售額與廣告費(fèi)用這兩者之間關(guān)系的散點(diǎn)圖，如下圖所示：從散點(diǎn)圖中可以看出，自變量和因變量之間基本上呈線性相關(guān)關(guān)系。任務(wù)一

（3）在散點(diǎn)圖中選中數(shù)據(jù)系列，單擊鼠標(biāo)右鍵，在快捷菜單中選擇【添加趨勢線】命令，如下圖所示。任務(wù)一

（4）在彈出的【設(shè)置趨勢線格式】對話框中，選擇【趨勢線選項(xiàng)】單選框中的【線性】，此處為默認(rèn)選項(xiàng)。拉滾動條至最下方，勾選【顯示公式】和【顯示R平方值】復(fù)選框，如下圖所示。任務(wù)一

（5）單擊【設(shè)置趨勢線】右上角的【關(guān)閉】按鈕，完成添加趨勢線。如下圖所示：任務(wù)二

從趨勢圖中可以看出，擬合的線性回歸方程為：y=8.0683x+433.99,也即是a=8.0683,b=433.99。R的平方值為0.9541，非常接近于1，說明擬合的效果很好。

這只是建立回歸模型的簡單方法，最終確認(rèn)的模型還需要經(jīng)過參數(shù)檢驗(yàn)，所以進(jìn)行完整的建模還是需要借助【數(shù)據(jù)分析】工具中的【回歸】分析來完成。任務(wù)三（1）在【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)中，單擊【數(shù)據(jù)分析】命令，彈出“數(shù)據(jù)分析”對話框，在對話框中選擇“回歸”。單擊【確定】按鈕，彈出“回歸”對話框。對“回歸”對話框中的各參數(shù)設(shè)置說明如下：“Y值輸入?yún)^(qū)域”：選擇因變量數(shù)據(jù)所在的區(qū)域，可以包含標(biāo)志，本例中選擇C1:C19。任務(wù)三“X值輸入?yún)^(qū)域”：選擇自變量取值數(shù)據(jù)所在的區(qū)域。如果選擇數(shù)據(jù)時包含了標(biāo)志則選擇“標(biāo)志”復(fù)選框，本例中選擇B1:B19。如果強(qiáng)制擬合線通過坐標(biāo)系遠(yuǎn)點(diǎn)則選擇“常數(shù)為零”復(fù)選框，本例中不要求回歸模型常數(shù)項(xiàng)為零，不勾選。任務(wù)三“置信度”：分析置信度，一般選擇95%。“輸出”選項(xiàng)：根據(jù)需要選擇分析結(jié)果輸出的位置，本例中選擇D2單元格作為輸出區(qū)域?！皻埐睢边x項(xiàng)：根據(jù)需要可選擇分析結(jié)果中包含殘差、標(biāo)準(zhǔn)殘差及殘差圖、線性擬合圖。如果希望輸出“正態(tài)概率圖”，則選擇相應(yīng)的復(fù)選框。任務(wù)三（2）按照如上所說進(jìn)行設(shè)置，如下圖所示：任務(wù)三（3）按【確定】按鈕，得到分析結(jié)果，如下圖所示。任務(wù)三從【回歸統(tǒng)計(jì)】中可以看出，自變量和因變量的相關(guān)系數(shù)絕對值大于0.97，說明廣告費(fèi)用和銷售額呈高度正相關(guān)。擬合度R的平方值大于0.95，非常接近1，說明模型的擬合效果很好。從【方差分析】中可以看出，F(xiàn)統(tǒng)計(jì)量的值為333.2387，對應(yīng)的SignificanceF的值遠(yuǎn)小于0.01，說明模型具有非常顯著的統(tǒng)計(jì)學(xué)意義。從回歸模型區(qū)域可以看出回歸系數(shù)的P-value值小于0.05，說明模型通過了顯著性檢驗(yàn)，最終得到的回歸方程為：y=8.068x+433.99。任務(wù)四

應(yīng)用此回歸模型進(jìn)行預(yù)測：如果該企業(yè)將在2018年投入廣告費(fèi)用為200萬元，則2018年的銷售額將達(dá)到2047.59萬元。5.3知識點(diǎn)擊模擬運(yùn)算表單變量求解方案分析規(guī)劃求解審核與跟蹤數(shù)據(jù)分析工具庫5．3．1模擬運(yùn)算表Excel中的模擬運(yùn)算表是一種適合用于進(jìn)行假設(shè)分析的工具。在一個工作表中，對一個單元格區(qū)域的數(shù)據(jù)進(jìn)行模擬運(yùn)算，測試在使用公式時涉及到的一個或兩個變量的取值變化對公式運(yùn)算結(jié)果的影響。在Excel中可以構(gòu)造兩種類型的模擬運(yùn)算表，即：單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表。5．3．1模擬運(yùn)算表一、單變量模擬運(yùn)算表單變量模擬運(yùn)算表是測試一個輸入變量的變化對一個或多個公式的影響。例如，在申請購房貸款時，需要考慮貸款的總額、貸款期限、利率的變化、月還款額的承受能力和利息總額等。5．3．1模擬運(yùn)算表

由于國家的利率時有變動、在貸款時需要全面考量利率變動對償還能力的影響。利用Excel的PMT函數(shù)可以直接計(jì)算出每月的償還額度，利用單變量模擬運(yùn)算表能夠直觀、便捷地以表格形式將不同利率下相對應(yīng)的月償還額做對比，方便購房家庭做參考。案例【例5-1】某個家庭想向銀行貸款60萬元用于購房，還款期限15年，目前的年利率是5.35%，使用“模擬運(yùn)算表”模擬計(jì)算不同的利率對月還款額的影響。操作步驟（1）在工作表中輸入貸款參數(shù)，選定單元格B4，如下圖所示：操作步驟：（2）在【公式】選項(xiàng)卡【函數(shù)庫】功能區(qū)中，單擊【插入函數(shù)】命令，在彈出的“插入函數(shù)”對話框中，選擇類別：財(cái)務(wù)，在此類別中選擇：PMT函數(shù)，如下圖所示，單擊【確定】按鈕。操作步驟：（3）在彈出的“函數(shù)參數(shù)”對話框中輸入?yún)?shù)，如下圖所示，單擊【確定】按鈕。操作步驟計(jì)算結(jié)果如下圖所示。即該家庭在貸款總額為60萬，年利率為5.35%，貸款期限為15年的情況下，需要每月還款4545.10元。操作步驟（4）設(shè)計(jì)模擬運(yùn)算表的存放區(qū)域，輸入模擬數(shù)據(jù)和計(jì)算公式。選擇存放區(qū)域?yàn)锳8：B15，在單元格區(qū)域A9：A15中輸入不同的利率；在單元格B8中輸入計(jì)算月還款額的函數(shù)：=PMT（B2/12，B3*12，B1）；然后選定整個模擬運(yùn)算表區(qū)域A8：B15，如右圖所示：操作步驟（5）在【數(shù)據(jù)】選項(xiàng)卡【數(shù)據(jù)工具】功能區(qū)中，單擊【模擬分析】命令，選擇“模擬運(yùn)算表”項(xiàng)，如下圖所示：操作步驟在彈出的“模擬運(yùn)算表”對話框中輸入$B$2（本例引用的是列數(shù)據(jù)：貸款年利率），如下圖所示：操作步驟（6）單擊【確定】按鈕。模擬運(yùn)算表的單變量“貸款年利率”的計(jì)算結(jié)果，如右圖所示：5．3．1模擬運(yùn)算表二、雙變量模擬運(yùn)算表雙變量模擬運(yùn)算表是測試兩個輸入變量的變化對一個或多個公式的影響。它與單變量模擬運(yùn)算表的不同在于雙變量模擬運(yùn)算表使用兩個輸入單元格，兩組輸入數(shù)值使用的是同一個公式，這個公式必須引用兩個不同的輸入單元格。如上例中，購房家庭要考慮年利率的變化和不同的貸款年限對月還款額的影響兩個因素。這種情況下，就要使用雙變量模擬運(yùn)算表來進(jìn)行計(jì)算對比。案例【例5-2】某個家庭想向銀行貸款60萬元用于購房，年利率在3.68%～5.65%之間變化，在各種年利率下，當(dāng)還款期限在5~30年之間變化時，計(jì)算每月等額的還款金額。操作步驟（1）選定A8：G15作為模擬運(yùn)算表的存放區(qū)域。（2）在單元格區(qū)域A9：A15中輸入不同的年利率數(shù)據(jù)；在單元格區(qū)域B8：G8中輸入不同的貸款年限數(shù)據(jù)。操作步驟（3）在單元格A8內(nèi)輸入函數(shù)：=PMT（B2/12,B3*12,B1），然后選定整個模擬運(yùn)算表區(qū)域A8:G15，如下圖所示。操作步驟（4）在【數(shù)據(jù)】選項(xiàng)卡【數(shù)據(jù)工具】功能區(qū)中，單擊【模擬分析】命令，選擇“模擬運(yùn)算表”項(xiàng)，在彈出的“模擬運(yùn)算表”對話框中輸入?yún)?shù)（本例中引用的行數(shù)據(jù)是：貸款年限，引用的列數(shù)據(jù)是：年利率），如下圖所示：操作步驟（5）單擊【確定】按鈕，顯示模擬運(yùn)算表的雙變量“貸款期限”和“貸款年利率”的計(jì)算結(jié)果，如下圖所示：5．3．1模擬運(yùn)算表三、修改模擬運(yùn)算表

當(dāng)創(chuàng)建了單變量或雙變量模擬運(yùn)算表后，還可以根據(jù)需要對模擬運(yùn)算表做各種修改。

1．修改模擬運(yùn)算表計(jì)算公式

當(dāng)計(jì)算公式發(fā)生變化時，模擬運(yùn)算表將重新計(jì)算，并在相應(yīng)的單元格中顯示出新的運(yùn)算結(jié)果。

2．修改用于模擬運(yùn)算的數(shù)值序列

當(dāng)這些數(shù)值序列的內(nèi)容被修改后，模擬運(yùn)算表將會重新計(jì)算，并在相應(yīng)的單元格顯示出新的計(jì)算結(jié)果。5．3．1模擬運(yùn)算表3．修改輸入單元格

選定整個模擬運(yùn)算表，在【數(shù)據(jù)】選項(xiàng)卡【數(shù)據(jù)工具】功能區(qū)中，單擊【模擬分析】命令中的“模擬運(yùn)算表”項(xiàng)，在彈出的“模擬運(yùn)算表”對話框中指定新的輸入單元格。不能直接清除部分模擬運(yùn)算表的計(jì)算結(jié)果，如果用戶想要僅僅刪除模擬運(yùn)算表的部分計(jì)算結(jié)果，就會出現(xiàn)如下圖所示的消息框，提示用戶不能進(jìn)行這樣的操作。5．3．1模擬運(yùn)算表

由于模擬運(yùn)算表中的計(jì)算結(jié)果是存放在數(shù)組中的，所以當(dāng)需要清除模擬運(yùn)算表的計(jì)算結(jié)果時，必須清除所有的計(jì)算結(jié)果才可以。

如果要刪除模擬運(yùn)算表的運(yùn)算結(jié)果，則只需選定整個運(yùn)算結(jié)果區(qū)域，然后按【del】鍵即可；或在【開始】選項(xiàng)卡【編輯】功能區(qū)中，單擊【清除】命令中“全部清除”項(xiàng)即可。5．3．2單變量求解

利用Excel的單變量求解功能可以在給定公式的前提下，通過調(diào)整可變單元格中的數(shù)值來謀求目標(biāo)單元格中的目標(biāo)值。Excel的單變量求解功能相當(dāng)于公式的逆運(yùn)算。公式是輸入數(shù)值，通過公式計(jì)算得到結(jié)果。單變量求解：輸入結(jié)果，通過該功能求出某個變量。下面這個例子是典型的單變量求解問題。案例

【例5-3】某商場營業(yè)利潤的計(jì)算方法是：營業(yè)額x28%=營業(yè)利潤，同時營業(yè)利潤中的15%用于發(fā)放員工工資，營業(yè)純利潤=營業(yè)利潤-員工工資。假如該商場希望在2018年賺取100000元，計(jì)算一年的最少營業(yè)額。操作步驟（1）在工作表中輸入營業(yè)額參數(shù)，在B2單元格中輸入公式“=B1*0.28*0.15”，在B3單元格中輸入公式“=B1*0.28-B2”，如下圖所示：操作步驟（2）選定工作表中的目標(biāo)單元格B3，在【數(shù)據(jù)】選項(xiàng)卡【數(shù)據(jù)工具】功能區(qū)中，單擊【模擬分析】命令中“單變量求解”項(xiàng)，彈出“單變量求解”對話框。操作步驟（3）“單變量求解”對話框中，在“目標(biāo)單元格”編輯框中已經(jīng)自動引用了所選定的目標(biāo)單元格B3，在“目標(biāo)值”編輯框中輸入目標(biāo)營業(yè)純利值100000，然后在“可變單元格”編輯框輸入單元格B1的引用$B$1。如下圖所示。操作步驟（4）單擊【確定】按鈕，彈出“單變量求解狀態(tài)”對話框，如下圖所示，經(jīng)過執(zhí)行后，求解的答案顯示在“可變單元格”對話框指定的單元格B3內(nèi)。

根據(jù)運(yùn)算結(jié)果，如果該商場在2018年賺取100000元的純利潤，則需要至少有420168元的營業(yè)額。5．3．3

方案分析

在企業(yè)的生產(chǎn)銷售過程中，由于人力成本、運(yùn)輸成本、市場價(jià)格等因素的不斷變化，企業(yè)的利潤受到影響。企業(yè)需要估計(jì)并分析這些因素對其生產(chǎn)銷售的影響。Excel中提供了稱為“方案管理器”的工具來解決上述問題。利用方案管理器，可以方便地對多個變化因素，形成對應(yīng)的多種方案來進(jìn)行模擬分析。案例

【例5-4】某個摩托車生產(chǎn)企業(yè)，生產(chǎn)摩托車的原料成本=2000元，固定費(fèi)用=200元，利潤=銷售單價(jià)-原料成本-固定費(fèi)用x（1+推銷費(fèi)率）-人力成本-運(yùn)輸成本，總利潤=利潤x銷售數(shù)量?，F(xiàn)以2017年的生產(chǎn)銷售情況為數(shù)據(jù)基礎(chǔ)，使用方案管理器，對2018年的利潤進(jìn)行評估分析。一、建立方案（1）在工作表中輸入有關(guān)利潤的各項(xiàng)參數(shù)，在單元格區(qū)域“B6：B10”中輸入要進(jìn)行模擬的5個變量，分別是銷售單價(jià)、銷售數(shù)量、推銷費(fèi)率、人力成本、運(yùn)輸成本。在B3單元格輸入公式“=B6-B1-B2*(1+B8)-B9-B10”，在B4單元格輸入公式“=B3*B7”，如下圖所示。操作步驟操作步驟（2）在【數(shù)據(jù)】選項(xiàng)卡【數(shù)據(jù)工具】功能區(qū)中，單擊【模擬分析】命令，選擇“方案管理器”項(xiàng)，彈出“方案管理器”對話框，如右圖所示：操作步驟（3）單擊【添加】按鈕，彈出“編輯方案”對話框，在“方案名”文本框中輸入當(dāng)前方案名稱，在“可變單元格”文本框中輸入可變單元格地址，這里以銷售單價(jià)、銷售數(shù)量、推銷費(fèi)率、人力成本和運(yùn)輸成本值作為預(yù)測時的可變值。如下圖所示：操作步驟（4）單擊【確定】按鈕，彈出“方案變量值”對話框。輸入每個可變單元格的值，在輸入過程中要使用【Tab】鍵在各輸入框之間進(jìn)行切換，如右圖所示。操作步驟（5）輸入完畢，單擊【確定】按鈕，將方案添加到序列中。如果需要再建立其他方案，可以選擇【添加】按鈕，重新進(jìn)入“編輯方案”對話框，進(jìn)行另一個方案的編輯。當(dāng)輸入完所有方案后，單擊【確定】按鈕，就會看到已設(shè)置了多種方案的對話框。如右圖所示：（6）單擊【關(guān)閉】按鈕，完成該項(xiàng)工作。操作步驟二、建立方案

在“方案管理器”中的“方案”列表框中，選擇某個方案選項(xiàng)，單擊【顯示】按鈕即可顯示該方案的結(jié)果。本例在工作表中將顯示當(dāng)前方案的5個變量的值，并顯示在該方案下，企業(yè)所能獲得的利潤及總利潤值。如下圖所示。操作步驟三、修改、刪除方案修改已經(jīng)做好的方案，需要在上圖所示的“方案管理器”對話框中選擇該方案，單擊【編輯】按鈕，在彈出的“編輯方案”對話框中進(jìn)行相應(yīng)的修改即可。需要刪除某一方案，在“方案管理器”對話框中單擊【刪除】按鈕即可。操作步驟四、建立方案摘要（1）在“方案管理器”中單擊【摘要】按鈕，將打開“方案摘要”對話框，選擇創(chuàng)建摘要報(bào)表的類型，如這里選擇默認(rèn)的“方案摘要”單選框，在“結(jié)果單元格”編輯框中輸入要顯示的結(jié)果單元格B3和B4（用逗號隔開），如右圖所示：操作步驟（2）完成設(shè)置后單擊【確定】按鈕，關(guān)閉“方案摘要”對話框。此時工作簿中將創(chuàng)建一個名為“方案摘要”的工作表，如下圖所示。5．3．4

規(guī)劃求解

在實(shí)際經(jīng)濟(jì)管理工作中，我們常常會遇到很多的優(yōu)化問題，例如，在企業(yè)的生產(chǎn)經(jīng)營中，管理者常常會思考這樣的問題：如何能夠合理地利用有限的人力、物力、財(cái)力等資源，達(dá)到最大利潤、最小成本、資源消耗最少等目標(biāo)。

這些在運(yùn)籌學(xué)上稱為最優(yōu)化原則問題，通常會涉及眾多的關(guān)聯(lián)因素，前面介紹的模擬運(yùn)算表、單變量求解、方案分析等方法都具有一定的局限性，Excel的“規(guī)劃求解”工具可以很好地解決由眾多因素交織的復(fù)雜問題。5．3．4

規(guī)劃求解

利用“規(guī)劃求解”工具，可以避開難懂的理論和計(jì)算細(xì)節(jié)，方便快捷地計(jì)算出各種規(guī)劃問題的最佳解。

“規(guī)劃求解”是對與目標(biāo)單元格中公式相關(guān)聯(lián)的一組單元格中的數(shù)值進(jìn)行調(diào)整，最終在目標(biāo)單元格中計(jì)算出期望的結(jié)果。在Excel中，一個規(guī)劃求解問題由以下3個部分組成：可變單元格、目標(biāo)函數(shù)、約束條件。

可變單元格：是實(shí)際問題中有待解決的未知因素，一個規(guī)劃問題中可能有一個變量，也可能有多個變量。在Excel的規(guī)劃求解中，可以有一個可變單元格，也可能有一組?？勺儐卧褚卜Q為決策變量，一組決策變量代表一個規(guī)劃求解方案。5．3．4

規(guī)劃求解

目標(biāo)函數(shù)：表示規(guī)劃求解要達(dá)到的最終目標(biāo)。一般來說，目標(biāo)函數(shù)是規(guī)劃模型中可變量的函數(shù)。目標(biāo)函數(shù)是規(guī)劃求解的關(guān)鍵，可以是線性函數(shù)，也可以是非線性函數(shù)。

約束條件：是實(shí)現(xiàn)目標(biāo)的限制條件，與規(guī)劃求解的結(jié)果有著密切的關(guān)系，對可變單元格中的值起著直接的限制作用，可以是等式，也可以是不等式。5．3．4

規(guī)劃求解

在Excel2013中，當(dāng)加載了分析工具后，“數(shù)據(jù)”選項(xiàng)卡中會顯示“分析”功能組。如下圖所示：5．3．4

規(guī)劃求解

在進(jìn)行規(guī)劃求解時，首先需將實(shí)際問題數(shù)學(xué)化、模型化，即將實(shí)際問題轉(zhuǎn)化為一組決策變量、一組用等式或不等式表示的約束條件，進(jìn)而建立數(shù)學(xué)模型，構(gòu)成目標(biāo)函數(shù)表示出來，然后應(yīng)用Excel的規(guī)劃求解工具求解。案例

【例5-5】現(xiàn)有某企業(yè)生產(chǎn)A、B、C三種產(chǎn)品，每生產(chǎn)一個產(chǎn)品A、B、C分別可以賺50元、70元和85元。生產(chǎn)一個產(chǎn)品A需要2小時機(jī)時，耗費(fèi)原材料3千克；生產(chǎn)一個產(chǎn)品B需要3.5小時機(jī)時，耗費(fèi)原料2.5千克，生產(chǎn)一個產(chǎn)品C需要4小時機(jī)時，耗費(fèi)原料2千克?，F(xiàn)在每個月能得到的原料為600千克，每個月能分配的機(jī)時為650小時。該公司每個月應(yīng)該如何分配三種產(chǎn)品的生產(chǎn)，才能賺取最大的利潤。案例

這個問題是一個典型的非線性規(guī)劃求解問題。先將其模型化，即根據(jù)實(shí)際問題確定決策變量，設(shè)置約束條件，構(gòu)造目標(biāo)函數(shù)。一、建立規(guī)劃模型（1）決策變量本例中的決策變量為三種產(chǎn)品的生產(chǎn)量，設(shè)A、B、C三種產(chǎn)品每個月的生產(chǎn)量分別為x1、x2、x3。案例（2）約束條件生產(chǎn)工時的約束條件：2*x1+3.5*x2+4*x3≦650生產(chǎn)原料的約束條件：3*x1+2.5*x2+2*x3≦600（3）確定目標(biāo)函數(shù)該問題的目標(biāo)是三種產(chǎn)品的利潤加在一起最大，設(shè)利潤為y，則y=50*x1+70*x2+85*x3，即求y的最大值問題。二、輸入規(guī)劃模型建立好規(guī)劃模型后，接著就是把規(guī)劃模型的有關(guān)數(shù)據(jù)輸入到工作表中。操作步驟（1）在表格中輸入與利潤有關(guān)的參數(shù)。如下圖所示：操作步驟（2）建立有關(guān)計(jì)算公式。在B9、B10、B11中輸入對應(yīng)的公式，B9中輸入“=C3*E3+C4*E4+C5*E5”，在B10中輸入“=D3*E3+D4*E4+D5*E5”，B11中輸入“=B3*E3+B4*E4+B5*E5”。如下圖所示。操作步驟三、規(guī)劃求解創(chuàng)建好規(guī)劃求解模型后，接下來正式開始求解，（1）在【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)中，單擊“規(guī)劃求解”按鈕，彈出“規(guī)劃求解參數(shù)”對話框。（2）設(shè)置目標(biāo)函數(shù)。在“設(shè)置目標(biāo)”單元格中輸入$B$11,并選擇“最大值”單選框。操作步驟（3）設(shè)置決策變量。指定“通過更改可變單元格”為決策變量所在的單元格區(qū)域。輸入$E$3:$E$5。如右圖所示：操作步驟（4）添加生產(chǎn)約束條件。即對生產(chǎn)機(jī)時數(shù)和消耗原料數(shù)的限制。單擊【添加】按鈕，分別將E3、E4、E5設(shè)置為整數(shù)，如下圖1所示；再分別設(shè)置B9≤B7，B10≤B8，如下圖2所示：

圖1添加約束條件為整數(shù)

圖2添加約束條件范圍操作步驟（5）單擊【確定】按鈕后，如右下圖所示：

操作步驟（6）約束條件設(shè)置完成后，單擊【求解】按鈕，彈出“規(guī)劃求解結(jié)果”對話框。如右圖所示：

操作步驟（7）在“規(guī)劃求解結(jié)果”對話框，單擊【確定】按鈕，即可計(jì)算出最優(yōu)解。如下圖所示：

操作步驟四、生成規(guī)劃求解的結(jié)果報(bào)告規(guī)劃求解不但可以在源工作表中保存求解結(jié)果，還可以將求解的結(jié)果制作成報(bào)告，更加直觀。如果在“規(guī)劃求解結(jié)果”對話框中，先單擊“運(yùn)算結(jié)果報(bào)告”項(xiàng)，然后再單擊【確定】按鈕，系統(tǒng)會自動在當(dāng)前工作薄中插入一個“運(yùn)算結(jié)果報(bào)告1”工作表，在該工作表中顯示運(yùn)算結(jié)果報(bào)告。在該報(bào)告中可以看到目標(biāo)單元格的最優(yōu)值，可變單元格的取值以及約束條件情況。如下圖所示。至此，一個完整的規(guī)劃求解過程完成。

5．3．5數(shù)據(jù)分析工具庫“分析工具庫”是一個外部宏（程序）模塊，它專門為用戶提供一些高級統(tǒng)計(jì)函數(shù)和實(shí)用的數(shù)據(jù)分析工具。利用數(shù)據(jù)分析工具庫，能夠構(gòu)造反映數(shù)據(jù)分布的直方圖；能夠從數(shù)據(jù)集合中隨機(jī)抽樣，獲得樣本的統(tǒng)計(jì)測度；能夠進(jìn)行時間數(shù)列分析和回歸分析；能夠?qū)?shù)據(jù)進(jìn)行傅立葉變換和其他變換等。分析工具庫內(nèi)置了19個模塊，可以分為以下幾大類,如下表所示：

分類工具模塊抽樣設(shè)計(jì)隨機(jī)數(shù)發(fā)生器抽樣數(shù)據(jù)整理直方圖參數(shù)估計(jì)描述統(tǒng)計(jì)排位與百分比排位假設(shè)檢驗(yàn)z-檢驗(yàn)：雙樣本均值差檢驗(yàn)t-檢驗(yàn)：平均值的成對二樣本分析t-檢驗(yàn)：雙樣本等方差假設(shè)t-檢驗(yàn)：雙樣本異方差假設(shè)F檢驗(yàn)：雙樣本方差檢驗(yàn)方差分析方差分析：單因素方差分析方差分析：無重復(fù)雙因素方差分析方差分析：可重復(fù)雙因素方差分析相關(guān)與回歸分析相關(guān)系數(shù)協(xié)方差回歸時間序列預(yù)測移動平均指數(shù)平滑傅利葉分析5．3．5數(shù)據(jù)分析工具庫一、加載“分析工具庫”（1）在【文件】選項(xiàng)卡【選項(xiàng)】功能區(qū)中，單擊“加載項(xiàng)”選項(xiàng)。（2）在“加載項(xiàng)”框中，選中“分析工具庫”復(fù)選框，如下圖所示：5．3．5數(shù)據(jù)分析工具庫（3）單擊【轉(zhuǎn)到】按鈕，出現(xiàn)“加載宏”對話框，如右圖所示：

5．3．5數(shù)據(jù)分析工具庫（4）在“可用加載宏”復(fù)選框中，選擇“分析工具庫”，單擊【確定】按鈕，完成“加載數(shù)據(jù)分析”功能。注意:

若要包括VisualBasic的應(yīng)用程序(VBA)函數(shù)，用于分析工具庫，可以在分析工具庫-VBA加載項(xiàng)加載，與加載分析工具庫的方式相同。在可用加載宏框中，選擇分析工具庫-VBA復(fù)選框。

5．3．5數(shù)據(jù)分析工具庫二、常用分析工具1.方差分析工具“方差分析”是一種統(tǒng)計(jì)檢驗(yàn)，用以判斷兩個或者更多的樣品是否是從同樣的總體中抽取的。使用分析工具庫中的方差分析工具，可以執(zhí)行三種類型的方差分析。（1）單因素：單向方差分析，每組數(shù)據(jù)只有一個樣品。（2）可重復(fù)雙因素：雙向方差分析，每組數(shù)據(jù)有多個樣品。（3）無重復(fù)雙因素：雙向方差分析，每組數(shù)據(jù)有一個樣品。下面舉例說明單因素方差工具的使用。

案例【例5-6】設(shè)有三個車間，以不同的工藝生產(chǎn)同一種產(chǎn)品。為考察不同工藝對產(chǎn)品的產(chǎn)量是否有顯著的影響，今對每個車間各記錄5天的日產(chǎn)量。分析不同的工藝對各車間的產(chǎn)量有無顯著性差異。操作步驟（1）打開一個Excel工作表，將樣本觀測值輸入到單元格A1:C5。如下圖所示：操作步驟（2）單擊【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)，單擊【數(shù)據(jù)分析】命令，在彈出的“數(shù)據(jù)分析”對話框中選擇“方差分析：單因素方差分析”。如下圖所示：

操作步驟（3）單擊【確定】按鈕，彈出“單因素方差分析”對話框。其中，“變量的范圍”框內(nèi)輸入A1：C5；選中“標(biāo)志行位于第一列”單選框；α代表檢驗(yàn)的統(tǒng)計(jì)置信水平，在“α（A）”內(nèi)填臨界值為0.05；選中輸出區(qū)域，這里填寫A10，表示輸出結(jié)果將放置于A10右下方的單元格中。如下圖所示:

操作步驟（4）單擊【確定】按鈕，彈出方差分析結(jié)果。如下圖5所示:

5．3．5數(shù)據(jù)分析工具庫2.直方圖工具直方圖工具對制作數(shù)據(jù)分布和直方圖表非常有用，它能夠快速統(tǒng)計(jì)出如某學(xué)校某次考試的分?jǐn)?shù)在各個區(qū)間內(nèi)的人數(shù)等。

案例【例5-7】某公司有員工99人，利用直方圖快速統(tǒng)計(jì)出該公司員工工資在各個區(qū)間的人數(shù)。

操作步驟（1）創(chuàng)建員工工資數(shù)據(jù)表，在A2:E100區(qū)域輸入相應(yīng)信息。如下圖所示:

操作步驟（2）在G2:G7區(qū)域輸入分段點(diǎn)數(shù)值。單擊【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)，單擊【數(shù)據(jù)分析】命令，彈出“數(shù)據(jù)分析”對話框，在“分析工具”框中，選擇“直方圖”選項(xiàng)。（3）單擊【確定】按鈕，彈出“直方圖”對話框。在“輸入?yún)^(qū)域”輸入E2:E100，在“接收區(qū)域”輸入G2:G7，依次選擇“新工作表組”輸出選項(xiàng)和“圖表輸出”項(xiàng)。如下圖所示。

操作步驟（4）單擊【確定】按鈕，創(chuàng)建的員工工資直方圖出現(xiàn)在新的工作表中，如下圖所示:

操作步驟（5）修飾直方圖，修改坐標(biāo)軸標(biāo)題。選擇直方圖中的“頻率”文本，將其刪除后輸入“員工工資”，選擇“接收”文本，將其刪除后輸入“工資區(qū)間（單位：元）”。（6）選擇直方圖中的縱坐標(biāo)，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)置坐標(biāo)軸格式”命令，在打開的對話框中進(jìn)行如下參數(shù)設(shè)置。如下圖所示:

操作步驟（7）設(shè)置圖表網(wǎng)格線、圖例和顯示數(shù)字等。切換到【圖表工具】選項(xiàng)卡，在【圖表布局】功能區(qū)中，單擊“添加圖表元素”命令，選擇“網(wǎng)格線”下面的“主軸主要垂直網(wǎng)格線”。選擇“數(shù)據(jù)標(biāo)簽”下的“數(shù)據(jù)標(biāo)簽外”選項(xiàng)，選擇“圖例”下的“無”，美化結(jié)果如下圖所示:

5．3．5數(shù)據(jù)分析工具庫

3.抽樣工具現(xiàn)需要統(tǒng)計(jì)出一個規(guī)模很大公司的員工性別比例或年齡構(gòu)成，由于人員眾多，把每一位員工的相關(guān)信息都納入統(tǒng)計(jì)，麻煩且沒有必要。一般都是通過抽取一部分員工作為總體樣本來進(jìn)行統(tǒng)計(jì)工作。

案例【例5-7】在一個擁有99名員工的公司中隨機(jī)抽取10個員工進(jìn)行抽樣調(diào)查統(tǒng)計(jì)，以得到整個公司員工的性別構(gòu)成。

操作步驟（1）創(chuàng)建員工基本信息數(shù)據(jù)表，在A2：C100區(qū)域輸入相關(guān)信息。為了便于統(tǒng)計(jì)，將性別“男”和“女”分別用數(shù)字1和2進(jìn)行指代。（2）在D1單元格輸入“性別指代數(shù)字”，在單元格D2中輸入函數(shù)=IF（C2=“男”，1,2），在E1單元格輸入文本“抽樣”。如下圖所示:

操作步驟（3）將光標(biāo)放在D2單元格右下角并按住鼠標(biāo)左鍵向下拖拽至最后一個員工的單元格位置時釋放，將所有員工的性別進(jìn)行指代。選中E2：E11單元格區(qū)域，在【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)中，單擊【數(shù)據(jù)分析】命令，彈出“數(shù)據(jù)分析”對話框。在“分析工具”框中，選擇“抽樣”，單擊【確定】按鈕后，彈出“抽樣”對話框。

操作步驟（4）在“抽樣”對話框的“輸入?yún)^(qū)域”輸入“D2:D100”，抽樣方法選擇“隨機(jī)”，“樣本數(shù)”輸入10，“輸出區(qū)域”輸入“E2:E11”，如右圖所示：

操作步驟（5）單擊【確定】按鈕，抽樣結(jié)果如下圖所示?？梢灾貜?fù)多次抽樣，綜合多次抽樣結(jié)果，可以了解公司員工的性別構(gòu)成。從本次抽樣結(jié)果來看，男女比例基本平衡。

5．3．5數(shù)據(jù)分析工具庫5.回歸分析工具在現(xiàn)實(shí)生活中，很多現(xiàn)象之間有聯(lián)系，如在市場經(jīng)濟(jì)環(huán)境下，商品的銷售量與商品的價(jià)格、質(zhì)量、廣告投入、消費(fèi)者的收入水平等因素有關(guān)。在生產(chǎn)活動中，糧食的產(chǎn)量與施肥量、氣候環(huán)境、降雨量等有關(guān)?；貧w分析是對具有因果關(guān)系的影響因素（自變量）和預(yù)測對象（因變量）所進(jìn)行的數(shù)理統(tǒng)計(jì)分析處理，是在掌握大量觀察數(shù)據(jù)的基礎(chǔ)上，建立一個或多個變量（自變量）與另一個變量（因變量）的定量關(guān)系是，稱作回歸方程。

5．3．5數(shù)據(jù)分析工具庫只有當(dāng)變量與因變量確實(shí)存在某種關(guān)系時，建立的回歸方程才有意義。因此，作為自變量的因素與作為因變量的預(yù)測對象是否有關(guān)，相關(guān)程度如何，以及判斷這種相關(guān)程度的把握性多大，就成為進(jìn)行回歸分析必須要解決的問題。

5．3．5數(shù)據(jù)分析工具庫常用的回歸方程根據(jù)自變量的多少分為一元回歸和多元回歸，根據(jù)模型中變量之間的變動關(guān)系可以分為線性回歸和非線性回歸，通常線性回歸分析法是最基本的分析方法。遇到非線性回歸問題，可以借助于數(shù)學(xué)手段轉(zhuǎn)化為線性回歸問題處理?；貧w方程一般表示為：Y=a+b1x1+b2x2+…+bnxn，其中Y是因變量，x1，x2,…是自變量。當(dāng)n=1時，稱為一元回歸分析，n>1時稱為多元回歸分析。在Excel中，運(yùn)用回歸工具即可以快捷地完成回歸分析預(yù)測的運(yùn)算。

案例【例5-8】近年來移動互聯(lián)網(wǎng)市場發(fā)展迅速，對移動互聯(lián)網(wǎng)市場交易規(guī)模的預(yù)測比較常見。移動互聯(lián)交易依賴于移動互聯(lián)終端，所以移動互聯(lián)網(wǎng)的交易規(guī)模必然和移動終端的銷售量有很大關(guān)系?，F(xiàn)有某移動終端銷售公司2008年-2017年的移動終端銷售量和移動互聯(lián)網(wǎng)交易規(guī)模的歷史記錄，預(yù)測2018年的移動互聯(lián)網(wǎng)交易規(guī)模。

操作步驟（1）建立數(shù)據(jù)模型。將統(tǒng)計(jì)數(shù)據(jù)輸入Excel數(shù)據(jù)表中，如下圖所示。

操作步驟（2）在【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)中，單擊【數(shù)據(jù)分析】命令，彈出“數(shù)據(jù)分析”對話框，在對話框中選擇“回歸”。單擊【確定】按鈕，彈出“回歸”對話框。（3）按下圖所示進(jìn)行設(shè)置。

操作步驟（4）按【確定】按鈕，分析的結(jié)果如下圖1、圖2所示。

圖1操作步驟圖2操作步驟由回歸分析結(jié)果可見，回歸方程y=ax+b中，a=0.039462671,b=-2.022018449，即回歸方程為y=0.039462671-2.022018499。在本例中，判定系數(shù)為0.955781543（單元格A16的值），表明移動終端的銷售量與移動互聯(lián)網(wǎng)的交易規(guī)模間存在很大的相關(guān)性。假設(shè)2018年的移動終端銷售量是6278萬臺，則根據(jù)回歸方程，可預(yù)測到2018年的移動互聯(lián)網(wǎng)交易規(guī)模為y=0.039462671*6278-2.022018449，即245.725億元

5．3．5數(shù)據(jù)分析工具庫5．移動平均工具移動平均法是根據(jù)時間序列資料逐項(xiàng)推移，依次計(jì)算包含一定項(xiàng)數(shù)的平均值，對時間序列數(shù)據(jù)做簡單平滑處理，從而預(yù)測現(xiàn)象的長期趨勢。

5．3．5數(shù)據(jù)分析工具庫移動平均方法包含簡單移動平均和加權(quán)移動平均。在簡單移動平均法中，計(jì)算移動平均數(shù)時每個觀測值都用相同的權(quán)數(shù)。而在加權(quán)移動平均法中，需要對每個數(shù)值選擇不同的權(quán)數(shù)，然后計(jì)算最近n個時期數(shù)值的加權(quán)平均數(shù)作為預(yù)測值。在大多數(shù)情況下，最近時間的觀測值更重要應(yīng)取得最大的權(quán)數(shù)，較遠(yuǎn)時間的權(quán)數(shù)應(yīng)依次遞減。進(jìn)行加權(quán)平均預(yù)測時，通常是先對數(shù)據(jù)進(jìn)行加權(quán)處理，然后再調(diào)用分析工具計(jì)算。

5．3．5數(shù)據(jù)分析工具庫簡單移動平均的計(jì)算公式如下。假設(shè)友誼時間序列x1，x2，…xi，按數(shù)據(jù)點(diǎn)的順序逐點(diǎn)推移求出N個數(shù)的平均數(shù)，即可得到移動平均數(shù)：

式中，Mi為第i個周期的移動平均數(shù)，yi為第i個周期的觀測值，N為移動平均的項(xiàng)數(shù)，即求每一個移動平均數(shù)使用的觀察值的個數(shù)。5．3．5數(shù)據(jù)分析工具庫該公式表明，當(dāng)i向前移動一個周期，就增加一個新近數(shù)據(jù)，去掉一個遠(yuǎn)期數(shù)據(jù)，得到一個新的平均數(shù)。由于它逐期向前移動，所以稱為移動平均法。由于移動平均可以平滑數(shù)據(jù)，消除周期變動和不規(guī)則變動的影響，使得長期趨勢能夠顯示出來，因而可以用于預(yù)測。其預(yù)測公式為：

即以第i周期的移動平均數(shù)作為第i+1周期的預(yù)測值。移動平均分析工具及其公式可以基于特定的過去某段時期中變量的均值，對未來值進(jìn)行預(yù)測。案例

【例5-9】某品牌內(nèi)衣公司從2015開始線上銷售，現(xiàn)需要根據(jù)近三年的歷史銷售數(shù)據(jù)來預(yù)測2018年5月的銷售額。

操作步驟（1）建立數(shù)據(jù)模型。將統(tǒng)計(jì)數(shù)據(jù)輸入Excel數(shù)據(jù)表中，如右圖所示:

操作步驟（2）在【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)中，單擊【數(shù)據(jù)分析】按鈕，打開【數(shù)據(jù)分析】對話框。（3）在“分析工具”列表框中選擇【移動平均】選項(xiàng)，單擊【確定】按鈕，彈出“移動平均”對話框，如下圖所示:

操作步驟（4）設(shè)置相關(guān)參數(shù)?！拜斎?yún)^(qū)域”：輸入B1:B41。“標(biāo)志位于第一行”復(fù)選框：數(shù)據(jù)源中包含了單元格B1的文本內(nèi)容，勾線此項(xiàng)?！伴g隔”：移動平均的項(xiàng)數(shù)，本例中設(shè)置為3?！拜敵鰠^(qū)域”：選擇單元格C2。

操作步驟“圖表輸出”復(fù)選框：勾選此選項(xiàng)，將會自動繪制“折線圖”，本例勾選此項(xiàng)?！皹?biāo)準(zhǔn)誤差”復(fù)選框：若勾選此項(xiàng)，將計(jì)算并保留標(biāo)準(zhǔn)誤差數(shù)據(jù)，可以再次基礎(chǔ)上進(jìn)一步進(jìn)行分析。本例中勾選此項(xiàng)。設(shè)置完成后，如下圖所示。

操作步驟（5）單擊【確定】按鈕，生成如下圖所示。

C列顯示的是移動平均值，D列顯示的是標(biāo)準(zhǔn)誤差。因?yàn)槠湟苿悠骄捻?xiàng)數(shù)為3，計(jì)算公式中沒有可用的數(shù)據(jù)，所以C2:C3和D2:D5單元格顯示的是“#N/A”。操作步驟（6）由于i+1期的預(yù)測值就等于i期的移動平均值，2018年5月份的銷售額即為2018年4月份的移動平均值。從輸出的結(jié)果可以預(yù)測出2018年5月份的銷售額約為20.633萬元。如下圖所示：

5．3．5數(shù)據(jù)分析工具庫6．z-檢驗(yàn)工具在Excel中，假設(shè)檢驗(yàn)工具主要有以下幾個，即：F-檢驗(yàn)：雙樣本方差檢驗(yàn)，檢驗(yàn)兩個正態(tài)隨機(jī)變量的總體方差是否相等的假設(shè)檢驗(yàn)。t-檢驗(yàn)：平均值的成對二樣本分析，是指在總體方差已知的條件下兩個樣本均值之差的檢驗(yàn)。

5．3．5數(shù)據(jù)分析工具庫t-檢驗(yàn)：雙樣本等方差假設(shè)，是指總體方差未知，但假定其在相等的條件下進(jìn)行的檢驗(yàn)。t-檢驗(yàn)：雙樣本異方差假設(shè)，是指總體方差未知，但假定其在不等的條件下進(jìn)行的檢驗(yàn)。z-檢驗(yàn)：雙樣本平均差檢驗(yàn)，是指配對樣本的t檢驗(yàn)。用Excel假設(shè)檢驗(yàn)工具進(jìn)行假設(shè)檢驗(yàn)的方法類似，下面介紹z-檢驗(yàn)。

案例【例5-10】某生產(chǎn)企業(yè)現(xiàn)有兩種產(chǎn)品工藝可供選擇，擬對兩種工藝生產(chǎn)產(chǎn)品所需時間（分鐘）進(jìn)行測試，隨機(jī)抽取10個工人，讓他們分別采用兩種工藝生產(chǎn)同一種產(chǎn)品，統(tǒng)計(jì)采用工藝A所需時間和采用工藝B所需時間。假設(shè)生產(chǎn)產(chǎn)品的時間服從正態(tài)分布，以?=0.05的顯著性水平比較兩種工藝的差別是否大。

操作步驟（1）建立數(shù)據(jù)模型，把相應(yīng)的數(shù)據(jù)輸入到數(shù)據(jù)表。如下圖所示。

操作步驟（2）在【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)中，單擊【數(shù)據(jù)分析】按鈕，打開“數(shù)據(jù)分析”對話框。（3）在“分析工具”列表框中，選擇【z-檢驗(yàn)：雙樣本平均差檢驗(yàn)】選項(xiàng)，單擊【確定】按鈕，彈出“z-檢驗(yàn)：雙樣本平均差檢驗(yàn)”對話框，按下圖所示輸入?yún)?shù)。

操作步驟（4）單擊【確定】按鈕，得到輸出結(jié)果，如下圖所示。

操作步驟在上面的輸出結(jié)果中，可以根據(jù)P值進(jìn)行判斷，也可以根據(jù)統(tǒng)計(jì)量和臨界值比較進(jìn)行判斷。在本例中采用單尾檢驗(yàn)，其單尾P值為0.319329604，大于給定的顯著性水平0.05，所以應(yīng)該接受原假設(shè)，即工藝A和工藝B無顯著差別；用臨界值判斷，即z的值為0.469574275，小于z的臨界值1.644853627，根據(jù)右尾校驗(yàn)，也是可接受原假設(shè)，即兩種工藝無顯著差別。

5．4實(shí)戰(zhàn)訓(xùn)練5．4.1實(shí)戰(zhàn)案例1GT公司職工培訓(xùn)費(fèi)用與效益的相關(guān)性GT公司比較注重職工的職業(yè)素養(yǎng)，每月都開展職業(yè)培訓(xùn)，以提高員工的整體素質(zhì)和工作效率。有人提出質(zhì)疑，真的就是投入培訓(xùn)的花費(fèi)越高，公司的效益就越高嗎？針對此疑問，該公司委托統(tǒng)計(jì)部門做一個這兩者之間的相關(guān)性分析。5．4.1實(shí)戰(zhàn)案例1如右圖所示，本表中顯示了該公司的各月用于培訓(xùn)的費(fèi)用和公司對應(yīng)月份的運(yùn)營效益。由統(tǒng)計(jì)學(xué)知識可知，系數(shù)越接近于1，說明這兩者之間存在著良好的正相關(guān)性。任務(wù)一：培訓(xùn)費(fèi)用與效益之間的相關(guān)系數(shù)（1）在【數(shù)據(jù)】選項(xiàng)卡【分析】功能區(qū)中，單擊【數(shù)據(jù)分析】按鈕，打開【數(shù)據(jù)分析】對話框。（2）在“分析工具”列表框中選