Công thức Excel: 3D SUMIF cho nhiều trang tính
Công thức Excel: 3D SUMIF cho nhiều trang tính

Công thức chung
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Tóm lược

Để tính tổng có điều kiện các phạm vi giống hệt nhau tồn tại trong các trang tính riêng biệt, tất cả trong một công thức, bạn có thể sử dụng Hàm SUMIF với GIÁN TIẾP, bọc trong GIỚI THIỆU. Trong ví dụ được hiển thị, công thức trong C9 là:

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))

Giải trình

Dữ liệu trên mỗi trang trong số ba trang tính đang được xử lý trông giống như sau:

Dữ liệu mẫu cho công thức SUMIFs 3d

Trước hết, hãy lưu ý rằng bạn không thể sử dụng SUMIF với dấu “bình thường“Tham chiếu 3D như thế này:

Sheet1:Sheet3!D4:D5

Đây là “cú pháp 3D” tiêu chuẩn 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 dải ô được đặt tên là “trang tính” liệt kê từng trang tính (tab trang tính) mà bạn muốn đưa vào. Tuy nhiên, để xây dựng các tham chiếu mà Excel sẽ diễn giải chính xác, chúng ta cần nối tên trang tính với phạm vi mà 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, bởi vì phạm vi được đặt tên “trang tính” chứa nhiều giá trị (tức là một mảng), 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 quả). Vì vậy, chúng tôi sử dụng SUMPRODUCT để xử lý nó, vì SUMPRODUCT có khả năng xử lý mảng nguyên bả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 hơi phức tạp. Một cách khác để xử lý vấn đề này là thực hiện tính tổng có điều kiện “cục bộ” trên mỗi trang tính, sau đó sử dụng 3D thông thường tổng để cộng từng giá trị trên tab tóm tắt.

Để thực hiện việc này, hãy thêm công thức SUMIF vào mỗi trang tính sử dụng ô tiêu chí trên bảng tóm tắt. Sau đó, khi bạn thay đổi tiêu chí, tất cả các công thức SUMIF được liên kết sẽ cập nhật.

https://exceljet.net/formula/3d-sumif-for-multiple-worksheets