Bảng tính 3 — PivotTable & Tổng hợp dữ liệu

PivotTable — tổng hợp dữ liệu chỉ bằng kéo thả

Trong bài Công thức & Hàm nâng cao ta đã dùng SUMIFS, COUNTIFS, SUMPRODUCT để tổng hợp có điều kiện. Chúng mạnh nhưng phải viết tay từng ô, và khi sếp hỏi "tách thêm theo loại tiền đi" là phải sửa cả cụm công thức. PivotTable giải quyết đúng chỗ đau đó: từ một bảng dữ liệu thô, bạn kéo thả trường vào bốn vùng và trong vài giây có ngay bảng tổng hợp đa chiều — đổi chiều phân tích chỉ bằng kéo trường sang vùng khác.

Với người có nền SQL, cách hiểu nhanh nhất: PivotTable chính là GROUP BY bằng chuột. Mọi khái niệm pivot đều có song ánh trực tiếp trong câu SELECT ... GROUP BY. Bài này khai thác đúng phép so sánh đó, rồi đi sâu vào cách dựng, tính toán trong pivot, bộ lọc trực quan, và các cạm bẫy khiến báo cáo sai số.

Lưu ý quy ước: các khối minh hoạ thao tác pivot không chạy trên SQL Builder. Cuối bài có một câu SQL đúng schema sandbox đánh dấu ▶ để bạn thấy "pivot chính là group-by".

Nguyên lý: từ dữ liệu tidy đến bảng tổng hợp

PivotTable chỉ hoạt động tốt trên dữ liệu dạng bảng (long / tidy): mỗi cột là một trường (thuộc tính), mỗi dòng là một bản ghi, không có ô gộp, không có tiêu đề nhiều tầng, không có dòng tổng chen giữa. Đây là điều kiện tiên quyết — dữ liệu càng "sạch theo cột" thì pivot càng linh hoạt.

Ví dụ bảng giao dịch nguồn:

Từ bảng này, pivot tính "tổng dư nợ theo chi nhánh × sản phẩm". Đó chính xác là:

SELECT chi_nhanh, san_pham, SUM(so_du)
FROM du_no
GROUP BY chi_nhanh, san_pham;

Khác biệt duy nhất: SQL trả kết quả dạng dài (mỗi tổ hợp một dòng), còn pivot xoay một trường lên thành các cột (dạng ma trận) để mắt người dễ đọc.

Song ánh SQL GROUP BY ↔ PivotTable

Hiểu được bảng này, bạn "đọc" được mọi PivotTable dưới dạng một câu query — và ngược lại, biết trước một câu group-by sẽ trông thế nào khi dựng thành pivot.

Cách dựng: bốn vùng Rows / Columns / Values / Filters

Chọn một ô bất kỳ trong vùng dữ liệu → Insert → PivotTable. Excel dựng khung rỗng và mở PivotTable Fields với danh sách trường ở trên, bốn vùng thả ở dưới:

• Rows — kéo chi_nhanh xuống đây: mỗi chi nhánh một dòng. Kéo thêm san_pham để có nhóm lồng (chi nhánh → sản phẩm bên trong).
• Columns — kéo loai_tien: mỗi loại tiền một cột. Rows × Columns tạo ma trận.
• Values — kéo so_du: đây là số liệu được tổng hợp. Mặc định Excel chọn Sum cho số, Count cho text.
• Filters — kéo ngay (hay thang): lọc toàn bảng theo giá trị chọn, như WHERE.

Chọn hàm tổng hợp

Nhấp trường trong Values → Value Field Settings → Summarize Values By:

Lưu ý quan trọng: Distinct Count chỉ có khi pivot chạy trên Data Model (xem cuối bài) — PivotTable thường không đếm distinct được. Đây là lý do thường phải bật "Add this data to the Data Model" khi tạo pivot nếu cần đếm số khách hàng duy nhất theo chi nhánh.

Show Values As — đổi cách hiển thị giá trị

Đây là tính năng bị bỏ phí nhiều nhất. Cùng một số liệu SUM(so_du), bạn đổi cách trình bày mà không cần công thức. Chuột phải ô Values → Show Values As:

Mẹo: kéo cùng một trường so_du vào Values hai lần — một cột để Sum giá trị tuyệt đối, một cột % of Column Total — là ra ngay bảng vừa có số vừa có cơ cấu, không viết một công thức nào.

Nhóm (grouping): ngày và số

Pivot cho phép gộp giá trị chi tiết thành khoảng ngay trong bảng, không cần thêm cột phụ ở dữ liệu nguồn.

• Nhóm ngày: kéo ngay vào Rows → chuột phải → Group → chọn Months / Quarters / Years (chọn nhiều tầng cùng lúc). Excel tự sinh cấp Năm → Quý → Tháng để bạn drill-down. Đây là cách nhanh nhất để từ dữ liệu giao dịch theo ngày ra báo cáo theo tháng/quý.
• Nhóm số thành bins: kéo so_du vào Rows → Group → đặt Starting at, Ending at, By (bước). Ví dụ chia số dư thành các dải 0–500tr, 500tr–1 tỷ, >1 tỷ để đếm phân bố tài khoản — tương đương CASE WHEN chia bin trong SQL nhưng làm bằng chuột.

Cạm bẫy: nếu cột ngày có ô là text (không phải kiểu date thật) thì Group sẽ báo lỗi "Cannot group that selection". Phải làm sạch kiểu dữ liệu nguồn trước.

Tính toán trong pivot: Calculated Field & Calculated Item

Khi cần một chỉ số không có sẵn trong dữ liệu nguồn, dùng PivotTable Analyze → Fields, Items & Sets:

• Calculated Field — tạo trường mới từ công thức trên các trường khác, tính trên tổng của mỗi ô. Ví dụ có du_no và du_no_qua_han, tạo trường ty_le_no_xau = du_no_qua_han / du_no. Lưu ý: Excel tính công thức trên giá trị đã tổng hợp của ô, không phải trên từng dòng nguồn — nên AVG của tỷ lệ ≠ tỷ lệ của các tổng. Với tỷ lệ, luôn để pivot cộng tử số và mẫu số riêng rồi mới chia (đúng như Calculated Field làm), đừng lấy trung bình cột tỷ lệ.
• Calculated Item — tạo thành viên mới trong một trường, ví dụ gộp "Vay tiêu dùng" + "Thấu chi" thành một mục "Bán lẻ không TSBĐ". Ít dùng và dễ gây nhầm tổng, nên cân nhắc kỹ.

Giới hạn: Calculated Field chỉ dùng được Sum (không dùng được Count/Average/Distinct Count bên trong), và không tham chiếu được ô ngoài pivot. Khi cần tính phức tạp hơn, chuyển sang Data Model + đo lường DAX (xem Bảng tính 5 — Data Model & DAX).

Slicer & Timeline — bộ lọc trực quan

Slicer là các nút bấm lọc pivot thay cho ô Filters khó thao tác. PivotTable Analyze → Insert Slicer → chọn trường (ví dụ chi_nhanh, san_pham). Người dùng bấm nút để lọc, thấy ngay giá trị nào đang chọn.

Timeline là slicer chuyên cho ngày: một thanh trượt chọn tháng/quý/năm rất trực quan, hợp báo cáo theo thời gian.

Sức mạnh thật sự: một slicer điều khiển nhiều pivot cùng lúc. Slicer → Report Connections → tích các pivot muốn liên kết. Nhờ vậy một dashboard gồm bảng dư nợ + biểu đồ huy động + bảng nợ xấu cùng lọc theo một nút "Chi nhánh Hà Nội" — nền tảng để dựng dashboard tương tác.

PivotChart — biểu đồ tổng hợp

PivotChart là biểu đồ gắn với pivot: đổi trường trong pivot thì biểu đồ tự đổi, bấm slicer thì biểu đồ tự lọc. PivotTable Analyze → PivotChart. Dùng cột chồng cho cơ cấu, đường cho xu hướng theo Timeline, thanh ngang cho xếp hạng chi nhánh. Đây là cách nhanh nhất biến một bảng group-by thành hình trực quan mà vẫn tương tác được.

GETPIVOTDATA — trích một ô cụ thể

Khi cần dựng báo cáo cố định (mẫu cứng, ô A5 luôn là "tổng dư nợ Hà Nội tháng 6") mà không muốn ô đó nhảy chỗ khi pivot đổi bố cục, dùng GETPIVOTDATA. Gõ = rồi bấm vào một ô trong pivot, Excel tự sinh:

=GETPIVOTDATA("so_du", $A$3, "chi_nhanh", "Hà Nội", "thang", "2026-06")

Hàm này bám theo trường + giá trị, không theo vị trí ô — nên dù pivot thêm dòng, di chuyển, hoán chiều, công thức vẫn lấy đúng con số. Đó là công cụ chuẩn để "đổ" số từ pivot vào một layout báo cáo đẹp, cố định. (Nếu bạn muốn tham chiếu thường theo ô, tắt tính năng: PivotTable Analyze → Options → Generate GetPivotData.)

Làm mới (refresh) & pivot cache

PivotTable không tự cập nhật khi dữ liệu nguồn đổi — đây là hiểu lầm gây sai số phổ biến nhất. Pivot đọc dữ liệu vào một bản chụp trong bộ nhớ gọi là pivot cache; bạn phải Refresh để đọc lại:

• Chuột phải trong pivot → Refresh, hoặc Data → Refresh All (làm mới mọi pivot).
• Bật tự làm mới khi mở file: PivotTable Options → Data → Refresh data when opening the file.

Nhiều pivot dùng chung một cache nếu cùng nguồn — tiết kiệm dung lượng nhưng refresh một cái là cả nhóm cập nhật. Nếu vùng nguồn thêm dòng mới mà pivot không thấy, nguyên nhân là range nguồn cố định: chuyển nguồn thành Excel Table (Ctrl+T) để nó tự co giãn, hoặc sửa Change Data Source.

Pivot trên Google Sheets — khác biệt

Google Sheets cũng có PivotTable (Insert → Pivot table), khái niệm giống hệt (Rows, Columns, Values, Filters), nhưng khác vài điểm:

• Calculated Field trong Google Sheets nhận công thức kiểu bảng tính, tham chiếu trường bằng tên: ví dụ =du_no_qua_han / du_no. Linh hoạt hơn Excel ở chỗ viết bằng cú pháp quen thuộc.
• Có Summarize by → Custom và một mục "Show as" tương tự Show Values As (% of row/column/grand total).
• Không có Slicer/Timeline giống Excel, nhưng có Slicer riêng của Sheets (Data → Add a slicer) lọc cả pivot lẫn biểu đồ.
• Distinct count có sẵn qua Summarize by → COUNTUNIQUE — tiện hơn Excel (không cần Data Model).
• Không có khái niệm pivot cache tách rời; pivot cập nhật gần như tức thời khi nguồn đổi.

Pivot từ Data Model — nhiều bảng, distinct count, DAX

Khi dữ liệu nằm ở nhiều bảng (bảng giao dịch + bảng danh mục chi nhánh + bảng sản phẩm), thay vì VLOOKUP gộp về một bảng phẳng, hãy nạp vào Data Model (Power Pivot) và tạo quan hệ giữa các bảng. PivotTable trên Data Model cho phép:

• Kéo trường từ nhiều bảng khác nhau vào cùng một pivot (nhờ quan hệ khoá).
• Distinct Count thật sự (đếm số khách hàng duy nhất).
• Viết đo lường DAX (measures) — công thức tổng hợp mạnh hơn hẳn Calculated Field, có ngữ cảnh lọc, tính theo thời gian, tỷ lệ động.

Đây là bước nâng cấp lớn từ pivot cơ bản, được trình bày kỹ ở Bảng tính 5 — Data Model & DAX. Nắm vững pivot cơ bản ở bài này là nền để hiểu Data Model.

Cạm bẫy thường gặp

• Dữ liệu nguồn không tidy: ô gộp, tiêu đề hai tầng, dòng tổng chen giữa → pivot đọc sai hoặc không tạo được. Làm phẳng về long trước (xem Power Query để tự động hoá bước này).
• Ô trống trong cột số: Excel đổi hàm mặc định sang Count thay vì Sum, ra kết quả vô nghĩa. Lấp trống bằng 0 hoặc kiểm lại Value Field Settings.
• Quên Refresh: dữ liệu nguồn đã đổi nhưng pivot vẫn số cũ. Luôn Refresh All trước khi chốt báo cáo; range cố định làm pivot bỏ sót dòng mới → dùng Table làm nguồn.
• Hiểu sai tổng %: cộng các cột "% of Parent Total" thuộc nhóm khác nhau ra con số vô nghĩa; luôn xác định rõ % đang tính trên tổng nào (row / column / grand / parent).
• Trung bình của tỷ lệ: tính tỷ lệ nợ xấu bằng cách lấy Average cột tỷ lệ là sai — phải cộng tử và mẫu riêng rồi chia (Calculated Field). Trung bình các tỷ lệ không bằng tỷ lệ của các tổng.
• Gộp text vào Values: kéo trường chữ vào Values chỉ đếm được (Count), không cộng được — nếu cần "gộp text" phải dùng cách khác (Power Query, TEXTJOIN), pivot không làm được.

Use case thực tế

Bối cảnh: cuối tháng 6/2026, khối Bán lẻ NCB cần bảng tổng hợp dư nợ theo sản phẩm × chi nhánh × tháng và cơ cấu huy động theo kỳ hạn để họp giao ban, có đối chiếu với sổ cái.

Các bước:

1. Xuất bảng giao dịch dư nợ dạng tidy (ngày, chi nhánh, sản phẩm, loại tiền, số dư), chuyển nguồn thành Excel Table (Ctrl+T) để tự co giãn khi thêm dữ liệu.
2. Tạo PivotTable: Rows = chi_nhanh → san_pham (lồng); Columns = thang; Values = Sum(so_du). Có ngay ma trận dư nợ 63 chi nhánh × sản phẩm × 6 tháng.
3. Thêm cột Values thứ hai % of Column Total → thấy cơ cấu dư nợ mỗi tháng. Bật Show Values As → Difference From (tháng trước) để đo tăng/giảm.
4. Chèn Slicer san_pham và Timeline theo tháng; Report Connections để một slicer điều khiển cả PivotChart cơ cấu huy động bên cạnh.
5. Bảng huy động: pivot thứ hai với Rows = ky_han (đã Group số ngày thành dải: KKH, ≤3T, 3–6T, 6–12T, >12T), Values = Sum(so_du) và % of Grand Total → cơ cấu huy động theo kỳ hạn.
6. Đối chiếu: ở ô báo cáo cố định, dùng GETPIVOTDATA lấy tổng dư nợ toàn hàng từ pivot, so với con số sổ cái. Lệch → kiểm ô trống, kiểm đã Refresh All chưa, kiểm range nguồn có bỏ sót dòng.

Kết quả: một file duy nhất, bấm slicer là ra báo cáo theo chi nhánh/sản phẩm bất kỳ, không sửa công thức; đối chiếu tự động qua GETPIVOTDATA.

Dữ liệu tổng hợp nguồn thường lấy thẳng từ kho bằng một câu group-by. Câu sau (đúng schema sandbox) minh hoạ "pivot chính là group-by": tổng và số dư trung bình theo thành phố và loại tiền:

-- ▶ Chạy được
SELECT
  c.city                              AS thanh_pho,
  a.currency                          AS loai_tien,
  COUNT(*)                            AS so_tai_khoan,
  SUM(a.balance)                      AS tong_so_du,
  ROUND(AVG(a.balance)::numeric, 2)   AS so_du_tb
FROM accounts a
JOIN customers c ON c.id = a.customer_id
GROUP BY c.city, a.currency
ORDER BY thanh_pho, loai_tien;

Đổ kết quả này vào bảng tính rồi đặt thanh_pho vào Rows, loai_tien vào Columns, tong_so_du vào Values — bạn có đúng bảng ma trận mà câu SQL đã tính, chỉ khác là được xoay lên cho dễ đọc. Đó là toàn bộ ý nghĩa của PivotTable.

Ghi nhớ

• PivotTable = GROUP BY bằng chuột: Rows/Columns ≈ cột GROUP BY, Values ≈ hàm tổng hợp, Filters ≈ WHERE, Grand Total ≈ ROLLUP.
• Điều kiện tiên quyết: dữ liệu tidy/long (mỗi cột một trường, không ô gộp, không dòng tổng chen giữa); nguồn nên là Excel Table để tự co giãn.
• Show Values As đổi cách trình bày mà không cần công thức: % of total/parent, running total, rank, difference from — kéo trường vào Values hai lần để vừa có số vừa có cơ cấu.
• Group ngày (→ tháng/quý/năm) và số (→ bins) ngay trong pivot; ngày phải là kiểu date thật mới group được.
• Calculated Field tính trên tổng của ô (đừng lấy trung bình cột tỷ lệ); phức tạp hơn thì dùng Data Model + DAX.
• Slicer & Timeline lọc trực quan, một slicer điều khiển nhiều pivot (Report Connections) — nền của dashboard.
• GETPIVOTDATA trích một ô theo trường/giá trị cho báo cáo cố định, không lệ thuộc vị trí ô.
• Luôn Refresh: pivot đọc từ cache, không tự cập nhật; range cố định làm bỏ sót dòng mới.
• Google Sheets khác: calculated field kiểu công thức, distinct count sẵn (COUNTUNIQUE), không có pivot cache tách rời.
• Bẫy: nguồn không tidy, ô trống làm đổi hàm mặc định, quên refresh, hiểu sai tổng %, và không "gộp text" được trong Values.

Đọc tiếp

• Bảng tính 2 — Công thức & Hàm nâng cao
• Bảng tính 4 — Power Query trong Excel
• Bảng tính 5 — Data Model & DAX
• Bảng tính 6 — Dashboard tương tác
• Chỉ số & KPI trong BI