Jäsennä tekstiä säännöllisillä lausekkeilla (RegExp) Excelissä

Yksi aikaa vievistä ja turhauttavimmista tehtävistä työskennellessäsi tekstin kanssa Excelissä on jäsentäminen – aakkosnumeerisen "puuron" jäsentäminen komponenteiksi ja tarvitsemamme palasten erottaminen siitä. Esimerkiksi:

• postinumeron purkaminen osoitteesta (on hyvä, jos postinumero on aina alussa, mutta entä jos se ei ole?)
• löytää laskun numeron ja päivämäärän tiliotteessa olevasta maksun kuvauksesta
• TIN-koodin poimiminen vastapuoliluettelossa olevien yritysten kirjavista kuvauksista
• etsi autonumero tai tuotenumero kuvauksesta jne.
  

Yleensä tällaisissa tapauksissa puolen tunnin ankean tekstin manuaalisen poimimisen jälkeen alkaa mieleen tulla ajatuksia tämän prosessin jotenkin automatisoimisesta (varsinkin jos dataa on paljon). Ratkaisuja on useita, ja niiden monimutkaisuus ja tehokkuus vaihtelevat:

• Käyttää sisäänrakennetut Excel-tekstitoiminnot etsiä-leikkaa-liimaa tekstiä: LEVSIMV (VASEN), OIKEA (OIKEA), PSTR (keski), STsEPIT (CONCATENATE) ja sen analogit, YHDISTÄÄ (JOINTEXT), TARKKA (TARKKA) jne. Tämä menetelmä on hyvä, jos tekstissä on selkeä logiikka (esim. hakemisto on aina osoitteen alussa). Muuten kaavoista tulee paljon monimutkaisempia ja joskus jopa taulukkokaavoja, mikä hidastaa suuresti suuria taulukoita.
• Käyttäminen kuten tekstin samankaltaisuusoperaattori Visual Basicista, joka on kääritty mukautettuun makrotoimintoon. Näin voit toteuttaa joustavamman haun käyttämällä jokerimerkkejä (*, #,? jne.). Valitettavasti tämä työkalu ei voi poimia haluttua osamerkkijonoa tekstistä – tarkista vain, sisältyykö se siihen.

Edellä mainittujen lisäksi on olemassa toinen lähestymistapa, joka tunnetaan hyvin kapeissa ohjelmoijien, web-kehittäjien ja muiden tekniikkojen piirissä – tämä on säännölliset lausekkeet (Säännölliset lausekkeet = RegExp = "säännölliset lausekkeet" = "säännölliset lausekkeet"). Yksinkertaisesti sanottuna, RegExp on kieli, jossa käytetään erikoismerkkejä ja sääntöjä etsimään tekstistä tarvittavia osamerkkijonoja, purkamaan niitä tai korvaamaan ne toisella tekstillä. Säännölliset lausekkeet ovat erittäin tehokas ja kaunis työkalu, joka ylittää kaikki muut tavat työstää tekstiä suuruusluokkaa. Monet ohjelmointikielet (C#, PHP, Perl, JavaScript…) ja tekstieditorit (Word, Notepad++…) tukevat säännöllisiä lausekkeita.

Microsoft Excelissä ei valitettavasti ole valmiina RegExp-tukea, mutta tämä voidaan helposti korjata VBA:lla. Avaa Visual Basic Editor välilehdeltä kehittäjä (Kehittäjä) tai pikanäppäimellä muut+F11. Aseta sitten uusi moduuli valikon kautta Insert – moduuli ja kopioi sinne seuraavan makrofunktion teksti:

Julkinen funktio RegExpExtract(teksti merkkijonona, kuvio merkkijonona, valinnainen kohde kokonaislukuna = 1) merkkijonona virheessä GoTo ErrHandl Aseta regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.T (Teksti) Aseta sitten ottelut = regex.Execute(Text) RegExpExtract = vastaa.Kohde(kohde - 1) Poistu funktiosta End If ErrHandl: RegExpExtract = CVERr(xlErrValue) Lopeta funktio  

Voimme nyt sulkea Visual Basic Editorin ja palata Exceliin kokeilemaan uutta ominaisuuttamme. Sen syntaksi on seuraava:

=RegExpExtract( Txt ; Pattern ; Item )

jossa

• txt – solu, jossa on tarkistamamme teksti ja josta haluamme poimia tarvitsemamme alimerkkijonon
• kuvio – maski (kuvio) osamerkkijonohakuun
• erä - erotettavan alimerkkijonon järjestysnumero, jos niitä on useita (jos ei ole määritetty, näytetään ensimmäinen esiintymä)

Mielenkiintoisin asia tässä on tietysti Pattern – erikoismerkkien mallijono RegExpin ”kielellä”, joka määrittää mitä tarkalleen ja mistä haluamme löytää. Tässä on alkeellisimmat, joilla pääset alkuun:

Jos etsimme tiettyä määrää merkkejä, esimerkiksi kuusinumeroista postinumeroa tai kaikkia kolmikirjaimia tuotekoodeja, tulemme apuun kvantorit or kvantorit ovat erikoislausekkeita, jotka määrittävät haettavien merkkien määrän. Kvantifiaattoreita käytetään sitä edeltävään merkkiin:

Siirrytään nyt mielenkiintoisimpaan osaan – analyysiin luodun funktion soveltamisesta ja siitä, mitä opimme malleista käytännön esimerkeillä elämästä.

Numeroiden poimiminen tekstistä

Aluksi analysoidaan yksinkertainen tapaus - sinun on poimittava ensimmäinen numero aakkosnumeerisesta puurosta, esimerkiksi keskeytymättömien virtalähteiden teho hinnastosta:

Säännöllisen lausekkeen taustalla oleva logiikka on yksinkertainen: d tarkoittaa mitä tahansa numeroa, ja kvantori + sanoo, että niiden lukumäärän tulisi olla yksi tai useampi. Kaksinkertainen miinus funktion edessä tarvitaan "lennossa" muuttamaan poimitut merkit täysluvuiksi numerosta tekstinä.

Postinumero

Ensi silmäyksellä kaikki on täällä yksinkertaista - etsimme täsmälleen kuutta numeroa peräkkäin. Käytämme erikoismerkkiä d numerolle ja kvantorille 6 {} merkkien määrälle:

Tilanne on kuitenkin mahdollinen, kun rivin indeksin vasemmalla puolella on toinen suuri joukko numeroita peräkkäin (puhelinnumero, TIN, pankkitili jne.) Silloin runkosarjamme vetää ensimmäiset 6 numerot siitä, eli ei toimi oikein:

Tämän estämiseksi meidän on lisättävä muokkaus säännöllisen lausekkeen reunojen ympärille b tarkoittaa sanan loppua. Tämä tekee Excelille selväksi, että tarvitsemamme fragmentin (hakemiston) tulee olla erillinen sana, ei osa toista fragmenttia (puhelinnumero):

Puhelin

Ongelma puhelinnumeron löytämisessä tekstistä on se, että numeroiden kirjoittamiseen on niin monia vaihtoehtoja – väliviivojen kanssa ja ilman, välilyöntejä, aluekoodin kanssa tai ilman suluissa jne. Siksi mielestäni on helpompi kirjoittaa puhdista ensin kaikki nämä merkit lähdetekstistä käyttämällä useita sisäkkäisiä funktioita SUBSTITUTE (KORVAA)niin, että se tarttuu yhteen yhdeksi kokonaisuudeksi, ja sitten primitiivisen säännöllisen kanssa d{11} vedä 11 numeroa peräkkäin:

ITN

Tässä on hieman monimutkaisempi, koska TIN-tunnus (maassamme) voi olla 10-numeroinen (juridisille henkilöille) tai 12-numeroinen (yksityishenkilöille). Jos et löydä vikaa erityisesti, on täysin mahdollista olla tyytyväinen tavalliseen d{10,12}, mutta tarkasti ottaen se vetää ulos kaikki 10 - 12 merkin luvut, eli virheellisesti syötetyt 11 numeroa. Olisi oikeampaa käyttää kahta kuviota, jotka on yhdistetty loogisella OR-operaattorilla | (pystypalkki):

Huomaa, että kyselyssä etsimme ensin 12-bittisiä numeroita ja vasta sitten 10-bittisiä numeroita. Jos kirjoitamme säännöllisen lausekkeen päinvastoin, se poistuu kaikilta, jopa pitkiltä 12-bittisiltä TIN-numeroilta, vain ensimmäiset 10 merkkiä. Eli sen jälkeen, kun ensimmäinen ehto on laukaissut, lisätarkistusta ei enää suoriteta:

Tämä on perustavanlaatuinen ero operaattorin välillä | tavallisesta Excelin logiikkafunktiosta OR (OR), jossa argumenttien järjestäminen uudelleen ei muuta tulosta.

Tuotteen SKU: t

Monissa yrityksissä tavaroille ja palveluille annetaan yksilöllisiä tunnisteita – artikkeleita, SAP-koodeja, SKU:ita jne. Jos niiden merkinnöissä on logiikkaa, ne voidaan helposti vetää pois mistä tahansa tekstistä säännöllisten lausekkeiden avulla. Jos esimerkiksi tiedämme, että artikkelimme koostuvat aina kolmesta englanninkielisestä isosta kirjaimesta, yhdysmerkistä ja sitä seuraavasta kolminumeroisesta numerosta, niin:

Mallin taustalla oleva logiikka on yksinkertainen. [AZ] – tarkoittaa mitä tahansa latinalaisten aakkosten isoja kirjaimia. Seuraava kvantori 3 {} sanoo, että meille on tärkeää, että tällaisia ​​kirjaimia on tasan kolme. Tavuviivan jälkeen odotamme kolmea numeroa, joten lisäämme loppuun d{3}

Käteissummat

Samalla tavalla kuin edellisessä kappaleessa, voit myös vetää hinnat (kustannukset, ALV…) pois tavaran kuvauksesta. Jos esimerkiksi rahamäärät on merkitty yhdysviivalla, niin:

Kuvio d kvantisaattorin kanssa + etsii mitä tahansa numeroa väliviivaan asti, ja d{2} etsii sen jälkeen penniä (kaksi numeroa).

Jos sinun ei tarvitse poimia hintoja, vaan ALV, voit käyttää RegExpExtract-funktiomme kolmatta valinnaista argumenttia, joka määrittää purettavan elementin järjestysnumeron. Ja tietysti voit korvata toiminnon SUBSTITUTE (KORVAA) tuloksissa yhdysviiva tavalliseen desimaalierottimeen ja lisää alkuun kaksinkertainen miinus, jotta Excel tulkitsee löydetyn ALV:n normaaliksi numeroksi:

Auton rekisterinumerot

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:

Aika:

Ajan poimimiseen HH:MM-muodossa seuraava säännöllinen lauseke sopii:

Jälkeen paksusuolen fragmentti [0-5]d, kuten on helppo selvittää, asettaa minkä tahansa luvun välillä 00-59. Ennen suluissa olevaa kaksoispistettä toimii kaksi kuviota, jotka erotetaan loogisella TAI:lla (pipe):

• [0-1]d – mikä tahansa numero välillä 00-19
• 2[0-3] – mikä tahansa numero välillä 20-23

Saatuun tulokseen voit käyttää lisäksi tavallista Excel-toimintoa AIKA (TIIMI)muuntaa sen ohjelman kannalta ymmärrettävään ja jatkolaskelmiin sopivaan aikamuotoon.

Salasanan tarkistus

Oletetaan, että meidän on tarkistettava käyttäjien keksimien salasanojen oikeellisuus. Sääntöjemme mukaan salasanat voivat sisältää vain englanninkielisiä kirjaimia (pieniä tai isoja) ja numeroita. Välilyöntejä, alaviivoja ja muita välimerkkejä ei sallita.

Tarkistaminen voidaan järjestää käyttämällä seuraavaa yksinkertaista säännöllistä lauseketta:

Itse asiassa tällaisella mallilla vaadimme, että alun välillä (^) ja loppu ($) tekstissämme oli vain merkkejä hakasulkeissa annetusta joukosta. Jos sinun on tarkistettava myös salasanan pituus (esim. vähintään 6 merkkiä), kvantori + voidaan korvata välillä "kuusi tai enemmän" muodossa {6,}:

Kaupunki osoitteesta

Oletetaan, että meidän täytyy vetää kaupunki osoitepalkista. Tavallinen ohjelma auttaa poimimaan tekstin "g:stä". seuraavaan pilkkuun:

Katsotaanpa tätä mallia tarkemmin.

Jos olet lukenut yllä olevan tekstin, olet jo ymmärtänyt, että joillakin säännöllisten lausekkeiden merkeillä (pisteet, tähdet, dollarimerkit jne.) on erityinen merkitys. Jos sinun on etsittävä näitä merkkejä itse, niitä edeltää kenoviiva (jota joskus kutsutaan suojaus). Siksi, kun etsit fragmenttia "g." meidän on kirjoitettava säännöllisellä lausekkeella Herra. jos etsimme plussaa, niin + ja niin edelleen

Mallissamme kaksi seuraavaa merkkiä, piste ja kvantoritähti, tarkoittavat mitä tahansa määrää mitä tahansa merkkejä, eli mitä tahansa kaupungin nimeä.

Mallin lopussa on pilkku, koska etsimme tekstiä "g":stä. pilkkuun. Mutta tekstissä voi olla useita pilkkuja, eikö niin? Ei vain kaupungin, vaan myös kadun, talojen jne. jälkeen. Mihin niistä pyyntömme päättyy? Sitä varten kysymysmerkki on. Ilman sitä säännöllinen lausekkeemme vetäisi pisimmän mahdollisen merkkijonon:

Säännöllisten lausekkeiden kannalta tällainen malli on "ahne". Tilanteen korjaamiseksi tarvitaan kysymysmerkki – se tekee kvantisoijasta, jonka jälkeen se on "niukka" - ja kyselymme vie tekstin vain ensimmäiseen laskuripilkuun asti "g":n jälkeen:

Tiedostonimi täydestä polusta

Toinen hyvin yleinen tilanne on purkaa tiedoston nimi koko polusta. Yksinkertainen lomakkeen säännöllinen lauseke auttaa tässä:

Temppu tässä on se, että haku tapahtuu itse asiassa vastakkaiseen suuntaan – lopusta alkuun, koska mallimme lopussa on $, ja etsimme kaikkea ennen sitä ensimmäiseen kenoviivaan oikealta saakka. Kenoviiva on erotettu, kuten piste edellisessä esimerkissä.

PS

"Loppua kohti" Haluan selventää, että kaikki yllä oleva on pieni osa kaikista säännöllisten lausekkeiden tarjoamista mahdollisuuksista. Erikoishahmoja ja niiden käyttöä koskevia sääntöjä on paljon, ja tästä aiheesta on kirjoitettu kokonaisia ​​kirjoja (suosittelen ainakin tätä alkuun). Tavallaan säännöllisten lausekkeiden kirjoittaminen on melkein taidetta. Lähes aina keksittyä säännöllistä lauseketta voidaan parantaa tai täydentää, jolloin siitä tulee tyylikkäämpi tai se pystyy toimimaan laajemman syöttötiedon kanssa.

Voit analysoida ja jäsentää muiden ihmisten säännöllisiä lausekkeita tai korjata omia virheitä käyttämällä useita käteviä verkkopalveluita: RegEx101, RegExr ja

Valitettavasti kaikkia klassisten säännöllisten lausekkeiden ominaisuuksia ei tueta VBA:ssa (esim. käänteinen haku tai POSIX-luokat) ja ne voivat toimia kyrillisellä kirjaimella, mutta mielestäni se, mitä on, riittää ensimmäisellä kerralla miellyttämään sinua.

Jos aihe ei ole sinulle uusi ja sinulla on jotain jaettavaa, jätä alla oleviin kommentteihin säännöllisiä lausekkeita, jotka ovat hyödyllisiä Excelissä työskennellessäsi. Yksi mieli on hyvä, mutta kaksi saappaat ovat pari!

• Tekstin korvaaminen ja puhdistaminen SUBSTITUTE-toiminnolla
• Search and highlighting of Latin characters in text
• Etsi lähin vastaava teksti (Ivanov = Ivonov = Ivanof jne.)