Nội dung
Một trong những tác vụ tốn thời gian và khó chịu nhất khi làm việc với văn bản trong Excel là phân tích cú pháp - phân tích cú pháp "cháo" chữ và số thành các thành phần và trích xuất các đoạn chúng ta cần từ nó. Ví dụ:
- giải nén mã zip từ địa chỉ (thật tốt nếu mã zip luôn ở đầu, nhưng nếu không thì sao?)
- tìm số và ngày của hóa đơn từ mô tả khoản thanh toán trong bảng sao kê ngân hàng
- trích xuất TIN từ mô tả motley của các công ty trong danh sách đối tác
- tìm kiếm số xe hoặc số bài báo trong mô tả, v.v.
Thông thường trong những trường hợp như vậy, sau nửa giờ mệt mỏi chọn văn bản theo cách thủ công, bằng cách nào đó, các suy nghĩ bắt đầu nảy ra trong đầu để tự động hóa quá trình này (đặc biệt nếu có nhiều dữ liệu). Có một số giải pháp và với các mức độ phức tạp-hiệu quả khác nhau:
- Sử dụng các hàm văn bản Excel tích hợp sẵn để tìm kiếm văn bản cắt keo: LEVSIMV (TRÁI), QUYỀN (ĐÚNG), PSTR (giữa), TIỀN (NỐI TIẾP) và các chất tương tự của nó, PHỐI HỢP (LIÊN KẾT), CHÍNH XÁC (CHÍNH XÁC) vv Phương pháp này tốt nếu có một logic rõ ràng trong văn bản (ví dụ: chỉ mục luôn ở đầu địa chỉ). Nếu không, các công thức trở nên phức tạp hơn nhiều và đôi khi, nó thậm chí là công thức mảng, điều này làm chậm rất nhiều trên các bảng lớn.
- Sử dụng như toán tử tương tự văn bản từ Visual Basic được bao bọc trong một hàm macro tùy chỉnh. Điều này cho phép bạn triển khai tìm kiếm linh hoạt hơn bằng cách sử dụng các ký tự đại diện (*, #,?, V.v.) Thật không may, công cụ này không thể trích xuất chuỗi con mong muốn từ văn bản - chỉ kiểm tra xem nó có được chứa trong đó hay không.
Ngoài cách trên, có một cách tiếp cận khác rất nổi tiếng trong giới lập trình viên chuyên nghiệp, nhà phát triển web và các công nghệ khác - đây là biểu thức chính quy (Biểu thức chính quy = RegExp = “regexps” = “biểu thức chính quy”). Chỉ cần đặt, RegExp là một ngôn ngữ sử dụng các ký tự và quy tắc đặc biệt để tìm kiếm các chuỗi con cần thiết trong văn bản, trích xuất chúng hoặc thay thế chúng bằng văn bản khác. Biểu thức chính quy là một công cụ rất mạnh mẽ và đẹp mắt, vượt qua tất cả các cách khác để làm việc với văn bản theo thứ tự độ lớn. Nhiều ngôn ngữ lập trình (C #, PHP, Perl, JavaScript…) và trình soạn thảo văn bản (Word, Notepad ++…) hỗ trợ các biểu thức chính quy.
Rất tiếc, Microsoft Excel không hỗ trợ RegExp, nhưng điều này có thể dễ dàng khắc phục bằng VBA. Mở Visual Basic Editor từ tab nhà phát triển (Nhà phát triển) hoặc phím tắt Khác+F11. Sau đó, chèn mô-đun mới thông qua menu Chèn - Mô-đun và sao chép văn bản của hàm macro sau vào đó:
Public Function RegExpExtract (Text As String, Pattern As String, Custom Item As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject ("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Văn bản) Sau đó Đặt các trận đấu = regex.Execute (Văn bản) RegExpExtract = trận đấu.Item (Mục - 1) Thoát Hàm Kết thúc Nếu ErrHandl: RegExpExtract = CVErr (xlErrValue) Kết thúc Hàm Bây giờ chúng tôi có thể đóng Visual Basic Editor và quay lại Excel để thử tính năng mới của chúng tôi. Cú pháp của nó như sau:
= RegExpExtract (Txt; Mẫu; Mục)
Ở đâu
- txt - một ô có văn bản mà chúng tôi đang kiểm tra và từ đó chúng tôi muốn trích xuất chuỗi con mà chúng tôi cần
- Belt Hold - mặt nạ (mẫu) để tìm kiếm chuỗi con
- Mục - số thứ tự của chuỗi con sẽ được trích xuất, nếu có một số trong số chúng (nếu không được chỉ định, thì lần xuất hiện đầu tiên được hiển thị)
Tất nhiên, điều thú vị nhất ở đây là Pattern - một chuỗi mẫu gồm các ký tự đặc biệt “bằng ngôn ngữ” của RegExp, chỉ định chính xác những gì và nơi chúng ta muốn tìm. Dưới đây là những điều cơ bản nhất để giúp bạn bắt đầu:
| Họa tiết | Mô tả |
| . | Đơn giản nhất là một dấu chấm. Nó khớp với bất kỳ ký tự nào trong mẫu tại vị trí được chỉ định. |
| s | Bất kỳ ký tự nào giống như dấu cách (dấu cách, tab hoặc ngắt dòng). |
| S | Một biến thể chống lại của mẫu trước đó, tức là bất kỳ ký tự nào không có khoảng trắng. |
| d | Bất kỳ số nào |
| D | Một biến thể chống của biến thể trước đó, tức là bất kỳ chữ số KHÔNG nào |
| w | Mọi ký tự Latinh (AZ), chữ số hoặc dấu gạch dưới |
| W | Một biến thể phản đối của biến thể trước đó, tức là không phải tiếng Latinh, không phải là số và không phải là dấu gạch dưới. |
| [nhân vật] | Trong dấu ngoặc vuông, bạn có thể chỉ định một hoặc nhiều ký tự được phép tại vị trí được chỉ định trong văn bản. Ví dụ Nghệ thuật sẽ khớp với bất kỳ từ nào: bàn or ghế. Bạn cũng không thể liệt kê các ký tự, nhưng hãy đặt chúng thành một phạm vi được phân tách bằng dấu gạch ngang, tức là thay vì [ABCDEF] viết [AF]. hoặc thay vào đó [4567] giới thiệu [-4 7]. Ví dụ: để chỉ định tất cả các ký tự Kirin, bạn có thể sử dụng mẫu [a-yaA-YayoYo]. |
| [^nhân vật] | Nếu sau dấu ngoặc vuông mở, thêm biểu tượng "nắp" ^, thì tập hợp sẽ có ý nghĩa ngược lại - tại vị trí được chỉ định trong văn bản, tất cả các ký tự sẽ được cho phép, ngoại trừ những ký tự được liệt kê. Có, mẫu [^ ЖМ] ut sẽ tìm thấy Đường dẫn or Chất or Quên, Nhưng không Scary or Mut, ví dụ. |
| | | Toán tử Boolean OR (HOẶC LÀ) để kiểm tra bất kỳ tiêu chí nào được chỉ định. Ví dụ (từThu | sthậm chí | hóa đơn) sẽ tìm kiếm văn bản cho bất kỳ từ nào được chỉ định. Thông thường, một tập hợp các tùy chọn được đặt trong dấu ngoặc đơn. |
| ^ | Đầu dòng |
| $ | Kết thúc dòng |
| b | Cuối từ |
Nếu chúng tôi đang tìm kiếm một số ký tự nhất định, chẳng hạn như mã bưu chính gồm sáu chữ số hoặc tất cả các mã sản phẩm gồm ba chữ cái, thì chúng tôi sẽ giải cứu định lượng or định lượng là các biểu thức đặc biệt chỉ định số ký tự cần tìm. Các bộ định lượng được áp dụng cho ký tự đứng trước nó:
| lượng tử | Mô tả |
| ? | Không hoặc một lần xuất hiện. Ví dụ .? sẽ có nghĩa là bất kỳ một ký tự nào hoặc sự vắng mặt của nó. |
| + | Một hoặc nhiều mục nhập. Ví dụ d+ có nghĩa là bất kỳ số chữ số nào (tức là bất kỳ số nào từ 0 đến vô cùng). |
| * | Số lần xuất hiện không hoặc nhiều hơn, tức là bất kỳ số lượng nào. Vì thế s* có nghĩa là bất kỳ số lượng khoảng trắng hoặc không có khoảng trắng. |
| {con số} or {Number1,Number2} | Nếu bạn cần chỉ định một số lần xuất hiện được xác định chặt chẽ, thì nó được chỉ định trong dấu ngoặc nhọn. Ví dụ d {6} có nghĩa là đúng sáu chữ số và mẫu s {2,5} - hai đến năm khoảng trắng |
Bây giờ chúng ta hãy chuyển sang phần thú vị nhất - phân tích ứng dụng của hàm tạo và những gì chúng ta học được về các mẫu trên các ví dụ thực tế từ cuộc sống.
Trích xuất số từ văn bản
Để bắt đầu, hãy phân tích một trường hợp đơn giản - bạn cần trích số đầu tiên từ cháo chữ và số, ví dụ, sức mạnh của bộ nguồn liên tục từ bảng giá:
Logic đằng sau biểu thức chính quy rất đơn giản: d có nghĩa là bất kỳ chữ số nào và bộ định lượng + nói rằng số của họ phải là một hoặc nhiều. Dấu trừ kép phía trước hàm là cần thiết để chuyển đổi các ký tự được trích xuất thành một số đầy đủ từ dạng số dưới dạng văn bản.
Mã bưu
Thoạt nhìn, mọi thứ đều đơn giản ở đây - chúng tôi đang tìm kiếm chính xác sáu chữ số liên tiếp. Chúng tôi sử dụng một ký tự đặc biệt d cho chữ số và định lượng 6 {} cho số ký tự:
Tuy nhiên, một tình huống có thể xảy ra khi ở bên trái chỉ mục trong dòng, có một bộ số lớn khác liên tiếp (số điện thoại, TIN, tài khoản ngân hàng, v.v.) Sau đó, mùa giải thông thường của chúng ta sẽ lấy ra 6 số đầu tiên các chữ số từ nó, tức là sẽ không hoạt động chính xác:
Để ngăn điều này xảy ra, chúng tôi cần thêm một công cụ sửa đổi xung quanh các cạnh của biểu thức chính quy của chúng tôi b biểu thị sự kết thúc của một từ. Điều này sẽ làm rõ với Excel rằng phân đoạn (chỉ mục) chúng ta cần phải là một từ riêng biệt và không phải là một phần của một phân đoạn khác (số điện thoại):
Điện thoại
Vấn đề với việc tìm số điện thoại trong văn bản là có quá nhiều tùy chọn để viết số - có và không có dấu gạch ngang, thông qua dấu cách, có hoặc không có mã vùng trong ngoặc, v.v. Do đó, theo tôi, sẽ dễ dàng hơn trước tiên hãy xóa tất cả các ký tự này khỏi văn bản nguồn bằng cách sử dụng một số hàm lồng nhau THAY THẾ (THAY THẾ)để nó kết hợp với nhau thành một tổng thể duy nhất và sau đó với một d {11} kéo ra 11 chữ số liên tiếp:
ITN
Ở đây hơi phức tạp hơn một chút, vì TIN (ở Quốc gia của chúng ta) có thể là 10 chữ số (đối với pháp nhân) hoặc 12 chữ số (đối với cá nhân). Nếu bạn không tìm thấy lỗi đặc biệt, thì bạn hoàn toàn có thể hài lòng với d {10,12}Nhưng, nói một cách chính xác, nó sẽ kéo ra tất cả các số từ 10 đến 12 ký tự, tức là và nhập sai 11 chữ số. Sẽ đúng hơn nếu sử dụng hai mẫu được kết nối bởi một toán tử OR logic | (thanh dọc):
Xin lưu ý rằng trong truy vấn đầu tiên, chúng tôi tìm kiếm các số 12 bit và sau đó chỉ tìm kiếm các số 10 bit. Nếu chúng ta viết biểu thức chính quy của mình theo cách khác, thì nó sẽ kéo ra cho tất cả mọi người, ngay cả các TIN dài 12 bit, chỉ có 10 ký tự đầu tiên. Nghĩa là, sau khi điều kiện đầu tiên được kích hoạt, việc xác minh thêm sẽ không còn được thực hiện nữa:
Đây là sự khác biệt cơ bản giữa nhà điều hành | từ một hàm logic excel tiêu chuẩn OR (HOẶC LÀ), trong đó việc sắp xếp lại các đối số không làm thay đổi kết quả.
Dòng sản phẩm
Trong nhiều công ty, các số nhận dạng duy nhất được gán cho hàng hóa và dịch vụ - vật phẩm, mã SAP, SKU, v.v. Nếu có logic trong ký hiệu của chúng, thì chúng có thể dễ dàng được lấy ra khỏi bất kỳ văn bản nào bằng cách sử dụng cụm từ thông dụng. Ví dụ: nếu chúng ta biết rằng các bài báo của chúng ta luôn bao gồm ba chữ cái tiếng Anh viết hoa, một dấu gạch ngang và một số có ba chữ số tiếp theo, thì:
Logic đằng sau mẫu rất đơn giản. [AZ] - có nghĩa là bất kỳ chữ cái viết hoa nào trong bảng chữ cái Latinh. Bộ định lượng tiếp theo 3 {} nói rằng điều quan trọng đối với chúng tôi là có chính xác ba chữ cái như vậy. Sau dấu gạch ngang, chúng tôi đang đợi ba chữ số, vì vậy chúng tôi thêm vào cuối d {3}
Số tiền mặt
Theo cách tương tự như đoạn trước, bạn cũng có thể lấy giá (chi phí, VAT…) từ phần mô tả hàng hóa. Nếu số tiền, ví dụ, được biểu thị bằng dấu gạch ngang, thì:
Họa tiết d với bộ định lượng + tìm kiếm bất kỳ số nào cho đến dấu gạch ngang, và d {2} sẽ tìm kiếm xu (hai chữ số) sau.
Nếu bạn cần trích xuất không phải giá mà là VAT, thì bạn có thể sử dụng đối số tùy chọn thứ ba của hàm RegExpExtract, hàm này chỉ định số thứ tự của phần tử sẽ được trích xuất. Và, tất nhiên, bạn có thể thay thế hàm THAY THẾ (THAY THẾ) trong kết quả, hãy gạch nối vào dấu phân tách thập phân tiêu chuẩn và thêm dấu trừ kép ở đầu để Excel diễn giải VAT tìm được là một số bình thường:
Biển số xe
If you do not take special vehicles, trailers and other motorcycles, then the standard car number is parsed according to the principle “letter – three numbers – two letters – region code”. Moreover, the region code can be 2- or 3-digit, and only those that are similar in appearance to the Latin alphabet are used as letters. Thus, the following regular expression will help us to extract numbers from the text:
Thời gian
Để trích xuất thời gian ở định dạng HH: MM, biểu thức chính quy sau là phù hợp:
Sau đoạn dấu hai chấm [0-5] ngày, vì có thể dễ dàng tìm ra, hãy đặt bất kỳ số nào trong phạm vi 00-59. Trước dấu hai chấm trong ngoặc đơn, có hai mẫu hoạt động, được phân tách bằng dấu OR (dấu gạch ngang) logic:
- [0-1] ngày - bất kỳ số nào trong phạm vi 00-19
- 2 [0-3] - bất kỳ số nào trong phạm vi 20-23
Với kết quả thu được, bạn có thể áp dụng thêm hàm Excel tiêu chuẩn THỜI GIAN (ĐỘI)để chuyển đổi nó thành một định dạng thời gian dễ hiểu đối với chương trình và phù hợp cho các tính toán tiếp theo.
Kiểm tra mật khẩu
Giả sử rằng chúng ta cần kiểm tra danh sách các mật khẩu do người dùng phát minh ra để xem có đúng không. Theo quy định của chúng tôi, mật khẩu chỉ có thể chứa các chữ cái tiếng Anh (chữ thường hoặc chữ hoa) và số. Không cho phép dấu cách, dấu gạch dưới và các dấu câu khác.
Việc kiểm tra có thể được sắp xếp bằng cách sử dụng biểu thức chính quy đơn giản sau:
Trên thực tế, với một mô hình như vậy, chúng tôi yêu cầu rằng ngay từ đầu (^) Và kết thúc ($) trong văn bản của chúng tôi chỉ có các ký tự từ tập hợp được đặt trong dấu ngoặc vuông. Nếu bạn cũng cần kiểm tra độ dài của mật khẩu (ví dụ: ít nhất 6 ký tự), thì bộ định lượng + có thể được thay thế bằng khoảng “sáu hoặc nhiều hơn” trong biểu mẫu {6,}:
Thành phố từ địa chỉ
Giả sử chúng ta cần kéo thành phố từ thanh địa chỉ. Chương trình thông thường sẽ trợ giúp, trích xuất văn bản từ “g.” đến dấu phẩy tiếp theo:
Chúng ta hãy xem xét kỹ hơn mô hình này.
Nếu bạn đã đọc văn bản ở trên, thì bạn đã hiểu rằng một số ký tự trong biểu thức chính quy (dấu chấm, dấu hoa thị, dấu đô la, v.v.) có một ý nghĩa đặc biệt. Nếu bạn cần tự tìm kiếm những ký tự này, thì chúng được đặt trước bởi một dấu gạch chéo ngược (đôi khi được gọi là che chắn). Do đó, khi tìm kiếm đoạn “g.” chúng ta phải viết trong biểu thức chính quy Ông. nếu chúng ta đang tìm kiếm một điểm cộng, thì + và vv
Hai ký tự tiếp theo trong mẫu của chúng tôi, dấu chấm và dấu hoa thị định lượng, đại diện cho bất kỳ số ký tự nào, tức là bất kỳ tên thành phố nào.
Có một dấu phẩy ở cuối mẫu, vì chúng tôi đang tìm kiếm văn bản từ “g”. đến một dấu phẩy. Nhưng có thể có một số dấu phẩy trong văn bản, phải không? Không chỉ sau thành phố, mà còn sau đường phố, nhà ở, v.v ... Yêu cầu của chúng ta sẽ dừng lại ở điểm nào trong số đó? Đó là những gì dấu chấm hỏi dành cho. Nếu không có nó, biểu thức chính quy của chúng tôi sẽ lấy ra chuỗi dài nhất có thể:
Về mặt biểu thức chính quy, một mẫu như vậy là "tham lam". Để khắc phục tình huống này, cần có một dấu chấm hỏi - nó làm cho bộ định lượng mà sau đó nó có nghĩa là "keo kiệt" - và truy vấn của chúng tôi chỉ đưa văn bản đến dấu phẩy phản đầu tiên sau "g.":
Tên tệp từ đường dẫn đầy đủ
Một tình huống rất phổ biến khác là trích xuất tên tệp từ đường dẫn đầy đủ. Một biểu thức chính quy đơn giản của biểu mẫu sẽ hữu ích ở đây:
Bí quyết ở đây là việc tìm kiếm, trên thực tế, xảy ra theo hướng ngược lại - từ cuối đến đầu, bởi vì ở cuối mẫu của chúng tôi là $và chúng tôi đang tìm kiếm mọi thứ trước khi có dấu gạch chéo ngược đầu tiên từ bên phải. Dấu gạch chéo ngược được thoát ra, giống như dấu chấm trong ví dụ trước.
PS
“Về cuối cùng” tôi muốn làm rõ rằng tất cả những điều trên là một phần nhỏ của tất cả các khả năng mà biểu thức chính quy cung cấp. Có rất nhiều ký tự đặc biệt và quy tắc sử dụng chúng, và toàn bộ sách đã được viết về chủ đề này (tôi khuyên bạn nên sử dụng ít nhất cái này để bắt đầu). Theo một cách nào đó, viết biểu thức chính quy gần như là một nghệ thuật. Hầu như luôn luôn, một biểu thức chính quy được phát minh có thể được cải tiến hoặc bổ sung, làm cho nó trở nên thanh lịch hơn hoặc có thể hoạt động với nhiều loại dữ liệu đầu vào hơn.
Để phân tích và phân tích cú pháp các cụm từ thông dụng của người khác hoặc gỡ lỗi của riêng bạn, có một số dịch vụ trực tuyến tiện lợi: RegEx101, RegExr và nhiều hơn nữa
Thật không may, không phải tất cả các tính năng của biểu thức chính quy cổ điển đều được hỗ trợ trong VBA (ví dụ: tìm kiếm ngược hoặc các lớp POSIX) và có thể hoạt động với Cyrillic, nhưng tôi nghĩ rằng những gì có đủ lần đầu tiên để làm hài lòng bạn.
Nếu bạn không quen thuộc với chủ đề này và bạn có điều gì đó muốn chia sẻ, hãy để lại biểu thức chính quy hữu ích khi làm việc trong Excel trong các nhận xét bên dưới. Một tâm là tốt, nhưng hai ủng là một đôi!
- Thay thế và xóa văn bản bằng chức năng SUBSTITUTE
- Search and highlighting of Latin characters in text
- Tìm kiếm văn bản tương tự gần nhất (Ivanov = Ivonov = Ivanof, v.v.)