Hàm Concatenate Có Điều Kiện

backlinks.vn sẽ giới thiệu chi tiết cách sử dụng 2 hàm hợp nhất văn bản, ghép nối thông tin trong bài viết này. Bạn có thể thành thạo thủ thuật này trong khi làm việc với Excel.

Bạn đang xem: Hàm concatenate có điều kiện

Đăng ký ngay khóa họcTuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

Những bạn sử dụngExcel phiên bản 2016cũng đừng lo lắng nhé, hãy chờ đến cuối bài viết để có thể ứng dụng cách sử dụng của những hàm này vào bất cứ phiên bản Excel nào.

Cách dùng hàm CONCATENATE

Ví dụ 1: Ghép họ và tên bằng hàm CONCATENATE

Hãy thử nhìn qua ví dụ phía bên dưới đây:

Yêu cầu của bài tập này là chúng ta cần phải ghép thông tin First Name và Last Name để điền vào ô Full Name

Sử dụng hàm CONCATENATE

Công thức của hàm CONCATENATE này là:

=CONCATENATE (text1, , …)

Trong đó, hàm CONCATENATE cho phép bạn kết nối các dữ liệu từ nhiều ô tính chứa dữ liệu khác nhau (cho dù là ô hay chữ)chỉ bằng việc sử dụng dấu "," để ngăn cách.

Lưu ý: Bạn nên để một khoảng trống " " giữa ô A2 và B2 để kết quả hiển thị sẽ có sự ngăn cách giữa họ và tên.

Sử dụng nhập kí tự mà không dùng bất kỳhàm ghép chuỗi trong Excel

Ngoài ra, có một cách làm khá đơn giản và thủ công hơn chính là sử dụng dấu (&) trong ô chứa công thức như sau:

Kết quả thu được cũng chính xác như khi dùng hàm CONCATENATE. Nhưng trong thực tế, việc sử dụng hàm CONCATENATE còn phức tạp hơn 2 cách sử dụng trên do độ dài của các chữ cái cần gõ ( như một câu bao gồm nhiều từ). Hãy cùng xem ví dụ bên dưới để hiểu rõ hơn nhé.

Ví dụ 2: Ghép nối ký tự với hàm CHAR

Trong trường hợp, chúng ta cần nối thông tin từ 2 ô nhưng cần sử dụng các cụm từ để diễn đạt được nội dung yêu cầu như sau:

Cụm từ chúng ta cần điền vào ô Analysis là câu nói thể hiện doanh thu bán hàng của tháng:We saw 20% growth with sales reaching $120M

Trong ví dụ này, chúng ta không những phải ghép thông tin 2 ô Growth với Sales lại mà còn phải sử dụng các từ ngữ thêm vào để tạo thành câu. Vậy, với cả 2 cách chúng tôi đã hướng dẫn các bạn như ở trên, các bạn sẽ làm thế nào?

Đây là câu trả lời được đưa ra:

Lưu ý:Khi bạn cho bất cừ từ gì dạng Text vào trong dấu ngoặc kép (""), nội dung trong dấu sẽ được xuất hiện y nguyên trên kết quả hiển thị. Như "We saw" hay "growth with sales reaching" trong ví dụ này

Mẹo (Tips):Trường hợp bạn đọc cần thêm dấu ngoặc kép (" ") vào trong câu, bạn có thể sử dụng công thức:

=CHAR(34) - để tạo hai dấu ngoặc kép

Ví dụ: Bạn ghi vào ô là =CHAR(34) & “Tên” & CHAR(34) khi đó kết quả sẽ được hiển thị trên màn hình là "Tên". Trong đó, CHAR(34) là công thức của dấu (") trước và sau từ Tên

Cách dùng hàm CONCAT thay thế hàn CONCATENATE

Hàm CONCAT có trong tất cả các phiên bảnmới nhất của Excel ( Excel 2016, Excel Mobile and Excel Online), hàm CONCATENATE trước đó cũng có trong các phiên bản này nhưng người dùng được khuyến khích sử dụng hàm CONCAT thay thế. Hàm CONCAT bao gồm tất cả tính năng của hàm CONCATENATE, nhưng sử dụng linh hoạt hơn.

Một trong những tính năng được nâng cấp hơn hàm CONCATENATE chính là có thể ghép nối thông tin từ một dãy các ô liên tiếp thay vì chọn các ô riêng lẻ như trước.

Xem thêm: 9 Cách Download Video Trên Youtube Nhanh Chóng, Đơn Giản Không Ngờ

Cùng xem qua ví dụ sau:

Ưu điểm của hàm: Khi bạn cần nối nhiều ô dữ liệu liên tiếp với nhau, việc chọn các ô đơn lẻ khá tốn công sức và thời gian, do đó hàm CONCAT cực kì hữu dụng. Đặc biệt, nếu bạn để ý sẽ thấy ô B3 không hề có dữ liệu, hàm CONCAT đã linh hoạt bỏ qua ô trống đó và vẫn đưa ra kết quả chính xác.

Nhược điểm của hàm: hàm CONCAT không đưa ra giải pháp tạo khoảng cách " " giữa các dữ liệu được ghép vào nhau. Vậy, phải làm thế nào khi vừa ghép được nhiều ô dữ liệu lại vừa thêm được khoảng ngăn cách giữa chúng. Cùng tìm hiểu tiếp dưới đây nhé.

Hàm TEXTJOIN -hàm ghép chuỗi trong Excel

Giải pháp cho bạn ở câu hỏi trên ví dụ trước chính là hàm TEXTJOIN, hàm ghép chuỗi trong Excel cho phép thêm khoảng cách giữa các Tên để ghép thành Tên đầy đủ. Công thức của hàm này như sau:

=TEXTJOIN(Delimiter,Ignore_Empty,Text)

Trong đó,

Delimiter (Dấu tách): Là dấu được đặt giữa các dữ liệu trong từng ô hay văn bản được ghép nối (dấu được chỉ định hoặc một chuỗi các dấu)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à “Bút,sách,vở”có thể thành “Bút,sách,,vở”)Text: Là văn bản cần ghép 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ờ, hãy thử dùng hàm ghép chuỗi trong Excel - TEXTJOIN để giải quyết yêu cầu trong ví dụ trên nhé

Kết quả hiện thị: Tên đầy đủ đã được tách nhau bởi dấu cách, ô trống B3 cũng được bỏ qua. Vậy nếu bạn chọn FALSE thay vì TRUE thì điều gì sẽ xảy ra? Hãy cùng xem hình dưới đây:

Như đã hướng dẫn trước đó, kết quả sẽ hiển thị 2 dấu tách (ở đây là dấu phẩy ","). Do đó, hầu hết mọi trường hợp, bạn đọc nên điền TRUE vào công thức của hàm.

Cách sử dụng hàm CONCAT và TEXTJOIN- các hàmghép chuỗi trong Excel phiên bản trước 2016

Nếu bạn không sử dụng Excel 2016, bạn hoàn toàn có thể sử dụng các hàm này trong phiên bản Excel của bạn bằng cách lập mã VBA, sao cho có thể bắt chước tính năng của 2 hàm ghép chuỗi trong Excel: hàm 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.

Xem thêm: Điểm Thi Đại Học Mở Hà Nội 2020 Chính Xác, Điểm Chuẩn Đại Học Mở Tp Hcm 2020

Hãy sử dụng phím tắtALT + F11 để mở cửa sổVisual Basic Editor (nếu phím tắt không hoạt động hãy đọc bài viết dưới mục này ). Sau đó, hãy thực hiện các thao tác sau:

Nhấn chuột phải vào tên file đang sử dụngVào InsertChọn ModuleỞ phần trống, dán mã code ( được cung cấp ở dưới)Thoát ra khỏi cửa sổ Visual Basic EditorLưu file

Mã code cho hàm ghép chuỗi trong Excel - CONCAT

Public Function CONCAT(ParamArray Text1() As Variant) As String‘PURPOSE: Replicates The Excel 2016 Function CONCAT‘SOURCE: www.TheSpreadsheetGuru.comDim RangeArea As VariantDim Cell As Range’Loop Through Each Cell in Given InputFor Each RangeArea In Text1If TypeName(RangeArea) = “Range” ThenFor Each Cell In RangeAreaIf Len(Cell.Value) 0 ThenCONCAT = CONCAT & Cell.ValueEnd IfNext CellElse‘Text String was EnteredCONCAT = CONCAT & RangeAreaEnd IfNext RangeAreaEnd Function

Mã code cho hàm ghép chuỗi trong Excel - 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 VariantDim Cell As Range’Loop Through Each Cell in Given InputFor Each RangeArea In Text1If TypeName(RangeArea) = “Range” ThenFor Each Cell In RangeAreaIf Len(Cell.Value) 0 Or Ignore_Empty = False ThenTEXTJOIN = TEXTJOIN & Delimiter & Cell.ValueEnd IfNext CellElse‘Text String was EnteredIf Len(RangeArea) 0 Or Ignore_Empty = False ThenTEXTJOIN = TEXTJOIN & Delimiter & RangeAreaEnd IfEnd IfNextTEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)End FunctionHy vọng qua bài viết này, backlinks.vn đã mang đến cho bạn đọc các giải pháp tin học nhanh chóng và hiệu quả, chúng mình luôn mong muốn đồng hành cùng những nhân viên văn phòng thế hệ tương lai những kiến thức hữu dụng không chỉ về cách dùng hàm ghép chuỗi trong Excel như hàm CONCATENATE hay CONCAT hay TEXTJOIN,.... Tuy nhiên, chỉ với 3 hàm được giới thiệu hôm nay, bạn cần những kiến thức liên quan đến hàm VLOOKUP, hàmIF hay hàm AND,... để có thể làm chủ cũng như dễ dàng hơn trong khi làm việc với Excel.

Cùng nhau học tốt nhé!

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế