Cách sử dụng hàm TEXTJOIN trong Excel 2016

Chúng ta đã quá quen thuộc với hàm CONCATENATE để kết hợp các chuỗi hoặc số khác nhau trong Excel. Tuy nhiên nó có một số hạn chế:

1. Đối số phải được tách bằng dấu phẩy.

2. Hàm này không thể lấy đối tượng trong một phạm vi để thực hiện kết hợp.

3. Nó không có điều khoản để thêm dấu phân cách.

Với những nhược điểm này, một hàm mới đã được giới thiệu trong Excel 2016 được gọi là TEXTJOINT, thực hiện chức năng tương tự như hàm CONCATENATE nhưng hiệu quả hơn.

Mô tả hàm TEXTJOIN

Hàm TEXTJOIN nối văn bản từ nhiều phạm vi và/hoặc các chuỗi, nó gồm có một dấu phân cách giữa giá trị văn bản được nối. Nếu dấu phân cách là một chuỗi văn bản trống, hàm này sẽ ghép nối các phạm vi một cách hiệu quả.

Cú pháp hàm TEXTJOIN

Hàm này có cú pháp như sau:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Chúng ta hãy cùng phân tích cú pháp trên:

• Delimiter (dấu phân cách) là ký tự được chèn giữa các văn bản được kết hợp. Ví dụ, họ và tên của một người được kết hợp với nhau bằng một dấu phân cách “_” (gạch dưới) để nhận dạng tên dễ dàng và tránh dấu cách trong chuỗi. Trong ví dụ này, dấu gạch dưới là một dấu phân cách và văn bản được kết hợp là tên và họ.
• Ignore empty được dùng để bỏ qua các ô trống trong khi nối đối tượng trong phạm vi. Chúng ta có thể thiết lập tùy chọn để bỏ qua ô trống bằng cách để giá trị TRUE cho tùy chọn này và FALSE nếu muốn có các ô trống.
• Text 1 là văn bản cần kết hợp, có thể là một ô riêng lẻ hoặc một dải ô.
• Text 2 là văn bản bổ sung cần kết hợp. Bạn có thể sử dụng tối đa 252 đối số văn bản, bao gồm Text 1. Tương tự như Text 1, nó có thể là một ô riêng lẻ hoặc một dải ô.

Ví dụ về hàm TEXTJOIN

Dưới đây là một số ví dụ để bạn có thể hiểu rõ hàm này hơn nhé.

Ví dụ 1:

Trong ví dụ này, chúng ta có bảng dữ liệu gồm tên, họ và bang sinh sống của từng người. Ở đây chúng ta sẽ sử dụng hàm TEXTJOIN để tạo một ID duy nhất cho từng người bằng cách kết hợp tên, họ và bang. Công thức sử dụng dấu gạch dưới làm dấu phân cách, thiết lập đối số thứ hai là TRUE để không có ô trống và dải A2: C2 là phạm vi được kết nối.

Kéo công thức ở ô D2 xuống, bạn sẽ có được ID cho từng người.

Ví dụ 2:

Trong ví dụ này, vẫn bảng dữ liệu trên, chúng ta tìm danh sách tất cả tên người ở bang LA bằng cách sử dụng hàm TEXTJOIN hai lần. Đầu tiên sử dụng hàm TEXTJOIN kết hợp với hàm IF để tìm ra người phù hợp với điều kiện trên, tức là người sống ở bang LA. Kết quả sẽ trả về tên họ đầy đủ của người đó, khi chúng ta kéo công thức xuống, cột sẽ được điền tên đầy đủ của họ. Nếu sống ở bang khác, nó sẽ để trống ô đó.

Tiếp theo, chúng ta sẽ sử dụng hàm TEXTJOIN lần nữa để lấy danh sách trong một ô được phân tách bằng dấu phẩy. Để làm như vậy, chúng ta sẽ viết công thức sau trong ô B2.

= TEXTJOIN (“,”, TRUE, D5: D13)

Như thể hiện trong hình dưới đây:

Kết quả sẽ là danh sách các tên được phân cách bằng dấu phẩy như được hiển thị bên dưới:

Các ô trống sẽ bị bỏ qua khỏi chuỗi đã được kết hợp vì chúng ta thiết lập tùy chọn hai là TRUE.

Ví dụ 3:

Chúng ta có thể sử dụng hàm TEXTJOIN để tạo ra các câu tự động hoàn thành. Ví dụ, sử dụng nó để viết nhận xét vào một bảng điểm để biết học sinh đó thuộc về lớp nào.

Trong ví dụ dưới đây, chúng ta sẽ tạo một bảng LOOKUP để tìm lớp tương ứng của học sinh và trả về các nhận xét bằng cách sử dụng hàm TEXTJOIN. Bảng dưới đây chứa thông tin phạm vi điểm và lớp tương ứng với khoảng điểm đó.

Công thức LOOKUP như sau:

= LOOKUP (F4, L5: L8, N5: N8)

Như chúng ta biết rằng hàm LOOKUP có ít nhất ba đối số, đầu tiên là giá trị tra cứu, F4 trong ví dụ này. Đối số thứ hai là phạm vi tìm kiếm tức là L5: L8 trong công thức này và đối số cuối cùng là phạm vi trả về là N5: N8 trong công thức. Vì vậy, công thức tìm kiếm điểm trong cột đầu tiên (Cột L) và trả về giá trị từ Cột N.

Công thức lấy thông tin từ bảng sau nằm giữa E3: F5.

Công thức Lookup được thảo luận trong đoạn trước có mặt trong ô F5 và trả về xếp hạng C cho điểm 34. Bây giờ chúng ta sẽ sử dụng hàm TEXTJOIN để tạo ra một nhận xét có thể được đặt trong các báo cáo.

Giả sử chúng ta muốn có câu như thế này:

[Candidate name] you have scored [marks], and your grade is [Grade].

Ta sẽ có công thức sau:

=TEXTJOIN(" ",TRUE,F3,"Bạn đạt",F4,"điểm, xếp hạng",F5,".")

Trong công thức này:

1. Đối số đầu tiên là dấu phân tách được đặt thành dấu cách (“ “), làm cho nó giống một câu.

2. Đối số thứ hai được đặt thành TRUE như bình thường.

3. F3 là tên của học sinh

4. Phần tiếp theo là “Bạn đạt” sẽ vẫn giữ nguyên cho mỗi trường hợp của học sinh.

5. Tiếp theo là F4, điểm cho học sinh.

6. Đối số tiếp theo phần cố định sẽ vẫn giữ nguyên cho mọi trường hợp.

7. Đối số thứ hai là lớp trong ô F5.

8. Đối số cuối cùng là dấu chấm câu biểu hiện kết thúc câu.

Chúc các bạn thực hiện thành công!

Xem thêm:

• Đây là những hàm cơ bản nhất trong Excel mà bạn cần nắm rõ
• Cách sử dụng hàm Vlookup trong Excel
• Cách sử dụng hàm SUM để tính tổng trong Excel