Мазмұны
Егер сіз кем дегенде тұрақты тіркестермен таныс болсаңыз, оларды жарнамалаудың қажеті жоқ. Егер сіз тақырыпты толық меңгермеген болсаңыз, онда тұрақты өрнектер (Regular Expressions = RegExp = “regexps” = “regulars”) – арнайы таңбалар мен ережелерді пайдалана отырып, мәтінде қажетті ішкі жолдар ізделетін тіл, олар алынып тасталады. немесе басқа мәтінмен ауыстырылады. Бұл өте күшті және әдемі құрал, мәтінмен жұмыс істеудің барлық басқа әдістерінен жоғары дәрежелі тәртіп.
Мен қарапайым макростар арқылы Excel бағдарламасына тұрақты өрнекті қолдауды қалай қосуға болатынын егжей-тегжейлі және өмірден алынған көптеген мысалдармен сипаттадым - егер сіз осы мақаланы оқымаған болсаңыз, жалғастырмас бұрын оны оқып шығуды ұсынамын. Сіз көптеген жаңа нәрселерді ашасыз, мен кепілдік беремін 🙂
Дегенмен, мәселе ашық күйінде қалды – Power Query бағдарламасында тұрақты өрнектерді пайдалану мүмкіндігін қалай қосуға болады? Power Query, әрине, өздігінен жақсы және мәтінмен көп нәрсе істей алады (кесу, желімдеу, тазалау және т.б.), бірақ оны тұрақты тіркестердің күшімен кесіп өтсеңіз, бұл жай ғана бомба болар еді.
Өкінішке орай, Power Query жүйесінде RegExps бағдарламасымен жұмыс істеуге арналған кірістірілген функциялар жоқ және Microsoft ресми анықтамасы мен техникалық қолдауы бұл сұраққа теріс жауап береді. Дегенмен, бұл шектеуді айналып өтудің жолы бар 🙂
Әдістің мәні
Негізгі идея - масқаралау оңай.
Кірістірілген Power Query мүмкіндіктерінің тізімінде функция бар Веб парақ. Microsoft корпорациясының ресми анықтамалық сайтындағы бұл функцияның сипаттамасы өте қысқа:
Аударылғанда бұл: «Құрамдас құрылымдарына бөлінген HTML құжатының мазмұнын, сонымен қатар тегтер жойылғаннан кейін толық құжат пен оның денесінің көрінісін қайтарады». Ашығын айтқанда, сипаттама.
Әдетте бұл функция интернеттен деректерді импорттау кезінде пайдаланылады және автоматты түрде ауыстырылады, мысалы, қойындыда таңдаған кезде мәліметтер бұйрық Интернеттен (Деректер — Интернеттен). Функцияға аргумент ретінде веб-бетті береміз және ол барлық тегтерді алдын ала тазалап, мазмұнын бізге кесте түрінде қайтарады.
Анықтама HTML белгілеу тіліне қосымша деп ЕМЕС функция Веб парақ JavaScript сценарийлерін қолдайды, ол қазір Интернеттегі веб-сайттарда бар. Және JavaScript, өз кезегінде, әрқашан тұрақты өрнектермен жұмыс істей алды және RegExps үшін кіріктірілген функциялары бар! Сонымен, Power Query жүйесінде тұрақты өрнектерді енгізу үшін Power Query үшін барлық жұмысты орындайтын шағын JavaScript бағдарламасына аргумент ретінде Web.Page функцияларын беруіміз керек.
Бұл таза JavaScript-те қалай көрінеді
Интернетте JavaScript тіліндегі тұрақты өрнектермен жұмыс істеу бойынша көптеген егжей-тегжейлі оқулықтар бар (мысалы, бір, екі).
Қысқаша және оңайлатылған JavaScript коды келесідей болады:
Мұнда:
- var str = 'Шұжық үшін 123 және 789 шоттарын төлеу'; – айнымалыны құру көш және оған біз талдайтын бастапқы мәтінді тағайындаңыз.
- var үлгісі = /d+/gi; – тұрақты өрнек құру және оны айнымалыға қою үлгі.
Өрнек қиғаш сызықтан (/) басталады.
Мұндағы өрнектің өзі, мысалы d+ цифрлардың кез келген тізбегін білдіреді.
Өрнектен кейінгі бөлшек арқылы қосымша іздеу параметрлері (модификаторлар) бар – оларды кез келген ретпен көрсетуге болады:
- g – ғаламдық іздеу дегенді білдіреді, яғни сәйкестікті тапқаннан кейін тоқтамай, мәтіннің соңына дейін іздеуді жалғастыру керек. Егер бұл модификатор орнатылмаса, біздің сценарий тек бірінші сәйкестікті қайтарады (123)
- i – әріптерді есепке алмай іздеу
- m – көп жолды іздеу (бастапқы мәтін бірнеше жолға бөлінгенде қолданылады)
- var нәтиже = str.match(үлгі).қосылу(';'); – бастапқы мәтінде іздеуді орындау (көш) берілген тұрақты өрнек арқылы (үлгі) және нәтижелерді айнымалыға қойыңыз нәтиже, пәрмені арқылы оларды нүктелі үтірмен біріктіру Қосылу
- document.write(нәтиже); – нәтиже айнымалысының мазмұнын көрсету
Сондай-ақ, JavaScript-тегі мәтін жолдары (тұрақты өрнектерді қоспағанда) Power Query немесе VBA-дағы тырнақша емес, апострофпен алынғанын ескеріңіз.
Нәтижеде бұл сценарий бізге бастапқы мәтінде табылған барлық сандарды береді:
123, 789
JavaScript қысқа курсы аяқталды, барлығыңызға рахмет. Сіз логиканы аласыз деп үміттенемін 🙂
Бұл құрылысты Power Query-ге көшіру қалады.
Power Query ішіндегі тұрақты өрнек арқылы мәтінді іздеу және шығару функциясы
Біз келесі әрекеттерді орындаймыз:
1. Excel бағдарламасын ашып, қойындыда жаңа бос Power Query жасаңыз Деректер – Деректерді алу / Сұраныс жасау – Басқа көздерден – Бос сұрау (Деректер — Деректерді алу / Жаңа сұрау — Басқа көздерден — Бос сұрау). Егер сізде Excel 2010-2013 және Power Query бағдарламасының ескі нұсқасы болса, сізде кірістірілген жоқ, бірақ бөлек қондырма ретінде орнатылған болса, мұның бәрі қойындыда болады. Қуат сұрауыЖоқ мәліметтер.
2. Ашылатын сұрау өңдегішінің бос терезесінде оң жақ панельде болашақ функциямыздың атын дереу енгізіңіз (мысалы, fxRegExpExtract)
3. Қойындыға барайық Көрініс – Кеңейтілген редактор (Қарау — Кеңейтілген редактор), біз бос сұраудың бүкіл M-кодын өшіреміз және сол жерге суперфункциямыздың кодын қоямыз:
Қолдарыңызды бақылаңыз:
Бірінші жолда біз функциямызда үш мәтіндік аргумент болатынын айтамыз: TXT – талданатын түпнұсқа мәтін; регистр – тұрақты өрнек үлгісі, шектеу — нәтижелерді көрсетуге арналған бөлгіш таңба.
Әрі қарай функцияны шақырамыз Веб парақ, оның аргументінде жоғарыда сипатталған JavaScript кодын қалыптастыру. Біз айнымалы аргументтерді кодқа қоямыз және ауыстырамыз.
Фрагмент:
[Деректер]{0}[Балалар]{0}[Балалар]{1}[Мәтін]{0}
... бізге қажетті нәтижелермен кестеге «түсу» үшін қажет. Мәселе мынада, бұл функция Веб парақ нәтижесінде ол веб-бет құрылымын қайталайтын бірнеше кірістірілген кестелерді шығарады. M-кодтың бұл бөлігі болмаса, біздің функция мынаны шығарады:
… және біз сөзді бірнеше рет басуымыз керек еді үстел, бағандардағы еншілес кірістірілген кестелерге дәйекті түрде "түшеді" Балалар:
Барлық осы дәйексөздің орнына біз функцияның кодында кесте мен бағанның кірістірілгенін бірден көрсетеміз (мәтін) бізге керек.
Міне, шын мәнінде, барлық құпиялар. Түймені басу қалады Аяқтау терезеде кеңейтілген редактор, онда біз кодты енгіздік, және сіз ең дәмдіге өтуіңізге болады - жұмыста біздің функцияны қолданып көріңіз.
Міне, тұқымның бірнеше мысалы.
Мысал 1. Төлем сипаттамасынан шот нөмірі мен күнін шығарып алу
Бізде төлемдердің сипаттамасы (мақсаты) бар банк үзіндісі бар, онда төленген шот-фактуралардың нөмірлері мен күндерін бөлек бағандарға шығару керек:
Біз кестені Power Query бағдарламасына стандартты жолмен жүктейміз Деректер – Кестеден/Ауқымнан (Дерек – Тқабілетті/Рперіште).
Содан кейін функциямыз арқылы есептелген бағанды қосамыз Баған қосу – теңшелетін функцияны шақыру (Баған қосу — теңшелетін функцияны шақыру) және оның аргументтерін енгізіңіз:
Тұрақты өрнек ретінде (аргумент регистр) біз қолданатын үлгі:
(d{3,5}|d{2}.d{2}.d{4})
… адам тіліне аударылған мағынасы:
3-тен 5-ке дейінгі сандар (шот нөмірлері)
or
«2 разрядты сан – нүкте – 2 разряд – нүкте – 4 разрядтық сан» пішінінің фрагменттері, яғни DD.MM.YYYY пішінінің күндері.
Бөлгіш таңба ретінде (аргумент шектеу) нүктелі үтірді енгізіңіз.
Басқаннан кейін OK Біздің сиқырлы функция барлық бастапқы деректерді тұрақты өрнекке сәйкес талдайды және біз үшін шот-фактуралардың табылған нөмірлері мен күндері бар бағанды құрайды:
Оны пәрмен арқылы нүктелі үтірмен бөлу қалады Басты — Бөлінген баған — Бөлгіш бойынша (Басты – Бөлу баған – Бөлгіш бойынша) және біз қалағанымызды аламыз:
Сұлулық!
2-мысал: Мәтіннен электрондық пошта мекенжайларын шығарып алыңыз
Бізде бастапқы деректер ретінде келесі кесте бар делік:
… сол жерден табылған электрондық пошта мекенжайларын шығарып алуымыз керек (түсінікті болу үшін мен оларды мәтінде қызыл түспен белгіледім).
Алдыңғы мысалдағыдай, кестені Power Query бағдарламасына стандартты жолмен жүктейміз Деректер – Кестеден/Ауқымнан (Дерек – Тқабілетті/Рперіште).
Содан кейін функциямыз арқылы есептелген бағанды қосамыз Баған қосу – теңшелетін функцияны шақыру (Баған қосу — теңшелетін функцияны шақыру) және оның аргументтерін енгізіңіз:
Электрондық пошта мекенжайларын талдау қиынырақ тапсырма және оны шешу үшін әртүрлі деңгейдегі қорқынышты тұрақты өрнектер бар. Мен қарапайым опциялардың бірін қолдандым - идеалды емес, бірақ көп жағдайда жұмыс істейді:
[w|.|-]*@w*.[w|.]*
Бөлгіш ретінде (шектеу) нүктелі үтір мен бос орын енгізуге болады.
Басыңыз OK және біз «ботқа» түпнұсқа мәтінінен алынған электрондық пошта мекенжайлары бар бағанды аламыз:
Сиқырлы!
PS
«Одан да жақсырақ болмайтын жақсы нәрсе жоқ» дегендей. Power Query өздігінен керемет және тұрақты өрнектермен біріктірілгенде, ол бізге кез келген мәтіндік деректерді өңдеуде мүлдем шынайы емес қуат пен икемділік береді. Microsoft корпорациясы бір күні Power Query және Power BI жаңартуларында RegExp қолдауын қосады деп үміттенемін және жоғарыда аталған домбырамен билеулердің барлығы өткен нәрсеге айналады. Ал, әзірше, иә.
Сондай-ақ, https://regexr.com/ сайтында тұрақты өрнектермен ойнау ыңғайлы екенін қосқым келеді – тікелей онлайн редакторында. Онда бөлімде Қауымдастық үлгілері Барлық жағдайлар үшін дайын тұрақты маусымдардың үлкен саны бар. Эксперимент – тұрақты өрнектердің барлық күші енді Power Query қызметінде!
- Тұрақты өрнектер (RegExp) дегеніміз не және оларды Excel бағдарламасында қалай пайдалану керек
- Power Query қолданбасында анық емес мәтін іздеу
- Power Query көмегімен әртүрлі файлдардан кестелерді құрастыру