Một lượng lớn dữ liệu trong Excel được sắp xếp thành nhiều cột để sắp xếp và phân loại. Đến với dữ liệu cá nhân, thường thì bạn sẽ thấy rằng tên của mọi người cũng được chia thành hai [hoặc nhiều] cột, giữ riêng biệt tên và họ. Mặc dù điều này giúp sắp xếp và lọc, thường được thực hiện với họ, nhưng hướng dẫn này dành cho những lúc bạn cần kết hợp họ và tên thành một tên đầy đủ
Chúng tôi có một số phương pháp và mẹo đơn giản mà không cần phải gõ thủ công tất cả các tên đầy đủ [phew. ]. Chúng ta sẽ để công việc văn thư cho Excel và xem qua các công thức, hàm và mẹo sẽ nối văn bản của hai hoặc nhiều ô. Hãy xem cách thêm Chuột vào Minnie và Vịt vào Donald
Mục lục
Kết hợp Họ và Tên
Với mục đích kết hợp họ và tên hoặc kết hợp nội dung của hai cột, chúng tôi sẽ sử dụng ví dụ sau làm cơ sở cho hướng dẫn này
Chúng tôi sẽ điền vào cột E, kết hợp nội dung của cột B và C, sử dụng một số phương pháp khác nhau là công thức, hàm và tính năng. Hãy bắt đầu nào
Sử dụng Toán tử '&'
Toán tử '&' còn được gọi là toán tử nối. Chúng tôi sẽ hợp nhất tên và họ trong ví dụ của chúng tôi bằng cách sử dụng toán tử '&' trong công thức như vậy
=B3&" "&C3
Những gì chúng tôi đang làm ở đây là lấy tên từ ô B3 và họ từ ô C3. Chúng tôi đang nối chúng với "&" nhưng nếu chúng tôi chỉ nối chúng theo cách này =B3&C3 hoặc thậm chí theo cách này =B3 & C3, chúng tôi sẽ có một tên đầy đủ không có ký tự khoảng trắng ở giữa e. g. kết quả của công thức này sẽ là AbbeyBrooks
Để đảm bảo rằng có một ký tự khoảng trắng trong tên kết hợp, chúng tôi tách toán tử '&' với B3 và C3 và thêm một ký tự khoảng trắng có dấu ngoặc kép ở giữa. Đây là kết quả của việc áp dụng và kéo công thức này từ E3 sang E12
Có vẻ ổn với chúng tôi. Nhiều hơn hai chuỗi văn bản cũng sẽ được kết hợp theo cùng một cách e. g. , nếu cũng có tên đệm, chúng tôi sẽ thêm nó theo cách tương tự với toán tử & và ký tự khoảng trắng
Chúng ta vừa xem cách kết hợp tên với công thức. Bây giờ hãy xem việc sử dụng các chức năng
Sử dụng Hàm CONCAT
Hàm CONCAT kết hợp một danh sách hoặc phạm vi chuỗi văn bản. Nó hoàn toàn phù hợp với công việc này, vì vậy hãy để nó hoạt động. Công thức chúng tôi sẽ sử dụng để kết hợp tên trong ví dụ trường hợp của chúng tôi là
=CONCAT[B3," ",C3]
Hãy đọc cái này. Chúng tôi có các ô mục tiêu được cung cấp cho chức năng là B3 và C3. Nếu chúng ta chỉ cung cấp các ô trong hàm, kết quả là chúng ta sẽ có AbbeyBrooks. Đó là lý do tại sao chúng ta cần nhập một ký tự khoảng trắng trong dấu ngoặc kép để có kết quả như thế này
Thành công. Nhiều hơn hai chuỗi văn bản cũng sẽ được kết hợp theo cùng một cách e. g. , nếu cũng có tên đệm, chúng ta sẽ thêm nó theo cách tương tự bằng cách tham chiếu đến ô có tên đệm và thêm ký tự khoảng trắng. Nhưng CONCAT cũng có thể được sử dụng theo cách khác vì nó chấp nhận phạm vi
Tiếp theo, chúng ta sẽ xem xét hàm CONCATENATE nhưng sau khi giới thiệu CONCAT trong Excel 2019, Microsoft khuyên chúng ta nên sử dụng hàm CONCAT. Microsoft dự định CONCAT sẽ thay thế CONCATENATE [có lẽ xem xét độ dài đáng sợ của tên?] Bởi vì CONCAT cũng hoạt động cho các phạm vi trong khi CONCATENATE thì không. Chấp nhận phạm vi là một điểm cộng về hàm CONCAT và chúng ta sẽ nói nhiều hơn về nó ở cuối hướng dẫn này
Sử dụng hàm CONCATENATE
Hàm CONCATENATE nối nhiều chuỗi văn bản thành một chuỗi văn bản. Đối với cách chúng ta đang sử dụng hàm CONCAT trong phần lớn hướng dẫn này, hàm CONCATENATE có thể thay thế chính xác. Hãy để chúng tôi chứng minh bằng ví dụ trường hợp của chúng tôi
Chúng tôi đã sử dụng CONCAT ở trên như =CONCAT[B3," ",C3] và điều duy nhất chúng tôi sẽ thay đổi trong công thức này là tên của hàm từ CONCAT thành CONCATENATE
=CONCATENATE[B3," ",C3]
Bạn sẽ thấy kết quả không khác gì những gì chúng tôi có trong phân đoạn trước với CONCAT
Nhiều hơn hai chuỗi văn bản cũng sẽ được kết hợp theo cùng một cách e. g. , nếu cũng có tên đệm, chúng ta sẽ thêm nó theo cách tương tự bằng cách tham chiếu đến ô có tên đệm và thêm ký tự khoảng trắng
Vậy tại sao CONCATENATE cũ tốt được thay thế bằng CONCAT khi cả hai đều làm điều tương tự? . Thứ hai, nó không hoàn toàn làm những gì CONCAT làm; . Cho đến bây giờ, đây dường như không phải là vấn đề vì chúng tôi đã nhập từng ô riêng lẻ để chèn ký tự khoảng trắng vào giữa. Nhưng nếu bạn cuộn đến cuối, chúng tôi sẽ chỉ cho bạn cách sử dụng CONCAT cho các dãy
Sử dụng Hàm TEXTJOIN
Hàm TEXTJOIN tham gia một danh sách hoặc phạm vi chuỗi văn bản bằng dấu phân cách. Dấu phân cách là dấu phân cách giữa các văn bản [ký tự khoảng trắng, dấu phẩy, v.v. ]. Hàm này hoạt động giống như hàm CONCAT nhưng chấp nhận dấu phân cách làm đối số trong hàm. Hãy xem điều này với ví dụ trường hợp của chúng tôi
Đây là công thức chúng tôi đã sử dụng
=TEXTJOIN[" ",TRUE,B3,C3]
Đối số đầu tiên là dấu phân cách. Vì chúng tôi muốn phân tách các tên bằng một ký tự khoảng trắng ở giữa, chúng tôi đã thêm một ký tự khoảng trắng được đặt trong dấu ngoặc kép làm dấu phân cách. "TRUE" [hoặc "1"] là xác nhận bỏ qua các ô trống. B3 và C3 là các văn bản [tên trong trường hợp của chúng tôi] mà chúng tôi muốn tham gia và sau đây là kết quả
Để nối một cặp văn bản, cả 3 chức năng được nêu chi tiết ở trên đều hoạt động tương tự nhau nên việc chọn một chức năng sẽ tùy thuộc vào sở thích của bạn
đề xuất đọc. Cách nối trong Excel
Sử dụng tính năng Flash Fill của Excel
Excel sẽ tự động điền dữ liệu nếu nó phát hiện thấy một mẫu trong dữ liệu được điền thủ công thông qua Flash Fill. Tính năng sao chép này chọn một mẫu vào lần thứ hai dữ liệu được nhập. Khi bắt đầu hướng dẫn này, chúng tôi biết rằng chúng tôi đã nói không với việc nhập thủ công nhưng phương pháp này đã được chúng tôi ưa thích vì lý do chính đáng. Xem Flash Fill có gì hay ho, áp dụng vào ví dụ
Không có công thức, không có chức năng. Chỉ cần gõ
- Chọn ô D3. Nhập tên đầy đủ "Abbey Brooks". nhấn nút Enter. Lựa chọn tự động chuyển sang ô tiếp theo trong cột
- Bắt đầu nhập "Anika". Lúc này, Flash Fill sẽ nhận ra mẫu của bạn và sẽ hiển thị gợi ý
- Nhấn Enter. Đề xuất sẽ được xác nhận bằng cách nhấn Enter, điền vào toàn bộ cột bằng mẫu được đề xuất
Lưu ý cách tính năng Flash Fill rất tiện lợi và chính xác theo mẫu được cung cấp. Tại đây, bạn sẽ không phải nghĩ đến việc điều chỉnh các điều kiện và định dạng trong một công thức và có thể để Flash Fill làm theo sau bạn. Trong khi bạn lưu ý điều này, cũng lưu ý một số phát hiện thú vị của chúng tôi về Flash Fill
đề xuất đọc. Tính năng Excel Flash Fill – Mọi thứ bạn cần biết
Một số đặc điểm của Flash Fill
- Flash Fill sẽ không hoạt động nếu có bất kỳ cột trống nào trong tập dữ liệu. Các cột phải được căn chỉnh với nhau để Flash Fill phát hiện mẫu
- Nếu bạn không thể làm cho Flash Fill hoạt động, hãy kiểm tra cài đặt trong tab Tệp > Tùy chọn ở bảng điều khiển bên trái > Nâng cao ở ngăn bên trái của hộp thoại Tùy chọn Excel đã khởi chạy > Tùy chọn chỉnh sửa. Đảm bảo rằng hộp kiểm Tự động Flash Điền được đánh dấu
- Flash Fill có thể được thêm thủ công từ nút Flash Fill trong menu ribbon
- Nhập tên thủ công, nhấn Enter. Chuyển đến tab Dữ liệu > phần Công cụ dữ liệu > nút Flash Fill. Mặc dù điều này cũng sẽ hiệu quả, nhưng bạn sẽ bỏ lỡ sự hài lòng khi nhìn thấy sự xuất hiện kỳ diệu của Flash Fill
- Flash Fill chỉ kích hoạt khi gõ lần điền thứ hai. Nếu bạn thực hiện bất kỳ tác vụ nào khác sau khi nhập phần tô đầu tiên, Flash Fill sẽ không kích hoạt trong khi nhập phần tô tiếp theo; . Tuy nhiên, đây không phải là vấn đề đáng lo ngại vì bạn luôn có thể nhập hai lần điền khác để kích hoạt Flash Fill hoặc bạn có thể chọn Flash Fill theo cách thủ công từ menu ribbon
- Lưu ý trường hợp câu trong tập dữ liệu của ví dụ của chúng tôi. Một số chữ cái đầu của tên được viết thường và một số viết hoa. Đối với trường hợp đầu tiên, tên Abbey có chữ cái đầu viết hoa và họ brooks có chữ cái đầu viết thường. Khi chúng tôi nhập tên đầy đủ Abbey Brooks, Flash Fill đã chọn mẫu này
- Tên – nhập nguyên trạng, không thay đổi
- Thêm ký tự khoảng trắng sau Tên
- Viết hoa chữ đầu của Họ
Do đó, chúng ta có thể thấy trong kết quả rằng Flash Fill đã viết hoa chữ cái đầu của tất cả các họ mặc dù có một số họ trong tập dữ liệu không có chữ cái đầu viết hoa. Điều này là do mô hình được phát hiện bởi Flash Fill
Đối với các tên đầu tiên, Abbey đã có chữ cái đầu viết hoa trong tập dữ liệu và Flash Fill đã phát hiện ra rằng không có thay đổi nào được thực hiện trước đối với các tên đầu tiên. Đó là lý do tại sao những cái tên đầu tiên đã được Flash Fill sao chép như chúng vốn có.
Nếu có bất kỳ sự chênh lệch nào như vậy trong tập dữ liệu của bạn, một mẹo hữu ích ở đây là thay đổi hàng đầu tiên trong tập dữ liệu để phù hợp với tất cả các thiếu sót [nếu có thể] có trong tập dữ liệu
Ví dụ của chúng ta, chúng ta nên thay đổi Abbey trong ô B3 thành abbey. Điều này sẽ khiến Flash Fill chọn mẫu này
- Viết hoa chữ cái đầu của Tên
- Thêm ký tự khoảng trắng sau Tên
- Viết hoa chữ cái đầu tiên của Họ
Bây giờ Flash Fill cũng sẽ viết hoa các chữ cái đầu tiên của tên
Điều này hoàn thành công việc nhưng mất rất nhiều công việc thủ công. Hãy xem làm thế nào để làm điều này nhanh hơn nhiều
Viết hoa ký tự đầu tiên của họ và tên
Một cách cực kỳ dễ dàng để tham gia viết hoa các chữ cái đầu tiên là hàm PROPER có thể được áp dụng cho tập dữ liệu hoặc cột kết quả. Sẽ là một ý tưởng hay nếu áp dụng hàm PROPER cùng với các hàm nối mà chúng ta đã thấy trong các phần trên
Các công thức cuối cùng sẽ là
=PROPER[B3&" "&C3] //Using & operator
=PROPER[CONCAT[B3," ",C3]] //Using CONCAT function
=PROPER[CONCATENATE[B3," ",C3]] //Using CONCATENATE function
=PROPER[TEXTJOIN[" ",TRUE,B3,C3]] //Using TEXTJOIN function
Bây giờ kết quả có vẻ tốt hơn nhiều
Cho đến bây giờ, chúng ta đã biết cách kết hợp họ và tên theo định dạng "FirstName LastName". Hãy đi vào kết hợp các tên ở các định dạng khác nhau
Kết hợp Họ, Tên
Bây giờ chúng ta sẽ tiến hành kết hợp họ và tên theo định dạng này. "Họ, Tên" [có dấu phẩy] từ cùng một tập dữ liệu được sử dụng trong ví dụ trường hợp của chúng tôi
Các công thức và hàm được sử dụng trong các phân đoạn trên sẽ được sử dụng theo cách tương tự. Chúng tôi sẽ chỉ chuyển đổi vị trí của các tham chiếu ô [họ được chọn trước, tên được chọn cuối cùng] và thêm dấu phẩy trước ký tự khoảng trắng
Sử dụng toán tử '&'
Công thức chúng ta sẽ áp dụng bằng toán tử '&'
=C3&", "&B3
Ở đây chúng tôi đã chọn họ trước và sau đó là tên. Chúng tôi đã nối chúng với toán tử '&' và dấu phân cách giữa các tên là dấu phẩy và ký tự khoảng trắng ", ". Kết quả của việc áp dụng công thức này là dưới đây
Sử dụng chức năng CONCAT
Công thức chúng ta sẽ sử dụng để áp dụng hàm CONCAT
=CONCAT[C3,", ",B3]
Ở đây chúng tôi đã chọn họ trước và sau đó là tên. Dấu phân cách giữa các tên là dấu phẩy và ký tự khoảng trắng ", ". Dưới đây là kết quả của việc áp dụng hàm CONCAT
Sử dụng hàm CONCATENATE
Công thức chúng ta sẽ sử dụng để áp dụng hàm CONCATENATE
=CONCATENATE[C3,", ",B3]
Ở đây chúng tôi đã chọn họ trước và sau đó là tên. Dấu phân cách giữa các tên là dấu phẩy và ký tự khoảng trắng ", ". Dưới đây là kết quả của việc áp dụng hàm CONCATENATE
Sử dụng tính năng Flash Fill
- Nhập thủ công điền đầu tiên là "Brooks, Abbey"
- nhấn nút Enter
- Bắt đầu nhập phần điền thứ hai, đề xuất Flash Fill sẽ xuất hiện
- Nhấn Enter để lấp đầy phần còn lại của các tên
Tất cả các phương pháp này đã được thảo luận chi tiết hơn trong các phần trên.
Kết hợp tên, đệm và họ
Như chúng tôi đã giải thích trong các phân đoạn ở trên, việc thêm tên đệm sẽ không khác gì việc thêm họ và tên nhưng nếu chúng tôi tính đến khả năng chấp nhận phạm vi của hàm TEXTJOIN, mọi thứ có thể dễ dàng hơn một chút
Tập dữ liệu dưới đây là ví dụ trường hợp của chúng tôi với việc bổ sung tên đệm
Chúng ta đã biết rằng hàm TEXTJOIN nối một danh sách hoặc phạm vi chuỗi văn bản bằng dấu phân cách. Vì vậy, chúng tôi có thể dễ dàng đặt dấu phân cách là " " [dấu cách] và chọn phạm vi chứa tên, tên đệm và họ là
________số 8
Làm điều này tiết kiệm thời gian và công sức của chúng tôi vì thay vì thêm từng tham chiếu ô, chúng tôi đang sử dụng phạm vi ngay lập tức
Nhưng đợi một chút, hàm CONCAT cũng chấp nhận phạm vi ô. Chúng ta không thể làm điều tương tự với CONCAT?
Nếu chúng ta áp dụng ngay công thức =CONCAT[B3. D3] tổ hợp tên kết quả sẽ không chứa ký tự khoảng trắng. Tuy nhiên, với một số điều chỉnh [như thêm khoảng trắng sau các cột Tên, Tên đệm và Họ], chúng tôi có thể làm cho nó hoạt động. Nhưng nó không ở đâu bằng tính linh hoạt và dễ dàng mà hàm TEXTJOIN mang lại
Các tùy chọn rất phong phú;
Kết hợp Tên, Tên viết tắt của Tên đệm và Họ
Wile E Coyote, Mary J Blige, Samuel L Jackson – Bây giờ đó là định dạng chúng tôi sẽ làm việc với. Mặc dù đặt cược nhanh nhất và dễ dàng nhất của bạn để đạt được điều này vẫn phụ thuộc vào Flash Fill, hướng dẫn là gì nếu nó chỉ đưa ra một tùy chọn? . Chúng tôi chỉ cần trích xuất chữ cái đầu tiên cho các tên, vì vậy đây là công thức của chúng tôi để trích xuất chữ cái đầu tiên
=LEFT[C3,1]
Hàm LEFT chọn ra chữ cái đầu tiên của tên đệm Lynn trong ô C3. 1 cho biết số lượng chữ cái đầu chúng tôi yêu cầu TRÁI để trích xuất. Kết quả là L. Điều này tất nhiên không giới hạn ở tên đệm. Hàm LEFT cũng sẽ hoạt động với định dạng như "A Brooks", trích xuất chữ cái đầu tiên từ tên
Giờ đây, hàm này có thể được hợp nhất với bất kỳ hàm hoặc công thức nào được sử dụng để kết hợp tên và tất cả chúng sẽ mang lại kết quả giống nhau
=CONCAT[B3," ",C3]0
Đây là cách kết quả pan ra
Nếu bạn định thêm dấu chấm sau dấu đầu tiên [Abbey L. Brooks], hãy nhập dấu phân cách trong công thức dưới dạng dấu chấm rồi ký tự khoảng trắng ". "
Và cuối cùng, chúng ta đi đến một kết thúc. Đầu tiên, chúng tôi muốn nhắc bạn một lần nữa rằng Flash Fill có thể là người bạn tốt nhất của bạn trong quá trình tạo nhưng chúng tôi cũng hy vọng đã giúp bạn hiểu sâu hơn về các tùy chọn khác có thể được sử dụng để kết hợp tên và cuối cùng là chuỗi văn bản. Cuối cùng, chúng tôi sẽ quay lại phục vụ bạn với một phần Excel khác;