Hầu hết mọi người lần đầu tiên gặp phải GETPIVOTDATA khi họ cố gắng xây dựng công thức bên ngoài bảng tổng hợp sử dụng các số trong bảng tổng hợp. Ví dụ: phần trăm phương sai này sẽ không sao chép xuống các tháng khác do Excel chèn các hàm GETPIVOTDATA.
Excel sẽ chèn GETPIVOTDATA bất cứ khi nào bạn sử dụng chuột hoặc phím mũi tên để trỏ đến một ô bên trong bảng tổng hợp trong khi xây dựng công thức bên ngoài bảng tổng hợp.
Nhân tiện, nếu bạn không muốn hàm GETPIVOTDATA xuất hiện, chỉ cần nhập một công thức chẳng hạn như =D5/C5-1không sử dụng chuột hoặc các phím mũi tên để trỏ đến các ô. Công thức đó sao chép mà không có bất kỳ vấn đề nào.
Đây là tập dữ liệu chứa một số gói mỗi tháng cho mỗi cửa hàng. Ngoài ra còn có doanh số bán hàng thực tế mỗi tháng cho mỗi cửa hàng cho những tháng đã hoàn thành. Mục tiêu của bạn là xây dựng một báo cáo hiển thị thực tế cho những tháng đã hoàn thành và lập kế hoạch cho những tháng tương lai.
Xây dựng bảng tổng hợp với Store in Rows. Đặt tháng và nhập vào các cột. Bạn nhận được báo cáo được hiển thị bên dưới, với Thực tế tháng 1, Kế hoạch tháng 1 và Kế hoạch + Thực tế tháng 1 hoàn toàn vô nghĩa.
Nếu bạn chọn một ô tháng và đi tới Cài đặt Trường, bạn có thể thay đổi Tổng phụ thành Không có.
Điều này sẽ loại bỏ Kế hoạch + Thực tế vô dụng. Nhưng bạn vẫn phải loại bỏ các cột kế hoạch từ tháng Giêng đến tháng Tư. Không có cách nào tốt để thực hiện việc này bên trong bảng xoay.
Vì vậy, quy trình làm việc hàng tháng của bạn trở thành:
- Thêm các thông tin thực tế cho tháng mới vào tập dữ liệu.
- Xây dựng một bảng tổng hợp mới từ đầu.
- Sao chép bảng tổng hợp và dán dưới dạng các giá trị để nó không phải là bảng tổng hợp nữa.
- Xóa các cột mà bạn không cần.
Có một con đường tốt hơn để đi. Hình rất nén sau đây cho thấy một trang tính Excel mới được thêm vào sổ làm việc. Đây là tất cả chỉ là Excel thẳng, không có bảng tổng hợp. Điều kỳ diệu duy nhất là một hàm IF ở hàng 4 chuyển đổi từ Thực tế sang Kế hoạch, dựa trên ngày trong ô P1.
Ô đầu tiên cần được điền vào là January Actual cho Baybrook. Bấm vào ô đó và nhập dấu bằng.
Sử dụng chuột, điều hướng trở lại bảng tổng hợp. Tìm ô cho Thực tế tháng Giêng cho Baybrook. Nhấp vào ô đó và nhấn Enter. Như thường lệ, Excel xây dựng một trong những hàm GETPIVOTDATA phiền phức không thể sao chép được.
Nhưng hôm nay, chúng ta hãy nghiên cứu cú pháp của GETPIVOTDATA.
Đối số đầu tiên bên dưới là trường số "Bán hàng". Đối số thứ hai là ô chứa bảng tổng hợp. Các cặp đối số còn lại là tên trường và giá trị. Bạn có thấy công thức được tạo tự động đã làm gì không? Nó được mã hóa cứng "Baybrook" làm tên của cửa hàng. Đó là lý do tại sao bạn không thể sao chép các công thức GETPIVOTDATA được tạo tự động này. Họ thực sự mã hóa tên khó thành công thức. Mặc dù bạn không thể sao chép các công thức này, nhưng bạn có thể chỉnh sửa chúng. Trong trường hợp này, sẽ tốt hơn nếu bạn chỉnh sửa công thức để trỏ đến ô $ D6.
Hình dưới đây cho thấy công thức sau khi bạn chỉnh sửa nó. Đã qua rồi "Baybrook", "Jan" và "Thực tế". Thay vào đó, bạn đang trỏ đến $ D6, E $ 3 và E $ 4.
Sao chép công thức này và sau đó chọn Dán công thức đặc biệt trong tất cả các ô số khác.
Bây giờ đây là quy trình làm việc hàng tháng của bạn:
- Xây dựng một bảng tổng hợp xấu xí mà không ai có thể nhìn thấy.
- Thiết lập trang tính báo cáo.
Mỗi tháng, bạn phải:
- Dán các hướng dẫn thực tế mới bên dưới dữ liệu.
- Làm mới bảng tổng hợp xấu xí.
-
Thay đổi ô P1 trên bảng báo cáo để phản ánh tháng mới. Tất cả các số cập nhật.
Bạn phải thừa nhận rằng việc sử dụng báo cáo lấy số từ bảng tổng hợp mang lại cho bạn điều tốt nhất của cả hai thế giới. Bạn có thể tự do định dạng báo cáo theo những cách mà bạn không thể định dạng bảng tổng hợp. Hàng trống cũng được. Bạn có thể có ký hiệu tiền tệ trên hàng đầu tiên và hàng cuối cùng nhưng không được ở giữa. Bạn cũng nhận được gạch chân kép dưới tổng số.
Cảm ơn @iTrainerMX đã đề xuất tính năng này.
