與數(shù)據(jù)處理數(shù)據(jù)分析工具及應(yīng)用ppt課件

1、Excel與數(shù)據(jù)處置與數(shù)據(jù)處置本章教學(xué)目的與要求本章教學(xué)目的與要求1 1、掌握宏的加載方法、掌握宏的加載方法2 2、掌握追蹤從屬或援用單元格的方法、掌握追蹤從屬或援用單元格的方法3 3、掌握限定單元格數(shù)據(jù)的范圍及圈釋無效數(shù)據(jù)的、掌握限定單元格數(shù)據(jù)的范圍及圈釋無效數(shù)據(jù)的 運(yùn)用方法運(yùn)用方法4 4、掌握模擬運(yùn)算表及變量求解的運(yùn)用、掌握模擬運(yùn)算表及變量求解的運(yùn)用5 5、掌握方案的建立和運(yùn)用、掌握方案的建立和運(yùn)用6 6、掌握規(guī)劃求解工具的運(yùn)用、掌握規(guī)劃求解工具的運(yùn)用7 7、了解假設(shè)檢驗(yàn)和回歸分析等工具的運(yùn)用、了解假設(shè)檢驗(yàn)和回歸分析等工具的運(yùn)用本章重點(diǎn)、難點(diǎn)及學(xué)時(shí)數(shù)本章重點(diǎn)、難點(diǎn)及學(xué)時(shí)數(shù)n重點(diǎn)：重點(diǎn)：n掌

2、握數(shù)據(jù)審核的方法掌握數(shù)據(jù)審核的方法n掌握模擬運(yùn)算表的運(yùn)用掌握模擬運(yùn)算表的運(yùn)用n掌握單變量求解的運(yùn)用掌握單變量求解的運(yùn)用n掌握方案的運(yùn)用掌握方案的運(yùn)用n掌握規(guī)劃求解的運(yùn)用掌握規(guī)劃求解的運(yùn)用n難點(diǎn)：難點(diǎn)：n掌握規(guī)劃求解的運(yùn)用掌握規(guī)劃求解的運(yùn)用n學(xué)時(shí)數(shù)：學(xué)時(shí)數(shù)：n1212學(xué)時(shí)上機(jī)學(xué)時(shí)上機(jī)6 6學(xué)時(shí)學(xué)時(shí)本章目錄本章目錄7.1 分析工具的安裝分析工具的安裝7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析7.3 模擬運(yùn)算表模擬運(yùn)算表7.4 單變量求解單變量求解7.5 方案分析方案分析7.6 線性規(guī)劃求解線性規(guī)劃求解7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)小結(jié)小結(jié)思索與練習(xí)思索與練習(xí)7.1 分析工具的安裝分析工具的

3、安裝1、加載宏的概念、加載宏的概念加載宏是一種可選擇性地安裝到計(jì)算機(jī)中的軟件組件，用戶加載宏是一種可選擇性地安裝到計(jì)算機(jī)中的軟件組件，用戶可根據(jù)需求決議能否安裝。其作用是為可根據(jù)需求決議能否安裝。其作用是為 Excel 添加命添加命令和函數(shù)，擴(kuò)展令和函數(shù)，擴(kuò)展Excel的功能。的功能。Excel加載宏的擴(kuò)展名是加載宏的擴(kuò)展名是.xla或或.xll。在默許情況下，在默許情況下，Excel將下表列出的加載宏程序安裝在如下將下表列出的加載宏程序安裝在如下某一磁盤位置：某一磁盤位置：“Microsoft OfficeOffice文件夾下文件夾下的的“Library文件夾或其子文件夾，或文件夾或其子文件

4、夾，或 Windows 所所在文件夾下的在文件夾下的“Profiles用戶名用戶名Application DataMicrosoftAddIns文件夾下。網(wǎng)絡(luò)管理員也文件夾下。網(wǎng)絡(luò)管理員也可將加載宏程序安裝到其他位置?？蓪⒓虞d宏程序安裝到其他位置。 7.1 分析工具的安裝分析工具的安裝2、Excel內(nèi)置加載宏內(nèi)置加載宏加加 載載 宏宏描描 述述分析工具分析工具庫(kù)庫(kù)添加財(cái)務(wù)、統(tǒng)計(jì)和工程分析工具和函數(shù)添加財(cái)務(wù)、統(tǒng)計(jì)和工程分析工具和函數(shù)條件求和條件求和向?qū)驅(qū)?duì)于數(shù)據(jù)清單中滿足指定條件的數(shù)據(jù)進(jìn)行求和對(duì)于數(shù)據(jù)清單中滿足指定條件的數(shù)據(jù)進(jìn)行求和計(jì)算計(jì)算歐元工具歐元工具將數(shù)值的格式設(shè)置為歐元的格式，并提供將

5、數(shù)值的格式設(shè)置為歐元的格式，并提供EUROCONVERT函數(shù)以用于轉(zhuǎn)換貨幣函數(shù)以用于轉(zhuǎn)換貨幣查閱向?qū)Р殚喯驅(qū)?chuàng)建一個(gè)公式，通過數(shù)據(jù)清單中的已知值查找創(chuàng)建一個(gè)公式，通過數(shù)據(jù)清單中的已知值查找所需數(shù)據(jù)所需數(shù)據(jù)ODBC 加加載宏載宏利用安裝的利用安裝的 ODBC 驅(qū)動(dòng)程序，通過開放式數(shù)據(jù)驅(qū)動(dòng)程序，通過開放式數(shù)據(jù)庫(kù)互連（庫(kù)互連（ODBC）功能與外部數(shù)據(jù)源相連）功能與外部數(shù)據(jù)源相連7.1 分析工具的安裝分析工具的安裝報(bào)告管理報(bào)告管理器器為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視面為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視面以及方案的報(bào)告以及方案的報(bào)告規(guī)劃求解規(guī)劃求解對(duì)基于可變單元格和條件單元格的假設(shè)分析方對(duì)基于

6、可變單元格和條件單元格的假設(shè)分析方案進(jìn)行求解計(jì)算案進(jìn)行求解計(jì)算模板工具模板工具提供提供 Excel 的內(nèi)置模板所使用的工具。使用內(nèi)置的內(nèi)置模板所使用的工具。使用內(nèi)置模板時(shí)就可自動(dòng)訪問這些工具模板時(shí)就可自動(dòng)訪問這些工具Internet Assistant VBA通過使用通過使用 Excel 97 Internet Assistant 語(yǔ)法，開語(yǔ)法，開發(fā)者可將發(fā)者可將 Excel 數(shù)據(jù)發(fā)布到數(shù)據(jù)發(fā)布到 Web 上上7.1 分析工具的安裝分析工具的安裝3、安裝分析工具、安裝分析工具選擇選擇“工具工具|“加載宏菜單加載宏菜單在對(duì)話框中選擇所需工具，在對(duì)話框中選擇所需工具，按確定按確定 注：假設(shè)在安裝注

7、：假設(shè)在安裝EXCEL系統(tǒng)時(shí)沒有安裝加載宏，那么必需重系統(tǒng)時(shí)沒有安裝加載宏，那么必需重新啟動(dòng)新啟動(dòng)EXCEL的安裝程序，選擇其中的的安裝程序，選擇其中的“添加添加/刪除命刪除命令，安裝令，安裝EXCEL的加載宏。的加載宏。7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析1、概念、概念數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯(cuò)誤來源的工具，快速地找出數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯(cuò)誤來源的工具，快速地找出具有援用關(guān)系的單元格，借此分析呵斥錯(cuò)誤的單元格。具有援用關(guān)系的單元格，借此分析呵斥錯(cuò)誤的單元格。數(shù)據(jù)審核運(yùn)用追蹤箭頭，經(jīng)過圖形的方式顯示或追蹤單元格與數(shù)據(jù)審核運(yùn)用追蹤箭頭，經(jīng)過圖形的方式顯示或追蹤單元格與公式之間

8、的關(guān)系。公式之間的關(guān)系。 2、數(shù)據(jù)審核的方式、數(shù)據(jù)審核的方式追蹤援用單元格追蹤援用單元格見見ch7.xls追蹤援用單元格追蹤援用單元格 操作方法：選定菜單操作方法：選定菜單“工具工具“審核審核 顯示顯示審核審核工具欄工具欄選擇要追蹤援用的含公式單元格選擇要追蹤援用的含公式單元格“審核工具欄中審核工具欄中“追蹤援追蹤援用單元格按鈕用單元格按鈕再次單擊再次單擊“追蹤援用單元格按鈕提供數(shù)追蹤援用單元格按鈕提供數(shù)據(jù)的下一級(jí)單元格據(jù)的下一級(jí)單元格移去援用單元格追蹤箭頭：移去援用單元格追蹤箭頭： 操作方法：選擇操作方法：選擇“審核工具欄中審核工具欄中“移去援用單元格中追蹤箭移去援用單元格中追蹤箭頭頭7.2

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

10、“審核工具欄中審核工具欄中“移去從屬單元格中追蹤箭移去從屬單元格中追蹤箭頭頭7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析3、 數(shù)據(jù)有效性數(shù)據(jù)有效性數(shù)據(jù)有效性：對(duì)數(shù)據(jù)進(jìn)展檢驗(yàn)和檢查的有效方法，把錯(cuò)誤數(shù)據(jù)有效性：對(duì)數(shù)據(jù)進(jìn)展檢驗(yàn)和檢查的有效方法，把錯(cuò)誤限制在數(shù)據(jù)輸入階段。限制在數(shù)據(jù)輸入階段。 限定數(shù)據(jù)類型和有效范圍：限定數(shù)據(jù)類型和有效范圍： 如：限定數(shù)據(jù)大小范圍、日期的范圍、輸入字符的個(gè)數(shù)、如：限定數(shù)據(jù)大小范圍、日期的范圍、輸入字符的個(gè)數(shù)、單元格的公式單元格的公式7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)限制的操作方法：選擇數(shù)據(jù)限制的操作方法：選擇“數(shù)據(jù)數(shù)據(jù) “有效性有效性在對(duì)話在對(duì)話 框中操

11、作：框中操作：限定文本長(zhǎng)度：限定文本長(zhǎng)度： “設(shè)置選項(xiàng)卡中設(shè)置選項(xiàng)卡中“允許允許 下拉列表中選下拉列表中選擇文本長(zhǎng)度。擇文本長(zhǎng)度。限定數(shù)據(jù)的有效范圍：限定數(shù)據(jù)的有效范圍：“設(shè)置選項(xiàng)卡中設(shè)置選項(xiàng)卡中“允許下拉列表允許下拉列表中選擇整數(shù)中選擇整數(shù)/小數(shù)小數(shù)- 確定最大確定最大/小值小值設(shè)置單元格有效范圍：設(shè)置單元格有效范圍：“設(shè)置選項(xiàng)卡中設(shè)置選項(xiàng)卡中“允許下拉列表允許下拉列表中選擇序列中選擇序列輸入序列值輸入序列值設(shè)置輸入提示信息：設(shè)置輸入提示信息： “輸入信息選項(xiàng)卡中輸入要顯示的輸入信息選項(xiàng)卡中輸入要顯示的信息信息7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析n例：例：見見ch7.xls限定數(shù)據(jù)范

12、圍限定數(shù)據(jù)范圍n 某班要建立一個(gè)成果登記表，為了減少成果輸入錯(cuò)某班要建立一個(gè)成果登記表，為了減少成果輸入錯(cuò)誤，可對(duì)成果表中數(shù)據(jù)的輸入類型及范圍進(jìn)展限制。誤，可對(duì)成果表中數(shù)據(jù)的輸入類型及范圍進(jìn)展限制。n限制學(xué)號(hào)為限制學(xué)號(hào)為8位字符，不能小于位字符，不能小于8位，也不能多于位，也不能多于8位。位。n限制一切學(xué)科成果為限制一切學(xué)科成果為0100之間的整數(shù)。之間的整數(shù)。n限制科目列標(biāo)題的取值范圍，如限制科目列標(biāo)題的取值范圍，如“高數(shù)不能輸入為高數(shù)不能輸入為“高等數(shù)學(xué)。高等數(shù)學(xué)。7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析4、圈釋無效數(shù)據(jù)、圈釋無效數(shù)據(jù) 運(yùn)用數(shù)據(jù)有效性規(guī)那么可限制單元格可接納的數(shù)據(jù)，但對(duì)

13、運(yùn)用數(shù)據(jù)有效性規(guī)那么可限制單元格可接納的數(shù)據(jù)，但對(duì)已輸入數(shù)據(jù)的區(qū)域，不能顯示出有誤的數(shù)據(jù)。采用圈釋無已輸入數(shù)據(jù)的區(qū)域，不能顯示出有誤的數(shù)據(jù)。采用圈釋無效數(shù)據(jù)的方法，可以顯示不滿足有效性規(guī)那么的錯(cuò)誤單元效數(shù)據(jù)的方法，可以顯示不滿足有效性規(guī)那么的錯(cuò)誤單元格。格。操作方法：選擇數(shù)據(jù)區(qū)域操作方法：選擇數(shù)據(jù)區(qū)域設(shè)置數(shù)據(jù)有效性規(guī)那么設(shè)置數(shù)據(jù)有效性規(guī)那么選選擇擇“工具菜單工具菜單“審核審核選擇選擇“顯示審核工具欄顯示審核工具欄選選中有效性檢測(cè)的數(shù)據(jù)區(qū)域中有效性檢測(cè)的數(shù)據(jù)區(qū)域單擊單擊“審核工具欄的審核工具欄的“圈釋無圈釋無效數(shù)據(jù)按鈕效數(shù)據(jù)按鈕 注：要先設(shè)置數(shù)據(jù)的有效范圍，然后再圈釋無效數(shù)據(jù)注：要先設(shè)置數(shù)據(jù)的有

14、效范圍，然后再圈釋無效數(shù)據(jù)例：例：見見ch7.xls圈釋無效數(shù)據(jù)圈釋無效數(shù)據(jù) 某班要建立一個(gè)成果登記表，曾經(jīng)對(duì)成果表中數(shù)據(jù)的輸入某班要建立一個(gè)成果登記表，曾經(jīng)對(duì)成果表中數(shù)據(jù)的輸入類型及范圍進(jìn)展限制，找出其中不符合規(guī)定的數(shù)據(jù)。類型及范圍進(jìn)展限制，找出其中不符合規(guī)定的數(shù)據(jù)。7.3 模擬運(yùn)算表模擬運(yùn)算表1、概念、概念模擬運(yùn)算表是對(duì)任務(wù)表中一個(gè)單元格區(qū)域內(nèi)的數(shù)據(jù)進(jìn)展模擬模擬運(yùn)算表是對(duì)任務(wù)表中一個(gè)單元格區(qū)域內(nèi)的數(shù)據(jù)進(jìn)展模擬運(yùn)算，測(cè)試運(yùn)用一個(gè)或兩個(gè)變量的公式中變量對(duì)運(yùn)算結(jié)果運(yùn)算，測(cè)試運(yùn)用一個(gè)或兩個(gè)變量的公式中變量對(duì)運(yùn)算結(jié)果的影響。的影響。2、模擬運(yùn)算表的類型、模擬運(yùn)算表的類型基于一個(gè)輸入變量的表，用這個(gè)輸

15、入變量測(cè)試它對(duì)多個(gè)公基于一個(gè)輸入變量的表，用這個(gè)輸入變量測(cè)試它對(duì)多個(gè)公式的影響；式的影響；單模擬運(yùn)算表單模擬運(yùn)算表基于兩個(gè)輸入變量的表，用這兩個(gè)變量測(cè)試它們對(duì)于單個(gè)基于兩個(gè)輸入變量的表，用這兩個(gè)變量測(cè)試它們對(duì)于單個(gè)公式的影響公式的影響雙模擬運(yùn)算表雙模擬運(yùn)算表7.3 模擬運(yùn)算表模擬運(yùn)算表3、單變量模擬運(yùn)算表、單變量模擬運(yùn)算表概念概念在單變量模擬運(yùn)算表中，輸入數(shù)據(jù)的值被安排在一行或一列中。在單變量模擬運(yùn)算表中，輸入數(shù)據(jù)的值被安排在一行或一列中。同時(shí)，單變量模擬表中運(yùn)用的公式必需援用同時(shí)，單變量模擬表中運(yùn)用的公式必需援用“輸入單元格。輸入單元格。輸入單元格，就是被交換的含有輸入數(shù)據(jù)的單元格輸入單元格

16、，就是被交換的含有輸入數(shù)據(jù)的單元格 操作步驟：操作步驟：1、在任務(wù)表中建立模擬運(yùn)算表的構(gòu)造；、在任務(wù)表中建立模擬運(yùn)算表的構(gòu)造；2、輸入模擬運(yùn)算表要用到的公式；、輸入模擬運(yùn)算表要用到的公式；3、選擇包括公式、援用單元格和運(yùn)算結(jié)果單元格區(qū)域、選擇包括公式、援用單元格和運(yùn)算結(jié)果單元格區(qū)域3部部分；分；4、選擇、選擇“數(shù)據(jù)菜單數(shù)據(jù)菜單“模擬運(yùn)算表選項(xiàng)；模擬運(yùn)算表選項(xiàng)；5、在、在“模擬運(yùn)算表對(duì)話框中輸入援用單用格行或列一模擬運(yùn)算表對(duì)話框中輸入援用單用格行或列一種種 確定確定7.3 模擬運(yùn)算表模擬運(yùn)算表n例：例：見見ch7.xls單變量模擬運(yùn)算表單變量模擬運(yùn)算表n 假設(shè)某人正思索購(gòu)買一套住房，要承當(dāng)一筆假

17、設(shè)某人正思索購(gòu)買一套住房，要承當(dāng)一筆250 000元的貸款，分元的貸款，分15年還清?，F(xiàn)想查看每月的還貸金額，年還清?，F(xiàn)想查看每月的還貸金額，并想查看在不同的利率下，每月的應(yīng)還貸金額。并想查看在不同的利率下，每月的應(yīng)還貸金額。 n 假設(shè)貸款額分別為假設(shè)貸款額分別為400 000，550 000，800 000元，每月的應(yīng)還貸金額又是多少？元，每月的應(yīng)還貸金額又是多少？ 7.3 模擬運(yùn)算表模擬運(yùn)算表4、雙變量模擬運(yùn)算表、雙變量模擬運(yùn)算表概念：概念： 單變量模擬運(yùn)算表只能處理一個(gè)輸入變量對(duì)一個(gè)或多個(gè)公式計(jì)單變量模擬運(yùn)算表只能處理一個(gè)輸入變量對(duì)一個(gè)或多個(gè)公式計(jì)算結(jié)果的影響，要查看兩個(gè)變量對(duì)公式計(jì)算結(jié)

18、果的影響，就要算結(jié)果的影響，要查看兩個(gè)變量對(duì)公式計(jì)算結(jié)果的影響，就要用到雙變量模擬運(yùn)算表。所謂雙模擬變量，就是指公式中有兩用到雙變量模擬運(yùn)算表。所謂雙模擬變量，就是指公式中有兩個(gè)變量。公式中兩個(gè)變量所在的單元格是任取的?？梢允侨蝿?wù)個(gè)變量。公式中兩個(gè)變量所在的單元格是任取的?？梢允侨蝿?wù)表中恣意空白單元格。表中恣意空白單元格。7.3 模擬運(yùn)算表模擬運(yùn)算表n操作步驟：操作步驟：n1、在任務(wù)表中建立模擬運(yùn)算表的構(gòu)造；、在任務(wù)表中建立模擬運(yùn)算表的構(gòu)造；n2、在行列交叉處輸入模擬運(yùn)算表要用到的公式；、在行列交叉處輸入模擬運(yùn)算表要用到的公式；n3、選擇包括公式、選擇包括公式,援用單元格和運(yùn)算結(jié)果單元格區(qū)域援

19、用單元格和運(yùn)算結(jié)果單元格區(qū)域3部分；部分；n4、選擇、選擇“數(shù)據(jù)菜單數(shù)據(jù)菜單“模擬運(yùn)算表選項(xiàng)；模擬運(yùn)算表選項(xiàng)；n5、在、在“模擬運(yùn)算表對(duì)話框中輸入公式中行和列援用模擬運(yùn)算表對(duì)話框中輸入公式中行和列援用的單用格的單用格確定確定n例：例：見見ch7.xls雙變量模擬運(yùn)算表雙變量模擬運(yùn)算表n 假設(shè)某人想貸款假設(shè)某人想貸款45萬元購(gòu)買一部車，要查看在不同萬元購(gòu)買一部車，要查看在不同的利率和不同的歸還年限下，每個(gè)月應(yīng)還的貸款金額。的利率和不同的歸還年限下，每個(gè)月應(yīng)還的貸款金額。假設(shè)要查看貸款利率為假設(shè)要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，歸還期限為，歸還期限為10年、年、15年

20、、年、20年、年、30年、年、35年時(shí)，每月應(yīng)歸還的貸款金額是多少年時(shí)，每月應(yīng)歸還的貸款金額是多少 ？7.4 單變量求解單變量求解1、概念、概念所謂單變量求解，就是求解具有一個(gè)變量的方程，所謂單變量求解，就是求解具有一個(gè)變量的方程，Excel經(jīng)經(jīng)過調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來滿足過調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來滿足目的單元格中的目的值目的單元格中的目的值.2、單變量求解方法、單變量求解方法在任務(wù)表中輸入原始數(shù)據(jù)；在任務(wù)表中輸入原始數(shù)據(jù)；建立可變數(shù)公式；建立可變數(shù)公式；設(shè)置求解公式：菜單設(shè)置求解公式：菜單“工具工具單變量求解單變量求解對(duì)話框中輸入：對(duì)話框中輸入：目的

21、單元格、目的值、可變單元格目的單元格、目的值、可變單元格例：例： 見見ch7.xls單變量求解單變量求解 某公司想向銀行貸款某公司想向銀行貸款900萬元人民幣，貸款利率是萬元人民幣，貸款利率是8.7%，貸款限期為貸款限期為8年，每年應(yīng)歸還多少金額？年，每年應(yīng)歸還多少金額？ 假設(shè)公司每年可歸還假設(shè)公司每年可歸還120萬元，該公司最多可貸款多少金萬元，該公司最多可貸款多少金額？額？ 前一問題可用前一問題可用PMT函數(shù)函數(shù), 后一問題可用單變量求解。后一問題可用單變量求解。7.5 方案分析方案分析1、概念、概念方案是已命名的一組輸入值，是方案是已命名的一組輸入值，是 Excel 保管在任務(wù)表中并可用

22、保管在任務(wù)表中并可用來自動(dòng)交換某個(gè)計(jì)算模型的輸入值，用來預(yù)測(cè)模型的輸出結(jié)果。來自動(dòng)交換某個(gè)計(jì)算模型的輸入值，用來預(yù)測(cè)模型的輸出結(jié)果。 例例:知某茶葉公司知某茶葉公司2019年的總銷售額及各種茶葉的銷售本錢，現(xiàn)要年的總銷售額及各種茶葉的銷售本錢，現(xiàn)要在此根底上制定一個(gè)五年方案。由于市場(chǎng)競(jìng)爭(zhēng)的不斷變化，所在此根底上制定一個(gè)五年方案。由于市場(chǎng)競(jìng)爭(zhēng)的不斷變化，所以只能對(duì)總銷售額及各種茶葉銷售本錢的增長(zhǎng)率做一些估計(jì)。以只能對(duì)總銷售額及各種茶葉銷售本錢的增長(zhǎng)率做一些估計(jì)。最好的方案當(dāng)然是總銷售額增長(zhǎng)率高，各茶葉的銷售本錢增長(zhǎng)最好的方案當(dāng)然是總銷售額增長(zhǎng)率高，各茶葉的銷售本錢增長(zhǎng)率低。率低。 最好的估計(jì)是總

23、銷售額增長(zhǎng)最好的估計(jì)是總銷售額增長(zhǎng)13%，花茶、綠茶、烏龍茶、紅茶，花茶、綠茶、烏龍茶、紅茶的銷售本錢分別增長(zhǎng)的銷售本錢分別增長(zhǎng)10%、6%、10%、7%。 見見ch7.xls方案方案7.5 方案分析方案分析n建立方案處理任務(wù)表建立方案處理任務(wù)表建立方法如下，輸入下表建立方法如下，輸入下表A列、列、B列及第列及第3行的一切數(shù)行的一切數(shù)據(jù)；在據(jù)；在C4單元格中輸入公單元格中輸入公式式“=B4*(1+$B$16)，然后將其復(fù)制到然后將其復(fù)制到D4F4；在在C7中輸入公式中輸入公式“=B7*(1+$B$17),并并將其復(fù)制到將其復(fù)制到D7F7；在在C8中輸入公式中輸入公式“=B8*(1+$B$18)

24、，并將其復(fù)制到并將其復(fù)制到D8和和F8；在在C9中輸入公式中輸入公式“=B9*(1+$B$19)，并將其復(fù)制到并將其復(fù)制到D9F9；在在C10中輸入公式中輸入公式“=B10*(1+$B$20)，并將其復(fù)制到并將其復(fù)制到D10F10；第第11行數(shù)據(jù)是第行數(shù)據(jù)是第7，8，9，10行數(shù)據(jù)對(duì)應(yīng)列之和；凈行數(shù)據(jù)對(duì)應(yīng)列之和；凈收入是相應(yīng)的總銷售額和收入是相應(yīng)的總銷售額和銷售本錢之差，銷售本錢之差，E19的總的總凈收入是第凈收入是第13行數(shù)據(jù)之和。行數(shù)據(jù)之和。 7.5 方案分析方案分析輸入方案變量值如以下圖所示：輸入方案變量值如以下圖所示：7.5 方案分析方案分析2、顯示方案、顯示方案 選擇選擇“工具工具

25、“方案菜單方案菜單選擇選擇“方案管理器對(duì)話框方案管理器對(duì)話框中的某一方案中的某一方案單擊單擊 “顯示按鈕顯示按鈕3、建立方案報(bào)告、建立方案報(bào)告見見ch7.xls方案摘要方案摘要 選擇選擇“工具工具 “方案菜單方案菜單選擇選擇“方案管理器對(duì)話框方案管理器對(duì)話框中的某一方案中的某一方案單擊單擊 “總結(jié)按鈕總結(jié)按鈕在在“方案總結(jié)對(duì)話方案總結(jié)對(duì)話框中結(jié)果類型中選擇框中結(jié)果類型中選擇“方案總結(jié)方案總結(jié)4、建立方案透視圖、建立方案透視圖見見ch7.xls方案數(shù)據(jù)透視圖方案數(shù)據(jù)透視圖 選擇選擇“工具工具 “方案菜單方案菜單選擇選擇“方案管理器對(duì)話框方案管理器對(duì)話框中的某一方案中的某一方案單擊單擊 “總結(jié)按鈕

26、總結(jié)按鈕在在“方案總結(jié)對(duì)話方案總結(jié)對(duì)話框中結(jié)果類型中選擇框中結(jié)果類型中選擇“方案數(shù)據(jù)透視表方案數(shù)據(jù)透視表7.6 線性規(guī)劃求解線性規(guī)劃求解1、概述、概述 EXCEL提供的規(guī)劃求解工具，可求解出線性與非線性兩提供的規(guī)劃求解工具，可求解出線性與非線性兩種規(guī)劃求解問題，規(guī)劃求解問題常用于處理產(chǎn)品比例、人種規(guī)劃求解問題，規(guī)劃求解問題常用于處理產(chǎn)品比例、人員調(diào)度、優(yōu)化道路、調(diào)配資料等方面問題。員調(diào)度、優(yōu)化道路、調(diào)配資料等方面問題。2、規(guī)劃求解問題的特點(diǎn)：、規(guī)劃求解問題的特點(diǎn)：?jiǎn)栴}有單一的目的，如求運(yùn)輸?shù)淖钫_道路、求消費(fèi)的最低問題有單一的目的，如求運(yùn)輸?shù)淖钫_道路、求消費(fèi)的最低本錢、求產(chǎn)品的最大盈利，求產(chǎn)

27、品周期的最短時(shí)間等。本錢、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時(shí)間等。問題有明確的不等式約束條件，例如消費(fèi)資料不能超越庫(kù)存，問題有明確的不等式約束條件，例如消費(fèi)資料不能超越庫(kù)存，消費(fèi)周期不能超越一個(gè)星期等。消費(fèi)周期不能超越一個(gè)星期等。問題有直接或間接影響約束條件的一組輸入值。問題有直接或間接影響約束條件的一組輸入值。 7.6 線性規(guī)劃求解線性規(guī)劃求解3、Excel規(guī)劃求解問題的組成部分規(guī)劃求解問題的組成部分 1一個(gè)或一組可變單元格一個(gè)或一組可變單元格 可變單元格稱為決策變量，一組決策變量代表一個(gè)規(guī)劃求可變單元格稱為決策變量，一組決策變量代表一個(gè)規(guī)劃求解的方案解的方案2目的函數(shù)目的函數(shù)目的函數(shù)表示

28、規(guī)劃求解要到達(dá)的最終目的，是規(guī)劃求解的關(guān)目的函數(shù)表示規(guī)劃求解要到達(dá)的最終目的，是規(guī)劃求解的關(guān)鍵。它是規(guī)劃求解中可變量的函數(shù)鍵。它是規(guī)劃求解中可變量的函數(shù) 3約束條件約束條件約束條件是實(shí)現(xiàn)目的的限制條件。約束條件是實(shí)現(xiàn)目的的限制條件。 意義：經(jīng)過規(guī)劃求解，用戶可為任務(wù)表的目的單元格中意義：經(jīng)過規(guī)劃求解，用戶可為任務(wù)表的目的單元格中的公式找到一個(gè)優(yōu)化值，規(guī)劃求解將直接或間接與目的單的公式找到一個(gè)優(yōu)化值，規(guī)劃求解將直接或間接與目的單元格公式相聯(lián)絡(luò)的一組單元格數(shù)值進(jìn)展調(diào)整，最終在目的元格公式相聯(lián)絡(luò)的一組單元格數(shù)值進(jìn)展調(diào)整，最終在目的單元格公式中求得期望的結(jié)果。單元格公式中求得期望的結(jié)果。7.6 線性規(guī)

29、劃求解線性規(guī)劃求解例：例： 見見ch7.xls規(guī)劃求解規(guī)劃求解某肥料廠專門搜集有機(jī)物渣滓，如青草、樹枝、凋謝的花朵某肥料廠專門搜集有機(jī)物渣滓，如青草、樹枝、凋謝的花朵等。該廠利用這些廢物，并摻進(jìn)不同比例的泥土和礦物質(zhì)等。該廠利用這些廢物，并摻進(jìn)不同比例的泥土和礦物質(zhì)來消費(fèi)高質(zhì)量的植物肥料，消費(fèi)的肥料分為底層肥料、中來消費(fèi)高質(zhì)量的植物肥料，消費(fèi)的肥料分為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料層肥料、上層肥料、劣質(zhì)肥料4種。為使問題簡(jiǎn)單，假設(shè)種。為使問題簡(jiǎn)單，假設(shè)搜集廢物的勞動(dòng)力是自愿的，除了搜集本錢之外，資料本搜集廢物的勞動(dòng)力是自愿的，除了搜集本錢之外，資料本錢是低廉的。錢是低廉的。該廠目前的原

30、資料、消費(fèi)各種肥料需求的原資料比例，各種該廠目前的原資料、消費(fèi)各種肥料需求的原資料比例，各種肥料的單價(jià)等如下頁(yè)各表所示。肥料的單價(jià)等如下頁(yè)各表所示。問題：求出在現(xiàn)有的情況下，即利用原資料的現(xiàn)有庫(kù)存，應(yīng)問題：求出在現(xiàn)有的情況下，即利用原資料的現(xiàn)有庫(kù)存，應(yīng)消費(fèi)各種類型的肥料各多少數(shù)量才干獲得最大利潤(rùn)，最大消費(fèi)各種類型的肥料各多少數(shù)量才干獲得最大利潤(rùn)，最大利潤(rùn)是多少？利潤(rùn)是多少？ 分析分析:所求是在現(xiàn)有的原資料情況下所求是在現(xiàn)有的原資料情況下,應(yīng)如何合理搭配應(yīng)如何合理搭配,才干獲才干獲取消費(fèi)產(chǎn)品的最大利潤(rùn)取消費(fèi)產(chǎn)品的最大利潤(rùn).7.6 線性規(guī)劃求解線性規(guī)劃求解表表2 消費(fèi)肥料的庫(kù)存原資料消費(fèi)肥料的庫(kù)存

31、原資料庫(kù)存庫(kù)存情況情況現(xiàn)有現(xiàn)有庫(kù)存庫(kù)存泥土泥土4100有機(jī)有機(jī)垃圾垃圾3200礦物礦物質(zhì)質(zhì)3500修剪修剪物物表1 各肥料廢品用料及其價(jià)錢表表的意思是消費(fèi)一個(gè)單位的肥料需求多少各種原資料多少單位產(chǎn)品產(chǎn)品泥土泥土有機(jī)垃圾有機(jī)垃圾礦物質(zhì)礦物質(zhì)修剪物修剪物單價(jià)單價(jià)底層肥料底層肥料55547623105.00中層肥料中層肥料6432452084.00上層肥料上層肥料43329844105.00劣質(zhì)肥料劣質(zhì)肥料1845231857.00表表3單位原資料本錢單價(jià)單位原資料本錢單價(jià)項(xiàng)項(xiàng) 目目單位成本單位成本泥土泥土0.20有機(jī)垃圾有機(jī)垃圾0.15礦物質(zhì)礦物質(zhì)0.10修剪物修剪物0.237.6 線性規(guī)劃求解線

32、性規(guī)劃求解n建立規(guī)劃求解模型步驟：建立規(guī)劃求解模型步驟：n規(guī)劃求解第一步規(guī)劃求解第一步建立求解任務(wù)表建立求解任務(wù)表(輸入原始數(shù)據(jù)及相應(yīng)輸入原始數(shù)據(jù)及相應(yīng)的各公式的各公式)7.6 線性規(guī)劃求解線性規(guī)劃求解規(guī)劃求解第二步規(guī)劃求解第二步設(shè)置求解參數(shù)設(shè)置求解參數(shù)選擇選擇“工具工具 “規(guī)劃求解菜單，設(shè)置以下求解的各項(xiàng)參規(guī)劃求解菜單，設(shè)置以下求解的各項(xiàng)參數(shù)：數(shù)：設(shè)置目的單元格：輸入目的函數(shù)所在單元格設(shè)置目的單元格：輸入目的函數(shù)所在單元格(為總余額單元為總余額單元格格)設(shè)置目的：最大值、最小值或值的數(shù)值設(shè)置目的：最大值、最小值或值的數(shù)值(最大利潤(rùn)最大利潤(rùn),即最大值即最大值)設(shè)置可變單元格：它確實(shí)定決議結(jié)果為

33、消費(fèi)數(shù)量設(shè)置可變單元格：它確實(shí)定決議結(jié)果為消費(fèi)數(shù)量設(shè)置約束條件：?jiǎn)螕粼O(shè)置約束條件：?jiǎn)螕簟疤砑影粹o添加按鈕輸入約束條件輸入約束條件按添按添加加依次輸入一切約束條件依次輸入一切約束條件確定確定7.6 線性規(guī)劃求解線性規(guī)劃求解n規(guī)劃求解第規(guī)劃求解第3步步保管求解結(jié)果保管求解結(jié)果n在規(guī)劃求解對(duì)話框中按在規(guī)劃求解對(duì)話框中按“求解求解在規(guī)劃求解結(jié)果對(duì)話在規(guī)劃求解結(jié)果對(duì)話框中按框中按“保管規(guī)劃求解結(jié)果保管規(guī)劃求解結(jié)果7.6 線性規(guī)劃求解線性規(guī)劃求解4、修正資源、修正資源 例例1：見見ch7.xls規(guī)劃求解規(guī)劃求解肥料廠接到一個(gè)：只需公司肯花肥料廠接到一個(gè)：只需公司肯花10元的運(yùn)費(fèi)就能得到元的運(yùn)費(fèi)就能得到15

34、0個(gè)個(gè)單位的礦物。這筆買賣稍稍降低了礦物質(zhì)的平均價(jià)錢，但單位的礦物。這筆買賣稍稍降低了礦物質(zhì)的平均價(jià)錢，但這些礦物質(zhì)值這些礦物質(zhì)值10元嗎？元嗎？處理該問題的方法是，將庫(kù)存礦物處理該問題的方法是，將庫(kù)存礦物3500改為改為3650，用規(guī)劃，用規(guī)劃求解重新計(jì)算最大盈余?？闯ィで蠼庵匦掠?jì)算最大盈余?？闯ィ?0的本錢后，盈余能的本錢后，盈余能否添加否添加 操作操作: 將庫(kù)存礦物將庫(kù)存礦物3500改為改為3650,其它一切公式不變其它一切公式不變,再次進(jìn)展再次進(jìn)展求解求解,求得盈余額為求得盈余額為4483.41,原盈余額為原盈余額為4425.89. 可知盈利為可知盈利為57.52.扣除扣除10元本

35、錢后仍有元本錢后仍有47.52.因此該礦物因此該礦物還是要的還是要的.7.6 線性規(guī)劃求解線性規(guī)劃求解5、修正約束條件、修正約束條件 見見ch7.xls規(guī)劃求解規(guī)劃求解肥料廠接到一個(gè)，一個(gè)老顧客急需肥料廠接到一個(gè)，一個(gè)老顧客急需25個(gè)單位的上層肥料，個(gè)單位的上層肥料，公司經(jīng)理在檢查打印結(jié)果后，發(fā)現(xiàn)沒有安排消費(fèi)上層肥料。公司經(jīng)理在檢查打印結(jié)果后，發(fā)現(xiàn)沒有安排消費(fèi)上層肥料。數(shù)量為數(shù)量為0。決議添加約束條件，為他消費(fèi)。決議添加約束條件，為他消費(fèi)25個(gè)單位的上層個(gè)單位的上層肥料。肥料。結(jié)果可發(fā)現(xiàn)結(jié)果可發(fā)現(xiàn):盈余額僅盈余額僅3246.51,比原來比原來4483.41少了少了1236.9。顯然不值得。但如

36、該顧客為長(zhǎng)期顧客，那么短。顯然不值得。但如該顧客為長(zhǎng)期顧客，那么短期內(nèi)將損失一些錢，但得到了顧客的信任。期內(nèi)將損失一些錢，但得到了顧客的信任。添加的添加的約束條約束條件件7.6 線性規(guī)劃求解線性規(guī)劃求解6、 規(guī)劃求解的結(jié)果報(bào)告規(guī)劃求解的結(jié)果報(bào)告運(yùn)算結(jié)果報(bào)告：列出目的單元格、可變單元格及它們的初始值、運(yùn)算結(jié)果報(bào)告：列出目的單元格、可變單元格及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。最終結(jié)果、約束條件和有關(guān)約束條件的信息。 見見ch7.xls運(yùn)算結(jié)果報(bào)告運(yùn)算結(jié)果報(bào)告7.6 線性規(guī)劃求解線性規(guī)劃求解n敏感性報(bào)告：敏感性報(bào)告： 見見ch7.xls敏感性報(bào)告敏感性報(bào)告7.6 線性規(guī)劃求解線

37、性規(guī)劃求解n極限報(bào)告：列出目的單元格、可變單元格及它們的數(shù)極限報(bào)告：列出目的單元格、可變單元格及它們的數(shù)值、上下限和目的值。下限為在滿足約束條件和堅(jiān)持值、上下限和目的值。下限為在滿足約束條件和堅(jiān)持其它可變單元格數(shù)值不變的情況下，某個(gè)可變單元格其它可變單元格數(shù)值不變的情況下，某個(gè)可變單元格可以獲得的最小值，上限那么為在這種情況下可以取可以獲得的最小值，上限那么為在這種情況下可以取到的最大值。到的最大值。 見見ch7.xls極限值報(bào)告極限值報(bào)告7.6 線性規(guī)劃求解線性規(guī)劃求解7、求解精度及求解模型設(shè)置、求解精度及求解模型設(shè)置 Excel采用迭代的方式進(jìn)展規(guī)劃求解，當(dāng)求解到一定精度采用迭代的方式進(jìn)展

38、規(guī)劃求解，當(dāng)求解到一定精度時(shí)就終了求解，但有時(shí)要修正求解的精度、計(jì)算時(shí)間、規(guī)時(shí)就終了求解，但有時(shí)要修正求解的精度、計(jì)算時(shí)間、規(guī)劃模型和迭代次數(shù)。修正上述設(shè)置的方法如下：劃模型和迭代次數(shù)。修正上述設(shè)置的方法如下： 在在“規(guī)劃求解參數(shù)對(duì)話框中設(shè)置好各項(xiàng)求解參數(shù)；規(guī)劃求解參數(shù)對(duì)話框中設(shè)置好各項(xiàng)求解參數(shù)； 單擊單擊“選項(xiàng)按鈕，在選項(xiàng)按鈕，在“規(guī)劃求解選項(xiàng)對(duì)話框中設(shè)置各規(guī)劃求解選項(xiàng)對(duì)話框中設(shè)置各項(xiàng)求解參數(shù)。項(xiàng)求解參數(shù)。 7.6 線性規(guī)劃求解線性規(guī)劃求解例例2：求解不等式：求解不等式：見見ch7.xls規(guī)劃求解不等式規(guī)劃求解不等式某工廠消費(fèi)甲、乙兩種產(chǎn)品，假設(shè)消費(fèi)甲產(chǎn)品某工廠消費(fèi)甲、乙兩種產(chǎn)品，假設(shè)消費(fèi)甲

39、產(chǎn)品1噸，要耗費(fèi)噸，要耗費(fèi)9噸噸煤，煤，4千瓦電力，千瓦電力，3噸鋼材，獲利噸鋼材，獲利0.7萬元；消費(fèi)乙產(chǎn)品萬元；消費(fèi)乙產(chǎn)品1噸，要噸，要耗費(fèi)耗費(fèi)4噸煤，噸煤，5千瓦電力，千瓦電力，10噸鋼材，獲利噸鋼材，獲利1.2萬元。按方案國(guó)萬元。按方案國(guó)家能提供應(yīng)該廠的煤為家能提供應(yīng)該廠的煤為360噸，電力噸，電力200千瓦，鋼材千瓦，鋼材300噸，問噸，問應(yīng)該消費(fèi)多少噸甲種產(chǎn)品和乙種產(chǎn)品，才干獲得最大利潤(rùn)？應(yīng)該消費(fèi)多少噸甲種產(chǎn)品和乙種產(chǎn)品，才干獲得最大利潤(rùn)？ 假設(shè)消費(fèi)甲種產(chǎn)品假設(shè)消費(fèi)甲種產(chǎn)品X1噸，消費(fèi)乙種產(chǎn)品噸，消費(fèi)乙種產(chǎn)品x2噸，噸， 其最大利潤(rùn)是求其最大利潤(rùn)是求=0.7x1+1.2x2的最大值

40、。這個(gè)問題可用數(shù)的最大值。這個(gè)問題可用數(shù)學(xué)建模如下：學(xué)建模如下：.0,0.300103.20054.3604921212121xxxxxxxx7.6 線性規(guī)劃求解線性規(guī)劃求解規(guī)劃求解如下：規(guī)劃求解如下：B3和和C3分別用于保管甲和乙產(chǎn)品的消費(fèi)量。分別用于保管甲和乙產(chǎn)品的消費(fèi)量。目的單元格為目的單元格為B8；可變單元格為；可變單元格為$B$3:$C$3；約束條件為：；約束條件為：$B$3=0$C$3=0$B$4=360$B$5=200$B$6=3007.6 線性規(guī)劃求解線性規(guī)劃求解n例例3： 見見ch7.xls線形規(guī)劃求解線形規(guī)劃求解n某公司在某公司在A地有一個(gè)消費(fèi)基地地有一個(gè)消費(fèi)基地,其消費(fèi)才

41、干為其消費(fèi)才干為400,隨著市隨著市場(chǎng)需求的增長(zhǎng)及該公司業(yè)務(wù)量的增大場(chǎng)需求的增長(zhǎng)及該公司業(yè)務(wù)量的增大,現(xiàn)有現(xiàn)有3個(gè)配送中心的個(gè)配送中心的需求都在增長(zhǎng)需求都在增長(zhǎng),估計(jì)分別為估計(jì)分別為200,400,300。公司正思索再。公司正思索再建立一個(gè)消費(fèi)才干為建立一個(gè)消費(fèi)才干為500的工廠，預(yù)備建在的工廠，預(yù)備建在B地。從地。從A地地的工廠向的工廠向3個(gè)配送中心的單位運(yùn)輸本錢分別為個(gè)配送中心的單位運(yùn)輸本錢分別為5.0元，元，6.0元，元，5.4元，從元，從B地的工廠向地的工廠向3個(gè)配送中心的單位運(yùn)輸本錢個(gè)配送中心的單位運(yùn)輸本錢是是7.0元，元，4.6元，元，6.6元。應(yīng)怎樣分配元。應(yīng)怎樣分配A、B兩地到

42、兩地到3個(gè)配個(gè)配送中心的產(chǎn)品量，才干使運(yùn)輸本錢最?。克椭行牡漠a(chǎn)品量，才干使運(yùn)輸本錢最??？n規(guī)劃模型解釋如下：規(guī)劃模型解釋如下：7.6 線性規(guī)劃求解線性規(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廠的消費(fèi)才干， H9是A，B兩廠的總消費(fèi)才干7.6 線性規(guī)劃求解線

43、性規(guī)劃求解G6G6，G8G8分別為分別為A A，B B兩廠各自的產(chǎn)品總量。兩廠各自的產(chǎn)品總量。G6G6公式：公式：=D6+E6+F6=D6+E6+F6；G8G8公式：公式：=G8+E8+F8=G8+E8+F8；D9D9，E9E9，F(xiàn)9F9分別為兩廠送到各配送中心的產(chǎn)品總量。分別為兩廠送到各配送中心的產(chǎn)品總量。D9D9公式：公式：=D6+D8=D6+D8；E9E9公式：公式：=E6+E8=E6+E8；F9F9公式：公式：=F6+F8=F6+F8；本模型的約束條件分析如下：本模型的約束條件分析如下：A A，B B兩廠送到各配送中心的產(chǎn)品總量不能超越各配送中心的負(fù)兩廠送到各配送中心的產(chǎn)品總量不能超越

44、各配送中心的負(fù)荷才干：荷才干：D9D9：F9=D10F9=0F6=0，D8:F8=0D8:F8=0本模型的目的函數(shù)，求以下公式本模型的目的函數(shù)，求以下公式的最大值：的最大值：S=D5S=D5* *D6+E5D6+E5* *E6+F5E6+F5* *F6+D7F6+D7* *D8+E7D8+E7* *E8+F7E8+F7* *F8F87.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)1、概述、概述Excel提供了一組數(shù)據(jù)分析工具，稱為分析工具庫(kù)。其中提提供了一組數(shù)據(jù)分析工具，稱為分析工具庫(kù)。其中提供的分析工具在工程分析、數(shù)理統(tǒng)計(jì)、經(jīng)濟(jì)計(jì)量分析等學(xué)供的分析工具在工程分析、數(shù)理統(tǒng)計(jì)、經(jīng)濟(jì)計(jì)量分析等學(xué)科中有較強(qiáng)的適

45、用價(jià)值。科中有較強(qiáng)的適用價(jià)值。分析工具庫(kù)由分析工具庫(kù)由Excel自帶的加載宏提供。自帶的加載宏提供。 假設(shè)啟動(dòng)假設(shè)啟動(dòng)Excel后，在后，在Excel的的“工具菜單中沒有工具菜單中沒有“數(shù)據(jù)分?jǐn)?shù)據(jù)分析菜單項(xiàng)，就需啟動(dòng)析菜單項(xiàng)，就需啟動(dòng)“工具中的工具中的“加載宏菜單項(xiàng)，將加載宏菜單項(xiàng)，將“分析工具庫(kù)加載到分析工具庫(kù)加載到Excel系統(tǒng)中。系統(tǒng)中。假設(shè)加載宏對(duì)話框中沒有分析工具庫(kù)，那么單擊加載宏對(duì)話假設(shè)加載宏對(duì)話框中沒有分析工具庫(kù)，那么單擊加載宏對(duì)話框中框中“閱讀按鈕，定位到分析工具庫(kù)加載宏文件閱讀按鈕，定位到分析工具庫(kù)加載宏文件“Analy32.dll所在的驅(qū)動(dòng)器和文件夾，通常位于所在的驅(qū)動(dòng)器和

46、文件夾，通常位于“Microsoft OfficeOfficeLibraryAnalysis中中,否那否那么需運(yùn)轉(zhuǎn)么需運(yùn)轉(zhuǎn)Office系統(tǒng)的安裝程序。系統(tǒng)的安裝程序。Excel的的“分析工具庫(kù)加載宏提供的一些統(tǒng)計(jì)函數(shù)、財(cái)務(wù)函分析工具庫(kù)加載宏提供的一些統(tǒng)計(jì)函數(shù)、財(cái)務(wù)函數(shù)和工程函數(shù)。這些函數(shù)只需在安裝了數(shù)和工程函數(shù)。這些函數(shù)只需在安裝了“分析工具庫(kù)后分析工具庫(kù)后才干運(yùn)用才干運(yùn)用 。7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)2、Excel分析工具庫(kù)中的工具分析工具庫(kù)中的工具分析工具分析工具名稱名稱說說 明明方差分析方差分析 包括包括3種類型的分析，它們是單因素方差分析、可種類型的分析，它們是單因素方差分析

47、、可重復(fù)雙因素分析、無重復(fù)雙因素分析重復(fù)雙因素分析、無重復(fù)雙因素分析相關(guān)系數(shù)相關(guān)系數(shù)分析分析用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。之間的關(guān)系。協(xié)方差分協(xié)方差分析析用于返回各數(shù)據(jù)點(diǎn)的一對(duì)均值偏差之間的乘積的用于返回各數(shù)據(jù)點(diǎn)的一對(duì)均值偏差之間的乘積的平均值。平均值。描述統(tǒng)計(jì)描述統(tǒng)計(jì)分析分析用于生成對(duì)輸入?yún)^(qū)域中數(shù)據(jù)的單變值分析，提供用于生成對(duì)輸入?yún)^(qū)域中數(shù)據(jù)的單變值分析，提供有關(guān)數(shù)據(jù)趨中性和易變性的信息有關(guān)數(shù)據(jù)趨中性和易變性的信息指數(shù)平滑指數(shù)平滑分析分析基于前期預(yù)測(cè)值導(dǎo)出相應(yīng)的新預(yù)測(cè)值，并修正前基于前期預(yù)測(cè)值導(dǎo)出相應(yīng)的新預(yù)測(cè)值，并修正前期預(yù)

48、測(cè)值的誤差。期預(yù)測(cè)值的誤差。7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)傅里葉分傅里葉分析析解決線性系統(tǒng)問題，并能通過快速傅里葉變換解決線性系統(tǒng)問題，并能通過快速傅里葉變換（FFT）分析周期性的數(shù)據(jù)。）分析周期性的數(shù)據(jù)。F-檢驗(yàn)檢驗(yàn)用來比較兩個(gè)樣本總體的方差用來比較兩個(gè)樣本總體的方差直方圖分直方圖分析析在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情況下，計(jì)算數(shù)據(jù)的個(gè)別和累計(jì)頻率況下，計(jì)算數(shù)據(jù)的個(gè)別和累計(jì)頻率移動(dòng)平均移動(dòng)平均分析分析基于特定的過去某段時(shí)期中變量的均值，對(duì)未來基于特定的過去某段時(shí)期中變量的均值，對(duì)未來值進(jìn)行預(yù)測(cè)值進(jìn)行預(yù)測(cè)t-檢驗(yàn)分檢驗(yàn)分析析提供提供3種

49、不同檢驗(yàn)：雙樣本等方差假設(shè)種不同檢驗(yàn)：雙樣本等方差假設(shè)t-檢驗(yàn)，雙檢驗(yàn)，雙樣本異方差假設(shè)樣本異方差假設(shè)t-檢驗(yàn)，平均值的成對(duì)雙樣本檢驗(yàn)，平均值的成對(duì)雙樣本t-檢驗(yàn)檢驗(yàn)回歸分析回歸分析通過對(duì)一組觀察值使用通過對(duì)一組觀察值使用“最小二乘法最小二乘法”直線擬合，直線擬合，進(jìn)行線形回歸分析。進(jìn)行線形回歸分析。抽樣分析抽樣分析以輸入?yún)^(qū)域?yàn)榭傮w構(gòu)造總體的一個(gè)樣本以輸入?yún)^(qū)域?yàn)榭傮w構(gòu)造總體的一個(gè)樣本z-檢驗(yàn)檢驗(yàn)雙樣本平均差檢驗(yàn)雙樣本平均差檢驗(yàn)7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)3、 統(tǒng)計(jì)分析統(tǒng)計(jì)分析 Excel的分析工具庫(kù)提供了的分析工具庫(kù)提供了3種統(tǒng)計(jì)觀測(cè)分析工具：指數(shù)平種統(tǒng)計(jì)觀測(cè)分析工具：指數(shù)平滑分析、挪

50、動(dòng)平均分析和回歸分析滑分析、挪動(dòng)平均分析和回歸分析 三種工具用法一樣，下面以指數(shù)平滑分析為例。三種工具用法一樣，下面以指數(shù)平滑分析為例。見見ch7.xls 指數(shù)平滑分析指數(shù)平滑分析1在任務(wù)表的一列上輸入各時(shí)間點(diǎn)上的察看值，如以下在任務(wù)表的一列上輸入各時(shí)間點(diǎn)上的察看值，如以下圖圖A列所示。列所示。 7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)2 2選擇選擇“工具菜單工具菜單“數(shù)據(jù)分析數(shù)據(jù)分析 選項(xiàng)，對(duì)話框中選擇選項(xiàng)，對(duì)話框中選擇“指指數(shù)平滑按確定。數(shù)平滑按確定。 3 3在在“指數(shù)平滑對(duì)話框中設(shè)置指數(shù)平滑對(duì)話框中設(shè)置“輸入?yún)^(qū)域、輸入?yún)^(qū)域、“阻尼系數(shù)阻尼系數(shù)、“輸出區(qū)域選項(xiàng)。輸出區(qū)域選項(xiàng)。4 4選定對(duì)話框中

51、選定對(duì)話框中“圖表輸出和圖表輸出和“規(guī)范誤差復(fù)選框標(biāo)志。規(guī)范誤差復(fù)選框標(biāo)志。分析結(jié)果：分析結(jié)果：B B列為分析之后輸出的預(yù)測(cè)數(shù)據(jù)；列為分析之后輸出的預(yù)測(cè)數(shù)據(jù)；C C列是分析工具列是分析工具輸出的規(guī)范誤差。輸出的規(guī)范誤差。7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)4、假設(shè)檢驗(yàn)、假設(shè)檢驗(yàn) 假設(shè)檢驗(yàn)是根據(jù)對(duì)事物進(jìn)展抽樣所得的少量樣本信息，判別假設(shè)檢驗(yàn)是根據(jù)對(duì)事物進(jìn)展抽樣所得的少量樣本信息，判別總體分布的某個(gè)假設(shè)能否成立的一種數(shù)理統(tǒng)計(jì)方法?？傮w分布的某個(gè)假設(shè)能否成立的一種數(shù)理統(tǒng)計(jì)方法。假設(shè)分析工具有三種：假設(shè)分析工具有三種：t-檢驗(yàn)、檢驗(yàn)、z-檢驗(yàn)、檢驗(yàn)、F-檢驗(yàn)。檢驗(yàn)。運(yùn)用這些檢驗(yàn)工具可以完成均值、方差的

52、假設(shè)檢驗(yàn)。運(yùn)用這些檢驗(yàn)工具可以完成均值、方差的假設(shè)檢驗(yàn)。 方法方法見下例見下例7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)n例：例： 見見ch7.xlst檢驗(yàn)檢驗(yàn)n 雙樣本等方差雙樣本等方差t-檢驗(yàn)，以確定兩個(gè)樣本均值實(shí)踐上檢驗(yàn)，以確定兩個(gè)樣本均值實(shí)踐上能否相等能否相等n 某種子公司為比較兩個(gè)稻種的產(chǎn)量，選擇了某種子公司為比較兩個(gè)稻種的產(chǎn)量，選擇了25塊條件塊條件類似的實(shí)驗(yàn)田，采用一樣的耕種方法進(jìn)展耕種實(shí)驗(yàn)，類似的實(shí)驗(yàn)田，采用一樣的耕種方法進(jìn)展耕種實(shí)驗(yàn)，結(jié)果播種甲稻種的結(jié)果播種甲稻種的13塊田的畝產(chǎn)量單位：市斤分塊田的畝產(chǎn)量單位：市斤分別是：別是：880、1 120、980、885、828、927、9

53、24、942、766、1 180、780、1 068、650；播種乙稻；播種乙稻種的種的12塊實(shí)驗(yàn)田的畝產(chǎn)量分別是：塊實(shí)驗(yàn)田的畝產(chǎn)量分別是：940、1 142、1 020、785、645、780、1 180、680、810、824、846、780。問這兩個(gè)稻種的產(chǎn)量有沒有明顯的高低。問這兩個(gè)稻種的產(chǎn)量有沒有明顯的高低之分。之分。 n闡明：要判別兩稻種有無顯著差別，用闡明：要判別兩稻種有無顯著差別，用t-檢驗(yàn)方法，檢驗(yàn)方法，需先計(jì)算各樣本的平均值和方差，才干作進(jìn)一步的檢需先計(jì)算各樣本的平均值和方差，才干作進(jìn)一步的檢驗(yàn)分析。驗(yàn)分析。7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)nt-t-檢驗(yàn)操作過程：檢驗(yàn)操

54、作過程：n(1)(1)輸入輸入A A、B B兩列樣本數(shù)據(jù)下表中右邊數(shù)據(jù)全為產(chǎn)生的兩列樣本數(shù)據(jù)下表中右邊數(shù)據(jù)全為產(chǎn)生的分析結(jié)果分析結(jié)果7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)n(2)(2)選擇選擇“工具菜單中工具菜單中“數(shù)據(jù)分析選項(xiàng)，對(duì)話框中選擇數(shù)據(jù)分析選項(xiàng)，對(duì)話框中選擇t t檢驗(yàn)雙樣本等方差假設(shè)；檢驗(yàn)雙樣本等方差假設(shè)；n3 3在在“雙樣本等方差假設(shè)分析對(duì)話框中設(shè)置雙樣本等方差假設(shè)分析對(duì)話框中設(shè)置t t檢驗(yàn)的檢驗(yàn)的各項(xiàng)參數(shù)各項(xiàng)參數(shù)n按確定按確定7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)5、回歸分析、回歸分析回歸分析主要用于分析單個(gè)因變量是如何受一個(gè)或幾個(gè)自變量影回歸分析主要用于分析單個(gè)因變量是如何受一個(gè)或

55、幾個(gè)自變量影響的。如察看某個(gè)運(yùn)發(fā)動(dòng)的運(yùn)動(dòng)成果與一系列統(tǒng)計(jì)要素的關(guān)系。響的。如察看某個(gè)運(yùn)發(fā)動(dòng)的運(yùn)動(dòng)成果與一系列統(tǒng)計(jì)要素的關(guān)系。如年齡、體重、身高等。如年齡、體重、身高等。回歸分析分為線性回歸和非線性回歸兩種。線性回歸的數(shù)學(xué)模型回歸分析分為線性回歸和非線性回歸兩種。線性回歸的數(shù)學(xué)模型為：為：Excel經(jīng)過對(duì)一組察看值運(yùn)用經(jīng)過對(duì)一組察看值運(yùn)用“最小二乘法直線擬合，進(jìn)展線性最小二乘法直線擬合，進(jìn)展線性回歸分析，該回歸分析可同時(shí)處理一元回歸與多元回歸問題?；貧w分析，該回歸分析可同時(shí)處理一元回歸與多元回歸問題。iiiyxu7.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)n例：用一個(gè)多元回歸線性分析例子來闡明回歸分析工

56、具的運(yùn)用方法nch7.xls回歸分析數(shù)據(jù)表中，列出了美國(guó)19561970年間歷年的人均可支配收入xi和人均可消費(fèi)支出yi的數(shù)據(jù)。試用圖中的數(shù)據(jù)擬合模型。n模型中的趨勢(shì)變量t，用于反映除人均收入之外的一切其他要素對(duì)人均消費(fèi)的影響 tttutxy2107.7 數(shù)據(jù)分析工具庫(kù)數(shù)據(jù)分析工具庫(kù)n利用回歸分析工具求解此模型的方法利用回歸分析工具求解此模型的方法n輸入原始數(shù)據(jù)表；輸入原始數(shù)據(jù)表；n選擇選擇“工具菜單中工具菜單中“數(shù)據(jù)分析選項(xiàng)，在數(shù)據(jù)分析選項(xiàng)，在“數(shù)據(jù)分析對(duì)數(shù)據(jù)分析對(duì)話框中選擇話框中選擇“回歸列表。系統(tǒng)彈出如下對(duì)話框；回歸列表。系統(tǒng)彈出如下對(duì)話框；n在在“回歸對(duì)話框中輸入因變量回歸對(duì)話框中輸入

57、因變量y和自變量和自變量x的數(shù)據(jù)區(qū)域；的數(shù)據(jù)區(qū)域；n假設(shè)需求線性擬合的假設(shè)需求線性擬合的“殘差圖和殘差圖和“線性擬合圖等，那么線性擬合圖等，那么需選擇相應(yīng)復(fù)選框需選擇相應(yīng)復(fù)選框n此題結(jié)果見此題結(jié)果見見見ch7.xls回歸分析的輸出結(jié)果回歸分析的輸出結(jié)果綜合實(shí)例綜合實(shí)例1.單變量模擬運(yùn)算表實(shí)例單變量模擬運(yùn)算表實(shí)例超市要進(jìn)展一些改革，如對(duì)某些產(chǎn)品采取分期付款的方式進(jìn)超市要進(jìn)展一些改革，如對(duì)某些產(chǎn)品采取分期付款的方式進(jìn)展銷售。假設(shè)有一液晶電視，每臺(tái)售價(jià)為展銷售。假設(shè)有一液晶電視，每臺(tái)售價(jià)為98,000，采用，采用分期付款的方式進(jìn)展銷售，初步確定分期付款的方式為零分期付款的方式進(jìn)展銷售，初步確定分期付

58、款的方式為零首付，月分期手續(xù)費(fèi)率為首付，月分期手續(xù)費(fèi)率為0.7，求在不同的分期付款期，求在不同的分期付款期數(shù)月下，每期月消費(fèi)者需求付款的金額數(shù)月下，每期月消費(fèi)者需求付款的金額 綜合實(shí)例綜合實(shí)例n【實(shí)例操作步驟】【實(shí)例操作步驟】n詳細(xì)操作步驟如下：詳細(xì)操作步驟如下：n步驟步驟1：創(chuàng)建任務(wù)表，建立根本的模擬運(yùn)算任務(wù)表，輸入：創(chuàng)建任務(wù)表，建立根本的模擬運(yùn)算任務(wù)表，輸入必要的數(shù)據(jù)及要測(cè)試的任務(wù)表中的數(shù)據(jù)，即不同的分期付必要的數(shù)據(jù)及要測(cè)試的任務(wù)表中的數(shù)據(jù)，即不同的分期付款期數(shù)，如下圖?？钇跀?shù)，如下圖。 綜合實(shí)例綜合實(shí)例n步驟步驟2：創(chuàng)建運(yùn)算公式：創(chuàng)建運(yùn)算公式n在單元格在單元格D3中輸入公式中輸入公式“=

59、PMTB5,C3,B2，得出，得出當(dāng)分期付款期數(shù)為當(dāng)分期付款期數(shù)為C3單元格中的數(shù)值時(shí)，消費(fèi)者每月的單元格中的數(shù)值時(shí)，消費(fèi)者每月的付款額。如圖付款額。如圖7-2所示。公式中所示。公式中B5為月分期手續(xù)費(fèi)率，為月分期手續(xù)費(fèi)率，C3為分期付款期數(shù)，以月為單位，為分期付款期數(shù)，以月為單位，B2為付款本金。為付款本金。綜合實(shí)例綜合實(shí)例n步驟步驟3：建立單變量模擬運(yùn)算表：建立單變量模擬運(yùn)算表n首先要選定公式、數(shù)值序列和模擬運(yùn)算結(jié)果所在的單元格首先要選定公式、數(shù)值序列和模擬運(yùn)算結(jié)果所在的單元格區(qū)域，即區(qū)域，即C3:D12，以定義這個(gè)模擬運(yùn)算表，然后選擇，以定義這個(gè)模擬運(yùn)算表，然后選擇“數(shù)據(jù)菜單中的數(shù)據(jù)菜單

60、中的“模擬運(yùn)算表命令，翻開模擬運(yùn)算表命令，翻開“模擬運(yùn)算表模擬運(yùn)算表對(duì)話框，在對(duì)話框，在“模擬運(yùn)算表對(duì)話框的模擬運(yùn)算表對(duì)話框的“輸入援用列的單元輸入援用列的單元格文本框中輸入第一個(gè)變量所在的單元格地址格文本框中輸入第一個(gè)變量所在的單元格地址“$C$3，如下圖。如下圖。綜合實(shí)例綜合實(shí)例n【實(shí)例操作結(jié)果】【實(shí)例操作結(jié)果】n最終的計(jì)算結(jié)果如下圖。最終的計(jì)算結(jié)果如下圖。 綜合實(shí)例綜合實(shí)例n雙變量模擬運(yùn)算表實(shí)例雙變量模擬運(yùn)算表實(shí)例n在前面的例子中，僅僅把分期付款期數(shù)作為單變量進(jìn)展模在前面的例子中，僅僅把分期付款期數(shù)作為單變量進(jìn)展模擬運(yùn)算，但在現(xiàn)實(shí)生活中，月分期付款手續(xù)費(fèi)率也是經(jīng)常擬運(yùn)算，但在現(xiàn)實(shí)生活中，