Cách tạo đường cong chuông phân phối bình thường trong Excel

Hướng dẫn này sẽ trình bày cách tạo đường cong hình chuông phân phối chuẩn trong tất cả các phiên bản Excel: 2007, 2010, 2013, 2016 và 2022.

Trong thống kê, đường cong hình chuông (còn được gọi là phân phối chuẩn chuẩn hoặc đường cong Gaussian) là một đồ thị đối xứng minh họa xu hướng dữ liệu tập hợp xung quanh một giá trị trung tâm hoặc giá trị trung bình trong một tập dữ liệu nhất định.

Trục y biểu thị xác suất tương đối của một giá trị nhất định xuất hiện trong tập dữ liệu trong khi trục x vẽ các giá trị trên biểu đồ để tạo ra một đường cong hình chuông, do đó có tên như vậy.

Biểu đồ giúp chúng tôi phân tích xem một giá trị cụ thể là một phần của biến thể dự kiến ​​hay có ý nghĩa thống kê và do đó phải được kiểm tra kỹ hơn.

Vì Excel không có bất kỳ giải pháp tích hợp nào để cung cấp, bạn sẽ phải tự vẽ nó. Đó là lý do tại sao chúng tôi phát triển Phần bổ trợ Trình tạo Biểu đồ, một công cụ cho phép bạn tạo các biểu đồ Excel nâng cao chỉ trong vài cú nhấp chuột.

Trong hướng dẫn từng bước này, bạn sẽ học cách tạo đường cong hình chuông phân phối chuẩn trong Excel từ cơ bản:

Để vẽ một đường cong Gaussian, bạn cần biết hai điều:

• Nghĩa (còn được gọi là tiêu chuẩn đo lường). Điều này xác định trung tâm của đường cong - đến lượt nó, đặc trưng cho vị trí của đường cong.
• Độ lệch chuẩn (SD) của các phép đo. Điều này xác định mức độ lan truyền dữ liệu của bạn trong phân phối chuẩn - hoặc bằng tiếng Anh đơn giản, đường cong phải rộng như thế nào. Ví dụ, trong đường cong hình chuông được hiển thị ở trên, một độ lệch chuẩn của giá trị trung bình thể hiện phạm vi giữa điểm thi là 53 và 85.

SD càng thấp, đường cong càng cao và dữ liệu của bạn sẽ càng ít bị dàn trải và ngược lại.

Điều đáng nói là quy tắc 68-95-99,7 có thể được áp dụng cho bất kỳ đường cong phân phối chuẩn nào, có nghĩa là khoảng 68% dữ liệu của bạn sẽ được đặt trong phạm vi một SD cách xa giá trị trung bình, 95% trong hai SD và 99,7% trong ba SD.

Bây giờ bạn đã biết những điều cơ bản, hãy chuyển từ lý thuyết sang thực hành.

Bắt đầu

Đối với mục đích minh họa, giả sử bạn có điểm kiểm tra của 200 học sinh và muốn cho điểm họ "theo đường cong", nghĩa là điểm của học sinh sẽ dựa trên thành tích tương đối của họ so với phần còn lại của lớp:

Bước # 1: Tìm giá trị trung bình.

Thông thường, bạn được cung cấp các giá trị trung bình và SD ngay từ đầu, nhưng nếu không phải như vậy, bạn có thể dễ dàng tính toán các giá trị này chỉ trong một vài bước đơn giản. Hãy giải quyết vấn đề trung bình trước.

Vì giá trị trung bình cho biết giá trị trung bình của một mẫu hoặc tập hợp dữ liệu, bạn có thể tìm phép đo tiêu chuẩn của mình bằng cách sử dụng hàm AVERAGE.

Nhập công thức sau vào bất kỳ ô trống nào (F1 trong ví dụ này) bên cạnh dữ liệu thực tế của bạn (cột A và NS) để tính điểm trung bình của các bài thi trong tập dữ liệu:

1	= AVERAGE (B2: B201)

Một lưu ý nhanh: thường xuyên hơn không, bạn có thể cần làm tròn đầu ra công thức. Để làm điều đó, chỉ cần bọc nó trong hàm ROUND như sau:

1	= ROUND (AVERAGE (B2: B201), 0)

Bước # 2: Tìm độ lệch chuẩn.

Một xuống, một để đi. May mắn thay, Excel có một chức năng đặc biệt để thực hiện tất cả công việc khó khăn là tìm ra độ lệch chuẩn cho bạn:

1	= STDEV.P (B2: B201)

Một lần nữa, công thức chọn tất cả các giá trị từ phạm vi ô được chỉ định (B2: B201) và tính toán độ lệch chuẩn của nó - cũng đừng quên làm tròn kết quả đầu ra.

1	= ROUND (STDEV.P (B2: B201), 0)

Bước # 3: Thiết lập các giá trị trục x cho đường cong.

Về cơ bản, biểu đồ tạo thành một số lượng lớn các khoảng (coi chúng như các bước) được nối với nhau bằng một đường để tạo ra một đường cong mượt mà.

Trong trường hợp của chúng tôi, các giá trị trục x sẽ được sử dụng để minh họa một điểm thi cụ thể trong khi các giá trị trục y sẽ cho chúng ta biết xác suất sinh viên đạt được điểm đó trong kỳ thi.

Về mặt kỹ thuật, bạn có thể bao gồm nhiều khoảng thời gian tùy thích - bạn có thể dễ dàng xóa dữ liệu thừa sau này bằng cách sửa đổi tỷ lệ trục ngang. Chỉ cần đảm bảo rằng bạn chọn một phạm vi sẽ kết hợp ba độ lệch chuẩn.

Hãy bắt đầu đếm tại một số điểm (vì không có cách nào để học sinh có thể bị điểm thi âm) và đạt đến con số 150-không thực sự quan trọng cho dù là 150 hay 1500-để thiết lập một bảng trợ giúp khác.

1. Chọn bất kỳ ô trống nào bên dưới dữ liệu biểu đồ (chẳng hạn như E 4) và loại “1,” giá trị xác định khoảng thời gian đầu tiên.
2. Điều hướng đến Nhà chuyển hướng.
3. bên trong Chỉnh sửa nhóm, chọn “Lấp đầy.”
4. Dưới "Sê-ri trong," lựa chọn "Cột.”
5. Vì "Giá trị bước," kiểu “1.” Giá trị này xác định các gia số sẽ được tự động cộng cho đến khi Excel đạt đến khoảng thời gian cuối cùng.
6. Vì "Giá trị dừng," kiểu "150,” giá trị đại diện cho khoảng thời gian cuối cùng và nhấp vào “VÂNG.”

Thật kỳ diệu, 149 ô trong cột E (E5: E153) đã được điền với các giá trị từ 2 đến 150.

LƯU Ý: Không ẩn các ô dữ liệu gốc như được hiển thị trên ảnh chụp màn hình. Nếu không, kỹ thuật sẽ không hoạt động.

Bước # 4: Tính toán các giá trị phân phối chuẩn cho mọi giá trị trục x.

Bây giờ, hãy tìm các giá trị phân phối chuẩn-xác suất sinh viên đạt được một điểm thi nhất định được biểu thị bằng một giá trị trục x cụ thể-cho mỗi khoảng thời gian. May mắn thay cho bạn, Excel có workhorse để thực hiện tất cả các phép tính này cho bạn: hàm NORM.DIST.

Nhập công thức sau vào ô bên phải (F4) của khoảng thời gian đầu tiên của bạn (E 4):

1	= NORM.DIST (E4, $ F $ 1, $ F $ 2, FALSE)

Đây là phiên bản đã giải mã để giúp bạn điều chỉnh cho phù hợp:

1	= NORM.DIST ([khoảng đầu tiên], [giá trị trung bình (tham chiếu tuyệt đối)], [độ lệch chuẩn (tham chiếu tuyệt đối), FALSE)

Bạn khóa các giá trị trung bình và SD để bạn có thể dễ dàng thực thi công thức cho các khoảng thời gian còn lại (E5: E153).

Bây giờ, hãy nhấp đúp vào ô điều khiển điền để sao chép công thức vào phần còn lại của các ô (F5: F153).

Bước # 5: Tạo một biểu đồ phân tán với các đường mịn.

Cuối cùng, thời gian để xây dựng đường cong chuông đã đến:

1. Chọn bất kỳ giá trị nào trong bảng trợ giúp có chứa các giá trị trục x và y (E4: F153).
2. Đi đến Chèn chuyển hướng.
3. Nhấn vào "Chèn Scatter (X, Y) hoặc Biểu đồ bong bóng" cái nút.
4. Lựa chọn "Phân tán với Đường trơn. ”

Bước # 6: Thiết lập bảng nhãn.

Về mặt kỹ thuật, bạn có đường cong chuông của bạn. Nhưng nó sẽ khó đọc vì nó thiếu bất kỳ dữ liệu nào mô tả nó.

Hãy làm cho phân phối chuẩn nhiều thông tin hơn bằng cách thêm các nhãn minh họa tất cả các giá trị độ lệch chuẩn bên dưới và trên giá trị trung bình (bạn cũng có thể sử dụng chúng để hiển thị điểm số z thay thế).

Đối với điều đó, hãy thiết lập một bảng trợ giúp khác như sau:

Đầu tiên, sao chép giá trị Trung bình (F1) bên cạnh ô tương ứng trong cột X-Giá trị (I5).

Tiếp theo, tính toán các giá trị độ lệch chuẩn dưới giá trị trung bình bằng cách nhập công thức đơn giản này vào ô I4:

1	= I5- $ F $ 2

Nói một cách đơn giản, công thức trừ tổng giá trị độ lệch chuẩn trước đó khỏi giá trị trung bình. Bây giờ, hãy kéo chốt điền lên trên để sao chép công thức vào hai ô còn lại (I2: I3).

Lặp lại quy trình tương tự đối với độ lệch chuẩn trên mức trung bình bằng cách sử dụng công thức phản chiếu:

1	= I5 + $ F $ 2

Theo cách tương tự, hãy thực thi công thức cho hai ô còn lại (I7: I8).

Cuối cùng, điền vào các giá trị nhãn trục y (J2: J8) với các số không khi bạn muốn đánh dấu dữ liệu được đặt trên trục hoành.

Bước # 7: Chèn dữ liệu nhãn vào biểu đồ.

Bây giờ, hãy thêm tất cả dữ liệu bạn đã chuẩn bị. Nhấp chuột phải vào sơ đồ biểu đồ và chọn “Chọn Dữ liệu.”

Trong hộp thoại bật lên, hãy chọn “Thêm vào.”

Đánh dấu các phạm vi ô tương ứng từ bảng trợ giúp-I2: I8 vì "Giá trị chuỗi X" và J2: J8 vì "Giá trị chuỗi Y ”-và nhấp vào “VÂNG.”

Bước # 8: Thay đổi loại biểu đồ của chuỗi nhãn.

Bước tiếp theo của chúng tôi là thay đổi loại biểu đồ của chuỗi mới được thêm vào để làm cho các điểm đánh dấu dữ liệu xuất hiện dưới dạng dấu chấm. Để làm điều đó, hãy nhấp chuột phải vào sơ đồ biểu đồ và chọn “Thay đổi loại biểu đồ.”

Tiếp theo, thiết kế một biểu đồ kết hợp:

1. Điều hướng đến Kết hợp chuyển hướng.
2. Vì Loạt “Series2”, thay đổi "Loại biểu đồ" đến "Tiêu tan.”
   • Lưu ý: Đảm bảo rằng “Series1”Vẫn là“Phân tán với các đường mịn. ” Đôi khi Excel sẽ thay đổi nó khi bạn thực hiện Kết hợp Đồng thời đảm bảo rằng “Series1”Không được đẩy đến Trục phụ - hộp kiểm bên cạnh loại biểu đồ sẽ không được đánh dấu.
3. Nhấp chuột "VÂNG.”

Bước # 9: Sửa đổi tỷ lệ trục hoành.

Căn giữa biểu đồ trên đường cong hình chuông bằng cách điều chỉnh tỷ lệ trục hoành. Nhấp chuột phải vào trục hoành và chọn “Định dạng trục”Từ menu.

Khi ngăn tác vụ xuất hiện, hãy làm như sau:

• Đi đến Tùy chọn trục chuyển hướng.
• Đặt Giới hạn tối thiểu có giá trị đối với "15.”
• Đặt Giới hạn tối đa có giá trị đối với "125.”

Bạn có thể điều chỉnh phạm vi tỷ lệ trục theo cách bạn thấy phù hợp, nhưng vì bạn biết phạm vi độ lệch chuẩn, hãy đặt các giá trị Bounds cách xa mỗi độ lệch chuẩn thứ ba một chút để hiển thị “đuôi” của đường cong.

Bước # 10: Chèn và định vị các nhãn dữ liệu tùy chỉnh.

Khi bạn hoàn thiện biểu đồ của mình, hãy đảm bảo thêm các nhãn dữ liệu tùy chỉnh. Đầu tiên, nhấp chuột phải vào bất kỳ dấu chấm nào đại diện cho Loạt “Series2” và chọn “Thêm nhãn dữ liệu.”

Tiếp theo, thay thế các nhãn mặc định bằng các nhãn bạn đã thiết lập trước đó và đặt chúng phía trên các điểm đánh dấu dữ liệu.

1. Nhấp chuột phải vào bất kỳ Loạt “Series2” nhãn dữ liệu.
2. Lựa chọn "Định dạng Nhãn Dữ liệu.”
3. Trong ngăn tác vụ, chuyển sang Tùy chọn nhãn chuyển hướng.
4. Kiểm tra "Giá trị X" hộp.
5. Bỏ chọn “Giá trị Y" hộp.
6. Dưới "Vị trí Nhãn," lựa chọn "Bên trên.”

Ngoài ra, bây giờ bạn có thể xóa các đường lưới (nhấp chuột phải vào chúng> Xóa).

Bước # 11: Tô màu lại các điểm đánh dấu dữ liệu (tùy chọn).

Cuối cùng, tô màu lại các chấm để giúp chúng phù hợp với kiểu biểu đồ của bạn.

1. Nhấp chuột phải vào bất kỳ Loạt “Series2” nhãn dữ liệu.
2. Nhấn vào "Lấp đầy" cái nút.
3. Chọn màu của bạn từ bảng màu xuất hiện.

Ngoài ra, hãy xóa các đường viền xung quanh các dấu chấm:

1. Nhấp chuột phải vào cùng một điểm đánh dấu dữ liệu và chọn “Đề cương.”
2. Lựa chọn "Không có đường viền.”

Bước # 12: Thêm các đường thẳng đứng (tùy chọn).

Như một điều chỉnh cuối cùng, bạn có thể thêm các đường thẳng đứng vào biểu đồ để giúp nhấn mạnh các giá trị SD.

• Chọn ô biểu đồ (theo cách đó, các đường sẽ được chèn trực tiếp vào biểu đồ).
• Đi đến Chèn chuyển hướng.
• Nhấn vào "Hình dạng" cái nút.
• Lựa chọn "Hàng.”

Giữ "SỰ THAY ĐỔI" trong khi kéo chuột để vẽ các đường thẳng đứng hoàn hảo từ mỗi dấu chấm đến vị trí mỗi đường giao nhau với đường cong hình chuông.

Thay đổi tiêu đề biểu đồ và đường cong hình chuông được cải thiện của bạn đã sẵn sàng hiển thị dữ liệu phân phối có giá trị của bạn.

Và đó là cách bạn làm điều đó. Bây giờ bạn có thể chọn bất kỳ tập dữ liệu nào và tạo đường cong hình chuông phân phối chuẩn theo các bước đơn giản sau!