Paragraf
Salah sawijining tugas sing paling akeh wektu lan mumetake nalika nggarap teks ing Excel yaiku parsing - parsing "bubur" alfanumerik dadi komponen lan ngekstrak fragmen sing dibutuhake. Tuladhane:
- extract kode pos saka alamat (iku apik yen kode pos tansah ing wiwitan, nanging yen ora?)
- nemokake nomer lan tanggal invoice saka gambaran saka pembayaran ing statement bank
- ekstraksi TIN saka deskripsi motley perusahaan ing dhaptar mitra
- goleki nomer mobil utawa nomer artikel ing katrangan, lsp.
Biasane ing kasus kaya mengkono, sawise setengah jam dreary njupuk ing teks kanthi manual, pikirane wiwit teka ing atine piye wae kanggo ngotomatisasi proses iki (utamane yen ana akeh data). Ana sawetara solusi lan kanthi tingkat kerumitan-efisiensi sing beda-beda:
- Gunakake fungsi teks Excel sing dibangun ing kanggo nggoleki teks lem: LEVIMV (Kiwa), Nang (KANAN), PSTR (tengah), STsEPIT (KONKATEN) lan analoge, KOMBIN (JOINTEXT), PASTI (TEPAT) etc Cara iki apik yen ana logika cetha ing teks (contone, indeks tansah ing wiwitan alamat). Yen ora, rumus dadi luwih rumit lan, kadhangkala, malah nerangake rumus susunan, kang banget slows mudhun ing tabel gedhe.
- nggunakake kaya operator persamaan teks saka Visual Basic kebungkus ing fungsi makro adat. Iki ngidini sampeyan ngleksanakake panelusuran luwih fleksibel nggunakake karakter wildcard (*, #,?, etc.) Sayange, alat iki ora bisa extract substring sing dikarepake saka teks - mung mriksa yen ana ing.
Saliyane ing ndhuwur, ana pendekatan liyane sing kondhang banget ing bunderan sempit programer profesional, pangembang web lan teknisi liyane - iki ungkapan biasa (Ekspresi Reguler = RegExp = "regexps" = "reguler"). Cukup, RegExp minangka basa ing ngendi karakter lan aturan khusus digunakake kanggo nggoleki substrings sing dibutuhake ing teks kasebut, ngekstrak utawa ngganti karo teks liyane.. Ekspresi reguler minangka alat sing kuat lan apik banget sing ngluwihi kabeh cara liya kanggo nggarap teks kanthi urutan gedhene. Akeh basa pemrograman (C#, PHP, Perl, JavaScript…) lan editor teks (Word, Notepad++…) ndhukung ekspresi reguler.
Microsoft Excel sayangé ora duwe dhukungan RegExp metu saka kothak, nanging iki bisa gampang didandani karo VBA. Bukak Editor Visual Basic saka tab pangembang (Pengembang) utawa trabasan keyboard Alt+F11. Banjur lebokake modul anyar liwat menu Insert - Modul lan salin teks fungsi makro ing ngisor iki:
Fungsi Umum RegExpExtract(Teks Minangka String, Pola Minangka String, Item Opsional Minangka Integer = 1) Minangka String Ing Kesalahan GoTo ErrHandl Setel regex = CreateObject("VBScript.RegExp") regex.Pattern = Pola regex.Global = Bener Yen regex.Test (Teks) Banjur Setel cocog = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function Saiki kita bisa nutup Editor Visual Basic lan bali menyang Excel kanggo nyoba fitur anyar kita. Sintaksipun inggih punika:
=RegExpExtract( Txt ; Pattern ; Item )
ngendi
- txt - sel kanthi teks sing kita priksa lan saka ngendi kita pengin ngekstrak substring sing dibutuhake
- pola – topeng (pola) kanggo nggoleki substring
- item - nomer urutan substring sing bakal diekstrak, yen ana sawetara (yen ora ditemtokake, kedadeyan pisanan ditampilake)
Sing paling menarik ing kene, mesthi, yaiku Pola - senar cithakan karakter khusus "ing basa" RegExp, sing nemtokake apa persis lan ing ngendi kita pengin nemokake. Mangkene sing paling dhasar kanggo miwiti:
| pola | Description |
| . | Sing paling gampang yaiku titik. Iki cocog karo karakter apa wae ing pola ing posisi sing ditemtokake. |
| s | Sembarang karakter sing katon kaya spasi (spasi, tab, utawa baris break). |
| S | Anti-varian saka pola sadurunge, yaiku karakter non-spasi putih. |
| d | Nomer apa wae |
| D | Anti-varian saka sing sadurunge, yaiku sembarang digit NOT |
| w | Sembarang aksara Latin (AZ), digit, utawa garis ngisor |
| W | Anti-varian saka sing sadurunge, yaiku dudu Latin, dudu angka lan dudu garis ngisor. |
| [karakter] | Ing kurung kothak, sampeyan bisa nemtokake siji utawa luwih karakter sing diidini ing posisi sing ditemtokake ing teks kasebut. Tuladhane Art bakal cocog karo salah sawijining tembung: Tabel or dhingklik. Sampeyan uga ora bisa ngetung karakter, nanging nyetel minangka sawetara sing dipisahake dening tanda hubung, yaiku tinimbang [ABDCDEF] nulis [AF]. utawa tinimbang [4567] introduce [-4 7]. Contone, kanggo nemtokake kabeh karakter Cyrillic, sampeyan bisa nggunakake cithakan [a-yaA-YayoYo]. |
| [^karakter] | Yen sawise mbukak kurung kothak nambah simbol "tutup" ^, banjur set bakal entuk makna ngelawan - ing posisi sing ditemtokake ing teks, kabeh karakter bakal diidini, kajaba sing kadhaptar. Ya, template [^ЖМ]ut bakal nemokake Path or Substansi or Lali, nanging ora Menakoni or wani, contone |
| | | Operator Boolean OR (UTAWA) kanggo mriksa samubarang kritéria sing ditemtokake. Tuladhane (sakaKam|smalah | invoice) bakal nggoleki teks kanggo samubarang tembung sing ditemtokake. Biasane, sakumpulan opsi dilebokake ing kurung. |
| ^ | Wiwitan baris |
| $ | Pungkasan baris |
| b | Pungkasan tembung |
Yen kita nggoleki sawetara karakter, contone, kode pos enem digit utawa kabeh kode produk telung huruf, banjur kita teka kanggo ngluwari jumlahe or jumlahe minangka ekspresi khusus sing nemtokake jumlah karakter sing bakal digoleki. Quantifiers ditrapake kanggo karakter sing sadurunge:
| Kuantor | Description |
| ? | Nol utawa siji kedadeyan. Tuladhane .? bakal tegese karakter siji utawa ora ana. |
| + | Siji utawa luwih entri. Tuladhane d+ tegese nomer digit apa wae (yaiku nomer antarane 0 lan tanpa wates). |
| * | Nol utawa luwih kedadeyan, yaiku jumlah apa wae. Dadi s* tegese sawetara spasi utawa ora ana spasi. |
| {nomer} or {nomer1,nomer2} | Yen sampeyan kudu nemtokake jumlah kedadeyan sing ditetepake kanthi ketat, banjur kasebut ing kurung kriting. Tuladhane d{6} tegese strictly enem digit, lan pola s{2,5} – loro nganti limang spasi |
Saiki ayo pindhah menyang bagean sing paling menarik - analisis aplikasi fungsi sing digawe lan apa sing kita sinau babagan pola ing conto praktis saka urip.
Extracting nomer saka teks
Kanggo miwiti, ayo nganalisa kasus sing gampang - sampeyan kudu ngekstrak nomer pertama saka bubur alfanumerik, umpamane, kekuwatan pasokan listrik sing ora bisa diganggu saka dhaptar rega:
Logika konco ekspresi reguler iku prasaja: d tegese sembarang digit, lan quantifier + ngandika sing nomer kudu siji utawa luwih. Minus pindho ing ngarepe fungsi dibutuhake kanggo "ing fly" Ngonversi karakter sing dijupuk menyang nomer lengkap saka nomer-minangka-teks.
Kode pos
Sepisanan, kabeh prasaja ing kene - kita nggoleki persis enem digit saurutan. Kita nggunakake karakter khusus d kanggo digit lan quantifier 6 {} kanggo jumlah karakter:
Nanging, kahanan bisa uga nalika, ing sisih kiwa indeks ing baris, ana set nomer akeh liyane ing saurutan (nomer telpon, TIN, akun bank, lan sapiturute) Banjur musim reguler kita bakal narik metu 6 pisanan. digit saka iku, IE ora bakal bisa digunakake kanthi bener:
Kanggo nyegah kedadeyan kasebut, kita kudu nambah modifier ing pinggiran ekspresi reguler b negesi pungkasaning tembung. Iki bakal nerangake Excel yen fragmen (indeks) sing dibutuhake kudu dadi tembung sing kapisah, lan dudu bagean saka fragmen liyane (nomer telpon):
Telpon
Masalah nemokake nomer telpon ing teks yaiku akeh pilihan kanggo nulis nomer - kanthi lan tanpa tanda hubung, liwat spasi, nganggo utawa tanpa kode wilayah ing kurung, lan liya-liyane. Mulane, miturut pendapatku, luwih gampang pisanan ngresiki kabeh karakter iki saka teks sumber nggunakake sawetara fungsi nested SUBSTITUT (GANTIKAN)supaya tetep bebarengan dadi siji wutuh, banjur karo biasa primitif d{11} tarik metu 11 digit saurutan:
ITN
Luwih rumit ing kene, amarga TIN (ing Negara Kita) bisa dadi 10 digit (kanggo entitas legal) utawa 12 digit (kanggo individu). Yen sampeyan ora nemokake fault utamané, iku cukup bisa kanggo wareg karo biasa d{10,12}, nanging, strictly ngandika, iku bakal narik metu kabeh nomer saka 10 kanggo 12 karakter, IE lan erroneously ngetik 11 digit. Bakal luwih bener nggunakake rong pola sing disambungake dening operator UTAWA logis | (bar vertikal):
Wigati dimangerteni menawa ing pitakonan kita pisanan nggoleki nomer 12-bit, banjur mung kanggo nomer 10-bit. Yen kita nulis ekspresi reguler kanthi cara liya, mula bakal metu kanggo kabeh wong, sanajan TIN 12-bit dawa, mung 10 karakter pisanan. Yaiku, sawise kondisi pisanan dipicu, verifikasi luwih lanjut ora ditindakake:
Iki prabédan dhasar antarane operator | saka fungsi logika excel standar OR (UTAWA), ing ngendi nyusun ulang argumen ora ngganti asil.
SKU Produk
Ing akeh perusahaan, pengenal unik ditugasake kanggo barang lan layanan - artikel, kode SAP, SKU, lan liya-liyane. Contone, yen kita ngerti manawa artikel kita mesthi kalebu telung huruf Inggris gedhe, tandha hubung lan nomer telung digit sabanjure, mula:
Logika konco cithakan punika prasaja. [AZ] – tegese sembarang huruf kapital saka aksara Latin. Kuantifier sabanjure 3 {} ngandika sing iku penting kanggo kita sing ana persis telung huruf kuwi. Sawise hyphen, kita ngenteni telung digit, supaya kita nambah ing mburi d{3}
Jumlah awis
Kanthi cara sing padha karo paragraf sadurunge, sampeyan uga bisa narik rega (biaya, PPN ...) saka katrangan barang. Yen jumlah moneter, contone, dituduhake nganggo tanda hubung, banjur:
pola d karo quantifier + nggoleki sembarang nomer nganti hyphen, lan d{2} bakal nggoleki dhuwit recehan (rong digit) sawise.
Yen sampeyan kudu ngekstrak dudu rega, nanging PPN, sampeyan bisa nggunakake argumen opsional katelu saka fungsi RegExpExtract, sing nemtokake nomer ordinal unsur sing bakal diekstrak. Lan, mesthi, sampeyan bisa ngganti fungsi kasebut SUBSTITUT (GANTIKAN) ing asil, hyphen menyang pemisah desimal standar lan nambah minus pindho ing wiwitan supaya Excel nerjemahake PPN sing ditemokake minangka nomer normal:
Nomer plat mobil
Yen sampeyan ora njupuk kendaraan khusus, Trailer lan motor liyane, nomer mobil standar wis parsed miturut prinsip "huruf - telung nomer - loro huruf - kode wilayah". Kajaba iku, kode wilayah bisa dadi 2 utawa 3 digit, lan mung sing katon padha karo aksara Latin sing digunakake minangka huruf. Dadi, ekspresi reguler ing ngisor iki bakal mbantu kita ngekstrak nomer saka teks kasebut:
wektu
Kanggo ngekstrak wektu ing format HH:MM, ekspresi reguler ing ngisor iki cocok:
Sawise pecahan kolon [0-5]d, minangka gampang kanggo tokoh metu, nyetel sembarang nomer ing sawetara 00-59. Sadurunge titik loro ing kurung, rong pola bisa digunakake, dipisahake dening OR logis (pipa):
- [0-1]d – sembarang nomer ing sawetara 00-19
- 2[0-3] – sembarang nomer ing sawetara 20-23
Kanggo asil sing dipikolehi, sampeyan uga bisa nggunakake fungsi Excel standar TIME (TIM)Ngonversi menyang format wektu sing bisa dingerteni kanggo program lan cocok kanggo petungan luwih.
Priksa sandhi
Upamane kita kudu mriksa dhaptar sandhi sing diciptakake pangguna kanggo bener. Miturut aturan kita, sandhi mung bisa ngemot huruf Inggris (huruf cilik utawa gedhe) lan angka. Spasi, garis ngisor lan tandha wacan liyane ora diidini.
Priksa bisa diatur nggunakake ekspresi reguler prasaja ing ngisor iki:
Nyatane, kanthi pola kasebut kita mbutuhake antarane wiwitan (^) lan pungkasan ($) ing teks kita mung ana karakter saka set diwenehi ing kurung kothak. Yen sampeyan uga kudu mriksa dawa sandi (contone, paling 6 karakter), banjur quantifier + bisa diganti dening interval "enem utawa luwih" ing wangun {6,}:
Kutha saka alamat
Ayo kita ngomong kita kudu narik kutha saka baris alamat. Program biasa bakal mbantu, ngekstrak teks saka "g." menyang koma sabanjure:
Ayo dideleng kanthi cetha babagan pola iki.
Yen sampeyan wis maca teks ing ndhuwur, sampeyan wis ngerti manawa sawetara karakter ing ekspresi reguler (periode, tanda bintang, tandha dolar, lan liya-liyane) duwe makna khusus. Yen sampeyan kudu nggoleki karakter kasebut dhewe, banjur didhisiki dening backslash (kadhangkala disebut tameng). Mulane, nalika nggoleki fragmen "g." kita kudu nulis ing expression biasa Mr. yen kita looking for a plus, banjur + etc.
Loro karakter sabanjure ing cithakan kita, titik lan tanda bintang quantifier, stands kanggo nomer sembarang karakter, IE sembarang jeneng kutha.
Ana koma ing mburi cithakan, amarga kita nggoleki teks saka "g." menyang koma. Nanging bisa uga ana sawetara koma ing teks, ta? Ora mung sawise kutha, nanging uga sawise dalan, omah, lan liya-liyane. Kuwi gunane tandha pitakon. Tanpa iku, ekspresi reguler kita bakal narik string paling dawa:
Ing babagan ekspresi reguler, pola kasebut "rakus". Kanggo mbenerake kahanan kasebut, tandha pitakon dibutuhake - iku ndadekake pangukuran sawise "pelit" - lan pitakon kita mung njupuk teks nganti koma counter pisanan sawise "g.":
Jeneng berkas saka path lengkap
Kahanan liyane sing umum yaiku ngekstrak jeneng file saka path lengkap. Ekspresi reguler prasaja saka formulir bakal mbantu ing kene:
Trik ing kene yaiku panelusuran, nyatane, ana ing arah sing ngelawan - saka pungkasan nganti wiwitan, amarga ing pungkasan cithakan kita $, lan kita lagi looking for kabeh sadurunge iku kanggo backslash pisanan saka tengen. Garis miring mundur bisa lolos, kaya titik ing conto sadurunge.
PS
"Menyang pungkasan" Aku pengin njlentrehake yen kabeh ing ndhuwur minangka bagean cilik saka kabeh kemungkinan sing nyedhiyakake ekspresi biasa. Ana akeh karakter khusus lan aturan kanggo nggunakake, lan kabeh buku wis ditulis ing topik iki (Aku nyaranake paling siji iki kanggo wiwitan). Ing cara, nulis ekspresi reguler meh minangka seni. Meh tansah, ekspresi reguler sing diciptakake bisa ditambah utawa ditambah, dadi luwih elegan utawa bisa nggarap data input sing luwih akeh.
Kanggo nganalisa lan ngurai ekspresi reguler wong liya utawa debug dhewe, ana sawetara layanan online sing trep: RegEx101, RegExr lan liyane
Sayange, ora kabeh fitur saka ekspresi reguler klasik didhukung ing VBA (contone, telusuran mbalikke utawa kelas POSIX) lan bisa digunakake karo Cyrillic, nanging aku sing apa ana cukup kanggo pisanan kanggo please sampeyan.
Yen sampeyan ora anyar kanggo topik, lan sampeyan duwe soko kanggo nuduhake, ninggalake ungkapan biasa migunani nalika digunakake ing Excel ing komentar ing ngisor iki. Siji pikiran apik, nanging loro boots minangka pasangan!
- Ngganti lan ngresiki teks nganggo fungsi SUBSTITUTE
- Telusuri lan nyorot aksara Latin ing teks
- Telusuri teks sing paling cedhak (Ivanov = Ivonov = Ivanof, lsp.)