Lớp học GPE tháng 10 - TPHCM: Conditional Formatting và Data Validation (tối 4, 6/10) | Excel cơ bản (tối 9, 11, 13/10) |
Thuần thục các hàm dò tìm (tối 10, 12/10) | Tất tần tật về PivotTable (tối 16, 18, 20/10) |
Tất tần tật về Filter và Advanced Filter (tối 23, 25/10) | Name động và biểu đồ (tối 24, 26, 28/10)

Đăng ký học Khởi đầu cùng Google Spreadsheet - 2 chủ nhật 1 và 8/10 - TPHCM

Đăng ký học Xây dựng ứng dụng Form bằng VBA - 2 chủ nhật 15 và 22/10 - TPHCM

Các phương pháp tách họ và tên đề nghị post ở đây

Thảo luận trong 'Hàm và công thức Excel' bắt đầu bởi daerty5, 7 Tháng chín 2006.

  1. daerty5

    daerty5 Thành viên mới

    Khi nhập liệu vào bảng tính Excel, thông thường ta nhập cả họ và tên vào cùng một ô. Tuy nhiên, có khi sau đó ta cần phải tách riêng tên ra khỏi họ và tên đệm, nếu làm thủ công bằng thao tác sao chép rồi dán qua ô khác thì sẽ rất bất tiện. Ngoài ra có những lúc bạn phải xắp xếp lại theo thứ tự A,B,C,...
    Nếu sử dụng hàm Right kết hợp với một vài hàm khác thì cũng có thể cho ra kết quả như bạn muốn, nhưng trong một số trường hợp sẽ cho kết quả không chính xác. Có một phương pháp tối ưu hơn sẽ giúp bạn hoàn thành tốt công việc này, cách thực hiện cũng khá đơn giản như sau:
    - Trên bảng tính chứa cột họ tên cần tách, bạn chọn menu Tools > Macro > Visual Basic Editor, lập tức xuất hiện chương trình Microsoft Visual Basic.
    - Tiếp theo, bạn nhấn menu Insert và chọn Module sẽ xuất hiện một cửa sổ soạn thảo.
    Mã:
    Private Function Tachten(ten As String, lg As Integer)
    Dim j As Integer
         Name = Trim(ten)
      For j = Len(Name) To 1 Step -1
        If Mid(Name, j, 1) = " " Then
           If lg = "1" Then
              Tachten = Right(Name, Len(Name) - j)
           Else
              Tachten = Left(Name, j)
           End If
      Exit For
        End If
      Next
    End Function 
    Bạn nhập đoạn mã lệnh sau vào cửa sổ soạn thảo:

    - Bạn nhấn Ctrl+S để lưu lại và quay trở về bảng tính Excel.
    - Bây giờ để tiến hành tách riêng họ và tên đệm vào một ô mới, bạn đặt con trỏ tại ô mới chứa họ và tên đệm rồi nhập vào công thức =TachTen(B3,0) và nhấn Enter sẽ thấy ngay kết quả.
    - Để tách riêng tên vào một ô mới, bạn đặt con trỏ tại ô sẽ chứa tên cần tách và nhập vào công thức =TachTen(B3,1) rồi nhấn Enter là xong.
    Ghi chú: B3 chính là địa chỉ của ô ban đầu chứa cả họ, tên đệm và tên.


    Có mấy góp ý sau :
    - Bạn nên hướng dẫn mọi người cách sử dụng, hàm này có tác dụng như thế nào ??
    - Việc khai báo các biến cần đầy đủ và tường minh hơn
    - Dòng : CatTen = "" là thừa, biến l là thừa
    - Hãy bẫy lỗi khi HoVaTen = ""
    - Nếu hàm này chỉ cắt mỗi tên thôi thì e rằng . . phí quá. hãy cho cái Option để có thể lấy HỌ - TÊN ĐỆM - TÊN
    - Trên GPE có các hàm như thế này rất nhiều rồi và đã tối ưu, bạn nên tham khảo nhé.

    --Chúc vui--
     

    Các file đính kèm:

    • a.xls
      Kích thước:
      15 KB
      Đọc:
      3,399
    Chỉnh sửa lần cuối bởi điều hành viên: 12 Tháng mười hai 2009
  2. chibi

    chibi Thành viên danh dự

    Bạn tham khảo ví dụ sau
     
  3. vungoc

    vungoc Thành viên tiêu biểu

    Tách họ và tên

    ĐÂY LÀ CÁCH TÔI ĐÃ HỌC ĐƯỢC TRÊN DIỄN ĐÀN VÀ ĐANG THỰC HIỆN, XIN CHIA SẺ CÙNG BẠN:

    Từ file EXCEL có chứa dữ liệu bạn đang muốn tách họ và tên - bạn hãy nhấn Alt + F11 sau đó nhấn insert nhấn module dán đọan mã sau vào trong cửa sổ module:

    Private Function Tachten(ten As String, lg As Integer)
    Dim j As Integer
    Name = Trim(ten)
    For j = Len(Name) To 1 Step -1
    If Mid(Name, j, 1) = " " Then
    If lg = "1" Then
    Tachten = Right(Name, Len(Name) - j)
    Else
    Tachten = Left(Name, j)
    End If
    Exit For
    End If
    Next
    End Function


    SỬ DỤNG HÀM TÍNH TÁCH HỌ VÀ TÊN:
    Giả sử bạn muốn tách tên ra khỏi họ và họ đệm tại ô A1 = Phạm Xuân Trường / Gõ = tachten(A1,0) / Kết quả: Phạm Xuân
    Giả sử bạn muốn tách tên ra khỏi họ và họ đệm tại ô A1 = Phạm Xuân Trường / Gõ = tachten(A1,1) / Kết quả: Trường


    (Nếu muốn sử dụng hàm này cho tòan excel bạn hãy đọc thêm trên diễn đàn này)

    Chúc bạn thành công !
     
    lê sơn trà, Mão Thảoliemslt thích bài viết này.
  4. phamduylong

    phamduylong Thành viên danh dự

    Hàm vả thủ tục tách họ tên

    Họ tên thường nhập chung 1 cột cho nhanh. Sau đó ta có thể tách riêng tên ra 1 cột. Nhưng có lúc chúng ra cần tách ra 2 cột: cột họ, cột tên. Đây là 2 làm làm công việc đó:
    '======================
    Function TachHo(hoten As String) As String
    hoten = Trim(hoten)
    If hoten = "" Then
    TachHo = ""
    Else
    vt = InStrRev(hoten, " ", Len(hoten))
    If vt = 0 Then
    TachHo = ""
    Else
    TachHo = Trim(Mid(hoten, 1, vt))
    End If
    End If
    End Function

    '====================
    Function TachTen(hoten As String) As String
    hoten = Trim(hoten)
    If hoten = "" Then
    TachTen = ""
    Else
    vt = InStrRev(hoten, " ", Len(hoten))
    If vt = 0 Then
    TachTen = hoten
    Else
    TachTen = Mid(hoten, vt + 1)
    End If
    End If
    End Function

    '===================
    Bạn sử dụng hàm này bình thường như các hàm khác của Excel, nhưng nếu bạn cần tách họ tên thành 2 cột riêng biệt thì bạn phải thực hiện một loạt các thao tác sau:
    1. tách họ, tách tên trên 2 cột phụ
    2. chèn thêm cột bên trái cột họ tên
    3. Copy 2 cột họ tên mới tách và dán bằng Paste Special - Value trở về nơi cũ.
    Để làm nhanh các công việc đó, bạn có thể sử dụng thủ tuc TachHoTen.
    Sử dụng thủ tục này như sau:
    1. Chọn tất cả các ô chứa họ tên, kể cả ô chứa tiêu đề trên đầu.
    2. Chạy Sub TachHoTen.
    VBA sẽ làm thay cho bạn các việc còn lại. Lưu ý bạn là số cột trong vùng chọn phải là 1, nếu lớn hơn 1 VBA không thực hiện.

    '====================
    Sub TachHoTen()
    rd = Selection.Row
    sr = Selection.Rows.Count
    rc = rd + sr - 1
    c = Selection.Column
    sc = Selection.Columns.Count
    If sc > 1 Then
    MsgBox "Ban chon " & sc & " cot. Ban phai chon lai 1 cot", vbOKOnly, "Thong bao"
    Exit Sub
    End If
    Range(Cells(rd, c), Cells(rc, c)).Insert Shift:=xlToRight
    Range(Cells(rd, c), Cells(rc, c)).Insert Shift:=xlToRight
    For r = rd To rc
    Cells(r, c) = TachHo(Cells(r, c + 2))
    Cells(r, c + 1) = TachTen(Cells(r, c + 2))
    Next
    Range(Cells(rd, c + 2), Cells(rc, c + 2)).Delete Shift:=xlToLeft
    End Sub
    '==============
    Bạn có thể tham khảo trong tập tin TachHoTen.zip
     

    Các file đính kèm:

  5. chibi

    chibi Thành viên danh dự

    1- Khởi động EXCEL
    2- Vào VBE bằng cách Alt+F11
    3- Insert Modul
    4- Nhập đoạn code trên
    5- Nếu cần phong cấp cho hàm thì Save với File type là AddIns, sau đó dùng chức năng Tools->AddIns để khai báo sử dụng trên mọi Workbook.
    6- Sử dụng: =tach_ten(hoten) - Họ tên có thể nhập trực tiếp hoặc địa chỉ ô chứa họ tên.
     
  6. Nguyễn Duy Tuân

    Nguyễn Duy Tuân Nghị Hách

    Ai không biết VBA (lập trình) thì làm theo tôi như sau:

    Giả sử A1 chứa "Họ và Tên".

    Lấy Họ:
    =LEFT(A1,LEN(A1)-FIND(" ",MID(A1,LEN(A1),1)&MID(A1,LEN(A1)-1,1)&MID(A1,LEN(A1)-2,1)&MID(A1,LEN(A1)-3,1)&MID(A1,LEN(A1)-4,1)&MID(A1,LEN(A1)-5,1)&MID(A1,LEN(A1)-6,1)&MID(A1,LEN(A1)-7,1)))

    Lấy Tên:
    =RIGHT(A1,FIND(" ",MID(A1,LEN(A1),1)&MID(A1,LEN(A1)-1,1)&MID(A1,LEN(A1)-2,1)&MID(A1,LEN(A1)-3,1)&MID(A1,LEN(A1)-4,1)&MID(A1,LEN(A1)-5,1)&MID(A1,LEN(A1)-6,1)&MID(A1,LEN(A1)-7,1))-1)
     
  7. tedaynui

    tedaynui (*_*)

    Chào các bạn.
    Bạn có thể tách họ và tên theo cách sau.
    1/ Đầu tiên bạn chọn vùng dữ liệu
    2/ Sau đó vào menu Data / Text to Columns.
    3/ Cửa sổ Step 1 of 3, bạn chọn Delimited, rồi chọn Next.
    4/ Ở Step 2 of 3, tại mục Delimiters chọn Space rồi Next.
    5/ Trong Step 3 of 3, ở hộp Destination bạn nhập địa chỉ để cho ra kết quả. Cuối cùng nhấn Finish để xem kết quả.

    Thân !
     
  8. phamduylong

    phamduylong Thành viên danh dự

    Cách này chỉ áp dụng được với số từ của họ tên bằng nhau. Họ tên ít nhất 2 từ, nhiều nhất 7, 8 từ hoặc hơn. Nếu trong danh sách có một người có tên 8 từ, Excel tách ra 8 cột. Nhặt tên ra khùng luôn !
     
  9. tedaynui

    tedaynui (*_*)

    Chào các bạn.
    Để không dùng VBA, mình có vài công thức các bạn test thử thế nào nhé.

    Giả sử ô A1 có chứa "Họ Chữ lót Tên"
    Lấy Họ :
    =LEFT(A4,FIND(" ",A4,1))

    Lấy Họ và Chữ lót :
    =LEFT(A4,FIND("*",SUBSTITUTE(A4," ","*",LEN(A4)-LEN(SUBSTITUTE(A4," ",""))))-1)

    Lấy Tên :
    =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))


    Thân !
     
  10. dvu58

    dvu58 Thành viên thường trực


    Tôi nghĩ, cách làm của bạn tedaynui cũng hay nhưng chỉ dùng cho các trường hợp đơn giản, thông thường thôi. Chứ nếu tui có 1 danh sách hàng vài nghìn tên với độ dài khác nhau, nhiều cột mục mà áp dụng cách này bảng tính của tôi trở thành "râu tóc te tua, xồm đầu" và xin bái.... bai đấy!

    Nếu muốn sort một danh sách cho dễ dàng, việc tách họ và tên là rất cần, theo tôi vẫn nên bằng cách nào đó phải có 1 đoạn code tiện ích như các bạn đã giới thiệu để có thể dùng thường xuyên, nhanh chóng, tiện lợi.
    Tôi đề nghị các bạn nghiên cứu và bổ sung thêm sao cho đoạn mã này tự động chạy cái "rẹt!" từ đầu đến cuối danh sách, chứ mà ngồi mổ cò cho hết cái công thức trường giang đại hải hoặc mỗi lần thực hiện nó thì phải giở "Tự điển GPE" này ra tra (chứ nhớ sao nổi?) thì "chết toi em" luôn.
     
    Lần chỉnh sửa cuối: 2 Tháng sáu 2007
  11. pxthanhcda

    pxthanhcda Thành viên mới

    Lấy họ:=IF(MID(A1,2,1)=" ",LEFT(A1,2),IF(MID(A1,3,1)=" ",LEFT(A1,2),IF(MID(A1,4,1)=" ",LEFT(A1,3),IF(MID(A1,5,1)=" ",LEFT(A1,4),IF(MID(A1,6,1)=" ",LEFT(A1,5),IF(MID(A1,7,1)=" ",LEFT(A1,6),LEFT(A1,7)))))))
    Lấy tên đệm:=IF(LEN(A2)-LEN(B2)-LEN(D2)-2>0,MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2)," ")
    Lấy tên thật:=IF(MID(A3,LEN(A3)-1,1)=" ",RIGHT(A3,2),IF(MID(A3,LEN(A3)-2,1)=" ",RIGHT(A3,2),IF(MID(A3,LEN(A3)-3,1)=" ",RIGHT(A3,3),IF(MID(A3,LEN(A3)-4,1)=" ",RIGHT(A3,4),IF(MID(A3,LEN(A3)-5,1)=" ",RIGHT(A3,5),IF(MID(A3,LEN(A3)-6,1)=" ",RIGHT(A3,6),RIGHT(A3,7)))))))
     
  12. BumBum

    BumBum Thành viên mới

    Cách dùng này rất hay đó các bạn, phần lấy tên rất OK.
    Mình bổ sung thêm phần lấy họ và lót (sau khi đã lấy được tên) thì làm như sau cho gọn:
    =LEFT(A1,LEN(A1)-LEN(ô chứa tên)-1)
     
  13. tedaynui

    tedaynui (*_*)

    Chào bạn
    Bạn tìm trên diễn đàn có rất nhiều bài nói về vấn đề này.
    Có thể dùng VBA hoặc dùng hàm. Dùng hàm cũng có rất nhiều cách và đây là 1 trong các cách :
    VD : Ô A2 chứa chuỗi họ và tên
    Họ lót ==LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)
    Tên ==RIGHT(A2,LEN(A2)-LEN(B2)-1)

    TDN
     
  14. dmtdmtbb

    dmtdmtbb Biệt danh: xDelx

    Ok có ngay !!!

    - Tải cái này về và xem hướng dẫn kèm theo để cài đặt.
    - Chạy ếch xèo lên vào Format -> TiengViet-TCVN -> Tach gop ho ten.
    - Chý ú : Insert thêm 1 cột để phần tên tách ra không đè lên cột bên cạnh.

    View attachment 2384
     
    Lần chỉnh sửa cuối: 3 Tháng bảy 2007
  15. Tu Anh

    Tu Anh Thành viên mới

    Macro tách chuỗi

    Bạn dùng thử Macro mình làm xem. Cái này có thể tách chuỗi bất kỳ bằng cách chọn ký tự làm mốc tách. Chú ý trong ô chọn mốc tách chỉ đánh 1 ký tự thôi nhé ví dụ +, -, " " (Ký tự trống), â, ô....)*&^))*&^)


    !$@!!!$@!!!$@!!!$@!! Ê mọi người đừng tải file ở đây nữa, nghe nói có virus. Chuyển sang cuối trang 2 tải file khác mình đã tải lên rồi, nhớ kiểm tra kỹ virus, nếu cần nhắn tin cho mình mình gửi code cho.
     

    Các file đính kèm:

    Lần chỉnh sửa cuối: 12 Tháng bảy 2007
  16. tedaynui

    tedaynui (*_*)

    Cám ơn bạn
    Bạn làm giao diện tuyệt đẹp đấy nhưng có vài góp ý nhỏ :
    - Cái mặt cười màu vàng không thể hiệu lực trên máy khác, chỉ có hiệu lực trên máy của bạn vì nút đó bạn tự tạo và gán cho nó 1 macro
    - Sao dung lượng file gần 1Mb, có vẻ lớn đấy (vì data không có - nguyên nhân là do Form có chèn hình đã quá)
    - Khi tách chuỗi thì cần xử lý chuỗi luôn :
    Nguyễn___Thanh_Sơn --> Nguyễn_Thanh --- Sơn
    Cần bỏ bớt các khoảng trắng thừa.
    - Có Form đẹp và hoành tráng nhưng chỉ tách nội dung 1 ô, vậy có thể chỉnh lại để có thể tách hàng loạt không ?
    Thanks

    TDN
     
    Lần chỉnh sửa cuối: 2 Tháng bảy 2007
  17. Mr Okebab

    Mr Okebab Ngon Ngất Ngây

    File của bạn có virus và rất nhiều name lỗi. Vì vậy mình không kịp thấy bạn đã làm gì vì Anti nó diệt mất rồi.

    [​IMG]

    Mong gửi lên File sạch nhé.

    Thân!
     
  18. digita

    digita Guest

    Chào bạn

    Thí dụ ở ô A1 bạn có tên Trần Văn Xoài.

    Tách họ trong ô B1 thì dung =LEFT(A1,FIND(" ",A1)-1)

    Phần tên còn lại ghi qua C1 bằng phương thức =RIGHT(A1,LEN(A1)-LEN(B1)-1)

    Mến
     
    Chỉnh sửa lần cuối bởi điều hành viên: 3 Tháng bảy 2007
  19. TranThanhPhong

    TranThanhPhong Thời gian !!!

  20. Tu Anh

    Tu Anh Thành viên mới

    Xin lỗi các bạn nhé, máy mình biết là bị nhiễm virus nhưng đã sử dụng cá phần mềm diệt virus vẫn không diệt được. Mình cũng mới tham gia diễn đàn, hôm trước có việc cần tách chuỗi nên tập tọe làm thử, thấy có bạn hỏi nên post lên để mọi người tham khảo thôi. Mong mọi người tiếp tục chỉ bảo. Mình sẽ tìm cách sửa để có thể tách một loạt các ô chọn, nhưng mình bận đi làm ít khi về nhà để lên mạng nên chưa thể làm ngay được. Và mình cũng sẽ bỏ bớt các hình nền để file đỡ nặng. Cám ơn các bạn đã góp ý.
     

Chia sẻ trang này