Trong hướng dẫn này, bạn sẽ khám phá cách tạo biểu đồ cột trong Google Sheets kèm theo việc chồng đè một đường cong phân phối chuẩn, giống như hình ảnh trên, bằng cách sử dụng Google Sheets.
Kỹ thuật trực quan này rất hữu ích để xác định liệu dữ liệu của bạn có phân phối chuẩn, không đối xứng hoặc chỉ đơn giản là phân tán hay không.
Đâu là biểu đồ cột?
Biểu đồ cột là biểu đồ thể hiện phân phối của một tập hợp dữ liệu.
Trong ví dụ này, tôi có 1000 điểm thi nằm trong khoảng từ 0 đến 100 và tôi muốn xem phân phối của các điểm này. Điểm trung bình là bao nhiêu? Có nhiều học sinh đạt điểm cao hơn hay thấp hơn? Các điểm của học sinh sẽ tập trung quanh giá trị trung bình như thế nào? Các điểm có phân phối chuẩn hay không đối xứng?
Đâu là đường cong phân phối chuẩn?
Đường cong phân phối chuẩn là biểu đồ thể hiện lý thuyết phân phối chuẩn, mà cho biết “… trung bình của các biến ngẫu nhiên, độc lập được chọn một cách ngẫu nhiên từ phân phối độc lập, hội tụ theo phân phối chuẩn, tức là trở thành phân phối chuẩn khi số lượng biến ngẫu nhiên đủ lớn”.
Có vẻ như khá phức tạp, nhưng thực tế, dữ liệu hội tụ quanh giá trị trung bình mà không có bất kỳ độ lệch về bên trái hoặc bên phải. Điều này có nghĩa là chúng ta biết xác suất của việc có bao nhiêu giá trị xảy ra gần giá trị trung bình.
Chúng ta mong đợi khoảng 68% giá trị nằm trong một độ lệch chuẩn của giá trị trung bình và 95% nằm trong hai độ lệch chuẩn. Giá trị nằm ngoài hai độ lệch chuẩn được coi là các giá trị ngoại lệ.
Chúng ta mong đợi điểm thi của mình khá gần với phân phối chuẩn, nhưng hãy xem xét điều này qua hình ảnh (rất khó để nhìn thấy chỉ từ dữ liệu).
Vậy giờ chúng ta hãy xem làm thế nào để tạo biểu đồ cột trong Google Sheets và chồng đè đường cong phân phối chuẩn, giống như hình ảnh đầu tiên trên.
Làm thế nào để tạo biểu đồ cột trong Google Sheets
Bước 1: Dữ liệu gốc
Sao chép dữ liệu gốc từ đây vào bảng tính Google trống của bạn. Đây là một danh sách 1000 điểm thi trong khoảng từ 0 đến 100, và chúng ta sẽ nghiên cứu phân phối của các điểm này.
Bước 2: Đặt tên cho phạm vi này
Tạo một phạm vi được đặt tên từ dữ liệu gốc này, gọi là “danh sách điểm”, để đơn giản hóa công việc của chúng ta. Chọn tất cả dữ liệu trong cột A, tức là các ô A1:A1000, sau đó nhấn chuột phải và chọn “Dữ liệu > Phạm vi được đặt tên…” và gọi phạm vi này là “danh sách điểm”.
Bước 3: Thống kê tổng hợp
Tạo một bảng tổng hợp nhỏ với giá trị trung bình, trung vị, mode và độ lệch chuẩn của dữ liệu của chúng ta. Công thức như sau:
- Trung bình:
- Trung vị:
- Mode:
- Độ lệch chuẩn:
Bước 4: Tạo các khoảng tần số
Tạo các khoảng tần số từ 0 đến 100 với khoảng cách 5. Đặt giá trị 0 vào ô F2, sau đó sử dụng công thức này để nhanh chóng điền vào các khoảng còn lại:
(Thêm 5 cho ô phía trên). Đặt tên phạm vi này là “khoảng tần số”.
Bước 5: Tính toán phân phối chuẩn
Tạo các giá trị của đường cong phân phối chuẩn.
Google Sheets có một công thức NORMDIST tính giá trị của hàm phân phối chuẩn cho một giá trị cụ thể, trung bình và độ lệch chuẩn. Chúng ta đã tính giá trị trung bình và độ lệch chuẩn ở bước 3, và chúng ta sẽ sử dụng các giá trị của khoảng tần số ở bước 4 trong công thức.
Đặt công thức này vào ô G2:
Kéo thả công thức này xuống ô G22 để điền tất cả các công thức NORMDIST.
Bước 6: Đường cong phân phối chuẩn
Hãy xem đường cong phân phối chuẩn như thế nào với dữ liệu này.
Chọn cột khoảng tần số và cột NORMDIST, sau đó chọn “Chèn > Biểu đồ” và chọn “Biểu đồ dạng đường” và làm cho nó nhẵn.
Bạn sẽ nhận được kết quả như sau:
Đây là một đường cong phân phối chuẩn, tập trung xung quanh giá trị trung bình của chúng ta là 56.9. Chúc mừng!
Bây giờ chúng ta cần tính phân phối của 1000 điểm thi cho biểu đồ cột của chúng ta.
Vì chúng ta sẽ tạo một biểu đồ mới với histogram và đường cong chuẩn đè lên (dễ hơn là sửa biểu đồ hiện tại), bạn có thể đặt biểu đồ phân phối chuẩn này sang một bên hoặc xóa nó đi.
Bước 7: Công thức tần số
Để trống cột H trong lúc này (chúng ta sẽ điền vào sau).
Trong cột I, hãy sử dụng công thức FREQUENCY để gán 1000 điểm của chúng ta vào các khoả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 ma trận. Nó sẽ điền tất cả các ô và gán tất cả các điểm vào các khoảng chính xác:
Nếu bạn mới bắt đầu với các công thức ma trận, hãy xem bài viết này: Cách làm việc với các công thức ma trận trong Google Sheets?
Bước 8: Sao chép giá trị
Sao chép cột giá trị tần số này vào cột kế bên J (chúng ta sẽ cần nó cho biểu đồ của chúng ta).
Mẹo chuyên gia: Bạn có thể đơn giản sao chép I1:I2 vào J1:J2, điều này sẽ điền toàn bộ cột với các giá trị.
Bước 9: Điều chỉnh đường cong phân phối chuẩn
Chúng ta cần điều chỉnh lại đường cong phân phối chuẩn để nó được hiển thị trên cùng một tỷ lệ với histogram. Vì chúng ta có 1000 giá trị trong khoảng 5, tỷ lệ với chúng là 5000. Điều này có nghĩa là khi nhân các giá trị của phân phối chuẩn với 5000, chúng sẽ có thể so sánh được với các giá trị của histogram trên cùng một trục. Hơn nữa, chúng sẽ cộng lại thành 1000, tương ứng với số lượng giá trị trong mẫu của chúng ta.
Cho nên, trong cột trống H, thêm công thức sau và kéo thả xuống ô H22:
Bảng dữ liệu của chúng ta hiện giờ nhìn như thế này:
Bước 10: Tạo biểu đồ
Đây là lúc chúng ta cuối cùng thấy cách tạo biểu đồ cột trong Google Sheets!
Lưu ý: Ảnh chụp màn hình được chia sẻ dưới đây hiển thị trình chỉnh sửa biểu đồ cũ. Trình chỉnh sửa biểu đồ mới mở trong một cửa sổ bên vàng, nhưng các bước và tùy chọn tương tự.
Nhấp và giữ phím Ctrl (trên PC) hoặc Cmd (trên Mac) để chọn cột dữ liệu khoảng tần số, phân phối chuẩn và hai cột dữ liệu của histogram, nhưng trừ cột các công thức NORMDIST như sau:
Tiếp theo, chọn “Chèn > Biểu đồ” và chọn “Biểu đồ kết hợp”:
Chọn tùy chọn để sử dụng cột F làm nhãn:
Trong tab Tùy chỉnh, xóa tiêu đề và chú thích. Chọn tùy chọn Làm mượt:
Chọn trục dọc. Xóa tên trục. Đặt phạm vi từ 0 đến 150 và đặt các đường chia chính thành 4.
Trong phần Chuỗi trong menu Tùy chỉnh, chọn chuỗi “Phân phối chuẩn” và chuyển từ cột sang dòng, để đồ thị của bạn trông giống như thế này:
Tiếp theo, chọn chuỗi “Histogram” và chuyển từ dòng sang cột:
Sau đó, chọn chuỗi “Histogram 2” và chuyển từ dòng sang dạng thang:
Sau đó, thay đổi màu thành màu đỏ, độ dày của đường thành 1px và độ mờ thành 70%, để biểu đồ của chúng ta trông giống một biểu đồ cột (đây là lý do tại sao chúng ta cần hai bản sao của cột tần số):
Cuối cùng, đặt kích thước phông chữ của các nhãn trục thành 10, sau đó nhấp vào vùng biểu đồ để di chuyển và thay đổi kích thước bằng cách kéo các cạnh ra bên ngoài, để nó lấp đầy toàn bộ vùng biểu đồ của chúng ta:
Và đó! Bây giờ bạn đã học cách tạo biểu đồ cột trong Google Sheets, kèm theo đường cong phân phối chuẩn chồng đè lên:
Voulez-vous votre propre copie de ce graphique d’histogramme ? Cliquez ici để truy cập vào bản sao của mẫu này.
Kết luận
Tóm lại, chúng ta có thể thấy rằng dữ liệu điểm thi của chúng ta rất gần với phân phối chuẩn. Yay!
Nếu nhìn kỹ, chúng ta nhận thấy chúng hơi không đối xứng về phía trái, tức là họ có một đuôi dài hơn ở phía trái, phân tán lớn hơn ở phía trái. Bạn có thể thấy có khoảng trống giữa các thanh cột màu đỏ và đường cong màu xanh dương phía bên trái, nhưng các thanh cột màu đỏ chồng lên đường cong màu xanh bên phải. Điều này khá tinh tế.
