Hướng dẫn how do you distribute data in google sheets? - làm thế nào để bạn phân phối dữ liệu trong trang tính của google?

Trong hướng dẫn này, bạn sẽ học cách tạo biểu đồ trong Google Sheets với đường cong phân phối bình thường được phủ lên, như thể hiện trong hình ảnh trên, sử dụng Google Sheets.

Nó là một kỹ thuật trực quan thực sự hữu ích để xác định xem dữ liệu của bạn có được phân phối bình thường hay không, bị lệch hay chỉ ở khắp nơi.

Biểu đồ là gì?

Biểu đồ là một biểu diễn đồ họa của phân phối bộ dữ liệu.

Trong ví dụ này, tôi có 1.000 điểm thi từ 0 đến 100 và tôi muốn xem phân phối các điểm đó là gì. Điểm trung bình là gì? Có nhiều học sinh ghi điểm cao hay thấp? Làm thế nào phân cụm xung quanh trung bình là điểm số của sinh viên? Điểm số thường được phân phối hoặc sai lệch?

Đường cong phân phối bình thường là gì?

Đường cong phân phối bình thường là một biểu diễn đồ họa của định lý phân phối bình thường nói rằng, trung bình của các biến ngẫu nhiên được rút ra độc lập từ các phân phối độc lập hội tụ trong phân phối sang bình thường, nghĩa là được phân phối bình thường khi số lượng biến ngẫu nhiên đủ lớn .

Một chút của một miệng, nhưng về bản chất, dữ liệu hội tụ xung quanh giá trị trung bình (trung bình) không bị lệch ở bên trái hoặc bên phải. Điều đó có nghĩa là chúng tôi biết xác suất có bao nhiêu giá trị xảy ra gần với giá trị trung bình.

Chúng tôi hy vọng 68% giá trị sẽ nằm trong một độ lệch chuẩn của giá trị trung bình và 95% sẽ nằm trong hai độ lệch chuẩn. Các giá trị bên ngoài hai độ lệch chuẩn được coi là ngoại lệ.

Chúng tôi hy vọng điểm thi của chúng tôi sẽ khá gần với phân phối bình thường, nhưng hãy để xác nhận rằng bằng đồ họa (nó khó nhìn thấy từ dữ liệu!).

Hãy cùng xem cách tạo biểu đồ trong Google Sheets và cách phủ một đường cong phân phối bình thường, như thể hiện trong hình ảnh đầu tiên ở trên.

Bước 1: Dữ liệu thô

Sao chép điểm dữ liệu thô từ đây vào tờ Google trống của riêng bạn. Nó có một danh sách 1.000 điểm thi từ 0 đến 100 và chúng tôi sẽ xem xét việc phân phối các điểm đó.

Bước 2: Tên phạm vi đó

Tạo một phạm vi được đặt tên từ các điểm dữ liệu thô này, được gọi là scores, để làm cho cuộc sống của chúng ta dễ dàng hơn. Đánh dấu tất cả các dữ liệu trong cột A, tức là các ô A1: A1000, sau đó nhấp vào Dữ liệu Menu> Phạm vi có tên và gọi phạm vi scores:scores, to make our life easier. Highlight all the data in column A, i.e. cells A1:A1000, then click on the menu Data > Named ranges… and call the range scores:

Bước 3: Thống kê tóm tắt

Thiết lập một bảng tóm tắt nhỏ với giá trị trung bình, trung bình, chế độ và độ lệch chuẩn của dân số chúng ta. Các công thức là:

Mean:

=AVERAGE(scores)

Median:

=MEDIAN(scores)

Mode:

=MODE(scores)

Độ lệch chuẩn:

=STDEVP(scores)

Bước 4: Tạo các thùng tần số

Thiết lập các thùng tần số, từ 0 đến 100 với các khoảng 5. Đặt 0 vào ô F2 và sau đó bạn có thể sử dụng công thức này để nhanh chóng điền vào các thùng còn lại:

=F4 + 5

(Nó thêm 5 vào ô ở trên). Đặt tên cho phạm vi này bins.bins.

Bước 5: Tính toán phân phối bình thường

Hãy để thiết lập các giá trị đường cong phân phối bình thường.

Google Sheets có một quy tắc công thức, tính toán giá trị của hàm phân phối bình thường cho một giá trị nhất định, độ lệch trung bình và độ lệch chuẩn. Chúng tôi đã tính toán độ lệch trung bình và độ lệch chuẩn trong bước 3 và chúng tôi sẽ sử dụng các giá trị thùng từ bước 4 trong công thức.

Trong G2, đặt công thức:

=NORMDIST(F2,$D$1,$D$4,FALSE)

Kéo tất cả các cách xuống G22 để điền vào toàn bộ cột Công thức Normdist:Normdist formula column:

Bước 6: Đường cong phân phối bình thường

Hãy cùng xem đường cong phân phối bình thường trông như thế nào với dữ liệu này.

Chọn cột Bins và cột Normdist sau đó chèn> Biểu đồ và chọn Biểu đồ dòng và làm cho nó mịn:Insert > Chart and select line chart, and make it smooth:

Bạn sẽ có một đầu ra như thế này:

Đó là một đường cong phân phối bình thường, xung quanh giá trị trung bình của chúng tôi là 56,9. Công việc tuyệt vời!

Bây giờ chúng tôi cần tính toán phân phối 1.000 điểm thi cho biểu đồ biểu đồ của chúng tôi.

Khi chúng tôi sẽ tạo ra một biểu đồ hoàn toàn mới với biểu đồ và đường cong bình thường được phủ lên (dễ dàng hơn so với sửa đổi bảng này), bạn có thể đặt biểu đồ phân phối bình thường này sang một bên bây giờ hoặc xóa nó.

Bước 7: Công thức tần số

Để lại cột H trống ngay bây giờ (chúng tôi sẽ điền vào điều này trong thời gian ngắn).

Trong cột I, hãy để sử dụng công thức tần số để gán 1000 điểm của chúng tôi cho các thùng tần số. Nhập công thức sau vào ô I2 và nhấn Ctrl + Shift + Enter (trên PC) hoặc CMD + Shift + Enter (trên Mac), để tạo công thức mảng. Nó sẽ điền vào toàn bộ cột và gán tất cả các điểm vào các thùng chính xác:

=ArrayFormula(FREQUENCY(scores,bins))

Nếu bạn mới sử dụng các công thức mảng, hãy xem bài đăng này: Làm thế nào để các công thức mảng hoạt động trong Google Sheets?

Bước 8: Sao chép các giá trị

Sao chép cột này của các giá trị tần số vào cột liền kề J (chúng ta cần điều này cho biểu đồ của chúng ta).

Mẹo chuyên nghiệp: Bạn chỉ có thể sao chép I1: I2 vào J1: J2, nó sẽ điền vào toàn bộ cột với các giá trị. you can just copy I1:I2 into J1:J2, it’ll fill out the whole column with values.

Bước 9: Chiếc quy mô đường cong phân phối bình thường

Chúng ta cần mở rộng quy mô đường cong phân phối bình thường của chúng ta để nó hiển thị trên cùng một tỷ lệ với biểu đồ. Vì chúng tôi có 1.000 giá trị trong các thùng 5, nên hệ số tỷ lệ của chúng tôi là 5.000. Có nghĩa là, khi tôi nhân các giá trị phân phối bình thường với 5.000, chúng sẽ có thể so sánh với các giá trị biểu đồ trên cùng một trục. Ngoài ra, họ sẽ tổng hợp với 1.000 phù hợp với số lượng giá trị trong dân số của chúng tôi.

Vì vậy, trong cột trống H, thêm công thức sau và kéo xuống H22:

scores0

Bảng dữ liệu đã hoàn thành của chúng tôi bây giờ trông giống như:

Bước 10: Tạo biểu đồ

Đây là nơi chúng ta thấy cách tạo biểu đồ trong Google Sheets cuối cùng!

Lưu ý: Các ảnh chụp màn hình được chia sẻ dưới đây cho thấy trình soạn thảo biểu đồ cũ. Trình chỉnh sửa biểu đồ mới mở ở một khung bên, nhưng các bước và tùy chọn về cơ bản là giống nhau.the screenshots shared below show the old chart editor. The new chart editor opens in a side pane, but the steps and options are essentially the same.

Giữ CTRL (PC) hoặc CMD (MAC) để làm nổi bật cột dữ liệu Bins, phân phối bình thường và hai cột biểu đồ, nhưng bỏ qua cột Công thức Normdist, như sau:

Sau đó chèn> biểu đồ và chọn biểu đồ combo:Insert > Chart, and select Combo chart:

Chọn tùy chọn sử dụng cột F làm nhãn:

Trong tab Tùy chỉnh, loại bỏ tiêu đề và truyền thuyết. Chọn tùy chọn suôn sẻ:

Chọn trục dọc. Xóa tên trục. Được đặt để có phạm vi từ 0 đến 150 và đặt các đường lưới chính thành 4.

Trong phần loạt của menu tùy chỉnh, chọn chuỗi phân phối bình thường và thay đổi từ các cột này sang cột khác, vì vậy biểu đồ của bạn trông như thế này:Normal Distribution series, and change from columns to line, so your chart looks like this:

Tiếp theo, chọn chuỗi biểu đồ và thay đổi loại từ dòng sang cột:

Chọn chuỗi biểu đồ 2 và thay đổi loại từ dòng này sang khu vực bước khác:

Sau đó, thay đổi màu thành màu đỏ, độ dày dòng thành 1px và độ mờ thành 70%, để làm cho biểu đồ của chúng tôi trông giống như một biểu đồ (đây là lý do tại sao chúng tôi cần hai bản sao của cột tần số):

Tidy cuối cùng: Đặt kích thước phông chữ của nhãn trục thành 10, sau đó nhấp vào khu vực biểu đồ để di chuyển và thay đổi kích thước CNTT bằng cách kéo các cạnh ra ngoài, để nó điền vào toàn bộ khung vẽ biểu đồ của chúng tôi:

Voila! Bây giờ bạn đã học được cách tạo biểu đồ trong Google Sheets, được phủ lên với một đường cong phân phối bình thường:

Muốn bản sao của riêng bạn của biểu đồ biểu đồ này? Nhấn vào đây để truy cập bản sao mẫu này >>
Click here to access your copy of this template >>

Conclusion:

Để kết luận, chúng ta có thể thấy dữ liệu điểm thi của chúng ta rất gần với phân phối bình thường. Hoan hô!

Nếu chúng ta nhìn kỹ, nó bị lệch rất, rất nhẹ về phía bên trái, tức là nó có đuôi dài hơn ở bên trái, lan rộng hơn ở bên trái. Xem làm thế nào có không gian giữa các thanh màu đỏ và đường màu xanh ở phía bên trái, nhưng các thanh màu đỏ chồng lên đường cong màu xanh ở phía bên phải. Nó tinh tế mặc dù.