EXCEL與數(shù)據(jù)分析教學(xué)

會計(jì)學(xué)1EXCEL與數(shù)據(jù)分析PPT教學(xué)課件2

第一節(jié)數(shù)據(jù)間的相關(guān)性第二節(jié)相關(guān)圖表與相關(guān)關(guān)系的初步判斷本章主要教學(xué)內(nèi)容第三節(jié)相關(guān)系數(shù)的計(jì)算和分析第四節(jié)線性回歸分析第1頁/共44頁3第一節(jié)數(shù)據(jù)間的相關(guān)性一、函數(shù)關(guān)系和相關(guān)關(guān)系二、線性相關(guān)和非線性相關(guān)

三、正線性相關(guān)和負(fù)線性相關(guān)

第2頁/共44頁4一、函數(shù)關(guān)系和相關(guān)關(guān)系任何事物都不是孤立存在的，總會與其他事物有或多或少的聯(lián)系，其運(yùn)行軌跡會受到其他事物的影響，同時(shí)也影響著其他事物的發(fā)展。因此，僅從事物本身出發(fā)研究其特征和規(guī)律是不全面的，還應(yīng)對事物之間的相關(guān)性進(jìn)行分析。在自然界和社會現(xiàn)象中，客觀現(xiàn)象之間的數(shù)量關(guān)系其通常有兩種類型，即：現(xiàn)象之間的數(shù)量關(guān)系

函數(shù)關(guān)系相關(guān)關(guān)系第3頁/共44頁5函數(shù)關(guān)系是現(xiàn)象（變量）之間客觀存在的確定性的數(shù)量對應(yīng)關(guān)系。如：圓面積與半徑的關(guān)系。相關(guān)關(guān)系即現(xiàn)象（變量）之間客觀存在的非確定性的數(shù)量依存關(guān)系。即當(dāng)一個(gè)現(xiàn)象取一定值時(shí)，與其相對應(yīng)的另一個(gè)現(xiàn)象的值不完全確定，而是有多個(gè)值與其對應(yīng)。例如：消費(fèi)支出與消費(fèi)收入的關(guān)系、學(xué)習(xí)成績與學(xué)習(xí)時(shí)間的關(guān)系、畝產(chǎn)量和澆水量之間的關(guān)系、企業(yè)生產(chǎn)規(guī)模和綜合單位成本之間的關(guān)系等。第4頁/共44頁6二、線性相關(guān)和非線性相關(guān)事物間的相關(guān)性又可以從相關(guān)的表現(xiàn)形式劃分為線性相關(guān)和非線性相關(guān)。線性相關(guān)：相關(guān)的兩個(gè)事物的對應(yīng)值畫在直角坐標(biāo)圖上，其數(shù)據(jù)散布點(diǎn)趨向直線形式，則稱為線性相關(guān)。如商品的銷售量與銷售額之間就是一種線性相關(guān)關(guān)系。非線性相關(guān)：如果其散布點(diǎn)分布趨向某種曲線形式，則稱為非線性（曲線）相關(guān)。

例如，隨著家庭年收入的增長，購買轎車的可能性也會增加。但當(dāng)年收入處在較低水平時(shí)，年收入增加，轎車的購買量增長幅度不會很大；當(dāng)年收入達(dá)到一定水平時(shí)，購買量的增長幅度會較大；而當(dāng)年收入達(dá)到一個(gè)較高水平時(shí)，購買量的增長幅度將會再次保持平穩(wěn)。這就是一個(gè)典型的非線性相關(guān)關(guān)系。第5頁/共44頁7線性相關(guān)關(guān)系是有方向性的，按照相關(guān)的方向不同可以分為正線性相關(guān)和負(fù)線性相關(guān)。負(fù)相關(guān)：如果自變量x的數(shù)值增加(或減少)，因變量y的數(shù)值則隨之減少(或增加)，即自變量與因變量的變動(dòng)方向是相反的，這種相關(guān)關(guān)系稱為負(fù)相關(guān)。如商品的價(jià)格和銷售量之間的關(guān)系。三、正線性相關(guān)和負(fù)線性相關(guān)正相關(guān)：

當(dāng)自變量x數(shù)值增加(或減少)時(shí),因變量y的數(shù)值也將隨之相應(yīng)的增加(或減少)，這種相關(guān)關(guān)系稱為正相關(guān)。如學(xué)習(xí)時(shí)間和學(xué)習(xí)成績之間的關(guān)系。第6頁/共44頁8第二節(jié)相關(guān)表、相關(guān)圖與相關(guān)關(guān)系的初步判斷一、相關(guān)表的制作二、相關(guān)圖的制作三、相關(guān)關(guān)系的初步判斷第7頁/共44頁9（一)定性分析（二)定量分析

相關(guān)表相關(guān)圖相關(guān)系數(shù)分析和判斷兩數(shù)值型數(shù)據(jù)間的相關(guān)性，可采用理論分析（定性分析）方法和定量分析方法兩大類。定量分析方法又包括圖表分析方法和數(shù)值分析方法。數(shù)據(jù)間相關(guān)性的初步判斷可通過定性分析及定量分析中的圖表分析方法。第8頁/共44頁10一、相關(guān)表的制作相關(guān)表是用來觀察兩個(gè)現(xiàn)象之間是否具有相關(guān)關(guān)系的數(shù)據(jù)表格，它是將數(shù)據(jù)表格中的其中一個(gè)現(xiàn)象進(jìn)行排序（降序或升序均可），然后觀察另一個(gè)數(shù)據(jù)隨著前一個(gè)數(shù)據(jù)的增加或減少是否呈現(xiàn)出有規(guī)律的變化，從而初步判斷兩個(gè)現(xiàn)象之間是否具有相關(guān)關(guān)系。相關(guān)表的制作很簡單，將兩個(gè)現(xiàn)象的數(shù)據(jù)分別兩列錄入Excel，然后將其中一列排序即可。第9頁/共44頁11學(xué)號學(xué)習(xí)時(shí)間（小時(shí)）學(xué)習(xí)成績（分）020305080604090110075.05.56.06.07.07.58.08.08.59.061606577788285929296表4-1學(xué)習(xí)時(shí)間與學(xué)習(xí)成績相關(guān)表第10頁/共44頁12二、相關(guān)圖的制作相關(guān)圖又稱散點(diǎn)圖，它是將相關(guān)表中的數(shù)值在平面直角坐標(biāo)系中用坐標(biāo)點(diǎn)描繪出來，以表明相關(guān)數(shù)據(jù)點(diǎn)的分布狀況。通過相關(guān)圖可觀察兩個(gè)現(xiàn)象之間關(guān)系的總體模式，初步判斷它們之間是否具有相關(guān)關(guān)系以及相關(guān)關(guān)系的強(qiáng)弱程度和相關(guān)的方向。相關(guān)圖的制作步驟：（1）將搜集的兩個(gè)現(xiàn)象的數(shù)據(jù)分兩列錄入Excel表；（2）選中制作圖表的數(shù)據(jù)區(qū)域；第11頁/共44頁13（3）點(diǎn)擊“插入”→點(diǎn)擊“圖表”，出現(xiàn)“圖表向?qū)А钡膹?fù)選框；或直接在Excel表上方的工具欄中點(diǎn)擊圖表向?qū)В海?）在“圖表類型”中選擇“XY散點(diǎn)圖”，并選擇“子圖表類型”中的第一類；（5）單擊“下一步”→“下一步”，填寫圖表標(biāo)題及X軸和Y軸的標(biāo)題；（6）單擊“下一步”→單擊“完成”。

案例：分析我國固定資產(chǎn)投資與GDP的相關(guān)性。第12頁/共44頁14相關(guān)圖描述了兩個(gè)現(xiàn)象之間的大致關(guān)系，比較典型的形態(tài)有以下幾種：（1）所有點(diǎn)均落在左下右上的一條直線上，表明數(shù)據(jù)間為完全正相關(guān)；（2）所有點(diǎn)均落在左上右下的一條直線上，表明數(shù)據(jù)間為完全負(fù)相關(guān)；上述兩種情況表明兩個(gè)事物之間是函數(shù)關(guān)系，而不是相關(guān)關(guān)系。（3）數(shù)據(jù)點(diǎn)雜亂無章，從形態(tài)上看不出任何特征和規(guī)律，表明現(xiàn)象之間不相關(guān)。第13頁/共44頁15（1）數(shù)據(jù)點(diǎn)大致落在左下右上的一條直線周圍，表明數(shù)據(jù)間存在正線性相關(guān)關(guān)系；（2）數(shù)據(jù)點(diǎn)大致落在左上右下的一條直線周圍，表明數(shù)據(jù)間存在負(fù)線性相關(guān)關(guān)系；（3）數(shù)據(jù)點(diǎn)大致呈現(xiàn)某種曲線形態(tài)，如拋物線等，表明現(xiàn)象之間不存在線性相關(guān)關(guān)系，但存在非線性相關(guān)關(guān)系。幾種主要的相關(guān)圖形如下：第14頁/共44頁16●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

完全正相關(guān)不相關(guān)幾種主要的相關(guān)圖yyxx完全負(fù)相關(guān)yx第15頁/共44頁17●●

●●●

●●●●●●●

●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●直線相關(guān)曲線相關(guān)學(xué)習(xí)時(shí)間學(xué)習(xí)成績商品價(jià)格購買量●●

●●●

●●●●●●●

●●●●●●●●●●●●

●●

●●●

●●●●●●●

●●●●●●●●●●●●

正相關(guān)負(fù)相關(guān)幾種主要的相關(guān)圖yyyyxxxx第16頁/共44頁18第三節(jié)相關(guān)系數(shù)的計(jì)算和分析一、相關(guān)系數(shù)的含義二、相關(guān)系數(shù)的計(jì)算三、案例分析第17頁/共44頁19一、相關(guān)系數(shù)的含義

（一）相關(guān)系數(shù)的概念

相關(guān)表和相關(guān)圖雖然能夠直觀展現(xiàn)數(shù)據(jù)之間的相關(guān)關(guān)系，但只是大概，不精確。

（簡單）相關(guān)系數(shù)是線性相關(guān)條件下，用來說明兩個(gè)變量之間相關(guān)關(guān)系密切程度和相關(guān)方向的統(tǒng)計(jì)指標(biāo)。它通過數(shù)字的方式準(zhǔn)確描述了數(shù)據(jù)間線性相關(guān)的方向和強(qiáng)弱程度。相關(guān)系數(shù)是一個(gè)用來度量數(shù)值型數(shù)據(jù)間的線性相關(guān)關(guān)系的系數(shù)，不受變量值計(jì)量單位的影響，一般記為r。其數(shù)學(xué)定義為：第18頁/共44頁20第19頁/共44頁21（二)相關(guān)系數(shù)r

的取值范圍－1≤r≤+1（三)相關(guān)系數(shù)r

的取值含義

或0≤︱r︱≤1第20頁/共44頁22第21頁/共44頁23二、相關(guān)系數(shù)的計(jì)算在EXCEL中，相關(guān)系數(shù)的計(jì)算可通過“數(shù)據(jù)分析”命令計(jì)算?；静僮鞑襟E如下：

（1）選擇“工具”菜單中的“數(shù)據(jù)分析”命令，出現(xiàn)如下對話框：

（2）選擇“相關(guān)系數(shù)”，單擊“確定”，出現(xiàn)“相關(guān)系數(shù)”的復(fù)選框：第22頁/共44頁24

（3）在“輸入?yún)^(qū)域”框中輸入待分析數(shù)據(jù)所在的單元格地址，并在“輸出選項(xiàng)”中進(jìn)行選擇，單擊“確定”，即可出現(xiàn)相關(guān)系數(shù)的計(jì)算結(jié)果。第23頁/共44頁25三、案例分析根據(jù)我國2019年各地區(qū)GDP和固定資產(chǎn)投資、居民消費(fèi)水平的數(shù)據(jù)資料，分析固定資產(chǎn)投資、居民消費(fèi)水平與各地區(qū)GDP之間的相關(guān)性。

利用EXCEL計(jì)算的相關(guān)系數(shù)結(jié)果為：

地區(qū)生產(chǎn)總值固定資產(chǎn)投資居民消費(fèi)水平地區(qū)生產(chǎn)總值1固定資產(chǎn)投資0.88811居民消費(fèi)水平0.40580.15431第24頁/共44頁26計(jì)算結(jié)果表明，各地區(qū)固定資產(chǎn)投資與地區(qū)生產(chǎn)總值的相關(guān)系數(shù)為0.8881，說明固定資產(chǎn)投資與地區(qū)生產(chǎn)總值之間為高度正相關(guān)；各地區(qū)居民消費(fèi)水平與地區(qū)生產(chǎn)總值的相關(guān)系數(shù)為0.4058，說明居民消費(fèi)水平與地區(qū)生產(chǎn)總值之間為低度正相關(guān)。上述數(shù)據(jù)分析結(jié)果顯示，我國的經(jīng)濟(jì)增長方式主要為投資拉動(dòng)型，固定資產(chǎn)投資是拉動(dòng)經(jīng)濟(jì)增長的主要因素，而居民消費(fèi)對經(jīng)濟(jì)增長的拉動(dòng)不力。從長遠(yuǎn)的意義來看，這種狀態(tài)不利于國民經(jīng)濟(jì)的可持續(xù)發(fā)展，因此，政府應(yīng)通過制定相應(yīng)的政策轉(zhuǎn)變經(jīng)濟(jì)增長方式，約束投資的數(shù)量，提高投資的質(zhì)量；并進(jìn)一步采取有效政策，引導(dǎo)和鼓勵(lì)居民消費(fèi)，提高居民消費(fèi)水平和消費(fèi)質(zhì)量。第25頁/共44頁27第四節(jié)線性回歸分析一、相關(guān)分析與回歸分析的關(guān)系二、簡單線性回歸分析三、回歸分析的EXCEL實(shí)現(xiàn)第26頁/共44頁28一、相關(guān)分析與回歸分析的關(guān)系回歸分析就是對具有相關(guān)關(guān)系的多個(gè)變量之間的數(shù)量變化進(jìn)行數(shù)量測定，配合一定的數(shù)學(xué)方程（模型），以便由自變量的數(shù)值對因變量的可能值進(jìn)行估計(jì)或預(yù)測的一種統(tǒng)計(jì)方法。

相關(guān)分析(相關(guān)系數(shù))能夠確定變量之間相關(guān)方向和相關(guān)的密切程度,但它不能指出兩個(gè)變量之間相互關(guān)系的具體形式,也無法從一個(gè)變量的數(shù)量變化來推測另一個(gè)變量的變化情況。（另外，在相關(guān)分析中，一般不必區(qū)分自變量和因變量，它不能說明兩個(gè)變量是主從關(guān)系還是因果關(guān)系。）（一）回歸分析的概念第27頁/共44頁29（二）相關(guān)分析和回歸分析的關(guān)系相關(guān)分析和回歸分析既有聯(lián)系，也有區(qū)別。聯(lián)系：1、相關(guān)分析是回歸分析的前提和基礎(chǔ)；2、回歸分析是相關(guān)分析的繼續(xù)和深入。區(qū)別：

1、相關(guān)分析中兩變量都是隨機(jī)的，不必確定自變量與因變量；而回歸分析中只有因變量是隨機(jī)的,必須事先確定自變量和因變量。2、相關(guān)分析中只能算出一個(gè)相關(guān)系數(shù)，而回歸分析中可有兩個(gè)回歸方程。3、相關(guān)分析的內(nèi)容是判斷事物之間是否具有相關(guān)關(guān)系及相關(guān)的方向和密切程度；而回歸分析則是要分析當(dāng)自變量發(fā)生變化時(shí)，因變量平均發(fā)生怎樣的變動(dòng)。第28頁/共44頁30二、簡單線性回歸分析回歸分析的種類按照回歸線的形狀按自變量的個(gè)數(shù)多元回歸一元回歸非線性（曲線）回歸線性（直線）回歸根據(jù)回歸分析的方法，得出的數(shù)學(xué)表達(dá)式稱為回歸方程（回歸模型），它有多種形式，可以是直線方程，也可以是曲線方程。用回歸方程來表明兩個(gè)變量之間線性相互關(guān)系的方程式，稱為簡單線性回歸方程（模型）。這種分析方法稱為簡單線性回歸分析。第29頁/共44頁31實(shí)際中，多數(shù)現(xiàn)象的調(diào)查采用抽樣調(diào)查，因此，通常使用樣本直線回歸方程作為總體回歸方程的估計(jì)值。

a：直線起點(diǎn)值，數(shù)學(xué)上稱為直線的截距；

b：數(shù)學(xué)上稱為直線的斜率；統(tǒng)計(jì)學(xué)上稱為回歸系數(shù)。它表示自變量x每變動(dòng)一個(gè)單位時(shí)，因變量y平均變動(dòng)的數(shù)值。

式中：（一）簡單直線回歸方程（樣本方程）第30頁/共44頁32

簡單線性回歸分析的任務(wù)就是如何根據(jù)實(shí)際樣本資料來確定參數(shù)a、b，即從各個(gè)相關(guān)點(diǎn)中配合一條合適的直線，使其能代表相關(guān)點(diǎn)的變動(dòng)趨勢（即用其表明兩變量x與y在線性相關(guān)條件下的具體變動(dòng)關(guān)系）。

●●

●●●

●●●●●●●●●

●●●●●●●●●●●數(shù)學(xué)證明，符合“離差平方和最小”的直線是最合適的。這種決定直線方程的方法，稱為最小二乘法（最小平方法）。（二）直線回歸方程的求解xy第31頁/共44頁33根據(jù)“離差平方和最小”的原則，a、b

的求解公式為：（三）利用回歸方程進(jìn)行預(yù)測

回歸方程描述了當(dāng)自變量發(fā)生變化時(shí)，因變量的平均變化情況。因此，可用回歸方程預(yù)測當(dāng)自變量為某個(gè)特定值時(shí)的因變量的取值。即，建立了回歸方程并通過了統(tǒng)計(jì)檢驗(yàn)之后，回歸方程的一個(gè)重要應(yīng)用就是根據(jù)給定的自變量x的值，對因變量y作出相應(yīng)的預(yù)測估計(jì)。第32頁/共44頁34

回歸方程的預(yù)測包括定值預(yù)測和區(qū)間預(yù)測。利用回歸方程，對自變量x的一個(gè)特定值x0，求出因變量y的一個(gè)估計(jì)值y0，這就是點(diǎn)估計(jì)，也稱定值預(yù)測。利用回歸方程，對自變量x的一個(gè)特定值x0，以一定的把握程度，計(jì)算出因變量y的一個(gè)估計(jì)值y0的置信區(qū)間，這就是區(qū)間估計(jì)，也稱區(qū)間預(yù)測。當(dāng)把握程度為95%時(shí)（實(shí)際中常用），y0的置信區(qū)間（簡單算法）為：

其中，Sy稱為估計(jì)標(biāo)準(zhǔn)誤差，是衡量因變量y的實(shí)際值和估計(jì)值離差一般水平的分析指標(biāo)，sy的大小與回歸方程的代表性成反比。第33頁/共44頁35三、回歸分析的EXCEL實(shí)現(xiàn)

（1）選擇“工具”菜單中的“數(shù)據(jù)分析”子菜單，在“分析工具”中選擇“回歸”；（2）單擊“確定”，出現(xiàn)如下對話框：

利用EXCEL進(jìn)行回歸分析，是通過“數(shù)據(jù)分析”命令實(shí)現(xiàn)的。具體操作如下：第34頁/共44頁36

（3）給出因變量Y和自變量X的數(shù)據(jù)所在的單元格區(qū)域，在“輸出選項(xiàng)”中選擇輸出的位置，同時(shí)選擇“線性擬合圖”；（4）單擊“確定”，輸出回歸結(jié)果。第35頁/共44頁37

對輸出結(jié)果的解釋：

（結(jié)合固定資產(chǎn)投資與地區(qū)GDP的關(guān)系案例）輸出結(jié)果包括“SUMMARYOUTPUT”（摘要輸出）和“RESIDUALOUTPUT”（殘差輸出）兩部分以及線性擬合圖。第36頁/共44頁38（1）“MultipleR”是自變量X和因變量Y之間的簡單相關(guān)系數(shù)r，等于0.8881，表明兩者間高度正相關(guān)；（2）“RSquare”是判定系數(shù)R2

,等于0.7887。判定系數(shù)R2是測定直線回歸模型擬合優(yōu)度的一個(gè)重要指標(biāo)，其意義同相關(guān)系數(shù)r具有一致性。計(jì)算結(jié)果表明，GDP的總誤差中有78.87%可以由固定資產(chǎn)投資與GDP的依存關(guān)系來解釋，只有21.13%屬于隨機(jī)因素的影響，因此這條回歸線是比較合適的；

（3）“AdjustedRSquare”是調(diào)整的R2

,在簡單線性回歸分析中沒有特別意義；

（4）“標(biāo)準(zhǔn)誤差”是回歸方程的估計(jì)標(biāo)準(zhǔn)差Sy，此例中的估計(jì)標(biāo)準(zhǔn)差為5330，很大，表明回歸方程的擬合度不高；第37頁/共44頁39（5）“方差分析”中的“SS”下面的三個(gè)數(shù)分別是回歸平方和SSR、剩余平方和SSE和總離差平方和SST；其中SSR反映了自變量的變化所引起的因變量y值的波動(dòng)，其大小反映了自變量x的重要程度；SSE是y的實(shí)際值與估計(jì)值之差的平方和，它主要反映隨機(jī)因素對因變量的影響程度。在總離差平方和中，SSR占的比例越大，說明自變量對因變量的影響越大，即x與y線性相關(guān)的程度越高，回歸方程的質(zhì)量越高；反之，SSE所占的比例越大，說明隨機(jī)因素對因變量的影響越大，而自變量與因變量的關(guān)系越不密切，回歸方程的質(zhì)量越低。F統(tǒng)計(jì)量就是反映SSR與SSE比例關(guān)系、并用于對回歸方程進(jìn)行顯著性檢驗(yàn)的統(tǒng)計(jì)量。

第38頁/共44頁40（6）F下面的數(shù)是回歸方程顯著性檢驗(yàn)中的F統(tǒng)計(jì)量的值，等于108.27；其右側(cè)單元格中的數(shù)是F統(tǒng)計(jì)量在原假設(shè)(X與Y線性相關(guān)不顯著)成立時(shí)發(fā)生的概率，為2.66×10-11，當(dāng)把握程度為95%時(shí)，由于2.6