PDA

Xem phiên bản đầy đủ : Công thức lấy phần họ, tên


nationpham
18-08-2007, 00:12
Thiên Bồng Nguyên Soái đã giới thiệu với các bạn cách viết Macro VB để tách họ, tên ra khỏi họ và tên. Theo tôi cách này hay và rất tiện lợi. Nhất là khi nó được lưu dưới dạng Add-Ins (*.xla) thì có thể sao chép thoải mái và đưa vào Add-Ins để sử dụng mà chẳng mất công để soạn.
Nhân dịp này tôi cũng xin giới thiệu với các bạn một bài tập Excel nâng cao tôi biên soạn để thử thách các học sinh giỏi trong học phần Excel của môn Tin học văn phòng. Nhiều khi tôi cần phải tách tên mà máy tôi đang sử dụng không có sẵn Add-Ins tách họ tên thì tôi cũng dùng cách này tách tên cho lẹ.
Đề ra như sau: Cho cột A chứa họ và tên học sinh có dữ liệu bắt đầu từ dòng thứ 2. Hãy lập công thức để tách tên ra khỏi họ và tên đặt vào cột D, tách họ ra khỏi họ và tên đặt vào cột C. Cho phép dùng thêm cột B làm cột phụ chứa công thức tính số ký tự của tên. Giả sử tên tối thiểu là 1 ký tự và tối đa là 8 ký tự tính luôn cả ký tự dấu tiếng Việt. Họ và tên không có khoảng trống thừa (đã dùng hàm TRIM để xử lý). Phía dưới là các phần giải đáp tôi cố tình ghi ngược thứ tự thực hiện để trình bày từ dễ đến khó.
Giải đáp công thức tách tên (giả sử đã biết số ký tự của phần tên trong cột B):
=RIGHT(A2,B2)

Giải đáp công thức tách họ (giả sử đã biết số ký tự của phần tên trong cột B):=LEFT(A2,LEN(A2)-B2-1)

Giải đáp công thức tính số ký tự của tên (phần dài dòng nhất):=IF(LEFT(RIGHT(A2,2))=" ",1, ... )
Copy toàn bộ hàm IF dán vào dấu ... và tăng các giá trị số lên 1 đơn vị cho tới khi có 7 hàm IF lồng vào nhau. Lúc đó thay dấu ... bằng số 8 là xong. Sau khi hoàn thành công thức có dạng:
=IF(LEFT(RIGHT(A2,2))=" ",1,IF(LEFT(RIGHT(A2,3))=" ",2,IF(LEFT(RIGHT(A2,4))=" ",3,IF(LEFT(RIGHT(A2,5))=" ",4,IF(LEFT(RIGHT(A2,6))=" ",5,IF(LEFT(RIGHT(A2,7))=" ",6,IF(LEFT(RIGHT(A2,8))=" ",7,8)))))))

Chúc các bạn mệt mỏi vì công thức này! :D (Thật ra nếu các bạn nắm được ý tưởng chính từ 3 công thức ở trên thì việc tách tên này vô cùng đơn giản). Xin nhờ các bạn một việc! Các bạn tìm giúp tôi xem có cái tên tiếng Việt có dấu của người Kinh nào mà dài hơn 8 ký tự không! Vì nếu có thì công thức trên của tôi chỉ dùng để làm bài tập thôi chứ không ứng dụng thực tiễn được! Thực tế thì tôi chưa gặp rắc rối khi sử dụng! Công thức này tôi nghĩ ra cách đây 5 năm nhờ vào lời thách thức của học trò tôi! Bạn nào có cùng ý tưởng xin thông cảm nhé! :)

thaygiaolang
18-09-2007, 22:13
Có một công thức khác để tách họ tên :
Tách phần họ:
=LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)
Tách phần tên:
=RIGHT(A2,LEN(A2)-LEN(B2)-1)

nationpham
21-09-2007, 20:55
Cám ơn Thầy Giáo Làng! Công thức thật là tuyệt! Trước giờ tôi cũng thử nhiều cách để dùng hàm Find nhưng không nghĩ ra cách dùng Substitute để xác định khoảng trắng trước tên như công thức của Thầy Giáo Làng! Một lần nữa xin cám ơn vì đã chia sẽ công thức quá độc! :)

thaygiaolang
23-09-2007, 15:30
Thật ra thì công thức trên cũng chưa hoàn chỉnh vì nếu ô A2 chỉ có tên không thôi (không chứa khoảng trắng) thì công thức sẽ báo lỗi.
Mời các bạn tham gia để hoàn chỉnh công thức này.

admin
24-09-2007, 09:27
Để giải quyết việc không có khoảng trắng ta sẽ dùng hàm ISERROR bẫy lỗi cho hàm FIND khi tìm khoảng trắng như sau (tách họ):
=IF(ISERROR(FIND(" ",A2)),"",LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1))

thaygiaolang
24-09-2007, 17:31
Công thức của thầy Tân đã giải quyết được vần đề. Tuy nhiên các bạn vẫn có thể đóng góp những cách giải quyết khác (ở đây không so sánh hay dở, miễn là giải quyết được vấn đề)

quanvu
27-09-2007, 10:23
Mình có một cách để tách họ và tên trên Excel như sau, mọi người tham khao xem sao:
Giả sử cột A là họ và tên đầy đủ.
Cột B cần tách phần họ lót: Công thức: = LEFT(A1, FIND(" ", A1, IF(LEN(A1)-6>(LEN(A1)/2), LEN(A1)-6, LEN(A1)-3)))
Cột C cần tách tên: = RIGHT(A1, LEN(A1)-LEN(B1))

thaygiaolang
27-09-2007, 16:53
Cám ơn bạn đã đề xuất một cách làm khác. Công thức của bạn có thể giải quyết vấn đề trong nhiều trường hợp. Tuy nhiên, nếu như họ tên dài hơn 12 ký tự và tên chỉ có 1 ký tự (ví dụ Nguyễn Thị Như Ý) thì công thức tính không đúng.
Mong bạn tiếp tục nghiên cứu để hoàn thiện công thức của bạn.

HoNgDon
28-09-2007, 18:37
Cái này tôi thấy thầy Trung ở Bình Dương đã tạo một Add-in, chi cần chạy setup là nó tích hợp vào Excel ngay, tao một nút lệnh trên thanh tiêu đề, sử dung rất thuận tiện, ngoài ra nó còn có thể xếp thứ tự Tên, Chữ lót, Họ theo yêu cầu của người dùng, xin load lên để ai cần thì tải về giải nén và sử dụng nhé. Nhắc lại của thầy Phạm văn Trung đó.

thaygiaolang
29-09-2007, 17:39
Nếu ta chỉ chú trọng vào công việc thì ta có thể dùng các tiện ích miễn phí (hiện nay trên mạng có khá nhiều), vừa đỡ tốn công, vừa chắc ăn hơn. Tuy nhiên, tôi khuyến khích các bạn nghiên cứu còn nhằm vào mục đích khác là tìm tòi, học hỏi, sáng tạo, ngoài việc phục vụ cho công việc của mình.

nationpham
29-09-2007, 22:36
Thầy giáo làng nói đúng rồi! Tôi cũng đã tự viết cái Add-in thực hiện các tiện ích trên tiếng Việt để sử dụng trong công việc cho tiện. Tôi khởi xướng chủ đề này chỉ nhằm mục đích tìm tòi, học hỏi và sáng tạo trong việc vận dụng hàm của Excel. Có nhiều việc nếu viết bằng Macro thì thuận tiện hơn nhiều nhưng nếu ta suy nghĩ được cách dùng hàm thì thật là thú vị. Tôi rất thích những bài toán thách thức kiểu này. Khi tìm ra được lời giải thì tôi thấy thật sung sướng. Cũng vì vậy mà công thức tách tên của Thầy Giáo Làng làm tôi vô cùng thích thú vì đã khám phá ra một tuyệt chiêu mới trong việc vận dụng hàm của Excel. Hàm trong Excel vô cùng phong phú nhưng cái khó của vận dụng là phải biết cách lồng ghép để cho ra kết quả chứ không thể viết ra thành những đoạn lệnh như trong lập trình được.

lmx-doantuong
09-10-2007, 17:15
Hay lắm đó. Thực tập Excel. Cảm ơn bạn.

Trần Thanh Từ
18-11-2007, 10:13
Cảm ơn các Thầy. Thực sự từ trước tới giờ tôi vẫn không nghĩ ra cách hay như vậy. Rất cảm ơn !!!

nhd1973
18-11-2007, 10:40
Thiên Bồng Nguyên Soái đã giới thiệu với các bạn cách viết Macro VB để tách họ, tên ra khỏi họ và tên. Theo tôi cách này hay và rất tiện lợi. Nhất là khi nó được lưu dưới dạng Add-Ins (*.xla) thì có thể sao chép thoải mái và đưa vào Add-Ins để sử dụng mà chẳng mất công để soạn.
Nhân dịp này tôi cũng xin giới thiệu với các bạn một bài tập Excel nâng cao tôi biên soạn để thử thách các học sinh giỏi trong học phần Excel của môn Tin học văn phòng. Nhiều khi tôi cần phải tách tên mà máy tôi đang sử dụng không có sẵn Add-Ins tách họ tên thì tôi cũng dùng cách này tách tên cho lẹ.
Đề ra như sau: Cho cột A chứa họ và tên học sinh có dữ liệu bắt đầu từ dòng thứ 2. Hãy lập công thức để tách tên ra khỏi họ và tên đặt vào cột D, tách họ ra khỏi họ và tên đặt vào cột C. Cho phép dùng thêm cột B làm cột phụ chứa công thức tính số ký tự của tên. Giả sử tên tối thiểu là 1 ký tự và tối đa là 8 ký tự tính luôn cả ký tự dấu tiếng Việt. Họ và tên không có khoảng trống thừa (đã dùng hàm TRIM để xử lý). Phía dưới là các phần giải đáp tôi cố tình ghi ngược thứ tự thực hiện để trình bày từ dễ đến khó.
Giải đáp công thức tách tên (giả sử đã biết số ký tự của phần tên trong cột B):=RIGHT(A2,B2)

Giải đáp công thức tách họ (giả sử đã biết số ký tự của phần tên trong cột B):=LEFT(A2,LEN(A2)-B2-1)

Giải đáp công thức tính số ký tự của tên (phần dài dòng nhất):=IF(LEFT(RIGHT(A2,2))=" ",1, ... )
Copy toàn bộ hàm IF dán vào dấu ... và tăng các giá trị số lên 1 đơn vị cho tới khi có 7 hàm IF lồng vào nhau. Lúc đó thay dấu ... bằng số 8 là xong. Sau khi hoàn thành công thức có dạng:
=IF(LEFT(RIGHT(A2,2))=" ",1,IF(LEFT(RIGHT(A2,3))=" ",2,IF(LEFT(RIGHT(A2,4))=" ",3,IF(LEFT(RIGHT(A2,5))=" ",4,IF(LEFT(RIGHT(A2,6))=" ",5,IF(LEFT(RIGHT(A2,7))=" ",6,IF(LEFT(RIGHT(A2,8))=" ",7,8)))))))


Chúc các bạn mệt mỏi vì công thức này! :D (Thật ra nếu các bạn nắm được ý tưởng chính từ 3 công thức ở trên thì việc tách tên này vô cùng đơn giản). Xin nhờ các bạn một việc! Các bạn tìm giúp tôi xem có cái tên tiếng Việt có dấu của người Kinh nào mà dài hơn 8 ký tự không! Vì nếu có thì công thức trên của tôi chỉ dùng để làm bài tập thôi chứ không ứng dụng thực tiễn được! Thực tế thì tôi chưa gặp rắc rối khi sử dụng! Công thức này tôi nghĩ ra cách đây 5 năm nhờ vào lời thách thức của học trò tôi! Bạn nào có cùng ý tưởng xin thông cảm nhé! :)

Hay quá, nhưng mình thử thì máy chỉ chấp nhận có 6 IF lồng, qua cái thứ 7 bị lỗi, các bạn có bị tình trạng đó không

nationpham
18-11-2007, 22:54
Cám ơn bạn đã thử. Trong hướng dẫn về hàm IF, Mircrosoft cũng có nói là cho phép lồng 7 hàm If vào nhau (Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests). Tôi nhớ là trước đây tôi dùng Office97 thì không có lỗi gì nhưng quả thật bây giờ tôi thử lại trên Office 2003 thì có lỗi. Có lẽ là chỉ lồng được 6 hàm If vào thôi! Cám ơn ơn bạn rất nhiều!

langkhach008
19-11-2007, 07:44
liên quan đến họ tên tôi xin hỏi luôn: khi nhập tên vào (ví dụ danh sách HS) tôi thường phải dùng phím shift hoặc Capslock nhiều lần để tạo một tên có chữ viết đầu là chữ in hoa,ví dụ: Nguyễn Văn A. thì thấy nmất nhiều time wá, ai bít cách nào nhanh giúp em. thanks

nhd1973
19-11-2007, 09:35
Bạn dùng thử hàm PROPER() NHÉ.
Ví dụ: Ô A1 bạn gõ chữ thường nguyễn văn a
sau đó dùng =Proper(A1)
Chúc bạn thành công

hoangduc
19-11-2007, 11:36
liên quan đến họ tên tôi xin hỏi luôn: khi nhập tên vào (ví dụ danh sách HS) tôi thường phải dùng phím shift hoặc Capslock nhiều lần để tạo một tên có chữ viết đầu là chữ in hoa,ví dụ: Nguyễn Văn A. thì thấy nmất nhiều time wá, ai bít cách nào nhanh giúp em. thanks

Bạn xem bài viết này nha ở trang 1 đó !

Cái này tôi thấy thầy Trung ở Bình Dương đã tạo một Add-in, chi cần chạy setup là nó tích hợp vào Excel ngay, tao một nút lệnh trên thanh tiêu đề, sử dung rất thuận tiện, ngoài ra nó còn có thể xếp thứ tự Tên, Chữ lót, Họ theo yêu cầu của người dùng, xin load lên để ai cần thì tải về giải nén và sử dụng nhé. Nhắc lại của thầy Phạm văn Trung đó.

....

nguyệt Ngô
19-11-2007, 14:19
Mình đã làm được như hướng dẫn nhưng mà Font chữ bị đổi không đúng theo ý mình. Bạn chỉ luôn cho mình với. Cám ơn nhiều.

hoangduc
19-11-2007, 15:48
Mình đã làm được như hướng dẫn nhưng mà Font chữ bị đổi không đúng theo ý mình. Bạn chỉ luôn cho mình với. Cám ơn nhiều.

Gửi đến bạn bản hoàn chỉnh + hướng dẫn sử dụng của tác giả Phạm Văn Trung

File download : 8026


.....

langkhach008
19-11-2007, 18:41
Gửi đến bạn bản hoàn chỉnh + hướng dẫn sử dụng của tác giả Phạm Văn Trung

File download : 8026


.....

Quá tuyệt. Cám ơn bạn nhiều!! Rất dễ sử dụng.

surau
21-06-2008, 19:22
Nếu sử dụng tiện ích miễn phí thì khỏe ru rồi còn nói làm gì. Cái này là ta đang học hỏi, bàn luận giải quyết vấn đề mà ...

haquocvan
22-06-2008, 07:54
Tổng kết công thức tách họ và tên
A2: chứa họ và tên đầy đủ (có thể dư khoảng trắng)
B2: tách họ và tên đệm
=IF(ISERROR(FIND(" ",TRIM(A2),1)),"",LEFT(TRIM(A2),FIND("#",SUBSTITUTE(TRIM(A2)," ","#",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1))
C2: tách tên
=IF(ISERROR(FIND(" ",TRIM(A2),1)),"",RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("#",SUBSTITUTE(TRIM(A2)," ","#",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))))
D2: chỉ chứa tên đệm
=IF(ISERROR(FIND(" ",TRIM(A2),1)),"",IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))<2,"", MID(TRIM(A2),FIND(" ",TRIM(A2))+1,FIND("#",SUBSTITUTE(TRIM(A2)," ","#",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-FIND(" ",TRIM(A2))-1)))

surau
24-06-2008, 15:45
Cám ơn bạn haquocvan tổng kết công thức, nhưng có 2 chỗ cần bàn sau :
1/ Ô B2 công thức tôi copy và dán vào thử thì bị báo lỗi #Name, thì ra do hàm SUBTITUT E dư 1 khoảng trắng --> SUBTITUTE.
2/ Công thức ở ô B2 và Ô C2 giống nhau, không lấy ra tên mà chỉ lấy họ và lót thôi.

haquocvan
24-06-2008, 21:14
Cám ơn thầy surau góp ý, mình đã sửa lại. Riêng có một chỗ "SUBTITUT E" thì mình không sửa được, nhập lại không có khoảng trắng nhưng vẫn hiện lên khoảng trắng vô duyên kia?

hoacofer
27-01-2009, 18:45
Cách trích Họ và Tên trong chuỗi HOTEN
Cần các hàm : Left, right, trim, substitute,len
Giả sử trong sheet như sau: cột A chứa danh sách gồm họ và tên, cột B trung gian xử lý từ cột A (thay 1 khoảng trống bằng 6 hoặc 7 khoảng trống), cột C xử lý từ cột B bằng hàm Left, cột D xử lý từ cột B bằng hàm right
..........A.........................B............. ......................C............... D
1....HOTEN.................HOTEN2................. ..........HO...............TEN
2 ...Phan Thao............Phan.....Thao...................Ph an............ THAO
3...Chu Duy Tân.........Chu.......Duy......Tân.........Chu.. .............Tan

Ý tưởng : Từ tiếng Việt dài tối đa 7 ký tự, do đó biến đổi chuỗi HOTEN sao cho chúng cách xa nhau khoảng 7 ký tự (Xem ở cột HOTEN2), từ đó ta có thể dùng Left (HOTEN2,7) để lấy HỌ, dùng right(HOTEN2,7) để lấy tên, nếu có khoảng trống dư thừa thì dùng thêm hàm trim
B2=SUBSTITUTE(A2," ","..........")
(Thay 1 khoảng trống bằng 6 khoảng trống_Do trình duyệt tự bỏ các khoảng trống nên tạm thay bằng dấu .)
C2=TRIM(LEFT(B2,7))
D2=TRIM(RIGHT(B2,7))
Nếu không dùng cột B là cột trung gian, ví dụ
........A......................B.................. ....C
21 HOTEN.................. HO................. TEN
22 Phan Thao............Phan...............THAO
23 Chu Duy Tân.........Chu.................Tan

B21=LEFT(A34,LEN(A34)-LEN(TRIM(RIGHT(SUBSTITUTE(A34," "," "),7)))-1)
C21=TRIM(RIGHT(SUBSTITUTE(A34," ","......."),7))
(xem file đính kèm)
Một chút đóng góp_ Mong được lĩnh hội thêm

hoacofer
28-01-2009, 09:37
Xin lỗi, tính chèn thêm table mà không được

trinhquangminh
13-12-2013, 19:36
=TRIM(RIGHT(SUBSTITUTE(A21," "," "),7))

=RIGHT(B5,LEN(B5) - FIND("#",SUBSTITUTE(B5," ","#",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))



=IF(ISERROR(FIND(" ",TRIM(B5),1)),"",RIGHT(TRIM(B5),LEN(TRIM(B5)) -FIND("#",SUBSTITUTE(TRIM(B5)," ","#",LEN(TRIM(B5))-LEN(SUBSTITUTE(TRIM(B5)," ",""))))))





Mình có tổng hợp trong file đính kèm:
https://www.mediafire.com/?uov9tvdmodk258q