Pivot Table là công cụ báo cáo tuyệt vời được tích hợp sẵn trong Excel. Mặc dù chức năng chính của bảng này là tóm tắt dữ liệu lớn nhưng bạn có sử dụng chúng để tính phần trăm thay đổi giữa các giá trị. Bài viết này sẽ hướng dẫn các bạn cách tính phần trăm thay đổi trong Pivot Table.
- Sử dụng Pivot Table trong Google Docs Spreadsheet
- Dùng VBA trong Excel để tạo và sửa chữa PivotTable
- 8 công cụ thuận tiện trong Excel có thể bạn chưa biết
Đây là ví dụ về sheet chúng ta sẽ sử dụng trong bài.
Đây là ví dụ về bảng doanh thu của một công ty, gồm các cột ngày đặt hàng, tên khách hàng, người bán hàng, tổng doanh số bán hàng và một số dữ liệu khác.
Trước tiên, chúng ta sẽ định dạng phạm vi giá trị dưới dạng bảng trong Excel và sau đó tạo Pivot Table để hiển thị phần trăm thay đổi.
Định dạng phạm vi dưới dạng bảng
Nếu phạm vi dữ liệu chưa được định dạng dưới dạng bảng, bạn nên thực hiện thao tác này. Dữ liệu được định dạng thành bảng sẽ thuận tiện hơn ở dạng ô trong trang tính, đặc biệt khi sử dụng PivotTable.
Để định dạng phạm vi dưới dạng bảng, chọn phạm vi ô và click vào Insert > Table.
Kiểm tra xem phạm vi đó có đúng không, tích vào ô My table has header nếu có tiêu đề trong dòng đầu tiên của phạm vi, sau đó click vào OK.
Bây giờ, phạm vi được định dạng như một bảng. Đặt tên bảng sẽ giúp bạn dễ dàng tham chiếu hơn khi tạo PivotTable, biểu đồ và công thức. Click vào tab Design trong Table Tools và nhập tên vào hộp ở Ribbon. Bảng này ở đây được đặt tên là Sales.
Tạo PivotTable để hiển thị phần trăm thay đổi
Bây giờ, hãy bắt đầu với việc tạo PivotTable. Từ bảng mới, click Insert > PivotTable. Cửa sổ Create PivotTable sẽ xuất hiện, nó sẽ tự động phát hiện bảng của bạn nhưng bạn có thể chọn bảng hoặc phạm vi muốn sử dụng cho PivotTable ở bước này.
Nhóm các ngày theo tháng
Sau đó, chúng ta sẽ kéo trường ngày muốn nhóm vào khu vực hàng của PivotTable. Trong ví dụ này là trường có tên Order Date.
Từ phiên bản Excel 2016 trở đi, giá trị ngày sẽ tự động được nhóm theo năm, quý và tháng. Nếu đang dùng phiên bản cũ hơn hoặc muốn thay đổi kiểu nhóm, click chuột phải vào một ô có chứa dữ liệu ngày và sau đó chọn lệnh Group.
Chọn nhóm muốn sử dụng, trong ví dụ này chọn Years và Months.
Bây giờ chúng ta sẽ sử dụng trường Year và Month để phân tích. Các tháng vẫn được đặt tên là Order Date.
Thêm các trường giá trị vào PivotTable
Di chuyển trường Year từ Rows vào Filter, thao tác này giúp người dùng lọc PivotTable theo năm thay vì xáo trộn PivotTable với quá nhiều thông tin. Kéo trường chứa các giá trị [tổng doanh số bán hàng trong ví dụ này] bạn muốn tính toán và thể hiện thay đổi ở Values hai lần.
Cả hai trường giá trị sẽ được mặc định là tổng và hiện không có định dạng. Chúng ta sẽ giữ giá trị trong cột đầu tiên ở dạng tổng và cần định dạng lại chúng. Click chuột phải vào số trong cột thứ nhất, chọn Number Formatting từ menu shortcut. Chọn định dạng Accounting với số thập phân 0 từ hộp thoại Format Cells.
PivotTable bây giờ trông sẽ như thế này:
Tạo cột thay đổi tỷ lệ phần trăm
Click chuột phải vào một giá trị trong cột thứ hai, trỏ đến Show Values và sau đó click vào tùy chọn % Difference from.
Chọn [Previous] trong Base Item, để giá trị tháng hiện tại luôn được so sánh với các tháng trước [trong trường Order Date].
Bảng PivotTable bây giờ hiển thị cả giá trị và phần trăm thay đổi.
Click vào ô chứa Row Labels và gõ Month làm tiêu đề cho cột đó, sau đó click vào ô tiêu đề cho cột giá trị thứ hai và gõ Variance.
Thêm mũi tên tăng, giảm
Để chỉnh sửa PivotTable này, chúng ta sẽ thêm một số mũi tên xanh và đỏ để thể hiện sự tăng giảm trong doanh số bán hàng.
Click vào bất cứ giá trị nào trong cột thứ hai và click vào Home > Conditional Formatting > New Rule. Trong cửa sổ Edit Formatting Rule thực hiện theo các bước dưới đây.
Bước 1. Chọn All cells showing Variance values for Order Date.
Bước 2. Chọn Icon Sets từ danh sách Format Style.
Bước 3. Chọn hình tam giác đỏ, xanh và màu hổ phách từ danh sách Icon Style.
Bước 4. Trong cột Type, thay đổi tùy chọn Percentage thành Number để thay đổi cột Value thành 0.
Click vào OK và định dạng có điều kiện được áp dụng cho PivotTable.
PivotTable là một công cụ đáng kinh ngạc và là một trong những cách đơn giản nhất để hiển thị phần trăm thay đổi theo thời gian cho các giá trị.
Chúc các bạn thực hiện thành công!