Làm sạch dữ liệu trong Excel: Text to Columns, Flash Fill và Power Query

Trong công việc phân tích dữ liệu thực tế, phần lớn thời gian không dành cho việc tính toán mà dành cho việc dọn dẹp: khoảng trắng thừa, ngày tháng sai định dạng, số bị lưu thành chữ, tên viết mỗi nơi một kiểu.

Đây cũng là lý do hai người cùng nhận một file, một người ra kết quả trong 20 phút, người kia loay hoay cả buổi mà SUMIFS vẫn trả về 0.

Bài này đi theo đúng thứ tự bạn nên làm khi nhận một file bẩn.

Dữ liệu "sạch" trong Excel nghĩa là gì?

Một bảng dữ liệu sạch thỏa sáu điều kiện:

1. Đúng một dòng tiêu đề, nằm ở dòng trên cùng.
2. Mỗi dòng là một bản ghi, mỗi cột là một thuộc tính.
3. Không có ô gộp (merge) trong vùng dữ liệu.
4. Không có dòng trống hay dòng tổng xen giữa.
5. Mỗi cột chỉ chứa một kiểu dữ liệu — cột ngày toàn ngày, cột số toàn số.
6. Không có ô "trang trí": tiêu đề phụ, ghi chú, logo nằm lẫn trong vùng dữ liệu.

Bảng thỏa sáu điều này chạy được với mọi công cụ: PivotTable, biểu đồ, hàm tra cứu, Power Query. Bảng vi phạm chúng sẽ gây lỗi ở mọi bước sau.

Vì vậy nguyên tắc số một: tách bảng "để máy đọc" khỏi bảng "để người xem". Sheet raw giữ dữ liệu thô đúng chuẩn; sheet báo cáo mới là nơi gộp ô, tô màu, thêm ghi chú.

Bước 1 — Kiểm tra trước khi sửa

Trước khi động vào dữ liệu, hãy chẩn đoán trong 5 phút:

Luôn tạo bản sao sheet gốc trước khi sửa. Đặt tên raw và không bao giờ chạm vào nó nữa.

Bước 2 — Xóa khoảng trắng và ký tự ẩn

Đây là nguyên nhân số một khiến SUMIFS, COUNTIFS và VLOOKUP trả kết quả sai.

=TRIM(B2)                                  → xóa khoảng trắng đầu, cuối và khoảng trắng đôi
=CLEAN(B2)                                 → xóa ký tự điều khiển không in được
=TRIM(SUBSTITUTE(B2, CHAR(160), " "))      → xử lý cả khoảng trắng cứng từ web

CHAR(160) là non-breaking space — thứ mà TRIM không xóa được. Nó xuất hiện hầu như mọi lần bạn dán dữ liệu từ trang web hoặc từ hệ thống quản trị. Nếu dữ liệu của bạn "nhìn giống hệt nhau" mà công thức vẫn báo không khớp, gần như chắc chắn là nó.

Công thức làm sạch tổng hợp dùng cho phần lớn trường hợp:

=TRIM(CLEAN(SUBSTITUTE(B2, CHAR(160), " ")))

Sau khi làm sạch bằng cột phụ, hãy Paste Values kết quả đè lên cột gốc rồi xóa cột phụ — nếu không, file sẽ đầy công thức phụ thuộc lẫn nhau.

Bước 3 — Sửa số bị lưu dưới dạng chữ

Dấu hiệu nhận biết: giá trị căn trái trong ô (số luôn căn phải), có tam giác xanh ở góc trên bên trái, và SUM cho ra 0.

Ba cách sửa, chọn theo tình huống:

Cách 1 — Text to Columns (nhanh nhất cho một cột): Chọn cột → Data → Text to Columns → Next → Next → Finish. Không cần thiết lập gì, chỉ việc bấm Finish. Excel sẽ diễn giải lại toàn bộ cột.

Cách 2 — Paste Special nhân 1: Gõ số 1 vào một ô trống, copy ô đó → chọn vùng cần sửa → Ctrl + Alt + V → chọn Values và Multiply → OK.

Cách 3 — Hàm VALUE:

=VALUE(B2)

Dùng khi bạn muốn giữ dữ liệu gốc để đối chiếu.

Trường hợp đặc biệt với dữ liệu Việt Nam: file dùng dấu chấm làm phân cách nghìn (1.250.000) trong khi máy đặt dấu phẩy. Khi đó VALUE sẽ lỗi. Xử lý bằng cách bỏ dấu phân cách trước:

=VALUE(SUBSTITUTE(B2, ".", ""))

Bước 4 — Sửa ngày tháng bị nhận sai

Đây là loại lỗi gây hậu quả âm thầm nhất, vì một phần dữ liệu vẫn "trông đúng".

Nguyên nhân gốc: Excel diễn giải chuỗi ngày theo cài đặt vùng của máy tính. File xuất từ hệ thống dùng mm/dd/yyyy, máy bạn đặt dd/mm/yyyy — khi đó 03/07/2026 bị hiểu thành 7 tháng 3 thay vì 3 tháng 7. Tệ hơn, 25/07/2026 không hợp lệ theo kiểu Mỹ nên bị giữ nguyên dạng chữ.

Kết quả: nửa cột là ngày thật (căn phải), nửa cột là chữ (căn trái). Sắp xếp và lọc theo cột này sẽ cho kết quả vô nghĩa.

Cách sửa chuẩn — Text to Columns có chỉ định định dạng:

1. Chọn cột ngày.
2. Data → Text to Columns → Next → Next.
3. Ở bước 3, chọn Date và chỉ đúng thứ tự gốc của dữ liệu: DMY hoặc MDY.
4. Finish.

Đây là cách duy nhất bảo bảo Excel hiểu đúng thứ tự ngày–tháng thay vì tự đoán.

Cách sửa bằng công thức khi biết rõ cấu trúc chuỗi:

=DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2))

Kiểm tra sau khi sửa: chọn cả cột và xem thanh trạng thái ở đáy màn hình. Nếu Excel hiện Count bằng số dòng nhưng không hiện Numerical Count, nghĩa là vẫn còn ô dạng chữ.

Bước 5 — Tách và ghép cột

Text to Columns — khi dữ liệu có dấu phân cách rõ ràng

Dùng cho: tách "Họ và tên" theo dấu cách, tách địa chỉ theo dấu phẩy, tách chuỗi CSV dán vào một cột.

Data → Text to Columns → Delimited → chọn dấu phân cách → Finish.

Lưu ý: kết quả sẽ ghi đè lên các cột bên phải. Hãy chèn sẵn vài cột trống trước khi làm.

Flash Fill (Ctrl+E) — khi quy luật dễ nhìn nhưng khó mô tả

Gõ mẫu cho 1–2 dòng đầu, nhấn Ctrl + E, Excel suy ra quy luật và điền phần còn lại. Hiệu quả với: lấy chữ cái đầu của họ tên, ghép "Nguyễn Văn An" thành "nguyen.van.an", chuẩn hóa số điện thoại về dạng có dấu chấm.

Hai giới hạn phải biết: kết quả là giá trị tĩnh, không cập nhật khi dữ liệu gốc đổi; và Flash Fill có thể đoán sai âm thầm nếu dữ liệu không đồng nhất. Luôn kiểm tra vài dòng cuối bảng, không chỉ vài dòng đầu.

Hàm — khi dữ liệu còn tiếp tục thay đổi

=TEXTBEFORE(A2," ",-1)      → họ và tên đệm
=TEXTAFTER(A2," ",-1)       → tên
=TEXTSPLIT(A2,",")          → tách thành nhiều cột
=TEXTJOIN(", ",TRUE,B2:D2)  → ghép lại, bỏ ô trống

Nguyên tắc chọn: dữ liệu tĩnh, làm một lần → Text to Columns hoặc Flash Fill. Dữ liệu còn thay đổi hoặc phải làm lại hằng tháng → hàm hoặc Power Query.

Bước 6 — Xử lý dữ liệu trùng

Tìm trước, xóa sau

Đừng bấm Remove Duplicates ngay. Hãy xem trước cái gì sắp bị xóa:

• Tô màu bản trùng: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
• Đếm số lần xuất hiện: =COUNTIFS($B$2:$B$500, B2) — lọc các dòng có giá trị lớn hơn 1.
• Đánh số thứ tự lần xuất hiện: =COUNTIFS($B$2:B2, B2) — dòng đầu tiên là 1, bản trùng là 2, 3…

Xóa trùng

Data → Remove Duplicates → chọn đúng các cột định nghĩa "trùng". Đây là chỗ hay sai: nếu tick tất cả các cột, hai dòng chỉ khác nhau ở một dấu cách sẽ được coi là khác nhau và không bị xóa.

Với dữ liệu quan trọng, an toàn hơn là dùng UNIQUE (Microsoft 365) ra một vùng mới, giữ nguyên bản gốc:

=UNIQUE(A2:D500)

Bước 7 — Gỡ những thứ phá cấu trúc

Ô gộp (Merge Cells)

Merge làm hỏng: sắp xếp, lọc, PivotTable, và mọi công thức tham chiếu vùng. Trong vùng dữ liệu, không bao giờ dùng merge.

Cách gỡ và điền lại giá trị:

1. Chọn vùng → Home → Merge & Center (bấm để bỏ gộp). Giờ chỉ ô đầu tiên có giá trị.
2. Vẫn giữ vùng chọn → Ctrl + G → Special → Blanks → OK.
3. Gõ = rồi nhấn mũi tên lên → Ctrl + Enter.
4. Paste Values đè lên để chuyển công thức thành giá trị.

Toàn bộ mất khoảng 20 giây và là kỹ thuật đáng thuộc.

Nếu bạn chỉ muốn tiêu đề trông đẹp mà không muốn phá cấu trúc, dùng Ctrl + 1 → Alignment → Center Across Selection. Nó trông y hệt merge nhưng không gộp ô thật.

Dòng trống và dòng tổng xen giữa

Ctrl + G → Special → Blanks → Ctrl + - → Entire row.

Rác định dạng làm file nặng

Nếu Ctrl + End nhảy tới dòng 100.000 trong khi dữ liệu chỉ tới dòng 500: chọn từ dòng 501 tới dòng cuối → Ctrl + - xóa dòng → làm tương tự với cột → lưu và đóng file → mở lại. Dung lượng file thường giảm rõ rệt.

Bước 8 — Tự động hóa bằng Power Query

Nếu bạn phải làm sạch cùng một loại file mỗi tuần, tất cả các bước trên đều nên chuyển sang Power Query. Đây là công cụ có sẵn trong Excel 2016 trở lên (Data → Get & Transform Data), không cần cài thêm gì.

Ý tưởng cốt lõi: bạn thao tác một lần, Power Query ghi lại các bước. Tháng sau, nhận file mới, chỉ cần bấm Refresh là toàn bộ quy trình chạy lại.

Quy trình cơ bản

1. Data → Get Data → From File → From Workbook (hoặc From Text/CSV, From Folder).
2. Chọn bảng → Transform Data để mở cửa sổ Power Query Editor.
3. Thực hiện các bước làm sạch bằng giao diện:
   • Use First Row as Headers — lấy dòng đầu làm tiêu đề.
   • Change Type — đặt đúng kiểu dữ liệu cho từng cột (đặc biệt là ngày: chọn Using Locale để chỉ định đúng vùng).
   • Transform → Format → Trim / Clean.
   • Remove Rows → Remove Blank Rows / Remove Duplicates.
   • Split Column By Delimiter — thay cho Text to Columns.
   • Merge Queries — thay cho VLOOKUP, ghép hai bảng theo cột khóa.
   • Append Queries — nối nhiều bảng cùng cấu trúc thành một.
4. Close & Load To… → đưa kết quả ra sheet mới hoặc thẳng vào PivotTable.

Bên phải cửa sổ có khung Applied Steps liệt kê mọi bước bạn đã làm. Bạn có thể sửa, xóa, đổi thứ tự bất kỳ bước nào — đây là điều Excel thuần không làm được.

Ba việc Power Query làm tốt hơn hẳn công thức

1. Gộp toàn bộ file trong một thư mục. Get Data → From Folder — 12 file báo cáo tháng thành một bảng duy nhất, tháng sau thả thêm file vào thư mục rồi bấm Refresh.

2. Unpivot — chuyển bảng ngang thành bảng dọc. Bảng có 12 cột tháng không dùng được cho PivotTable. Chọn các cột tháng → chuột phải → Unpivot Columns → bảng biến thành hai cột Tháng và Giá trị. Đây là thao tác đắt giá nhất của Power Query và gần như không thể làm gọn bằng công thức.

3. Xử lý dữ liệu lớn mà không làm file ì. Power Query xử lý ở tầng dưới, kết quả nạp vào sheet hoặc vào Data Model, nhẹ hơn nhiều so với hàng chục nghìn công thức.

Danh sách kiểm tra khi nhận một file mới

Sao chép danh sách này và chạy qua mỗi lần nhận dữ liệu từ người khác:

• Tạo bản sao sheet gốc, đặt tên raw.
• Ctrl + End — vùng dữ liệu có bất thường không?
• Có ô gộp trong vùng dữ liệu không?
• Có dòng trống hay dòng tổng xen giữa không?
• Cột số: COUNT có bằng COUNTA không?
• Cột ngày: có ô nào căn trái không?
• Cột văn bản: LEN có bằng LEN(TRIM()) không?
• Số giá trị duy nhất có đúng như kỳ vọng không?
• Chuyển vùng thành Table (Ctrl + T) và đặt tên có nghĩa.

Bài tập thực hành

1. Tạo file bẩn rồi tự sửa. Lấy một bảng sạch, cố tình thêm khoảng trắng, đổi vài số thành chữ, gộp vài ô, chèn dòng trống. Sau đó dọn sạch lại bằng đúng quy trình trên và bấm giờ.

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

TRIM không xóa được khoảng trắng, vì sao?

Vì đó không phải khoảng trắng thường mà là CHAR(160) — non-breaking space, rất phổ biến khi dán từ web. Dùng =TRIM(SUBSTITUTE(B2, CHAR(160), " ")). Nếu vẫn còn, dữ liệu có thể chứa ký tự Unicode ẩn khác; khi đó CLEAN hoặc Power Query (Transform → Format → Trim) sẽ xử lý triệt để hơn.

Power Query có trên phiên bản Excel nào?

Có sẵn trong Excel 2016 trở lên trên Windows với tên Get & Transform Data trong thẻ Data. Excel 2010 và 2013 cần cài add-in riêng. Trên Excel cho Mac, Power Query có nhưng ít tính năng hơn bản Windows.

Làm sạch bằng công thức hay bằng Power Query?

Câu hỏi quyết định là: việc này có lặp lại không? Làm một lần → công thức hoặc thao tác tay nhanh hơn. Làm lại mỗi tuần/tháng với file cùng cấu trúc → Power Query, vì lần sau chỉ tốn một cú bấm Refresh.

Xóa trùng rồi mới phát hiện xóa nhầm thì sao?

Ctrl + Z ngay nếu chưa đóng file. Đây chính là lý do phải giữ sheet raw nguyên vẹn — thói quen này cứu bạn nhiều lần hơn bạn nghĩ.

Vì sao số nhìn giống nhau mà VLOOKUP không khớp?

Một bên là số, một bên là chữ. Kiểm tra bằng =ISTEXT(B2) và =ISNUMBER(D2). Sửa bằng Text to Columns cho cột bị lưu dạng chữ, hoặc ép cả hai về cùng kiểu trong công thức tra cứu.

Có cách nào chuẩn hóa tên tiếng Việt viết hoa lộn xộn không?

=PROPER(TRIM(LOWER(B2))) xử lý được phần lớn trường hợp. Nhưng hãy kiểm tra thủ công các trường hợp đặc biệt như "TP.HCM", "ĐH Bách Khoa", hoặc tên có chữ đệm viết tắt — PROPER sẽ viết sai chúng. Với danh sách quan trọng, dùng PROPER để làm nháp rồi soát lại bằng mắt.

Nguồn tham khảo

• Split text into different columns with the Convert Text to Columns Wizard – Microsoft Support
• About Power Query in Excel – Microsoft Support
• Unpivot columns (Power Query) – Microsoft Support
• Filter for unique values or remove duplicate values – Microsoft Support

Kết luận

Dữ liệu bẩn không phải sự cố bất thường — nó là trạng thái mặc định của mọi file bạn nhận được từ người khác. Người làm việc hiệu quả không phải người gặp ít dữ liệu bẩn hơn, mà là người có một quy trình cố định để dọn.

Hãy nhớ ba điều: luôn giữ một bản raw nguyên vẹn, kiểm tra trước khi sửa, và chuyển sang Power Query ngay khi công việc lặp lại lần thứ ba.

Bài viết liên quan

• PivotTable từ A đến Z
• VLOOKUP, XLOOKUP và INDEX MATCH
• Data Validation và Conditional Formatting
• 15 lỗi Excel thường gặp