Dự báo khoảng thời gian trong excel để phân tích hồi quy. Chúng tôi rất vui vì chúng tôi có thể giúp bạn giải quyết vấn đề. Kích hoạt gói phân tích
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 vào các yếu tố chính để dự đoán và lập kế hoạch phát triển lĩnh vực ưu tiên, ra 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 việc xây dựng là một ví dụ mô hình hồi quy trong Excel và diễn giải kết quả. Hãy lấy loại tuyến tính hồi quy.
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 lương trung bình.
Người mẫu hồi quy tuyến tính Nó có lượt xem tiếp theo:
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ẽ:
1. Nhấp vào nút “Office” và chuyển đến tab “” Tùy chọn Excel" "Tiện ích bổ sung".
2. Ở dưới cùng, trong danh sách thả xuống, trong trường “Quản lý” sẽ có dòng chữ “ Tiện ích bổ sung của Excel» (nếu không có thì nhấn vào ô đánh dấu bên phải và chọn). Và nút “Đi”. Nhấp chuột.
3. Danh sách các tiện ích bổ sung có sẵn sẽ mở ra. Chọn “Gói phân tích” và nhấp vào OK.
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.
1. Mở menu của công cụ “Phân tích dữ liệu”. Chọn "Hồi quy".
2. Một menu sẽ mở ra để chọn các giá trị đầu vào và tùy chọn đầu ra (nơi hiển thị kết quả). Trong các trường dành cho dữ liệu ban đầu, chúng tôi chỉ ra phạm vi của tham số được mô tả (Y) và yếu tố ảnh hưởng đến nó (X). Phần còn lại không cần phải điền.
3. Sau khi nhấn OK, chương trình sẽ hiển thị các phép tính trên một trang tính mới (bạn có thể chọn khoảng thời gian hiển thị trên trang tính hiện tại hoặc gán đầu ra cho một sổ làm việc mới).
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 chất lượng tốt hơn. 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ị ảnh hưởng xấu: lương càng cao thì càng ít người nghỉ việc. Điều đó là công bằng.
Phân tích hồi quy- Cái này phương pháp thống kê nghiên cứu 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à 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 đư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 6 doanh nghiệp, mức lương bình quân tháng và số lao động nghỉ việc do theo ý muốn. TRONG dạng bảng chúng ta có:
Số người bỏ cuộc | Lương |
||
30.000 rúp |
|||
35.000 rúp |
|||
40.000 rúp |
|||
45.000 rúp |
|||
50.000 rúp |
|||
55.000 rúp |
|||
60.000 rúp |
Để 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 = a 0 + a 1 x 1 +...+a k x k, trong đó x i là các biến ảnh hưởng, a i là hệ số hồi quy và k là số lượng nhân tố.
Đối với bài toán này, Y là chỉ số nghỉ việc của nhân viên 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, ở phía bên phải của tab “Dữ liệu”, nằm phía trên bảng tính “Excel”, bạn sẽ thấy nút mong muốn.
trong Excel
Bây giờ bạn đã có mọi thứ bạn cần trong tay nhạc cụ ảoĐể thực hiện các phép tính kinh tế lượng, chúng ta có thể bắt đầu giải bài toá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ả chương trình sẽ tự động điền lá mới bộ xử lý bảng dữ liệu phân tích hồi quy. 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ụ: nó có thể là cùng một trang tính chứa các giá trị Y và X hoặc thậm chí Một quyển sách 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 Dữ liệu Excel thu được trong quá trình xử lý dữ liệu của 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 trong ví dụ này R-squared = 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 xét là 75,5%. Giá trị của hệ số xác định càng cao thì mô hình được lựa chọn được coi là có khả năng áp dụng cao hơn cho 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<0,5, то такой анализа регрессии в Excel нельзя считать резонным.
Phân tích tỷ lệ cược
Số 64,1428 cho biết giá trị của Y sẽ là bao nhiêu nếu tất cả các biến xi trong mô hình mà chúng ta đang xem xét được đặt lại về 0. Nói cách khác, có thể lập luận rằng giá trị của tham số được 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ột mô hình cụ thể.
Hệ số tiếp theo -0,16285, nằm trong ô B18, biểu thị trọng số ảnh hưởng của biến X đến Y. Điều này có nghĩa là mức lương trung bình hàng tháng của nhân viên trong mô hình đang được xem xét ảnh hưởng đến số người bỏ việc có trọng số -0,16285, tức là. mức độ ảnh hưởng của nó là hoàn toàn nhỏ. Dấu "-" cho biết hệ số âm. Điều này là hiển nhiên, vì mọi người đều biết rằng mức lương ở doanh nghiệp càng cao thì càng ít người bày tỏ mong muốn chấm dứt hợp đồng lao động hoặc nghỉ việc.
Hồi quy bội
Thuật ngữ này đề cập đến một phương trình quan hệ với một số biến độc lập có dạng:
y=f(x 1 +x 2 +…x m) + ε, trong đó y là đặc tính tổng hợp (biến phụ thuộc), và x 1, x 2,…x m là đặc điểm nhân tố (biến độc lập).
Ước tính tham số
Đối với hồi quy bội (MR), nó được thực hiện bằng phương pháp bình phương tối thiểu (OLS). Đối với các phương trình tuyến tính có dạng Y = a + b 1 x 1 +…+b m x m + ε ta xây dựng hệ phương trình chuẩn (xem bên dưới)
Để hiểu nguyên tắc của phương pháp, hãy xem xét trường hợp hai yếu tố. Khi đó ta có tình huống được mô tả bằng công thức
Từ đây chúng tôi nhận được:
trong đó σ là phương sai của đặc điểm tương ứng được phản ánh trong chỉ mục.
OLS có thể áp dụng cho phương trình MR trên thang đo chuẩn hóa. Trong trường hợp này, chúng ta nhận được phương trình:
trong đó ty y, t x 1, ... t xm là các biến chuẩn hóa, có giá trị trung bình bằng 0; β i là hệ số hồi quy chuẩn hóa và độ lệch chuẩn là 1.
Xin lưu ý rằng tất cả β i trong trong trường hợp nàyđược quy định là tiêu chuẩn hóa và tập trung, do đó việc so sánh chúng với nhau được coi là chính xác và có thể chấp nhận được. Ngoài ra, thông thường người ta sàng lọc các yếu tố bằng cách loại bỏ những yếu tố có giá trị βi thấp nhất.
Vấn đề sử dụng phương trình hồi quy tuyến tính
Giả sử chúng ta có một bảng diễn biến giá của một sản phẩm cụ thể N trong 8 tháng qua. Cần phải đưa ra quyết định về việc có nên mua một lô với mức giá 1850 rúp/tấn hay không.
số tháng | tên tháng | giá sản phẩm N |
|
1750 rúp mỗi tấn |
|||
1755 rúp mỗi tấn |
|||
1767 rúp mỗi tấn |
|||
1760 rúp mỗi tấn |
|||
1770 rúp mỗi tấn |
|||
1790 rúp mỗi tấn |
|||
1810 rúp mỗi tấn |
|||
1840 rúp mỗi tấn |
|||
Để giải quyết vấn đề này trong bộ xử lý bảng tính Excel, bạn cần sử dụng công cụ "Phân tích dữ liệu", đã được biết đến từ ví dụ được trình bày ở trên. Tiếp theo, chọn phần “Hồi quy” và đặt tham số. Cần phải nhớ rằng trong trường “Khoảng đầu vào Y”, phải nhập một loạt giá trị cho biến phụ thuộc (trong trường hợp này là giá hàng hóa trong các tháng cụ thể trong năm) và trong “Khoảng đầu vào X” - đối với biến độc lập (số tháng). Xác nhận hành động bằng cách nhấp vào “Ok”. Trên một trang tính mới (nếu được chỉ định), chúng tôi thu được dữ liệu hồi quy.
Chúng tôi xây dựng theo họ phương trình đường thẳng có dạng y=ax+b, trong đó các tham số a và b là các hệ số của dòng có tên số tháng và các hệ số và dòng “Giao điểm Y” từ bảng có kết quả phân tích hồi quy. Do đó, phương trình hồi quy tuyến tính (LR) cho nhiệm vụ 3 được viết là:
Giá sản phẩm N = 11,714* số tháng + 1727,54.
hoặc trong ký hiệu đại số
y = 11,714 x + 1727,54
Phân tích kết quả
Để quyết định liệu phương trình hồi quy tuyến tính thu được có phù hợp hay không, các hệ số tương quan bội (MCC) và phép xác định được sử dụng, cũng như phép thử Fisher và phép thử t của Sinh viên. Trong bảng tính Excel có kết quả hồi quy, chúng lần lượt được gọi là bội số R, R bình phương, F-statistic và t-statistic.
KMC R giúp đánh giá mức độ gần gũi của mối quan hệ xác suất giữa các biến độc lập và biến phụ thuộc. Giá trị cao của nó cho thấy mối liên hệ khá chặt chẽ giữa các biến “Số tháng” và “Giá của sản phẩm N tính bằng rúp trên 1 tấn”. Tuy nhiên, bản chất của mối quan hệ này vẫn chưa được biết.
Bình phương của hệ số xác định R2 (RI) là một đặc tính số của tỷ lệ của tổng độ phân tán và biểu thị độ phân tán của phần nào của dữ liệu thực nghiệm, tức là. giá trị của biến phụ thuộc tương ứng với phương trình hồi quy tuyến tính. Trong bài toán đang được xem xét, giá trị này bằng 84,8%, tức là dữ liệu thống kê được mô tả với độ chính xác cao bằng SD kết quả.
Thống kê F, còn được gọi là phép thử Fisher, được sử dụng để đánh giá tầm quan trọng của mối quan hệ tuyến tính, bác bỏ hoặc xác nhận giả thuyết về sự tồn tại của nó.
(Bài kiểm tra của học sinh) giúp đánh giá tầm quan trọng của hệ số đối với một số hạng chưa biết hoặc tự do của mối quan hệ tuyến tính. Nếu giá trị của t-test > tcr thì giả thuyết về sự không đáng kể 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ề sự không đáng kể 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ổ phần 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 mũi tên màu đỏ ở bên phải cửa sổ “Phạm vi đầu vào X” và đánh dấu trên trang tính phạm vi của tất cả các giá trị từ cột B, C,D,F.
Đá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 MMMJSC đượ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ộ xử lý 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 quyết định vấn đề thực tế từ lĩnh vực kinh tế lượng.
Theo tôi, khi còn là sinh viên, kinh tế lượng là một trong những ngành khoa học ứng dụng nhất mà tôi có thể làm quen trong khuôn viên trường đại học của mình. Với sự trợ giúp của nó, thực sự có thể giải quyết các vấn đề ứng dụng ở quy mô doanh nghiệp. Câu hỏi thứ ba là những quyết định này sẽ có hiệu quả như thế nào. Điểm mấu chốt là hầu hết kiến thức sẽ vẫn là lý thuyết, nhưng kinh tế lượng và phân tích hồi quy vẫn đáng được quan tâm đặc biệt nghiên cứu.
Hồi quy giải thích điều gì?
Trước khi chúng ta bắt đầu xem xét các chức năng của MS Excel cho phép chúng ta giải quyết những vấn đề này, tôi muốn giải thích chi tiết cho bạn về bản chất, phân tích hồi quy bao gồm những gì. Điều này sẽ giúp bạn vượt qua kỳ thi dễ dàng hơn và quan trọng nhất là việc học môn này sẽ thú vị hơn.
Hy vọng rằng bạn đã quen với khái niệm hàm số trong toán học. Hàm là mối quan hệ giữa hai biến. Khi một biến thay đổi, điều gì đó sẽ xảy ra với biến khác. Chúng ta thay đổi X và Y thay đổi tương ứng. Chức năng mô tả các luật khác nhau. Biết hàm này, chúng ta có thể thay thế các giá trị tùy ý của X và xem Y thay đổi như thế nào.
Nó có tầm quan trọng lớn, vì hồi quy là một nỗ lực nhằm giải thích, thoạt nhìn, các quá trình hỗn loạn và không có hệ thống bằng cách sử dụng một chức năng nhất định. Ví dụ, có thể xác định mối quan hệ giữa tỷ giá hối đoái của đồng đô la và tỷ lệ thất nghiệp ở Nga.
Nếu chúng ta có thể phát hiện ra mô hình này, thì bằng cách sử dụng hàm chúng ta thu được trong quá trình tính toán, chúng ta sẽ có thể đưa ra dự báo về tỷ lệ thất nghiệp sẽ là bao nhiêu theo tỷ giá hối đoái của đô la thứ N so với đồng rúp.
Mối quan hệ này sẽ được gọi là tương quan. Phân tích hồi quy bao gồm việc tính toán hệ số tương quan sẽ giải thích mối quan hệ chặt chẽ giữa các biến số mà chúng ta đang xem xét (tỷ giá hối đoái đồng đô la và số lượng việc làm).
Hệ số này có thể dương hoặc âm. Giá trị của nó nằm trong khoảng từ -1 đến 1. Theo đó, chúng ta có thể quan sát thấy mối tương quan âm hoặc dương cao. Nếu nó là tích cực, thì sự gia tăng tỷ giá hối đoái của đồng đô la sẽ kéo theo việc tạo ra việc làm mới. Nếu nó âm, điều đó có nghĩa là tỷ giá hối đoái tăng sẽ kéo theo việc giảm việc làm.
Có một số loại hồi quy. Nó có thể là tuyến tính, parabol, lũy thừa, hàm mũ, v.v. Chúng tôi chọn một mô hình tùy thuộc vào hồi quy nào sẽ tương ứng cụ thể với trường hợp của chúng tôi, mô hình nào sẽ càng gần với mối tương quan của chúng tôi càng tốt. Hãy xem xét vấn đề này bằng cách sử dụng một vấn đề mẫu và giải nó trong MS Excel.
Hồi quy tuyến tính trong MS Excel
Để giải quyết các vấn đề hồi quy tuyến tính, bạn sẽ cần chức năng Phân tích dữ liệu. Nó có thể không được kích hoạt cho bạn, vì vậy bạn cần kích hoạt nó.
- Nhấp vào nút “Tệp”;
- Chọn mục “Tùy chọn”;
- Nhấp vào tab áp chót “Tiện ích bổ sung” ở phía bên trái;
- Bên dưới chúng ta sẽ thấy dòng chữ “Quản lý” và nút “Đi”. Nhấn vào nó;
- Đánh dấu vào ô “Gói phân tích”;
- Nhấp vào “được”.
Nhiệm vụ mẫu
Chức năng phân tích hàng loạt được kích hoạt. Hãy giải quyết vấn đề sau. Chúng tôi có một mẫu dữ liệu trong vài năm về số lượng tình huống khẩn cấp trên lãnh thổ của doanh nghiệp và số lượng công nhân được tuyển dụng. Chúng ta cần xác định mối quan hệ giữa hai biến này. Có một biến giải thích X – đây là số lượng công nhân và một biến giải thích – Y – đây là số sự cố khẩn cấp. Hãy phân phối dữ liệu nguồn thành hai cột.
Hãy chuyển đến tab “dữ liệu” và chọn “Phân tích dữ liệu”
Trong danh sách xuất hiện, chọn “Hồi quy”. Trong các khoảng đầu vào Y và X, chúng tôi chọn các giá trị phù hợp.
Nhấp vào "Được". Quá trình phân tích hoàn tất và chúng ta sẽ thấy kết quả trong một trang tính mới.
Các giá trị quan trọng nhất đối với chúng tôi được đánh dấu trong hình bên dưới.
Nhiều R là hệ số xác định. Nó có một công thức tính toán phức tạp và cho thấy bạn có thể tin tưởng hệ số tương quan của chúng tôi đến mức nào. Theo đó, giá trị này càng cao thì càng có nhiều sự tin cậy, mô hình của chúng tôi nói chung càng thành công.
Y-Intercept và X1-Intercept là các hệ số hồi quy của chúng tôi. Như đã đề cập, hồi quy là một hàm và nó có các hệ số nhất định. Do đó, hàm của chúng ta sẽ có dạng: Y = 0,64*X-2,84.
Điều này mang lại cho chúng ta điều gì? Điều này cho chúng ta cơ hội để đưa ra dự báo. Giả sử chúng ta muốn thuê 25 công nhân cho một doanh nghiệp và chúng ta cần hình dung đại khái số lượng sự cố khẩn cấp sẽ là bao nhiêu. Chúng tôi thay thế nó vào chức năng của chúng tôi giá trị đã cho và ta được kết quả Y = 0,64 * 25 – 2,84. Chúng ta sẽ có khoảng 13 trường hợp khẩn cấp.
Hãy xem nó hoạt động như thế nào. Hãy xem hình dưới đây. Các giá trị thực tế của các nhân viên liên quan được thay thế vào hàm mà chúng tôi nhận được. Xem mức độ gần gũi của các giá trị với người chơi thực sự.
Bạn cũng có thể xây dựng trường tương quan bằng cách chọn vùng của Y và X, nhấp vào tab "chèn" và chọn biểu đồ phân tán.
Các chấm nằm rải rác nhưng nhìn chung di chuyển lên trên, như thể có một đường thẳng ở giữa. Và bạn cũng có thể thêm dòng này bằng cách vào tab “Bố cục” trong MS Excel và chọn “Đường xu hướng”
Nhấp đúp vào dòng xuất hiện và bạn sẽ thấy những gì đã được đề cập trước đó. Bạn có thể thay đổi loại hồi quy tùy thuộc vào trường tương quan của bạn trông như thế nào.
Bạn có thể cảm thấy rằng các điểm vẽ một hình parabol chứ không phải một đường thẳng và sẽ tốt hơn nếu bạn chọn một kiểu hồi quy khác.
Phần kết luận
Hy vọng bài viết này đã giúp bạn hiểu rõ hơn về phân tích hồi quy là gì và tại sao nó lại cần thiết. Tất cả điều này có tầm quan trọng thực tế lớn.
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 tại đặt giá trị X,
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 sẽ xuất hiện nút mới 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ư được hiển thị 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 .
Hồi quy trong chương trình excel
Việc xử lý dữ liệu thống kê cũng có thể được thực hiện bằng cách sử dụng tiện ích bổ sung Gói phân tích trong mục phụ menu “Dịch vụ”. Trong Excel 2003, nếu bạn mở DỊCH VỤ, chúng tôi không thể tìm thấy tab PHÂN TÍCH DỮ LIỆU, sau đó nhấn chuột trái để mở tab TUYỆT VỜI và đối diện với điểm GÓI PHÂN TÍCH Nhấn chuột trái để đánh dấu (Hình 17).
Cơm. 17. Cửa sổ TUYỆT VỜI
Sau đó trong menu DỊCH VỤ tab xuất hiện PHÂN TÍCH DỮ LIỆU.
Trong Excel 2007 để cài đặt GÓI PHÂN TÍCH bạn cần nhấn nút VĂN PHÒNG bên trái góc trên cùng chiếc lá (Hình 18a). Tiếp theo, bấm vào nút CÀI ĐẶT EXCEL. Trong cửa sổ hiện ra CÀI ĐẶT EXCEL nhấp chuột trái vào mục TUYỆT VỜI và ở bên phải danh sách thả xuống, chọn mục GÓI PHÂN TÍCH. Tiếp theo bấm vào ĐƯỢC RỒI.
Cơm. 18. Cài đặt GÓI PHÂN TÍCH trong Excel 2007
Để cài đặt Gói Phân tích, nhấp vào nút ĐI, nằm ở dưới cùng của cửa sổ đang mở. Một cửa sổ sẽ xuất hiện như trong Hình. 12. Đánh dấu vào trước GÓI PHÂN TÍCH. Trong tab DỮ LIỆU một nút sẽ xuất hiện PHÂN TÍCH DỮ LIỆU(Hình 19).
Từ các mục gợi ý, hãy chọn mục “ HỒI QUY" và nhấp vào nó bằng nút chuột trái. Tiếp theo, nhấp vào OK.
Một cửa sổ sẽ xuất hiện như trong Hình. 21
Công cụ phân tích " HỒI QUY» được sử dụng để khớp biểu đồ với một tập hợp các quan sát bằng phương pháp bình phương tối thiểu. Hồi quy được sử dụng để phân tích tác động lên một cá nhân phụ thuộc biến giá trị một hoặc nhiều biến độc lập. Ví dụ: một số yếu tố ảnh hưởng đến thành tích thể thao của vận động viên, bao gồm tuổi tác, chiều cao và cân nặng. Có thể tính toán mức độ mà mỗi yếu tố trong số ba yếu tố này ảnh hưởng đến thành tích của một vận động viên và sau đó sử dụng dữ liệu đó để dự đoán thành tích của một vận động viên khác.
Công cụ hồi quy sử dụng hàm DÒNG.
Hộp thoại HỒI PHỤC
Nhãn Chọn hộp kiểm nếu hàng đầu tiên hoặc cột đầu tiên của phạm vi đầu vào có chứa tiêu đề. Xóa hộp kiểm này nếu không có tiêu đề. Trong trường hợp này, các tiêu đề phù hợp cho dữ liệu bảng đầu ra sẽ được tạo tự động.
Mức độ tin cậy Chọn hộp kiểm để bao gồm một mức bổ sung trong bảng tóm tắt đầu ra. Trong trường thích hợp, hãy nhập mức độ tin cậy mà bạn muốn áp dụng, ngoài mức 95% mặc định.
Hằng số - 0 Chọn hộp kiểm để buộc đường hồi quy đi qua gốc tọa độ.
Phạm vi đầu ra Nhập tham chiếu đến ô trên cùng bên trái của phạm vi đầu ra. Cung cấp ít nhất bảy cột cho bảng tóm tắt đầu ra, trong đó sẽ bao gồm: kết quả ANOVA, hệ số, sai số chuẩn của phép tính Y, độ lệch chuẩn, số lượng quan sát, sai số chuẩn cho các hệ số.
Trang tính mới Chọn tùy chọn này để mở một trang tính mới trong sổ làm việc và dán kết quả phân tích, bắt đầu từ ô A1. Nếu cần, hãy nhập tên cho trang tính mới vào trường nằm đối diện với nút radio tương ứng.
Mới sách bài tậpĐặt nút chuyển sang vị trí này để tạo một sổ làm việc mới trong đó kết quả sẽ được thêm vào một trang tính mới.
Phần dư Chọn hộp kiểm để đưa phần dư vào bảng đầu ra.
Số dư được chuẩn hóa Chọn hộp kiểm để bao gồm số dư được chuẩn hóa trong bảng đầu ra.
Biểu đồ phần dư Chọn hộp kiểm để vẽ biểu đồ phần dư cho từng biến độc lập.
Fit Plot Chọn hộp kiểm để vẽ các giá trị dự đoán so với giá trị quan sát được.
Biểu đồ xác suất chuẩn Chọn hộp kiểm để vẽ đồ thị xác suất chuẩn.
Chức năng DÒNG
Để thực hiện các phép tính, hãy dùng con trỏ chọn ô mà chúng ta muốn hiển thị giá trị trung bình và nhấn phím = trên bàn phím. Tiếp theo, trong trường Tên, cho biết chức năng mong muốn, Ví dụ TRUNG BÌNH(Hình 22).
Cơm. 22 Hàm tìm kiếm trong Excel 2003
Nếu trong lĩnh vực này TÊN tên hàm không xuất hiện, sau đó nhấp chuột trái vào hình tam giác bên cạnh trường, sau đó sẽ xuất hiện cửa sổ danh sách hàm. Nếu chức năng này không có trong danh sách thì nhấn chuột trái vào mục danh sách CAC CHƯC NĂNG KHAC, một hộp thoại sẽ xuất hiện CHỨC NĂNG CHỦ, trong đó, sử dụng cuộn dọc chọn chức năng mong muốn, đánh dấu nó bằng con trỏ và nhấp vào ĐƯỢC RỒI(Hình 23).
Cơm. 23. Trình hướng dẫn chức năng
Để tìm kiếm một hàm trong Excel 2007, có thể mở bất kỳ tab nào trong menu, sau đó để thực hiện các phép tính, hãy dùng con trỏ chọn ô mà chúng ta muốn hiển thị giá trị trung bình và nhấn phím = trên bàn phím. Tiếp theo, tại trường Tên, chỉ định chức năng TRUNG BÌNH. Cửa sổ tính hàm tương tự như trong Excel 2003.
Bạn cũng có thể chọn tab Công thức và nhấp chuột trái vào nút trong menu “ CHÈN CHỨC NĂNG"(Hình 24), một cửa sổ sẽ xuất hiện CHỨC NĂNG CHỦ, giao diện của nó tương tự như Excel 2003. Ngoài ra, trong menu, bạn có thể chọn ngay một danh mục hàm (được sử dụng gần đây, tài chính, logic, văn bản, ngày giờ, toán học, các hàm khác) mà chúng tôi sẽ tìm kiếm mong muốn chức năng.
Cơm. 24 Chọn hàm trong Excel 2007
Chức năng DÒNG tính toán số liệu thống kê cho một chuỗi sử dụng bình phương tối thiểu để tính một đường thẳng cách tốt nhất xấp xỉ dữ liệu có sẵn và sau đó trả về một mảng mô tả đường thẳng kết quả. Bạn cũng có thể kết hợp chức năng DÒNG với các hàm khác để tính toán các loại mô hình khác tuyến tính với các tham số chưa xác định (có các tham số chưa xác định là tuyến tính), bao gồm chuỗi đa thức, logarit, hàm mũ và lũy thừa. Vì một mảng giá trị được trả về nên hàm phải được chỉ định dưới dạng công thức mảng.
Phương trình của đường thẳng là:
(trong trường hợp có nhiều phạm vi giá trị x),
trong đó giá trị phụ thuộc y là hàm của giá trị độc lập x, giá trị m là các hệ số tương ứng với từng biến độc lập x và b là hằng số. Lưu ý rằng y, x và m có thể là vectơ. Chức năng DÒNG trả về một mảng 
. DÒNG cũng có thể trả lại thêm thống kê hồi quy.
DÒNG(known_values_y; known_values_x; const; thống kê)
Known_y_values là tập hợp các giá trị y đã được biết đến cho mối quan hệ.
Nếu mảng known_y_values có một cột thì mỗi cột trong mảng known_x_values được coi là một biến riêng biệt.
Nếu mảng known_y_values có một hàng thì mỗi hàng trong mảng known_x_values được coi là một biến riêng biệt.
Known_x-values là một tập hợp các giá trị x tùy chọn đã được biết đến cho mối quan hệ.
Mảng known_x_values có thể chứa một hoặc nhiều bộ biến. Nếu chỉ sử dụng một biến thì mảng known_y_values và known_x_values có thể có bất kỳ hình dạng nào - miễn là chúng có cùng kích thước. Nếu sử dụng nhiều hơn một biến thì known_y_values phải là một vectơ (tức là một khoảng cao một hàng hoặc rộng một cột).
Nếu bỏ qua mảng_known_x_values thì mảng (1;2;3;...) được giả định có cùng kích thước với mảng_known_values_y.
Const là một giá trị boolean xác định xem hằng số b có bắt buộc phải bằng 0 hay không.
Nếu đối số "const" là TRUE hoặc bị bỏ qua thì hằng số b sẽ được đánh giá như bình thường.
Nếu đối số “const” là FALSE, thì giá trị của b được đặt thành 0 và các giá trị của m được chọn sao cho mối quan hệ được thỏa mãn.
Thống kê - Giá trị boolean cho biết liệu có nên trả về số liệu thống kê hồi quy bổ sung hay không.
Nếu số liệu thống kê là TRUE, LINEST trả về số liệu thống kê hồi quy bổ sung. Mảng trả về sẽ có dạng như sau: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).
Nếu số liệu thống kê là FALSE hoặc bị bỏ qua, LINEST chỉ trả về hệ số m và hằng số b.
Thống kê hồi quy bổ sung.
Hình dưới đây cho thấy thứ tự trả về số liệu thống kê hồi quy bổ sung.
Ghi chú:
Bất kỳ đường thẳng nào cũng có thể được mô tả bằng độ dốc và giao điểm của nó với trục y:
Độ dốc (m): Để xác định độ dốc của một đường thẳng, thường ký hiệu là m, bạn lấy hai điểm trên đường thẳng đó và ; độ dốc sẽ bằng 
.
Điểm chặn Y (b): Điểm chặn y của một đường thẳng, thường được ký hiệu là b, là giá trị y cho điểm mà tại đó đường thẳng giao với trục y.
Phương trình đường thẳng có dạng . Nếu biết giá trị của m và b thì bất kỳ điểm nào trên đường thẳng đều có thể được tính bằng cách thay các giá trị của y hoặc x vào phương trình. Bạn cũng có thể sử dụng chức năng TREND.
Nếu chỉ có một biến độc lập x, bạn có thể thu được độ dốc và điểm chặn y trực tiếp bằng các công thức sau:
Độ dốc: INDEX(LINEST(known_y_values; known_x_values); 1)
Chặn Y: INDEX(LINEST(known_y_values; known_x_values); 2)
Độ chính xác của phép tính gần đúng bằng đường thẳng được tính bằng hàm LINEST phụ thuộc vào mức độ phân tán dữ liệu. Dữ liệu càng gần đường thẳng thì mô hình được hàm LINEST sử dụng càng chính xác. Hàm LINEST sử dụng bình phương tối thiểu để xác định mức độ phù hợp nhất với dữ liệu. Khi chỉ có một biến độc lập x thì m và b được tính theo công thức sau:
trong đó x và y là phương tiện mẫu, ví dụ x = AVERAGE(known_x's) và y = AVERAGE(known_y's).
Các hàm khớp LINEST và LGRFPFRIBL có thể tính toán đường thẳng hoặc đường cong hàm mũ phù hợp nhất với dữ liệu. Tuy nhiên, họ không trả lời câu hỏi kết quả nào trong hai kết quả đó phù hợp hơn để giải quyết vấn đề. Bạn cũng có thể đánh giá hàm TREND(known_y's; known_x's) cho đường thẳng hoặc hàm GROW(known_y's; known_x's) cho đường cong hàm mũ. Các hàm này, trừ khi các giá trị new_x được chỉ định, sẽ trả về một mảng các giá trị y được tính toán cho các giá trị x thực tế dọc theo một đường hoặc đường cong. Sau đó, bạn có thể so sánh các giá trị được tính toán với các giá trị thực tế. Bạn cũng có thể tạo biểu đồ để so sánh trực quan.
Bằng cách thực hiện phân tích hồi quy, Microsoft Excel tính toán cho mỗi điểm bình phương chênh lệch giữa giá trị y dự đoán và giá trị y thực tế. Tổng của các hiệu bình phương này được gọi là tổng bình phương dư (ssresid). Sau đó, Microsoft Excel sẽ tính tổng bình phương (sstotal). Nếu const = TRUE hoặc giá trị của đối số này không được chỉ định, tổng cộng các bình phương sẽ bằng tổng bình phương của các hiệu giữa giá trị thực của y và giá trị trung bình của y. Khi const = FALSE thì tổng bình phương sẽ bằng tổng bình phương của các giá trị y thực (không trừ giá trị y trung bình từ giá trị y riêng phần). Tổng hồi quy của các bình phương khi đó có thể được tính như sau: ssreg = sstotal - ssresid. Tổng bình phương còn lại càng nhỏ thì nhiều giá trị hơn hệ số xác định r2, cho thấy phương trình thu được bằng phân tích hồi quy giải thích mối quan hệ giữa các biến tốt đến mức nào. Hệ số r2 bằng ssreg/sstotal.
Trong một số trường hợp, một hoặc nhiều cột X (để giá trị Y và X nằm trong cột) không có giá trị dự đoán bổ sung trong các cột X khác. Nói cách khác, việc loại bỏ một hoặc nhiều cột X có thể dẫn đến tính toán giá trị Y. với độ chính xác như nhau. Trong trường hợp này, các cột X dư thừa sẽ bị loại khỏi mô hình hồi quy. Hiện tượng này được gọi là "cộng tuyến" vì các cột dư thừa của X có thể được biểu diễn dưới dạng tổng của một số cột không dư thừa. Hàm LINEST kiểm tra tính cộng tuyến và loại bỏ mọi cột X dư thừa khỏi mô hình hồi quy nếu phát hiện thấy chúng. Các cột X đã xóa có thể được xác định trong đầu ra LINEST theo hệ số 0 và giá trị se là 0. Việc xóa một hoặc nhiều cột dư thừa sẽ thay đổi giá trị của df vì nó phụ thuộc vào số lượng cột X thực sự được sử dụng cho mục đích dự đoán. Để biết thêm thông tin về cách tính df, hãy xem Ví dụ 4 bên dưới. Khi df thay đổi do loại bỏ các cột dư thừa, giá trị của sey và F cũng thay đổi. Không nên sử dụng cộng tuyến thường xuyên. Tuy nhiên, nó nên được sử dụng nếu một số cột X chứa 0 hoặc 1 làm chỉ báo cho biết liệu đối tượng của thử nghiệm có được đưa vào hay không. nhóm riêng biệt. Nếu const = TRUE hoặc giá trị cho đối số này không được chỉ định, LINEST sẽ chèn cột X bổ sung để mô hình hóa điểm giao nhau. Nếu có một cột có giá trị 1 cho nam và 0 cho nữ và có một cột có giá trị 1 cho nữ và 0 cho nam thì cột cuối cùng sẽ bị loại bỏ vì có thể lấy được giá trị của nó từ cột "chỉ số nam".
Việc tính df cho trường hợp X cột không bị loại khỏi mô hình do cộng tuyến xảy ra như sau: nếu có k cột known_x và giá trị const = TRUE hoặc không được chỉ định thì df = n – k – 1. Nếu const = SAI thì df = n - k. Trong cả hai trường hợp, việc loại bỏ các cột X do cộng tuyến sẽ làm tăng giá trị df lên 1.
Các công thức trả về mảng phải được nhập dưới dạng công thức mảng.
Khi nhập một mảng các hằng số làm đối số, ví dụ known_x_values, bạn nên sử dụng dấu chấm phẩy để phân tách các giá trị trên cùng một dòng và dấu hai chấm để phân tách các dòng. Các ký tự phân cách có thể khác nhau tùy thuộc vào cài đặt trong cửa sổ Ngôn ngữ và Cài đặt trong Bảng Điều khiển.
Cần lưu ý rằng các giá trị y được dự đoán bởi phương trình hồi quy có thể không chính xác nếu chúng nằm ngoài phạm vi các giá trị y được sử dụng để xác định phương trình.
Thuật toán cơ bản được sử dụng trong hàm DÒNG, khác với thuật toán hàm chính NGHIÊNG Và ĐOẠN ĐƯỜNG. Sự khác biệt giữa các thuật toán có thể dẫn đến các kết quả khác nhau với dữ liệu không chắc chắn và dữ liệu cộng tuyến. Ví dụ: nếu điểm dữ liệu đối số known_y_values là 0 và điểm dữ liệu đối số known_x_values là 1 thì:
Chức năng DÒNG trả về giá trị bằng 0. Thuật toán hàm DÒNGđược sử dụng để trả về các giá trị phù hợp cho dữ liệu cộng tuyến và trong trường hợp này có thể tìm thấy ít nhất một câu trả lời.
Hàm SLOPE và LINE trả về lỗi #DIV/0! Thuật toán của hàm SLOPE và INTERCEPT được sử dụng để chỉ tìm một câu trả lời, nhưng trong trường hợp này có thể có nhiều câu trả lời.
Ngoài việc tính toán số liệu thống kê cho các loại hồi quy khác, LINEST có thể được sử dụng để tính phạm vi cho các loại hồi quy khác bằng cách nhập hàm của biến x và y dưới dạng chuỗi biến x và y cho LINEST. Ví dụ: công thức sau:
LINEST(y_values, x_values^COLUMN($A:$C))
hoạt động bằng cách có một cột giá trị Y và một cột giá trị X để tính xấp xỉ khối (đa thức bậc 3) có dạng sau:
Công thức có thể được sửa đổi để tính toán các loại hồi quy khác, nhưng trong một số trường hợp, giá trị đầu ra và các số liệu thống kê khác có thể cần phải được điều chỉnh.
