PivotTable (bảng tổng hợp) biến một bảng dữ liệu hàng nghìn dòng thành báo cáo tóm tắt chỉ bằng kéo thả, không cần viết công thức. Cách tạo: bấm vào một ô bất kỳ trong dữ liệu, chọn Insert (Chèn) > PivotTable, bấm OK; rồi kéo trường cần nhóm (ví dụ Nhân viên) vào ô Rows, kéo trường cần tính (Doanh thu) vào ô Values. Ngay lập tức bạn có tổng doanh thu theo từng nhân viên.
Muốn chia tiếp theo khu vực, kéo Khu vực vào ô Columns; muốn chỉ xem một tháng, kéo Ngày vào ô Filters. Bài này đi từ khâu chuẩn bị dữ liệu (nơi quyết định Pivot có chạy đúng hay không) đến nhóm theo tháng, tính phần trăm, lọc bằng Slicer và làm mới số liệu. Tên menu có thể khác đôi chút giữa Excel 2016, 2019, 2021 và Microsoft 365.
Chuẩn bị dữ liệu: 80% thành công nằm ở đây
PivotTable chỉ thông minh khi dữ liệu nguồn ngăn nắp. Một bảng chuẩn cho Pivot có dạng "nhật ký": mỗi dòng là một sự kiện (một đơn hàng, một lần chi), mỗi cột là một thuộc tính. Trước khi tạo, rà các điểm sau:
- Một hàng tiêu đề duy nhất, mỗi cột có tên riêng, không trùng, không bỏ trống. Cột không tên sẽ hiện thành "Column1" khó hiểu.
- Không gộp ô ở bất cứ đâu trong vùng dữ liệu.
- Không có dòng trống, cột trống chen giữa; Excel sẽ tưởng bảng kết thúc ở đó.
- Không có dòng tổng, dòng tiêu đề nhóm xen giữa dữ liệu. Pivot sẽ cộng luôn dòng tổng, ra số gấp đôi.
- Mỗi cột một kiểu dữ liệu: cột Doanh thu toàn số, cột Ngày toàn ngày. Chỉ một ô chữ trong cột số cũng khiến Pivot mặc định "Count" thay vì "Sum".
- Không tách dữ liệu theo tháng thành nhiều cột (T1, T2, T3…). Nên để một cột Tháng và một cột Giá trị; Pivot sẽ tự xoay thành cột cho bạn.
Sau khi rà xong, bấm vào một ô trong dữ liệu và bấm Ctrl + T để định dạng thành Bảng (Table). Lợi ích lớn nhất: khi thêm dòng mới ở cuối, Pivot dựa trên Bảng tự nhận vùng mới, không phải chỉnh lại nguồn dữ liệu bằng tay.
Tạo PivotTable đầu tiên, từng bước
Lấy ví dụ bảng bán hàng có các cột Ngày, Nhân viên, Khu vực, Mặt hàng, Số lượng, Doanh thu. Mục tiêu: xem doanh thu mỗi nhân viên theo từng khu vực.
- Bấm vào một ô bất kỳ trong bảng dữ liệu.
- Vào thẻ Insert (Chèn), bấm PivotTable. Hộp thoại hiện tên bảng hoặc vùng đã tự chọn; giữ lựa chọn "New Worksheet" (Trang tính mới) rồi bấm OK.
- Bên phải hiện khung PivotTable Fields: phía trên là danh sách các cột, phía dưới là bốn ô Filters, Columns, Rows, Values.
- Kéo Nhân viên vào Rows. Bảng bên trái hiện danh sách tên, mỗi tên một dòng, không trùng.
- Kéo Doanh thu vào Values. Excel hiện "Sum of Doanh thu" — tổng doanh thu mỗi người, kèm dòng Grand Total.
- Kéo Khu vực vào Columns. Bảng tách thành các cột Bắc, Trung, Nam.
- Bấm chuột phải vào một số trong bảng, chọn Number Format, chọn kiểu Number với dấu phân cách hàng nghìn để số dễ đọc.
Muốn đổi góc nhìn, chỉ cần kéo các trường qua lại: đưa Mặt hàng vào Rows thay cho Nhân viên là có báo cáo theo mặt hàng. Không có thao tác nào làm thay đổi dữ liệu gốc, nên cứ thử thoải mái. Kéo một trường ra khỏi khung là bỏ nó khỏi báo cáo.
Đổi cách tính: tổng, đếm, trung bình, phần trăm
Value Field Settings. Bấm vào mũi tên cạnh "Sum of Doanh thu" trong ô Values, chọn Value Field Settings. Tab Summarize Values By cho đổi sang Count (đếm số đơn), Average (trung bình mỗi đơn), Max, Min. Kéo Doanh thu vào Values hai lần để có cả tổng lẫn số đơn trong cùng bảng.
Vì sao ra Count mà không phải Sum. Cột số có ô trống hoặc ô chứa chữ, nên Excel không chắc đó là cột số. Sửa dữ liệu nguồn, hoặc đổi tay sang Sum như trên.
Tỷ trọng phần trăm. Trong Value Field Settings, tab Show Values As, chọn % of Grand Total để biết mỗi nhân viên chiếm bao nhiêu phần trăm tổng doanh thu; % of Row Total để xem trong doanh thu của một người, mỗi khu vực chiếm bao nhiêu.
So với kỳ trước. Show Values As còn có Difference From và % Difference From: chọn trường cơ sở là Tháng, mục cơ sở là (previous) để xem tăng giảm so với tháng trước, không cần công thức.
Sắp xếp. Bấm chuột phải vào một số trong cột tổng, chọn Sort, Largest to Smallest để xếp nhân viên từ doanh thu cao xuống thấp. Muốn xem 5 người đứng đầu, bấm mũi tên lọc ở tiêu đề Row Labels, chọn Value Filters, Top 10 và sửa thành 5.
Nhóm theo tháng, quý và nhóm khoảng giá trị
Khi kéo một cột ngày vào Rows, Excel bản mới thường tự nhóm thành Năm, Quý, Tháng. Nếu không, bấm chuột phải vào một ngày trong Pivot, chọn Group, rồi chọn Months và Years (nên chọn cả Years nếu dữ liệu trải qua nhiều năm, nếu không tháng 3 năm nay và tháng 3 năm ngoái sẽ bị cộng chung). Muốn bỏ nhóm, chuột phải chọn Ungroup.
Nếu lệnh Group báo "Cannot group that selection", nghĩa là cột ngày có ô trống hoặc ô là chữ trông giống ngày. Lọc cột ngày trong dữ liệu nguồn, tìm các ô căn trái hoặc ô trống, sửa lại rồi làm mới Pivot.
Nhóm cũng dùng được cho số. Ví dụ kéo Doanh thu vào Rows (lần này làm nhãn chứ không phải giá trị), chuột phải, Group, đặt Starting at 0, By 1000000: Pivot sẽ chia đơn hàng thành các khoảng 0–1 triệu, 1–2 triệu… Kết hợp với Count ở Values, bạn biết ngay phần lớn đơn hàng rơi vào khoảng giá nào. Còn với chữ, bôi đen vài nhãn (ví dụ ba mặt hàng nhỏ), chuột phải chọn Group để gộp thành một nhóm "Khác".
Slicer, làm mới số liệu và những lỗi hay gặp
Slicer là bộ nút lọc trực quan. Bấm vào Pivot, vào thẻ PivotTable Analyze (bản cũ là Analyze hoặc Options), chọn Insert Slicer, tích Khu vực. Một hộp nút hiện ra; bấm nút Nam là cả bảng chỉ còn số của miền Nam, giữ Ctrl để chọn nhiều nút. Slicer rất hợp để gửi báo cáo cho người không rành Excel. Insert Timeline làm việc tương tự cho cột ngày, kéo thanh trượt để chọn tháng.
Làm mới: Pivot không tự cập nhật khi dữ liệu nguồn thay đổi. Sau khi sửa hoặc thêm dữ liệu, bấm chuột phải vào Pivot chọn Refresh, hoặc bấm Alt + F5; Ctrl + Alt + F5 làm mới tất cả Pivot trong tệp. Có thể bật tự làm mới khi mở tệp trong PivotTable Options, tab Data, mục Refresh data when opening the file.
Các lỗi hay gặp: thêm dòng mà Pivot không thấy (do nguồn là vùng cố định thay vì Bảng — sửa ở Change Data Source); xuất hiện nhãn "(blank)" (dữ liệu có ô trống); một nhân viên xuất hiện hai lần (tên gõ khác nhau, có khoảng trắng thừa); bảng bị cộng gấp đôi (dữ liệu nguồn có dòng tổng). Khi cần lấy một con số từ Pivot sang chỗ khác, Excel sẽ tạo công thức GETPIVOTDATA; nếu muốn tham chiếu ô bình thường thì tắt tuỳ chọn Generate GetPivotData trong PivotTable Options.
Biến PivotTable thành báo cáo gửi sếp
Bảng Pivot mặc định trông khá thô: tiêu đề "Row Labels", "Sum of Doanh thu", số không có dấu phân cách. Trước khi gửi, sửa tên tiêu đề bằng cách bấm vào ô và gõ thẳng tên mới như "Nhân viên", "Doanh thu (đồng)". Lưu ý không được đặt tên trùng hệt tên cột gốc; thêm một khoảng trắng ở cuối là cách nhiều người dùng để lách.
Thẻ Design có ba nút đáng biết: Subtotals (bật tắt dòng cộng phụ của từng nhóm), Grand Totals (bật tắt dòng và cột tổng chung), Report Layout (dạng Compact gọn, Outline hay Tabular giống bảng thường). Báo cáo một cấp thì Compact là đủ; báo cáo nhiều cấp cần chép sang chỗ khác xử lý tiếp thì nên chọn Tabular.
Muốn có biểu đồ đi kèm, bấm vào Pivot rồi chọn PivotChart trong thẻ PivotTable Analyze. Biểu đồ này gắn với Pivot: lọc bằng Slicer thì biểu đồ đổi theo. Cuối cùng, nếu gửi cho người không cần xoay dữ liệu, chép bảng rồi Dán đặc biệt dạng giá trị sang một tệp mới để người nhận không thấy dữ liệu chi tiết bên dưới — Pivot lưu bản sao dữ liệu nguồn, ai nhấp đúp vào một ô số là xem được toàn bộ các dòng tạo nên con số đó.
Câu hỏi thường gặp
PivotTable có làm thay đổi dữ liệu gốc không?
Không. PivotTable chỉ đọc dữ liệu nguồn và tạo bảng tóm tắt ở chỗ khác. Bạn kéo thả, nhóm, lọc thế nào cũng không ảnh hưởng tới bảng gốc. Ngược lại, nếu sửa số trực tiếp trong ô của Pivot, Excel sẽ không cho phép.
Vì sao PivotTable không cập nhật khi thêm dữ liệu mới?
Pivot cần được làm mới bằng Refresh hoặc Alt + F5. Nếu làm mới rồi mà vẫn thiếu dòng mới, nguồn dữ liệu đang là một vùng cố định. Vào PivotTable Analyze, chọn Change Data Source để mở rộng vùng, hoặc chuyển dữ liệu nguồn thành Bảng bằng Ctrl + T để tự mở rộng về sau.
Có thể tạo PivotTable từ nhiều sheet không?
Được nếu các sheet có cùng cấu trúc cột. Cách gọn nhất là dùng Power Query, trong thẻ Data chọn Get Data, gộp các bảng bằng Append rồi tải kết quả vào Pivot. Cách khác là tích ô Add this data to the Data Model khi tạo Pivot để liên kết nhiều bảng qua một cột chung.
Làm sao in PivotTable cho đẹp?
Chọn thẻ Design, đổi Report Layout sang Show in Tabular Form để mỗi trường một cột, bật Repeat All Item Labels để không còn ô trống, chọn một kiểu PivotTable Style nhạt. Trong PivotTable Options, tab Printing, có thể đặt lặp tiêu đề hàng trên mỗi trang in.
Google Sheets có PivotTable không?
Có, gọi là bảng tổng hợp. Vào Chèn, chọn Bảng tổng hợp, sau đó thêm hàng, cột, giá trị và bộ lọc trong khung bên phải, cách làm tương tự Excel. Bảng tổng hợp của Google Sheets tự cập nhật khi dữ liệu nguồn thay đổi, không cần bấm làm mới.
Calculated Field trong Pivot dùng khi nào?
Khi cần một chỉ số tính từ các trường có sẵn mà dữ liệu nguồn chưa có cột, ví dụ đơn giá trung bình bằng Doanh thu chia Số lượng. Vào PivotTable Analyze, chọn Fields, Items and Sets, rồi Calculated Field. Lưu ý trường tính luôn tính trên tổng chứ không tính từng dòng rồi cộng lại.
