Tạo phân phối bình thường trong Excel

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

Biểu đồ giúp chúng tôi phân tích xem một giá trị cụ thể có phải là một phần của biến thể dự kiến ​​hay có ý nghĩa thống kê hay không 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 nên bạn sẽ phải tự vẽ đồ thị. Đó 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 biểu đồ Excel nâng cao chỉ bằng vài cú nhấp chuột

Trong hướng dẫn từng bước này, bạn sẽ tìm hiểu cách tạo đường cong chuông phân phối chuẩn trong Excel từ đầu

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

• Giá trị trung bình (còn được gọi là phép đo tiêu chuẩn). Điều này xác định tâm của đường cong, do đó, đặ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 bình thường—hay nói một cách đơn giản bằng tiếng Anh, độ rộng của đường cong. Chẳng hạn, 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 biểu thị 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ẽ bị phân tán càng ít và ngược lại

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

Để minh họa, giả sử bạn có điểm kiểm tra của 200 học sinh và muốn chấm điểm cho 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 ý nghĩa

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ỉ bằng một vài bước đơn giản. Trước tiên hãy giải quyết ý nghĩa

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, nên 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à B) để tính điểm trung bình của các bài kiểm tra trong tập dữ liệu

=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 kết quả công thức. Để làm điều đó, chỉ cần bọc nó trong hàm ROUND như sau

=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 bẩn thỉu là tìm độ lệch chuẩn cho bạn

=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ó—đừng quên làm tròn kết quả

=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 (nghĩ về 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 bài kiểm tra cụ thể trong khi các giá trị trục y sẽ cho chúng tôi biết xác suất học sinh đạt được điểm đó trong bài kiểm tra

Về mặt kỹ thuật, bạn có thể bao gồm bao nhiêu khoảng tùy thích—bạn có thể dễ dàng xóa dữ liệu dư 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ừ một (vì không có cách nào học sinh có thể bị điểm âm) và tăng dần lên đến 150 — thực sự không quan trọng 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ư E4) và nhập “1”, giá trị xác định khoảng đầu tiên
2. Điều hướng đến tab Trang chủ
3. Trong nhóm Chỉnh sửa, chọn “Điền. ”
4. Trong “Sê-ri trong”, hãy chọn “Cột. ”
5. Đối với “Giá trị bước”, hãy nhập “1. ” Giá trị này xác định các khoảng tăng sẽ được tự động thêm cho đến khi Excel đạt đến khoảng cuối cùng
6. Đối với “Giá trị dừng”, hãy nhập “150”, giá trị đại diện cho khoảng thời gian cuối cùng và nhấp vào “OK. ”

Thật kỳ diệu, 149 ô ở cột E (E5. E153) đã được lấp đầy với các giá trị từ 2 đến 150

GHI CHÚ. 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 bình thường 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 của một học sinh đạt điểm bài kiểm tra nhất định được biểu thị bằng một giá trị trục x cụ thể—cho từng khoảng thời gian. Thật may mắn cho bạn, Excel có công cụ để thực hiện tất cả các phép tính này cho bạn. BÌNH THƯỜNG. chức năng 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 (E4)

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

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

=NORM.DIST([the first interval],[the mean(absolute reference)],[the standard deviation(absolute reference),FALSE)

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

Bây giờ, bấm đúp vào ô điều khiển điền để sao chép công thức vào các ô còn lại (F5. F153)

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

Cuối cùng, thời điểm để xây dựng đường cong hình 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à trục y (E4. F153)
2. Chuyển đến tab Chèn
3. Nhấp vào nút “Chèn biểu đồ phân tán (X, Y) hoặc bong bóng”
4. Chọn “Scatter with Smooth Lines. ”

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

Về mặt kỹ thuật, bạn có đường cong hình chuông. 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 có 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à bên trên giá trị trung bình (thay vào đó, bạn cũng có thể sử dụng chúng để hiển thị điểm z)

Đố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 Giá trị X (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

=I5-$F$2

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

Lặp lại quy trình tương tự cho các độ lệch chuẩn trên giá trị trung bình bằng cách sử dụng công thức nhân bản

=I5+$F$2

Theo cách tương tự, thực hiện công thức cho hai ô còn lại (I7. I8)

Cuối cùng, điền các giá trị nhãn trục y (J2. J8) với các số 0 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ờ, thêm tất cả dữ liệu bạn đã chuẩn bị. Nhấp chuột phải vào biểu đồ và chọn “Select Data. ”

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

Đánh dấu các phạm vi ô tương ứng từ bảng trợ giúp—I2. I8 cho “giá trị sê-ri X” và J2. J8 cho “Các giá trị sê-ri Y”—và nhấp vào “OK. ”

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 đó, nhấp chuột phải vào 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 tab Combo
2. Đối với Sê-ri “Sê-ri 2”, hãy thay đổi “Loại biểu đồ” thành “Scatter. ”
   • Ghi chú. Đảm bảo “Series1” vẫn là “Scatter with Smooth Lines. ” Đôi khi Excel sẽ thay đổi nó khi bạn tạo một Combo Ngoài ra, hãy đảm bảo rằng “Series1” không được đẩy vào Trục phụ—không nên đánh dấu hộp kiểm bên cạnh loại biểu đồ
3. Nhấp vào “OK. ”

Bước #9. Sửa đổi tỷ lệ trục ngang

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 ngang. Nhấp chuột phải vào trục ngang và chọn “Format Axis” từ menu

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

• Chuyển đến tab Tùy chọn trục
• Đặt giá trị Giới hạn Tối thiểu thành “15. ”
• Đặt giá trị Giới hạn Tối đa thành “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ị Giới hạn cách xa từng độ 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ị nhãn dữ liệu tùy chỉnh

Khi bạn hoàn thiện biểu đồ của mình, hãy nhớ 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 Series “Series2” và chọn “Add Data Labels. ”

Tiếp theo, thay thế các nhãn mặc định bằng 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ỳ nhãn dữ liệu Series “Series2” nào
2. Chọn “Định dạng nhãn dữ liệu. ”
3. Trong ngăn tác vụ, chuyển sang tab Tùy chọn Nhãn
4. Đánh dấu vào ô “Giá trị X”
5. Bỏ chọn hộp “Giá trị Y”
6. Trong “Vị trí nhãn”, chọ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 dấu 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ỳ nhãn dữ liệu Series “Series2” nào
2. Nhấp vào nút "Điền"
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 “Outline. ”
2. Chọn “Không có đường viền. ”

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

Là đ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 đồ)
• Chuyển đến tab Chèn
• Nhấp vào nút "Hình dạng"
• Chọn “Dòng. ”

Giữ phím “SHIFT” 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 nơi 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ải tiế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

Bạn có thể tạo phân phối bình thường trong excel không?

Hàm tạo dữ liệu phân phối chuẩn trong excel là gì?