Đường cong độ lệch chuẩn Excel

Đường cong hình chuông, còn được gọi là phân phối chuẩn, là loại phân phối phổ biến nhất cho một biến. Nó được chấp nhận như là một phân phối xảy ra tự nhiên trong nhiều tình huống. Trong một đường cong hình chuông, điểm cao nhất của cung xác định giá trị trung bình, là số lần xuất hiện cao nhất của một phần tử. Xác suất xảy ra giảm về phía hai bên của đường cong. Bạn có thể sử dụng đường cong hình chuông để so sánh hiệu suất bằng cách loại trừ các điểm cực đoan hoặc xác định kỳ vọng của mình bằng khả năng kết quả sẽ nằm trong phạm vi bên trái hoặc bên phải của trung tâm. Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn cách tạo đường cong hình chuông trong Excel với một trường hợp sử dụng trong thế giới thực làm ví dụ

Tải xuống sổ làm việc

Khái niệm cơ bản

Tất cả những gì bạn cần là giá trị trung bình [trung bình] và độ lệch chuẩn của tập dữ liệu của bạn. Cả hai chỉ số này đều có thể được tính toán trong Excel bằng các công thức bên dưới

Để tính giá trị trung bình [trung bình] =AVERAGE[data]

Để tính độ lệch chuẩn =STDEV. P[dữ liệu]

Khi xem xét hai giá trị này, các giá trị phân phối chuẩn tuân theo các quy tắc này

1. Tổng diện tích dưới đường cong bằng 1 [100%]
2. Tâm của đường cong hình chuông là giá trị trung bình của điểm dữ liệu
3. [1-σ] Khoảng 68. 2% diện tích dưới đường cong nằm trong một độ lệch chuẩn [Trung bình ± Độ lệch chuẩn]
4. [2-σ] Khoảng 95. 5% diện tích dưới đường cong nằm trong hai độ lệch chuẩn [Trung bình ± 2 * Độ lệch chuẩn]
5. [3-σ] Khoảng 99. 7% diện tích dưới đường cong nằm trong ba độ lệch chuẩn [Trung bình ± 3 * Độ lệch chuẩn]

Hình ảnh từ Đại học Virginia

Tạo đường cong hình chuông trong Excel

Hãy lấy một ví dụ phổ biến và giả sử chúng ta đang phân tích kết quả bài kiểm tra cho một lớp học sinh. Chúng tôi sẽ sử dụng đường cong hình chuông để đo lường kết quả bài kiểm tra để so sánh tốt hơn

Chúng tôi bắt đầu bằng cách tính toán các số liệu để tạo dữ liệu phân tán bình thường sẽ tạo ra đường cong của chúng tôi. Chúng ta cần tính toán

• Giá trị trung bình [trung bình] của các giá trị
• Độ lệch chuẩn của các giá trị
• 3-giới hạn độ lệch chuẩn cho giá trị trung bình trước và sau
• Giá trị khoảng cho các điểm dữ liệu được phân phối bình thường. Điều này cũng đòi hỏi phải xác định các điểm khoảng. Bạn có thể chọn bất kỳ số nào, nhưng hãy nhớ rằng, nhiều khoảng thời gian hơn có nghĩa là chính xác hơn

Ghi chú nhanh trước khi đi sâu vào các công thức. Chúng tôi đã sử dụng các phạm vi được đặt tên thay vì tham chiếu ô để giúp đọc công thức dễ dàng hơn. Bạn có thể tìm thêm chi tiết về các phạm vi được đặt tên tại đây. Phạm vi được đặt tên trong Excel

số liệu

Bắt đầu bằng cách tính giá trị trung bình và độ lệch chuẩn của dữ liệu. Bạn có thể sử dụng AVERAGE và STDEV. Các hàm P để tính giá trị trung bình và độ lệch chuẩn tương ứng

Bước tiếp theo là tính toán 3 giá trị độ lệch chuẩn để đặt giá trị tối thiểu và tối đa cho 99. 7% dữ liệu

3 giá trị độ lệch chuẩn có thể vượt quá tập dữ liệu thực tế. Đây là một hành vi bình thường có thể xảy ra thường xuyên với các tập dữ liệu nhỏ hơn

Tối thiểu = 83. 23 - 3*5. 54 = 66. 61

Tối đa = 83. 23 - 3*5. 54 = 99. 86

Sau khi đặt giá trị tối thiểu và tối đa cho đường cong của chúng tôi, chúng tôi cần tạo các khoảng. Các giá trị khoảng sẽ là cơ sở cho các giá trị phân phối bình thường. Để tính toán các khoảng thời gian, tất cả những gì bạn cần làm là chia diện tích giữa các giá trị tối thiểu và tối đa cho số lượng khoảng thời gian. Trong ví dụ này, chúng tôi đặt giá trị này thành 20, nhưng bạn có thể sử dụng số lớn hơn để tăng số lượng điểm dữ liệu

Giá trị khoảng = [99. 85 - 66. 60]/20 = 1. 66

Khi giá trị khoảng thời gian được tính toán, bạn có thể tạo các điểm dữ liệu. Để thực hiện việc này, hãy nhập giá trị nhỏ nhất vào một ô. Sau đó, ngay bên dưới giá trị tối thiểu, hãy nhập công thức để thêm giá trị khoảng vào giá trị tối thiểu. Ở đây, chúng tôi đã sử dụng các tham chiếu ô [như J4] giúp điền các điểm dữ liệu dễ dàng lên đến giá trị tối đa

Bước tiếp theo là tính toán các giá trị được phân phối bình thường từ các điểm dữ liệu được tạo. Bạn có thể sử dụng NORM của Excel. Hàm DIST để tạo các giá trị này

Sử dụng các điểm dữ liệu đã điền làm đối số đầu tiên của hàm. Các giá trị trung bình và độ lệch chuẩn là các đối số tiếp theo. Kết thúc công thức với giá trị Boolean FALSE để sử dụng loại không tích lũy của hàm này

Đồ thị

Chúng ta gần xong rồi. Chọn các điểm dữ liệu và giá trị phân phối chuẩn, sau đó chèn biểu đồ X-Y Scatter. Sử dụng phiên bản Scattered with Smooth Lines để tạo đường cong hình chuông trong Excel

Biểu đồ có vẻ hơi khác trước. Hãy xem cách bạn có thể làm cho nó trông đẹp hơn

Để thay đổi tiêu đề của biểu đồ, bấm đúp vào tiêu đề và cập nhật tên

Tiếp theo, nhấp đúp vào trục X và xác định các giá trị tối thiểu và tối đa từ bảng Tùy chọn trục để loại bỏ khoảng trắng ở cả hai bên. Điều này sẽ cung cấp cho biểu đồ của bạn một hình chuông tốt hơn. Chúng tôi đặt các giá trị nằm ngoài tập dữ liệu của mình một chút. Ví dụ: 66 – 100 cho giá trị 66. 30 – 99. 86

Bạn có thể tạo một đường cong tiêu chuẩn trên Excel không?

Đường cong độ lệch chuẩn là gì?