Khi tải các File Excel các nguồn bên ngoài như website, file CSV, cơ sở dữ liệu hay phần mềm bán hàng,… ta sẽ gặp trường hợp dữ liệu đầu vào không “Sạch” với các lỗi như:
- Khoảng trắng thừa, ký tự ẩn khó phát hiện
- Dòng dữ liệu bị trùng lặp
- Định dạng chữ không thống nhất (in hoa, in thường, viết hoa đầu câu…)
- Ngày tháng bị sai định dạng
- Số bị lưu dưới dạng văn bản…
Để có dữ liệu để phân tích và làm báo cáo, ta cần phải làm sạch dữ liệu trước. Dưới đây mình sẽ hướng dẫn bạn quy trình và các công cụ để làm sạch dữ liệu trong Excel:
I. Quy trình làm sạch dữ liệu trong Excel
Trước khi tiến hành làm sạch dữ liệu, nên dành vài phút để kiểm tra và sắp xếp lại dữ liệu đầu vào để tránh lỗi lan truyền khi làm sạch dữ liệu.
Quy trình làm sạch nên thực hiện như sau:
- Bước 1: Nhập dữ liệu đầu vào (mở Tab Data → Get Data để nhập từ Text/CSV/Web/Database.)
- Bước 2: Tạo bản sao lưu dữ liệu gốc để làm dữ liệu backup (chuột phải → Move or Copy → Create a copy để tạo File sao chép hoặc Ctrl + Shift + S để lưu 1 File mới).
- Bước 3: Kiểm tra cấu trúc dữ liệu bảng để đảm bảo:
- Mỗi cột chứa cùng một loại thông tin (VD: Họ tên, Ngày sinh, Doanh thu…).
- Không có hàng trống hoặc cột trống giữa dữ liệu.
- Hàng đầu tiên phải là tiêu đề cột (header).
- Toàn bộ dữ liệu nằm trong một Excel Table (Ctrl + T) để tiện lọc, sắp xếp.
- Bước 4: Tiến hành làm sạch dữ liệu với các công cụ theo thứ tự:
- Làm các bước kiểm tra đơn giản trước (chính tả, trùng lặp).
- Sau đó mới chuyển sang xử lý phức tạp (loại ký tự ẩn, tách/gộp cột…).
II. Làm sạch dữ liệu trong Excel cơ bản
1. Kiểm tra chính tả dữ liệu
Thực hiện:
- Chọn Review → Spelling (F7) để Excel tự dò lỗi chính tả.
- Nếu ta có danh mục riêng (VD: tên công ty, tên sản phẩm), hãy thêm vào Custom Dictionary để Excel nhận diện đúng.
Ví dụ: Trong cột “Công ty” có các giá trị: “Kế Toán VN“, “kế toán vn“, “Kế toán Vn”
=> Ta sẽ thêm “Kế Toán VN” vào Custom Dictionary để đảm bảo tât cả dữ liệu được thống nhất.
2. Xóa dữ liệu trùng lặp
Các dòng dữ liệu bị trùng lặp là lỗi thường gặp nhất khi lấy dữ liệu từ nhiều nguồn khác nhau.
Ví dụ: danh sách khách hàng tổng hợp từ nhiều nguồn có đến 350 dòng nhưng thực tế chỉ có 250 dòng có giá trị duy nhất và 100 dòng giá trị trùng lặp.
Để dọn sạch dữ liệu trùng lặp, ta sử dụng:
- Vào tab Data → Remove Duplicates → chọn các cột cần so sánh.
- Trước khi xóa, có thể lọc giá trị duy nhất qua Data → Advanced Filter → Unique records only để xem trước kết quả.
>>> Xem thêm: Hàm VLOOKUP lọc trùng dữ liệu trong Excel (Kèm ví dụ minh họa)
3. Tìm và thay thế các nội dung không mong muốn
Khi dữ liệu đầu vào chứa nhiều tiền tố, hậu tố hoặc ký tự lỗi. Ta có thể xử lý về dữ liệu chuẩn bằng công cụ Find & Replace:
Ví dụ Cột “Địa chỉ” có các giá trị:
Hà Nội:TP.HCM:
Ta sẽ sẽ loại bỏ ký tự : bằng cách như sau:
- Bấm Ctrl + H để mở Find & Replace
- Tại Find what: nhập
: - Tại Replace with: ta để trống.
=> Excel sẽ loại bỏ tất cả các ký tự : trong toàn bộ cột.
III. Làm sạch dữ liệu nâng cao trong Excel
1. Loại bỏ khoảng trắng và ký tự ẩn
Dữ liệu từ website hoặc phần mềm thường chứa khoảng trắng không nhìn thấy hoặc ký tự không in được (non-printable characters).
Để làm sạch ta có thể sử dụng công thức sau:
=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160),""))
Trong đó:
CLEAN()loại bỏ ký tự ẩn ASCII 0–31SUBSTITUTE(...CHAR(160),"")loại bỏ khoảng trắng đặc biệt (Unicode).TRIM()xóa khoảng trắng đầu, cuối và giữa chuỗi.
Ví dụ: ô A2 có giá trị gốc là “ Kế Toán VN ” sau khi sử dụng hàm trên sẽ trả về “Kế Toán VN”
2. Chuẩn hóa dữ liệu về quy tắc viết hoa
Các dữ liệu đầu vào rất hay gặp lỗi về quy tắc viết hoa về Họ Và Tên, Địa chỉ,… Để xử lý lỗi này ta có thể sử dụng các hàm sau:
| Hàm | Chức năng | Ứng dụng thực tế |
|---|---|---|
=LOWER(A2) |
Chuyển toàn bộ thành chữ thường | Email, tên miền |
=UPPER(A2) |
Viết in hoa toàn bộ | Mã sản phẩm |
=PROPER(A2) |
Viết hoa chữ cái đầu | Họ tên, tiêu đề |
Ví dụ: ô A2 chứa dữ liệu gốc “nguyễn thị thu hằng” thì ta sẽ viết hoa các chữ cái đầu bằng hàm =PROPER(A2) => và trả về kết quả “Nguyễn Thị Thu Hằng”
3. Thực hiện gộp và tách cột dữ liệu
- Để tách cột:
- Sử dụng công cụ Text to Colums (bằng cách vào Data => chọn Text to Colums).
- Chọn dấu phân cách: dấu phẩy, khoảng trắng, tab, dấu gạch ngang…
- Ví dụ: Cột “Họ và Tên” → tách thành “Họ” và “Tên”.
- Để gộp cột:
- Cách 1: Sử dụng công thức nối chuỗi:
=B2 & " " & C2 - Cách 2: Sử dụng hàm CONCATENATE với công thức:
=CONCATENATE(B2," ",C2) - Ví dụ: Ô B2 chứa “Nguyễn Xuân“, C2 chứa “Nam” thì gộp thành cột Họ và Tên thành “Nguyễn Xuân Nam“.
- Cách 1: Sử dụng công thức nối chuỗi:
>>> Xem thêm: 3 Cách tách 1 cột thành 2 cột hoặc nhiều cột trong Excel siêu nhanh
4. Chuẩn hóa dữ liệu số
Lỗi phổ biến khi lấy dữ liệu từ các nguồn bên ngoài về là phần giá trị số lại lưu dưới dạng văn bản khiến khi thực hiện các phép tính toán không cho ra kết quả hoặc kết quả sai. Do đó, ta cần chuẩn hóa để các cột giá trị số về đúng định dạng tương ứng.
Cách thực hiện như sau:
- Cách 1 – Sử dụng hàm
VALUE(A2)hoặc đơn giản dùng phép nhân=A2*1để chuyển về giá trị số. - Cách 2 – Sử dụng Format Cells để định dạng Number.
- Cách 3 – Sử dụng các hàm làm tròn (ROUND/MROUND/ROUNDDOWN/ROUNDUP…) để làm tròn số lên xuống.
>>> Xem thêm:
5. Chuẩn hóa định dạng ngày giờ
Ngày tháng sai định dạng là lỗi phổ biến nhất và rất hay gặp. Để chuẩn hóa dữ liệu ta sẽ sử dụng các hàm về ngày và giờ trong Excel:
| Hàm | Công dụng | Ví dụ |
|---|---|---|
DATEVALUE(A2) |
Chuyển ngày dạng text thành số sê-ri | “25/10/2025” → 45853 |
TIMEVALUE(A2) |
Chuyển giờ dạng text thành số thập phân | “14:30” → 0.6042 |
TEXT(A2,"dd/mm/yyyy") |
Định dạng lại ngày | 25/10/2025 |
DATE(YEAR(A2),MONTH(A2),DAY(A2)) |
Chuẩn hóa dữ liệu ngày | — |
- Hàm DATE trong Excel và các ứng dụng thực tiễn
- Cách sửa lỗi định dạng ngày tháng trong excel bị đảo lộn
- Cách định dạng dd/mm/yyyy trong Excel với Format Cells
IV. Chuyển đổi, đối chiếu và kiểm tra tính nhất quán của dữ liệu
1. Chuyển đổi hàng và cột
Cách thực hiện:
- Cách 1: Copy vùng dữ liệu → Home → Paste → Transpose
- Cách 2: Sử dụng hàm TRANSPOSE với công thức
=TRANSPOSE(A1:B5)
=> Giúp chuyển bảng dọc thành ngang để so sánh hoặc tổng hợp.
2. Đối chiếu/lấy dữ liệu từ các bảng
Để đối chiếu dữ liệu hoặc lấy dữ liệu theo các điều kiện, ta có thể sử dụng các hàm tra cứu trong excel:
| Hàm | Mục đích |
|---|---|
VLOOKUP() |
Tra cứu dữ liệu theo cột đầu tiên |
HLOOKUP() |
Tra cứu theo hàng đầu tiên |
INDEX() + MATCH() |
Linh hoạt, tra cứu hai chiều |
XLOOKUP() (Excel 365) |
Tra cứu hai chiều, dễ viết hơn |
>>> Xem thêm:
- Hàm VLOOKUP trong Excel: Công thức, Cách dùng và Ví dụ chi tiết
- Cách kết hợp hàm Match và Index , Vlookup, Hlookup trong Excel để tra cứu dữ liệu
V. Tự động hóa làm sạch dữ liệu trong Excel
Nếu phải thường xuyên lặp lại các việc làm sạch dữ liệu, ta có thể sử dụng các cách sau để tự động hóa:
- Cách 1 – Sử dụng Macro:
- Chọn Developer → Record Macro → Thực hiện thao tác → Stop Recording. → Lần sau chỉ cần chạy macro là Excel tự làm sạch toàn bộ.
- Cách 2 – Dùng Power Query:
- Vào Data → Get & Transform Data → From Table/Range.
- Power Query cho phép:
- Loại trùng, đổi kiểu dữ liệu
- Chia/gộp cột, lọc dữ liệu
- Lưu “kịch bản làm sạch” và tự động áp dụng khi cập nhật dữ liệu mới.
Ví dụ ta có thể sử dụng Macro để tự động làm Format dữ liệu đầu vào theo mong muốn như cách dưới đây:
Bước 1: Đầu tiên, các bạn mở file Excel chứa dữ liệu cần làm sạch lên. Sau đó các bạn chọn thẻ File trên thanh công cụ. Tiếp theo, các bạn chọn mục Options ở góc bên trái phía dưới màn hình.
Bước 2: Lúc này, cửa sổ Excel Options hiện ra. Tại danh mục trên trái các bạn chọn thẻ Customize Ribbon. Sau đó tại mục Main Tabs ở bên phải các bạn tìm tới mục Developer rồi tích chọn vào ô vuông đó. Cuối cùng các bạn nhấn OK để hoàn tất cài đặt.
Bước 3: Lúc này, thẻ Developer đã xuất hiện trên thanh công cụ. Các bạn chọn thẻ Developer. Sau đó chọn mục Record Macro. Lúc này hộp thoại Record Macro hiện ra, các bạn đặt tên rồi nhấn OK.
Bước 4: Bây giờ các thao tác của bạn sẽ được Excel bắt đầu ghi lại. Các bạn tiến hành chỉnh sửa dữ liệu theo ý muốn như chỉnh sửa kiểu chữ, size chữ, tô màu nền hay là sắp xếp theo thứ tự… Sau khi chỉnh sửa xong, các bạn nhấn chọn mục Stop Recording để dừng việc tạo Record Macro.
Bước 4: Sau khi tạo Macro xong, các bạn có thể sử dụng nó để chỉnh sửa dữ liệu một cách nhanh chóng bằng cách chọn dữ liệu cần format.
Sau đó các bạn chọn thẻ Developer => Macros => chọn Macro vừa tạo ở trên, sau đó nhấn Run. Chỉ cần như vậy là dữ liệu sẽ tự động được chỉnh sửa như trên bước tạo Macro một cách nhanh chóng.
Nếu bạn cần sử dụng Macro cho dữ liệu khác file thì vẫn tương tự như sử dụng cho dữ liệu cùng file. Các bạn chọn thẻ Developer => Macros. Sau khi Macro hiển thị, ta có thể thấy Excel gợi ý cho ta sử dụng Macro của file ta tạo ở phía trên. Các bạn chọn Macro đó và nhấn Run.
Chỉ cần như vậy là dữ liệu trong file này cũng sẽ tự động được căn chỉnh giống như bước tạo Macro ở trên.
Như vậy, bài viết trên đã hướng dẫn các bạn cách làm sạch dữ liệu thô tự động trong Excel. Hy vọng bài viết sẽ hữu ích với các bạn trong quá trình làm việc. Chúc các bạn thành công!
