Pivot Table trong Excel: hướng dẫn phân tích dữ liệu bán hàng

Pivot Table trong Excel (Excel gọi là PivotTable) giúp bạn biến vài nghìn dòng đơn hàng thành bảng tổng hợp doanh thu theo cửa hàng, ngành hàng, tháng chỉ bằng thao tác kéo thả, không cần viết công thức. Bài viết hướng dẫn từng bước trên một bảng dữ liệu bán hàng giả định: chuẩn bị dữ liệu, tạo PivotTable, đổi cách tính, nhóm theo tháng, thêm Slicer, vẽ PivotChart, làm mới dữ liệu và 6 lỗi hay gặp. Mỗi bước có kết quả mong đợi để bạn tự đối chiếu.

Pivot Table trong Excel là gì và khi nào nên dùng?

PivotTable là bảng tổng hợp động. Bạn chọn trường làm dòng, làm cột, trường cần cộng hoặc đếm, Excel tự tính. Muốn xem góc khác, chỉ cần kéo trường sang chỗ khác, dữ liệu gốc không đổi.

Nó giúp trả lời nhanh các câu hỏi như:

• Tháng này cửa hàng nào bán được nhiều nhất?
• Ngành hàng nào chiếm bao nhiêu phần trăm doanh thu?
• Doanh thu từng tháng tăng hay giảm, ở khu vực nào?

Mới làm quen với phân tích dữ liệu? Bài Business Analytics là gì cho bạn bức tranh rộng hơn.

Lưu ý về phiên bản: tên menu trong bài theo Excel cho Microsoft 365 trên Windows, giao diện tiếng Anh. Bản cũ hơn hoặc Excel trên Mac có thể khác vị trí nút và tên tab, ví dụ tab PivotTable Analyze ở bản cũ có tên Analyze hoặc Options. Bạn có thể đối chiếu thêm trang hướng dẫn Create a PivotTable to analyze worksheet data của Microsoft.

Chuẩn bị dữ liệu bán hàng trước khi tạo PivotTable

Dữ liệu nguồn chuẩn có ba đặc điểm: mỗi dòng là một giao dịch, mỗi cột là một loại thông tin, dòng đầu là tiêu đề. Không gộp ô, không dòng trống, không dòng tổng cộng xen giữa.

Bảng dưới đây là dữ liệu giả định của một chuỗi cửa hàng hư cấu, doanh thu tính bằng nghìn đồng. Gõ lại vào trang tính trống, bắt đầu từ ô A1, để làm theo:

Kiểu hiển thị ngày tuỳ cài đặt vùng của máy. Điều quan trọng là Excel hiểu đó là ngày thật: ô ngày thật thường căn phải, ô chữ căn trái.

Chuyển vùng dữ liệu thành Table (Ctrl+T)

1. Bấm vào một ô bất kỳ trong vùng dữ liệu.
2. Nhấn Ctrl+T (hoặc vào tab Insert, chọn Table).
3. Trong hộp thoại Create Table, kiểm tra vùng chọn là A1:E9 và đã tích My table has headers (bảng có dòng tiêu đề), rồi bấm OK.
4. Vào tab Table Design (bản cũ là Design), ở ô Table Name bên trái, đổi tên mặc định thành BanHang.

Kết quả mong đợi: bảng có màu xen kẽ, tiêu đề có nút lọc. Khi thêm dòng ngay dưới bảng, Table tự giãn ra, nên PivotTable sẽ nhận dòng mới sau khi làm mới.

Tạo Pivot Table trong Excel qua 5 bước

1. Bấm vào một ô trong bảng BanHang.
2. Vào tab Insert, chọn PivotTable. Ở Microsoft 365, nếu hiện menu nhỏ thì chọn From Table/Range (từ bảng hoặc vùng dữ liệu).
3. Trong hộp thoại, ô Table/Range phải ghi BanHang. Chọn New Worksheet (đặt báo cáo ở trang tính mới) rồi bấm OK.
4. Bên phải màn hình hiện khung PivotTable Fields. Kéo trường Cửa hàng vào ô Rows.
5. Kéo trường Doanh thu vào ô Values.

Kết quả mong đợi (cột giá trị có tiêu đề Sum of Doanh thu):

Khung PivotTable Fields biến mất? Bấm vào PivotTable, vào tab PivotTable Analyze, chọn Field List.

Bốn vùng Rows, Columns, Values, Filters

Kéo thêm Ngành hàng vào Columns, bảng thành ma trận: CH Cầu Giấy có Bánh kẹo 1.050 và Đồ uống 2.550; CH Hải Châu có 700 và 1.000; CH Quận 3 có 2.000 và 1.500. Dòng Grand Total ghi Bánh kẹo 3.750, Đồ uống 5.050. Kéo Ngành hàng ra khỏi Columns trước khi làm tiếp.

Đổi cách tính với Value Field Settings

Mặc định, Excel cộng (Sum) với cột số. Muốn đếm số đơn hoặc tính trung bình mỗi đơn, bạn đổi trong Value Field Settings:

1. Trong ô Values, bấm mũi tên cạnh Sum of Doanh thu, chọn Value Field Settings (hoặc chuột phải vào một số trong bảng, chọn cùng lệnh).
2. Ở thẻ Summarize Values By, chọn Count để đếm số dòng, Average để tính trung bình, Max hoặc Min để tìm lớn nhất, nhỏ nhất.
3. Bấm Number Format để chọn định dạng số có dấu phân cách hàng nghìn, rồi OK.

Với Average, kết quả mong đợi: CH Cầu Giấy 1.200, CH Hải Châu 850, CH Quận 3 khoảng 1.166,7. Đổi lại về Sum trước khi làm tiếp.

Xem tỉ trọng bằng % of Grand Total

Để thấy cả số tiền lẫn tỉ trọng, kéo Doanh thu vào Values lần thứ hai. Mở Value Field Settings của trường thứ hai, sang thẻ Show Values As, chọn % of Grand Total (phần trăm trên tổng cộng). Kết quả mong đợi: CH Cầu Giấy 40,9%, CH Hải Châu 19,3%, CH Quận 3 39,8%, tổng 100%.

Khi bảng có cả dòng và cột, thử thêm % of Row Total để xem trong từng cửa hàng, ngành nào chiếm bao nhiêu.

Nhóm theo tháng, thêm Slicer và PivotChart

Nhóm ngày theo tháng

1. Bỏ Cửa hàng khỏi Rows, kéo Ngày vào Rows. Một số bản Excel tự nhóm ngày theo tháng, quý hoặc năm; nếu đã tự nhóm, bạn có thể bỏ qua bước 2, 3.
2. Nếu vẫn hiện từng ngày, chuột phải vào một ngày trong PivotTable, chọn Group.
3. Trong hộp thoại Grouping, chọn Months. Dữ liệu nhiều năm thì chọn thêm Years để tháng 1 năm này không bị cộng chung với tháng 1 năm khác. Bấm OK.

Kết quả mong đợi: hai dòng Jan 4.150 và Feb 4.650 (Excel tiếng Anh ghi tên tháng bằng tiếng Anh), tổng vẫn 8.800.

Thêm Slicer để lọc bằng một cú bấm

1. Bấm vào trong PivotTable, vào tab PivotTable Analyze, chọn Insert Slicer.
2. Tích Cửa hàng, bấm OK.
3. Bấm "CH Quận 3" trên Slicer: bảng chỉ còn số của cửa hàng này. Giữ Ctrl để chọn nhiều mục, bấm biểu tượng phễu có dấu x ở góc Slicer để bỏ lọc.

Vẽ PivotChart

1. Bấm vào trong PivotTable, vào tab PivotTable Analyze, chọn PivotChart.
2. Chọn Column, kiểu Clustered Column, bấm OK.

Biểu đồ đi theo PivotTable: lọc bằng Slicer hay đổi trường, biểu đồ đổi theo.

Làm mới dữ liệu và 6 lỗi hay gặp

PivotTable không tự tính lại khi dữ liệu nguồn đổi. Sau khi thêm hoặc sửa dữ liệu, chuột phải vào PivotTable, chọn Refresh, hoặc vào tab Data, chọn Refresh All để làm mới cả tệp. Muốn tệp tự làm mới khi mở, vào PivotTable Analyze, chọn Options, sang thẻ Data, tích Refresh data when opening the file.

Khi báo cáo phải cập nhật hằng tuần từ nhiều nguồn cho nhiều người xem, hãy cân nhắc công cụ chuyên cho báo cáo. Bài sức mạnh của Power BI với doanh nghiệp giới thiệu hướng đi này.

Học cùng Vietdemy

Nếu bạn muốn luyện Pivot Table và báo cáo trên dữ liệu gần với công việc thật, khoá Phân tích dữ liệu với Excel & Power BI là khoá tự học trên một bộ dữ liệu bán hàng giả định của chuỗi bán lẻ: làm sạch dữ liệu, phân tích bằng PivotTable, rồi dựng báo cáo, dashboard bằng Power BI. Bài thực hành có tệp mẫu và đáp án, mỗi chương có quiz.

Khoá phù hợp với nhân viên kinh doanh, kế toán, vận hành, marketing đang làm báo cáo bằng Excel và muốn nhanh hơn, ít sai hơn. Hoàn thành khoá, bạn nhận chứng nhận hoàn thành của Vietdemy. Hãy xem đề cương khoá học để chọn chương bắt đầu.

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

Pivot Table có trong phiên bản Excel nào?

PivotTable có trong mọi bản Excel phổ biến, kể cả trên Mac. Khác biệt chủ yếu ở tên tab và vị trí nút giữa các phiên bản.

Làm sao đếm số khách hàng không trùng trong Pivot Table?

Count đếm số dòng, nên khách mua ba lần bị đếm ba lần. Khi tạo PivotTable, hãy tích Add this data to the Data Model; sau đó trong Value Field Settings sẽ có thêm lựa chọn Distinct Count để đếm giá trị không trùng.

Pivot Table có tự cập nhật khi thêm dữ liệu không?

Không. Bạn cần bấm Refresh hoặc Refresh All. Nguồn là Table thì dòng mới được nhận sau khi làm mới; nguồn là vùng cố định thì phải đổi vùng bằng Change Data Source.

Nên dùng Pivot Table hay hàm SUMIFS?

SUMIFS hợp với báo cáo bố cục cố định, tự tính lại ngay khi số thay đổi. PivotTable hợp khi bạn cần khám phá dữ liệu, đổi góc nhìn liên tục bằng kéo thả.

Microsoft, Excel và Power BI là nhãn hiệu của tập đoàn Microsoft. Bài viết không do Microsoft bảo trợ hay xác nhận.