Анализирајте го текстот со регуларни изрази (RegExp) во Excel

Една од најодземачките и најфрустрирачките задачи при работа со текст во Excel е парсирање – парсирање на алфанумеричката „каша“ во компоненти и извлекување на фрагментите што ни се потребни од неа. На пример:

• извлекување на поштенскиот код од адресата (добро е ако поштенскиот код е секогаш на почетокот, но што ако не е?)
• наоѓање на бројот и датумот на фактурата од описот на уплатата во банкарскиот извод
• екстракција на TIN од шарени описи на компании во списокот на договорни страни
• побарајте број на автомобил или број на статија во описот итн.
  

Вообичаено во такви случаи, по половина час мрачно берење на текстот рачно, почнуваат да ми доаѓаат мисли некако да се автоматизира овој процес (особено ако има многу податоци). Постојат неколку решенија и со различен степен на сложеност-ефикасност:

• Користете вградени текстуални функции на Excel за пребарување-сечење-лепење текст: ЛЕВСИМВ (ЛЕВО), ПРАВО (ПРАВО), ПСТР (средина), STsEPIT (КОНКАТЕНАТ) и неговите аналози, КОМБИНА (ЗОЈИНТЕКСТ), Токму (ТОЧНО) итн. Овој метод е добар ако има јасна логика во текстот (на пример, индексот е секогаш на почетокот на адресата). Во спротивно, формулите стануваат многу покомплицирани, а понекогаш дури и доаѓа до низа формули, што значително забавува на големите маси.
• Користење како оператор за сличност на текст од Visual Basic завиткан во приспособена макро функција. Ова ви овозможува да имплементирате пофлексибилно пребарување со помош на знаци (*, #,?, итн.) За жал, оваа алатка не може да ја извлече саканата подниза од текстот - само проверете дали е содржана во неа.

Покрај горенаведеното, постои уште еден пристап кој е многу добро познат во тесните кругови на професионални програмери, веб-програмери и други техничари – ова е редовни изрази (Регуларни изрази = RegExp = „regexps“ = „редовни“). Едноставно кажано, RegExp е јазик каде што се користат специјални знаци и правила за пребарување на потребните поднизи во текстот, нивно извлекување или замена со друг текст.. Редовните изрази се многу моќна и убава алатка која ги надминува сите други начини на работа со текст по ред на големина. Многу програмски јазици (C#, PHP, Perl, JavaScript…) и текстуални уредници (Word, Notepad++…) поддржуваат редовни изрази.

Microsoft Excel, за жал, нема поддршка за RegExp надвор од кутијата, но ова може лесно да се поправи со VBA. Отворете го уредувачот на Visual Basic од табулаторот инвеститорот (програмер) или кратенка на тастатура Alt+F11. Потоа вметнете го новиот модул низ менито Вметни – Модул и копирајте го текстот на следната макро функција таму:

Јавна функција RegExpExtract(Текст како низа, шема како низа, изборна ставка како цел број = 1) како низа на грешка Оди во ErrHandl Постави regex = CreateObject ("VBScript.RegExp") regex. Шаблон = Шема regex.Global = Вистински ако (Текст) Потоа Постави совпаѓања = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVERr(xlErrValue) Крајна функција  

Сега можеме да го затвориме уредникот на Visual Basic и да се вратиме во Excel за да ја испробаме нашата нова функција. Неговата синтакса е следна:

=RegExpExtract(Txt; Шема; Ставка)

каде

• txt – ќелија со текстот што го проверуваме и од кој сакаме да ја извадиме поднизата што ни е потребна
• шема – маска (шема) за пребарување на поднизи
• Содржина – секвенцискиот број на поднизата што треба да се извлече, ако има неколку од нив (ако не е наведено, тогаш се прикажува првата појава)

Овде, секако, најинтересно е Pattern – шаблонска низа од специјални знаци „на јазикот“ на RegExp, која одредува што точно и каде сакаме да најдеме. Еве ги најосновните за да започнете:

Ако бараме одреден број знаци, на пример, шестцифрен поштенски код или сите шифри на производи со три букви, тогаш доаѓаме на помош квантификатори or квантификатори се специјални изрази кои го одредуваат бројот на знаци што треба да се пребаруваат. Квантификаторите се применуваат на знакот што доаѓа пред него:

Сега да преминеме на најинтересниот дел – анализа на примената на креираната функција и она што го научивме за обрасците на практични примери од животот.

Извлекување броеви од текст

За почеток, да анализираме едноставен случај - треба да го извлечете првиот број од алфанумеричка каша, на пример, моќта на непрекинато напојување од ценовникот:

Логиката зад регуларниот израз е едноставна: d значи која било цифра и квантификатор + вели дека нивниот број треба да биде еден или повеќе. Двојниот минус пред функцијата е потребен за „во лет“ да се конвертираат извлечените знаци во целосен број од бројот-како-текст.

Поштенски код

На прв поглед, сè е едноставно овде - бараме точно шест цифри по ред. Ние користиме посебен карактер d за цифра и квантификатор 6 {} за бројот на знаци:

Сепак, можна е ситуација кога, лево од индексот во линијата, има уште еден голем сет на броеви по ред (телефонски број, TIN, банкарска сметка итн.) Тогаш нашата регуларна сезона ќе ги повлече првите 6 цифри од него, односно нема да работи правилно:

За да спречиме тоа да се случи, треба да додадеме модификатор околу рабовите на нашиот редовен израз b означува крај на зборот. Ова ќе му даде јасно до знаење на Excel дека фрагментот (индексот) што ни треба треба да биде посебен збор, а не дел од друг фрагмент (телефонски број):

телефон

Проблемот со наоѓање телефонски број во текстот е што има толку многу опции за пишување броеви – со и без цртички, низ празни места, со или без регионален код во загради итн. Затоа, според мене, полесно е да се прво исчистете ги сите овие знаци од изворниот текст користејќи неколку вгнездени функции СУПСТИТУТ (ЗАМЕНА)така што се залепи во една единствена целина, а потоа и со примитивна правилна д{11} извлечете 11 цифри по ред:

ИТН

Овде е малку покомплицирано, бидејќи TIN (во нашата земја) може да биде 10-цифрен (за правни лица) или 12-цифрен (за физички лица). Ако особено не наоѓате мана, тогаш сосема е можно да бидете задоволни со редовното д{10,12}, но, строго кажано, ќе ги извлече сите броеви од 10 до 12 знаци, односно и погрешно внесени 11 цифри. Би било поправилно да се користат два шеми поврзани со логички ИЛИ оператор | (вертикална лента):

Ве молиме имајте предвид дека во барањето прво бараме 12-битни броеви, а дури потоа 10-битни броеви. Ако го напишеме нашиот редовен израз обратно, тогаш тој ќе ги извади за сите, дури и долгите 12-битни TIN, само првите 10 знаци. Односно, откако ќе се активира првиот услов, понатамошната верификација повеќе не се врши:

Ова е основната разлика помеѓу операторот | од стандардна ексел логичка функција OR (ИЛИ), каде што преуредувањето на аргументите не го менува резултатот.

SKU на производот

Во многу компании, единствени идентификатори се доделуваат на стоки и услуги - написи, SAP кодови, SKU итн. На пример, ако знаеме дека нашите написи секогаш се состојат од три големи англиски букви, цртичка и последователен трицифрен број, тогаш:

Логиката зад шаблонот е едноставна. [АЗ] – значи сите големи букви од латинската азбука. Следниот квантификатор 3 {} вели дека за нас е важно да има точно три такви букви. По цртичката чекаме три цифри, па додаваме на крајот д{3}

Готовински износи

На сличен начин како во претходниот став, можете да ги извадите и цените (трошоци, ДДВ...) од описот на стоката. Ако паричните износи, на пример, се означени со цртичка, тогаш:

моделот d со квантификатор + бара кој било број до цртичка и д{2} ќе бара пени (две цифри) после.

Ако треба да извлечете не цени, туку ДДВ, тогаш можете да го користите третиот изборен аргумент на нашата функција RegExpExtract, кој го одредува редниот број на елементот што треба да се извлече. И, се разбира, можете да ја замените функцијата СУПСТИТУТ (ЗАМЕНА) во резултатите, испишете цртичка на стандардниот децимален сепаратор и додадете двоен минус на почетокот, така што Excel го толкува пронајдениот ДДВ како нормален број:

Броеви на автомобилски таблички

Ако не земате специјални возила, приколки и други мотоцикли, тогаш стандардниот број на автомобилот се анализира според принципот „буква – три броја – две букви – регионален код“. Покрај тоа, шифрата на регионот може да биде 2- или 3-цифрена, а како букви се користат само оние што по изглед се слични на латинската азбука. Така, следниот правилен израз ќе ни помогне да извлечеме броеви од текстот:

време

За да го извлечете времето во формат HH:MM, соодветен е следниот регуларен израз:

По фрагмент од дебелото црево [0-5]д, како што е лесно да се сфати, поставува кој било број во опсегот 00-59. Пред дебелото црево во загради, работат два шаблони, одделени со логичка ИЛИ (цевка):

• [0-1]д – кој било број во опсегот 00-19
• 2[0-3] – кој било број во опсегот 20-23

До добиениот резултат, можете дополнително да ја примените стандардната функција Excel ВРЕМЕ (ТИМ)да го претворите во временски формат што е разбирлив за програмата и погоден за понатамошни пресметки.

Проверка на лозинка

Да претпоставиме дека треба да ја провериме листата на лозинки измислени од корисниците за точност. Според нашите правила, лозинките можат да содржат само англиски букви (мали или големи) и бројки. Празни места, долни црти и други интерпункциски знаци не се дозволени.

Проверката може да се организира со користење на следниов едноставен регуларен израз:

Всушност, со таква шема бараме помеѓу почетокот (^) и крај ($) во нашиот текст имаше само знаци од множеството дадени во квадратни загради. Ако треба да ја проверите и должината на лозинката (на пример, најмалку 6 знаци), тогаш квантификаторот + може да се замени со интервалот „шест или повеќе“ во форма {6,}:

Град од адреса

Да речеме дека треба да го извлечеме градот од лентата за адреси. Редовната програма ќе помогне, извлекувајќи го текстот од „g“. до следната запирка:

Ајде внимателно да ја разгледаме оваа шема.

Ако сте го прочитале горенаведениот текст, тогаш веќе сте разбрале дека некои знаци во правилни изрази (периоди, ѕвездички, знаци за долари, итн.) имаат посебно значење. Ако треба самите да ги барате овие ликови, тогаш пред нив има обратна коса црта (понекогаш се нарекува екранировка). Затоа, при пребарување на фрагментот „g“. треба да пишуваме во регуларен израз Г-дин ако бараме плус, тогаш + итн

Следните два знака во нашиот шаблон, точката и квантификаторната ѕвездичка, значат кој било број од кој било знак, т.е. кое било име на градот.

На крајот од шаблонот има запирка, бидејќи бараме текст од „g“. до запирка. Но, во текстот може да има неколку запирки, нели? Не само по градот, туку и по улицата, куќите итн. На кои од нив ќе престане нашето барање? За тоа е прашалникот. Без него, нашиот редовен израз би ја извадил најдолгата можна низа:

Во однос на редовните изрази, таквата шема е „алчен“. За да се поправи ситуацијата, потребен е прашалник – го прави квантификаторот по што стои „скржав“ – а нашето барање го зема текстот само до првата контра-запирка по „g.“:

Име на датотека од целосна патека

Друга многу честа ситуација е да се извлече името на датотеката од целосната патека. Едноставно редовно изразување на формата ќе помогне овде:

Трикот овде е што пребарувањето, всушност, се случува во спротивна насока - од крајот кон почетокот, бидејќи на крајот од нашиот шаблон е $, и бараме сè пред тоа до првиот обратен црти од десно. Задна коса црта е избегната, како точката во претходниот пример.

PS

„Кон крајот“ сакам да појаснам дека сето горенаведено е мал дел од сите можности што ги даваат регуларните изрази. Има многу специјални знаци и правила за нивна употреба, а напишани се цели книги на оваа тема (ја препорачувам барем оваа за почеток). На некој начин пишувањето правилни изрази е речиси уметност. Речиси секогаш, измислениот регуларен израз може да се подобри или дополни, што го прави поелегантен или способен да работи со поширок опсег на влезни податоци.

За да ги анализирате и анализирате редовните изрази на другите луѓе или да ги дебагирате вашите, постојат неколку практични онлајн услуги: RegEx101, RegExr и повеќе

За жал, не се поддржани сите карактеристики на класичните правилни изрази во VBA (на пример, обратно пребарување или класи на POSIX) и можат да работат со кирилица, но мислам дека она што е таму е доволно за прв пат да ве задоволи.

Ако не сте нови во темата, а имате што да споделите, оставете редовни изрази корисни кога работите во Excel во коментарите подолу. Еден ум е добар, но две чизми се пар!

• Заменување и чистење на текстот со функцијата SUBSTITUTE
• Пребарување и истакнување на латински знаци во текстот
• Пребарајте го најблискиот сличен текст (Иванов = Ивонов = Иваноф итн.)