Concatenate là gì? Cách dùng hàm Concatenate để nối chuỗi, nối ô trong Excel

Concatenate là khái niệm không mới trong các thủ thuật Excel thông thường. Vận dụng tốt chức năng của hàm này sẽ giúp chúng ta giải quyết rất nhiều tình huống khó khăn trong Excel cơ bản.

Làm thế nào để thực hiện nối văn bản ở nhiều ô vào thành 1 chuỗi văn bản trong 1 ô? Kỹ thuật nối văn bản cụ thể sẽ như thế nào để kết quả đúng như ý muốn? Đó là những thắc mắc của rất nhiều người trong quá trình tìm hiểu các hàm Excel. Trong bài viết này chúng ta cùng 123job.vn tìm hiểu về hàmCONCATENATE, một hàm nối chuỗi rất mạnh trong Excel nhé.

I. Hàm Concatenate là gì?

Về bản chất, 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 gộp các ô, nghĩa là thao tác hợp nhất hai hoặc nhiều ô vào một ô đơn lẻ. Kết quả là cuối cùng bạn sẽ 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ực hiện nối các ô trong Excel, bạn chỉ cần ghép nội dung của các ô đó. Nói cách khác, ghép các ô trong Excel là quá trình kết hợp hai hoặc nhiều giá trị cụ thể với nhau. Phương pháp sử dụng hàm nối chuỗi 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 ô Excel khác nhau (về mặt kỹ thuật, chúng được gọi là kỹ thuật text strings hoặc simply strings đơn giản) hoặc bạn có thể 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 những đặc điểm khác biệt cơ bản giữa hai phương pháp này:

Đặc điểm khác biệt cơ bản

Sử dụng các hàm Excelđể kết hợp ô là chủ đề của bài viết này, và trong hướng dẫn sau đây, chúng ta sẽ có hai cách giải quyết cụ thể và cực kỳ chi tiết để thực hiện nối các chuỗi trong Excel – bằng cách sử dụng hàm nối chuỗi hay hàmCONCATENATE và toán tử  &.

II. Phân biệt giữa Trộn ô với Nối chuỗi

Thông thường trong bảng tính Excel của chúng ta có hai cách để kết hợp dữ liệu:

• Hợp nhất các ô trong Microsoft Excel (hay còn gọi là trộn ô, gộp ô). Việc này là quá trình bạn sẽ biến nhiều ô riêng lẻ thành 1 ô duy nhất. Bạn có thể thực hiện điều này rất dễ dàng với chức năng Merge Cells.
• Kết nối các giá trị của ô (hay còn gọi là thao tác nối chuỗi). Việc này là bạn sẽ thực hiện nối giá trị (văn bản) tại nhiều ô thành 1 đoạn văn bản trên 1 ô duy nhất được xác định trong Excel bằng hàm. Bạn có thể thực hiện điều này với các hàm nối chuỗi trong Excel, đặc biệt là hàm CONCATENATE.

Hình sau đây thể hiện sự khác biệt quan trọng giữa hai phương pháp này:

Sự khác biệt

III. Hàm CONCATENATE để nối chuỗi, nối ô trong Excel

1. Ví dụ 1: Dùng hàm nối chuỗi để nối giá trị trong 2 cột họ và tên riêng lẻ thành 1 cột duy nhất

Dùng hàm nối chuỗi để nối giá trị trong 2 cột họ và tên riêng lẻ thành 1 cột duy nhất

Để làm được yêu cầu này bằng các hàm Excel, thông thường bạn sẽ nghĩ sử dụng hàm CONCAT (CONCATENATE) theo dạng cụ thể:

=CONCAT(A2,B2)

Tức là khi đó chúng ta chỉ dùng 2 ô A2 và B2 làm phần tham số cần nối. Nhưng như vậy kết quả trả về sẽ là:

Kết quả sai

Bạn sẽ thấy các đoạn text được hiển thị trong 2 ô A2 và B2 đã nối với nhau và cho ra kết quả khá không ổn như tại ô C2. Nhưng phần tên của các dữ liệu này lại viết sát với tên đệm (chỗ mũi tên màu xanh bên dưới đang chỉ vào). Điều này khiến cách viết Họ và tên của từng người chưa chính xác. 

Dấu cách trong Họ tên không tự động sinh ra khi bạn sử dụng hàm CONCAT (CONCATENATE). Muốn có nó, bạn phải trực tiếp nối thêm dấu đó vào, coi nó như 1 tham số trong hàm CONCATENATE cần nối. Lưu ý rằng phải đặt đúng vị trí ở giữa 2 ô A2, B2 như sau:

=CONCAT(A2," ",B2)

Lúc này kết quả trả về của hàm sẽ là:

Kết quả trả về của hàm

2. Ví dụ 2: Nối kết quả của các công thức tính

Trong một số trường hợp giá trị mà bạn cần thực hiện  nối lại là kết quả của 1 công thức tính, vậy điều này sẽ thể hiện ra sao khi chúng ta dùng hàm CONCAT - hàm CONCATENATE? Hãy cùng xem xét ví dụ được cho dưới đây:

Ví dụ 2: Nối kết quả của các công thức tính

Trong yêu cầu trên ảnh chúng ta có:

• Đầu tiên phải xác định ngày hết hạn của hợp đồng dựa vào Loại hợp đồng và Ngày ký.
• Sau đó sử dụng hàm nối chuỗi hay hàm CONCATENATE để nối kết quả ngày hết hạn với chữ Ngày ở trước

Các bước thực hiện yêu cầu cụ thể như sau:

Bước 1: Xác định ngày hết hạn của hợp đồng

Chúng ta có công thức để xác định ngày hết hạn là:

Ngày hết hạn của hợp đồng =DATE(Năm của ngày ký + thời hạn số năm, Tháng của ngày ký + thời hạn số tháng, Ngày ký + thời hạn số ngày)

Để có thể viết công thức này dễ hơn, bạn có thể tạo thêm 3 cột thể hiện lần lượt các giá trị số năm, số tháng, số ngày của các loại hợp đồng. Khi đó ta có kết quả được cho như hình sau:

Kết quả

Hàm sử dụng là các hàm DATE, hàm YEAR, hàm MONTH và hàm DAY:

• Hàm YEAR, hàm MONTH, hàm DAY để thực hiện tách ra các giá trị số năm, số tháng, số ngày từ giá trị ngày ký. Sau đó chúng ta thực hiện cộng thêm các thời hạn tương ứng theo Năm, tháng, ngày.
• Hàm DATE trong Excel sẽ tạo ra giá trị ngày tháng trong 1 ô dựa trên các tham số cụ thể Năm, tháng, ngày (viết đúng thứ tự)

Ô F2 cần nhập hàm 

=DATE(YEAR(E2)+B2, MONTH(E2)+C2, DAY(E2)+D2)

Bước 2: Sử dụng hàm CONCATENATE trong Excel để nối thêm ký tự Ngày vào kết quả:

=CONCATENATE("Ngày ", DATE(....))

Chữ Ngày và dấu cách hoàn toàn có thể thực hiện viết liền nhau và phải đặt trong cặp dấu nháy kép. Đây là nguyên tắc của các hàm Excelkhi bạn muốn viết trực tiếp một giá trị cụ thể ở dạng văn bản trong hàm.

Kết quả là:

Bước 2

Lúc này kết quả hàm DATE trong Excel lại trả về dưới dạng một con số chứ không phải là dạng ngày/tháng/năm. Bởi khi nối 1 hàm với 1 đoạn text, kết quả cuối cùng của các hàm Excel sẽ trả về là kết quả của hàm nhưng không kèm theo định dạng (Số 43634 là định dạng gốc, dạng General trong Excel của ngày 18/06/2019). Hàm nối chuỗi - hàm CONCATENATE cho ta kết quả là dạng text, do đó nó không thể định dạng các dữ liệu bằng Format Cells được.

Để có thể giữ được định dạng ngày tháng trong đoạn văn bản này bằng các hàm Excel, bạn phải sử dụng hàm TEXT kết hợp với hàm CONCATENATE như sau:

=CONCATENATE("Ngày ", TEXT(DATE(....),"dd/MM/yyyy"))

Kết quả hàm CONCATENATE thu được là:

Kết quả

IV. Những chú ý khi dùng hàm CONCATENATE/CONCAT

Khi sử dụng hàm nối chuỗi này, bạn hãy chú ý một số nội dung sau để giúp kết quả thu về được chính xác hơn:

• Hàm CONCATENATE sẽ yêu cầu ít nhất có một tham số: ít nhất là 1 ô hoặc 1 đoạn text cụ thể
• Bạn có thể ghép tối đa 255 tham số trong hàm CONCATENATE, tổng cộng là 8,192 ký tự.
• Kết quả của hàm CONCATENATE trả về sẽ luôn luôn là một chuỗi văn bản (bản chất là dạng Text, không phải dạng Số hay ngày tháng, cho dù ngay từ đầu bạn nhìn bằng mắt thường có thể chứa toàn các con số hoặc hiển thị như 1 giá trị của ngày)
• Để thể hiện kết quả cuối cùng của hàm CONCATENATE sau khi nối có định dạng Số hoặc Ngày thì bạn cần thực hiện nó kết hợp với hàm TEXT để thay đổi cách hiển thị.
• Hàm CONCATENATE không cho phép bạn có thể viết dạng mảng, vùng ô, mà phải chú ý viết từng ô riêng, từng đoạn text riêng trong các tham số của hàm. Ví dụ, bạn nên viết hàm là:

=CONCATENATE(A1, A2, A3) 

thay vì 

=CONCATENATE(A1:A3).

• Nếu ít nhất một trong các đối số quan trọng của hàm CONCATENATE không hợp lệ, công thức hàm sẽ trả về lỗi #VALUE!
• Hàm CONCATENATE hoặc hàm CONCAT trong Excel đều là một.

Để thực hiện nối một số ký tự đặc biệt, bạn có thể sử dụng hàm CHAR trong Excel với mã số tương ứng:

• 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 trong Excel có thể dễ dàng tìm thấy.

Ví dụ:

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

sẽ tương đương với hàm

=CONCATENATE(A1, "/", B1)

Những chú ý

V. Tải về file bài tập thực hành

File bài tập thực hành

VI. Kết luận

Qua bài viết này, bạn đã có thể tự tin sử dụng các hàm nối chuỗi hay hàm CONCATENATE trong Excelchưa? Hy vọng những chia sẻ trong bài viết này sẽ thực sự hữu ích trong công việc của bạn. Chúc bạn thành công!