Công thức Excel: LAMBDA tách văn bản thành mảng
Công thức Excel: LAMBDA tách văn bản thành mảng

Công thức chung
=LAMBDA(text,delim,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(text,delim,"</y><y>")&"</y></x>","//y")))
Tóm lược

Excel không cung cấp một hàm chuyên dụng để chia một chuỗi văn bản thành một mảng, chẳng hạn như PHP của bùng nổ () hoặc split () của Python. Tuy nhiên, bạn có thể sử dụng Hàm LAMBDA để tạo một hàm tùy chỉnh hoạt động theo cách tương tự. Trong ví dụ dưới đây, công thức trong ô C5 là:

=SplitTextToArray(B5,",")

Công thức này chia văn bản trong B5 bằng cách sử dụng dấu phẩy (“,”) làm dấu phân cách. Kết quả là một chiều ngang mảng cái đó tràn ra vào các cột từ D đến H. Dấu phân cách được cung cấp làm đối số thứ hai cho hàm và có thể được thay đổi để phù hợp với tình huống.

Giải trình

Excel không cung cấp một hàm chuyên dụng để chia một chuỗi văn bản thành một mảng, chẳng hạn như PHP của bùng nổ () hoặc split () của Python. Tuy nhiên, bạn có thể sử dụng Hàm LAMBDA để tạo một hàm tùy chỉnh hoạt động theo cách tương tự. Các chức năng LAMBDA tùy chỉnh không yêu cầu VBA, nhưng chỉ khả dụng 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 thú vị được tạo bằng FILTERXML, SUBSTITUTE, và VẬN CHUYỂN chức năng:

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>","//y"))

Tóm lại, công thức này biến đổi văn bản gốc thành định dạng XML nguyên thủy, sau đó phân tích cú pháp XML bằng hàm FILTERXML. Đọc mô tả chi tiết tại đây. Mặc dù công thức này hoạt động, nhưng bản thân công thức hơi lộn xộn và không trực quan, và do đó, là một ứng cử viên tốt cho hàm LAMBDA tùy chỉnh, hàm này sẽ ẩn độ phức tạp và làm cho công thức dễ sử dụng hơn.

Vì chúng ta đã biết công thức hoạt động, bước tiếp theo là chuyển đổi công thức thành 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 được tách và một cho dấu phân cách để sử dụng khi tách. Chúng cần xuất hiện dưới dạng đối số đầu tiên trong công thức LAMBDA, tiếp theo là đối số thứ ba chứa công thức để thực thi, được điều chỉnh để sử dụng hai đối số đầu tiên theo tên. Kết quả trông như thế này:

=LAMBDA(text,delim,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(text,delim,"</y><y>")&"</y></x>","//y")))

Công thức này có thể được kiểm tra trên trang tính bằng cách sử dụng cú pháp kiểm tra LAMBDA, đặt các đối số đầu vào trong một tập hợp các dấu ngoặc đơn riêng biệt ở cuối:

=LAMBDA(text,delim,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(text,delim,"</y><y>")&"</y></x>","//y")))(B5,",")

Cuối cùng, chúng tôi xác định và đặt tên cho LAMBDA chung bằng cách sử dụng Trình quản lý tên (xem các ví dụ trên trang này để được giải thích chi tiết hơn). Khi công thức được đặt tên đã được tạo, nó có thể được sử dụng ở bất kỳ đâu trong sổ làm việc.

Các dấu phân cách khác

Thiết kế của chức năng tùy chỉnh này cho phép dễ dàng thay đổi dấu phân cách để phù hợp với tình huống:

=SplitTextToArray(A1,",") // split by comma
=SplitTextToArray(A1," ") // split by space (words)
=SplitTextToArray(A1,"-") // split by hyphen

Kích thước mảng kết quả sẽ phụ thuộc vào số lượng dấu phân cách tồn tại trong chuỗi văn bản gốc.

https://exceljet.net/formula/lambda-split-text-to-array