Excel-də müntəzəm ifadələrlə (RegExp) mətni təhlil edin

Excel-də mətnlə işləyərkən ən çox vaxt aparan və sinir bozucu işlərdən biri də budur ayrıştırma – alfasayısal “sıyıq”ı komponentlərə ayırmaq və ondan bizə lazım olan fraqmentləri çıxarmaq. Misal üçün:

• poçt kodunu ünvandan çıxarmaq (poçt kodu həmişə başlanğıcda olsa yaxşıdır, amma olmasa nə olar?)
• bank çıxarışındakı ödənişin təsvirindən hesab-fakturanın nömrəsini və tarixini tapmaq
• kontragentlər siyahısındakı şirkətlərin rəngarəng təsvirlərindən VÖEN-in çıxarılması
• təsvirdə avtomobil nömrəsini və ya məqalə nömrəsini axtarın və s.
  

Adətən belə hallarda mətni əl ilə yarım saat sönük seçdikdən sonra bu prosesi avtomatlaşdırmaq üçün (xüsusilə də çoxlu məlumat varsa) fikirlər ağlına gəlməyə başlayır. Müxtəlif mürəkkəblik dərəcələri ilə bir neçə həll yolu var:

• istifadə daxili Excel mətn funksiyaları mətni axtarmaq-kəsmək-yapışdırmaq üçün: LEVSIMV (SOL), SAĞ (SAĞ), PSTR (orta), STsEPIT (BİRLƏŞDİRİR) və onun analoqları, COMBINE (JOINTEXT), EXACT (DƏqiq) və s. Mətndə aydın məntiq varsa, bu üsul yaxşıdır (məsələn, indeks həmişə ünvanın əvvəlindədir). Əks təqdirdə, düsturlar daha mürəkkəbləşir və bəzən hətta massiv düsturlarına da gəlir ki, bu da böyük cədvəllərdə çox yavaşlayır.
• Istifadə mətn oxşarlıq operatoru kimi xüsusi makro funksiyasına bükülmüş Visual Basic-dən. Bu, joker simvollardan (*, #,? və s.) istifadə edərək daha çevik axtarış həyata keçirməyə imkan verir. Təəssüf ki, bu alət mətndən istədiyiniz alt sətri çıxara bilmir – yalnız onun içində olub-olmadığını yoxlayın.

Yuxarıda göstərilənlərə əlavə olaraq, peşəkar proqramçıların, veb tərtibatçıların və digər texniki işçilərin dar dairələrində çox yaxşı tanınan başqa bir yanaşma var - bu müntəzəm ifadələr (Daimi İfadələr = RegExp = “regexps” = “müntəzəmlər”). Sadəcə qoymaq, RegExp mətndə lazımi alt sətirləri axtarmaq, onları çıxarmaq və ya başqa mətnlə əvəz etmək üçün xüsusi simvol və qaydaların istifadə edildiyi bir dildir.. Daimi ifadələr çox güclü və gözəl bir vasitədir və mətnlə işləməyin bütün digər üsullarını böyüklük sırasına görə üstələyir. Bir çox proqramlaşdırma dilləri (C#, PHP, Perl, JavaScript…) və mətn redaktorları (Word, Notepad++…) normal ifadələri dəstəkləyir.

Təəssüf ki, Microsoft Excel-də RegExp dəstəyi yoxdur, lakin bu, VBA ilə asanlıqla düzəldilə bilər. Nişandan Visual Basic Redaktorunu açın geliştirici (İnkişaf etdirici) və ya klaviatura qısa yolu Dayandırmaq+F11. Sonra menyu vasitəsilə yeni modulu daxil edin Daxil et - Modul və aşağıdakı makro funksiyasının mətnini ora köçürün:

İctimai Funksiya RegExpExtract(Mətn Sətir kimi, Nümunə Sətir kimi, Könüllü Element Tam Ədəd = 1) Sətir kimi Xətaya Get ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True Əgər regex.Test (Mətn) Sonra uyğunluqları təyin edin = regex.Execute(Mətn) RegExpExtract = matches.Item(Item - 1) Çıxış Funksiyasının sonu ErrHandl olduqda: RegExpExtract = CVErr(xlErrValue) Son Funksiya  

İndi Visual Basic Redaktorunu bağlaya və yeni xüsusiyyətimizi sınamaq üçün Excel-ə qayıda bilərik. Onun sintaksisi belədir:

=RegExpExtract( Txt ; Pattern ; Madde )

hara

• txt – yoxladığımız və bizə lazım olan alt sətri çıxarmaq istədiyimiz mətni olan xana
• naxış – alt sətir axtarışı üçün maska ​​(naxış).
• maddə – çıxarılacaq alt sətirin sıra nömrəsi, əgər onlardan bir neçəsi varsa (əgər göstərilməyibsə, onda ilk hadisə göstərilir)

Burada ən maraqlı şey, əlbəttə ki, Pattern - RegExp-in "dilində" xüsusi simvolların şablon sətiridir və bu, dəqiq nəyi və harada tapmaq istədiyimizi göstərir. Başlamaq üçün ən əsasları bunlardır:

Müəyyən sayda simvol axtarırıqsa, məsələn, altı rəqəmli poçt kodu və ya bütün üç hərfli məhsul kodları, o zaman köməyə gəlirik. ölçücülər or ölçücülər axtarılacaq simvolların sayını təyin edən xüsusi ifadələrdir. Kəmiyyət ifadələri özündən əvvəl gələn simvola tətbiq edilir:

İndi keçək ən maraqlı hissəyə – yaradılmış funksiyanın tətbiqinin təhlilinə və həyatdan praktik nümunələr üzərində nümunələr haqqında öyrəndiklərimizə.

Mətndən rəqəmlərin çıxarılması

Başlamaq üçün sadə bir işi təhlil edək - alfasayısal sıyıqdan ilk nömrəni çıxarmaq lazımdır, məsələn, qiymət siyahısından fasiləsiz enerji təchizatı gücünü:

Normal ifadənin arxasında duran məntiq sadədir: d istənilən rəqəmi və kəmiyyət göstəricisini bildirir + deyir ki, onların sayı bir və ya bir neçə olmalıdır. Çıxarılan simvolları mətn kimi nömrədən tam ədədə çevirmək üçün funksiyanın qarşısındakı ikiqat minus lazımdır.

Poçt Kodu

İlk baxışdan burada hər şey sadədir – biz ardıcıl altı rəqəm axtarırıq. Xüsusi bir xarakterdən istifadə edirik d rəqəm və kəmiyyət üçün 6 {} simvol sayı üçün:

Bununla belə, sətirdəki indeksin solunda, ard-arda başqa bir böyük nömrələr dəsti (telefon nömrəsi, VÖEN, bank hesabı və s.) olduqda, adi mövsümümüz ilk 6-nı çıxaracaq bir vəziyyət mümkündür. ondan rəqəmlər, yəni düzgün işləməyəcək:

Bunun baş verməməsi üçün normal ifadəmizin kənarlarına dəyişdirici əlavə etməliyik b sözün sonunu bildirir. Bu, Excel-ə aydınlaşdıracaq ki, bizə lazım olan fraqment (indeks) başqa bir fraqmentin (telefon nömrəsi) bir hissəsi deyil, ayrıca söz olmalıdır:

telefon

Mətndə telefon nömrəsinin tapılması ilə bağlı problem ondadır ki, nömrələrin yazılması üçün çoxlu variant var – tire ilə və tiresiz, boşluqlar vasitəsilə, mötərizədə rayon kodu ilə və ya olmadan və s. Buna görə də, məncə, daha asandır əvvəlcə bir neçə iç-içə funksiyadan istifadə edərək bütün bu simvolları mənbə mətndən təmizləyin Substitute (ƏVƏZİNƏ)belə ki, o, bir bütövlükdə bir-birinə yapışsın, sonra isə primitiv nizamnamə ilə d{11} ardıcıl olaraq 11 rəqəmi çıxarın:

ITN

Burada bir az daha mürəkkəbdir, çünki VÖEN (Ölkəmizdə) 10 rəqəmli (hüquqi şəxslər üçün) və ya 12 rəqəmli (fiziki şəxslər üçün) ola bilər. Xüsusilə qüsur tapmırsınızsa, o zaman müntəzəmdən razı qalmaq olduqca mümkündür d{10,12}, lakin, ciddi desək, 10-dan 12 simvola qədər bütün nömrələri çıxaracaq, yəni və səhvən 11 rəqəmi daxil etdi. Məntiqi OR operatoru ilə birləşdirilən iki nümunədən istifadə etmək daha düzgün olardı | (şaquli bar):

Nəzərə alın ki, sorğuda biz əvvəlcə 12 bitlik nömrələri, sonra isə 10 bitlik nömrələri axtarırıq. Normal ifadəmizi tərsinə yazsaq, o, hər kəs üçün, hətta uzun 12 bitlik VÖEN-lər, yalnız ilk 10 simvolu çıxaracaq. Yəni, ilk şərt işə salındıqdan sonra əlavə yoxlama artıq həyata keçirilmir:

Bu operator arasındakı əsas fərqdir | standart excel məntiq funksiyasından OR (OR), burada arqumentlərin yenidən təşkili nəticəni dəyişmir.

Məhsul SKU

Bir çox şirkətlərdə mal və xidmətlərə unikal identifikatorlar təyin olunur – məqalələr, SAP kodları, SKU-lar və s. Əgər onların qeydində məntiq varsa, o zaman onları adi ifadələrdən istifadə etməklə asanlıqla istənilən mətndən çıxarmaq olar. Məsələn, məqalələrimizin həmişə üç böyük ingilis hərfindən, tire və sonrakı üç rəqəmli rəqəmdən ibarət olduğunu bilsək, onda:

Şablonun arxasındakı məntiq sadədir. [AZ] – Latın əlifbasının istənilən böyük hərfləri deməkdir. Növbəti kəmiyyət göstəricisi 3 {} deyir ki, bizim üçün belə hərflərin düz üç olması vacibdir. Defisdən sonra üç rəqəm gözləyirik, ona görə də sonuna əlavə edirik d{3}

Nağd pul məbləğləri

Əvvəlki paraqrafa bənzər şəkildə, siz də malların təsvirindən qiymətləri (xərclər, ƏDV...) çıxara bilərsiniz. Əgər pul məbləğləri, məsələn, defislə göstərilibsə, onda:

model d kəmiyyət göstəricisi ilə + tireyə qədər istənilən rəqəmi axtarır və d{2} sonra qəpik (iki rəqəm) axtaracaq.

Əgər qiymətləri deyil, ƏDV-ni çıxarmaq lazımdırsa, onda siz çıxarılacaq elementin sıra nömrəsini təyin edən RegExpExtract funksiyamızın üçüncü əlavə arqumentindən istifadə edə bilərsiniz. Və, əlbəttə ki, funksiyanı əvəz edə bilərsiniz Substitute (ƏVƏZİNƏ) nəticələrdə standart onluq ayırıcıya defis qoyun və əvvəlinə ikiqat mənfi əlavə edin ki, Excel tapılan ƏDV-ni normal rəqəm kimi şərh etsin:

Avtomobil nömrələri

Xüsusi nəqliyyat vasitələri, qoşqular və digər motosikletlər götürməsəniz, standart avtomobil nömrəsi "hərf - üç rəqəm - iki hərf - bölgə kodu" prinsipinə uyğun olaraq təhlil edilir. Üstəlik, bölgə kodu 2 və ya 3 rəqəmli ola bilər və yalnız latın əlifbası ilə oxşar olanlar hərf kimi istifadə olunur. Beləliklə, aşağıdakı müntəzəm ifadə mətndən rəqəmləri çıxarmağa kömək edəcəkdir:

vaxt

HH:MM formatında vaxtı çıxarmaq üçün aşağıdakı müntəzəm ifadə uyğun gəlir:

Kolon parçasından sonra [0-5]d, anlamaq asan olduğu üçün 00-59 diapazonunda istənilən ədədi təyin edir. Mötərizədə iki nöqtədən əvvəl məntiqi OR (boru) ilə ayrılmış iki nümunə işləyir:

• [0-1]d – 00-19 diapazonunda istənilən nömrə
• 2[0-3] – 20-23 diapazonunda istənilən nömrə

Əldə edilən nəticəyə əlavə olaraq standart Excel funksiyasını tətbiq edə bilərsiniz TIME (KOMANDA)onu proqram üçün başa düşülən və sonrakı hesablamalar üçün uyğun vaxt formatına çevirmək.

Şifrə yoxlanışı

Tutaq ki, istifadəçilər tərəfindən icad edilən parolların düzgünlüyünü yoxlamaq lazımdır. Qaydalarımıza görə, parollarda yalnız ingilis hərfləri (kiçik və ya böyük hərf) və rəqəmlər ola bilər. Boşluq, alt xətt və digər durğu işarələrinə icazə verilmir.

Yoxlama aşağıdakı sadə müntəzəm ifadədən istifadə etməklə təşkil edilə bilər:

Əslində, belə bir nümunə ilə biz başlanğıc arasında (^) və bitir ($) mətnimizdə kvadrat mötərizədə verilmiş çoxluqdan yalnız simvollar var idi. Əgər parolun uzunluğunu da yoxlamaq lazımdırsa (məsələn, ən azı 6 simvol), onda kəmiyyət göstəricisi + şəklində “altı və ya daha çox” intervalı ilə əvəz edilə bilər {6,}:

Ünvandan şəhər

Deyək ki, şəhəri ünvan çubuğundan çəkməliyik. Adi proqram mətni “g” dən çıxararaq kömək edəcək. növbəti vergül üçün:

Bu nümunəyə daha yaxından nəzər salaq.

Əgər yuxarıdakı mətni oxumusunuzsa, onda siz artıq başa düşmüsünüz ki, müntəzəm ifadələrdəki bəzi simvollar (nöqtələr, ulduzlar, dollar işarələri və s.) xüsusi məna daşıyır. Əgər bu simvolların özlərini axtarmaq lazımdırsa, o zaman onlardan əvvəl əks kəsik işarəsi (bəzən deyilir Qoruyan). Buna görə də, “g” fraqmentini axtararkən. müntəzəm ifadə ilə yazmalıyıq Cənab. bir artı axtarırıqsa, o zaman + s.

Şablonumuzdakı növbəti iki simvol, nöqtə və kəmiyyət ulduzu istənilən simvolların istənilən sayını, yəni istənilən şəhər adını ifadə edir.

Şablonun sonunda vergül var, çünki biz “g” hərfindən mətn axtarırıq. vergül üçün. Amma mətndə bir neçə vergül ola bilər, elə deyilmi? Təkcə şəhərdən sonra yox, küçədən, evlərdən və s-dən sonra... Onların hansının üstündə bizim xahişimiz dayanacaq? Sual işarəsi bunun üçündür. Bu olmasaydı, bizim müntəzəm ifadəmiz mümkün olan ən uzun sətri çıxarardı:

Müntəzəm ifadələr baxımından belə bir nümunə “xəsislik”dir. Vəziyyəti düzəltmək üçün sual işarəsi lazımdır - o, kəmiyyət göstəricisini "xəsis" edir - və sorğumuz mətni yalnız "g." dən sonra ilk əks vergülə qədər götürür:

Tam yoldan fayl adı

Başqa bir çox yayılmış vəziyyət fayl adını tam yoldan çıxarmaqdır. Formanın sadə müntəzəm ifadəsi burada kömək edəcəkdir:

Buradakı hiylə ondan ibarətdir ki, axtarış, əslində, əks istiqamətdə baş verir - axırdan əvvələ, çünki şablonumuzun sonunda $, və biz ondan əvvəl sağdan ilk tərs kəsişə qədər hər şeyi axtarırıq. Əvvəlki nümunədəki nöqtə kimi tərs kəsişmə qaçırılır.

PS

“Sona doğru” Mən aydınlaşdırmaq istəyirəm ki, yuxarıda göstərilənlərin hamısı müntəzəm ifadələrin təqdim etdiyi bütün imkanların kiçik bir hissəsidir. Bir çox xüsusi simvol və onlardan istifadə qaydaları var və bu mövzuda bütöv kitablar yazılmışdır (ən azı bunu başlanğıc üçün tövsiyə edirəm). Bir növ, nizamlı ifadələr yazmaq az qala bir sənətdir. Demək olar ki, həmişə icad edilmiş müntəzəm ifadə təkmilləşdirilə və ya əlavə oluna bilər ki, bu da onu daha zərif və ya daha geniş diapazonlu daxiletmə məlumatları ilə işləməyi bacarır.

Başqalarının adi ifadələrini təhlil etmək və təhlil etmək və ya öz ifadələrinizdən çıxmaq üçün bir neçə rahat onlayn xidmət var: RegEx101, RegExr və daha çox

Təəssüf ki, klassik normal ifadələrin bütün xüsusiyyətləri VBA-da dəstəklənmir (məsələn, əks axtarış və ya POSIX sinifləri) və kiril əlifbası ilə işləyə bilər, lakin məncə, orada olanlar sizi sevindirmək üçün ilk dəfə kifayətdir.

Mövzu ilə tanış deyilsinizsə və paylaşacağınız bir şey varsa, aşağıdakı şərhlərdə Excel-də işləyərkən faydalı olan müntəzəm ifadələr buraxın. Bir ağıl yaxşıdır, amma iki çəkmə bir cütdür!

• SUBSTITUTE funksiyası ilə mətnin dəyişdirilməsi və təmizlənməsi
• Mətndə Latın hərflərinin axtarışı və vurğulanması
• Ən yaxın oxşar mətni axtarın (İvanov = İvonov = İvanof və s.)