Hàm Nối Dữ Liệu Nhiều Ô Trong Excel

Trong trả lời này, bạn sẽ học những cách khác biệt để nối những chuỗi, các ô, những mảng, các cột và các hàng vào Excel bằng cách sử dụng hàm CONCATENATE với toán tử “&” .

Bạn đang xem: Hàm nối dữ liệu nhiều ô trong excel

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


Xem nhanh


Hàm CONCATENATE:Những điều cần nhớ khi thực hiện CONCATENATE vào ExcelLàm vậy nào để nối một mảng ô vào Excel:

“Concatenate” trong Excel là gì?

Về phiên bản chất, gồm hai cách để kết hợp tài liệu trong bảng tính Excel:

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

Khi bạn gộp các ô, tức là hợp độc nhất vô nhị hai hoặc các ô vào một trong những ô đối kháng lẻ. Kết trái là bạn có một ô lớn hơn được hiển thị trên nhiều hàng với / hoặc cột vào 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 phương pháp khác, ghép vào Excel là vượt trình phối hợp hai hoặc nhiều giá trị cùng với nhau. Phương pháp này hay được thực hiện để phối kết hợp một vài đoạn văn bạn dạng nằm trong số ô không giống nhau (về khía cạnh kỹ thuật, chúng được gọi là text strings hoặc simply strings đơn giản) hoặc chèn một quý giá được đo lường bằng phương pháp ở giữa một vài 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ủ thể của nội dung bài viết tiếp theo của chúng ta, với trong chỉ dẫn này, chúng ta sẽ tất cả hai cách giải quyết để nối những chuỗi vào Excel – bằng phương pháp sử dụng hàm CONCATENATE và toán tử  & .


*

*

Đăng ký ngay: khóa học Excel văn phòng 1 thầy 1 trò

Hàm CONCATENATE:

Hàm CONCATENATE trong Excel có phong cách thiết kế để nối những đoạn văn bạn dạng khác nhau lại hoặc phối hợp các giá bán trị từ một vài ô vào trong 1 ô.

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

CONCATENATE (text1, , …)

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

Dưới đấy là một vài ví dụ về phong thái sử dụng hàm CONCATENATE vào Excel.

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

Công thức CONCATENATE dễ dàng và đơn giản nhất để phối kết hợp các quý giá của ô A1 cùng B1 như sau:

=CONCATENATE(A1, B1)

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

Để bóc tách các giá chỉ trị với tầm trắng, hãy nhập “” vào đối số thiết bị hai, như trong loại 3 trong hình ảnh chụp screen dưới đây.

=CONCATENATE(A1, ” “, B1)

*

Chia sẻ tài liệu excel cơ bản hay nhất 2018

Kết nối chuỗi văn bạn dạng và quý hiếm di động:

Không có điều gì tinh giảm hàm CONCATENATE chỉ nối quý hiếm của ô. Bạn rất có thể sử dụng nó nhằm nối các chuỗi văn phiên bản khác nhau để gia công cho tác dụng có chân thành và ý nghĩa hơn. Ví dụ:

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

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

Đương nhiên, chúng ta cũng có thể thêm một chuỗi văn bản ngay từ trên đầu hoặc sinh sống giữa phương pháp Concatenate của bạn:

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

Một khoảng không (“”) được sản xuất giữa các giá trị kết hợp, để công dụng hiển thị như thể “Project1 ” chứ không hẳn là “Project1 “.

*

Kết nối một chuỗi văn bản và một quý hiếm từ trong phương pháp tính toán

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

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

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

 

*

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

Để đảm bảo rằng các công thức CONCATENATE của chúng ta luôn mang lại tác dụng chính xác, hãy nhớ các quy tắc dễ dàng sau đây:

CONCATENATE yêu thương cầu tối thiểu một “text” đối số để làm việc.Trong một bí quyết CONCATENATE, chúng ta cũng 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 toàn bộ các quý hiếm nguồn là những 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ụ, chúng ta nên viết =CONCATENATE(A1, A2, A3) nỗ lực vì =CONCATENATE(A1:A3).Nếu ít nhất một trong những đối số của hàm CONCATENATE không hợp lệ, phương pháp sẽ trả về lỗi #VALUE!.

Dùng “&” nhằm kết nối những chuỗi vào Excel:

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

Tương trường đoản cú với hàm CONCATENATE, chúng ta cũng có thể sử dụng “&” vào Excel để phối kết hợp các chuỗi văn bạn dạng khác nhau, giá trị của ô và hiệu quả trả về bởi những hàm khác.

Ví dụ về “&”.

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

Kết nối những giá trị vào A1 cùng B1:

=A1&B1

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

=A1&” “&B1

Kết nối những giá trị vào A1, B1 cùng ​​một chuỗi văn bản:

=A1 và B1 & ” completed”

Kết nối một chuỗi và tác dụng của hàm TEXT / TODAY:

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

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

*

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

Nhiều người tiêu dùng tự hỏi rằng liệu gồm cách hiệu quả hơn nhằm nối những chuỗi vào hàm CONCATENATE hoặc toán tử “&” xuất xắc không.

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

Vì 255 là cũng một số lượng thực sự phệ và vào thực tế, có thể không lúc nào dùng hết khi phối kết hợp nhiều chuỗi, sự khác hoàn toàn còn lại là sự thoải mái và tiện nghi hơn. Một số người tiêu dùng thấy phương pháp CONCATENATE đọc dễ hơn, cá thể tôi thích sử dụng phương pháp “&”. Vì vậy, chỉ việc dùng chuyên môn nối mà bạn cảm thấy dễ chịu hơn.

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

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

Xem thêm: Reviews 10 Thuốc Trị Đau Vai Gáy Của Mỹ Tốt Nhất Thị Trường 2021

Kết nối nhì ô cùng với một khoảng trắng:

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

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

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

Kết nối nhì ô với một vết nối:

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

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

*

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

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

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

Trên Windows, sử dụng CHAR (10) trong số ấy 10 là mã ASCII để xuống dòng .Trên khối hệ thống Mac, thực hiện CHAR (13) trong các số ấy 13 là mã ASCII .

Trong ví dụ như này, chúng ta có những phần add trong những cột A mang lại F, và bọn họ ghép bọn chúng lại cùng với nhau trong cột G bằng cách sử dụng toán tử nối “&”. Các quý giá được hợp tuyệt nhất được ngăn cách bằng vệt phẩy (“,”), dấu giải pháp (“”) và dấu xuống dòng CHAR (10):

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

*

Chú ý. Khi thực hiện 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ị thiết yếu xác. Để tiến hành 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ũng 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 cánh (/) – CHAR (47)Dấu sao (*) – CHAR (42)Danh sách không thiếu các mã ASCII có thể thuận tiện tìm thấy.

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

Dù bằng cách nào, toàn bộ bốn công thức sau đây mang lại hiệu quả giống hệt nhau:

=A1 và CHAR(47) và B1

=A1 và “/” & B1

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

=CONCATENATE(A1, “/”, B1)

*

Làm cố gắng nào nhằm nối những cột vào Excel:

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

Ví dụ, nhằm nối hai cột (cột A cùng B) tách các giá bán trị với khoảng trắng, các bạn nhập phương pháp sau vào ô C2, với sau đó xào nấu nó xuống các ô khác. Khi chúng ta kéo xuống để coppy công thức, con trỏ loài chuột sẽ chuyển đổ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 gấp rút để xào luộc công thức xuống các ô không giống trong cột là di chuột cho ô có công thức và nhấp đúp vào dấu cùng màu đen .

Xin xem xét cách Microsoft Excel xác định xào nấu các ô mang lại đâu sau khi nhấp đúp con chuột ở các ô có công thức của bạn. Nếu gồm có ô trống vào bảng của bạn, mang sử là ô A6 cùng B6 trống trong lấy ví dụ này, thì bí quyết sẽ chỉ được xào nấu đến hàng 5. Trong trường đúng theo này, các bạn sẽ cần buộc 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 cấp Hà Nội

Làm cầm cố nào nhằm nối một mảng ô vào Excel:

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

Để ghép một số ô, ví dụ như A1 cho tới A4, chúng ta cần một trong hai cách làm sau:

=CONCATENATE(A1, A2, A3, A4)

hoặc là

=A1 & A2 và A3 và A4

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

Phương pháp 1. Thừa nhận CTRL để chọn các ô để nối

Để cấp tốc chóng chọn 1 số ô, bạn có thể nhấn phím CTRL và nhấp vào cụ thể từng ô mà bạn có nhu cầu nối trong bí quyết CONCATENATE. Dưới phía trên 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 vào thanh công thức.Nhấn và giữ CTRL và nhấp vào cụ thể từng ô mà bạn có nhu cầu ghép lại.Nhả nút CTRL, gõ vệt ngoặc đóng trong thanh công thức và thừa nhận Enter.

*

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

Phương pháp 2. áp dụng hàm TRANSPOSE để đưa phạm vi

Khi bạn phải ghép một dải rộng lớn bao hàm hàng chục hoặc hàng trăm ô, phương thức trước đó không được nhanh bởi nó đòi hỏi phải nhấp vào từng ô. Trong trường đúng theo này, tất cả cách tốt hơn là áp dụng hàm TRANSPOSE nhằm trả về một mảng, và kế tiếp thay nạm nó bằng những tham chiếu ô riêng rẽ lẻ.

Chọn ô mà bạn có nhu cầu 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, dấn F9 để thay thế sửa chữa công thức với các giá trị được xem toán.Xoá dấu ngoặc nhọn phát triển thành một cách làm 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 phương pháp của bạn.

*

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

 

*

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

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

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

Hàm TEXT (value, format_text) gồm hai đối số:

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

Tôi đang chỉ nhắc chúng ta rằng khi phối hợp một chuỗi văn bạn dạng và ngày tháng , chúng ta phải thực hiện hàm TEXT nhằm hiển thị ngày làm việc định dạng hy vọng 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ụ như công thức bổ sung cập nhật nối tiếp một giá trị văn bạn dạng và số sau:

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

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

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

*

Để hoàn toàn có thể ứng dụng giỏi Excel vào trong công việc, bọn họ không chỉ nắm rõ được các hàm mà lại còn nên sử dụng tốt cả các công cầm cố của Excel. đông đảo 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ơ chế thường áp 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 hoàn toàn có thể học được trong số khóa học tập của Học Excel Online. Chúc bạn làm việc tốt