Tổng tiền mua của từng nhà cung cấp trong quý bằng PivotTable, khỏi lọc từng tên

ExcelCơ bản Video 1:46

Bài toán

Sếp hỏi quý này mua của từng nhà cung cấp bao nhiêu tiền, thuế bao nhiêu; bạn lọc từng tên rồi bấm máy tính cộng.

Làm xong được gì

Một bảng tổng theo nhà cung cấp: tiền hàng, tiền thuế, tỷ trọng, xếp từ lớn đến nhỏ; thêm hoá đơn mới chỉ cần gõ nối vào bảng kê rồi Refresh All.

Dùng trong bài

TablePivotTableSortShow Values As

File thực hành gồm

BangKeMuaVao_Q3.xlsx
Sheet Mua vào Q3, bảng kê hoá đơn mua vào quý 3 xuất từ phần mềm kế toán: tiêu đề ba dòng, tên cột ở dòng 5, dữ liệu từ dòng 6 tới 190. Có 185 tờ của 15 nhà cung cấp xếp theo ngày, các nhà nằm lẫn nhau. Các dòng nhóm và dòng Tổng cộng đã gỡ sẵn. Vùng dữ liệu còn là vùng thường, chưa phải Table.
BangKeMuaVao_Q3_dap_an.xlsx
Bản đã có PivotTable ở Sheet1, mở ra để so kết quả.

Các bước làm

1
Thử lọc tay một nhà cung cấp
Bấm ô E5 rồi Ctrl+Shift+L để bật nút lọc. Mở nút lọc cột Tên người bán, gõ MŨI GHE vào ô Search, Enter: còn 26 tờ của Vận tải Mũi Ghe. Bấm ô tiền hàng đầu tiên đang hiện (G6) rồi Ctrl+Shift+↓, số Sum ở thanh trạng thái là 380.494.000. Đó mới là một nhà, còn 14 nhà nữa và chưa có cột thuế. Bấm Ctrl+Shift+L lần nữa để tắt lọc.
2
Chuyển bảng kê thành Table
Bấm ô A6 rồi bấm Ctrl+T. Hộp Create Table hiện vùng $A$5:$I$190 và đã tích My table has headers, bấm Enter. Từ đây tờ nào gõ thêm ở cuối thì bảng tự dài ra.
3
Chèn PivotTable
Vào Insert › PivotTable. Hộp thoại đã điền sẵn Table1, để nguyên New Worksheet rồi bấm OK. Excel mở một sheet mới (Sheet1) có PivotTable trống ở ô A3 và khung PivotTable Fields bên phải.
4
Tích ba trường
Trong khung PivotTable Fields tích Tên người bán, Tiền hàng và Thuế GTGT. Tên người bán vào Rows, hai trường tiền vào Values và tự cộng (Sum). Mỗi nhà cung cấp gom về một dòng, bảng nằm ở A3:C19.
5
Xếp từ lớn đến nhỏ
Bấm ô B4 trong cột tiền hàng rồi vào Data › Sort Z to A (với ô số, Excel gọi nút này là Sort Largest to Smallest). Nhà mua nhiều nhất lên đầu: Thép Bến Vạc, 1.597.414.000.
6
Thêm cột tỷ trọng
Trong khung PivotTable Fields, bấm chuột phải vào Tiền hàng, chọn Add to Values. Cột D hiện thêm Sum of Tiền hàng2. Bấm chuột phải ô D4, chọn Show Values As › % of Grand Total. Bấm ô D3, gõ đè tên cột thành Tỷ trọng rồi Enter. Thép Bến Vạc chiếm 27,93%, ba nhà đầu cộng lại 65,42% tiền mua cả quý.
7
Kiểm dòng Grand Total
Ô B19 (Grand Total của tiền hàng) là 5.720.097.000, khớp tổng cột Tiền hàng của bảng kê.
8
Thử thêm một hoá đơn về muộn rồi Refresh
Về sheet Mua vào Q3, bấm ô A191 ngay dưới dòng cuối của bảng. Gõ lần lượt, mỗi ô một Tab: 186, C26TGS, 1182, 2026-09-30, CÔNG TY CP BAO BÌ GÒ SẬY, 0307690747, 210000000, 8 %, 16800000, rồi Enter. Bảng tự nới xuống dòng 191. Về Sheet1 bấm Data › Refresh All: Bao bì Gò Sậy lên dòng đầu với 1.629.778.000, Grand Total thành 5.930.097.000, tỷ trọng tự tính lại.

Lưu ý khi làm với file thật

File xuất từ phần mềm còn dòng Tổng cộng và các dòng nhóm chen giữa thì xoá trước khi bấm Ctrl+T, không thì PivotTable cộng đôi.
Một nhà cung cấp mà tên gõ hai kiểu thì PivotTable tách thành hai dòng: sửa tên cho thống nhất, hoặc gom theo mã số thuế.
Bảng kê đã là Table thì hoá đơn gõ thêm tự vào PivotTable sau Refresh All, khỏi chọn lại vùng.

PivotTable, Table và Show Values As có từ Excel 2010: Excel 2016, 2019, 2021, 365 làm được y hệt. Bản cũ số trong PivotTable có thể chưa có dấu ngăn nghìn: bấm chuột phải ô số › Number Format.

File thực hành của bài này

EzTech sẽ cập nhật link tải sau.

Dữ liệu trong bài là dữ liệu minh hoạ.

Bài học khác

Tất cả bài học