1、Excel與數(shù)據(jù)處理,第七章 數(shù)據(jù)分析工具及應(yīng)用,本章教學(xué)目的與要求,1、掌握宏的加載方法 2、掌握追蹤從屬或引用單元格的方法 3、掌握限定單元格數(shù)據(jù)的范圍及圈釋無效數(shù)據(jù)的 應(yīng)用方法 4、掌握模擬運(yùn)算表及變量求解的應(yīng)用 5、掌握方案的建立和應(yīng)用 6、掌握規(guī)劃求解工具的應(yīng)用 7、了解假設(shè)檢驗(yàn)和回歸分析等工具的應(yīng)用,本章重點(diǎn)、難點(diǎn)及學(xué)時(shí)數(shù),重點(diǎn)： 掌握數(shù)據(jù)審核的方法 掌握模擬運(yùn)算表的應(yīng)用 掌握單變量求解的應(yīng)用 掌握方案的應(yīng)用 掌握規(guī)劃求解的應(yīng)用 難點(diǎn)： 掌握規(guī)劃求解的應(yīng)用 學(xué)時(shí)數(shù)： 12學(xué)時(shí)（上機(jī)6學(xué)時(shí)）,本章目錄,7.1 分析工具的安裝 7.2 數(shù)據(jù)審核及跟蹤分析 7.3 模擬運(yùn)算表 7.4 單

2、變量求解 7.5 方案分析 7.6 線性規(guī)劃求解 7.7 數(shù)據(jù)分析工具庫 小結(jié) 思考與練習(xí),7.1 分析工具的安裝,1、加載宏的概念 加載宏是一種可選擇性地安裝到計(jì)算機(jī)中的軟件組件，用戶可根據(jù)需要決定是否安裝。其作用是為 Excel 添加命令和函數(shù)，擴(kuò)充Excel的功能。 Excel加載宏的擴(kuò)展名是.xla或.xll。 在默認(rèn)情況下，Excel將下表列出的加載宏程序安裝在如下某一磁盤位置：“Microsoft OfficeOffice”文件夾下的“Library”文件夾或其子文件夾，或 Windows 所在文件夾下的“Profiles用戶名Application DataMicrosoftAd

3、dIns”文件夾下。網(wǎng)絡(luò)管理員也可將加載宏程序安裝到其他位置。,7.1 分析工具的安裝,2、Excel內(nèi)置加載宏,7.1 分析工具的安裝,7.1 分析工具的安裝,3、安裝分析工具 選擇“工具”|“加載宏”菜單在對(duì)話框中選擇所需工具，按確定 注：若在安裝EXCEL系統(tǒng)時(shí)沒有安裝加載宏，則必須重新啟動(dòng)EXCEL的安裝程序，選擇其中的“添加/刪除”命令，安裝EXCEL的加載宏。,目錄,7.2 數(shù)據(jù)審核及跟蹤分析,1、概念 數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯(cuò)誤來源的工具，快速地找出具有引用關(guān)系的單元格，借此分析造成錯(cuò)誤的單元格。 數(shù)據(jù)審核使用追蹤箭頭，通過圖形的方式顯示或追蹤單元格與公式之間的關(guān)系。 2、

4、數(shù)據(jù)審核的方式 追蹤引用單元格見ch7.xls追蹤引用單元格 操作方法：選定菜單“工具”“審核” 顯示審核工具欄選擇要追蹤引用的含公式單元格“審核”工具欄中“追蹤引用單元格”按鈕再次單擊“追蹤引用單元格”按鈕提供數(shù)據(jù)的下一級(jí)單元格 移去引用單元格追蹤箭頭： 操作方法：選擇“審核”工具欄中“移去引用單元格中追蹤箭頭”,7.2 數(shù)據(jù)審核及跟蹤分析,追蹤從屬單元格見ch7.xls追蹤從屬單元格 某單元格公式引用了其它單元格，則該單元格為從屬單元格。 操作方法：選定菜單“工具”“審核” 顯示審核工具欄選擇要追蹤從屬單元格的單元格“審核”工具欄中“追蹤從屬單元格”按鈕再次單擊“追蹤從屬單元格”按鈕提供從

5、屬的的單元格 移去引用單元格追蹤箭頭： 操作方法：選擇“審核”工具欄中“移去從屬單元格中追蹤箭頭”,7.2 數(shù)據(jù)審核及跟蹤分析,3、 數(shù)據(jù)有效性 數(shù)據(jù)有效性：對(duì)數(shù)據(jù)進(jìn)行檢驗(yàn)和檢查的有效方法，把錯(cuò)誤限制在數(shù)據(jù)輸入階段。 限定數(shù)據(jù)類型和有效范圍： 如：限定數(shù)據(jù)大小范圍、日期的范圍、輸入字符的個(gè)數(shù)、單元格的公式,7.2 數(shù)據(jù)審核及跟蹤分析,數(shù)據(jù)限制的操作方法：選擇“數(shù)據(jù)” “有效性”在對(duì)話 框中操作： 限定文本長度： “設(shè)置”選項(xiàng)卡中“允許” 下拉列表中選擇文本長度。 限定數(shù)據(jù)的有效范圍：“設(shè)置”選項(xiàng)卡中“允許”下拉列表中選擇整數(shù)/小數(shù)- 確定最大/小值 設(shè)置單元格有效范圍：“設(shè)置”選項(xiàng)卡中“允許”

6、下拉列表中選擇序列輸入序列值 設(shè)置輸入提示信息： “輸入信息”選項(xiàng)卡中輸入要顯示的信息,7.2 數(shù)據(jù)審核及跟蹤分析,例：見ch7.xls限定數(shù)據(jù)范圍 某班要建立一個(gè)成績登記表，為了減少成績輸入錯(cuò)誤，可對(duì)成績表中數(shù)據(jù)的輸入類型及范圍進(jìn)行限制。 限制學(xué)號(hào)為8位字符，不能小于8位，也不能多于8位。 限制所有學(xué)科成績?yōu)?100之間的整數(shù)。 限制科目列標(biāo)題的取值范圍，如“高數(shù)”不能輸入為“高等數(shù)學(xué)”。,7.2 數(shù)據(jù)審核及跟蹤分析,4、圈釋無效數(shù)據(jù) 使用數(shù)據(jù)有效性規(guī)則可限制單元格可接收的數(shù)據(jù)，但對(duì)已輸入數(shù)據(jù)的區(qū)域，不能顯示出有誤的數(shù)據(jù)。采用圈釋無效數(shù)據(jù)的方法，可以顯示不滿足有效性規(guī)則的錯(cuò)誤單元格。 操作方

7、法：（選擇數(shù)據(jù)區(qū)域設(shè)置數(shù)據(jù)有效性規(guī)則）選擇“工具”菜單“審核”選擇“顯示審核工具欄”選中有效性檢測的數(shù)據(jù)區(qū)域單擊“審核”工具欄的“圈釋無效數(shù)據(jù)”按鈕 注：要先設(shè)置數(shù)據(jù)的有效范圍，然后再圈釋無效數(shù)據(jù) 例：見ch7.xls圈釋無效數(shù)據(jù) 某班要建立一個(gè)成績登記表，已經(jīng)對(duì)成績表中數(shù)據(jù)的輸入類型及范圍進(jìn)行限制，找出其中不符合規(guī)定的數(shù)據(jù)。,目錄,7.3 模擬運(yùn)算表,1、概念 模擬運(yùn)算表是對(duì)工作表中一個(gè)單元格區(qū)域內(nèi)的數(shù)據(jù)進(jìn)行模擬運(yùn)算，測試使用一個(gè)或兩個(gè)變量的公式中變量對(duì)運(yùn)算結(jié)果的影響。 2、模擬運(yùn)算表的類型 基于一個(gè)輸入變量的表，用這個(gè)輸入變量測試它對(duì)多個(gè)公式的影響；單模擬運(yùn)算表 基于兩個(gè)輸入變量的表，用這

8、兩個(gè)變量測試它們對(duì)于單個(gè)公式的影響雙模擬運(yùn)算表,7.3 模擬運(yùn)算表,3、單變量模擬運(yùn)算表 概念 在單變量模擬運(yùn)算表中，輸入數(shù)據(jù)的值被安排在一行或一列中。同時(shí)，單變量模擬表中使用的公式必須引用“輸入單元格”。 輸入單元格，就是被替換的含有輸入數(shù)據(jù)的單元格 操作步驟： 1、在工作表中建立模擬運(yùn)算表的結(jié)構(gòu)； 2、輸入模擬運(yùn)算表要用到的公式； 3、選擇包括公式、引用單元格和運(yùn)算結(jié)果單元格區(qū)域（3部分）； 4、選擇“數(shù)據(jù)”菜單“模擬運(yùn)算表”選項(xiàng)； 5、在“模擬運(yùn)算表”對(duì)話框中輸入引用單用格（行或列一種） 確定,7.3 模擬運(yùn)算表,例：見ch7.xls單變量模擬運(yùn)算表 假設(shè)某人正考慮購買一套住房，要承擔(dān)一

9、筆250 000元的貸款，分15年還清?，F(xiàn)想查看每月的還貸金額，并想查看在不同的利率下，每月的應(yīng)還貸金額。 若貸款額分別為400 000，550 000，800 000元，每月的應(yīng)還貸金額又是多少？,7.3 模擬運(yùn)算表,4、雙變量模擬運(yùn)算表 概念： 單變量模擬運(yùn)算表只能解決一個(gè)輸入變量對(duì)一個(gè)或多個(gè)公式計(jì)算結(jié)果的影響，要查看兩個(gè)變量對(duì)公式計(jì)算結(jié)果的影響，就要用到雙變量模擬運(yùn)算表。所謂雙模擬變量，就是指公式中有兩個(gè)變量。公式中兩個(gè)變量所在的單元格是任取的?？梢允枪ぷ鞅碇腥我饪瞻讍卧?。,7.3 模擬運(yùn)算表,操作步驟： 1、在工作表中建立模擬運(yùn)算表的結(jié)構(gòu)； 2、在行列交叉處輸入模擬運(yùn)算表要用到的公式

10、； 3、選擇包括公式,引用單元格和運(yùn)算結(jié)果單元格區(qū)域（3部分）； 4、選擇“數(shù)據(jù)”菜單“模擬運(yùn)算表”選項(xiàng)； 5、在“模擬運(yùn)算表”對(duì)話框中輸入公式中行和列引用的單用格確定 例：見ch7.xls雙變量模擬運(yùn)算表 假設(shè)某人想貸款45萬元購買一部車，要查看在不同的利率和不同的償還年限下，每個(gè)月應(yīng)還的貸款金額。假設(shè)要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，償還期限為10年、15年、20年、30年、35年時(shí)，每月應(yīng)歸還的貸款金額是多少 ？,目錄,7.4 單變量求解,1、概念 所謂單變量求解，就是求解具有一個(gè)變量的方程，Excel通過調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來滿足目標(biāo)

11、單元格中的目標(biāo)值. 2、單變量求解方法 在工作表中輸入原始數(shù)據(jù)； 建立可變數(shù)公式； 設(shè)置求解公式：菜單“工具”單變量求解對(duì)話框中輸入：目標(biāo)單元格、目標(biāo)值、可變單元格 例： 見ch7.xls單變量求解 某公司想向銀行貸款900萬元人民幣，貸款利率是8.7%，貸款限期為8年，每年應(yīng)償還多少金額？ 如果公司每年可償還120萬元，該公司最多可貸款多少金額？ 前一問題可用PMT函數(shù), 后一問題可用單變量求解。,目錄,7.5 方案分析,1、概念 方案是已命名的一組輸入值，是 Excel 保存在工作表中并可用來自動(dòng)替換某個(gè)計(jì)算模型的輸入值，用來預(yù)測模型的輸出結(jié)果。 例: 已知某茶葉公司2004年的總銷售額及

12、各種茶葉的銷售成本，現(xiàn)要在此基礎(chǔ)上制訂一個(gè)五年計(jì)劃。由于市場競爭的不斷變化，所以只能對(duì)總銷售額及各種茶葉銷售成本的增長率做一些估計(jì)。最好的方案當(dāng)然是總銷售額增長率高，各茶葉的銷售成本增長率低。 最好的估計(jì)是總銷售額增長13%，花茶、綠茶、烏龍茶、紅茶的銷售成本分別增長10%、6%、10%、7%。 見ch7.xls方案,7.5 方案分析,建立方案解決工作表,建立方法如下，輸入下表A列、B列及第3行的所有數(shù)據(jù)；在C4單元格中輸入公式“=B4*(1+$B$16)”，然后將其復(fù)制到D4F4；在C7中輸入公式“=B7*(1+$B$17)”,并將其復(fù)制到D7F7； 在C8中輸入公式“=B8*(1+$B$1

13、8)”，并將其復(fù)制到D8和F8； 在C9中輸入公式“=B9*(1+$B$19)”，并將其復(fù)制到D9F9； 在C10中輸入公式“=B10*(1+$B$20)”，并將其復(fù)制到D10F10；第11行數(shù)據(jù)是第7，8，9，10行數(shù)據(jù)對(duì)應(yīng)列之和；凈收入是相應(yīng)的總銷售額和銷售成本之差，E19的總凈收入是第13行數(shù)據(jù)之和。,7.5 方案分析,輸入方案變量值如下圖所示：,7.5 方案分析,2、顯示方案 選擇“工具” “方案”菜單選擇“方案管理器”對(duì)話框中的某一方案單擊 “顯示”按鈕 3、建立方案報(bào)告見ch7.xls方案摘要 選擇“工具” “方案”菜單選擇“方案管理器”對(duì)話框中的某一方案單擊 “總結(jié)”按鈕在“方案

14、總結(jié)”對(duì)話框中結(jié)果類型中選擇“方案總結(jié)” 4、建立方案透視圖見ch7.xls方案數(shù)據(jù)透視圖 選擇“工具” “方案”菜單選擇“方案管理器”對(duì)話框中的某一方案單擊 “總結(jié)”按鈕在“方案總結(jié)”對(duì)話框中結(jié)果類型中選擇“方案數(shù)據(jù)透視表”,目錄,7.6 線性規(guī)劃求解,1、概述 EXCEL提供的規(guī)劃求解工具，可求解出線性與非線性兩種規(guī)劃求解問題，規(guī)劃求解問題常用于解決產(chǎn)品比例、人員調(diào)度、優(yōu)化路線、調(diào)配材料等方面問題。 2、規(guī)劃求解問題的特點(diǎn)： 問題有單一的目標(biāo)，如求運(yùn)輸?shù)淖罴崖肪€、求生產(chǎn)的最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時(shí)間等。 問題有明確的不等式約束條件，例如生產(chǎn)材料不能超過庫存，生產(chǎn)周期不能

15、超過一個(gè)星期等。 問題有直接或間接影響約束條件的一組輸入值。,7.6 線性規(guī)劃求解,3、Excel規(guī)劃求解問題的組成部分 （1）一個(gè)或一組可變單元格 可變單元格稱為決策變量，一組決策變量代表一個(gè)規(guī)劃求解的方案 （2）目標(biāo)函數(shù) 目標(biāo)函數(shù)表示規(guī)劃求解要達(dá)到的最終目標(biāo)，是規(guī)劃求解的關(guān)鍵。它是規(guī)劃求解中可變量的函數(shù) （3）約束條件 約束條件是實(shí)現(xiàn)目標(biāo)的限制條件。 意義：通過規(guī)劃求解，用戶可為工作表的目標(biāo)單元格中的公式找到一個(gè)優(yōu)化值，規(guī)劃求解將直接或間接與目標(biāo)單元格公式相聯(lián)系的一組單元格數(shù)值進(jìn)行調(diào)整，最終在目標(biāo)單元格公式中求得期望的結(jié)果。,7.6 線性規(guī)劃求解,例： 見ch7.xls規(guī)劃求解 某肥料廠專

16、門收集有機(jī)物垃圾，如青草、樹枝、凋謝的花朵等。該廠利用這些廢物，并摻進(jìn)不同比例的泥土和礦物質(zhì)來生產(chǎn)高質(zhì)量的植物肥料，生產(chǎn)的肥料分為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料4種。為使問題簡單，假設(shè)收集廢物的勞動(dòng)力是自愿的，除了收集成本之外，材料成本是低廉的。 該廠目前的原材料、生產(chǎn)各種肥料需要的原材料比例，各種肥料的單價(jià)等如下頁各表所示。 問題：求出在現(xiàn)有的情況下，即利用原材料的現(xiàn)有庫存，應(yīng)生產(chǎn)各種類型的肥料各多少數(shù)量才能獲得最大利潤，最大利潤是多少？ 分析:所求是在現(xiàn)有的原材料情況下,應(yīng)如何合理搭配,才能獲取生產(chǎn)產(chǎn)品的最大利潤.,7.6 線性規(guī)劃求解,表2 生產(chǎn)肥料的庫存原材料,表1 各肥料成品

17、用料及其價(jià)格表 表的意思是生產(chǎn)一個(gè)單位的肥料需要多少各種原材料多少單位,表3單位原材料成本單價(jià),7.6 線性規(guī)劃求解,建立規(guī)劃求解模型步驟： 規(guī)劃求解第一步建立求解工作表(輸入原始數(shù)據(jù)及相應(yīng)的各公式),7.6 線性規(guī)劃求解,規(guī)劃求解第二步設(shè)置求解參數(shù) 選擇“工具” “規(guī)劃求解”菜單，設(shè)置以下求解的各項(xiàng)參數(shù)： 設(shè)置目標(biāo)單元格：輸入目標(biāo)函數(shù)所在單元格(為總余額單元格) 設(shè)置目標(biāo)：最大值、最小值或值的數(shù)值(最大利潤,即最大值) 設(shè)置可變單元格：它的確定決定結(jié)果（為生產(chǎn)數(shù)量） 設(shè)置約束條件：單擊“添加”按鈕輸入約束條件按添加依次輸入所有約束條件確定,7.6 線性規(guī)劃求解,規(guī)劃求解第3步保存求解結(jié)果 在

18、規(guī)劃求解對(duì)話框中按“求解”在規(guī)劃求解結(jié)果對(duì)話框中按“保存規(guī)劃求解結(jié)果”,7.6 線性規(guī)劃求解,4、修改資源 例1：見ch7.xls規(guī)劃求解 肥料廠接到一個(gè)電話：只要公司肯花10元的運(yùn)費(fèi)就能得到150個(gè)單位的礦物。這筆交易稍稍降低了礦物質(zhì)的平均價(jià)格，但這些礦物質(zhì)值10元嗎？ 解決該問題的方法是，將庫存礦物3500改為3650，用規(guī)劃求解重新計(jì)算最大盈余?？闯ィ?0的成本后，盈余是否增加 操作: 將庫存礦物3500改為3650,其它所有公式不變,再次進(jìn)行求解,求得盈余額為4483.41,原盈余額為4425.89. 可知盈利為57.52.扣除10元成本后仍有47.52.因此該礦物還是要的.,7.6

19、 線性規(guī)劃求解,5、修改約束條件 見ch7.xls規(guī)劃求解 肥料廠接到一個(gè)電話，一個(gè)老顧客急需25個(gè)單位的上層肥料，公司經(jīng)理在檢查打印結(jié)果后，發(fā)現(xiàn)沒有安排生產(chǎn)上層肥料。數(shù)量為0。決定增加約束條件，為他生產(chǎn)25個(gè)單位的上層肥料。 結(jié)果可發(fā)現(xiàn):盈余額僅3246.51,比原來4483.41少了1236.9。顯然不值得。但如該顧客為長期顧客，則短期內(nèi)將損失一些錢，但得到了顧客的信任。,增加的約束條件,7.6 線性規(guī)劃求解,6、 規(guī)劃求解的結(jié)果報(bào)告 運(yùn)算結(jié)果報(bào)告：列出目標(biāo)單元格、可變單元格及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。 見ch7.xls運(yùn)算結(jié)果報(bào)告,7.6 線性規(guī)劃求解,敏感性

20、報(bào)告： 見ch7.xls敏感性報(bào)告,7.6 線性規(guī)劃求解,極限報(bào)告：列出目標(biāo)單元格、可變單元格及它們的數(shù)值、上下限和目標(biāo)值。下限為在滿足約束條件和保持其它可變單元格數(shù)值不變的情況下，某個(gè)可變單元格可以取得的最小值，上限則為在這種情況下可以取到的最大值。 見ch7.xls極限值報(bào)告,7.6 線性規(guī)劃求解,7、求解精度及求解模型設(shè)置 Excel采用迭代的方式進(jìn)行規(guī)劃求解，當(dāng)求解到一定精度時(shí)就結(jié)束求解，但有時(shí)要修改求解的精度、計(jì)算時(shí)間、規(guī)劃模型和迭代次數(shù)。修改上述設(shè)置的方法如下： 在“規(guī)劃求解參數(shù)”對(duì)話框中設(shè)置好各項(xiàng)求解參數(shù)； 單擊“選項(xiàng)”按鈕，在“規(guī)劃求解選項(xiàng)”對(duì)話框中設(shè)置各項(xiàng)求解參數(shù)。,7.6

21、線性規(guī)劃求解,例2： 求解不等式：見ch7.xls規(guī)劃求解不等式 某工廠生產(chǎn)甲、乙兩種產(chǎn)品，假設(shè)生產(chǎn)甲產(chǎn)品1噸，要消耗9噸煤，4千瓦電力，3噸鋼材，獲利0.7萬元；生產(chǎn)乙產(chǎn)品1噸，要消耗4噸煤，5千瓦電力，10噸鋼材，獲利1.2萬元。按計(jì)劃國家能提供給該廠的煤為360噸，電力200千瓦，鋼材300噸，問應(yīng)該生產(chǎn)多少噸甲種產(chǎn)品和乙種產(chǎn)品，才能獲得最大利潤？ 假設(shè)生產(chǎn)甲種產(chǎn)品X1噸，生產(chǎn)乙種產(chǎn)品x2噸， 其最大利潤是求=0.7x1+1.2x2的最大值。這個(gè)問題可用數(shù)學(xué)建模如下：,7.6 線性規(guī)劃求解,規(guī)劃求解如下： B3和C3分別用于保存甲和乙產(chǎn)品的生產(chǎn)量。 目標(biāo)單元格為B8；可變單元格為$B$3

22、:$C$3；約束條件為： $B$3=0 $C$3=0 $B$4=360 $B$5=200 $B$6=300,7.6 線性規(guī)劃求解,例3： 見ch7.xls線形規(guī)劃求解 某公司在A地有一個(gè)生產(chǎn)基地,其生產(chǎn)能力為400,隨著市場需求的增長及該公司業(yè)務(wù)量的增大,現(xiàn)有3個(gè)配送中心的需求都在增長,預(yù)計(jì)分別為200,400,300。公司正考慮再建立一個(gè)生產(chǎn)能力為500的工廠，準(zhǔn)備建在B地。從A地的工廠向3個(gè)配送中心的單位運(yùn)輸成本分別為5.0元，6.0元，5.4元，從B地的工廠向3個(gè)配送中心的單位運(yùn)輸成本是7.0元，4.6元，6.6元。應(yīng)怎樣分配A、B兩地到3個(gè)配送中心的產(chǎn)品量，才能使運(yùn)輸成本最??？ 規(guī)劃模

23、型解釋如下：,7.6 線性規(guī)劃求解,D5、E5、F5表示從A廠將1個(gè)單位產(chǎn)品分別送到配送中心1，2，3的費(fèi)用； D7，E7，F(xiàn)7表示從B廠將1個(gè)單位產(chǎn)品分別送到配送中心1，2，3的費(fèi)用； D6，E6，F(xiàn)6為可變單元格，保存從A廠運(yùn)到3個(gè)配送中心的最佳產(chǎn)品量； D8，E8，F(xiàn)8為可變單元格，保存從B廠運(yùn)到3個(gè)配送中心的最佳產(chǎn)品量； D10，E10，F(xiàn)10表示配送中心1，2，3的最大負(fù)荷能力； H5，H7分別是A廠、B廠的生產(chǎn)能力， H9是A，B兩廠的總生產(chǎn)能力,7.6 線性規(guī)劃求解,G6，G8分別為A，B兩廠各自的產(chǎn)品總量。 G6公式：=D6+E6+F6； G8公式：=G8+E8+F8； D9，

24、E9，F(xiàn)9分別為兩廠送到各配送中心的產(chǎn)品總量。 D9公式：=D6+D8； E9公式：=E6+E8； F9公式：=F6+F8； 本模型的約束條件分析如下： A，B兩廠送到各配送中心的產(chǎn)品總量不能超過各配送中心的負(fù)荷能力：D9：F9=0，D8:F8=0,本模型的目標(biāo)函數(shù)，求下列公式的最大值： S=D5*D6+E5*E6+F5*F6+D7*D8+E7*E8+F7*F8,目錄,7.7 數(shù)據(jù)分析工具庫,1、概述 Excel提供了一組數(shù)據(jù)分析工具，稱為分析工具庫。其中提供的分析工具在工程分析、數(shù)理統(tǒng)計(jì)、經(jīng)濟(jì)計(jì)量分析等學(xué)科中有較強(qiáng)的實(shí)用價(jià)值。 分析工具庫由Excel自帶的加載宏提供。 如果啟動(dòng)Excel后，

25、在Excel的“工具”菜單中沒有“數(shù)據(jù)分析”菜單項(xiàng)，就需啟動(dòng)“工具”中的“加載宏”菜單項(xiàng)，將“分析工具庫”加載到Excel系統(tǒng)中。 如果加載宏對(duì)話框中沒有分析工具庫，則單擊加載宏對(duì)話框中“瀏覽”按鈕，定位到分析工具庫加載宏文件“Analy32.dll”所在的驅(qū)動(dòng)器和文件夾，通常位于“Microsoft OfficeOfficeLibraryAnalysis”中,否則需運(yùn)行Office系統(tǒng)的安裝程序。 Excel的“分析工具庫”加載宏提供的一些統(tǒng)計(jì)函數(shù)、財(cái)務(wù)函數(shù)和工程函數(shù)。這些函數(shù)只有在安裝了“分析工具庫”后才能使用 。,7.7 數(shù)據(jù)分析工具庫,2、Excel分析工具庫中的工具,7.7 數(shù)據(jù)分析

26、工具庫,7.7 數(shù)據(jù)分析工具庫,3、 統(tǒng)計(jì)分析 Excel的分析工具庫提供了3種統(tǒng)計(jì)觀測分析工具：指數(shù)平滑分析、移動(dòng)平均分析和回歸分析 三種工具用法相同，下面以指數(shù)平滑分析為例。見ch7.xls 指數(shù)平滑分析 （1）在工作表的一列上輸入各時(shí)間點(diǎn)上的觀察值，如下圖A列所示。,7.7 數(shù)據(jù)分析工具庫,（2）選擇“工具”菜單“數(shù)據(jù)分析” 選項(xiàng)，對(duì)話框中選擇“指數(shù)平滑”按確定。 （3）在“指數(shù)平滑”對(duì)話框中設(shè)置“輸入?yún)^(qū)域”、“阻尼系數(shù)”、“輸出區(qū)域”選項(xiàng)。 （4）選定對(duì)話框中“圖表輸出”和“標(biāo)準(zhǔn)誤差”復(fù)選框標(biāo)志。 分析結(jié)果：B列為分析之后輸出的預(yù)測數(shù)據(jù)；C列是分析工具輸出的標(biāo)準(zhǔn)誤差。,7.7 數(shù)據(jù)分析

27、工具庫,4、假設(shè)檢驗(yàn) 假設(shè)檢驗(yàn)是根據(jù)對(duì)事物進(jìn)行抽樣所得的少量樣本信息，判斷總體分布的某個(gè)假設(shè)是否成立的一種數(shù)理統(tǒng)計(jì)方法。 假設(shè)分析工具有三種：t-檢驗(yàn)、z-檢驗(yàn)、F-檢驗(yàn)。 運(yùn)用這些檢驗(yàn)工具可以完成均值、方差的假設(shè)檢驗(yàn)。 方法見下例,7.7 數(shù)據(jù)分析工具庫,例： 見ch7.xlst檢驗(yàn) （雙樣本等方差t-檢驗(yàn)，以確定兩個(gè)樣本均值實(shí)際上是否相等） 某種子公司為比較兩個(gè)稻種的產(chǎn)量，選擇了25塊條件相似的試驗(yàn)田，采用相同的耕種方法進(jìn)行耕種試驗(yàn)，結(jié)果播種甲稻種的13塊田的畝產(chǎn)量（單位：市斤）分別是：880、1 120、980、885、828、927、924、942、766、1 180、780、1 06

28、8、650；播種乙稻種的12塊試驗(yàn)田的畝產(chǎn)量分別是：940、1 142、1 020、785、645、780、1 180、680、810、824、846、780。問這兩個(gè)稻種的產(chǎn)量有沒有明顯的高低之分。 說明：要判斷兩稻種有無顯著差別，用t-檢驗(yàn)方法，需先計(jì)算各樣本的平均值和方差，才能作進(jìn)一步的檢驗(yàn)分析。,7.7 數(shù)據(jù)分析工具庫,t-檢驗(yàn)操作過程： (1)輸入A、B兩列樣本數(shù)據(jù)（下表中右邊數(shù)據(jù)全為產(chǎn)生的分析結(jié)果）,7.7 數(shù)據(jù)分析工具庫,(2)選擇“工具”菜單中“數(shù)據(jù)分析”選項(xiàng)，對(duì)話框中選擇t檢驗(yàn)雙樣本等方差假設(shè)； （3）在“雙樣本等方差假設(shè)分析”對(duì)話框中設(shè)置t檢驗(yàn)的各項(xiàng)參數(shù) 按確定,7.7 數(shù)

29、據(jù)分析工具庫,5、回歸分析 回歸分析主要用于分析單個(gè)因變量是如何受一個(gè)或幾個(gè)自變量影響的。如觀察某個(gè)運(yùn)動(dòng)員的運(yùn)動(dòng)成績與一系列統(tǒng)計(jì)因素的關(guān)系。如年齡、體重、身高等。 回歸分析分為線性回歸和非線性回歸兩種。線性回歸的數(shù)學(xué)模型為： Excel通過對(duì)一組觀察值使用“最小二乘法”直線擬合，進(jìn)行線性回歸分析，該回歸分析可同時(shí)解決一元回歸與多元回歸問題。,7.7 數(shù)據(jù)分析工具庫,例：（用一個(gè)多元回歸線性分析例子來說明回歸分析工具的使用方法） ch7.xls回歸分析數(shù)據(jù)表中，列出了美國19561970年間歷年的人均可支配收入xi和人均可消費(fèi)支出yi的數(shù)據(jù)。試用圖中的數(shù)據(jù)擬合模型。 模型中的趨勢變量t，用于反映

30、除人均收入之外的所有其他因素對(duì)人均消費(fèi)的影響,7.7 數(shù)據(jù)分析工具庫,利用回歸分析工具求解此模型的方法 輸入原始數(shù)據(jù)表； 選擇“工具”菜單中“數(shù)據(jù)分析”選項(xiàng)，在“數(shù)據(jù)分析”對(duì)話框中選擇“回歸”列表。系統(tǒng)彈出如下對(duì)話框； 在“回歸”對(duì)話框中輸入因變量y和自變量x的數(shù)據(jù)區(qū)域； 若需要線性擬合的“殘差圖”和“線性擬合圖”等，則需選擇相應(yīng)復(fù)選框 本題結(jié)果見見ch7.xls回歸分析的輸出結(jié)果,目錄,綜合實(shí)例,1.單變量模擬運(yùn)算表實(shí)例 超市要進(jìn)行一些改革，如對(duì)某些產(chǎn)品采取分期付款的方式進(jìn)行銷售。假設(shè)有一液晶電視，每臺(tái)售價(jià)為98,000，采用分期付款的方式進(jìn)行銷售，初步確定分期付款的方式為零首付，月分期手續(xù)

31、費(fèi)率為0.7，求在不同的分期付款期數(shù)（月）下，每期（月）消費(fèi)者需要付款的金額,綜合實(shí)例,【實(shí)例操作步驟】 具體操作步驟如下： 步驟1：創(chuàng)建工作表，建立基本的模擬運(yùn)算工作表，輸入必要的數(shù)據(jù)及要測試的工作表中的數(shù)據(jù)，即不同的分期付款期數(shù)，如圖所示。,綜合實(shí)例,步驟2：創(chuàng)建運(yùn)算公式 在單元格D3中輸入公式“=PMT（B5,C3,B2）”，得出當(dāng)分期付款期數(shù)為C3單元格中的數(shù)值時(shí)，消費(fèi)者每月的付款額。如圖7-2所示。公式中B5為月分期手續(xù)費(fèi)率，C3為分期付款期數(shù)，以月為單位，B2為付款本金。,綜合實(shí)例,步驟3：建立單變量模擬運(yùn)算表 首先要選定公式、數(shù)值序列和模擬運(yùn)算結(jié)果所在的單元格區(qū)域，即C3:D12

32、，以定義這個(gè)模擬運(yùn)算表，然后選擇“數(shù)據(jù)”菜單中的“模擬運(yùn)算表”命令，打開“模擬運(yùn)算表”對(duì)話框，在“模擬運(yùn)算表”對(duì)話框的“輸入引用列的單元格”文本框中輸入第一個(gè)變量所在的單元格地址“$C$3”，如圖所示。,綜合實(shí)例,【實(shí)例操作結(jié)果】 最終的計(jì)算結(jié)果如圖所示。,綜合實(shí)例,雙變量模擬運(yùn)算表實(shí)例 在前面的例子中，僅僅把分期付款期數(shù)作為單變量進(jìn)行模擬運(yùn)算，但在現(xiàn)實(shí)生活中，月分期付款手續(xù)費(fèi)率也是經(jīng)常要發(fā)生變化的。假設(shè)想查看在不同的分期付款期數(shù)和不同的月分期付款手續(xù)費(fèi)率下消費(fèi)者每月付款金額的變化情況，就必須建立雙變量模擬運(yùn)算表了。,綜合實(shí)例,【實(shí)例操作步驟】 步驟1：創(chuàng)建工作表 建立基本的運(yùn)算工作表，輸入必

33、要的數(shù)據(jù)及要測試的工作表中的數(shù)據(jù)，即不同的月分期付款手續(xù)費(fèi)率和分期付款期數(shù)，其中，月分期付款手續(xù)費(fèi)率屬于行變量，分期付款期數(shù)屬于列變量。如圖所示。,綜合實(shí)例,步驟2：創(chuàng)建運(yùn)算公式 雙變量模擬運(yùn)算中首先要在行、列交叉處所在的單元格輸入運(yùn)算公式。在本例中，在分期付款期數(shù)和月分期付款手續(xù)費(fèi)率的交叉處，即D3單元格中輸入公式“=PMT(B5,B7,B2)”，運(yùn)算結(jié)果如圖所示。,綜合實(shí)例,步驟3：建立雙變量模擬運(yùn)算表 首先要選定公式、數(shù)值序列和模擬運(yùn)算結(jié)果所在的單元格區(qū)域，即D3:J13，以定義這個(gè)模擬運(yùn)算表，然后選擇“數(shù)據(jù)”菜單中的“模擬運(yùn)算表”命令，打開“模擬運(yùn)算表”對(duì)話框，在“輸入引用行的單元格”

34、中輸入月分期手續(xù)費(fèi)率所在的單元格“$B$5”，在“輸入引用列的單元格”中輸入分期付款期數(shù)所在的單元格“$B$7”，如圖所示。最后單擊“模擬運(yùn)算表”對(duì)話框的“確定”按鈕。,綜合實(shí)例,【實(shí)例操作結(jié)果】 最終的計(jì)算結(jié)果如圖所示。,綜合實(shí)例,利用單變量求解計(jì)算付款期數(shù)案例 在上面模擬運(yùn)算的例子中，主要是求出消費(fèi)者在固定的分期手續(xù)費(fèi)率和分期付款期數(shù)下每月應(yīng)付的金額。假設(shè)現(xiàn)在有一消費(fèi)者可負(fù)擔(dān)的每月的付款金額為￥5000，想知道在固定的月分期手續(xù)費(fèi)率下，該消費(fèi)者可承受的付款期數(shù)是多少？,綜合實(shí)例,【案例操作步驟】 步驟1：在單變量求解工作表中輸入原始數(shù)據(jù)。如圖所示,綜合實(shí)例,步驟2：在月付款金額對(duì)應(yīng)的單元格

35、B4中輸入公式“=PMT(B3,B5,B2)”，如圖所示。,綜合實(shí)例,步驟3：選擇“工具”菜單的“單變量求解”命令，打開“單變量求解”對(duì)話框，在“目標(biāo)單元格”中輸入“$B$4”，在“目標(biāo)值”中輸入“-5000”，表示經(jīng)過求解之后，單元格B4的值應(yīng)是-5000，目標(biāo)單元格必須包含公式，在“可變單元格”中輸入“$B$5”，即最后分期付款期數(shù)所在的單元格，如左圖所示。接著在單擊“確定”按鈕后，屏幕上可能出現(xiàn)出錯(cuò)提示，表示無法進(jìn)行求解，如右圖所示。,綜合實(shí)例,在這里可以先給可變單元格設(shè)置一個(gè)值，比如5，因?yàn)槠渲械臄?shù)值也要根據(jù)目標(biāo)單元格中的結(jié)果而發(fā)生變化，所以事先給的值并不會(huì)影響到最終的計(jì)算結(jié)果。具體設(shè)

36、置如左圖所示。 步驟4：再打開“單變量求解”對(duì)話框，按上面同樣的設(shè)置，可得到“單變量求解狀態(tài)”對(duì)話框，表明求得一個(gè)解，如右圖所示。,綜合實(shí)例,【案例操作結(jié)果】 求解結(jié)果如圖所示。,綜合實(shí)例,超市收入預(yù)測方案實(shí)例 假設(shè)已知超市在2007年的各部門的收入情況，現(xiàn)在想對(duì)2008年的收入情況做一個(gè)估計(jì)，分析在不同增長方案下的收入情況，這些增長方案包括經(jīng)營情況一般、經(jīng)營情況良好和經(jīng)營情況較差三種方案。,綜合實(shí)例,【案例操作步驟】 具體操作步驟如下： 步驟1：制作如圖所示的工作表，包括2007年度的收入情況，以及2008年度根據(jù)經(jīng)營情況（一般）設(shè)置的收入增長率。其中在單元格D3輸入公式“=C3-B3”，然

37、后將其復(fù)制到D4、D5、D6；在單元格D11中輸入公式“=C3*(1+C11)-B3*(1+B11)”，并將其復(fù)制到D12、D13、D14；在單元格D15中輸入公式“=SUM(D11:D14)”。從圖中可以看出，在經(jīng)營情況一般這種方案下，2008年企業(yè)的總利潤為5052715。,綜合實(shí)例,步驟2：選擇“工具”菜單的“方案”命令，彈出“方案管理器”對(duì)話框，如圖所示。,綜合實(shí)例,步驟3：在“方案管理器”中單擊“添加”按鈕，彈出“編輯方案”對(duì)話框，在“方案名”中輸入方案名稱“經(jīng)營情況一般”；在“可變單元格”中輸入需要更改的單元格的引用“$B$11:$C$14”，選中“防止更改”復(fù)選項(xiàng)。如圖所示。,綜

38、合實(shí)例,步驟4：在上圖所示的對(duì)話框中單擊“確定”按鈕，將打開“方案變量值”對(duì)話框。在“方案變量值”對(duì)話框中分別輸入可變單元格所對(duì)應(yīng)的單元格，如圖所示。,綜合實(shí)例,步驟5：在上圖所示的對(duì)話框中單擊“確定”按鈕，則該方案創(chuàng)建完成，返回“方案管理器”對(duì)話框，將顯示出所定義方案的名稱。如圖所示。,綜合實(shí)例,步驟6：在“方案管理器”對(duì)話框中單擊“添加”按鈕，使用類似的步驟再創(chuàng)建兩個(gè)方案。 【實(shí)例操作結(jié)果】 最終結(jié)果如圖所示,綜合實(shí)例,利用規(guī)劃求解制定銷售決策實(shí)例 假設(shè)超市某一部門要銷售兩種商品，其中A商品的采購價(jià)為900元，銷售價(jià)為2400元，B商品的采購價(jià)位800元，銷售價(jià)為1800元，而且兩種商品的

39、物流和倉儲(chǔ)成本不同，A商品的物流成本和倉儲(chǔ)成本分別為30元和19.5元，B商品物流成本和倉儲(chǔ)成本分別為25元和6.5元，現(xiàn)在的問題是在每月的物流和倉儲(chǔ)費(fèi)用固定的情況下（物流和倉儲(chǔ)總費(fèi)用分別為2800元和1500元）。該如何分配A、B兩種商品的進(jìn)貨數(shù)量，才能得到最大的銷售利潤？,綜合實(shí)例,【實(shí)例操作步驟】 具體操作步驟如下： 步驟 1：建立商品數(shù)據(jù)表，如圖所示。,綜合實(shí)例,步驟 2：輸入公式，在B12單元格中輸入公式“=(B6-B5-B4-B3)*B7+(C6-C5-C4-C3)*C7”，在單元格B13和B14中分別輸入“=B4*B7+C4*C7”和“=B5*B7+C5*C7”。如圖所示。,綜合

40、實(shí)例,步驟 3：建立好上面表格后，就可以利用規(guī)劃求解工具對(duì)表格的問題進(jìn)行求解了。選擇“工具”菜單中的“規(guī)劃求解”命令，彈出“規(guī)劃求解參數(shù)”對(duì)話框，如圖所示。在“設(shè)置目標(biāo)單元格”文本框中輸入B12單元格，即要求的銷售最大利潤。在“等于”單選項(xiàng)中選擇“最大值”，表示所要求的目標(biāo)函數(shù)的最大值。在“可變單元格”文本框中輸入B7:C7，就是目標(biāo)函數(shù)的兩個(gè)變量，也就是兩種商品各自的進(jìn)貨量。這里的引用均為絕對(duì)引用。,綜合實(shí)例,步驟4：設(shè)置規(guī)劃求解約束條件。選擇“規(guī)劃求解參數(shù)”對(duì)話框中的“添加”按鈕，彈出“添加約束”對(duì)話框，在對(duì)話框中添加限制條件。對(duì)于物流總成本的限制，應(yīng)該是物流總成本小于每月的物流費(fèi)用控制，即B13B9。添加完