Hàm TEXTJOIN trong Excel để hợp nhất văn bản từ nhiều ô

Cho đến gần đây, có hai phương pháp phổ biến để hợp nhất nội dung ô trong Excel: điều hành nối và XÁC NHẬN chức năng. Với sự ra đời của TEXTJOIN, có vẻ như một sự thay thế mạnh mẽ hơn đã xuất hiện, cho phép bạn tham gia văn bản theo cách linh hoạt hơn bao gồm bất kỳ dấu phân cách nào ở giữa. Nhưng trong thực tế, có nhiều hơn thế!

Hàm Excel TEXTJOIN

TEXTJOIN trong Excel hợp nhất các chuỗi văn bản từ nhiều ô hoặc phạm vi và phân tách các giá trị kết hợp với bất kỳ dấu phân cách nào bạn chỉ định. Nó có thể bỏ qua hoặc bao gồm các ô trống trong kết quả.

Chức năng có sẵn trong Excel cho Office 365 và Excel 2019.

Cú pháp của hàm TEXTJOIN như sau:

TEXTJOIN (dấu phân cách, ign_empty, text1, [text2], Giáo)

Ở đâu:

• Dấu phân cách (bắt buộc) – là dấu phân cách giữa mỗi giá trị văn bản mà bạn kết hợp. Thông thường, nó được cung cấp dưới dạng một chuỗi văn bản được đặt trong dấu ngoặc kép hoặc tham chiếu đến một ô chứa chuỗi văn bản. Một số được cung cấp dưới dạng dấu phân cách được coi là văn bản.
• Mặc kệ (bắt buộc) – Xác định xem có bỏ qua các ô trống hay không:
  • TRUE – bỏ qua bất kỳ ô trống.
  • SAI – bao gồm các ô trống trong chuỗi kết quả.
• Văn bản 1 (bắt buộc) – giá trị đầu tiên để tham gia. Có thể được cung cấp dưới dạng một chuỗi văn bản, một tham chiếu đến một ô có chứa một chuỗi hoặc một chuỗi các chuỗi như một phạm vi các ô.
• Văn bản2, Hoài (tùy chọn) – các giá trị văn bản bổ sung được nối với nhau. Tối đa 252 đối số văn bản được cho phép, bao gồm Văn bản 1.

Ví dụ: hãy kết hợp các phần địa chỉ từ các ô B2, C2 và D2 ​​lại với nhau thành một ô, tách các giá trị bằng dấu phẩy và dấu cách:

Với hàm CONCATENATE, bạn cần chỉ định từng ô riêng lẻ và đặt dấu phân cách (“,”) sau mỗi tham chiếu, có thể gây khó chịu khi hợp nhất nội dung của nhiều ô:

=CONCATENATE(A2, ", ", B2, ", ", C2)

Với Excel TEXTJOIN, bạn chỉ định dấu phân cách chỉ một lần trong đối số đầu tiên và cung cấp một phạm vi ô cho đối số thứ ba:

=TEXTJOIN(", ", TRUE, A2:C2)

TEXTJOIN trong Excel – 6 điều cần nhớ

Để sử dụng hiệu quả TEXTJOIN trong trang tính của bạn, có một vài điểm quan trọng cần lưu ý:

1. TEXTJOIN là một chức năng mới, chỉ có trong Excel 2019 trên Windows và Mac cũng như trong Office 365. Trong các phiên bản Excel trước, vui lòng sử dụng Hàm CONCATENATE hoặc là “&” nhà điều hành thay thế.
2. Trong Office 365 và Excel 2019, bạn cũng có thể sử dụng hàm CONCAT để nối các giá trị từ các ô và phạm vi riêng biệt, nhưng không có tùy chọn nào cho các dấu phân cách hoặc ô trống.
3. Bất kỳ số nào được cung cấp cho TEXTJOIN cho dấu phân cách hoặc là bản văn đối số được chuyển đổi thành văn bản.
4. Nếu dấu phân cách không được chỉ định hoặc là một chuỗi rỗng (“”), các giá trị văn bản được nối mà không có bất kỳ dấu phân cách nào.
5. Hàm này có thể xử lý tối đa 252 đối số văn bản.
6. Chuỗi kết quả có thể chứa tối đa 32.767 ký tự, là giới hạn ô trong Excel. Nếu vượt quá giới hạn này, công thức TEXTJOIN trả về #VALUE! lỗi.

Cách tham gia văn bản trong Excel – ví dụ về công thức

Để hiểu rõ hơn tất cả các ưu điểm của TEXTJOIN, chúng ta hãy xem cách sử dụng chức năng trong các tình huống thực tế.

Chuyển đổi cột thành danh sách được phân tách bằng dấu phẩy

Khi bạn đang tìm cách ghép một danh sách dọc ngăn cách các giá trị bằng dấu phẩy, dấu chấm phẩy hoặc bất kỳ dấu phân cách nào khác, TEXTJOIN là hàm phù hợp để sử dụng.

Trong ví dụ này, chúng tôi sẽ kết hợp các chiến thắng và thua lỗ của mỗi đội từ bảng dưới đây. Điều này có thể được thực hiện với các công thức sau, chỉ khác nhau trong phạm vi các ô được nối.

Đối với đội 1:

=TEXTJOIN(",", FALSE, B2:B6)

Đối với đội 2:

=TEXTJOIN(",", FALSE, C2:C6)

Và như thế.

Trong tất cả các công thức, các đối số sau đây được sử dụng:

• Dấu phân cách – dấu phẩy (“,”).
• Mặc kệ được đặt thành FALSE để bao gồm các ô trống vì chúng tôi cần hiển thị những trò chơi nào không được chơi.

Kết quả là, bạn sẽ nhận được bốn danh sách được phân tách bằng dấu phẩy thể hiện thắng và thua của mỗi đội dưới dạng rút gọn:

Tham gia các tế bào với các dấu phân cách khác nhau

Trong trường hợp khi bạn cần tách các giá trị kết hợp với các dấu phân cách khác nhau, bạn có thể cung cấp một số dấu phân cách như một hằng số mảng hoặc nhập từng dấu phân cách trong một ô riêng biệt và sử dụng tham chiếu phạm vi cho dấu phân cách tranh luận.

Giả sử bạn muốn tham gia các ô chứa các phần tên khác nhau và nhận kết quả ở định dạng này: Họ, Tên đầu tiên tên đệm.

Như bạn có thể thấy, Họ và Tên được phân tách bằng dấu phẩy và dấu cách (“,”) trong khi Tên và Tên đệm chỉ bằng một khoảng trắng (“”). Vì vậy, chúng tôi bao gồm hai dấu phân cách này trong một hằng số mảng {“,”, “”} và nhận công thức sau:

=TEXTJOIN({", "," "}, TRUE, A2:C2)

Trong đó A2: C2 là các phần tên được kết hợp.

Ngoài ra, bạn có thể nhập các dấu phân cách không có dấu ngoặc kép trong một số ô trống (giả sử dấu phẩy và khoảng trắng trong F3 và khoảng trắng trong G3) và sử dụng phạm vi $ F $ 3: $ G $ 3 (vui lòng lưu ý tài liệu tham khảo tế bào tuyệt đối) cho dấu phân cách tranh luận:

=TEXTJOIN($F$3:$G$3, TRUE, A2:C2)

Bằng cách sử dụng phương pháp chung này, bạn có thể hợp nhất nội dung ô dưới nhiều hình thức khác nhau.

Ví dụ: nếu bạn muốn kết quả trong Tên đầu tiên Trung ban đầu Họ định dạng, sau đó sử dụng Chức năng Left để trích xuất ký tự đầu tiên (chữ cái đầu) từ ô C2. Đối với các dấu phân cách, chúng ta đặt một khoảng trắng (“”) giữa tên đầu tiên và chữ cái đầu; dấu chấm và dấu cách (“.”) giữa Tên ban đầu và Họ:

=TEXTJOIN({" ",". "}, TRUE, B2, LEFT(C2,1), A2)

Tham gia văn bản và ngày trong Excel

Trong một trường hợp cụ thể khi bạn hợp nhất văn bản và ngày, việc cung cấp ngày trực tiếp cho công thức TEXTJOIN sẽ không hoạt động. Như bạn có thể nhớ, Excel lưu trữ ngày dưới dạng số sê-ri, vì vậy công thức của bạn sẽ trả về một số biểu thị ngày như trong ảnh chụp màn hình bên dưới:

=TEXTJOIN(" ", TRUE, A2:B2)

Để sửa lỗi này, bạn cần chuyển đổi ngày thành một chuỗi văn bản trước khi nối nó. Và đây Hàm văn bản với mã định dạng mong muốn (“mm / dd / yyyy” trong trường hợp của chúng tôi) có ích:

=TEXTJOIN(" ", TRUE, A2, TEXT(B2, "mm/dd/yyyy"))

Hợp nhất văn bản với ngắt dòng

Nếu bạn muốn hợp nhất văn bản trong Excel để mỗi giá trị bắt đầu trong một dòng mới, hãy sử dụng CHAR (10) làm dấu phân cách (trong đó 10 là ký tự nguồn cấp dữ liệu).

Ví dụ: để kết hợp văn bản từ các ô A2 và B2 tách các giá trị bằng dấu ngắt dòng, đây là công thức nên sử dụng:

=TEXTJOIN(CHAR(10), TRUE, A2:B2)

Tham gia văn bản với điều kiện

Do khả năng của Excel TEXTJOIN để xử lý các chuỗi chuỗi, nó cũng có thể được sử dụng để hợp nhất một cách có điều kiện nội dung của hai hoặc nhiều ô. Để hoàn thành nó, hãy sử dụng Hàm IF để đánh giá một phạm vi ô và trả về một mảng các giá trị đáp ứng điều kiện cho Văn bản 1 lập luận của TEXTJOIN.

Từ bảng hiển thị trong ảnh chụp màn hình bên dưới, giả sử bạn muốn lấy danh sách Đội 1 các thành viên. Để đạt được điều này, hãy lồng câu lệnh IF sau vào Văn bản 1 tranh luận:

IF($B$2:$B$9=1, $A$2:$A$9, "")

Trong tiếng Anh đơn giản, công thức trên cho biết: Nếu cột B bằng 1, trả về một giá trị từ cột A trong cùng một hàng; mặt khác trả về một chuỗi rỗng

Công thức hoàn chỉnh cho Đội 1 có hình dạng này:

=TEXTJOIN(", ", TRUE, IF($B$2:$B$9=1, $A$2:$A$9, ""))

Theo cách tương tự, bạn có thể nhận được danh sách các thành viên được phân tách bằng dấu phẩy Đội 2:

=TEXTJOIN(", ", TRUE, IF($B$2:$B$9=2, $A$2:$A$9, ""))

Tra cứu và trả lại nhiều kết quả khớp trong danh sách được phân tách bằng dấu phẩy

Như bạn có thể biết, Excel Hàm VLOOKUP chỉ có thể trả lại trận đấu tìm thấy đầu tiên. Nhưng điều gì sẽ xảy ra nếu bạn cần có được tất cả các trận đấu cho một ID, SKU cụ thể hoặc một cái gì đó khác?

Để xuất kết quả trong các ô riêng biệt, hãy sử dụng một trong các công thức được mô tả trong Cách VLOOKUP nhiều giá trị trong Excel.

Để tra cứu và trả về tất cả các giá trị khớp trong một ô dưới dạng danh sách được phân tách bằng dấu phẩy, hãy sử dụng hàm TEXTJOIN.

Để xem nó hoạt động như thế nào trong thực tế, hãy lấy danh sách các sản phẩm được mua bởi một người bán nhất định từ bảng mẫu bên dưới. Điều này có thể dễ dàng thực hiện với công thức sau:

=TEXTJOIN(", ", TRUE, IF($A$2:$A$12=D2, $B$2:$B$12, ""))

Trong đó A2: A12 là tên người bán, B2: B12 là sản phẩm và D2 ​​là người bán quan tâm.

Công thức trên thuộc về E2 và mang tất cả các trận đấu cho người bán mục tiêu trong D2 (Adam). Do sử dụng thông minh quan hệ (cho người bán mục tiêu) và tuyệt đối (đối với tên người bán và sản phẩm) tham chiếu ô, công thức sao chép chính xác vào các ô bên dưới và cũng hoạt động độc đáo cho hai người bán khác:

Ghi chú. Cũng như ví dụ trước, công thức này hoạt động như một công thức thông thường trong Excel 365 và dưới dạng công thức CSE (Ctrl + Shift + Enter) trong Excel 2019.

Logic của công thức hoàn toàn giống như trong ví dụ trước:

Câu lệnh IF so sánh từng tên trong A2: A12 so với tên đích trong D2 (Adam trong trường hợp của chúng tôi):

IF($A$2:$A$12=D2, $B$2:$B$12, "")

Nếu kiểm tra logic ước tính thành TRUE (nghĩa là tên trong D2 khớp với tên trong cột A), công thức trả về một sản phẩm từ cột B; mặt khác, một chuỗi rỗng (“”) được trả về. Kết quả của IF là mảng sau:

{"";"";"Bananas";"Apples";"";"";"";"Oranges";"";"Lemons";""}

Mảng đi đến hàm TEXTJOIN là Văn bản 1 tranh luận. Và bởi vì TEXTJOIN được cấu hình để phân tách các giá trị bằng dấu phẩy và dấu cách (“,”), chúng tôi lấy chuỗi này làm kết quả cuối cùng:

Chuối, Táo, Cam, Chanh

Excel TEXTJOIN không hoạt động

Khi công thức TEXTJOIN của bạn bị lỗi, rất có thể đó là một trong những điều sau đây:

• #TÊN? lỗi xảy ra khi TEXTJOIN được sử dụng trong phiên bản Excel cũ hơn khi chức năng này không được hỗ trợ (trước năm 2019) hoặc khi tên của hàm bị sai chính tả.
• #GIÁ TRỊ! xảy ra lỗi nếu chuỗi kết quả vượt quá 32.767 ký tự.
• #GIÁ TRỊ! lỗi cũng có thể xảy ra nếu Excel không nhận ra dấu phân cách dưới dạng văn bản, ví dụ nếu bạn cung cấp một số ký tự không in được, chẳng hạn như CHAR (0).

Đó là cách sử dụng hàm TEXTJOIN trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của Dịch vụ kế toán tại Hà Nội vào tuần tới!