Tin mới

Bao giờ con gái kế toán lấy chồng
“Bao giờ lấy chồng” hay “Sao lần nào về cũng một mình vậy?”, hay “Không dắt thằng nào về cùng à” “Có người yêu chưa?...
Những vấn đề chung về kế toán thuê tài sản
Kế toán thuê tài sản là gì? Sự khác biệt giữa thuê tài chính và thuê hoạt động là như thế nào? Khi doanh nghiệp thực...
Hạch toán chi phí quà tặng tết cho khách hàng và nhân viên
Qùa tặng là một phần không thể thiếu trong mỗi dịp lễ tết trong mỗi doanh nghiệp. Quà tặng để bày tỏ sự cảm ơn đến...
Nợ doanh nghiệp - giải pháp thu hồi nhanh
Cuối năm, doanh nghiệp luôn cần nguồn tài chính để chi trả cho nhiều vấn đề. Nhưng một khoản nợ của doanh nghiệp vẫn bị...
6 bước để săn tìm công việc kế toán
Công việc kế toán tưởng chừng như đơn giản như chính những con số vậy. Nhưng không hẳn nó đã dễ dàng với nhiều người....

Hình ảnh

Được tài trợ

Khuyến mãi lớn của MISA
Kiên thức, thủ thuật tin học văn phòng

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

28/06/2016 03:03

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Ể

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

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

 

ĐÀ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 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

HỌ VÀ TÊN

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

HỌ
TRÍCH HỌ BẰNG
LEFT+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

FIND
TÌ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é.

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

- 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;" ";""))))

Bây giờ để tìm ra VỊ TRÍ CỦA DẤU CÁCH CUỐI CÙNG đó các bạn có thể dùng hàm FIND. Nhưng vấn đề đặt ra ở đây là trong họ và tên có rất nhiều dấu cách giống nhau, nên các bạn không thể FIND một cách bình thường được. Mục đích của hàm substitute này là tách hai thành phần họ đệm và tên ra bởi một ký tự đặc biệt nào đó, sau đó các bạn dùng hàm Find để tìm ra ký tự đó để lấy làm num_char cho hàm left. Lúc đó các bạn có thể trích ra họ đệm trong họ và tên một cách dễ dàng.
 

HỌ VÀ TÊN

FIND
 - TÌM VỊ TRÍ DẤU CÁCH CUỐI  ĐỂ TÁCH HỌ ĐỆM = SUBSTITUE

ĐỨC MINH

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

ĐÀO BÁ ĐẠO

=FIND("$";SUBSTITUTE(A3;" ";"$";LEN(A3)-LEN(SUBSTITUTE(A3;" ";""))))

ĐÀO ĐĂNG KHOA

=FIND("$";SUBSTITUTE(A4;" ";"$";LEN(A4)-LEN(SUBSTITUTE(A4;" ";""))))

NGUYỄN PHI THƯỜNG

=FIND("$";SUBSTITUTE(A5;" ";"$";LEN(A5)-LEN(SUBSTITUTE(A5;" ";""))))

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

=FIND("$";SUBSTITUTE(A6;" ";"$";LEN(A6)-LEN(SUBSTITUTE(A6;" ";""))))

Kết quả:

HỌ VÀ TÊN

FIND
 - TÌM VỊ TRÍ DẤU CÁCH CUỐI  ĐỂ TÁCH HỌ ĐỆM

ĐỨC MINH

4

ĐÀO BÁ ĐẠO

7

ĐÀO ĐĂNG KHOA

9

NGUYỄN PHI THƯỜNG

11

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

19

 

 

 

 

 

 

 

 

 

 

 

- 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)

HỌ VÀ TÊN

HỌ ĐỆM

ĐỨC MINH

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

ĐÀO BÁ ĐẠO

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

ĐÀO ĐĂNG KHOA

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

NGUYỄN PHI THƯỜNG

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

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

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

Kết quả:

HỌ VÀ TÊN

HỌ ĐỆM

ĐỨC MINH

ĐỨC

ĐÀO BÁ ĐẠO

ĐÀO BÁ

ĐÀO ĐĂNG KHOA

ĐÀO ĐĂNG

NGUYỄN PHI THƯỜNG

NGUYỄN PHI

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

TRẦN THỊ TỐ NỮ BẢO

- 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))

HỌ VÀ TÊN

TÊN

ĐỨC MINH

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

ĐÀO BÁ ĐẠO

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

ĐÀO ĐĂNG KHOA

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

NGUYỄN PHI THƯỜNG

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

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

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

Kết quả:

HỌ VÀ TÊN

TÊN

ĐỨC MINH

 MINH

ĐÀO BÁ ĐẠO

 ĐẠO

ĐÀO ĐĂNG KHOA

 KHOA

NGUYỄN PHI THƯỜNG

 THƯỜNG

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

 CHÂU

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:

HỌ VÀ TÊN

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

HỌ ĐỆM

TÊN

ĐỨC MINH

ĐỨC

ĐỨC

 MINH

ĐÀO BÁ ĐẠO

ĐÀO

ĐÀO BÁ

 ĐẠO

ĐÀO ĐĂNG KHOA

ĐÀO

ĐÀO ĐĂNG

 KHOA

NGUYỄN PHI THƯỜNG

NGUYỄN

NGUYỄN PHI

 THƯỜNG

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

ĐOÀN

TRẦN THỊ TỐ NỮ BẢO

 CHÂU

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

HỌ VÀ TÊN

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

HỌ ĐỆM

TÊN

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

TRẦN

TRẦN THỊ TỐ NỮ BẢO

 CHÂU

ĐÀO BÁ ĐẠO

ĐÀO

ĐÀO BÁ

 ĐẠO

ĐÀO ĐĂNG KHOA

ĐÀO

ĐÀO ĐĂNG

 KHOA

ĐỨC MINH

ĐỨC

ĐỨC

 MINH

NGUYỄN PHI THƯỜNG

NGUYỄN

NGUYỄN PHI

 THƯỜNG

 

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 các bạn xem thêm các bài viết cực kỳ hay và tâm huyết tại:

>>> 100 thủ thuật Excel

>>> Sinh viên có nên học thêm tin học văn phòng ?

>>> Chuyển đổi số thành chữ trong excel

>>> Quản lý hàng hóa bằng data validation trong excel

>>> Cách học excel

 

  • Chia sẻ
Bình luận

BÀI VIẾT LIÊN QUAN