SUMIF với Tham chiếu 3D trong Excel

Nó trông phức tạp nhưng nó không phải là (nhiều như vậy).

“‘” name_range_of_sheet_names “‘”: * Nó là một liên kết : / excel-range-name-all-about-excel-name-range-excel-range-name [đã đặt tên dải] có chứa các tên trang tính. Cái này rất quan trọng.

“tiêu chí_ dải ô”: * Nó là tham chiếu văn bản của tiêu chí chứa dải ô. (Nó phải giống nhau trong tất cả các trang đối với tác phẩm tham chiếu 3-D.)

tiêu chí: * Nó chỉ đơn giản là điều kiện mà bạn muốn đặt để tính tổng. Nó có thể là một tham chiếu văn bản hoặc ô.

“sum_range”: * Đây là tham chiếu văn bản của phạm vi tổng. (Nó phải giống nhau trong tất cả các trang tính đối với công việc tham chiếu 3-D.)

Lý thuyết là đủ, hãy tham chiếu 3D với hàm SUMIF hoạt động. === Ví dụ: Tính tổng theo Vùng từ Nhiều Trang tính bằng cách sử dụng Tham chiếu 3D của Excel: *

Chúng tôi đang lấy cùng một dữ liệu mà chúng tôi đã lấy trong `liên kết: / excel-General-3d-reference-in-excel [ví dụ tham chiếu 3D đơn giản] ‘. Trong ví dụ này, tôi có năm trang tính khác nhau chứa dữ liệu tương tự. Mỗi trang tính chứa dữ liệu của một tháng. Trong trang tính Chính, tôi muốn tổng các đơn vị và tập hợp theo vùng từ tất cả các trang tính. Hãy làm điều đó cho các Đơn vị trước. Các đơn vị nằm trong phạm vi D2: D14 trong tất cả các trang tính.

Bây giờ nếu bạn sử dụng tham chiếu 3D thông thường với hàm SUMIF, = SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)

Nó sẽ trả về #VALUE! lỗi. Vì vậy, chúng tôi không thể sử dụng nó. Chúng tôi sẽ sử dụng công thức chung đã đề cập ở trên.

Sử dụng công thức SUMIF tham chiếu chung 3D ở trên của excel, hãy viết công thức này vào ô C3:

Đây là một liên kết “ liên kết: / excel-range-name-all-about-excel-name-range-excel-range-name [phạm vi được đặt tên] `chứa tên của các trang tính. Đây là điều tối quan trọng.

Khi bạn nhấn enter, bạn sẽ nhận được đầu ra chính xác của mình.

How does it work? The core of the formula is the INDIRECT function and the named range. Here the string”‘”&Months&”‘!” & “A2:A14″translates to an array of range references of each sheet in the named range.*

Mảng này chứa tham chiếu văn bản * của phạm vi, không phải phạm vi thực tế. Bây giờ vì nó là một tham chiếu văn bản, nó có thể được sử dụng bởi hàm INDIRECT để chuyển đổi chúng thành các phạm vi thực tế. Điều này xảy ra cho cả hai hàm INDIRECT. Sau khi giải quyết các văn bản bên trong các hàm INDIRECT (giữ chặt chẽ), công thức có dạng như sau:

Bây giờ, hàm SUMIF hoạt động (không phải hàm INDIRECT, như bạn có thể đã đoán). Điều kiện được so khớp với phạm vi đầu tiên “‘Jan’! A2: A14”. Ở đây, hàm INDIRECT hoạt động động và chuyển văn bản này thành phạm vi thực tế (_ đó là lý do tại sao nếu bạn cố gắng giải INDIRECT trước bằng phím F9, bạn sẽ không nhận được kết quả_). Tiếp theo tính tổng các giá trị phù hợp trong phạm vi “‘Jan’! D2: D14”. * Điều này xảy ra cho mỗi phạm vi trong mảng. Cuối cùng, chúng ta sẽ có một mảng được trả về bởi hàm SUMIF.

Bây giờ SUMPRODUCT làm những gì nó làm tốt nhất. Nó tổng hợp các giá trị này và chúng tôi nhận được hàm SUMIF 3D của chúng tôi hoạt động.

Tôi hy vọng tôi đã giải thích đủ. Nếu bạn có bất kỳ nghi ngờ nào về việc excel tham chiếu bất kỳ truy vấn nào khác liên quan đến Excel / VBA, hãy hỏi trong phần nhận xét bên dưới.

Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.