GHÉP NỐI KÝ TỰ VỚI HÀM CONCAT VÀ TEXTJOIN TRONG EXCEL

Chuyên mục:
VBA

Làm thế nào để ghép chuỗi trong excel, đừng bỏ lỡ qua bài viết này của Blog Học Excel bạn nhé!

Nghệ Thuật Ghép Nối Ký Tự

Có rất nhiều lý do tại sao bạn muốn ghép hay nối nhiều giá trị ở các ô khác nhau vào cùng một ô: có thể là tạo 1 ID để thực hiện tra cứu hay chỉ đơn giản là muốn ghép nối các thông tin lại với nhau; ví dụ như tên thành phố với tên quốc gia. Những người phát triển Microsoft Excel đã nhận ra tầm quan trọng của việc nối dữ liệu trong Excel và do đó đã tạo ra hai hàm mới giúp chúng ta có thể hợp nhất văn bản chỉ với các thao tác đơn giản. Trong bài này, bạn sẽ học cách nối ký tự căn bản với hai hàm mới này và hy vọng rằng bạn sẽ áp dụng chúng khi làm việc với bảng tính.

Tuy nhiên, nếu bạn sử dụng các phiên bản Excel trước 2016 thì cũng đừng lo lắng bởi cuối bài viết này bạn sẽ được học cách làm thế nào để sử dụng các hàm này trong bất cứ phiên bản Excel nào.

Xem nhanh

Hàm Nối Ký Tự – CONCAT

CONCAT () thực sự là một hàm thú vị; nó có trong các phiên bản mới nhất của Excel ( Excel 2016, Excel Mobile and Excel Online). Tuy hàm trước đó CONCATENATE() vẫn có trong các phiên bản mới này, nhưng bạn được khuyến khích nên sử dụng hàm CONCAT thay thế. Hàm CONCAT () bao hàm tất cả tính năng mà hàm CONCATENATE có trước đó nhưng tăng thêm tính linh hoạt.

Trước tiên, hãy cùng bàn luận về hàm CONCATENATE(). Nếu bạn muốn ghép dữ liệu trong nhiều ô khác nhau, thì câu trả lời hầu hết là sử dụng hàm CONCATENATE. Tuy nhiên, đây không phải là cách làm duy nhất trong Excel. Xem ví dụ dưới đây:

Chúng ta có thể sử dụng hàm CONCATENATE() để tạo thành tên đầy đủ, nhưng có một cách đơn giản hơn đó là sử dụng ký hiêu (ie &) để ghép nội dung các ô lại với nhau.

Chú ý rằng phải để một khoảng trống (ie “ “) giữa ô A2 và ô B2 để khi nối lại sẽ có khoảng cách giữa họ và tên. Bây giờ hãy xem kết quả khi áp dụng với hàm CONCENTENATE ().

Rõ ràng là từ những ví dụ trên có thể thấy rằng cùng một kết quả có thể thực hiện bằng nhiều cách khác nhau. Trong thực tế,có lẽ bạn sẽ cho rằng sử dụng CONCATENATE sẽ phức tạp hơn hai phương pháp nêu trên bởi độ dài của các chữ cái cần gõ.

Cú pháp của hàm CONCENTENATE là : =CONCATENATE (text1, [text2], …). Về cơ bản, hàm cho phép bạn nối nội dung trong nhiều ô bằng cách đặt dấu phẩy giữa mỗi chuỗi văn bản cho dù nó là ô hay chữ.

Trước khi chuyển sang tính năng mới của hàm CONCAT thì hãy xem cách mà chúng ta có thể mở rộng việc nối nội dung trong nhiều ô bằng cách chèn chữ vào trong chuỗi để nối với nhau. Xem dữ liệu sau:

Mục tiêu ở đây là tạo một câu để nói về doanh thu bán hàng của tháng. Trong trường hợp này, không những phải chèn nội dung của các ô vào công thức CONCATENATE() mà còn cả các chữ để tạo thành câu. Kết quả sẽ được như sau:

We saw 24% growth with sales reaching $105M.

Trong giải thích dưới đây, lưu ý rằng khoảng trống được chèn thêm vào văn bản (ie “We saw “). Khi bạn chèn bất cứ thứ gì vào giữa hai dấu ngoặc kép thì nó sẽ được xuất hiện trong kết quả.

Pro Tip: Nếu bạn cần thêm dấu ngoặc kép vào trong câu, thì có thể sử dụng công thức =CHAR(34) để tạo hai dấu ngoặc kẹp. Ví dụ: = CHAR(34) & “Sales” & CHAR(34) sẽ cho ra kết quả là “Sale”.

Nhắc lại từ ví dụ đầu tiên, hàm CONCATENATE()dùng để nối nội dung các ô mà chúng ta đưa vào công thức. Và nhớ thêm khoảng trống giữa các chữ.

Giới Thiệu Hàm Mới- Hàm CONCAT

Điểm hạn chế chính của hàm CONCATENATE() mà hàm CONCAT() đã khắc phục được là bây giờ người dùng có thể chọn một dải ô để nối lại với nhau trong khi trước kia chúng ta bị giới hạn khi phải ấn chọn từng ô đơn lẻ.

Đây là một tính năng rất hữu ích, đặc biệt là khi có nhiều cột dữ liệu cần nối. Hơn nữa, chú ý rằng trong dòng 3 ở ví dụ trên, khi không có dữ liệu ở cột B, hàm CONCAT() đã hoàn toàn bỏ qua nó và tiếp tục nối dữ liệu trong ô tiếp theo.

Các ví dụ cụ thể trên đã nêu ra một câu hỏi thú vị là : Vậy làm thế nào nếu chúng ta cần khoảng cách giữu các chữ khi nối chúng lại với nhau? Trong khi hàm CONCAT() không đưa ra giải pháp trực tiếp cho điều này,thì sau đây là một hàm khác mà chúng ta có thể sử dụng.

Xem thêm: Hướng dẫn sử dụng excel 2020

Thêm Dấu Tách Khoảng Trống Với Hàm TEXTJOIN

Theo như ví dụ trước, chúng ta có thể sử dụng một hàm khác tên là TEXTJOIN() để thêm khoảng cách trong tên đầy đủ. Nếu bạn chưa biết các sử dụng hàng TEXTJOIN thì xem bài viết này trước nhé, sau đó hãy quay lại bài viết này.

Cách sử dụng Hàm TEXTJOIN

Đầu tiên hãy xem 3 đầu vào cần nhập của TEXTJOIN():

Delimiter (Dấu tách): Đây là dấu được chỉ định hoặc một chuỗi các dấu được chèn vào giữa văn bản hay ô.
Ignore_Empty: Hỏi liệu bạn có muốn bỏ qua các giá trị trống không? Nếu chọn FALSE, thì bạn có thể sẽ bị 2 dấu tách đặt cạnh nhau ( ví dụ : kết quả là “Apple,Orange,Banana”có thể thành “Apple, Orange,,Banana”)
Text: văn bản cần nối. Có thể là chuỗi văn bản hoặc một dãy các chuỗi ( một dải ô). Điều này tương tự như khi áp dụng với hàm CONCATENATE() hay CONCAT().

Bây giờ chúng ta đã biết cách nhập công thức cho hàm TEXTJOIN(), Hãy cùng quay lại ví dụ về tên và thử nối tên, tên đệm và họ lại và có khoảng cách giữa chúng.

Có thể thấy rằng chúng ta đã phân tách gía trị ở các ô bằng khoảng trống và bỏ qua các giá trị trống ( ô B3). Bây giờ, hãy xem kết quả ra sao nếu chúng ta thay đổi Ignore_Empty thành FALSE.

Hãy xem một ví dụ khác khi phân cách bằng dấu phẩy để thấy rõ hơn.

Phần được nhấn mạnh trong hình trên, vì giá trị trống ở ô B3 đã tạo ra kết quả là 2 dấu phẩy đứng liền nhau. Tùy theo kết quả bạn mong muốn, nhưng trong hầu hết trường hợp thì nên để Ignore_Empty là TRUE.

Hàm Do Người Dùng Định Nghĩa ( User Defined Functions ) VBA Cho Các Phiên Bản Trước 2016

Nếu như bạn không dùng Excel 2016, vẫn có cách khác để sử dụng các hàm này. Dưới đây là quy trình lập mã VBA,cái mà có thể bắt chước tính năng của CONCAT và TEXTJOIN. Bạn có thể nhúng mã macro VBA này vào trong bảng tính của mình và gọi chúng ra từ thanh công thức giống như các hàm bình thường khác.

Để thêm các hàm này, sử dụng phím tắt ALT + F11 để mở Visual Basic Editor. Khi vào phần chỉnh sửa, bạn có thể:

Nhấn chuột phải vào tên file đang sử dụng
Vào Insert
Chọn Module
Ở phần trống, dán mã code
Thoát ra khỏi cửa sổ Visual Basic Editor
Lưu file

Và bắt đầu gõ tên hàm trong thanh công thức và nó sẽ xuất hiện trong hộp gợi ý.

Hàm CONCAT

Public Function CONCAT(ParamArray Text1() As Variant) As String
‘PURPOSE: Replicates The Excel 2016 Function CONCAT
‘SOURCE: www.TheSpreadsheetGuru.comDim RangeArea As Variant
Dim Cell As Range’Loop Through Each Cell in Given Input
For Each RangeArea In Text1
If TypeName(RangeArea) = “Range” Then
For Each Cell In RangeArea
If Len(Cell.Value) <> 0 Then
CONCAT = CONCAT & Cell.Value
End If
Next Cell
Else
‘Text String was Entered
CONCAT = CONCAT & RangeArea
End If
Next RangeAreaEnd Function

Hàm TEXTJOIN

Public Function TEXTJOIN(Delimiter As String, Ignore_Empty As Boolean, ParamArray Text1() As Variant) AsString
‘PURPOSE: Replicates The Excel 2016 Function CONCAT
‘SOURCE: www.TheSpreadsheetGuru.comDim RangeArea As Variant
Dim Cell As Range’Loop Through Each Cell in Given Input
For Each RangeArea In Text1
If TypeName(RangeArea) = “Range” Then
For Each Cell In RangeArea
If Len(Cell.Value) <> 0 Or Ignore_Empty = False Then
TEXTJOIN = TEXTJOIN & Delimiter & Cell.Value
End If
Next Cell
Else
‘Text String was Entered
If Len(RangeArea) <> 0 Or Ignore_Empty = False Then
TEXTJOIN = TEXTJOIN & Delimiter & RangeArea
End If
End If
Next
TEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)
End Function

Tham khảo thêm cách gộp ô, ghép chữ, chuỗi theo điều kiện.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học VBA:

[Đăng ký] Khoá học VBA Cơ bản