Làm chủ Regex trong Excel:Khóa học cấp tốc dành cho người dùng thành thạo

Đây là khóa học cấp tốc về Regex của chúng tôi dành cho người dùng thành thạo Excel.

regex là gì?

Regex (biểu thức chính quy) là ngôn ngữ mẫu cho văn bản. Thay vì tìm kiếm hoặc khớp một từ chính xác, biểu thức chính quy cho phép bạn mô tả hình dạng của văn bản. Trong Excel, biểu thức chính quy giống như một tổ hợp TÌM + MID + SUBSTITUTE siêu mạnh, trong đó một mẫu có thể xác thực, trích xuất hoặc xóa văn bản.

Bạn có thể khớp các cấu trúc phổ biến trong Excel bằng cách sử dụng biểu thức chính quy:địa chỉ email, số điện thoại, ngày ở nhiều định dạng hoặc bất kỳ ID nào tuân theo quy tắc.

Trong Excel hiện đại, bạn sử dụng chủ yếu với:

• REGEXTEST (kiểm tra xem mẫu có tồn tại không)
• REGEXEXTRACT (kéo phần phù hợp)
• REGEXREPLACE (thay thế/làm sạch các kết quả khớp)

Cơ bản về Regex:Mẫu xây dựng

Mẫu Regex là các chuỗi xác định những gì bạn đang tìm kiếm. Chúng kết hợp các ký tự chữ (ví dụ:“abc”) với các siêu ký tự đặc biệt. Bạn có thể sử dụng bảng này để tham khảo nhanh.

Siêu ký tự và cú pháp chính:

• Bộ định lượng (*, +, ?, {}) áp dụng cho phần tử đứng trước chúng.
• Cờ: Các hàm biểu thức chính quy của Excel không hỗ trợ các cờ nội tuyến như (?i) trong các mẫu; sử dụng đối số của hàm để phân biệt chữ hoa chữ thường (ví dụ:đối số thứ ba của REGEXTEST) hoặc sử dụng các lớp ký tự (ví dụ:[Aa]).
• Tham lam và lười biếng: Theo mặc định, các bộ định lượng là tham lam (khớp càng nhiều càng tốt). Thêm vào ? sau bộ định lượng để làm cho nó lười (ví dụ:.*?).

Các hàm Regex của Excel

Excel cung cấp ba hàm biểu thức chính quy nguyên bản. Các hàm này lấy chuỗi văn bản và mẫu biểu thức chính quy làm đầu vào và xác thực hoặc chuyển đổi văn bản dựa trên mẫu. Hãy cùng tìm hiểu từng chức năng trước khi đi sâu vào khóa học.

Cú pháp:

=REGEXTEST(text, pattern, [case_sensitivity])

Hàm này kiểm tra xem mẫu có khớp với bất kỳ phần nào của văn bản được cung cấp hay không. Trả về TRUE nếu mẫu khớp với bất kỳ vị trí nào trong văn bản; FALSE nếu không.

• văn bản: Tham chiếu văn bản hoặc ô bạn muốn kiểm tra.
• mẫu: Mẫu biểu thức chính quy (regex) mô tả văn bản bạn muốn khớp.
• [case_sensitive]: Xác định xem kết quả khớp có phân biệt chữ hoa chữ thường hay không. Theo mặc định, kết quả khớp có phân biệt chữ hoa chữ thường.
  • 0: Phân biệt chữ hoa chữ thường
  • 1: Không phân biệt chữ hoa chữ thường

Hàm này trả về TRUE nếu A1 chứa ba chữ số liên tiếp.

Cú pháp:

=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

Hàm này trích xuất văn bản phù hợp.

• [return_mode]: Một số chỉ định chuỗi bạn muốn trích xuất. Theo mặc định, chế độ trả về là 0.
  • 0: Trả về chuỗi đầu tiên khớp với mẫu
  • 1: Trả về tất cả các chuỗi khớp với mẫu dưới dạng một mảng
  • 2: Trả về các nhóm bắt giữ từ trận đấu đầu tiên dưới dạng một mảng

=REGEXEXTRACT(A1, "\d{3}-\d{3}-\d{4}")

Trích xuất một số điện thoại như “123-456-7890”.

Cú pháp:

=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

Hàm này thay thế các kết quả khớp bằng văn bản mới.

• thay thế: Văn bản bạn muốn sử dụng thay cho mẫu. Bạn có thể tham khảo các nhóm chụp với $1, $2, v.v.
• lần xuất hiện: Chỉ định phiên bản nào của mẫu bạn muốn thay thế. Theo mặc định, số lần xuất hiện là 0, thay thế tất cả các trường hợp. Số âm thay thế trường hợp đó, tìm kiếm từ cuối.

=REGEXREPLACE(A1, "\d{3}", "***")

Che giấu mọi chuỗi ba chữ số.

Các hàm này sẽ tràn nếu trả về mảng (ví dụ:trích xuất nhiều lần).

Ví dụ thực tế dành cho người dùng Excel thành thạo

Giả sử bạn có dữ liệu lộn xộn. Hãy áp dụng biểu thức chính quy cho các tình huống phổ biến.

Ví dụ 1:Xác thực địa chỉ email

Bạn có thể sử dụng REGEXTEST để gắn cờ các email hợp lệ của công ty (các kết quả trùng khớp không phân biệt chữ hoa chữ thường sẽ an toàn hơn).

Sử dụng mẫu sau để xác thực email.

• Mẫu: ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

=REGEXTEST(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")

Sử dụng định dạng có điều kiện để đánh dấu email không hợp lệ:

• Truy cập Trang chủ tab>> chọn Định dạng có điều kiện>> chọn Quy tắc mới
• Chọn Sử dụng công thức để xác định ô nào cần định dạng
• Chèn công thức sau
• Chọn Định dạng>> chọn Màu tô>> nhấp vào OK

• Địa chỉ email không hợp lệ được đánh dấu màu đỏ

Bạn cũng có thể sử dụng REGEXTEST để gắn cờ các email công ty hợp lệ.

=REGEXTEST(B2,"@company\.com$",1)

• Đối số thứ ba (1 ) làm cho kết quả khớp không phân biệt chữ hoa chữ thường, vì vậy Company.com vẫn vượt qua
• \. thoát khỏi dấu chấm. Một đơn giản. có nghĩa là “bất kỳ ký tự nào”

Ví dụ 2:Trích xuất dữ liệu từ các ghi chú lộn xộn

Giả sử bạn có dữ liệu hỗn hợp, như ID đơn hàng hoặc số điện thoại, trong ghi chú. Bạn có thể trích xuất số điện thoại hoặc ID đơn hàng bằng cách sử dụng chức năng REGEXEXTRACT.

Trích xuất ID đơn đặt hàng:

=REGEXEXTRACT(D2,"TX-\d{4}")

• Nếu một số hàng không có nó, hãy gói bằng IFERROR:

=IFERROR(REGEXEXTRACT(D2,"TX-\d{4}"),"")

Trích xuất số điện thoại:

• Dữ liệu: “Hãy gọi cho tôi theo số 123-456-7890 hoặc (987) 654-3210.”
• Mẫu: (\d{3 tính-. )]+){2}\d{4

=REGEXEXTRACT(A2, "(\d{3}[-. )]+){2}\d{4}", 0, 1)

Công thức này trích xuất số điện thoại từ văn bản.

Ví dụ 3:Chuẩn hóa số điện thoại bằng cách xóa các chữ số không có chữ số

• Trước tiên hãy tạo phiên bản chỉ có chữ số.

=REGEXREPLACE(C2,"[^\d]","")

• Bây giờ bạn có thể định dạng (ví dụ:10 chữ số cuối dưới dạng số ở Hoa Kỳ nếu có):

=LET(x,REGEXREPLACE(C2,"[^\d]",""),
d,RIGHT(x,10),
IF(LEN(d)=10,"("&LEFT(d,3)&") "&MID(d,4,3)&"-"&RIGHT(d,4),""))

Cách tiếp cận này loại bỏ tất cả các ký tự không có chữ số và định dạng số điện thoại thu được.

Ví dụ 4:Phân tích ngày tháng và định dạng lại

Bạn có thể phân tích ngày và định dạng lại nó bằng biểu thức chính quy.

• Mẫu: (\d{1,2})/(\d{1,2})/(\d{4})

=REGEXREPLACE(A2, "(\d{1,2})/(\d{1,2})/(\d{4})", "$3-$2-$1")

Công thức này phân tích ngày và sau đó định dạng lại thành định dạng hợp lệ giống ISO. Bạn cũng có thể sử dụng nhóm chụp () để tham chiếu các bộ phận thay thế.

Ví dụ 5:Dọn dẹp dữ liệu lộn xộn (ví dụ:Xóa khoảng trắng thừa)

Bạn có thể sử dụng biểu thức chính quy để dọn dẹp dữ liệu lộn xộn, chẳng hạn như xóa khoảng trắng thừa và chuẩn hóa định dạng.

Để xóa khoảng trắng thừa, hãy sử dụng mẫu sau.

• Mẫu: \s+

=REGEXREPLACE(A2, "\s+", " ")

Công thức này thay thế các khoảng trắng bằng một khoảng trắng.

Ví dụ 6:Xóa mọi thứ trong ngoặc đơn (Bao gồm cả dấu ngoặc đơn)

• Chọn một ô và chèn công thức sau:

=REGEXREPLACE(D2,"\s*\(.*?\)","")

• .*? là một người không tham lam trận đấu. Nó dừng ở dấu ngoặc đơn đóng đầu tiên thay vì dấu ngoặc cuối cùng.

Công thức này loại bỏ tất cả dữ liệu trong dấu ngoặc đơn, bao gồm cả dấu ngoặc đơn.

Ví dụ 7:Lọc các hàng chứa ID đơn hàng

=FILTER(A2:F16,REGEXTEST(D2:D16,"TX-\d{4}"))

Công thức này lọc dữ liệu dựa trên mẫu biểu thức chính quy.

Kỹ thuật Regex nâng cao trong Excel

• Nhìn trước/Nhìn sau: Khẳng định điều kiện mà không tiêu tốn văn bản.
  • Cái nhìn tích cực:(?=…) ví dụ:\d+(?=%)$ cho các số trước dấu %.
  • Sử dụng trong REGEXEXTRACT để trích xuất giá:\d+\.\d{2}(?=\sUSD)
• Nhóm không bắt giữ: (?:…) để nhóm mà không chụp.
• Thay thế: Đối với các tùy chọn, ví dụ:(http|https)://\S+ cho URL.
• Xử lý mảng: Nếu REGEXEXTRACT trả về nhiều nhóm, hãy sử dụng INDEX hoặc phạm vi tràn.
• Kết hợp với các chức năng khác: Kết hợp với IF, FILTER hoặc LAMBDA để có quy trình làm việc hiệu quả.

Mẹo và phương pháp hay nhất

• Mẫu thử nghiệm: Sử dụng các công cụ trực tuyến như Regex101.com với phiên bản “ECMAScript” (gần giống với Regex của Excel nhất).
• Hiệu suất: Regex có thể chậm trên các tập dữ liệu lớn; kiểm tra mẫu trước.
• Lỗi: Nếu không khớp, REGEXEXTRACT trả về #N/A; xử lý bằng IFNA hoặc IFERROR.
• Hạn chế: Regex của Excel dựa trên một tập hợp con, không hỗ trợ PCRE đầy đủ, vì vậy hãy tránh các tính năng nâng cao như đệ quy.
• Tìm hiểu thêm: Thực hành với tập dữ liệu thực. Regex trở nên dễ dàng hơn khi sử dụng!

Tải xuống sổ tay thực hành

Kết thúc

Người dùng thành thạo Excel có thể theo dõi khóa học về sự cố về biểu thức chính quy của chúng tôi để tăng tốc quá trình làm sạch, xác thực và tự động hóa dữ liệu. Khi bạn đã cảm thấy thoải mái với các khối xây dựng cơ bản, hãy khám phá phần nhìn phía trước và phần nhìn phía sau để khớp mẫu phức tạp hơn. Khóa học cấp tốc này giúp bạn bắt đầu:trước tiên hãy hiểu biểu thức, sau đó thử nghiệm các mẫu trong sổ làm việc trống. Regex có thể biến đổi cách bạn xử lý văn bản trong Excel!