Dùng PivotTable để lập báo cáo trong Excel
Bài toán 1: Lập báo cáo doanh thu bán hàng của mỗi nhân viên bằng cách sử dụng PivotTable.
Giả sử dữ liệu bán hàng của các nhân viên như hình bên dưới:
Để sử dụng PivotTable lập báo cáo doanh thu bán hàng của mỗi nhân viên các bạn thực hiện như sau:
Bước 1: Xác định các cột dữ liệu liên quan đến báo cáo
- Doanh thu bán hàng của nhân viên dựa vào cột Số tiền để tính
- Đối tượng báo cáo ở đây là cột Nhân viên
Bước 2: Tiến hành lập báo cáo bằng PivotTable
1. Chọn bảng hoặc ô (bao gồm các tiêu đề cột) có chứa dữ liệu bạn muốn sử dụng.
2. Vào thẻ Insert -> chọn PivotTable trong nhóm Tables
3. Hộp thoại Create PivotTable xuất hiện, bạn chọn các tùy chọn sau:
- Khung Table/Range: chứa địa chỉ bảng dữ liệu là Table1, có thể chọn lại nếu chưa đúng.
- Tích chọn Use an external data source nếu sử dụng nguồn dữ liệu bên ngoài Excel, ví dụ các tập tin cơ sở dữ liệu hay XML…
- Chọn New Worksheet để đặt PivotTable trong trang tính mới (nếu các bạn muốn báo cáo xuất hiện trong trang tính hiện tại hãy chọn Existing Worksheet, sau đó chọn một ô để đặt PivotTable).
4. Nhấp chọn OK và giao diện xây dựng PivotTable sẽ xuất hiện trên một bảng tính mới như sau:
5. Trong PivotTable Fields hãy chọn các cột liên quan đến báo cáo. Vì chúng ta muốn biết tổng số tiền mỗi nhân viên bán hàng bán được là bao nhiêu nên chúng ta sẽ chọn trường Số tiền và Nhân viên bán hàng. Sau khi chọn trường Nhân viên bán hàng được thêm vào khu vực Rows, còn trường Số tiền được thêm vào khu vực Values. Ngoài ra, bạn có thể nhấp, giữ và kéo một trường đến khu vực mong muốn.
6. PivotTable sẽ tính toán và hiển thị doanh thu bán hàng của mỗi nhân viên như sau:
Trong báo cáo, đổi tên các trường dữ liệu:
A3: Row Labels –> Nhân viên bán hàng
B3: Sum of Số tiền –> Doanh Thu
A12: Grand Total –> Tổng doanh thu
Bài toán 2: Sắp xếp doanh thu bán hàng của các nhân viên từ cao xuống thấp dựa vào báo cáo doanh thu của Bài toán 1
1. Sau khi có được báo cáo doanh thu bán hàng của các nhân viên, bạn nhấp chuột chọn một ô bất kỳ trong cột Doanh thu (không nhấp chọn tiêu đề cột)
2. Vào thẻ Data -> chọn Sort Largest to Smallest trong nhóm Sort & Filter
3. Thu được kết quả là bảng doanh thu bán hàng đã được sắp xếp theo thứ tự doanh thu từ cao xuống thấp của các nhân viên
Bài toán 3: Dựa vào báo cáo doanh thu của Bài toán 1, lọc ra TOP 3 nhân viên có doanh thu bán hàng cao nhất để trao thưởng.
Để tiến hành lọc ra TOP 3 nhân viên có doanh thu bán hàng cao nhất các bạn thực hiện các thao tác sau:
1. Trong cột Nhân viên bán hàng (ô A3), tại vị trí nút lọc (hình mũi tên trỏ xuống) -> nhấp chọn Value Filters -> chọn Top 10…
2. Hộp thoại Top 10 Filter xuất hiện, hiệu chỉnh số 10 thành số 3 và nhấp chọn OK (nếu muốn hiển thị TOP 5 thì bạn chỉnh số 10 thành số 5, còn muốn để TOP 10 thì dữ nguyên, …)
3. Kết quả thu được sau khi lọc là TOP 3, nếu muốn sắp xếp thì nhấp chọn Sort Largest to Smallest trong nhóm Sort & Filter của thẻ Data. Xem hình minh họa
Chú ý: Nếu bạn thay đổi bất kỳ dữ liệu nào trong bảng nguồn của mình, PivotTable sẽ không tự động cập nhật. Để cập nhật, bạn nhấp chọn PivotTable -> thẻ ngữ cảnh PivotTable Tools xuất hiện -> chọn tab Analyze -> chọn Refresh trong nhóm Data.
Bài toán 4: Lập báo cáo doanh thu bán hàng theo tháng, dựa vào báo cáo của Bài toán 1?
1. Yêu cầu của bài toán là lập doanh thu bán hàng theo tháng nên đối tượng báo cáo sẽ là Tháng.
Tại bảng PivotTable Fields của Bài toán 1 ta bỏ chọn trường Nhân viên và tích chọn trường Tháng.
2. PivotTable sẽ tự động điều chỉnh hoặc xoay trục để hiển thị dữ liệu mới. Bạn sẽ thu được báo cáo doanh thu bán hàng theo tháng như hình minh họa:
Cột Row Labels (ô A3) được đổi tên thành Tháng
Cột Sum of Order Amount (ô B3) đổi tên thành Doanh thu
Cột Grand Total (ô A7) đổi tên thành Tổng doanh thu
