Daten in Excel prüfen, wie man eine Liste ändert. Excel. Datenprüfung


Bedingte Formatierung (5)
Listen und Bereiche (5)
Makros (VBA-Prozeduren) (63)
Verschiedenes (39)
Excel-Fehler und -Störungen (3)

Data Validation ist ein gutes Excel-Tool zum Überwachen von an Tabellen vorgenommenen Änderungen, ohne auf VBA zurückgreifen zu müssen. Mit seiner Hilfe können Sie die Eingabe in eine Zelle einschränken, sodass Sie nur Datums- und Uhrzeitangaben oder nur Zahlen eingeben können. Darüber hinaus können Sie auch einen Datumsbereich oder eine Zahlenbeschränkung festlegen (z. B. von 1 bis 10). Ich denke, Sie können sich viele Anwendungen einfallen lassen: Damit viele Formeln korrekt funktionieren, sind korrekte Quelldaten erforderlich. Deshalb können wir verwenden Datenprüfungen Erlauben Sie dem Benutzer, nur den Datentyp und -bereich einzugeben, den die Formel verarbeiten kann, ohne einen Fehlerwert zurückzugeben. Lass uns genauer hinschauen.

Dieses wunderbare Tool befindet sich: Daten-. Es sollte ein Fenster erscheinen:

Zuerst müssen Sie die Zelle (oder den Zellbereich) auswählen, für die Sie die Prüfung festlegen möchten. Dann gehen wir zu Daten-Datenvalidierung.

Erste Registerkarte – Einstellungen

Auf dem Feld Datentyp (Zulassen)- Wählen Sie direkt den Datentyp aus, der in die Zelle geschrieben werden soll. Insgesamt stehen 8 Typen zur Verfügung: Beliebiger Wert, Ganze Zahl, Dezimalzahl, Liste, Datum, Uhrzeit, Textlänge, Benutzerdefiniert. Absatz Beliebiger Wert standardmäßig installiert ist, erfolgt keine Prüfung. Weitere Informationen zum Listenelement und seiner Erstellung finden Sie im Artikel Dropdown-Listen. Dort wird alles ausführlich und mit Nuancen zu Listen bei der Datenüberprüfung beschrieben, daher werden wir uns in diesem Artikel mit den verbleibenden 6 Typen befassen. Kurz gesagt, bei der Auswahl eines Artikels Aufführen In der Zelle wird eine Dropdown-Liste angezeigt akzeptable Werte. Und Sie können nur den Wert eingeben, der in der Liste vorhanden ist

Andere Datentypen:

  • Ganze Zahl- spricht für sich selbst. Nach der Installation einer solchen Prüfung kann in eine Zelle nur noch eine Ganzzahl eingegeben werden. Diese. eine Zahl, die keinen gebrochenen Rest hat (z. B. 9,1 kann nicht mehr eingegeben werden). Es ist auch nicht möglich, beliebigen Text einzugeben. Am häufigsten wird eine solche Prüfung in Feldern zur Erfassung der Stückzahl eines Produkts usw. verwendet. Diese. wo es keine gebrochenen Werte geben kann.
  • Real (Dezimal)- das gleiche wie im vorherigen Absatz, aber Sie können eine beliebige Zahl eingeben – sogar eine ganze Zahl, sogar einen Bruch, aber es ist unmöglich, Text einzugeben
  • Datum- Die Zelle kann nur einen Datumswert enthalten. Das Datum kann in jedem Format geschrieben werden, das in der aktuellen Windows-Lokalisierung akzeptiert wird. Hier ist die gleiche Nuance wie bei der Suche nach Zahlen, nur in Rückseite- Jedes Datum ist eine Zahl, Sie können also im Grunde jede ganze Zahl eingeben, die von Excel in ein Datum umgewandelt werden kann.
  • Zeit- Sie können Werte im Zeitformat für die aktuelle Lokalisierung schreiben und wie bei einem Datum nicht nur die Zeit, sondern auch beliebige Zahlen: ganze Zahlen oder Brüche in eine Zelle eingeben
  • Textlänge- ermöglicht Ihnen, die Anzahl der in eine Zelle eingegebenen Zeichen zu begrenzen. Die Daten können eine Zahl, ein Text oder eine Zeit sein. Wie auch immer, sie müssen lediglich die anderen Verifizierungsbedingungen erfüllen. Es wird häufig verwendet, um die Eingabe von Daten zu TIN, Kontrollpunkt, BIC, Artikeln usw. zu kontrollieren.

    • Wenn Sie sich das Bild am Anfang des Artikels ansehen, können Sie neben den von mir aufgelisteten noch drei weitere Felder sehen: Messwert), Minimum Und Maximal. Feld Bedeutung enthält mehrere Prüfoptionen: Zwischen, nicht zwischen, gleich, ungleich, größer als, kleiner als, größer als oder gleich, kleiner als oder gleich) und ist nur verfügbar, wenn Sie auswählen Datentyp Ganzzahl, reell, Datum, Uhrzeit, Textlänge. Für andere Typen ist das Feld „Wert“ nicht verfügbar. Abhängig davon, welches Element im Feld ausgewählt ist Bedeutung Es erscheinen zusätzliche Felder: Minimum und Maximum. In diesem Fall kann es nur ein Feld geben und der Name kann unterschiedlich sein. Zum Beispiel für Option Gleich Es wird nur ein Feld angezeigt, das den Namen „Wert“ trägt. Aber hier gibt es keine Tücken und Sie können diese Felder ohne Anstrengung und ohne einen halben Liter herausfinden :)

    Tatsächlich sind alle Punkte ziemlich ausdrucksstark und ich denke, dass es keinen Sinn macht, jeden einzelnen Punkt im Detail zu erklären. Zum Beispiel, Zwischen (zwischen)– gibt den Datums- oder Zahlenbereich an, den die Bedingung umfassen soll (z. B. eine ganze Zahl von 1 bis 12: Minimum = 1, Maximum = 12). Wenn der Benutzer versucht, eine Zahl außerhalb des angegebenen Bereichs einzugeben (z. B. die Zahl 0, -3 oder 14), zeigt Excel eine Fehlermeldung an (siehe unten). Möglichkeit Größer als oder gleich wie ermöglicht Ihnen die Eingabe nur positiver Werte größer als Null, wenn Sie im Feld Minimum den Wert 1 angeben.
    Der Verifizierungsmöglichkeit sollte etwas mehr Aufmerksamkeit geschenkt werden Draußen (nicht dazwischen). Wenn eingestellt auf Draußen können nur Daten in die Zelle eingegeben werden, die nicht in dem in den Feldern angegebenen Bereich enthalten sind Minimum Und Maximal. Diese. Unter den gleichen Bedingungen von 1 bis 12 ist die Eingabe von 0 und -3 und 14 möglich, die Eingabe von 1 oder 10 ist jedoch nicht möglich.
    In diesem Fall können Sie in den Feldern zur Eingabe von Werten einen Zellbezug angeben. Beispielsweise enthält Zelle A1 das Startdatum für die Annahme von Bewerbungen und Zelle B1 das Enddatum. Und Sie müssen in den Zellen A2:A50 den in A1 und B1 angegebenen Datumsbereich einchecken. Wählen Sie dazu die Zellen A2:A50 aus und erstellen Sie darin eine Datenprüfung: Datentyp: Datum, Wert: Zwischen, Startdatum: = A1, Enddatum: = B1. Jetzt können Sie das Intervall anpassen, ohne die Datenvalidierung selbst zu ändern – indem Sie einfach die Werte der Zellen A1 und B1 ändern.

  • Andere (Benutzerdefiniert)- meiner Meinung nach die interessanteste und leistungsfähigste Art der Verifizierung. Hier erhalten wir die Möglichkeit, eine größere Kontrolle über die Dateneingabe zu haben. Es muss nur ein Feld ausgefüllt werden: Formel. Sie müssen eine Formel hineinschreiben und jedes Mal, wenn Sie Werte in eine Zelle eingeben, prüft Excel, ob der eingegebene Ausdruck WAHR ist. Genauer gesagt berechnet Excel zunächst die Formel in diesem Feld basierend auf dem in der Zelle eingegebenen Wert mit Datenüberprüfung und prüft dann, ob die Formel den Wert TRUE zurückgibt. Wenn das Ergebnis ist WAHR, dann wird der eingegebene Wert in der Zelle gespeichert, und wenn FALSCH, dann erscheint ein Fenster mit einer Fehlermeldung:

    Lassen Sie mich Ihnen ein einfaches Beispiel geben. Geben Sie in Zelle A1 die Zahl 1 ein. Wählen Sie Zelle B1 aus – weisen Sie die Datenüberprüfung zu – Ein anderer. Im Feld Formel geben wir ein: = B1 = A1. Jetzt können Sie in Zelle B1 nur einen Wert eingeben, der vollständig mit dem Wert in Zelle A1 identisch ist.

    Wichtig: Wenn die Datenvalidierung später in andere Zellen kopiert wird, werden die Referenzen verschoben, als wäre es eine einfache Formel. Daher ist es bei der Erstellung von Formeln in der Datenvalidierung notwendig, die Möglichkeit eines Offsets zu berücksichtigen und ggf. feste Verknüpfungen (Dollarzeichen - $ ).

    • Registerkarte „Eingabenachricht“.

    Hier legen Sie den Text fest, der angezeigt wird, wenn eine Zelle mit Datenvalidierung ausgewählt wird:

    Das Praktischste an dieser Nachricht ist, dass Sie die Datenverifizierung selbst nicht installieren müssen, sondern nur diesen Tab ausfüllen müssen. Wenn Sie eine Zelle auswählen, wird dann eine Meldung angezeigt, es gibt jedoch keine Einschränkungen hinsichtlich der Eingabemöglichkeiten in die Zelle. Diese. Verwenden Sie diese Funktion einfach, um zusätzliche Informationen über das bevorzugte Datenformat bereitzustellen oder einfach die Art der Informationen anzugeben, die in die Zelle eingegeben werden sollen.

    Registerkarte „Fehlerwarnung“.
    Hier können Sie festlegen, ob eine Meldung über fehlerhafte Eingaben angezeigt werden soll und welche Art der Fehlermeldung angezeigt werden soll.

    • Fehlermeldung anzeigen, nachdem ungültige Daten eingegeben wurden- In den meisten Fällen müssen Sie das Kontrollkästchen aktivieren. Wenn das Kontrollkästchen deaktiviert ist, werden die in die Zelle eingegebenen Daten nicht überprüft.
    • Typ
      • Stopp, Nachricht (Stopp, Information)- Sie können nur Werte eingeben, die von der Prüfung zugelassen werden. Lediglich die Art der Meldung unterscheidet sich (ein Beispiel für eine Stop-Meldung ist in der Abbildung oben dargestellt).
      • Warnung- Sie können einen beliebigen Wert in die Zelle eingeben. Wenn Sie jedoch einen Wert eingeben, der der Überprüfung widerspricht, wird eine Warnmeldung angezeigt, die die Dateneingabe bestätigt.
    • Titel– Text, der im Titel der Fehlermeldung angezeigt wird. Wenn nicht angegeben, lautet der Titel Microsoft Excel.
    • Fehlermeldung- der Text der Fehlermeldung selbst. Wenn nicht angegeben, wird Text ähnlich dem folgenden angezeigt:
      Dieser Wert erfüllt nicht die für diese Zelle festgelegten Datenvalidierungsbeschränkungen
      (Dieser Wert entspricht nicht den für diese Zelle definierten Datenvalidierungsbeschränkungen.)

    Sie müssen jedoch auch bedenken, dass der Wert in der Zelle unabhängig von der von Ihnen festgelegten Prüfbedingung durch Klicken auf die Schaltfläche gelöscht werden kann Del. Oder indem Sie eine Zelle aus einer anderen Arbeitsmappe oder einem anderen Blatt kopieren und anstelle der Datenprüfung einfügen – die Prüfung verschwindet, weil die gesamte Zelle wird durch die kopierte ersetzt. Und daran führt kein Weg vorbei – die Entwickler haben diese Prüfung durchgeführt ...

    So kopieren Sie die Datenvalidierung in andere Zellen
    Es ist ganz einfach: Kopieren Sie die Zelle von notwendige Prüfung Daten – Wählen Sie Zellen aus, um in ihnen die gleiche Bedingung zu erstellen -Rechte Taste Mäuse - Spezial einfügen-im Fenster auswählen Bedingungen für Werte (Validierung) -OK:

    So entfernen Sie die Datenvalidierung aus Zellen
    Wählen Sie die gewünschten Zellen aus – Registerkarte Daten-Datenvalidierung. Auf dem Feld Datentyp (Zulassen) Installieren Beliebiger Wert -OK.

    Ein kleiner Trick zur Verwendung der Datenüberprüfung
    Wenn die Datenvalidierung für Zellen nach der Dateneingabe installiert wurde, ist es sehr gut zu verstehen, ob alle Daten die Überprüfungsbedingungen erfüllen. Das ist nicht schwer. Nachdem den Zellen die Datenüberprüfung zugewiesen wurde, wechseln Sie zur Registerkarte Daten-Datenvalidierung-Erweitern Sie das Menü und wählen Sie Kreisen Sie ungültige Daten ein. Alle Zellen, in denen die Daten die Datenüberprüfungsbedingungen nicht erfüllen, werden mit einer roten Linie umkreist:

    Dies kann nicht nur verwendet werden, wenn eine Datenüberprüfung erforderlich ist, sondern auch einfach, um fehlerhafte Werte zu identifizieren. Beispielsweise gibt es in einem Lager eine Warenliste und es gibt ein Blatt, in das dieses Produkt von Mitarbeitern manuell eingetragen wurde. Es muss festgestellt werden, welche Produktnamen nicht mit den im Lager gelagerten Produktnamen übereinstimmen. Wir erstellen eine Datenprüfliste (Dropdown-Listen), in der wir als Quelle die Warenliste im Lager angeben. Als nächstes erweitern wir diese Prüfung auf Zellen mit von Mitarbeitern eingegebenen Daten. Und jetzt wenden wir die Funktion an Kreisen Sie falsche Informationen ein.

    Unmittelbar nach der Korrektur der falschen Werte mit denen in der Liste verschwindet der Strich. Wenn nicht alle Werte korrigiert werden müssen, der Strich aber dennoch entfernt werden muss, dann gehen Sie nach allen notwendigen Bearbeitungen einfach auf die Registerkarte Daten -Datenvalidierung-Erweitern Sie das Menü und wählen Sie Validierungskreise löschen

    Hat der Artikel geholfen? Teilen Sie den Link mit Ihren Freunden! Video-Lektionen

    („Untere Leiste“:(„textstyle“: „static“, „textpositionstatic“: „bottom“, „textautohide“: true, „textpositionmarginstatic“: 0, „textpositiondynamic“: „bottomleft“, „textpositionmarginleft“: 24,“ textpositionmarginright“:24, „textpositionmargintop“:24, „textpositionmarginbottom“:24, „texteffect“: „slide“, „texteffecteasing“: „easeOutCubic“, „texteffectduration“: 600, „texteffectslidedirection“: „left“, „texteffectslidedistance“ :30, „texteffectdelay“:500, „texteffectseparate“:false, „texteffect1“: „slide“, „texteffectslidedirection1“: „rechts“, „texteffectslidedistance1“: 120, „texteffecteasing1“: „easeOutCubic“, „texteffectduration1“: 600 ,"texteffectdelay1":1000,"texteffect2":schieben,texteffectslidedirection2:rechts,texteffectslidedistance2:120,texteffecteasing2:easeOutCubic,texteffectduration2:600,texteffectdelay2:1500, textcss: „display:block; padding:12px; text-align:left;“, „textbgcss“: „display:block; position:absolute; top:0px; left:0px; width:100 %; height:100 % ; Hintergrundfarbe:#333333; Deckkraft:0,6; Filter:alpha(Deckkraft=60);","titlecss":"display:block; Position:relativ; Schriftart:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; Position:relativ; Schriftart: 12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; Farbe:#fff; „margin-top:8px;“, „buttoncss“: „display:block; Position:relativ; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive": "Schriftgröße:12px;", "descriptioncssresponsive": "display:none !important;", "buttoncssresponsive": „“, „addgooglefonts“:false, „googlefonts“: „“, „textleftrightpercentforstatic“:40))

Beim manuellen Ausfüllen von Tabellen in Excel müssen viele Daten eingegeben werden, was zu vielen Tippfehlern und Fehlern führen kann. Einige dieser Fehler können durch eine Überprüfung der Daten in Excel bei der Eingabe behoben werden, worauf wir als Nächstes eingehen.

Es ist sofort erwähnenswert, dass die Überprüfung der Eingabedaten in Excel nur funktioniert, wenn diese Daten über die Tastatur eingegeben werden. Beim Einfügen von Informationen aus der Zwischenablage oder einer anderen Quelle wird die Prüfung nicht durchgeführt. Bei bereits eingegebenen Werten können Sie dies noch überprüfen, was dazu führt, dass falsche Daten in einem roten Oval eingekreist werden, aber dazu später mehr.

Um Daten in Excel für bestimmte Zellen zu überprüfen, müssen Sie diese auswählen und zur Registerkarte wechseln "Daten", und finden Sie im Abschnitt „Arbeiten mit Daten“ Speisekarte „Datenprüfung“. Wenn Sie auf den Pfeil rechts neben diesem Punkt klicken, erscheinen drei Menüpunkte, von denen wir den allerersten benötigen „Datenprüfung…“.

Im erscheinenden Fenster „Eingabewerte prüfen“Überprüfung konfiguriert ist. Auf der Registerkarte "Optionen" Der Datentyp, der überprüft und in den von uns ausgewählten Zellbereich eingegeben werden soll, wird aus der Liste ausgewählt. Lassen Sie uns zum Beispiel wählen "Ganze Zahl".

Nach Auswahl des Datentyps ist es möglich, die Bedingung für die Übereinstimmung mit dem eingegebenen Wert in der Spalte auszuwählen "Bedeutung:". Wählen wir als Beispiel "Zwischen".

Auf der nächsten Registerkarte „Einzugebende Nachricht“ Sie können den Titel und die Hinweismeldung selbst angeben, die angezeigt wird, wenn eine Zelle aktiviert wird, um die eingegebenen Daten zu validieren.

Auf der Registerkarte "Fehlermeldung" Sie können den Titel und die Meldung selbst festlegen, die bei fehlerhafter Dateneingabe angezeigt wird, sowie die auszuführende Aktion. Auf der Registerkarte "Sicht:" Es stehen drei Optionen zur Verfügung. "Stoppen" verhindert die Eingabe ungültiger Daten und fordert Sie auf, es erneut zu versuchen. "Warnung" erzeugt eine Fehlermeldung, gibt Ihnen aber die Möglichkeit, weiterhin einen ungültigen Wert einzugeben. "Nachricht" informiert Sie lediglich darüber, dass der eingegebene Wert ungültig ist.

Nachrichtenhinweis.

Sicht "Stoppen".

Sicht "Warnung".

Manchmal muss man sich vor Fehlern schützen, bevor man Daten in Zellen eingibt, die bestimmte Bedingungen nicht erfüllen. Beispielsweise sollte es in der Produkt- und Preisnomenklatur eines Geschäfts nicht möglich sein, negative Zahlen und Nullen einzugeben. Schließlich gibt der Laden Waren nicht gegen Aufpreis oder kostenlos aus.

Validierung von Eingabedaten in Excel

Um Computerfehler bei der Eingabe von Bedienern in Excel zu verhindern, verwenden wir die Überprüfung der Eingabedaten. So schaffen wir für ihn komfortable Bedingungen für Routinearbeiten, bei denen es schwierig ist, Fehler zu vermeiden.

Wir haben ein Blatt mit der Produktpalette des Shops:

Lassen Sie uns nun überprüfen. Geben Sie in Zelle B2 eine natürliche Zahl und in Zelle B3 eine negative Zahl ein. Wie Sie in Zelle B3 sehen können, ist die Aktion des Wählbetreibers blockiert. Es wird eine Fehlermeldung angezeigt: „Der eingegebene Wert ist falsch.“

Notiz. Wenn Sie möchten, können Sie auf der dritten Registerkarte der Tooleinstellungen „Fehlermeldung“ einen eigenen Text für den Fehler schreiben.

Um die Datenvalidierung in Excel zu entfernen, müssen Sie: den entsprechenden Zellbereich auswählen, das Werkzeug auswählen und auf die Schaltfläche „Alle löschen“ klicken (im zweiten Bild angezeigt).



Merkmale der Datenüberprüfung

Diese Methode prüft Daten nur während des Eingabevorgangs. Wenn die Daten bereits eingegeben wurden, erfolgt keine Überprüfung. Sie können beispielsweise keinen Text in Spalte B eingeben, nachdem Sie die Bedingungen zum Füllen der Zellen darin festgelegt haben. Die Überschrift in Zelle B1 „Preis“ blieb jedoch ohne Fehlermeldung.

Aufmerksamkeit! Wenn Zellen kopiert und nicht eingegeben werden, werden deren Werte auch nicht überprüft.

Um zu überprüfen, ob alle eingegebenen Daten bestimmten Bedingungen in der Spalte entsprechen und ob Fehler vorliegen, sollten Sie ein anderes Tool verwenden: „Daten“ – „Datenprüfung“ – „Falsche Daten einkreisen“.


Wenn die Werte in Spalte B bestimmte Bedingungen erfüllen müssen, aber Fehler enthalten, werden sie alle in einem roten Oval umrandet. Dieses Tool ist sehr praktisch, wenn Sie bereits eingegebene oder kopierte Daten überprüfen müssen.

Natürlich ist es möglich, die Daten in einer Spalte mithilfe eines Booleschen Werts zu validieren Excel-Funktionen- "WENN". Oder bedingte Formatierung. Die Verwendung des Datenvalidierungstools ist für diese Aufgabe jedoch effektiver, bequemer und produktiver. Vor allem, wenn wir mehrere Spalten gleichzeitig überprüfen müssen. In solchen Fällen ist die Rationalität seiner Verwendung deutlicher erkennbar.

"Schutz Excel-Zellen aus falsch eingegebenen Daten.“ Hier betrachten wir eine weitere Option – wie man die korrekte Schreibweise des Codes mit Buchstaben und Zahlen in Excel überprüft.
Erste Wahl.
Validierung der Dateneingabe inExcel.
Wir haben so einen Tisch. In Spalte A schreiben wir Codes mit Buchstaben und Zahlen.
In diesen Zellen installieren wir die Datenverifizierung gemäß unseren Bedingungen. Wählen Sie die Zellen der Spalte A (A31:A36) aus.
Klicken Sie auf der Registerkarte „Daten“ im Abschnitt „Arbeiten mit Daten“ auf die Schaltfläche „Datenprüfung“. Das Dialogfeld „Eingabewerte validieren“ wird angezeigt.
Wählen Sie in diesem Fenster auf der Registerkarte „Parameter“ im Abschnitt „Validierungsbedingungen“ – „Datentyp“ die Funktion „Andere“.
In die Zeile „Formel“ schreiben wir die folgende Formel.
=AND(LEFT(A31)="F",LENGTH(A31)=3,ESNUMBER(VALUE(RIGHT(A31,2))))
Erläuterungen zur Formel.
Mit dieser Formel teilen wir Excel mit, dass in den Zellen der Spalte A (ab Zelle A31) die Daten mit dem Buchstaben „f“ beginnen sollen – dies ist Teil der Formel „LEFT(A31)="F"; .
Insgesamt sollten 3 Zeichen in die Zelle geschrieben werden – das ist die Funktion in der Formel – LÄNGE (A31) = 3.
Und die Funktion ISNUMBER(VALUE(RIGHT(A31,2))) besagt, dass die letzten 2 Zeichen in der Zelle Zahlen sein müssen.
Klicken Sie auf die Schaltfläche „OK“. Alle Datenprüfungen wurden installiert. Lass uns das Prüfen.
In Zelle A34 haben wir den ersten Buchstaben „a“ geschrieben. Es erscheint ein Fehlerwarnfenster.
Wenn wir eine Zahl mit weniger als oder mehr als 3 Ziffern eingeben, erscheint erneut ein Fenster, das auf einen Fehler hinweist.
Aufmerksamkeit!
Sie können eine Nachricht schreiben – angeben, um welchen Fehler es sich handelt, oder einen Hinweis schreiben, wie der Code richtig geschrieben wird. Gehen Sie dazu auf den Reiter „Fehlermeldung“ oder den Reiter „Eingabemeldung“.
Informationen zum Festlegen von Fehlermeldungen oder Zellenaufforderungen finden Sie im Artikel „Validieren von Daten in Excel“.
Zweite Option.
Validierung der eingegebenen Daten inExcel.
Sie können in der Formel beliebig viele Zeichen, beliebige Buchstaben, mehrere Buchstaben etc. schreiben. Zum Beispiel diese Formel.
=AND(LEFT(C33)="Fruit/";LENGTH(C32)=8,ESNUMBER(VALUE(RIGHT(C32,2))))
Das Ergebnis ist der folgende Code.
Mithilfe der Datenvalidierungsfunktion kann verhindert werden, dass Daten in einer Tabelle nachträglich korrigiert werden. Weitere Informationen zu dieser Methode finden Sie im Artikel „So vermeiden Sie die Korrektur von Daten in.“ Excel Tabelle Rückdatierung".
Sie können die Eingabe doppelter Daten verhindern. Informationen zu dieser Methode finden Sie im Artikel „Eingabe doppelter Werte in Excel verhindern“.
Sie können es selbst in Excel tun ein einfacher Tisch, können Sie eine komplexe Tabelle mit Filtern usw. erstellen. Möglichkeiten zum Erstellen einer Tabelle in Excel finden Sie im Artikel „

Bei der Eingabe vieler Informationen in Tabellenzellen kann es leicht zu Fehlern kommen. In Excel gibt es ein Tool zur Überprüfung der eingegebenen Daten direkt nach dem Drücken der Eingabetaste – Datenvalidierung.

Werkzeug Datenprüfung() wäre nicht so beliebt, wenn seine Funktionalität nur auf die Verifizierung selbst beschränkt wäre. Die grundlegenden Techniken für die Arbeit mit diesem Tool werden im Folgenden kurz aufgeführt.

A. Überprüfung der eingegebenen Werte

Wie Sie in der Abbildung unten sehen können, können die Bedingungen zur Überprüfung des eingegebenen Werts sehr flexibel konfiguriert werden.

Sie können die Eingabe von Werten in eine Zelle eines bestimmten Typs nur zulassen, indem Sie die erforderliche Bedingung aus der Dropdown-Liste auswählen:

  • Ganze Zahl. In eine Zelle dürfen nur ganze Zahlen eines bestimmten Bereichs eingegeben werden;
  • Gültig. In die Zelle können nur Zahlen eingegeben werden, auch solche mit Dezimalteil (Sie können keinen Text, aber ein Datum eingeben);
  • Datum von. Es wird davon ausgegangen, dass Datumsangaben vom 01.01.1900 bis zum 31.12.9999 in die Zelle eingegeben werden. Lesen Sie mehr über das Datumsformat im Artikel
  • Zeit. Es wird davon ausgegangen, dass in der Zelle mit Dieser Scheck Dieser Typ gibt die Uhrzeit ein. Die folgende Abbildung zeigt beispielsweise eine Bedingung, wenn es erlaubt ist, in einer Zelle eine Zeit einzugeben, die nur zur zweiten Tageshälfte gehört, d. h. von 12:00:00 bis 23:59:59. Anstatt mühsam den Wert 12:00:00 einzugeben, können Sie dessen numerisches Äquivalent von 0,5 verwenden. Die Möglichkeit, Zahlen anstelle der Zeit einzugeben, ergibt sich aus der Tatsache, dass jedem Datum in Excel eine positive ganze Zahl zugeordnet ist und daher die Zeit (da sie Teil des Tages ist) dem Bruchteil der Zahl entspricht (z. B. 0,5). ist Mittag). Das numerische Äquivalent für 23:59:59 wäre 0,99999.

  • Textlänge. In eine Zelle kann nur eine bestimmte Anzahl Zeichen eingegeben werden. Mit dieser Einschränkung können Sie sowohl Zahlen als auch Datumsangaben eingeben, Hauptsache, die Anzahl der eingegebenen Zeichen steht nicht im Widerspruch zur Textlängenbeschränkung. Wenn die Anzahl der Zeichen beispielsweise auf weniger als 5 begrenzt ist, können Sie kein Datum nach dem 13.10.2173 eingeben, weil es entspricht der Zahl 99999, und der 14.10.2173 ist bereits 100000, d.h. 6 Zeichen. Interessanterweise können Sie die Formel =ROOT(2) nicht in die Zelle eingeben, wenn Sie beispielsweise weniger als 5 Zeichen begrenzen, weil result =1.4142135623731 (abhängig von der in EXCEL angegebenen Genauigkeit), aber =ROOT(4) – Sie können, weil das Ergebnis =2 ist und dies nur 1 Zeichen ist.
  • Aufführen. Wahrscheinlich der interessanteste Datentyp. In diesem Fall kann die Eingabe von Werten in eine Zelle auf eine zuvor definierte Liste beschränkt werden. Wenn Sie beispielsweise die Maßeinheit eines Produkts als Quelle angeben, getrennt durch ein Semikolon Stück;kg;m²;kub.m, dann können Sie aus der Liste nichts anderes als diese 4 Werte auswählen. In der Quelle können Sie einen Zellbereich angeben, der eine vorab generierte Liste oder einen Link zu enthält. Ein Beispiel finden Sie im Artikel
  • Ein anderer. Die Zelle darf Werte eingeben, die komplexere Kriterien erfüllen. Um die Kriterien festzulegen, müssen Sie eine Formel verwenden. Betrachten wir diesen Zustand genauer.

Mit dem ausgewählten Typ Ein anderer, auf dem Feld Formel Sie müssen eine Formel eingeben, um den logischen Wert zu berechnen. Wenn das Ergebnis TRUE ist, darf ein solcher Wert in die Zelle eingegeben werden. Wenn es FALSE ist, kann Ihr Wert nicht eingegeben werden. Als Formelargument müssen Sie einen Verweis auf die Zelle selbst verwenden, auf die es angewendet wird. Datenprüfung oder ein Verweis auf eine Zelle, die davon abhängt. Zum Beispiel,

Zu

Geben Sie die Formel ein

Erläuterung

Zelle B2 enthielt nur Text

ITEXT(B2)

IN Datentyp Keine Möglichkeit, den Typ auszuwählen Text, also müssen wir dies indirekt erreichen. Sie können Datenüberprüfung Direkt auf die Zelle auftragen B2

Ermöglicht die Eingabe eines Werts in eine Zelle B1 Nur wenn nach Eingabe des Wertes in die Zelle D1 wird mehr als 100 sein D2 weniger als 400

UND(D1>100;D2<400)

Datenüberprüfung Auf die Zelle anwenden B1 . Außerdem in der Zelle D1 die Formel =B1*2 wurde eingeführt, und in D2 – Formel =B1*3. Obwohl diese Formel der Einschränkung entspricht Gültig mit einem Bereich von 50 bis 133,33, aber bei komplexeren Zellverbindungen kann diese Technik nützlich sein

Der Wert in der Zelle, die das Alter des Mitarbeiters enthält ( C1 ), muss immer größer sein als die Anzahl der vollen Arbeitsjahre ( D1 ) plus 18 (Mindestalter für Beschäftigung)

=IF(C1>D1+18,TRUE,FALSE)

Beim Ausfüllen einer Tabelle mit Daten zu Alter und Berufserfahrung können Sie diese Prüfung für beide Zellen setzen ( C1 Und D1 ). Dazu müssen Sie 2 Zellen gleichzeitig auswählen und anrufen Datenüberprüfung und modifizieren Sie die Formel leicht =IF($C1>$D1+18,TRUE,FALSE)

Alle Daten in einem Bereich von Zellen A1:A20 enthaltene Werte

=ZÄHLENWENN($A$1:$A$20,A1)=1

=MATCH(A1,$A:$A,0)=ROW(A1)

Zellen müssen ausgewählt werden A1:A20 , rufen Sie das Tool auf Datenprüfung und geben Sie die Formel ein. Die zweite Formel kann für die gesamte Spalte verwendet werden A , dazu müssen Sie nicht den Bereich, sondern die gesamte Spalte auswählen A

Der Wert in der Zelle, die den Produktcodenamen enthält ( B5 ), begann immer mit dem Standardpräfix „ID-“ und war mindestens 10 Zeichen lang.

=AND(LEFT(B5,3)="ID-"; LENGTH(B5)>9)

Wir geben die Datenüberprüfung für die Zelle ein B5

Vergessen Sie bei der Auswahl mehrerer Zellen ggf. nicht, einen absoluten Bezug zu den Zellen anzugeben (z. B. 1 $A$:20$A$ ).

Bei der Verwendung des Tools Datenprüfung, wird davon ausgegangen, dass Konstanten in die Zelle eingegeben werden ( 123, Produkt1, 01.05.2010 usw.), obwohl niemand die Eingabe von Formeln verbietet. In diesem Fall wird das Ergebnis der Formelberechnung noch überprüft. Im Allgemeinen empfehle ich nicht, Formeln in Zellen mit Datenüberprüfung einzugeben – es kann leicht zu Verwirrung kommen. In diesem Fall empfehle ich Ihnen die Verwendung von .

IN.Zeigt einen Kommentar an, wenn die Zelle die aktuelle ist.

Verwenden Sie die Registerkarte Ausgabenachricht um den Kommentar anzuzeigen.

Im Gegensatz zur üblichen Notiz ( Überprüfen/Kommentieren/Kommentar erstellen), die verschwindet, nachdem der Mauszeiger die Zelle verlässt (sofern die Option nicht aktiv ist). Alle Notizen anzeigen), wird dieser Kommentar immer dann angezeigt, wenn eine Zelle ausgewählt wird.

MIT.Zeigt eine detaillierte Fehlermeldung an.

Nach Eingabe eines falschen Werts Datenprüfung anzeigen kann ausführliche Nachricht darüber, was falsch gemacht wurde. Das ist eine Art Analogon Msgbox() aus VBA.

D.Verknüpfte Bereiche (Listen) erstellen

E. Verwendung von Verweisen auf andere Blätter in Regeln

In EXCEL 2007 Datenüberprüfung, wie in: Sie können keinen direkten Link zu den Bereichen eines anderen Blatts angeben, beispielsweise wie folgt: =Sheet2!$A$1 . Ermöglicht Ihnen, diese Einschränkung mithilfe von zu umgehen.

Wenn drin Datenüberprüfung Sie müssen beispielsweise einen Verweis auf eine Zelle erstellen A1 Wenn Sie ein anderes Blatt erstellen, müssen Sie zunächst die Zelle definieren und dann darauf verweisen Name in der Regel Datenüberprüfung.

In Excel 2010 hingegen können Sie Datenvalidierungsregeln verwenden, die auf Werte in anderen Tabellenblättern verweisen. In Excel 2007 und Excel 97-2003 wird diese Art der Datenvalidierung nicht unterstützt und erscheint nicht im Arbeitsblatt. Allerdings bleiben alle Datenvalidierungsregeln in der Arbeitsmappe verfügbar und werden angewendet, wenn Sie die Arbeitsmappe in Excel 2010 erneut öffnen, es sei denn, sie wurden in Excel 2007 oder Excel 97-2003 geändert.

F. Wie die Hintergrundüberprüfung funktioniert

Datenprüfung wird explizit ausgelöst, wenn Werte über die Tastatur in eine Zelle eingegeben und anschließend eine Taste gedrückt wird EINGEBEN. In diesem Fall erscheint ein Fenster mit der Beschreibung des Fehlers.

Wenn Werte über eingefügt werden Zwischenablage (Werte einfügen) oder über eine Tastenkombination STRG+ D(Kopieren des Wertes aus der Zelle von oben) oder von oben nach unten kopiert werden, erfolgt die Prüfung nicht explizit. Darüber hinaus können Sie beim Kopieren von Werten versehentlich Regeln vollständig löschen Datenprüfungen, beispielsweise wenn die Quellzelle nicht definiert ist Datenprüfung, und Daten daraus werden über eingefügt Zwischenablage und über eine Tastenkombination STRG+V.

Lassen Sie es uns anhand eines Beispiels erklären. Angenommen, Zelle A1 angewandt Datenprüfung vorbehaltlich der Überprüfung Ein anderer, wobei =ROW(A1)=1 in das Formelfeld eingetragen wird, also für alle Zellen ab der ersten Zeile die Bedingung Datenprüfungen nimmt den Wert TRUE an, für andere Zeilen - FALSE, unabhängig vom Inhalt der Zelle.

Jetzt wählen wir die Zelle aus A2 und drücke STRG+D. Wert von A1 kopiert wird A2 zusammen mit dem Zustand Datenprüfungen. Trotz der Tatsache, dass jetzt der Zustand Datenprüfungen wird als FALSE ausgewertet und es wird keine Warnmeldung angezeigt. Um sicherzustellen, dass die Daten in den Zellen die in definierten Bedingungen erfüllen Datenüberprüfung, müssen Sie den Menübefehl aufrufen Kreisen Sie falsche Informationen ein (). Zellen mit falschen Daten werden in roten Ovalen umrandet. Jetzt wählen wir die Zelle erneut aus A2 und drücken Sie die Taste F2(Gehen Sie in den Bearbeitungsmodus) und klicken Sie dann auf EINGEBEN- Es erscheint ein Fenster mit der Meldung, dass der eingegebene Wert falsch ist.

Es gibt eine andere Möglichkeit, Hintergrundüberprüfungen zu umgehen. Nehmen wir an, die Eingabe in eine Zelle ist auf Werte von 1 bis 3 beschränkt. Jetzt in jede andere Zelle ohne Datenprüfungen Geben Sie den Wert 4 ein. Wählen Sie diese Zelle aus, in Formelleiste Wählen Sie den Wert 4 und kopieren Sie ihn nach Zwischenablage. Wählen wir nun die Zelle mit aus Datenüberprüfung und drücke STRG+V. Der Wert wurde in die Zelle eingefügt! Außerdem, Datenprüfung blieb unberührt, im Gegensatz zu dem Fall, als, durch Zwischenablage Beispielsweise wird ein Wert aus WORD eingefügt. Um sicherzustellen, dass die Daten in einer Zelle nicht die in definierten Bedingungen erfüllen Datenüberprüfung, müssen Sie den Menübefehl aufrufen Kreisen Sie falsche Informationen ein (Daten/ Mit Daten arbeiten/ Daten validieren/ Falsche Daten einkreisen).

G. Suche nach Zellen mit Datenvalidierung

Wenn das Blatt viele Zellen enthält mit Datenüberprüfung, dann können Sie das Tool ( Startseite/Suchen und Auswählen/Auswählen einer Gruppe von Zellen).

Möglichkeit Datenprüfung Mit diesem Tool können Sie Zellen auswählen, die einer Datenvalidierung unterliegen (angegeben mit dem Befehl). Daten / Mit Daten arbeiten / Daten validieren). Bei der Auswahl eines Schalters Alle Alle diese Zellen werden ausgewählt. Bei Auswahl der Option Diese Dasselbe Es werden nur die Zellen hervorgehoben, für die dieselben Datenvalidierungsregeln festgelegt sind wie für die aktive Zelle.



IN VERBINDUNG STEHENDE ARTIKEL