Giải chuyên đề Tin 12 Cánh diều Bài 3. Dùng bảng Pivot để phân tích thống kê có đáp án

Trong một trang tính của Excel, có một bảng dữ liệu lớn theo dõi hoạt động bán hàng của một cửa hàng bán lẻ với khá nhiều hàng, nhiều cột (ví dụ các cột: tên nhân viên, ngày bản, số lượng, đơn giá, thành tiền,...). Để làm báo cáo tổng hợp cuối quý về doanh số hàng tháng của từng nhân viên từ bảng dữ liệu lớn trong Excel, có thể thực hiện các bước sau:

1. Chuẩn bị dữ liệu:

Trước tiên bảng dữ liệu đã được sắp xếp hợp lý, mỗi cột có tiêu đề rõ ràng (ví dụ: Tên nhân viên, Ngày bán, Số lượng, v.v.).

Thêm cột phụ nếu cần thiết: Nếu bảng dữ liệu của bạn chưa có cột "Tháng" thì bạn cần thêm một cột mới để xác định tháng của mỗi giao dịch.

2. Sử dụng PivotTable (Bảng tổng hợp): Chọn toàn bộ bảng dữ liệu của bạn. Vào tab Insert trên thanh công cụ và chọn PivotTable. Trong hộp thoại Create PivotTable, chọn nơi bạn muốn đặt PivotTable (có thể là trên một trang tính mới hoặc cùng trang tính hiện tại). Khi PivotTable Field List xuất hiện: Kéo "Tên nhân viên" vào vùng Rows (Hàng). Kéo "Tháng" vào vùng Columns (Cột). Kéo "Thành tiền" vào vùng Values (Giá trị). Excel sẽ tự động tính tổng cho các giá trị này.

3. Định dạng và tinh chỉnh PivotTable:

Có thể thay đổi cách hiển thị của dữ liệu trong PivotTable bằng cách nhấn chuột phải vào các vùng giá trị và chọn Value Field Settings để thay đổi cách tính toán (Sum, Average, Count, etc.).

Để dữ liệu dễ đọc hơn, bạn có thể thêm các bộ lọc (Filters) như "Tên nhân viên" hoặc "Tháng" để tùy chỉnh cách hiển thị dữ liệu.

4. Định dạng báo cáo:

Sau khi có PivotTable, bạn có thể định dạng nó theo ý muốn, ví dụ như thay đổi font chữ, màu nền, thêm đường viền, v.v. Để làm rõ hơn, bạn có thể thêm tiêu đề cho báo cáo, ngày tháng lập báo cáo và bất kỳ ghi chú nào khác cần thiết.

Lập bảng dữ liệu như hình, các bước tạo bảng thống kê bằng PivotTable:

1. Tạo PivotTable: Chọn toàn bộ bảng dữ liệu của. Vào tab Insert trên thanh công cụ và chọn PivotTable. Trong hộp thoại Create PivotTable, chọn nơi bạn muốn đặt PivotTable (có thể là trên một trang tính mới hoặc cùng trang tính hiện tại).

2. Cấu hình PivotTable: Khi PivotTable Field List xuất hiện, Kéo "Xếp loại" vào vùng Rows (Hàng). Kéo "Xếp loại" vào vùng Values (Giá trị). Excel sẽ tự động tính tổng cho các giá trị này.

3. Định dạng và tinh chỉnh PivotTable:

Muốn thay đổi cách tính toán giá trị, nhấn chuột phải vào một ô trong vùng giá trị và chọn Value Field Settings. Tại đây, bạn có thể chọn các phép tính khác như Sum, Average, Count, v.v. Bạn có thể thêm các bộ lọc (Filters) để tùy chỉnh cách hiển thị dữ liệu.

4. Định dạng báo cáo: Bạn có thể định dạng PivotTable để dễ đọc hơn bằng cách thay đổi font chữ, màu nền, thêm đường viền, v.v. Thêm tiêu đề cho báo cáo, ngày tháng lập báo cáo và bất kỳ ghi chú nào khác cần thiết.

Nhập bảng dữ liệu là danh sách học sinh với các trường Họ và tên, Giới tính, Tuổi như Hình 5a.

1) Tạo bảng thống kê số lượng học sinh theo từng nhóm tuổi với khoảng là 10.

Bước 1: Khởi tạo bảng Pivot, chọn trường phân loại cần quan tâm “Tuổi”, đánh dấu chọn khối ô trong cột mang tên “Tuổi” > Chọn lệnh Insert > PivotTable > OK > Kéo thả “Tuổi” vào vùng ROWS và ∑ VALUES > nháy chọn Sum of Tuổi > chọn Value Field Setting > Chọn COUNT > OK

Bước 2: Thiết lập lựa chọn ghép nhóm các giá trị trong trường tuổi

2) Tạo bảng thống kê số lượng học sinh theo tuổi và giới tính. Nói cách khác, cần tạo bằng tần số theo tuổi được ghép nhóm hai chiều: Tuổi (với khoảng là 10) và Giới tính (để tách riêng cho Nam và Nữ).

- Bước 1: Khởi tạo bảng Pivot và chọn hai trường phân loại cần quan tâm ( Giới tính và Tuổi)

- Bước 2: Thiết lập lựa chọn ghép nhóm các giá trị trong trường tuổi.

Cho bảng dữ liệu gồm các cột: Ngày tháng, Người bán, Tên hàng, Số lượng, Số tiền (Hình 8a).

a) Cách lập bảng tổng hợp báo cáo số lượng hàng bán của mỗi người trên mỗi cột

- Bước 1: Chọn khối A1: E14 và khởi tạo bảng Pivot dựa trên khối ô này.

- Bước 2: Kéo thả các trường dữ liệu theo thiết kế bảng báo cáo tổng hợp:

Kéo thả Người bán -> COLUMNS, Ngày tháng -> ROWS, Số lượng (kg) -> ∑ VALUES

b) Lập bảng tổng hợp doanh số bán hàng theo các cột ứng với từng tháng

- Thao tác kéo thả tương tự như ý a) và chỉ cần thay đổi đích đến theo cách tương ứng.