Tìm kiếm các cách để làm sạch dữ liệu tự động trong Excel? Sau đó, đây là nơi thích hợp cho bạn. Đôi khi, dữ liệu chúng tôi nhận được không sạch và chưa sẵn sàng để phân tích. Trong những trường hợp đó, chúng ta cần làm sạch và chuẩn bị dữ liệu bằng cách làm theo một số bước. Tại đây, bạn sẽ tìm thấy 10 các cách giải thích từng bước khác nhau để có kiến thức về làm sạch dữ liệu tự động trong Excel.
10 cách làm sạch dữ liệu tự động trong Excel
Ở đây, chúng tôi có một tập dữ liệu vô tổ chức chứa Tên , Ngày sinh , Nghề nghiệp, và Mức lương của một số người. Bây giờ, chúng tôi sẽ chỉ cho bạn các cách để làm sạch dữ liệu tự động trong Excel bằng cách sử dụng tập dữ liệu này.
1. Sử dụng Tính năng Power Query để làm sạch dữ liệu trong Excel
Trong phương pháp đầu tiên, chúng tôi sẽ sử dụng Tính năng truy vấn nguồn để làm sạch dữ liệu tự động . Làm theo các bước để làm điều đó trên tập dữ liệu của riêng bạn.
Các bước:
- Đầu tiên, chọn Phạm vi ô B4:D10 .
- Sau đó, chuyển đến tab Dữ liệu >> nhấp vào Từ Bảng / Phạm vi .
- Bây giờ, Tạo bảng hộp sẽ mở ra và tập dữ liệu đã được chọn.
- Sau đó, nhấn OK .
- Tiếp theo, Power Query Editor sẽ xuất hiện.
- Sau đó, nhấp vào Sử dụng Hàng đầu tiên làm Tiêu đề để đặt tiêu đề.
- Sau đó, để thay đổi cách viết hoa văn bản, hãy chọn Tên cột.
- Bây giờ, hãy chuyển đến tab Chuyển đổi >> nhấp vào Cột văn bản >> nhấp vào Định dạng >> chọn Viết hoa từng từ .
- Tiếp theo, nhấp vào nút bên dưới để xóa các hàng có ô trống.
- Sau đó, nhấp vào Xóa trống .
- Sau đó, nhấp vào Đóng &Tải >> chọn Đóng &Tải Tới .
- Bây giờ, Nhập dữ liệu hộp sẽ mở ra.
- Tiếp theo, chọn Trang tính mới tùy chọn.
- Sau đó, nhấp vào OK .
- Do đó, bạn có thể tự động làm sạch dữ liệu sử dụng Power Query Editor .
2. Áp dụng tính năng văn bản cho cột trong Excel
Ở đây, trong tập dữ liệu, chúng tôi có cả họ và tên của một người trong cùng một cột có tiêu đề là Tên . Chúng tôi có thể tách dữ liệu thành hai cột khác nhau bằng cách sử dụng Văn bản thành cột tính năng làm sạch dữ liệu tự động trong Excel.
Đây là các bước.
Các bước:
- Đầu tiên, hãy chọn Phạm vi ô B5:B10 .
- Sau đó, chuyển đến tab Dữ liệu >> nhấp vào Công cụ dữ liệu >> chọn Văn bản thành cột .
- Sau đó, nhấp vào Tiếp theo .
- Tiếp theo, chọn Dấu chấm phẩy , Dấu phẩy , Không gian và @ trong Khác là Dấu phân cách .
- Sau đó, nhấp vào Tiếp theo .
- Bây giờ, hãy chèn Ô C5 là Điểm đến .
- Sau đó, nhấp vào Hoàn tất .
- Cuối cùng, bạn có thể thấy rằng dữ liệu đã được chia thành hai cột Tên và Họ .
3. Sử dụng Tính năng Flash Fill để làm sạch dữ liệu tự động trong Excel
Bây giờ, bạn sẽ tìm thấy cách sử dụng Tính năng tô màu cho Flash để làm sạch dữ liệu tự động trong Excel. Ở đây, chúng tôi có các ký hiệu không mong muốn khác nhau trong tập dữ liệu. Bạn có thể xóa chúng bằng cách làm theo các bước dưới đây.
Các bước:
- Đầu tiên, hãy chèn Jack trong First Nam cột e.
- Sau đó, chuyển đến tab Dữ liệu >> nhấp vào Công cụ dữ liệu >> nhấp vào Flash Fill .
- Bây giờ, bạn sẽ thấy rằng tất cả các ký hiệu khác đã bị xóa.
4. Sử dụng hàm SUBSTITUTE để làm sạch dữ liệu trong Excel
Tiếp theo, chúng tôi sẽ chỉ cho bạn cách bạn có thể xóa các ký hiệu không mong muốn để làm sạch dữ liệu tự động trong Excel bằng cách sử dụng hàm SUBSTITUTE .
Hãy làm theo các bước dưới đây để tự thực hiện.
Các bước:
- Ở phần đầu, hãy chọn Ô D5 .
- Sau đó, chèn công thức sau.
=SUBSTITUTE(C5,"@", )
Đây, trong SUBSTITUTE , chúng tôi đã chèn Ô C5 dưới dạng văn bản , “@” dưới dạng old_text và trống (““) dưới dạng new_text .
- Sau đó, nhấn ENTER và kéo xuống Xử lý điền công cụ để Tự động điền công thức cho các ô còn lại.
- Cuối cùng là hàm SUBSTITUTE đã xóa các ký hiệu không mong muốn khỏi cột D .
5. Áp dụng Tính năng Tìm &Thay thế trong Excel
Trong phương pháp thứ năm, chúng tôi sẽ xóa dữ liệu bằng cách sử dụng Tìm và thay thế trong Excel. Tại đây, chúng ta có thể thấy hai giá trị chứa “#” và hai giá trị chứa “@” và “;” . Chúng tôi sẽ thay thế giá trị này bằng giá trị trống bằng cách áp dụng Tìm và thay thế tùy chọn.
Các bước:
- Đầu tiên, chọn Phạm vi ô B5:D10 .
- Sau đó, chuyển đến tab Trang chủ >> nhấp vào Chỉnh sửa >> nhấp vào Tìm &Chọn >> chọn Thay thế .
- Bây giờ, Tìm và Thay thế hộp công cụ sẽ mở ra.
- Tiếp theo, chèn “#” trong phần Tìm gì hộp và trống trong phần Thay thế bằng hộp.
- Sau đó, nhấp vào Thay thế .
- Sau đó, bạn sẽ thấy rằng “#” đã bị xóa.
- Tương tự, bạn có thể xóa “@” và “;” từ tập dữ liệu bằng cách sử dụng Tính năng Tìm &Thay thế .
6. Sử dụng định dạng số để làm sạch dữ liệu tự động trong Excel
Bây giờ, bạn sẽ học cách thay đổi Định dạng số để làm sạch dữ liệu tự động trong Excel. Đây là một số giá trị của Ngày sinh không có trong Ngày định dạng. Thực hiện các bước được cung cấp bên dưới để làm sạch dữ liệu của riêng bạn.
Các bước:
- Đầu tiên, chọn Phạm vi ô C5:C10 .
- Sau đó, chuyển đến tab Trang chủ >> nhấp vào Định dạng số >> nhấp vào trình đơn thả xuống nút.
- Sau đó, chọn Ngày Ngắn .
- Do đó, bạn có thể thay đổi Định dạng số của tập dữ liệu để tự động làm sạch dữ liệu trong Excel.
7. Sử dụng tùy chọn Xóa bản sao để làm sạch dữ liệu
Bây giờ, chúng tôi sẽ xóa các giá trị trùng lặp bằng cách sử dụng Xóa các giá trị trùng lặp tính năng làm sạch dữ liệu tự động trong Excel.
Các bước:
- Đầu tiên, chọn Phạm vi ô B4:D11 .
- Sau đó, chuyển đến tab Dữ liệu >> nhấp vào Công cụ dữ liệu >> chọn Xóa từ trùng lặp .
- Bây giờ, Xóa các bản sao hộp thoại sẽ mở ra.
- Sau đó, nhấn OK .
- Tiếp theo, một hộp khác chứa thông tin của các bản sao sẽ xuất hiện.
- Sau đó, nhấn OK .
- Cuối cùng, Xóa các bản sao tính năng này sẽ xóa các giá trị trùng lặp khỏi tập dữ liệu.
8. Áp dụng Chuyển đến Tính năng Đặc biệt để Làm sạch Dữ liệu Tự động
Bây giờ, chúng tôi sẽ sử dụng Chuyển đến tính năng Đặc biệt để phát hiện các cách ô trống để làm sạch dữ liệu tự động trong Excel.
Đây là các bước.
Các bước:
- Ở phần đầu, hãy chọn Phạm vi ô B4:D11 .
- Sau đó, chuyển đến tab Trang chủ >> nhấp vào Chỉnh sửa >> nhấp vào Tìm &Chọn >> chọn Chuyển đến Đặc biệt .
- Bây giờ, Chuyển đến Đặc biệt hộp sẽ xuất hiện.
- Sau đó, chọn Khoảng trống .
- Tiếp theo, nhấp vào OK .
- Sau đó, bạn sẽ thấy rằng ô trống đã được chọn.
- Giờ đây, bạn có thể thay đổi định dạng của các ô đã chọn theo lựa chọn của mình.
- Tại đây, chúng tôi sẽ chuyển đến tab Trang chủ >> nhấp vào Tô màu >> chọn Đỏ dưới dạng Màu tô .
- Do đó, bạn có thể phát hiện ô trống để làm sạch dữ liệu trong Excel tự động .
9. Đối sánh văn bản trong danh sách để làm sạch dữ liệu trong Excel
Bạn có thể có một số dữ liệu và bạn muốn kiểm tra dữ liệu này chống lại một danh sách có sẵn khác . Xem ảnh chụp màn hình sau đây của ví dụ của chúng tôi. Chúng ta sẽ tìm ra những người đã từ chức từ phía bên trái của ví dụ của chúng ta. Có một danh sách ở phía bên phải các số đã từ chức.
Ảnh chụp màn hình ở trên cho thấy một ví dụ đơn giản. Dữ liệu nằm trong phạm vi B5:D22 . Mục tiêu là xác định hàng trong vùng dữ liệu xuất hiện trong Thành viên đã từ chức danh sách, trong cột F . Bạn có thể xóa những hàng không cần thiết này sau.
Các bước:
- Đầu tiên, chọn Ô D5 .
- Sau đó, chèn công thức sau.
=IF(COUNTIF($F$5:$F$11,C5),"Resigned","")
Đây, hàm COUNTIF của công thức sẽ trả về 1 nếu nó (phần in đậm) khớp với một giá trị danh sách (Dải ô F5:F11 ) với một giá trị dữ liệu (Ô C5 ). Nếu phần này trả về 1 hoặc hơn thế nữa , rồi đến hàm IF sẽ trả về “Đã từ chức” , nếu không thì không có gì.
- Sau đó, nhấn ENTER và kéo xuống Xử lý điền công cụ để Tự động điền công thức cho các ô còn lại.
Toàn bộ công thức này sẽ hiển thị từ “Đã từ chức” nếu “Số thành viên” trong cột C được tìm thấy trong “Thành viên đã từ chức” danh sách. Nếu Số thành viên không tìm thấy , nó trả về một chuỗi trống .
- Bây giờ, toàn bộ công thức này sẽ hiển thị từ “Đã từ chức” nếu “Số thành viên” trong cột C được tìm thấy trong “Thành viên đã từ chức” danh sách. Nếu số Thành viên không được tìm thấy, nó sẽ trả về một chuỗi trống .
Bạn có thể sắp xếp danh sách theo cột D , các hàng cho tất cả Thành viên đã từ chức sẽ xuất hiện cùng nhau và có thể nhanh chóng bị xóa.
- Để sắp xếp theo cột D , chỉ cần chọn Ô D5 đến D22.
- Sau đó, chọn Trang chủ ⇒ Chỉnh sửa ⇒ Sắp xếp &Bộ lọc ⇒ Sắp xếp Z thành A .
- Tiếp theo, Cảnh báo sắp xếp hộp sẽ mở ra.
- Sau đó, chọn Mở rộng lựa chọn .
- Bây giờ, hãy nhấp vào Sắp xếp .
- Sau đó, tệp mẫu Excel của chúng tôi sẽ giống như tệp này.
Kỹ thuật này có thể được điều chỉnh cho phù hợp với các loại nhiệm vụ đối sánh danh sách khác.
10. Sử dụng Kiểm tra chính tả để làm sạch trong Excel
Trong bước cuối cùng, chúng tôi sẽ hướng dẫn bạn cách Kiểm tra chính tả để làm sạch dữ liệu tự động trong Excel. Hãy làm theo các bước dưới đây để tự thực hiện.
Các bước:
- Ở phần đầu, hãy chọn Phạm vi ô C5:C10 .
- Sau đó, để chuyển đến tab Xem lại >> nhấp vào abc Spelling .
- Bây giờ, Chính tả hộp sẽ mở ra.
- Sau đó, chọn Người quản lý .
- Sau đó, nhấp vào Thay đổi .
- Tiếp theo, chọn Nhân viên lễ tân .
- Sau đó, nhấp vào Thay đổi .
- Bây giờ, chọn Thư ký .
- Sau đó, chọn Thay đổi .
- Tiếp theo, một Microsoft Excel hộp cảnh báo sẽ xuất hiện.
- Sau đó, nhấp vào OK .
- Do đó, bạn có thể sử 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 này, bạn sẽ tìm thấy một sổ làm việc Excel như hình bên dưới để thực hành độc lập.
Kết luận
Vì vậy, trong bài viết này, chúng tôi đã chỉ cho bạn các cách để làm sạch dữ liệu tự động trong Excel. Tôi hy vọng bạn thấy bài viết này thú vị và hữu ích. Nếu có điều gì khó hiểu, vui lòng để lại bình luận. Vui lòng cho chúng tôi biết nếu có thêm bất kỳ lựa chọn thay thế nào mà chúng tôi có thể đã bỏ qua. Và, hãy truy cập ExcelDemy cho nhiều bài báo như thế này. Xin cảm ơn!