Cách tính Sai số bình phương trung bình (MSE) trong Excel

Trong thống kê, sai số bình phương trung bình (MSE) là một số liệu quan trọng dùng để đo mức độ sai sót trong mô hình thống kê trên Excel. Nó thường được dùng để đánh giá độ chính xác của một mô hình được sử dụng qua đó dự đoán các giá trị trong tương lai. Và trong bài viết này, TekZone sẽ giải thích chi tiết hơn về MSE cũng như hướng dẫn bạn cách tính MSE trong Excel nhé!

I. Sai số bình phương trung bình (MSE) là gì?

Sai số bình phương trung bình (MSE) hay còn gọi lỗi bình phương trung bình là một phép tính đo lường chênh lệch bình phương trung bình giữa giá trị ước tính và giá trị thực tế trong tập dữ liệu. Nói cách khác, nó ước tính mức độ sai sót trong một mô hình thống kê. Đối với những người đam mê thống kê, nó tính toán mức độ phù hợp của đường hồi quy (SSR) với một tập hợp các điểm dữ liệu.

Ví dụ: Bạn có một mô hình dự đoán giá trị doanh thu mà bạn sẽ nhận được với tư cách là một công ty phần mềm trong một năm. Vào cuối năm, bạn nhập giá trị bán hàng thực tế mà bạn đã tạo ra. Sau đó, bạn có thể tính toán MSE để xem mô hình dự đoán kết quả tốt đến mức nào.

MSE được tính bằng cách lấy tổng bình phương sai số chia cho số lượng mẫu.

Công thức tính MSE trông như thế này:

Trong đó:

• Σ có nghĩa là tổng các giá trị.
• n là cỡ mẫu hoặc số lượng quan sát.
• Y i là các giá trị quan sát được.
• Ŷ i là giá trị dự đoán.

Giá trị lỗi thấp hơn cho thấy mô hình có sai số chuẩn nhỏ hơn và dự đoán biến mục tiêu tốt hơn. MSE được sử dụng rộng rãi trong hồi quy thống kê và phân tích dữ liệu, nó rất hữu ích trong việc so sánh các mô hình khác nhau hoặc điều chỉnh các tham số để cải thiện độ chính xác của dự đoán.

II. Cách tính sai số bình phương trung bình (MSE) trong Microsoft Excel

Có hai cách chính để bạn có thể tính MSE trong Microsoft Excel là dùng hàm SUMSQ và hàm AVERAGE. Trong hưỡng dẫn này, chúng tôi sẽ sử dụng ví dụ bên dưới để chỉ cho bạn cách tính MSE bằng cách sử dụng từng hàm sau:

Trong ví dụ đơn giản này, chúng ta sẽ xem xét giá trị doanh thu hư cấu cho mỗi tháng (Cột A). Các giá trị ước tính được trình bày ở Cột B và các giá trị thực tế ở Cột C.

1. Tính MSE bằng hàm SUMSQ

Hàm SUMSQ trong Excel được sử dụng để tính tổng các bình phương của các số trong một phạm vi. Và đây là cách sử dụng nó để tính toán MSE:

• Bước1: Hãy tạo một cột mới D đặt tên “Difference” để thể hiện sự khác biệt giữa giá trị dự kiến ​​và giá trị thực tế.

• Bước 2: Tại ô D2, sử dụng công thức “=C2-B2” để tính chênh lệch cho các điểm dữ liệu trong mỗi hàng.

• Bước 3: Chọn một ô mới khác bất kỳ và nhập cú pháp sau: “=SUMSQ(D2:D13/COUNT(D2:D13)”. Thay thế các ô trong công thức bằng các ô chính xác từ bảng tính Excel. Kết quả cuối cùng trong ô này là giá trị MSE.

2. Tính MSE bằng hàm AVERAGE

Hàm AVERAGE trong Excel được sử dụng để tính trung bình cộng của một dãy số. Phương thức này trả về kết quả tương tự như hàm SUMSQ, nhưng mỗi bước được thực hiện thủ công. Cách làm như sau:

• Thực hiện Bước 1 và 2 được mô tả trong phương pháp SUMSQ. Điều này sẽ trả về sự khác biệt giữa giá trị dự báo và dữ liệu thực tế.

• Bước 3: Thêm một cột E đặt tên “Square of Differences”, sau đó sử dụng công thức “=D2^2” để tính bình phương hiệu của từng giá trị trong Cột D.

• Bước 4: Chọn một ô mới bất kỳ và tính giá trị trung bình của Cột E. Điều này sẽ trả về giá trị trung bình của các giá trị bình phương mà bạn đã tính toán trước đó. Để làm như vậy, hãy nhập công thức “=AVERAGE(E2:E12)” và nhấn Enter. Nhớ thay thế các ô bằng các ô bạn đã sử dụng trong trang tính của mình. Giá trị dữ liệu kết quả nhận được chính là MSE.

Hy vọng với hướng dẫn này, giờ đây bạn có thể sử dụng Excel để tính MSE một cách dễ dàng. Chúc các bạn thành công!