Excel'de normal ifadelerle (RegExp) metni ayrıştırma

Excel'de metinle çalışırken en çok zaman alan ve sinir bozucu görevlerden biri, ayrıştırma – alfasayısal “lapayı” bileşenlere ayrıştırmak ve ihtiyacımız olan parçaları ondan çıkarmak. Örneğin:

• adresten posta kodunun çıkarılması (posta kodunun her zaman başında olması iyidir, ama ya değilse?)
• banka ekstresindeki ödeme açıklamasından fatura numarasını ve tarihini bulma
• karşı taraflar listesindeki şirketlerin rengarenk açıklamalarından TIN'in çıkarılması
• Açıklamada bir araba numarası veya ürün numarası arayın, vb.
  

Genellikle bu gibi durumlarda, metinde manuel olarak yarım saatlik sıkıcı bir seçimden sonra, bu işlemi otomatikleştirmek için (özellikle çok fazla veri varsa) düşünceler akla gelmeye başlar. Çeşitli çözümler ve değişen derecelerde karmaşıklık-verimlilik vardır:

• kullanım yerleşik Excel metin işlevleri metni kesip yapıştırarak aramak için: LEVSİMV (AYRILDI), SAĞ (SAĞ), PSTR (orta), STsEPIT (BİRLEŞTİR) ve analogları, KOMBİNE (ORTAK METİN), TAM (BİRE BİR AYNI) vb. Metinde açık bir mantık varsa bu yöntem iyidir (örneğin, dizin her zaman adresin başındadır). Aksi takdirde, formüller çok daha karmaşık hale gelir ve bazen büyük tablolarda büyük ölçüde yavaşlayan dizi formüllerine bile gelir.
• kullanma metin benzerliği operatörü gibi Visual Basic'ten özel bir makro işlevine sarılmış. Bu, joker karakterler (*, #,?, vb.) kullanarak daha esnek bir arama yapmanızı sağlar. Ne yazık ki, bu araç metinden istenen alt dizeyi çıkaramaz – yalnızca içinde olup olmadığını kontrol edin.

Yukarıdakilere ek olarak, dar profesyonel programcılar, web geliştiriciler ve diğer teknisyenler çevrelerinde çok iyi bilinen başka bir yaklaşım daha vardır - bu düzenli ifadeler (Normal İfadeler = Normal İfade = "normal ifade" = "düzenli"). Basit ifadeyle, RegExp, metinde gerekli alt dizeleri aramak, çıkarmak veya başka bir metinle değiştirmek için özel karakterlerin ve kuralların kullanıldığı bir dildir.. Normal ifadeler, metinle çalışmanın diğer tüm yollarını büyüklük sırasına göre aşan çok güçlü ve güzel bir araçtır. Birçok programlama dili (C#, PHP, Perl, JavaScript…) ve metin düzenleyiciler (Word, Notepad++…) düzenli ifadeleri destekler.

Microsoft Excel maalesef kutudan RegExp desteğine sahip değil, ancak bu VBA ile kolayca düzeltilebilir. Sekmeden Visual Basic Düzenleyicisini açın geliştirici (Geliştirici) veya klavye kısayolu Ara Toplam+F11. Ardından yeni modülü menüden yerleştirin Ekle – Modül ve aşağıdaki makro işlevinin metnini buraya kopyalayın:

Genel İşlev RegExpExtract(Dize Olarak Metin, Dize Olarak Model, Tamsayı Olarak İsteğe Bağlı Öğe = 1) Hatada Dize Olarak Git ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Model regex.Global = True If regex.Test (Metin) Ardından Set eşleşmeleri = regex.Execute(Text) RegExpExtract = eşleşmeler.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVERr(xlErrValue) End Function  

Artık Visual Basic Düzenleyiciyi kapatabilir ve yeni özelliğimizi denemek için Excel'e dönebiliriz. Sözdizimi aşağıdaki gibidir:

=RegExpExtract( Txt ; Model ; Öğe )

nerede

• txt – kontrol ettiğimiz ve ihtiyacımız olan alt diziyi çıkarmak istediğimiz metni içeren bir hücre
• model – alt dizi araması için maske (desen)
• + – birkaç tane varsa, ayıklanacak alt dizinin sıra numarası (belirtilmemişse, ilk oluşum görüntülenir)

Buradaki en ilginç şey, elbette, Desen - tam olarak neyi ve nerede bulmak istediğimizi belirten RegExp'in "dilinde" özel karakterlerden oluşan bir şablon dizesi. İşte başlamanız için en temel olanlar:

Belirli sayıda karakter arıyorsak, örneğin altı basamaklı bir posta kodu veya üç harfli ürün kodlarının tümü, kurtarmaya geliriz. niceleyiciler or niceleyiciler aranacak karakter sayısını belirten özel ifadelerdir. Niceleyiciler kendisinden önce gelen karaktere uygulanır:

Şimdi en ilginç kısma geçelim – yaratılan fonksiyonun uygulamasının bir analizi ve hayattan pratik örnekler üzerindeki kalıplar hakkında öğrendiklerimiz.

Metinden sayıları çıkarma

Başlamak için, basit bir durumu analiz edelim - ilk sayıyı alfasayısal yulaf lapasından çıkarmanız gerekir, örneğin, fiyat listesinden kesintisiz güç kaynaklarının gücü:

Normal ifadenin arkasındaki mantık basittir: d herhangi bir rakam anlamına gelir ve niceleyici + sayılarının bir veya daha fazla olması gerektiğini söylüyor. Fonksiyonun önündeki çift eksi, “anında” ayıklanan karakterleri metin olarak sayıdan tam sayıya dönüştürmek için gereklidir.

Posta Kodu

İlk bakışta, burada her şey basit - arka arkaya tam olarak altı rakam arıyoruz. Özel bir karakter kullanıyoruz d basamak ve niceleyici için 6 {} karakter sayısı için:

Bununla birlikte, satırdaki dizinin solunda, arka arkaya başka bir büyük sayı kümesi (telefon numarası, TIN, banka hesabı vb.) olduğunda bir durum mümkündür. O zaman normal sezonumuz ilk 6'yı çıkaracaktır. ondan rakamlar, yani düzgün çalışmayacak:

Bunun olmasını önlemek için normal ifademizin kenarlarına bir değiştirici eklememiz gerekiyor. b bir kelimenin bitişini ifade eder. Bu, Excel'e, ihtiyacımız olan parçanın (dizin) başka bir parçanın (telefon numarası) parçası değil, ayrı bir kelime olması gerektiğini açıkça gösterecektir:

Telefon

Metinde bir telefon numarası bulmanın sorunu, sayıları yazmak için çok fazla seçeneğin olmasıdır - tireli ve tiresiz, boşluklar içinde, parantez içinde bölge kodu olsun veya olmasın, vb. Bu nedenle, bence, bence daha kolay. önce birkaç iç içe işlevi kullanarak tüm bu karakterleri kaynak metinden temizleyin VEKİL (VEKİL)böylece tek bir bütün halinde birbirine yapışır ve sonra ilkel bir düzenli g{11} arka arkaya 11 rakamı çıkarın:

ITN

Burada biraz daha karmaşık çünkü TIN (Ülkemizde) 10 haneli (tüzel kişiler için) veya 12 haneli (bireyler için) olabilir. Özellikle kusur bulmuyorsanız, normalden memnun kalmanız oldukça olasıdır. g{10,12}, ancak kesin olarak konuşursak, 10 ila 12 karakter arasındaki tüm sayıları, yani ve hatalı girilen 11 haneyi çıkaracaktır. Mantıksal bir VEYA operatörü ile bağlanmış iki desen kullanmak daha doğru olur. | (dikey çubuk):

Lütfen sorguda önce 12 bitlik sayıları, sonra da 10 bitlik sayıları aradığımızı unutmayın. Normal ifademizi tersini yazarsak, o zaman herkes için, uzun 12 bitlik TIN'ler bile, yalnızca ilk 10 karakter için çıkar. Yani, ilk koşul tetiklendikten sonra artık daha fazla doğrulama yapılmaz:

Bu, operatör arasındaki temel farktır. | standart bir excel mantık işlevinden OR (TD), burada argümanları yeniden düzenlemek sonucu değiştirmez.

Ürün SKU'ları

Birçok şirkette, mal ve hizmetlere benzersiz tanımlayıcılar atanır - makaleler, SAP kodları, SKU'lar vb. Gösterimlerinde mantık varsa, normal ifadeler kullanılarak herhangi bir metinden kolayca çıkarılabilirler. Örneğin, makalelerimizin her zaman üç büyük İngilizce harf, bir tire ve ardından üç basamaklı bir sayıdan oluştuğunu biliyorsak, o zaman:

Şablonun arkasındaki mantık basittir. [AZ] - Latin alfabesinin herhangi bir büyük harfi anlamına gelir. Bir sonraki niceleyici 3 {} tam olarak böyle üç harf olmasının bizim için önemli olduğunu söylüyor. Tireden sonra üç hane bekliyoruz, bu yüzden sonuna ekliyoruz g{3}

Nakit tutarlar

Bir önceki paragrafa benzer şekilde, mal tanımından fiyatları da (maliyetler, KDV…) çıkarabilirsiniz. Örneğin parasal tutarlar bir tire ile belirtilirse, o zaman:

model d niceleyici ile + kısa çizgiye kadar herhangi bir sayıyı arar ve g{2} sonra pennies (iki basamaklı) arayacaktır.

Fiyatları değil, KDV'yi çıkarmanız gerekiyorsa, o zaman çıkarılacak öğenin sıra numarasını belirten RegExpExtract işlevimizin üçüncü isteğe bağlı argümanını kullanabilirsiniz. Ve elbette, işlevi değiştirebilirsiniz VEKİL (VEKİL) sonuçlarda, standart ondalık ayırıcıya kısa çizgi ekleyin ve Excel'in bulunan KDV'yi normal bir sayı olarak yorumlaması için başına bir çift eksi ekleyin:

Araba plaka numaraları

Özel araç, römork ve diğer motosikletleri götürmezseniz standart araç numarası “harf – üç rakam – iki harf – bölge kodu” ilkesine göre ayrıştırılır. Üstelik bölge kodu 2 veya 3 haneli olabiliyor ve harf olarak yalnızca Latin alfabesine benzeyenler kullanılıyor. Böylece, aşağıdaki düzenli ifade metinden sayıları çıkarmamıza yardımcı olacaktır:

Zaman

Saati SS:MM biçiminde çıkarmak için aşağıdaki normal ifade uygundur:

kolon fragmanından sonra [0-5]d, anlaması kolay olduğu için 00-59 aralığında herhangi bir sayı ayarlar. Parantez içindeki iki nokta üst üste işaretinden önce, mantıksal bir VEYA (boru) ile ayrılmış iki desen çalışır:

• [0-1]d – 00-19 aralığındaki herhangi bir sayı
• 2[0-3] – 20-23 aralığındaki herhangi bir sayı

Elde edilen sonuca ek olarak standart Excel işlevini de uygulayabilirsiniz. ZAMAN (TAKIM)program tarafından anlaşılabilir ve daha sonraki hesaplamalar için uygun bir zaman formatına dönüştürmek.

Parola kontrolu

Kullanıcılar tarafından icat edilen şifrelerin listesinin doğruluğunu kontrol etmemiz gerektiğini varsayalım. Kurallarımıza göre şifreler yalnızca İngilizce harflerden (küçük veya büyük harf) ve rakamlardan oluşabilir. Boşluk, alt çizgi ve diğer noktalama işaretlerine izin verilmez.

Kontrol, aşağıdaki basit normal ifade kullanılarak düzenlenebilir:

Aslında, böyle bir kalıpla, başlangıç ​​(^) ve son ($) metnimizde sadece köşeli parantez içinde verilen kümeden karakterler vardı. Parolanın uzunluğunu da kontrol etmeniz gerekiyorsa (örneğin, en az 6 karakter), o zaman niceleyici + şeklinde "altı veya daha fazla" aralığı ile değiştirilebilir {6,}:

Adresten şehir

Diyelim ki şehri adres çubuğundan çekmemiz gerekiyor. Normal program, metni “g” den çıkarmaya yardımcı olacaktır. sonraki virgül için:

Bu kalıba daha yakından bakalım.

Yukarıdaki metni okuduysanız, normal ifadelerdeki (noktalar, yıldızlar, dolar işaretleri vb.) bazı karakterlerin özel bir anlamı olduğunu zaten anladınız. Bu karakterlerin kendilerini aramanız gerekiyorsa, bu karakterlerin önüne bir ters eğik çizgi gelir (bazen koruyucu). Bu nedenle, “g” parçasını ararken. normal ifadede yazmalıyız Bay. bir artı arıyorsak, o zaman + vb.

Şablonumuzdaki sonraki iki karakter, nokta ve nicelik belirteci yıldız işareti, herhangi bir sayıdaki herhangi bir karakteri, yani herhangi bir şehir adını temsil eder.

Şablonun sonunda virgül var çünkü “g”den gelen metni arıyoruz. bir virgül için. Ancak metinde birkaç virgül olabilir, değil mi? Sadece şehirden sonra değil, caddeden, evlerden vs sonra da talebimiz hangisinde duracak? Soru işareti bunun içindir. Onsuz, normal ifademiz mümkün olan en uzun dizeyi çıkarır:

Düzenli ifadeler açısından, böyle bir kalıp "açgözlü" olur. Durumu düzeltmek için, bir soru işareti gereklidir – niceleyiciyi “cimri” hale getirir – ve sorgumuz metni yalnızca “g” den sonraki ilk sayaç virgülüne kadar alır:

Tam yoldan dosya adı

Diğer bir çok yaygın durum, dosya adını tam yoldan çıkarmaktır. Formun basit bir düzenli ifadesi burada yardımcı olacaktır:

Buradaki hile, aramanın aslında ters yönde gerçekleşmesidir – sondan başlangıca, çünkü şablonumuzun sonunda $, ve sağdan ilk ters eğik çizgiye kadar ondan önceki her şeyi arıyoruz. Önceki örnekteki nokta gibi ters eğik çizgiden kaçılır.

PS

“Sona doğru” Yukarıdakilerin hepsinin düzenli ifadelerin sağladığı tüm olasılıkların küçük bir parçası olduğunu açıklığa kavuşturmak istiyorum. Pek çok özel karakter ve kullanımları için kurallar vardır ve bu konuda koca kitaplar yazılmıştır (en azından başlangıç ​​için bunu tavsiye ederim). Bir bakıma, düzenli ifadeler yazmak neredeyse bir sanattır. Hemen hemen her zaman, icat edilmiş bir düzenli ifade geliştirilebilir veya tamamlanabilir, bu da onu daha zarif hale getirir veya daha geniş bir girdi verisi yelpazesiyle çalışabilir.

Başkalarının normal ifadelerini analiz etmek ve ayrıştırmak ya da kendinizinkinde hata ayıklamak için birkaç uygun çevrimiçi hizmet vardır: NormalEx101, Normal İfade ve dahası

Ne yazık ki, klasik düzenli ifadelerin tüm özellikleri VBA'da desteklenmiyor (örneğin, ters arama veya POSIX sınıfları) ve Kiril ile çalışabilir, ancak orada olanın ilk kez sizi memnun etmek için yeterli olduğunu düşünüyorum.

Konuda yeni değilseniz ve paylaşacak bir şeyiniz varsa, aşağıdaki yorumlarda Excel'de çalışırken kullanışlı olan normal ifadeleri bırakın. Bir zihin iyidir, ama iki çizme bir çifttir!

• SUBSTITUTE işleviyle metni değiştirme ve temizleme
• Metindeki Latin karakterlerini arama ve vurgulama
• En yakın benzer metni arayın (Ivanov = Ivonov = Ivanof, vb.)