GetPivotData trong Excel - Thủ thuật Excel

Bạn vừa tạo một bảng tổng hợp trong Excel. Bạn nhấp vào bên ngoài bảng tổng hợp. Bạn xây dựng một công thức Excel. Bạn sao chép công thức này xuống tất cả các hàng trong bảng tổng hợp. Phép tính báo cáo câu trả lời sai cho tất cả trừ hàng đầu tiên của bảng tổng hợp. Bạn vừa bị kích thích bởi "tính năng" Tạo GetPivotData. Chúng ta hãy xem đây là gì và làm thế nào để ngăn chặn nó.

Chỉ cần ngăn chặn sự cố GetPivotData - Phương pháp nhanh

Cách nhanh nhất để ngăn Excel tạo GetPivotData là nhập công thức của bạn mà không sử dụng chuột hoặc bất kỳ phím mũi tên nào. Nếu bạn chỉ nhập =E5/D5, thì Excel sẽ tạo một công thức tương đối "bình thường" có thể được sao chép xuống tất cả các hàng liền kề với bảng tổng hợp.

Ngăn chặn sự cố GetPivotData - Phương pháp vĩnh viễn

Có một cài đặt ẩn để ngăn Excel tạo GetPivotData. Nó ẩn trong Excel 2003 nhiều hơn trong Excel 2007.

Trong Excel 2007, hãy làm theo các bước sau:

• Tạo bảng tổng hợp trong Excel 2007
• Đảm bảo rằng ô hiện hoạt nằm bên trong bảng tổng hợp
• Nhìn ở phía bên trái của tab Tùy chọn công cụ PivotTable của ruy-băng. Có một nút Tùy chọn. Đừng nhấp vào nút Tùy chọn! Ở bên phải của nút Tùy chọn là một mũi tên thả xuống. Nhấp vào mũi tên thả xuống. Bỏ chọn Tạo GetPivotData.
• Bây giờ bạn có thể nhập công thức bằng chuột, phím mũi tên hoặc nhập.

Trong Excel 2003 trở về trước, hãy làm theo các bước sau:

• Chọn công cụ, tùy chỉnh
• Có ba tab trong hộp thoại Tùy chỉnh. Chọn tab Lệnh ở giữa.
• Trong hộp danh sách bên trái, hãy chọn mục thứ 7, Dữ liệu
• Trong hộp danh sách bên phải, cuộn gần hết xuống dưới cùng. Biểu tượng thứ 8 từ cuối danh sách là Tạo GetPivotData. Kéo biểu tượng này từ hộp danh sách và thả nó vào giữa bất kỳ thanh công cụ hiện có nào.
• Nhấp vào nút Đóng để đóng hộp thoại.
• Nhấp vào biểu tượng bạn vừa thêm vào thanh công cụ Excel. Thao tác này sẽ tắt tính năng này. Bây giờ bạn có thể nhập công thức bằng chuột mà không cần tạo các hàm GetPivotData.

Đây là một mẹo bổ sung: Tôi đã từng coi thường tính năng này và chỉ đơn giản là muốn nó tắt mọi lúc. Tôi đã thêm biểu tượng vào thanh công cụ của mình, tắt biểu tượng, sau đó xóa nó khỏi thanh công cụ. Bây giờ… tôi đã nguôi ngoai một chút. Tôi có thể thấy rằng biểu tượng này ngẫu nhiên có một số công dụng tốt. Vì vậy, tôi đã tạo một bảng tổng hợp trong Excel 2003 và đảm bảo rằng con trỏ ô nằm trong bảng tổng hợp. Sau đó, tôi đã sử dụng hộp thoại tùy chỉnh để kéo biểu tượng Tạo GetPivotData vào Thanh công cụ Bảng Pivot của mình. Bây giờ - khi tôi ở trong bảng tổng hợp, tôi có thể chọn bật hoặc tắt tính năng này.

Tại sao Microsoft làm điều này? Có cách sử dụng nào tốt cho GetPivotData không?

GetPivotData phải làm gì? Rõ ràng, Microsoft thích chức năng này vì họ đã ép buộc nó dựa trên hàng triệu người không nghi ngờ sử dụng bảng xoay.

GetPivotData sẽ trả về bất kỳ ô hiển thị nào từ bảng tổng hợp. Tuy nhiên, thay vì trỏ đến một địa chỉ ô, bạn có thể mô tả giá trị như một giao điểm của các giá trị trường khác nhau.

Khi bảng tổng hợp thay đổi, GetPivotData sẽ tiếp tục trả về cùng một dữ liệu logic từ bảng tổng hợp, miễn là dữ liệu đó vẫn hiển thị trong bảng tổng hợp. Điều này có thể quan trọng nếu bạn đang thay đổi từ tháng này sang tháng khác trong trường trang của bảng tổng hợp và bạn muốn luôn trả lại doanh số cho một khách hàng cụ thể. Khi danh sách khách hàng thay đổi mỗi tháng, vị trí của khách hàng sẽ thay đổi. Bằng cách sử dụng =GETPIVOTDATA, bạn có thể đảm bảo rằng bạn trả về giá trị thích hợp từ bảng tổng hợp.

=GETPIVOTDATAluôn bắt đầu bằng hai đối số cụ thể. Sau đó, nó tùy chọn có các cặp đối số bổ sung.

Đây là hai đối số bắt buộc:

1. Tên của trường dữ liệu. Đây có thể là "Tổng doanh thu" hoặc đơn giản là "Doanh thu".
2. Bất kỳ ô nào nằm "bên trong" bảng tổng hợp. Bạn có biết rằng bảng tổng hợp của bạn có một cái tên? Bạn có thể không biết điều này vì bạn không thể tìm thấy tên trong giao diện Excel. Bạn sẽ phải vào VBA để tìm hiểu tên của bảng xoay. Vì vậy, Microsoft đã dễ dàng hơn khi cho phép bạn xác định bảng tổng hợp bằng cách trỏ đến bất kỳ ô nào trong bảng tổng hợp. Mặc dù bạn có thể chọn bất kỳ ô nào, nhưng an toàn nhất là chọn ô ở góc trên cùng bên trái. Có khả năng bảng tổng hợp của bạn có thể bị thu hẹp đối với một tổ hợp các trường trang cụ thể. Trong trường hợp đó, sử dụng ô góc trên cùng bên trái sẽ đảm bảo bạn tiếp tục trỏ vào bên trong bảng tổng hợp.

Sau đó, bạn tiếp tục hàm với các cặp đối số bổ sung. Mỗi cặp bao gồm một tên trường và một giá trị.

GetPivotData chỉ có thể trả về giá trị hiển thị trong bảng Pivot

Sau khi bạn thấy một vài hàm GetPivotData hoạt động, bạn có thể nghĩ rằng chúng mạnh hơn thực tế. Trên thực tế, hàm sẽ chỉ hoạt động nếu giá trị cụ thể hiển thị trong bảng tổng hợp. Hãy xem xét hình ảnh dưới đây.

• Trong ô A2, GETPIVOTDATA trả về doanh số tháng 1 năm 2004 của ABC tại Miền Trung. Đây là lấy 80003 từ ô G19.
• Tuy nhiên, công thức trong A6 không thành công. Nó đang yêu cầu doanh số bán hàng của ABC trên tất cả các khu vực. Bảng tổng hợp hiển thị tổng ABC cho miền Trung, tổng ABC cho miền Đông, tổng ABC cho miền Tây, nhưng nó không bao giờ hiển thị tổng ABC cho tất cả các vùng, vì vậy kết quả là #REF! lỗi.
• Nếu bạn xoay trường vùng lên đến vùng trang, công thức trong A6 sẽ bắt đầu hoạt động, nhưng công thức trong A2 & A4 sẽ bắt đầu trả về lỗi #REF !. Nếu bạn chọn Trung tâm từ trình đơn thả xuống Khu vực, thì tất cả bốn công thức sẽ hoạt động.
• Việc tắt Tổng số trong hộp thoại Tùy chọn PivotTable SẼ khiến nhiều hàm GetPivotData bắt đầu trả về lỗi #REF! lỗi.