Cách tính phần trăm thay đổi trong Pivot Table trong Excel

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.

Đây là ví dụ về sheet chúng ta sẽ sử dụng trong bài.

Ví dụ 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.

Hộp thoại Create 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.

Nhập tên vào hộp ở Ribbon

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.

Chọn bảng muốn sử dụng

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.

Nhóm ngày theo tháng

Chọn nhóm muốn sử dụng, trong ví dụ này chọn YearsMonths.

Chọn Months và Years

Bây giờ chúng ta sẽ sử dụng trường YearMonth để phân tích. Các tháng vẫn được đặt tên là Order Date.

Chọn trường thêm vào báo cáo

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.

Kéo trường Year vào Filter

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:

Bảng PivotTable sau khi được định dạng

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 % 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).

Chọn Previous

Bảng PivotTable bây giờ hiển thị cả giá trị và phần trăm thay đổi.

Bảng PivotTable hiển thị 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.

Đặt tên cột thứ hai 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.

Thay đổi các tùy chọn trong Edit Formatting Rule

Click vào OK và định dạng có điều kiện được áp dụng cho PivotTable.

Áp dụng định dạng có điều kiện 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! 

Thứ Năm, 02/05/2019 08:16
53 👨 662