Hướng dẫn cách làm sạch dữ liệu trong các tệp Excel

Khi nhập dữ liệu trong Excel, đặc biệt là dữ liệu lớn, sẽ có những sai sót có thể xảy ra như cấu trúc, vị trí, định dạng, khoảng trắng thừa … Trong bài viết này Gitiho sẽ Hướng dẫn các bạn 10 cách đơn giản để làm sạch dữ liệu trong Excel.

Xóa không gian thừa

Các khoảng trống thừa rất khó phát hiện trong Excel. Mặc dù bằng cách nào đó 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ố, nhưng các dấu cách ở cuối thậm chí không hiển thị. Bằng cách sử dụng hàm TRIM sẽ giúp bạn loại bỏ những khoảng trống thừa này.

Cú pháp: TRIM(text)

Hàm TRIM trong Excel sẽ lấy tham chiếu ô (hoặc văn bản) làm đầu vào rồi 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).

Chọn và xử lý tất cả các ô trống

Các ô trống thừa cũng là một trong yếu tố cần xóa. Bạn có thể muốn điền vào tất cả các ô trống giá trị '0' hoặc 'Không có sẵn', hoặc có thể chỉ muốn đánh dấu nó. Nếu có một tập dữ liệu lớn, việc thực hiện việc này theo cách thủ công có thể mất hàng giờ. Rất may, có một cách bạn có thể chọn tất cả các ô trống cùng một lúc.

Bước 1: Chọn toàn bộ tập dữ liệu

Bước 2: Nhấn F5 (thao tác này sẽ mở hộp thoại Go to)

Bước 3: Nhấp vào nút Special… (ở dưới cùng bên trái) để mở hộp thoại Go To Special.

Bước 4: Chọn tùy chọn Blank và bấm nút OK

Thao tác này sẽ chọn tất cả các ô trống trong tập dữ liệu. Nếu bạn muốn nhập “0” hoặc “Không có sẵn” trong tất cả các ô này, chỉ cần nhập nó và nhấn Ctrl + Enter (hãy nhớ nếu bạn chỉ nhấn Enter, giá trị chỉ được chèn vào ô hiện được chọn).

Chuyển đổi số ở dạng văn bản trở thành số

Đôi khi 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ố để nhập dưới dạng văn bản. Điều này có thể gây sai khác nghiêm trọng kết quả nếu bạn sử dụng các ô này trong tính toán. Dưới đây là 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 dạng số.

Bước 1: Trong bất kỳ ô trống nào, hãy nhập 1

Bước 2: Chọn ô mà bạn đã nhập 1 và nhấn Ctrl + C

Bước 3: Chọn ô/dải ô mà bạn muốn chuyển đổi thành số

Bước 4: Chọn Paste > Paste Special

Bước 5: Trong hộp thoại Paste Special, chọn tùy chọn Multiply

Bước 6: Bấm nút OK để chuyển đổi tất cả các số ở định dạng văn bản trở lại số.

Bạn có thể làm nhiều hơn thế với các tùy chọn thao tác dán đặc biệt Paste Special chẳng hạn như copy công thức giữ nguyên kết quả, copy định dạng, tạo liên kết bảng,... Dưới đây là nhiều cách khác nhau để nhân trong Excel bằng cách sử dụng Paste Special.

Xóa các bản sao

Có 2 điều bạn có thể làm với dữ liệu trùng lặp là đánh dấu hoặc xóa. 

- Để đánh dấu dữ liệu trùng lặp: Bạn chọn dữ liệu và truy cập vào thẻ Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Sau đó chỉ định định dạng và tất cả các giá trị trùng lặp được đánh dấu.

- Xóa các bản sao trong dữ liệu: Chọn dữ liệu và truy cập vào thẻ Data > Remove Duplicates. Nếu dữ liệu của bạn có tiêu đề, hãy đảm bảo rằng tùy chọn ở trên cùng bên phải My data has headers được chọn.

Chọn cột mà bạn muốn xóa các bản sao và bấm OK.

Điều này sẽ loại bỏ các giá trị trùng lặp khỏi danh sách. Nếu bạn muốn danh sách gốc còn nguyên vẹn, hãy sao chép và dán dữ liệu sang vị trí khác và sau đó thực hiện việc này.

Đánh dấu lỗi

Có 2 cách bạn có thể làm nổi bật lỗi trong Data của Excel:

Sử dụng định dạng có điều kiện

Bước 1: Chọn toàn bộ tập dữ liệu

Bước 2: Truy cập vào thẻ Home > Conditional Formatting > New Rule

Bước 3: Trong hộp thoại New Formatting Rule, chọn tùy chọn Format Only Cells that Contain.

Bước 4: Tại mục Rule Description, chọn Errors từ menu thả xuống

Bước 5: Chọn Format rồi bấm nút OK để làm nổi bật bất kỳ giá trị lỗi nào trong tập dữ liệu đã chọn. 

Sử dụng tùy chọn Go To Special

Bước 1: Chọn toàn bộ tập dữ liệu

Bước 2: Nhấn F5 để mở hộp thoại Go to

Bước 3: Bấm nút Special ở dưới cùng bên trái

Bước 4: Chọn Formulas và bỏ chọn tất cả các tùy chọn ngoại trừ Errors

Thao tác này sẽ chọn tất cả các ô có lỗi trong đó. Giờ đây, bạn có thể đánh dấu chúng theo cách thủ công, xóa hoặc nhập bất kỳ nội dung gì vào đó.

Đổi văn bản thành chữ thường/chữ hoa/chữ hoa phù hợp

Khi bạn sử dụng file Excel từ người khác gửi cho hoặc nhập dữ liệu từ tệp văn bản, thường tên hoặc tiêu đề sẽ không nhất quán. Đôi khi tất cả văn bản có thể ở dạng chữ thường/hoa hoặc có thể là sự kết hợp của cả hai. Bạn có thể dễ dàng làm cho tất cả đều nhất quán bằng cách sử dụng ba hàm sau:

- LOWER () - Chuyển đổi tất cả văn bản thành chữ thường.

- UPPER () - Chuyển đổi tất cả văn bản thành chữ hoa.

- PROPER () - Chuyển đổi tất cả Văn bản thành chữ hoa phù hợp.

Phân tích cú pháp dữ liệu với tính năng Text to Column 

Khi bạn lấy dữ liệu từ cơ sở dữ liệu hoặc nhập dữ liệu từ tệp văn bản, có thể xảy ra trường hợp tất cả văn bản nằm trong một ô. Trong trường hợp này bạn có thể phân tích cú pháp văn bản này thành nhiều ô bằng cách sử dụng tính năng Text to Column trong Excel.

 Bước 1: Chọn dữ liệu/văn bản bạn muốn phân tích cú pháp

Bước 2: Truy cập vào thẻ Data > Text to Column để mở trình hướng dẫn Text to Columns Wizard. 

Bước 3: Chọn kiểu dữ liệu (chọn Delimited nếu dữ liệu không có khoảng cách đều nhau và được phân tách bằng các ký tự như dấu phẩy, dấu gạch ngang, dấu chấm ..). Nhấn Next.

Bước 3: Chọn Delimiter (ký tự phân tách dữ liệu). Bạn có thể chọn dấu phân cách được xác định trước hoặc bất kỳ thứ gì khác bằng cách sử dụng tùy chọn Other. 

Bước 4: Chọn định dạng dữ liệu rồi chọn ô đích. Nếu ô đích không được chọn, ô hiện tại sẽ bị ghi đè. 

Bước 5: Bấm nút Finish để kết thúc.

Kiểm tra chính tả

Lỗi chính tả là điều khó chấp nhận trong tài liệu, nhất là đối với những người dùng chuyên nghiệp. Tuy nhiên đôi khi do yếu tố khách quan thì vẫn có sai sót khó tránh.

Sử dụng phím tắt F7 để chạy kiểm tra chính tả cho tập dữ liệu của bạn.

Xóa tất cả định dạng

Khi sử dụng Excel nếu sử dụng nhiều cơ sở dữ liệu để lấy dữ liệu. Mỗi cơ sở dữ liệu đều có định dạng dữ liệu riêng nên khi gom vào một nơi sẽ rất lộn xộn, trong trường hợp đó dưới đây là cách bạn có thể xóa tất cả các định dạng cùng một lúc:

Bước 1: Chọn tập dữ liệu

Bước 2: Truy cập vào thẻ Home > Clear > Clear Formats. 

Tương tự, bạn cũng có thể chọn để xóa các nhận xét (comments), siêu liên kết (hyperlinks) hoặc nội dung (content).  

Sử dụng tính năng Find and Replace 

Find and Replace là không thể thiếu khi nói đến việc làm sạch dữ liệu Excel. Ví dụ bạn có thể chọn và loại bỏ tất cả các số không, thay đổi các tham chiếu trong công thức, tìm và thay đổi định dạng …

Trên đây Gitiho vừa hướng dẫn các bạn 10 cách để làm sạch dữ liệu trong Excel. Nếu có thêm bất kỳ cách nào khác mà các bạn sử dụng để làm sạch dữ liệu file Excel, hãy chia sẻ với chúng tôi trong phần Comments ở phía dưới nhé. Chúc các bạn thành công!