Với bảng tính gồm danh sách tên và mong muốn có thể tiến hành lọc theo Họ/Tên/Tên đệm thì thực hiện như thế nào? Dưới đây, Kế Toán VN sẽ hướng dẫn bạn cách để làm sạch dữ liệu danh sách tên, tách tên gốc thành các phần Họ/Tên Đệm/Tên và sử dụng hàm FILTER hoặc hàm SORT để tiến hành lọc theo tên:
Bước 1. Làm sạch dữ liệu họ tên trong Excel
Trước khi bắt đầu vào tiến hành sử dụng bộ lọc để tìm theo tên, ta cần phải làm sạch dữ liệu trước. Ví dụ như danh sách họ và tên nhân viên mà chứa nhiều khoảng trắng thừa, viết hoa, viết thường không đúng quy chuẩn như bảng dữ liệu dưới đây:
Để tiến hành đưa dữ liệu danh sách họ và tên trên đúng ta sẽ:
- Tiến hành lọc khoảng trắng thừa và dọn sạch dữ liệu bằng sử dụng hàm CLEAN và TRIM như sau:
=TRIM(CLEAN(A2))
Sau đó ta tiến hành quy chuẩn viết hoa chữ cái đầu cho họ và tên với hàm Proper như sau: =PROPER(TRIM(CLEAN(A2)))
Xem ngay File thực hành hàm lọc theo tên trong Excel
Bước 2: Tách Họ/Tên Đệm/Tên trong Excel
Sau khi dữ liệu tên đã được dọn sạch ở bước 1, để tiến hành tạo bộ lọc theo tên mong muốn. Ta sẽ tiến hành tạo 3 cột phụ gồm Họ/Tên Đệm/Tên để tách Họ Và Tên thành các thành phần tương ứng để lát sử dụng cho mục đích lọc ở bước 3.
Nguyên tắc tách như sau:
- Họ là từ đầu tiên.
- Tên là từ cuối cùng.
- Tên đệm là phần còn lại giữa họ và tên.
Để thực hiện tách ta sẽ sử dụng các hàm với công thức như sau:
1. Công thức tách Họ
Giả sử ô C2 chứa Họ và Tên đã tiến hành làm sạch và viết hoa chữ cái đầu ở bước 1. Ta tiến hành tách phần Họ ra bằng công thức sau:
=IF(C2="","",IFERROR(LEFT(C2,SEARCH(" ",C2)-1),C2))
Trong đó:
IF(C2="","", … ): Nếu ô C2 rỗng → trả rỗng; ngược lại thực hiện phần còn lại.SEARCH(" ",C2): Tìm vị trí dấu cách đầu tiên trong chuỗi C2. (SEARCH không phân biệt hoa/thường; nếu không có dấu cách → lỗi.)LEFT(C2, SEARCH(" ",C2)-1): Lấy ký tự từ trái sang đến ngay trước dấu cách đầu tiên → chính là Họ.IFERROR( … , C2): Nếu SEARCH lỗi (tức không có dấu cách, tên chỉ có 1 từ) → trả luôn toàn bộ C2 làm Họ.
2. Công thức tách Tên
Để tách Tên từ họ và tên đầy đủ ở ô C2 ta sử dụng công thức sau:
=IF(C2="","",
TRIM(
RIGHT(
C2,
LEN(C2) - IFERROR(FIND("@",SUBSTITUTE(C2," ","@",LEN(C2)-LEN(SUBSTITUTE(C2," ","")))),0)
)
)
)
Trong đó:
IF(C2="","", … ): để trả về giá trị rỗng khi ô C2 không có giá trị.- Để tìm Tên (tức là ta sẽ đi lấy các ký tự sau dấu cách cuối cùng trong chuỗi):
LEN(C2)-LEN(SUBSTITUTE(C2," ",""))→ đếm số dấu cách trong C2.SUBSTITUTE(C2," ","@", <thứ tự>)→ thay dấu cách thứ N (N là số đếm ở trên) bằng ký tự đánh dấu@.FIND("@", …)→ tìm vị trí@đó ⇒ vị trí của dấu cách cuối.IFERROR( … , 0)→ nếu không có dấu cách (chuỗi 1 từ) thì cho “vị trí” = 0 để công thức vẫn chạy.
- Sau khi biết bị trí dấu cách cuối cùng, ta cắt phần tên sau dấu cách:
LEN(B2) - p→ số ký tự từ sau p đến hết chuỗi.RIGHT(B2, LEN(B2)-p)→ lấy đoạn bên phải → chính là Tên.TRIM( … )→ loại khoảng trắng thừa phòng rủi ro.
3. Công thức tách Tên Đệm
Để tách phần tên đệm, ta sử dụng tư duy sau: Tên đệm là tất cả ký tự giữa dấu cách đầu tiên và dấu cách cuối cùng. Nếu họ tên không có đệm (tức < 2 dấu cách), trả về chuỗi rỗng.
Công thức:
=IF(LEN(C2)-LEN(SUBSTITUTE(C2," ",""))<2,"",
MID(C2,
FIND(" ",C2)+1,
FIND("♦",SUBSTITUTE(C2," ","♦",LEN(C2)-LEN(SUBSTITUTE(C2," ",""))))
- FIND(" ",C2) - 1
)
)
Trong đó:
- Kiểm tra xem có tên đệm hay không:
LEN(C2)-LEN(SUBSTITUTE(C2," ",""))để đếm số dấu cách trong C2.- Lấy độ dài gốc trừ độ dài sau khi xóa hết khoảng trắng ⇒ ra số lượng khoảng trắng.
- Nếu <2 (tức chỉ có 0 hoặc 1 dấu cách → 1–2 từ) ⇒ không có đệm →
IF(...,"", ...)trả về “”.
- Xác định biên trái (sau họ)
FIND(" ",C2)tìm vị trí dấu cách đầu tiên.FIND(" ",C2)+1là vị trí bắt đầu của đoạn đệm (ngay sau họ).
- Xác định biên phải (trước tên):
- Mẹo gắn “mốc” vào dấu cách cuối cùng:
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))= số dấu cách = N.SUBSTITUTE(B2," ","♦", N)thay dấu cách thứ N (tức dấu cách cuối) thành ký tự đánh dấu ♦.FIND("♦", ... )trả về vị trí “mốc” này ⇒ chính là vị trí dấu cách cuối cùng (đã biến thành ♦).- Như vậy
FIND("♦",SUBSTITUTE(...))là biên phải, còn biên trái làFIND(" ",B2).
- Cắt phần tên đệm:
- Dùng
MID(text, start, num_chars): - start =
FIND(" ",B2)+1 - num_chars =
vị_trí_mốc_cuối - vị_trí_cách_đầu - 1
- Dùng
Bước 3: Tiến hành lọc theo tên với hàm FILTER và SORT
Sau khi hoàn thành bước 2 ta đã có 1 cột phụ chứ Tên, lúc này ta sẽ sử dụng hàm FILTER và hàm SORT với từng trường hợp như sau:
TH1: Lọc tất cả giá trị theo tên (không phân biệt chữ hoa/thường)
Giả sử ta cần tìm tất cả giá trị theo người tên “Anh“, lúc này ta sẽ tạo một vùng để đặt điều kiện tiêu chí lọc. Ví dụ ô H2 ta nhập “Anh“. Lúc này ta sử dụng công thức sau:
=IF(H2="","",FILTER(C2:F1000,LOWER(E2:E1000)=LOWER(H2), "Không tìm thấy"))
Trong đó:
IF(H2="","", … )dùng để đặt điều kiện nếu H2 trống thì công thức trả về rỗng “”, ngược lại sẽ chạy hàm FILTER.FILTER(C2:F1000, điều_kiện, [nếu_không_tìm_thấy])dùng để trả về tất cả các hàng vùngC2:F1000thỏa mãn điều kiệnLOWER(E2:E1000)=LOWER(H2):- LOWER(…) chuyển chữ hoa → thường để so sánh không phân biệt hoa/thường.
- Vế trái là mảng (toàn bộ cột Tên), vế phải là một ô (H2). Excel sẽ “so khớp từng dòng” và trả về mảng TRUE/FALSE cho FILTER.
"Không tìm thấy"dùng để trả về kết quả nếu không dòng nào đúng.
Với công thức lọc trên, Excel sẽ tiến hành Lọc ra các hàng từ vùng C2:F1000 với điều kiện lọc là cột Tên (vùng E2:E1000) bằng giá trị nhập ở H2 (so sánh không phân biệt hoa/thường). Nếu H2 trống → trả về rỗng. Nếu không có kết quả → trả đúng chuỗi “Không tìm thấy" thay vì lỗi #CALC!.
TH2: Lọc theo tên có chứa từ khóa
Nếu muốn tìm kiếm tất cả danh sách họ và tên có chứa từ “Minh” thì ta sẽ cũng sẽ đặt 1 vùng làm điều kiện. Ví dụ như tại ô H8 ta nhập “Minh” và sử dụng lọc bằng công thức sau:
=IF(H8="","",FILTER(C2:C1000, ISNUMBER(SEARCH(H8, B2:B1000)), "Không có"))
Trong đó:
IF(H8="","", … )giúp chỉ chạy hàm lọc FILTER khi ô H8 chứa giá trị.FILTER(C2:C1000, điều_kiện, "Không có")giúp lấy các hàng trong cột C ứng với dòng điều kiện cho kết quả TRUE.SEARCH(H8, B2:B1000)giúp tìm vị trí xuất hiện của chuỗi trong H8 nằm trong từng ô của cột B. Do đó, nếu chứa từ “Minh” thì kết quả sẽ trả về 1 số vị trí.ISNUMBER( … )dùng để chuyển kết quảSEARCH(H8, B2:B1000)thành dạng TRUE/FALSE để làm điều kiện cho FILTER.
TH3: Danh sách tên duy nhất theo thứ tự
Với cột dữ liệu tên ở trên, ta cũng có thể sử dụng hàm SORT để tiến hành tạo danh sách tên duy nhất có sắp xếp với công thức sau:
=SORT(UNIQUE(E2:E1000, TRUE, TRUE))
Trong đó:
UNIQUE(E2:E1000, TRUE, TRUE)dùng để:- Trả về danh sách giá trị “duy nhất” trong cột E (tức là các giá trị xuất hiện đúng 1 lần trong E2:E1000).
- Tham số 2 = TRUE (by_col): so sánh theo cột thay vì theo hàng. Với một cột đơn như E2:E1000 thì giá trị này không khác FALSE — có thể bỏ qua, chỉ là dư nhưng không gây lỗi.
- Tham số 3 = TRUE (exactly_once): chỉ giữ các giá trị xuất hiện chính xác 1 lần; mọi giá trị lặp từ 2 lần trở lên sẽ bị loại bỏ.
SORT( … )dùng để sắp xếp kết quả của UNIQUE theo tăng dần (mặc định).
Trên đây là hướng dẫn về cách để lọc theo tên trong Excel. Ta có thể áp dụng cách này với hàm FILTER để tiến hành lọc các giá trị trong bảng theo tên của một người. Chúc các bạn áp dụng thành công!
