Hàm CONCATENATE trong Excel, ứng dụng để nối các chuỗi, ô, mảng và cột

Trong hướng dẫn này, bạn sẽ học các cách khác nhau để nối các chuỗi, các ô, các mảng, các cột và các hàng trong Excel bằng cách sử dụng hàm CONCATENATE và toán tử “&” .

Trong bảng tính Excel của bạn, dữ liệu không phải lúc nào cũng được cấu trúc theo nhu cầu của bạn. Thường thì bạn có thể muốn chia nội dung của một ô thành nhiều ô, hoặc làm ngược lại – kết hợp dữ liệu từ hai cột trở lên vào một cột đơn. Ví dụ phổ biến về việc phải ghép nối trong Excel là nhập tên và địa chỉ, kết hợp văn bản với giá trị được định hướng bằng công thức, hiển thị ngày và giờ với định dạng mong muốn, để đặt tên,..

Xem nhanh

“Concatenate” trong Excel là gì?

Về bản chất, có hai cách để kết hợp dữ liệu trong bảng tính Excel:

Hợp nhất các ô
Kết nối các giá trị của ô.

Khi bạn gộp các ô, nghĩa là hợp nhất hai hoặc nhiều ô vào một ô đơn lẻ. Kết quả là bạn có một ô lớn hơn được hiển thị trên nhiều hàng và / hoặc cột trong bảng tính.

Khi bạn nối các ô trong Excel, bạn chỉ ghép nội dung của các ô đó. Nói cách khác, ghép trong Excel là quá trình kết hợp hai hoặc nhiều giá trị với nhau. Phương pháp này thường được sử dụng để kết hợp một vài đoạn văn bản nằm trong các ô khác nhau (về mặt kỹ thuật, chúng được gọi là text strings hoặc simply strings đơn giản) hoặc chèn một giá trị được tính toán bằng công thức ở giữa một số văn bản.

Hình sau đây thể hiện sự khác biệt giữa hai phương pháp này:

Kết hợp các ô trong Excel là chủ đề của bài viết tiếp theo của chúng ta, và trong hướng dẫn này, chúng ta sẽ có hai cách giải quyết để nối các chuỗi trong Excel – bằng cách sử dụng hàm CONCATENATE và toán tử & .

Đăng ký ngay: khóa học Excel văn phòng 1 thầy 1 trò

Hàm CONCATENATE:

Hàm CONCATENATE trong Excel được thiết kế để nối các đoạn văn bản khác nhau lại hoặc kết hợp các giá trị từ một vài ô vào một ô.

Cú pháp của CONCATENATE như sau:

CONCATENATE (text1, [text2], …)

Trong đó text là một chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức.

Dưới đây là một vài ví dụ về cách sử dụng hàm CONCATENATE trong Excel.

Kết nối các giá trị của một số ô:

Công thức CONCATENATE đơn giản nhất để kết hợp các giá trị của ô A1 và B1 như sau:

=CONCATENATE(A1, B1)

Xin lưu ý rằng các giá trị sẽ được đan lại với nhau mà không có dấu phân tách, như trong hàng 2 trong hình bên dưới.

Để tách các giá trị với khoảng trắng, hãy nhập “” vào đối số thứ hai, như trong dòng 3 trong ảnh chụp màn hình dưới đây.

=CONCATENATE(A1, ” “, B1)

Chia sẻ tài liệu excel cơ bản hay nhất 2018

Kết nối chuỗi văn bản và giá trị di động:

Không có điều gì hạn chế hàm CONCATENATE chỉ nối giá trị của ô. Bạn có thể sử dụng nó để nối các chuỗi văn bản khác nhau để làm cho kết quả có ý nghĩa hơn. Ví dụ:

=CONCATENATE(A1, ” “, B1, ” completed”)

Công thức trên cho người dùng biết rằng công việc nào đó đã hoàn thành, như trong hàng hai trong hình dưới đây. Xin lưu ý rằng chúng ta thêm một dấu cách trước chữ ” completed ” để tách các chuỗi văn bản được nối.

Đương nhiên, bạn có thể thêm một chuỗi văn bản ngay từ đầu hoặc ở giữa công thức Concatenate của bạn:

=CONCATENATE(“See “, A1, ” “, B1)

Một khoảng trống (“”) được thêm vào giữa các giá trị kết hợp, để kết quả hiển thị như là “Project1 ” chứ không phải là “Project1 “.

Kết nối một chuỗi văn bản và một giá trị từ trong công thức tính toán

Để làm cho kết quả trả lại bằng một số công thức dễ hiểu hơn cho người dùng, bạn có thể nối nó với một chuỗi văn bản để giải thích giá trị thực sự là gì.

Ví dụ, bạn có thể sử dụng công thức sau đây để trả lại ngày hiện tại:

=CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy”))

Những điều cần nhớ khi sử dụng CONCATENATE trong Excel

Để đảm bảo rằng các công thức CONCATENATE của bạn luôn mang lại kết quả chính xác, hãy nhớ các quy tắc đơn giản sau đây:

CONCATENATE yêu cầu ít nhất một “text” đối số để làm việc.
Trong một công thức CONCATENATE, bạn có thể nối ghép lên tới 255 chuỗi, tổng cộng là 8,192 ký tự.
Kết quả của hàm CONCATENATE luôn luôn là một chuỗi văn bản, ngay cả khi tất cả các giá trị nguồn là các con số.
CONCATENATE không nhận biết các mảng. Mỗi tham chiếu ô phải được liệt kê riêng. Ví dụ, bạn nên viết =CONCATENATE(A1, A2, A3) thay vì =CONCATENATE(A1:A3).
Nếu ít nhất một trong các đối số của hàm CONCATENATE không hợp lệ, công thức sẽ trả về lỗi #VALUE!.

Dùng “&” để kết nối các chuỗi trong Excel:

Trong Microsoft Excel, toán tử & là một cách khác để nối các ô. Phương pháp này rất có ích trong nhiều trường hợp bởi vì việc gõ (&) nhanh hơn nhiều so với gõ “concatenate”.

Tương tự với hàm CONCATENATE, bạn có thể sử dụng “&” trong Excel để kết hợp các chuỗi văn bản khác nhau, giá trị của ô và kết quả trả về bởi các hàm khác.

Ví dụ về “&”.

Để xem toán tử nối đang hoạt động, hãy viết lại các công thức CONCATENATE đã thảo luận ở trên:

Kết nối các giá trị trong A1 và B1:

=A1&B1

Kết nối các giá trị trong A1 và B1 với khoảng trống:

=A1&” “&B1

Kết nối các giá trị trong A1, B1 và một chuỗi văn bản:

=A1 & B1 & ” completed”

Kết nối một chuỗi và kết quả của hàm TEXT / TODAY:

=”Today is ” & TEXT(TODAY(), “dd-mmm-yy”)

Như đã trình bày trong ảnh chụp màn hình bên dưới, hàm CONCATENATE và toán tử “&” trả kết quả giống hệt nhau:

Xem thêm: Tổng hợp các thủ thuật Excel hay nhất mọi thời đại

“&” với hàm CONCATENATE trong Excel

Nhiều người dùng tự hỏi rằng liệu có cách hiệu quả hơn để nối các chuỗi trong hàm CONCATENATE hoặc toán tử “&” hay không.

Sự khác biệt duy nhất cần thiết giữa hàm CONCATENATE và “&” là hàm CONCATENATE có giới hạn 255 còn & thì không có. Ngoài ra, không có gì khác nhau giữa hai phương pháp nối này, cũng không có sự khác biệt về hiệu lực giữa các công thức CONCATENATE và “&”.

Vì 255 là cũng một con số thực sự lớn và trong thực tế, có thể không bao giờ dùng hết khi kết hợp nhiều chuỗi, sự khác biệt còn lại là sự thoải mái và tiện dụng hơn. Một số người dùng thấy công thức CONCATENATE dễ đọc hơn, cá nhân tôi thích sử dụng phương pháp “&”. Vì vậy, chỉ cần dùng kỹ thuật nối mà bạn cảm thấy thoải mái hơn.

Kết nối các ô bằng dấu cách, dấu phẩy và các ký tự khác:

Trong bảng tính của bạn, bạn có thể thường phải nhập các giá trị có dấu phẩy, dấu cách, dấu chấm câu hoặc các ký tự khác như dấu gạch ngang hoặc dấu gạch chéo. Để làm điều này, chỉ cần bao gồm ký tự mà bạn muốn dùng trong công thức nối của bạn. Hãy nhớ kèm theo ký tự đó trong dấu ngoặc kép, như trong các ví dụ sau đây.

Kết nối hai ô với một khoảng trắng:

=CONCATENATE(A1, ” “, B1) hoặc là =A1 & ” ” & B1

Kết nối hai ô với dấu phẩy:

=CONCATENATE(A1, “, “, B1) hoặc là =A1 & “, ” & B1

Kết nối hai ô với một dấu nối:

=CONCATENATE(A1, “-“, B1) hoặc là =A1 & “-” & B1

Hình sau đây thể hiện kết quả sẽ như thế nào:

Kết nối các chuỗi văn bản với các xuống dòng

Thông thường, bạn sẽ tách các chuỗi văn bản được nối với các dấu chấm câu và dấu cách, như thể hiện trong ví dụ trước. Tuy vậy, Trong một số trường hợp, có thể cần phải tách các giá trị bằng một ngắt dòng. Một ví dụ phổ biến là phải hợp nhất địa chỉ gửi thư từ dữ liệu trong các cột riêng biệt.

Một vấn đề xảy ra là bạn không thể đơn giản chỉ gõ một ngắt dòng trong công thức như một ký tự bình thường và do đó cần một hàm CHAR với mã ASCII tương ứng cho công thức nối:

Trên Windows, sử dụng CHAR (10) trong đó 10 là mã ASCII để xuống dòng .
Trên hệ thống Mac, sử dụng CHAR (13) trong đó 13 là mã ASCII .

Trong ví dụ này, chúng ta có các phần địa chỉ trong các cột A đến F, và chúng ta ghép chúng lại với nhau trong cột G bằng cách sử dụng toán tử nối “&”. Các giá trị được hợp nhất được phân cách bằng dấu phẩy (“,”), dấu cách (“”) và dấu xuống dòng CHAR (10):

=A2 & ” ” & B2 & CHAR(10) & C2 & CHAR(10) & D2 & “, ” & E2 & ” ” & F2

Chú ý. Khi sử dụng ngắt dòng để tách các giá trị được nối, bạn phải có tùy chọn “Wrap text ” cho phép kết quả hiển thị chính xác. Để thực hiện việc này, hãy nhấn Ctrl + 1 để mở hộp thoại Format Cells, chuyển sang tab Alignment và kiểm tra hộp Wrap text.

Theo cách tương tự, bạn có thể tách biệt chuỗi nối với các ký tự khác như:

Dấu ngoặc kép (“) – CHAR (34)
Chéo chéo (/) – CHAR (47)
Dấu sao (*) – CHAR (42)
Danh sách đầy đủ các mã ASCII có thể dễ dàng tìm thấy.

Mặc dù thế, có một cách dễ dàng hơn để chèn các ký tự có thể in được trong công thức nối là chỉ cần gõ chúng vào dấu ngoặc kép như chúng ta đã làm trong ví dụ trước .

Dù bằng cách nào, tất cả bốn công thức dưới đây mang lại kết quả giống hệt nhau:

=A1 & CHAR(47) & B1

=A1 & “/” & B1

=CONCATENATE(A1, CHAR(47), B1)

=CONCATENATE(A1, “/”, B1)

Làm thế nào để nối các cột trong Excel:

Để nối hai cột trở lên trong Excel, bạn chỉ cần nhập công thức nối bình thường vào ô đầu tiên và sau đó sao chép nó xuống các ô khác bằng cách kéo dấu cộng (hình vuông nhỏ xuất hiện ở góc dưới bên phải của ô Chọn).

Ví dụ, để nối hai cột (cột A và B) tách các giá trị với khoảng trắng, bạn nhập công thức sau vào ô C2, và sau đó sao chép nó xuống các ô khác. Khi bạn kéo xuống để sao chép công thức, con trỏ chuột sẽ thay đổi thành hình chữ thập, như thể hiện trong hình bên dưới:

Mẹo. Một cách nhanh chóng để sao chép công thức xuống các ô khác trong cột là di chuột đến ô có công thức và nhấp đúp vào dấu cộng màu đen .

Xin lưu ý cách Microsoft Excel xác định sao chép các ô đến đâu sau khi nhấp đúp chuột ở các ô có công thức của bạn. Nếu có những ô trống trong bảng của bạn, giả sử là ô A6 và B6 trống trong ví dụ này, thì công thức sẽ chỉ được sao chép đến hàng 5. Trong trường hợp này, bạn sẽ cần phải kéo xuống bằng tay để nối tất cả các cột.

Nên xem: khóa học excel nâng cao Hà Nội

Làm thế nào để nối một mảng ô trong Excel:

Kết hợp các giá trị từ nhiều ô có thể mất nhiều công hơn vì hàm CONCATENATE không chấp nhận các mảng và yêu cầu một tham chiếu ô duy nhất trong mỗi đối số.

Để ghép một số ô, ví dụ A1 tới A4, bạn cần một trong hai công thức sau:

=CONCATENATE(A1, A2, A3, A4)

hoặc là

=A1 & A2 & A3 & A4

Khi nối một phạm vi nhỏ, thì nhập tất cả các ô tham chiếu trong công thức cũng không phải vấn đề lớn. Nhưng với nhiều ô thì nhập từng ô theo cách thủ công sẽ rất mất công. Dưới đây bạn sẽ tìm thấy 3 phương pháp ghép nối nhanh chóng trong Excel.

Phương pháp 1. Nhấn CTRL để chọn nhiều ô để nối

Để nhanh chóng chọn một số ô, bạn có thể nhấn phím CTRL và nhấp vào từng ô mà bạn muốn nối trong công thức CONCATENATE. Dưới đây là các bước chi tiết:

Chọn một ô nơi bạn muốn nhập công thức.

Nhập = CONCATENATE (trong ô đó hoặc trong thanh công thức.

Nhấn và giữ CTRL và nhấp vào từng ô mà bạn muốn ghép lại.
Nhả nút CTRL, gõ dấu ngoặc đóng trong thanh công thức và nhấn Enter.

Chú ý. Khi sử dụng phương pháp này bạn phải nhấp vào từng ô riêng lẻ. Việc chọn một dải bằng chuột sẽ thêm một mảng vào công thức, và hàm CONCATENATE không chấp nhận.

Phương pháp 2. Sử dụng hàm TRANSPOSE để lấy phạm vi

Khi bạn cần ghép một dải rộng lớn bao gồm hàng chục hoặc hàng trăm ô, phương pháp trước đó không đủ nhanh vì nó đòi hỏi phải nhấp vào từng ô. Trong trường hợp này, có cách tốt hơn là sử dụng hàm TRANSPOSE để trả về một mảng, và sau đó thay thế nó bằng các tham chiếu ô riêng lẻ.

Chọn ô mà bạn muốn xuất ra dải ô nối.
Nhập công thức TRANSPOSE trong ô đó, ví dụ =TRANSPOSE(A1:A10).
Trong thanh công thức, nhấn F9 để thay thế công thức với các giá trị được tính toán.
Xoá dấu ngoặc nhọn biến một công thức Excel thông thường thành công thức mảng . Kết quả là, bạn sẽ có tất cả các ô tham chiếu để nối trong công thức của bạn.

Gõ = CONCATENATE (ở phía trước của các tham chiếu ô trong thanh công thức, gõ dấu ngoặc đóng và nhấn Enter.

Chú ý. Cho dù bạn sử dụng phương pháp nào, giá trị nối trong C1 là một chuỗi văn bản (chú ý dữ liệu được canh lề trái trong ô), mặc dù mỗi giá trị ban đầu là một số. Điều này là do hàm CONCATENATE luôn trả về một chuỗi văn bản bất kể loại dữ liệu nguồn.

Concatenate với số và ngày tháng ở các định dạng khác nhau:

Khi bạn nối một chuỗi văn bản với một số hoặc ngày, bạn có thể muốn định dạng kết quả khác nhau tùy thuộc vào tập dữ liệu của bạn. Để làm điều này, nhúng các hàm TEXT trong công thức Excel của bạn.

Hàm TEXT (value, format_text) có hai đối số:

Trong đối số đầu tiên ( value), bạn cung cấp một số hoặc ngày để được chuyển đổi sang văn bản hoặc tham chiếu đến ô có chứa một giá trị số.
Trong đối số thứ hai ( format_text ), bạn nhập định dạng mong muốn các mã để hàm TEXT có thể hiểu.

Tôi sẽ chỉ nhắc bạn rằng khi kết hợp một chuỗi văn bản và ngày tháng , bạn phải sử dụng hàm TEXT để hiển thị ngày ở định dạng mong muốn. Ví dụ:

=CONCATENATE(“Today is “, TEXT(TODAY(), “mm/dd/yy”))

hoặc là

=”Today is ” & TEXT(TODAY(), “mm/dd/yy”)

Một vài ví dụ công thức bổ sung nối tiếp một giá trị văn bản và số sau:

=A2 & ” ” & TEXT(B2, “$#,#0.00”) – hiển thị số với 2 chữ số thập phân và dấu $.

=A2 & ” ” & TEXT(B2, “0.#”) – không hiển thị số không trước dấu phảy và dấu $.

=A2 & ” ” & TEXT(B2, “# ?/???”) – hiển thị số dưới dạng một phân số.

Để 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 các khóa học của Học Excel Online. Chúc bạn học tốt