Concatenate được ứng dụng khá nhiều trong Excel cho các thao tác nối và gộp chuỗi. Còn trong bài viết này thì Học Excel Online sẽ đề cập đến Hàm concatenate trong Google Sheet để giúp bạn dễ dàng giải quyết câu đố về nối.  

Tuy nhiên, tập dữ liệu của bạn lớn đến mức nào, bạn có thể gặp phải công việc kết hợp các bản ghi từ nhiều ô với nhau. Và tôi chắc chắn bạn sẽ muốn không chỉ giữ cho tất cả các giá trị không bị mất mà còn thêm một số dấu phẩy, dấu cách hoặc các ký tự khác, hoặc thậm chí tách các bản ghi đó với văn bản khác.  

Google Sheet cung cấp một số công cụ cho tác vụ này.

Hàm CONCAT của Google Sheet

Về cơ bản thì cú pháp và cách sử dụng của hàm concatenate trong google sheet cũng gần tương tự với hàm concatenate trong Excel.
Hàm CONCAT là phiên bản đơn giản hóa của hàm CONCATENATE: 

=CONCAT(value1, value2)

Để nối các ô, nó yêu cầu: 

• value1 – một bản ghi mà value2 phải được nối vào.  
• value2 – giá trị để tham gia.  

Để nhận một chuỗi từ 2 đơn vị văn bản hoặc số, công thức sẽ giống như bên dưới, với mỗi bản ghi trong dấu ngoặc kép: 

=CONCAT(“2019:”,”The Lion King”)

Trong thực tế, dữ liệu của bạn rất có thể đã nằm trong các ô. Bạn có thể tham chiếu trực tiếp các ô đó thay vì đặt từng số hoặc văn bản làm đối số. Vì vậy, công thức dữ liệu thực sẽ như thế này: 

=CONCAT(A2,B2)

Mẹo: Để sao chép công thức của bạn vào toàn bộ cột, hãy chọn ô có công thức và bấm đúp vào hình vuông nhỏ ở góc dưới cùng bên phải của ô. Toàn bộ cột sẽ được tự động điền vào công thức, cho đến cuối bảng.  

Như bạn có thể thấy, hàm này siêu đơn giản, nhưng nó có điểm yếu chính: 

• Nó chỉ cho phép 2 giá trị cùng một lúc. 

• Nó không thể kết hợp các vùng dữ liệu, chỉ các ô đơn lẻ.  

Nếu bạn cố gắng hợp nhất nhiều ô, bạn sẽ gặp lỗi hoặc chỉ có hai giá trị đầu tiên được kết hợp, như sau: 

=CONCAT(A2:A11,B2:B11)

CONCAT thay thế: toán tử ký hiệu và (&)

Google Sheet chứa các toán tử khác nhau cho các mục đích khác nhau. Concatenation không phải là một ngoại lệ. Sử dụng ký tự và (&) trong công thức thay vì hàm CONCAT sẽ cung cấp cho bạn kết quả tương tự: 

=A2&B2

Nhưng bạn ít biết rằng toán tử сoncatenation này linh hoạt hơn CONCAT. Đây là những gì nó có thể làm: 

1. Hợp nhất nhiều hơn hai giá trị cùng một lúc: 

=A2&B2&C2

1. Không chỉ hợp nhất một số ô trong Google Sheet mà còn tách chúng bằng các ký tự khác nhau: 

=A2&” “&B2&”; “&C2

Nếu bạn vẫn không nhận được kết quả mong muốn với các tùy chọn này, có một chức năng nữa bạn nên thử.

Cách sử dụng CONCATENATE trong Google Sheet

Tôi tin rằng hàm CONCATENATE của Google Sheet là hàm đầu tiên được sử dụng khi kết hợp nhiều bản ghi với nhau.

CONCATENATE văn bản và số trong Google Sheet 

Mẫu công thức bao gồm những thứ sau: 

=CONCATENATE(string1, [string2, …]) 

• string1 là chuỗi đầu tiên bạn muốn nối các giá trị khác vào. Tham số này là bắt buộc.  
• string2,… là viết tắt của tất cả các chuỗi khác mà bạn có thể muốn thêm vào chuỗi ban đầu. Tham số này là tùy chọn.  

Lưu ý: Bản ghi kết quả sẽ bao gồm các chuỗi theo thứ tự xuất hiện của chúng trong công thức. 

Nếu tôi điều chỉnh công thức với dữ liệu của mình, tôi sẽ nhận được điều này: 

=CONCATENATE(A2,B2,C2)

Hoặc, vì hàm chấp nhận vùng: 

= CONCATENATE (A2: D2)

Bạn có thể nhận thấy ngay ưu điểm đầu tiên của Google Sheets CONCATENATE: nó có thể dễ dàng kết hợp trên hai ô với cả văn bản và số.

Bảng tính Google: nối với dấu phân cách 

Kết hợp các ô trong Google Sheet là một nửa công việc. Nhưng để làm cho kết quả trông đẹp và dễ đọc, bạn nên thêm một số ký tự phụ.  

Nếu bạn đặt công thức như vậy, nó sẽ nối văn bản và gắn mọi thứ lại với nhau: BonnieJacksonCA, BonnieJacksonIN, v.v. Tin tốt là Google Sheets CONCATENATE cũng lấy các ký tự làm tham số.  

Do đó, để chia các chuỗi kết hợp bằng dấu phân cách, chỉ cần đề cập đến chúng trong công thức trong dấu ngoặc kép: 

=CONCATENATE(A2,” “,B2,”, “,C2) 

Ở đây tôi muốn tách A2 khỏi B2 bằng dấu cách và B2 từ C2  với dấu phẩy và dấu cách:

Bạn có thể tự do sử dụng hầu hết mọi ký tự trong hàm như thế này, nhưng ngắt dòng yêu cầu một cách tiếp cận khác.  

Mẹo: Trong trường hợp có các ô trống trong một số cột bạn đang hợp nhất, có một chức năng khác mà bạn có thể quan tâm. TEXTJOIN không chỉ hợp nhất các ô với văn bản mà còn bỏ qua khoảng trống: 

= TEXTJOIN (“”, TRUE, A2: C2)  

Đây là cách nó hoạt động: 

• Chỉ ra một dấu phân cách mong muốn làm tham số đầu tiên – dấu cách (“”) cho tôi.  
• Đặt TRUE làm tham số thứ hai để bỏ qua các ô trống hoặc FALSE để đưa chúng vào kết quả.  
• Nhập phạm vi để hợp nhất.

  

Nối văn bản bằng dấu ngắt dòng 

Mặc dù rõ ràng cách nhập hầu hết các dấu phân cách vào hàm, bạn không thể nhập dấu ngắt dòng theo cùng một cách ở đó. Nhưng may mắn thay, Google cho phép bạn làm việc với nhiều loại thẻ khác nhau.  

Có một chức năng giúp lấy các ký tự đặc biệt – nó được gọi là CHAR. Và bạn có thể biết rằng tất cả các ký tự đều có một vị trí trong bảng Unicode. Vì vậy, chỉ cần nạp số thứ tự của ký tự từ bảng đó vào hàm và hàm sau sẽ trả về chính ký tự đó. 

Đây là công thức để lấy dấu ngắt dòng: 

= CHAR (10) 

Thêm nó vào công thức nối để có các bản ghi được phân tách bằng dấu ngắt dòng: 

=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2,CHAR(10),D2)

Google Sheet: kết hợp ngày và giờ 

Nếu bạn cố gắng kết hợp ngày và giờ bằng một trong các phương pháp trên, chúng sẽ không hoạt động. Bảng tính của bạn sẽ trả về các bản ghi dưới dạng số đơn giản:

Để hợp nhất các ô này một cách chính xác trong Google Sheet, bạn cần sử dụng hàm 

=TEXT(number, format) 

• trong đó number là bất kỳ số, ngày hoặc giờ nào bạn muốn lấy ở định dạng mong muốn 
• và định dạng là mẫu bạn muốn áp dụng.  

Mẹo: Trong ví dụ của tôi, tôi sẽ sử dụng tham chiếu đến các ô có ngày và giờ, mặc dù bạn có thể tự do sử dụng trực tiếp các bản ghi trong công thức hoặc thậm chí hàm như DATE hoặc TIME. 

1. Tôi sử dụng công thức TEXT đầu tiên để thay đổi định dạng ngày từ 7/9/2019 đến 9/7/2019: 

=TEXT(B2,”D MMM YYYY”)

1. thứ hai trả về thời gian: 

=TEXT(C2,”HH:MM:SS”) 

1. Sử dụng những thứ này trong CONCATENATE, Google Sheet cho phép tôi kết hợp ngày và giờ ở định dạng mong muốn với các ký tự hoặc văn bản khác: 

=ARRAYFORMULA(A2:A11&” “&B2:B11&”; “&C2:C11)

Kết hợp các cột trong Google Sheet 

Với những điều chỉnh nhỏ, tất cả các cách tôi đã đề cập đều có thể hợp nhất các cột trong Google Sheet.  

Ví dụ 1: CONCAT của Google Sheet 

Để hợp nhất toàn bộ các cột trong Google Sheet với CONCAT, hãy chọn toàn bộ dải ô chứa kết quả (trong trường hợp của tôi là C2: C11) và chứa hàm trong ARRAYFORMULA: 

=ARRAYFORMULA(CONCAT(A2:A11,B2:B11))

Lưu ý: Nếu bạn muốn sử dụng hàm CONCATENATE, nó sẽ kết hợp tất cả các bản ghi trong một ô vì nó dễ dàng hợp nhất nhiều ô và vùng dữ liệu.  

Ví dụ 2. Toán tử nối 

Tạo công thức mảng để kết hợp toàn bộ cột với dấu và và thêm dấu phân tách cùng lúc: 

=ARRAYFORMULA(A2:A11&” “&B2:B11&”; “&C2:C11)

Nếu bạn có quá nhiều cột, việc liệt kê tất cả chúng có thể trở nên khó chịu, đặc biệt là nếu bạn vô tình bỏ qua / sao chép / trộn lẫn bất kỳ ký tự nào. 

Ngoài ra, nếu bạn quyết định thêm nhiều cột vào công thức sau đó, bạn sẽ phải chỉnh sửa từng phạm vi hiện có trong công thức theo cách thủ công. 

Ví dụ tiếp theo giải quyết những vấn đề này.  

Ví dụ 3: Google Sheets QUERY 

Hàm Google Sheets QUERY cũng thích hợp để hợp nhất một số cột trong Google Sheet. Hãy xem: 

=TRANSPOSE(QUERY(TRANSPOSE(A2:D10),,9^9))

Bạn có thể nghĩ rằng công thức kỳ lạ này nằm ngoài khả năng của bạn, nhưng hãy để tôi trình bày tất cả các phần của nó cho bạn: 

1. =TRANSPOSE(A2:D10) biến các hàng dữ liệu thành cột.  
2. =QUERY(TRANSPOSE(A2:D10),,9^9) hợp nhất các bản ghi trong mỗi cột với các ô trên cùng.  

Mẹo: Khi tôi đặt 9^9 vào công thức, tôi đảm bảo rằng tất cả các hàng từ tất cả các cột sẽ được kéo vào hàng đầu tiên như thể chúng là tiêu đề. Nó là 9^9 vì nó bao gồm tất cả các ô có thể có trong bảng tính (hãy nhớ giới hạn cho 5 triệu ô?) Và rất dễ nhớ.  🙂 

1. =TRANSPOSE(QUERY(TRANSPOSE(A2:D10),,9^9)) lấy hàng tiêu đề đó từ QUERY và biến nó thành một cột giống như cột mà tôi đã có. 

Các đặc quyền của Google Sheets QUERY đối với việc kết hợp các cột nằm ở những điều sau: 

• bạn không cần phải chọn toàn bộ cột như cách bạn làm đối với công thức mảng, 
• bạn không cần phải đề cập đến từng cột trong công thức trừ khi chúng không liền nhau. Trong trường hợp này, đây là cách công thức sẽ trông như sau: 

=TRANSPOSE(QUERY(TRANSPOSE({A2:A10,C2:C10,E2:E10,G2:G10}),,9^9))

Qua các ví dụ các bạn đã hiểu hơn về cách sử dụng của hàm concatenate trong google sheet chưa? Học Excel Online xin chúc bạn học tập tốt!