Cách rút ra kết luận trong kinh tế lượng excel. Hồi quy trong Excel
Phân tích hồi quy trong Microsoft Excel- hầu hết hướng dẫn đầy đủ về việc sử dụng MS Excel để giải các bài toán phân tích hồi quy trong lĩnh vực phân tích kinh doanh. Konrad Carlberg giải thích rõ ràng các vấn đề lý thuyết, kiến thức về lý thuyết này sẽ giúp bạn tránh được nhiều sai lầm khi tự mình tiến hành phân tích hồi quy và khi đánh giá kết quả phân tích do người khác thực hiện. Tất cả tài liệu, từ các mối tương quan đơn giản và kiểm định t đến phân tích hiệp phương sai, đều dựa trên ví dụ thực tế và được đi kèm miêu tả cụ thể trình tự các bước tương ứng.
Cuốn sách thảo luận về những điểm bất thường và tranh cãi liên quan đến các hàm hồi quy của Excel, xem xét ý nghĩa của từng tùy chọn và đối số, đồng thời giải thích cách sử dụng các phương pháp hồi quy một cách đáng tin cậy nhất. Những khu vực khác nhau, từ nghiên cứu y học đến phân tích tài chính.
Konrad Carlberg. Phân tích hồi quy trong Microsoft Excel. – M.: Phép biện chứng, 2017. – 400 tr.
Tải xuống ghi chú ở định dạng hoặc, ví dụ ở định dạng
Chương 1: Đánh giá sự biến đổi của dữ liệu
Các nhà thống kê có nhiều thước đo về sự biến thiên theo ý của họ. Một trong số đó là tổng độ lệch bình phương của các giá trị riêng lẻ so với mức trung bình. Trong Excel, hàm SQUARE() được sử dụng cho việc này. Nhưng phương sai được sử dụng thường xuyên hơn. Độ phân tán là giá trị trung bình của độ lệch bình phương. Phương sai không nhạy cảm với số lượng giá trị trong tập dữ liệu đang nghiên cứu (trong khi tổng độ lệch bình phương tăng theo số lần đo).
Excel cung cấp hai hàm trả về phương sai: DISP.G() và DISP.V():
- Sử dụng hàm DISP.G() nếu các giá trị được xử lý tạo thành một tập hợp. Nghĩa là, các giá trị có trong phạm vi là những giá trị duy nhất mà bạn quan tâm.
- Sử dụng hàm DISP.B() nếu các giá trị cần xử lý tạo thành một mẫu từ tổng thể lớn hơn. Người ta cho rằng có ý nghĩa bổ sung, phương sai mà bạn cũng có thể ước tính.
Nếu một đại lượng như giá trị trung bình hoặc hệ số tương quan được tính từ tổng thể thì nó được gọi là tham số. Một đại lượng tương tự được tính toán trên cơ sở mẫu được gọi là số liệu thống kê. Đếm độ lệch từ mức trung bình V. bộ này, bạn sẽ nhận được tổng bình phương độ lệch nhỏ hơn so với khi bạn đếm chúng từ bất kỳ giá trị nào khác. Một tuyên bố tương tự là đúng cho phương sai.
Cỡ mẫu càng lớn thì giá trị thống kê tính toán càng chính xác. Nhưng không có cỡ mẫu nào nhỏ hơn cỡ tổng thể mà bạn có thể tin tưởng rằng giá trị thống kê khớp với giá trị tham số.
Giả sử bạn có một tập hợp gồm 100 độ cao có giá trị trung bình khác với giá trị trung bình của tổng thể, cho dù sự khác biệt có nhỏ đến đâu. Bằng cách tính phương sai cho một mẫu, bạn sẽ nhận được một giá trị, giả sử là 4. Giá trị này nhỏ hơn bất kỳ giá trị nào khác có thể thu được bằng cách tính độ lệch của từng giá trị trong số 100 giá trị chiều cao so với bất kỳ giá trị nào khác ngoài giá trị trung bình của mẫu , bao gồm cả tương đối với mức trung bình thực sự của dân số nói chung. Do đó, phương sai được tính toán sẽ khác và nhỏ hơn so với phương sai mà bạn sẽ nhận được nếu bằng cách nào đó bạn tìm ra và sử dụng tham số tổng thể thay vì giá trị trung bình mẫu.
Tổng bình phương trung bình được xác định cho mẫu cung cấp ước tính thấp hơn về phương sai tổng thể. Phương sai được tính theo cách này được gọi là di dờiđánh giá. Hóa ra là để loại bỏ độ lệch và có được ước tính không thiên vị, việc chia tổng bình phương độ lệch không cho N, Ở đâu N- cỡ mẫu, và n – 1.
Kích cỡ n – 1được gọi là số (số) bậc tự do. Hiện hữu những cách khác tính toán đại lượng này, mặc dù tất cả chúng đều liên quan đến việc trừ một số số khỏi cỡ mẫu hoặc đếm số loại mà các quan sát rơi vào.
Bản chất của sự khác biệt giữa hàm DISP.G() và DISP.V() như sau:
- Trong hàm VAR.G(), tổng bình phương được chia cho số lượng quan sát và do đó thể hiện ước tính sai lệch của phương sai, giá trị trung bình thực.
- Trong hàm DISP.B(), tổng bình phương được chia cho số lượng quan sát trừ đi 1, tức là. bằng số bậc tự do, điều này đưa ra ước tính chính xác hơn, không thiên vị hơn về phương sai của tổng thể mà mẫu được rút ra.
Độ lệch chuẩn độ lệch chuẩn, SD) – là căn bậc hai của phương sai:
Bình phương các độ lệch sẽ biến thang đo thành một số liệu khác, là bình phương của thước đo ban đầu: mét - thành mét vuông, đô la - thành đô la vuông, v.v. Độ lệch chuẩn là căn bậc hai của phương sai và do đó đưa chúng ta trở về đơn vị đo ban đầu. Cái nào thuận tiện hơn.
Thông thường cần phải tính độ lệch chuẩn sau khi dữ liệu đã bị xử lý. Và mặc dù trong những trường hợp này, kết quả chắc chắn là độ lệch chuẩn nhưng chúng thường được gọi là lỗi chuẩn. Có một số loại sai số chuẩn, bao gồm sai số chuẩn của phép đo, sai số chuẩn của tỷ lệ và sai số chuẩn của giá trị trung bình.
Giả sử bạn đã thu thập dữ liệu chiều cao của 25 người đàn ông trưởng thành được chọn ngẫu nhiên ở mỗi bang trong số 50 tiểu bang. Tiếp theo, bạn tính chiều cao trung bình của nam giới trưởng thành ở mỗi tiểu bang. Lần lượt, 50 giá trị trung bình thu được có thể được coi là quan sát. Từ đó, bạn có thể tính được độ lệch chuẩn của chúng, đó là sai số chuẩn của giá trị trung bình. Cơm. 1. so sánh sự phân bố của 1.250 giá trị cá nhân thô (dữ liệu chiều cao của 25 nam giới ở mỗi trong số 50 tiểu bang) với sự phân bổ của mức trung bình của 50 tiểu bang. Công thức ước tính sai số chuẩn của giá trị trung bình (nghĩa là độ lệch chuẩn của giá trị trung bình, không phải các quan sát riêng lẻ):
sai số chuẩn của giá trị trung bình ở đâu; S- độ lệch chuẩn của các quan trắc ban đầu; N– số lượng quan sát trong mẫu.
Cơm. 1. Sự khác biệt về giá trị trung bình giữa các tiểu bang nhỏ hơn đáng kể so với sự khác biệt trong các quan sát riêng lẻ.
Trong thống kê có sự nhất trí về việc sử dụng tiếng Hy Lạp và chữ cái Latinhđể biểu thị các đại lượng thống kê. Thông thường, người ta thường biểu thị các tham số của tổng thể bằng các chữ cái Hy Lạp và số liệu thống kê mẫu bằng các chữ cái Latinh. Vì vậy, nếu Chúng ta đang nói về về độ lệch chuẩn của dân số, ta viết là σ; nếu xem xét độ lệch chuẩn của mẫu thì chúng ta sử dụng ký hiệu s. Đối với các ký hiệu để chỉ mức trung bình, chúng không ăn khớp với nhau lắm. Giá trị trung bình của dân số được ký hiệu bằng chữ cái Hy Lạp μ. Tuy nhiên, ký hiệu X̅ thường được sử dụng để biểu thị giá trị trung bình mẫu.
điểm z biểu thị vị trí của một quan sát trong phân bố theo đơn vị độ lệch chuẩn. Ví dụ: z = 1,5 có nghĩa là quan sát cách giá trị trung bình 1,5 độ lệch chuẩn. Thuật ngữ điểm zđược sử dụng để đánh giá cá nhân, tức là cho các kích thước được gán cho các phần tử mẫu riêng lẻ. Thuật ngữ dùng để chỉ số liệu thống kê đó (chẳng hạn như mức trung bình của tiểu bang) điểm z:
trong đó X̅ là giá trị trung bình mẫu, μ là giá trị trung bình tổng thể, là sai số chuẩn của giá trị trung bình của một tập hợp mẫu:
trong đó σ là sai số chuẩn của tổng thể (các phép đo riêng lẻ), N- cỡ mẫu.
Giả sử bạn làm việc với tư cách là người hướng dẫn tại một câu lạc bộ chơi gôn. Bạn đã có thể đo khoảng cách các cú đánh của mình trong một khoảng thời gian dài và biết rằng trung bình là 205 thước Anh và độ lệch chuẩn là 36 thước Anh. Bạn được cung cấp một cây gậy mới, tuyên bố rằng nó sẽ tăng khoảng cách đánh của bạn thêm 10 thước. Bạn yêu cầu mỗi người trong số 81 khách hàng tiếp theo của câu lạc bộ đánh thử một cây gậy mới và ghi lại khoảng cách vung gậy của họ. Hóa ra khoảng cách trung bình với câu lạc bộ mới là 215 thước Anh. Xác suất để sự khác biệt 10 yard (215 – 205) chỉ do lỗi lấy mẫu là bao nhiêu? Hay nói cách khác: Khả năng là, trong thử nghiệm rộng rãi hơn, gậy mới sẽ không cho thấy sự gia tăng khoảng cách đánh so với mức trung bình dài hạn hiện tại là 205 yard là bao nhiêu?
Chúng ta có thể kiểm tra điều này bằng cách tạo ra điểm z. Lỗi tiêu chuẩn trung bình:
Sau đó, điểm z:
Chúng ta cần tìm xác suất để trung bình mẫu cách trung bình tổng thể 2,5σ. Nếu xác suất nhỏ thì sự khác biệt không phải do ngẫu nhiên mà do chất lượng của câu lạc bộ mới. Excel không có điểm z để xác định xác suất. chức năng đã hoàn thành. Tuy nhiên, bạn có thể sử dụng công thức =1-NORM.ST.DIST(z-score,TRUE), trong đó hàm NORM.ST.DIST() trả về diện tích dưới đường cong pháp tuyến ở bên trái của điểm z (Hình 2).
Cơm. 2. Hàm NORM.ST.DIST() trả về diện tích dưới đường cong bên trái của giá trị z; Để phóng to hình ảnh bấm vào nó click chuột phải chuột và chọn Mở hình ảnh trong trang mới
Đối số thứ hai của hàm NORM.ST.DIST() có thể nhận hai giá trị: TRUE – hàm trả về diện tích của vùng dưới đường cong bên trái điểm được chỉ định bởi đối số thứ nhất; FALSE – hàm trả về chiều cao của đường cong tại điểm được chỉ định bởi đối số đầu tiên.
Nếu không biết trung bình tổng thể (μ) và độ lệch chuẩn (σ), thì giá trị t sẽ được sử dụng (xem chi tiết). Cấu trúc điểm z và điểm t khác nhau ở chỗ độ lệch chuẩn s thu được từ kết quả mẫu được sử dụng để tìm điểm t thay vì giá trị đã biết của tham số tổng thể σ. Đường cong chuẩn có một hình dạng duy nhất và hình dạng của phân bố giá trị t thay đổi tùy thuộc vào số bậc tự do df. bậc tự do) của mẫu mà nó đại diện. Số bậc tự do của mẫu bằng n – 1, Ở đâu N- cỡ mẫu (Hình 3).
Cơm. 3. Hình dạng của phân phối t phát sinh trong trường hợp tham số σ chưa biết khác với hình dạng của phân phối chuẩn
Excel có hai hàm phân phối t, còn được gọi là phân phối Sinh viên: STUDENT.DIST() trả về diện tích dưới đường cong bên trái của một giá trị t nhất định và STUDENT.DIST.PH() trả về diện tích cho Phải.
Chương 2. Mối tương quan
Tương quan là thước đo sự phụ thuộc giữa các phần tử của một tập hợp các cặp có thứ tự. Sự tương quan được đặc trưng Hệ số tương quan Pearson–r. Hệ số có thể lấy các giá trị trong khoảng từ –1.0 đến +1.0.
Ở đâu Sx Và S y– độ lệch chuẩn của các biến X Và Y, S xy– hiệp phương sai:
Trong công thức này, hiệp phương sai được chia cho độ lệch chuẩn của các biến X Và Y, do đó loại bỏ các hiệu ứng chia tỷ lệ liên quan đến đơn vị khỏi hiệp phương sai. Excel sử dụng hàm CORREL(). Tên của hàm này không chứa các phần tử đủ điều kiện Г và В, được sử dụng trong tên của các hàm như STANDARDEV(), VARIANCE() hoặc COVARIANCE(). Mặc dù hệ số tương quan mẫu cung cấp ước tính sai lệch, lý do dẫn đến sai lệch là khác so với trường hợp phương sai hoặc độ lệch chuẩn.
Tùy thuộc vào độ lớn của hệ số tương quan tổng quát (thường được ký hiệu bằng chữ Hy Lạp ρ ), Hệ số tương quan r tạo ra ước tính sai lệch, với hiệu ứng sai lệch tăng lên khi cỡ mẫu giảm. Tuy nhiên, chúng tôi không cố gắng điều chỉnh độ lệch này theo cách tương tự như cách chúng tôi đã làm khi tính độ lệch chuẩn, khi chúng tôi thay thế không phải số lượng quan sát mà là số bậc tự do vào công thức tương ứng. Trong thực tế, số lượng quan sát được sử dụng để tính hiệp phương sai không ảnh hưởng đến độ lớn.
Hệ số tương quan tiêu chuẩn được thiết kế để sử dụng với các biến có liên quan với nhau bằng mối quan hệ tuyến tính. Sự hiện diện của tính phi tuyến và/hoặc sai sót trong dữ liệu (các giá trị ngoại lệ) dẫn đến tính toán sai hệ số tương quan. Để chẩn đoán các vấn đề về dữ liệu, nên tạo các biểu đồ phân tán. Đây là loại biểu đồ duy nhất trong Excel coi cả trục ngang và trục dọc là trục giá trị. Biểu đồ đường xác định một trong các cột là trục danh mục, điều này làm biến dạng hình ảnh dữ liệu (Hình 4).
Cơm. 4. Các đường hồi quy tưởng chừng giống nhau nhưng so sánh phương trình của chúng với nhau
Các quan sát dùng để xây dựng biểu đồ đường được sắp xếp cách đều nhau dọc theo trục hoành. Các nhãn phân chia dọc theo trục này chỉ là nhãn chứ không phải giá trị số.
Mặc dù mối tương quan thường có nghĩa là có mối quan hệ nhân quả nhưng nó không thể được sử dụng để chứng minh trường hợp này. Thống kê không được sử dụng để chứng minh liệu một lý thuyết là đúng hay sai. Để loại trừ những lời giải thích cạnh tranh cho các kết quả quan sát, hãy đặt thí nghiệm theo kế hoạch. Thống kê được sử dụng để tóm tắt thông tin được thu thập trong các thí nghiệm đó và định lượng khả năng quyết định được đưa ra có thể không chính xác dựa trên bằng chứng sẵn có.
Chương 3: Hồi quy đơn giản
Nếu hai biến có liên quan với nhau, do đó giá trị của hệ số tương quan vượt quá 0,5, thì trong trường hợp này có thể dự đoán (với độ chính xác nhất định) giá trị chưa biết của một biến từ giá trị đã biết của biến kia . Để có được giá trị dự báo dựa trên dữ liệu được hiển thị trong Hình. 5, bất kỳ một số có thể được sử dụng những cách có thể, nhưng bạn gần như chắc chắn sẽ không sử dụng cái được hiển thị trong Hình. 5. Tuy nhiên, bạn nên làm quen với nó, vì không có phương pháp nào khác cho phép bạn chứng minh mối liên hệ giữa mối tương quan và dự đoán rõ ràng như phương pháp này. Trong bộ lễ phục. 5 trong phạm vi B2:C12 hiển thị mẫu ngẫu nhiên gồm mười ngôi nhà và cung cấp dữ liệu về diện tích của mỗi ngôi nhà (tính bằng feet vuông) và giá bán của nó.
Cơm. 5. Dự báo giá trị giá bán tạo thành một đường thẳng
Tìm giá trị trung bình, độ lệch chuẩn và hệ số tương quan (phạm vi A14:C18). Tính điểm z diện tích (E2:E12). Ví dụ: ô E3 chứa công thức: =(B3-$B$14)/$B$15. Tính điểm z của giá dự báo (F2:F12). Ví dụ: ô F3 chứa công thức: =ЕЗ*$В$18. Chuyển đổi điểm z thành giá đô la (H2:H12). Trong ô NZ, công thức là: =F3*$C$15+$C$14.
Lưu ý rằng giá trị dự đoán luôn có xu hướng dịch chuyển về phía giá trị trung bình bằng 0. Hệ số tương quan càng gần 0 thì điểm z dự đoán càng gần 0. Trong ví dụ của chúng tôi, hệ số tương quan giữa diện tích và giá bán là 0,67 và giá dự báo là 1,0 * 0,67, tức là. 0,67. Điều này tương ứng với việc vượt quá một giá trị trên giá trị trung bình bằng hai phần ba độ lệch chuẩn. Nếu hệ số tương quan bằng 0,5 thì giá dự báo sẽ là 1,0 * 0,5, tức là. 0,5. Điều này tương ứng với sự vượt quá một giá trị trên giá trị trung bình chỉ bằng một nửa độ lệch chuẩn. Bất cứ khi nào giá trị của hệ số tương quan khác với giá trị lý tưởng, tức là. lớn hơn -1,0 và nhỏ hơn 1,0, điểm của biến dự đoán phải gần với giá trị trung bình của nó hơn điểm của biến dự đoán (độc lập) với chính nó. Hiện tượng này được gọi là hồi quy về giá trị trung bình, hay đơn giản là hồi quy.
Excel có một số hàm để xác định các hệ số của phương trình đường hồi quy (được gọi là đường xu hướng trong Excel) y =kx + b. Để xác định k phục vụ chức năng
=SLOPE(giá trị_y_đã biết, giá trị_x_đã biết)
Đây Tại là biến dự đoán và X- biến độc lập. Bạn phải tuân thủ nghiêm ngặt thứ tự biến đổi này. Độ dốc của đường hồi quy, hệ số tương quan, độ lệch chuẩn của các biến và hiệp phương sai có liên quan chặt chẽ với nhau (Hình 6). Hàm INTERMEPT() trả về giá trị bị chặn bởi đường hồi quy trên trục tung:
=LIMIT(giá_trị_y_đã_biết, giá trị_x_đã_biết)
Cơm. 6. Mối quan hệ giữa độ lệch chuẩn chuyển đổi hiệp phương sai thành hệ số tương quan và độ dốc của đường hồi quy
Lưu ý rằng số lượng giá trị x và y được cung cấp làm đối số cho hàm SLOPE() và INTERCEPT() phải giống nhau.
Phân tích hồi quy sử dụng một cách khác chỉ số quan trọng– R 2 (R bình phương), hay hệ số xác định. Nó xác định sự đóng góp nào vào sự biến thiên tổng thể của dữ liệu được tạo ra bởi mối quan hệ giữa X Và Tại. Trong Excel, có một hàm gọi là CVPIERSON(), hàm này nhận các đối số chính xác giống như hàm CORREL().
Hai biến có hệ số tương quan khác 0 giữa chúng được cho là giải thích phương sai hoặc có phương sai giải thích. Phương sai được giải thích thông thường được biểu thị bằng phần trăm. Vì thế R 2 = 0,81 có nghĩa là 81% phương sai (tán xạ) của hai biến được giải thích. 19% còn lại là do biến động ngẫu nhiên.
Excel có hàm TREND giúp việc tính toán trở nên dễ dàng hơn. Hàm TREND():
- chấp nhận các giá trị đã biết mà bạn cung cấp X và các giá trị đã biết Tại;
- tính toán độ dốc của đường hồi quy và hằng số (chặn);
- trả về giá trị dự đoán Tại, được xác định bằng cách áp dụng phương trình hồi quy cho các giá trị đã biết X(Hình 7).
Hàm TREND() là một hàm mảng (tôi khuyên bạn nên sử dụng nếu bạn chưa từng gặp những hàm như vậy trước đây).
Cơm. 7. Sử dụng hàm TREND() cho phép bạn tăng tốc và đơn giản hóa các phép tính so với việc sử dụng cặp hàm SLOPE() và INTERCEPT()
Để nhập hàm TREND() dưới dạng công thức mảng trong các ô G3:G12, hãy chọn phạm vi G3:G12, nhập công thức TREND (NW:S12;V3:B12), nhấn và giữ các phím
Hàm TREND() có thêm hai đối số: giá trị mới_x Và hằng số. Giá trị đầu tiên cho phép bạn đưa ra dự báo cho tương lai và giá trị thứ hai có thể buộc đường hồi quy đi qua gốc tọa độ (giá trị TRUE yêu cầu Excel sử dụng hằng số được tính toán, giá trị FALSE yêu cầu Excel sử dụng hằng số = 0 ). Excel cho phép bạn vẽ đường hồi quy trên biểu đồ để nó đi qua gốc tọa độ. Bắt đầu bằng cách vẽ biểu đồ phân tán, sau đó nhấp chuột phải vào một trong các điểm đánh dấu chuỗi dữ liệu. Chọn mục trong menu ngữ cảnh mở ra Thêm đường xu hướng; chọn một tùy chọn tuyến tính; nếu cần, hãy cuộn xuống bảng, chọn hộp Thiết lập giao lộ; Đảm bảo hộp văn bản liên quan của nó được đặt thành 0,0.
Nếu bạn có ba biến và bạn muốn xác định mối tương quan giữa hai trong số chúng đồng thời loại bỏ ảnh hưởng của biến thứ ba, bạn có thể sử dụng tương quan một phần. Giả sử bạn quan tâm đến mối quan hệ giữa tỷ lệ phần trăm cư dân thành phố đã hoàn thành đại học và số lượng sách trong thư viện của thành phố. Bạn đã thu thập dữ liệu của 50 thành phố, nhưng... Vấn đề là cả hai thông số này có thể phụ thuộc vào mức độ hạnh phúc của cư dân của một thành phố cụ thể. Tất nhiên, rất khó để tìm thấy 50 thành phố khác có đặc điểm giống hệt nhau về mức độ hạnh phúc của người dân.
Bằng cách sử dụng các phương pháp thống kê để kiểm soát ảnh hưởng của của cải đối với cả hỗ trợ tài chính của thư viện và khả năng chi trả của trường đại học, bạn có thể định lượng chính xác hơn về độ mạnh của mối quan hệ giữa các biến quan tâm, cụ thể là số lượng sách và số lượng sinh viên tốt nghiệp. Mối tương quan có điều kiện như vậy giữa hai biến, khi giá trị của các biến khác cố định, được gọi là tương quan từng phần. Một cách để tính toán nó là sử dụng phương trình:
Ở đâu rC.B. . W- hệ số tương quan giữa các biến Đại học và Sách với ảnh hưởng bị loại trừ ( giá trị cố định) sự giàu có biến đổi; rC.B.- hệ số tương quan giữa biến College và Books; rCW- hệ số tương quan giữa biến Cao đẳng và Phúc lợi; rB.W.- hệ số tương quan giữa biến Sách vở và biến Phúc lợi.
Mặt khác, mối tương quan một phần có thể được tính toán dựa trên phân tích phần dư, tức là sự khác biệt giữa các giá trị dự đoán và kết quả liên quan của các quan sát thực tế (cả hai phương pháp được trình bày trong Hình 8).
Cơm. 8. Tương quan từng phần là tương quan của phần dư
Để đơn giản hóa việc tính toán ma trận hệ số tương quan (B16:E19), hãy sử dụng gói Phân tích Excel(thực đơn Dữ liệu –> Phân tích –> Phân tích dữ liệu). Theo mặc định, gói này không hoạt động trong Excel. Để cài đặt nó, hãy đi qua menu Tài liệu –> Tùy chọn –> Tiện ích bổ sung. Ở dưới cùng của cửa sổ đang mở Tùy chọnExcel tìm trường Điều khiển, lựa chọn Tiện ích bổ sungExcel, nhấp chuột Đi. Chọn hộp bên cạnh bổ trợ Gói phân tích. Bấm vào A phân tích dữ liệu, chọn tùy chọn Tương quan. Chỉ định $B$2:$D$13 làm khoảng thời gian đầu vào, chọn hộp Nhãn ở dòng đầu tiên, chỉ định $B$16:$E$19 làm khoảng thời gian đầu ra.
Một khả năng khác là xác định mối tương quan bán phần. Ví dụ: bạn đang nghiên cứu ảnh hưởng của chiều cao và tuổi tác đến cân nặng. Do đó, bạn có hai biến dự đoán - chiều cao và tuổi, và một biến dự đoán - cân nặng. Bạn muốn loại trừ ảnh hưởng của một biến dự đoán đến một biến dự đoán khác, nhưng không loại trừ ảnh hưởng của biến dự đoán:
trong đó H – Chiều cao, W – Cân nặng, A – Tuổi; chỉ số hệ số tương quan bán một phần sử dụng dấu ngoặc tròn, với sự trợ giúp của nó, nó cho biết ảnh hưởng của biến nào bị loại bỏ và biến cụ thể nào bị loại bỏ. TRONG trong trường hợp này ký hiệu W(H.A) chỉ ra rằng ảnh hưởng của biến Tuổi bị loại bỏ khỏi biến Chiều cao, nhưng không loại bỏ khỏi biến Cân nặng.
Có vẻ như vấn đề đang được thảo luận không có tầm quan trọng đáng kể. Rốt cuộc, điều quan trọng nhất là nó hoạt động chính xác như thế nào phương trình tổng quát hồi quy, trong khi vấn đề về sự đóng góp tương đối của các biến riêng lẻ vào tổng phương sai được giải thích dường như chỉ có tầm quan trọng thứ yếu. Tuy nhiên, đây không phải là trường hợp. Khi bạn bắt đầu tự hỏi liệu một biến có đáng sử dụng trong phương trình hồi quy bội hay không thì vấn đề sẽ trở nên quan trọng. Nó có thể ảnh hưởng đến việc đánh giá tính đúng đắn của việc lựa chọn mô hình để phân tích.
Chương 4. Hàm LINEST()
Hàm LINEST() trả về 10 thống kê hồi quy. Hàm LINEST() là hàm mảng. Để nhập nó, hãy chọn một phạm vi chứa năm hàng và hai cột, nhập công thức và nhấp vào
LINEST(B2:B21,A2:A21,TRUE,TRUE)
Cơm. 9. Hàm LINEST(): a) chọn phạm vi D2:E6, b) nhập công thức như hiển thị trên thanh công thức, c) nhấp vào
Hàm LINEST() trả về:
- hệ số hồi quy (hoặc độ dốc, ô D2);
- đoạn (hoặc hằng số, ô E3);
- sai số chuẩn của hệ số hồi quy và hằng số (phạm vi D3:E3);
- hệ số xác định R 2 cho hồi quy (ô D4);
- sai số chuẩn của ước tính (ô E4);
- Kiểm tra F cho hồi quy đầy đủ (ô D5);
- số bậc tự do của tổng bình phương còn lại (ô E5);
- hồi quy tổng bình phương (ô D6);
- tổng bình phương còn lại (ô E6).
Chúng ta hãy xem từng số liệu thống kê này và cách chúng tương tác.
Lỗi tiêu chuẩn trong trường hợp của chúng tôi, đó là độ lệch chuẩn được tính cho các lỗi lấy mẫu. Nghĩa là, đây là tình huống trong đó dân số nói chung có một số liệu thống kê và mẫu có một số liệu thống kê khác. Chia hệ số hồi quy cho sai số chuẩn sẽ cho bạn giá trị 2,092/0,818 = 2,559. Nói cách khác, hệ số hồi quy 2,092 là hai sai số chuẩn rưỡi so với 0.
Nếu hệ số hồi quy bằng 0 thì dự đoán tốt nhất biến dự đoán là giá trị trung bình của nó. Sai số chuẩn hai rưỡi là khá lớn và bạn có thể giả định một cách an toàn rằng hệ số hồi quy của tổng thể là khác không.
Bạn có thể xác định xác suất đạt được hệ số hồi quy mẫu là 2,092 nếu giá trị thực của nó trong dân số là 0,0 bằng cách sử dụng hàm
STUDENT.DIST.PH (t-tiêu chí = 2,559; số bậc tự do = 18)
Nói chung, số bậc tự do = n – k – 1, trong đó n là số lượng quan sát và k là số biến dự đoán.
Công thức này trả về 0,00987 hoặc được làm tròn thành 1%. Nó cho chúng ta biết những điều sau: nếu hệ số hồi quy của tổng thể là 0% thì xác suất lấy được mẫu gồm 20 người mà giá trị tính toán Hệ số hồi quy là 2,092, khiêm tốn 1%.
Kiểm tra F (ô D5 trong Hình 9) thực hiện các chức năng tương tự liên quan đến hồi quy hoàn toàn như kiểm tra t liên quan đến hệ số hồi quy theo cặp đơn giản. Kiểm định F được sử dụng để kiểm tra xem hệ số xác định R 2 cho một hồi quy có đủ lớn để bác bỏ giả thuyết rằng trong quần thể nó có giá trị 0,0, điều này cho thấy rằng không có phương sai được giải thích bởi biến dự đoán và biến dự đoán. Khi chỉ có một biến dự đoán, kiểm định F chính xác bằng bình phương kiểm định t.
Cho đến nay chúng ta đã xem xét các biến khoảng. Nếu bạn có các biến có thể nhận nhiều giá trị, biểu diễn tên đơn giản, ví dụ: Đàn ông và Phụ nữ hoặc Bò sát, Lưỡng cư và Cá, hãy tưởng tượng họ như mã số. Các biến như vậy được gọi là danh nghĩa.
Thống kê R2định lượng tỷ lệ phương sai được giải thích.
Sai số chuẩn của ước tính. Trong bộ lễ phục. Hình 4.9 trình bày các giá trị dự đoán của biến Weight, thu được trên cơ sở mối quan hệ của nó với biến Height. Phạm vi E2:E21 chứa các giá trị dư cho biến Weight. Chính xác hơn, những phần dư này được gọi là sai số - do đó có thuật ngữ sai số chuẩn của ước lượng.
Cơm. 10. Cả R 2 và sai số chuẩn của ước tính đều thể hiện độ chính xác của dự báo thu được bằng phương pháp hồi quy
Sai số chuẩn của ước tính càng nhỏ thì phương trình hồi quy càng chính xác và bạn càng mong đợi bất kỳ dự đoán nào do phương trình tạo ra phù hợp với quan sát thực tế càng gần hơn. Sai số chuẩn của ước tính cung cấp một cách để định lượng những kỳ vọng này. Cân nặng của 95% người có chiều cao nhất định sẽ nằm trong khoảng:
(chiều cao * 2,092 – 3,591) ± 2,092 * 21,118
Thống kê F là tỷ lệ giữa phương sai giữa các nhóm với phương sai trong nhóm. Tên này được giới thiệu bởi nhà thống kê George Snedesign để vinh danh Ngài, người đã phát triển phân tích phương sai (ANOVA, Phân tích phương sai) vào đầu thế kỷ 20.
Hệ số xác định R 2 biểu thị tỷ lệ của tổng bình phương liên quan đến hồi quy. Giá trị (1 – R 2) biểu thị tỷ lệ của tổng bình phương liên quan đến phần dư - sai số dự báo. Có thể thu được phép thử F bằng cách sử dụng hàm LINEST (ô F5 trong Hình 11), sử dụng tổng bình phương (phạm vi G10:J11), sử dụng tỷ lệ phương sai (phạm vi G14:J15). Các công thức có thể được nghiên cứu trong tệp Excel đính kèm.
Cơm. 11. Tính chỉ tiêu F
Khi sử dụng các biến danh nghĩa, mã hóa giả được sử dụng (Hình 12). Để mã hóa các giá trị, thuận tiện nhất là sử dụng giá trị 0 và 1. Xác suất F được tính bằng hàm:
F.DIST.PH(K2;I2;I3)
Ở đây, hàm F.DIST.PH() trả về xác suất đạt được tiêu chí F tuân theo phân phối F trung tâm (Hình 13) cho hai bộ dữ liệu với số bậc tự do được cho trong các ô I2 và I3, giá trị trùng với giá trị được cho trong ô K2.
Cơm. 12. Phân tích hồi quy sử dụng biến giả
Cơm. 13. Phân bố F trung tâm tại λ = 0
Chương 5. Hồi quy bội
Khi bạn chuyển từ hồi quy cặp đơn giản với một biến dự đoán sang hồi quy bội, bạn thêm một hoặc nhiều biến dự đoán. Lưu trữ giá trị của các biến dự đoán trong các cột liền kề, chẳng hạn như cột A và B trong trường hợp có hai yếu tố dự đoán hoặc A, B và C trong trường hợp có ba yếu tố dự đoán. Trước khi nhập công thức bao gồm hàm LINEST(), hãy chọn năm hàng và số cột tùy theo số biến dự đoán, cộng thêm một cột nữa cho hằng số. Trong trường hợp hồi quy với hai biến dự đoán, có thể sử dụng cấu trúc sau:
DÒNG(A2: A41; B2: C41;;TRUE)
Tương tự trong trường hợp ba biến:
LINEST(A2:A61,B2:D61,;TRUE)
Giả sử bạn muốn nghiên cứu những tác động có thể có của tuổi tác và chế độ ăn uống đối với mức LDL - lipoprotein mật độ thấp, được cho là nguyên nhân hình thành các mảng xơ vữa động mạch, gây ra chứng huyết khối động mạch (Hình 14).
Cơm. 14. Hồi quy bội
R 2 của hồi quy bội (được phản ánh trong ô F13) lớn hơn R 2 của bất kỳ hồi quy đơn giản nào (E4, H4). Hồi quy bội sử dụng nhiều biến dự đoán cùng một lúc. Trong trường hợp này, R2 hầu như luôn tăng.
Đối với bất kỳ đơn giản phương trình đường thẳng Trong hồi quy với một biến dự đoán, sẽ luôn có mối tương quan hoàn hảo giữa các giá trị dự đoán và giá trị của biến dự đoán, vì trong phương trình như vậy, các giá trị dự đoán được nhân với một hằng số và một hằng số khác là thêm vào từng sản phẩm. Hiệu ứng này không tồn tại trong hồi quy bội.
Hiển thị kết quả được hàm LINEST() trả về cho hồi quy bội (Hình 15). Các hệ số hồi quy được đưa ra như một phần của kết quả được trả về bởi hàm LINEST() theo thứ tự ngược lại của các biến(G–H–I tương ứng với C–B–A).
Cơm. 15. Các hệ số và sai số chuẩn của chúng được hiển thị trong thứ tự ngược lại theo dõi họ trên bảng tính
Các nguyên tắc và quy trình được sử dụng trong phân tích hồi quy biến dự đoán đơn có thể dễ dàng được điều chỉnh để tính đến nhiều biến dự đoán. Hóa ra phần lớn sự thích ứng này phụ thuộc vào việc loại bỏ ảnh hưởng của các biến dự đoán lẫn nhau. Cái sau được liên kết với các mối tương quan một phần và bán một phần (Hình 16).
Cơm. 16. Hồi quy bội có thể được biểu thị thông qua hồi quy cặp phần dư (xem công thức trong tệp Excel)
Trong Excel, có các hàm cung cấp thông tin về phân phối t và F. Các hàm có tên bao gồm phần DIST, chẳng hạn như STUDENT.DIST() và F.DIST(), lấy t-test hoặc F-test làm đối số và trả về xác suất quan sát được một giá trị được chỉ định. Các hàm có tên bao gồm phần OBR, chẳng hạn như STUDENT.INV() và F.INR(), lấy giá trị xác suất làm đối số và trả về giá trị tiêu chí tương ứng với xác suất đã chỉ định.
Vì chúng tôi đang tìm kiếm các giá trị tới hạn của phân phối t cắt các cạnh của vùng đuôi của nó, nên chúng tôi chuyển 5% làm đối số cho một trong các hàm STUDENT.INV(), hàm này trả về giá trị tương ứng với xác suất này (Hình 17, 18).
Cơm. 17. Kiểm định t hai đuôi
Cơm. 18. Kiểm tra t một đuôi
Bằng cách thiết lập quy tắc quyết định cho vùng alpha đuôi đơn, bạn sẽ tăng sức mạnh thống kê của thử nghiệm. Nếu khi bắt đầu thử nghiệm, bạn tự tin rằng mình có mọi lý do để kỳ vọng hệ số hồi quy dương (hoặc âm), thì bạn nên thực hiện thử nghiệm một đuôi. Trong trường hợp này, xác suất bạn chấp nhận giải pháp đúng, bác bỏ giả thuyết về hệ số hồi quy bằng 0 trong tổng thể, sẽ cao hơn.
Các nhà thống kê thích sử dụng thuật ngữ này kiểm tra theo hướng dẫn thay vì thuật ngữ thử nghiệm đuôi đơn và thời hạn kiểm tra vô hướng thay vì thuật ngữ thử nghiệm hai đuôi. Các thuật ngữ có hướng và không có hướng được ưa chuộng hơn vì chúng nhấn mạnh đến loại giả thuyết hơn là bản chất của các đuôi của phân phối.
Một cách tiếp cận để đánh giá tác động của các yếu tố dự đoán dựa trên so sánh mô hình. Trong bộ lễ phục. Hình 19 trình bày kết quả phân tích hồi quy nhằm kiểm tra sự đóng góp của biến Diet vào phương trình hồi quy.
Cơm. 19. So sánh hai mô hình bằng cách kiểm tra sự khác biệt trong kết quả của chúng
Kết quả của hàm LINEST() (phạm vi H2:K6) có liên quan đến cái mà tôi gọi là mô hình đầy đủ, hồi quy biến LDL trên các biến Chế độ ăn uống, Tuổi tác và HDL. Phạm vi H9:J13 trình bày các phép tính mà không tính đến biến dự đoán Diet. Tôi gọi đây là mô hình giới hạn. Trong mô hình đầy đủ, 49,2% phương sai của biến phụ thuộc LDL được giải thích bằng các biến dự đoán. Trong mô hình hạn chế, chỉ có 30,8% LDL được giải thích bằng các biến Tuổi và HDL. Tổn thất trong R 2 do loại biến Diet khỏi mô hình là 0,183. Trong phạm vi G15:L17, các phép tính được thực hiện cho thấy rằng chỉ có xác suất 0,0288 rằng tác động của biến Chế độ ăn kiêng là ngẫu nhiên. Trong 97,1% còn lại, Chế độ ăn có ảnh hưởng đến LDL.
Chương 6: Các giả định và lưu ý khi phân tích hồi quy
Thuật ngữ "giả định" không được định nghĩa đủ chặt chẽ và cách nó được sử dụng cho thấy rằng nếu giả định không được đáp ứng thì ít nhất kết quả của toàn bộ phân tích cũng có vấn đề hoặc có thể không hợp lệ. Thực tế không phải vậy, mặc dù chắc chắn có những trường hợp vi phạm một giả định về cơ bản sẽ làm thay đổi bức tranh. Các giả định cơ bản: a) phần dư của biến Y được phân phối chuẩn tại bất kỳ điểm X nào dọc theo đường hồi quy; b) Giá trị Y nằm trong sự phụ thuộc tuyến tính từ các giá trị X; c) độ phân tán của phần dư gần như giống nhau tại mỗi điểm X; d) không có sự phụ thuộc giữa các dư lượng.
Nếu các giả định không đóng vai trò quan trọng thì các nhà thống kê cho rằng phân tích này có khả năng vi phạm giả định đó. Cụ thể, khi bạn sử dụng hồi quy để kiểm tra sự khác biệt giữa các giá trị trung bình của nhóm, giả định rằng các giá trị Y - và do đó phần dư - được phân phối bình thường không đóng một vai trò quan trọng: các thử nghiệm chắc chắn sẽ vi phạm giả định về tính quy tắc. Điều quan trọng là phân tích dữ liệu bằng biểu đồ. Ví dụ: được bao gồm trong tiện ích bổ sung Phân tích dữ liệu dụng cụ hồi quy.
Nếu dữ liệu không đáp ứng các giả định của hồi quy tuyến tính, bạn có thể sử dụng các cách tiếp cận khác ngoài hồi quy tuyến tính. Một trong số đó là hồi quy logistic (Hình 20). Gần giới hạn trên và giới hạn dưới của biến dự đoán, hồi quy tuyến tính tạo ra những dự đoán không thực tế.
Cơm. 20. Hồi quy logistic
Trong bộ lễ phục. Hình 6.8 hiển thị kết quả của hai phương pháp phân tích dữ liệu nhằm kiểm tra mối quan hệ giữa thu nhập hàng năm và khả năng mua nhà. Rõ ràng, khả năng mua hàng sẽ tăng lên khi thu nhập ngày càng tăng. Biểu đồ giúp bạn dễ dàng nhận ra sự khác biệt giữa kết quả mà hồi quy tuyến tính dự đoán khả năng mua nhà và kết quả bạn có thể nhận được khi sử dụng một cách tiếp cận khác.
Theo cách nói của các nhà thống kê, việc bác bỏ giả thuyết không trong khi thực tế nó đúng được gọi là lỗi Loại I.
Trong tiện ích bổ sung Phân tích dữ liệu cung cấp một công cụ thuận tiện để tạo ra Số ngẫu nhiên, cho phép người dùng chỉ định hình dạng mong muốn của phân phối (ví dụ: Bình thường, Nhị thức hoặc Poisson), cũng như giá trị trung bình và độ lệch chuẩn.
Sự khác biệt giữa các hàm của họ STUDENT.DIST(). Bắt đầu bằng Các phiên bản Excel 2010 ba có sẵn hình dạng khác nhau một hàm trả về tỷ lệ phân phối ở bên trái và/hoặc bên phải của một giá trị t-test nhất định. Hàm STUDENT.DIST() trả về phần diện tích bên dưới đường cong phân phối ở bên trái giá trị t-test mà bạn chỉ định. Giả sử bạn có 36 quan sát, vậy số bậc tự do cho phân tích là 34 và giá trị t-test = 1,69. Trong trường hợp này công thức
SINH VIÊN.DIST(+1.69,34,TRUE)
trả về giá trị 0,05 hoặc 5% (Hình 21). Đối số thứ ba của hàm STUDENT.DIST() có thể là TRUE hoặc FALSE. Nếu được đặt thành TRUE, hàm sẽ trả về diện tích tích lũy dưới đường cong bên trái của cho bài kiểm tra t, được biểu thị dưới dạng phân số. Nếu là FALSE, hàm trả về chiều cao tương đối của đường cong tại điểm tương ứng với phép thử t. Các phiên bản khác của hàm STUDENT.DIST() - STUDENT.DIST.PH() và STUDENT.DIST.2X() - chỉ lấy giá trị t-test và số bậc tự do làm đối số và không yêu cầu chỉ định giá trị thứ ba lý lẽ.
Cơm. 21. Vùng tô bóng đậm hơn ở phần đuôi bên trái của phân bố tương ứng với tỷ lệ diện tích dưới đường cong bên trái của giá trị t-test dương lớn
Để xác định diện tích bên phải của phép thử t, hãy sử dụng một trong các công thức:
1 — STIODENT.DIST (1, 69;34;TRUE)
SINH VIÊN.DIST.PH(1.69;34)
Toàn bộ diện tích dưới đường cong phải là 100%, do đó, trừ đi 1 phần diện tích bên trái của giá trị t-test mà hàm trả về sẽ là phần diện tích bên phải của giá trị t-test. Bạn có thể thấy tốt hơn là lấy trực tiếp phần diện tích mà bạn quan tâm bằng cách sử dụng hàm STUDENT.DIST.PH(), trong đó PH có nghĩa là phần đuôi bên phải của phân bố (Hình 22).
Cơm. 22. Vùng alpha 5% cho thử nghiệm định hướng
Việc sử dụng các hàm STUDENT.DIST() hoặc STUDENT.DIST.PH() ngụ ý rằng bạn đã chọn một giả thuyết làm việc có hướng. Giả thuyết làm việc định hướng kết hợp với việc đặt giá trị alpha thành 5% có nghĩa là bạn đặt tất cả 5% vào đuôi bên phải của phân bố. Bạn sẽ chỉ phải bác bỏ giả thuyết không nếu xác suất của giá trị kiểm định t mà bạn đạt được là 5% hoặc ít hơn. Các giả thuyết mang tính định hướng thường dẫn đến các kiểm định thống kê có độ nhạy cao hơn (độ nhạy cao hơn này còn được gọi là sức mạnh thống kê lớn hơn).
Trong thử nghiệm vô hướng, giá trị alpha vẫn ở mức 5% như cũ nhưng mức phân phối sẽ khác. Vì bạn phải cho phép xảy ra hai kết quả nên xác suất xảy ra kết quả dương tính giả phải được phân bổ giữa hai đầu của phân phối. Người ta thường chấp nhận phân phối xác suất này một cách đồng đều (Hình 23).
Sử dụng cùng giá trị t-test thu được và cùng số bậc tự do như trong ví dụ trước, hãy sử dụng công thức
SINH VIÊN.DIST.2Х(1.69;34)
Không vì lý do cụ thể nào, hàm STUDENT.DIST.2X() trả về mã lỗi #NUM! nếu nó được cung cấp một giá trị t-test âm làm đối số đầu tiên.
Nếu các mẫu chứa lượng dữ liệu khác nhau, hãy sử dụng phép thử t hai mẫu với các phương sai khác nhau có trong gói Phân tích dữ liệu.
Chương 7: Sử dụng hồi quy để kiểm tra sự khác biệt giữa các phương tiện nhóm
Các biến trước đây xuất hiện dưới tên biến dự đoán sẽ được gọi là biến kết quả trong chương này và thuật ngữ biến nhân tố sẽ được sử dụng thay cho thuật ngữ biến dự đoán.
Cách tiếp cận đơn giản nhất để mã hóa một biến danh nghĩa là mã hóa giả(Hình 24).
Cơm. 24. Phân tích hồi quy dựa trên mã hóa giả
Khi sử dụng bất kỳ loại mã hóa giả nào, cần tuân thủ các quy tắc sau:
- Số cột dành riêng cho dữ liệu mới phải bằng số cấp hệ số trừ đi
- Mỗi vectơ đại diện cho một cấp độ yếu tố.
- Các đối tượng ở một trong các cấp độ, thường là nhóm kiểm soát, được mã hóa 0 trong tất cả các vectơ.
Công thức trong các ô F2:H6 =LINEST(A2:A22,C2:D22,;TRUE) trả về số liệu thống kê hồi quy. Để so sánh, trong hình. Hình 24 cho thấy kết quả ANOVA truyền thống được công cụ trả về. ANOVA một chiều tiện ích bổ sung Phân tích dữ liệu.
Mã hóa hiệu ứng Trong một loại mã hóa khác được gọi là mã hóa hiệu ứng, Giá trị trung bình của mỗi nhóm được so sánh với giá trị trung bình của nhóm. Khía cạnh này của mã hóa hiệu ứng là do việc sử dụng -1 thay vì 0 làm mã cho nhóm, nhóm này nhận cùng một mã trong tất cả các vectơ mã (Hình 25).
Cơm. 25. Mã hóa hiệu ứng
Khi sử dụng mã hóa giả, giá trị không đổi được trả về bởi LINEST() là giá trị trung bình của nhóm được gán mã 0 trong tất cả các vectơ (thường là nhóm tham chiếu). Trong trường hợp mã hóa hiệu ứng, hằng số bằng giá trị trung bình tổng thể (ô J2).
Mô hình tuyến tính tổng quát - Cách hữu ích khái niệm hóa các thành phần của giá trị của biến kết quả:
Y ij = μ + α j + ε ij
Việc sử dụng các chữ cái Hy Lạp trong công thức này thay vì các chữ cái Latinh nhấn mạnh thực tế là nó đề cập đến tổng thể mà các mẫu được lấy ra, nhưng nó có thể được viết lại để chỉ ra rằng nó đề cập đến các mẫu được lấy từ một tổng thể nhất định:
Y ij = Y̅ + a j + e ij
Ý tưởng là mỗi quan sát Y ij có thể được xem là tổng của ba thành phần sau: trung bình tổng, μ; hiệu quả điều trị j, và j ; giá trị e ij, biểu thị độ lệch của chỉ số định lượng riêng lẻ Y ij so với giá trị tổng hợp của mức trung bình chung và hiệu ứng thứ j xử lý (Hình 26). Mục tiêu của phương trình hồi quy là cực tiểu hóa tổng bình phương của phần dư.
Cơm. 26. Các quan sát được phân tách thành các thành phần của mô hình tuyến tính tổng quát
Phân tích nhân tố. Nếu mối quan hệ giữa biến kết quả và hai hoặc nhiều yếu tố được nghiên cứu đồng thời thì trong trường hợp này chúng ta nói về việc sử dụng phân tích nhân tố. Việc thêm một hoặc nhiều yếu tố vào ANOVA một chiều có thể tăng sức mạnh thống kê. Trong phân tích phương sai một chiều, phương sai của biến kết quả không thể quy cho một yếu tố sẽ được đưa vào bình phương trung bình dư. Nhưng cũng có thể sự khác biệt này có liên quan đến một yếu tố khác. Sau đó, sự thay đổi này có thể được loại bỏ khỏi sai số bình phương trung bình, việc giảm sai số này dẫn đến tăng các giá trị kiểm tra F và do đó làm tăng khả năng thống kê của kiểm tra. Kiến trúc thượng tầng Phân tích dữ liệu bao gồm một công cụ xử lý đồng thời hai yếu tố (Hình 27).
Cơm. 27. Công cụ Phân tích phương sai hai chiều với sự lặp lại của Gói phân tích
Công cụ ANOVA được sử dụng trong hình này rất hữu ích vì nó trả về giá trị trung bình và phương sai của biến kết quả cũng như giá trị bộ đếm cho mỗi nhóm được đưa vào thiết kế. Trong bàn Phân tích phương sai hiển thị hai tham số không có trong đầu ra của phiên bản một yếu tố của công cụ ANOVA. Chú ý đến nguồn biến động Vật mẫu Và Cộtở dòng 27 và 28. Nguồn biến thể Cộtđề cập đến giới tính. Nguồn biến thiên Vật mẫuđề cập đến bất kỳ biến nào có giá trị chiếm các dòng khác nhau. Trong bộ lễ phục. 27 giá trị cho nhóm KursLech1 nằm ở dòng 2-6, nhóm KursLech2 nằm ở dòng 7-11 và nhóm KursLechZ nằm ở dòng 12-16.
Điểm chính là cả hai yếu tố, Giới tính (nhãn Cột trong ô E28) và Phương pháp xử lý (nhãn Mẫu trong ô E27), đều được đưa vào bảng ANOVA dưới dạng nguồn biến thể. Phương tiện dành cho nam giới khác với phương tiện dành cho phụ nữ và điều này tạo ra nguồn gốc của sự biến đổi. Phương tiện cho ba phương pháp điều trị cũng khác nhau, tạo ra một nguồn biến thể khác. Ngoài ra còn có nguồn thứ ba, Tương tác, đề cập đến tác động kết hợp của các biến Giới tính và Đối xử.
Chương 8. Phân tích hiệp phương sai
Phân tích hiệp phương sai, hay ANCOVA (Phân tích hiệp phương sai), làm giảm sai lệch và tăng sức mạnh thống kê. Hãy để tôi nhắc bạn rằng một trong những cách để đánh giá độ tin cậy phương trình hồi quy là các bài kiểm tra F:
F = Hồi quy MS/Dư lượng MS
trong đó MS (Bình phương trung bình) là bình phương trung bình và các chỉ số Hồi quy và Phần dư tương ứng biểu thị các thành phần hồi quy và phần dư. Phần dư MS được tính bằng công thức:
Số dư MS = Số dư SS / Số dư df
trong đó SS (Tổng bình phương) là tổng bình phương và df là số bậc tự do. Khi bạn thêm hiệp phương sai vào phương trình hồi quy, một phần của tổng bình phương không được bao gồm trong SS ResiduaI mà được bao gồm trong SS Regression. Điều này dẫn đến giảm SS Residua l và do đó giảm MS Residual. Phần dư MS càng nhỏ thì F-test càng lớn và bạn càng có nhiều khả năng bác bỏ giả thuyết không về việc không có sự khác biệt giữa các giá trị trung bình. Kết quả là bạn phân phối lại độ biến thiên của biến kết quả. Trong ANOVA, khi hiệp phương sai không được tính đến, độ biến thiên sẽ trở thành lỗi. Nhưng trong ANCOVA, một phần của sự thay đổi trước đây được quy cho thuật ngữ lỗi được gán cho hiệp phương sai và trở thành một phần của hồi quy SS.
Hãy xem xét một ví dụ trong đó cùng một tập dữ liệu được phân tích trước tiên bằng ANOVA và sau đó bằng ANCOVA (Hình 28).
Cơm. 28. Phân tích ANOVA chỉ ra rằng kết quả thu được từ phương trình hồi quy là không đáng tin cậy
Nghiên cứu so sánh tác động tương đối của tập thể dục, giúp phát triển sức mạnh cơ bắp và tập thể dục nhận thức (làm trò chơi ô chữ), giúp kích thích hoạt động của não. Các chủ đề đã ngẫu nhiênđược phân bổ thành hai nhóm sao cho khi bắt đầu thí nghiệm, cả hai nhóm đều ở trong cùng điều kiện. Sau ba tháng, hiệu suất nhận thức của các đối tượng được đo lường. Kết quả của các phép đo này được thể hiện ở cột B.
Phạm vi A2:C21 chứa dữ liệu nguồn được chuyển đến hàm LINEST() để thực hiện phân tích bằng cách sử dụng mã hóa hiệu ứng. Kết quả của hàm LINEST() được đưa ra trong phạm vi E2:F6, trong đó ô E2 hiển thị hệ số hồi quy liên quan đến vectơ tác động. Ô E8 chứa t-test = 0,93 và ô E9 kiểm tra độ tin cậy của t-test này. Giá trị chứa trong ô E9 chỉ ra rằng xác suất gặp phải sự khác biệt giữa các giá trị trung bình của nhóm được quan sát trong thí nghiệm này, là 36% nếu giá trị trung bình của nhóm bằng nhau trong tổng thể. Rất ít người coi kết quả này là có ý nghĩa thống kê.
Trong bộ lễ phục. Hình 29 cho thấy điều gì sẽ xảy ra khi bạn thêm một hiệp phương sai vào phân tích. Trong trường hợp này, tôi đã thêm độ tuổi của từng đối tượng vào tập dữ liệu. Hệ số xác định R 2 cho phương trình hồi quy sử dụng hiệp phương sai là 0,80 (ô F4). Giá trị R 2 trong phạm vi F15:G19, trong đó tôi đã sao chép kết quả ANOVA thu được mà không có hiệp phương sai, chỉ là 0,05 (ô F17). Do đó, phương trình hồi quy bao gồm hiệp phương sai sẽ dự đoán các giá trị cho biến Điểm nhận thức chính xác hơn nhiều so với việc chỉ sử dụng vectơ Tác động. Đối với ANCOVA, xác suất nhận được giá trị F-test hiển thị trong ô F5 một cách tình cờ là nhỏ hơn 0,01%.
Cơm. 29. ANCOVA mang lại một bức tranh hoàn toàn khác
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 một tiện ích bổ sung GÓI PHÂN TÍCH(Hình 62).
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. 63.
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 độ ảnh hưởng của từng yếu tố trong số ba yếu tố này đế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 chứa các 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ố.
Bảng tính mới Đặt nút chuyển sang vị trí này để mở lá mới vào 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).
Chức năng DÒNG tính toán số liệu thống kê cho một chuỗi bằng phương pháp bình phương tối thiểu để tính đường thẳng gần đúng nhất với dữ liệu có sẵn và sau đó trả về một mảng mô tả đường thẳng thu được. 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à:
y=m 1 x 1 +m 2 x 2 +…+b (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ảng(mn;mn-1;…;m 1 ;b). DÒNG cũng có thể trả về số liệu thống kê hồi quy bổ sung.
DÒNG(known_values_y; known_values_x; const; thống kê)
Known_y_values - một tập hợp các giá trị y đã được biết đến cho mối quan hệ y=mx+b.
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ệ y=mx+b.
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ệ y=mx đượ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êm vào thống kê hồi quy.(Bảng 17)
| Kích cỡ | Sự miêu tả |
| se1,se2,...,sen | Giá trị sai số chuẩn của các hệ số m1,m2,...,mn. |
| seb | Giá trị lỗi tiêu chuẩn cho hằng số b (seb = #N/A nếu const là FALSE). |
| r2 | Hệ số xác định. So sánh các giá trị thực của y và các giá trị thu được từ phương trình đường thẳng; Dựa trên kết quả so sánh, hệ số tất định được tính toán, chuẩn hóa từ 0 đến 1. Nếu bằng 1 thì có sự tương quan hoàn toàn với mô hình, tức là không có sự khác biệt giữa giá trị thực tế và giá trị ước tính của bạn. Trong trường hợp ngược lại, nếu hệ số xác định bằng 0 thì việc sử dụng phương trình hồi quy để dự đoán các giá trị của y cũng chẳng ích gì. Để biết thêm thông tin về cách tính r2, hãy xem phần "Ghi chú" ở cuối phần này. |
| sey | Sai số chuẩn khi ước tính y. |
| F | Giá trị thống kê F hoặc giá trị F quan sát được. Thống kê F được sử dụng để xác định xem mối quan hệ được quan sát giữa biến phụ thuộc và biến độc lập có phải là ngẫu nhiên hay không. |
| df | Các bậc tự do. Bậc tự do rất hữu ích cho việc tìm các giá trị tới hạn F trong bảng thống kê. Để xác định mức độ tin cậy của mô hình, bạn so sánh các giá trị trong bảng với thống kê F được hàm LINEST trả về. Để biết thêm thông tin về cách tính df, hãy xem phần “Ghi chú” ở cuối phần này. Tiếp theo, Ví dụ 4 cho thấy việc sử dụng các giá trị F và df. |
| ssreg | Tổng hồi quy của bình phương. |
| ssresid | Tổng bình phương còn lại. Để biết thêm thông tin về cách tính ssreg và ssresid, hãy xem phần “Ghi chú” ở cuối phần này. |
Hình dưới đây cho thấy thứ tự trả về số liệu thống kê hồi quy bổ sung (Hình 64).
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 cần lấy hai điểm trên đường thẳng đó (x 1 ,y 1) và (x 2 ,y 2); độ dốc sẽ bằng (y 2 -y 1)/(x 2 -x 1).
Giao điểm Y (b): Giao điểm 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 của đường thẳng là y=mx+b. 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 thế 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 cách sử dụ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 nhỏ nhất để 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_values; known_x_values) cho đường thẳng hoặc hàm GROWTH(known_y_values; known_x_values) 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.
Khi thực hiện phân tích hồi quy, Microsoft Excel sẽ tính toán bình phương chênh lệch giữa giá trị y được dự đoán và giá trị y thực tế cho mỗi điểm. 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 cách sử dụng phân tích hồi quy giải thích mối quan hệ giữa các biến tốt như thế 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 giá trị Y được tính bằng độ 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:
y=m 1 x+m 2 x 2 +m 3 x 3 +b
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.
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”.
- Một cửa sổ mở ra Cài đặt Excel. Chuyển đến phần phụ “Tiện ích bổ sung”.
- Ở dưới cùng của cửa sổ mở ra, di chuyển công tắc trong khối “Điều khiển” sang vị trí “ Tiện ích bổ sung của 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 như thế nào thời tiết dưới dạng nhiệt độ không khí có thể ảnh hưởng đến sự tham gia của 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ư chúng ta có thể thấy, sử dụng chương trình Microsoft Excel khá dễ dàng để tạo bảng phân tích hồi quy. 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?
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:
ŷ - 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:
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 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
Để 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.
Để 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.
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 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à 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
Chúng ta hãy xem xét vấn đề xác định sự phụ thuộc của số lượng thành viên trong nhóm bỏ cuộc lương trung bình tại 6 xí 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ó:
Để 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ờ 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ả 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ụ: 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 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 lựa chọn càng đượ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 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 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 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.
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 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 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 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ị 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. Phân loại mối tương quan cho Những khu vực khác nhau Sẽ khác. 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 sử dụng Công cụ Excel tìm hệ số tương quan.
Để 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 liên hệ giữa thời gian hoạt động máy tiện và chi phí bảo trì nó.
Đặ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. Để xây dựng mô hình hồi quy, bạn phải chọn Tools\Data Analysis\Regression (trong Excel 2007 chế độ này nằm trong khối Data/Data Analysis/Regression). Sau đó sao chép kết quả vào một khối để phân tích.
