Садржај
Један од најзахтевнијих и најфрустрирајућих задатака при раду са текстом у Екцел-у је рашчлањивање – рашчлањивање алфанумеричке „каше“ на компоненте и извлачење фрагмената који су нам потребни. На пример:
- издвајање поштанског броја из адресе (добро је ако је поштански број увек на почетку, али шта ако није?)
- проналажење броја и датума рачуна из описа уплате у изводу банке
- издвајање ПИБ-а из шароликих описа предузећа у списку уговорних страна
- потражите у опису број аутомобила или артикла итд.
Обично у таквим случајевима, након пола сата туробног ручног пребирања текста, почну да падају на памет мисли да се некако аутоматизује овај процес (нарочито ако има много података). Постоји неколико решења и са различитим степеном сложености-ефикасности:
- употреба уграђене Екцел текстуалне функције за претрагу-исецање-лепљење текста: ЛЕВСИМВ (LEVO), ПРАВО (ЈЕЛ ТАКО), ПСТР (средина), СТсЕПИТ (СПОЈИТИ) и његових аналога, ЦОМБИНЕ (ЈОИНТЕКСТ), ЕКСАЦТ (ТАЧНО) итд. Овај метод је добар ако постоји јасна логика у тексту (на пример, индекс је увек на почетку адресе). У супротном, формуле постају много компликованије, а понекад чак долази и до формула низа, што у великој мери успорава на великим табелама.
- Коришћење попут оператора сличности текста из Висуал Басица умотаног у прилагођену макро функцију. Ово вам омогућава да примените флексибилнију претрагу користећи џокер знакове (*, #,?, итд.) Нажалост, овај алат не може да издвоји жељени подниз из текста – само проверите да ли се он налази у њему.
Поред наведеног, постоји још један приступ који је веома познат у уским круговима професионалних програмера, веб програмера и других техничара – ово је регуларни изрази (Регуларни изрази = РегЕкп = “регекпс” = “регуларс”). Једноставно речено, РегЕкп је језик у којем се користе посебни знакови и правила за тражење потребних подстрингова у тексту, њихово издвајање или замену другим текстом. Регуларни изрази су веома моћан и леп алат који за ред величине превазилази све друге начине рада са текстом. Многи програмски језици (Ц#, ПХП, Перл, ЈаваСцрипт…) и уређивачи текста (Ворд, Нотепад++…) подржавају регуларне изразе.
Мицрософт Екцел нажалост нема подршку за РегЕкп из кутије, али то се лако може поправити помоћу ВБА. Отворите Висуал Басиц Едитор са картице развијач (Програмер) или пречица на тастатури други+ФКСНУМКС. Затим уметните нови модул кроз мени Инсерт – Модул и тамо копирајте текст следеће макро функције:
Јавна функција РегЕкпЕктрацт(текст као стринг, образац као стринг, опциона ставка као цео број = 1) Као стринг при грешци ГоТо ЕррХандл Сет регек = ЦреатеОбјецт("ВБСцрипт.РегЕкп") регек.Паттерн = Паттерн регек.Глобал = Т Труест Иф реге (Текст) Затим поставите подударања = регек.Екецуте(Тект) РегЕкпЕктрацт = матцхес.Итем(Итем - 1) Излаз из функције Енд Иф ЕррХандл: РегЕкпЕктрацт = ЦВЕРр(клЕррВалуе) Крај функције Сада можемо да затворимо Висуал Басиц Едитор и вратимо се у Екцел да испробамо нашу нову функцију. Његова синтакса је следећа:
=РегЕкпЕктрацт(Ткт; Паттерн; Итем)
где
- ТКСТ – ћелија са текстом који проверавамо и из које желимо да издвојимо подниз који нам је потребан
- образац – маска (шаблон) за претрагу подстринга
- Тачка – редни број подниза који се издваја, ако их има неколико (ако није наведено, онда се приказује прво појављивање)
Најзанимљивија ствар овде је, наравно, Паттерн – шаблонски низ специјалних знакова „на језику“ РегЕкп-а, који одређује шта тачно и где желимо да пронађемо. Ево најосновнијих за почетак:
| Образац | Opis |
| . | Најједноставнији је тачка. Поклапа се са било којим знаком у шаблону на наведеној позицији. |
| s | Сваки знак који изгледа као размак (размак, табулатор или прелом реда). |
| S | Антиваријанта претходног обрасца, тј. било који знак без размака. |
| d | Било који број |
| D | Антиваријанта претходног, односно било која НЕ цифра |
| w | Било који латинични знак (АЗ), цифра или доња црта |
| W | Антиваријанта претходног, односно не латиница, не број и не доња црта. |
| [карактера] | У угластим заградама можете навести један или више знакова дозвољених на наведеној позицији у тексту. На пример Уметност ће одговарати било којој од речи: табела or столица. Такође не можете да набрајате знакове, већ их поставите као опсег одвојен цртицом, тј. [АБДЦДЕФ] write (писати) [АФ]. или уместо тога [КСНУМКС] увести [-4 7]. На пример, да бисте означили све ћириличке знакове, можете користити шаблон [а-иаА-ИаиоИо]. |
| [^карактера] | Ако после почетне угласте заграде додате симбол „поклопац“ ^, тада ће скуп добити супротно значење – на наведеној позицији у тексту биће дозвољени сви знакови осим наведених. Да, шаблон [^ЖМ]ут наћи Путања or Супстанца or заборавити, Али не Сцари or Мут, на пример. |
| | | Булов оператор OR (ИЛИ) да провери било који од наведених критеријума. На пример (сачет|счак|фактуру) тражиће у тексту било коју од наведених речи. Типично, скуп опција је затворен у заградама. |
| ^ | Почетак линије |
| $ | Крај линије |
| b | Крај речи |
Ако тражимо одређени број знакова, на пример, шестоцифрени поштански број или све трословне кодове производа, онда долазимо у помоћ квантификатори or квантификатори су посебни изрази који одређују број знакова који се траже. Квантификатори се примењују на карактер који долази пре њега:
| Куантор | Opis |
| ? | Нула или једна појава. На пример .? ће значити било који знак или његово одсуство. |
| + | Један или више уноса. На пример d+ означава било који број цифара (тј. било који број између 0 и бесконачности). |
| * | Нула или више појављивања, односно било која количина. Тако s* означава било који број размака или ниједан размак. |
| {број} or {бројКСНУМКС,бројКСНУМКС} | Ако треба да наведете стриктно дефинисан број појављивања, онда је то наведено у витичастим заградама. На пример д{6} значи стриктно шест цифара, а образац с{2,5} – два до пет места |
Пређимо сада на најзанимљивији део – анализу примене креиране функције и онога што смо научили о обрасцима на практичним примерима из живота.
Издвајање бројева из текста
За почетак, анализирајмо једноставан случај – потребно је да из алфанумеричке каше извучете први број, на пример, снагу непрекидног напајања из ценовника:
Логика која стоји иза регуларног израза је једноставна: d означава било коју цифру и квантификатор + каже да њихов број треба да буде један или више. Дупли минус испред функције је потребан да би се „у ходу“ претворили издвојени знакови у пун број из броја као текста.
Поштански
Овде је на први поглед све једноставно – тражимо тачно шест цифара за редом. Користимо посебан карактер d за цифру и квантификатор 6 {} за број знакова:
Међутим, могућа је ситуација када се лево од индекса у реду налази још један велики скуп бројева у низу (број телефона, ПИБ, банковни рачун, итд.) Тада ће наш регуларни део извући првих 6 цифре из њега, тј. неће радити исправно:
Да бисмо спречили да се то догоди, морамо да додамо модификатор око ивица нашег регуларног израза b означавајући крај речи. Ово ће Екцелу учинити јасно да фрагмент (индекс) који нам је потребан треба да буде посебна реч, а не део другог фрагмента (број телефона):
Телефон
Проблем са проналажењем броја телефона у тексту је што постоји толико опција за писање бројева – са и без цртица, кроз размаке, са или без кода региона у заградама итд. Стога је, по мом мишљењу, лакше прво очистите све ове знакове из изворног текста користећи неколико угнежђених функција ЗАМЕНА (ЗАМЕНА)тако да се слепи у јединствену целину, а затим са примитивним регуларним д{11} извуците 11 цифара за редом:
ИТН
Овде је мало компликованије, јер ПИБ (код нас) може бити 10-цифрени (за правна лица) или 12-цифрени (за физичка лица). Ако не нађете посебно замерке, онда је сасвим могуће бити задовољан редовним д{10,12}, али ће, строго говорећи, извући све бројеве од 10 до 12 знакова, односно погрешно унешених 11 цифара. Исправније би било користити два обрасца повезана логичким ОР оператором | (вертикална трака):
Имајте на уму да у упиту прво тражимо 12-битне бројеве, а тек онда 10-битне бројеве. Ако наш регуларни израз напишемо обрнуто, онда ће он извући за све, чак и дугачке 12-битне ТИН-ове, само првих 10 знакова. То јест, након што се покрене први услов, даља верификација се више не врши:
Ово је основна разлика између оператера | из стандардне ексел логичке функције OR (ИЛИ), где преуређивање аргумената не мења резултат.
Шифре производа
У многим компанијама се роби и услугама додељују јединствени идентификатори – артикли, САП кодови, СКУ-ови, итд. Ако постоји логика у њиховој нотацији, онда се могу лако извући из било ког текста помоћу регуларних израза. На пример, ако знамо да се наши чланци увек састоје од три велика енглеска слова, цртице и следећег троцифреног броја, онда:
Логика која стоји иза шаблона је једноставна. [АЗ] – означава свако велико слово латинице. Следећи квантификатор 3 {} каже да је за нас важно да постоје тачно три таква писма. После цртице чекамо три цифре, па додајемо на крају д{3}
Износи готовине
На сличан начин као у претходном пасусу можете извући цене (трошкове, ПДВ...) из описа робе. Ако су новчани износи, на пример, означени цртицом, онда:
Образац d са квантификатором + тражи било који број до цртице и д{2} ће тражити пеније (две цифре) после.
Ако треба да издвојите не цене, већ ПДВ, онда можете да користите трећи опциони аргумент наше функције РегЕкпЕктрацт, који одређује редни број елемента који треба да се издвоји. И, наравно, можете заменити функцију ЗАМЕНА (ЗАМЕНА) у резултатима ставите цртицу на стандардни децимални сепаратор и додајте дупли минус на почетку тако да Екцел протумачи пронађени ПДВ као нормалан број:
Бројеви аутомобила
If you do not take special vehicles, trailers and other motorcycles, then the standard car number is parsed according to the principle “letter – three numbers – two letters – region code”. Moreover, the region code can be 2- or 3-digit, and only those that are similar in appearance to the Latin alphabet are used as letters. Thus, the following regular expression will help us to extract numbers from the text:
Време
Да бисте издвојили време у формату ХХ:ММ, погодан је следећи регуларни израз:
После фрагмента дебелог црева [0-5]д, као што је лако схватити, поставља било који број у опсегу 00-59. Пре двоточка у заградама функционишу два обрасца, раздвојена логичким ИЛИ (цевом):
- [0-1]д – било који број у опсегу 00-19
- 2[0-3] – било који број у опсегу 20-23
На добијени резултат можете додатно применити стандардну Екцел функцију ВРЕМЕ (ТИМ)да га конвертује у временски формат који је разумљив програму и погодан за даље прорачуне.
Провера лозинке
Претпоставимо да треба да проверимо тачност листе лозинки које су измислили корисници. Према нашим правилима, лозинке могу да садрже само енглеска слова (мала или велика) и бројеве. Размаци, доње црте и други знаци интерпункције нису дозвољени.
Провера се може организовати коришћењем следећег једноставног регуларног израза:
У ствари, са таквим обрасцем захтевамо да између почетка (^) и крај ($) у нашем тексту су били само знакови из скупа дати у угластим заградама. Ако такође треба да проверите дужину лозинке (на пример, најмање 6 карактера), онда квантификатор + може се заменити интервалом „шест или више“ у обрасцу {6,}:
Град са адресе
Рецимо да треба да повучемо град из адресне траке. Редовни програм ће помоћи, издвајајући текст из „г. до следећег зареза:
Хајде да ближе погледамо овај образац.
Ако сте прочитали горњи текст, онда сте већ схватили да неки знакови у регуларним изразима (тачке, звездице, знаци долара итд.) имају посебно значење. Ако треба да тражите саме ове знакове, онда им претходи обрнута коса црта (понекад се назива заштита). Стога, када тражите фрагмент „г. морамо писати у регуларном изразу Господин. ако тражимо плус, онда + итд
Следећа два знака у нашем шаблону, тачка и квантификатор звездица, означавају било који број било ког карактера, односно било које име града.
На крају шаблона је зарез, јер тражимо текст од „г“. на зарез. Али у тексту може бити неколико зареза, зар не? Не само после града, већ и после улице, кућа итд. На ком ће од њих стати наш захтев? За то је знак питања. Без тога, наш регуларни израз би извукао најдужи могући низ:
У смислу регуларних израза, такав образац је „похлепан“. Да бисмо исправили ситуацију, потребан је знак питања – он чини квантификатор после којег стоји „шкрт“ – а наш упит води текст само до прве контра запете после „г.“:
Име датотеке са пуне путање
Друга врло честа ситуација је да се име датотеке издвоји из пуне путање. Једноставан регуларни израз форме ће помоћи овде:
Трик је у томе што се претрага, у ствари, одвија у супротном смеру – од краја ка почетку, јер је на крају нашег шаблона $, и тражимо све пре њега до прве обрнуте косе црте са десне стране. Обрнута коса црта се избегава, као и тачка у претходном примеру.
PS
„Пред крај“ Желим да појасним да је све наведено само мали део свих могућности које пружају регуларни изрази. Постоји много посебних знакова и правила за њихову употребу, а на ову тему су написане читаве књиге (за почетак препоручујем бар ову). На неки начин, писање регуларних израза је готово уметност. Скоро увек, измишљени регуларни израз се може побољшати или допунити, чинећи га елегантнијим или способним да ради са ширим спектром улазних података.
Да бисте анализирали и рашчланили регуларне изразе других људи или отклонили сопствене грешке, постоји неколико згодних онлајн услуга: РегЕк101, РегЕкр и више
Нажалост, нису све карактеристике класичних регуларних израза подржане у ВБА (на пример, обрнута претрага или ПОСИКС класе) и могу да раде са ћирилицом, али мислим да је оно што има довољно за први пут да вам прија.
Ако нисте нови у овој теми, а имате шта да поделите, оставите регуларне изразе корисне када радите у Екцел-у у коментарима испод. Једна памет је добра, али две чизме су пар!
- Замена и чишћење текста са функцијом СУБСТИТУТЕ
- Search and highlighting of Latin characters in text
- Потражите најближи сличан текст (Иванов = Ивонов = Иваноф, итд.)