會計學1EXCEL與數據分析PPT教學課件2

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

三、正線性相關和負線性相關

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

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

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

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

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

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

完全正相關不相關幾種主要的相關圖yyxx完全負相關yx第15頁/共44頁17●●

●●●

●●●●●●●

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

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●直線相關曲線相關學習時間學習成績商品價格購買量●●

●●●

●●●●●●●

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

●●

●●●

●●●●●●●

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

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

（一）相關系數的概念

相關表和相關圖雖然能夠直觀展現數據之間的相關關系，但只是大概，不精確。

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

的取值范圍－1≤r≤+1（三)相關系數r

的取值含義

或0≤︱r︱≤1第20頁/共44頁22第21頁/共44頁23二、相關系數的計算在EXCEL中，相關系數的計算可通過“數據分析”命令計算。基本操作步驟如下：

（1）選擇“工具”菜單中的“數據分析”命令，出現如下對話框：

（2）選擇“相關系數”，單擊“確定”，出現“相關系數”的復選框：第22頁/共44頁24

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

利用EXCEL計算的相關系數結果為：

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

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

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

a：直線起點值，數學上稱為直線的截距；

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

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

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

●●

●●●

●●●●●●●●●

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

的求解公式為：（三）利用回歸方程進行預測

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

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

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

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

利用EXCEL進行回歸分析，是通過“數據分析”命令實現的。具體操作如下：第34頁/共44頁36

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

對輸出結果的解釋：

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

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

（3）“AdjustedRSquare”是調整的R2

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

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

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