數(shù)據(jù)分析數(shù)據(jù)分析:根據(jù)電子表格中的數(shù)據(jù)以圖形或數(shù)值的方式得出一些有意義的結(jié)論.比如:數(shù)據(jù)的排序,篩選,運算,統(tǒng)計,圖表化等等都可以說是數(shù)據(jù)分析的手段.這里我們主要講以下幾個數(shù)據(jù)分析問題:一.變量求解:有些數(shù)據(jù)之間存在因果關(guān)系,這些因果關(guān)系可以用數(shù)學(xué)模型來描述,如方程式,不等式等,如果知道了結(jié)果數(shù)據(jù),能否根據(jù)這些數(shù)學(xué)模型求出原因變量的值?EXCEL提供一些這樣的方法,如單變量求解,規(guī)劃求解等.二.模擬運算表:根據(jù)數(shù)學(xué)模型(公式)為一組假設(shè)數(shù)據(jù)產(chǎn)生預(yù)測結(jié)果,以供分析決策三.相關(guān)性分析:判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。四.回歸分析:當一個結(jié)果與一個或多個參數(shù)之間存在聯(lián)系時，可以進行回歸分析，通?？捎梢粋€或多個自變量來預(yù)測一個變量的值。

1精選課件ppt數(shù)據(jù)分析方法1.分析工具庫

Excel提供了一組數(shù)據(jù)分析工具，稱為“分析工具庫”，在建立復(fù)雜統(tǒng)計或工程分析時可節(jié)省步驟。只需為每一個分析工具提供必要的數(shù)據(jù)和參數(shù)，該工具就會使用適宜的統(tǒng)計或工程函數(shù)，在輸出表格中顯示相應(yīng)的結(jié)果。其中有些工具在生成輸出表格時還能同時生成圖表。安裝“分析工具庫”:

要使用查看可用的分析工具，請單擊“工具”菜單中的“數(shù)據(jù)分析”命令。如果“工具”菜單中沒有“數(shù)據(jù)分析”命令，則需要安裝“分析工具庫”。2.工作表函數(shù)

Excel還提供了其他一些統(tǒng)計、財務(wù)和工程函數(shù)。某些統(tǒng)計函數(shù)是內(nèi)置函數(shù)，而其他一些函數(shù)只有在安裝了“分析工具庫”之后才能使用。預(yù)備知識要使用這些工具，用戶還必須熟悉需要進行分析的統(tǒng)計學(xué)或工程學(xué)的特定領(lǐng)域。2精選課件ppt三.相關(guān)性分析相關(guān)性分析：判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。相關(guān)系數(shù)（R）：相關(guān)性計算的返回值。用來確定兩個區(qū)域中數(shù)據(jù)的變化是否相關(guān)，以及相關(guān)的程度。是兩組數(shù)據(jù)集的協(xié)方差除以它們標準偏差的乘積。1、R>0：一個集合的較大數(shù)據(jù)與另一個集合的較大數(shù)據(jù)相對應(yīng)（正相關(guān)）2、R<0：一個集合的較大數(shù)據(jù)與另一個集合的較小數(shù)據(jù)相對應(yīng)（負相關(guān)）3、R=0：兩個集合中的數(shù)據(jù)互不相關(guān)。|R|<0.4：低度相關(guān)；0.4<=|R|<0.6:中度相關(guān)；0.6<=|R|<0.8:高度相關(guān)；|R|>=0.8:非常高度相關(guān)3精選課件ppt1、CORREL函數(shù)：

CORREL(array1,array2)返回單元格區(qū)域array1和array2之間的相關(guān)系數(shù)。使用相關(guān)系數(shù)可以確定兩種屬性之間的關(guān)系。例如，可以檢測某地的平均溫度和空調(diào)使用情況之間的關(guān)系。Array1

第一組數(shù)值單元格區(qū)域。Array2

第二組數(shù)值單元格區(qū)域。如果數(shù)組或引用包含文本、邏輯值或空白單元格，這些數(shù)值將被忽略，但是包含零值的單元格將計算在內(nèi)。

如果array1和array2的數(shù)據(jù)點的數(shù)目不同，函數(shù)CORREL返回錯誤值#N/A。

4精選課件ppt例1、利用統(tǒng)計數(shù)據(jù)計算廣告費與銷售額之間的相關(guān)系數(shù)。相關(guān)系數(shù)CORREL(B4:B15,C4:C15)=0.922518185精選課件ppt例2.我們收集了某廠家同一車型中舊車的車齡及其售價數(shù)據(jù)，求價格與車齡的相關(guān)系數(shù)6精選課件ppt例3、總平均成績、出勤率、選修學(xué)分與每周打工小時數(shù)的關(guān)系。2、使用數(shù)據(jù)分析工具求相關(guān)矩陣利用CORREL函數(shù)只能返回兩種屬性之間的相關(guān)系數(shù)，如果使用Excel提供的“數(shù)據(jù)分析”工具可以計算多組數(shù)據(jù)間的相關(guān)系數(shù)，組成相關(guān)系數(shù)表。7精選課件ppt例利用“數(shù)據(jù)分析”工具計算汽車扳金、省油與價格的滿意度之間的相關(guān)系數(shù)。8精選課件ppt回歸分析:當一個結(jié)果與一個或多個參數(shù)之間存在聯(lián)系時，可以進行回歸分析，通?？捎梢粋€或多個自變量來預(yù)測一個變量的值。回歸方程:表達參數(shù)與結(jié)果之間相互關(guān)系的數(shù)學(xué)方程式(數(shù)學(xué)模型)線性回歸:如果變量與結(jié)果之間具有線性關(guān)系,我們可以用線性方程式來描述它們之間的關(guān)系,這種回歸方法叫線性回歸.非線性回歸:如果變量與結(jié)果之間不具有線性關(guān)系,我們必須用非線性方程式來描述它們之間的關(guān)系(如指數(shù)關(guān)系,對數(shù)關(guān)系等等),這種回歸方法叫非線性回歸.單回歸:當一個結(jié)果只與一個參數(shù)存在聯(lián)系時，進行的回歸分析稱為單回歸。復(fù)回歸:當一個結(jié)果與多個參數(shù)存在聯(lián)系時，進行的回歸分析稱為復(fù)回歸。判定系數(shù)(R2):用來確定回歸方程式的可解釋性，即吻合程度。范圍在0-1之間，越接近1，解釋性越強，即吻合程度越高?；貧w方法：1、給圖表增加趨勢線；2、使用Excel提供的“數(shù)據(jù)分析工具”；3、利用回歸函數(shù)四.回歸分析9精選課件ppt例4(線性回歸)、我們收集了某廠家同一車型中舊車的車齡及其售價數(shù)據(jù)，請使用給圖表增加趨勢線，求車齡對售價的回歸方程,并計算車齡為6.5年的舊車售價是多少.車齡價格(萬)156.0248.5342.0437.6532.5628.7722.2818.5915.01012.56.5年的舊車車價==-4.8091*6.5+57.8=26.541、給圖表增加趨勢線進行回歸分析10精選課件ppt11精選課件ppt有些數(shù)據(jù)間并不是簡單的線性關(guān)系，如果用線性模式求其回歸方程式，判定系數(shù)（R2）很小，根本不具有任何解釋力。因此要引入非線性回歸，如多項式、指數(shù)、對數(shù)等回歸方法。例5(非線性回歸)、我們收集了年齡與月收入關(guān)系的數(shù)據(jù)，請繪制該數(shù)據(jù)的散點圖，并求年齡對月收入的回歸方程式。12精選課件ppt例6(非線性回歸)、我們收集了一個原始森林中樹的直徑與高度之間的關(guān)系數(shù)據(jù),請繪制該數(shù)據(jù)的散點圖，并求直徑與高度的回歸方程式(指數(shù)回歸)。13精選課件ppt加入趨勢線可以進行簡單的回歸分析，但要獲得更多的統(tǒng)計數(shù)據(jù)，可以使用數(shù)據(jù)分析工具，求簡單相關(guān)系數(shù)、判定系數(shù)、用F檢定判定變量與自變量間是否有回歸關(guān)系存在、用t檢定判定各回歸系數(shù)是否不為0、計算回歸系數(shù)的置信度、標準殘差等。例7、根據(jù)廣告費與銷售額統(tǒng)計數(shù)據(jù)使用分析工具進行回歸分析。2、使用“數(shù)據(jù)分析工具”進行回歸分析回歸分析信息說明14精選課件ppt方差分析(F檢定):用回歸分析檢定,判定變量與自變量之間是否有顯著的回歸關(guān)系存在。如果顯著水準(SignificanceF)<a值,回歸關(guān)系存在,否則不存在。(在這里判定系數(shù)a=1-置信度,在我們回歸分析中置信度取95%,所以a=1-95%=0.05)15精選課件pptT檢定:判斷回歸系數(shù)與常數(shù)項是否為0Intercept:回歸方程中的常數(shù)項信息廣告費(萬):自變量X的回歸系數(shù)信息16精選課件ppt車齡價格(萬)156.0248.5342.0437.6532.5628.7722.2818.5915.01012.5例8.我們收集了某廠家同一車型中舊車的車齡及其售價數(shù)據(jù)，請使用數(shù)據(jù)分析工具，求車齡對售價的回歸方程y=-4.8091x+57.817精選課件ppt練習(xí)題:以下是銀行客戶的存款金額與貸款金額對照表,用數(shù)據(jù)分析工具求存款金額對貸款金額的回歸方程,并預(yù)測當存款金額為80千萬時,貸款金額為多少?y=0.8903x–3.446118精選課件ppt通過給圖表加入趨勢線只能進行簡單的單一參數(shù)的回歸分析，但在許多情況下需要使用多個自變量來預(yù)測一個變量的情況,這種回歸分析叫復(fù)回歸，復(fù)回歸必須通過數(shù)據(jù)分析工具進行。復(fù)回歸19精選課件ppt例9、銀行為了核發(fā)信用卡，收集了申請人的每月總收入、不動產(chǎn)、動產(chǎn)、每月房貸與撫養(yǎng)支出費用等數(shù)據(jù)，并以業(yè)務(wù)主管員的經(jīng)驗，主觀地給予一個信用分數(shù)。為使評估信用分數(shù)能有一套公式，免得老是依賴主管評分，請使用復(fù)回歸求其回歸方程式。y=a1x1+a2x2+a3x3+a4x4+a5x5+b20精選課件ppt21精選課件ppt例10.我們收集了某廠家同一車型中舊車的車齡,行駛里程與售價數(shù)據(jù)，請使用數(shù)據(jù)分析工具，求車齡,行駛里程對售價的回歸方程y=-1.5407*車齡-0.4278*里程+65.995922精選課件ppt練習(xí)影響出勤率的因素分析,1-5代表影響程度，請使用復(fù)回歸求其回歸方程式。23精選課件ppt非線性的復(fù)回歸例11.我們收集了年齡與月收入關(guān)系的數(shù)據(jù)，使用數(shù)據(jù)分析工具求年齡對月收入的回歸方程式。y=a1x2+a2x+b24精選課件ppty=-36.54x2+3463.7x-4208725精選課件ppt(1)直線回歸函數(shù)LINEST()使用最小平方法計算最適合于變量區(qū)域的回歸直線公式,并傳回該直線公式的數(shù)組。可以用于單回歸和復(fù)回歸.語法:LINEST(變量區(qū)域,自變量區(qū)域,常數(shù)項是否不為零,是否返回附加的統(tǒng)計值)操作方法:1.選定輸出區(qū)域2.輸入公式3.Ctrl+Shift+Enter例12.以廣告費與銷售量的單一參數(shù)數(shù)據(jù)為例使用LINEST函數(shù),求線性回歸方程式。3、使用回歸函數(shù)進行回歸分析單回歸(y=bx+c)輸出結(jié)果y=9.1846x+299.868826精選課件ppt復(fù)回歸(y=bnx+bn-1x+…+b1x+c)輸出結(jié)果例13.我們收集了某廠家同一車型中舊車的車齡,行駛里程與售價數(shù)據(jù)，請使用LINEST函數(shù)，求車齡,行駛里程對售價的回歸方程y=-1.5407*車齡-0.4278*里程+65.995927精選課件ppt(2)線性預(yù)測函數(shù)(FORECAST)根據(jù)給定的數(shù)據(jù)計算或預(yù)測未來值。以數(shù)組或數(shù)據(jù)區(qū)域的形式給定x值和y值后，返回基于x的線性回歸預(yù)測值。使用此函數(shù)可以對未來銷售額、庫存需求或消費趨勢進行預(yù)測。FORECAST(x,known_y,known_x)X

為需要進行預(yù)測的數(shù)據(jù)點。Known_y

為因變量數(shù)組或數(shù)據(jù)區(qū)域。Known_x

為自變量數(shù)組或數(shù)據(jù)區(qū)域。例14.以廣告費與銷售量的單一參數(shù)數(shù)據(jù)為例使用Forecast函數(shù)預(yù)測當廣告費用為500萬元時銷售量為多少。FORECAST(500,$C$2:$C$13,$B$2:$B$13)=4892萬28精選課件ppt(3)線性趨勢函數(shù)（TREND）返回一條線性回歸擬合線的一組縱坐標值（y值）。即找到適合給定的數(shù)組known_y和known_x的直線（用最小二乘法），并返回指定數(shù)組new_x's值在直線上對應(yīng)的y值。語法：TREND(known_y,known_x,new_x,const)操作方法:1.選定輸出區(qū)域2.輸入公式3.Ctrl+Shift+Enter例15、以廣告費與銷售量的單一參數(shù)數(shù)據(jù)為例使用TREND函數(shù)預(yù)測一組廣告費的銷售量。29精選課件ppt(4)指數(shù)回歸函數(shù)

LOGEST：指數(shù)回歸函數(shù)，計算最符合觀測數(shù)據(jù)組的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)組。此曲線的方程為：y=b*mxory=(b*(m1x1)*(m2x2)*…)（如果有多個x值）LOGEST(kn