Menghuraikan teks dengan ungkapan biasa (RegExp) dalam Excel

Salah satu tugas yang paling memakan masa dan mengecewakan apabila bekerja dengan teks dalam Excel ialah parsing – menghuraikan "bubur" alfanumerik kepada komponen dan mengekstrak serpihan yang kita perlukan daripadanya. Sebagai contoh:

• mengekstrak kod zip dari alamat (ada baiknya jika kod zip sentiasa di awal, tetapi bagaimana jika tidak?)
• mencari nombor dan tarikh invois daripada keterangan pembayaran dalam penyata bank
• pengekstrakan TIN daripada perihalan beraneka ragam syarikat dalam senarai rakan niaga
• cari nombor kereta atau nombor artikel dalam keterangan, dsb.
  

Biasanya dalam kes sedemikian, selepas setengah jam memetik teks yang suram secara manual, pemikiran mula terlintas di fikiran entah bagaimana untuk mengautomasikan proses ini (terutamanya jika terdapat banyak data). Terdapat beberapa penyelesaian dan dengan pelbagai tahap kecekapan-kerumitan:

• Penggunaan fungsi teks Excel terbina dalam untuk mencari teks gam-potong: LEVSIMV (DIBIARKAN), HAK (KANAN), PSTR (pertengahan), STsEPIT (CONCATENATE) dan analognya, GABUNG (JOINTEXT), TEPAT (TEPAT) dan lain-lain. Kaedah ini bagus jika terdapat logik yang jelas dalam teks (contohnya, indeks sentiasa berada di awal alamat). Jika tidak, formula menjadi lebih rumit dan, kadangkala, ia juga datang kepada formula tatasusunan, yang sangat perlahan pada jadual besar.
• Menggunakan seperti operator persamaan teks daripada Visual Basic yang dibungkus dalam fungsi makro tersuai. Ini membolehkan anda melaksanakan carian yang lebih fleksibel menggunakan aksara kad bebas (*, #,?, dsb.) Malangnya, alat ini tidak boleh mengeluarkan subrentetan yang diingini daripada teks – hanya semak sama ada ia terkandung di dalamnya.

Sebagai tambahan kepada perkara di atas, terdapat satu lagi pendekatan yang sangat terkenal dalam kalangan sempit pengaturcara profesional, pembangun web dan juruteknik lain - ini adalah ungkapan biasa (Ungkapan Biasa = RegExp = "regexps" = "biasa"). Hanya meletakkan, RegExp ialah bahasa di mana aksara dan peraturan khas digunakan untuk mencari subrentetan yang diperlukan dalam teks, mengekstraknya atau menggantikannya dengan teks lain. Ungkapan biasa ialah alat yang sangat berkuasa dan cantik yang mengatasi semua cara lain untuk bekerja dengan teks mengikut susunan magnitud. Banyak bahasa pengaturcaraan (C#, PHP, Perl, JavaScript…) dan penyunting teks (Word, Notepad++…) menyokong ungkapan biasa.

Microsoft Excel malangnya tidak mempunyai sokongan RegExp di luar kotak, tetapi ini boleh diperbaiki dengan mudah dengan VBA. Buka Editor Visual Basic dari tab pemaju (Pembangun) atau pintasan papan kekunci Alt+F11. Kemudian masukkan modul baharu melalui menu Sisipan – Modul dan salin teks fungsi makro berikut di sana:

Fungsi Awam RegExpExtract(Teks Sebagai Rentetan, Corak Sebagai Rentetan, Item Pilihan Sebagai Integer = 1) Sebagai Rentetan Pada Ralat PergiTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Corak regex.Global = Benar Jika regex.Test (Teks) Kemudian Tetapkan padanan = regex.Execute(Teks) RegExpExtract = padanan.Item(Item - 1) Keluar Fungsi Tamat Jika ErrHandl: RegExpExtract = CVErr(xlErrValue) Fungsi Tamat  

Kami kini boleh menutup Editor Visual Basic dan kembali ke Excel untuk mencuba ciri baharu kami. Sintaksnya adalah seperti berikut:

=RegExpExtract( Txt ; Corak ; Item )

di mana

• txt – sel dengan teks yang kami semak dan dari mana kami ingin mengekstrak subrentetan yang kami perlukan
• pola – topeng (corak) untuk carian subrentetan
• Perkara – nombor urutan subrentetan yang akan diekstrak, jika terdapat beberapa daripadanya (jika tidak dinyatakan, maka kejadian pertama dipaparkan)

Perkara yang paling menarik di sini, sudah tentu, ialah Corak – rentetan templat aksara khas "dalam bahasa" RegExp, yang menentukan apa sebenarnya dan di mana kita ingin mencari. Berikut ialah yang paling asas untuk anda bermula:

Jika kami mencari bilangan aksara tertentu, contohnya, kod pos enam digit atau semua kod produk tiga huruf, maka kami datang untuk menyelamatkan pembilang or pembilang ialah ungkapan khas yang menentukan bilangan aksara yang hendak dicari. Pengkuantiti digunakan pada aksara yang datang sebelumnya:

Sekarang mari kita beralih ke bahagian yang paling menarik – analisis aplikasi fungsi yang dicipta dan apa yang kita pelajari tentang corak pada contoh praktikal dari kehidupan.

Mengeluarkan nombor daripada teks

Sebagai permulaan, mari kita menganalisis kes mudah – anda perlu mengekstrak nombor pertama daripada bubur alfanumerik, sebagai contoh, kuasa bekalan kuasa tidak terganggu daripada senarai harga:

Logik di sebalik ungkapan biasa adalah mudah: d bermaksud sebarang digit, dan pengkuantiti + mengatakan bahawa bilangan mereka haruslah satu atau lebih. Tolak berganda di hadapan fungsi diperlukan untuk "dengan cepat" menukar aksara yang diekstrak menjadi nombor penuh daripada nombor-sebagai-teks.

Poskod

Pada pandangan pertama, semuanya mudah di sini - kami sedang mencari tepat enam digit berturut-turut. Kami menggunakan watak istimewa d untuk digit dan pengkuantiti 6 {} untuk bilangan aksara:

Walau bagaimanapun, situasi mungkin berlaku apabila, di sebelah kiri indeks dalam baris, terdapat satu lagi set nombor besar berturut-turut (nombor telefon, TIN, akaun bank, dll.) Kemudian musim biasa kami akan mengeluarkan 6 yang pertama digit daripadanya, iaitu tidak akan berfungsi dengan betul:

Untuk mengelakkan perkara ini daripada berlaku, kita perlu menambah pengubah suai di sekeliling tepi ungkapan biasa kita b menandakan akhir sesuatu perkataan. Ini akan menjelaskan kepada Excel bahawa serpihan (indeks) yang kita perlukan mestilah perkataan yang berasingan, dan bukan sebahagian daripada serpihan lain (nombor telefon):

Telefon

Masalah dengan mencari nombor telefon dalam teks ialah terdapat begitu banyak pilihan untuk menulis nombor – dengan dan tanpa tanda sempang, melalui ruang, dengan atau tanpa kod wilayah dalam kurungan, dsb. Oleh itu, pada pendapat saya, lebih mudah untuk pertama bersihkan semua aksara ini daripada teks sumber menggunakan beberapa fungsi bersarang PENGGANTI (GANTIAN)supaya ia melekat bersama menjadi satu keseluruhan, dan kemudian dengan tetap primitif d{11} keluarkan 11 digit berturut-turut:

ITN

Ia lebih rumit di sini, kerana TIN (di Negara Kita) boleh menjadi 10 digit (untuk entiti sah) atau 12 digit (untuk individu). Jika anda tidak mencari kesalahan terutamanya, maka agak mungkin untuk berpuas hati dengan yang biasa d{10,12}, tetapi, secara tegasnya, ia akan mengeluarkan semua nombor daripada 10 hingga 12 aksara, iaitu dan tersilap memasukkan 11 digit. Adalah lebih tepat untuk menggunakan dua corak yang disambungkan oleh pengendali ATAU logik | (bar menegak):

Sila ambil perhatian bahawa dalam pertanyaan kami mula-mula mencari nombor 12-bit, dan hanya kemudian untuk nombor 10-bit. Jika kita menulis ungkapan biasa kita sebaliknya, maka ia akan menarik keluar untuk semua orang, walaupun TIN 12-bit yang panjang, hanya 10 aksara pertama. Iaitu, selepas syarat pertama dicetuskan, pengesahan selanjutnya tidak lagi dilakukan:

Ini adalah perbezaan asas antara pengendali | daripada fungsi logik excel standard OR (OR), di mana menyusun semula hujah tidak mengubah keputusan.

SKU Produk

Dalam banyak syarikat, pengecam unik diberikan kepada barangan dan perkhidmatan – artikel, kod SAP, SKU, dsb. Jika terdapat logik dalam notasi mereka, maka ia boleh ditarik keluar dengan mudah daripada mana-mana teks menggunakan ungkapan biasa. Sebagai contoh, jika kami tahu bahawa artikel kami sentiasa terdiri daripada tiga huruf besar Inggeris, tanda sempang dan nombor tiga digit berikutnya, maka:

Logik di sebalik templat adalah mudah. [AZ] – bermaksud sebarang huruf besar abjad Latin. Pengkuantiti seterusnya 3 {} mengatakan bahawa adalah penting bagi kita bahawa terdapat tiga huruf sedemikian. Selepas tanda sempang, kami menunggu tiga digit, jadi kami menambah pada penghujung d{3}

Jumlah tunai

Dengan cara yang sama seperti perenggan sebelumnya, anda juga boleh mengeluarkan harga (kos, VAT …) daripada perihalan barangan. Jika jumlah kewangan, sebagai contoh, ditunjukkan dengan tanda sempang, maka:

corak d dengan pengkuantiti + mencari sebarang nombor sehingga tanda sempang, dan d{2} akan mencari sen (dua digit) selepas.

Jika anda perlu mengekstrak bukan harga, tetapi VAT, maka anda boleh menggunakan argumen pilihan ketiga bagi fungsi RegExpExtract kami, yang menentukan nombor ordinal elemen yang akan diekstrak. Dan, sudah tentu, anda boleh menggantikan fungsi tersebut PENGGANTI (GANTIAN) dalam keputusan, tanda sempang pada pemisah perpuluhan standard dan tambahkan tolak berganda pada permulaan supaya Excel mentafsirkan VAT yang ditemui sebagai nombor biasa:

Nombor plat kereta

Jika anda tidak mengambil kenderaan khas, treler dan motosikal lain, maka nombor kereta standard dihuraikan mengikut prinsip "huruf - tiga nombor - dua huruf - kod wilayah". Selain itu, kod wilayah boleh menjadi 2- atau 3 digit, dan hanya kod yang serupa dengan abjad Latin digunakan sebagai huruf. Oleh itu, ungkapan biasa berikut akan membantu kita mengekstrak nombor daripada teks:

Masa

Untuk mengekstrak masa dalam format HH:MM, ungkapan biasa berikut adalah sesuai:

Selepas serpihan kolon [0-5]d, kerana mudah untuk difikirkan, tetapkan sebarang nombor dalam julat 00-59. Sebelum kolon dalam kurungan, dua corak berfungsi, dipisahkan oleh OR logik (paip):

• [0-1]d – sebarang nombor dalam julat 00-19
• 2[0-3] – sebarang nombor dalam julat 20-23

Untuk hasil yang diperoleh, anda juga boleh menggunakan fungsi Excel standard MASA (PASUKAN)untuk menukarnya kepada format masa yang boleh difahami oleh program dan sesuai untuk pengiraan selanjutnya.

Semakan kata laluan

Katakan kita perlu menyemak senarai kata laluan yang dicipta oleh pengguna untuk ketepatannya. Mengikut peraturan kami, kata laluan hanya boleh mengandungi huruf Inggeris (huruf kecil atau besar) dan nombor. Ruang, garis bawah dan tanda baca lain tidak dibenarkan.

Semakan boleh diatur menggunakan ungkapan biasa mudah berikut:

Malah, dengan corak sedemikian kami memerlukan antara permulaan (^) dan akhir ($) dalam teks kami hanya terdapat aksara daripada set yang diberikan dalam kurungan segi empat sama. Jika anda juga perlu menyemak panjang kata laluan (contohnya, sekurang-kurangnya 6 aksara), maka pengkuantiti + boleh digantikan dengan selang "enam atau lebih" dalam bentuk {6,}:

Bandar dari alamat

Katakan kita perlu menarik bandar dari bar alamat. Program biasa akan membantu, mengekstrak teks daripada "g." ke koma seterusnya:

Mari kita lihat lebih dekat corak ini.

Jika anda telah membaca teks di atas, maka anda sudah memahami bahawa beberapa aksara dalam ungkapan biasa (tempoh, asterisk, tanda dolar, dll.) mempunyai makna yang istimewa. Jika anda perlu mencari watak-watak ini sendiri, maka ia didahului dengan garis serong ke belakang (kadangkala dipanggil melindungi). Oleh itu, apabila mencari serpihan "g." kita perlu menulis dalam ungkapan biasa Mr. jika kita mencari tambahan, maka + dan lain-lain.

Dua aksara seterusnya dalam templat kami, titik dan asterisk pengkuantiti, mewakili sebarang nombor sebarang aksara, iaitu sebarang nama bandar.

Terdapat koma di hujung templat, kerana kami sedang mencari teks daripada "g." kepada koma. Tetapi mungkin terdapat beberapa koma dalam teks, bukan? Bukan sahaja selepas bandar, tetapi juga selepas jalan, rumah, dsb. Di antara mereka, permintaan kami yang manakah akan dihentikan? Itulah gunanya tanda tanya. Tanpa itu, ungkapan biasa kami akan mengeluarkan rentetan terpanjang yang mungkin:

Dari segi ungkapan biasa, corak sedemikian adalah "tamak". Untuk membetulkan keadaan, tanda soal diperlukan - ia menjadikan pengkuantiti selepas itu ia berdiri "kedekut" - dan pertanyaan kami membawa teks hanya sehingga koma balas pertama selepas "g.":

Nama fail daripada laluan penuh

Satu lagi situasi yang sangat biasa ialah mengekstrak nama fail dari laluan penuh. Ungkapan biasa mudah borang akan membantu di sini:

Caranya di sini ialah carian, sebenarnya, berlaku dalam arah yang bertentangan - dari akhir hingga awal, kerana pada akhir templat kami adalah $, dan kami sedang mencari segala-galanya sebelum itu sehingga garis serong ke belakang pertama dari kanan. Garis miring ke belakang dilepaskan, seperti titik dalam contoh sebelumnya.

PS

"Menjelang akhir" Saya ingin menjelaskan bahawa semua perkara di atas adalah sebahagian kecil daripada semua kemungkinan yang disediakan oleh ungkapan biasa. Terdapat banyak watak dan peraturan khas untuk kegunaan mereka, dan keseluruhan buku telah ditulis mengenai topik ini (saya cadangkan sekurang-kurangnya yang ini sebagai permulaan). Dari satu segi, menulis ungkapan biasa adalah hampir satu seni. Hampir selalu, ungkapan biasa yang dicipta boleh diperbaiki atau ditambah, menjadikannya lebih elegan atau dapat berfungsi dengan julat data input yang lebih luas.

Untuk menganalisis dan menghuraikan ungkapan biasa orang lain atau nyahpepijat anda sendiri, terdapat beberapa perkhidmatan dalam talian yang mudah: RegEx101, RegExr dan banyak lagi

Malangnya, bukan semua ciri ungkapan biasa klasik disokong dalam VBA (contohnya, carian terbalik atau kelas POSIX) dan boleh berfungsi dengan Cyrillic, tetapi saya fikir apa yang ada sudah cukup untuk kali pertama untuk menggembirakan anda.

Jika anda bukan orang baru dalam topik ini, dan anda mempunyai sesuatu untuk dikongsi, biarkan ungkapan biasa berguna apabila bekerja dalam Excel dalam ulasan di bawah. Satu fikiran bagus, tetapi dua but adalah sepasang!

• Mengganti dan membersihkan teks dengan fungsi SUBSTITUTE
• Cari dan serlahkan aksara Latin dalam teks
• Cari teks serupa yang terdekat (Ivanov = Ivonov = Ivanof, dsb.)