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

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

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

3、具的安裝1、加載宏的概念加載宏的概念n加載宏是一種可選擇性地安裝到計算機中的軟件組加載宏是一種可選擇性地安裝到計算機中的軟件組件，用戶可根據(jù)需要決定是否安裝。其作用是為件，用戶可根據(jù)需要決定是否安裝。其作用是為 Excel Excel 添加命令和函數(shù)，擴充添加命令和函數(shù)，擴充ExcelExcel的功能。的功能。nExcelExcel加載宏的擴展名是加載宏的擴展名是. .xlaxla或或. .xllxll。n在默認情況下，在默認情況下，ExcelExcel將下表列出的加載宏程序安裝將下表列出的加載宏程序安裝在如下某一磁盤位置：在如下某一磁盤位置：“Microsoft OfficeOffice”M

4、icrosoft OfficeOffice”文件夾下的文件夾下的“Library”Library”文件夾或其子文件夾，或文件夾或其子文件夾，或 Windows Windows 所在文件夾下的所在文件夾下的“ProfilesProfiles用戶名用戶名Application DataMicrosoftApplication DataMicrosoftAddInsAddIns”文件夾下。文件夾下。網(wǎng)絡(luò)管理員也可將加載宏程序安裝到其他位置。網(wǎng)絡(luò)管理員也可將加載宏程序安裝到其他位置。 7.1 分析工具的安裝分析工具的安裝2、ExcelExcel內(nèi)置加載宏內(nèi)置加載宏加加 載載 宏宏描描 述述分析工具分析

5、工具庫庫添加財務(wù)、統(tǒng)計和工程分析工具和函數(shù)添加財務(wù)、統(tǒng)計和工程分析工具和函數(shù)條件求和條件求和向?qū)驅(qū)τ跀?shù)據(jù)清單中滿足指定條件的數(shù)據(jù)進行求和對于數(shù)據(jù)清單中滿足指定條件的數(shù)據(jù)進行求和計算計算歐元工具歐元工具將數(shù)值的格式設(shè)置為歐元的格式，并提供將數(shù)值的格式設(shè)置為歐元的格式，并提供EUROCONVERT函數(shù)以用于轉(zhuǎn)換貨幣函數(shù)以用于轉(zhuǎn)換貨幣查閱向?qū)Р殚喯驅(qū)?chuàng)建一個公式，通過數(shù)據(jù)清單中的已知值查找創(chuàng)建一個公式，通過數(shù)據(jù)清單中的已知值查找所需數(shù)據(jù)所需數(shù)據(jù)ODBC 加加載宏載宏利用安裝的利用安裝的 ODBC 驅(qū)動程序，通過開放式數(shù)據(jù)驅(qū)動程序，通過開放式數(shù)據(jù)庫互連（庫互連（ODBC）功能與外部數(shù)據(jù)源相連）功能

6、與外部數(shù)據(jù)源相連7.1 分析工具的安裝分析工具的安裝報告管理報告管理器器為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視面為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視面以及方案的報告以及方案的報告規(guī)劃求解規(guī)劃求解對基于可變單元格和條件單元格的假設(shè)分析方對基于可變單元格和條件單元格的假設(shè)分析方案進行求解計算案進行求解計算模板工具模板工具提供提供 Excel 的內(nèi)置模板所使用的工具。使用內(nèi)置的內(nèi)置模板所使用的工具。使用內(nèi)置模板時就可自動訪問這些工具模板時就可自動訪問這些工具Internet Assistant VBA通過使用通過使用 Excel 97 Internet Assistant 語法，開語法，開發(fā)者可將

7、發(fā)者可將 Excel 數(shù)據(jù)發(fā)布到數(shù)據(jù)發(fā)布到 Web 上上7.1 分析工具的安裝分析工具的安裝3、安裝分析工具安裝分析工具n選擇選擇“工具工具”|“|“加載宏加載宏”菜單菜單在對話框中選擇所需在對話框中選擇所需工具，按確定工具，按確定 n注：若在安裝注：若在安裝EXCELEXCEL系統(tǒng)時沒有安裝加載宏，則必須重系統(tǒng)時沒有安裝加載宏，則必須重新啟動新啟動EXCELEXCEL的安裝程序，選擇其中的的安裝程序，選擇其中的“添加添加/ /刪除刪除”命令，安裝命令，安裝EXCELEXCEL的加載宏。的加載宏。目錄目錄7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析1、概念概念n數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯誤

8、來源的工具，快速數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯誤來源的工具，快速地找出具有引用關(guān)系的單元格，借此分析造成錯誤的單地找出具有引用關(guān)系的單元格，借此分析造成錯誤的單元格。元格。n數(shù)據(jù)審核使用追蹤箭頭，通過圖形的方式顯示或追蹤單數(shù)據(jù)審核使用追蹤箭頭，通過圖形的方式顯示或追蹤單元格與公式之間的關(guān)系。元格與公式之間的關(guān)系。 2、數(shù)據(jù)審核的方式數(shù)據(jù)審核的方式n追蹤引用單元格追蹤引用單元格 見見ch7ch7.xls.xls追蹤引用單元格追蹤引用單元格 操作方法：選定菜單操作方法：選定菜單“工具工具”“”“審核審核” ” 顯示顯示審核審核工具欄工具欄選擇要追蹤引用的含公式單元格選擇要追蹤引用的含公式單元格“審

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

10、具”“審核審核” 顯示顯示審核審核工具欄工具欄選擇要追蹤從屬單元格的單元格選擇要追蹤從屬單元格的單元格“審核審核”工具工具欄中欄中“追蹤從屬單元格追蹤從屬單元格”按鈕按鈕再次單擊再次單擊“追蹤從屬單元追蹤從屬單元格格”按鈕提供從屬的的單元格按鈕提供從屬的的單元格n移去引用單元格追蹤箭頭：移去引用單元格追蹤箭頭： 操作方法：選擇操作方法：選擇“審核審核”工具欄中工具欄中“移去從屬單元格中移去從屬單元格中追蹤箭頭追蹤箭頭”7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析3、 數(shù)據(jù)有效性數(shù)據(jù)有效性數(shù)據(jù)有效性：對數(shù)據(jù)進行檢驗和檢查的有效方法，把錯誤數(shù)據(jù)有效性：對數(shù)據(jù)進行檢驗和檢查的有效方法，把錯誤限制在數(shù)

11、據(jù)輸入階段。限制在數(shù)據(jù)輸入階段。n 限定數(shù)據(jù)類型和有效范圍：限定數(shù)據(jù)類型和有效范圍： 如：限定數(shù)據(jù)大小范圍、日期的范圍、輸入字符的個數(shù)、如：限定數(shù)據(jù)大小范圍、日期的范圍、輸入字符的個數(shù)、單元格的公式單元格的公式7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)限制的操作方法：選擇數(shù)據(jù)限制的操作方法：選擇“數(shù)據(jù)數(shù)據(jù)” “有效性有效性”在對在對話話 框中操作：框中操作：限定文本長度：限定文本長度： “設(shè)置設(shè)置”選項卡中選項卡中“允許允許” 下拉列表中下拉列表中選擇文本長度。選擇文本長度。限定數(shù)據(jù)的有效范圍：限定數(shù)據(jù)的有效范圍：“設(shè)置設(shè)置”選項卡中選項卡中“允許允許”下拉列下拉列表中選擇整數(shù)表中選擇整

12、數(shù)/小數(shù)小數(shù)- 確定最大確定最大/小值小值設(shè)置單元格有效范圍：設(shè)置單元格有效范圍：“設(shè)置設(shè)置”選項卡中選項卡中“允許允許”下拉列下拉列表中選擇序列表中選擇序列輸入序列值輸入序列值設(shè)置輸入提示信息：設(shè)置輸入提示信息： “輸入信息輸入信息”選項卡中輸入要顯示選項卡中輸入要顯示的信息的信息7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析n例：例：見見ch7.xls限定數(shù)據(jù)范圍限定數(shù)據(jù)范圍 某班要建立一個成績登記表，為了減少成績輸入錯某班要建立一個成績登記表，為了減少成績輸入錯誤，可對成績表中數(shù)據(jù)的輸入類型及范圍進行限制。誤，可對成績表中數(shù)據(jù)的輸入類型及范圍進行限制。n限制學號為限制學號為8位字符，不能小

13、于位字符，不能小于8位，也不能多于位，也不能多于8位。位。n限制所有學科成績?yōu)橄拗扑袑W科成績?yōu)?100之間的整數(shù)。之間的整數(shù)。n限制科目列標題的取值范圍，如限制科目列標題的取值范圍，如“高數(shù)高數(shù)”不能輸入不能輸入為為“高等數(shù)學高等數(shù)學”。7.2 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)審核及跟蹤分析4、圈釋無效數(shù)據(jù)圈釋無效數(shù)據(jù) 使用數(shù)據(jù)有效性規(guī)則可限制單元格可接收的數(shù)據(jù)，但對使用數(shù)據(jù)有效性規(guī)則可限制單元格可接收的數(shù)據(jù)，但對已輸入數(shù)據(jù)的區(qū)域，不能顯示出有誤的數(shù)據(jù)。采用圈釋無已輸入數(shù)據(jù)的區(qū)域，不能顯示出有誤的數(shù)據(jù)。采用圈釋無效數(shù)據(jù)的方法，可以顯示不滿足有效性規(guī)則的錯誤單元格。效數(shù)據(jù)的方法，可以顯示不滿足有效性規(guī)則

14、的錯誤單元格。n操作方法：（選擇數(shù)據(jù)區(qū)域操作方法：（選擇數(shù)據(jù)區(qū)域設(shè)置數(shù)據(jù)有效性規(guī)設(shè)置數(shù)據(jù)有效性規(guī)則）則）選擇選擇“工具工具”菜單菜單“審核審核”選擇選擇“顯示審顯示審核工具欄核工具欄”選中有效性檢測的數(shù)據(jù)區(qū)域選中有效性檢測的數(shù)據(jù)區(qū)域單擊單擊“審審核核”工具欄的工具欄的“圈釋無效數(shù)據(jù)圈釋無效數(shù)據(jù)”按鈕按鈕 注：要先設(shè)置數(shù)據(jù)的有效范圍，然后再圈釋無效數(shù)據(jù)注：要先設(shè)置數(shù)據(jù)的有效范圍，然后再圈釋無效數(shù)據(jù)n例：例：見見ch7.xls圈釋無效數(shù)據(jù)圈釋無效數(shù)據(jù) 某班要建立一個成績登記表，已經(jīng)對成績表中數(shù)據(jù)某班要建立一個成績登記表，已經(jīng)對成績表中數(shù)據(jù)的輸入類型及范圍進行限制，找出其中不符合規(guī)定的輸入類型及范圍

15、進行限制，找出其中不符合規(guī)定的數(shù)據(jù)。的數(shù)據(jù)。目錄目錄7.3 模擬運算表模擬運算表1、概念概念n模擬運算表是對工作表中一個單元格區(qū)域內(nèi)的數(shù)據(jù)進模擬運算表是對工作表中一個單元格區(qū)域內(nèi)的數(shù)據(jù)進行模擬運算，測試使用一個或兩個變量的公式中變量行模擬運算，測試使用一個或兩個變量的公式中變量對運算結(jié)果的影響。對運算結(jié)果的影響。2、模擬運算表的類型模擬運算表的類型n基于一個輸入變量基于一個輸入變量的表，用這個輸入變量測試它對的表，用這個輸入變量測試它對多個公式的影響；多個公式的影響；單模擬運算表單模擬運算表n基于兩個輸入變量的表，用這兩個變量測試它們對基于兩個輸入變量的表，用這兩個變量測試它們對于單個公式的影

16、響于單個公式的影響雙模擬運算表雙模擬運算表7.3 模擬運算表模擬運算表3、單變量模擬運算表單變量模擬運算表n概念概念n在單變量模擬運算表中，輸入數(shù)據(jù)的值被安排在一行或在單變量模擬運算表中，輸入數(shù)據(jù)的值被安排在一行或一列中。同時，單變量模擬表中使用的公式必須引用一列中。同時，單變量模擬表中使用的公式必須引用“輸入單元格輸入單元格”。n輸入單元格，就是被替換的含有輸入數(shù)據(jù)的單元格輸入單元格，就是被替換的含有輸入數(shù)據(jù)的單元格 n操作步驟：操作步驟：1、在工作表中建立模擬運算表的結(jié)構(gòu)；、在工作表中建立模擬運算表的結(jié)構(gòu)；2、輸入模擬運算表要用到的公式；、輸入模擬運算表要用到的公式；3、選擇包括公式、引用

17、單元格和運算結(jié)果單元格區(qū)域（、選擇包括公式、引用單元格和運算結(jié)果單元格區(qū)域（3部分）；部分）；4、選擇、選擇“數(shù)據(jù)數(shù)據(jù)”菜單菜單“模擬運算表模擬運算表”選項；選項；5、在、在“模擬運算表模擬運算表”對話框中輸入引用單用格（行或列一對話框中輸入引用單用格（行或列一種）種） 確定確定7.3 模擬運算表模擬運算表n例：例：見見ch7.xls單變量模擬運算表單變量模擬運算表 假設(shè)某人正考慮購買一套住房，要承擔一筆假設(shè)某人正考慮購買一套住房，要承擔一筆250 000250 000元的貸款，分元的貸款，分1515年還清?，F(xiàn)想查看每月的還貸金額，年還清?，F(xiàn)想查看每月的還貸金額，并想查看在不同的利率下，每月的

18、應(yīng)還貸金額。并想查看在不同的利率下，每月的應(yīng)還貸金額。 若貸款額分別為若貸款額分別為400 000400 000，550 000550 000，800 000800 000元，元，每月的應(yīng)還貸金額又是多少？每月的應(yīng)還貸金額又是多少？ 7.3 模擬運算表模擬運算表4、雙變量模擬運算表、雙變量模擬運算表n概念：概念： 單變量模擬運算表只能解決一個輸入變量對一個或多個公式單變量模擬運算表只能解決一個輸入變量對一個或多個公式計算結(jié)果的影響，要查看兩個變量對公式計算結(jié)果的影響，計算結(jié)果的影響，要查看兩個變量對公式計算結(jié)果的影響，就要用到雙變量模擬運算表。所謂雙模擬變量，就是指公式就要用到雙變量模擬運算表

19、。所謂雙模擬變量，就是指公式中有兩個變量。公式中兩個變量所在的單元格是任取的。可中有兩個變量。公式中兩個變量所在的單元格是任取的?？梢允枪ぷ鞅碇腥我饪瞻讍卧瘛Ｒ允枪ぷ鞅碇腥我饪瞻讍卧?。7.3 模擬運算表模擬運算表n操作步驟：操作步驟：n1、在工作表中建立模擬運算表的結(jié)構(gòu)；、在工作表中建立模擬運算表的結(jié)構(gòu)；n2、在行列交叉處輸入模擬運算表要用到的公式；、在行列交叉處輸入模擬運算表要用到的公式；n3、選擇包括公式、選擇包括公式,引用單元格和運算結(jié)果單元格區(qū)引用單元格和運算結(jié)果單元格區(qū)域（域（3部分）；部分）；n4、選擇、選擇“數(shù)據(jù)數(shù)據(jù)”菜單菜單“模擬運算表模擬運算表”選項；選項；n5、在、在“

20、模擬運算表模擬運算表”對話框中輸入公式中行和列引對話框中輸入公式中行和列引用的單用格用的單用格確定確定n例：例： 見見ch7.xls.xls雙變量模擬運算表雙變量模擬運算表 假設(shè)某人想貸款假設(shè)某人想貸款4545萬元購買一部車，要查看在不同萬元購買一部車，要查看在不同的利率和不同的償還年限下，每個月應(yīng)還的貸款金的利率和不同的償還年限下，每個月應(yīng)還的貸款金額。假設(shè)要查看貸款利率為額。假設(shè)要查看貸款利率為5%5%、5.5%5.5%、6.5%6.5%、7%7%、7.5%7.5%、8%8%，償還期限為，償還期限為1010年、年、1515年、年、2020年、年、3030年、年、3535年時，每月應(yīng)歸還的貸

21、款金額是多少年時，每月應(yīng)歸還的貸款金額是多少 ？目錄目錄7.4 單變量求解單變量求解1、概念、概念 所謂單變量求解，就是求解具有一個變量的方程，所謂單變量求解，就是求解具有一個變量的方程，ExcelExcel通過調(diào)整可變單元格中的數(shù)值，使之按照給定的通過調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來滿足目標單元格中的公式來滿足目標單元格中的目標值目標值.2、單變量求解方法、單變量求解方法 在工作表中輸入原始數(shù)據(jù)；在工作表中輸入原始數(shù)據(jù)； 建立可變數(shù)公式；建立可變數(shù)公式； 設(shè)置求解公式：菜單設(shè)置求解公式：菜單“工具工具”單變量求解單變量求解對話框?qū)υ捒蛑休斎耄耗繕藛卧?、目標值、可變單元格中輸入?/p>

22、目標單元格、目標值、可變單元格n例：例： 見見ch7.xls單變量求解單變量求解 某公司想向銀行貸款某公司想向銀行貸款900900萬元人民幣，貸款利率是萬元人民幣，貸款利率是8.7%8.7%，貸款限期為貸款限期為8 8年，每年應(yīng)償還多少金額？年，每年應(yīng)償還多少金額？ 如果公司每年可償還如果公司每年可償還120120萬元，該公司最多可貸款多少萬元，該公司最多可貸款多少金額？金額？ 前一問題可用前一問題可用PMTPMT函數(shù)函數(shù), , 后一問題可用單變量求解。后一問題可用單變量求解。目錄目錄7.5 方案分析方案分析1、概念、概念n方案是已命名的一組輸入值，是方案是已命名的一組輸入值，是 Excel

23、保存在工作表中并保存在工作表中并可用來自動替換某個計算模型的輸入值，用來預(yù)測模型的可用來自動替換某個計算模型的輸入值，用來預(yù)測模型的輸出結(jié)果。輸出結(jié)果。 例例:n已知某茶葉公司已知某茶葉公司2004年的總銷售額及各種茶葉的銷售成本，年的總銷售額及各種茶葉的銷售成本，現(xiàn)要在此基礎(chǔ)上制訂一個五年計劃。由于市場競爭的不斷現(xiàn)要在此基礎(chǔ)上制訂一個五年計劃。由于市場競爭的不斷變化，所以只能對總銷售額及各種茶葉銷售成本的增長率變化，所以只能對總銷售額及各種茶葉銷售成本的增長率做一些估計。最好的方案當然是總銷售額增長率高，各茶做一些估計。最好的方案當然是總銷售額增長率高，各茶葉的銷售成本增長率低。葉的銷售成本

24、增長率低。 n最好的估計是總銷售額增長最好的估計是總銷售額增長13%，花茶、綠茶、烏龍茶、，花茶、綠茶、烏龍茶、紅茶的銷售成本分別增長紅茶的銷售成本分別增長10%、6%、10%、7%。 見見ch7.xls方案方案7.5 方案分析方案分析n建立方案解決工作表建立方案解決工作表建立方法如下，輸入下表建立方法如下，輸入下表A列、列、B列及第列及第3行的所有數(shù)行的所有數(shù)據(jù)；在據(jù)；在C4單元格中輸入公單元格中輸入公式式“=B4*(1+$B$16)”，然后將其復(fù)制到然后將其復(fù)制到D4F4；在在C7中輸入公式中輸入公式“=B7*(1+$B$17)”,并并將其復(fù)制到將其復(fù)制到D7F7；在在C8中輸入公式中輸入

25、公式“=B8*(1+$B$18)”，并將其復(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ù)對應(yīng)列之和；凈行數(shù)據(jù)對應(yīng)列之和；凈收入是相應(yīng)的總銷售額和收入是相應(yīng)的總銷售額和銷售成本之差，銷售成本之差，E19的總的總凈收入是第凈收入是第13行數(shù)據(jù)之和。行數(shù)據(jù)之和。 7.5 方案分析方案分析輸入方案變量值如下圖所示：輸入方案變量值如下圖所示：7.5 方案分析方案分析2、顯示方

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

27、器”對話對話框中的某一方案框中的某一方案單擊單擊 “總結(jié)總結(jié)”按鈕按鈕在在“方案總結(jié)方案總結(jié)”對話框中結(jié)果類型中選擇對話框中結(jié)果類型中選擇“方案數(shù)據(jù)透視表方案數(shù)據(jù)透視表”目錄目錄7.6 線性規(guī)劃求解線性規(guī)劃求解1、概述概述 EXCELEXCEL提供提供的規(guī)劃求解工具，可求解出線性與非線性兩種的規(guī)劃求解工具，可求解出線性與非線性兩種規(guī)劃求解問題，規(guī)劃求解問題常用于解決產(chǎn)品比例、人員規(guī)劃求解問題，規(guī)劃求解問題常用于解決產(chǎn)品比例、人員調(diào)度、優(yōu)化路線、調(diào)配材料等方面問題。調(diào)度、優(yōu)化路線、調(diào)配材料等方面問題。2、規(guī)劃求解問題的特點：、規(guī)劃求解問題的特點：n問題有單一的目標，如求運輸?shù)淖罴崖肪€、求生產(chǎn)的問

28、題有單一的目標，如求運輸?shù)淖罴崖肪€、求生產(chǎn)的最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時間等。間等。n問題有明確的不等式約束條件，例如生產(chǎn)材料不能超問題有明確的不等式約束條件，例如生產(chǎn)材料不能超過庫存，生產(chǎn)周期不能超過一個星期等。過庫存，生產(chǎn)周期不能超過一個星期等。n問題有直接或間接影響約束條件的一組輸入值。問題有直接或間接影響約束條件的一組輸入值。 7.6 線性規(guī)劃求解線性規(guī)劃求解3、Excel規(guī)劃求解問題的組成部分規(guī)劃求解問題的組成部分 （1 1）一個或一組可變單元格）一個或一組可變單元格 可變單元格稱為決策變量，一組決策變量代表一個規(guī)劃可變

29、單元格稱為決策變量，一組決策變量代表一個規(guī)劃求解的方案求解的方案（2 2）目標函數(shù)）目標函數(shù)目標函數(shù)表示規(guī)劃求解要達到的最終目標，是規(guī)劃求解目標函數(shù)表示規(guī)劃求解要達到的最終目標，是規(guī)劃求解的關(guān)鍵。它是規(guī)劃求解中可變量的函數(shù)的關(guān)鍵。它是規(guī)劃求解中可變量的函數(shù) （3 3）約束條件）約束條件約束條件是實現(xiàn)目標的限制條件。約束條件是實現(xiàn)目標的限制條件。 意義：通過規(guī)劃求解，用戶可為工作表的目標單元格意義：通過規(guī)劃求解，用戶可為工作表的目標單元格中的公式找到一個優(yōu)化值，規(guī)劃求解將直接或間接與中的公式找到一個優(yōu)化值，規(guī)劃求解將直接或間接與目標單元格公式相聯(lián)系的一組單元格數(shù)值進行調(diào)整，目標單元格公式相聯(lián)系的

30、一組單元格數(shù)值進行調(diào)整，最終在目標單元格公式中求得期望的結(jié)果。最終在目標單元格公式中求得期望的結(jié)果。7.6 線性規(guī)劃求解線性規(guī)劃求解例：例： 見見ch7.xls規(guī)劃求解規(guī)劃求解n某肥料廠專門收集有機物垃圾，如青草、樹枝、凋謝某肥料廠專門收集有機物垃圾，如青草、樹枝、凋謝的花朵等。該廠利用這些廢物，并摻進不同比例的泥的花朵等。該廠利用這些廢物，并摻進不同比例的泥土和礦物質(zhì)來生產(chǎn)高質(zhì)量的植物肥料，生產(chǎn)的肥料分土和礦物質(zhì)來生產(chǎn)高質(zhì)量的植物肥料，生產(chǎn)的肥料分為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料4種。為種。為使問題簡單，假設(shè)收集廢物的勞動力是自愿的，除了使問題

31、簡單，假設(shè)收集廢物的勞動力是自愿的，除了收集成本之外，材料成本是低廉的。收集成本之外，材料成本是低廉的。n該廠目前的原材料、生產(chǎn)各種肥料需要的原材料比例，該廠目前的原材料、生產(chǎn)各種肥料需要的原材料比例，各種肥料的單價等如下頁各表所示。各種肥料的單價等如下頁各表所示。n問題：求出在現(xiàn)有的情況下，即利用原材料的現(xiàn)有庫問題：求出在現(xiàn)有的情況下，即利用原材料的現(xiàn)有庫存，應(yīng)生產(chǎn)各種類型的肥料各多少數(shù)量才能獲得最大存，應(yīng)生產(chǎn)各種類型的肥料各多少數(shù)量才能獲得最大利潤，最大利潤是多少？利潤，最大利潤是多少？ n分析分析:所求是在現(xiàn)有的原材料情況下所求是在現(xiàn)有的原材料情況下,應(yīng)如何合理搭配應(yīng)如何合理搭配,才能獲

32、取生產(chǎn)產(chǎn)品的最大利潤才能獲取生產(chǎn)產(chǎn)品的最大利潤.7.6 線性規(guī)劃求解線性規(guī)劃求解表表2 生產(chǎn)肥料的庫存原材料生產(chǎn)肥料的庫存原材料庫存情況庫存情況現(xiàn)有庫存現(xiàn)有庫存泥土泥土4100有機垃圾有機垃圾3200礦物質(zhì)礦物質(zhì)3500修剪物修剪物1600表1 各肥料成品用料及其價格表表的意思是生產(chǎn)一個單位的肥料需要多少各種原材料多少單位產(chǎn)品產(chǎn)品泥土泥土有機垃圾有機垃圾礦物質(zhì)礦物質(zhì)修剪物修剪物單價單價底層肥料底層肥料55547623105.00中層肥料中層肥料6432452084.00上層肥料上層肥料43329844105.00劣質(zhì)肥料劣質(zhì)肥料1845231857.00表表3單位原材料成本單價單位原材料成本單

33、價項項 目目單位成本單位成本泥土泥土0.20有機垃圾有機垃圾0.15礦物質(zhì)礦物質(zhì)0.10修剪物修剪物0.237.6 線性規(guī)劃求解線性規(guī)劃求解n建立規(guī)劃求解模型步驟：建立規(guī)劃求解模型步驟：規(guī)劃求解第一步規(guī)劃求解第一步建立求解工作表建立求解工作表(輸入原始數(shù)據(jù)及相應(yīng)的各輸入原始數(shù)據(jù)及相應(yīng)的各公式公式)7.6 線性規(guī)劃求解線性規(guī)劃求解規(guī)劃求解第二步規(guī)劃求解第二步設(shè)置求解參數(shù)設(shè)置求解參數(shù)n選擇選擇“工具工具” “規(guī)劃求解規(guī)劃求解”菜單，設(shè)置以下求解的菜單，設(shè)置以下求解的各項參數(shù)：各項參數(shù)：n設(shè)置目標單元格：輸入目標函數(shù)所在單元格設(shè)置目標單元格：輸入目標函數(shù)所在單元格(為總余額為總余額單元格單元格)n設(shè)

34、置目標：最大值、最小值或值的數(shù)值設(shè)置目標：最大值、最小值或值的數(shù)值(最大利潤最大利潤,即最即最大值大值)n設(shè)置可變單元格：它的確定決定結(jié)果（為生產(chǎn)數(shù)量）設(shè)置可變單元格：它的確定決定結(jié)果（為生產(chǎn)數(shù)量）n設(shè)置約束條件：單擊設(shè)置約束條件：單擊“添加添加”按鈕按鈕輸入約束條件輸入約束條件按添加按添加依次輸入所有約束條件依次輸入所有約束條件確定確定7.6 線性規(guī)劃求解線性規(guī)劃求解n規(guī)劃求解第規(guī)劃求解第3步步保存求解結(jié)果保存求解結(jié)果在規(guī)劃求解對話框中按在規(guī)劃求解對話框中按“求解求解”在規(guī)劃求解結(jié)果對在規(guī)劃求解結(jié)果對話框中按話框中按“保存規(guī)劃求解結(jié)果保存規(guī)劃求解結(jié)果”7.6 線性規(guī)劃求解線性規(guī)劃求解4、修改

35、資源、修改資源 例例1 1： 見見ch7ch7.xls.xls規(guī)劃求解規(guī)劃求解 n肥料廠接到一個電話：只要公司肯花肥料廠接到一個電話：只要公司肯花1010元的運費就能元的運費就能得到得到150150個單位的礦物。這筆交易稍稍降低了礦物質(zhì)的個單位的礦物。這筆交易稍稍降低了礦物質(zhì)的平均價格，但這些礦物質(zhì)值平均價格，但這些礦物質(zhì)值1010元嗎？元嗎？n解決該問題的方法是，將庫存礦物解決該問題的方法是，將庫存礦物35003500改為改為36503650，用，用規(guī)劃求解重新計算最大盈余。看除去￥規(guī)劃求解重新計算最大盈余?？闯ィ?010的成本后，的成本后，盈余是否增加盈余是否增加 n操作操作: : 將庫

36、存礦物將庫存礦物35003500改為改為3650,3650,其它所有公式不變其它所有公式不變, ,再次進再次進行求解行求解, ,求得盈余額為求得盈余額為4483.41,4483.41,原盈余額為原盈余額為4425.89.4425.89. 可知盈利為可知盈利為57.52.57.52.扣除扣除1010元成本后仍有元成本后仍有47.52.47.52.因此該因此該礦物還是要的礦物還是要的. .7.6 線性規(guī)劃求解線性規(guī)劃求解5、修改約束條件、修改約束條件 見見ch7.xls規(guī)劃求解規(guī)劃求解n肥料廠接到一個電話，一個老顧客急需肥料廠接到一個電話，一個老顧客急需2525個單位的上個單位的上層肥料，公司經(jīng)理

37、在檢查打印結(jié)果后，發(fā)現(xiàn)沒有安排層肥料，公司經(jīng)理在檢查打印結(jié)果后，發(fā)現(xiàn)沒有安排生產(chǎn)上層肥料。數(shù)量為生產(chǎn)上層肥料。數(shù)量為0 0。決定增加約束條件，為他生。決定增加約束條件，為他生產(chǎn)產(chǎn)2525個單位的上層肥料。個單位的上層肥料。n結(jié)果可發(fā)現(xiàn)結(jié)果可發(fā)現(xiàn): :盈余額僅盈余額僅3246.51,3246.51,比原來比原來4483.414483.41少了少了1236.91236.9。顯然不值得。但如該顧客為長期顧客，則短。顯然不值得。但如該顧客為長期顧客，則短期內(nèi)將損失一些錢，但得到了顧客的信任。期內(nèi)將損失一些錢，但得到了顧客的信任。增加的增加的約束條約束條件件7.6 線性規(guī)劃求解線性規(guī)劃求解6、 規(guī)劃求解

38、的結(jié)果報告規(guī)劃求解的結(jié)果報告n運算結(jié)果報告：列出目標單元格、可變單元格及它們的運算結(jié)果報告：列出目標單元格、可變單元格及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。 見見ch7.xls運算結(jié)果報告運算結(jié)果報告7.6 線性規(guī)劃求解線性規(guī)劃求解n敏感性報告：敏感性報告： 見見ch7.xls敏感性報告敏感性報告7.6 線性規(guī)劃求解線性規(guī)劃求解n極限報告：列出目標單元格、可變單元格及它們的數(shù)極限報告：列出目標單元格、可變單元格及它們的數(shù)值、上下限和目標值。下限為在滿足約束條件和保持值、上下限和目標值。下限為在滿足約束條件和保持其它可變單元格數(shù)值

39、不變的情況下，某個可變單元格其它可變單元格數(shù)值不變的情況下，某個可變單元格可以取得的最小值，上限則為在這種情況下可以取到可以取得的最小值，上限則為在這種情況下可以取到的最大值。的最大值。 見見ch7.xls極限值報告極限值報告7.6 線性規(guī)劃求解線性規(guī)劃求解7、求解精度及求解模型設(shè)置求解精度及求解模型設(shè)置 Excel采用迭代的方式進行規(guī)劃求解，當求解到一定精度采用迭代的方式進行規(guī)劃求解，當求解到一定精度時就結(jié)束求解，但有時要修改求解的精度、計算時間、規(guī)時就結(jié)束求解，但有時要修改求解的精度、計算時間、規(guī)劃模型和迭代次數(shù)。修改上述設(shè)置的方法如下：劃模型和迭代次數(shù)。修改上述設(shè)置的方法如下： 在在“規(guī)

40、劃求解參數(shù)規(guī)劃求解參數(shù)”對話框中設(shè)置好各項求解參數(shù)；對話框中設(shè)置好各項求解參數(shù)； 單擊單擊“選項選項”按鈕，在按鈕，在“規(guī)劃求解選項規(guī)劃求解選項”對話框中設(shè)置對話框中設(shè)置各項求解參數(shù)。各項求解參數(shù)。 7.6 線性規(guī)劃求解線性規(guī)劃求解例例2：求解不等式：求解不等式： 見見ch7ch7.xls.xls規(guī)劃求解不等式規(guī)劃求解不等式 某工廠生產(chǎn)甲、乙兩種產(chǎn)品，假設(shè)生產(chǎn)甲產(chǎn)品某工廠生產(chǎn)甲、乙兩種產(chǎn)品，假設(shè)生產(chǎn)甲產(chǎn)品1 1噸，要消耗噸，要消耗9 9噸煤，噸煤，4 4千瓦電力，千瓦電力，3 3噸鋼材，獲利噸鋼材，獲利0.70.7萬元；生產(chǎn)乙產(chǎn)品萬元；生產(chǎn)乙產(chǎn)品1 1噸，噸，要消耗要消耗4 4噸煤，噸煤，5

41、5千瓦電力，千瓦電力，1010噸鋼材，獲利噸鋼材，獲利1.21.2萬元。按計劃萬元。按計劃國家能提供給該廠的煤為國家能提供給該廠的煤為360360噸，電力噸，電力200200千瓦，鋼材千瓦，鋼材300300噸，噸，問應(yīng)該生產(chǎn)多少噸甲種產(chǎn)品和乙種產(chǎn)品，才能獲得最大利潤？問應(yīng)該生產(chǎn)多少噸甲種產(chǎn)品和乙種產(chǎn)品，才能獲得最大利潤？ 假設(shè)生產(chǎn)甲種產(chǎn)品假設(shè)生產(chǎn)甲種產(chǎn)品X1X1噸，生產(chǎn)乙種產(chǎn)品噸，生產(chǎn)乙種產(chǎn)品x2x2噸，噸， 其最大利潤是求其最大利潤是求=0.7x1+1.2x2=0.7x1+1.2x2的最大值。這個問題可用數(shù)學的最大值。這個問題可用數(shù)學建模如下：建模如下：.0,0.300103.20054.3

42、604921212121xxxxxxxx7.6 線性規(guī)劃求解線性規(guī)劃求解規(guī)劃求解如下：規(guī)劃求解如下：nB3B3和和C3C3分別用于保存甲和乙產(chǎn)品的生產(chǎn)量。分別用于保存甲和乙產(chǎn)品的生產(chǎn)量。n目標單元格為目標單元格為B8B8；可變單元格為可變單元格為$ $B$3:$C$3B$3:$C$3；約束條件為：約束條件為：n$ $B$3=0B$3=0n$C$3=0$C$3=0n$ $B$4=360B$4=360n$ $B$5=200B$5=200n$ $B$6=300B$6=3007.6 線性規(guī)劃求解線性規(guī)劃求解n例例3： 見見ch7.xls線形規(guī)劃求解線形規(guī)劃求解n某公司在某公司在A A地有一個生產(chǎn)基地地

43、有一個生產(chǎn)基地, ,其生產(chǎn)能力為其生產(chǎn)能力為400,400,隨著市場隨著市場需求的增長及該公司業(yè)務(wù)量的增大需求的增長及該公司業(yè)務(wù)量的增大, ,現(xiàn)有現(xiàn)有3 3個配送中心的需個配送中心的需求都在增長求都在增長, ,預(yù)計分別為預(yù)計分別為200,400,300200,400,300。公司正考慮再建立。公司正考慮再建立一個生產(chǎn)能力為一個生產(chǎn)能力為500500的工廠，準備建在的工廠，準備建在B B地。從地。從A A地的工廠地的工廠向向3 3個配送中心的單位運輸成本分別為個配送中心的單位運輸成本分別為5.05.0元，元，6.06.0元，元，5.45.4元，從元，從B B地的工廠向地的工廠向3 3個配送中心的

44、單位運輸成本是個配送中心的單位運輸成本是7.07.0元，元，4.64.6元，元，6.66.6元。應(yīng)怎樣分配元。應(yīng)怎樣分配A A、B B兩地到兩地到3 3個配送中心的產(chǎn)個配送中心的產(chǎn)品量，才能使運輸成本最小？品量，才能使運輸成本最?。縩規(guī)劃模型解釋如下規(guī)劃模型解釋如下：7.6 線性規(guī)劃求解線性規(guī)劃求解D5D5、E5E5、F5F5表示從表示從A A廠將廠將1 1個單位產(chǎn)品分別個單位產(chǎn)品分別送到配送中心送到配送中心1 1，2 2，3 3的費用；的費用；D7D7，E7E7，F(xiàn)7F7表示從表示從B B廠將廠將1 1個單位產(chǎn)品分別個單位產(chǎn)品分別送到配送中心送到配送中心1 1，2 2，3 3的費用；的費用；

45、D6D6，E6E6，F(xiàn)6F6為可變單元格，保存從為可變單元格，保存從A A廠運廠運到到3 3個配送中心的最佳產(chǎn)品量；個配送中心的最佳產(chǎn)品量；D8D8，E8E8，F(xiàn)8F8為可變單元格，保存從為可變單元格，保存從B B廠運廠運到到3 3個配送中心的最佳產(chǎn)品量；個配送中心的最佳產(chǎn)品量；D10D10，E10E10，F(xiàn)10F10表示配送中心表示配送中心1 1，2 2，3 3的最的最大負荷能力；大負荷能力；H5H5，H7H7分別是分別是A A廠、廠、B B廠的生產(chǎn)能力廠的生產(chǎn)能力， H9H9是是A A，B B兩廠的總生產(chǎn)能力兩廠的總生產(chǎn)能力7.6 線性規(guī)劃求解線性規(guī)劃求解G6G6，G8G8分別為分別為A

46、A，B B兩廠各自的產(chǎn)品總量。兩廠各自的產(chǎn)品總量。G6G6公式：公式：= =D6+E6+F6D6+E6+F6；G8G8公式：公式：= =G8+E8+F8G8+E8+F8；D9D9，E9E9，F(xiàn)9F9分別為兩廠送到各配送中心的產(chǎn)品總量。分別為兩廠送到各配送中心的產(chǎn)品總量。D9D9公式：公式：= =D6+D8D6+D8；E9E9公式：公式：= =E6+E8E6+E8；F9F9公式：公式：= =F6+F8F6+F8；本模型的約束條件分析如下：本模型的約束條件分析如下：A A，B B兩廠送到各配送中心的產(chǎn)品總量不能超過各配送中心的負兩廠送到各配送中心的產(chǎn)品總量不能超過各配送中心的負荷能力：荷能力：D9

47、D9：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* *F8F8目錄目錄7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫1、概述、概述nExcelExcel提供了一組數(shù)據(jù)分析工具，稱為分析工具庫。其提供了一組數(shù)據(jù)分析工具，稱為分析工具庫。其中提供的分析工具在工程分析、數(shù)理統(tǒng)計、經(jīng)濟計量中提供的分析工具在工程分析、數(shù)理統(tǒng)計、經(jīng)濟計量分析等學科中有較強的實用價值。分析等學科中有

48、較強的實用價值。n分析工具庫由分析工具庫由ExcelExcel自帶的加載宏提供。自帶的加載宏提供。 如果啟動如果啟動ExcelExcel后，在后，在ExcelExcel的的“工具工具”菜單中沒有菜單中沒有“數(shù)據(jù)分析數(shù)據(jù)分析”菜單項，就需啟動菜單項，就需啟動“工具工具”中的中的“加載加載宏宏”菜單項，將菜單項，將“分析工具庫分析工具庫”加載到加載到ExcelExcel系統(tǒng)中。系統(tǒng)中。n如果加載宏對話框中沒有分析工具庫，則單擊加載宏如果加載宏對話框中沒有分析工具庫，則單擊加載宏對話框中對話框中“瀏覽瀏覽”按鈕，定位到分析工具庫加載宏文按鈕，定位到分析工具庫加載宏文件件“Analy32.dll”An

49、aly32.dll”所在的驅(qū)動器和文件夾，通常位于所在的驅(qū)動器和文件夾，通常位于“Microsoft OfficeOfficeLibraryAnalysis”Microsoft OfficeOfficeLibraryAnalysis”中中, ,否則需運行否則需運行OfficeOffice系統(tǒng)的安裝程序。系統(tǒng)的安裝程序。nExcelExcel的的“分析工具庫分析工具庫”加載宏提供的一些統(tǒng)計函數(shù)、加載宏提供的一些統(tǒng)計函數(shù)、財務(wù)函數(shù)和工程函數(shù)。這些函數(shù)只有在安裝了財務(wù)函數(shù)和工程函數(shù)。這些函數(shù)只有在安裝了“分析分析工具庫工具庫”后才能使用后才能使用 。7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫2、Excel

50、Excel分析工具庫中的工具分析工具庫中的工具分析工具分析工具名稱名稱說說 明明方差分析方差分析 包括包括3種類型的分析，它們是單因素方差分析、可種類型的分析，它們是單因素方差分析、可重復(fù)雙因素分析、無重復(fù)雙因素分析重復(fù)雙因素分析、無重復(fù)雙因素分析相關(guān)系數(shù)相關(guān)系數(shù)分析分析用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。之間的關(guān)系。協(xié)方差分協(xié)方差分析析用于返回各數(shù)據(jù)點的一對均值偏差之間的乘積的用于返回各數(shù)據(jù)點的一對均值偏差之間的乘積的平均值。平均值。描述統(tǒng)計描述統(tǒng)計分析分析用于生成對輸入?yún)^(qū)域中數(shù)據(jù)的單變值分析，提供用于生成對輸入?yún)^(qū)域中數(shù)據(jù)的單變

51、值分析，提供有關(guān)數(shù)據(jù)趨中性和易變性的信息有關(guān)數(shù)據(jù)趨中性和易變性的信息指數(shù)平滑指數(shù)平滑分析分析基于前期預(yù)測值導(dǎo)出相應(yīng)的新預(yù)測值，并修正前基于前期預(yù)測值導(dǎo)出相應(yīng)的新預(yù)測值，并修正前期預(yù)測值的誤差。期預(yù)測值的誤差。7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫傅里葉分傅里葉分析析解決線性系統(tǒng)問題，并能通過快速傅里葉變換解決線性系統(tǒng)問題，并能通過快速傅里葉變換（FFT）分析周期性的數(shù)據(jù)。）分析周期性的數(shù)據(jù)。F-檢驗檢驗用來比較兩個樣本總體的方差用來比較兩個樣本總體的方差直方圖分直方圖分析析在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情況下，計算數(shù)據(jù)的個別和累計頻率況下，計算

52、數(shù)據(jù)的個別和累計頻率移動平均移動平均分析分析基于特定的過去某段時期中變量的均值，對未來基于特定的過去某段時期中變量的均值，對未來值進行預(yù)測值進行預(yù)測t-檢驗分檢驗分析析提供提供3種不同檢驗：雙樣本等方差假設(shè)種不同檢驗：雙樣本等方差假設(shè)t-檢驗，雙檢驗，雙樣本異方差假設(shè)樣本異方差假設(shè)t-檢驗，平均值的成對雙樣本檢驗，平均值的成對雙樣本t-檢驗檢驗回歸分析回歸分析通過對一組觀察值使用通過對一組觀察值使用“最小二乘法最小二乘法”直線擬合，直線擬合，進行線形回歸分析。進行線形回歸分析。抽樣分析抽樣分析以輸入?yún)^(qū)域為總體構(gòu)造總體的一個樣本以輸入?yún)^(qū)域為總體構(gòu)造總體的一個樣本z-檢驗檢驗雙樣本平均差檢驗雙樣本

53、平均差檢驗7.7 數(shù)據(jù)分析數(shù)據(jù)分析工具庫工具庫3、 統(tǒng)計分析統(tǒng)計分析 nExcelExcel的分析工具庫提供了的分析工具庫提供了3 3種統(tǒng)計觀測分析工具：指種統(tǒng)計觀測分析工具：指數(shù)平滑分析、移動平均分析和回歸分析數(shù)平滑分析、移動平均分析和回歸分析 n三種工具用法相同，下面以指數(shù)平滑分析為例。三種工具用法相同，下面以指數(shù)平滑分析為例。 見見ch7ch7.xls .xls 指數(shù)平滑分析指數(shù)平滑分析 （1 1）在工作表的一列上輸入各時間點上的觀察值，如下）在工作表的一列上輸入各時間點上的觀察值，如下圖圖A A列所示。列所示。 7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫（2 2）選擇）選擇“工具工具”菜單菜

54、單“數(shù)據(jù)分析數(shù)據(jù)分析” ” 選項，對話框中選擇選項，對話框中選擇“指數(shù)平滑指數(shù)平滑”按確定。按確定。 （3 3）在）在“指數(shù)平滑指數(shù)平滑”對話框中設(shè)置對話框中設(shè)置“輸入?yún)^(qū)域輸入?yún)^(qū)域”、“阻尼系阻尼系數(shù)數(shù)”、“輸出區(qū)域輸出區(qū)域”選項。選項。（4 4）選定對話框中）選定對話框中“圖表輸出圖表輸出”和和“標準誤差標準誤差”復(fù)選框標志。復(fù)選框標志。分析結(jié)果：分析結(jié)果：B B列為分析之后輸出的預(yù)測數(shù)據(jù)；列為分析之后輸出的預(yù)測數(shù)據(jù)；C C列是分析工具列是分析工具輸出的標準誤差。輸出的標準誤差。7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫4、假設(shè)檢驗、假設(shè)檢驗 n假設(shè)檢驗是根據(jù)對事物進行抽樣所得的少量樣本信息，假設(shè)

55、檢驗是根據(jù)對事物進行抽樣所得的少量樣本信息，判斷總體分布的某個假設(shè)是否成立的一種數(shù)理統(tǒng)計方判斷總體分布的某個假設(shè)是否成立的一種數(shù)理統(tǒng)計方法。法。n假設(shè)分析工具有三種：假設(shè)分析工具有三種：t-檢驗、檢驗、z-檢驗、檢驗、F-檢驗。檢驗。n運用這些檢驗工具可以完成均值、方差的假設(shè)檢驗。運用這些檢驗工具可以完成均值、方差的假設(shè)檢驗。 方法見下例方法見下例7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫n例：例： 見見ch7.xlst檢驗檢驗 （雙樣本等方差（雙樣本等方差t-t-檢驗，以確定兩個樣本均值實際上檢驗，以確定兩個樣本均值實際上是否相等）是否相等） 某種子公司為比較兩個稻種的產(chǎn)量，選擇了某種子公司為比較

56、兩個稻種的產(chǎn)量，選擇了2525塊條塊條件相似的試驗田，采用相同的耕種方法進行耕種試件相似的試驗田，采用相同的耕種方法進行耕種試驗，結(jié)果播種甲稻種的驗，結(jié)果播種甲稻種的1313塊田的畝產(chǎn)量（單位：市塊田的畝產(chǎn)量（單位：市斤）分別是：斤）分別是：880880、1 1201 120、980980、885885、828828、927927、924924、942942、766766、1 1801 180、780780、1 0681 068、650650；播種；播種乙稻種的乙稻種的1212塊試驗田的畝產(chǎn)量分別是：塊試驗田的畝產(chǎn)量分別是：940940、1 1421 142、1 0201 020、785785

57、、645645、780780、1 1801 180、680680、810810、824824、846846、780780。問這兩個稻種的產(chǎn)量有沒有明顯的高低。問這兩個稻種的產(chǎn)量有沒有明顯的高低之分。之分。 說明：要判斷兩稻種有無顯著差別，用說明：要判斷兩稻種有無顯著差別，用t-t-檢驗方法，需檢驗方法，需先計算各樣本的平均值和方差，才能作進一步的檢驗先計算各樣本的平均值和方差，才能作進一步的檢驗分析。分析。7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫nt-t-檢驗操作過程：檢驗操作過程：n(1)(1)輸入輸入A A、B B兩列樣本數(shù)據(jù)（下表中右邊數(shù)據(jù)全為產(chǎn)生的兩列樣本數(shù)據(jù)（下表中右邊數(shù)據(jù)全為產(chǎn)生的分析

58、結(jié)果）分析結(jié)果）7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫n(2)(2)選擇選擇“工具工具”菜單中菜單中“數(shù)據(jù)分析數(shù)據(jù)分析”選項，對話框中選選項，對話框中選擇擇t t檢驗雙樣本等方差假設(shè)；檢驗雙樣本等方差假設(shè)；n（3 3）在）在“雙樣本等方差假設(shè)分析雙樣本等方差假設(shè)分析”對話框中設(shè)置對話框中設(shè)置t t檢驗的檢驗的各項參數(shù)各項參數(shù)n按確定按確定7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫5、回歸分析、回歸分析n回歸分析主要用于分析單個因變量是如何受一個或幾個自回歸分析主要用于分析單個因變量是如何受一個或幾個自變量影響的。如觀察某個運動員的運動成績與一系列統(tǒng)計變量影響的。如觀察某個運動員的運動成績與一系列統(tǒng)計因

59、素的關(guān)系。如年齡、體重、身高等。因素的關(guān)系。如年齡、體重、身高等。n回歸分析分為線性回歸和非線性回歸兩種。線性回歸的數(shù)回歸分析分為線性回歸和非線性回歸兩種。線性回歸的數(shù)學模型為：學模型為：nExcelExcel通過對一組觀察值使用通過對一組觀察值使用“最小二乘法最小二乘法”直線擬合，進直線擬合，進行線性回歸分析，該回歸分析可同時解決一元回歸與多元行線性回歸分析，該回歸分析可同時解決一元回歸與多元回歸問題?；貧w問題。iiiyxu7.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫n例：（用一個多元回歸線性分析例子來說明回歸分例：（用一個多元回歸線性分析例子來說明回歸分析工具的使用方法析工具的使用方法）nch7.

60、xlsch7.xls回歸分析數(shù)據(jù)表中，列出了美國回歸分析數(shù)據(jù)表中，列出了美國1956197019561970年間歷年的人均可支配收入年間歷年的人均可支配收入x xi i和人均和人均可消費支出可消費支出y yi i的數(shù)據(jù)。試用圖中的數(shù)據(jù)擬合模型。的數(shù)據(jù)。試用圖中的數(shù)據(jù)擬合模型。n模型中的趨勢變量模型中的趨勢變量t t，用于反映除人均收入之外，用于反映除人均收入之外的所有其他因素對人均消費的影響的所有其他因素對人均消費的影響 tttutxy2107.7 數(shù)據(jù)分析工具庫數(shù)據(jù)分析工具庫n利用回歸分析工具求解此模型的方法利用回歸分析工具求解此模型的方法n輸入原始數(shù)據(jù)表；輸入原始數(shù)據(jù)表；n選擇選擇“工具工