Thông thường, khi bạn tạo bảng Pivot , bạn cần phải 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 như một phần của nó.

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 các điểm dữ liệu hiện có trong Bảng Pivot, bạn không cần quay lại và thêm nó vào dữ liệu nguồn. Thay vào đó, bạn có thể sử dụng Trường Tính toán Bảng Pivot để thực hiện việc này.

Trường Tính toán Bảng Pivot là gì?

Hãy bắt đầu với một ví dụ cơ bản về Pivot Table.

Giả sử bạn có một tập dữ liệu về các nhà bán lẻ và bạn tạo Bảng Pivot như được hiển thị bên dưới:

Excel Pivot Table Calculated Field - Data

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

Bây giờ, điều gì sẽ xảy ra 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 (trong đó tỷ suất lợi nhuận là “Lợi nhuận” chia cho “Doanh số”).

Có một vài cách để làm điều này:

  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 thực hiện việc này, bạn cần cập nhật dữ liệu nguồn của Pivot Table để lấy cột mới này làm một phần của nó.
    • Mặc dù phương pháp này là một khả năng, bạn sẽ cần phải quay lại tập dữ liệu và thực hiện các tính toán theo cách thủ công. Ví dụ: bạn có thể cầ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 sẽ phải thêm cột này vào dữ liệu nguồn của mình 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 Pivot 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 Pivot . Đây có thể là một tùy chọn nếu cấu trúc Pivot Table của bạn không có khả năng thay đổi. Nhưng nếu bạn thay đổi bảng Pivot, 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ả sai hoặc lỗi. Như được hiển thị bên dưới, tôi đã tính toán Biên 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 sang khu vực, công thức đã xuất hiện lỗi. Pivot Table Calculated Field - Error
  3. Sử dụng Trường Tính toán Bảng Tổng hợp . Đây là cách hiệu quả nhất để sử dụng dữ liệu Pivot Table hiện có và tính toán số liệu mong muốn. Hãy coi Trường được Tính là một cột ảo mà bạn đã thêm bằng cách sử dụng các cột hiện có từ Bảng Tổng hợp. Có rất nhiều lợi ích khi sử dụng Trường Tính toán trong Bảng Pivot (như chúng ta sẽ thấy trong một phút nữa):
    • Nó không yêu cầu bạn xử lý công thức hoặc cập nhật dữ liệu nguồn.
    • Nó có thể mở rộng vì nó sẽ tự động tính đến bất kỳ dữ liệu mới nào mà bạn có thể thêm vào Pivot Table của mình. Sau khi thêm Trường 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 của mình.
    • Nó dễ dàng cập nhật và quản lý. Ví dụ: nếu số liệu thay đổi hoặc bạn cần thay đổi phép tính, bạn có thể dễ dàng thực hiện điều đó từ chính Bảng tổng hợp.

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

Hãy xem cách thêm Trường Tính toán Bảng Pivot trong Bảng Pivot hiện có.

Giả sử bạn có Bảng xoay vòng như được hiển thị bên dưới và bạn muốn tính toán tỷ suất lợi nhuận cho từng nhà bán lẻ:

Pivot Table Calculated Field - Data

Dưới đây là các bước để thêm Trường Tính toán trong Bảng Pivot:

  • Chọn bất kỳ ô nào trong Bảng tổng hợp.
  • Đi tới Công cụ bảng Pivot -> Phân tích -> Tính toán -> Trường, Mục và Bộ. Pivot Table Calculated Field - Analyze Tab
  • Từ menu thả xuống, hãy chọn Trường tính toán. Pivot Table Calculated Field - Calculated Field
  • Trong hộp thoại Chèn tính toán được Filed:
    • Đặt tên cho nó bằng cách nhập nó vào trường Tên.
    • Trong trường Công thức, hãy tạo công thức bạn muốn cho trường được tính toán. Lưu ý rằng bạn có thể chọn từ các tên trường được liệt kê bên dưới nó. Trong trường hợp này, công thức là ‘= Lợi nhuận / Doanh số’. Bạn có thể nhập tên trường theo cách thủ công hoặc nhấp đúp vào tên trường được liệt kê trong hộp Trường.
  • Nhấp vào Thêm và đóng hộp thoại.

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

Pivot Table Calculated Field - Field List

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 trong Bảng Pivot (lưu ý rằng bạn không thể sử dụng Trường được tính trong Bảng Pivot 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 Tính toán Bảng Pivot là bạn có thể thay đổi cấu trúc của Bảng Pivot và nó sẽ tự động điều chỉnh.

Ví dụ: nếu tôi kéo và thả khu vực trong khu vực hàng, bạn sẽ nhận được kết quả như hình dưới đây, trong đó giá trị Biên lợi nhuận được báo cáo cho các nhà bán lẻ cũng như khu vực.

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 một 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 tính toán bảng tổng hợp, đâ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 trong khi tạo Trường Tính toán Bảng Pivot. Điều đó sẽ loại trừ rất 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 có thể hoạt động mà không cần tham chiếu (chẳng hạn như SUM, IF, COUNT, v.v.).
  • Bạn có thể sử dụng một hằng số trong công thức. Ví dụ: nếu bạn muốn biết doanh số dự báo được dự báo sẽ tăng 10%, bạn có thể sử dụng công thức = Doanh số * 1.1 (trong đó 1,1 là hằng số).
  • Thứ tự ưu tiên được tuân theo trong công thức tạo 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 bạn không phải nhớ thứ tự ưu tiên.

Bây giờ, chúng ta hãy xem một ví dụ về việc sử dụng công thức nâng cao để tạo Trường tính toán.

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

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

Lưu ý: Các con số 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 để làm điều này:

  • Chọn bất kỳ ô nào trong Bảng tổng hợp.
  • Đi tới Công cụ bảng Pivot -> Phân tích -> Tính toán -> Trường, Mục và Bộ. Pivot Table Calculated Field - Analyze Tab
  • Từ menu thả xuống, hãy chọn Trường tính toán. Pivot Table Calculated Field - Calculated Field
  • Trong hộp thoại Chèn tính toán được Filed:
    • Đặt tên cho nó bằng cách nhập nó vào trường Tên.
    • Trong trường Công thức, hãy sử dụng công thức sau: = IF (Vùng = ”Miền Nam”, Doanh số * 1,05, Doanh số * 1,1) Pivot Table Calculated Field - advanced formula
  • Nhấp vào Thêm và đóng hộp thoại.

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

Pivot Table Calculated Field - advanced formula result

Sự cố với các trường được tính toán trong bảng tổng hợp

Trường tính toán là một tính năng tuyệt vời thực sự nâng cao giá trị của Bảng tổng hợp của bạn 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 Trường được Tính toán trong Bảng Pivot mà bạn phải biết trước khi sử dụng nó.

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

Pivot Table Calculated Field - advanced formula result

Lưu ý rằng tổng phụ và tổng không đúng.

Mặc dù những thứ này sẽ thêm giá trị dự báo bán hàng riêng lẻ cho từng nhà bán lẻ, nhưng 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 sai 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ị.

Pivot Table Calculated Field - Wrong Value

Thật không may, không có cách nào bạn có thể sửa lỗi này.

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

Bạn cũng có thể xem qua một số cách giải quyết sáng tạo mà Debra đã chỉ ra để xử lý vấn đề này.

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

Khi bạn đã tạo Trường Tính toán trong Bảng Pivot, bạn có thể sửa đổi hoặc xóa công thức bằng cách sử dụng các bước sau:

  • Chọn bất kỳ ô nào trong Bảng tổng hợp.
  • Đi tới Công cụ bảng Pivot -> Phân tích -> Tính toán -> Trường, Mục và Bộ.
  • Từ menu thả xuống, hãy chọn Trường tính toán.
  • Trong trường Tên, bấm vào mũi tên thả xuống (mũi tên nhỏ xuống ở cuối trường). Pivot Table Calculated Field - drop down
  • Từ danh sách, hãy chọn trường được tính toán mà bạn muốn xóa hoặc sửa đổi. Pivot Table Calculated Field - select CF
  • Thay đổi công thức trong trường hợp bạn muốn sửa đổi nó hoặc nhấp vào Xóa trong trường hợp bạn muốn xóa nó. Pivot Table Calculated Field - modify delete CF

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 Pivot Table Calculated, đừng lo lắng về việc theo dõi công thức được sử dụng trong từng 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 Trường 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ụ Bảng Pivot -> Phân tích -> Trường, Mục & Bộ -> Liệt kê Công thức. Pivot Table Calculated Field - List Formulas

Ngay sau khi bạn bấm vào Công thức Danh sách, Excel sẽ tự động chèn một trang tính mới có thông tin chi tiết về 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.

Trả lời

Email của bạn sẽ không được hiển thị công khai.