Tổng hợp các phương pháp đơn giản làm sạch dữ liệu trong Excel

Không phải lúc nào bạn cũng có quyền kiểm soát định dạng và kiểu dữ liệu mà bạn nhập từ nguồn dữ liệu bên ngoài (như cơ sở dữ liệu, tệp văn bản hoặc trang Web) vào Excel vì vậy nó thường xuyên xảy ra lỗi mỗi khi bạn muốn thực hiện bất cứ thao tác nào. Vì vậy, trước khi phân tích dữ liệu, bạn phải loại bỏ những phần bị lỗi trong Excel. Trong bài này, Gitiho sẽ giới thiệu cho bạn những cách đơn giản để làm sạch dữ liệu trong Excel.

Biến Excel thành công cụ phân tích dữ liệu chuyên sâu.

Làm sạch dữ liệu bằng cách xóa bỏ khoảng trắng thừa

Trong Excel, các khoảng trống thừa thường rất khó phát hiện, đặc biệt là khi file của bạn nhiều thông tin khác nhau. Mặc dù bạn có thể phát hiện ra các khoảng trắng thừa giữa các từ hoặc số bằng mắt, nhưng có các dấu cách ở cuối thậm chí không hiển thị. Vì vậy, một cách để loại bỏ những khoảng trống không cần thiết này là sử dụng hàm TRIM

Công thức hàm TRIM trong Excel:

=TRIM(Text)

Trong đó: 

• TRIM là lệnh thực hiện thao tác.
• text là ô dữ liệu có đoạn văn bản chứa khoảng trắng cần loại bỏ.

Dưới đây là một ví dụ về hàm TRIM để làm sạch dữ liệu: 

 

Có thể thấy, hàm TRIM đã loại bỏ các khoảng trắng đầu và cuối cũng như các khoảng trắng bổ sung giữa các từ (ngoại trừ các khoảng trắng đơn).

Lưu ý: Nếu bạn sử dụng hàm TRIM rồi nhưng vẫn bị lỗi là do có nhiều loại kí tự không nhìn thấy hoặc kí tự không in được (xuống dòng, tab, v.v…). Khi đó, chúng ta dùng kết hợp hàm TRIM và hàm CLEAN để làm sạch dữ liệu như hình dưới đây:

 

 

Xem thêm: Hướng dẫn cách xóa khoảng trắng trong Excel bằng hàm Trim kết hợp hàm Clear

Chọn và xử lý tất cả các ô trống để làm sạch dữ liệu

Chúng ta thường gặp vấn đề với các ô trống trong tập dữ liệu được sử dụng để tạo báo cáo hoặc Dashboard. Bạn có thể muốn điền vào tất cả các ô trống bằng '0' hoặc 'Không có sẵn' hoặc có thể chỉ muốn đánh dấu nó. Nếu bạn có một tập dữ liệu lớn, thực hiện việc này theo cách thủ công có thể mất hàng giờ. Tuy nhiên, có một cách giúp bạn chọn được tất cả các ô trống cùng một lúc.

• Chọn toàn bộ dữ liệu
• Nhấn tổ hợp phím Ctrl + G (thao tác này sẽ mở hộp Go to)
• Nhấp vào nút Special… để có những tùy chọn đặc biệt

 

• Chọn Blanks và ấn OK 

 

 

Thao tác này sẽ chọn tất cả các ô trống trong tập dữ liệu của bạn. Nếu bạn muốn nhập 0 hoặc Không có sẵn trong tất cả các ô trống này, chỉ cần nhập vào và nhấn Ctrl + Enter. Như vậy, có thể thấy đây là một cách rất đơn giản giúp bạn làm sạch dữ liệu một cách dễ dàng.

Làm sạch dữ liệu bằng cách chuyển đổi định dạng văn bản

Khi bạn nhập dữ liệu từ tệp văn bản hoặc cơ sở dữ liệu bên ngoài, các số được lưu trữ dưới dạng văn bản. Ngoài ra, một số người có thói quen sử dụng dấu nháy đơn (') trước một số để làm cho nó có thành văn bản. Điều này có thể tạo ra các vấn đề nghiêm trọng nếu bạn đang sử dụng các ô này trong phần tính toán. Dưới đây là một cách để bạn làm sạch dữ liệu bằng cách chuyển những số này được lưu trữ dưới dạng văn bản trở lại thành số:

• Trong bất kỳ ô trống nào, hãy nhập số 1
• Chọn ô mà bạn đã nhập 1 và nhấn Control + C

 

• Chọn tất cả những ô mà bạn muốn chuyển đổi thành dạng số
• Chọn Paste Special ( Phím tắt Bàn phím: Alt + E + S ). 
• Trong hộp Paste Special, chọn Multiply và OK

 

Vậy là bạn đã chuyển đổi tất cả các số ở định dạng văn bản trở lại định dạng số mà mình mong muốn.

Làm sạch dữ liệu bằng cách loại bỏ những dữ liệu lặp lại (Remove Duplicates)

Có thể có 2 phương pháp mà bạn có thể xử lí dữ liệu trùng lặp là Đánh dấu hoặc Xóa dữ liệu đó

Đánh dấu dữ liệu trùng lặp

• Chọn toàn bộ dữ liệu bạn muốn đánh dấu sự lặp lại
• Chọn Home -> Conditional Formatting –> Highlight Cells Rules –> Duplicate Values.

 

 

• Định dạng và chọn màu đánh dấu cho các giá trị trùng lặp.
• Nhấn OK

 

Xóa dữ liệu trùng lặp để làm sạch dữ liệu

• Chọn toàn bộ phần dữ liệu mà bạn muốn xóa bỏ.
• Chọn Data -> Chọn biểu tượng của Remove Duplicates.

 

• Chọn các cột mà bạn muốn làm sạch dữ liệu

 

 

• Bấm OK. 

 

Vậy là bạn đã loại bỏ các giá trị trùng lặp khỏi danh sách. 

 

Chú ý: Công cụ Remove Duplicates trong Excel sẽ xóa tất cả những dữ liệu lặp lại, nên tốt nhất bạn hãy làm 1 bản copy của dữ liệu đó trước khi thực hiện những bước tiếp theo.

Xem thêm: Cách xóa dữ liệu trùng lặp bằng Advance Filter

Làm sạch dữ liệu bằng cách thay đổi kiểu chữ

Khi bạn nhận được một tập dữ liệu hoặc nhập dữ liệu từ các tệp văn bản, có thể tên hoặc tiêu đề thường không nhất quán. Đôi khi tất cả văn bản có thể ở dạng viết thường/viết hoa hoặc có thể trộn lẫn của cả viết thường và viết hoa. Điều đó gây ra mất thẩm mỹ cũng như khó lọc dữ liệu trong khi bạn sử dụng. Bạn có thể dễ dàng làm sạch dữ liệu bằng cách cho tất cả đều nhất quán bằng cách sử dụng ba hàm sau:

• Hàm LOWER(): Chuyển đổi tất cả văn bản thành Chữ thường.

 

• Hàm UPPER(): Chuyển đổi tất cả văn bản thành Chữ hoa.

• Hàm PROPER(): Chuyển đổi tất cả Văn bản thành Chữ hoa phù hợp

 

Làm sạch dữ liệu bằng cách xóa tất cả các định dạng

Thông thường, mỗi cơ sở dữ liệu đều có định dạng dữ liệu riêng. Khi bạn có tất cả dữ liệu định dạng khác nhau thì bạn có thể sử dụng cách làm sạch dữ liệu dưới đây để xóa tất cả định dạng cùng một lúc:  

• Chọn toàn bộ tập dữ liệu bạn cần chỉnh sửa
• Chọn Home -> Clear -> Clear Formats

 

 

Thật đơn giản để bạn làm sạch dữ liệu bằng cách thống nhất các định dạng trong toàn bộ tập dữ liệu của mình.

Xem thêm: 25 kỹ năng Excel mà bạn nên biết từ cơ bản, trung cấp đến nâng cao

Kết luận

Qua bài viết này chúng ta có thể thấy có rất nhiều phương pháp khác nhau để làm sạch dữ liệu và khắc phục các lỗi thường gặp trong Excel. Việc làm sạch dữ liệu sẽ giúp bạn dễ dàng tìm kiếm thông tin và làm cho bài Excel của bạn chuyên nghiệp hơn. Nếu bạn đã thử dùng và thành công thì đừng ngại ngần bình luận ở phía dưới cho Gitiho biết nhé!

Đừng quên theo dõi chúng mình để xem thêm các bài viết bổ ích về những kĩ năng khác trong Excel nhé!