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, tài liệu không phải khi nào cũng được cấu trúc theo nhu yếu của bạn. Thường thì bạn hoàn toà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 – phối hợp tài liệu từ hai cột trở lên vào một cột đơn. Ví dụ phổ cập về việc phải ghép nối trong Excel là nhập tên và địa chỉ, tích hợp văn bản với giá trị được khuynh hướng bằng công thức, hiển thị ngày và giờ với định dạng mong ước, để đặt tên, ..
Tóm Tắt
“Concatenate” trong Excel là gì?
Về thực chất, có hai cách để tích hợp tài 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 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 khác nhau lại hoặc phối hợp những 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 thuần nhất để tích hợp những giá trị của ô A1 và B1 như sau :
=CONCATENATE(A1, B1)
Xin chú ý quan tâm rằng những 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 những giá trị với khoảng chừ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 hiển thị 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 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 hoàn toàn có thể sử dụng nó để nối những chuỗi văn bản khác nhau để làm cho tác dụng 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 việc làm nào đó đã triển khai xong, như trong hàng hai trong hình dưới đây. Xin chú ý quan tâm rằng tất cả chúng ta thêm một dấu cách trước chữ ” completed ” để tách những chuỗi văn bản được nối .
Đương nhiên, bạn hoàn toà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 những giá trị tích hợp, để tác dụng hiển thị như thể “ 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 hiệu quả trả lại bằng một số ít công thức dễ hiểu hơn cho người dùng, bạn hoàn toàn có thể nối nó với một chuỗi văn bản để lý giải giá trị thực sự là gì .
Ví dụ, bạn hoàn toà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
Để bảo vệ rằng những công thức CONCATENATE của bạn luôn mang lại hiệu quả đúng chuẩn, hãy nhớ những quy tắc đơn thuầ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 hoàn toàn có thể sử dụng “ và ” trong Excel để tích hợp những chuỗi văn bản khác nhau, giá trị của ô và tác dụng trả về bởi những hàm khác .
Ví dụ về “&”.
Để xem toán tử nối đang hoạt động giải trí, hãy viết lại những công thức CONCATENATE đã đàm đạo ở 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à hiệu quả của hàm TEXT / TODAY :
=”Today is ” & TEXT(TODAY(), “dd-mmm-yy”)
Như đã trình diễn trong ảnh chụp màn hình hiển thị bên dưới, hàm CONCATENATE và toán tử “ và ” trả tác dụng giống hệt nhau :
Xem thêm: Tổng hợp các thủ thuật Excel hay nhất mọi thời đạ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 suất cao hơn để nối những chuỗi trong hàm CONCATENATE hoặc toán tử “ và ” hay không .
Sự độc lạ duy nhất thiết yếu giữa hàm CONCATENATE và “ và ” là hàm CONCATENATE có số lượng giới hạn 255 còn và thì không có. Ngoài ra, không có gì khác nhau giữa hai giải pháp nối này, cũng không có sự độc lạ về hiệu lực thực thi hiện hành giữa những công thức CONCATENATE và “ và ” .
Vì 255 là cũng một số lượng thực sự lớn và trong trong thực tiễn, hoàn toàn có thể không khi nào dùng hết khi phối hợp nhiều chuỗi, sự độc lạ còn lại là sự tự do và tiện lợi hơn. Một số người dùng thấy công thức CONCATENATE dễ đọc hơn, cá thể tôi thích sử dụng giải pháp “ và ”. Vì vậy, chỉ cần dùng kỹ thuật nối mà bạn cảm thấy tự do 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 hoàn toàn có thể thường phải nhập những giá trị có dấu phẩy, dấu cách, dấu chấm câu hoặc những 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 gồm có 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 những 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 bộc lộ hiệu 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 những chuỗi văn bản được nối với những dấu chấm câu và dấu cách, như bộc lộ trong ví dụ trước. Tuy vậy, Trong 1 số ít trường hợp, hoàn toàn có thể cần phải tách những giá trị bằng một ngắt dòng. Một ví dụ thông dụng là phải hợp nhất địa chỉ gửi thư từ tài liệu trong những cột riêng không liên quan gì đến nhau .
Một yếu tố xảy ra là bạn không hề đơn thuần chỉ gõ một ngắt dòng trong công thức như một ký tự thông 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, tất cả chúng ta có những phần địa chỉ trong những cột A đến F, và tất cả 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 “ và ”. Các giá trị được hợp nhất được phân làn 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ựa như, bạn hoàn toàn 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 đủ các mã ASCII có thể dễ dàng tìm thấy.
Mặc dù thế, có một cách thuận tiện hơn để chèn những ký tự hoàn toàn 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ư tất cả chúng ta đã làm trong ví dụ trước .
Dù bằng cách nào, tổng thể bốn công thức dưới đây mang lại tác dụng 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 thông thường vào ô tiên phong 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 vắn nhỏ Open ở 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 những giá trị với khoảng chừng trắng, bạn nhập công thức sau vào ô C2, và sau đó sao chép nó xuống những ô khác. Khi bạn kéo xuống để sao chép công thức, con trỏ chuột sẽ biến hóa thành hình chữ thập, như bộc lộ 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 chú ý quan tâm cách Microsoft Excel xác lập sao chép những ô đến đâu sau khi nhấp đúp chuột ở những ô 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 toàn bộ những cột .
Nên xem : khóa học excel nâng cao TP.HN
Làm thế nào để nối một mảng ô trong Excel:
Kết hợp những giá trị từ nhiều ô hoàn toàn có thể mất nhiều công hơn vì hàm CONCATENATE không gật đầu những mảng và nhu yếu một tham chiếu ô duy nhất trong mỗi đối số .
Để ghép 1 số ít ô, 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 khoanh vùng phạm vi nhỏ, thì nhập toàn bộ những ô tham chiếu trong công thức cũng không phải yếu tố lớn. Nhưng với nhiều ô thì nhập từng ô theo cách thủ công bằng tay sẽ rất mất công. Dưới đây bạn sẽ tìm thấy 3 giải pháp ghép nối nhanh gọn trong Excel .
Phương pháp 1. Nhấn CTRL để chọn nhiều ô để nối
Để nhanh gọn chọn 1 số ít ô, bạn hoàn toà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à những bước chi tiết cụ thể :
- 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 to lớn gồm có hàng chục hoặc hàng trăm ô, chiêu thức trước đó không đủ nhanh vì nó yên cầu 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 đó sửa chữa thay thế nó bằng những tham chiếu ô riêng không liên quan gì đến nhau .
- 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ố ít hoặc ngày, bạn hoàn toàn có thể muốn định dạng tác dụng 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 những 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
Để 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
Source: https://final-blade.com
Category : Kiến thức Internet
