Excelda muntazam ifodalar (RegExp) bilan matnni tahlil qilish

Excelda matn bilan ishlashda eng ko'p vaqt talab qiluvchi va asabiylashuvchi vazifalardan biri bu tahlil qilish - alfanumerik "porridge" ni tarkibiy qismlarga ajratish va undan bizga kerak bo'lgan qismlarni ajratib olish. Masalan:

• manzildan pochta indeksini chiqarib olish (pochta indeksi har doim boshida bo'lsa yaxshi, lekin bo'lmasa nima bo'ladi?)
• bank ko'chirmasidagi to'lov tavsifidan schyot-fakturaning raqami va sanasini topish
• kontragentlar ro'yxatidagi kompaniyalarning rang-barang tavsiflaridan TINni olish
• tavsifda avtomobil raqami yoki maqola raqamini qidiring va hokazo.
  

Odatda, bunday hollarda, matnni qo'lda yarim soat davomida ma'yus terishdan so'ng, qandaydir tarzda bu jarayonni avtomatlashtirish haqida fikrlar paydo bo'la boshlaydi (ayniqsa, ma'lumotlar juda ko'p bo'lsa). Turli darajadagi murakkablik va samaradorlik bilan bir nechta echimlar mavjud:

• foydalanish o'rnatilgan Excel matn funktsiyalari matnni kesish uchun: LEVSIMV (SOL), deportatsiya (To'g'ri), PSTR (o'rta), STsEPIT (UVLASH) va uning analoglari, ARALASHTIRMOQ (JOINTEXT), MAQOLA (aniq) va hokazo. Agar matnda aniq mantiq bo'lsa, bu usul yaxshi (masalan, indeks har doim manzilning boshida). Aks holda, formulalar ancha murakkablashadi va ba'zida bu hatto massiv formulalariga ham keladi, bu katta jadvallarda sezilarli darajada sekinlashadi.
• foydalanish matn o'xshashlik operatori kabi maxsus so'l funksiyasiga o'ralgan Visual Basic-dan. Bu joker belgilar (*, #,?, va hokazo) yordamida yanada moslashuvchan qidiruvni amalga oshirishga imkon beradi, afsuski, ushbu vosita kerakli pastki qatorni matndan chiqarib ololmaydi - faqat uning tarkibida mavjudligini tekshiring.

Yuqoridagilarga qo'shimcha ravishda, professional dasturchilar, veb-ishlab chiquvchilar va boshqa texniklarning tor doiralarida juda yaxshi ma'lum bo'lgan yana bir yondashuv mavjud - bu muntazam iboralar (Oddiy ifodalar = RegExp = "regexps" = "muntazam"). Oddiy qilib aytganda, RegExp - bu matnda kerakli pastki qatorlarni qidirish, ularni ajratib olish yoki boshqa matn bilan almashtirish uchun maxsus belgilar va qoidalar qo'llaniladigan til.. Muntazam iboralar juda kuchli va chiroyli vosita bo'lib, matn bilan ishlashning barcha boshqa usullarini kattalik tartibida ortda qoldiradi. Ko'pgina dasturlash tillari (C#, PHP, Perl, JavaScript…) va matn muharrirlari (Word, Notepad++…) oddiy ifodalarni qo'llab-quvvatlaydi.

Afsuski, Microsoft Excel-da RegExp-ni qo'llab-quvvatlamaydi, ammo buni VBA yordamida osongina tuzatish mumkin. Yorliqdan Visual Basic muharririni oching dasturchi (ishlab chiquvchi) yoki klaviatura yorlig'i Alt+F11. Keyin menyu orqali yangi modulni joylashtiring Qo'shish - modul va u yerga quyidagi makrofunktsiyaning matnini ko'chiring:

Umumiy funktsiya RegExpExtract(Matn satr sifatida, naqsh satr sifatida, ixtiyoriy element butun son sifatida = 1) String On xatolik sifatida GoTo ErrHandl regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True Agar regex.Test bo'lsa (Matn) Keyin mosliklarni o'rnating = regex.Execute(Matn) RegExpExtract = matches.Item(Item - 1) Funktsiyadan chiqish ErrHandl bo'lsa: RegExpExtract = CVErr(xlErrValue) End Funktsiya  

Endi biz Visual Basic muharririni yopishimiz va yangi xususiyatimizni sinab ko'rish uchun Excelga qaytishimiz mumkin. Uning sintaksisi quyidagicha:

=RegExpExtract( Txt ; Pattern ; Element )

qayerda

• txt - biz tekshirayotgan va biz kerakli pastki qatorni chiqarmoqchi bo'lgan matnli katak
• namuna – pastki qatorni qidirish uchun niqob (naqsh).
• buyum - ajratib olinadigan pastki qatorning tartib raqami, agar ulardan bir nechtasi bo'lsa (agar ko'rsatilmagan bo'lsa, unda birinchi hodisa ko'rsatiladi)

Bu erda eng qiziq narsa, albatta, Pattern - RegExp-ning "tilidagi" maxsus belgilarning shablonlar qatori, biz aniq nimani va qaerdan topishni xohlayotganimizni ko'rsatadi. Sizni boshlash uchun eng asosiylari:

Agar biz ma'lum miqdordagi belgilarni, masalan, olti raqamli pochta indeksini yoki barcha uch harfli mahsulot kodlarini qidirayotgan bo'lsak, biz yordamga kelamiz. miqdoriy o'lchagichlar or miqdoriy o'lchagichlar izlanadigan belgilar sonini ko'rsatuvchi maxsus iboralardir. Miqdor ko'rsatkichlari o'zidan oldin kelgan belgiga qo'llaniladi:

Keling, eng qiziqarli qismga - yaratilgan funktsiyani qo'llash tahliliga o'tamiz va biz hayotdan amaliy misollar bo'yicha naqshlar haqida bilib oldik.

Matndan raqamlarni ajratib olish

Boshlash uchun oddiy ishni tahlil qilaylik - alfanumerik bo'tqadan birinchi raqamni olishingiz kerak, masalan, narxlar ro'yxatidan uzluksiz quvvat manbalarining quvvati:

Muntazam ifoda ortidagi mantiq oddiy: d har qanday raqam va miqdorni bildiradi + ularning soni bir yoki bir nechta bo'lishi kerakligini aytadi. Funktsiya oldidagi er-xotin minus, ajratilgan belgilarni matn sifatida raqamdan to'liq raqamga aylantirish uchun kerak bo'ladi.

Pochta indeksi

Bir qarashda, bu erda hamma narsa oddiy - biz ketma-ket oltita raqamni qidirmoqdamiz. Biz maxsus belgidan foydalanamiz d raqam va miqdor uchun 6 {} belgilar soni uchun:

Biroq, qatordagi indeksning chap tomonida yana bir katta raqamlar to'plami (telefon raqami, TIN, bank hisobvarag'i va boshqalar) mavjud bo'lganda, bizning muntazam mavsumimiz birinchi 6 tasini chiqaradi. undagi raqamlar, ya'ni to'g'ri ishlamaydi:

Bunga yo'l qo'ymaslik uchun oddiy ifodaning chetiga o'zgartiruvchi qo'shishimiz kerak b so'zning oxirini bildiradi. Bu Excelga bizga kerak bo'lgan fragment (indeks) boshqa fragmentning (telefon raqami) qismi emas, balki alohida so'z bo'lishi kerakligini aniq ko'rsatib beradi:

Telefon

Matnda telefon raqamini topish bilan bog‘liq muammo shundaki, raqamlarni yozishning juda ko‘p variantlari mavjud – tire bilan va tiresiz, bo‘shliqlar orqali, qavs ichida mintaqa kodi bilan yoki bo‘lmasdan va hokazo. Shuning uchun, mening fikrimcha, buni qilish osonroq avval bir nechta ichki funksiyalar yordamida manba matndagi barcha belgilarni tozalang Substitut (O'RNISHA)Shunday qilib, u bir butunga, keyin esa ibtidoiy muntazam bilan birga yopishadi d{11} ketma-ket 11 ta raqamni chiqaring:

ITN

Bu erda biroz murakkabroq, chunki TIN (Mamlakatimizda) 10 raqamli (yuridik shaxslar uchun) yoki 12 raqamli (jismoniy shaxslar uchun) bo'lishi mumkin. Agar siz ayniqsa kamchilik topmasangiz, odatdagidan mamnun bo'lishingiz mumkin d{10,12}, lekin, qat'iy aytganda, u 10 dan 12 ta belgigacha bo'lgan barcha raqamlarni chiqarib tashlaydi, ya'ni va noto'g'ri kiritilgan 11 ta raqam. Mantiqiy OR operatori bilan bog'langan ikkita naqshdan foydalanish to'g'riroq bo'ladi | (vertikal bar):

E'tibor bering, so'rovda biz birinchi navbatda 12 bitli raqamlarni, keyin esa 10 bitli raqamlarni qidiramiz. Agar biz oddiy iboramizni aksincha yozsak, u hamma uchun, hatto uzun 12-bitli TIN-lar ham, faqat dastlabki 10 ta belgini chiqarib tashlaydi. Ya'ni, birinchi shart ishga tushirilgandan so'ng, keyingi tekshirish endi amalga oshirilmaydi:

Bu operator o'rtasidagi asosiy farq | standart excel mantiqiy funktsiyasidan OR (OR), bu erda argumentlarni qayta tartibga solish natijani o'zgartirmaydi.

Mahsulot SKUlari

Ko'pgina kompaniyalarda tovarlar va xizmatlarga noyob identifikatorlar tayinlanadi - maqolalar, SAP kodlari, SKU'lar va boshqalar. Agar ularning yozuvlarida mantiq mavjud bo'lsa, u holda ularni oddiy iboralar yordamida har qanday matndan osongina tortib olish mumkin. Misol uchun, agar bizning maqolalarimiz har doim ingliz tilidagi uchta bosh harf, chiziqcha va keyingi uch xonali raqamdan iborat ekanligini bilsak, unda:

Shablon ortidagi mantiq oddiy. [AZ] – lotin alifbosining har qanday bosh harflarini bildiradi. Keyingi miqdor ko'rsatkichi 3 {} Biz uchun aynan uchta shunday harf borligi muhimligini aytadi. Defisdan keyin biz uchta raqamni kutmoqdamiz, shuning uchun biz oxirida qo'shamiz d{3}

Naqd pul miqdori

Oldingi bandga o'xshash tarzda siz tovarlar tavsifidan narxlarni (xarajat, QQS ...) chiqarib olishingiz mumkin. Agar pul summalari, masalan, chiziqcha bilan ko'rsatilgan bo'lsa, unda:

namuna d miqdor ko'rsatkichi bilan + defisgacha bo'lgan istalgan raqamni qidiradi va d{2} keyin pennies (ikki raqam) qidiradi.

Agar siz narxlarni emas, balki QQSni chiqarishingiz kerak bo'lsa, siz RegExpExtract funksiyamizning uchinchi ixtiyoriy argumentidan foydalanishingiz mumkin, bu esa olinadigan elementning tartib raqamini belgilaydi. Va, albatta, siz funktsiyani almashtirishingiz mumkin Substitut (O'RNISHA) natijalarda standart kasrni ajratuvchiga tire qo'ying va boshida ikki marta minus qo'shing, shunda Excel topilgan QQSni oddiy raqam sifatida izohlaydi:

Avtomobil raqamlari

Agar siz maxsus transport vositalarini, treylerlarni va boshqa mototsikllarni olmasangiz, standart avtomobil raqami "harf - uchta raqam - ikki harf - mintaqa kodi" tamoyiliga muvofiq tahlil qilinadi. Bundan tashqari, mintaqa kodi 2 yoki 3 xonali bo'lishi mumkin va faqat lotin alifbosiga o'xshash bo'lganlar harf sifatida ishlatiladi. Shunday qilib, quyidagi oddiy ibora bizga matndan raqamlarni chiqarishga yordam beradi:

vaqt

Vaqtni HH:MM formatida chiqarish uchun quyidagi muntazam ifoda mos keladi:

Yo'g'on ichak bo'lagidan keyin [0-5]k, tushunish oson bo'lgani uchun, 00-59 oralig'ida istalgan raqamni o'rnatadi. Qavslar ichidagi yo'g'on nuqtadan oldin mantiqiy OR (quvur) bilan ajratilgan ikkita naqsh ishlaydi:

• [0-1]k - 00-19 oralig'idagi istalgan raqam
• 2[0-3] - 20-23 oralig'idagi istalgan raqam

Olingan natijaga qo'shimcha ravishda standart Excel funktsiyasini qo'llashingiz mumkin TIME (Jamoa)uni dastur uchun tushunarli va keyingi hisob-kitoblar uchun mos bo'lgan vaqt formatiga aylantirish uchun.

Parolni tekshirish

Aytaylik, biz foydalanuvchilar tomonidan ixtiro qilingan parollar ro'yxatini to'g'riligini tekshirishimiz kerak. Bizning qoidalarimizga ko'ra, parollar faqat inglizcha harflar (kichik yoki katta) va raqamlardan iborat bo'lishi mumkin. Bo'shliqlar, pastki chiziq va boshqa tinish belgilariga yo'l qo'yilmaydi.

Tekshirish quyidagi oddiy oddiy ifoda yordamida tashkil etilishi mumkin:

Aslida, bunday naqsh bilan biz boshlang'ich o'rtasida (^) va oxiri ($) bizning matnimizda faqat kvadrat qavs ichida berilgan to'plamdagi belgilar mavjud edi. Agar siz parol uzunligini ham tekshirishingiz kerak bo'lsa (masalan, kamida 6 ta belgi), keyin miqdoriy belgi + shakldagi "olti yoki undan ko'p" oralig'i bilan almashtirilishi mumkin {6,}:

Manzildan shahar

Aytaylik, biz shaharni manzil satridan tortib olishimiz kerak. Oddiy dastur "g" dan matnni olib tashlashga yordam beradi. keyingi vergulgacha:

Keling, ushbu naqshni batafsil ko'rib chiqaylik.

Agar siz yuqoridagi matnni o'qigan bo'lsangiz, unda siz oddiy iboralardagi ba'zi belgilar (nuqtalar, yulduzchalar, dollar belgilari va boshqalar) alohida ma'noga ega ekanligini allaqachon tushungansiz. Agar siz ushbu belgilarni o'zingiz qidirishingiz kerak bo'lsa, unda ular oldida teskari chiziq (ba'zan deyiladi himoya qilish). Shuning uchun, "g" fragmentini qidirishda. muntazam ifodada yozishimiz kerak Janob. agar biz ortiqcha narsani qidirayotgan bo'lsak, unda + va hokazo

Shablonimizdagi keyingi ikkita belgi, nuqta va miqdor belgilovchi yulduzcha har qanday belgilarning istalgan sonini, ya'ni istalgan shahar nomini bildiradi.

Shablon oxirida vergul bor, chunki biz "g" dan matn qidirmoqdamiz. vergulgacha. Lekin matnda bir nechta vergul bo'lishi mumkin, to'g'rimi? Nafaqat shahardan keyin, balki ko'cha, uylar va hokazolardan keyin ham iltimosimiz ularning qaysi birida to'xtaydi? Savol belgisi aynan shu uchun. Busiz, bizning oddiy iboramiz mumkin bo'lgan eng uzun satrni chiqarib tashlaydi:

Muntazam iboralar nuqtai nazaridan, bunday naqsh "ochko'z" dir. Vaziyatni to'g'irlash uchun savol belgisi kerak bo'ladi - u miqdor ko'rsatkichini "ziqna" qiladi - va bizning so'rovimiz matnni faqat "g." dan keyin birinchi qarama-qarshi vergulgacha oladi:

To'liq yo'ldan fayl nomi

Yana bir keng tarqalgan holat - fayl nomini to'liq yo'ldan chiqarib olish. Shaklning oddiy oddiy ifodasi bu erda yordam beradi:

Bu erda hiyla-nayrang shundaki, qidiruv, aslida, teskari yo'nalishda - oxiridan boshigacha sodir bo'ladi, chunki bizning shablonimizning oxirida. $, va biz o'ngdan birinchi teskari chiziqgacha bo'lgan hamma narsani qidiramiz. Oldingi misoldagi nuqta kabi, teskari qiyshiq chiziq qochib ketgan.

PS

"Oxirigacha" Men yuqorida aytilganlarning barchasi muntazam iboralar taqdim etadigan barcha imkoniyatlarning kichik bir qismi ekanligini aniqlamoqchiman. Ulardan foydalanish uchun juda ko'p maxsus belgilar va qoidalar mavjud va bu mavzu bo'yicha butun kitoblar yozilgan (men hech bo'lmaganda buni boshlash uchun tavsiya qilaman). Qaysidir ma'noda, muntazam iboralarni yozish deyarli san'atdir. Deyarli har doim ixtiro qilingan muntazam ifodani yaxshilash yoki to'ldirish mumkin, bu uni yanada oqlangan yoki kengroq kirish ma'lumotlari bilan ishlashga qodir qiladi.

Boshqa odamlarning oddiy iboralarini tahlil qilish va tahlil qilish yoki o'zingiznikini tuzatish uchun bir nechta qulay onlayn xizmatlar mavjud: RegEx101, RegExr va yana

Afsuski, klassik oddiy iboralarning barcha xususiyatlari VBA-da (masalan, teskari qidiruv yoki POSIX sinflari) qo'llab-quvvatlanmaydi va kirill alifbosi bilan ishlay oladi, lekin menimcha, birinchi marta sizni xursand qilish uchun mavjud narsa etarli.

Agar siz mavzuga yangi bo'lmasangiz va baham ko'radigan narsangiz bo'lsa, quyidagi izohlarda Excelda ishlashda foydali bo'lgan oddiy iboralarni qoldiring. Bir aql yaxshi, lekin ikkita etik bir juft!

• SUBSTITUTE funksiyasi bilan matnni almashtirish va tozalash
• Matndagi lotin harflarini qidirish va ajratib ko'rsatish
• Eng yaqin o'xshash matnni qidiring (Ivanov = Ivonov = Ivanof va boshqalar)