Excel與數(shù)據(jù)處理-數(shù)據(jù)分析工具及應用課件

Excel與數(shù)據(jù)處理第七章本章教學目的與要求1、掌握宏的加載方法2、掌握追蹤從屬或引用單元格的方法3、掌握限定單元格數(shù)據(jù)的范圍及圈釋無效數(shù)據(jù)的應用方法4、掌握模擬運算表及變量求解的應用5、掌握方案的建立和應用6、掌握規(guī)劃求解工具的應用7、了解假設檢驗和回歸分析等工具的應用本章重點、難點及學時數(shù)重點：掌握數(shù)據(jù)審核的方法掌握模擬運算表的應用掌握單變量求解的應用掌握方案的應用掌握規(guī)劃求解的應用難點：掌握規(guī)劃求解的應用學時數(shù)：12學時（上機6學時）7.1分析工具的安裝1、加載宏的概念加載宏是一種可選擇性地安裝到計算機中的軟件組件，用戶可根據(jù)需要決定是否安裝。其作用是為Excel添加命令和函數(shù)，擴充Excel的功能。Excel加載宏的擴展名是.xla或.xll。在默認情況下，Excel將下表列出的加載宏程序安裝在如下某一磁盤位置：“MicrosoftOffice\Office”文件夾下的“Library”文件夾或其子文件夾，或Windows所在文件夾下的“Profiles\用戶名\ApplicationData\Microsoft\AddIns”文件夾下。網(wǎng)絡管理員也可將加載宏程序安裝到其他位置。7.1分析工具的安裝2、Excel內(nèi)置加載宏加載宏描述分析工具庫添加財務、統(tǒng)計和工程分析工具和函數(shù)條件求和向?qū)τ跀?shù)據(jù)清單中滿足指定條件的數(shù)據(jù)進行求和計算歐元工具將數(shù)值的格式設置為歐元的格式，并提供EUROCONVERT函數(shù)以用于轉(zhuǎn)換貨幣查閱向?qū)?chuàng)建一個公式，通過數(shù)據(jù)清單中的已知值查找所需數(shù)據(jù)ODBC加載宏利用安裝的ODBC驅(qū)動程序，通過開放式數(shù)據(jù)庫互連（ODBC）功能與外部數(shù)據(jù)源相連7.1分析工具的安裝報告管理器為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視面以及方案的報告規(guī)劃求解對基于可變單元格和條件單元格的假設分析方案進行求解計算模板工具提供Excel的內(nèi)置模板所使用的工具。使用內(nèi)置模板時就可自動訪問這些工具InternetAssistantVBA通過使用Excel97InternetAssistant語法，開發(fā)者可將Excel數(shù)據(jù)發(fā)布到Web上7.2數(shù)據(jù)審核及跟蹤分析1、概念數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯誤來源的工具，快速地找出具有引用關(guān)系的單元格，借此分析造成錯誤的單元格。數(shù)據(jù)審核使用追蹤箭頭，通過圖形的方式顯示或追蹤單元格與公式之間的關(guān)系。

2、數(shù)據(jù)審核的方式追蹤引用單元格[見ch7.xls—追蹤引用單元格]操作方法：選定菜單“工具”—“審核”—顯示‘審核’工具欄—選擇要追蹤引用的含公式單元格—“審核”工具欄中“追蹤引用單元格”按鈕—再次單擊“追蹤引用單元格”按鈕提供數(shù)據(jù)的下一級單元格移去引用單元格追蹤箭頭：操作方法：選擇“審核”工具欄中“移去引用單元格中追蹤箭頭”7.2數(shù)據(jù)審核及跟蹤分析追蹤從屬單元格[見ch7.xls—追蹤從屬單元格]某單元格公式引用了其它單元格，則該單元格為從屬單元格。操作方法：選定菜單“工具”—“審核”–顯示‘審核’工具欄—選擇要追蹤從屬單元格的單元格—“審核”工具欄中“追蹤從屬單元格”按鈕—再次單擊“追蹤從屬單元格”按鈕提供從屬的的單元格移去引用單元格追蹤箭頭：操作方法：選擇“審核”工具欄中“移去從屬單元格中追蹤箭頭”7.2數(shù)據(jù)審核及跟蹤分析3、數(shù)據(jù)有效性數(shù)據(jù)有效性：對數(shù)據(jù)進行檢驗和檢查的有效方法，把錯誤限制在數(shù)據(jù)輸入階段。限定數(shù)據(jù)類型和有效范圍：如：限定數(shù)據(jù)大小范圍、日期的范圍、輸入字符的個數(shù)、單元格的公式7.2數(shù)據(jù)審核及跟蹤分析例：[見ch7.xls—限定數(shù)據(jù)范圍]某班要建立一個成績登記表，為了減少成績輸入錯誤，可對成績表中數(shù)據(jù)的輸入類型及范圍進行限制。限制學號為8位字符，不能小于8位，也不能多于8位。限制所有學科成績?yōu)?~100之間的整數(shù)。限制科目列標題的取值范圍，如“高數(shù)”不能輸入為“高等數(shù)學”。7.2數(shù)據(jù)審核及跟蹤分析4、圈釋無效數(shù)據(jù)

使用數(shù)據(jù)有效性規(guī)則可限制單元格可接收的數(shù)據(jù)，但對已輸入數(shù)據(jù)的區(qū)域，不能顯示出有誤的數(shù)據(jù)。采用圈釋無效數(shù)據(jù)的方法，可以顯示不滿足有效性規(guī)則的錯誤單元格。操作方法：（選擇數(shù)據(jù)區(qū)域—設置數(shù)據(jù)有效性規(guī)則）—選擇“工具”菜單—“審核”—選擇“顯示審核工具欄”—選中有效性檢測的數(shù)據(jù)區(qū)域—單擊“審核”工具欄的“圈釋無效數(shù)據(jù)”按鈕

注：要先設置數(shù)據(jù)的有效范圍，然后再圈釋無效數(shù)據(jù)例：[見ch7.xls—圈釋無效數(shù)據(jù)]某班要建立一個成績登記表，已經(jīng)對成績表中數(shù)據(jù)的輸入類型及范圍進行限制，找出其中不符合規(guī)定的數(shù)據(jù)。目錄7.3模擬運算表1、概念模擬運算表是對工作表中一個單元格區(qū)域內(nèi)的數(shù)據(jù)進行模擬運算，測試使用一個或兩個變量的公式中變量對運算結(jié)果的影響。2、模擬運算表的類型①基于一個輸入變量的表，用這個輸入變量測試它對多個公式的影響；——單模擬運算表②基于兩個輸入變量的表，用這兩個變量測試它們對于單個公式的影響——雙模擬運算表7.3模擬運算表例：[見ch7.xls—單變量模擬運算表]

假設某人正考慮購買一套住房，要承擔一筆250000元的貸款，分15年還清?，F(xiàn)想查看每月的還貸金額，并想查看在不同的利率下，每月的應還貸金額。若貸款額分別為400000，550000，800000元，每月的應還貸金額又是多少？7.3模擬運算表4、雙變量模擬運算表概念：單變量模擬運算表只能解決一個輸入變量對一個或多個公式計算結(jié)果的影響，要查看兩個變量對公式計算結(jié)果的影響，就要用到雙變量模擬運算表。所謂雙模擬變量，就是指公式中有兩個變量。公式中兩個變量所在的單元格是任取的?？梢允枪ぷ鞅碇腥我饪瞻讍卧?。7.3模擬運算表操作步驟：1、在工作表中建立模擬運算表的結(jié)構(gòu)；2、在行列交叉處輸入模擬運算表要用到的公式；3、選擇包括公式,引用單元格和運算結(jié)果單元格區(qū)域（3部分）；4、選擇“數(shù)據(jù)”菜單—“模擬運算表”選項；5、在“模擬運算表”對話框中輸入公式中行和列引用的單用格—確定例：[見ch7.xls—雙變量模擬運算表]假設某人想貸款45萬元購買一部車，要查看在不同的利率和不同的償還年限下，每個月應還的貸款金額。假設要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，償還期限為10年、15年、20年、30年、35年時，每月應歸還的貸款金額是多少？目錄7.5方案分析1、概念方案是已命名的一組輸入值，是Excel保存在工作表中并可用來自動替換某個計算模型的輸入值，用來預測模型的輸出結(jié)果。例:已知某茶葉公司2004年的總銷售額及各種茶葉的銷售成本，現(xiàn)要在此基礎上制訂一個五年計劃。由于市場競爭的不斷變化，所以只能對總銷售額及各種茶葉銷售成本的增長率做一些估計。最好的方案當然是總銷售額增長率高，各茶葉的銷售成本增長率低。最好的估計是總銷售額增長13%，花茶、綠茶、烏龍茶、紅茶的銷售成本分別增長10%、6%、10%、7%。[見ch7.xls—方案]7.5方案分析建立方案解決工作表建立方法如下，輸入下表A列、B列及第3行的所有數(shù)據(jù)；在C4單元格中輸入公式“=B4*(1+$B$16)”，然后將其復制到D4~F4；在C7中輸入公式“=B7*(1+$B$17)”,并將其復制到D7~F7；在C8中輸入公式“=B8*(1+$B$18)”，并將其復制到D8和F8；在C9中輸入公式“=B9*(1+$B$19)”，并將其復制到D9~F9；在C10中輸入公式“=B10*(1+$B$20)”，并將其復制到D10~F10；第11行數(shù)據(jù)是第7，8，9，10行數(shù)據(jù)對應列之和；凈收入是相應的總銷售額和銷售成本之差，E19的總凈收入是第13行數(shù)據(jù)之和。

7.5方案分析輸入方案變量值如下圖所示：7.6線性規(guī)劃求解1、概述

EXCEL提供的規(guī)劃求解工具，可求解出線性與非線性兩種規(guī)劃求解問題，規(guī)劃求解問題常用于解決產(chǎn)品比例、人員調(diào)度、優(yōu)化路線、調(diào)配材料等方面問題。2、規(guī)劃求解問題的特點：問題有單一的目標，如求運輸?shù)淖罴崖肪€、求生產(chǎn)的最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時間等。問題有明確的不等式約束條件，例如生產(chǎn)材料不能超過庫存，生產(chǎn)周期不能超過一個星期等。問題有直接或間接影響約束條件的一組輸入值。

7.6線性規(guī)劃求解3、Excel規(guī)劃求解問題的組成部分（1）一個或一組可變單元格可變單元格稱為決策變量，一組決策變量代表一個規(guī)劃求解的方案（2）目標函數(shù)目標函數(shù)表示規(guī)劃求解要達到的最終目標，是規(guī)劃求解的關(guān)鍵。它是規(guī)劃求解中可變量的函數(shù)（3）約束條件約束條件是實現(xiàn)目標的限制條件。意義：通過規(guī)劃求解，用戶可為工作表的目標單元格中的公式找到一個優(yōu)化值，規(guī)劃求解將直接或間接與目標單元格公式相聯(lián)系的一組單元格數(shù)值進行調(diào)整，最終在目標單元格公式中求得期望的結(jié)果。7.6線性規(guī)劃求解建立規(guī)劃求解模型步驟：規(guī)劃求解第一步——建立求解工作表(輸入原始數(shù)據(jù)及相應的各公式)7.6線性規(guī)劃求解規(guī)劃求解第二步——設置求解參數(shù)選擇“工具”—“規(guī)劃求解”菜單，設置以下求解的各項參數(shù)：設置目標單元格：輸入目標函數(shù)所在單元格(為總余額單元格)設置目標：最大值、最小值或值的數(shù)值(最大利潤,即最大值)設置可變單元格：它的確定決定結(jié)果（為生產(chǎn)數(shù)量）設置約束條件：單擊“添加”按鈕—輸入約束條件—按添加—依次輸入所有約束條件—確定7.6線性規(guī)劃求解規(guī)劃求解第3步——保存求解結(jié)果在規(guī)劃求解對話框中按“求解”—在規(guī)劃求解結(jié)果對話框中按“保存規(guī)劃求解結(jié)果”7.6線性規(guī)劃求解4、修改資源

例1：[見ch7.xls—規(guī)劃求解]肥料廠接到一個電話：只要公司肯花10元的運費就能得到150個單位的礦物。這筆交易稍稍降低了礦物質(zhì)的平均價格，但這些礦物質(zhì)值10元嗎？解決該問題的方法是，將庫存礦物3500改為3650，用規(guī)劃求解重新計算最大盈余?？闯ィ?0的成本后，盈余是否增加操作:將庫存礦物3500改為3650,其它所有公式不變,再次進行求解,求得盈余額為4483.41,原盈余額為4425.89.可知盈利為57.52.扣除10元成本后仍有47.52.因此該礦物還是要的.7.6線性規(guī)劃求解5、修改約束條件[見ch7.xls—規(guī)劃求解]肥料廠接到一個電話，一個老顧客急需25個單位的上層肥料，公司經(jīng)理在檢查打印結(jié)果后，發(fā)現(xiàn)沒有安排生產(chǎn)上層肥料。數(shù)量為0。決定增加約束條件，為他生產(chǎn)25個單位的上層肥料。結(jié)果可發(fā)現(xiàn):盈余額僅3246.51,比原來4483.41少了1236.9。顯然不值得。但如該顧客為長期顧客，則短期內(nèi)將損失一些錢，但得到了顧客的信任。增加的約束條件7.6線性規(guī)劃求解6、規(guī)劃求解的結(jié)果報告運算結(jié)果報告：列出目標單元格、可變單元格及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。[見ch7.xls—運算結(jié)果報告]7.6線性規(guī)劃求解敏感性報告：[見ch7.xls—敏感性報告]7.6線性規(guī)劃求解極限報告：列出目標單元格、可變單元格及它們的數(shù)值、上下限和目標值。下限為在滿足約束條件和保持其它可變單元格數(shù)值不變的情況下，某個可變單元格可以取得的最小值，上限則為在這種情況下可以取到的最大值。[見ch7.xls—極限值報告]7.6線性規(guī)劃求解7、求解精度及求解模型設置

Excel采用迭代的方式進行規(guī)劃求解，當求解到一定精度時就結(jié)束求解，但有時要修改求解的精度、計算時間、規(guī)劃模型和迭代次數(shù)。修改上述設置的方法如下：在“規(guī)劃求解參數(shù)”對話框中設置好各項求解參數(shù)；單擊“選項”按鈕，在“規(guī)劃求解選項”對話框中設置各項求解參數(shù)。

7.6線性規(guī)劃求解例2：求解不等式：[見ch7.xls—規(guī)劃求解不等式]某工廠生產(chǎn)甲、乙兩種產(chǎn)品，假設生產(chǎn)甲產(chǎn)品1噸，要消耗9噸煤，4千瓦電力，3噸鋼材，獲利0.7萬元；生產(chǎn)乙產(chǎn)品1噸，要消耗4噸煤，5千瓦電力，10噸鋼材，獲利1.2萬元。按計劃國家能提供給該廠的煤為360噸，電力200千瓦，鋼材300噸，問應該生產(chǎn)多少噸甲種產(chǎn)品和乙種產(chǎn)品，才能獲得最大利潤？假設生產(chǎn)甲種產(chǎn)品X1噸，生產(chǎn)乙種產(chǎn)品x2噸，其最大利潤是求=0.7x1+1.2x2的最大值。這個問題可用數(shù)學建模如下：7.6線性規(guī)劃求解規(guī)劃求解如下：B3和C3分別用于保存甲和乙產(chǎn)品的生產(chǎn)量。目標單元格為B8；可變單元格為$B$3:$C$3；約束條件為：$B$3>=0$C$3>=0$B$4<=360$B$5<=200$B$6<=3007.6線性規(guī)劃求解例3：[見ch7.xls—線形規(guī)劃求解]某公司在A地有一個生產(chǎn)基地,其生產(chǎn)能力為400,隨著市場需求的增長及該公司業(yè)務量的增大,現(xiàn)有3個配送中心的需求都在增長,預計分別為200,400,300。公司正考慮再建立一個生產(chǎn)能力為500的工廠，準備建在B地。從A地的工廠向3個配送中心的單位運輸成本分別為5.0元，6.0元，5.4元，從B地的工廠向3個配送中心的單位運輸成本是7.0元，4.6元，6.6元。應怎樣分配A、B兩地到3個配送中心的產(chǎn)品量，才能使運輸成本最?。恳?guī)劃模型解釋如下：7.6線性規(guī)劃求解D5、E5、F5表示從A廠將1個單位產(chǎn)品分別送到配送中心1，2，3的費用；D7，E7，F(xiàn)7表示從B廠將1個單位產(chǎn)品分別送到配送中心1，2，3的費用；D6，E6，F(xiàn)6為可變單元格，保存從A廠運到3個配送中心的最佳產(chǎn)品量；D8，E8，F(xiàn)8為可變單元格，保存從B廠運到3個配送中心的最佳產(chǎn)品量；D10，E10，F(xiàn)10表示配送中心1，2，3的最大負荷能力；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，E9，F(xiàn)9分別為兩廠送到各配送中心的產(chǎn)品總量。D9公式：=D6+D8；E9公式：=E6+E8；F9公式：=F6+F8；本模型的約束條件分析如下：A，B兩廠送到各配送中心的產(chǎn)品總量不能超過各配送中心的負荷能力：D9：F9<=D10：F10A，B兩廠送到各配送中心的產(chǎn)品總量就是各自的生產(chǎn)能力；G6=H5，G8=H7A，B兩廠送到三個配送中心的產(chǎn)品量不能小于0D6：F6>=0，D8:F8>=0本模型的目標函數(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īng)濟計量分析等學科中有較強的實用價值。分析工具庫由Excel自帶的加載宏提供。如果啟動Excel后，在Excel的“工具”菜單中沒有“數(shù)據(jù)分析”菜單項，就需啟動“工具”中的“加載宏”菜單項，將“分析工具庫”加載到Excel系統(tǒng)中。如果加載宏對話框中沒有分析工具庫，則單擊加載宏對話框中“瀏覽”按鈕，定位到分析工具庫加載宏文件“Analy32.dll”所在的驅(qū)動器和文件夾，通常位于“MicrosoftOffice\Office\Library\Analysis”中,否則需運行Office系統(tǒng)的安裝程序。Excel的“分析工具庫”加載宏提供的一些統(tǒng)計函數(shù)、財務函數(shù)和工程函數(shù)。這些函數(shù)只有在安裝了“分析工具庫”后才能使用。7.7數(shù)據(jù)分析工具庫2、Excel分析工具庫中的工具分析工具名稱說明方差分析包括3種類型的分析，它們是單因素方差分析、可重復雙因素分析、無重復雙因素分析相關(guān)系數(shù)分析用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。協(xié)方差分析用于返回各數(shù)據(jù)點的一對均值偏差之間的乘積的平均值。描述統(tǒng)計分析用于生成對輸入?yún)^(qū)域中數(shù)據(jù)的單變值分析，提供有關(guān)數(shù)據(jù)趨中性和易變性的信息指數(shù)平滑分析基于前期預測值導出相應的新預測值，并修正前期預測值的誤差。7.7數(shù)據(jù)分析工具庫傅里葉分析解決線性系統(tǒng)問題，并能通過快速傅里葉變換（FFT）分析周期性的數(shù)據(jù)。F-檢驗用來比較兩個樣本總體的方差直方圖分析在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情況下，計算數(shù)據(jù)的個別和累計頻率移動平均分析基于特定的過去某段時期中變量的均值，對未來值進行預測t-檢驗分析提供3種不同檢驗：雙樣本等方差假設t-檢驗，雙樣本異方差假設t-檢驗，平均值的成對雙樣本t-檢驗回歸分析通過對一組觀察值使用“最小二乘法”直線擬合，進行線形回歸分析。抽樣分析以輸入?yún)^(qū)域為總體構(gòu)造總體的一個樣本z-檢驗雙樣本平均差檢驗7.7數(shù)據(jù)分析工具庫3、統(tǒng)計分析Excel的分析工具庫提供了3種統(tǒng)計觀測分析工具：指數(shù)平滑分析、移動平均分析和回歸分析三種工具用法相同，下面以指數(shù)平滑分析為例。[見ch7.xls—指數(shù)平滑分析]（1）在工作表的一列上輸入各時間點上的觀察值，如下圖A列所示。7.7數(shù)據(jù)分析工具庫（2）選擇“工具”菜單“數(shù)據(jù)分析”選項，對話框中選擇“指數(shù)平滑”按確定。（3）在“指數(shù)平滑”對話框中設置“輸入?yún)^(qū)域”、“阻尼系數(shù)”、“輸出區(qū)域”選項。（4）選定對話框中“圖表輸出”和“標準誤差”復選框標志。分析結(jié)果：B列為分析之后輸出的預測數(shù)據(jù)；C列是分析工具輸出的標準誤差。7.7數(shù)據(jù)分析工具庫4、假設檢驗假設檢驗是根據(jù)對事物進行抽樣所得的少量樣本信息，判斷總體分布的某個假設是否成立的一種數(shù)理統(tǒng)計方法。假設分析工具有三種：t-檢驗、z-檢驗、F-檢驗。運用這些檢驗工具可以完成均值、方差的假設檢驗。方法見下例7.7數(shù)據(jù)分析工具庫例：[見ch7.xls—t檢驗]

（雙樣本等方差t-檢驗，以確定兩個樣本均值實際上是否相等）某種子公司為比較兩個稻種的產(chǎn)量，選擇了25塊條件相似的試驗田，采用相同的耕種方法進行耕種試驗，結(jié)果播種甲稻種的13塊田的畝產(chǎn)量（單位：市斤）分別是：880、1120、980、885、828、927、924、942、766、1180、780、1068、650；播種乙稻種的12塊試驗田的畝產(chǎn)量分別是：940、1142、1020、785、645、780、1180、680、810、824、846、780。問這兩個稻種的產(chǎn)量有沒有明顯的高低之分。說明：要判斷兩稻種有無顯著差別，用t-檢驗方法，需先計算各樣本的平均值和方差，才能作進一步的檢驗分析。7.7數(shù)據(jù)分析工具庫t-檢驗操作過程：(1)輸入A、B兩列樣本數(shù)據(jù)（下表中右邊數(shù)據(jù)全為產(chǎn)生的分析結(jié)果）7.7數(shù)據(jù)分析工具庫(2)選擇“工具”菜單中“數(shù)據(jù)分析”選項，對話框中選擇t檢驗雙樣本等方差假設；（3）在“雙樣本等方差假設分析”對話框中設置t檢驗的各項參數(shù)按確定7.7數(shù)據(jù)分析工具庫5、回歸分析回歸分析主要用于分析單個因變量是如何受一個或幾個自變量影響的。如觀察某個運動員的運動成績與一系列統(tǒng)計因素的關(guān)系。如年齡、體重、身高等?；貧w分析分為線性回歸和非線性回歸兩種。線性回歸的數(shù)學模型為：Excel通過對一組觀察值使用“最小二乘法”直線擬合，進行線性回歸分析，該回歸分析可同時解決一元回歸與多元回歸問題。7.7數(shù)據(jù)分析工具庫例：（用一個多元回歸線性分析例子來說明回歸分析工具的使用方法）ch7.xls回歸分析數(shù)據(jù)表中，列出了美國1956~1970年間歷年的人均可支配收入xi和人均可消費支出yi的數(shù)據(jù)。試用圖中的數(shù)據(jù)擬合模型。模型中的趨勢變量t，用于反映除人均收入之外的所有其他因素對人均消費的影響7.7數(shù)據(jù)分析工具庫利用回歸分析工具求解此模型的方法輸入原始數(shù)據(jù)表；選擇“工具”菜單中“數(shù)據(jù)分析”選項，在“數(shù)據(jù)分析”對話框中選擇“回歸”列表。系統(tǒng)彈出如下對話框；在“回歸”對話框中輸入因變量y和自變量x的數(shù)據(jù)區(qū)域；若需要線性擬合的“殘差圖”和“線性擬合圖”等，則需選擇相應復選框本題結(jié)果見[見ch7.xls—回歸分析的輸出結(jié)果]目錄綜合實例1.單變量模擬運算表實例超市要進行一些改革，如對某些產(chǎn)品采取分期付款的方式進行銷售。假設有一液晶電視，每臺售價為98,000，采用分期付款的方式進行銷售，初步確定分期付款的方式為零首付，月分期手續(xù)費率為0.7％，求在不同的分期付款期數(shù)（月）下，每期（月）消費者需要付款的金額綜合實例【實例操作步驟】具體操作步驟如下：步驟1：創(chuàng)建工作表，建立基本的模擬運算工作表，輸入必要的數(shù)據(jù)及要測試的工作表中的數(shù)據(jù)，即不同的分期付款期數(shù)，如圖所示。綜合實例步驟2：創(chuàng)建運算公式在單元格D3中輸入公式“=PMT（B5,C3,B2）”，得出當分期付款期數(shù)為C3單元格中的數(shù)值時，消費者每月的付款額。如圖7-2所示。公式中B5為月分期手續(xù)費率，C3為分期付款期數(shù)，以月為單位，B2為付款本金。綜合實例步驟3：建立單變量模擬運算表首先要選定公式、數(shù)值序列和模擬運算結(jié)果所在的單元格區(qū)域，即C3:D12，以定義這個模擬運算表，然后選擇“數(shù)據(jù)”菜單中的“模擬運算表”命令，打開“模擬運算表”對話框，在“模擬運算表”對話框的“輸入引用列的單元格”文本框中輸入第一個變量所在的單元格地址“$C$3”，如圖所示。綜合實例【實例操作結(jié)果】最終的計算結(jié)果如圖所示。綜合實例雙變量模擬運算表實例在前面的例子中，僅僅把分期付款期數(shù)作為單變量進行模擬運算，但在現(xiàn)實生活中，月分期付款手續(xù)費率也是經(jīng)常要發(fā)生變化的。假設想查看在不同的分期付款期數(shù)和不同的月分期付款手續(xù)費率下消費者每月付款金額的變化情況，就必須建立雙變量模擬運算表了。綜合實例【實例操作步驟】步驟1：創(chuàng)建工作表建立基本的運算工作表，輸入必要的數(shù)據(jù)及要測試的工作表中的數(shù)據(jù)，即不同的月分期付款手續(xù)費率和分期付款期數(shù)，其中，月分期付款手續(xù)費率屬于行變量，分期付款期數(shù)屬于列變量。如圖所示。綜合實例步驟2：創(chuàng)建運算公式雙變量模擬運算中首先要在行、列交叉處所在的單元格輸入運算公式。在本例中，在分期付款期數(shù)和月分期付款手續(xù)費率的交叉處，即D3單元格中輸入公式“=PMT(B5,B7,B2)”，運算結(jié)果如圖所示。綜合實例步驟3：建立雙變量模擬運算表首先要選定公式、數(shù)值序列和模擬運算結(jié)果所在的單元格區(qū)域，即D3:J13，以定義這個模擬運算表，然后選擇“數(shù)據(jù)”菜單中的“模擬運算表”命令，打開“模擬運算表”對話框，在“輸入引用行的單元格”中輸入月分期手續(xù)費率所在的單元格“$B$5”，在“輸入引用列的單元格”中輸入分期付款期數(shù)所在的單元格“$B$7”，如圖所示。最后單擊“模擬運算表”對話框的“確定”按鈕。綜合實例【實例操作結(jié)果】最終的計算結(jié)果如圖所示。綜合實例利用單變量求解計算付款期數(shù)案例在上面模擬運算的例子中，主要是求出消費者在固定的分期手續(xù)費率和分期付款期數(shù)下每月應付的金額。假設現(xiàn)在有一消費者可負擔的每月的付款金額為￥5000，想知道在固定的月分期手續(xù)費率下，該消費者可承受的付款期數(shù)是多少？綜合實例【案例操作步驟】步驟1：在單變量求解工作表中輸入原始數(shù)據(jù)。如圖所示綜合實例步驟2：在月付款金額對應的單元格B4中輸入公式“=PMT(B3,B5,B2)”，如圖所示。綜合實例步驟3：選擇“工具”菜單的“單變量求解”命令，打開“單變量求解”對話框，在“目標單元格”中輸入“$B$4”，在“目標值”中輸入“-5000”，表示經(jīng)過求解之后，單元格B4的值應是-5000，目標單元格必須包含公式，在“可變單元格”中輸入“$B$5”，即最后分期付款期數(shù)所在的單元格，如左圖所示。接著在單擊“確定”按鈕后，屏幕上可能出現(xiàn)出錯提示，表示無法進行求解，如右圖所示。綜合實例在這里可以先給可變單元格設置一個值，比如5，因為其中的數(shù)值也要根據(jù)目標單元格中的結(jié)果而發(fā)生變化，所以事先給的值并不會影響到最終的計算結(jié)果。具體設置如左圖所示。步驟4：再打開“單變量求解”對話框，按上面同樣的設置，可得到“單變量求解狀態(tài)”對話框，表明求得一個解，如右圖所示。綜合實例【案例操作結(jié)果】求解結(jié)果如圖所示。綜合實例超市收入預測方案實例假設已知超市在2007年的各部門的收入情況，現(xiàn)在想對2008年的收入情況做一個估計，分析在不同增長方案下的收入情況，這些增長方案包括經(jīng)營情況一般、經(jīng)營情況良好和經(jīng)營情況較差三種方案。綜合實例【案例操作步驟】具體操作步驟如下：步驟1：制作如圖所示的工作表，包括2007年度的收入情況，以及2008年度根據(jù)經(jīng)營情況（一般）設置的收入增長率。其中在單元格D3輸入公式“=C3-B3”，然后將其復制到D4、D5、D6；在單元格D11中輸入公式“=C3*(1+C11)-B3*(1+B11)”，并將其復制到D12、D13、D14；在單元格D15中輸入公式“=SUM(D11:D14)”。從圖中可以看出，在經(jīng)營情況一般這種方案下，2008年企業(yè)的總利潤為。綜合實例步驟2：選擇“工具”菜單的“方案”命令，彈出“方案管理器”對話框，如圖所示。綜合實例步驟3：在“方案管理器”中單擊“添加”按鈕，彈出“編輯方案”對話框，在“方案名”中輸入方案名稱“經(jīng)營情況一般”；在“可變單元格”中輸入需要更改的單元格的引用“$B$11:$C$14”，選中“防止更改”復選項。如圖所示。綜合實例步驟4：在上圖所示的對話框中單擊“確定”按鈕，將打開“方案變量值”對話框。在“方案變量值”對話框中分別輸入可變單元格所對應的單元格，如圖所示。綜合實例步驟5：在上圖所示的對話框中單擊“確定”按鈕，則該方案創(chuàng)建完成，返回“方案管理器”對話框，將顯示出所定義方案的名稱。如圖所示。綜合實例步驟6：在“方案管理器”對話框中單擊“添加”按鈕，使用類似的步驟再創(chuàng)建兩個方案?！緦嵗僮鹘Y(jié)果】最終結(jié)果如圖所示綜合實例利用規(guī)劃求解制定銷售決策實例假設超市某一部門要銷售兩種商品，其中A商品的采購價為900元，銷售價為2400元，B商品的采購價位800元，銷售價為1800元，而且兩種商品的物流和倉儲成本不同，A商品的物流成本和倉儲成本分別為30元和19.5元，B商品物流成本和倉儲成本分別為25元和6.5元，現(xiàn)在的問題是在每月的物流和倉儲費用固定的情況下（物流和倉儲總費用分別為2800元和1500元）。該如何分配A、B兩種商品的進貨數(shù)量，才能得到最大的銷售利潤？綜合實例【實例操作步驟】具體操作步驟如下：步驟1：建立商品數(shù)據(jù)表，如圖所示。綜合實例步驟2：輸入公式，在B12單元格中輸入公式“=(B6-B5-B4-B3)*B7+(C6-C5-C4-C3)*C7”，在單元格B13和B14中分別輸入“=B4*B7+C4*C7”和“=B5*B7+C5*C7”。如圖所示。綜合實例步驟3：建立好上面表格后，就可以利用規(guī)劃求解工具對表格的問題進行求解了。選擇“工具”菜單中的“規(guī)劃求解”命令，彈出“規(guī)劃求解參數(shù)”對話框，如圖所示。在“設置目標單元格”文本框中輸入B12單元格，即要求的銷售最大利潤。在“等于”單選項中選擇“最大值”，表示所要求的目標函數(shù)的最大值。在“可變單元格”文本框中輸入B7:C7，就是目標函數(shù)的兩個變量，也就是兩種商品各自的進貨量。這里的引用均為絕對引用。綜合實例步驟4：設置規(guī)劃求解約束條件。選擇“規(guī)劃求解參數(shù)”對話框中的“添加”按鈕，彈出“添加約束”對話框，在對話框中添加限制條件。對于物流總成本的限制，應該是物流總成本小于每月的物流費用控制，即B13≤B9。添加完該約束后，單擊對話框中的“添加”按