Nội dung chính
“Nối” thường có nghĩa là liên kết một cái gì đó với nhau trong một chuỗi hoặc một chuỗi. Thao tác này được sử dụng trong bảng tính Google bất cứ khi nào bạn cần tham gia các bản ghi từ nhiều ô. Bài viết này thu thập các giải pháp phổ biến và dễ dàng nhất để giúp bạn giải quyết câu đố ghép.
Tuy nhiên, dữ liệu của bạn rất lớn, bạn có thể bắt gặp nhiệm vụ kết hợp các bản ghi từ nhiều ô lại với nhau. Và tôi chắc chắn rằng 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 đó bằng văn bản khác.
Google Sheets cung cấp một số công cụ cho nhiệm vụ này.
Chức năng CONCAT của Google Sheets
Hàm CONCAT là phiên bản đơn giản hóa của CONCATENATE:
= CONCAT (value1, value2)
Để tham gia các tế bào, nó đòi hỏi:
- giá trị1 – một bản ghi mà value2 sẽ được thêm vào.
- giá trị2 – giá trị tham gia.
Để có được một chuỗi từ 2 đơn vị văn bản hoặc số, công thức sẽ như dưới đây, 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ể đã có trong các tế bào. Bạn có thể trực tiếp giới thiệu 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)
Tiền boa. Để sao chép công thức của bạn vào toàn bộ cột, chọn ô có công thức và nhấp đúp vào ô 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, chức năng này rất đơn giản, nhưng nó có chức năng chính điểm yếu:
- nó chỉ tham gia 2 giá trị tại một thời điểm.
- nó không thể kết hợp các phạm vi dữ liệu, chỉ các ô duy nhất. Nếu bạn cố gắng hợp nhất nhiều ô, bạn sẽ gặp lỗi hoặc chỉ hai giá trị đầu tiên sẽ được nối, như thế này:
=CONCAT(A2:A11,B2:B11)
CONCAT thay thế: ampersand toán tử ghép (&)
Google Sheets chứa các nhà khai thác khác nhau cho các mục đích khác nhau. Sự kết hợp không phải là một ngoại lệ. Sử dụng ký tự dấu và (&) trong công thức thay cho hàm CONCAT sẽ cung cấp cho bạn kết quả tương tự:
=A2&B2
Nhưng ít bạn có biết rằng toán tử tạo hình này linh hoạt hơn CONCAT. Đây là những gì nó có thể làm:
- Hợp nhất nhiều hơn hai giá trị cùng một lúc:
=A2&B2&C2 - Không chỉ hợp nhất một số ô trong Google Sheets mà còn phân tách chúng bằng nhiều 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ó thêm một chức năng bạn nên thử.
Cách sử dụng CONCATENATE trong Google Sheets
Tôi tin rằng chức năng CONCATENATE của Google Sheets là chức năng đầu tiên được sử dụng khi nối nhiều bản ghi lại với nhau.
CONCATENATE văn bản và số trong Google Sheets
Mẫu công thức bao gồm:
= CONCATENATE (chuỗi1, [string2, …])
- chuỗi1 là chuỗi đầu tiên bạn muốn nối các giá trị khác vào. Lập luận này là bắt buộc.
- chuỗi2, đào là viết tắt của tất cả các chuỗi khác mà bạn có thể muốn nối vào chuỗi ban đầu. Đối số này là tùy chọn.
Ghi chú. 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 phạm vi:
=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 tham gia qua hai ô có cả văn bản và số.
Bảng tính Google: nối với các dấu phân cách
Kết hợp các ô trong Google Sheets 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ư hiện tại, nó sẽ ghép văn bản và dá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 đối số.
Do đó, để phân chia các chuỗi kết hợp với 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 bằng 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 đòi hỏi một cách tiếp cận khác.
Nối văn bản với ngắt dòng
Mặc dù rõ ràng là làm thế nào để nhập hầu hết các dấu phân cách vào hàm, bạn không thể gõ một dòng ngắt giống như vậy. Nhưng may mắn thay, Google cho phép bạn chơi nhiều thẻ khác nhau.
Có một chức năng giúp có được các ký tự đặc biệt – đó 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 đưa số thứ tự của ký tự từ bảng đó vào hàm và cái sau sẽ trả về chính ký tự đó.
Đây là một công thức để có được 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 Sheets: kết hợp ngày và giờ
Nếu bạn cố gắng kết hợp ngày và thời gian 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 Sheets, bạn cần sử dụng chức năng TEXT:
= Văn bản (số, định dạng)
- Ở đâu con số là bất kỳ số, ngày hoặc thời gian nào bạn muốn có ở định dạng mong muốn
- và định dạng là mẫu bạn muốn áp dụng.
Tiền boa. Trong ví dụ của tôi, tôi sẽ sử dụng các tham chiếu đến các ô có ngày và thời gian, mặc dù bạn có thể tự do sử dụng các bản ghi trực tiếp trong công thức hoặc thậm chí là hàm như DATE hoặc TIME.
- Tôi sử dụng công thức văn bản đầu tiên để thay đổi định dạng ngày từ 7/9/2019 đến 9 tháng 7 năm 2019:
=TEXT(B2,"D MMM YYYY") - Văn bản thứ hai trả về thời gian:
=TEXT(C2,"HH:MM:SS") - Sử dụng những điều này trong CONCATENATE, Google Sheets cho phép tôi kết hợp ngày và giờ theo định dạng mong muốn với các ký tự hoặc văn bản khác:
=CONCATENATE(TEXT(B2,"D MMM YYYY"),", ",TEXT(C2,"HH:MM:SS"))
Kết hợp các cột trong Google Sheets
Với những điều chỉnh nhỏ, tất cả các cách tôi đã đề cập đều có khả năng hợp nhất các cột trong Google Sheets.
Ví dụ 1. CONCAT Google Sheets
Để hợp nhất toàn bộ các cột trong Google Sheets với CONCAT, hãy chọn toàn bộ phạm vi sẽ chứa kết quả (C2: C11 trong trường hợp của tôi) và bọc hàm trong ARRAYFORMULA:
=ARRAYFORMULA(CONCAT(A2:A11,B2:B11))
Ghi chú. Nếu bạn muốn sử dụng hàm CONCATENATE, nó sẽ tham gia tất cả các bản ghi trong một ô vì nó dễ dàng hợp nhất nhiều ô và phạm vi dữ liệu.
Ví dụ 2. Toán tử ghép
Tạo các công thức mảng để kết hợp toàn bộ các cột với ký hiệu và thêm dấu phân cách cùng một 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ở thành một nỗi đau ở cổ, đặc biệt nếu bạn vô tình bỏ qua / sao chép / trộ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
Chức năng Google Sheets QUERY cũng phù hợp để hợp nhất một số cột trong Google Sheets. Có một cái nhìn:
=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 đặt tất cả các phần của nó ra cho bạn:
- = TRANSPOSE (A2: D10) biến các hàng dữ liệu thành các cột.
- = QUERY (TRANSPOSE (A2: D10) ,, 9 ^ 9) hợp nhất các bản ghi trong mỗi cột vào các ô trên cùng.
Tiền boa. Khi tôi đặt 9 ^ 9 vào công thức, tôi đảm bảo 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à các 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 Các tế bào 5M?) và rất dễ nhớ. 🙂
- = 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 tôi đã có.
Các đặc quyền của Google Sheets QUERY để kết hợp các cột nằm trong các sự kiện sau:
- bạn không cần phải chọn toàn bộ cột như bạn làm cho các công thức mảng
- bạn không cần đề cập đến từng cột trong công thức trừ khi chúng không liền kề. Trong trường hợp này, đây là công thức sẽ như thế nào:
=TRANSPOSE(QUERY(TRANSPOSE({A2:A10,C2:C10,E2:E10,G2:G10}),,9^9))
Nối và thêm văn bản theo vị trí
Bạn đã biết rằng bạn có thể thêm văn bản, số và ký tự bị thiếu vào chuỗi của mình bằng hàm CONCATENATE.
Nhưng nếu có quá nhiều hồ sơ để tham gia, bất kỳ nhân vật phụ nào cũng có thể mở rộng cách thức công thức của bạn vượt ra ngoài những gì bạn đã lên kế hoạch. Trong những trường hợp như thế này, tốt hơn là hợp nhất các ô như chúng hoặc sử dụng các dấu phân cách đơn giản như khoảng trắng – và thêm văn bản sau đó. Một công cụ đặc biệt của chúng tôi sẽ giúp bạn.
Thêm văn bản theo vị trí chèn bất kỳ ký tự và chuỗi nào vào các ô dựa trên vị trí bạn chỉ định, không cần công thức. Hãy để tôi chỉ cho bạn cách nó hoạt động.
Trong ví dụ trước, QUERY kết hợp tên và số điện thoại cho tôi. Nhưng tôi muốn thêm chữ viết tắt quốc gia: (Hoa Kỳ / CA) trước số điện thoại bắt đầu bằng +1 và Anh trước +44:
Tách các ô trong Google Sheets
Khi quá trình nối diễn ra, thường phải chia các ô đã hợp nhất lại. Có ba cách để làm điều đó trong Google Sheets:
- Xây dựng chức năng SPLIT của Google Sheets.
- Sử dụng dụng cụ bảng tính tiêu chuẩn – Tách văn bản thành các cột.
- Hoặc dùng thử phiên bản nâng cao của công cụ tích hợp – Tách văn bản thành các cột cho Google Sheets:
Nó cho phép bạn phân chia các ô bằng bất kỳ dấu phân cách hoặc thậm chí các bộ phân tách, coi chúng là một và bao gồm các liên từ nếu cần thiết. Nó cũng cung cấp tùy chọn phân chia các ô trong Google Sheets theo vị trí hoặc tách tên và họ.
Cách hợp nhất các ô trong Google Sheets mà không cần công thức
Nếu nắm vững các công thức khác nhau không phải là một phần trong kế hoạch của bạn, bạn sẽ được hưởng lợi từ chúng tôi Hợp nhất giá trị bổ trợ. Bổ trợ nhanh chóng kết hợp các bản ghi trong các hàng, cột hoặc toàn bộ phạm vi ô. Các tùy chọn của nó rất rõ ràng và tất cả những gì bạn cần làm là chọn phạm vi và quyết định xem kết quả sẽ như thế nào.
- Bạn có thể chọn cột kết hợp trong Google Sheets, phân tách chúng bằng dấu phẩy và dấu cách và đặt kết quả ở bên phải của bản ghi gốc:
- Hoặc là hợp nhất các hàng trong Google Sheets, phân chia các bản ghi với dấu ngắt dòng và xóa nội dung của các ô được chọn:
- Hoặc chọn phạm vi và có tất cả các tế bào hợp nhất thành một hoàn toàn:
Nếu bạn đã quan tâm đến công cụ này, bạn có thể xem qua mọi thứ nó làm trang đặc biệt này.
- Có thêm một tiện ích chúng tôi cung cấp để ghép nối trong Google Sheets – Kết hợp các hàng trùng lặp. Một mặt, nó hợp nhất các ô từ các hàng khác nhau có cùng bản ghi khóa. Mặt khác, nó ghép các số nằm rải rác trên bảng của bạn nhưng vẫn thuộc cùng một bản ghi:
Tôi hy vọng bây giờ bạn đã quyết định cách nào phù hợp nhất với trường hợp của bạn. Nếu bạn có bất kỳ phương pháp nào khác trong tâm trí, xin vui lòng chia sẻ với chúng tôi trong phần bình luận bên dưới. 🙂
Chat với tư vấn viên
Gọi ngay