Cách tách tên để sắp xếp tên theo abc trong excel 2003 2007 2010 2013 (Có video)

Hôm nay Tin học Đức Minh sẽ hướng dẫn cho các bạn cách tách tên để sắp xếp tên theo abc trong các phiên bản excel 2003 2007 2010 2013 CỰC KỲ CỤ THỂ

Link video: https://www.youtube.com/watch?v=AFL5zXhlR6I

I. Về vấn đề sắp xếp tên và tách họ tên trong excel

Xin chào các bạn, hôm nay Tin học Đức Minh sẽ hướng dẫn cho các bạn cách sắp xếp tên theo abc trong excel 2003 2007 2010 2013 bằng cách tách riêng các thành phần họ, họ đệm và tên để cho việc sắp xếp trở nên đơn giản nhất.

Trong thời gian đào tạo ở Tin học Đức Minh, qua rất nhiều thế hệ học viên, tôi – một giáo viên tại Tin học Đức Minh, thường xuyên nhận được câu hỏi từ các bạn học viên về cách sắp xếp tên theo abc trong excel. Thường thì cách sắp xếp dữ liệu chữ theo abc hay sắp xếp dữ liệu số theo thứ tự độ lớn khá là dễ, các bạn chỉ việc dùng chức năng lọc trong excel hoặc custom sort là xong. Tuy nhiên vấn đề đặt ra ở đây các bạn sẽ gặp phải khi sắp xếp tên theo abc trong excel là excel luôn sắp xếp theo ký tự đầu tiên của một từ hay một cụm từ. Vì vậy chúng các bạn sẽ gặp khó khăn rất lớn trong việc sắp xếp một lượng dữ liệu gồm họ và tên đầy đủ, ví dụ các trường học quản lý hàng chục nghìn, trăm nghìn họ tên học sinh sinh viên một lúc chẳng hạn. Để các bạn dễ hiểu ở đây Tin học Đức Minh sẽ đưa ra một ví dụ mình họa. Ví dụ các bạn có một loạt tên như sau: ĐÀO BÁ ĐẠO, ĐÀO, ĐĂNG KHOA, ĐỨC MINH, NGUYỄN PHI THƯỜNG, TRẦN THỊ TỐ NỮ BẢO CHÂU. Thì khi sắp xếp theo thứ tự a đến z các bạn chỉ nhận được kết quả sắp xếp tên theo chữ cái đầu tiên của mỗi ô, cụ thể là:

Thực ra kết quả các ban muốn phải là như thế này:

Trong trường hợp này các bạn có thể dùng một số cách khác nhau, ở phạm vi bài viết này, Tin học Đức Minh sẽ giới thiệu cho các bạn các sắp xếp tên theo abc trong excel bằng cách tách tên, họ đệm, họ ra riêng thành từng thành phần khác nhau để tiện cho việc sắp xếp. Chúng các bạn có thể dùng một số hàm trong nhóm hàm chuỗi trong excel như left, right, len, find, substitute để thực hiện công việc này.

II. Các bước tách tên và họ để sắp xếp tên trong excel 2003 2007 2010 2013

Để các bạn được dễ hiểu, Tin học Đức Minh sẽ không làm hàm gộp ngay mà tách ra từng phần một để các bạn tiện theo dõi.

1. Tách họ bằng hàm LEFT + FIND

- Bước 1: Tìm vị trí dấu cách đầu để tách họ bằng hàm left:

Như bảng trên các bạn tách họ bằng hàm =FIND(" ";A2) . Mục đích của việc các bạn dùng hàm Find này là do để trích họ thì các bạn cần trích ký tự từ bên trái sang của họ và tên, nhưng vấn đề đặt ra là mỗi họ lại có số ký tự khác nhau, ví dụ họ đào thì có 3 ký tự, đoàn thì có 4 ký tự,…. Nhưng các bạn có thể tinh ý thấy một điểm chung là dù có bao nhiêu ký tự đi nữa thì chúng nó cũng sẽ có chung một điểm kết thúc đó chính là dấu cách đầu tiên. Vậy các bạn có thể dùng hàm find để tìm ra số thứ tự của vị trí dấu cách đầu tiên đó, lấy cơ sở để làm “num-char” cho hàm left (trích bao nhiêu thứ tự từ bên trái qua). Ở đây các bạn sẽ có kết quả là:

- Bước 2: Từ số vị trí dấu cách đầu các bạn trích họ ra bằng hàm left

Như bảng trên các bạn tách họ bằng hàm gộp =LEFT(A2;FIND(" ";A2)-1) hoặc bạn cũng có thể làm hàm tắt là =LEFT(A2;B2-1)

 Cũng được, vì lúc nãy các bạn đã tính ô B2 ra số vị trí dấu cách đầu tiên rồi. Kết quả sẽ là:

2. Tách họ đệm bằng hàm LEFT + LEN + FIND + SUBSTITUTE

Hàm đầy đủ để tách họ đệm ra là:

=LEFT(A2;FIND("$";SUBSTITUTE(A2;" ";"$";LEN(A2)-LEN(SUBSTITUTE(A2;" ";""))))-1).

- Trước tiên các bạn cần phân tích thấy dù họ và tên có dài và nhiều từ thế nào các bạn cũng sẽ thấy một điểm chung nhất lấy làm cơ sở tính toán, đó chính là chung 1 dấu cách cuối cùng, phân cách với phần họ đệm ở phía trước. Bởi vậy chúng ta mới phải dùng tới các hàm trên, còn dùng như thế nào các bạn xem tiếp các bước cụ thể ở phần dưới sẽ hiểu.

Hàm này khá là dài và phức tạp, bởi vậy Tin học Đức Minh sẽ bóc tách và phân tích cụ thể từng phần cho những bạn nào muốn hiểu bản chất của hàm này, còn nếu các bạn lười đọc thì có thể bỏ qua các bước sau và copy luôn công thức trên nhé.

- Bước 1: Tính số dấu cách trong họ và tên bằng hàm len + substitute

Cú pháp: =LEN(A2)-LEN(SUBSTITUTE(A2;" ";"")

Vấn đề xuất hiện đó là khi các bạn dùng substitute để thay thế các bạn lại không thể xác định ra SỐ LẦN XUẤT HIỆN CỦA DẤU CÁCH CUỐI CÙNG (INSTANCE_NUM trong SUBSTITUTE).

Lúc này các bạn có thể thấy một ví dụ là một cô gái tên là TRẦN THỊ TỐ NỮ BẢO CHÂU chẳng hạn, thì SỐ LẦN XUẤT HIỆN CỦA DẤU CÁCH CUỐI CÙNG (INSTANCE_NUM trong SUBSTITUTE) chính là 5. Vậy số 5 này xác định bằng cách nào? Các bạn lại nhận thấy rằng số 5 này chính là số chênh lệch giữa SỐ KÝ TỰ HỌ TÊN ĐẦY ĐỦ và SỐ KÝ TỰ CỦA HỌ VÀ TÊN KHÔNG CÓ DẤU CÁCH – Lúc này ta có thể dùng hàm LEN(A2)-LEN(SUBSTITUTE(A2;" ";"") để tính ra.

Kết quả:

- Bước 2: Xác định SỐ LẦN XUẤT HIỆN dấu cách cuối bằng hàm len ở trên, thay bằng “$” với hàm substitute.

Vậy lúc này các bạn cần dùng một hàm THAY THẾ DẤU CÁCH CUỐI CÙNG BẰNG MỘT KÝ TỰ ĐẶC BIỆT, ví dụ “$” chẳng hạn, để việc dùng hàm FIND trở nên dễ dàng. Lúc này hàm subsititute sẽ là hàm thích hợp nhất

Cú pháp =SUBSTITUTE(A2;" ";"$";LEN(A2)-LEN(SUBSTITUTE(A2;" ";"")))

Kết quả:

ĐỂ TÌM DẤU CÁCH CUỐI BẰNG HÀM SUBSTITUE TA PHẢI XÁC ĐỊNH ĐƯỢC INSTANCE_NUM BẰNG CÁCH LẤY ĐỘ DÀI HỌ TÊN CÓ CÁCH - ĐỘ DÀI HỌ TÊN VIẾT LIỀN.

- Bước 3: Dùng hàm find + substitute để tìm XÁC ĐỊNH VỊ TRÍ DẤU CÁCH CUỐI.

Cú pháp: 

=Find(“$”,SUBSTITUTE(A2;" ";"$";LEN(A2)-LEN(SUBSTITUTE(A2;" ";""))))

Kết quả:

- Bước 5: Tách họ đệm khỏi họ và tên bằng hàm left + các hàm ở trên:

Cú pháp:

=LEFT(A2;FIND("$";SUBSTITUTE(A2;" ";"$";LEN(A2)-LEN(SUBSTITUTE(A2;" ";""))))-1)

Kết quả:

- Bước 6: Tách tên bằng hàm Right kết hợp hàm Len

Đã có họ và tên + họ đệm, ta chỉ việc dùng hàm len để trừ số ký tự của họ và tên trừ đi số ký tự của họ đệm là ra số ký tự cần trích từ bên phải sang.

Cú pháp:

=RIGHT(Họ và tên;LEN(Họ và tên)-LEN(Họ đệm))

Kết quả:

Bây giờ đã có 4 cột: họ và tên, họ, họ đệm, tên ta đi tới bước sắp xếp tên theo thứ tự abc trong excel. Ta có thể ẩn những cột không cần thiết bằng cách hide chúng đi

II. Sắp xếp tên theo abc trong excel

Sau khi thực hiện các bước ở phần 2, đã có 4 cột: họ và tên, họ, họ đệm, tên. Ta bật chức năng lọc Filter lên, sau đó chọn lọc tên theo abc,cột họ và tên và các cột khác sẽ tự động nhảy theo:

Và đây là kết quả:

Bài viết hơi dài nhưng thật sự tôi đã dành tất cả tâm huyết và thời gian để giúp các bạn hiểu thật rõ. Chúc các bạn thành công! Nếu có thắc mắc gì các bạn để lại comment Đức Minh sẽ giải đáp thật chi tiết cho các bạn! Ở bài viết sau Tin học Đức Minh sẽ hướng dẫn cho các bạn cách tách họ và tên trong excel CỰC NHANH nhé!

Mọi người nếu copy vui lòng ghi rõ nguồn nhé (đặt link bài viết này ở cuối bài), cám ơn nhiều:

>>> http://ketoanducminh.edu.vn/tin-tuc/102/2768/Cach-tach-ten-de-sap-xep-ten-theo-abc-trong-excel-2003-2007-2010-2013.html

Tác giả bài viết: Khoa PTT

Youtube Channel: https://www.youtube.com/channel/UC8dZoB0lsYIefHR7VXSZCuQ

Mời các bạn tham khảo thêm các bài viết khác của tác giả:

>>>> 100 thủ thuật trong Excel

>>>> Ẩn dữ liệu trong excel cực hay

>>>> Kỹ năng tin học văn phòng cơ bản cần nắm vững

>>> Cách tạo siêu liên kết Hyperlink trong excel siêu hữu dụng

>>> Căn lề in mặc định trong Excel – điều ít người biết tới!

>>> Cách quản lý hàng hóa bằng Data Validation trong Excel cực hay

>>> Cách tách tên để sắp xếp tên theo abc trong excel 2003 2007 2010 2013 (Có video)

>>>Tiếng Anh tin học – 1000 Từ vựng tiếng Anh trong Excel 

>>> Tạo chữ ký cho Gmail và chèn ảnh, link, Anchor Text vào chữ ký Gmail

>>> Tiếng Anh tin học – 1000 Từ vựng tiếng Anh trong Word – Phần 1: Giao diện

>>> Các phím tắt hữu dụng trong Microsoft Word (Phần 1)

• Chia sẻ
• Tweet