Ngân sách được thực hiện ở cấp cao nhất - doanh thu theo dòng sản phẩm theo khu vực theo tháng. Thực tế tích lũy chậm dần theo thời gian - hóa đơn theo hóa đơn, chi tiết đơn hàng theo chi tiết đơn hàng. So sánh tệp Ngân sách nhỏ với dữ liệu thực tế khổng lồ đã là một khó khăn mãi mãi. Tôi thích thủ thuật này của Rob Collie, hay còn gọi là PowerPivotPro.com.
Để thiết lập ví dụ, bạn có một bảng ngân sách 54 hàng: 1 hàng mỗi tháng cho mỗi vùng trên mỗi sản phẩm.
Tệp hóa đơn ở cấp độ chi tiết: 422 hàng cho đến nay trong năm nay.
Không có hàm VLOOKUP nào trên thế giới cho phép bạn khớp hai tập dữ liệu này. Tuy nhiên, nhờ Power Pivot (hay còn gọi là Mô hình Dữ liệu trong Excel 2013+), điều này trở nên dễ dàng.
Bạn cần tạo các bảng nhỏ nhỏ mà tôi gọi là “bộ nối” để liên kết hai tập dữ liệu lớn hơn.
Trong trường hợp của tôi, Sản phẩm, Khu vực và Ngày là điểm chung giữa hai bảng. Bảng Sản phẩm là một bảng bốn ô nhỏ. Ditto cho Vùng. Tạo mỗi bảng trong số đó bằng cách sao chép dữ liệu từ một bảng và sử dụng Xóa bản sao.
Bảng lịch bên phải thực sự khó tạo hơn. Dữ liệu ngân sách có một hàng mỗi tháng, luôn rơi vào cuối tháng. Dữ liệu hóa đơn hiển thị ngày hàng ngày, thường là các ngày trong tuần. Vì vậy, tôi phải sao chép trường Ngày từ cả hai tập dữ liệu vào một cột duy nhất và sau đó loại bỏ các bản sao để đảm bảo rằng tất cả các ngày đều được đại diện. Sau đó, tôi đã sử dụng =TEXT(J4,"YYYY-MM")để tạo cột Tháng từ các ngày hàng ngày.
Nếu bạn không có bổ trợ Power Pivot đầy đủ, bạn cần tạo bảng tổng hợp từ bảng Ngân sách và chọn hộp kiểm Thêm Dữ liệu này vào Mô hình Dữ liệu.
Như đã thảo luận trong mẹo trước, khi bạn thêm các trường vào bảng tổng hợp, bạn sẽ phải xác định sáu mối quan hệ. Trong khi bạn có thể thực hiện điều này với sáu lần truy cập vào hộp thoại Tạo mối quan hệ, tôi đã kích hoạt bổ trợ Power Pivot của mình và sử dụng dạng xem sơ đồ để xác định sáu mối quan hệ.
Đây là chìa khóa để thực hiện tất cả công việc này: Bạn có thể tự do sử dụng các trường số từ Ngân sách và từ Thực tế. Nhưng nếu bạn muốn hiển thị Khu vực, Sản phẩm hoặc Tháng trong bảng tổng hợp, chúng phải đến từ các bảng nối!
Đây là một bảng tổng hợp với dữ liệu đến từ năm bảng. Cột A đến từ bộ nối Vùng. Hàng 2 đến từ trình kết hợp Lịch. Máy cắt sản phẩm là từ bộ ghép nối Sản phẩm. Số Ngân sách lấy từ bảng Ngân sách và số Thực tế lấy từ bảng Hóa đơn.
Điều này hoạt động bởi vì các bảng nối áp dụng bộ lọc cho bảng Ngân sách và Thực tế. Đó là một kỹ thuật đẹp và cho thấy rằng Power Pivot không chỉ dành cho dữ liệu lớn.
