мазмуну
Эгер сиз кадимки сөз айкаштары менен бир аз болсо да тааныш болсоңуз, анда аларды жарнамалоонун кереги жок. Эгер сиз бул темада толук эмес болсоңуз, анда регулярдуу туюнтмалар (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 pattern = /d+/gi; – регулярдуу туюнтманы түзүңүз жана аны өзгөрмөгө коюңуз узор.
Туунду сызык (/) менен башталат.
Бул жерде сөздүн өзү, мисалы, болуп саналат d+ цифралардын каалаган ырааттуулугун билдирет.
Тууралоодон кийинки бөлчөк аркылуу кошумча издөө параметрлери (модификаторлор) бар – аларды каалаган тартипте көрсөтүүгө болот:
- g – глобалдык издөө дегенди билдирет, башкача айтканда дал келген нерсени тапкандан кийин, сиз токтоп калбастан, тексттин аягына чейин издөөнү улантыңыз. Эгерде бул өзгөрткүч орнотулбаса, анда биздин скрипт биринчи дал келүүнү гана кайтарат (123)
- i – тамгаларды эсепке албастан издөө
- m – көп саптуу издөө (баштапкы текст бир нече саптарга бөлүнгөндө колдонулат)
- var result = str.match(pattern).join(';'); – баштапкы текстте издөө жүргүзүү (ул) берилген туруктуу сөз айкашы менен (узор) жана натыйжаларды өзгөрмөгө коюңуз жыйынтык, буйругун колдонуу менен аларды чекиттүү үтүр менен бириктирүү кошулуу
- документ.жазуу(натыйжа); – натыйжа өзгөрмөнүн мазмунун көрсөтүү
Ошондой эле, 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 - түпнуска текстти талдоо; regex - кадимки туюнтма үлгүсү, делим — натыйжаларды көрсөтүү үчүн бөлүүчү белги.
Андан кийин биз функцияны чакырабыз Веб баракча, анын аргументинде жогоруда сүрөттөлгөн JavaScript кодун түзүү. Кодго өзгөрмө аргументибизди чаптап, алмаштырабыз.
Фрагмент:
[Дайындар]{0}[Балдар]{0}[Балдар]{1}[Текст]{0}
... бизге керектүү натыйжалар менен таблицага "жыгылып кетүү" керек. Кеп функцияда Веб баракча Натыйжада, ал веб-баракчанын структурасын кайталаган бир нече уяланган таблицаларды чыгарат. M-коддун бул бөлүгү болбосо, биздин функция муну чыгарат:
… жана биз сөздү бир нече жолу басууга туура келет стол, ырааттуу түрдө мамычалардагы уяча салынган таблицаларга "түшүүдө" балдар:
Бардык бул цитаталардын ордуна, биз дароо функциябыздын кодунда кайсы таблицаны жана тилкени (текст) бизге керек.
Бул жерде, чынында, бардык сырлар. Бул баскычты басуу бойдон калууда бүтүрүү терезеде өркүндөтүлгөн редактор, анда биз кодубузду киргиздик жана сиз эң даамдууга өтсөңүз болот - биздин функцияны жумушта байкап көрүңүз.
Бул жерде бир нече үрөн мисалдары бар.
Мисал 1. Төлөмдүн сүрөттөмөсүнөн эсептин номерин жана датасын алуу
Бизде төлөмдөрдүн сыпаттамасы (максаты) бар банктык көчүрмө бар, анда төлөнгөн эсеп-фактуралардын номерлерин жана даталарын өзүнчө графаларга чыгаруу керек:
Таблицаны Power Queryге стандарттуу жол менен жүктөйбүз Берилиштер – Таблицадан/Аразиядан (Маалыматтар — Тжөндөмдүү/Рпериште).
Андан кийин аркылуу функциябыз менен эсептелген тилкени кошобуз Мамыча кошуу - Ыңгайлаштырылган функцияны чакыруу (Мамыча кошуу — Ыңгайлаштырылган функцияны чакыруу) жана анын аргументтерин киргизиңиз:
Кадимки сөз айкашы катары (аргумент regex) биз колдонгон шаблон:
(г{3,5}|d{2}.d{2}.d{4})
… адам тилине которулган мааниси:
3төн 5 орунга чейинки сандар (эсептин номерлери)
or
“2-бит сан – чекит – 2-бит сан – чекит – 4-бит сан” түрүндөгү фрагменттери, башкача айтканда, DD.MM.YYYY түрүндөгү даталар.
Бөлүүчү белги катары (аргумент делим) үтүрлүү чекит киргизиңиз.
Кийин басуу боюнча OK Биздин сыйкырдуу функциябыз бардык баштапкы маалыматтарды кадимки туюнтмабызга ылайык талдайт жана биз үчүн эсеп-фактуралардын табылган сандары жана датасы менен мамычаны түзөт:
Аны буйрукту колдонуу менен чекиттүү үтүр менен бөлүү калды Башкы бет — Бөлүнгөн тилке — Бөлүүчү боюнча (Башкы бет — Бөлүнгөн тилке — Бөлүүчү боюнча) жана биз каалаган нерсени алабыз:
Сулуулук!
Мисал 2: Тексттен электрондук почта даректерин чыгарып алыңыз
Алгачкы маалыматтар катары бизде төмөнкү таблица бар дейли:
… ошол жерден табылган электрондук почта даректерин чыгарып алышыбыз керек (түшүнүктүү болушу үчүн мен аларды текстте кызыл түс менен бөлүп көрсөттүм).
Мурунку мисалдагыдай эле, биз таблицаны Power Queryге стандарттуу түрдө жүктөйбүз Берилиштер – Таблицадан/Аразиядан (Маалыматтар — Тжөндөмдүү/Рпериште).
Андан кийин аркылуу функциябыз менен эсептелген тилкени кошобуз Мамыча кошуу - Ыңгайлаштырылган функцияны чакыруу (Мамыча кошуу — Ыңгайлаштырылган функцияны чакыруу) жана анын аргументтерин киргизиңиз:
Электрондук почта даректерин талдоо татаалыраак иш болуп саналат жана аны чечүү үчүн ар кандай деңгээлдеги коркунучтуу туюнтмалардын бир тобу бар. Мен жөнөкөй варианттардын бирин колдондум - идеалдуу эмес, бирок көпчүлүк учурда абдан жакшы иштейт:
[w|.|-]*@w*.[w|.]*
бөлүүчү катары (делим) чекиттүү үтүр жана боштук киргизе аласыз.
Click жөнүндө OK жана биз түпнуска тексттен алынган электрондук почта даректери бар тилкени алабыз "каткы":
Magic!
PS
«Мындан да жакшырбай турган жакшы нерсе жок» дегендей. Power Query өзүнчө сонун жана кадимки туюнтмалар менен айкалышканда, ал бизге эч кандай тексттик маалыматтарды иштетүүдө реалдуу эмес күч жана ийкемдүүлүк берет. Microsoft качандыр бир күнү Power Query жана Power BI жаңыртууларында RegExp колдоосун кошот деп үмүттөнөм жана жогоруда айтылган дап менен бийлер өткөн нерсе болуп калат. Ооба, азыр, ооба.
Мен ошондой эле https://regexr.com/ сайтында туруктуу сөз айкаштары менен ойноо ыңгайлуу экенин кошумчалай кетким келет – түз онлайн редактордо. Бөлүмдө бар Community Patterns Бардык учурлар үчүн даярдалган үзгүлтүксүз мезгилдердин көп саны бар. Эксперимент – кадимки туюнтмалардын бардык күчү азыр Power Query кызматында!
- Кадимки туюнтмалар (RegExp) деген эмне жана аларды Excelде кантип колдонуу керек
- Power Query'де бүдөмүк текст издөө
- Power Query аркылуу ар кандай файлдардан таблицаларды чогултуу