Nếu bạn làm việc với tập dữ liệu tên, sắp xếp nó là một trong những tác vụ phổ biến mà bạn phải thực hiện thường xuyên.
Rất dễ dàng để sắp xếp dữ liệu theo thứ tự bảng chữ cái theo tên đầy đủ, trong đó Excel sử dụng ký tự đầu tiên của tên để sắp xếp.
Tuy nhiên, nếu bạn muốnSắp xếp dữ liệu theo họ trong Excelleo lênSắp xếpLàm thế nào để làm gì?
Tuy không đơn giản như vậy nhưng vẫn có thể thực hiện được (cũng phụ thuộc nhiều vào cách cấu trúc dữ liệu tên).
Cho dù bạn sử dụng phương pháp nào, bằng cách nào đó bạn phải trích xuất họ từ đầy đủ và đặt nó vào một cột riêng biệt.Sau đó, bạn có thể sử dụng cột này để sắp xếp dữ liệu theo thứ tự bảng chữ cái theo họ.
Trong hướng dẫn Excel này, tôi sẽ chỉ cho bạn cách sắp xếp một cột có tên dựa trên họ.
Vậy hãy bắt đầu!
Nội dung
Trích xuất và sắp xếp theo họ bằng Tìm và Thay thế
Bước đầu tiên để sắp xếp theo họ là lấy họ trong một cột riêng biệt.
Bạn có thể làm điều này bằng cách thay thế mọi thứ trước họ bằng khoảng trống, vì vậy bạn chỉ còn lại họ.
Giả sử bạn có một tập dữ liệu như bên dưới và bạn muốn sắp xếp dữ liệu đó theo thứ tự bảng chữ cái theo họ.
Dưới đây là các bước để sắp xếp theo họ:
- Chọn tập dữ liệu có chứa tiêu đề (trong trường hợp này, nó sẽ là A1: A10)
- sao chép nó vào cột liền kề (nếu cột liền kề không trống, hãy chèn một cột mới, sau đó sao chép tên)
- Đổi tên các tiêu đề cột đã sao chép.Trong ví dụ này, tôi sẽ đặt tên là "Last Name"
- Chọn tất cả các tên đã sao chép (không chọn tiêu đề)
- Giữ phím Điều khiển, rồi nhấn phím H.Thao tác này sẽ mở hộp thoại Tìm và Thay thế.
- Trong trường Tìm gì, hãy nhập * (dấu hoa thị theo sau là ký tự khoảng trắng)
- Để trống trường Thay thế bằng
- Nhấp vào Thay thế Tất cả.Điều này sẽ thay thế tất cả các tên cùng một lúc và bạn sẽ chỉ còn lại họ.
Các bước trên sẽ giữ lại họ và xóa mọi thứ trước nó.Điều này hoạt động ngay cả khi bạn có tên đệm hoặc tiền tố như Mr. hoặc Ms.
Khi bạn có họ trong các cột liền kề, bạn có thể dễ dàng sắp xếp tập dữ liệu (bao gồm cả tên đầy đủ) theo thứ tự bảng chữ cái theo họ.
Dưới đây là các bước để sắp xếp theo họ:
- Chọn toàn bộ tập dữ liệu có tiêu đề (bao gồm họ và tên được trích xuất).Bạn cũng có thể bao gồm các cột khác để sắp xếp với tên
- Nhấp vào tab Dữ liệu
- Bấm để sắp xếp
- Trong hộp thoại Sắp xếp, hãy đảm bảo Dữ liệu của tôi có tiêu đề được chọn.
- Trong tùy chọn "Sắp xếp theo", hãy chọn tên của cột chỉ có họ
- Trong Sắp xếp Theo, chọn Giá trị Ô
- Trong tùy chọn "Đặt hàng", hãy chọn "A đến Z"
- Bấm OK
Các bước trên sẽ sắp xếp toàn bộ tập dữ liệu đã chọn dựa trên họ.
Sau khi hoàn tất, bạn có thể bỏ cột có họ.
Mẹo chuyên nghiệp: Bất cứ lúc nào bạn nghĩ rằng mình có thể cần quay lại dữ liệu ban đầu, bạn cần có cách để hủy sắp xếp tập dữ liệu đó.Đối với điều này, trong các cột liền kề (trái hoặc phải), có số thứ tự trước khi sắp xếp.Bây giờ, nếu bạn cần lấy lại dữ liệu ban đầu, bạn có thể lấy nó bằng cách sắp xếp dựa trên các con số.
Trích xuất và sắp xếp theo thứ tự bảng chữ cái theo họ bằng công thức
Mặc dù phương pháp được hiển thị ở trên (sử dụng tìm và thay thế) là những gì tôi muốn lấy tất cả họ và sắp xếp dựa trên nó, một trong những hạn chế của nó là dữ liệu kết quả là tĩnh.
Điều này có nghĩa là nếu tôi thêm nhiều tên vào danh sách, tôi sẽ phải thực hiện lại quy trình tương tự để lấy được họ.
Nếu đây không phải là điều bạn muốn, bạn có thể sử dụng phương pháp công thức để sắp xếp dữ liệu theo họ.
Giả sử bạn có tập dữ liệu được hiển thị bên dưới.
Đây là công thức để trích xuất họ từ tên đầy đủ:
= RIGHT (A2, LEN (A2) -FIND ("", A2))
Công thức trên dựa trên một mẫu có đầy đủ tên (trong ví dụ này chỉ có họ và tên).Mô hình là sẽ có một ký tự khoảng trắng giữa họ và tên.
Hàm FIND được sử dụng để lấy vị trí của ký tự khoảng trắng.Giá trị này sau đó được trừ vào tổng độ dài của tên để có tổng số ký tự trong họ.
Sau đó, sử dụng giá trị đó trong hàm RIGHT để lấy họ.
Khi bạn có cột họ, bạn có thể sắp xếp dữ liệu này (điều này được trình bày chi tiết trong phương pháp đầu tiên).
Công thức trên sẽ hoạt động khi bạn chỉ có họ và tên.
Nhưng nếu bạn cũng có tên đệm.hoặc có thể chào trước tên (ví dụ: Ông hoặc Bà)
Trong trường hợp này, bạn cần sử dụng công thức sau:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
Công thức trên tìm vị trí của ký tự khoảng trắng cuối cùng và sau đó sử dụng nó để trích xuất họ.
Tôi đề nghị bạn sử dụng công thức thứ hai trong mọi trường hợp, nó đơn giản hơn và xử lý được mọi trường hợp (miễn là họ ở cuối tên đầu tiên).
Lưu ý: Hai công thức này dựa trên điều kiện là chỉ có một ký tự khoảng trắng giữa mỗi phần tử tên.Công thức này sẽ cho kết quả không chính xác nếu có dấu cách kép hoặc dấu cách ở đầu / cuối.Trong trường hợp này, cách tốt nhất là sử dụng hàm TRIM để loại bỏ bất kỳ khoảng trắng đầu, cuối và kép nào trước, sau đó sử dụng công thức trên.
Mặc dù đây có vẻ là một cách tiếp cận phức tạp, nhưng lợi ích của việc sử dụng công thức là nó làm cho kết quả động.Nếu bạn thêm nhiều tên vào danh sách, tất cả những gì bạn phải làm là sao chép công thức và nó sẽ cung cấp cho bạn họ.
Sử dụng văn bản thành cột
Văn bản thành cột là một cách dễ dàng khác để chia ô trong Excel.
Bạn có thể chỉ định một dấu phân tách (chẳng hạn như dấu phẩy hoặc dấu cách) và sử dụng nó để tách nội dung của một ô.Sau khi tách các phần tử trong các cột riêng biệt, bạn có thể sử dụng cột có họ để sắp xếp dữ liệu theo thứ tự bảng chữ cái.
Giả sử bạn có một tập dữ liệu giống như sau:
Dưới đây là các bước để sắp xếp theo họ bằng cách sử dụng Văn bản thành Cột:
- Chọn các cột có tên (không bao gồm tiêu đề)
- Nhấp vào tab Dữ liệu
- Trong nhóm Công cụ Dữ liệu, hãy nhấp vào tùy chọn Văn bản thành Cột.Thao tác này sẽ mở trình hướng dẫn Chuyển văn bản thành Cột
- Trong Bước 1 của Trình hướng dẫn Chuyển văn bản thành Cột, hãy chọn Phân cách và nhấp vào Tiếp theo
- Ở bước 2, chọn "Dấu cách" làm dấu phân cách (bỏ chọn bất kỳ thứ gì khác nếu được chọn) và nhấp vào nút Tiếp theo.
- Ở bước 3, chọn cột tên trong phần xem trước dữ liệu, sau đó chọn tùy chọn "Không nhập cột (bỏ qua)".Điều này đảm bảo rằng tên không phải là một phần của kết quả, bạn chỉ nhận được họ.
- Cũng trong bước 3, thay đổi ô đích thành ô liền kề với dữ liệu ban đầu.Điều này sẽ đảm bảo rằng bạn chỉ nhận được họ và dữ liệu tên gốc đã hoàn chỉnh.
- Nhấp vào Kết thúc
Sau khi có kết quả, bạn có thể sắp xếp theo họ.
Bạn cũng có thể sử dụng text-to-column để phân tách họ và tên khi bạn có dấu phẩy làm dấu phân cách.
Lấp lánh
Một cách nhanh chóng khác để lấy họ là sử dụng tính năng Flash Fill.
Được giới thiệu trong Excel 2013, Flash Fill giúp thao tác dữ liệu bằng cách xác định các mẫu.Để làm được điều này, bạn cần hiển thị Flash Fill nhiều lần để có kết quả như mong đợi.
Khi nó nhận ra mẫu, nó sẽ nhanh chóng thực hiện phần còn lại cho bạn.
Giả sử bạn có tập dữ liệu tên sau đây.
Dưới đây là các bước để lấy họ bằng Flash Fill và sử dụng nó để sắp xếp:
- Trong ô B2, nhập văn bản "Maury".Đây là những gì bạn mong đợi trong một phòng giam.
- Chuyển đến ô tiếp theo và nhập họ của tên vào ô liền kề (trong trường hợp này là Elliot).
- chọn hai ô
- Di con trỏ qua phần dưới bên phải của vùng chọn.Bạn sẽ nhận thấy rằng con trỏ chuyển thành biểu tượng dấu cộng.
- Bấm đúp vào nó (hoặc bấm và kéo xuống).Điều này sẽ cung cấp cho bạn một số kết quả trong ô (không chắc là kết quả bạn muốn)
- Nhấp vào biểu tượng Tùy chọn Tự động điền.
- Nhấp vào Điền nhanh
Điều này sẽ cung cấp cho bạn một kết quả có thể là họ trong tất cả các ô.
Tôi nói có thể vì Flash Fill có thể không hoạt động trong một số trường hợp.Vì nó phụ thuộc vào chế độ nhận dạng, nó có thể không phải lúc nào cũng làm được điều này.Hoặc đôi khi, mẫu mà nó giải mã có thể không phải là mẫu chính xác.
Trong trường hợp này, bạn nên nhập kết quả mong đợi vào một hoặc nhiều ô và sau đó thực hiện các bước 4-7.
Sau khi bạn có tất cả họ trong cột, bạn có thể sắp xếp dữ liệu dựa trên những họ đó.
Do đó, bạn có thể sắp xếp dữ liệu theo họ theo bốn cách khác nhau.Cách tốt nhất là sử dụng các kỹ thuật tìm và thay thế, nhưng nếu bạn muốn làm cho kết quả của mình trở nên năng động, thì phương pháp công thức là cách tốt nhất.
Hy vọng bạn thấy hướng dẫn này hữu ích.
