Cách thực hiện phân tích hồi quy trong excel. Konrad Carlberg. Phân tích hồi quy trong Microsoft Excel

Phân tích hồi quy và tương quan là phương pháp nghiên cứu thống kê. Đây là những cách phổ biến nhất để thể hiện sự phụ thuộc của một tham số vào một hoặc nhiều biến độc lập.

Dưới đây, bằng cách sử dụng các ví dụ thực tế cụ thể, chúng ta sẽ xem xét hai phân tích rất phổ biến này trong giới kinh tế học. Chúng tôi cũng sẽ đưa ra một ví dụ về việc thu được kết quả khi kết hợp chúng.

Phân tích hồi quy trong Excel

Cho thấy ảnh hưởng của một số giá trị (độc lập, độc lập) đến biến phụ thuộc. Ví dụ, số lượng dân số hoạt động kinh tế phụ thuộc như thế nào vào số lượng doanh nghiệp, tiền lương và các thông số khác. Hoặc: đầu tư nước ngoài, giá năng lượng, v.v. ảnh hưởng như thế nào đến mức GDP.

Kết quả phân tích cho phép bạn làm nổi bật các ưu tiên. Và dựa trên các yếu tố chính để dự đoán, lập kế hoạch phát triển các lĩnh vực ưu tiên và đưa ra các quyết định quản lý.

Hồi quy xảy ra:

tuyến tính (y = a + bx);
parabol (y = a + bx + cx 2);
hàm mũ (y = a * exp(bx));
công suất (y = a*x^b);
hyperbol (y = b/x + a);
logarit (y = b * 1n(x) + a);
hàm mũ (y = a * b^x).

Hãy xem một ví dụ về xây dựng mô hình hồi quy trong Excel và diễn giải kết quả. Hãy lấy kiểu hồi quy tuyến tính.

Nhiệm vụ. Tại 6 doanh nghiệp, mức lương bình quân tháng và số lao động nghỉ việc được phân tích. Cần xác định sự phụ thuộc của số lượng nhân viên nghỉ việc vào mức lương bình quân.

Mô hình hồi quy tuyến tính trông như thế này:

Y = a 0 + a 1 x 1 +…+a k x k.

Trong đó a là hệ số hồi quy, x là các biến ảnh hưởng, k là số lượng nhân tố.

Trong ví dụ của chúng tôi, Y là chỉ báo về việc nhân viên nghỉ việc. Yếu tố ảnh hưởng là tiền lương (x).

Excel có sẵn các hàm có thể giúp bạn tính toán các tham số của mô hình hồi quy tuyến tính. Nhưng tiện ích bổ sung “Gói phân tích” sẽ thực hiện việc này nhanh hơn.

Chúng tôi kích hoạt một công cụ phân tích mạnh mẽ:

Sau khi được kích hoạt, tiện ích bổ sung sẽ có sẵn trong tab Dữ liệu.

Bây giờ chúng ta hãy tự phân tích hồi quy.

Trước hết, chúng ta chú ý đến R bình phương và các hệ số.

R bình phương là hệ số xác định. Trong ví dụ của chúng tôi – 0,755, hoặc 75,5%. Điều này có nghĩa là các tham số tính toán của mô hình giải thích được 75,5% mối liên hệ giữa các tham số nghiên cứu. Hệ số xác định càng cao thì mô hình càng tốt. Tốt - trên 0,8. Xấu – dưới 0,5 (phân tích như vậy khó có thể được coi là hợp lý). Trong ví dụ của chúng tôi – “không tệ”.

Hệ số 64,1428 cho thấy Y sẽ bằng bao nhiêu nếu tất cả các biến trong mô hình đang xem xét đều bằng 0. Nghĩa là giá trị của tham số phân tích cũng bị ảnh hưởng bởi các yếu tố khác không được mô tả trong mô hình.

Hệ số -0,16285 thể hiện trọng số của biến X trên Y. Nghĩa là, mức lương trung bình hàng tháng trong mô hình này ảnh hưởng đến số người bỏ việc có trọng số -0,16285 (đây là mức độ ảnh hưởng nhỏ). Dấu “-” biểu thị tác động tiêu cực: lương càng cao thì càng ít người bỏ việc. Điều đó là công bằng.

Phân tích tương quan trong Excel

Phân tích tương quan giúp xác định liệu có mối liên hệ giữa các chỉ số trong một hoặc hai mẫu hay không. Chẳng hạn, giữa thời gian vận hành của máy và chi phí sửa chữa, giá thiết bị và thời gian vận hành, chiều cao và cân nặng của trẻ em, v.v.

Nếu có một kết nối, thì việc tăng một tham số sẽ dẫn đến tăng (tương quan dương) hoặc giảm (âm) của tham số kia. Phân tích tương quan giúp nhà phân tích xác định liệu giá trị của một chỉ báo có thể được sử dụng để dự đoán giá trị có thể có của chỉ báo khác hay không.

Hệ số tương quan được ký hiệu là r. Thay đổi từ +1 đến -1. Việc phân loại các mối tương quan cho các khu vực khác nhau sẽ khác nhau. Khi hệ số bằng 0, không có mối quan hệ tuyến tính giữa các mẫu.

Hãy xem cách tìm hệ số tương quan bằng Excel.

Để tìm các hệ số ghép đôi, hàm CORREL được sử dụng.

Mục tiêu: Xác định xem có mối quan hệ giữa thời gian vận hành của máy tiện và chi phí bảo trì máy tiện hay không.

Đặt con trỏ vào bất kỳ ô nào và nhấn nút fx.

Trong danh mục “Thống kê”, chọn chức năng CORREL.
Đối số “Mảng 1” - phạm vi giá trị đầu tiên – thời gian vận hành máy: A2:A14.
Đối số “Mảng 2” - phạm vi giá trị thứ hai – chi phí sửa chữa: B2:B14. Bấm vào đồng ý.

Để xác định loại kết nối, bạn cần nhìn vào số lượng tuyệt đối của hệ số (mỗi lĩnh vực hoạt động có thang đo riêng).

Để phân tích tương quan của một số tham số (nhiều hơn 2), sẽ thuận tiện hơn khi sử dụng “Phân tích dữ liệu” (tiện ích bổ sung “Gói phân tích”). Bạn cần chọn mối tương quan từ danh sách và chỉ định mảng. Tất cả.

Các hệ số kết quả sẽ được hiển thị trong ma trận tương quan. Như thế này:

Phân tích tương quan và hồi quy

Trong thực tế, hai kỹ thuật này thường được sử dụng cùng nhau.

Ví dụ:

Bây giờ dữ liệu phân tích hồi quy đã hiển thị.

Gói MS Excel cho phép bạn thực hiện hầu hết công việc rất nhanh khi xây dựng phương trình hồi quy tuyến tính. Điều quan trọng là phải hiểu cách diễn giải kết quả thu được.

Yêu cầu một tiện ích bổ sung để hoạt động Gói phân tích, phải được bật trong mục menu Dịch vụ\Tiện ích bổ sung

Trong Excel 2007, để kích hoạt gói phân tích, bạn cần nhấp vào go to block Tùy chọn Excel bằng cách nhấp vào nút ở góc trên bên trái và sau đó nhấp vào " Tùy chọn Excel"ở cuối cửa sổ:

Để xây dựng mô hình hồi quy, bạn phải chọn mục Dịch vụ\Phân tích dữ liệu\Hồi quy. (Trong Excel 2007 chế độ này nằm trong khối Dữ liệu/Phân tích dữ liệu/Hồi quy). Một hộp thoại hiện ra bạn cần điền:

1) Khoảng đầu vào Y¾ chứa liên kết đến các ô chứa các giá trị của đặc tính kết quả y. Các giá trị phải được sắp xếp theo một cột;

2) Khoảng đầu vào X¾ chứa liên kết đến các ô chứa giá trị hệ số. Các giá trị phải được sắp xếp theo cột;

3) Ký tên Thẻđặt nếu các ô đầu tiên chứa văn bản giải thích (nhãn dữ liệu);

4) Mức độ tin cậy¾ là mức độ tin cậy, được coi là 95% theo mặc định. Nếu bạn không hài lòng với giá trị này thì bạn cần bật cờ này và nhập giá trị được yêu cầu;

5) Ký tên Hằng số khôngđược đưa vào nếu cần xây dựng phương trình trong đó biến tự do là ;

6) Tùy chọn đầu ra xác định nơi kết quả sẽ được đặt. Theo chế độ xây dựng mặc định Bảng tính mới;

7) Chặn Thức ăn thừa cho phép bạn bao gồm đầu ra của phần dư và việc xây dựng biểu đồ của chúng.

Kết quả là thông tin được hiển thị chứa tất cả thông tin cần thiết và được nhóm thành ba khối: Thống kê hồi quy, Phân tích phương sai, Rút số dư. Chúng ta hãy xem xét kỹ hơn về họ.

1. Thống kê hồi quy:

nhiều Rđược xác định bởi công thức ( Hệ số tương quan Pearson);

R (hệ số xác định);

Chuẩn hóa R-vuông được tính theo công thức (được sử dụng cho hồi quy bội);

Lỗi tiêu chuẩn S tính theo công thức ;

Quan sát ¾ là lượng dữ liệu N.

2. Phân tích phương sai, đường kẻ hồi quy:

Tham số df bằng tôi(số bộ nhân tố x);

Tham số SSđược xác định theo công thức;

Tham số bệnh đa xơ cứngđược xác định theo công thức;

Số liệu thống kê Fđược xác định theo công thức;

Ý nghĩa F. Nếu số kết quả vượt quá , thì giả thuyết được chấp nhận (không có mối quan hệ tuyến tính), ngược lại giả thuyết được chấp nhận (có mối quan hệ tuyến tính).

3. Phân tích phương sai, đường kẻ còn lại:

Tham số df tương đương với ;

Tham số SSđược xác định bởi công thức ;

Tham số bệnh đa xơ cứngđược xác định bởi công thức.

4. Phân tích phương sai, đường kẻ Tổng cộng chứa tổng của hai cột đầu tiên.

5. Phân tích phương sai, đường kẻ Giao lộ chữ Y chứa hệ số, sai số chuẩn và t-số liệu thống kê.

P-value ¾ là giá trị của các mức ý nghĩa tương ứng với giá trị tính toán t-các nhà thống kê. Được xác định bởi hàm STUDIST( t-số liệu thống kê; ). Nếu như P-value vượt quá , thì biến tương ứng không có ý nghĩa thống kê và có thể bị loại khỏi mô hình.

Dưới 95% Và 95% hàng đầu¾ là giới hạn dưới và giới hạn trên của khoảng tin cậy 95 phần trăm đối với các hệ số của phương trình hồi quy tuyến tính lý thuyết. Nếu giá trị xác suất tin cậy trong khối đầu vào dữ liệu được giữ ở giá trị mặc định thì hai cột cuối cùng sẽ trùng lặp với các cột trước đó. Nếu người dùng đã nhập giá trị độ tin cậy thì hai cột cuối cùng chứa các giá trị giới hạn dưới và giới hạn trên cho mức độ tin cậy được chỉ định.

6. Phân tích phương sai, dòng chứa giá trị các hệ số, sai số chuẩn, t-nhà thống kê, P-giá trị và khoảng tin cậy cho tương ứng.

7. Chặn Rút số dư chứa các giá trị dự đoán y(theo ký hiệu của chúng tôi đây là ) và dư lượng .

Phương pháp hồi quy tuyến tính cho phép chúng ta mô tả một đường thẳng phù hợp nhất với một chuỗi các cặp có thứ tự (x, y). Phương trình của một đường thẳng, được gọi là phương trình tuyến tính, được đưa ra dưới đây:

ŷ là giá trị kỳ vọng của y đối với giá trị x cho trước,

x là một biến độc lập,

a là đoạn thẳng trên trục y của một đường thẳng,

b là độ dốc của đường thẳng

Hình dưới đây minh họa khái niệm này bằng đồ họa:

Hình trên thể hiện đường thẳng được mô tả bởi phương trình ŷ =2+0,5x. Giao điểm y là điểm tại đó đường thẳng cắt trục y; trong trường hợp của chúng ta, a = 2. Độ dốc của đường b, tỷ lệ giữa độ cao của đường thẳng và chiều dài của đường thẳng, có giá trị là 0,5. Độ dốc dương có nghĩa là đường tăng dần từ trái sang phải. Nếu b = 0 thì đường nằm ngang, nghĩa là không có mối quan hệ giữa biến phụ thuộc và biến độc lập. Nói cách khác, việc thay đổi giá trị của x không ảnh hưởng đến giá trị của y.

ŷ và y thường bị nhầm lẫn. Đồ thị thể hiện 6 cặp điểm và một đường thẳng có thứ tự theo phương trình đã cho

Hình này thể hiện điểm tương ứng với cặp thứ tự x = 2 và y = 4. Lưu ý rằng giá trị kỳ vọng của y theo đường thẳng tại X= 2 là ŷ. Chúng ta có thể xác nhận điều này bằng phương trình sau:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Giá trị y biểu thị điểm thực tế và giá trị ŷ là giá trị mong đợi của y bằng cách sử dụng phương trình tuyến tính cho giá trị x cho trước.

Bước tiếp theo là xác định phương trình tuyến tính phù hợp nhất với tập hợp các cặp có thứ tự, chúng ta đã nói về vấn đề này trong bài viết trước, trong đó chúng ta đã xác định loại phương trình bằng .

Sử dụng Excel để xác định hồi quy tuyến tính

Để sử dụng được công cụ phân tích hồi quy tích hợp trong Excel, bạn phải kích hoạt add-in Gói phân tích. Bạn có thể tìm thấy nó bằng cách nhấp vào tab Tệp -> Tùy chọn(2007+), trong hộp thoại xuất hiện Tùy chọnExcelđi tới tab Tiện ích bổ sung. Trong lĩnh vực Điều khiển chọn Tiện ích bổ sungExcel và nhấp vào Đi. Trong cửa sổ xuất hiện, đánh dấu vào ô bên cạnh Gói phân tích, nhấp chuột ĐƯỢC RỒI.

Trong tab Dữ liệu trong nhóm Phân tích một nút mới sẽ xuất hiện Phân tích dữ liệu.

Để chứng minh hoạt động của tiện ích bổ sung, chúng tôi sẽ sử dụng dữ liệu trong đó một chàng trai và một cô gái ngồi chung bàn trong phòng tắm. Nhập dữ liệu từ ví dụ về bồn tắm của chúng tôi vào Cột A và B của trang trống.

Chuyển đến tab Dữ liệu, trong nhóm Phân tích nhấp chuột Phân tích dữ liệu. Trong cửa sổ hiện ra Phân tích dữ liệu lựa chọn hồi quy như trong hình và nhấn OK.

Đặt các tham số hồi quy cần thiết trong cửa sổ hồi quy, như thể hiện trên hình ảnh:

Nhấp chuột ĐƯỢC RỒI. Hình dưới đây thể hiện kết quả thu được:

Những kết quả này phù hợp với những kết quả chúng tôi thu được bằng cách thực hiện các phép tính của riêng mình trong .

Việc xây dựng hồi quy tuyến tính, đánh giá các tham số của nó và tầm quan trọng của chúng có thể được thực hiện nhanh hơn nhiều khi sử dụng gói phân tích Excel (Regression). Chúng ta hãy xem xét việc giải thích kết quả thu được trong trường hợp chung ( k biến giải thích) theo ví dụ 3.6.

Trong bàn thống kê hồi quy các giá trị sau được đưa ra:

Nhiều R – hệ số tương quan bội;

R- quảng trường- hệ số xác định R 2 ;

Chuẩn hóa R - quảng trường- điều chỉnh R 2 được điều chỉnh theo số bậc tự do;

Lỗi tiêu chuẩn– sai số chuẩn hồi quy S;

Quan sát – số lượng quan sát N.

Trong bàn Phân tích phương saiđược tặng:

1. Cột df - số bậc tự do bằng

cho chuỗi hồi quy df = k;

cho chuỗi còn lạidf = N – k – 1;

cho chuỗi Tổng cộngdf = N– 1.

2. Cột SS – tổng độ lệch bình phương bằng

cho chuỗi hồi quy ;

cho chuỗi còn lại ;

cho chuỗi Tổng cộng .

3. Cột bệnh đa xơ cứng phương sai được xác định theo công thức bệnh đa xơ cứng = SS/df:

cho chuỗi hồi quy– sự phân tán yếu tố;

cho chuỗi còn lại- phương sai dư.

4. Cột F - giá trị tính toán F-tiêu chí được tính bằng công thức

F = bệnh đa xơ cứng(hồi quy)/ bệnh đa xơ cứng(phần còn lại).

5. Cột Ý nghĩa F – giá trị mức ý nghĩa tương ứng với giá trị tính toán F-số liệu thống kê .

Ý nghĩa F= FDIST( F- số liệu thống kê, df(hồi quy), df(phần còn lại)).

Nếu ý nghĩa F < стандартного уровня значимости, то R 2 có ý nghĩa thống kê.

	Tỷ lệ cược	Lỗi tiêu chuẩn	thống kê t	giá trị P	Dưới 95%	95% hàng đầu
Y	65,92	11,74	5,61	0,00080	38,16	93,68
X	0,107	0,014	7,32	0,00016	0,0728	0,142

Bảng này cho thấy:

1. Tỷ lệ cược– giá trị hệ số Một, b.

2. Lỗi tiêu chuẩn– sai số chuẩn của hệ số hồi quy S a, sb.

3. t- số liệu thống kê– giá trị tính toán t - Chỉ tiêu tính theo công thức:

t-statistic = Hệ số/Sai số chuẩn.

4.R-giá trị (ý nghĩa t) là giá trị mức ý nghĩa tương ứng với giá trị được tính toán t- số liệu thống kê.

R-giá trị = NGHIÊN CỨU(t-số liệu thống kê, df(phần còn lại)).

Nếu như R-nghĩa< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. 95% dưới cùng và 95% trên cùng– giới hạn dưới và giới hạn trên của khoảng tin cậy 95% đối với các hệ số của phương trình hồi quy tuyến tính lý thuyết.

RÚT TIỀN Phần còn lại
Quan sát	Dự đoán y	dư lượng e
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

Trong bàn RÚT TIỀN Phần còn lại chỉ ra:

trong cột Quan sát- số quan sát;

trong cột được báo trước y – giá trị tính toán của biến phụ thuộc;

trong cột Thức ăn thừa e – chênh lệch giữa giá trị quan sát và giá trị tính toán của biến phụ thuộc.

Ví dụ 3.6. Có dữ liệu (đơn vị thông thường) về chi phí thực phẩm y và thu nhập bình quân đầu người x cho chín nhóm gia đình:

x
y

Sử dụng kết quả của gói phân tích Excel (Regression), chúng ta sẽ phân tích sự phụ thuộc của chi phí lương thực vào thu nhập bình quân đầu người.

Kết quả phân tích hồi quy thường được viết dưới dạng:

trong đó sai số chuẩn của các hệ số hồi quy được ghi trong ngoặc đơn.

Hệ số hồi quy MỘT = 65,92 và B= 0,107. Hướng giao tiếp giữa y Và x xác định dấu của hệ số hồi quy b= 0,107, tức là kết nối là trực tiếp và tích cực. hệ số b= 0,107 cho thấy thu nhập bình quân đầu người tăng thêm 1 lần như thường lệ. các đơn vị chi phí thực phẩm tăng 0,107 đơn vị thông thường. các đơn vị

Chúng ta hãy đánh giá ý nghĩa của các hệ số của mô hình kết quả. Ý nghĩa của các hệ số ( một, b) được kiểm tra bởi t-Bài kiểm tra:

Giá trị P ( Một) = 0,00080 < 0,01 < 0,05

Giá trị P ( b) = 0,00016 < 0,01 < 0,05,

do đó, các hệ số ( một, b) có ý nghĩa ở mức ý nghĩa 1% và thậm chí còn cao hơn ở mức ý nghĩa 5%. Như vậy, các hệ số hồi quy là có ý nghĩa và mô hình phù hợp với dữ liệu gốc.

Các kết quả ước lượng hồi quy không chỉ tương thích với các giá trị thu được của các hệ số hồi quy mà còn tương thích với một tập hợp nhất định của chúng (khoảng tin cậy). Với xác suất 95%, khoảng tin cậy cho các hệ số là (38,16 – 93,68) cho Một và (0,0728 – 0,142) cho b.

Chất lượng của mô hình được đánh giá bằng hệ số xác định R 2 .

Kích cỡ R 2 = 0,884 có nghĩa là hệ số thu nhập bình quân đầu người có thể giải thích 88,4% sự biến động (phân tán) trong chi phí thực phẩm.

Ý nghĩa R 2 được kiểm tra bởi F- kiểm tra: ý nghĩa F = 0,00016 < 0,01 < 0,05, следовательно, R 2 có ý nghĩa ở mức ý nghĩa 1% và thậm chí còn cao hơn ở mức ý nghĩa 5%.

Trong trường hợp hồi quy tuyến tính theo cặp, hệ số tương quan có thể được định nghĩa là . Giá trị hệ số tương quan thu được cho thấy mối quan hệ giữa chi phí lương thực thực phẩm và thu nhập bình quân đầu người là rất chặt chẽ.

Phân tích hồi quy là một trong những phương pháp nghiên cứu thống kê phổ biến nhất. Nó có thể được sử dụng để thiết lập mức độ ảnh hưởng của các biến độc lập đến biến phụ thuộc. Microsoft Excel có các công cụ được thiết kế để thực hiện loại phân tích này. Hãy xem chúng là gì và cách sử dụng chúng.

Kết nối gói phân tích

Tuy nhiên, để sử dụng chức năng cho phép bạn thực hiện phân tích hồi quy, trước tiên bạn cần kích hoạt Gói Phân tích. Chỉ khi đó các công cụ cần thiết cho quy trình này mới xuất hiện trên dải băng Excel.

Di chuyển đến tab “Tệp”.
Đi tới phần “Cài đặt”.
Cửa sổ Tùy chọn Excel sẽ mở ra. Chuyển đến phần phụ “Tiện ích bổ sung”.
Ở dưới cùng của cửa sổ mở ra, hãy di chuyển nút gạt trong khối “Quản lý” sang vị trí “Phần bổ trợ Excel”, nếu nó ở một vị trí khác. Bấm vào nút “Đi”.
Một cửa sổ các phần bổ trợ Excel có sẵn sẽ mở ra. Chọn hộp bên cạnh “Gói phân tích”. Bấm vào nút “OK”.

Bây giờ, khi chúng ta chuyển đến tab “Dữ liệu”, trên dải băng trong khối công cụ “Phân tích”, chúng ta sẽ thấy một nút mới - “Phân tích dữ liệu”.

Các loại phân tích hồi quy

Có một số loại hồi quy:

parabol;
nghiêm trang;
logarit;
số mũ;
Biểu tình;
hyperbol;
hồi quy tuyến tính.

Chúng ta sẽ nói chi tiết hơn về việc thực hiện loại phân tích hồi quy cuối cùng trong Excel sau.

Hồi quy tuyến tính trong Excel

Ví dụ, bên dưới là bảng hiển thị nhiệt độ không khí trung bình hàng ngày bên ngoài và số lượng khách hàng của cửa hàng trong ngày làm việc tương ứng. Hãy cùng tìm hiểu bằng cách sử dụng phân tích hồi quy chính xác xem điều kiện thời tiết dưới dạng nhiệt độ không khí có thể ảnh hưởng như thế nào đến sự có mặt của một cơ sở bán lẻ.

Phương trình hồi quy tuyến tính tổng quát như sau: Y = a0 + a1x1 +…+ akhk. Trong công thức này, Y có nghĩa là biến mà chúng ta đang cố gắng nghiên cứu ảnh hưởng của các yếu tố. Trong trường hợp của chúng tôi, đây là số lượng người mua. Giá trị của x là các yếu tố khác nhau ảnh hưởng đến biến. Các tham số a là các hệ số hồi quy. Tức là họ là người quyết định tầm quan trọng của một yếu tố cụ thể. Chỉ số k biểu thị tổng số các yếu tố tương tự.

Phân tích kết quả phân tích

Kết quả phân tích hồi quy được hiển thị dưới dạng bảng ở vị trí được chỉ định trong cài đặt.

Một trong những chỉ số chính là bình phương R. Nó cho thấy chất lượng của mô hình. Trong trường hợp của chúng tôi, hệ số này là 0,705 hoặc khoảng 70,5%. Đây là mức chất lượng có thể chấp nhận được. Sự phụ thuộc nhỏ hơn 0,5 là xấu.

Một chỉ báo quan trọng khác nằm trong ô ở giao điểm của hàng “Giao lộ Y” và cột “Hệ số”. Điều này cho biết giá trị Y sẽ có và trong trường hợp của chúng tôi, đây là số lượng người mua, với tất cả các yếu tố khác bằng 0. Trong bảng này, giá trị này là 58,04.

Giá trị tại giao điểm của cột “Biến X1” và “Hệ số” thể hiện mức độ phụ thuộc của Y vào X. Trong trường hợp của chúng tôi, đây là mức độ phụ thuộc của số lượng khách hàng tại cửa hàng vào nhiệt độ. Hệ số 1,31 được coi là chỉ số ảnh hưởng khá cao.

Như bạn có thể thấy, bằng cách sử dụng Microsoft Excel, việc tạo bảng phân tích hồi quy khá dễ dàng. Nhưng chỉ người được đào tạo mới có thể làm việc với dữ liệu đầu ra và hiểu được bản chất của nó.

Chúng tôi rất vui vì chúng tôi có thể giúp bạn giải quyết vấn đề.

Đặt câu hỏi của bạn trong phần bình luận, mô tả chi tiết bản chất của vấn đề. Các chuyên gia của chúng tôi sẽ cố gắng trả lời nhanh nhất có thể.

Bài báo này hữu ích với bạn?

ŷ - giá trị kỳ vọng của y đối với giá trị x cho trước,

x - biến độc lập,

a - đoạn trên trục y của một đường thẳng,

b là độ dốc của đường thẳng

Hình dưới đây minh họa khái niệm này bằng đồ họa:

ŷ và y thường bị nhầm lẫn. Đồ thị thể hiện 6 cặp điểm và một đường thẳng có thứ tự theo phương trình đã cho

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Giá trị y biểu thị điểm thực tế và giá trị ŷ là giá trị mong đợi của y bằng cách sử dụng phương trình tuyến tính cho giá trị x cho trước.

Bước tiếp theo là xác định phương trình tuyến tính phù hợp nhất với tập hợp các cặp có thứ tự, chúng ta đã nói về vấn đề này trong bài viết trước, trong đó chúng ta đã xác định dạng phương trình bằng phương pháp bình phương tối thiểu.

Sử dụng Excel để xác định hồi quy tuyến tính

Trong tab Dữ liệu trong nhóm Phân tích một nút mới sẽ xuất hiện Phân tích dữ liệu.

Để minh họa cách hoạt động của phần bổ trợ, hãy sử dụng dữ liệu từ bài viết trước, trong đó một chàng trai và một cô gái ngồi chung bàn trong phòng tắm. Nhập dữ liệu từ ví dụ về bồn tắm của chúng tôi vào Cột A và B của trang trống.

Đặt các tham số hồi quy cần thiết trong cửa sổ hồi quy, như thể hiện trên hình ảnh:

Nhấp chuột ĐƯỢC RỒI. Hình dưới đây thể hiện kết quả thu được:

Những kết quả này phù hợp với kết quả chúng tôi thu được bằng cách tự tính toán trong bài viết trước.

Phân tích hồi quy là một phương pháp nghiên cứu thống kê cho phép bạn chỉ ra sự phụ thuộc của một tham số cụ thể vào một hoặc nhiều biến độc lập. Trong thời kỳ tiền máy tính, việc sử dụng nó khá khó khăn, đặc biệt là khi có khối lượng dữ liệu lớn. Ngày nay, sau khi học cách xây dựng hồi quy trong Excel, bạn có thể giải quyết các vấn đề thống kê phức tạp chỉ trong vài phút. Dưới đây là những ví dụ cụ thể từ lĩnh vực kinh tế.

Các loại hồi quy

Bản thân khái niệm này đã được Francis Galton đưa vào toán học vào năm 1886. Hồi quy xảy ra:

tuyến tính;
parabol;
nghiêm trang;
số mũ;
hyperbol;
Biểu tình;
logarit.

ví dụ 1

Xét bài toán xác định sự phụ thuộc của số lượng thành viên trong nhóm nghỉ việc vào mức lương bình quân tại 6 doanh nghiệp công nghiệp.

Nhiệm vụ. Tại sáu doanh nghiệp, mức lương bình quân tháng và số lượng nhân viên tự nguyện nghỉ việc được phân tích. Ở dạng bảng ta có:

Để thực hiện nhiệm vụ xác định sự phụ thuộc của số lao động nghỉ việc vào mức lương bình quân tại 6 doanh nghiệp, mô hình hồi quy có dạng phương trình Y = a0 + a1×1 +…+аkxk, trong đó хi là các biến ảnh hưởng, ai là các hệ số hồi quy và k là số lượng nhân tố.

Đối với nhiệm vụ này, Y là chỉ số nhân viên nghỉ việc và yếu tố ảnh hưởng là tiền lương, được ký hiệu là X.

Sử dụng các khả năng của bộ xử lý bảng tính Excel

Phân tích hồi quy trong Excel phải được thực hiện trước bằng cách áp dụng các hàm dựng sẵn cho dữ liệu dạng bảng hiện có. Tuy nhiên, với những mục đích này, tốt hơn hết bạn nên sử dụng tiện ích bổ sung “Gói phân tích” rất hữu ích. Để kích hoạt nó, bạn cần:

từ tab “Tệp”, hãy chuyển đến phần “Tùy chọn”;
trong cửa sổ mở ra, chọn dòng “Tiện ích bổ sung”;
nhấp vào nút “Đi” nằm bên dưới, bên phải dòng “Quản lý”;
đánh dấu vào ô bên cạnh tên “Gói phân tích” và xác nhận hành động của bạn bằng cách nhấp vào “Ok”.

Nếu mọi thứ được thực hiện chính xác, nút bắt buộc sẽ xuất hiện ở phía bên phải của tab “Dữ liệu”, nằm phía trên bảng tính Excel.

Hồi quy tuyến tính trong Excel

Bây giờ chúng ta đã có sẵn tất cả các công cụ ảo cần thiết để thực hiện các phép tính kinh tế lượng, chúng ta có thể bắt đầu giải quyết vấn đề của mình. Đối với điều này:

Nhấp vào nút “Phân tích dữ liệu”;
trong cửa sổ mở ra, nhấp vào nút “Hồi quy”;
trong tab xuất hiện, nhập phạm vi giá trị cho Y (số lượng nhân viên nghỉ việc) và cho X (lương của họ);
Chúng tôi xác nhận hành động của mình bằng cách nhấn nút “Ok”.

Kết quả là chương trình sẽ tự động điền dữ liệu phân tích hồi quy vào bảng tính mới. Ghi chú! Excel cho phép bạn đặt thủ công vị trí bạn thích cho mục đích này. Ví dụ: đây có thể là cùng một trang tính chứa các giá trị Y và X hoặc thậm chí là một sổ làm việc mới được thiết kế đặc biệt để lưu trữ dữ liệu đó.

Phân tích kết quả hồi quy cho R bình phương

Trong Excel, dữ liệu thu được trong quá trình xử lý dữ liệu trong ví dụ đang xem xét có dạng:

Trước hết, bạn nên chú ý đến giá trị bình phương R. Nó đại diện cho hệ số xác định. Trong ví dụ này, R-square = 0,755 (75,5%), tức là các tham số tính toán của mô hình giải thích được mối quan hệ giữa các tham số đang xem xét là 75,5%. Giá trị của hệ số xác định càng cao thì mô hình được chọn càng phù hợp cho một nhiệm vụ cụ thể. Nó được coi là mô tả chính xác tình huống thực tế khi giá trị bình phương R trên 0,8. Nếu R bình phương là tcr thì giả thuyết về sự vô nghĩa của số hạng tự do của phương trình tuyến tính bị bác bỏ.

Trong bài toán đang xem xét số hạng tự do, sử dụng công cụ Excel, ta thu được t = 169,20903 và p = 2,89E-12, tức là chúng ta có xác suất bằng 0 rằng giả thuyết đúng về tầm quan trọng của số hạng tự do sẽ bị bác bỏ . Đối với hệ số của ẩn số t=5,79405 và p=0,001158. Nói cách khác, xác suất mà giả thuyết đúng về tầm quan trọng của hệ số đối với ẩn số sẽ bị bác bỏ là 0,12%.

Vì vậy, có thể lập luận rằng phương trình hồi quy tuyến tính thu được là phù hợp.

Vấn đề về tính khả thi của việc mua một khối cổ phiếu

Hồi quy bội trong Excel được thực hiện bằng cùng một công cụ Phân tích dữ liệu. Hãy xem xét một vấn đề ứng dụng cụ thể.

Ban lãnh đạo công ty NNN phải quyết định xem có nên mua 20% cổ phần của Công ty Cổ phần MMM hay không. Chi phí của gói (SP) là 70 triệu đô la Mỹ. Các chuyên gia của NNN đã thu thập dữ liệu về các giao dịch tương tự. Người ta quyết định đánh giá giá trị của khối cổ phiếu theo các thông số như vậy, được biểu thị bằng hàng triệu đô la Mỹ, như:

khoản phải trả (VK);
khối lượng doanh thu hàng năm (VO);
các khoản phải thu (VD);
nguyên giá tài sản cố định (COF).

Ngoài ra, thông số nợ lương của doanh nghiệp (V3 P) tính bằng nghìn đô la Mỹ cũng được sử dụng.

Giải pháp sử dụng bộ xử lý bảng tính Excel

Trước hết, bạn cần tạo một bảng dữ liệu nguồn. Nó trông như thế này:

gọi cửa sổ “Phân tích dữ liệu”;
chọn phần “Hồi quy”;
Trong ô “Nhập khoảng Y”, nhập khoảng giá trị của các biến phụ thuộc từ cột G;
Nhấp vào biểu tượng có mũi tên màu đỏ ở bên phải cửa sổ “Khoảng thời gian nhập X” và đánh dấu phạm vi của tất cả các giá trị từ các cột B, C, D, F trên trang tính.

Đánh dấu mục “Bảng tính mới” và nhấp vào “Ok”.

Có được một phân tích hồi quy cho một vấn đề nhất định.

Nghiên cứu kết quả và kết luận

Chúng tôi “thu thập” phương trình hồi quy từ dữ liệu được làm tròn được trình bày ở trên trên bảng tính Excel:

SP = 0,103*SOF + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.

Ở dạng toán học quen thuộc hơn, nó có thể được viết là:

y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844

Số liệu của MMM CTCP được trình bày trong bảng:

Thay chúng vào phương trình hồi quy, chúng ta có được con số 64,72 triệu đô la Mỹ. Điều này có nghĩa là cổ phiếu của MMMJSC không đáng mua vì giá trị 70 triệu đô la Mỹ của chúng là khá cao.

Như bạn có thể thấy, việc sử dụng bảng tính Excel và phương trình hồi quy giúp bạn có thể đưa ra quyết định sáng suốt về tính khả thi của một giao dịch rất cụ thể.

Bây giờ bạn đã biết hồi quy là gì. Các ví dụ Excel được thảo luận ở trên sẽ giúp bạn giải quyết các vấn đề thực tế trong lĩnh vực kinh tế lượng.