Làm sạch dữ liệu là bước then chốt cho phân tích và tính toán dữ liệu, cũng là bước đầu tiên trong quy trình chuẩn bị và định dạng dữ liệu. Làm sạch dữ liệu có nghĩa là loại bỏ các sự không nhất quán, lỗi và định dạng không mong muốn. Cả Power Query của Microsoft Excel và hàm REGEX của Google Sheets đều là công cụ mạnh mẽ cho việc làm sạch dữ liệu nâng cao. Bài viết này sẽ hướng dẫn bạn sử dụng Power Query và hàm REGEX của Google Sheets để làm sạch dữ liệu nâng cao, đồng thời so sánh hai công cụ này.
Power Query Của Excel
Power Query là tính năng tích hợp sẵn trong Excel, cung cấp giao diện thân thiện cho việc chuyển đổi 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 công thức phức tạp. 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.
Các Hàm REGEX Của Google Sheets
Các hàm REGEX của Google Sheets sử dụng biểu thức chính quy để tìm kiếm mẫu trong dữ liệu văn bản, làm cho chúng trở nê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 chỉ định.
- =REGEXREPLACE(text, regular_expression, replacement)
- REGEXMATCH: Kiểm tra xem chuỗi văn bản có khớp với biểu thức chính quy được chỉ định hay không.
- =REGEXMATCH(text, regular_expression)
- REGEXEXTRACT: Trích xuất chuỗi con khớp từ văn bản dựa trên biểu thức chính quy.
- =REGEXEXTRACT(text, regular_expression)
Các hàm này hữu ích cho làm sạch dữ liệu, nhận diện 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, ký tự không mong muốn, lỗi, khoảng trống, v.v. Chúng tôi sẽ sử dụng Power Query của Excel và hàm REGEX của Google Sheets để trình bày quy trình làm sạch dữ liệu nâng cao, đồng thời so sánh hai phương pháp.
Loại Bỏ Ký Tự Không Mong Muốn
Làm sạch tập dữ liệu 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 khoảng trắng.
Power Query
- Chọn phạm vi dữ liệu.
- Chuyển đến tab Dữ liệu >> chọn Từ Bảng/Phạm vi.
- Trình soạn thảo Power Query sẽ hiện ra. Chọn cột. Chúng tôi chọn cột Số Điện Thoại.
- Chuyển đến tab Chuyển đổi >> chọn Thay Giá Trị.
- Hộp thoại Thay Giá Trị xuất hiện;
- Trong ô Giá Trị Cần Tìm: Nhập từng ký tự không mong muốn ((, ), -, ).
- Trong ô Thay Bằng: Để trống hoặc nhập giá trị thay thế nếu có.
- Nhấn OK.
- Chọn Đóng & Tải để đưa dữ liệu đã làm sạch trở lại Excel.
Hàm REGEXREPLACE Của Google Sheets
Hãy làm sạch dữ liệu cùng định dạng trong Google Sheets bằng hàm REGEXREPLACE. 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), "Không Hợp Lệ"))
Công thức này sẽ loại bỏ tất cả ký tự không phải số. Sau đó, nó kiểm tra xem số đã làm sạch có đúng 10 chữ số hay không. Nếu hợp lệ, định dạng thành (XXX) XXX-XXXX; ngược lại, trả về "Không Hợp Lệ".
Hàm REGEXREPLACE Của Google Sheets (Tiếp)
Bạn có thể sử dụng công thức sau để làm sạch các ký tự đặc biệt không cần thiết.
Công thức:
=REGEXREPLACE(D2, "[^a-zA-Z0-9]", "")
Mẫu [^a-zA-Z0-9] này loại bỏ bất kỳ ký tự nào không phải 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ể dùng hàm REGEXREPLACE, nó loại bỏ mọi ký tự trừ chữ cái và chữ số chỉ trong một công thức duy nhất.
Chuẩn Hóa Chữ Hoa/Chữ Thường
Tập dữ liệu có thể chứa văn bản lẫn lộn chữ hoa/chữ thường; bạn có thể chuyển tất cả các mục nhập văn bản thành chữ thường hoặc chữ hoa đầu từ.
Power Query
Mở Power Query bằng cách chọn phạm vi dữ liệu và điều hướng đến tab Dữ liệu >> chọn Từ Bảng/Phạm vi.
- Chọn cột chứa văn bản cần chuyển đổi.
- Chuyển đến tab Chuyển đổi >> chọn Viết Hoa Mỗi Từ.
Hàm PROPER Của Google Sheets
Trong Google Sheets, không có cách nào để viết hoa mỗi từ chỉ thuần túy bằng regex, vì REGEXREPLACE của Google Sheets không hỗ trợ trực tiếp hàm UPPER và LOWER trong mẫu hoặc thay thế regex. Bạn có thể 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ừ được viết hoa, và các ký tự không phải chữ cái đầu được chuyển thành chữ thường.
Tách Dữ Liệu Theo Dấu Phân Cách
Bạn có thể tách bất kỳ văn bản hoặc tên đã gộp 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 Power Query và hàm REGEX của Google Sheets.
Power Query
Mở Power Query bằng cách chọn phạm vi dữ liệu và điều hướng đến tab Dữ liệu >> chọn Từ Bảng/Phạm vi.
- Chọn cột chứa tên.
- Chuyển đến tab Trang Chủ >> từ Tách Cột > chọn Theo Dấu Phân Cách.
- Trong hộp thoại Tách Cột Theo Dấu Phân Cách;
- Trong Chọn và Nhập Dấu Phân Cách: chọn Khoảng Trắng ( ).
- Nhấn OK.
- Đổi tên các cột kết quả thành "Họ" và "Tên".
Hàm REGEXEXTRACT Của Google Sheets
Bạn có thể dùng hàm REGEXEXTRACT để tách dữ liệu theo dấu phân cách. Chèn công thức sau vào ô đã chọ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 Dùng Power Query So Với Google Sheets REGEX
Power Query hữu ích nhất cho làm sạch dữ liệu từ cơ bản đến nâng cao. Nó lý tưởng cho việc chuyển đổi dữ liệu có cấu trúc, mở rộng với giao diện thân thiện. Nó hiệu quả khi làm việc với tập dữ liệu phức tạp, kết nối có cấu trúc và hợp nhất dữ liệu.
Các hàm REGEX của Google Sheets hoàn hảo cho các thao tác văn bản nhanh chóng và trích xuất văn bản cụ thể trong từng ô riêng lẻ. Các hàm REGEX đặc biệt hữu ích nếu bạn thoải mái với cú pháp biểu thức chính quy.
Kết Luận
Power Query và hàm REGEX của Google Sheets cung cấp các công cụ bổ sung cho nhau cho 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 tập dữ liệu lớn, trong khi hàm REGEX của Google Sheets cung cấp sự linh hoạt cho các thao tác dựa trên văn bản trong tập dữ liệu nhỏ hơn. Bạn có thể sử dụng cả hai hiệu quả để làm sạch dữ liệu mạnh mẽ, 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ụ làm sạch dù bạn đang dùng Excel hay Google Sheets!
Nhận Miễn Phí Bài Tập Excel Nâng Cao Kèm Giải Pháp!