Mündəricat
Əgər adi ifadələrlə az da olsa tanışsınızsa, onları reklam etməyə ehtiyac yoxdur. Mövzuda kifayət qədər deyilsinizsə, müntəzəm ifadələr (Regular Expressions = RegExp = “regexps” = “regulars”) xüsusi simvol və qaydalardan istifadə edərək mətndə lazımi alt sətirlərin axtarıldığı, çıxarıldığı bir dildir. və ya başqa mətnlə əvəz olunur. Bu, mətnlə işləməyin bütün digər üsullarından üstün olan çox güclü və gözəl bir vasitədir.
Sadə makrolardan istifadə edərək Excel-ə müntəzəm ifadə dəstəyini necə əlavə edə biləcəyinizi artıq ətraflı şəkildə və həyatdan bir dəstə nümunə ilə təsvir etdim - bu məqaləni oxumamısınızsa, davam etməzdən əvvəl oxumağı çox tövsiyə edirəm. Bir çox yeni şeylər kəşf edəcəksiniz, zəmanət verirəm 🙂
Bununla belə, sual açıq qalır – Power Query-də müntəzəm ifadələrdən istifadə etmək imkanı necə əlavə etmək olar? Power Query, əlbəttə ki, tək başına yaxşıdır və mətnlə çox şey edə bilər (kəsmək, yapışdırmaq, təmizləmək və s.), lakin müntəzəm ifadələrin gücü ilə onu keçə bilsəniz, sadəcə bir bomba olardı.
Təəssüf ki, Power Query-də RegExps ilə işləmək üçün daxili funksiyalar yoxdur və rəsmi Microsoft yardımı və texniki dəstəyi bu suala mənfi cavab verir. Bununla belə, bu məhdudiyyətin bir yolu var 🙂
Metodun mahiyyəti
Əsas fikir rüsvay etmək asandır.
Daxili Power Query imkanları siyahısında bir funksiya var Veb səhifə. Rəsmi Microsoft yardım saytında bu funksiyanın təsviri son dərəcə qısadır:
Tərcümə edilərsə, bu belə olardı: "Kompanent strukturlarına bölünmüş HTML sənədinin məzmununu, həmçinin etiketlər silindikdən sonra tam sənədin və onun gövdəsinin təsvirini qaytarır." Açıqcası belə təsvir.
Adətən bu funksiya internetdən məlumat idxal edərkən istifadə olunur və məsələn, nişanda seçdiyimiz zaman avtomatik olaraq əvəz olunur. Tarix Komanda İnternetdən (Məlumat - Vebdən). Biz funksiyaya arqument kimi veb səhifə veririk və o, əvvəllər bütün teqləri təmizləyərək öz məzmununu bizə cədvəllər şəklində qaytarır.
Yardımın demədiyi şey HTML işarələmə dilinə əlavə olaraq funksiyası Veb səhifə JavaScript skriptlərini dəstəkləyir, indi İnternetdəki veb saytlarda hər yerdə var. JavaScript isə öz növbəsində həmişə müntəzəm ifadələrlə işləməyi bacarıb və RegExps üçün daxili funksiyalara malikdir! Beləliklə, Power Query-də müntəzəm ifadələri həyata keçirmək üçün biz Web.Page funksiyalarını Power Query üçün bütün işləri görəcək kiçik JavaScript proqramına arqument kimi təqdim etməliyik.
Təmiz JavaScript-də necə görünür
İnternetdə JavaScript-də müntəzəm ifadələrlə işləmək üçün çoxlu ətraflı dərsliklər var (məsələn, bir, iki).
Qısa və sadələşdirilmiş şəkildə JavaScript kodu belə görünəcək:
Burada:
- var str = 'Kolbasa üçün 123 və 789 hesablarını ödəyin'; - dəyişən yaradın str və ona təhlil edəcəyimiz mənbə mətni təyin edin.
- var model = /d+/gi; – müntəzəm ifadə yaradın və onu dəyişənə qoyun naxış.
İfadə slash (/) ilə başlayır.
Burada ifadənin özü, məsələn, belədir d+ istənilən rəqəm ardıcıllığını ifadə edir.
İfadədən sonra kəsr vasitəsilə əlavə axtarış parametrləri (dəyişdiricilər) var - onlar istənilən qaydada göstərilə bilər:
- g – qlobal axtarış deməkdir, yəni uyğunluq tapdıqdan sonra dayanmamalı, mətnin sonuna qədər axtarışa davam etməlisiniz. Bu dəyişdirici təyin olunmayıbsa, skriptimiz yalnız ilk uyğunluğu qaytaracaq (123)
- i – hərflərdən asılı olmayaraq axtarış
- m – çox sətirli axtarış (mənbə mətn bir neçə sətirə bölündükdə istifadə olunur)
- var nəticə = str.match(pattern).join(';'); – mənbə mətndə axtarış aparın (str) verilmiş müntəzəm ifadə ilə (naxış) və nəticələri dəyişənə qoyun nəticələnəcək, əmrindən istifadə edərək onları nöqtəli vergüllə birləşdirin qoşulmaq
- sənəd.yazmaq(nəticə); – nəticə dəyişəninin məzmununu göstərin
Onu da nəzərə alın ki, JavaScript-də mətn sətirləri (müntəzəm ifadələr istisna olmaqla) Power Query və ya VBA-da olduğu kimi dırnaqlara deyil, apostroflara əlavə olunur.
Nəticədə bu skript bizə mənbə mətnində olan bütün nömrələri verəcəkdir:
123, 789
JavaScript qısa kursu bitdi, hamınıza təşəkkür edirəm. Ümid edirəm ki, məntiqi başa düşəcəksiniz 🙂
Bu konstruksiyanı Power Query-ə köçürmək qalır.
Power Query-də müntəzəm ifadə ilə mətni axtarın və çıxarın
Biz aşağıdakıları edirik:
1. Excel-i açın və tabda yeni boş Power Query yaradın Data – Məlumat əldə et / Sorğu yarat – Digər mənbələrdən – Boş sorğu (Məlumat - Məlumat əldə et / Yeni sorğu - Digər mənbələrdən - Boş sorğu). Əgər sizdə köhnə Excel 2010-2013 və Power Query versiyanız varsa, sizdə quraşdırılmamış, lakin ayrıca əlavə kimi quraşdırılıbsa, onda bütün bunlar tabda olacaq. Güc sorğusuVə yox Tarix.
2. Açılan sorğu redaktorunun boş pəncərəsində, sağ paneldə dərhal gələcək funksiyamızın adını daxil edin (məsələn, fxRegExpExtract)
3. Gəlin nişana keçək Görünüş - Qabaqcıl redaktor (Bax - Qabaqcıl Redaktor), biz boş sorğunun bütün M kodunu silir və super funksiyamızın kodunu ora yapışdırırıq:
Əllərinizə baxın:
Birinci sətirdə funksiyamızın üç mətn arqumenti olacağını deyirik: txt - təhlil edilən orijinal mətn; regex - müntəzəm ifadə nümunəsi, ayırmaq — nəticələri göstərmək üçün ayırıcı simvol.
Sonra funksiyanı çağırırıq Veb səhifə, arqumentində yuxarıda təsvir edilən JavaScript kodunu formalaşdırır. Dəyişən arqumentlərimizi koda yapışdırıb əvəz edirik.
Fraqment:
[Data]{0}[Uşaqlar]{0}[Uşaqlar]{1}[Mətn]{0}
... bizə lazım olan nəticələrlə cədvələ "düşmək" lazımdır. Məsələ ondadır ki, funksiya Veb səhifə nəticədə o, veb səhifənin strukturunu təkrarlayan bir neçə iç-içə cədvəl yaradır. Bu M kodu parçası olmadan funksiyamız bunu çıxaracaq:
… və biz sözü bir neçə dəfə klikləməliyik Masa, ardıcıl olaraq sütunlardakı uşaq iç-içə cədvəllərə "düşmək" Uşaqlar:
Bütün bu sitatların əvəzinə biz dərhal funksiyamızın kodunda hansı cədvəl və sütunun yerləşdiyini göstəririk (Mətn) ehtiyacımız var.
Burada, əslində, bütün sirlər var. Düyməni basmaq qalır finiş pəncərədə qabaqcıl redaktor, kodumuzu daxil etdiyimiz yerə və siz ən dadlısına keçə bilərsiniz – funksiyamızı iş yerində sınayın.
Budur bir neçə toxum nümunəsi.
Nümunə 1. Ödəniş təsvirindən hesab nömrəsi və tarixin alınması
Ödənişlərin təsviri (məqsədi) olan bank çıxarışımız var, burada ödənilmiş hesab-fakturaların nömrələrini və tarixlərini ayrı sütunlara çıxarmaq lazımdır:
Cədvəli standart şəkildə Power Query-ə yükləyirik Data – Cədvəldən/Aralıqdan (Məlumatlar - T.-dənbacarır/Rmələk).
Sonra vasitəsilə funksiyamızla hesablanmış sütun əlavə edirik Sütun əlavə et - Xüsusi funksiyaya zəng edin (Sütun əlavə et - Xüsusi funksiyanı çağır) və onun arqumentlərini daxil edin:
Normal ifadə kimi (arqument regex) istifadə etdiyimiz şablon:
(d{3,5}|d{2}.d{2}.d{4})
… insan dilinə tərcümə edilən məna:
3-dən 5-ə qədər rəqəmlər (hesab nömrələri)
or
“2 bitlik nömrə – nöqtə – 2 bitlik nömrə – nöqtə – 4 bitlik nömrə” formasının fraqmentləri, yəni GG.AA.YYYY formasının tarixləri.
Ayırıcı xarakter kimi (arqument ayırmaq) nöqtəli vergül daxil edin.
Klikdən sonra OK sehrli funksiyamız bütün ilkin məlumatları müntəzəm ifadəmizə uyğun olaraq təhlil edir və bizim üçün hesab-fakturaların tapılan nömrələri və tarixləri ilə bir sütun təşkil edir:
Komandadan istifadə edərək onu nöqtəli vergüllə ayırmaq qalır Əsas səhifə — Ayrılmış sütun — Ayırıcı ilə (Ev - Ayrılmış sütun - Ayırıcı ilə) və istədiyimizi əldə edirik:
Gözəllik!
Misal 2: Mətndən e-poçt ünvanlarını çıxarın
Tutaq ki, ilkin məlumat kimi aşağıdakı cədvəlimiz var:
… orada tapılan e-poçt ünvanlarını çıxarmalı olduğumuz yerdən (aydınlıq üçün onları mətndə qırmızı rənglə vurğuladım).
Əvvəlki nümunədə olduğu kimi cədvəli Power Query-ə standart şəkildə vasitəsilə yükləyirik Data – Cədvəldən/Aralıqdan (Məlumatlar - T.-dənbacarır/Rmələk).
Sonra vasitəsilə funksiyamızla hesablanmış sütun əlavə edirik Sütun əlavə et - Xüsusi funksiyaya zəng edin (Sütun əlavə et - Xüsusi funksiyanı çağır) və onun arqumentlərini daxil edin:
E-poçt ünvanlarını təhlil etmək daha çətin bir işdir və onu həll etmək üçün müxtəlif dərəcəli kabusların bir çox müntəzəm ifadələri var. Sadə variantlardan birini istifadə etdim - ideal deyil, lakin əksər hallarda tam işləyir:
[w|.|-]*@w*.[w|.]*
ayırıcı kimi (ayırmaq) nöqtəli vergül və boşluq daxil edə bilərsiniz.
Basın OK və biz "sıyıq" orijinal mətnindən çıxarılmış e-poçt ünvanları olan bir sütun alırıq:
Sehrli!
PS
Necə deyərlər: “Belə yaxşı şey yoxdur ki, ondan da yaxşısı olmasın”. Power Query özü-özlüyündə sərindir və müntəzəm ifadələrlə birləşdirildikdə bizə hər hansı mətn məlumatının emalında tamamilə qeyri-real güc və çeviklik verir. Ümid edirəm ki, Microsoft nə vaxtsa Power Query və Power BI yeniləmələrində RegExp dəstəyini əlavə edəcək və yuxarıda göstərilən qavalla rəqslərin hamısı keçmişdə qalacaq. Yaxşı, hələlik, bəli.
Onu da əlavə etmək istəyirəm ki, https://regexr.com/ saytında - düz onlayn redaktorda müntəzəm ifadələrlə oynamaq rahatdır. Orada bölmədə İcma Nümunələri Bütün hallar üçün çox sayda hazır müntəzəm mövsüm var. Təcrübə – adi ifadələrin bütün gücü indi Power Query-də xidmətinizdədir!
- Normal ifadələr (RegExp) nədir və onlardan Excel-də necə istifadə olunur
- Power Query-də qeyri-səlis mətn axtarışı
- Power Query istifadə edərək müxtəlif fayllardan cədvəllərin yığılması