Excelде кадимки туюнтмалар (RegExp) менен текстти талдоо

Excelде текст менен иштөөдө эң көп убакытты талап кылган жана кыжырдантуучу иштердин бири талдоо – алфавиттик-сандык “катканы” компоненттерге талдоо жана андан бизге керектүү фрагменттерди алуу. Мисалы:

• даректен почта индексин чыгарып алуу (почта индекси ар дайым башында турса жакшы, бирок андай болбосочу?)
• банктык көчүрмөдөгү төлөмдүн сүрөттөлүшүнөн эсеп-фактуранын номерин жана датасын табуу
• контрагенттердин тизмесиндеги компаниялардын түрдүү сыпаттамаларынан ИНН алуу
• сыпаттамада машинанын номерин же макаланын номерин издөө ж.б.
  

Адатта, мындай учурларда текстти кол менен терип жарым саат өткөндөн кийин, кандайдыр бир жол менен бул процессти автоматташтыруу (айрыкча, маалыматтар көп болсо) ойлорго келе баштайт. Бир нече чечимдер бар жана ар кандай деңгээлдеги татаалдык-натыйжалуулугу менен:

• колдонуу камтылган Excel текст функциялары текстти издөө үчүн: LEVSIMV (СОЛ), УКУГУ (ОҢ), PSTR (орто), STsEPIT (КОНКАТЕНАТ) жана анын аналогдору, КОМБИНАТ (JOINTEXT), пост (ТАК) ж.б. Эгерде текстте так логика болсо, бул ыкма жакшы (мисалы, индекс дайыма даректин башында турат). Болбосо, формулалар бир топ татаалдашып, кээде массив формулаларына да келет, бул чоң таблицаларда абдан жайлатат.
• колдонуу менен текст окшоштук оператору сыяктуу ыңгайлаштырылган макро функцияга оролгон Visual Basicтен. Бул сизге ийкемдүү издөөнү (*, #,?, ж.б.) белгилерди колдонуу менен ишке ашырууга мүмкүндүк берет.

Жогоруда айтылгандардан тышкары, кесипкөй программисттердин, веб-иштеп чыгуучулардын жана башка техникалардын тар чөйрөсүндө абдан белгилүү болгон дагы бир ыкма бар - бул туруктуу сөз айкаштары (Кадимки туюнтмалар = RegExp = "regexps" = "кадимки"). Жөнөкөй сөз менен айтканда, RegExp - бул текстте керектүү субсаптарды издөө, аларды чыгаруу же башка текст менен алмаштыруу үчүн атайын символдор жана эрежелер колдонулган тил. Регулярдуу сөз айкаштары - бул текст менен иштөөнүн бардык башка ыкмаларын чоңдук тартиби боюнча ашкан абдан күчтүү жана кооз курал. Көптөгөн программалоо тилдери (C#, PHP, Perl, JavaScript…) жана текст редакторлору (Word, Notepad++…) кадимки туюнтмаларды колдойт.

Microsoft Excel тилекке каршы кутудан тышкары RegExp колдоосуна ээ эмес, бирок аны VBA менен оңой эле оңдоого болот. Өтмөктөн Visual Basic редакторун ачыңыз иштеп (Иштеп чыгуучу) же баскычтоптун жарлыгы Alt+F11. Андан кийин меню аркылуу жаңы модулду киргизиңиз Кыстаруу – модуль жана ал жерге төмөнкү макро функциянын текстин көчүрүңүз:

Коомдук Функция RegExpExtract(Текст сап катары, үлгү сап катары, кошумча нерсе бүтүн сан катары = 1) Ката боюнча сап катары GoTo ErrHandl коюу regex = CreateObject("VBScript.RegExp") regex.Pattern = Үлгү regex.Global = Чын болсо, regex.Test (Текст) Андан кийин дал келүүлөрдү коюңуз = regex.Execute(Текст) RegExpExtract = matches.Item(Пермент - 1) Функциядан чыгуу, эгерде ErrHandl: RegExpExtract = CVErr(xlErrValue) Аяктоо функциясы  

Эми биз Visual Basic редакторун жаап, жаңы функциябызды сынап көрүү үчүн Excelге кайтып барсак болот. Анын синтаксиси төмөнкүдөй:

=RegExpExtract( Txt ; Үлгү ; Элемент )

кайда

• TXT – биз текшерип жаткан тексти бар уяча жана андан бизге керектүү субсапты чыгаргыбыз келет
• узор – субсап издөө үчүн маска (үлгү).
• нерсе – чыгарыла турган субсаптын катар номери, эгерде алардын бир нечеси болсо (эгерде көрсөтүлбөсө, анда биринчи көрүнүш көрсөтүлөт)

Бул жерде эң кызыктуусу, албетте, Pattern – RegExpтин “тилиндеги” атайын символдордон турган шаблон саптары, анда биз так эмнени жана кайдан тапкыбыз келет. Бул жерде сизди баштоо үчүн эң негизгилери:

Эгерде биз белгилердин белгилүү бир санын издеп жаткан болсок, мисалы, алты орундуу почта индекси же бардык үч тамгалуу продукт коддору, анда биз жардамга келебиз. кванторлор or кванторлор изделе турган символдордун санын көрсөткөн атайын туюнтмалар. Анын алдында келген символго сандык көрсөткүчтөр колдонулат:

Эми эң кызыктуу бөлүгүнө – түзүлгөн функциянын колдонулушунун анализине жана турмуштан практикалык мисалдар боюнча калыптар жөнүндө эмнелерди билдик.

Тексттен сандарды алуу

Баштоо үчүн, жөнөкөй ишти талдап көрөлү – сиз алфавиттик-сандык боткодон биринчи санды, мисалы, прейскуранттан үзгүлтүксүз энергия булактарынын күчүн чыгарып алышыңыз керек:

Регулярдуу сөз айкашынын логикасы жөнөкөй: d ар кандай цифраны жана сандык көрсөткүчтү билдирет + алардын саны бир же бир нече болушу керек дейт. Функциянын алдындагы кош минус алынган символдорду текст катары сандан толук санга айландыруу үчүн керек.

Почталык

Бир караганда, бул жерде баары жөнөкөй - биз катары менен так алты цифраны издеп жатабыз. Биз өзгөчө белгини колдонобуз d сан жана сан үчүн 6 {} белгилердин саны үчүн:

Бирок, сапта индекстин сол жагында катардагы дагы бир чоң сандар топтому (телефон номери, ИНН, банк эсеби ж. анын сандары, башкача айтканда, туура иштебейт:

Мунун алдын алуу үчүн, биз кадимки туюнтмабыздын четине модификаторду кошушубуз керек b сөздүн аягын билдирет. Бул Excelге бизге керектүү фрагмент (индекс) башка фрагменттин (телефон номуру) бөлүгү эмес, өзүнчө сөз болушу керек экенин ачык-айкын көрсөтөт:

Phone

Тексттен телефон номерин табуу көйгөйү - сандарды жазуу үчүн көптөгөн варианттар бар - дефис менен жана сызыксыз, боштуктар аркылуу, кашаанын ичинде аймак коду менен же болбосо ж.б. адегенде бир нече уяча функцияларды колдонуп, баштапкы тексттен бардык бул белгилерди тазалаңыз АЛМАШТЫРУУ (АЛМАШТЫРУУ)Ошентип, ал бир бүтүнгө, анан примитивдүү регулярдыкка жабышат г{11} катарынан 11 санды чыгарып:

ИНН

Бул жерде бир аз татаалыраак, анткени ИНН (Биздин өлкөдө) 10 орундуу (юридикалык жактар ​​үчүн) же 12 орундуу (жеке адамдар үчүн) болушу мүмкүн. Айрыкча кемчилик таппасаңыз, анда кадимкидей канааттануу толук мүмкүн г{10,12}, бирок, так айтканда, ал 10дон 12 белгиге чейинки бардык сандарды чыгарып салат, б.а. жана 11 цифра туура эмес киргизилген. Логикалык ЖЕ оператору менен байланышкан эки калыпты колдонуу туурараак болмок | (тик тилке):

Суранычта биз адегенде 12 биттик сандарды, андан кийин гана 10 биттик сандарды издейбиз. Эгерде биз кадимки сөз айкашыбызды башка жол менен жазсак, анда ал бардыгы үчүн, атүгүл узун 12 биттик ТИНдерди, биринчи 10 символду гана чыгарып салат. Башкача айтканда, биринчи шарт ишке киргенден кийин, андан ары текшерүү жүргүзүлбөйт:

Бул оператордун ортосундагы негизги айырма | стандарттык Excel логикалык функциясынан OR (ЖЕ), бул жерде аргументтерди кайра иретке келтирүү натыйжаны өзгөртпөйт.

Продукт SKUs

Көптөгөн компанияларда товарларга жана кызматтарга уникалдуу идентификаторлор ыйгарылган – макалалар, SAP коддору, SKU ж.б. Эгерде алардын белгилеринде логика бар болсо, анда аларды кадимки туюнтмалардын жардамы менен каалаган тексттен оңой эле чыгарып алса болот. Мисалы, биздин макалалар ар дайым англис тилиндеги үч баш тамгадан, дефистен жана андан кийинки үч орундуу сандан турат деп билсек, анда:

Шаблондун логикасы жөнөкөй. [AZ] – латын алфавитинин ар кандай баш тамгаларын билдирет. Кийинки сандык көрсөткүч 3 {} Биз үчүн так ушундай үч кат бар экени маанилүү дейт. Дефистен кийин биз үч санды күтүп жатабыз, ошондуктан аягында кошобуз г{3}

Накталай суммалар

Мурунку абзацка окшоп, сиз товарлардын сыпаттамасынан бааларды (чыгашалар, КНС...) чыгара аласыз. Эгерде акчалай суммалар, мисалы, дефис менен көрсөтүлсө, анда:

узор d сандык көрсөткүч менен + дефиске чейин каалаган санды издейт жана г{2} кийин пенни (эки цифра) издейт.

Эгер сиз бааларды эмес, КНСти чыгарып алышыңыз керек болсо, анда сиз биздин RegExpExtract функциябыздын үчүнчү кошумча аргументин колдонсоңуз болот, ал чыгарыла турган элементтин иреттик номерин аныктайт. Жана, албетте, сиз функцияны алмаштыра аласыз АЛМАШТЫРУУ (АЛМАШТЫРУУ) натыйжаларда, стандарттык ондук бөлгүчкө дефис коюп, Excel табылган КНСти кадимки сан катары чечмелеши үчүн, башына кош минус кошуңуз:

Автоунаанын номерлери

Эгерде сиз атайын унааларды, чиркегичтерди жана башка мотоциклдерди албасаңыз, анда стандарттуу унаа номери “тамга – үч сан – эки тамга – аймактын коду” принцибине ылайык талданат. Мындан тышкары, аймактын коду 2 же 3 орундуу болушу мүмкүн жана тамга катары сырткы көрүнүшү латын алфавитине окшош болгондор гана колдонулат. Ошентип, төмөнкү туруктуу сөз айкашы тексттен сандарды чыгарууга жардам берет:

убакыт

Убакытты HH:MM форматында чыгаруу үчүн төмөнкү кадимки туюнтма ылайыктуу:

Жоон ичегинин фрагментинен кийин [0-5]к, аны аныктоо оңой болгондуктан, 00-59 диапазонунда каалаган санды орнотот. кашаанын ичиндеги кош чекиттин алдында логикалык ЖЕ (түтүк) менен бөлүнгөн эки үлгү иштейт:

• [0-1]к – 00-19 диапазонундагы каалаган сан
• 2[0-3] – 20-23 диапазонундагы каалаган сан

Алынган натыйжага сиз кошумча стандарттуу Excel функциясын колдоно аласыз TIME (КОМАНДА)аны программага түшүнүктүү жана андан аркы эсептөөлөр үчүн ылайыктуу убакыт форматына айландыруу.

Сырсөздү текшерүү

Колдонуучулар ойлоп тапкан сырсөздөрдүн тууралыгын текшеришибиз керек дейли. Биздин эрежелерге ылайык, сырсөздөр англис тамгаларын (кичи же чоң тамга) жана сандарды гана камтышы мүмкүн. Боштуктарга, астынкы сызыктарга жана башка тыныш белгилерине жол берилбейт.

Текшерүүнү төмөнкү жөнөкөй регулярдуу туюнтманы колдонуу менен уюштурууга болот:

Чынында, мындай үлгү менен биз башталышынын ортосунда (^) жана бүттү ($) биздин текстте төрт бурчтуу кашаанын ичинде берилген топтомдун символдору гана болгон. Эгер сиз ошондой эле паролдун узундугун текшерүү керек болсо (мисалы, кеминде 6 белги), анда кванттоочу + түрүндө "алты же андан көп" интервал менен алмаштырылышы мүмкүн {6,}:

Шаар адресинен

Шаарды дарек тилкесинен тартып алышыбыз керек дейли. Кадимки программа "g" дан текстти чыгарып, жардам берет. кийинки үтүргө:

Келгиле, бул үлгүнү жакындан карап көрөлү.

Эгерде сиз жогорудагы текстти окуган болсоңуз, анда туруктуу сөз айкаштарындагы кээ бир белгилердин (чекит, жылдызча, доллар белгиси ж.б.) өзгөчө мааниге ээ экенин түшүндүңүз. Эгер сиз бул каармандардын өздөрүн издешиңиз керек болсо, анда алардын алдында тескери сызык (кээде деп аталат калкалоо). Ошондуктан, "g" фрагментин издөөдө. биз кадимки туюнтма менен жазышыбыз керек Mr. плюс издеп жаткан болсок, анда + жана башкалар

Калыбыбыздын кийинки эки символу, чекит жана квантор жылдызчасы каалаган символдордун каалаган санын, б.а. каалаган шаардын атын билдирет.

Үлгүнүн аягында үтүр бар, анткени биз "g" тамгасынан текст издеп жатабыз. үтүргө. Бирок текстте бир нече үтүр болушу мүмкүн, туурабы? Шаардан кийин эле эмес, көчө, үйлөр ж.б.у.с.. Алардын кайсынысына биздин өтүнүчүбүз токтойт? Суроо белгиси мына ушуну үчүн. Ансыз, биздин кадимки сөз айкашы мүмкүн болушунча узун сапты чыгарат:

Туруктуу сөз айкаштары жагынан мындай үлгү “ач көздүк”. Кырдаалды оңдоо үчүн суроо белгиси керек – ал квантордук кылат, андан кийин ал “сараң” болот – жана биздин суроо текстти “g.”ден кийинки биринчи каршы үтүргө чейин гана алат:

Толук жолдогу файлдын аталышы

Дагы бир кеңири таралган жагдай - файлдын атын толук жолдон алуу. Бул жерде форманын жөнөкөй кадимки туюнтмасы жардам берет:

Бул жерде куулук, издөө, чындыгында, карама-каршы багытта ишке ашат - башынан аягына чейин, анткени биздин шаблондун аягында $, жана биз анын алдында оңдон биринчи арткы сызыкчага чейин баарын издеп жатабыз. Мурунку мисалдагы чекит сыяктуу тескери сызык алынып салынат.

PS

«Акырына карай» Мен жогоруда айтылгандардын бардыгы туруктуу сөз айкаштары берген бардык мүмкүнчүлүктөрдүн кичинекей бөлүгү экенин тактагым келет. Көптөгөн өзгөчө каармандар жана аларды колдонуу эрежелери бар жана бул тема боюнча бүт китептер жазылган (мен башталышы үчүн жок дегенде ушуну сунуштайм). Башкача айтканда, туруктуу сөз айкаштарын жазуу дээрлик искусство. Дээрлик ар дайым ойлоп табылган регулярдуу сөз айкашы жакшыртылган же толукталышы мүмкүн, бул аны көрктүү кылып же киргизүү маалыматтарынын кеңири диапазону менен иштөөгө жөндөмдүү кылат.

Башка адамдардын кадимки туюнтмаларын талдоо жана талдоо же өзүңүздүн мүчүлүштүктөрдү оңдоо үчүн бир нече ыңгайлуу онлайн кызматтар бар: RegEx101, RegExr жана

Тилекке каршы, VBA-да классикалык туруктуу сөз айкаштарынын бардык мүмкүнчүлүктөрү колдоого алынбайт (мисалы, тескери издөө же POSIX класстары) жана кириллица менен иштей алат, бирок менимче, бул жерде биринчи жолу сизге жагуу үчүн жетиштүү нерсе бар.

Эгер сиз темага жаңы эмес болсоңуз жана бөлүшө турган нерсеңиз болсо, төмөндөгү комментарийлерде Excelде иштөөдө пайдалуу кадимки туюнтмаларды калтырыңыз. Бир акыл жакшы, бирок эки өтүк бир түгөй!

• SUBSTITUTE функциясы менен текстти алмаштыруу жана тазалоо
• Текстте латын тамгаларын издөө жана бөлүп көрсөтүү
• Жакынкы окшош текстти издөө (Иванов = Ивонов = Иваноф ж.б.)