Thực đơn
trang chủInternet

Giải thích về hồi quy trong Excel. Phân tích tương quan trong Excel. Kích hoạt gói phân tích

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. Vấn đề 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 để nghiên cứu với sự quan tâm đặc biệt.

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 để 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 mô hình này có thể được phát hiện, thì bằng cách sử dụng hàm chúng tôi thu được trong quá trình tính toán, chúng tôi 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. Hàm chúng tôi thu được chứa các giá trị thực tế của các nhân viên có liên quan. 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.

Đây là cách phổ biến nhất để thể hiện sự phụ thuộc của một số biến vào các biến khác, ví dụ, làm thế nào mức GDP từ kích thước đầu tư nước ngoài hoặc từ Lãi suất cho vay của Ngân hàng Nhà nước hoặc từ giá các nguồn năng lượng quan trọng.

Việc lập mô hình cho phép bạn hiển thị mức độ phụ thuộc (các hệ số) này, nhờ đó bạn có thể đưa ra dự báo trực tiếp và thực hiện một số loại kế hoạch dựa trên những dự báo này. Ngoài ra, dựa trên phân tích hồi quy, có thể đưa ra các quyết định quản lý nhằm kích thích các nguyên nhân ưu tiên ảnh hưởng đến kết quả cuối cùng, bản thân mô hình sẽ giúp làm nổi bật những yếu tố ưu tiên này.

Tổng quan về mô hình hồi quy tuyến tính:

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

Ở đâu Một - các tham số hồi quy (hệ số), x - những nhân tố ảnh hưởng, k - số lượng các yếu tố mô hình.

Dữ liệu ban đầu

Trong số các dữ liệu ban đầu, chúng ta cần một bộ dữ liệu nhất định biểu thị một số giá trị liên tiếp hoặc liên kết với nhau của tham số Y cuối cùng (ví dụ: GDP) và cùng một số giá trị của các chỉ số có ảnh hưởng mà chúng ta đang nghiên cứu ( ví dụ như đầu tư nước ngoài).

Hình trên cho thấy một bảng có cùng dữ liệu ban đầu, Y là chỉ số về dân số hoạt động kinh tế và số lượng doanh nghiệp, lượng đầu tư vào vốn và thu nhập hộ gia đình là những yếu tố ảnh hưởng, tức là X.

Dựa vào hình vẽ, người ta cũng có thể đưa ra kết luận sai lầm rằng mô hình hóa chỉ có thể là về chuỗi thời gian, tức là chuỗi thời điểm được ghi tuần tự theo thời gian, nhưng trường hợp này không thành công như nhau, người ta có thể mô hình hóa theo cấu trúc; , ví dụ: các giá trị được chỉ ra trong bảng có thể được chia nhỏ không phải theo năm mà theo khu vực.

Để xây dựng đủ mô hình tuyến tínhĐiều mong muốn là dữ liệu nguồn không bị sụt giảm hoặc sụp đổ mạnh; trong những trường hợp như vậy, nên tiến hành làm mịn, nhưng chúng ta sẽ nói về việc làm mịn vào lần sau.

Gói phân tích

Các tham số của mô hình hồi quy tuyến tính cũng có thể được tính toán thủ công bằng Phương pháp bình phương tối thiểu thông thường (OLS), nhưng việc này khá tốn thời gian. Điều này có thể được tính toán nhanh hơn một chút bằng cách sử dụng cùng một phương pháp bằng cách sử dụng các công thức trong Excel, nơi chính chương trình sẽ thực hiện các phép tính nhưng bạn vẫn sẽ phải nhập công thức theo cách thủ công.

Excel đã có add-in Gói phân tích cái nào đẹp công cụ đắc lựcđể giúp đỡ nhà phân tích. Bộ công cụ này, cùng với những thứ khác, có thể tính toán các tham số hồi quy bằng cách sử dụng cùng một phương pháp bình phương tối thiểu, chỉ trong vài cú nhấp chuột. Trên thực tế, cách sử dụng công cụ này sẽ được thảo luận thêm.

Kích hoạt gói phân tích

Theo mặc định, tiện ích bổ sung này bị tắt và bạn sẽ không tìm thấy nó trong menu tab, vì vậy chúng tôi sẽ xem xét từng bước cách kích hoạt tiện ích bổ sung này.

Trong Excel, ở trên cùng bên trái, hãy kích hoạt tab Tài liệu, trong menu mở ra, hãy tìm mục Tùy chọn và bấm vào nó.

Trong cửa sổ mở ra, ở bên trái, hãy tìm mục Tiện ích bổ sung và kích hoạt nó, trong tab này ở phía dưới sẽ có một danh sách điều khiển thả xuống, theo mặc định nó sẽ được viết Tiện ích bổ sung của Excel , sẽ có một nút ở bên phải danh sách thả xuống Đi, bạn cần phải bấm vào nó.

Một cửa sổ bật lên sẽ nhắc bạn chọn các tiện ích bổ sung có sẵn trong đó; Gói phân tích và đồng thời, để đề phòng, Tìm giải pháp(cũng là một điều hữu ích), sau đó xác nhận lựa chọn của bạn bằng cách nhấp vào nút ĐƯỢC RỒI.

Hướng dẫn tìm tham số hồi quy tuyến tính bằng Analysis Package

Sau khi kích hoạt tiện ích bổ sung Gói phân tích, nó sẽ luôn có sẵn trong tab menu chính Dữ liệu dưới liên kết Phân tích dữ liệu

Trong cửa sổ công cụ đang hoạt động Phân tích dữ liệu từ danh sách các khả năng chúng tôi tìm kiếm và chọn hồi quy

Tiếp theo, sẽ mở ra cửa sổ thiết lập và lựa chọn nguồn dữ liệu để tính toán các tham số của mô hình hồi quy. Ở đây, bạn cần chỉ ra các khoảng của dữ liệu ban đầu, cụ thể là tham số được mô tả (Y) và các yếu tố ảnh hưởng đến nó (X), như minh họa trong hình bên dưới, về nguyên tắc, các tham số còn lại là tùy chọn để định cấu hình.

Sau khi bạn đã chọn dữ liệu nguồn và nhấp vào nút OK, Excel sẽ tạo các phép tính trên một trang tính mới của sổ làm việc đang hoạt động (trừ khi nó được đặt khác trong cài đặt), các phép tính này trông giống như sau:

Các ô chính đã được lấp đầy màu vàngĐây là những thông số bạn cần chú ý trước hết, các thông số khác cũng quan trọng nhưng chúng phân tích chi tiết Có lẽ yêu cầu một bài viết riêng biệt.

Vì thế, 0,865 - Cái này R 2- hệ số xác định, cho thấy 86,5% các tham số tính toán của mô hình, tức là chính mô hình, giải thích sự phụ thuộc và sự thay đổi của tham số đang nghiên cứu - Y từ các yếu tố nghiên cứu - của X. Nếu phóng đại thì đây là một chỉ số về chất lượng của mô hình và nó càng cao thì càng tốt. Rõ ràng là nó không thể lớn hơn 1 và được coi là tốt khi R 2 trên 0,8 và nếu nó nhỏ hơn 0,5 thì có thể nghi ngờ tính hợp lý của mô hình đó một cách an toàn.

Bây giờ chúng ta hãy chuyển sang hệ số mô hình:
2079,85 - Cái này số 0- hệ số biểu thị Y sẽ bằng bao nhiêu nếu tất cả các yếu tố sử dụng trong mô hình đều bằng 0, được hiểu đây là sự phụ thuộc vào các yếu tố khác không được mô tả trong mô hình;
-0,0056 - một 1- hệ số biểu thị mức độ ảnh hưởng của yếu tố x 1 đến Y, tức là số lượng doanh nghiệp trong một mô hình nhất định ảnh hưởng đến chỉ tiêu dân số hoạt động kinh tế với trọng số chỉ -0,0056 (mức độ ảnh hưởng khá nhỏ) ). Dấu trừ cho thấy ảnh hưởng này là tiêu cực, tức là càng có nhiều doanh nghiệp thì dân số càng ít hoạt động kinh tế, cho dù điều này có ý nghĩa nghịch lý đến mức nào;
-0,0026 - một 2- hệ số ảnh hưởng của khối lượng đầu tư vốn đến quy mô dân số hoạt động kinh tế, theo mô hình, ảnh hưởng này cũng âm;
0,0028 - số 3- hệ số ảnh hưởng của thu nhập dân số đến quy mô dân số hoạt động kinh tế, ở đây ảnh hưởng là dương, tức là theo mô hình, thu nhập tăng sẽ góp phần làm tăng quy mô dân số hoạt động kinh tế.

Hãy thu thập các hệ số tính toán vào mô hình:

Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3

Thực ra đây là tuyến tính mô hình hồi quy, đối với dữ liệu nguồn được sử dụng trong ví dụ này trông giống hệt như thế này.

Ước tính và dự báo mô hình

Như chúng ta đã thảo luận ở trên, mô hình được xây dựng không chỉ để chỉ ra mức độ phụ thuộc của tham số đang được nghiên cứu vào các yếu tố ảnh hưởng mà còn để khi biết các yếu tố ảnh hưởng này, có thể đưa ra dự đoán. Việc đưa ra dự báo này khá đơn giản; bạn chỉ cần thay thế các giá trị của các yếu tố ảnh hưởng vào vị trí của X tương ứng trong phương trình mô hình thu được. Trong hình bên dưới, các phép tính này được thực hiện trong Excel ở một cột riêng.

Giá trị thực tế (những giá trị diễn ra trong thực tế) và giá trị tính toán theo mô hình trong cùng một hình được hiển thị dưới dạng biểu đồ để thể hiện sự khác biệt và do đó sai số của mô hình.

Tôi nhắc lại một lần nữa, để đưa ra dự báo bằng mô hình, điều cần thiết là phải có các yếu tố ảnh hưởng đã biết và nếu Chúng ta đang nói về về một chuỗi thời gian và theo đó, dự báo cho tương lai, chẳng hạn như năm hoặc tháng tiếp theo, không phải lúc nào cũng có thể tìm ra những yếu tố ảnh hưởng sẽ như thế nào trong chính tương lai này. Trong những trường hợp như vậy, cũng cần phải đưa ra dự báo về các yếu tố ảnh hưởng; việc này thường được thực hiện bằng cách sử dụng mô hình tự hồi quy - một mô hình trong đó các yếu tố ảnh hưởng là đối tượng được nghiên cứu và thời gian, tức là sự phụ thuộc của chỉ báo. dựa trên những gì nó đã có trong quá khứ được mô hình hóa.

Chúng ta sẽ xem xét cách xây dựng mô hình tự hồi quy trong bài viết tiếp theo, nhưng bây giờ hãy giả sử rằng chúng ta biết giá trị của các yếu tố ảnh hưởng sẽ như thế nào trong giai đoạn tương lai (trong ví dụ là năm 2008) và bằng cách thay thế các giá trị này ​​vào các tính toán, chúng tôi sẽ nhận được dự báo cho năm 2008.

28 Tháng 10

Chào buổi chiều, độc giả blog thân mến! Hôm nay chúng ta sẽ nói về hồi quy phi tuyến tính. Giải pháp hồi quy tuyến tính có thể được xem qua LINK.

Phương pháp nàyđược sử dụng chủ yếu trong mô hình hóa và dự báo kinh tế. Mục tiêu của nó là quan sát và xác định sự phụ thuộc giữa hai chỉ số.

Các loại hồi quy phi tuyến chính là:

  • đa thức (bậc hai, bậc ba);
  • hyperbol;
  • nghiêm trang;
  • Biểu tình;
  • logarit

Cũng có thể được sử dụng kết hợp khác nhau. Ví dụ: đối với phân tích chuỗi thời gian trong khu vực ngân hàng Các nghiên cứu về nhân khẩu học và bảo hiểm sử dụng đường cong Gompzer, một loại hồi quy logarit.

Khi dự báo bằng phương pháp hồi quy phi tuyến, điều chính là tìm ra hệ số tương quan, hệ số này sẽ cho chúng ta biết liệu có mối quan hệ chặt chẽ giữa hai tham số hay không. Theo quy luật, nếu hệ số tương quan gần bằng 1 thì có sự liên quan và dự báo sẽ khá chính xác. Một yếu tố quan trọng khác của hồi quy phi tuyến là sai số tương đối trung bình ( MỘT ), nếu nó nằm trong khoảng<8…10%, значит модель достаточно точна.

Đây là nơi chúng ta có thể sẽ hoàn thành khối lý thuyết và chuyển sang tính toán thực tế.

Ta có bảng doanh số bán ô tô trong khoảng thời gian 15 năm (ký hiệu là X), số bước đo sẽ là đối số n, ta cũng có doanh thu trong các khoảng thời gian này (ký hiệu là Y), ta cần dự đoán xem doanh thu sẽ có trong tương lai. Hãy xây dựng bảng sau:

Đối với nghiên cứu này, chúng ta sẽ cần giải phương trình (sự phụ thuộc của Y vào X): y=ax 2 +bx+c+e. Đây là một hồi quy bậc hai theo cặp. Trong trường hợp này, chúng ta áp dụng phương pháp bình phương tối thiểu để tìm ra các đối số chưa biết - a, b, c. Nó sẽ dẫn đến một hệ phương trình đại số có dạng:

Để giải hệ này, chẳng hạn, chúng ta sẽ sử dụng phương pháp Cramer. Chúng ta thấy rằng các tổng có trong hệ thống là hệ số của các ẩn số. Để tính toán chúng, chúng ta sẽ thêm một số cột vào bảng (D,E,F,G,H) và ký hiệu theo ý nghĩa của phép tính - trong cột D chúng ta sẽ bình phương x, trong E chúng ta sẽ lập phương nó, trong F ta sẽ nhân số mũ x và y, trong H ta bình phương x và nhân với y.

Bạn sẽ nhận được một bảng chứa đầy những thông tin cần thiết để giải phương trình.

Hãy tạo thành một ma trận MỘT hệ bao gồm các hệ số ẩn ở vế trái của phương trình. Hãy đặt nó vào ô A22 và gọi nó là " A=". Chúng tôi tuân theo hệ phương trình mà chúng tôi đã chọn để giải hồi quy.

Nghĩa là, trong ô B21, chúng ta phải đặt tổng của cột nơi chúng ta đã nâng chỉ báo X lên lũy thừa thứ tư - F17. Chúng ta hãy tham khảo ô - “=F17”. Tiếp theo, chúng ta cần tổng của cột nơi X được lập phương - E17, sau đó chúng ta thực hiện đúng theo hệ thống. Vì vậy, chúng ta sẽ cần điền vào toàn bộ ma trận.

Theo thuật toán của Cramer, chúng ta sẽ gõ ma trận A1, tương tự như A, trong đó thay vì các phần tử của cột đầu tiên, nên đặt các phần tử thuộc vế phải của hệ phương trình. Tức là tổng bình phương của cột X nhân với Y, tổng của cột XY và tổng của cột Y.

Chúng ta cũng sẽ cần thêm hai ma trận nữa - hãy gọi chúng là A2 và A3 trong đó cột thứ hai và thứ ba sẽ bao gồm các hệ số ở vế phải của phương trình. Hình ảnh sẽ như thế này.

Theo thuật toán đã chọn, chúng ta sẽ cần tính giá trị của các định thức (định thức, D) của ma trận thu được. Hãy sử dụng công thức MOPRED. Chúng tôi sẽ đặt kết quả vào các ô J21:K24.

Ta sẽ tính các hệ số của phương trình theo Cramer trong các ô đối diện với định thức tương ứng bằng công thức: Một(trong ô M22) - “=K22/K21”; b(trong ô M23) - “=K23/K21”; Với(trong ô M24) - “=K24/K21”.

Chúng ta nhận được phương trình hồi quy bậc hai theo cặp mong muốn:

y=-0,074x 2 +2,151x+6,523

Chúng ta hãy đánh giá mức độ gần gũi của mối quan hệ tuyến tính bằng cách sử dụng chỉ số tương quan.

Để tính toán, hãy thêm một cột J bổ sung vào bảng (hãy gọi nó là y*). Việc tính toán sẽ như sau (theo phương trình hồi quy mà chúng tôi thu được) - “=$m$22*B2*B2+$M$23*B2+$M$24.” Hãy đặt nó vào ô J2. Tất cả những gì còn lại là kéo điểm đánh dấu tự động điền xuống ô J16.

Để tính tổng (trung bình Y-Y) 2, hãy thêm cột K và L vào bảng với các công thức tương ứng. Chúng tôi tính giá trị trung bình cho cột Y bằng hàm AVERAGE.

Trong ô K25, chúng ta sẽ đặt công thức tính chỉ số tương quan - “=ROOT(1-(K17/L17))”.

Chúng ta thấy rằng giá trị 0,959 rất gần với 1, có nghĩa là có mối quan hệ phi tuyến tính chặt chẽ giữa doanh số và số năm.

Vẫn còn phải đánh giá chất lượng phù hợp của phương trình hồi quy bậc hai thu được (chỉ số xác định). Nó được tính bằng công thức tính chỉ số tương quan bình phương. Nghĩa là, công thức trong ô K26 sẽ rất đơn giản - “=K25*K25”.

Hệ số 0,920 gần bằng 1, cho thấy mức độ phù hợp cao.

Bước cuối cùng là tính toán sai số tương đối. Hãy thêm một cột và nhập công thức vào đó: “=ABS((C2-J2)/C2), ABS - module, giá trị tuyệt đối. Vẽ điểm đánh dấu xuống và trong ô M18 hiển thị giá trị trung bình (AVERAGE), gán định dạng phần trăm cho các ô. Kết quả thu được - 7,79% nằm trong giá trị lỗi chấp nhận được<8…10%. Значит вычисления достаточно точны.

Nếu có nhu cầu, chúng ta có thể xây dựng biểu đồ bằng cách sử dụng các giá trị thu được.

Có file ví dụ đính kèm - LINK!

Thể loại:// từ 28/10/2017

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.

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.


Bây giờ khi chúng ta chuyển đến tab "Dữ liệu", trên dải băng trong hộp 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ụ, dưới đây 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 lượng khách đến tham dự 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à một biến, ảnh hưởng của các yếu tố mà chúng ta đang cố gắng nghiên cứu. Trong trường hợp của chúng tôi, đây là số lượng người mua. Nghĩa x là những yếu tố khác nhau ảnh hưởng đến một biến. Tùy chọn Một 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ể. Mục lục 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à Quảng trườ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 ô tại giao điểm của đường "Giao lộ chữ Y" và cột "Tỷ lệ cược". Đ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 đồ thị "Biến X1" Và "Tỷ lệ cược" cho thấy 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ó.

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 có dạng sau:

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.

  1. Trong danh mục “Thống kê”, chọn chức năng CORREL.
  2. Đối số “Mảng 1” - phạm vi giá trị đầu tiên – thời gian vận hành máy: A2:A14.
  3. Đố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ị.