Tìm cách tự động hóa làm sạch dữ liệu (automated data cleaning) trong Excel? Đây là nơi đúng. Đôi khi dữ liệu thu được không sạch sẵn sàng phân tích. Cần làm sạch và chuẩn bị theo các bước. Bài viết giới thiệu 10 cách từng bước về tự động hóa làm sạch dữ liệu trong Excel.
10 Cách Tự động hóa Làm sạch Dữ liệu trong Excel
Tập dữ liệu lộn xộn chứa Tên (Name), Ngày sinh (Date of Birth), Nghề nghiệp (Profession), Lương (Salary) của một số người. Sẽ trình bày cách tự động làm sạch dữ liệu dùng tập dữ liệu này.
1. Dùng tính năng Truy vấn Năng lực (Power Query) làm sạch dữ liệu
Phương pháp đầu: dùng Tính năng Truy vấn Năng lực (Power Query Feature) cho làm sạch dữ liệu tự động. Các bước:
Các bước:
- Chọn phạm vi ô B4:D10.
- Vào tab Dữ liệu (Data) >> nhấn Từ Bảng/Phạm vi (From Table/Range).
- Hộp thoại Tạo Bảng (Create Table) mở, dữ liệu đã chọn.
- Nhấn OK.
- Trình soạn thảo Truy vấn Năng lực (Power Query Editor) hiện ra.
- Nhấn Dùng hàng đầu làm tiêu đề (Use First Row as Headers) để đặt tiêu đề.
- Để đổi chữ hoa/thường, chọn cột Tên (Name).
- Vào tab Chuyển đổi (Transform) >> Cột văn bản (Text Column) >> Định dạng (Format) >> chọn Viết hoa mỗi từ (Capitalize Each Word).
- Nhấn nút dưới để xóa hàng có ô trống.
- Chọn Xóa trống (Remove Empty).
- Nhấn Đóng & Tải (Close & Load) >> chọn Đóng & Tải đến (Close & Load To).
- Hộp thoại Nhập Dữ liệu (Import Data) mở.
- Chọn tùy chọn Trang tính mới (New worksheet).
- Nhấn OK.
- Bạn đã làm sạch dữ liệu tự động dùng Trình soạn thảo Truy vấn Năng lực.
2. Áp dụng tính năng Văn bản thành cột (Text to Columns)
Trong tập dữ liệu, cột Tên chứa cả tên và họ. Tách thành hai cột bằng Văn bản thành cột (Text to Columns) cho làm sạch dữ liệu tự động.
Các bước:
Các bước:
- Chọn phạm vi ô B5:B10.
- Vào tab Dữ liệu >> Công cụ dữ liệu (Data Tools) >> chọn Văn bản thành cột.
- Nhấn Tiếp theo (Next).
- Chọn Dấu chấm phẩy (Semicolon), Dấu phẩy (Comma), Khoảng trắng (Space) và @ trong Khác (Others) làm Dấu phân cách (Delimiters).
- Nhấn Tiếp theo.
- Nhập ô C5 làm Đích (Destination).
- Nhấn Hoàn tất (Finish).
- Kết quả: dữ liệu tách thành hai cột Tên đầu (First Name) và Họ (Last Name).
3. Dùng Flash Fill làm sạch dữ liệu tự động
Cách dùng Flash Fill cho làm sạch dữ liệu tự động. Tập dữ liệu có ký tự không mong muốn. Loại bỏ theo các bước:
Các bước:
- Nhập Jack vào cột Tên đầu (First Name).
- Vào tab Dữ liệu >> Công cụ dữ liệu >> nhấn Flash Fill.
- Kết quả: các ký tự không mong muốn đã bị loại bỏ.
4. Dùng hàm SUBSTITUTE làm sạch dữ liệu
Tiếp theo, loại bỏ ký tự không mong muốn cho làm sạch dữ liệu tự động dùng hàm SUBSTITUTE.
Các bước:
Các bước:
- Chọn ô D5.
- Nhập công thức:
=SUBSTITUTE(C5,"@", )
Trong SUBSTITUTE, ô C5 là text, "@" là old_text, rỗng (" ") là new_text.
- Nhấn ENTER và kéo thanh tràn Tự động điền (AutoFill) cho các ô còn lại.
- Cuối cùng, hàm SUBSTITUTE đã xóa ký tự không mong muốn khỏi cột D.
5. Áp dụng Tìm & Thay thế (Find & Replace)
Phương pháp 5: làm sạch dữ liệu dùng Tìm và Thay thế (Find and Replace). Dữ liệu có hai giá trị chứa “#” và hai giá trị chứa “@” và “;”. Thay bằng rỗng dùng Tìm & Thay thế.
Các bước:
- Chọn phạm vi ô B5:D10.
- Vào tab Trang chủ (Home) >> Chỉnh sửa (Editing) >> Tìm & Chọn (Find & Select) >> chọn Thay thế (Replace).
- Hộp thoại Tìm và Thay thế mở.
- Nhập “#” vào ô Tìm gì (Find what), rỗng vào Thay bằng (Replace with).
- Nhấn Thay thế (Replace).
- “#” đã bị xóa.
- Tương tự xóa “@” và “;” dùng Tìm & Thay thế.
6. Dùng Định dạng số (Number Format) làm sạch dữ liệu
Cách đổi Định dạng số (Number Format) cho làm sạch dữ liệu tự động. Một số giá trị Ngày sinh (Date of Birth) không ở định dạng Ngày (Date). Các bước:
Các bước:
- Chọn phạm vi ô C5:C10.
- Vào tab Trang chủ >> Định dạng số (Number Format) >> nhấn nút mũi tên thả xuống (drop-down).
- Chọn Ngày ngắn (Short Date).
- Bạn đã đổi Định dạng số để làm sạch dữ liệu tự động.
7. Dùng tùy chọn Xóa trùng lặp (Remove Duplicates)
Xóa giá trị trùng lặp bằng Xóa trùng lặp (Remove Duplicates) cho làm sạch dữ liệu tự động.
Các bước:
- Chọn phạm vi ô B4:D11.
- Vào tab Dữ liệu >> Công cụ dữ liệu >> chọn Xóa trùng lặp.
- Hộp thoại Xóa trùng lặp mở.
- Nhấn OK.
- Hộp khác hiện thông tin các bản trùng (duplicates).
- Nhấn OK.
- Cuối cùng, Xóa trùng lặp loại bỏ giá trị trùng khỏi tập dữ liệu.
8. Áp dụng Chuyển đến Đặc biệt (Go To Special) làm sạch dữ liệu
Dùng Chuyển đến Đặc biệt (Go to Special) phát hiện ô trống cho làm sạch dữ liệu tự động.
Các bước:
Các bước:
- Chọn phạm vi ô B4:D11.
- Vào tab Trang chủ >> Chỉnh sửa >> Tìm & Chọn >> chọn Chuyển đến Đặc biệt.
- Hộp thoại Chuyển đến Đặc biệt hiện.
- Chọn Ô trống (Blanks).
- Nhấn OK.
- Các ô trống (blank cells) được chọn.
- Bây giờ có thể đổi định dạng ô đã chọn tùy ý.
- Ví dụ: vào Trang chủ >> Màu điền (Fill Color) >> chọn Đỏ (Red).
- Đã phát hiện ô trống để làm sạch dữ liệu tự động.
9. Khớp văn bản trong danh sách (Matching Text in List)
Bạn có dữ liệu và muốn đối chiếu với danh sách khác có sẵn. Xem ảnh chụp ví dụ. Tìm người đã nghỉ việc từ bên trái. Danh sách số thành viên nghỉ ở bên phải.
Ảnh chụp ví dụ đơn giản. Dữ liệu trong phạm vi B5:D22. Mục tiêu: xác định hàng (rows) trong vùng dữ liệu xuất hiện trong danh sách Thành viên nghỉ việc (Resigned Members) ở cột F. Có thể xóa hàng không cần thiết sau.
Các bước:
- Chọn ô D5.
- Nhập công thức:
=IF(COUNTIF($F$5:$F$11,C5),"Đã nghỉ việc","")
Ở đây, hàm COUNTIF trả về 1 nếu (phần in đậm) khớp (matches) giá trị danh sách (phạm vi F5:F11) với giá trị dữ liệu (ô C5). Nếu phần này trả về 1 hoặc nhiều hơn, hàm IF trả về “Đã nghỉ việc”, ngược lại rỗng.
- Nhấn ENTER và kéo thanh tràn Tự động điền (AutoFill) cho các ô còn lại.
Công thức hiển thị “Đã nghỉ việc” nếu “Số thành viên” (Member Num) cột C tìm thấy trong danh sách “Thành viên nghỉ việc”. Nếu không tìm thấy, trả về chuỗi rỗng (empty string).
- Công thức hiển thị “Đã nghỉ việc” nếu “Số thành viên” cột C tìm thấy trong “Thành viên nghỉ việc”. Nếu không tìm thấy, trả về chuỗi rỗng.
Bạn có thể sắp xếp theo cột D, hàng của Thành viên nghỉ việc sẽ gom lại và xóa nhanh.
- Để sắp xếp theo cột D, chọn ô D5 đến D22.
- Chọn Trang chủ ⇒ Chỉnh sửa ⇒ Sắp xếp & Lọc ⇒ Sắp xếp Z đến A.
- Hộp thoại Cảnh báo sắp xếp (Sort Warning) mở.
- Chọn Mở rộng lựa chọn (Expand the selection).
- Nhấn Sắp xếp (Sort).
- File Excel mẫu trở thành như hình.
Kỹ thuật này thích ứng với các tác vụ khớp danh sách khác.
10. Dùng Kiểm tra chính tả (Spell Check) làm sạch
Bước cuối: Kiểm tra chính tả (Spell Check) cho làm sạch dữ liệu tự động. Các bước:
Các bước:
- Chọn phạm vi ô C5:C10.
- Vào tab Xem lại (Review) >> nhấn abc Chính tả (Spelling).
- Hộp thoại Chính tả (Spelling) mở.
- Chọn Quản lý (Manager).
- Nhấn Thay đổi (Change).
- Chọn Tiếp thị viên (Receptionist).
- Nhấn Thay đổi.
- Chọn Thư ký (Clerk).
- Nhấn Thay đổi.
- Hộp cảnh báo Microsoft Excel hiện.
- Nhấn OK.
- Đã dùng Kiểm tra chính tả để làm sạch dữ liệu trong Excel.
Phần Thực hành
Trong bài viết, bạn tìm thấy tệp Excel thực hành như hình dưới để tự luyện tập.
Kết luận
Bài viết trình bày các cách tự động hóa làm sạch dữ liệu trong Excel. Hy vọng hữu ích và thú vị. Nếu khó hiểu, hãy để lại bình luận. Cho biết thêm phương án khác nếu có. Ghé ExcelDemy để xem thêm bài tương tự. Cảm ơn!