ch數(shù)據(jù)審核與數(shù)據(jù)分析

本章學(xué)習(xí)目標(biāo)1、了解加載宏的使用方法2、掌握數(shù)據(jù)審核的方法3、掌握模擬運(yùn)算表的建立方法4、掌握單變量求解的方法5、掌握方案分析的方法6、掌握線性規(guī)劃求解的方法7、掌握數(shù)據(jù)分析工具的應(yīng)用方法

8.1加載宏與分析工具安裝加載宏的概念加載宏是一種可選擇性地安裝到計(jì)算機(jī)中的軟件組件，用戶可根據(jù)需要決定是否安裝。其作用是為Excel添加命令和函數(shù)，擴(kuò)充Excel的功能。Excel加載宏的擴(kuò)展名是.xla或.xll。8.1加載宏與分析工具安裝加載宏描述

與Access鏈接創(chuàng)建可以使用Excel數(shù)據(jù)的Access窗體和報(bào)表，并將Excel數(shù)據(jù)導(dǎo)入到Access中。要使用AccessLinks加載宏，必須先安裝MicrosoftAccess

分析工具庫(kù)添加財(cái)務(wù)、統(tǒng)計(jì)和工程分析工具和函數(shù)

“自動(dòng)保存”加載宏以指定間隔自動(dòng)保存工作簿

條件求和向?qū)?duì)于數(shù)據(jù)清單中滿足指定條件的數(shù)據(jù)進(jìn)行求和計(jì)算

歐元工具將數(shù)值的格式設(shè)置為歐元的格式，并提供EUROCONVERT函數(shù)以用于貨幣轉(zhuǎn)換

查閱向?qū)?chuàng)建一個(gè)公式，通過(guò)數(shù)據(jù)清單中的已知值查找所需數(shù)據(jù)

ODBC加載宏利用安裝的ODBC驅(qū)動(dòng)程序，通過(guò)開(kāi)放式數(shù)據(jù)庫(kù)互連（ODBC）功能與外部數(shù)據(jù)源相連

報(bào)告管理器為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視圖以及方案的報(bào)告

規(guī)劃求解加載宏對(duì)基于可變單元格和條件單元格的假設(shè)分析方案進(jìn)行求解計(jì)算MSQuery加載宏將采用MicrosoftExcel97和MicrosoftExcel2000格式的外部數(shù)據(jù)區(qū)域轉(zhuǎn)換為MicrosoftExcel5.0/95格式，并允許使用通過(guò)以前版本Excel中的VisualBasicforApplications所創(chuàng)建的宏模板工具提供Excel的內(nèi)置模板所使用的工具。使用內(nèi)置模板時(shí)就可自動(dòng)訪問(wèn)這些工具數(shù)據(jù)追蹤模板向?qū)赡０澹ㄟ^(guò)與之鏈接的數(shù)據(jù)庫(kù)中的記錄進(jìn)行數(shù)據(jù)跟蹤和分析更新加載宏鏈接更新對(duì)Excel4.0版加載宏的鏈接，以便能直接訪問(wèn)Excel5.0/7.0、Excel97和Excel2000的內(nèi)置功能InternetAssistantVBA通過(guò)使用Excel97InternetAssistant語(yǔ)法，開(kāi)發(fā)者可將Excel數(shù)據(jù)發(fā)布到Web上2、Excel內(nèi)置加載宏8.1加載宏與分析工具安裝3、加載宏的安裝選擇“工具”|“加載宏”菜單8.2數(shù)據(jù)審核及跟蹤分析1、概念數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯(cuò)誤來(lái)源的工具，通過(guò)它可以快速地找出具有引用關(guān)系的單元格，借此分析造成錯(cuò)誤的單元格。數(shù)據(jù)審核使用追蹤箭頭，通過(guò)圖形的方式顯示或追蹤單元格與公式之間的關(guān)系。2、數(shù)據(jù)審核的方式追蹤引用單元格追蹤從屬單元格8.2數(shù)據(jù)審核及跟蹤分析3、追蹤引用單元格示例8.2數(shù)據(jù)審核及跟蹤分析4、數(shù)據(jù)有效性限制數(shù)據(jù)的范圍案例某班要建立一個(gè)成績(jī)登記表，為了減少成績(jī)輸入錯(cuò)誤，可以對(duì)成績(jī)表中數(shù)據(jù)的輸入類型及范圍進(jìn)行限制。限制學(xué)號(hào)為8位字符，不能小于8位，也不能多于8位。限制所有學(xué)科成績(jī)?yōu)?~100之間的整數(shù)。限制科目列標(biāo)題的取值范圍，如“高數(shù)”不能輸入為“高等數(shù)學(xué)”。

8.2數(shù)據(jù)審核及跟蹤分析成績(jī)表示例8.2數(shù)據(jù)審核及跟蹤分析限制方法選擇“數(shù)據(jù)”|“有效性”限定數(shù)據(jù)類型限定方式設(shè)置數(shù)據(jù)大小范圍或長(zhǎng)度8.2數(shù)據(jù)審核及跟蹤分析5、圈釋無(wú)效數(shù)據(jù)8.3模擬運(yùn)算表1、概念模擬運(yùn)算表是對(duì)工作表中一個(gè)單元格區(qū)域內(nèi)的數(shù)據(jù)進(jìn)行模擬運(yùn)算，測(cè)試使用一個(gè)或兩個(gè)變量的公式中變量對(duì)運(yùn)算結(jié)果的影響。2、模擬運(yùn)算表的類型①基于一個(gè)輸入變量的表，用這個(gè)輸入變量測(cè)試它對(duì)多個(gè)公式的影響；——單模擬運(yùn)算表②基于兩個(gè)輸入變量的表，用這兩個(gè)變量測(cè)試它們對(duì)于單個(gè)公式的影響——雙模擬運(yùn)算表8.3模擬運(yùn)算表1、單變量模擬運(yùn)算表概念在單變量模擬運(yùn)算表中，輸入數(shù)據(jù)的值被安排在一行或一列中。同時(shí)，單變量模擬表中使用的公式必須引用“輸入單元格”。所謂輸入單元格，就是被替換的含有輸入數(shù)據(jù)的單元格案例假設(shè)某人正考慮購(gòu)買一套住房，要承擔(dān)一筆250000元的貸款，分15年還清?，F(xiàn)想查看每月的還貸金額，并想查看在不同的利率下，每月的應(yīng)還貸金額。8.3模擬運(yùn)算表1、建立模擬運(yùn)算表3、C4的公式中引用了B4單元格，在實(shí)際計(jì)算時(shí)，將用B列B5:B11的值逐一代替公式中的B4。2、選擇“數(shù)據(jù)”|“模擬運(yùn)算表”8.3模擬運(yùn)算表案例假設(shè)某人想貸款45萬(wàn)元購(gòu)買一部車，要查看在不同的利率和不同的償還年限下，每個(gè)月應(yīng)還的貸款金額。假設(shè)要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，償還期限為10年、15年、20年、30年、35年時(shí)，每月應(yīng)歸還的貸款金額是多少8.3模擬運(yùn)算表模擬運(yùn)算表的設(shè)置=PMT(B1/12,B2*12,D1)8.4單變量求解概念所謂單變量求解，就是求解具有一個(gè)變量的方程，Excel通過(guò)調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來(lái)滿足目標(biāo)單元格中的目標(biāo)值.案例某公司想向銀行貸款900萬(wàn)元人民幣，貸款利率是8.7%，貸款限期為8年，每年應(yīng)償還多少金額？如果公司每年可償還120萬(wàn)元，該公司最多可貸款多少金額？8.4單變量求解單變量求解方法1、建立求解公式：2、設(shè)置求解公式3、求解結(jié)果8.5方案分析1、概念方案是已命名的一組輸入值，是Excel保存在工作表中并可用來(lái)自動(dòng)替換某個(gè)計(jì)算模型的輸入值，用來(lái)預(yù)測(cè)模型的輸出結(jié)果。2、案例已知某茶葉公司2001年的總銷售額及各種茶葉的銷售成本，現(xiàn)要在此基礎(chǔ)上制訂一個(gè)五年計(jì)劃。由于市場(chǎng)竟?fàn)幍牟粩嘧兓?，所以只能?duì)總銷售額及各種茶葉銷售成本的增長(zhǎng)率做一些估計(jì)。最好的方案當(dāng)然是總銷售額增長(zhǎng)率高，各茶葉的銷售成本增長(zhǎng)率低。最好的估計(jì)是總銷售額增長(zhǎng)13%，花茶、綠茶、烏龍茶、紅茶的銷售成本分別增長(zhǎng)10%、6%、10%、7%。

8.5方案分析建立方案解決工作表建立方法如下，輸入下表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中輸入公式“=B9*(1+$B$20)”，并將其復(fù)制到D10~F10；第11行數(shù)據(jù)是第7，8，9，10行數(shù)據(jù)對(duì)應(yīng)列之和；凈收入是相應(yīng)的總銷售額和銷售成本之差，E19的總凈收入是第13行數(shù)據(jù)之和。8.5方案分析建立方案（1）選擇“工具”|“方案”菜單單擊添加按鈕，然后在添加方案對(duì)話框中輸入方案名8.5方案分析輸入方案變量8.5方案分析建立方案報(bào)告8.5方案分析建立方案透視圖8.6線性規(guī)劃求解1、規(guī)劃求解問(wèn)題的特點(diǎn)：?jiǎn)栴}有單一的目標(biāo)，如求運(yùn)輸?shù)淖罴崖肪€、求生產(chǎn)的最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時(shí)間等。問(wèn)題有明確的不等式約束條件，例如生產(chǎn)材料不能超過(guò)庫(kù)存，生產(chǎn)周期不能超過(guò)一個(gè)星期等。問(wèn)題有直接或間接影響約束條件的一組輸入值。8.6線性規(guī)劃求解2、Excel規(guī)劃求解問(wèn)題由以下3部分組成（1）可變單元格（2）目標(biāo)函數(shù)（3）約束條件8.6線性規(guī)劃求解3、案例某肥料廠專門收集有機(jī)物垃圾，如青草、樹(shù)枝、凋謝的花朵等。該廠利用這些廢物，并摻進(jìn)不同比例的泥土和礦物質(zhì)來(lái)生產(chǎn)高質(zhì)量的植物肥料，生產(chǎn)的肥料分為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料4種。為使問(wèn)題簡(jiǎn)單，假設(shè)收集廢物的勞動(dòng)力是自愿的，除了收集成本之外，材料成本是低廉的。該廠目前的原材料、生產(chǎn)各種肥料需要的原材料比例，各種肥料的單價(jià)等如下各表所示。問(wèn)題：求出在現(xiàn)有的情況下，即利用原材料的現(xiàn)有庫(kù)存，應(yīng)生產(chǎn)各種類型的肥料各多少數(shù)量才能獲得最大利潤(rùn)，最大利潤(rùn)是多少？8.6線性規(guī)劃求解表2生產(chǎn)肥料的庫(kù)存原材料庫(kù)存情況現(xiàn)有庫(kù)存泥土4100有機(jī)垃圾3200礦物質(zhì)3500修剪物1600表1各肥料成品用料及其價(jià)格表產(chǎn)品泥土有機(jī)垃圾礦物質(zhì)修剪物單價(jià)底層肥料55547623105.00中層肥料6432452084.00上層肥料43329844105.00劣質(zhì)肥料1845231857.00表3單位原材料成本單價(jià)項(xiàng)目單位成本泥土0.20有機(jī)垃圾0.15礦物質(zhì)0.10修剪物0.238.6線性規(guī)劃求解規(guī)劃求解第一步——建立求解工作表8.6線性規(guī)劃求解規(guī)劃求解第二步——設(shè)置求解參數(shù)選擇“工具”|“規(guī)劃求解”菜單，設(shè)置求解的各項(xiàng)參數(shù)，如下圖所示。8.6線性規(guī)劃求解規(guī)劃求解第3步——求解8.6線性規(guī)劃求解修改資源肥料廠接到一個(gè)電話：只要公司肯花10元的運(yùn)費(fèi)就能得到150個(gè)單位的礦物。這筆交易稍稍降低了礦物質(zhì)的平均價(jià)格，但這些礦物質(zhì)值10元嗎？解決該問(wèn)題的方法是，將庫(kù)存礦物3500改為3650，用規(guī)劃求解重新計(jì)算最大盈余?？闯ィ?0的成本后，盈余是否增加8.6線性規(guī)劃求解修改約束條件肥料廠接到一個(gè)電話，一個(gè)老顧客急需25個(gè)單位的上層肥料，公司經(jīng)理在檢查打印結(jié)果后，發(fā)現(xiàn)沒(méi)有安排生產(chǎn)上層肥料。決定增加約束條件，為他生產(chǎn)25個(gè)單位的上層肥料。增加的約束條件8.6線性規(guī)劃求解

規(guī)劃求解的結(jié)果報(bào)告運(yùn)算結(jié)果報(bào)告8.6線性規(guī)劃求解2、敏感性報(bào)告8.6線性規(guī)劃求解3、極限報(bào)告8.7數(shù)據(jù)分析工具庫(kù)1、概述分析工具庫(kù)由Excel自帶的加載宏提供，如果啟動(dòng)Excel后，在Excel的“工具”菜單中沒(méi)有“數(shù)據(jù)分析”菜單項(xiàng)，就需啟動(dòng)“工具”中的“加載宏”菜單項(xiàng)，將“分析工具庫(kù)”加載到Excel系統(tǒng)中。Excel的“分析工具庫(kù)”加載宏還提供了一些統(tǒng)計(jì)函數(shù)、財(cái)務(wù)函數(shù)和工程函數(shù)。這些函數(shù)只有在安裝了“分析工具庫(kù)”后才能使用8.7數(shù)據(jù)分析工具庫(kù)分析工具名稱說(shuō)明方差分析包括3種類型的分析，它們是單因素方差分析、可重復(fù)雙因素分析、無(wú)重復(fù)雙因素分析相關(guān)系數(shù)分析用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。協(xié)方差分析用于返回各數(shù)據(jù)點(diǎn)的一對(duì)均值偏差之間的乘積的平均值。描述統(tǒng)計(jì)分析用于生成對(duì)輸入?yún)^(qū)域中數(shù)據(jù)的單變值分析，提供有關(guān)數(shù)據(jù)趨中性和易變性的信息指數(shù)平滑分析基于前期預(yù)測(cè)值導(dǎo)出相應(yīng)的新預(yù)測(cè)值，并修正前期預(yù)測(cè)值的誤差。傅里葉分析解決線性系統(tǒng)問(wèn)題，并能通過(guò)快速傅里葉變換（FFT）分析周期性的數(shù)據(jù)。F-檢驗(yàn)用來(lái)比較兩個(gè)樣本總體的方差直方圖分析在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情況下，計(jì)算數(shù)據(jù)的個(gè)別和累計(jì)頻率移動(dòng)平均分析基于特定的過(guò)去某段時(shí)期中變量的均值，對(duì)未來(lái)值進(jìn)行預(yù)測(cè)t-檢驗(yàn)分析雙樣本等方差假設(shè)t-檢驗(yàn)，雙樣本異方差假設(shè)t-檢驗(yàn)，平均值的成對(duì)雙樣本t-檢驗(yàn)回歸分析通過(guò)對(duì)一組觀察值使用“最小二乘法”直線擬合，進(jìn)行線形回歸分析。抽樣分析以輸入?yún)^(qū)域?yàn)榭傮w構(gòu)造總體的一個(gè)樣本z-檢驗(yàn)雙樣本平均差檢驗(yàn)2、Excel分析工具庫(kù)中的工具8.7數(shù)據(jù)分析工具庫(kù)3、統(tǒng)計(jì)分析Excel的分析工具庫(kù)提供了3種統(tǒng)計(jì)觀測(cè)分析的工具：指數(shù)平滑分析、移動(dòng)平均分析和回歸分析。三種工具的用法相同，以指數(shù)平滑分析為例說(shuō)明其用法。（1）在工作表的一列上輸入各時(shí)間點(diǎn)上的觀察值，如下圖A列所示。8.7數(shù)據(jù)分析工具庫(kù)（2）選擇“工具”“數(shù)據(jù)分析”菜單項(xiàng)，從彈出的對(duì)話框中選擇“指數(shù)平滑”。（3）設(shè)置“輸入”、“輸出”選項(xiàng)，如下圖所示。8.7數(shù)據(jù)分析工具庫(kù)4、假設(shè)檢驗(yàn)Excel的分析工具庫(kù)中也提供了一些假設(shè)分析工具，如：t-檢驗(yàn)、z-檢驗(yàn)、F-檢驗(yàn)。運(yùn)用檢驗(yàn)工具可以輕松地完成均值、