Мундариҷа
Агар шумо ақаллан каме бо ибораҳои муқаррарӣ шинос бошед, пас ба шумо лозим нест, ки онҳоро таблиғ кунед. Агар шумо пурра дар ин мавзӯъ набошед, пас ибораҳои муқаррарӣ (Ифодаҳои муқаррарӣ = RegExp = “regexps” = “regulars”) забонест, ки дар он бо истифода аз аломатҳо ва қоидаҳои махсус зерсатри зарурӣ дар матн ҷустуҷӯ карда мешаванд, онҳо истихроҷ карда мешаванд. ё бо матни дигар иваз карда шавад. Ин як асбоби хеле пурқувват ва зебост, ки аз ҳама усулҳои дигари кор бо матн бартарӣ дорад.
Ман аллакай ба таври муфассал ва бо як қатор мисолҳо аз ҳаёт тавсиф кардам, ки чӣ гуна шумо метавонед бо истифода аз макросҳои оддӣ ба Excel дастгирии ифодаи муқаррариро илова кунед - агар шумо ин мақоларо нахонда бошед, ман тавсия медиҳам, ки пеш аз идома додан онро хонед. Шумо бисёр чизҳои навро кашф хоҳед кард, ман кафолат медиҳам 🙂
Бо вуҷуди ин, савол боз боқӣ мемонад - чӣ гуна қобилияти истифодаи ифодаҳои муқаррариро дар Power Query илова кардан мумкин аст? Power Query, албатта, худ аз худ хуб аст ва метавонад бо матн бисёр корҳоро анҷом диҳад (буридан, часпак кардан, тоза кардан ва ғайра), аммо агар шумо онро бо қудрати ибораҳои муқаррарӣ убур карда тавонед, он танҳо як бомба мебуд.
Мутаассифона, ягон функсияи дарунсохт барои кор бо RegExps дар Power Query вуҷуд надорад ва кӯмаки расмии Microsoft ва дастгирии техникии ин савол ба таври манфӣ ҷавоб медиҳанд. Бо вуҷуди ин, роҳи атрофи ин маҳдудият вуҷуд дорад 🙂
Моҳияти усул
Идеяи асосй оддй аст, ки расво кардан.
Дар рӯйхати қобилиятҳои дарунсохташудаи Power Query, функсия мавҷуд аст Торнамо. Тавсифи ин функсия дар сайти расмии ёрирасони Microsoft хеле мухтасар аст:
Тарҷумашуда, ин чунин хоҳад буд: "Мӯҳтавои ҳуҷҷати HTML-ро, ки ба сохторҳои ҷузъии он тақсим карда шудаанд, инчунин намоиши ҳуҷҷати пурра ва ҷисми онро пас аз нест кардани тегҳо бармегардонад." Ҳамин тавр тавсиф, ошкоро.
Одатан, ин функсия ҳангоми воридоти маълумот аз веб истифода мешавад ва ба таври худкор иваз карда мешавад, масалан, вақте ки мо дар ҷадвал интихоб мекунем Маълумот фармон Аз интернет (Маълумот — аз веб). Мо ба функсия ҳамчун аргумент саҳифаи веб медиҳем ва он мундариҷаи худро дар шакли ҷадвалҳо ба мо бармегардонад, ки қаблан ҳамаи тегҳоро тоза карда буд.
Он чизе ки кӯмак намегӯяд, ин аст, ки ба ғайр аз забони аломатгузории HTML функсия Торнамо скриптҳои JavaScript-ро дастгирӣ мекунад, ки ҳоло дар вебсайтҳои интернетӣ дар ҳама ҷо ҷойгир аст. Ва JavaScript, дар навбати худ, ҳамеша тавонист бо ифодаҳои муқаррарӣ кор кунад ва дорои функсияҳои дарунсохт барои RegExps! Ҳамин тавр, барои татбиқи ифодаҳои муқаррарӣ дар Power Query, мо бояд вазифаҳои Web.Page-ро ҳамчун далел ба як барномаи хурди JavaScript таъмин кунем, ки тамоми корҳоро барои Power Query иҷро мекунад.
Он дар JavaScript-и тоза чӣ гуна ба назар мерасад
Дар Интернет бисёр дастурҳои муфассал оид ба кор бо ибораҳои муқаррарӣ дар JavaScript мавҷуданд (масалан, як, ду).
Дар кӯтоҳ ва соддакардашуда, рамзи JavaScript чунин хоҳад буд:
Ин ҷо:
- var str = 'Ҳисобҳои 123 ва 789-ро барои ҳасиб пардохт кунед'; - тағирёбанда эҷод кунед кӯча ва ба он матни сарчашмаеро таъин кунед, ки мо онро таҳлил мекунем.
- var pattern = /d+/gi; – ифодаи муқаррарӣ созед ва онро ба тағирёбанда гузоред намуна.
Ифода бо аломати слэш (/) оғоз мешавад.
Худи ифода дар ин ҷо, масалан, аст d+ барои ҳар як пайдарпаии рақамҳо истодагарӣ мекунад.
Тавассути касри пас аз ифода, параметрҳои иловагии ҷустуҷӯ (тағирдиҳандаҳо) мавҷуданд - онҳоро метавон бо дилхоҳ тартиб нишон дод:
- g – маънои ҷустуҷӯи умумиро дорад, яъне пас аз дарёфти мувофиқат, шумо набояд қатъ накунед, балки ҷустуҷӯро то охири матн идома диҳед. Агар ин тағирдиҳанда таъин нашуда бошад, пас скрипти мо танҳо бозии аввалро бармегардонад (123)
- i – кофтуков бе назардошти ҳолати ҳарфҳо
- m - Ҷустуҷӯи бисёрсатрӣ (ҳангоми ба якчанд сатр тақсим шудани матни манбаъ истифода мешавад)
- var натиҷа = str.match(намуна).ҳамроҳ шудан(';'); - ҷустуҷӯро дар матни манбаъ анҷом диҳед (кӯча) бо ифодаи муқаррарии додашуда (намуна) ва натиҷаҳоро ба тағирёбанда гузоред натиҷа, бо истифода аз фармон пайваст кардани онҳо бо нуқта-вергул ҳамроҳ
- ҳуҷҷат.навиштан(натиҷа); – намоиши мундариҷаи тағирёбандаи натиҷа
Инчунин қайд кунед, ки сатрҳои матнӣ (ба истиснои ифодаҳои муқаррарӣ) дар JavaScript дар апострофҳо дохил карда шудаанд, на иқтибосҳо, зеро онҳо дар Power Query ё VBA мебошанд.
Дар натиҷа, ин скрипт ба мо ҳама рақамҳои дар матни манбаъ мавҷудбударо медиҳад:
123, 789
Курси кӯтоҳи JavaScript ба охир расид, ба ҳама ташаккур. Умедворам, ки шумо мантиқро ба даст меоред 🙂
Интиқоли ин сохтмон ба Power Query боқӣ мемонад.
Функсияи матнро бо ифодаи муқаррарӣ дар Power Query ҷустуҷӯ ва истихроҷ
Мо корҳои зеринро иҷро мекунем:
1. Excel-ро кушоед ва дар ҷадвал дархости нави холии барқро эҷод кунед Маълумот - Гирифтани маълумот / Эҷоди дархост - Аз дигар манбаъҳо - Дархости холӣ (Маълумот - Гирифтани маълумот / Дархости нав - Аз дигар манбаъҳо - Дархости холӣ). Агар шумо версияи кӯҳнаи Excel 2010-2013 ва Power Query дошта бошед, шумо дарун надоред, аммо ҳамчун иловаи алоҳида насб карда шуда бошед, пас ҳамаи ин дар ҷадвал хоҳад буд. Дархости барқВа на Маълумот.
2. Дар равзанаи холии муҳаррири дархост, ки дар панели рост кушода мешавад, фавран номи функсияи ояндаи моро ворид кунед (масалан, fxRegExpExtract)
3. Биёед ба ҷадвал равем Намоиш - Муҳаррири пешрафта (Намоиш — Муҳаррири пешрафта), мо тамоми рамзи M-и дархости холиро нест мекунем ва рамзи суперфунксияи худро дар он ҷо мегузорем:
Дастҳои худро нигоҳ доред:
Дар сатри аввал мо мегӯем, ки функсияи мо дорои се далели матнӣ хоҳад буд: txt – матни аслии мавриди таҳлил қарордошта; регекс - намунаи ифодаи муқаррарӣ, ҳудуд — аломати ҷудокунанда барои намоиши натиҷаҳо.
Минбаъд мо функсияро даъват мекунем Торнамо, ташаккул додани рамзи JavaScript, ки дар аргументи он дар боло тавсиф шудааст. Мо далелҳои тағирёбандаи худро ба код часбонем ва иваз мекунем.
Фрагмент:
[Маълумот]{0}[Кӯдакон]{0}[Кӯдакон]{1}[Матн]{0}
... лозим аст, ки ба ҷадвал бо натиҷаҳои ба мо лозим "афтида". Гап дар он аст, ки функсия Торнамо дар натиља, он якчанд љадвалњои љойгиршударо тавлид мекунад, ки сохтори веб-сањифаро такрор мекунанд. Бе ин порчаи M-код, функсияи мо чунин мебарорад:
... ва мо бояд калимаро якчанд маротиба пахш кунем Љадвали, пай дар пай ба ҷадвалҳои лона гузошташуда дар сутунҳо "меафтад" Кӯдакон:
Ба ҷои ин ҳама иқтибос, мо дарҳол дар коди функсияи худ нишон медиҳем, ки ҷадвал ва сутун (Матн) ба мо лозим аст.
Дар ин ҷо, дар асл, ҳама асрори. Барои пахш кардани тугма боқӣ мемонад тамом кардан дар тиреза муҳаррири пешрафта, ки мо рамзи худро ворид кардем ва шумо метавонед ба лазизтарин гузаред - функсияи моро дар ҷои кор санҷед.
Дар ин ҷо якчанд мисоли тухмиҳо ҳастанд.
Мисоли 1. Гирифтани рақами суратҳисоб ва сана аз тавсифи пардохт
Мо як изҳороти бонкӣ бо тавсифи (мақсад) пардохтҳо дорем, ки дар он шумо бояд рақамҳо ва санаи ҳисобнома-фактураҳои пардохтшударо ба сутунҳои алоҳида кашед:
Мо ҷадвалро ба Power Query бо роҳи стандартӣ бор мекунем Маълумот - Аз ҷадвал/диапазон (Маълумот — Аз Тқодир/Рфаришта).
Сипас мо сутуни ҳисобшударо бо функсияи худ тавассути Иловаи сутун - Функсияи фармоишии занг (Илова кардани сутун - Функсияи фармоиширо даъват кунед) ва далелҳои онро ворид кунед:
Ҳамчун ифодаи муқаррарӣ (далел регекс) қолаберо, ки мо истифода мебарем:
(г{3,5}|д{2}.д{2}.д{4})
… ба забони инсон тарҷума шудааст, маънои:
рақамҳо аз 3 то 5 рақам (рақамҳои ҳисоб)
or
порчаҳои шакли «рақами 2-бит - адади 2-бит - нуқта - рақами 4-бит», яъне санаҳои шакли DD.MM.YYYY.
Ҳамчун аломати ҷудокунанда (далел ҳудуд) нуктаи вергул ворид кунед.
Баъд аз пахш кардан OK Функсияи ҷодугарии мо ҳама маълумоти ибтидоиро мувофиқи ифодаи муқаррарии мо таҳлил мекунад ва барои мо сутунеро бо рақамҳо ва санаҳои ҳисобнома-фактураҳои ёфтшуда ташкил медиҳад:
Бо истифода аз фармон бо нуқта-вергул ҷудо кардани он боқӣ мемонад Хона — Сутуни тақсим — Бо ҷудокунанда (Хона - Сутуни тақсим - Бо ҷудокунанда) ва мо он чизеро, ки мехостем, ба даст меорем:
Зебоӣ!
Мисоли 2: Суроғаҳои почтаи электрониро аз матн хориҷ кунед
Фарз мекунем, ки мо ҳамчун маълумоти ибтидоӣ ҷадвали зерин дорем:
… аз он ҷое, ки мо бояд суроғаҳои почтаи электронии дар он ҷо мавҷудбударо хориҷ кунем (барои возеҳият, ман онҳоро дар матн бо ранги сурх қайд кардам).
Мисли мисоли қаблӣ, мо ҷадвалро ба Power Query бо роҳи стандартӣ тавассути Маълумот - Аз ҷадвал/диапазон (Маълумот — Аз Тқодир/Рфаришта).
Сипас мо сутуни ҳисобшударо бо функсияи худ тавассути Иловаи сутун - Функсияи фармоишии занг (Илова кардани сутун - Функсияи фармоиширо даъват кунед) ва далелҳои онро ворид кунед:
Таҳлили суроғаҳои почтаи электронӣ кори мушкилтар аст ва барои ҳалли он як қатор ифодаҳои муқаррарии дараҷаҳои гуногуни даҳшат вуҷуд доранд. Ман яке аз вариантҳои оддиро истифода кардам - идеалӣ нест, аммо дар аксари ҳолатҳо хеле кор мекунад:
[w|.|-]*@w*.[w|.]*
Ҳамчун ҷудокунанда (ҳудуд) шумо метавонед нуқтаи вергул ва фосила ворид кунед.
Тафтиш кунед OK ва мо сутунеро ба даст меорем, ки суроғаҳои почтаи электрониро аз матни аслии "порридж" гирифтаанд:
Ҷодугар!
PS
Чунон ки дар урфият мегуянд: «Чун чизи хубе нест, ки аз ин хам бехтар карда нашавад». Power Query худ аз худ сард аст ва дар якҷоягӣ бо ибораҳои муқаррарӣ, он ба мо қудрат ва чандирии комилан ғайривоқеиро дар коркарди ҳама гуна маълумоти матнӣ медиҳад. Умедворам, ки Microsoft рӯзе дастгирии RegExp-ро дар навсозиҳои Power Query ва Power BI илова мекунад ва ҳамаи рақсҳои дар боло бо даф ба гузашта табдил меёбанд. Хуб, ҳоло, ҳа.
Ман инчунин мехоҳам илова кунам, ки бозӣ кардан бо ибораҳои муқаррарӣ дар сайти https://regexr.com/ - рост дар муҳаррири онлайн қулай аст. Он ҷо дар бахш Намунаҳои ҷомеа Шумораи зиёди мавсимҳои муқаррарии омода барои ҳама ҳолатҳо мавҷуданд. Таҷриба - ҳама қудрати ифодаҳои муқаррарӣ ҳоло дар хидмати шумо дар Power Query аст!
- Ифодаҳои муқаррарӣ (RegExp) чистанд ва чӣ гуна онҳоро дар Excel истифода бурдан мумкин аст
- Ҷустуҷӯи матни номуайян дар Power Query
- Ҷамъоварии ҷадвалҳо аз файлҳои гуногун бо истифода аз Power Query