Công thức Excel: Trích xuất tất cả các kết quả phù hợp với cột trợ giúp -

Mục lục

Công thức chung

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Tóm lược

Một cách để trích xuất nhiều kết quả phù hợp trong Excel là sử dụng INDEX và MATCH với cột trợ giúp gắn cờ dữ liệu phù hợp. Điều này tránh sự phức tạp của một công thức mảng nâng cao hơn. Trong ví dụ được hiển thị, công thức trong H6 là:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

trong đó ct (G3), dữ liệu (B3: E52) và trình trợ giúp (E3: E52) là các phạm vi được đặt tên.

Giải trình

Thách thức với các công thức tra cứu truy xuất nhiều hơn một kết quả phù hợp là quản lý các bản sao (tức là nhiều kết quả phù hợp). Các công thức tra cứu như VLOOKUP và INDEX + MATCH có thể dễ dàng tìm thấy kết quả phù hợp đầu tiên, nhưng việc tra cứu "tất cả các kết quả phù hợp" sẽ khó hơn nhiều khi các tiêu chí tìm thấy nhiều hơn một kết quả phù hợp.

Công thức này giải quyết thách thức này bằng cách sử dụng cột trợ giúp trả về giá trị số có thể được sử dụng để dễ dàng trích xuất nhiều kết quả phù hợp. Công thức trong cột trợ giúp trông giống như sau:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Cột trợ giúp kiểm tra từng hàng trong dữ liệu để xem Phòng ở cột C có khớp với giá trị trong I3 và Tòa nhà trong cột D khớp với giá trị trong J3 hay không. Cả hai phép thử logic phải trả về TRUE để AND trả về TRUE.

Đối với mỗi hàng, kết quả từ hàm AND được thêm vào "giá trị ở trên" trong cột trợ giúp để tạo số lượng. Hiệu quả thực tế của công thức này là bộ đếm tăng dần chỉ thay đổi khi tìm thấy kết quả phù hợp (mới). Sau đó, giá trị vẫn giữ nguyên cho đến khi tìm thấy kết quả khớp tiếp theo. Điều này hoạt động vì kết quả TRUE / FALSE trả về bởi AND được ép buộc thành 1/0 giá trị như một phần của phép toán tổng. Kết quả FALSE không thêm gì và kết quả TRUE thêm 1.

Quay lại khu vực trích xuất, công thức tra cứu Tên trong cột H trông giống như sau:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Làm việc từ trong ra ngoài, phần INDEX + MATCH của công thức tìm kiếm tên cho kết quả khớp đầu tiên được tìm thấy, sử dụng số hàng trong cột G làm giá trị khớp:

INDEX(data,MATCH($G6,helper,0),1)

INDEX nhận tất cả 3 cột dữ liệu dưới dạng mảng (dải ô được đặt tên là "dữ liệu") và MATCH được định cấu hình để khớp với số hàng bên trong cột trợ giúp (dải ô được đặt tên là "helper") ở chế độ đối sánh chính xác (đối số thứ 3 được đặt thành 0) .

Đây là lúc mà tính thông minh của công thức trở nên rõ ràng. Cột trợ giúp rõ ràng chứa các bản sao, nhưng điều đó không quan trọng, vì MATCH sẽ chỉ khớp với giá trị đầu tiên. Theo thiết kế, mỗi "giá trị đầu tiên" tương ứng với hàng chính xác trong bảng dữ liệu.

Các công thức trong cột I và J giống như H, ngoại trừ số cột, được tăng lên trong mỗi trường hợp một.

Câu lệnh IF bao bọc công thức INDEX / MATCH thực hiện một chức năng đơn giản - nó kiểm tra từng số hàng trong vùng trích xuất để xem liệu số hàng có nhỏ hơn hoặc bằng giá trị trong G3 (dải ô được đặt tên là "ct") hay không, là tổng số của tất cả các bản ghi phù hợp. Nếu vậy, logic INDEX / MATCH được chạy. Nếu không, IF xuất ra một chuỗi rỗng ("").

Công thức trong G3 (dải ô được đặt tên là "ct") rất đơn giản:

=MAX(helper)

Vì giá trị lớn nhất trong cột trợ giúp giống với tổng số trận đấu, nên hàm MAX là tất cả những gì chúng ta cần.

Lưu ý: khu vực trích xuất cần được cấu hình thủ công để xử lý nhiều dữ liệu nếu cần (ví dụ: 5 hàng, 10 hàng, 20 hàng, v.v.). Trong ví dụ này, nó được giới hạn ở 5 hàng chỉ để giữ trang tính nhỏ gọn.

Tôi đã học kỹ thuật này trong cuốn sách của Mike Girvin Control + Shift + Enter.