Cách tạo Pivot Table trong Excel từng bước

Pivot table nhóm các hàng của một bảng theo một danh mục, như Region, và tính tổng một con số, như Sales, cho từng nhóm mà không cần công thức. Để tạo pivot table, bấm vào một ô trong vùng dữ liệu, vào Insert > PivotTable, nhấn OK, rồi kéo Region vào Rows và Sales vào Values. Trang tính bên dưới không phải pivot table: nó dựng cùng bảng tổng hợp đó bằng công thức, để bạn thấy các tổng thay đổi theo dữ liệu.

Công thứcĐặt lại

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

UNIQUE liệt kê mỗi khu vực một lần và SUMIF tính tổng cho từng khu vực: North 455, South 305 và East 170, lần lượt là 49%, 33% và 18% của tổng 930. Đổi C3 thành 185, tổng của South và cả ba tỷ lệ phần trăm thay đổi theo ngay. Pivot table cũng cho ra các con số này, nhưng chỉ sau khi bạn refresh.

Cách tạo pivot table

Trước khi bắt đầu, hãy kiểm tra dữ liệu nguồn: một hàng tiêu đề với tên ở mọi cột, mỗi hàng là một bản ghi, không có hàng hay cột trống ở giữa và không có hàng tổng phụ.

1. Bấm vào ô bất kỳ trong vùng dữ liệu.
2. Vào Insert > PivotTable (ở một số phiên bản là Insert > PivotTable > From Table/Range).
3. Excel tự điền vùng dữ liệu. Chọn New Worksheet rồi nhấn OK.
4. Một pivot table trống xuất hiện cùng khung PivotTable Fields ở bên phải, liệt kê các tiêu đề cột của bạn.
5. Kéo Region vào ô Rows và Sales vào ô Values. Pivot table hiện mỗi khu vực một lần, bên cạnh là Sum of Sales, cùng một hàng Grand Total.
6. Để đổi nội dung hiển thị, kéo các trường giữa các ô hoặc kéo chúng ra khỏi khung.

Nếu chưa biết bắt đầu từ đâu, Insert > Recommended PivotTables gợi ý vài bố cục có sẵn cho dữ liệu của bạn. Trên máy Mac, menu cũng giống vậy: Insert > PivotTable.

Rows, Columns, Values và Filters

Khung PivotTable Fields có bốn ô, và mọi pivot table đều là một lựa chọn cột nào đặt vào ô nào:

• Rows: các danh mục xếp dọc ở bên trái, mỗi giá trị khác nhau một hàng (Region).
• Columns: các danh mục xếp ngang ở trên cùng, mỗi giá trị khác nhau một cột (Product).
• Values: các con số cần tính cho từng tổ hợp. Với cột số, mặc định là Sum; Count, Average, Max, Min và các phép khác nằm trong Value Field Settings.
• Filters: một trường dùng để lọc toàn bộ pivot table, hiện dưới dạng danh sách thả xuống phía trên nó.

Với Region ở Rows, Product ở Columns và Sales ở Values, pivot table của dữ liệu trên trông như sau:

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

Phiên bản công thức của bố cục này liệt kê các khu vực theo chiều dọc bằng UNIQUE, các sản phẩm theo chiều ngang bằng TRANSPOSE(UNIQUE()), và tính mọi ô của lưới bằng một hàm SUMIFS nhận hai danh sách đó làm điều kiện:

Công thứcĐặt lại

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

E2 trả tràn North, South và East xuống dưới, F1 trả tràn Apple và Pear sang ngang, và hàm SUMIFS ở F2 lấp đầy lưới 3 x 2 ở giữa: mỗi cặp khu vực và sản phẩm có một tổng. Đổi B5 từ Apple thành Pear, cả hai ô của East đều thay đổi. Thứ tự ở đây là thứ tự các giá trị xuất hiện lần đầu; pivot table thì sắp xếp nhãn theo bảng chữ cái.

Đếm, tính trung bình hoặc phần trăm thay vì tính tổng

Trong pivot table, bấm vào trường trong ô Values và chọn Value Field Settings. Thẻ Summarize Values By chuyển giữa Sum, Count, Average, Max và Min; thẻ Show Values As đổi các con số thành % of Grand Total, % of Column Total, tổng lũy kế và nhiều dạng khác. Mỗi lựa chọn đều có công thức tương ứng:

Công thứcĐặt lại

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

North có 3 đơn, trung bình 151,7; South có 3 đơn, trung bình 101,7; East có 2 đơn, trung bình 85,0. Cột % of Grand Total nằm ở trang tính đầu tiên của trang này.

Lọc bảng tổng hợp theo một sản phẩm

Ô Filters đặt một danh sách thả xuống phía trên pivot table. Phiên bản công thức là một ô có danh sách thả xuống cùng hàm SUMIFS, hàm thêm một điều kiện nữa so với SUMIF. Chọn một sản phẩm ở F1:

Công thứcĐặt lại

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

Khi chọn Apple, North hiện 215, South 150 và East 60. Chọn Pear thì chúng đổi thành 240, 155 và 110. Xem SUMIFS để thêm điều kiện, và trang danh sách thả xuống để biết cách tạo danh sách trong Excel.

Refresh pivot table

Pivot table giữ một bản sao của dữ liệu nguồn (pivot cache) và không tính lại khi một ô trong nguồn thay đổi. Sau khi sửa dữ liệu:

• Bấm chuột phải vào bất kỳ đâu trong pivot table và chọn Refresh, hoặc nhấn Alt+F5 trên Windows.
• Data > Refresh All (Ctrl+Alt+F5) refresh mọi pivot table trong workbook.
• Để tự refresh mỗi lần mở tệp, bấm chuột phải vào pivot table, chọn PivotTable Options, rồi ở thẻ Data đánh dấu Refresh data when opening the file.

Các hàng mới thêm bên dưới vùng nguồn không được tính, kể cả sau khi refresh. Hãy đổi vùng trong PivotTable Analyze > Change Data Source, hoặc tốt hơn, chuyển vùng nguồn thành bảng trước khi tạo pivot table: chọn dữ liệu rồi nhấn Ctrl+T, hoặc dùng Insert > Table. Bảng tự mở rộng khi bạn thêm hàng, và pivot table sẽ lấy các hàng đó ở lần refresh tiếp theo.

Tính tổng mọi khu vực bằng một công thức

Công thứcĐặt lại

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

Đến lượt bạn: Ở F2, tính tổng doanh số của mọi khu vực liệt kê ở E2:E4 chỉ bằng một công thức.

Kiểm traHiện đáp án

Đáp án trả tràn ra 455, 305 và 170. Khi đưa cả danh sách E2:E4 làm điều kiện, SUMIF trả về một tổng cho mỗi khu vực, nên không cần kéo công thức xuống. Excel 2019 trở về trước không có UNIQUE và cũng không có cơ chế trả tràn: hãy gõ các khu vực vào E2:E4 rồi kéo =SUMIF($A$2:$A$9,E2,$C$2:$C$9) xuống. Không có dấu $, các vùng sẽ dịch xuống theo từng hàng và các tổng bị sai.

GROUPBY và PIVOTBY: pivot table trong một công thức

Excel cho Microsoft 365 có hai hàm dựng toàn bộ bảng tổng hợp chỉ với một công thức và tự tính lại như mọi công thức khác, không cần refresh. Bạn cần có gói Microsoft 365 hiện hành. Với dữ liệu ở trên:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

GROUPBY nhận trường hàng, các giá trị và hàm tính (SUM, COUNTA, AVERAGE, MAX, PERCENTOF). PIVOTBY thêm một trường cột vào giữa. Cả hai đều sắp xếp nhãn và thêm hàng tổng, giống pivot table.

Nên dùng pivot table hay công thức

Dùng pivot table để khám phá dữ liệu và trả lời một câu hỏi một lần; dùng công thức cho bảng tổng hợp nằm trong báo cáo, cung cấp số liệu cho các công thức khác và luôn phải cập nhật. Để kiểm tra số liệu của pivot table, hãy dựng lại một ô của nó bằng SUMIFS: nếu hai kết quả khác nhau, thường là pivot table cần refresh hoặc vùng nguồn của nó quá ngắn.