Làm sạch dữ liệu là rất quan trọng để thực hiện phân tích và tính toán dữ liệu và đây là bước đầu tiên trong việc chuẩn bị và định dạng dữ liệu. Làm sạch dữ liệu có nghĩa là loại bỏ sự không nhất quán, lỗi và định dạng không mong muốn. Cả hai chức năng Power Query của Microsoft Excel và REGEX của Google Trang tính đều mạnh mẽ để dọn dẹp nâng cao. Hãy để chúng tôi chỉ cho bạn cách sử dụng hàm REGEX của Power Query và Google Trang tính để làm sạch dữ liệu nâng cao trong khi so sánh chúng.
Truy vấn Power của Excel
Power Query là một tính năng Excel tích hợp cung cấp giao diện thân thiện với người dùng để chuyển đổi dữ liệu và làm sạch dữ liệu. Nó làm sạch và định dạng dữ liệu mà không cần bất kỳ công thức phức tạp nào. Power Query nhập dữ liệu từ nhiều nguồn khác nhau và cung cấp khả năng kết nối dữ liệu mạnh mẽ. Nó có các tính năng linh hoạt để thực hiện các tác vụ làm sạch dữ liệu phức tạp.
Chức năng REGEX của Google Trang tính
Hàm REGEX của Google Trang tính sử dụng biểu thức chính quy để tìm mẫu trong dữ liệu văn bản, lý tưởng cho việc phân tích cú pháp, định dạng hoặc xác thực các mục nhập văn bản.
- REGEXREPLACE: Thay thế văn bản khớp với biểu thức chính quy bằng văn bản được chỉ định.
- =REGEXREPLACE(văn bản, biểu thức chính quy, thay thế)
- REGEXMATCH: Kiểm tra xem chuỗi văn bản có khớp với biểu thức chính quy đã chỉ định hay không.
- =REGEXMATCH(văn bản, biểu thức chính quy)
- REGEXEXTRACT: Trích xuất các chuỗi con phù hợp từ văn bản dựa trên biểu thức chính quy.
- =REGEXEXTRACT(văn bản, biểu thức chính quy)
Các chức năng này rất hữu ích cho việc làm sạch dữ liệu, nhận dạng mẫu và thao tác văn bản động.
Hãy xem xét một tập dữ liệu thô chứa định dạng sai, các ký tự không mong muốn, lỗi, khoảng trống, v.v. Bằng cách sử dụng hàm REGEX của Power Query và Google Trang tính, chúng tôi sẽ hiển thị cho bạn cách làm sạch dữ liệu nâng cao trong khi so sánh lẫn nhau.
Xóa các ký tự không mong muốn
Xóa tập dữ liệu gồm các số điện thoại chứa các ký tự không mong muốn như dấu ngoặc đơn, dấu gạch ngang hoặc dấu cách.
Power Query
- Chọn phạm vi dữ liệu.
- Đi tới Dữ liệu tab>> chọn Từ Bảng/Phạm vi .
- Power Query trình soạn thảo sẽ bật lên. Chọn cột. Chúng tôi đã chọn Số điện thoại cột.
- Đi tới Biến đổi tab>> chọn Thay thế giá trị .
- Thay thế giá trị hộp thoại sẽ bật lên;
- Trong Giá trị cần tìm hộp:Nhập riêng các ký tự không mong muốn ((, ), -, ).
- Trong phần Thay thế bằng hộp:Để trống hoặc nếu bạn có giá trị cần thay thế, bạn có thể chèn giá trị đó.
- Nhấp vào OK .
- Chọn Đóng và tải để đưa dữ liệu đã được làm sạch trở lại Excel.
Chức năng REGEXREPLACE của Google Trang tính
Hãy làm sạch dữ liệu cùng với định dạng trong Google Trang tính bằng cách sử dụng hàm REGREPLACE. Chèn công thức sau vào ô G2.
=ARRAYFORMULA(IF(LEN(REGEXREPLACE(D2:D6, "[^0-9]", ""))=10,
"(" & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 1, 3) & ") " &
MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 4, 3) & "-" &
MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 7, 4),
"Invalid"))
Công thức này sẽ loại bỏ tất cả các ký tự không phải số. Sau đó, nó sẽ kiểm tra xem số được làm sạch có đúng 10 chữ số hay không. Nếu hợp lệ, hãy định dạng nó là (XXX) XXX-XXXX; nếu không, nó sẽ trả về “Không hợp lệ”.
Chức năng REGREPLACE của Google Trang tính
Bạn có thể sử dụng công thức sau để xóa các ký tự đặc biệt không cần thiết.
Công thức:
=REGEXREPLACE(D2, "[^a-zA-Z0-9]", "")
[^a-zA-Z0-9] này mẫu sẽ xóa mọi ký tự không phải là chữ cái hoặc chữ số.
Nếu bạn quen thuộc với biểu thức chính quy, bạn có thể sử dụng hàm REGREPLACE, hàm này sẽ xóa mọi ký tự ngoại trừ chữ cái hoặc chữ số trong một công thức.
Chuẩn hóa kiểu chữ
Tập dữ liệu có thể chứa văn bản hỗn hợp; bạn có thể chuyển đổi tất cả các mục văn bản thành chữ thường hoặc chữ thường.
Power Query
Mở Power Query bằng cách chọn phạm vi dữ liệu và điều hướng đến Dữ liệu tab>> chọn Từ Bảng/Phạm vi .
- Chọn cột có văn bản cần chuyển đổi.
- Đi tới Biến đổi tab>> chọn Viết hoa từng từ .
Chức năng PHÙ HỢP của Google Trang tính
Trong Google Trang tính, không có cách nào để viết hoa từng từ hoàn toàn bằng biểu thức chính quy, vì REGEXREPLACE của Google Trang tính không hỗ trợ trực tiếp các hàm UPPER và LOWER trong các mẫu hoặc thay thế biểu thức chính quy. Bạn có thể sử dụng các hàm bên ngoài như PROPER, UPPER hoặc LOWER.
Công thức này đảm bảo mỗi từ đều được viết hoa và mọi chữ cái không phải đầu tiên đều được chuyển đổi thành chữ thường.
Chia dữ liệu theo dấu phân cách
Bạn có thể chia mọi văn bản hoặc tên đã hợp nhất thành các cột riêng biệt bằng dấu phân cách. Hãy làm sạch dữ liệu bằng cách sử dụng hàm REGEX của Power Query và Google Trang tính.
Power Query
Mở Power Query bằng cách chọn phạm vi dữ liệu và điều hướng đến Dữ liệu tab>> chọn Từ Bảng/Phạm vi .
- Chọn cột chứa tên.
- Đi tới Trang chủ tab>> từ Tách cột> chọn Theo dấu phân cách .
- Trong Chia cột theo dấu phân cách hộp thoại;
- Trong Chọn và nhập dấu phân cách: chọn một Dấu cách( ) .
- Nhấp vào OK .
- Đổi tên các cột kết quả thành “Tên” và “Họ”.
Chức năng REGEXEXTRACT của Google Trang tính
Bạn có thể sử dụng hàm REGEXEXTRACT để phân chia dữ liệu theo dấu phân cách. Chèn công thức sau vào ô đã chọn của bạn.
=REGEXEXTRACT(E9, "^([^ ]+) (.+)$")
Công thức này tách họ và tên thành hai ô. Nó sử dụng các cột bổ sung nếu cần.
Khi nào nên sử dụng Power Query so với REGEX của Google Trang tính
Power Query hữu ích nhất cho việc làm sạch dữ liệu từ cơ bản đến nâng cao. Đó là lý tưởng cho việc chuyển đổi dữ liệu có cấu trúc, rộng rãi hơn với giao diện thân thiện với người dùng. Nó hiệu quả khi làm việc với các tập dữ liệu phức tạp, các phép nối có cấu trúc và hợp nhất dữ liệu.
Các hàm REGEX của Google Trang tính hoàn hảo cho các thao tác dựa trên văn bản nhanh chóng và trích xuất văn bản cụ thể trong từng ô riêng lẻ. Hàm REGEX đặc biệt hữu ích nếu bạn cảm thấy thoải mái với cú pháp biểu thức chính quy.
Kết luận
Các hàm REGEX của Power Query và Google Trang tính cung cấp các công cụ bổ sung để làm sạch dữ liệu. Power Query lý tưởng cho người dùng Excel cần chuyển đổi hàng loạt trên các tập dữ liệu lớn, trong khi các hàm REGEX của Google Trang tính mang đến sự linh hoạt cho các thao tác dựa trên văn bản trong các tập dữ liệu nhỏ hơn. Bạn có thể sử dụng cả hai một cách hiệu quả để làm sạch dữ liệu một cách hiệu quả và hợp lý! Bằng cách hiểu rõ cả hai công cụ, bạn có thể xử lý hầu hết các tác vụ dọn dẹp cho dù bạn đang làm việc trong Excel hay Google Trang tính!
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!