Pengesahan Data Excel Menggunakan Ekspresi Biasa (REGEX)

Tutorial menunjukkan cara melakukan pengesahan data dalam Excel menggunakan ekspresi biasa dengan bantuan fungsi regexmatch tersuai.

Ketika datang untuk menyekat input pengguna dalam lembaran kerja Excel, pengesahan data sangat diperlukan. Ingin hanya membenarkan nombor atau tarikh dalam sel tertentu? Atau hadkan nilai teks ke panjang tertentu? Atau mungkin tidak membenarkan masa di luar julat yang diberikan? Tiada masalah, semua ini boleh dilakukan dengan mudah dengan kriteria pengesahan pratetap atau adat. Tetapi bagaimana jika saya mahu hanya membenarkan alamat e -mel atau rentetan yang sah yang sepadan dengan corak tertentu? Malangnya, itu tidak mungkin. Regex anda katakan? Hmm ... yang mungkin berfungsi!

• Cara Melakukan Pengesahan Data Excel dengan Regex
• Regex tersuai untuk mengesahkan rentetan teks
• Regex untuk mengesahkan alamat e -mel
• Regex untuk mengesahkan kata laluan

Cara Melakukan Pengesahan Data Excel dengan Regex

Malangnya, tidak ada ciri-ciri Excel terbina dalam yang menyokong regexes, dan pengesahan data tidak terkecuali. Untuk dapat mengesahkan input sel menggunakan ungkapan biasa, anda perlu membuat fungsi regex tersuai terlebih dahulu. Satu lagi komplikasi ialah fungsi yang ditentukan oleh pengguna VBA tidak dapat disampaikan kepada pengesahan data secara langsung - anda memerlukan pengantara dalam bentuk formula yang dinamakan.

Memandangkan perkara di atas, marilah kita menggariskan langkah -langkah yang akan diambil untuk mengesahkan data dalam Excel menggunakan regexes:

1. Buat fungsi Regex tersuai yang memeriksa sama ada nilai input sepadan dengan ungkapan biasa.
2. Tentukan nama untuk formula regex anda.
3. Konfigurasikan peraturan pengesahan data berdasarkan formula yang dinamakan.
4. Salin tetapan pengesahan ke seberapa banyak sel yang anda mahukan.

Terdengar seperti rancangan? Mari cuba melaksanakannya dalam amalan!

Pengesahan data Excel menggunakan ekspresi biasa tersuai

Contoh ini menangani kes yang sangat biasa - bagaimana untuk membenarkan hanya nilai corak tertentu.

Katakan anda menyimpan beberapa kod SKU dalam lembaran kerja anda dan ingin memastikan bahawa hanya kod yang sepadan dengan corak yang diberikan masuk ke dalam senarai. Dengan syarat bahawa setiap SKU terdiri daripada 2 kumpulan watak yang dipisahkan dengan tanda hubung, kumpulan pertama termasuk 3 huruf besar dan kumpulan kedua - 3 digit, anda boleh mengenal pasti nilai -nilai tersebut menggunakan regex di bawah.

Corak : ^[az] {3}-\ d {3} $

Sila ambil perhatian bahawa permulaan (^) dan akhir ($) rentetan itu berlabuh, jadi tidak ada aksara selain daripada corak yang boleh dimasukkan ke dalam sel.

1. Tambahkan fungsi perlawanan regex tersuai

Mulakan dengan memasukkan fungsi regexpmatch dalam buku kerja anda. Kod ini sudah ditulis oleh Gurus Excel kami, jadi anda hanya perlu menyalinnya dari halaman dan tampal di atas di editor VBA anda.

Inilah sintaks fungsi untuk rujukan anda:

Regexpmatch (teks, corak, [match_case])

Di mana:

• Teks (diperlukan) - rentetan sumber (dalam konteks kami - sel yang disahkan).
• Corak (diperlukan) - Ungkapan biasa untuk dipadankan.
• Match_case (pilihan) - Jenis perlawanan. Benar atau ditinggalkan - kes sensitif; FALSE - Case -Insensitive.

Petua. Jika anda adalah pengguna suite muktamad kami, maka anda boleh melakukan pengesahan data REGEX dalam Excel tanpa menambah sebarang kod VBA ke buku kerja anda. Hanya memanfaatkan fungsi Custom AbebitsRegexMatch yang disertakan dengan alat Regex kami.

2. Buat formula bernama

Dalam lembaran kerja sasaran anda, pilih Cell A1 (tanpa mengira kandungannya dan tidak kira sel mana yang anda sebenarnya akan mengesahkan), tekan Ctrl F3 untuk membuka Nama Pengurus, dan tentukan nama untuk formula ini:

=RegExpMatch(Sheet1!A1, "^[AZ]{3}-\d{3}$")

Atau anda boleh memasukkan regex dalam beberapa sel (A2 dalam contoh ini) dan membekalkan $ A $ 2 untuk hujah kedua:

=RegExpMatch(Sheet1!A1, Sheet1!$A$2)

Untuk formula berfungsi dengan betul, pastikan anda menggunakan rujukan relatif untuk hujah teks (A1) dan rujukan mutlak untuk corak ($ ​​A $ 2).

Memandangkan formula kami berhasrat untuk mengesahkan nombor SKU, kami namakannya dengan sewajarnya: validate_sku .

Nota penting! Apabila menentukan formula, sila semak semula bahawa hujah pertama merujuk kepada sel yang dipilih sekarang , jika tidak formula tidak akan berfungsi. Sebagai contoh, jika sel A1 dipilih pada lembaran, letakkan A1 dalam hujah pertama (seperti cadangan kami); Jika B2 dipilih, kemudian gunakan B2 untuk hujah pertama, dan sebagainya. Ia tidak semestinya rujukan khusus yang anda gunakan selagi ia sepadan dengan sel yang dipilih sekarang.

Untuk arahan langkah demi langkah, sila lihat cara membuat formula bernama dalam Excel.

3. Sediakan Pengesahan Data

Pilih sel pertama yang akan diperiksa (A5 dalam kes kami) dan membuat peraturan pengesahan data tersuai berdasarkan formula yang dinamakan. Untuk ini, lakukan perkara berikut:

1. Klik Tab Data > Pengesahan Data .
2. Dalam senarai drop-down yang dibenarkan , pilih Custom .
3. Masukkan formula di bawah dalam kotak yang sepadan.

   =Validate_SKU

4. Biasa pilihan kosong , jika tidak, peraturan anda tidak akan berfungsi.

Secara pilihan, anda boleh menaip mesej ralat tersuai untuk dipaparkan apabila data tidak sah dimasukkan ke dalam sel.

Jika anda berasa seperti anda memerlukan langkah -langkah terperinci, di sini anda pergi: bagaimana untuk menyediakan pengesahan data tersuai dalam Excel.

4. Salin pengesahan data ke lebih banyak sel

Untuk menyalin tetapan pengesahan ke lebih banyak sel, inilah yang perlu anda lakukan:

1. Pilih sel dengan pengesahan data dan tekan Ctrl C untuk menyalinnya.
2. Pilih sel-sel lain yang anda ingin sahkan, klik kanannya, klik Tampal Khas , dan pilih pilihan pengesahan .
3. Klik OK .

   

Maklumat lanjut boleh didapati dalam cara menyalin pengesahan data.

Sekarang, setiap kali seseorang cuba memasuki SKU yang tidak sah di mana -mana sel yang disahkan, mesej amaran berikut akan muncul:

Pengesahan e -mel dengan regex

Untuk melakukan pengesahan e -mel, anda mulakan dengan menulis ungkapan biasa yang sepadan dengan alamat e -mel.

Corak : ^[\ w \. \-] @[a-z0-9] [a-z0-9 \. \-]*[a-z0-9] \. [A-za-z] {2,24} $

Untuk penjelasan terperinci mengenai sintaks, sila lihat Regex untuk memadankan alamat e -mel yang sah.

Dan sekarang, tentukan kriteria pengesahan dengan melaksanakan langkah -langkah yang sudah biasa:

1. Masukkan regex di atas dalam b2.
2. Pilih sel A1 dan tentukan nama yang dipanggil Validate_email yang merujuk kepada:

   =RegExpMatch(Sheet1!A1, Sheet1!$B$2)

   

3. Untuk sel B5, gunakan pengesahan data tersuai menggunakan formula di bawah. Adalah penting bahawa pilihan kosong tidak boleh dipilih.

   =Validate_Email

   

   Di samping itu, anda boleh mengkonfigurasi mesej ralat tersuai yang mendorong pengguna memasukkan alamat e -mel yang sah.

4. Salin peraturan ke sel di bawah.

Jika alamat e -mel yang anda masukkan dalam sel yang disahkan tidak sepadan dengan corak regex, amaran berikut akan muncul:

Mengesahkan kata laluan menggunakan ungkapan biasa

Apabila menggunakan Regex untuk pengesahan kata laluan, perkara pertama yang ditentukan adalah tepat apa yang perlu diperiksa oleh ekspresi biasa anda. Berikut adalah beberapa contoh yang mungkin menjadikan anda di landasan yang betul.

Kata laluan mestilah sekurang -kurangnya 6 aksara dan hanya boleh mengandungi huruf (huruf besar atau huruf kecil) dan digit:

Corak : ^[A-Za-Z0-9] {6,} $

Kata laluan mestilah minimum 6 aksara dan memasukkan sekurang -kurangnya satu huruf dan satu digit:

Corak : ^(? =.*[A-za-z]) (? =.*\ D) [a-za-z \ d] {6,} $

Kata laluan mestilah min 6 aksara dan memasukkan sekurang -kurangnya satu huruf besar, satu huruf kecil dan satu digit:

Corak : ^(? =.*[Az]) (? =.*[Az]) (? =.*\ D) [a-za-z \ d] {6,} $

Kata laluan mestilah min 6 aksara dan memasukkan sekurang -kurangnya satu huruf, satu digit dan satu watak khas:

Corak : ^(? =.*[A-za-z]) (?

Dengan corak yang ditetapkan, anda boleh meneruskan untuk menyediakan pengesahan data:

1. Masukkan kata laluan anda di C2.
2. Pilih sel A1 dan buat formula bernama yang dipanggil Validate_Password :

   =RegExpMatch(Sheet1!A1, Sheet1!$C$2)

   

3. Untuk sel C5, buat peraturan pengesahan tersuai dengan formula di bawah. Ingatlah untuk melepaskan kotak semak kosong .

   =Validate_Password

   

4. Salin peraturan ke seberapa banyak sel yang anda mahukan.

Sekarang, anda boleh menambah kata laluan baru ke senarai dengan selamat. Jika rentetan input tidak sepadan dengan regex, amaran berikut akan mengingatkan anda apa jenis nilai yang diterima:

Pengesahan data regex tidak berfungsi

Jika pengesahan data REGEX tidak berfungsi dalam Excel anda, kemungkinan besar ia adalah kerana salah satu sebab berikut.

Fungsi regexpmatch hilang

Sebelum menggunakan pengesahan data, pastikan anda memasukkan kod fungsi regexpmatch dalam buku kerja anda.

Ungkapan biasa yang tidak betul

Untuk memastikan regex anda berfungsi seperti yang diharapkan, anda boleh memasukkan formula regexpmatch dalam beberapa sel dan memeriksa hasilnya. Untuk maklumat lanjut, sila lihat Excel Expression Expression yang sesuai dengan contoh.

Untuk menganalisis dan debug ungkapan biasa anda, anda boleh menggunakan perkhidmatan ujian Regex dalam talian percuma seperti Regex101 atau Regexr.

Formula bernama yang salah

Sebab yang sangat umum mengenai kegagalan pengesahan data adalah formula bernama regex merujuk kepada sel yang salah. Dalam semua contoh, kami mencadangkan untuk menentukan formula yang merujuk kepada A1:

=RegExpMatch(A1, regex)

Ini hanya berfungsi jika sel A1 aktif apabila menentukan nama dan rujukan relatif (tanpa tanda $) digunakan.

Idea ini ialah rujukan relatif yang dinyatakan dalam formula (A1) secara automatik akan berubah berdasarkan kedudukan relatif sel yang disahkan. Dalam erti kata lain, Cell A1 dipilih hanya untuk kemudahan dan konsistensi. Malah, anda boleh memilih sel B1 dan merujuk kepada B1, pilih C1 C1 dan rujuk C1, dan sebagainya. Perkara utama ialah sel yang dirujuk harus sel aktif .

Untuk memeriksa sama ada formula bernama anda betul, pilih mana -mana sel di lembaran kerja anda, buka Pengurus Nama, dan lihat sel mana formula menunjuk. Jika ia merujuk kepada sel yang sedang dipilih, formula itu betul. Jika tidak, anda harus mengubah rujukan dalam hujah pertama.

Dalam tangkapan skrin di bawah, sel A7 dipilih, yang bermaksud formula bernama harus mempunyai A7 dalam hujah pertama. Hujah kedua ($ a $ 2) merujuk kepada regex - rujukan ini sepatutnya tetap tetap, jadi ia terkunci dengan tanda $.

Abaikan pilihan kosong dipilih

Apabila menyediakan peraturan pengesahan data tersuai, adalah penting untuk melepaskan kotak semak kosong . Jika tidak, peraturan itu tidak akan berfungsi kerana sebab berikut:

Sekiranya perlawanan tidak dijumpai, fungsi regexpmatch mengembalikan palsu. Dengan pilihan kosong yang diabaikan , palsu sama dengan kosong dan diabaikan.

Penyelesaian alternatif secara jelas menyatakan bahawa formula harus kembali benar:

=RegExpMatch(…)=TRUE

Itulah cara melakukan pengesahan data dalam Excel menggunakan ungkapan biasa. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Contoh Pengesahan Data Regex (fail .xlsm)

Atas ialah kandungan terperinci Pengesahan Data Excel Menggunakan Ekspresi Biasa (REGEX). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!