Regression trong Excel: ví dụ phương trình. hồi quy tuyến tính

phân tích hồi quy - một phương pháp nghiên cứu thống kê cho thấy sự phụ thuộc của một tham số của một hoặc độc lập nhiều biến. Trong thời đại trước máy tính, sử dụng của nó đã được khá khó khăn, đặc biệt là khi nó đến khối lượng lớn dữ liệu. Hôm nay, học tập làm thế nào để xây dựng một hồi quy trong Excel, bạn có thể giải quyết 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ể về kinh tế.

loại hồi quy

Khái niệm này được giới thiệu với toán học của Francis Galton vào năm 1886. Hồi quy là:

• tuyến tính;
• parabol;
• điện;
• mũ;
• hyperbol;
• mũ;
• logarit.

VÍ DỤ 1

Hãy xem xét các vấn đề xác định sự phụ thuộc của số lượng đơn từ chức của các thành viên của đội ngũ nhân viên mức lương trung bình trong 6 doanh nghiệp công nghiệp.

Nhiệm vụ. Sáu công ty đã phân tích mức lương trung bình hàng tháng và số lượng nhân viên từ bỏ cách tự nguyện. Dưới dạng bảng ta có:

	Một	B	C
1	X	Số vụ từ chức	tiền lương
2		y	30000 rúp
3	1	60	35000 rúp
4	2	35	40000 rúp
5	3	20	45000 rúp
6	4	20	50.000 rúp
7	5	15	55000 rúp
8	6	15	60000 rúp

Đối với vấn đề xác định sự phụ thuộc của những người lao động số lượng tách từ mức lương trung bình cho 6 doanh nghiệp mô hình hồi quy có dạng của phương trình Y = a ₀ + a ₁ x ₁ + ... + a _k x _k, trong đó x _i - biến ảnh hưởng, a _i - hệ số hồi quy, ak - số yếu tố.

Y đối với một nhiệm vụ nhất định - đó là một chỉ số để bắn một nhân viên, một yếu tố góp - tiền lương, được biểu thị bởi X.

Tận dụng sức mạnh của "Excel" bảng

phân tích hồi quy trong Excel nên được bắt đầu bằng ứng dụng dữ liệu bảng hiện có tích hợp chức năng. Tuy nhiên, vì những mục đích nó là tốt hơn để sử dụng một add-in "phân tích gói" rất hữu ích. Để kích hoạt nó, bạn cần phải:

• với tab "File" hãy vào "Settings";
• trong cửa sổ mở ra, chọn 'Add-ons';
• bấm vào nút "Go", nằm ở góc dưới bên phải của dòng "quản lý";
• đặt một dấu kiểm bên cạnh "Phân tích ToolPak" và khẳng định hành động của bạn bằng cách nhấn "OK".

Nếu được thực hiện một cách chính xác, phía bên phải của các "dữ liệu" tab, nằm phía trên tờ làm việc "Excel", cho thấy các nút mong muốn.

Linear Regression trong Excel

Bây giờ bạn đã có trong tay tất cả những công cụ ảo cần thiết cho các tính toán kinh tế lượng, chúng ta có thể bắt đầu để giải quyết vấn đề của chúng tôi. Để làm điều này:

• button được click vào "Phân tích dữ liệu";
• bấm vào nút "hồi quy" trong cửa sổ mở;
• một tab xuất hiện để giới thiệu một loạt các giá trị Y (số lượng công nhân tách) và X (lương của họ);
• khẳng định hành động của họ bằng cách nhấn vào nút «OK».

Do vậy, chương trình sẽ tự động điền vào các tấm bảng phân tích hồi quy dữ liệu mới. Hãy chú ý! Trong Excel, có một cơ hội để thiết lập vị trí mà bạn thích cho mục đích này. Ví dụ, nó có thể là tấm cùng, nơi các giá trị Y và X, hoặc thậm chí một cuốn sách mới, được thiết kế đặc biệt cho việc lưu trữ các dữ liệu đó.

Kết quả phân tích hồi quy cho R-square

Các dữ liệu Excel thu được trong các dữ liệu ví dụ coi có dạng:

Trước hết, chúng ta nên chú ý đến giá trị của R-squared. Nó đại diện cho hệ số xác định. Trong ví dụ này, R-square = 0,755 (75,5%), m. E. Các thông số tính toán của mô hình để giải thích mối quan hệ giữa các thông số được coi là bằng 75,5%. Càng cao giá trị của hệ số xác định, mô hình được chọn sẽ được coi là hữu ích hơn cho các nhiệm vụ cụ thể. Người ta tin để mô tả một cách chính xác tình hình thực tế theo giá trị R-square trên 0,8. Nếu R-square <0,5, sau đó một phân tích hồi quy trong Excel có thể không được coi là hợp lý.

phân tích tỷ lệ

Số 64,1428 cho thấy những gì sẽ là giá trị của Y, nếu tất cả các biến xi trong mô hình của chúng tôi sẽ được đặt lại. Nói cách khác, nó có thể lập luận rằng giá trị của tham số phân tích bị ảnh hưởng bởi các yếu tố khác so với những mô tả trong mô hình cụ thể.

Yếu tố tiếp theo -0,16285 nằm ở B18 thoại di động, cho thấy sự ảnh hưởng quan trọng của biến X để Y. Điều này có nghĩa rằng mức lương trung bình của người lao động trong mô hình ảnh hưởng đến số lượng đơn từ chức từ trọng lượng của -0,16285, t. E. Mức độ ảnh hưởng của nó ở tất cả nhỏ. Dấu hiệu "-" chỉ ra rằng hệ số là tiêu cực. Rõ ràng, vì tất cả chúng ta đều biết rằng mức lương ở những doanh nghiệp, người dân ít đã bày tỏ mong muốn chấm dứt hợp đồng lao động hoặc sa thải.

hồi quy nhiều

Theo thuật ngữ này dùng để chỉ phương trình thông tin liên lạc với một số biến độc lập có dạng:

y = f (x ₁ + x ₂ + ... x _m) + ε, trong đó y - là một điểm tính năng (biến phụ thuộc), và x _1, x _2, ... x _m - những dấu hiệu yếu tố (biến độc lập).

ước lượng tham số

Đối với nhiều hồi quy (MR) nó được thực hiện bằng một phương pháp bình phương nhỏ nhất (LSM). Đối với phương trình tuyến tính có dạng Y = a + b ₁ x _{1 + ... + b m x m +} ε xây dựng một hệ phương trình bình thường (cm Dưới đây.)

Để hiểu được nguyên tắc của phương pháp này, chúng ta xem xét trường hợp hai yếu tố. Sau đó, chúng tôi đã tình hình được mô tả bởi công thức

Do đó, chúng tôi có được:

nơi σ - là phương sai của các tính năng tương ứng, phản ánh trong chỉ mục.

MNC áp dụng đối với phương trình MR để standartiziruemom quy mô. Trong trường hợp này, chúng tôi nhận được phương trình:

trong đó t _y, t _{x 1, ...} t _xm - standartiziruemye biến mà giá trị trung bình là 0; beta _i - hệ số hồi quy chuẩn và độ lệch chuẩn - 1.

Xin lưu ý rằng tất cả các beta _i trong trường hợp này được xác định là bình thường và tsentraliziruemye, do đó một sự so sánh giữa một coi là hợp lệ và có thể chấp nhận. Bên cạnh đó, nó được chấp nhận để thực hiện kiểm tra các yếu tố, loại bỏ những người có giá trị thấp nhất của βi.

Vấn đề với việc sử dụng phương trình hồi quy tuyến tính

Giả sử bạn có một bảng của sự năng động của giá của một sản phẩm N đặc biệt đối với 8 tháng qua. Nó là cần thiết để quyết định xem việc mua lại của đảng của ông với mức giá năm 1850 rúp. / T.

	Một	B	C
1	tháng	tên của tháng	Giá N
2	1	tháng giêng	1750 rúp mỗi tấn
3	2	tháng hai	1755 rúp mỗi tấn
4	3	tháng ba	1767 rúp mỗi tấn
5	4	tháng tư	1760 rúp mỗi tấn
6	5	có thể	1770 rúp mỗi tấn
7	6	tháng sáu	1790 rúp mỗi tấn
8	7	tháng bảy	1810 rúp mỗi tấn
9	8	tháng tám	1840 rúp mỗi tấn

Để giải quyết vấn đề này trong bộ vi xử lý bảng "Excel" cần thiết để sử dụng đã được biết đến ví dụ cụ "Phân tích dữ liệu" trình bày ở trên. Tiếp theo, chọn "Regression" và thiết lập các thông số. Chúng ta phải nhớ rằng trong "phạm vi Input Y» nên được giới thiệu với một loạt các giá trị của biến phụ thuộc (trong trường hợp này giá của hàng hóa trong tháng cụ thể trong năm) và trong "Input khoảng X» - cho một độc lập (tháng). Chúng tôi khẳng định hành động bằng cách nhấn vào «Ok». Trong một bảng tính mới (nếu có chỉ định), chúng tôi có được dữ liệu cho hồi quy.

Chúng tôi đang xây dựng trên đó phương trình tuyến tính có dạng y = ax + b, trong đó là các thông số a và b là các hệ số từ số dòng của tháng và tên của các hệ số và «Y-ngã" dòng của tấm với các kết quả phân tích hồi quy. Do đó, phương trình tuyến tính hồi quy (EQ) 3 cho vấn đề có thể được viết như sau:

Giá hàng hóa N = 11.714 * 1727,54 tháng số +.

hoặc trong ký hiệu đại số

y = 11.714 x + 1727,54

phân tích kết quả

Để quyết định có nhận được phương trình hồi quy tuyến tính đầy đủ bằng cách sử dụng nhiều hệ số tương quan (CMC) và quyết tâm cũng như kiểm tra và t-test Fisher. Trong bảng "Excel" hồi quy với kết quả chúng hoạt động dưới tên nhiều R, R-Square, F-t-thống kê và thống kê, tương ứng.

KMC R cho phép để ước lượng mối quan hệ gần gũi giữa xác suất biến độc lập và phụ thuộc. có giá trị cao của nó cho thấy một kết nối đủ chặt chẽ giữa biến "Số của tháng" và "N giá sản phẩm trong rúp mỗi 1 tấn." Tuy nhiên, bản chất của mối quan hệ này là không rõ.

Bình phương của hệ số xác định R ² (RI) là một đặc trưng số của tỷ lệ tổng phân tán và cho thấy một phân tán của phần dữ liệu thực nghiệm, ví dụ: giá trị của các biến phụ thuộc tương ứng với một phương trình hồi quy tuyến tính. Trong vấn đề này, giá trị này là 84,8%, E. kê với một mức độ chính xác cao thu được được mô tả SD mp..

F-thống kê, còn được gọi là tiêu chuẩn Fisher dùng để đánh giá tầm quan trọng của sự phụ thuộc tuyến tính hoặc bác bỏ giả thuyết xác nhận sự tồn tại của nó.

Giá trị của t-statistic (thử nghiệm t sinh viên) giúp đánh giá tầm quan trọng của hệ số tại bất kỳ thành viên phụ thuộc tuyến tính không rõ miễn phí. Nếu giá trị của t-test> t _cr, giả thuyết của một phương trình vô nghĩa tuyến tính của hạn miễn phí bị từ chối.

Trong bài toán này với nhiệm kỳ miễn phí thông qua công cụ "Excel" nó đã được tìm thấy rằng t = 169,20903, và p = 2,89E-12, t. E. Có một xác suất zero rằng các tín hữu sẽ bị từ chối giả thuyết của vô nghĩa của thuật ngữ miễn phí. Đối với hệ số chưa biết tại t = 5,79405, và p = 0,001158. Nói cách khác, xác suất mà một giả thuyết đúng từ chối sẽ vô nghĩa của hệ số cho biết, là 0,12%.

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

Vấn đề của advisability mua cổ phiếu

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

Hướng dẫn 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». giá trọn gói (SP) là 70 triệu USD. Các chuyên gia của «NNN» thu thập dữ liệu về giao dịch tương tự. Đó là quyết định để đánh giá giá trị của cổ phiếu trên các thông số như vậy, thể hiện bằng triệu đô la Mỹ, chẳng hạn như:

• Phải trả (VK);
• khối lượng doanh thu hàng năm (VO);
• Phải thu (VD);
• giá trị tài sản cố định (SOF).

Bên cạnh đó, sử dụng các khoản nợ lương của các doanh nghiệp (V3 U) trong hàng ngàn đô la Mỹ.

Bộ xử lý bảng quyết định phương tiện Excel

Trước tiên, bạn cần tạo một bảng dữ liệu đầu vào. Nó là như sau:

tiếp theo:

• hộp gọi là "phân tích dữ liệu";
• chọn "Regression" phần;
• cửa sổ "Input khoảng Y» quản lý phạm vi giá trị biến phụ thuộc từ cột G;
• nhấp vào biểu tượng với một mũi tên màu đỏ ở bên phải của cửa sổ "Input khoảng X» và bị cô lập trên một loạt tờ của tất cả các giá trị của cột B, C, D, F.

Đánh dấu điểm "New bảng" và bấm vào nút "Ok".

Nhận một phân tích hồi quy cho nhiệm vụ này.

Các kết quả nghiên cứu và kết luận

"Thu thập" làm tròn từ dữ liệu trình bày ở trên trên bàn tờ phương trình Excel xử lý hồi quy:

SD = 0,103 * SOF + 0.541 * VO - 0031 * VK + 0.405 * VD + 0.691 * VZP - 265.844.

Trong hình thức toán học thông thường hơn nó có thể được viết như sau:

y = 0.103 * x1 + 0.541 * x2 - 0031 * x3 + 0.405 * x4 + 0.691 * x5 - 265.844

Dữ liệu cho «MMM» Công ty Cổ phần thể hiện trong bảng dưới đây:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102,5	535,5	45.2	41,5	21,55	64,72

Thay chúng vào phương trình hồi quy, thu được một con số 64.720.000 USD. Điều này có nghĩa rằng cổ phiếu của Công ty Cổ phần «MMM» không nên mua, vì chi phí của họ là khá đắt đỏ tại 70 triệu USD.

Như bạn có thể thấy, việc sử dụng các bảng tính "Excel" và phương trình hồi quy được phép đưa ra quyết định thông báo về việc advisability giao dịch khá cụ thể.

Bây giờ bạn biết những gì một hồi quy. Ví dụ để Excel, thảo luận ở trên, sẽ giúp bạn giải quyết vấn đề thực tế của kinh tế.