Bài 11: Báo Cáo Động Với Pivot Table & Pivot Chart
Giai đoạn 3 – Nâng cao · Mục tiêu: Tổng hợp hàng vạn dòng dữ liệu thành báo cáo tóm tắt trong vài giây với Pivot Table và Pivot Chart.
1. Pivot Table là gì?
Pivot Table (Bảng tổng hợp xoay) là công cụ giúp:
- Tóm tắt hàng nghìn/triệu dòng dữ liệu thành bảng báo cáo gọn.
- Phân nhóm dữ liệu theo nhiều tiêu chí (phòng ban, sản phẩm, thời gian...).
- Xoay chiều phân tích dữ liệu chỉ bằng thao tác kéo thả.
2. Quy tắc dữ liệu nguồn (Data Source)
Dữ liệu nguồn PHẢI đạt chuẩn trước khi tạo Pivot Table:
| Chuẩn | Sai |
|---|---|
| Mỗi cột có tiêu đề rõ ràng | Cột không có tiêu đề hoặc trùng tên |
| Không gộp ô (Merge Cells) | Có ô gộp trong vùng dữ liệu |
| Không có dòng trống xen kẽ | Dòng trống làm gián đoạn dữ liệu |
| Mỗi dòng là 1 bản ghi (giao dịch) | Dữ liệu tổng hợp sẵn (cross-tab) |
| Kiểu dữ liệu nhất quán trong mỗi cột | Cùng cột vừa số vừa text |
:::warning Lỗi phổ biến nhất Gộp ô (Merge Cells) trong dữ liệu nguồn sẽ khiến Pivot Table hoạt động sai hoàn toàn! Hãy Unmerge tất cả trước khi tạo Pivot. :::
3. Tạo Pivot Table
Bước 1: Chọn dữ liệu
Click vào bất kỳ ô nào trong vùng dữ liệu → Excel tự nhận diện vùng.
Bước 2: Chèn Pivot Table
Tab Insert → PivotTable → Chọn New Worksheet hoặc Existing Worksheet.
Bước 3: Kéo thả trường dữ liệu
Bảng PivotTable Fields có 4 vùng:
| Vùng | Chức năng | Ví dụ |
|---|---|---|
| Filters | Bộ lọc tổng thể | Lọc theo Năm |
| Columns | Tiêu đề cột | Quý 1, Quý 2, Quý 3, Quý 4 |
| Rows | Tiêu đề dòng | Tên sản phẩm, Phòng ban |
| Values | Giá trị tính toán | Sum of Doanh thu, Count of Đơn hàng |
Ví dụ: Báo cáo doanh thu theo sản phẩm và quý
- Rows: Kéo
Tên Sản Phẩm - Columns: Kéo
Quý - Values: Kéo
Doanh Thu(tự động chọn Sum) - Filters: Kéo
Khu Vực
4. Tùy chỉnh giá trị tính toán
Click vào trường trong vùng Values → Value Field Settings:
| Phép tính | Ý nghĩa |
|---|---|
| Sum | Tổng (mặc định cho số) |
| Count | Đếm số bản ghi |
| Average | Trung bình |
| Min / Max | Giá trị nhỏ nhất / lớn nhất |
| % of Grand Total | Phần trăm so với tổng cộng |
| % of Column Total | Phần trăm so với tổng cột |
| Running Total | Tổng lũy kế |
5. Nhóm dữ liệu (Grouping)
Nhóm Ngày tháng
- Click chuột phải vào ô ngày trong Pivot Table →
Group. - Chọn nhóm: Months, Quarters, Years.
→ Tự động gom 01/01/2024, 15/01/2024, 28/01/2024 thành Tháng 1.
Nhóm Số
- Click chuột phải vào ô số →
Group. - Thiết lập
Starting at,Ending at,By.
→ Gom lương thành các khoảng: 5-10 triệu, 10-15 triệu, 15-20 triệu.
6. Bộ lọc tương tác: Slicer & Timeline
Slicer – Bộ lọc trực quan
- Click vào Pivot Table → Tab
PivotTable Analyze→Insert Slicer. - Chọn trường cần lọc (ví dụ: Phòng Ban, Khu Vực).
- Một bảng nút bấm xuất hiện → click để lọc nhanh.
Ưu điểm: Trực quan, dễ sử dụng, có thể liên kết nhiều Pivot Table cùng lúc.
Timeline – Bộ lọc thời gian
- Tab
PivotTable Analyze→Insert Timeline. - Chọn trường ngày.
- Thanh trượt thời gian xuất hiện → kéo để lọc theo khoảng thời gian.
:::tip Mẹo Kết hợp Slicer + Timeline để tạo Dashboard tương tác chuyên nghiệp! :::
7. Pivot Chart
Pivot Chart là biểu đồ tự động liên kết với Pivot Table:
- Click vào Pivot Table → Tab
PivotTable Analyze→PivotChart. - Chọn kiểu biểu đồ.
- Biểu đồ sẽ tự động cập nhật khi thay đổi Pivot Table hoặc Slicer.
Bài tập thực hành
Cho bảng Nhật ký bán hàng 2024 (1000+ dòng): Ngày, Mã SP, Tên SP, Nhóm hàng, Nhân viên, Khu vực, SL, Đơn giá, Thành tiền.
- Tạo Pivot Table: Doanh thu theo Nhóm hàng và Quý.
- Thêm % of Grand Total để xem tỷ trọng từng nhóm.
- Tạo Slicer cho Khu vực và Timeline cho Ngày.
- Tạo Pivot Chart dạng Column kết hợp Slicer.
Bài trước: Bài 10 – Conditional Formatting · Bài tiếp: Bài 12 – Biểu Đồ & Dashboard