Trong phân tích thống kê, phương sai giữa các thành viên của tập dữ liệu cho thấy các điểm dữ liệu cách nhau bao xa so với đường xu hướng, còn được gọi là đường Hồi quy. Phương sai càng cao, các điểm dữ liệu càng trải rộng. Nghiên cứu về phân tích phương sai cho thấy phần nào của phương sai có thể được giải thích bằng các đặc điểm của dữ liệu và có thể được quy cho các yếu tố ngẫu nhiên. Phần của phương sai không thể giải thích được gọi là phương sai dư.
Sử dụng bảng tính Excel để tính toán phương sai dư
Công thức tính phương sai dư bao gồm nhiều phép tính phức tạp. Đối với các tập dữ liệu nhỏ, quá trình tính toán phương sai còn lại bằng tay có thể rất tẻ nhạt. Đối với các tập dữ liệu lớn, tác vụ có thể bị cạn kiệt. Bằng cách sử dụng bảng tính Excel, bạn chỉ cần nhập các điểm dữ liệu và chọn công thức chính xác. Chương trình xử lý các tính toán phức tạp và cung cấp kết quả nhanh chóng.
Điểm dữ liệu
Mở bảng tính Excel mới và nhập các điểm dữ liệu vào hai cột. Các đường hồi quy yêu cầu mỗi điểm dữ liệu có hai phần tử. Các nhà thống kê thường dán nhãn các yếu tố này "X" và "Y." Ví dụ, Công ty Bảo hiểm Chung muốn tìm phương sai còn lại về chiều cao và cân nặng của nhân viên. Biến X đại diện cho chiều cao và biến Y đại diện cho trọng lượng. Nhập độ cao vào Cột A và các trọng số vào Cột B.
Tìm ý nghĩa
Các nghĩa là đại diện cho mức trung bình cho mỗi phần tử trong tập dữ liệu. Trong ví dụ này, Bảo hiểm chung muốn tìm độ lệch trung bình, độ lệch chuẩn và hiệp phương sai của 10 chiều cao và trọng lượng của nhân viên. Có thể tìm thấy trung bình của các độ cao được liệt kê trong Cột A bằng cách nhập hàm "= AVERAGE (A1: A10)" vào ô F1. Có thể tìm thấy trung bình của các trọng số được liệt kê trong Cột B bằng cách nhập hàm "= AVERAGE (B1: B10)" vào ô F3.
Tìm độ lệch chuẩn và hiệp phương sai
Các độ lệch chuẩn đo lường cách xa các điểm dữ liệu được lan truyền từ giá trị trung bình. Các hiệp phương sai đo lường bao nhiêu phần tử của điểm dữ liệu thay đổi cùng nhau. Độ lệch chuẩn của độ cao được tìm thấy bằng cách nhập hàm "= STDEV (A1: A10)" vào ô F2. Độ lệch chuẩn của các trọng số được tìm thấy bằng cách nhập hàm "= STDEV (B1: B10)" vào ô F4. Hiệp phương sai giữa độ cao và trọng lượng được tìm thấy bằng cách nhập hàm "= COVAR (A1: A10; B1: B10)" vào ô F5.
Tìm đường hồi quy
Các đường Hồi quy đại diện cho một hàm tuyến tính theo xu hướng của các điểm dữ liệu. Công thức cho đường hồi quy trông như thế này: Y = aX + b.
Người dùng có thể tìm thấy các giá trị cho "a" và "b" bằng cách sử dụng các tính toán cho phương tiện, độ lệch chuẩn và hiệp phương sai. Giá trị cho "b" đại diện cho điểm mà đường hồi quy chặn trục Y. Giá trị có thể được tìm thấy bằng cách lấy hiệp phương sai và chia nó cho bình phương độ lệch chuẩn của các giá trị X. Công thức Excel đi vào ô F6 và trông như thế này: = F5 / F2 ^ 2.
Giá trị cho "a" biểu thị độ dốc của đường hồi quy. Công thức Excel đi vào ô F7 và trông như thế này: = F3-F6 * F1.
Để xem công thức cho đường hồi quy, hãy nhập chuỗi nối này vào ô F8:
= CONCATENATE ("Y ="; ROUND (F6; 2); "X"; IF (ĐĂNG KÝ (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Tính giá trị Y
Bước tiếp theo liên quan đến việc tính toán các giá trị Y trên đường hồi quy cho các giá trị X đã cho trong tập dữ liệu. Công thức tìm các giá trị Y đi vào cột C và trông như thế này:
= $ F $ 6 * A (i) + $ F $ 7
Trong đó A (i) là giá trị cho Cột A trong Hàng (i). Các công thức trông như thế này trong bảng tính:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, v.v.
Các mục trong Cột D cho thấy sự khác biệt giữa giá trị dự kiến và giá trị thực cho Y. Các công thức trông như thế này:
= B (i) -C (i), Trong đó B (i) và C (i) lần lượt là các giá trị trong Hàng (i) trong Cột B và C.
Tìm phương sai dư
Các công thức cho phương sai dư đi vào Cell F9 và trông như thế này:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Trong đó SUMSQ (D1: D10) là tổng bình phương của sự khác biệt giữa các giá trị Y thực tế và dự kiến và (COUNT (D1: D10) -2) là số điểm dữ liệu, trừ 2 cho mức độ tự do trong dữ liệu.
