Công thức dọn dẹp dữ liệu phổ biến trong Excel

công thức excel

Trong nhiều năm, tôi đã sử dụng ấn phẩm này như một nguồn tài liệu không chỉ mô tả cách thực hiện công việc mà còn để ghi chép lại để tôi tra cứu sau này! Hôm nay, chúng tôi có một khách hàng giao cho chúng tôi tệp dữ liệu khách hàng, đó là một thảm họa. Hầu như mọi trường đều được định dạng sai và; kết quả là chúng tôi không thể nhập dữ liệu. Mặc dù có một số tiện ích bổ sung tuyệt vời cho Excel để thực hiện dọn dẹp bằng Visual Basic, chúng tôi chạy Office cho Mac sẽ không hỗ trợ macro. Thay vào đó, chúng tôi tìm kiếm các công thức thẳng để hỗ trợ. Tôi nghĩ tôi sẽ chia sẻ một số trong số đó ở đây để những người khác có thể sử dụng chúng.

Xóa các ký tự không phải là số

Hệ thống thường yêu cầu số điện thoại phải được chèn vào một công thức cụ thể gồm 11 chữ số với mã quốc gia và không có dấu chấm câu. Tuy nhiên, mọi người thường nhập dữ liệu này bằng dấu gạch ngang và dấu chấm. Đây là một công thức tuyệt vời cho xóa tất cả các ký tự không phải số trong Excel. Công thức xem xét dữ liệu trong ô A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Bây giờ bạn có thể sao chép cột kết quả và sử dụng Chỉnh sửa> Dán giá trị để ghi lên dữ liệu với kết quả được định dạng đúng.

Đánh giá nhiều trường bằng OR

Chúng tôi thường xóa các bản ghi không đầy đủ từ một lần nhập. Người dùng không nhận ra rằng không phải lúc nào bạn cũng phải viết các công thức phân cấp phức tạp và thay vào đó bạn có thể viết câu lệnh OR. Trong ví dụ dưới đây, tôi muốn kiểm tra A2, B2, C2, D2 hoặc E2 để tìm dữ liệu bị thiếu. Nếu bất kỳ dữ liệu nào bị thiếu, tôi sẽ trả về 0, ngược lại là 1. Điều đó sẽ cho phép tôi sắp xếp thứ tự dữ liệu và xóa các bản ghi chưa hoàn thành.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Trường Trim và Concatenate

Nếu dữ liệu của bạn có trường Họ và Tên, nhưng lần nhập của bạn có trường tên đầy đủ, bạn có thể nối các trường với nhau một cách gọn gàng bằng cách sử dụng Kết hợp hàm tích hợp trong Excel, nhưng hãy đảm bảo sử dụng TRIM để loại bỏ bất kỳ khoảng trống nào trước hoặc sau bản văn. Chúng tôi bao bọc toàn bộ trường bằng TRIM trong trường hợp một trong các trường không có dữ liệu:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Kiểm tra địa chỉ email hợp lệ

Một công thức khá đơn giản tìm kiếm cả @ và. trong một địa chỉ email:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Trích xuất họ và tên

Đôi khi, vấn đề là ngược lại. Dữ liệu của bạn có trường tên đầy đủ nhưng bạn cần phân tích cú pháp họ và tên. Các công thức này tìm kiếm khoảng cách giữa họ và tên và lấy văn bản nếu cần. CNTT cũng xử lý nếu không có họ hoặc có mục nhập trống trong A2.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

Và họ:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Giới hạn số lượng ký tự và thêm…

Bạn đã bao giờ muốn xóa các mô tả meta của mình chưa? Nếu bạn muốn kéo nội dung vào Excel và sau đó cắt nội dung để sử dụng trong trường Mô tả Meta (150 đến 160 ký tự), bạn có thể làm điều đó bằng cách sử dụng công thức này từ Vị trí của tôi. Nó ngắt rõ ràng mô tả tại một khoảng trắng và sau đó thêm…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Tất nhiên, những điều này không có nghĩa là toàn diện… chỉ là một số công thức nhanh để giúp bạn có một khởi đầu tốt! Bạn thấy mình đang sử dụng công thức nào khác? Thêm chúng vào phần bình luận và tôi sẽ ghi công cho bạn khi cập nhật bài viết này.

Bạn nghĩ gì?

Trang web này sử dụng Akismet để giảm spam. Tìm hiểu cách xử lý dữ liệu nhận xét của bạn.