excel與數(shù)據(jù)處理之?dāng)?shù)據(jù)分析工具及應(yīng)用-文庫吧在線文庫
【正文】 大值、最小值或值的數(shù)值 (最大利潤 ,即最大值 )n 設(shè)置可變單元格：它的確定決定結(jié)果（為生產(chǎn)數(shù)量）n 設(shè)置約束條件：單擊 “添加 ”按鈕 —輸入約束條件 —按添加 —依次輸入所有約束條件 —確定 線性規(guī)劃求解n 規(guī)劃求解第 3步 ——保存求解結(jié)果在規(guī)劃求解對話框中按 “求解 ”—在規(guī)劃求解結(jié)果對話框中按 “保存規(guī)劃求解結(jié)果 ” 線性規(guī)劃求解修改資源 例 1： [見 — 規(guī)劃求解 ]n 肥料廠接到一個電話：只要公司肯花 10元的運費就能得到 150個單位的礦物。增加的約束條件 線性規(guī)劃求解 規(guī)劃求解的結(jié)果報告n 運算結(jié)果報告：列出目標(biāo)單元格、可變單元格及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。n 目標(biāo)單元格為 B8； 可變單元格為 $B$3:$C$3； 約束條件為：n $B$3=0n $C$3=0n $B$4=360n $B$5=200n $B$6=300 線性規(guī)劃求解n 例 3： [見 —線形規(guī)劃求解 ]n 某公司在 A地有一個生產(chǎn)基地 ,其生產(chǎn)能力為 400,隨著市場需求的增長及該公司業(yè)務(wù)量的增大 ,現(xiàn)有 3個配送中心的需求都在增長 ,預(yù)計分別為 200,400,300。 如果啟動 Excel后，在 Excel的 “ 工具 ” 菜單中沒有 “數(shù)據(jù)分析 ” 菜單項，就需啟動 “ 工具 ” 中的 “ 加載宏” 菜單項，將 “ 分析工具庫 ” 加載到 Excel系統(tǒng)中。F檢驗 用來比 較 兩個 樣 本 總 體的方差直方 圖 分析在 給 定工作表中數(shù)據(jù) 單 元格區(qū)域和接收區(qū) 間 的情況下， 計 算數(shù)據(jù)的個 別 和累 計頻 率移 動 平均分析基于特定的 過 去某段 時 期中 變 量的均 值 ， 對 未來 值進 行 預(yù)測t檢驗 分析提供 3種不同 檢驗 ：雙 樣 本等方差假 設(shè) t檢驗 ，雙 樣 本異方差假 設(shè) t檢驗 ，平均 值 的成 對 雙 樣本 t檢驗回 歸 分析 通 過對 一 組觀 察 值 使用 “最小二乘法 ”直 線擬 合， 進 行 線 形回 歸 分析。n 假設(shè)分析工具有三種： t檢驗、 z檢驗、 F檢驗。n 回歸分析分為線性回歸和非線性回歸兩種。如圖 72所示。綜合實例n 步驟 2：創(chuàng)建運算公式n 雙變量模擬運算中首先要在行、列交叉處所在的單元格輸入運算公式。綜合實例n 步驟 3：選擇 “工具 ”菜單的 “單變量求解 ”命令，打開 “單變量求解 ”對話框，在 “目標(biāo)單元格 ”中輸入 “$B$4”，在 “目標(biāo)值 ”中輸入 “5000”，表示經(jīng)過求解之后，單元格 B4的值應(yīng)是 5000，目標(biāo)單元格必須包含公式，在 “可變單元格 ”中輸入 “$B$5”，即最后分期付款期數(shù)所在的單元格，如左圖所示。其中在單元格 D3輸入公式“=C3B3”，然后將其復(fù)制到D D D6；在單元格 D11中輸入公式 “=C3*(1+C11)B3*(1+B11)”，并將其復(fù)制到 D1 D1 D14；在單元格 D15中輸入公式“=SUM(D11:D14)”。如圖所示。在 “設(shè)置目標(biāo)單元格 ”文本框中輸入 B12單元格，即要求的銷售最大利潤。最終添加完約束條件的結(jié)果如圖所示。單擊 “確定 ”按鈕，回歸分析的摘要就輸出在本工作表上。綜合實例n 【 實例操作結(jié)果 】n 最終求解結(jié)果如圖所示。某人欲貸款 100萬元買一幢別墅，此人每月有還 1萬元的能力，基于年利率 7%每月末還款至少多久能還清？思考用函數(shù)和用單變量求解兩種方法。要分析兩個變量之間的因果關(guān)系， 模擬運算和單變量求解 是一個較佳的工具。綜合實例n 【 實例操作步驟 】n 對于實例所提出的問題，可以通過直方圖分析工具來解決。綜合實例n 利用數(shù)據(jù)分析工具進行銷售預(yù)測實例n 超市有關(guān)部門要作如下的銷售預(yù)測。 綜合實例n 步驟 4：設(shè)置規(guī)劃求解約束條件。綜合實例n 步驟 2：輸入公式，在 B12單元格中輸入公式 “=(B6B5B4B3)*B7+(C6C5C4C3)*C7”，在單元格 B13和B14中分別輸入 “=B4*B7+C4*C7”和“=B5*B7+C5*C7”。如圖所示。n 步驟 4：再打開 “單變量求解 ”對話框，按上面同樣的設(shè)置，可得到 “單變量求解狀態(tài) ”對話框，表明求得一個解，如右圖所示。 綜合實例n 【 實例操作結(jié)果 】n 最終的計算結(jié)果如圖所示。 綜合實例n 雙變量模擬運算表實例n 在前面的例子中，僅僅把分期付款期數(shù)作為單變量進行模擬運算，但在現(xiàn)實生活中，月分期付款手續(xù)費率也是經(jīng)常要發(fā)生變化的。n 模型中的趨勢變量 t，用于反映除人均收入之外的所有其他因素對人均消費的影響 數(shù)據(jù)分析工具庫n 利用回歸分析工具求解此模型的方法n 輸入原始數(shù)據(jù)表；n 選擇 “工具 ”菜單中 “數(shù)據(jù)分析 ”選項，在 “數(shù)據(jù)分析 ”對話框中選擇 “回歸 ”列表。 說明：要判斷兩稻種有無顯著差別，用 t檢驗方法，需先計算各樣本的平均值和方差，才能作進一步的檢驗分析。 （ 3）在 “ 指數(shù)平滑 ” 對話框中設(shè)置 “ 輸入?yún)^(qū)域 ” 、 “ 阻尼系數(shù) ” 、 “ 輸出區(qū)域 ” 選項。 數(shù)據(jù)分析工具庫 Excel分析工具庫中的工具分析工具名稱說 明方差分析 包括 3種 類 型的分析，它 們 是 單 因素方差分析、可重復(fù)雙因素分析、無重復(fù)雙因素分析相關(guān)系數(shù)分析用于判斷兩 組 數(shù)據(jù)集（可以使用不同的度量 單 位）之 間 的關(guān)系。G6公式： =D6+E6+F6；G8公式： =G8+E8+F8；D9， E9， F9分別為兩廠送到各配送中心的產(chǎn)品總量。修改上述設(shè)置的方法如下： 在 “規(guī)劃求解參數(shù) ”對話框中設(shè)置好各項求解參數(shù)； 單擊 “選項 ”按鈕，在 “規(guī)劃求解選項 ”對話框中設(shè)置各項求解參數(shù)。決定增加約束條件，為他生產(chǎn) 25個單位的上層肥料。 線性規(guī)劃求解例： [見 —規(guī)劃求解 ]n 某肥料廠專門收集有機物垃圾，如青草、樹枝、凋謝的花朵等。 [見 —方案 ] 方案分析n 建立方案解決工作表建立方法如下，輸入下表 A列、 B列及第 3行的所有數(shù)據(jù)；在 C4單元格中輸入公式 “=B4*(1+$B$16)”，然后將其復(fù)制到 D4~F4；在 C7中輸入公式“=B7*(1+$B$17)”,并將其復(fù)制到 D7~F7；在 C8中輸入公式“=B8*(1+$B$18)”，并將其復(fù)制到 D8和 F8；在 C9中輸入公式“=B9*(1+$B$19)”，并將其復(fù)制到 D9~F9；在 C10中輸入公式“=B10*(1+$B$20)”，并將其復(fù)制到 D10~F10；第 11行數(shù)據(jù)是第 7， 8， 9， 10行數(shù)據(jù)對應(yīng)列之和；凈收入是相應(yīng)的總銷售額和銷售成本之差， E19的總凈收入是第 13行數(shù)據(jù)之和?？梢允枪ぷ鞅碇腥我饪瞻讍卧瘛Ｄ夸? 模擬運算表 概念n 模擬運算表是對工作表中一個單元格區(qū)域內(nèi)的數(shù)據(jù)進行模擬運算，測試使用一個或兩個變量的公式中變量對運算結(jié)果的影響。n 限定數(shù)據(jù)類型和有效范圍： 如：限定數(shù)據(jù)大小范圍、日期的范圍、輸入字符的個數(shù)、單元格的公式 數(shù)據(jù)審核及跟蹤分析數(shù)據(jù)限制的操作方法：選擇 “數(shù)據(jù) ” —“有效性 ”—在對話 框中操作：限定文本長度： “設(shè)置 ”選項卡中 “允許 ” 下拉列表中
外語相關(guān)推薦
鄂ICP備17016276號-1