Bài viết sẽ chỉ cho bạn cách chuyển đổi dữ liệu sang phân phối chuẩn (normal distribution) trong Excel. Trong thống kê, việc áp dụng khái niệm phân phối chuẩn rất quan trọng để hiểu vị trí của một hoặc nhiều dữ liệu trong tập dữ liệu. Tính toán thủ công các giá trị phân phối chuẩn của dữ liệu tốn thời gian và cũng là quá trình mệt mỏi. May mắn thay, chúng ta có một hàm trong Excel có thể giải quyết vấn đề này ngay lập tức. Tên hàm này là NORM.DIST. Hãy xem phần còn lại của bài viết để hiểu rõ hơn.
Phân phối chuẩn là gì?
Phân phối chuẩn còn được gọi là phân phối Gaussian. Đây là phân phối xác suất của một không gian mẫu. Phân phối của các dữ liệu này đối xứng với giá trị trung bình. Nó cũng là thước đo cho thấy dữ liệu gần trung bình xuất hiện nhiều hơn dữ liệu xa trung bình. Quan trọng là phải biết trung bình (mean) bằng không và độ lệch chuẩn (Standard Deviation) bằng 1 trong phân phối chuẩn. Trong thực tế, phân phối chuẩn giúp chúng ta hiểu ý nghĩa của dữ liệu trong một nhóm dữ liệu. Phương trình tìm phân phối chuẩn trong một tập dữ liệu cho dưới đây.
Trong đó,
σ = Độ lệch chuẩn
x = Dữ liệu mẫu
μ = Trung bình/Mean
2 cách chuyển đổi dữ liệu sang phân phối chuẩn trong Excel
Trong tập dữ liệu, chúng ta có thông tin về hiệu suất của một số học sinh theo điểm số trong một kỳ thi. Chúng ta sẽ xác định ai có hiệu suất xuất sắc trong kỳ thi đó bằng cách chuyển đổi điểm số của họ sang phân phối chuẩn sử dụng Excel.
1. Sử dụng hàm Excel để chuyển đổi dữ liệu sang phân phối chuẩn
Cách tốt nhất để chuyển đổi dữ liệu sang phân phối chuẩn là sử dụng hàm NORM.DIST của Excel. Nó sẽ tự động tính toán dữ liệu phân phối chuẩn bằng công thức đã nêu. Hãy xem quy trình bên dưới.
Các bước:
- Trước tiên, tạo một cột để lưu giá trị phân phối chuẩn và chọn hai ô để lưu Trung bình (Mean) và Độ lệch chuẩn (Standard Deviation).
- Nhập công thức sau để tính Trung bình và nhấn ENTER. Bạn sẽ thấy Trung bình của Điểm (Marks) của các học sinh này.
=AVERAGE(C5:C11)
Ở đây, hàm AVERAGE trả về giá trị Trung bình của Điểm của các học sinh.
- Sau đó, dùng công thức sau để tính Độ lệch chuẩn.
=STDEV.P(C5:C11)
Hàm STDEV.P trả về Độ lệch chuẩn của dữ liệu này.
- Tiếp theo, nhập công thức sau vào ô D5.
=NORM.DIST(C5,$C$13,$C$14,FALSE)
- Sau đó, nhấn nút ENTER và bạn sẽ thấy giá trị phân phối chuẩn của điểm ở ô C5.
- Sau đó, dùng Fill Handle (Tay cầm điền) để AutoFill (Tự động điền) các ô dưới.
Vậy bạn có thể chuyển đổi toàn bộ dữ liệu của tập dữ liệu sang phân phối chuẩn trong Excel.
- Nếu bạn muốn hiển thị dữ liệu dưới dạng đồ thị phân phối chuẩn, hãy chọn phạm vi C4:D11.
- Sau đó, vào Insert (Chèn) >> Scatter with Smooth Lines (Điểm phân tán với đường mượt)
Sau đó, bạn sẽ thấy dữ liệu trong đồ thị phân phối chuẩn.
Hãy nhớ rằng, khoảng 68% dữ liệu của bạn nằm trong khoảng Trung bình ± Độ lệch chuẩn, 95% dữ liệu nằm trong khoảng Trung bình ± 2*Độ lệch chuẩn và 99.7% dữ liệu sẽ rơi trong khoảng Trung bình ± 3*Độ lệch chuẩn. Nghĩa là, 68% học sinh có điểm giữa 74.57 ± 10.66 hoặc 63.91 đến 85.23. Tương tự, bạn có thể xác định các khoảng khác cho 95% và 99.7%.
2. Áp dụng công thức toán học để chuyển đổi dữ liệu sang phân phối chuẩn
Nếu bạn giỏi thống kê và không quan tâm dùng hàm NORM.DIST tích hợp sẵn, bạn có thể dùng công thức toán học của Phân phối Chuẩn. Hãy xem quy trình bên dưới.
Các bước:
- Trước tiên, tạo một cột để lưu giá trị phân phối chuẩn và chọn hai ô để lưu Trung bình và Độ lệch chuẩn.
- Tính Trung bình và Độ lệch chuẩn theo quy trình của Phần 1.
- Tiếp theo, nhập công thức sau vào ô D5.
=EXP(-0.5*((C5-$C$13)/$C$14)^2)/($C$14*SQRT(2*PI()))
Ở đây, công thức dùng hàm EXP và SQRT để tạo công thức toán học của chuyển đổi phân phối chuẩn. Nó sẽ trả về giá trị phân phối chuẩn của dữ liệu.
- Sau đó, nhấn nút ENTER và bạn sẽ thấy giá trị phân phối chuẩn của điểm ở ô C5.
- Sau đó, dùng Fill Handle (Tay cầm điền) để AutoFill (Tự động điền) các ô dưới.
Vậy bạn có thể chuyển đổi toàn bộ dữ liệu của tập dữ liệu sang phân phối chuẩn dùng công thức toán học.
- Nếu bạn muốn hiển thị dữ liệu dưới dạng đồ thị phân phối chuẩn, hãy làm theo liên kết của Phần 1.
Phần thực hành
Ở đây, tôi cung cấp tập dữ liệu của bài viết để bạn tự thực hành các phương pháp này.
Kết luận
Tóm lại, bài viết này giúp bạn hiểu các phương pháp cơ bản về cách chuyển đổi dữ liệu sang phân phối chuẩn trong Excel. Nếu bạn có phương pháp tốt hơn hoặc câu hỏi, phản hồi về bài viết, hãy chia sẻ trong hộp bình luận. Điều này sẽ giúp tôi làm giàu các bài viết sắp tới. Để thắc mắc thêm, vui lòng truy cập trang web ExcelDemy.