Hàm CONCATENATE trong Excel

Deal Score0
Deal Score0

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

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. Nói chung, 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 hoặc nhiều cột thành một cột duy nhất. Một ví dụ phổ biến về việc phải kết nối trong Excel là nhập tên và địa chỉ. Kết hợp văn bản với các giá trị theo hướng công thức Hiển thị ngày và giờ ở định dạng mong muốn, đặt tên, v.v.

“Concatenate” trong Excel là gì?

Về cơ bản 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 thêm ô trong Excel, bạn chỉ kết nối. Nội dung những tế bào đó Nói cách khác, nối trong Excel là quá trình nối hai hoặc nhiều giá trị lại với nhau. Phương pháp này thường được sử dụng để kết hợp nhiều đoạn văn bản nằm trong các ô khác nhau. (về mặt kỹ thuật được gọi là chuỗi văn bản hoặc chuỗi ký tự) hoặc để chèn một giá trị được tính bằng công thức. ở giữa một số văn bản

Hình sau cho thấy sự khác biệt giữa hai phương pháp:
Kết hợp và tham gia trong Excel

Kết hợp các ô trong Excel là chủ đề của bài viết tiếp theo của chúng tôi. trong hướng dẫn này Chúng tôi sẽ cung cấp hai giải pháp để nối các chuỗi trong Excel bằng CONCATENATE và & .

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)

Công thức CONCATENATE kết hợp các giá trị của hai ô.

Nối các chuỗi văn bản và giá trị ô:

Không có giới hạn nào rằng hàm CONCATENATE chỉ có thể nối các giá trị ô. 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 một cái gì đó đã được hoàn thành. Như thể hiện trong hàng thứ hai của hình sau. Lưu ý rằng chúng tôi đã thêm một khoảng trắng trước từ “hoàn thành” để phân tách các chuỗi văn bản được nối.

Tất nhiên, bạn có thể thêm một chuỗi văn bản ở đầu hoặc ở giữa công thức tổng hợp:

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

Thêm khoảng trắng (“”) giữa các giá trị được kết hợp. để kết quả hiển thị “Project1” thay vì “Project1”.

Nối các chuỗi văn bản và giá trị ô.

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

Để người dùng hiểu kết quả của một số công thức dễ dàng hơn. Bạn có thể nối kết quả với chuỗi văn bản mô tả ý nghĩa thực sự của các giá trị.

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

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

Nối các chuỗi văn bản và các giá trị tính toán công thức.

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

Điều này đảm bảo rằng công thức CONCATENATE của bạn luôn cho kết quả chính xác. Hãy nhớ những quy tắc đơn giản sau:

  • 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!.

Sử dụng “&” để nối các chuỗi trong Excel:

Trong Microsoft Excel, các toán tử & Đó là một cách khác để kết nối các ô. Phương pháp này hữu ích trong nhiều trường hợp. Vì gõ (&) nhanh hơn gõ phím. “nối” rất nhiều

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

“&” thí dụ.

Để xem sơ yếu lý lịch đang hoạt động, hãy viết lại công thức CONCATENATE được đề cập ở 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:

Sử dụng toán tử & để nối các chuỗi trong Excel.

“&” Và hàm CONCATENATE trong Excel

Nhiều người dùng muốn biết liệu có cách nào 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 cần thiết duy nhất giữa hàm CONCATENATE và “&” là hàm CONCATENATE có giới hạn là 255 trong khi hàm & thì không. Cũng không có sự khác biệt giữa hai phương pháp nối và không có sự khác biệt về cách nối. Độ chính xác giữa CONCATENATE công thức và công thức “&”

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

Kết nối các ô với dấu cách dấu phẩy và các nhân vật khác:

trong bảng tính của bạn Bạn có thể cần nhập các giá trị bằng dấu phẩy, dấu cách, dấu chấm câu. hoặc các ký tự khác (chẳng hạn như dấu gạch ngang hoặc dấu gạch chéo). Chỉ cần bao gồm các ký tự sẽ được sử dụng trong công thức kết nối. Hãy chắc chắn bao gồm ký tự này trong dấu ngoặc kép. Như một ví dụ bên dưới

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 ảnh dưới đây cho thấy kết quả như thế nào:

Kết nối các ô với dấu cách  dấu phẩy  và các nhân vật khác

Nối các chuỗi văn bản với các dòng mới.

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

Một vấn đề là bạn không thể nhập ký tự dòng mới làm ký tự bình thường trong công thức. Vì vậy, bạn cần hàm CHAR và mã ASCII tương ứng để nối các công thức:

  • 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 tôi có các phân đoạn địa chỉ trong các cột từ A đến F và chúng tôi sử dụng toán tử “&” trong cột G để kết nối chúng với nhau. Các giá trị kết hợp được phân tách bằng dấu phẩy (“,”), dấu cách (“”) và CHAR dòng mới (10):

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

Hàm CONCATENATE trong Excel

chú ý. Khi sử dụng ngắt dòng để phân tách các giá trị được nối, dấu “bọc “Giúp hiển thị kết quả một cách chính xác. Để thực hiện việc này, hãy nhấn Ctrl + 1 để mở hộp thoại. “Định dạng ô” chuyển sang tab Sau đó chọn hộp văn bản “Cắt”.

theo cùng một cách Bạn có thể trích xuất các chuỗi được nối với các ký tự khác, chẳng hạn 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.

Tuy nhiên, cách để chèn các ký tự có thể in vào công thức nối dễ dàng hơn là chỉ nhập dấu ngoặc kép trong ví dụ trước.

bằng mọi cách Tất cả bốn công thức dưới đây sẽ cho cùng một kết quả:

=A1 & CHAR(47) & B1

=A1 & “/” & B1

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

=CONCATENATE(A1, “/”, B1)

Kết nối các ô với các ký tự đặc biệt.

Cách nối các cột trong Excel:

Để nối hai hoặc nhiều cột trong Excel, hãy nhập công thức tổng hợp bình thường vào ô đầu tiên. Sau đó, sao chép nó sang một ô khác bằng cách kéo dấu cộng. (hinh vuông nhỏ xuất hiện ở góc dưới bên phải của cột) ô) được căn giữa. lựa chọn).

Ví dụ: để nối hai cột (cột A và cột B) với các giá trị được phân tách bằng dấu cách. Nhập công thức sau vào ô C2 và sao chép nó vào một ô khác. Khi kéo công thức đã sao chép xuống Con trỏ chuột trở thành một hình chữ thập. Như trong hình bên dưới:

Kết hợp hai cột trong Excel

lời khuyên. 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 .

Lưu ý cách Microsoft Excel xác định vị trí của các ô được sao chép sau khi bấm đúp vào ô chứa công thức. Nếu có ô trống trong bảng, ví dụ: ô A6 và B6 trống trong ví dụ này. Công thức chỉ được sao chép vào hàng thứ 5. Trong trường hợp này, bạn phải cuộn xuống theo cách thủ công để nối tất cả các cột.

Để kết nối một nhóm ô trong Excel:

Việc kết hợp các giá trị từ nhiều ô có thể yêu cầu nhiều công việc hơn. Điều này là do hàm CONCATENATE không chấp nhận mảng và yêu cầu tham chiếu ô duy nhất trong mỗi tham số.

Để kết nối nhiều ô, chẳng hạn như A1 với A4, bạn phải sử dụng một trong hai công thức sau.

Để 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 kết nối các nhịp nhỏ với nhau Nhập tất cả các ô được tham chiếu trong công thức không phải là vấn đề lớn. nhưng nhiều ô Và sẽ rất khó để nhập từng ô theo cách thủ công, dưới đây bạn sẽ tìm thấy 3 phương pháp so khớp nhanh trong Excel.

Cách 1: Nhấn CTRL để chọn nhiều ô tham gia.

Để nhanh chóng chọn nhiều ô Bạn có thể nhấn phím CTRL, sau đó bấm vào từng ô bạn muốn thêm vào 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.

Để kết nối một loạt ô, nhấn CTRL để chọn nhiều ô để kết nối.

Phương pháp thứ hai sử dụng hàm TRANSPOSE để lấy phạm vi.

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

  • 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.

Sử dụng hàm TRANSPOSE để kế thừa.

  • 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.

Sử dụng hàm CONCATENATE để kết hợp tất cả các giá trị.

chú ý. Bất kể bạn sử dụng phương pháp nào Các giá trị được nối trong C1 là các chuỗi văn bản. (Lưu ý rằng dữ liệu được căn trái trong ô.) Ngay cả khi mỗi giá trị ban đầu là một số. Vì 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 là gì

Kết hợp số và ngày ở nhiều đị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 tháng Tùy thuộc vào tập dữ liệu của bạn, bạn có thể muốn định dạng kết quả khác nhau. Để làm điều này Cho phép nhúng hàm TEXT vào công thức Excel.

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ẽ 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ố.

We will be happy to hear your thoughts

Leave a reply

Phần mềm FREE