Công thức chung
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Tóm lược
Để trích xuất tất cả các kết quả phù hợp dựa trên kết quả so khớp từng phần, bạn có thể sử dụng công thức mảng dựa trên các hàm INDEX và AGGREGATE, với sự hỗ trợ từ ISNUMBER và SEARCH. Trong ví dụ được hiển thị, công thức trong G5 là:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
với các phạm vi được đặt tên sau: "search" = D5, "ct" = D8, "data" = B5: B55.
Lưu ý: đây là một công thức mảng, nhưng nó không yêu cầu control + shift + enter, vì AGGREGATE có thể xử lý mảng nguyên bản.
Giải trình
Cốt lõi của công thức này là hàm INDEX, với AGGREGATE được sử dụng để tìm ra "đối sánh thứ n" cho mỗi hàng trong vùng trích xuất:
INDEX(data,nth_match_formula)
Hầu như tất cả công việc là tìm ra và báo cáo những hàng nào trong "dữ liệu" khớp với chuỗi tìm kiếm và báo cáo vị trí của từng giá trị phù hợp cho INDEX. Điều này được thực hiện với hàm AGGREGATE được cấu hình như sau:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Đối số đầu tiên, 15, cho AGGREGATE hoạt động như SMALL và trả về giá trị nhỏ nhất thứ n. Đối số thứ hai, 6, là một tùy chọn để bỏ qua lỗi. Đối số thứ ba là một biểu thức tạo ra một mảng kết quả phù hợp (được mô tả bên dưới). Đối số thứ tư, F5, hoạt động giống như "k" trong SMALL để chỉ định giá trị "thứ n".
AGGREGATE hoạt động trên mảng và biểu thức bên dưới xây dựng một mảng cho đối số thứ ba bên trong AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Ở đây, hàm ROW được sử dụng để tạo một mảng các số hàng tương đối và ISNUMBER và SEARCH được sử dụng cùng nhau để đối sánh chuỗi tìm kiếm với các giá trị trong dữ liệu, tạo ra một mảng các giá trị TRUE và FALSE.
Một mẹo thông minh là chia số hàng cho kết quả tìm kiếm. Trong một phép toán như thế này, TRUE hoạt động như 1 và FALSE hoạt động như 0. Kết quả là các số hàng được liên kết với một kết quả khớp dương được chia cho 1 và tồn tại trong thao tác, trong khi các số hàng được liên kết với các giá trị không khớp sẽ bị hủy và trở thành lỗi # DIV / 0. Vì AGGREGATE được đặt để bỏ qua lỗi, nó sẽ bỏ qua lỗi # DIV / 0 và trả về số nhỏ nhất "thứ n" trong các giá trị còn lại, sử dụng số trong cột F cho "thứ n".
Quản lý hiệu suất
Giống như tất cả các công thức mảng, công thức này khá "tốn kém" về tài nguyên với một tập dữ liệu lớn. Để giảm thiểu tác động đến hiệu suất, toàn bộ công thức INDEX và MATCH được bao bọc trong IF như thế này:
=IF(F5>ct,"",formula)
trong đó phạm vi được đặt tên "ct" (D8) chứa công thức này:
=COUNTIF(data,"*"&search&"*")
Việc kiểm tra này sẽ ngăn phần INDEX và AGGREGATE của công thức chạy sau khi tất cả các giá trị phù hợp đã được trích xuất.
Công thức mảng với NHỎ
Nếu phiên bản Excel của bạn không có hàm AGGREGATE, bạn có thể sử dụng công thức thay thế dựa trên SMALL và IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
