Hãy cùng chúng tôi tìm hiểu về cách sử dụng hàm Concatenate trong Excel trong bài viết bên dưới nhé!
“Concatenate” trong Excel là gì?
Xét về bản chất, sẽ có hai cách để thực hiện 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 tiến hành gộp các ô, tức là hợp nhất hai hoặc là nhiều ô vào một ô đơn lẻ. Kết quả bạn nhận được sẽ là một ô lớn hơn được hiển thị ở nhiều hàng và / hoặc là nhiều cột trong bảng tính.
Khi bạn thực hiện nối những ô trong Excel, bạn chỉ ghép nội dung của những ô đó. Hay nói cách khác, quá trình ghép dữ liệu trong Excel là quá trình kết hợp giữa hai hoặc là nhiều giá trị với nhau. Cách làm này thường được dùng để thực hiện kết hợp một vài đoạn văn bản nằm trong những ô khác nhau (về mặt kỹ thuật, các ô này được gọi là text strings hoặc simply strings đơn giản) hoặc là chèn một giá trị được tính toán bằng việc sử dụng công thức ở giữa một số văn bản.
Ảnh minh họa dưới đây thể hiện được sự khác biệt giữa hai phương pháp này:
Việc kết hợp những ô trong Excel là chủ đề của bài viết tiếp theo và trong hướng dẫn này, bạn sẽ có hai cách giải quyết để nối những chuỗi trong Excel – bằng việc dùng hàm CONCATENATE và toán tử & .
Hàm CONCATENATE:
Hàm CONCATENATE được thiết kế nối những đoạn văn bản khác nhau lại hoặc là kết hợp những giá trị từ một vài ô vào một ô.
Cú pháp của CONCATENATE như bên dưới:
CONCATENATE (text1, [text2], …)
Trong đó, text là một chuỗi văn bản, các tham chiếu ô hoặc là giá trị trong công thức.
Sau đây sẽ là một vài ví dụ về cách dùng hàm CONCATENATE.
Kết nối những giá trị của một số ô:
Công thức CONCATENATE đơn giản nhất để thực hiện kết hợp giá trị của ô A1 và B1 như sau:
=CONCATENATE(A1, B1)
Lưu ý rằng, giá trị sẽ được đan lại với nhau mà không có sự xuất hiện của dấu phân tách, như trong hàng 2 ở hình bên dưới.
Để tiến hành tách các giá trị với khoảng trắng, bạn hãy nhập “” vào đối số thứ hai, tương tự như trong dòng 3 dưới ảnh chụp màn hình:
=CONCATENATE(A1, ” “, B1)
Kết nối chuỗi văn bản và giá trị di động:
Không có gì có thể hạn chế hàm CONCATENATE chỉ nối giá trị của ô. Bạn hoàn toàn có thể dùng nó để thực hiện nối các chuỗi văn bản khác nhau sao cho kết quả có ý nghĩa hơn. Ví dụ như:
=CONCATENATE(A1, ” “, B1, ” completed”)
Công thức này cho chúng ta biết rằng công việc nào đó đã hoàn thành, như tại hàng hai ở hình dưới đây. Bạn cũng nên lưu ý rằng việc thêm một dấu cách phía trước chữ ” completed ” để tách những chuỗi văn bản được nối.
Tất nhiên, bạn có thể tiến hành thêm một chuỗi văn bản ngay từ đầu hoặc là ở 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 những giá trị kết hợp, điều này giúp cho 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
Để có thể làm cho kết quả trả lại dưới dạng một số công thức dễ hiểu hơn, bạn có thể tiến hành nối nó với một chuỗi văn bản để có thể giải thích giá trị thực sự là gì.
Ví dụ như, bạn có thể 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 dùng hàm CONCATENATE trong Excel
Để đảm bảo rằng những công thức CONCATENATE luôn mang lại kết quả chính xác,bạn hãy nhớ những quy tắc đơn giản sau đây:
- CONCATENATE là hàm yêu cầu ít nhất một “text” đối số để làm việc.
- Trong một công thức của hàm CONCATENATE, bạn có thể thực hiện nối ghép lên tới 255 chuỗi, tổng cộng 8,192 ký tự.
- Kết quả của hàm CONCATENATE sẽ mặc định là một chuỗi văn bản, ngay cả khi toàn bộ những giá trị nguồn là các con số.
- CONCATENATE không thể nhận biết các mảng. Mỗi tham chiếu ô phải được liệt kê riêng. Ví dụ như bạn nên viết =CONCATENATE(A1, A2, A3) thay cho =CONCATENATE(A1:A3).
- Nếu như ít nhất một trong những đối số của hàm CONCATENATE không hợp lệ thì 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 thì toán tử & là một phương pháp khác để nối các ô. Cách này có ích trong nhiều trường hợp vì việc gõ (&) nhanh hơn rất nhiều so với gõ “concatenate”.
Tương tự như hàm CONCATENATE, bạn có thể dùng “&” trong Excel để tiến hành kết hợp những chuỗi văn bản khác nhau, giá trị của ô và kết quả trả về bởi những hàm khác.
Ví dụ về “&”.
Để có thể xem toán tử nối đang hoạt động, bạn hãy viết lại các công thức CONCATENATE đã thảo luận ở trên:
Kết nối những giá trị trong A1 và B1:
=A1&B1
Kết nối những giá trị trong A1 và B1 với khoảng trống:
=A1&” “&B1
Kết nối những 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”)
Theo dõi cách trình bày trong ảnh dưới, hàm CONCATENATE và toán tử “&” đã trả kết quả giống hệt nhau:
“&” với hàm CONCATENATE trong Excel
Nhiều người hay tự hỏi rằng liệu có cách nào hiệu quả hơn để thực hiệ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 xem là cần thiết giữa hàm CONCATENATE và “&” đó là hàm CONCATENATE sẽ có giới hạn 255, trong khi & thì không có. Ngoài điểm trên, không có gì khác nhau giữa 2 phương pháp nối này, cũng như không có sự khác biệt về hiệu lực giữa những công thức CONCATENATE và “&”.
Vì 255 trong thực tế là một con số thực sự lớn, bạn có thể không bao giờ sử dụng hết khi kết hợp nhiều chuỗi. Sự khác biệt còn lại đó chính là sự thoải mái và tiện dụng 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:
Ở bảng tính, bạn có thể thường phải thực hiện nhập những giá trị có dấu phẩy, dấu cách, dấu chấm câu hoặc là các ký tự khác như là dấu gạch ngang hay dấu gạch chéo. Để làm được điều này, chỉ cần bao gồm ký tự bạn muốn dùng trong công thức nối của bạn. Lưu ý là nhớ kèm theo ký tự đó trong dấu ngoặc kép, tham khảo ngay ví dụ dưới đây:.
Kết nối hai ô cùng một khoảng trắng:
=CONCATENATE(A1, ” “, B1) hoặc là =A1 & ” ” & B1
Kết nối hai ô cùng dấu phẩy:
=CONCATENATE(A1, “, “, B1) hoặc là =A1 & “, ” & B1
Kết nối hai ô cùng một dấu nối:
=CONCATENATE(A1, “-“, B1) hoặc là =A1 & “-” & B1
Và kết quả được thể hiện ở ảnh bên dưới:
Kết nối các chuỗi văn bản với các xuống dòng
Thường thì bạn sẽ tách các chuỗi văn bản được nối với những dấu chấm câu và dấu cách . Tuy vậy, ở một vài trường hợp, đôi khi bạn cần phải tách những 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 những cột riêng biệt.
Vấn đề ở đây là rất khó để bạn chỉ gõ một ngắt dòng trong công thức tương tự như một ký tự bình thường. Do đó, cần có một hàm CHAR với mã ASCII tương ứng cho công thức nối này:
- Trong Windows, bạn sử dụng CHAR (10) trong đó 10 là mã ASCII để xuống dòng .
- Trong hệ thống Mac, bạn sử dụng CHAR (13) trong đó 13 là mã ASCII .
Ví dụ bên dưới, bạn có các phần địa chỉ trong các cột A đến F, và cần ghép chúng lại với nhau trong cột G bằng cách dùng toán tử nối “&”. Những giá trị được hợp nhất phân cách bằng dấu phẩy (“,”), dấu cách (“”), dấu xuống dòng CHAR (10):
=A2 & ” ” & B2 & CHAR(10) & C2 & CHAR(10) & D2 & “, ” & E2 & ” ” & F2
Chú ý. Khi dùng ngắt dòng để thực hiện tách những giá trị được nối, bạn cần phải có tùy chọn “Wrap text ” để kết quả được hiển thị chính xác. Bạn hãy nhấn Ctrl + 1, mở hộp thoại Format Cells, rồi chuyển sang tab Alignment, sau đó kiểm tra hộp Wrap text.
Tương tự, bạn cũng có thể tách biệt chuỗi nối với những 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 đủ những mã ASCII có thể dễ dàng tìm thấy.
Tuy vậy, còn có một cách làm dễ dàng hơn trong việc chèn ký tự có thể in được trong công thức nối, đó là chỉ cần gõ chúng vào trong dấu ngoặc kép như bạn đã làm ở ví dụ trước .
Cho dù bạn dùng cách nào, thì cả bốn công thức dưới đây đều 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)
Cách nối các cột trong Excel:
Để có thể nối 2 cột trở lên, bạn chỉ đơn giản nhập công thức nối vào ô đầu tiên, rồi thực hiện sao chép nó xuống những ô khác bằng việc kéo dấu cộng nhỏ ở góc dưới bên phải của ô chọn.
Ví dụ nhưu, bạn đang muốn nối 2 cột A và B tác các giá trị với khoảng trống. Bạn tiến hành nhập công thức sau đây vào ô C2, rồi thực hiện sao chép nó xuống những ô khác. Khi bạn đã kéo xuống dưới để sao chéo công thức, con trỏ chuột lúc này sẽ thay đổi thành hình chữ nhật như hình mô tả bên dưới.
Mẹo. Một phương pháp nhanh chóng để tiến ahnhf sao chép công thức xuống những ô khác trong cột đó là di chuột đến ô có công thức và thực hiện nhấp đúp vào dấu cộng màu đen .
Lưu ý rằng, cách mà Microsoft Excel xác định sao chép những ô đến đâu sau việc nhấp đúp chuột ở những ô có công thức của bạn. Nếu có các ô trống trong bảng của bạn, ví dụ ô A6 và B6 trống ở ví dụ này, công thức sẽ chỉ được sao chến tới hàng 5. Ở trường hợp này, bạn cần thực hiện kéo xuống bằng tay để có thể nối tất cả các cột.
Làm thế nào để nối một mảng ô trong Excel:
Kết hợp những giá trị từ nhiều ô có thể mất nhiều thời gian hơn bởi vì hàm CONCATENATE không chấp nhận những mảng và yêu cầu một tham chiếu ô duy nhất trong từng đối số.
Để có thể ghép một số ô, ví dụ như A1 tới A4, bạn cần thực hiệ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ì việc nhập tất cả những ô tham chiếu trong công thức được xem không phải vấn đề lớn. Tuy nhiên, với nhiều ô thì nhập từng ô theo phương pháp thủ công sẽ rất mất công. Bên dưới đây, bạn sẽ tìm thấy 3 cách ghép nối nhanh chóng trong Excel.
Phương pháp 1. Nhấn CTRL để chọn nhiều ô để nối
Để có thể nhanh chóng chọn một số ô, bạn nhấn phím CTRL, rồi nhấp vào từng ô bạn muốn nối trong công thức CONCATENATE. Các bước chi tiết như sau:
Bước 1: Đầu tiên, bạn chọn một ô nơi mà bạn muốn nhập công thức.
Bước 2: Tiếp đó, nhập = CONCATENATE (trong ô đó hoặc trong thanh công thức.
Bước 3: Nhấn và giữ CTRL, rồi nhấp vào từng ô mà bạn muốn ghép lại.
Bước 4: Cuối cùng, nhả nút CTRL, gõ dấu ngoặc đóng trong thanh công thức rồi nhấn Enter.
Chú ý. Khi bạn dùng này, phải nhấp vào từng ô riêng lẻ. Việc chọn một dải bằng chuột sẽ giúp thêm một mảng vào công thức, và hàm CONCATENATE sẽ không chấp nhận.
Xem thêm: Cách in 2 mặt đóng sách trong word không cần máy in hai mặt
Phương pháp 2. Sử dụng hàm TRANSPOSE để lấy phạm vi
Khi bạn muốn ghép một dải rộng lớn bao gồm hàng chục hoặc là hàng trăm ô,cách làm trước đó không đủ nhanh bởi vì nó đòi hỏi phải nhấp vào từng ô. Ở trường hợp này, có cách tốt hơn là dùng hàm TRANSPOSE để trả về một mảng, rồi thay thế nó bằng những tham chiếu ô riêng lẻ. Các bước như sau:
Bước 1: Đầu tiên, bạn chọn ô mà bạn muốn xuất ra dải ô nối.
Bước 2: Tiến hành nhập công thức TRANSPOSE trong ô đó, ví dụ như =TRANSPOSE(A1:A10).
Bước 3: Tại thanh công thức, bạn nhấn F9 để thay thế công thức với các giá trị được tính toán.
Bước 4: Sau đó, xoá dấu ngoặc nhọn biến một công thức Excel thông thường trở thành công thức mảng . Kết quả lúc này là bạn sẽ có toàn bộ các ô tham chiếu để nối trong công thức của bạn.
Bước 5: Cuối cùng, 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 rồi nhấn Enter.
Chú ý. Cho dù bạn dùng cách nào, thì 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 cho mỗi giá trị ban đầu là một số. Điều này là bởi 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 nào.
Concatenate với số và ngày tháng ở các định dạng khác nhau:
Khi bạn thực hiện nối một chuỗi văn bản với một số hay ngày, bạn 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 được điều này, bạn nhúng các hàm TEXT trong công thức Excel.
Hàm TEXT (value, format_text) sẽ có hai đối số:
- Trong đối số đầu tiên ( value), bạn cung cấp một số hoặc là ngày để được chuyển đổi thành văn bản hoặc là tham chiếu đến ô có chứa một giá trị số.
- Trong đối số thứ hai ( format_text ), bạn thực hiện nhập định dạng mong muốn những mã để hàm TEXT có thể hiểu.
Bạn lưu ý rằng khi kết hợp một chuỗi văn bản và ngày tháng , bạn sẽ 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ố.
Trên đây là hướng dẫn vô cùng chi tiết về cách sử dụng hàm Concatenate trong Excel, chúc bạn thực hiện thành công!
Xem thêm: Hướng dẫn cách sử dụng hàm Choose trong Excel
