Trang 69
SAU BÀI HỌC NÀY EM SẼ:
• Trình bày được dữ liệu thống kê bằng: bảng tần số/tần suất một, hai chiều, bảng thống kê theo số liệu ghép nhóm và bằng tổng hợp nhiều chiều.
| MỞ ĐẦU
Hình 12.1. Trung bình Tổng điểm theo giới tính
Hình 12.2. Tần suất (%) sinh viên theo giới tính và ngành học Em có thể sử dụng công cụ nào của phần mềm bảng tính để tạo các bảng này? |
1. MÔ TẢ SỐ LIỆU BẰNG PIVOTTABLE
| Hoạt động: Tìm hiểu các bảng thống kê tổng hợp Hãy mô tả một số bảng thống kê tổng hợp em đã được học trong môn Toán. |
Có nhiều dạng bảng tổng hợp mô tả dữ liệu thống kê, ví dụ, các bảng tần số/tần suất, bảng thống kê ghép nhóm hay bảng thống kê tổng hợp theo nhiều tiêu chí khác nhau (bảng tổng hợp nhiều chiều),... Các bảng thống kê tổng hợp này giúp ta hiểu rõ hơn về tính chất và đặc điểm của tập dữ liệu.
Cũng như các phần mềm bảng tính khác, Excel cũng cung cấp PivotTable – một công cụ mạnh mẽ để tạo các dạng bảng tổng hợp nêu trên. PivotTable giúp tổ chức và phân loại dữ liệu một cách linh hoạt, cho phép thực hiện các phân tích thống kê và tổng hợp dữ liệu thuận tiện.
a) Kích hoạt PivotTable
Trong mục này sẽ sử dụng dữ liệu vùng A1:H25 trong Hình 11.1 để minh hoạ.
Bước 1. Khởi tạo PivotTable.
– Đặt con trỏ vào ô bất kì của vùng dữ liệu.
– Chọn lệnh Insert → PivotTable.
– Chọn PivotTable (Hình 12.3).
Trang 70
Hình 12.3. Chọn PivotTable trong dải lệnh Insert
Bước 2. Chọn vùng dữ liệu cần lập bảng tổng hợp và vị trí hiển thị bảng. Hộp thoại tiếp theo (Hình 12.4) cho phép lựa chọn:
Hình 12.4. Hộp thoại chọn vùng dữ liệu và bằng đầu ra
– Table/Range: Địa chỉ vùng dữ liệu.
– Chọn vị trí hiển thị PivotTable: Existing Worksheet (bảng tổng hợp được tạo ở trang tính hiện thời).
– Location: Chọn vị trí góc trên bên trái của bảng tổng hợp (J2).
Bước 3. Chọn OK để Excel hiển thị hộp thoại PivotTable cho phép chọn các trường (biến) cho các chức năng.
b) Khai báo tham số tạo bảng PivotTable
Hộp thoại khai báo và chọn biến của PivotTable gồm hai phần (Hình 12.5).
a) Các trường/biến dữ liệu đã chọn
b) 1. Vùng chứa biến lọc; 2. Vùng chứa biến cột; 3. Vùng chứa biến động; 4. Vùng chứa biến tính toán
Hình 12.5. Giao diện chọn biến cho các PivotTable
– Biến lọc: Kéo thả từ danh sách biến vào vùng 1 (Hình 12.5b).
– Lựa chọn biến: Biến phân nhóm theo cột dòng kéo thả từ danh sách biến vào các vùng 2 (vùng chứa các biến cột) và vùng 3 (vùng chứa các biến dòng).
– Biến tính toán: Kéo thả từ danh sách biến vào vùng 4.
Việc thêm bớt, đổi chỗ, hoán đổi các biến dòng, cột thực hiện dễ dàng nhờ kéo thả các biến trong các vùng 2, 3 và 4.
Trang 71
c) Lựa chọn hàm và cách thức hiển thị
Với mỗi biến tính toàn trong vùng 4 có thể chọn nhiều lần, mỗi lần một đặc trưng (trung bình, trung vị, số phần tử, độ lệch tiêu chuẩn,...), hàm mặc định là Sum với biến định lượng
và Count với biến định tính. Các hàm cho các biến tính toán có thể được chọn trong hộp thoại Value Field Settings (Hình 12.6).
Hình 12.6. Bảng chọn hàm và hiển thị giá trị hàm
| Công cụ PivotTable trong Excel giúp tạo bảng tổng hợp trên cơ sở tóm tắt các đặc trưng của dữ liệu, phân loại dữ liệu theo nhiều cách khác nhau. |
Câu hỏi
1. Có thể chọn biến định tính đồng thời vừa là biến dòng/cột và vừa là biến tính toán được không?
2. Mỗi câu sau đây là đúng hay sai?
a) Các biến định tính có thể được chọn làm các biến dòng cột với mục đích chia nhóm theo dấu hiệu, mỗi biến chỉ chọn một lần vào một trong hai vùng 2 và 3.
b) Hàm duy nhất có nghĩa với biến định tính khi dùng làm biến tính toán (vùng 4) là hàm Count.
c) Đa số các nhãn đầu ra có thể sửa trực tiếp trên bảng hoặc trong khi chọn hàm cho mỗi biến.
2. THỰC HÀNH
Nhiệm vụ 1: Lập bảng tần số/tần suất một, hai chiều
Yêu cầu: Theo dữ liệu trong Hình 11.1, em hãy so sánh:
a) Số lượng và tỉ lệ sinh viên theo giới tính khác nhau như thế nào?
b) Số lượng và tỉ lệ sinh viên theo giới tính và ngành học khác nhau như thế nào?
Hướng dẫn:
a) Để so sánh số lượng và tỉ lệ sinh viên nam và sinh viên nữ, em có thể lập bảng tần số hoặc tần suất hoặc bảng gộp cả tần số và tần suất theo giới tính. Phần hướng dẫn
sau sử dụng công cụ PivotTable để lập bảng tần số và tần suất theo giới tính.
Bước 1: Tạo cột dữ liệu theo giới tính
– Kích hoạt PivotTable với dữ liệu Hình 11.1: Theo hướng dẫn ở Mục 1.a.
– Kéo thả biến Giới tính vào vùng 2. Giới tính trở thành biến cột.
Bước 2: Tạo các dòng dữ liệu tần số
– Kéo thả biến Giới tính vào vùng 4, vì Giới tính là biến định tính nên Excel mặc định chọn hàm Count.
Trang 72
– Trong vùng 4, nháy chuột vào Count of Giới tính, chọn Value Field Settings (Hình 12.7a). Trong hộp thoại Value Field Settings, sửa Custom Name thành Tần số.
Bước 3: Tạo các dòng dữ liệu lần suất
– Một lần nữa, kéo thả biến Giới tính vào vùng 4, vì Giới tính là biến định tính nên Excel mặc định chọn hàm Count.
– Trong vùng 4, nháy chuột vào dòng Count of Giới tính, chọn Value Field Settings như Hình 12.7a. Trong hộp thoại Value Field Settings, sửa ô Custom Name thành Tần suất. Trong trang Show Values As, trong khung Show values as, chọn % of Grand Total như Hình 12.7b.
Xoay bảng: Kéo thả Σ values từ vùng 2 sang vùng 3 để chuyển các giá trị từ xếp theo cột thành xếp theo dòng như Hình 12.7c.
a)
b)
c)
d)
Hình 12.7. Các thao tác chọn hàm, dồi nhân hàm, xoay bảng và kết quả
Kết quả em nhận được như Hình 12.7d. Từ kết quả này, có thể thấy số lượng các em học sinh nữ chiếm tỉ lệ 62.5% (15 em), nhiều hơn các em học sinh nam chỉ chiếm 37.5% (9 em).
b) Tương tự mục a, ta cần so sánh số lượng và tỉ lệ sinh viên theo cả giới tính và ngành học, em cần chọn thêm Ngành học làm biến dòng. Thao tác thêm có thể thực hiện như sau:
Kéo thả Ngành học vào vùng 3 (Ngành học thành biến dòng) sau cho Ngành học nằm trước Σ values như Hình 12.8a. Kết quả nhận được như Hình 12.8b.
Trang 73
a)
b)
Hình 12.8. Thao tác thêm biến dòng và kết quả
Từ kết quả ở Hình 12.8b, có thể thấy với ngành học N1, số sinh viên nam và nữ bằng nhau, cùng chiếm 16,67% (4 sinh viên), nhưng ở ngành học N2 và N3 số sinh viên nữ nhiều hơn hẳn số sinh viên nam.
Nhiệm vụ 2: Lập bảng thống kê theo số liệu ghép nhóm
Yêu cầu: Theo số liệu ở Hình 11.1, Thầy giáo cần chia điểm Hóa học thành 4 nhóm thấp, trung bình thấp, trung bình cao và cao; đồng thời thống kê số lượng sinh viên nam và sinh viên nữ theo từng nhóm. Từ kết quả nhận được, em hãy cho biết sinh viên nữ hay nam có nhiều hơn trong nhóm điểm cao nhất.
Hướng dẫn:
Trong khoảng điểm hiện có của môn Hoá học, em cần chia thành 4 khoảng giá trị bằng nhau, mỗi khoảng là một nhóm. Đồng thời, cần đếm số lượng sinh viên nam và nữ của mỗi nhóm.
Bước 1. Kích hoạt PivotTable với dữ liệu Hình 11.1: Theo hướng dẫn ở mục 1.a.
Kéo thả biến Giới tính vào vùng 2 trở thành biến dòng. Sau đó, tiếp tục kéo thả biến Giới tính vào vùng 4 trở thành biến tính toán. Vì Giới tính là biến định tính nên Excel chọn hàm Count.
Bước 2. Kéo thả biến Hóa học vào vùng 3 trở thành biến dòng. PivotTable hiển thị toàn bộ bảng điểm môn Hoá học. Kết quả như Hình 12.9.
Hình 12.9. Bảng điểm môn Hoá học
Hình 12.10. Bảng chọn chức năng ghép nhóm
Trang 74
Nháy nút phải chuột vào ô bất kì trong cột Row labels của Hình 12.9, chọn Group... (Hình 12.10) để mở hộp thoại Grouping (Hình 12.11). Vì chia khoảng điểm Hóa học thành 4 nhóm bằng nhau, mỗi nhóm có độ rộng là (9.75–3.25)4 = 1.625, do đó nhập độ rộng 1.625 các nhóm vào ô By. Nhắn phím Enter để nhận được kết quả như Hình 12.12.
Hình 12.11. Hộp thoại chọn độ rộng nhóm
Hình 12.12. Kết quả tần số ghép nhóm điểm Hóa học theo Giới tính
Từ kết quả Hình 12.12, có thể thấy có 5 sinh viên nữ nhưng chỉ có 1 sinh viên nam trong nhóm đạt điểm Hóa học cao nhất (từ 8.125 đến 9.75).
Nhiệm vụ 3: Lập bảng tổng hợp nhiều chiều
Yêu cầu: Từ dữ liệu ở Hình 11.1, em hãy cho biết ở ngành nào các sinh viên Nam, Nữ có trung bình Tổng điểm cao nhất; ở ngành học nào, các sinh viên này có mức phân tán Tổng điểm lớn nhất.
Hướng dẫn: Để thực hiện được yêu cầu của nhiệm vụ, em cần lập một bảng tổng hợp hai chiều theo Giới tính và Ngành học, trong đó cần có các số liệu về Trung bình của Tổng điểm và Độ lệch chuẩn của Tổng điểm.
Bước 1. Kích hoạt PivotTable với dữ liệu Hình 11.1: Theo hướng dẫn ở Mục 1.a.
Hình 12.13. Bảng PivotTable với các biến Ngành học, Giới tính, Tổng điểm
– Kéo thả biến Ngành học vào vùng 2. Ngành học trở thành biến dòng.
– Kéo thả biến Giới tính vào vùng 3. Giới tính trở thành biến cột.
– Kéo thả biến Tổng điểm vào vùng 4. Tổng điểm thành biến tính toán.
Kết quả như Hình 12.13.
Bước 2. Chọn Sum of Tổng điểm trong vùng 4, chọn Value Field Settings... để mở bảng chọn, chọn hàm Average (thay cho hàm Sum mặc định). Đặt lại nhãn Trung bình của Tổng điểm cho hàm tại ô Custom Name như Hình 12.14. Nháy chọn OK.
Hình 12.14. Chọn hàm và đặt nhãn cho biển Tổng điểm
Trang 75
Bước 3. Thêm hàm: Tiếp tục kéo thả biến Tổng điểm vào vùng 4. Thực hiện các thao tác tương tự của bước 2, nhưng chọn hàm StdDev cho biến Tổng điểm, đặt lại nhãn là Độ lệch chuẩn của Tổng điểm.
Bước 4. Xoay bảng: Chuyển Σ Values từ vùng 2 vào vùng 3 như Hình 12.15 để chuyển các giá trị từ xếp theo cột thành xếp theo dòng. Kết quả nhận được như Hình 12.16.
Hình 12.15. Thao tác xoay bảng
Hình 12.16. Kết quả bảng tổng hợp nhiều chiều
Từ Hình 12.16, ta thấy:
– Đối với các sinh viên nam: Trung bình của Tổng điểm theo ngành N1 là cao nhất (24.75 điểm), ngành N3 có độ phân tán của Tổng điểm là lớn nhất (độ lệch chuẩn lên tới 2.06 điểm).
– Đối với các sinh viên nữ: Trung bình của Tổng điểm theo ngành N3 là cao nhất (24.60 điểm) nhưng ngành N2 có độ phân tán của điểm là lớn nhất (độ lệch chuẩn lên tới 2.57 điểm).
LUYỆN TẬP
1. Em có thể chọn một biến định tính đưa vào ô 4 (Values) của bảng khai báo biến khi lập PivotTable và chọn hàm Average hay không? Nếu Excel cho phép thì kết quả là gì?
2. Em có thể chỉnh sửa tiêu đề ngay trong bảng chọn hàm cho biến tính toán hay không? Nếu có thì nhập tiêu đề vào đâu? Kết quả của thao tác này là gì?
VẬN DỤNG
1. Lập PivotTable Hình 12.13 với tất cả các giá trị là tỉ lệ % so với tổng chung (% of Grand Total).
2. Tạo PivotTable ghép nhóm Tổng điểm thành bốn nhóm.
3. Sử dụng PivotTable lập các bảng tần số điểm môn Toán tương ứng với các biểu đồ Hình 11.2.
