Danh sách khách hàng gom từ form đăng ký, tin nhắn Zalo và file cũ thường có họ tên gõ lộn xộn, số điện thoại lẫn dấu chấm, ngày tháng lưu dạng chữ và khách bị nhập 2 lần. Bài viết này hướng dẫn bạn làm sạch dữ liệu trong Excel trên một danh sách khách hàng như vậy, theo từng bước, mỗi bước có ảnh chụp màn hình.
Bạn làm sạch dữ liệu theo 3 việc chính: gõ công thức làm sạch ở cột mới cạnh dữ liệu gốc, thay công thức bằng giá trị, rồi xóa dòng trùng. Ví dụ, ô B2 chứa " nguyễn văn an " có dấu cách thừa và chữ thường, công thức =PROPER(TRIM(B2)) trả về "Nguyễn Văn An". Sau khi làm sạch cả bảng, danh sách 9 dòng trong bài còn 8 khách hàng, vì một khách bị nhập 2 lần dưới 2 dạng khác nhau.
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Bảng gốc nằm ở cột A đến E, bảng sạch nằm ở cột G đến M. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy, ví dụ =LEFT(I2,1) đổi thành =LEFT(I2;1).
Làm sạch dữ liệu trong Excel gồm những bước nào?
Bài viết đi qua 9 bước theo thứ tự sau:
Bạn nhận biết dữ liệu bẩn và sao lưu bản gốc.
Bạn chuẩn hóa họ tên bằng
=PROPER(TRIM()).Bạn xóa dấu cách copy từ web và ký tự xuống dòng bằng SUBSTITUTE, CLEAN.
Bạn chuẩn hóa mã khách hàng bằng UPPER.
Bạn chuẩn hóa số điện thoại bằng SUBSTITUTE, IF, LEFT và kiểm tra bằng LEN.
Bạn đổi ngày và số đang lưu dạng chữ về ngày, số thật.
Bạn thay công thức bằng giá trị và xóa các cột không cần.
Bạn xóa dòng trùng bằng Remove Duplicates.
Bạn tách tên ra cột riêng bằng Flash Fill.
Thứ tự này quan trọng ở 2 điểm. Thứ nhất, bạn luôn làm ở cột mới và giữ cột gốc cho tới khi kiểm tra xong. Thứ hai, bạn làm sạch xong rồi mới xóa trùng. Hai dòng của cùng một khách chỉ giống hệt nhau sau khi đã bỏ dấu cách thừa, bỏ dấu chấm và đưa số về cùng một dạng.
Bước 1: Bạn nhận biết dữ liệu bẩn như thế nào?
Bảng gốc có 9 dòng khách hàng ở vùng A1:E10. Bạn nhìn từng cột để tìm các dấu hiệu bẩn trước khi gõ công thức.

Bảng này có các lỗi sau:
Cột Mã KH gốc có mã lúc viết hoa, lúc viết thường. Ô
A4vàA8còn có dấu cách ở cuối, mắt thường không thấy.Cột Họ tên gốc có dấu cách thừa ở đầu và giữa, chữ hoa, chữ thường lộn xộn. Ô
B4có một dấu cách lạ ở cuối do copy từ trang web, còn ôB7có một ký tự xuống dòng ở cuối. Hai ký tự này cũng không nhìn thấy.Cột Điện thoại gốc có số lẫn dấu chấm, dấu cách và đầu số +84. Ô
C4nằm sát lề phải vì Excel đã hiểu đây là số và bỏ mất số 0 ở đầu. ÔC7chỉ có 9 chữ số.Cột Ngày mua gốc nằm sát lề trái vì cả cột là chữ. Ngày thật trong Excel nằm sát lề phải.
Cột Doanh số gốc có ô nằm sát lề trái là chữ, ví dụ "1.250.000", và ô nằm sát lề phải là số. Nếu bạn gõ
=SUM(E2:E10)vào ôE12, Excel chỉ cộng 3 ô số và trả về 8,250,000 thay vì 18,180,000. Bạn xóa ôE12sau khi xem.Dòng 2 và dòng 8 là cùng một khách KH0158, nhưng Excel không coi là trùng vì 2 dòng khác nhau ở dấu cách, dấu chấm và dạng số.
Trước khi làm, bạn nên sao lưu sheet gốc: bạn nhấn chuột phải vào tên sheet, chọn Move or Copy, tích Create a copy rồi nhấn OK. Sau đó, bạn làm sạch ở các cột G đến M, không sửa thẳng lên cột gốc.
Bạn dựng lại bảng bẩn này để làm theo như thế nào?
Nếu bạn gõ lại bảng như bình thường, Excel tự đổi một phần dữ liệu và bạn sẽ không thấy đúng các lỗi trong bài. Bạn dựng bảng theo 4 bước sau:
Bạn chọn vùng
C2:E10, vào tab Home, ở ô định dạng số trong nhóm Number bạn chọn Text. Sau đó, bạn chọn riêng các ôC4,E3,E6,E8và đổi lại thành General. Ô định dạng Text giữ nguyên chữ bạn gõ, nên số 0 ở đầu, ngày "05/11/2026" và "1.250.000" không bị Excel đổi thành số. Bạn cũng có thể gõ dấu nháy đơn trước nội dung, ví dụ'05/11/2026.Bạn gõ dữ liệu theo ảnh ở trên. Ô
A4vàA8có thêm một dấu cách ở cuối. ÔB2có 2 dấu cách ở đầu, 3 dấu cách giữa "nguyễn" và "văn", 1 dấu cách ở cuối. ÔB6có 2 dấu cách giữa 2 chữ và ôB10có 1 dấu cách ở đầu.Bàn phím không gõ được dấu cách mã 160 và ký tự xuống dòng ở cuối ô, nên bạn tạo bằng công thức. Bạn gõ
="lê hoa"&CHAR(160)vào ôB4và="vũ thị mai"&CHAR(10)vào ôB7.Bạn gõ tiêu đề của bảng sạch vào vùng
G1:M1: Mã KH, Họ tên, ĐT bỏ ký tự, Điện thoại, Số ký tự, Ngày mua, Doanh số. Bạn chọn cột L, nhấn Ctrl + 1, chọn Custom rồi gõdd/mm/yyyy. Bạn chọn cột M, nhấn Ctrl + 1, chọn Custom rồi gõ#,##0.
Bước 2: Bạn chuẩn hóa họ tên bằng PROPER(TRIM()) như thế nào?
Bạn ghép 2 hàm để sửa họ tên:
Hàm TRIM xóa dấu cách ở đầu, ở cuối và chỉ giữ 1 dấu cách giữa các chữ. Ví dụ,
TRIM(" nguyễn văn an ")trả về "nguyễn văn an".Hàm PROPER viết hoa chữ cái đầu của mỗi chữ và viết thường các chữ cái còn lại.
Bạn chọn ô H2, gõ công thức sau rồi nhấn Enter:
=PROPER(TRIM(B2))

Tiếp theo, bạn đưa chuột tới chấm vuông ở góc dưới bên phải ô H2. Khi con trỏ thành dấu cộng đen mảnh, bạn kéo xuống ô H10.

Cột H đã đúng ở dấu cách và chữ hoa. Ô H4 trông như "Lê Hoa" nhưng vẫn chưa sạch, bước 3 cho bạn cách phát hiện và xử lý.
Bước 3: Bạn xóa dấu cách copy từ web và ký tự xuống dòng thế nào?
Bạn phát hiện ký tự không nhìn thấy bằng LEN như thế nào?
Hàm LEN đếm số ký tự trong ô, kể cả ký tự không nhìn thấy. Bạn tạm dùng cột K để kiểm tra. Bạn chọn ô K2, gõ công thức sau rồi nhấn Enter:
=LEN(H2)

Sau đó, bạn kéo công thức từ ô K2 xuống ô K10.

"Lê Hoa" có 6 ký tự, gồm cả dấu cách ở giữa, nhưng ô K4 đếm ra 7. "Vũ Thị Mai" có 10 ký tự nhưng ô K7 đếm ra 11. Mỗi ô còn thừa 1 ký tự mà hàm TRIM không xóa được:
Ô
B4có dấu cách không ngắt dòng, mã số 160. Ký tự này hay đi theo dữ liệu copy từ trang web. Hàm TRIM chỉ xóa dấu cách thường có mã số 32.Ô
B7có ký tự xuống dòng, mã số 10. Ký tự này sinh ra khi người nhập nhấn Alt + Enter hoặc copy từ phần mềm khác.
Nếu muốn biết ký tự cuối là gì, bạn gõ =CODE(RIGHT(B4,1)) vào ô O4 và =CODE(RIGHT(B7,1)) vào ô O7. Hai công thức trả về 160 và 10. Bạn xóa 2 ô này sau khi xem.
Bạn xóa 2 ký tự lạ bằng SUBSTITUTE và CLEAN như thế nào?
Bạn sửa lại công thức ở cột H. Bạn chọn ô H2, gõ công thức sau rồi nhấn Enter:
=PROPER(TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))))

Công thức chạy theo 4 lớp từ trong ra ngoài:
Hàm
SUBSTITUTE(B2,CHAR(160)," ")thay dấu cách mã 160 bằng dấu cách thường.Hàm CLEAN xóa các ký tự không in được có mã từ 0 đến 31, trong đó có ký tự xuống dòng mã 10.
Hàm TRIM xóa dấu cách thừa, kể cả dấu cách vừa được đổi từ mã 160.
Hàm PROPER viết hoa chữ cái đầu mỗi chữ.
Sau đó, bạn kéo lại công thức từ ô H2 xuống ô H10. Cột K tự tính lại theo kết quả mới.

Kiểm tra xong, bạn xóa công thức ở vùng K2:K10 để dùng cột K cho bước 5.
Hàm CLEAN xóa hẳn ký tự xuống dòng chứ không thay bằng dấu cách. Nếu ký tự xuống dòng nằm giữa 2 chữ, ví dụ "Vũ Thị" xuống dòng "Mai", hàm CLEAN trả về "Vũ ThịMai" và công thức 4 lớp ở trên trả về "Vũ Thịmai". Trường hợp này, bạn thay ký tự xuống dòng bằng dấu cách: =PROPER(TRIM(SUBSTITUTE(B2,CHAR(10)," "))). Bài hàm xử lý chuỗi trong Excel giải thích riêng từng hàm TRIM, SUBSTITUTE, LEFT, MID, FIND.
Bước 4: Bạn chuẩn hóa mã khách hàng bằng UPPER thế nào?
Hàm UPPER đổi mọi chữ cái thành chữ hoa. Mã khách hàng, mã hàng, biển số xe thường cần viết hoa hết. Bạn chọn ô G2, gõ công thức sau rồi nhấn Enter:
=UPPER(TRIM(A2))

Sau đó, bạn kéo công thức từ ô G2 xuống ô G10.

Với email, bạn dùng hàm LOWER để viết thường hết. Ví dụ, nếu bảng của bạn có ô N2 chứa email "Lan.Nguyen@Example.COM", bạn gõ =LOWER(TRIM(N2)) vào ô O2 và nhận "lan.nguyen@example.com".
Bước 5: Bạn chuẩn hóa số điện thoại thế nào?
Số điện thoại di động Việt Nam chuẩn gồm 10 chữ số, bắt đầu bằng số 0 và không có dấu chấm, dấu cách. Số máy bàn có 11 chữ số, nên bạn tách riêng nếu danh sách có số máy bàn. Bạn làm theo 3 cột: cột I bỏ ký tự thừa, cột J thêm số 0, cột K kiểm tra đủ 10 số.
Bạn bỏ dấu chấm, dấu cách và đổi +84 thành 0 bằng SUBSTITUTE như thế nào?
Hàm SUBSTITUTE thay một đoạn chữ bằng đoạn chữ khác. Bạn lồng 3 hàm SUBSTITUTE để làm 3 việc. Bạn chọn ô I2, gõ công thức sau rồi nhấn Enter:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"+84","0"),".","")," ","")

Công thức chạy theo 3 lớp từ trong ra ngoài:
Lớp trong cùng thay "+84" bằng "0".
Lớp giữa thay dấu chấm bằng chuỗi rỗng
"", tức là xóa dấu chấm.Lớp ngoài cùng xóa dấu cách.
Sau đó, bạn kéo công thức từ ô I2 xuống ô I10.

Ô I4 còn thiếu số 0 vì ô C4 gốc là số, Excel đã bỏ số 0 từ lúc nhập.
Bạn thêm số 0 ở đầu bằng IF và LEFT như thế nào?
Bạn chọn ô J2, gõ công thức sau rồi nhấn Enter:
=IF(LEFT(I2,1)="0",I2,"0"&I2)

Hàm LEFT(I2,1) lấy ký tự đầu tiên của ô I2. Nếu ký tự đó là "0", công thức giữ nguyên số. Nếu không, công thức ghép thêm "0" vào đầu bằng dấu &. Sau đó, bạn kéo công thức từ ô J2 xuống ô J10.
Bạn không nên dùng điều kiện "đủ 9 số thì thêm số 0". Ô I7 có 9 ký tự "098765432" nhưng đã có số 0 ở đầu, tức là khách ghi thiếu một số ở chỗ khác. Nếu thêm số 0, bạn được một số đủ 10 ký tự nhưng sai, và bước kiểm tra sau không phát hiện được.
Bạn kiểm tra đủ 10 số bằng LEN như thế nào?
Bạn chọn ô K2, gõ công thức sau rồi nhấn Enter:
=LEN(J2)

Sau đó, bạn kéo công thức từ ô K2 xuống ô K10.

Cột J giờ có số 0 ở đầu mọi số điện thoại. Ô K7 ra 9, nên bạn ghi lại khách KH0163 để gọi hoặc nhắn hỏi lại số đúng. Excel không tự đoán được chữ số bị thiếu. Bài hàm LEN trong Excel có thêm cách lọc riêng các số điện thoại bị thiếu số.
Kết quả ở cột I và cột J là chữ, không phải số. Nhờ vậy, Excel giữ nguyên số 0 ở đầu.
Bước 6: Bạn đổi ngày và số dạng chữ về ngày, số thật thế nào?
Bạn đổi ngày dạng chữ bằng DATE, RIGHT, MID, LEFT như thế nào?
Ngày dạng chữ "05/11/2026" không cộng trừ, lọc theo tháng hay sắp xếp đúng được. Bạn cắt chữ thành 3 phần rồi ghép lại bằng hàm DATE. Bạn chọn ô L2, gõ công thức sau rồi nhấn Enter:
=DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))

Với "05/11/2026", các hàm cắt chữ như sau:
Hàm
RIGHT(D2,4)lấy 4 ký tự cuối là năm "2026".Hàm
MID(D2,4,2)lấy 2 ký tự bắt đầu từ vị trí thứ 4 là tháng "11".Hàm
LEFT(D2,2)lấy 2 ký tự đầu là ngày "05".Hàm DATE ghép năm, tháng, ngày thành một ngày thật.
Sau đó, bạn kéo công thức từ ô L2 xuống ô L10. Cột L đã được định dạng sẵn kiểu dd/mm/yyyy nên hiện ngày dạng 05/11/2026. Nếu ô của bạn hiện một số như 46331, bạn chọn cột, nhấn Ctrl + 1, chọn Date hoặc Custom rồi gõ dd/mm/yyyy.
Bạn không nên dùng hàm DATEVALUE cho ngày dạng chữ. Hàm DATEVALUE đọc ngày theo cài đặt ngày tháng của Windows: máy đặt kiểu ngày/tháng/năm hiểu "05/11/2026" là ngày 5 tháng 11, còn máy đặt kiểu tháng/ngày/năm hiểu là ngày 11 tháng 5. Công thức DATE ở trên cho cùng kết quả trên mọi máy.
Bạn đổi số dạng chữ có dấu chấm bằng VALUE và SUBSTITUTE như thế nào?
Doanh số "1.250.000" là chữ vì có dấu chấm ngăn hàng nghìn. Bạn chọn ô M2, gõ công thức sau rồi nhấn Enter:
=VALUE(SUBSTITUTE(E2,".",""))

Hàm SUBSTITUTE xóa dấu chấm và trả về "1250000". Hàm VALUE đổi chữ "1250000" thành số 1250000. Với ô đã là số như E3, công thức vẫn trả về đúng 2500000. Sau đó, bạn kéo công thức từ ô M2 xuống ô M10.

Giờ bạn gõ =SUM(M2:M10) vào ô M12 sẽ nhận 18,180,000, đúng bằng tổng 9 dòng. Bạn xóa ô M12 sau khi xem. Bạn chỉ dùng cách xóa dấu chấm khi dấu chấm là dấu ngăn hàng nghìn. Nếu cột có số thập phân như "12.5", công thức sẽ trả về 125. Bài chuyển text thành số trong Excel có thêm các cách đổi chữ thành số khác như Paste Special và Text to Columns.
Bước 7: Bạn thay công thức bằng giá trị thế nào?
Bảng sạch ở cột G đến M vẫn là công thức lấy dữ liệu từ cột gốc. Nếu bạn xóa cột gốc lúc này, mọi công thức báo lỗi #REF!. Vì vậy, bạn đổi công thức thành giá trị trước:
Bạn chọn vùng
G1:M10rồi nhấn Ctrl + C.Bạn nhấn chuột phải vào ô
G1, ở mục Paste Options bạn chọn biểu tượng Values (biểu tượng có số 123). Bạn cũng có thể nhấn Ctrl + Alt + V, chọn Values rồi nhấn OK.Bạn nhấn Esc để bỏ viền nhấp nháy quanh vùng vừa sao chép.
Sau đó, bạn xóa các cột không cần nữa. Bạn chọn cột K, nhấn chuột phải và chọn Delete, rồi làm tương tự với cột I. Cuối cùng, bạn chọn các cột từ A đến F và xóa. Bảng sạch dồn về cột A đến E.

Số điện thoại vẫn nằm sát lề trái vì đây là chữ. Bạn giữ nguyên dạng chữ cho cột này. Nếu ô có tam giác xanh nhỏ ở góc trên bên trái, Excel đang nhắc "Number Stored as Text". Bạn bỏ qua nhắc nhở này và không chọn Convert to Number, vì đổi sang số sẽ làm mất số 0 ở đầu.
Bước 8: Bạn xóa dòng trùng bằng Remove Duplicates thế nào?
Bạn chọn một ô bất kỳ trong bảng, ví dụ ô A2. Sau đó, bạn vào tab Data, nhóm Data Tools, bấm Remove Duplicates.

Trong hộp thoại, ô My data has headers đã được tích vì dòng 1 là tiêu đề. Cả 5 cột đều được tích, nghĩa là Excel chỉ xóa dòng giống nhau ở cả 5 cột. Bạn nhấn OK.

Bạn nhấn OK để đóng thông báo.

Excel giữ dòng xuất hiện đầu tiên và xóa các dòng giống nó ở phía dưới. Nếu bạn chạy Remove Duplicates ngay trên bảng gốc ở bước 1, Excel không xóa được dòng nào, vì dòng 2 và dòng 8 khác nhau ở dấu cách, dấu chấm và dạng số. Remove Duplicates không phân biệt chữ hoa, chữ thường, nhưng có phân biệt dấu cách và phân biệt số dạng chữ với số thật.
Nếu muốn mỗi mã khách hàng chỉ còn một dòng dù các cột khác lệch nhau, bạn bấm Unselect All rồi chỉ tích cột Mã KH. Bài lọc dữ liệu trùng trong Excel hướng dẫn thêm cách đếm và lọc dòng trùng bằng COUNTIF, UNIQUE trước khi xóa.
Bước 9: Bạn tách tên ra cột riêng bằng Flash Fill thế nào?
Danh sách khách hàng tiếng Việt thường được sắp xếp theo tên, nên bạn cần cột Tên riêng. Tính năng Flash Fill điền dữ liệu theo mẫu bạn gõ ở ô đầu tiên. Excel có Flash Fill từ bản 2013.
Bạn gõ tiêu đề "Tên" vào ô F1. Bạn gõ "An" vào ô F2, là tên của khách ở ô B2, rồi nhấn Enter.

Con trỏ đang ở ô F3, bạn nhấn Ctrl + E. Bạn cũng có thể vào tab Data và bấm Flash Fill.

Kết quả của Flash Fill là giá trị, không phải công thức. Nếu bạn sửa họ tên ở cột B, cột F không tự cập nhật. Bạn nên xem lại vài dòng sau khi nhấn Ctrl + E, vì Flash Fill đoán theo mẫu và có thể đoán sai với dữ liệu lạ.
Nếu cần công thức, bạn gõ =LEFT(B2,FIND(" ",B2)-1) vào ô G2 để lấy họ. Công thức này tìm vị trí dấu cách đầu tiên bằng FIND rồi lấy phần đứng trước, nên "Nguyễn Văn An" trả về "Nguyễn". Bạn cũng có thể dùng Data > Text to Columns để tách theo dấu cách, nhưng họ tên 3 chữ và 4 chữ sẽ bị chia thành số cột khác nhau. Bài tách tên trong Excel có công thức lấy tên cho mọi độ dài họ tên.
Những hàm nào hay dùng khi làm sạch dữ liệu trong Excel?
Bảng sau tóm tắt các hàm làm sạch dữ liệu trong Excel đã dùng trong bài.
Hàm | Việc hàm làm | Ví dụ trong bài |
|---|---|---|
TRIM | Hàm này xóa dấu cách ở đầu, cuối và giữ 1 dấu cách giữa các chữ. |
|
CLEAN | Hàm này xóa ký tự không in được có mã 0 đến 31, gồm ký tự xuống dòng. |
|
SUBSTITUTE | Hàm này thay một đoạn chữ bằng đoạn chữ khác hoặc xóa đi. |
|
PROPER | Hàm này viết hoa chữ cái đầu mỗi chữ. |
|
UPPER, LOWER | Hai hàm này viết hoa hết hoặc viết thường hết. |
|
LEFT, RIGHT, MID | Ba hàm này lấy ký tự ở đầu, ở cuối hoặc ở giữa chuỗi. |
|
FIND | Hàm này trả về vị trí của một ký tự trong chuỗi. |
|
LEN | Hàm này đếm số ký tự, dùng để phát hiện ký tự thừa hoặc thiếu. |
|
VALUE | Hàm này đổi chữ trông như số thành số thật. |
|
DATE | Hàm này ghép năm, tháng, ngày thành ngày thật. |
|
IF | Hàm này chọn kết quả theo điều kiện. |
|
Khi nào bạn nên làm sạch dữ liệu bằng Power Query?
Nếu mỗi tháng bạn nhận một file xuất từ phần mềm với cùng kiểu lỗi, bạn có thể dùng Power Query. Power Query ghi lại từng bước làm sạch. Tháng sau, bạn dán dữ liệu mới vào bảng gốc rồi vào Data > Refresh All, các bước tự chạy lại. Power Query có sẵn từ Excel 2016 ở tab Data. Bản 2016 gọi nhóm này là Get & Transform và nút là From Table, các bản mới hơn gọi là Get & Transform Data và From Table/Range.
Power Query có sẵn nhiều lệnh làm việc giống các hàm trong bài:
Tab Transform > Format có Trim, Clean, Capitalize Each Word (giống PROPER) và UPPERCASE (giống UPPER).
Tab Transform có Replace Values để thay một ký tự bằng ký tự khác. Muốn xóa ký tự, bạn để trống ô Replace With.
Tab Home có Remove Rows > Remove Duplicates để xóa dòng trùng và Close & Load để đưa bảng sạch ra sheet mới.
Các lệnh có sẵn này chưa làm đủ việc như các công thức trong bài. Nếu bạn chỉ chạy Clean, Trim, Capitalize Each Word, UPPERCASE, Replace Values cho dấu chấm và dấu cách, rồi Remove Duplicates trên danh sách ở bước 1, bảng vẫn còn 9 dòng vì 4 lý do sau:
Lệnh Trim của Power Query chỉ xóa dấu cách ở đầu và cuối, không gộp nhiều dấu cách ở giữa thành 1 như hàm TRIM. Ô
B2vẫn ra "Nguyễn Văn An".Cột mã cần chạy thêm Trim, vì UPPERCASE không xóa dấu cách ở cuối "KH0158 ".
Cột điện thoại cần thêm Replace Values đổi "+84" thành "0" và một cột tùy chỉnh (Custom Column) để thêm số 0 cho số bị mất 0.
Remove Duplicates của Power Query phân biệt chữ hoa, chữ thường, khác với Remove Duplicates trên sheet. Bạn phải chuẩn hóa chữ hoa, chữ thường trước khi xóa trùng.
Vì vậy, bạn nên làm sạch bằng hàm trước để biết dữ liệu cần sửa những gì. Khi chuyển sang Power Query, bạn thêm đủ các bước còn thiếu ở trên và đặt Remove Duplicates ở cuối cùng.
Chuẩn hóa dữ liệu trong Excel khác làm sạch dữ liệu thế nào?
Nhiều người dùng 2 cụm từ này thay cho nhau. Làm sạch dữ liệu là sửa nội dung bị sai trong từng ô, như dấu cách thừa, ký tự lạ, số dạng chữ và dòng trùng. Chuẩn hóa dữ liệu là đưa mọi ô trong cột về cùng một quy ước, ví dụ họ tên viết hoa chữ đầu, số điện thoại đủ 10 số bắt đầu bằng 0, ngày theo dạng dd/mm/yyyy. Các bước 2 đến 6 trong bài vừa làm sạch vừa chuẩn hóa.
Chuẩn hóa còn gồm việc đưa bảng về đúng cấu trúc: mỗi cột chỉ chứa một loại thông tin, mỗi dòng là một khách hàng, không gộp ô và không có dòng trống giữa bảng. Bảng đúng cấu trúc giúp các bước lọc, Pivot Table và hàm tra cứu chạy đúng.
Bạn hay gặp lỗi gì khi làm sạch dữ liệu trong Excel?
Lỗi | Nguyên nhân | Cách sửa |
|---|---|---|
Bạn dùng TRIM rồi mà LEN vẫn đếm thừa ký tự | Ô có dấu cách mã 160 copy từ web hoặc ký tự xuống dòng mã 10. | Bạn dùng |
Hai chữ bị dính vào nhau sau khi dùng CLEAN | Ký tự xuống dòng nằm giữa 2 chữ, CLEAN xóa hẳn ký tự đó. | Bạn dùng |
PROPER trả về "NguyễN Thị ÁNh" | Họ tên được gõ bằng bảng mã Unicode tổ hợp, dấu thanh là một ký tự riêng nên PROPER viết hoa chữ đứng sau dấu. | Bạn đổi dữ liệu sang Unicode dựng sẵn bằng công cụ chuyển mã của Unikey (Ctrl + Shift + F6) rồi dùng lại PROPER. |
Số điện thoại mất số 0 ở đầu | Cột điện thoại bị đổi sang số, hoặc dữ liệu được nhập như số từ đầu. | Bạn giữ cột điện thoại ở dạng chữ và thêm số 0 bằng |
Ngày ra năm 6333 | Ô ngày gốc đã là ngày thật, công thức LEFT, MID, RIGHT cắt nhầm con số bên trong ngày. | Bạn dùng |
Ngày báo | Ngày gõ thiếu số 0, ví dụ "5/11/2026", nên MID cắt sai vị trí. | Bạn dùng Data > Text to Columns, ở bước 3 chọn Date kiểu DMY. |
VALUE báo | Ô còn ký tự khác số, ví dụ chữ "đ" hoặc dấu cách mã 160. | Bạn xóa thêm ký tự đó bằng một lớp SUBSTITUTE nữa. |
Ô trống ra "0" hoặc | Công thức vẫn chạy trên ô gốc trống. | Bạn bọc công thức bằng IF kiểm tra ô gốc của từng cột, ví dụ |
Remove Duplicates không xóa được dòng nào | Bạn xóa trùng trên dữ liệu chưa làm sạch. | Bạn làm sạch xong, thay công thức bằng giá trị rồi mới xóa trùng. |
Bảng sạch báo | Bảng sạch vẫn là công thức tham chiếu cột gốc. | Bạn nhấn Ctrl + Z, thay công thức bằng giá trị ở bước 7 rồi mới xóa cột gốc. |
Số thập phân bị sai sau khi xóa dấu chấm | Dấu chấm trong ô là dấu thập phân, không phải dấu ngăn hàng nghìn. | Bạn chỉ xóa dấu chấm ở cột chắc chắn là số nguyên. |
Bạn muốn học Excel bài bản hơn?
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.