Excel -Datenvalidierung mit regulären Ausdrücken (REGEX)

Das Tutorial zeigt, wie die Datenvalidierung in Excel mit regelmäßigen Ausdrücken mit Hilfe einer benutzerdefinierten RegexMatch -Funktion durchführt.

Wenn es darum geht, die Benutzereingaben in Excel -Arbeitsblättern einzuschränken, ist die Datenvalidierung unverzichtbar. Möchten Sie nur Zahlen oder Daten in einer bestimmten Zelle zulassen? Oder Textwerte auf eine bestimmte Länge begrenzen? Oder vielleicht Zeiten außerhalb eines bestimmten Bereichs nicht zulassen? Kein Problem, all dies kann einfach mit voreingestellten oder benutzerdefinierten Validierungskriterien erfolgen. Aber was ist, wenn ich nur gültige E -Mail -Adressen oder Zeichenfolgen zulassen möchte, die mit einem bestimmten Muster übereinstimmen? Leider ist das nicht möglich. Regex sagst du? Hmm ... das könnte funktionieren!

• So führen Sie die Excel -Datenvalidierung mit Regex durch
• Benutzerdefinierte Regex zur Überprüfung von Textzeichenfolgen
• Regex zur Überprüfung von E -Mail -Adressen
• Regex zur Überprüfung von Passwörtern

So führen Sie die Excel -Datenvalidierung mit Regex durch

Bedauerlicherweise unterstützt keiner der integrierten Excel-Funktionen Regexes, und die Datenvalidierung ist keine Ausnahme. Um die Zelleingabe mit regelmäßigen Ausdrücken validieren zu können, müssen Sie zuerst eine benutzerdefinierte Regex -Funktion erstellen. Eine weitere Komplikation ist, dass die von VBA -benutzerdefinierten Funktionen nicht direkt der Datenvalidierung zugestellt werden können - Sie benötigen einen Mediator in Form einer benannten Formel.

Wenn wir die oben genannten berücksichtigen, skizzieren wir kurz die Schritte, die zur Validierung von Daten in Excel mithilfe von Regexes unternommen werden sollen:

1. Erstellen Sie eine benutzerdefinierte REGEX -Funktion, mit der überprüft wird, ob ein Eingangswert einem regulären Ausdruck übereinstimmt.
2. Definieren Sie einen Namen für Ihre Regex -Formel.
3. Konfigurieren Sie eine Datenvalidierungsregel basierend auf der benannten Formel.
4. Kopieren Sie die Validierungseinstellungen in so viele Zellen, wie Sie möchten.

Klingt nach einem Plan? Versuchen wir, es in der Praxis umzusetzen!

Excel -Datenvalidierung mit benutzerdefinierten regulären Ausdrücken

Dieses Beispiel befasst sich mit einem sehr häufigen Fall - wie nur die Werte eines bestimmten Musters zuzulassen.

Angenommen, Sie haben einige SKU -Codes in Ihrem Arbeitsblatt und möchten sicher sein, dass nur die Codes, die mit einem bestimmten Muster übereinstimmen, in die Liste einbeziehen. Vorausgesetzt, dass jede SKU aus 2 Gruppen von Zeichen besteht, die mit einem Bindestrich, der ersten Gruppe, einschließlich 3 Großbuchstaben und der zweiten Gruppe - 3 Ziffern, identifizieren können, können Sie solche Werte mithilfe der folgenden Regex identifizieren.

Muster : ^[Az] {3}-\ d {3} $

Bitte beachten Sie, dass der Start (^) und das Ende ($) der Zeichenfolge verankert sind, sodass keine anderen Zeichen als im Muster in eine Zelle eingegeben werden können.

1. Fügen Sie eine benutzerdefinierte Regex -Spielfunktion hinzu

Beginnen Sie mit dem Einfügen der RegexpMatch -Funktion in Ihrem Arbeitsbuch. Der Code ist bereits von unserem Excel-Gurus geschrieben, sodass Sie ihn nur von der oben gebliebenen Seite kopieren und in Ihren VBA-Editor einfügen müssen.

Hier ist die Syntax der Funktion für Ihre Referenz:

Regexpmatch (Text, Muster, [match_case])

Wo:

• Text (erforderlich) - eine Quellzeichenfolge (in unserem Kontext - eine validierte Zelle).
• Muster (erforderlich) - Ein regelmäßiger Ausdruck, der übereinstimmt.
• Match_case (optional) - Übereinstimmungstyp. Wahr oder weggelassen - Fallempfindlich; Falsch - Fall -unempfindlich.

Tipp. Wenn Sie ein Benutzer unserer ultimativen Suite sind, können Sie in Excel eine Regex -Datenvalidierung durchführen, ohne Ihren Arbeitsmappen VBA -Code hinzuzufügen. Nutzen Sie einfach eine kundenspezifische FähigkeitsregexMatch -Funktion, die in unseren Regex -Tools enthalten ist.

2. Erstellen Sie eine benannte Formel

Wählen Sie in Ihrem Zielarbeitsblatt Cell A1 (unabhängig von seinem Inhalt und unabhängig davon, welche Zelle Sie tatsächlich validieren werden) Strg F3 , um den Namensmanager zu öffnen, und definieren Sie einen Namen für diese Formel:

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

Oder Sie können die Regex in einer Zelle (A2 in diesem Beispiel) eingeben und 2 US -Dollar für das zweite Argument liefern:

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

Verwenden Sie eine relative Referenz für das Textargument (A1) und die absolute Referenz für Muster ($ a $ 2), damit die Formel korrekt funktioniert.

Angesichts der Tatsache, dass unsere Formel für die Validierung von SKU -Nummern beabsichtigt ist, nennen wir sie entsprechend: validate_sku .

Wichtiger Hinweis! Wenn Sie die Formel definieren, überprüfen Sie bitte, ob sich das erste Argument auf die aktuell ausgewählte Zelle bezieht , andernfalls funktioniert die Formel nicht. Wenn beispielsweise Zelle A1 auf dem Blatt ausgewählt ist, setzen Sie A1 in das erste Argument (gemäß unseren Empfehlungen); Wenn B2 ausgewählt ist, verwenden Sie B2 für das erste Argument und so eine. Es spielt keine Rolle, welche spezielle Referenz Sie verwenden, solange sie mit der aktuell ausgewählten Zelle übereinstimmt.

Für Schritt-für-Schritt-Anweisungen finden Sie in Excel eine benannte Formel.

3. Einrichten der Datenvalidierung

Wählen Sie die erste Zelle aus (in unserem Fall A5) und erstellen Sie basierend auf der benannten Formel eine benutzerdefinierte Datenvalidierungsregel. Dafür machen Sie Folgendes:

1. Klicken Sie auf die Registerkarte Daten > Datenvalidierung .
2. Wählen Sie in der Dropdown -Liste der Dropdown-Liste die Benutzerdefinierte aus.
3. Geben Sie die folgende Formel in das entsprechende Feld ein.

   =Validate_SKU

4. Die Option von leeren Ignorieren ignorieren, andernfalls funktioniert Ihre Regel nicht.

Optional können Sie eine benutzerdefinierte Fehlermeldung eingeben, die angezeigt werden soll, wenn ungültige Daten in eine Zelle eingegeben werden.

Wenn Sie das Gefühl haben, die detaillierten Schritte zu benötigen, finden Sie hier: So richten Sie die benutzerdefinierte Datenvalidierung in Excel ein.

4. Kopieren Sie die Datenvalidierung in mehr Zellen

Um die Validierungseinstellungen in mehr Zellen zu kopieren, müssen Sie dies tun:

1. Wählen Sie die Zelle mit Datenvalidierung aus und drücken Sie Strg C , um sie zu kopieren.
2. Wählen Sie andere Zellen aus, die Sie validieren möchten, klicken Sie mit der rechten Maustaste auf sie, klicken Sie auf Special einfügen und wählen Sie die Validierungsoption .
3. Klicken Sie auf OK .

   

Weitere Informationen finden Sie in der Kopie der Datenvalidierung.

Jedes Mal, wenn jemand versucht, in einem der validierten Zellen eine ungültige SKU einzugeben, wird die folgende Warnmeldung angezeigt:

E -Mail -Validierung mit Regex

Um eine E -Mail -Validierung durchzuführen, beginnen Sie mit dem Schreiben eines regulären Ausdrucks, der einer E -Mail -Adresse entspricht.

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

Die detaillierte Erläuterung der Syntax finden Sie in Regex, um gültige E -Mail -Adressen anzupassen.

Geben Sie nun die Validierungskriterien an, indem Sie die bereits bekannten Schritte ausführen:

1. Geben Sie die obige Regex in B2 ein.
2. Wählen Sie Cell A1 aus und definieren Sie einen Namen namens validate_email , der sich auf:

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

   

3. Wenden Sie für Zelle B5 benutzerdefinierte Datenvalidierung mit der folgenden Formel an. Es ist wichtig, dass die Option von Ignoring Leere nicht ausgewählt werden sollte.

   =Validate_Email

   

   Darüber hinaus können Sie eine benutzerdefinierte Fehlermeldung konfigurieren, die den Benutzer dazu auffordert, eine gültige E -Mail -Adresse einzugeben.

4. Kopieren Sie die Regel in die folgenden Zellen.

Wenn eine E -Mail -Adresse, die Sie in eine validierte Zelle eingeben, nicht mit einem Regex -Muster übereinstimmt, wird der folgende Alarm angezeigt:

Validierung von Passwörtern mit regulären Ausdrücken

Bei der Verwendung von Regex zur Passwortvalidierung ist das erste, was Sie entscheiden sollten, genau das, was Ihr regulärer Ausdruck überprüfen sollte. Hier sind einige Beispiele, die Sie möglicherweise auf dem richtigen Weg setzen.

Ein Passwort muss mindestens 6 Zeichen lang sein und kann nur Buchstaben (Großbuchstaben oder Kleinbuchstaben) und Ziffern enthalten:

Muster : ^[a-za-z0-9] {6,} $

Ein Passwort muss mindestens 6 Zeichen lang sein und mindestens einen Buchstaben und eine Ziffer enthalten:

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

Ein Passwort muss mindestens 6 Zeichen lang sein und mindestens einen Großbuchstaben, einen Kleinbuchstaben und eine Ziffer enthalten:

Muster : ^(? =.*[Az]) (? =.*[Az]) (?

Ein Passwort muss minar 6 Zeichen lang sein und mindestens einen Buchstaben, eine Ziffer und einen besonderen Charakter enthalten:

Muster : ^(? =.*[A-za-z]) (? =.*\ D) (? =.*[@$!%*#?

Mit dem festgelegten Muster können Sie die Datenvalidierung einrichten:

1. Geben Sie Ihr Passwort Regex in C2 ein.
2. Wählen Sie Cell A1 und erstellen Sie eine benannte Formel namens Validate_Password :

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

   

3. Erstellen Sie für Zelle C5 eine benutzerdefinierte Validierungsregel mit der folgenden Formel. Denken Sie daran, das Kontrollkästchen Blank ignorieren zu können.

   =Validate_Password

   

4. Kopieren Sie die Regel in so viele Zellen, wie Sie möchten.

Jetzt können Sie der Liste sicher neue Passwörter hinzufügen. Wenn eine Eingangszeichenfolge nicht mit dem Regex übereinstimmt, erinnert Sie die folgende Warnung daran, welche Arten von Werten akzeptiert werden:

Regex -Datenvalidierung funktioniert nicht

Wenn die REGEX -Datenvalidierung in Ihrem Excel nicht funktioniert, liegt sie höchstwahrscheinlich aus einem der folgenden Gründe.

RegexpMatch -Funktion fehlen

Stellen Sie vor der Anwendung der Datenvalidierung den Code der RegexpMatch -Funktion in Ihre Arbeitsmappe ein.

Falscher regulärer Ausdruck

Um sicherzustellen, dass Ihr Regex wie erwartet funktioniert, können Sie eine RegexpMatch -Formel in einer Zelle eingeben und die Ergebnisse untersuchen. Weitere Informationen finden Sie in Excel Regular Expressions Matching mit Beispielen.

Um Ihre regulären Ausdrücke zu analysieren und zu debuggen, können Sie kostenlose Online -REGEX -Testdienste wie REGEX101 oder REGEXR verwenden.

Falsch benannte Formel

Ein sehr häufiger Grund für die Daten zur Datenvalidierung ist eine Regex -Namensformel, die sich auf eine falsche Zelle bezieht. In allen Beispielen haben wir empfohlen, eine Formel zu definieren, die sich auf A1 bezieht:

=RegExpMatch(A1, regex)

Dies funktioniert nur, wenn die Zelle A1 bei der Definition eines Namens aktiv ist und eine relative Referenz (ohne $ sign) verwendet wird.

Die Idee ist, dass sich eine in der Formel (A1) angegebene relative Referenz basierend auf der relativen Position der validierten Zelle automatisch ändert. Mit anderen Worten, Cell A1 wird nur aus Bequemlichkeit und Konsistenz ausgewählt. Tatsächlich können Sie Zelle B1 auswählen und sich auf B1 beziehen, Zelle C1 auswählen und auf C1 usw. Der Schlüssel ist, dass die referenzierte Zelle die aktive Zelle sein sollte.

Um zu überprüfen, ob Ihre benannte Formel korrekt ist, wählen Sie eine Zelle in Ihrem Arbeitsblatt aus, öffnen Sie den Namensmanager und sehen Sie, auf welche Zelle die Formel zeigt. Wenn es sich auf die aktuell ausgewählte Zelle bezieht, ist die Formel richtig. Andernfalls sollten Sie die Referenz im ersten Argument ändern.

Im folgenden Screenshot wird Zelle A7 ausgewählt, was bedeutet, dass eine benannte Formel A7 im ersten Argument haben sollte. Das zweite Argument ($ a $ 2) bezieht sich auf den Regex - diese Referenz soll konstant bleiben, daher ist es mit dem $ sign gesperrt.

Leere Option Ignorieren Sie ausgewählt

Bei der Einrichtung einer benutzerdefinierten Datenvalidierungsregel ist es wichtig, das Kontrollkästchen Blank ignorieren zu können. Andernfalls funktioniert die Regel aufgrund des folgenden Grundes nicht:

Wenn keine Übereinstimmung gefunden wird, gibt die RegexpMatch -Funktion false zurück. Mit der ausgewählten Option ignorieren Sie leere Option, ist FALSE auf leer und wird ignoriert.

Eine alternative Lösung ist explizit, dass die Formel wahr zurückkehren sollte:

=RegExpMatch(…)=TRUE

So führen Sie die Datenvalidierung in Excel mit regelmäßigen Ausdrücken durch. Ich danke Ihnen für das Lesen und freue mich darauf, Sie nächste Woche in unserem Blog zu sehen!

Üben Sie die Arbeitsmappe zum Download

Beispiele für Regex -Datenvalidierung (.xlsm -Datei)

Das obige ist der detaillierte Inhalt vonExcel -Datenvalidierung mit regulären Ausdrücken (REGEX). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!