《office數(shù)據(jù)分析》課件

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

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

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

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

Excel還提供了其他一些統(tǒng)計(jì)、財(cái)務(wù)和工程函數(shù)。某些統(tǒng)計(jì)函數(shù)是內(nèi)置函數(shù)，而其他一些函數(shù)只有在安裝了“分析工具庫(kù)”之后才能使用。預(yù)備知識(shí)要使用這些工具，用戶還必須熟悉需要進(jìn)行分析的統(tǒng)計(jì)學(xué)或工程學(xué)的特定領(lǐng)域。2精選課件ppt三.相關(guān)性分析相關(guān)性分析：判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。相關(guān)系數(shù)（R）：相關(guān)性計(jì)算的返回值。用來(lái)確定兩個(gè)區(qū)域中數(shù)據(jù)的變化是否相關(guān)，以及相關(guān)的程度。是兩組數(shù)據(jù)集的協(xié)方差除以它們標(biāo)準(zhǔn)偏差的乘積。1、R>0：一個(gè)集合的較大數(shù)據(jù)與另一個(gè)集合的較大數(shù)據(jù)相對(duì)應(yīng)（正相關(guān)）2、R<0：一個(gè)集合的較大數(shù)據(jù)與另一個(gè)集合的較小數(shù)據(jù)相對(duì)應(yīng)（負(fù)相關(guān)）3、R=0：兩個(gè)集合中的數(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)系。例如，可以檢測(cè)某地的平均溫度和空調(diào)使用情況之間的關(guān)系。Array1

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

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

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

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

為需要進(jìn)行預(yù)測(cè)的數(shù)據(jù)點(diǎn)。Known_y

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

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

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