Cách thêm và sử dụng các trường được tính toán trong Excel PivotTable

Thông thường, khi bạn tạo bảng tổng hợp, bạn cần mở rộng phân tích của mình và bao gồm nhiều dữ liệu / tính toán hơn trong đó.

Nếu bạn cần một điểm dữ liệu mới có thể lấy được bằng cách sử dụng một điểm dữ liệu hiện có trong bảng tổng hợp, bạn không cần phải quay lại và thêm nó vào dữ liệu nguồn.Thay vào đó, bạn có thể sử dụngCác trường được tính toán bằng PivotTableđể làm điều này.

Trường Tính toán PivotTable là gì?

Hãy bắt đầu với một ví dụ cơ bản về bảng tổng hợp.

Giả sử bạn có một tập dữ liệu về các nhà bán lẻ và bạn tạo một bảng tổng hợp như sau:

Bảng tổng hợp ở trên tóm tắt doanh số và giá trị lợi nhuận của nhà bán lẻ.

Bây giờ, nếu bạn cũng muốn biết tỷ suất lợi nhuận của các nhà bán lẻ này là bao nhiêu (tỷ suất lợi nhuận là "lợi nhuận" chia cho "doanh số bán hàng")?

Có nhiều hướng khác nhau để làm điều đó:

1. Quay lại tập dữ liệu ban đầu và thêm điểm dữ liệu mới này.Vì vậy, bạn có thể chèn một cột mới vào dữ liệu nguồn và tính toán tỷ suất lợi nhuận trong đó.Sau khi hoàn tất, bạn cần cập nhật dữ liệu nguồn của bảng tổng hợp để lấy cột mới này làm một phần của nó.
   • Trong khi phương pháp này hoạt động, bạn cần phải quay lại tập dữ liệu theo cách thủ công và thực hiện các phép tính.Ví dụ: bạn có thể muốn thêm một cột khác để tính doanh thu trung bình trên mỗi đơn vị (doanh số / số lượng).Một lần nữa, bạn phải thêm cột này vào dữ liệu nguồn và sau đó cập nhật bảng tổng hợp.
   • Phương pháp này cũng làm phồng bảng tổng hợp của bạn khi bạn thêm dữ liệu mới vào đó.
2. Thêm các phép tính bên ngoài bảng tổng hợp.Chọn tùy chọn này nếu cấu trúc bảng tổng hợp của bạn không có khả năng thay đổi.Tuy nhiên, nếu bạn thay đổi bảng tổng hợp, các tính toán có thể không cập nhật tương ứng và có thể cung cấp cho bạn kết quả không chính xác hoặc lỗi.Như hình dưới đây, tôi đã tính toán tỷ suất lợi nhuận khi có các nhà bán lẻ trong hàng.Nhưng khi tôi thay đổi nó từ khách hàng này sang khu vực khác, công thức bị sai.
3. Tính toán các trường bằng cách sử dụng bảng tổng hợp.đây làSử dụng dữ liệu PivotTable hiện có và tính toáncách hiệu quả nhất.Hãy coi các trường được tính toán là các cột ảo mà bạn thêm bằng cách sử dụng các cột hiện có trong bảng tổng hợp của mình.Sử dụng các trường được tính toán trong bảng tổng hợp có nhiều lợi ích (như chúng ta sẽ thấy ở phần sau):
   • Nó không yêu cầu bạn làm việc với các công thức hoặc cập nhật dữ liệu nguồn.
   • Nó có thể mở rộng vì nó tự động tính đến bất kỳ dữ liệu mới nào mà bạn có thể thêm vào bảng tổng hợp.Sau khi thêm trường được tính toán, bạn có thể sử dụng trường đó giống như bất kỳ trường nào khác trong bảng tổng hợp.
   • Nó rất dễ dàng để cập nhật và quản lý.Ví dụ: nếu một số liệu thay đổi hoặc bạn cần thay đổi một phép tính, bạn có thể dễ dàng thực hiện việc đó từ chính bảng tổng hợp.

Thêm các trường được tính toán vào bảng tổng hợp

Hãy xem cách thêm trường được tính toán trong bảng tổng hợp vào bảng tổng hợp hiện có.

Giả sử bạn có một bảng tổng hợp trông như thế này và bạn muốn tính toán tỷ suất lợi nhuận cho từng nhà bán lẻ:

Dưới đây là các bước để thêm trường được tính toán PivotTable:

Sau khi thêm trường được tính toán, trường đó sẽ xuất hiện dưới dạng một trong các trường trong danh sách Trường PivotTable.

Bây giờ bạn có thể sử dụng trường được tính toán này như bất kỳ trường nào khác của bảng tổng hợp (lưu ý rằng bạn không thể sử dụng trường được tính toán trong bảng tổng hợp làm bộ lọc báo cáo hoặc bộ cắt).

Như tôi đã đề cập trước đây, lợi ích của việc sử dụng trường được tính toán bằng bảng tổng hợp là bạn có thể thay đổi cấu trúc của bảng tổng hợp và nó sẽ tự động điều chỉnh.

Ví dụ: nếu tôi kéo và thả một khu vực vào khu vực hàng, bạn sẽ nhận được kết quả như sau, nơi các giá trị biên lợi nhuận cho nhà bán lẻ và khu vực được báo cáo.

 

Trong ví dụ trên, tôi đã sử dụng một công thức đơn giản (= Lợi nhuận / Doanh số) để chèn trường được tính toán.Tuy nhiên, bạn cũng có thể sử dụng một số công thức nâng cao.

Trước khi tôi chỉ cho bạn một ví dụ về việc sử dụng công thức nâng cao để tạo trường được tính toán PivotTable, đây là một số điều bạn phải biết:

• Bạn không thể sử dụng tham chiếu hoặc phạm vi đã đặt tên khi tạo trường được tính toán PivotTable.Điều này sẽ loại trừ nhiều công thức như VLOOKUP, INDEX, OFFSET, v.v.Tuy nhiên, bạn có thể sử dụng các công thức hoạt động mà không cần tham chiếu (ví dụ: SUM, IF, COUNT, v.v.).
• Bạn có thể sử dụng hằng số trong công thức.Ví dụ: nếu bạn muốn biết doanh số dự báo sẽ tăng 10%, bạn sẽ sử dụng công thức = Doanh số * 1.1 (trong đó 1.1 là hằng số).
• Thực hiện theo thứ tự ưu tiên trong các công thức tạo các trường được tính toán.Cách tốt nhất là sử dụng dấu ngoặc đơn để đảm bảo rằng bạn không phải nhớ mức độ ưu tiên.

Bây giờ, hãy xem một ví dụ về việc tạo trường được tính toán bằng công thức nâng cao.

Giả sử bạn có tập dữ liệu được hiển thị bên dưới và bạn cần hiển thị doanh số dự báo trong bảng tổng hợp.

Đối với các giá trị dự báo, bạn sẽ sử dụng mức tăng doanh số 300% cho các nhà bán lẻ lớn (doanh số trên 5 triệu) và mức tăng doanh số 300% cho các nhà bán lẻ vừa và nhỏ (doanh số dưới 10 triệu).

Lưu ý: Các số liệu bán hàng ở đây là giả mạo và đã được sử dụng để minh họa cho các ví dụ trong hướng dẫn này.

Đây là cách thực hiện:

Thao tác này sẽ thêm một cột mới vào PivotTable với các giá trị dự báo bán hàng.

Vấn đề trường được tính toán trong bảng tổng hợp

Các trường được tính toán là một tính năng tuyệt vời thực sự làm tăng giá trị của bảng tổng hợp với các phép tính trường, trong khi vẫn giữ cho mọi thứ có thể mở rộng và quản lý được.

Tuy nhiên, có một vấn đề với các trường được tính toán PivotTable mà bạn phải hiểu trước khi sử dụng chúng.

Giả sử, tôi có một bảng tổng hợp như bên dưới và tôi sử dụng một trường được tính toán để lấy số lượng bán hàng dự báo.

Lưu ý rằng tổng phụ và tổng không chính xác.

Trong khi các giá trị này sẽ thêm các giá trị dự báo bán hàng riêng biệt cho từng nhà bán lẻ, trên thực tế, nó tuân theo cùng một công thức trường được tính toán mà chúng tôi đã tạo.

Vì vậy, đối với South Total, trong khi giá trị phải là 22,824,000, South Total báo cáo không chính xác là 22,287,000.Điều này xảy ra vì nó sử dụng công thức 21,225,800 * 1.05 để nhận giá trị.

Thật không may, bạn không thể sửa chữa vấn đề này.

Cách tốt nhất để xử lý điều này là xóa tổng phụ và tổng lớn khỏi bảng tổng hợp.

Bạn cũng có thể vượt qua Trình bày bởi DebraMột số cách giải quyết sáng tạo để đối phó với vấn đề này.

Làm cách nào để sửa đổi hoặc xóa trường được tính toán trong bảng tổng hợp?

Sau khi bạn tạo trường tính toán PivotTable, bạn có thể sửa đổi hoặc xóa công thức bằng các bước sau:

Làm thế nào để có được danh sách tất cả các công thức trường được tính toán?

Nếu bạn tạo nhiều trường được tính toán trong PivotTable, bạn không phải lo lắng về việc theo dõi các công thức được sử dụng trong mỗi trường.

Excel cho phép bạn nhanh chóng tạo danh sách tất cả các công thức được sử dụng để tạo các trường được tính toán.

Dưới đây là các bước để nhanh chóng nhận được danh sách tất cả các công thức trường được tính toán:

• Chọn bất kỳ ô nào trong bảng tổng hợp.
• Đi tới Công cụ PivotTable -> Phân tích -> Trường, Mục và Bộ -> Liệt kê Công thức.

Khi bạn nhấp vào công thức danh sách, Excel sẽ tự động chèn một trang tính mới với chi tiết của tất cả các trường / mục được tính toán mà bạn đã sử dụng trong bảng tổng hợp.

Đây có thể là một công cụ rất hữu ích nếu bạn phải gửi công việc cho khách hàng hoặc chia sẻ nó với nhóm của mình.