Làm sạch dữ liệu trong Excel: 9 lỗi hay gặp và cách xử lý

VLOOKUP báo #N/A ở 11/12 dòng dù mã khách hàng hai bên nhìn y hệt nhau. Cột tiền cộng ra 0. Danh sách khách có 1.000 dòng nhưng thực ra chỉ có 820 khách. Dữ liệu tải từ phần mềm, sao kê ngân hàng hay file người khác gửi thường mang những lỗi như vậy, và phần lớn không nhìn ra bằng mắt.

Bài này đi qua 9 lỗi hay gặp khi làm sạch dữ liệu trong Excel. Với mỗi lỗi có cách nhận ra, cách sửa bằng công cụ có sẵn của Excel, và cách làm trong Power Query cho trường hợp tháng nào cũng nhận một file mới. Mọi ví dụ đã chạy thử trong Excel Microsoft 365 trên các file demo của EzTech, trên một máy đặt định dạng ngày kiểu Mỹ (tháng/ngày/năm). Chi tiết này quan trọng ở lỗi số 4.

Trước khi làm sạch dữ liệu: giữ bản gốc và ghi lại vài con số

Làm trên bản sao hoặc trên cột phụ, không sửa thẳng vào dữ liệu gốc. Trước khi sửa, ghi lại số dòng và tổng các cột tiền. Sửa xong đếm lại: số dòng chỉ được giảm đúng bằng số dòng mình định xóa, tổng tiền không được đổi, trừ khi chính việc sửa là đổi chữ thành số.

Nhóm 1: lỗi nằm trong từng ô chữ

1. Dấu cách thừa và ký tự ẩn

File demo có 12 dòng doanh số, 11 mã khách hàng dính dấu cách ở đầu hoặc cuối (" KH003", "KH001 "). Tra tên khách bằng VLOOKUP khớp chính xác thì cả 11 dòng báo #N/A. Nhận ra bằng =LEN(A2): mã KH003 dài 5 ký tự, ô báo 6 hay 7 là có dấu cách.

Sửa: =VLOOKUP(TRIM(A2),$E$2:$F$9,2,FALSE), hoặc dùng TRIM ở cột phụ rồi dán giá trị. Sau TRIM, số dòng #N/A về 0.

Chỗ TRIM không xử lý được: dấu cách không ngắt (mã 160), loại hay đi theo dữ liệu dán từ web. Microsoft ghi rõ TRIM chỉ xóa dấu cách thường (mã 32). Chạy thử một ô gồm dấu cách không ngắt và "KH003": sau TRIM vẫn dài 6 ký tự, VLOOKUP vẫn #N/A. Cách sửa là đổi nó thành dấu cách thường trước: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

Ký tự xuống dòng trong ô (Alt + Enter) thì dùng CLEAN, nhưng CLEAN xóa luôn chỗ ngắt mà không chèn dấu cách: "Cty TNHH Minh Phát" xuống dòng "CN Hà Nội" thành "Cty TNHH Minh PhátCN Hà Nội". Dùng =TRIM(SUBSTITUTE(A2,CHAR(10)," ")) sẽ ra "Cty TNHH Minh Phát CN Hà Nội".

Power Query: Transform → Format → Trim. Chạy thử, Trim của Power Query xóa cả dấu cách thường lẫn dấu cách không ngắt ở hai đầu chuỗi. Format → Clean cũng nối dính chữ giống hàm CLEAN.

2. Ký tự thừa trong mã và số tài khoản

12 số tài khoản ngân hàng nhận về viết mỗi nơi một kiểu: "0123.456.789", "9876-543-210", "1900 8888 66". Công thức =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".",""),"-","")," ","") đưa cả 12 số về đúng 10 chữ số.

Đừng đổi kết quả sang dạng số. 5/12 số tài khoản bắt đầu bằng 0. Bọc thêm VALUE thì cả 5 mất số 0 đứng đầu: "0123456789" thành 123456789. Cột số tài khoản, mã số thuế, mã hàng phải giữ kiểu Text.

Power Query: chọn cột, Transform → Replace Values, thay lần lượt dấu chấm, gạch và dấu cách bằng ô trống. Kiểm cột vẫn ở kiểu Text (biểu tượng ABC ở đầu cột).

3. Chữ hoa, chữ thường lộn xộn

12 tên khách hàng gõ tùy hứng: "cÔng ty tnhh hoàng nam", "CÔNG TY CP MINH PHÁT", "dntn thanh bình". Hàm PROPER viết hoa chữ cái đầu mỗi từ, xử lý đúng tiếng Việt có dấu, nhưng làm hỏng chữ viết tắt ở cả 12/12 tên: "Công Ty Tnhh Hoàng Nam", "Công Ty Cp Minh Phát", "Dntn Thanh Bình".

Sửa bằng cách thay lại các chữ viết tắt sau PROPER:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2),"Tnhh","TNHH")," Cp "," CP "),"Dntn","DNTN")

Power Query: Transform → Format → Capitalize Each Word cho ra đúng kết quả như PROPER, kể cả lỗi "Tnhh". Thêm các bước Replace Values cho từng chữ viết tắt. Danh sách chữ viết tắt nên lập một lần (TNHH, CP, DNTN, MTV, CN) rồi dùng lại.

Nhóm 2: đúng giá trị nhưng sai kiểu dữ liệu

4. Ngày tháng lưu dạng chữ

File demo có 15 chứng từ, cột ngày ghi dạng "05.06.2026" và đang là chữ nên không lọc được theo tháng. Cách hay được chỉ là Ctrl + H, thay dấu chấm bằng dấu gạch chéo. Cách này chỉ đúng khi máy đặt định dạng ngày/tháng/năm. Trên máy đặt kiểu Mỹ, kết quả là:

• 7/15 ngày bị đảo ngày với tháng mà không có cảnh báo nào: "05.06.2026" thành ngày 6 tháng 5.
• 8/15 ngày còn lại vẫn là chữ, vì những ngày lớn hơn 12 không đọc được thành tháng.

Hai cách không phụ thuộc cài đặt máy, cả hai chạy thử đều đúng 15/15:

• Text to Columns: chọn cột, Data → Text to Columns → Delimited, bỏ tích mọi dấu phân cách, bước 3 chọn Date: DMY. Excel đọc được cả dấu chấm lẫn dấu gạch chéo.
• Công thức: =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)), lấy năm, tháng, ngày theo vị trí ký tự.

Power Query: bấm chuột phải cột → Change Type → Using Locale..., Data Type là Date, Locale là Vietnamese (Vietnam). Lỗi ngày tháng trong Power Query có bài riêng tại sửa lỗi ngày tháng Power Query.

5. Số lưu dạng chữ

Ô số lưu dạng chữ thường căn trái và có tam giác xanh ở góc. SUM bỏ qua các ô này nên tổng thấp hơn thật, có khi bằng 0. Kiểm nhanh bằng =ISTEXT(A2), hoặc so COUNT với COUNTA trên cùng cột: hai số lệch nhau là có ô chữ lẫn vào.

Cách sửa nhanh nhất là chọn cột, vào Data → Text to Columns rồi bấm Finish. Số có dấu phân cách nghìn khác với cài đặt máy (kiểu "6,700,000.50" trên máy dùng dấu phẩy làm dấu thập phân) thì ở bước 3 bấm Advanced để khai báo dấu thập phân và dấu phân cách nghìn. Ví dụ đầy đủ với bảng 1.000 hồ sơ công nợ có hai cột tiền dạng chữ nằm trong bài tách họ tên, tách cột trong Excel.

Power Query: Change Type → Using Locale, chọn Decimal Number và vùng khớp với cách viết số trong file.

6. Mã bị Excel tự đổi thành ngày hoặc mất số 0

12 mã hàng lấy từ chứng từ: "03-05", "0035", "1/2", "0120"... Gõ hoặc dán vào ô định dạng General, cả 12 mã đều bị Excel đổi: "03-05" và "1/2" thành ngày tháng (máy kiểu Mỹ hiện "5-Mar" và "2-Jan"), "0035" thành số 35. Cùng 12 mã đó gõ vào ô đã đặt định dạng Text trước thì không mã nào bị đổi.

Đã đổi rồi thì không lấy lại được dạng gốc bằng cách đổi định dạng, phải nhập lại. Nên phòng từ đầu: đặt cột mã về Text (Home → Number Format → Text) trước khi dán. Với file CSV, mở bằng Power Query (Data → From Text/CSV) thay vì mở thẳng, rồi sửa bước Changed Type để cột mã giữ kiểu Text.

Nhóm 3: lỗi cấu trúc bảng

7. Dòng trống xen giữa dữ liệu

Bảng thu chi demo có 20 dòng, trong đó 6 dòng trống hoàn toàn. Cách hay được chỉ: F5 → Special → Blanks → Delete → Entire row. Cách này xóa mọi dòng có ít nhất một ô trống, không chỉ dòng trống hoàn toàn. Thử để trống ô diễn giải của chứng từ PC002 rồi làm đúng các bước trên: bảng còn 13 chứng từ thay vì 14, PC002 bị xóa theo.

Cách an toàn: lọc cột bắt buộc phải có (Số CT) theo giá trị (Blanks), xóa các dòng lọc ra, rồi bỏ lọc.

Power Query: Home → Remove Rows → Remove Blank Rows. Lệnh này chỉ xóa dòng mà mọi ô đều trống. Chạy thử, dòng thiếu diễn giải được giữ lại.

8. Ô trống trong cột nhóm

Bảng mua hàng demo 15 dòng, cột Nhóm hàng chỉ ghi ở dòng đầu mỗi nhóm, 7 ô bên dưới để trống. Nhìn thì dễ đọc nhưng lọc hay làm Pivot theo nhóm sẽ sai. Cách điền:

1. Chọn cột Nhóm hàng, F5 → Special → Blanks.
2. Gõ dấu =, bấm phím mũi tên lên để trỏ vào ô ngay phía trên, nhấn Ctrl + Enter.
3. Copy cả cột, Paste Special → Values để bỏ công thức.

Chạy thử, cả 7 ô nhận đúng nhóm. Bẫy: nếu cột đang định dạng Text, cả 7 ô nhận nguyên chuỗi công thức dạng chữ thay vì kết quả. Đổi cột về General trước khi làm.

Power Query: Transform → Fill → Down. Fill Down chỉ điền vào ô null. Ô chứa chuỗi rỗng (hay gặp trong file xuất từ phần mềm) được giữ nguyên, chạy thử cho đúng kết quả đó. Trường hợp này dùng Replace Values trước: ô Value To Find để trống, ô Replace With gõ null.

9. Dòng trùng

Danh sách khách hàng demo có 1.000 dòng nhưng chỉ có 820 mã khách khác nhau: 163 mã xuất hiện 2 đến 3 lần, tổng cộng 180 dòng thừa. Các bản trùng nằm rải rác, có bản cách bản gốc 969 dòng, nên dò bằng mắt không ra. Đánh dấu trước khi xóa: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, hoặc thêm cột =COUNTIF($A$2:$A$1001,A2).

Data → Remove Duplicates, chọn cột nào quyết định kết quả:

• Theo cột Mã KH, hoặc theo cả 6 cột: còn 820 dòng, xóa đúng 180 dòng trùng.
• Theo cột Tên KH: còn 788 dòng, xóa 212. File có 32 tên được dùng chung cho 64 mã khách khác nhau, nên 32 khách thật bị xóa nhầm.

Excel và Power Query còn khác nhau ở chữ hoa, chữ thường. Chạy thử trên ba dòng "Cty ABC", "CTY ABC", "Cty ABC": Remove Duplicates của Excel coi cả ba là trùng, còn 1 dòng; Remove Duplicates của Power Query giữ 2 dòng, vì Power Query phân biệt chữ hoa, chữ thường (Microsoft ghi rõ điều này). Muốn Power Query xử lý giống Excel, đổi cột về chữ in hoa trước (Format → UPPERCASE) rồi mới xóa trùng.

Làm sạch dữ liệu hằng tháng bằng Power Query

Sửa bằng hàm và công cụ của Excel hợp khi làm một lần. Nếu tháng nào cũng nhận một file cùng kiểu, dựng các bước làm sạch trong Power Query một lần: Trim, Replace Values, Change Type Using Locale, Fill Down, Remove Blank Rows, Remove Duplicates. Tháng sau thay file nguồn và bấm Refresh, các bước chạy lại theo đúng thứ tự.

Lỗi	Nhận ra bằng	Sửa trong Excel	Sửa trong Power Query
Dấu cách thừa	LEN	TRIM, SUBSTITUTE CHAR(160)	Format → Trim
Ký tự thừa trong mã	LEN, lọc	SUBSTITUTE, giữ kiểu Text	Replace Values
Hoa thường lộn xộn	Nhìn cột	PROPER, sửa lại chữ viết tắt	Capitalize Each Word, Replace Values
Ngày dạng chữ	ISTEXT, căn trái	Text to Columns DMY, DATE	Change Type Using Locale
Số dạng chữ	ISTEXT, COUNT so với COUNTA	Text to Columns, VALUE	Change Type Using Locale
Mã bị tự đổi	Mất số 0, hiện ngày tháng	Đặt Text trước khi dán	Sửa bước Changed Type
Dòng trống	Lọc (Blanks)	Lọc theo cột khóa rồi xóa	Remove Blank Rows
Ô trống cột nhóm	Lọc (Blanks)	Go To Special, Ctrl + Enter	Replace "" thành null, Fill Down
Dòng trùng	COUNTIF, tô màu trùng	Remove Duplicates theo cột khóa	UPPERCASE rồi Remove Duplicates

Câu hỏi thường gặp

Có công cụ nào làm sạch dữ liệu tự động hết không?

Không có lệnh nào sửa được cả 9 lỗi trên cùng lúc, vì mỗi lỗi cần một quyết định riêng: cột nào giữ kiểu Text, ngày viết theo thứ tự nào, trùng theo cột nào. Power Query giúp ghi lại các quyết định đó thành các bước chạy lại được, nhưng vẫn cần người quyết định từng bước ở lần đầu.

Làm sạch xong làm sao biết dữ liệu đã đúng?

So các con số đã ghi trước khi sửa: số dòng, tổng từng cột tiền, số mã khác nhau. Chênh lệch nào cũng phải giải thích được, ví dụ số dòng giảm đúng bằng số dòng trùng đã đánh dấu.

Dữ liệu xuất từ phần mềm kế toán có lỗi gì riêng?

Thường có thêm dòng tiêu đề nhiều tầng, dòng cộng xen giữa và cột ô gộp. Cách xử lý file xuất từ MISA có ở bài xử lý dữ liệu MISA trong Excel.

Học tiếp

Lỗi dấu cách thừa là nguyên nhân hay gặp nhất khiến hàm tra cứu báo #N/A. So sánh đầy đủ hai hàm tra cứu có ở bài XLOOKUP và VLOOKUP khác gì nhau. Nhiều tình huống dán dữ liệu từ web, PDF, phần mềm được gom sẵn trong mục tra cứu tình huống Excel.

Khóa DATA FINANCE của EzTech đi từ Power Query chuyên sâu trong Excel đến báo cáo quản trị trên Power BI, dành cho người làm kế toán, tài chính. Có thể tự kiểm tra mức nắm Power Query hiện tại bằng bài trắc nghiệm Power Query miễn phí.