Cách Sắp Xếp Tên Theo Abc Trong Excel 2013, 201, Cách Sắp Xếp Tên Theo Thứ Tự Chữ Cái Trong Excel

*

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

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 excelhoặ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à:

ĐÀO BÁ ĐẠO

ĐÀO ĐĂNG KHOA

ĐỨC MINH

NGUYỄN PHI THƯỜNG

TRẦN THỊ TỐ NỮ BẢO CHÂU

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

TRẦN THỊ TỐ NỮ BẢO CHÂU

ĐÀO BÁ ĐẠO

ĐÀO ĐĂNG KHOA

ĐỨC MINH

NGUYỄN PHI THƯỜNG

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 excelnhư left, right, len, find, substitute để thực hiện công việc này.

Đang xem: Sắp xếp tên theo abc trong excel 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

HỌ VÀ TÊN

FINDTÌM VỊ TRÍ DẤU CÁCH ĐẦU ĐỂ TÁCH HỌ = HÀM LEFT

HỌTRÍCH HỌ BẰNGLEFT+FIND

ĐỨC MINH

=FIND(” “;A2)

=LEFT(A2;FIND(” “;A2)-1)

ĐÀO BÁ ĐẠO

=FIND(” “;A3)

=LEFT(A3;FIND(” “;A3)-1)

ĐÀO ĐĂNG KHOA

=FIND(” “;A4)

=LEFT(A4;FIND(” “;A4)-1)

NGUYỄN PHI THƯỜNG

=FIND(” “;A5)

=LEFT(A5;FIND(” “;A5)-1)

TRẦN THỊ TỐ NỮ BẢO CHÂU

=FIND(” “;A6)

=LEFT(A6;FIND(” “;A6)-1)

– 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à:

HỌ VÀ TÊN

FINDTÌM VỊ TRÍ DẤU CÁCH ĐẦU ĐỂ TÁCH HỌ = HÀM LEFT

ĐỨC MINH

4

ĐÀO BÁ ĐẠO

4

ĐÀO ĐĂNG KHOA

4

NGUYỄN PHI THƯỜNG

7

TRẦN THỊ TỐ NỮ BẢO CHÂU

5

– 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à:

HỌ VÀ TÊN

HỌTRÍCH HỌ BẰNG LEFT+FIND

ĐỨC MINH

ĐỨC

ĐÀO BÁ ĐẠO

ĐÀO

ĐÀO ĐĂNG KHOA

ĐÀO

NGUYỄN PHI THƯỜNG

NGUYỄN

TRẦN THỊ TỐ NỮ BẢO CHÂU

ĐOÀN

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é.

Xem thêm: Vở Bài Tập Toán Lớp 5 Bài 109 Luyện Tập Chung, Bài 109 : Luyện Tập Chung

– 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.

HỌ VÀ TÊN

LENĐỂ TÍNH SỐ DẤU CÁCH TRONG HỌ TÊN

ĐỨC MINH

=LEN(A2)-LEN(SUBSTITUTE(A2;” “;””))

ĐÀO BÁ ĐẠO

=LEN(A3)-LEN(SUBSTITUTE(A3;” “;””))

ĐÀO ĐĂNG KHOA

=LEN(A4)-LEN(SUBSTITUTE(A4;” “;””))

NGUYỄN PHI THƯỜNG

=LEN(A5)-LEN(SUBSTITUTE(A5;” “;””))

TRẦN THỊ TỐ NỮ BẢO CHÂU

=LEN(A6)-LEN(SUBSTITUTE(A6;” “;””))

Kết quả:

HỌ VÀ TÊN

LENĐỂ TÍNH SỐ DẤU CÁCH TRONG HỌ TÊN

ĐỨC MINH

1

ĐÀO BÁ ĐẠO

2

ĐÀO ĐĂNG KHOA

2

NGUYỄN PHI THƯỜNG

2

TRẦN THỊ TỐ NỮ BẢO CHÂU

5

– 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;” “;””)))

HỌ VÀ TÊN

SUBSTITUE(XÁC ĐỊNH ĐẤU CÁCH CUỐI, THAY = $)

ĐỨC MINH

=SUBSTITUTE(A2;” “;”$”;LEN(A2)-LEN(SUBSTITUTE(A2;” “;””)))

ĐÀO BÁ ĐẠO

=SUBSTITUTE(A3;” “;”$”;LEN(A3)-LEN(SUBSTITUTE(A3;” “;””)))

ĐÀO ĐĂNG KHOA

=SUBSTITUTE(A4;” “;”$”;LEN(A4)-LEN(SUBSTITUTE(A4;” “;””)))

NGUYỄN PHI THƯỜNG

=SUBSTITUTE(A5;” “;”$”;LEN(A5)-LEN(SUBSTITUTE(A5;” “;””)))

TRẦN THỊ TỐ NỮ BẢO CHÂU

=SUBSTITUTE(A6;” “;”$”;LEN(A6)-LEN(SUBSTITUTE(A6;” “;””)))

Kết quả:

HỌ VÀ TÊN

SUBSTITUE(XÁC ĐỊNH ĐẤU CÁCH CUỐI, THAY = $)

ĐỨC MINH

ĐỨC$MINH

ĐÀO BÁ ĐẠO

ĐÀO BÁ$ĐẠO

ĐÀO ĐĂNG KHOA

ĐÀO ĐĂNG$KHOA

NGUYỄN PHI THƯỜNG

NGUYỄN PHI$THƯỜNG

TRẦN THỊ TỐ NỮ BẢO CHÂU

TRẦN THỊ TỐ NỮ BẢO$CHÂU

ĐỂ 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.

Xem thêm: Đồ Án Khách Sạn 4 Sao File Cad Hotel Khách Sạn 4 Sao, Đồ Án Khách Sạ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;” “;””))))

Xem thêm bài viết thuộc chuyên mục: Excel