Analyzujte text s regulárnymi výrazmi (RegExp) v Exceli

Jednou z najviac časovo náročných a frustrujúcich úloh pri práci s textom v Exceli je parsovanie – analyzovanie alfanumerickej „kaše“ na zložky a extrahovanie fragmentov, ktoré z nej potrebujeme. Napríklad:

• extrahovanie PSČ z adresy (je dobré, ak je PSČ vždy na začiatku, ale čo ak nie je?)
• zistenie čísla a dátumu faktúry z popisu platby vo výpise z účtu
• extrakcia DIČ z pestrých popisov spoločností v zozname protistrán
• vyhľadať číslo auta alebo číslo tovaru v popise atď.
  

Zvyčajne sa v takýchto prípadoch po pol hodine bezútešného manuálneho vyberania v texte začnú objavovať myšlienky na automatizáciu tohto procesu (najmä ak je veľa údajov). Existuje niekoľko riešení s rôznym stupňom zložitosti a účinnosti:

• Použitie vstavané textové funkcie programu Excel na vyhľadávanie-rezaného-lepiaceho textu: LEVSIMV (VĽAVO), PRÁVO (SPRÁVNY), PSTR (uprostred), STsEPIT (CONCATENATE) a jeho analógov, COMBINE (JOINTEXT), EXACT (PRESNÉ) atď. Táto metóda je dobrá, ak je v texte jasná logika (napríklad index je vždy na začiatku adresy). V opačnom prípade sa vzorce stanú oveľa komplikovanejšími a niekedy dokonca dôjde k maticovým vzorcom, čo sa pri veľkých tabuľkách výrazne spomalí.
• Použitím ako operátor podobnosti textu z jazyka Visual Basic zabalený do funkcie vlastného makra. To vám umožňuje implementovať flexibilnejšie vyhľadávanie pomocou zástupných znakov (*, #,?, atď.). Bohužiaľ, tento nástroj nedokáže extrahovať požadovaný podreťazec z textu – iba skontrolujte, či je v ňom obsiahnutý.

Okrem vyššie uvedeného existuje ešte jeden prístup, ktorý je veľmi dobre známy v úzkych kruhoch profesionálnych programátorov, webových vývojárov a iných technikov – regulárne výrazy (Regulárne výrazy = RegExp = „regexps“ = „regulars“). Jednoducho povedané, RegExp je jazyk, v ktorom sa používajú špeciálne znaky a pravidlá na vyhľadávanie potrebných podreťazcov v texte, ich extrahovanie alebo nahradenie iným textom. Regulárne výrazy sú veľmi silným a krásnym nástrojom, ktorý rádovo prekonáva všetky ostatné spôsoby práce s textom. Mnoho programovacích jazykov (C#, PHP, Perl, JavaScript…) a textových editorov (Word, Notepad++…) podporuje regulárne výrazy.

Microsoft Excel bohužiaľ nemá podporu RegExp hneď po vybalení, ale to sa dá ľahko opraviť pomocou VBA. Otvorte editor jazyka Visual Basic z karty vývojka (vývojár) alebo klávesová skratka ostatné+F11. Potom vložte nový modul cez ponuku Vložiť – Modul a skopírujte tam text nasledujúcej funkcie makra:

Verejná funkcia RegExpExtract(Text ako reťazec, vzor ako reťazec, voliteľná položka ako celé číslo = 1) Ako reťazec pri chybe Prejsť na ErrHandl Nastaviť regulárny výraz = CreateObject("VBScript.RegExp") regex.Pattern = Regulárny výraz vzoru.Globálny = True Ak regulárny výraz.Test (Text) Potom Set zhody = regex.Execute(Text) RegExpExtract = zhody.Item(Položka - 1) Koniec funkcie End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Teraz môžeme zatvoriť editor jazyka Visual Basic a vrátiť sa do Excelu, aby sme vyskúšali našu novú funkciu. Jeho syntax je nasledovná:

=RegExpExtract( Txt ; Vzor ; Položka )

kde

• txt – bunka s textom, ktorý kontrolujeme a z ktorého chceme vytiahnuť potrebný podreťazec
• vzor – maska ​​(vzor) na vyhľadávanie podreťazcov
• Položka – poradové číslo podreťazca, ktorý sa má extrahovať, ak ich je niekoľko (ak nie je zadané, zobrazí sa prvý výskyt)

Najzaujímavejší je tu samozrejme Pattern – šablónový reťazec špeciálnych znakov „v jazyku“ RegExp, ktorý špecifikuje, čo presne a kde chceme nájsť. Tu sú tie najzákladnejšie, ako začať:

Ak hľadáme určitý počet znakov, napríklad šesťmiestne PSČ alebo všetky trojpísmenové kódy produktov, prídeme na pomoc kvantifikátory or kvantifikátory sú špeciálne výrazy, ktoré určujú počet znakov, ktoré sa majú hľadať. Kvantifikátory sa aplikujú na znak, ktorý je pred ním:

Teraz prejdime k tomu najzaujímavejšiemu – rozboru aplikácie vytvorenej funkcie a toho, čo sme sa o vzoroch dozvedeli na praktických príkladoch zo života.

Extrahovanie čísel z textu

Na začiatok si analyzujme jednoduchý prípad – z alfanumerickej kaše musíte vytiahnuť prvé číslo, napríklad výkon neprerušiteľných zdrojov energie z cenníka:

Logika regulárneho výrazu je jednoduchá: d znamená ľubovoľnú číslicu a kvantifikátor + hovorí, že ich počet by mal byť jeden alebo viac. Dvojité mínus pred funkciou je potrebné na „za pochodu“ prevod extrahovaných znakov na celé číslo z čísla ako textu.

PSČ

Na prvý pohľad je tu všetko jednoduché – hľadáme presne šesť číslic v rade. Používame špeciálny znak d pre číslicu a kvantifikátor 6 {} pre počet znakov:

Je však možná situácia, keď naľavo od indexu v riadku je ďalšia veľká sada čísel v rade (telefónne číslo, DIČ, bankový účet atď.) Potom naša pravidelná sezóna vytiahne prvých 6 číslice z neho, tj nebude fungovať správne:

Aby sme tomu zabránili, musíme okolo okrajov nášho regulárneho výrazu pridať modifikátor b označujúci koniec slova. Excelu tak bude jasné, že fragment (index), ktorý potrebujeme, by mal byť samostatným slovom a nie súčasťou iného fragmentu (telefónne číslo):

Telefón

Problém s nájdením telefónneho čísla v texte je, že existuje toľko možností, ako písať čísla – so spojovníkmi a bez nich, cez medzery, s alebo bez regionálneho kódu v zátvorkách atď. Preto je podľa mňa jednoduchšie najprv vymažte všetky tieto znaky zo zdrojového textu pomocou niekoľkých vnorených funkcií NÁHRADA (NÁHRADIE)aby sa to zlepilo do jedného celku a potom s primitívnou pravidelnosťou d{11} vytiahnite 11 číslic v rade:

ITN

Tu je to trochu zložitejšie, pretože DIČ (u nás) môže byť 10-miestne (pre právnické osoby) alebo 12-miestne (pre fyzické osoby). Ak nenájdete chybu najmä, potom je celkom možné uspokojiť sa s pravidelným d{10,12}, ale striktne povedané, vytiahne všetky čísla od 10 do 12 znakov, teda a chybne zadaných 11 číslic. Správnejšie by bolo použiť dva vzory spojené logickým operátorom OR | (vertikálna lišta):

Upozorňujeme, že v dotaze najskôr hľadáme 12-bitové čísla a až potom 10-bitové čísla. Ak náš regulárny výraz napíšeme opačne, tak každému, aj dlhým 12-bitovým DIČ, vytiahne len prvých 10 znakov. To znamená, že po spustení prvej podmienky sa ďalšie overovanie už nevykonáva:

Toto je zásadný rozdiel medzi operátorom | zo štandardnej logickej funkcie Excelu OR (OR), kde preusporiadanie argumentov nezmení výsledok.

SKU produktu

V mnohých spoločnostiach sú tovarom a službám priradené jedinečné identifikátory – články, SAP kódy, SKU atď. Ak je v ich zápise logika, potom sa dajú ľahko vytiahnuť z akéhokoľvek textu pomocou regulárnych výrazov. Napríklad, ak vieme, že naše články sa vždy skladajú z troch veľkých anglických písmen, spojovníka a následného trojmiestneho čísla, potom:

Logika šablóny je jednoduchá. [AZ] – znamená akékoľvek veľké písmená latinskej abecedy. Ďalší kvantifikátor 3 {} hovorí, že pre nás je dôležité, aby také písmená boli presne tri. Za pomlčkou čakáme na tri číslice, preto pridávame na koniec d{3}

Sumy v hotovosti

Obdobným spôsobom ako v predchádzajúcom odseku si môžete z popisu tovaru vytiahnuť aj ceny (náklady, DPH...). Ak sú napríklad peňažné sumy označené pomlčkou, potom:

Pattern d s kvantifikátorom + vyhľadá ľubovoľné číslo až po spojovník a d{2} bude hľadať haliere (dve číslice).

Ak potrebujete extrahovať nie ceny, ale DPH, potom môžete použiť tretí voliteľný argument našej funkcie RegExpExtract, ktorý špecifikuje poradové číslo prvku, ktorý sa má extrahovať. A, samozrejme, funkciu môžete nahradiť NÁHRADA (NÁHRADIE) vo výsledkoch zadajte pomlčku na štandardný oddeľovač desatinných miest a na začiatok pridajte dvojité mínus, aby Excel interpretoval nájdenú DPH ako normálne číslo:

ŠPZ áut

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:

čas

Na extrahovanie času vo formáte HH:MM je vhodný nasledujúci regulárny výraz:

Po fragmente hrubého čreva [0-5]d, ako je ľahké zistiť, nastaví ľubovoľné číslo v rozsahu 00-59. Pred dvojbodkou v zátvorkách fungujú dva vzory oddelené logickým OR (zvislou čiarou):

• [0-1]d – akékoľvek číslo v rozsahu 00-19
• 2[0-3] – akékoľvek číslo v rozsahu 20-23

Na získaný výsledok môžete dodatočne použiť štandardnú funkciu Excel TIME (TEAM)previesť ho do formátu času, ktorý je pre program zrozumiteľný a vhodný pre ďalšie výpočty.

Kontrola hesla

Predpokladajme, že musíme skontrolovať správnosť zoznamu hesiel vynájdených používateľmi. Podľa našich pravidiel môžu heslá obsahovať iba anglické písmená (malé alebo veľké) a čísla. Medzery, podčiarkovníky a iné interpunkčné znamienka nie sú povolené.

Kontrola môže byť organizovaná pomocou nasledujúceho jednoduchého regulárneho výrazu:

V skutočnosti pri takomto vzore požadujeme, aby medzi začiatkom (^) a koniec ($) v našom texte boli len znaky z množiny uvedené v hranatých zátvorkách. Ak potrebujete skontrolovať aj dĺžku hesla (napríklad aspoň 6 znakov), tak kvantifikátor + možno vo formulári nahradiť intervalom „šesť a viac“. {6 XNUMX XNUMX,}:

Mesto z adresy

Povedzme, že potrebujeme stiahnuť mesto z panela s adresou. Pomôže bežný program, ktorý extrahuje text z „g“. do ďalšej čiarky:

Pozrime sa bližšie na tento vzor.

Ak ste si prečítali vyššie uvedený text, už ste pochopili, že niektoré znaky v regulárnych výrazoch (bodky, hviezdičky, znaky dolára atď.) majú osobitný význam. Ak potrebujete hľadať tieto znaky samotné, potom im predchádza spätná lomka (niekedy tzv tienenie). Preto pri hľadaní fragmentu „g.“ musíme písať regulárnym výrazom Pán. ak hľadáme plus, tak + a tak ďalej

Nasledujúce dva znaky v našej šablóne, bodka a kvantifikátorová hviezdička, predstavujú ľubovoľný počet ľubovoľných znakov, teda ľubovoľný názov mesta.

Na konci šablóny je čiarka, pretože hľadáme text z „g“. na čiarku. Ale v texte môže byť niekoľko čiarok, však? Nielen po meste, ale aj po ulici, domoch atď. Na ktorom z nich sa naša požiadavka zastaví? Na to je ten otáznik. Bez neho by náš regulárny výraz vytiahol najdlhší možný reťazec:

Z hľadiska regulárnych výrazov je takýto vzor „chamtivý“. Na nápravu situácie je potrebný otáznik – vďaka nemu je kvantifikátor, za ktorým stojí, „skúpy“ – a náš dopyt zaberá text len ​​po prvú protičiarku za „g.“:

Názov súboru z celej cesty

Ďalšou veľmi častou situáciou je extrahovanie názvu súboru z úplnej cesty. Tu pomôže jednoduchý regulárny výraz formulára:

Trik je v tom, že vyhľadávanie v skutočnosti prebieha v opačnom smere – od konca na začiatok, pretože na konci našej šablóny je $, a hľadáme všetko pred tým až po prvé spätné lomítko sprava. Opačná lomka sa vynechá, podobne ako bodka v predchádzajúcom príklade.

PS

„Na koniec“ chcem objasniť, že všetko vyššie uvedené je malá časť všetkých možností, ktoré regulárne výrazy poskytujú. Špeciálnych postáv a pravidiel na ich použitie je veľa a na túto tému sú napísané celé knihy (pre začiatok odporúčam aspoň túto). Svojím spôsobom je písanie regulárnych výrazov takmer umením. Takmer vždy sa dá vynájdený regulárny výraz vylepšiť alebo doplniť, čím sa stane elegantnejším alebo dokáže pracovať so širším rozsahom vstupných údajov.

Ak chcete analyzovať a analyzovať regulárne výrazy iných ľudí alebo ladiť svoje vlastné, existuje niekoľko pohodlných online služieb: RegEx101, RegExr viac

Žiaľ, nie všetky funkcie klasických regulárnych výrazov sú vo VBA podporované (napríklad spätné vyhľadávanie alebo triedy POSIX) a dokážu pracovať s azbukou, ale myslím si, že to, čo tam je, na prvý krát stačí na potešenie.

Ak nie ste v tejto téme nováčikom a máte sa o čo podeliť, zanechajte regulárne výrazy užitočné pri práci v Exceli v komentároch nižšie. Jedna myseľ je dobrá, ale dve topánky sú pár!

• Nahradenie a vyčistenie textu pomocou funkcie SUBSTITUTE
• Search and highlighting of Latin characters in text
• Vyhľadajte najbližší podobný text (Ivanov = Ivonov = Ivanof atď.)