7 cách dọn dẹp dữ liệu trong Microsoft Excel

Thứ năm - 13/07/2023 09:26

Lỗi chính tả, khoảng trắng thừa, trùng lặp, lỗi công thức và ô trống đều có thể gây rắc rối khi phân tích dữ liệu bảng tính. Để dọn dẹp bảng tính Microsoft Excel của bạn, hãy thử một hoặc nhiều cách sau đây để xử lý dữ liệu.

1. Loại bỏ khoảng trắng thừa

Cho dù từ nhập hoặc gõ sai, bạn có thể có thêm khoảng trống trong dữ liệu của mình. Điều này có thể bao gồm khoảng trắng ở đầu ô, dấu cách ở cuối ô hoặc khoảng trắng linh tinh giữa các từ hoặc giá trị. Mặc dù chúng có vẻ không quan trọng, nhưng bạn có thể dễ gặp sự cố khi lọc, sắp xếp hoặc tạo dữ liệu.

Có hai cách đơn giản để loại bỏ khoảng trắng thừa trong trang tính Excel của bạn: hàm TRIM và công cụ Find and Replace.

Sử dụng hàm TRIM

Hàm TRIM trong Excel hoạt động đặc biệt tốt để xóa khoảng trắng khỏi dữ liệu của bạn. Cú pháp của công thức đơn giản là TRIM(text). Nhập tham chiếu ô hoặc văn bản cho đối số.

Sử dụng công cụ Find and Replace

Nếu khoảng trắng nằm rải rác trên trang tính, bạn có thể muốn sử dụng công cụ Find and Replace của Excel thay vì hàm TRIM. Với phương pháp này, bạn có thể xóa tất cả hoặc một số khoảng trắng cụ thể.

1. Chuyển đến tab “Home”, mở menu drop-down “Find & Select” trong phần Editing và chọn “Replace”.

Tùy chọn Replace trong menu Find & Select

2. Trong hộp Find and Replace xuất hiện, hãy sử dụng hộp “Find what” để nhập một khoảng trắng nếu bạn muốn xóa tất cả hoặc số lượng khoảng trắng chính xác mà bạn muốn xóa.

3. Trong hộp “Replace with”, không nhập gì nếu bạn muốn xóa tất cả khoảng trắng hoặc số lượng khoảng trắng chính xác mà bạn muốn giữ lại.

Thay thế trường Replace with bằng một khoảng trắng

4. Chọn “Replace All” để xóa các khoảng trắng thừa và bạn sẽ thấy trang tính của mình cập nhật ngay lập tức.

2. Xóa các ký tự không in được

Các ký tự không in được tương tự như các khoảng trắng thừa. Đây là 32 ký tự đầu tiên, kể cả số 0, trong mã ASCII 7 bit dành cho những thứ như ký tự rỗng, phần đầu của tiêu đề, nguồn cấp biểu mẫu, dấu xuống dòng và dấu tách đơn vị. Giống như khoảng trắng, các ký tự này có thể gây ra sự cố khi tính toán công thức, sắp xếp hoặc lọc trong trang tính Excel của bạn.

Thuận tiện là bạn có thể sử dụng một hàm đơn giản để loại bỏ các ký tự không in được trong Excel. Hàm này được gọi là CLEAN và cú pháp của công thức là CLEAN(text), trong đó bạn có thể nhập văn bản hoặc tham chiếu ô cho đối số.

Ví dụ này có các ký tự không in được CHAR(10) ở đầu và cuối công thức trong ô A1.

Sử dụng công thức này để loại bỏ chúng:

=CLEAN(A1)

Bạn cũng có thể nhập văn bản như một phần của hàm CLEAN. Sử dụng công thức sau nếu bạn muốn thay thế văn bản hiện có bằng văn bản gọn hơn.

=CLEAN(CHAR(10)&"John Jacob Smith"&CHAR(10))

Các ký tự không in được loại bỏ, chỉ để lại văn bản.

3. Xóa hàng trống

Nếu bạn có các hàng trống trong trang tính của mình, những hàng này có thể chiếm dung lượng rất cần thiết cho dữ liệu thực tế. Bạn có thể loại bỏ các hàng trống trong Excel chỉ với vài bước và lấy lại không gian trong trang tính của mình.

Tham khảo: Cách thêm hàng, xóa hàng trong Excel để biết chi tiết cách thực hiện.

4. Tìm và loại bỏ dữ liệu trùng lặp

Dữ liệu trùng lặp là thứ mà bạn có thể muốn xóa trong trang tính Excel của mình. Đó có thể là tên khách hàng, địa chỉ email, số điện thoại hoặc thứ gì đó tương tự.

Có một số cách để tìm và loại bỏ dữ liệu trùng lặp trong Microsoft Excel và bạn có thể xem hướng dẫn: Cách xóa dữ liệu, nội dung trùng lặp trong Excel để biết các phương pháp khác nhau.

5. Xóa định dạng

Nếu có nhiều người làm việc trên một trang tính hoặc cùng sao chép và dán dữ liệu từ một vị trí khác, thì tài liệu có thể có thêm nhiều định dạng mà bạn không cần. Điều này có thể bao gồm phông chữ đậm, tô màu hoặc đường viền. May mắn thay, bạn không phải thay đổi từng ô, hàng hoặc cột. Bạn có thể xóa định dạng trong trang tính của mình bằng một cú nhấp chuột.

Lưu ý rằng nếu bạn thiết lập định dạng có điều kiện trong trang tính của mình, việc xóa định dạng như được giải thích ở đây cũng sẽ xóa định dạng đó.

1. Chọn phạm vi ô chứa định dạng bạn muốn xóa. Ví dụ đã áp dụng chữ đậm, màu và chữ nghiêng cho văn bản cùng với màu tô và đường viền.

Định dạng ô có phông chữ và đường viền trong Excel

2. Chuyển đến tab “Home”, mở menu drop-down “Clear” và chọn “Clear Formats”.

3. Bạn sẽ thấy tất cả định dạng trong các ô đó biến mất.

6. Chuyển văn bản thành cột

Khi bạn lấy dữ liệu từ một nguồn khác, không phải lúc nào dữ liệu cũng được trình bày theo cách bạn muốn. Bạn có thể có những hàng với dữ liệu cần được đặt vào các ô riêng biệt trong các cột. Bạn có thể chuyển đổi văn bản này thành các cột chỉ trong vài bước để dễ dàng thao tác hơn.

1. Chọn các ô bạn muốn chuyển đổi, chuyển đến tab “Data” và chọn “Text to Columns” trong nhóm Data Tools.

2. Khi Convert Text to Column Wizard mở ra, hãy thực hiện một vài bước để chuyển đổi dữ liệu của bạn dựa trên trạng thái hiện tại và cách bạn muốn hiển thị dữ liệu đó. Bắt đầu bằng cách chọn “Delimited” hoặc “Fixed width”. Excel cung cấp cho bạn đề xuất dựa trên dữ liệu của bạn, nhưng bạn có thể chọn bất kỳ tùy chọn nào phù hợp nhất. Bấm Next.

3. Tùy thuộc vào tùy chọn bạn chọn trong bước đầu tiên, bạn sẽ thấy bước thứ hai tương ứng. Ví dụ, nếu chọn Delimited data, bạn có thể chọn dấu phân cách và nếu chọn Fixed data, bạn có thể điều chỉnh độ rộng cột. Bấm Next.

4. Chọn định dạng dữ liệu cho cột, chẳng hạn như văn bản hoặc ngày và nhập đích cho dữ liệu được chuyển đổi. Nhấp vào “Finish”.

5. Bạn sẽ thấy văn bản của mình được chuyển đổi thành các cột, sẵn sàng để bạn làm việc.

Chuyển đổi văn bản thành cột trong Excel

7. Đánh dấu lỗi

Mặc dù Excel thực hiện tốt công việc chỉ ra lỗi trong các ô, chẳng hạn như các lỗi trong công thức, nhưng bạn có thể không nhận thấy những lỗi này nếu bạn có một bảng tính dài. Với định dạng có điều kiện, bạn có thể đánh dấu các lỗi để dễ dàng nhìn thấy và sửa chúng hơn.

1. Chọn toàn bộ trang tính bằng cách nhấp vào nút “Select All” (hình tam giác) ở trên cùng bên trái của trang tính. Nếu bạn chỉ muốn kiểm tra một số ô nhất định, hãy chọn những ô đó.

2. Chuyển đến tab “Home”, mở menu drop-down “Conditional Formatting” trong nhóm Styles và chọn “New Rule”.

Quy tắc mới trong menu Conditional Formatting

3. Khi cửa sổ New Formatting Rule mở ra, hãy chọn “Format only cells that contain” ở trên cùng và “Errors” trong danh sách drop-down ở dưới cùng.

Thiết lập quy tắc mới cho các ô chứa lỗi

4. Nhấp vào nút “Format” để chọn định dạng bạn muốn áp dụng. Bạn có thể thực hiện những việc như tô màu cho các ô, in đậm văn bản hoặc thêm đường viền đậm. Chọn “OK” sau khi bạn thiết lập định dạng.

Các tùy chọn định dạng cho một quy tắc mới trong Excel

5. Bạn sẽ thấy bản xem trước lựa chọn của mình ở cuối cửa sổ New Formatting Rule. Nhấp vào “OK” để lưu và áp dụng quy tắc.

6. Khi xem trang tính hoặc các ô đã chọn, bạn sẽ thấy các lỗi đó xuất hiện. Sau khi bạn sửa lỗi, định dạng sẽ biến mất và bạn có thể chuyển sang lỗi tiếp theo.