Công thức Excel: LAMBDA chứa những thứ nào
Công thức Excel: LAMBDA chứa những thứ nào

Tóm lược

Ví dụ này giải thích việc chuyển đổi công thức Excel tiêu chuẩn tại đây thành một tập quán Hàm LAMBDA. Trong ví dụ được hiển thị, ô C5 chứa hàm tùy chỉnh, được sao chép xuống:

=ContainsWhichThings(B5,things,", ","")

trong đó B5 chứa văn bản để xử lý, nhiều thứphạm vi được đặt tên E5: E9, dấu phân cách là dấu phẩy và giá trị mặc định là dấu chuỗi trống (“”). Xem bên dưới để được giải thích chi tiết.

Giải trình

Mục tiêu trong ví dụ này là sử dụng một công thức để báo cáo những thứ nào tồn tại trong một ô. Danh sách những thứ cần kiểm tra nằm trong phạm vi được đặt tên nhiều thứ (E5: E9). Kết quả được trả về dưới dạng một chuỗi văn bản được phân tách bằng dấu phẩy.

Bước đầu tiên trong việc tạo một hàm tùy chỉnh với Hàm LAMBDA là xác minh logic cần thiết để giải quyết vấn đề. Công thức dưới đây sẽ thực hiện công việc và trả về kết quả trong cột C:

=TEXTJOIN(", ",1,FILTER(things,ISNUMBER(SEARCH(things,B5)),""))

Công thức này sử dụng bốn hàm riêng biệt: TEXTJOIN, BỘ LỌC, THÁNG NĂM, và TÌM KIẾM. Logic tìm kiếm cốt lõi là giải thích chi tiết tại đây. FILTER bắt đầu ra từ SEARCH và trả về danh sách các chuỗi phù hợp, và TEXTJOIN nối các giá trị với nhau và trả về kết quả cuối cùng.

Suy nghĩ về logic một cách tổng quát hơn, chúng ta có thể thấy rằng có ít nhất bốn đầu vào tiềm năng: bản văn để xử lý, nhiều thứ để tìm kiếm, dấu phân cách để sử dụng khi tham gia kết quả cuối cùng và mặc định giá trị để trả về nếu không tìm thấy công thức nào phù hợp. Công thức dưới đây là một cổng trực tiếp tới cú pháp LAMBDA, với bốn đầu vào ở trên được thiết lập dưới dạng các đối số được đặt tên:

=LAMBDA(text,things,delim,default,TEXTJOIN(delim,1,FILTER(things,ISNUMBER(SEARCH(things,text)),default)))

Lưu ý rằng bốn đầu vào ở trên đã được định nghĩa là các đối số của hàm. Sau khi phiên bản chung này của hàm được đặt tên và xác định với Người quản lý tên, chức năng tùy chỉnh có thể được sử dụng như thế này:

=ContainsWhichThings(B5,things,", ","")

với kết quả tương tự như trước đây.

Thêm một tùy chọn sắp xếp

Trong ví dụ LAMBDA ở trên, lợi ích chính của việc tạo một hàm tùy chỉnh là dễ sử dụng: hàm tùy chỉnh dễ gọi và cấu hình hơn công thức ban đầu.

Tuy nhiên, nếu chúng ta mở rộng công thức để sắp xếp kết quả theo thứ tự những thứ được tìm thấy trong văn bản, thì công thức cơ sở sẽ trở nên phức tạp và dư thừa hơn đáng kể:

=TEXTJOIN(", ",1,SORTBY(FILTER(things,ISNUMBER(SEARCH(things,B5)),""),SEARCH(FILTER(things,ISNUMBER(SEARCH(things,B5)),""),B5)))

Trong phiên bản này, chúng tôi sắp xếp danh sách được FILTER trả về theo vị trí mà mọi thứ xảy ra trong văn bản. Chúng tôi làm điều này với Hàm SORTBY và phức tạp chính là tạo ra một sort_by đối số, được thực hiện ở đây:

SEARCH(FILTER(things,ISNUMBER(SEARCH(things,B5)),""),B5) // sort_by

Lưu ý rằng mã bên trong TÌM KIẾM bên ngoài là mã lặp lại mã đã có trong công thức. Để dọn dẹp mọi thứ, chúng tôi sẽ muốn sử dụng Hàm LET, nhưng trước tiên, chúng tôi sẽ cập nhật mã LAMBDA hiện có để sử dụng logic sắp xếp mới:

=LAMBDA(text,things,delim,default,
  TEXTJOIN(", ",1,
    SORTBY(
      FILTER(things,ISNUMBER(SEARCH(things,text)),""),
      SEARCH(FILTER(things,ISNUMBER(SEARCH(things,text)),""),text))
  )
)(B5,things,", ","")

Hàm chung ở trên hoạt động tốt, nhưng vẫn còn thừa. Chúng ta có thể giảm bớt mã thừa bằng cách gán kết quả trung gian cho các biến bằng hàm LET. Dưới đây là phiên bản đã cấu trúc lại của công thức trên:

=LAMBDA(text,things,delim,default,
LET(
  searchResults,FILTER(things,ISNUMBER(SEARCH(things,text)),""),
  sortedResults,SORTBY(searchResults,SEARCH(searchResults,text)),
  result,TEXTJOIN(", ",1,sortedResults),
  result
  )
)(B5,things,", ","")

Lưu ý rằng mã FILTER (ISNUMBER (SEARCH ())) chính chỉ xuất hiện một lần bây giờ và kết quả được gán cho biến “searchResults”, được sử dụng hai lần ở dòng bên dưới. Tiếp theo, chúng ta sẽ làm cho việc sắp xếp là tùy chọn, bằng cách thêm một đối số mới được gọi là “sort”:

=LAMBDA(text,things,delim,default,sort,
LET(
  searchResults,FILTER(things,ISNUMBER(SEARCH(things,text)),""),
  sortedResults,IF(sort,
  SORTBY(searchResults,SEARCH(searchResults,text)),searchResults),
  result,TEXTJOIN(", ",1,sortedResults),
  result
  )
)(B5,things,", ","",TRUE)

Đối số “sắp xếp” hoạt động giống như một chuyển đổi. Khi nào sắp xếp là TRUE, chức năng sẽ sắp xếp các kết quả tìm kiếm theo thứ tự xuất hiện trong văn bản. Khi nào sắp xếp là FALSE, hàm sẽ để danh sách không được sắp xếp, và các mục tìm thấy sẽ xuất hiện theo thứ tự ban đầu của chúng (tức là thứ tự chúng được liệt kê trong “thứ”). Logic cho điều này được xử lý bởi Hàm IF. Đây là một ví dụ điển hình về cách các hàm LAMBDA và LET kết hợp với nhau để có thể mở rộng hoạt động của một hàm tùy chỉnh.

Màn hình bên dưới hiển thị phiên bản mới của công thức đang hoạt động. Chú ý sắp xếp đối số đã được đặt thành TRUE, vì vậy các kết quả hiện được sắp xếp theo thứ tự xuất hiện bản văn:

LAMBDA chứa các chuỗi được sắp xếp

https://exceljet.net/formula/lambda-contains-which-things