Cách tạo đường cong hình chuông trong Excel
Đường cong phân phối Gaussian, thường được gọi là đường cong hình chuông, là đồ thị phân phối chuẩn giúp phân tích phương sai trong tập dữ liệu. Trong đường cong hình chuông, điểm cao nhất (cũng là giá trị trung bình) đại diện cho sự kiện có nhiều khả năng xảy ra nhất, trong khi phần còn lại của các sự kiện được phân phối theo cách đối xứng với giá trị trung bình.
Từ việc phân loại học sinh tương đối và tạo ra các hệ thống đánh giá cạnh tranh đến dự đoán lợi nhuận, đường cong hình chuông có rất nhiều ứng dụng. Ở đây, chúng tôi sẽ hướng dẫn bạn quy trình tạo đường cong hình chuông trong Excel.
Mục Lục
Kiến thức cơ bản về tạo đường cong hình chuông trong Excel
Để hiểu cách tạo đường cong hình chuông trong Excel, hãy giả sử bạn là một giáo sư lịch sử cần cho điểm sinh viên dựa trên thành tích của họ trong một bài kiểm tra. Giả sử lớp học có 16 học sinh với các điểm sau:
Bây giờ, trước khi bạn có thể tạo đường cong hình chuông của bất kỳ tập dữ liệu nào, bạn cần tính toán:
- Bần tiện – giá trị trung bình của tập dữ liệu (cho trung tâm của đường cong)
- Độ lệch chuẩn – đo lường mức độ phân tán của các điểm dữ liệu so với giá trị trung bình (cho biết mức độ lan truyền của đường cong)
Tìm ý nghĩa
Bạn có thể sử dụng các hàm tích hợp sẵn trong Excel để tính toán các thống kê cơ bản như giá trị trung bình, độ lệch chuẩn, tỷ lệ phần trăm, v.v. Để tìm giá trị trung bình, hãy sử dụng hàm AVERAGE trong Excel:
Loại hình = AVERAGE (B2: B16) để tìm giá trị trung bình của bảng đánh dấu đã cho ở trên. Bạn sẽ nhận thấy rằng nó cho giá trị là 53,93.
Nếu bạn muốn một giá trị số nguyên mà bạn thường muốn, bạn có thể sử dụng hàm ROUND. Để làm như vậy, hãy nhập:
=ROUND(AVERAGE(B2:B16),0)Bây giờ giá trị trung bình trở thành 54.
Tìm độ lệch chuẩn
Excel hiển thị hai công thức cho độ lệch chuẩn:
- STDEV.P được sử dụng khi bạn biết rằng dữ liệu của mình đã hoàn chỉnh, tức là đó là một tập hợp.
- STDEV.S được sử dụng khi dữ liệu của bạn không đầy đủ, tức là bạn có một mẫu dân số.
Trong thống kê, mọi người thường chọn mẫu từ một quần thể, vì vậy STEV.S được sử dụng bình thường. Vì bạn có dữ liệu đầy đủ tức là điểm của tất cả học sinh trong lớp, chúng tôi sẽ sử dụng STDEV.P. Để có được độ lệch chuẩn của bảng đánh dấu đã cho, hãy nhập:
=STDEV.P(B2:B16)
Bạn sẽ nhận được 27,755. Nếu bạn muốn giá trị của mình ở dạng số nguyên, chỉ cần làm tròn giá trị bằng cách nhập:
=ROUND(STDEV.P(B2:B16),0)Bạn sẽ nhận được 28.
Sắp xếp dữ liệu theo thứ tự tăng dần
Để bạn tạo hình chuông cho biểu đồ phân phối chuẩn của mình, dữ liệu cần phải theo thứ tự tăng dần. Nếu dữ liệu của bạn không theo thứ tự tăng dần (như trong ví dụ của chúng tôi), chỉ cần chọn tất cả các giá trị (điểm kiểm tra) trong tập dữ liệu của bạn, đi tới Sắp xếp & Lọc trong bảng điều khiển trên cùng và chọn Sắp xếp Tăng dần.
Cách tạo đường cong hình chuông trong Excel
Bây giờ bạn đã có cả độ lệch chuẩn và giá trị trung bình (trung bình), đã đến lúc tính toán phân phối chuẩn của các giá trị đã cho. Sau khi có điều đó, chúng ta sẽ có mọi thứ cần thiết để tạo đường cong hình chuông bằng cách sử dụng tùy chọn biểu đồ phân tán của Excel. Trước tiên, hãy tìm phân phối chuẩn của tất cả các giá trị bên trong tập dữ liệu:
1. Tìm phân phối chuẩn
Đã đến lúc tính toán phân phối chuẩn của các điểm dữ liệu. Trong Excel, bạn có thể tìm thấy phân phối chuẩn bằng cách sử dụng NORM.DIST yêu cầu các biến sau:
- X là điểm dữ liệu mà bạn muốn tính toán phân phối chuẩn.
- Bần tiện là giá trị trung bình của dữ liệu đã cho (đã được tính toán).
- Độ lệch chuẩn là độ lệch chuẩn của dữ liệu đã cho (đã được tính toán).
- Tích lũy là giá trị logic được sử dụng để chỉ định loại phân phối cần thiết.
Để tính toán phân phối chuẩn của điểm kiểm tra của chúng tôi:
- Loại hình = NORM.DIST ( trong một ô mới (ô C2 trong trường hợp của chúng ta.)
- Nhập các giá trị bắt buộc bằng dấu phẩy giữa các giá trị như được hiển thị trong cú pháp.
- Đối với x, hãy nhập B2cho điểm dữ liệu đầu tiên, tức là 12.
- Có nghĩa là, hãy nhập D2cung cấp giá trị trung bình cho dữ liệu của chúng tôi và nhấn F4. Các F4 khóa giá trị của giá trị trung bình được biểu thị bằng ký hiệu đô la ($ D $ 2) để khi bạn sao chép công thức này cho các giá trị khác nhau trong dữ liệu của chúng tôi, giá trị của giá trị trung bình vẫn giữ nguyên.
- Đối với độ lệch chuẩn, hãy nhập E2 để lấy độ lệch chuẩn và khóa giá trị của nó bằng cách nhấn F4.
- Cuối cùng, nhập SAI thay cho giá trị logic tích lũy và đóng dấu ngoặc đơn. Bằng cách gõ SAIbạn nhận được hàm mật độ xác suất bình thường (PDF.) Ghi chú: Bạn cần phải nhập THẬT để có được hàm phân phối chuẩn tích lũy (CDF).
- Sau khi nhận được phân phối chuẩn cho giá trị đầu tiên, chỉ cần kéo nó để nhận phân phối chuẩn cho các giá trị còn lại trong tập dữ liệu của bạn.
2. Tạo Lô phân tán
Bây giờ bạn đã có các điểm dữ liệu và phân phối chuẩn, bạn có mọi thứ cần thiết để tạo đường cong hình chuông của mình. Để làm được điều đó, bạn phải tạo biểu đồ phân tán trong Excel bằng cách làm theo các bước dưới đây:
- Chọn tập dữ liệu (điểm của học sinh) và phân phối chuẩn của chúng.
- Đi đến Chèn> Sơ đồ phân tán.
- Lựa chọn Phân tán với các đường mịn.
- Và bạn đã có đường cong chuông của mình.
Bạn có thể thấy rằng biểu đồ không hoàn toàn có hình chuông. Đó là bởi vì tập dữ liệu (điểm của học sinh) không được phân phối bình thường (nghĩa là giá trị trung bình, điểm trung bình và chế độ của tập dữ liệu không giống nhau.)
3. Tùy chỉnh đường cong chuông
Chúng tôi đã có đường cong chuông của mình, nhưng chúng tôi có thể làm cho nó tốt hơn một chút bằng cách tùy chỉnh nó. Trước hết, hãy cập nhật tiêu đề của đường cong bằng cách nhấp đúp vào tiêu đề và nhập tiêu đề mong muốn. (Bạn có thể sửa đổi loại phông chữ, kích thước phông chữ và vị trí của tiêu đề, trong số những thứ khác.)
Bạn có thể loại bỏ Series1 được viết ở cuối đường cong chuông của bạn bằng cách tắt Truyền thuyết chuyển đổi. Để cung cấp cho đường cong hình chuông của bạn có hình dạng tốt hơn, bạn có thể xác định các giá trị tối đa và tối thiểu của biểu đồ hình chuông của bạn. Để làm điều đó, hãy nhấp đúp vào trục x và bạn sẽ nhận được tab Tùy chọn trục, từ đó bạn có thể thực hiện các thay đổi cần thiết.
Sử dụng Bell Curves trong Excel
Bây giờ bạn đã có đường cong hình chuông, hiển thị dữ liệu phân phối sâu sắc, bạn có thể sử dụng nó để chấm điểm cho học sinh của mình. Bạn có thể áp dụng quy trình tương tự để tạo đường cong hình chuông cho bất kỳ dữ liệu nhất định nào.
Mặc dù đường cong hình chuông cung cấp xác suất của một điểm dữ liệu cụ thể trong tập dữ liệu của bạn, nhưng có một số biểu đồ khác mà bạn có thể tạo trong Excel để tìm hiểu thông tin chi tiết thú vị khác về tập dữ liệu của mình.