Excel бағдарламасында тұрақты өрнектермен (RegExp) мәтінді талдау

Excel бағдарламасында мәтінмен жұмыс істеу кезінде ең көп уақытты қажет ететін және көңілсіз жұмыстардың бірі талдау – әріптік-сандық «ботқаны» компоненттерге талдау және одан бізге қажетті фрагменттерді алу. Мысалға:

• мекенжайдан пошталық индексті шығарып алу (почталық индекс әрқашан басында болса жақсы, бірақ олай болмаса ше?)
• банк үзіндісіндегі төлемнің сипаттамасынан шот-фактураның нөмірі мен күнін табу
• контрагенттер тізіміндегі компаниялардың түрлі-түсті сипаттамасынан СТН алу
• сипаттамада көлік нөмірін немесе мақала нөмірін іздеу және т.б.
  

Әдетте мұндай жағдайларда мәтінді қолмен жарты сағаттық ренжіткеннен кейін бұл процесті автоматтандыру туралы ойлар келе бастайды (әсіресе деректер көп болса). Күрделілігі мен тиімділігі әртүрлі бірнеше шешімдер бар:

• пайдалану кірістірілген Excel мәтіндік функциялары мәтінді іздеу-қиып алу үшін: LEVSIMV (СОЛ), RIGHT (ДҰРЫС), PSTR (орта), STsEPIT (ҚЫСТЫРУ) және оның аналогтары, КОМБИНА (JOINTEXT), ДӘЛ (ДӘЛ) т.б. Бұл әдіс мәтінде нақты логика болса жақсы (мысалы, индекс әрқашан адрестің басында болады). Әйтпесе, формулалар әлдеқайда күрделене түседі және кейде массив формулаларына келеді, бұл үлкен кестелерде айтарлықтай баяулайды.
• пайдалану мәтіндік ұқсастық операторы сияқты теңшелетін макрос функциясына оралған Visual Basic жүйесінен. Бұл қойылмалы таңбаларды (*, #,?, т.б.) пайдаланып, икемді іздеуді жүзеге асыруға мүмкіндік береді. Өкінішке орай, бұл құрал мәтіннен қажетті ішкі жолды шығара алмайды – тек оның құрамында бар-жоғын тексеріңіз.

Жоғарыда айтылғандардан басқа, кәсіби бағдарламашылардың, веб-әзірлеушілердің және басқа техникалардың тар шеңберлерінде өте жақсы белгілі тағы бір тәсіл бар - бұл тұрақты тіркестер (Тұрақты өрнектер = RegExp = “regexps” = “тұрақтылар”). Қарапайым тілмен айтқанда, RegExp - мәтіндегі қажетті ішкі жолдарды іздеу, оларды шығару немесе оларды басқа мәтінмен ауыстыру үшін арнайы таңбалар мен ережелер қолданылатын тіл.. Тұрақты өрнектер - бұл мәтінмен жұмыс істеудің барлық басқа тәсілдерін шамасы бойынша асып түсетін өте күшті және әдемі құрал. Көптеген бағдарламалау тілдері (C#, PHP, Perl, JavaScript…) және мәтіндік редакторлар (Word, Notepad++…) тұрақты өрнектерді қолдайды.

Өкінішке орай, Microsoft Excel бағдарламасында RegExp қолдауы жоқ, бірақ оны VBA көмегімен оңай түзетуге болады. Visual Basic өңдегішін қойындыдан ашыңыз әзірлеуші (Әзірлеуші) немесе пернелер тіркесімі Alt+F11. Содан кейін мәзір арқылы жаңа модульді салыңыз Кірістіру – модуль және келесі макрофункцияның мәтінін сол жерге көшіріңіз:

Қоғамдық функция RegExpExtract(жол ретінде мәтін, жол ретінде үлгі, бүтін сан ретінде қосымша элемент = 1) Қате жол ретінде GoTo ErrHandl орнату regex = CreateObject("VBScript.RegExp") regex.Pattern = Үлгі regex.Global = True Егер regex.Test болса (Мәтін) Содан кейін сәйкестіктерді орнату = regex.Execute(Мәтін) RegExpExtract = matches.Item(Параметр - 1) Функциядан шығу ErrHandl болса: RegExpExtract = CVErr(xlErrValue) Аяқтау функциясы  

Біз енді Visual Basic өңдегішін жауып, жаңа мүмкіндікті пайдаланып көру үшін Excel бағдарламасына орала аламыз. Оның синтаксисі келесідей:

=RegExpExtract( Txt ; Үлгі ; Элемент )

қайда

• TXT – біз тексеріп жатқан мәтіні бар ұяшық және одан бізге қажетті ішкі жолды шығарғымыз келеді
• үлгі – ішкі жолды іздеуге арналған маска (үлгі).
• тармақ – шығарылатын ішкі жолдың реттік нөмірі, егер олардың бірнешеуі болса (егер көрсетілмесе, онда бірінші орын көрсетіледі)

Мұндағы ең қызық нәрсе, әрине, Pattern – RegExp тіліндегі арнайы таңбалардың үлгі тізбегі, ол нақты нені және қай жерден тапқымыз келетінін көрсетеді. Міне, сізге бастау үшін ең негізгілері:

Егер біз белгілі бір таңбалар санын, мысалы, алты таңбалы пошта индексін немесе барлық үш әріпті өнім кодтарын іздейтін болсақ, онда біз көмекке келеміз. сандық көрсеткіштер or сандық көрсеткіштер ізделетін таңбалар санын көрсететін арнайы өрнектер. Өзінен бұрын келетін таңбаға кванторлар қолданылады:

Енді ең қызықты бөлігіне көшейік – құрылған функцияның қолданылуын талдау және біз өмірден практикалық мысалдар бойынша үлгілер туралы не білдік.

Мәтіннен сандарды шығару

Бастау үшін қарапайым жағдайды талдап көрейік – әріптік-сандық ботқадан бірінші нөмірді алу керек, мысалы, бағалар тізімінен үздіксіз қуат көздерінің қуаты:

Тұрақты өрнектің логикасы қарапайым: d кез келген цифрды және кванторды білдіреді + олардың саны бір немесе бірнеше болуы керектігін айтады. Функцияның алдындағы қос минус алынған таңбаларды мәтін ретіндегі саннан толық санға түрлендіру үшін қажет.

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

Бір қарағанда, мұнда бәрі қарапайым - біз қатарынан дәл алты цифрды іздейміз. Біз арнайы таңбаны қолданамыз d цифр және квантор үшін 6 {} таңбалар саны үшін:

Дегенмен, жолдағы индекстің сол жағында қатарда тағы бір үлкен сандар жинағы (телефон нөмірі, СТН, банк шоты және т. одан сандар, яғни дұрыс жұмыс істемейді:

Бұған жол бермеу үшін тұрақты өрнектің шетіне модификаторды қосу керек b сөздің соңын білдіреді. Бұл Excel бағдарламасына бізге қажет фрагмент (индекс) басқа фрагменттің (телефон нөмірі) бөлігі емес, жеке сөз болуы керек екенін түсіндіреді:

Телефон

Мәтіннен телефон нөмірін табудың қиындығы мынада: сандарды жазудың көптеген нұсқалары бар – дефиспен және дефиссіз, бос орындар арқылы, жақшадағы аймақ коды бар немесе онсыз және т.б. Сондықтан, менің ойымша, бұл оңайырақ. алдымен бірнеше кірістірілген функцияларды пайдаланып бастапқы мәтіннен осы таңбалардың барлығын тазалаңыз СУБСТИТУТ (АЛМАСТЫРУ)осылайша ол бір бүтінге, содан кейін қарабайыр регулярға жабысады d{11} қатарынан 11 цифрды шығарыңыз:

ITN

Бұл жерде сәл күрделірек, өйткені СТН (Біздің елде) 10 таңбалы (заңды тұлғалар үшін) немесе 12 таңбалы (жеке тұлғалар үшін) болуы мүмкін. Әсіресе мін таппасаңыз, кәдімгіге қанағаттануға әбден болады d{10,12}, бірақ, қатаң айтқанда, ол 10-нан 12 таңбаға дейінгі барлық сандарды шығарады, яғни қате енгізілген 11 цифр. Логикалық НЕМЕСЕ операторымен қосылған екі үлгіні пайдалану дұрысырақ болар еді | (тік жолақ):

Сұрауда біз алдымен 12 разрядты сандарды, содан кейін ғана 10 разрядты сандарды іздейтінімізді ескеріңіз. Егер біз тұрақты өрнекті керісінше жазсақ, ол барлығына, тіпті ұзын 12 биттік СТТН-ға, тек алғашқы 10 таңбаға ғана шығады. Яғни, бірінші шарт іске қосылғаннан кейін қосымша тексеру енді орындалмайды:

Бұл оператор арасындағы негізгі айырмашылық | стандартты Excel логикалық функциясынан OR (OR), мұнда аргументтерді қайта реттеу нәтижені өзгертпейді.

Өнім SKUs

Көптеген компанияларда тауарлар мен қызметтерге бірегей идентификаторлар тағайындалады – мақалалар, SAP кодтары, SKU және т.б. Егер олардың белгілеуінде логика болса, оларды тұрақты өрнектерді пайдаланып кез келген мәтіннен оңай шығаруға болады. Мысалы, егер мақалаларымыз әрқашан ағылшын тіліндегі үш бас әріптен, сызықшадан және одан кейінгі үш таңбалы саннан тұратынын білсек, онда:

Үлгінің логикасы қарапайым. [AZ] – латын әліпбиінің кез келген бас әріптерін білдіреді. Келесі квантор 3 {} біз үшін дәл осындай үш әріптің болуы маңызды дейді. Дефистен кейін біз үш цифрды күтеміз, сондықтан біз соңында қосамыз d{3}

Қолма-қол ақша сомалар

Алдыңғы абзацқа ұқсас жолмен сіз тауарлардың сипаттамасынан бағаларды (шығындар, ҚҚС ...) алып тастай аласыз. Егер ақшалай сомалар, мысалы, сызықшамен белгіленсе, онда:

үлгі d квантормен + сызықшаға дейінгі кез келген санды іздейді және d{2} кейін пенни (екі сан) іздейді.

Егер сізге бағаларды емес, ҚҚС шығару қажет болса, онда сіз шығарылатын элементтің реттік нөмірін көрсететін RegExpExtract функциямыздың үшінші қосымша аргументін пайдалана аласыз. Және, әрине, функцияны ауыстыруға болады СУБСТИТУТ (АЛМАСТЫРУ) нәтижелерде стандартты ондық бөлгішке сызықша қойып, басына қос минус қосыңыз, осылайша Excel табылған ҚҚС-ты қалыпты сан ретінде түсіндіреді:

Көлік нөмірлері

Егер сіз арнайы көліктерді, тіркемелерді және басқа мотоциклдерді алмасаңыз, онда стандартты автомобиль нөмірі «әріп – үш сан – екі әріп – аймақ коды» принципі бойынша талданады. Оның үстіне аймақ коды 2 немесе 3 таңбалы болуы мүмкін және әріп ретінде сыртқы түрі латын әліпбиіне ұқсастары ғана қолданылады. Осылайша, мәтіннен сандарды шығаруға келесі тұрақты өрнек көмектеседі:

уақыт

HH:MM пішіміндегі уақытты шығару үшін келесі тұрақты өрнек қолайлы:

Тоқ ішек фрагментінен кейін [0-5]күн, оны анықтау оңай болғандықтан, 00-59 аралығындағы кез келген санды орнатады. Жақшадағы қос нүктенің алдында логикалық НЕМЕСЕ (құбыр) арқылы бөлінген екі үлгі жұмыс істейді:

• [0-1]күн – 00-19 аралығындағы кез келген сан
• 2[0-3] – 20-23 аралығындағы кез келген сан

Алынған нәтижеге стандартты Excel функциясын қосымша қолдануға болады TIME (КОМАНДА)оны бағдарламаға түсінікті және әрі қарай есептеулер үшін қолайлы уақыт пішіміне түрлендіру үшін.

Құпия сөзді тексеру

Пайдаланушылар ойлап тапқан құпия сөздердің дұрыстығын тексеру керек делік. Біздің ережелерімізге сәйкес, құпия сөздерде тек ағылшын әріптері (кіші немесе бас әріптер) және сандар болуы мүмкін. Бос орындар, астын сызу және басқа тыныс белгілеріне жол берілмейді.

Тексеруді келесі қарапайым тұрақты өрнек арқылы ұйымдастыруға болады:

Шындығында, мұндай үлгімен біз басынан бастап (^) және аяқталуы ($) біздің мәтінде төртбұрышты жақшада берілген жиынның таңбалары ғана болды. Егер сізге парольдің ұзындығын (мысалы, кем дегенде 6 таңба) тексеру қажет болса, онда квантор + пішіндегі «алты немесе одан да көп» интервалымен ауыстырылуы мүмкін {6,}:

Мекенжайдан қала

Қаланы мекенжай жолағынан шығару керек делік. Кәдімгі бағдарлама «g» мәтінінен мәтінді шығаруға көмектеседі. келесі үтірге:

Осы үлгіні толығырақ қарастырайық.

Егер сіз жоғарыдағы мәтінді оқыған болсаңыз, онда сіз тұрақты тіркестердегі кейбір таңбалардың (нүктелер, жұлдызшалар, доллар белгілері және т.б.) ерекше мағынаға ие екенін түсіндіңіз. Егер сізге осы кейіпкерлерді өздері іздеу керек болса, олардың алдында кері қиғаш сызық (кейде деп аталады қалқан). Сондықтан «g» фрагментін іздеу кезінде. тұрақты өрнекпен жазуымыз керек Мырза. егер біз плюс іздейтін болсақ, онда + және т.б.

Үлгідегі келесі екі таңба, нүкте және квантор жұлдызшасы кез келген таңбалардың кез келген санын, яғни кез келген қала атауын білдіреді.

Үлгінің соңында үтір бар, өйткені біз «g» мәтінін іздейміз. үтірге дейін. Бірақ мәтінде бірнеше үтір болуы мүмкін, солай емес пе? Қаладан кейін ғана емес, көше, үй, т.б. солардың қайсысына біздің өтінішіміз тоқтайды? Сұрақ белгісі сол үшін қойылған. Онсыз біздің тұрақты өрнек мүмкін болатын ең ұзын жолды шығарады:

Тұрақты тіркестер тұрғысынан мұндай үлгі «ашкөз» болып табылады. Жағдайды түзету үшін сұрақ белгісі қажет – ол кванторды одан кейін «сараң» етеді – және біздің сұрау мәтінді тек «g.»-ден кейінгі бірінші қарсы үтірге дейін алады:

Толық жолдан файл атауы

Тағы бір жиі кездесетін жағдай - файл атауын толық жолдан шығару. Пішіннің қарапайым тұрақты өрнегі мұнда көмектеседі:

Мұндағы қулық мынада, іздеу, шын мәнінде, қарама-қарсы бағытта жүреді – соңынан басына дейін, өйткені біздің шаблонның соңында $, және біз оң жақтағы бірінші кері қиғаш сызыққа дейін оның алдында барлығын іздейміз. Алдыңғы мысалдағы нүкте сияқты кері қиғаш сызық қашады.

PS

«Соңына қарай» Жоғарыда айтылғандардың барлығы тұрақты тіркестер беретін барлық мүмкіндіктердің кішкене бөлігі екенін түсіндіргім келеді. Көптеген арнайы кейіпкерлер мен оларды пайдалану ережелері бар және бұл тақырып бойынша бүкіл кітаптар жазылған (мен ең болмағанда осыны ұсынамын). Бір жағынан тұрақты тіркестерді жазу өнер десе де болады. Әрқашан дерлік ойлап тапқан тұрақты өрнекті жақсартуға немесе толықтыруға болады, бұл оны талғампаз етеді немесе кіріс деректерінің кең ауқымымен жұмыс істей алады.

Басқа адамдардың тұрақты өрнектерін талдау және талдау немесе өзіңізді түзету үшін бірнеше ыңғайлы онлайн қызметтер бар: RegEx101, RegExr және одан да көп

Өкінішке орай, VBA-да классикалық тұрақты өрнектердің барлық мүмкіндіктеріне қолдау көрсетілмейді (мысалы, кері іздеу немесе POSIX сыныптары) және кириллицамен жұмыс істей алады, бірақ менің ойымша, бұл жерде бірінші рет сізді қуанту үшін жеткілікті нәрсе бар.

Тақырыппен таныс емес болсаңыз және бөлісетін бірдеңеңіз болса, төмендегі түсініктемелерде Excel бағдарламасында жұмыс істегенде пайдалы тұрақты өрнектерді қалдырыңыз. Бір ақыл жақсы, екі етік жұп!

• SUBSTITUTE функциясымен мәтінді ауыстыру және тазалау
• Мәтіндегі латын әріптерін іздеу және бөлектеу
• Ең жақын ұқсас мәтінді іздеңіз (Иванов = Ивонов = Иваноф, т.б.)