Hàm trong Excel rất rộng và đa dạng, bởi vậy có những hàm mọi người chưa thực sự hiểu hết được. Ví dụ chính là hàm Concatenate. Bài viết này của Công Ty Quảng Cáo Marketing Online Limoseo sẽ hướng dẫn các bạn cách sử dụng hàm concatenate trong excel.
MỤC LỤC
1. Biểu thức hàm Concatenate
Hàm CONCATENATE (hoặc CONCAT) là một trong những hàm văn bản được sử dụng để kết nối các ký tự và văn bản khác nhau trong một ô.
Cú pháp hàm CONCAT/CONCATENATE:
=CONCATENATE(text1, text2, …)
text1, text2 là chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức.
Ghi chú:
- Tách text1, text2,… bằng dấu phẩy (,).
- Đối số văn bản phải bao gồm dấu ngoặc kép (“) ở đầu và cuối chuỗi mong muốn, nếu không hàm sẽ trả về #NAME. Bạn có thể nối tối đa 255 chuỗi tương ứng với 8.192 ký tự trong biểu thức CONCATENATE.
- Hàm CONCATENATE luôn trả về một chuỗi văn bản, ngay cả khi nó trông giống một số. CONCATENATE không hoạt động với mảng. Thay vào đó, các đối số nên được liệt kê riêng biệt, phân tách bằng dấu phẩy.
2. Cách sử dụng hàm concatenate trong excel
Dưới đây là một số cách sử dụng hàm concatenate trong excel phổ biến:
- Thêm từ và chữ cái.
- Kết hợp hàm TEXT để chèn ngày, tháng vào một chuỗi.
- Thêm khoảng trắng giữa họ và tên.
- Thêm số vào chuỗi.
2.1 Kết hợp dữ liệu từ hai cột trở lên
Để hợp nhất dữ liệu từ hai cột, hãy nhập công thức hợp nhất vào ô đầu tiên của cột mà bạn muốn truy xuất dữ liệu. Giả sử bạn muốn lấy cột Họ bằng cách nối dữ liệu trong cột Họ và Tên. Trong trường hợp cần có khoảng cách giữa họ và tên để phân tách các từ.
Tại ô C2 nhập: =CONCATENATE(A2,” “,B2) hoặc: =A2&” “&B2
Sau khi nhập công thức, nhấn Enter. Sau đó đặt con trỏ chuột ở góc dưới bên phải của ô có công thức đã chọn và kéo chuột đến các ô còn lại trong cột để sao chép công thức. Nhập và sao chép công thức nối cột trong ô đầu tiên.
2.2. Nối dữ liệu trong một mảng
Cách 1: Liệt kê/chọn từng đối số văn bản.
- Đầu tiên bạn nhập công thức cho hàm CONCATENATE, tìm đến mục liệt kê các đối số mà bạn muốn, đồng thời nhấn giữ phím ctrl và nhấp vào từng ô mà bạn muốn nối (chú ý thứ tự xuất hiện của các chữ cái này cho mình nhé).
- Sau khi được chọn, nhả phím Ctrl để đóng dấu ngoặc đơn biểu thức.
Cách 2: Dùng hàm TRANSPOSE kết hợp với phím F9, biến đổi trước rồi chuyển kết quả sang hàm CONCATENATE.
Lưu ý: Phương pháp này chỉ áp dụng nếu các ô chứa các chuỗi cần nối được sắp xếp theo các cột liên tiếp.
Hàm TRANSPOSE chuyển hàng thành cột và ngược lại. Ở đây, chúng ta chỉ đơn giản sử dụng hàm này để lấy công thức mảng, sử dụng phím F9 để thay thế công thức bằng giá trị được tính toán và cuối cùng nối chúng với CONCATENATE.
Biểu thức của hàm TRANSPOSE: =TRANSPOSE(mảng) trong đó mảng là mảng chứa các chuỗi cần nối. Làm như sau:
- Bước 1: Nhập công thức =TRANSPOSE(B1:B3) và nhấn F9 (thay công thức bằng giá trị tính toán).
- Bước 2: Sau khi nhấn F9, công thức hàm TRANSPOSE sẽ biến mất nhưng các chuỗi hiển thị trong các ô của mảng đã chọn được phân tách bằng dấu phẩy và nằm trong dấu ngoặc nhọn. Tiếp tục loại bỏ dấu ngoặc nhọn biểu thị công thức mảng để hiển thị các giá trị chứa trong các ô mà bạn muốn nối thành chuỗi. loại bỏ dấu ngoặc nhọn
- Bước 3: Giá trị thu được sau khi thực hiện các thao tác trên trở thành đối số văn bản của CONCATENATE. Lúc này, bạn đặt con trỏ trước giá trị đầu tiên trong biểu thức và tiếp tục gõ tên hàm CONCATENATE để sử dụng hàm này nối chuỗi như bình thường. Bạn có thể chỉnh sửa công thức thêm bớt ký tự để đạt được mục đích hiển thị mong muốn của mình.
2.3 Cách kết hợp chuỗi và dòng mới
Đôi khi bạn cần nối các chuỗi và định dạng chúng bằng các dòng mới để làm cho chúng dễ đọc hơn. Ví dụ các dữ liệu như tên, địa chỉ, số điện thoại (thường là thư từ).
Để hiển thị dòng mới, bạn cần có hàm CHAR (trả về ký tự được chỉ định bởi số mã) với mã ASCII thích hợp trong dữ liệu của bạn. Cú pháp hàm CHAR: =CHAR(số) trong đó số là một số nguyên từ 1 đến 255. Ở đây bạn phải nhớ mã ASCII cho dấu xuống dòng. Đặc biệt:
- Trên Windows, sử dụng CHAR(10). trong đó 10 là mã ASCII cho dòng mới.
- Trên Mac, sử dụng CHAR(13). trong đó 13 là mã ASCII cho dòng mới.
Lưu ý: Vào tab Home > Wrap Text (Excel 2016) hoặc nhấn tổ hợp phím Ctrl + 1, vào tab Wrap Text và click vào “Text Format” Wrap. Chọn văn bản được bọc
Khi nhập công thức, bạn có thể sử dụng hàm CONCATENATE hoặc các công thức có chứa các ký tự nối. Cả hai phương thức đều trả về cùng một kết quả nếu nhập đúng.
Ví dụ, để có được kết quả hiển thị bên dưới, bạn có thể sử dụng hai phương thức với hai biểu thức:
- Cách 1: Sử dụng hàm CONCATENATE. Nhập: =CONCATENATE(A2,CHAR(10),B2,”,” “,C2,CHAR(10),D2)
- Cách 2: Sử dụng công thức để nối chuỗi với “&”. Nhập: =A2&CHAR(10)&B2&”, “&C2&CHAR(10)&D2
Để tạo bảng tổng hợp, hãy nhập một trong hai công thức trên vào ô đầu tiên của mảng để lấy giá trị và nhấn Enter.
Bạn có thể tiếp tục điền vào các ô còn lại trong cột Giá trị bằng cách sử dụng tính năng Sao chép Công thức.
Nhập một biểu thức chuỗi và nhấn Enter để sao chép
Như vậy, Limoseo đã hướng dẫn sử dụng hàm concatenate trong excel tới các bạn.
3. Lỗi thường gặp khi sử dụng concatenate
3.1 Lỗi #NAME?
Nguyên nhân: Nếu đối số văn bản là chuỗi văn bản đặt trong dấu ngoặc kép (“”) thì hàm trả về lỗi #NAME?.
Ví dụ: nếu bạn nhập một ô chứa công thức =CONCATENATE(a,v), hàm sẽ ngay lập tức trả về lỗi #NAME?.
Sửa lỗi: thêm dấu ngoặc kép trước ký tự đầu tiên và sau ký tự cuối cùng của chuỗi văn bản trong đối số văn bản.
3.2 Văn bản hiển thị không như mong đợi
Nguyên nhân: Khi nối chuỗi kết quả hiển thị thường bị thiếu dấu cách, dấu chấm, dấu phẩy… Điều này có nghĩa là văn bản nối trông xấu. Điều này là do chúng tôi đã không thêm các phần tử như dấu cách, dấu chấm, dấu phẩy,… vào vị trí tương ứng của chúng trong công thức.
Khắc phục sự cố: Kiểm tra biểu thức và đưa các ký tự bắt buộc vào đối số văn bản của hàm.
Như vậy, thông qua bài viết trên, Công Ty Quảng Cáo Marketing Online Limoseo đã mang tới cho bạn đọc các thông tin hữu ích về Excel, đặc biệt là cách sử dụng hàm concatenate trong excel. Limoseo rất mong những chia sẻ trên sẽ có ích với các bạn, đừng quên like, share và follow để cập nhật những tin tức mới nhất về Excel nhé.
