Công thức Excel: LAMBDA chứa một trong nhiều
Công thức Excel: LAMBDA chứa một trong nhiều

Công thức chung
=LAMBDA(text,strings,SUM(--ISNUMBER(SEARCH(strings,text)))>0)
Tóm lược

Excel không cung cấp hàm “chứa” chuyên dụng để kiểm tra một ô cho một hoặc nhiều chuỗi văn bản. Tuy nhiên, bạn có thể sử dụng Hàm LAMBDA để tạo một chức năng tùy chỉnh cho mục đích này. Trong ví dụ dưới đây, công thức trong ô C5 là:

=ContainsOneOfMany(B5,{"red","blue","green"})

Công thức này sẽ trả về TRUE nếu một ô chứa bất kỳ chuỗi nào được chuyển vào đó. Bởi vì nó được xây dựng với Chức năng tìm kiếm, nó tự động thực hiện đối sánh từng phần và hỗ trợ ký tự đại diện.

Giải trình

Excel không cung cấp hàm “chứa” chuyên dụng, nhưng bạn có thể tạo một hàm tùy chỉnh để kiểm tra xem một ô có chứa một hoặc nhiều chuỗi với Hàm LAMBDA. Các chức năng LAMBDA không yêu cầu VBA, nhưng chỉ có sẵn trong Excel 365.

Bước đầu tiên trong việc tạo một hàm LAMBDA tùy chỉnh là xác minh logic cần thiết với một công thức chuẩn của Excel. Công thức LAMBDA này dựa trên một công thức Excel được tạo ra với ba hàm: GIỚI THIỆU, THÁNG NĂM, và TÌM KIẾM:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0

Đọc mô tả chi tiết tại đây. Bởi vì chức năng LAMBDA chỉ có sẵn trong phiên bản mảng động của Excel, xử lý công thức mảng nguyên bản, chúng tôi đang sử dụng SUM thay vì SUMPRODUCT (xem ghi chú bên dưới) và đổi tên “things” thành “string” để làm cho các đối số công thức tự nhiên hơn một chút:

=SUM(--ISNUMBER(SEARCH(strings,A1)))>0 // base formula

Màn hình bên dưới hiển thị công thức này được sử dụng với ba chuỗi “red”, “blue” và “green”:

Công thức Excel tiêu chuẩn đang hoạt động

Công thức này trả về TRUE cho bất kỳ ô nào trong cột B có chứa bất kỳ một trong các chuỗi “red”, “blue” hoặc “green”.

Bước tiếp theo là chuyển đổi công thức này thành một công thức LAMBDA chung chung (không có tên). Chúng ta sẽ cần hai tham số đầu vào, một cho văn bản và một cho các chuỗi để kiểm tra. Chúng cần xuất hiện dưới dạng các đối số đầu tiên trong công thức LAMBDA. Đối số cuối cùng chứa phép tính cần thực hiện, được điều chỉnh từ công thức Excel tiêu chuẩn của chúng tôi ở trên. Đây là LAMBDA chung:

=LAMBDA(text,strings,SUM(--ISNUMBER(SEARCH(strings,text)))>0)

Màn hình bên dưới hiển thị công thức này đang hoạt động, với cú pháp kiểm tra cần thiết để cung cấp giá trị cho văn bản và chuỗi:

=LAMBDA(text,strings,SUM(--ISNUMBER(SEARCH(strings,text)))>0)(B5,{"red","blue","green"})

Kiểm tra công thức LAMBDA chung

Lưu ý rằng kết quả tương tự như trên. Bước tiếp theo trong việc tạo LAMBDA tùy chỉnh là đặt tên và xác định công thức với Người quản lý tên. Trong trường hợp này, chúng tôi sẽ sử dụng tên “ContainsOneOfMany”:

Phiên bản LAMBDA được xác định trong Trình quản lý tên

Cuối cùng, chúng tôi cập nhật trang tính để sử dụng chức năng tùy chỉnh mới và xác nhận rằng kết quả là giống nhau:

Chức năng LAMBDA tùy chỉnh đang hoạt động

Ghi chú

Mặc dù chúng tôi đang mã hóa các chuỗi “red”, “blue” và “green” dưới dạng mảng hằng số trong ví dụ này để đơn giản, công thức sẽ hoạt động tốt nếu thay vào đó chúng ta cung cấp một phạm vi:

=ContainsOneOfMany(A1,range)

Ngoài ra, công thức cũng sẽ hoạt động chính xác nếu chúng tôi chỉ cung cấp một chuỗi:

=ContainsOneOfMany(A1,"red")

Lưu ý: Theo truyền thống, GIỚI THIỆU thường được thấy trong công thức mảng, vì nó có thể xử lý mảng nguyên bản mà không cần điều khiển + shift + enter. Điều này làm cho công thức trở nên “thân thiện” hơn với hầu hết người dùng. Các Hàm SUM cũng có thể được sử dụng trong những trường hợp này, nhưng công thức sau đó phải được nhập bằng control + shift + enter. Trong Excel 365, hàm SUM sẽ hoạt động trong những trường hợp này mà không cần xử lý đặc biệt nào. Vì LAMBDA chỉ khả dụng trong Excel 365, ví dụ này sử dụng SUM, vì SUMPRODUCT không cung cấp thêm lợi ích nào.

https://exceljet.net/formula/lambda-contains-one-of-many