PivotTable từ A đến Z: dựng báo cáo tổng hợp trong 5 phút

Nếu chỉ được học một tính năng Excel duy nhất, hãy chọn PivotTable.

Lý do đơn giản: bài toán phổ biến nhất trong công việc văn phòng là "tổng hợp một bảng dữ liệu dài theo vài chiều khác nhau". Làm bằng công thức, bạn cần hàng chục SUMIFS và phải sửa lại mỗi lần sếp đổi câu hỏi. Làm bằng PivotTable, bạn kéo thả trong 30 giây và đổi góc nhìn tùy ý.

Điều kiện tiên quyết: dữ liệu phải đúng cấu trúc

90% vấn đề với PivotTable bắt nguồn từ dữ liệu nguồn, không phải từ PivotTable.

Bảng nguồn phải là bảng phẳng (flat table):

Yêu cầu:

• Một dòng tiêu đề duy nhất, mọi cột đều phải có tên.
• Mỗi dòng là một giao dịch/bản ghi.
• Không ô gộp, không dòng trống, không dòng tổng xen giữa.
• Mỗi cột một kiểu dữ liệu.

Bảng ngang không dùng được. Nếu dữ liệu của bạn có 12 cột tháng (T1, T2, … T12), PivotTable không tổng hợp theo thời gian được. Phải chuyển thành dạng dọc (Tháng – Doanh thu) bằng Power Query — xem thao tác Unpivot trong bài Làm sạch dữ liệu trong Excel.

Luôn chuyển nguồn thành Table trước (Ctrl + T). Khi đó thêm dữ liệu mới vào cuối bảng, PivotTable chỉ cần Refresh là cập nhật — không phải chỉnh lại vùng nguồn.

Tạo PivotTable đầu tiên

1. Đặt con trỏ vào bất kỳ ô nào trong bảng dữ liệu.
2. Insert → PivotTable.
3. Chọn nơi đặt: New Worksheet (khuyến nghị cho báo cáo mới).
4. OK.

Bên phải xuất hiện khung PivotTable Fields với danh sách tên cột ở trên và bốn vùng ở dưới.

Bốn vùng và ý nghĩa

Quy tắc bố cục dễ đọc: trường có nhiều giá trị đặt vào Rows, trường có ít giá trị đặt vào Columns. 30 sản phẩm nằm ngang sẽ khiến bạn phải cuộn ngang; nằm dọc thì đọc thoải mái.

Ví dụ đầu tiên nên thử: kéo Khu vực vào Rows, Doanh thu vào Values. Bạn có ngay bảng tổng doanh thu theo khu vực. Kéo thêm Sản phẩm vào Rows dưới Khu vực — bảng lập tức thành báo cáo hai cấp.

Đổi cách tính trong vùng Values

Mặc định Excel dùng Sum cho trường số và Count cho trường chữ. Để đổi: nhấn vào trường trong vùng Values → Value Field Settings.

Lỗi kinh điển: kéo trường số vào Values nhưng Excel hiện Count of Doanh thu thay vì Sum. Nguyên nhân: cột đó có ít nhất một ô chứa chữ hoặc ô trống được nhập bằng dấu cách. Đây là tín hiệu dữ liệu nguồn chưa sạch — hãy sửa nguồn chứ đừng chỉ đổi tay sang Sum.

Hiển thị dưới dạng phần trăm

Chuột phải vào vùng số → Show Values As:

• % of Grand Total — tỷ trọng trên tổng toàn bảng.
• % of Row Total / % of Column Total — cơ cấu theo dòng hoặc theo cột.
• % of Parent Row Total — tỷ trọng trong nhóm cha, rất hợp với báo cáo hai cấp.
• Difference From / % Difference From — so với kỳ trước, dùng để tính tăng trưởng.
• Running Total In — lũy kế theo thời gian.

Mẹo hay dùng: kéo Doanh thu vào Values hai lần, để một cái là Sum và một cái là % of Grand Total. Bạn có cả số tuyệt đối và tỷ trọng cạnh nhau mà không cần công thức nào.

Nhóm dữ liệu

Nhóm theo thời gian

Chuột phải vào một ô ngày trong PivotTable → Group → chọn Months, Quarters, Years.

Từ Excel 2016 trở lên, khi kéo trường ngày vào Rows, Excel thường tự tạo nhóm Năm/Quý/Tháng. Nếu không tự nhóm được, nguyên nhân gần như luôn là cột ngày đang chứa giá trị dạng chữ — quay lại bước sửa ngày tháng ở bài làm sạch dữ liệu.

Lưu ý về thứ tự tháng: hãy để Excel tự nhóm từ cột ngày thật, đừng tạo cột chữ "Tháng 1", "Tháng 10" — chúng sẽ sắp xếp theo bảng chữ cái, cho ra thứ tự 1, 10, 11, 12, 2, 3…

Nhóm theo khoảng số

Chuột phải vào một ô số → Group → đặt Starting at, Ending at, By. Ví dụ nhóm điểm sinh viên theo bước 1 điểm, hoặc nhóm giá trị đơn hàng theo bước 1 triệu — cách nhanh nhất để dựng bảng phân phối tần suất.

Nhóm thủ công

Chọn nhiều mục trong Rows (giữ Ctrl) → chuột phải → Group. Dùng để gom "Miền Bắc" và "Miền Trung" thành "Phía Bắc" mà không cần thêm cột vào dữ liệu nguồn.

Trường tính toán (Calculated Field)

Khi cần một chỉ số không có sẵn trong dữ liệu nguồn — ví dụ tỷ suất lợi nhuận:

PivotTable Analyze → Fields, Items & Sets → Calculated Field → đặt tên và nhập công thức:

= Loi_nhuan / Doanh_thu

Cảnh báo quan trọng: trường tính toán áp dụng lên giá trị đã tổng hợp, không phải trên từng dòng. Với các phép cộng trừ thì kết quả đúng; với phép chia và tỷ lệ thì thường vẫn đúng ở cấp tổng, nhưng với các công thức phức tạp hơn (nhân, lũy thừa) kết quả có thể sai lệch so với kỳ vọng.

Cách an toàn hơn cho các chỉ số phức tạp: thêm cột tính toán vào bảng dữ liệu nguồn, rồi kéo cột đó vào PivotTable.

Slicer và Timeline: bộ lọc cho người xem

Vùng Filters chỉ hợp với bạn. Nếu file được người khác dùng, hãy dùng Slicer.

Slicer: PivotTable Analyze → Insert Slicer → chọn trường muốn lọc. Bạn được một khung nút bấm trực quan, ai cũng biết dùng mà không cần hướng dẫn.

Timeline: Insert Timeline — thanh trượt thời gian, chỉ dùng được với trường ngày thật.

Kết nối một Slicer với nhiều PivotTable: chuột phải vào Slicer → Report Connections → tick các PivotTable muốn điều khiển. Đây là nền tảng để dựng dashboard: một lần bấm lọc, mọi bảng và biểu đồ cùng đổi.

PivotChart

PivotTable Analyze → PivotChart. Biểu đồ liên kết trực tiếp với PivotTable: đổi bố cục bảng thì biểu đồ đổi theo, bấm Slicer thì cả hai cùng lọc.

Ba lưu ý:

• PivotChart không hỗ trợ một số loại biểu đồ như scatter (phân tán). Khi cần, hãy copy kết quả Pivot sang vùng khác rồi vẽ biểu đồ thường.
• Nút lọc hiện trên biểu đồ có thể tắt nếu làm rối: chuột phải → Hide All Field Buttons on Chart.
• Chọn đúng loại biểu đồ quan trọng hơn làm nó đẹp — xem bài Chọn đúng biểu đồ trong Excel.

Định dạng và trình bày

Vài thiết lập nên làm cho mọi PivotTable dùng để báo cáo:

Điểm cuối cùng quan trọng: nếu bạn định dạng bằng Ctrl + 1 trên vùng ô, định dạng sẽ mất sau khi Refresh. Định dạng qua Value Field Settings → Number Format thì gắn với trường và giữ được lâu dài.

Report Layout dạng Tabular + Repeat All Item Labels là cặp thiết lập biến PivotTable từ "bảng để xem" thành "bảng để tái sử dụng" — kết quả copy ra chỗ khác vẫn đầy đủ dữ liệu ở mọi dòng.

Bảy lỗi PivotTable thường gặp

1. Refresh nhưng dữ liệu mới không xuất hiện. Vùng nguồn cố định không bao gồm dòng mới. Sửa gốc: chuyển nguồn thành Table (Ctrl + T), hoặc Change Data Source và chọn lại vùng.

2. Trường số bị đếm thay vì cộng. Cột chứa ô chữ hoặc ô trống có dấu cách. Sửa ở dữ liệu nguồn.

3. Vẫn còn tên cũ trong bộ lọc dù dữ liệu đã xóa. Đó là "ma dữ liệu" trong bộ nhớ đệm. PivotTable Options → Data → Number of items to retain per field: chọn None → Refresh.

4. Không nhóm được theo ngày. Cột ngày có ô dạng chữ hoặc ô trống. Kiểm tra bằng =ISNUMBER(A2).

5. Lỗi "The PivotTable field name is not valid". Có cột trong bảng nguồn thiếu tiêu đề. Điền tên cho mọi cột.

6. Hai PivotTable ảnh hưởng lẫn nhau khi nhóm. Chúng dùng chung bộ nhớ đệm. Nếu cần độc lập, tạo Pivot mới từ nguồn theo cách riêng, hoặc tạo bản sao dữ liệu nguồn.

7. Số tổng không khớp với tổng của các dòng. Thường do bộ lọc còn bật ở đâu đó, hoặc do trường tính toán đang tính trên giá trị đã tổng hợp. Kiểm tra lại cả hai.

Bài tập thực hành

1. Báo cáo doanh thu. Tạo bảng 200 dòng giao dịch giả định có các cột Ngày, Khu vực, Nhân viên, Sản phẩm, Doanh thu. Dựng PivotTable tổng doanh thu theo khu vực và theo tháng.

Câu hỏi thường gặp

PivotTable có làm hỏng dữ liệu gốc không?

Không. PivotTable chỉ đọc dữ liệu nguồn, mọi thao tác kéo thả, nhóm, lọc đều không ghi ngược lại. Đây là lý do nó an toàn hơn nhiều so với việc sắp xếp và lọc trực tiếp trên bảng dữ liệu.

Khi nào nên dùng SUMIFS thay vì PivotTable?

Dùng SUMIFS khi con số cần nằm ở một vị trí cố định trong mẫu báo cáo có sẵn, hoặc khi kết quả phải tự cập nhật mà không cần ai bấm Refresh. Dùng PivotTable khi bạn cần khám phá dữ liệu và đổi góc nhìn liên tục. Trong thực tế, người làm báo cáo thường dùng PivotTable để phân tích, rồi dùng SUMIFS/GETPIVOTDATA để đưa số vào mẫu trình bày.

GETPIVOTDATA là gì và có nên tắt không?

Khi bạn gõ = rồi bấm vào một ô trong PivotTable, Excel tự sinh hàm GETPIVOTDATA thay vì tham chiếu ô thường. Hàm này bền vững hơn (bố cục Pivot đổi thì công thức vẫn đúng) nhưng khó kéo. Nếu muốn tắt: PivotTable Analyze → Options (mũi tên xuống) → bỏ tick Generate GetPivotData.

Distinct Count không có trong danh sách, làm sao bật?

Khi tạo PivotTable, tick ô Add this data to the Data Model. Sau đó Value Field Settings sẽ có thêm lựa chọn Distinct Count. Lưu ý PivotTable dùng Data Model bị hạn chế một số thao tác nhóm.

PivotTable có làm file nặng không?

Có, vì nó lưu một bản sao dữ liệu trong bộ nhớ đệm (pivot cache). Với file lớn, hãy để nhiều PivotTable dùng chung một cache (tạo bằng cách copy PivotTable có sẵn thay vì tạo mới từ đầu), và bật tùy chọn Save source data with file chỉ khi thật sự cần.

Có PivotTable trên Google Sheets không?

Có, với tên Pivot table trong menu Insert. Khái niệm giống hệt (Rows, Columns, Values, Filters) nên kỹ năng chuyển đổi được. Khác biệt chính: Google Sheets yếu hơn ở phần Slicer đa bảng, trường tính toán và Data Model.

Nguồn tham khảo

• Create a PivotTable to analyze worksheet data – Microsoft Support
• Group or ungroup data in a PivotTable – Microsoft Support
• Show different calculations in PivotTable value fields – Microsoft Support
• Use slicers to filter data – Microsoft Support

Kết luận

PivotTable không khó — nó chỉ đòi hỏi dữ liệu nguồn đúng cấu trúc. Nếu bạn từng thử và thấy nó "dở chứng", nguyên nhân gần như chắc chắn nằm ở bảng nguồn: ô gộp, dòng trống, cột thiếu tiêu đề, hoặc ngày tháng dạng chữ.

Hãy dành 20 phút dựng PivotTable đầu tiên trên chính dữ liệu của bạn. Từ lần thứ hai trở đi, những báo cáo từng ngốn cả buổi sẽ chỉ còn vài phút kéo thả.

Bài viết liên quan

• Làm sạch dữ liệu trong Excel
• Chọn đúng biểu đồ Excel và dựng dashboard
• 30 hàm Excel dân văn phòng dùng hằng ngày
• 50 phím tắt Excel