Садржај
Ако сте бар мало упознати са регуларним изразима, онда не морате да их рекламирате. Ако нисте баш у теми, онда су регуларни изрази (Регулар Екпрессионс = РегЕкп = “регекпс” = “регуларс”) језик у којем се помоћу специјалних знакова и правила траже потребни поднизови у тексту, они се издвајају или замењен другим текстом . Ово је веома моћан и леп алат, ред величине супериорнији од свих других начина рада са текстом.
Већ сам детаљно и са гомилом примера из живота описао како можете додати подршку за регуларне изразе у Екцел користећи једноставне макрое – ако нисте прочитали овај чланак, топло препоручујем да га прочитате пре него што наставите. Открићете много нових ствари, гарантујем 🙂
Међутим, остаје отворено питање – како додати могућност коришћења регуларних израза у Повер Куерију? Повер Куери је, наравно, добар сам по себи и може много тога да уради са текстом (сечење, лепљење, чишћење итд.), али ако бисте га могли укрстити снагом регуларних израза, то би била само бомба.
Нажалост, не постоје уграђене функције за рад са РегЕкпс-ом у Повер Куери-у, а званична Мицрософт помоћ и техничка подршка одговарају на ово питање негативно. Међутим, постоји начин да се заобиђе ово ограничење 🙂
Суштина методе
Главна идеја је једноставна за срамоту.
На листи уграђених Повер Куери могућности постоји функција Веб страна. Опис ове функције на званичном сајту за помоћ компаније Мицрософт је изузетно сажет:
Преведено, ово би било: „Враћа садржај ХТМЛ документа разбијен на структуре његових компоненти, као и приказ целог документа и његовог тела након што су ознаке уклоњене.“ Тако-тако опис, искрено.
Обично се ова функција користи приликом увоза података са веба и аутоматски се замењује, на пример, када изаберемо на картици Датум команда Од интернета (Подаци — са веба). Дајемо функцији веб страницу као аргумент, а она нам враћа њен садржај у облику табела, након што је претходно обрисала све ознаке.
Оно што помоћ НЕ каже је да поред ХТМЛ језика за означавање функција Веб страна подржава ЈаваСцрипт скрипте, који је сада свеприсутан на веб локацијама на Интернету. А ЈаваСцрипт је, заузврат, увек могао да ради са регуларним изразима и има уграђене функције за РегЕкпс! Дакле, да бисмо имплементирали регуларне изразе у Повер Куери, мораћемо да унесемо функције Веб.Паге као аргумент малом ЈаваСцрипт програму који ће обавити сав посао за Повер Куери.
Како то изгледа у чистом ЈаваСцрипт-у
На Интернету постоји много детаљних туторијала о раду са регуларним изразима у ЈаваСцрипт-у (на пример, један, два).
Укратко и поједностављено, ЈаваСцрипт код ће изгледати овако:
Овде:
- вар стр = 'Плати рачуне 123 и 789 за кобасицу'; – креирати променљиву Стр и доделити му изворни текст који ћемо анализирати.
- вар образац = /д+/ги; – креирајте регуларни израз и ставите га у променљиву образац.
Израз почиње косом цртом (/).
Сам израз овде, на пример, јесте d+ означава било који низ цифара.
Кроз разломак после израза постоје додатни параметри претраге (модификатори) – они се могу навести било којим редоследом:
- g – означава глобалну претрагу, односно након проналажења подударања, не треба прекинути, већ наставити претрагу до краја текста. Ако овај модификатор није подешен, онда ће наша скрипта вратити само прво подударање (123)
- i – претраживање без обзира на велика и мала слова
- m – претрага у више редова (користи се када је изворни текст подељен на неколико редова)
- вар резултат = стр.матцх(паттерн).јоин(';'); – изврши претрагу у изворном тексту (Стр) према датом регуларном изразу (образац) и стави резултате у променљиву резултат, спајајући их са тачком и зарезом помоћу команде придружи
- доцумент.врите(резултат); – приказати садржај променљиве резултата
Такође имајте на уму да су текстуални низови (осим регуларних израза) у ЈаваСцрипт-у затворени у апострофе, а не наводнике као што су у Повер Куери-у или ВБА.
На излазу, ова скрипта ће нам као резултат дати све бројеве који се налазе у изворном тексту:
КСНУМКС, КСНУМКС
Кратки курс ЈаваСцрипт је завршен, хвала свима. Надам се да сте схватили логику 🙂
Остаје да се ова конструкција пренесе на Повер Куери.
Функција претраживања и издвајања текста помоћу регуларног израза у Повер Куерију
Радимо следеће:
1. Отворите Екцел и креирајте нови празан Повер Куери на картици Подаци – Добијте податке / Креирајте захтев – Из других извора – Празан захтев (Подаци — Добијте податке / Нови упит — Из других извора — Празан упит). Ако имате стару верзију програма Екцел 2010-2013 и Повер Куери-а немате уграђен, већ је инсталиран као посебан додатак, онда ће све ово бити на картици Повер КуериИ не Датум.
2. У празан прозор уређивача упита који се отвори, у десном панелу, одмах унесите назив наше будуће функције (нпр. фкРегЕкпЕктрацт)
3. Идемо на картицу Поглед – Напредни уређивач (Приказ — Напредни уређивач), бришемо цео М-код празног захтева и тамо налепимо код наше суперфункције:
Пази на руке:
У првом реду кажемо да ће наша функција имати три текстуална аргумента: ТКСТ – оригинални текст који се анализира, регек – образац регуларног израза, делим — знак за разграничење за приказ резултата.
Затим позивамо функцију Веб страна, формирајући ЈаваСцрипт код описан горе у свом аргументу. Налепимо и заменимо наше променљиве аргументе у код.
Фрагмент:
[Подаци]{0}[Деца]{0}[Деца]{1}[Текст]{0}
… је потребно да „упаднемо“ у табелу са резултатима који су нам потребни. Поента је да функција Веб страна као резултат, производи неколико угнежђених табела које понављају структуру веб странице. Без овог дела М-кода, наша функција би избацила ово:
… и морали бисмо да кликнемо на реч неколико пута Табела, сукцесивно „пропадајуће“ у подређене угнежђене табеле у колонама Деца:
Уместо целог овог цитата, у коду наше функције одмах указујемо која је угнежђена табела и колона (текст) морамо.
Овде су, у ствари, све тајне. Остаје да притиснете дугме завршити у прозору напредни уредник, где смо убацили наш код, а ви можете да пређете на најукусније – испробајте нашу функцију на послу.
Ево неколико примера семена.
Пример 1. Преузимање броја рачуна и датума из описа плаћања
Имамо банковни извод са описом (наменом) плаћања, где је потребно да извучете бројеве и датуме плаћених фактура у посебне колоне:
Учитавамо табелу у Повер Куери на стандардни начин Подаци – из табеле/опсега (Подаци — од Тспособан/Ранђео).
Затим додајемо израчунату колону са нашом функцијом преко Додај колону – Позовите прилагођену функцију (Додај колону — Позовите прилагођену функцију) и унесите његове аргументе:
Као регуларни израз (аргумент регек) шаблон који користимо:
(д{3,5}|д{2}.д{2}.д{4})
… преведено на људски језик значење:
бројеви од 3 до 5 цифара (бројеви рачуна)
or
фрагменти облика „2-битни број – тачка – 2-битни број – тачка – 4-битни број”, односно датуме облика ДД.ММ.ГГГГ.
Као знак за раздвајање (аргумент делим) унесите тачку и зарез.
После клика на OK наша магична функција анализира све почетне податке према нашем регуларном изразу и формира колону за нас са пронађеним бројевима и датумима фактура:
Остаје да га одвојите тачком и зарезом помоћу команде Почетна — Подели колону — По граничнику (Почетна — Подели колону — По граничнику) и добијамо оно што смо желели:
Лепота!
Пример 2: Издвојите имејл адресе из текста
Претпоставимо да имамо следећу табелу као почетне податке:
… одакле треба да извучемо адресе е-поште које се тамо налазе (ради јасноће, означио сам их црвеном бојом у тексту).
Као иу претходном примеру, учитавамо табелу у Повер Куери на стандардни начин преко Подаци – из табеле/опсега (Подаци — од Тспособан/Ранђео).
Затим додајемо израчунату колону са нашом функцијом преко Додај колону – Позовите прилагођену функцију (Додај колону — Позовите прилагођену функцију) и унесите његове аргументе:
Рашчлањивање адреса е-поште је тежи задатак и постоји гомила регуларних израза различитог степена ноћне море за његово решавање. Користио сам једну од једноставних опција – није идеална, али у већини случајева прилично ради:
[в|.|-]*@в*.[в|.]*
Као сепаратор (делим) можете унети тачку и размак.
Кликните на OK и добијамо колону са адресама е-поште извучене из оригиналног текста „каша“:
Магија!
PS
Како се каже: „Нема тако доброг што се не може учинити још бољим“. Повер Куери је кул сам по себи, а када се комбинује са регуларним изразима, даје нам потпуно нереалну моћ и флексибилност у обради било каквих текстуалних података. Надам се да ће Мицрософт једног дана додати РегЕкп подршку у Повер Куери и Повер БИ ажурирања и сви горе наведени плесови са тамбуром ће постати ствар прошлости. Па, за сада, да.
Такође желим да додам да је згодно играти се регуларним изразима на сајту хттпс://регекр.цом/ – директно у онлајн уређивачу. Тамо у одељку Обрасци заједнице Постоји огроман број готових редовних сезона за све прилике. Експериментишите – сва моћ регуларних израза вам је сада на услузи у Повер Куерију!
- Шта су регуларни изрази (РегЕкп) и како их користити у Екцел-у
- Претраживање нејасног текста у Повер Куери-ју
- Склапање табела из различитих датотека користећи Повер Куери