Tổng quan bài viết
3D SUMIF cho nhiều bảng tính trong excel
Công thức chung
= SUMPRODUCT ( SUMIF ( TIẾP ( ” ‘” & tấm & “‘!” & “RNG” ), tiêu chí , KHÔNG TRỰC TIẾP ( ” ‘” & tấm & “‘!” & “Sumrng” )))Giải trình
Nếu bạn cần điều kiện các phạm vi giống hệt nhau trong các bảng tính riêng biệt, tất cả trong một công thức, bạn có thể làm như vậy với chức năng SUMIF + INDIRECT, được bao bọc trong SUMPRODUCT.
Trong ví dụ, công thức sẽ như sau:
= SUMPRODUCT ( SUMIF ( TIẾP ( ” ‘” & tấm & “‘!” & “D4: D5″ ), B9 , KHÔNG TRỰC TIẾP ( ” ‘” & tấm & “‘!” & “E4: E5” )))
Dữ liệu trên mỗi ba tấm được xử lý sẽ như sau:
Dữ liệu mẫu cho công thức 3d SUMIFs
Giải trình
Trước hết, lưu ý rằng bạn không thể sử dụng SUMIFs với tham chiếu 3D "bình thường" như sau: Sheet1: Sheet3 ! D4: D5 Đây là tiêu chuẩn "cú pháp 3D" nhưng nếu bạn cố gắng sử dụng nó với SUMIF, bạn sẽ gặp lỗi #VALUE. Vì vậy, để giải quyết vấn đề này bạn có thể sử dụng một "sheets" có tên là liệt kê mỗi trang tính (bảng tính) mà bạn muốn đưa vào. Tuy nhiên, để xây dựng tài liệu tham khảo mà Excel sẽ giải thích chính xác, chúng ta cần phải nối các tên bảng với các dãy chúng ta cần làm việc và sau đó sử dụng INDIRECT để Excel nhận ra chúng một cách chính xác. Ngoài ra, vì phạm vi tên "sheets" có nhiều giá trị (ví dụ mảng của nó), kết quả của SUMIF trong trường hợp này cũng là một mảng (đôi khi được gọi là mảng kết hợp). Vì vậy, chúng ta sử dụng SUMPRODUCT để xử lý nó, vì SUMPRODUCT có khả năng xử lý các mảng tự nhiên mà không yêu cầu Ctrl-Shift-Enter, giống như nhiều công thức mảng khác. Cách khác Ví dụ trên là hơi phức tạp. Một cách khác để xử lý vấn đề này là thực hiện tổng hợp có điều khoản "cục bộ" trên mỗi trang tính, sau đó sử dụng một khoản tiền 3D thông thường để thêm mỗi giá trị trên tab tóm tắt. Để làm điều này, thêm một công thức SUMIF vào mỗi trang tính sử dụng một tế bào tiêu chuẩn trên tờ tóm tắt. Sau đó, khi bạn thay đổi các tiêu chí, tất cả các công thức SUMIF được liên kết sẽ cập nhật.