Excel-Datenanalyse - Datenvalidierung

Die Datenüberprüfung ist ein sehr nützliches und benutzerfreundliches Tool in Excel, mit dem Sie Datenüberprüfungen für die eingegebenen Daten festlegen können, die in Ihr Arbeitsblatt eingegeben werden.

Für jede Zelle im Arbeitsblatt können Sie

• Zeigen Sie eine Eingabenachricht darüber an, was eingegeben werden muss.
• Beschränken Sie die eingegebenen Werte.
• Geben Sie eine Liste mit Werten zur Auswahl an.
• Zeigen Sie eine Fehlermeldung an und lehnen Sie eine ungültige Dateneingabe ab.

Betrachten Sie den folgenden Risk Tracker, mit dem Sie die identifizierten Risikoinformationen eingeben und verfolgen können.

In diesem Tracker werden die in die folgenden Spalten eingegebenen Daten mit voreingestellten Datenbeschränkungen validiert und die eingegebenen Daten werden nur akzeptiert, wenn sie die Validierungskriterien erfüllen. Andernfalls erhalten Sie eine Fehlermeldung.

• Probability
• Impact
• Risikokategorie
• Risikoquelle
• Status

Die Spalte Risikoposition enthält berechnete Werte, und Sie können keine Daten eingeben. Sogar die SäuleS. No. ist so eingestellt, dass berechnete Werte angepasst werden, auch wenn Sie eine Zeile löschen.

Jetzt lernen Sie, wie Sie ein solches Arbeitsblatt einrichten.

Bereiten Sie die Struktur für das Arbeitsblatt vor

So bereiten Sie die Struktur für das Arbeitsblatt vor:

• Beginnen Sie mit einem leeren Arbeitsblatt.
• Setzen Sie den Header in Zeile 2.
• Fügen Sie die Spaltenüberschriften in Zeile 3 ein.
• Für die Spaltenüberschriften Wahrscheinlichkeit, Auswirkung und Risikoexposition -
  • Klicken Sie mit der rechten Maustaste auf die Zelle.
  • Klicken Sie in der Dropdown-Liste auf Zellen formatieren.
  • Klicken Sie im Dialogfeld Zellen formatieren auf die Registerkarte Ausrichtung.
  • Geben Sie 90 unter Orientierung ein.
• Führen Sie die Zellen in den Zeilen 3, 4 und 5 für jede der Spaltenüberschriften zusammen und zentrieren Sie sie.
• Formatrahmen für die Zellen in den Zeilen 2 - 5.
• Passen Sie die Zeilen- und Spaltenbreite an.

Ihr Arbeitsblatt sieht wie folgt aus:

Legen Sie gültige Werte für die Risikokategorie fest

Geben Sie in die Zellen M5 - M13 die folgenden Werte ein (M5 ist Überschrift und M6 - M13 sind die Werte)

Kategoriewerte

Endnutzer

Kunde

Management

Zeitplan

Zeitplan

Umgebung

Produkt

Projekt

• Klicken Sie auf die erste Zelle unter der Spalte Risikokategorie (H6).
• Klicken Sie in der Multifunktionsleiste auf die Registerkarte DATEN.
• Klicken Sie in der Gruppe Datentools auf Datenüberprüfung.
• Wählen Sie Datenvalidierung… aus der Dropdown-Liste.

Das Dialogfeld Datenüberprüfung wird angezeigt.

• Klicken Sie auf die Registerkarte Einstellungen.
• Unter Validierungskriterien in der Allow: Dropdown-Liste, Wählen Sie die Option List.

• Wählen Sie im angezeigten Feld Quelle: den Bereich M6: M13 aus.
• Aktivieren Sie die Kontrollkästchen Leere und In-Cell-Dropdown ignorieren, die angezeigt werden.

Eingabemeldung für Risikokategorie festlegen

• Klicken Sie im Dialogfeld Datenüberprüfung auf die Registerkarte Eingabemeldung.
• Aktivieren Sie das Kontrollkästchen Show input message wenn Zelle ausgewählt ist.
• Geben Sie in das Feld unter Titel: Risikokategorie ein:
• Im Feld unter Eingabemeldung: Wählen Sie die Kategorie des Risikos aus der Liste.

Fehlerwarnung für Risikokategorie festlegen

Fehlerwarnung einstellen -

• Klicken Sie im Dialogfeld Datenüberprüfung auf die Registerkarte Fehlerwarnung.
• Aktivieren Sie das Kontrollkästchen Fehlermeldung anzeigen, nachdem ungültige Daten eingegeben wurden.
• Wählen Sie unter Stil: Dropdown die Option Stopp
• Geben Sie in das Feld unter Titel: Ungültigen Eintrag ein:
• In das Feld unter Fehlermeldung: Typ Wählen Sie einen Wert aus der Dropdown-Liste.
• OK klicken.

Überprüfen Sie die Datenvalidierung für die Risikokategorie

Für die ausgewählte erste Zelle unter Risikokategorie

• Datenvalidierungskriterien sind festgelegt
• Eingangsnachricht ist gesetzt
• Fehlerwarnung ist gesetzt

Jetzt können Sie Ihre Einstellungen überprüfen.

Klicken Sie in die Zelle, für die Sie Datenüberprüfungskriterien festgelegt haben. Die Eingabemeldung wird angezeigt. Die Dropdown-Schaltfläche wird auf der rechten Seite der Zelle angezeigt.

Die Eingabemeldung wird korrekt angezeigt.

• Klicken Sie auf die Dropdown-Schaltfläche rechts in der Zelle. Die Dropdown-Liste wird mit den Werten angezeigt, die ausgewählt werden können.

• Vergleichen Sie die Werte in der Dropdown-Liste mit denen, die zum Erstellen der Dropdown-Liste verwendet werden.

Beide Wertesätze stimmen überein. Beachten Sie, dass bei einer höheren Anzahl von Werten rechts in der Dropdown-Liste eine Bildlaufleiste angezeigt wird.

Wählen Sie einen Wert aus der Dropdown-Liste. Es erscheint in der Zelle.

Sie können sehen, dass die Auswahl gültiger Werte einwandfrei funktioniert.

Versuchen Sie abschließend, einen ungültigen Eintrag einzugeben und die Fehlerwarnung zu überprüfen.

Geben Sie Personen in die Zelle ein und drücken Sie die Eingabetaste. Die für die Zelle festgelegte Fehlermeldung wird angezeigt.

• Überprüfen Sie die Fehlermeldung.
• Sie haben die Möglichkeit, es erneut zu versuchen oder abzubrechen. Überprüfen Sie beide Optionen.

Sie haben die Datenüberprüfung für die Zelle erfolgreich festgelegt.

Note - Es ist sehr wichtig, die Rechtschreibung und Grammatik Ihrer Nachrichten zu überprüfen.

Legen Sie gültige Kriterien für die Risikokategoriespalte fest

Jetzt können Sie die Datenüberprüfungskriterien auf alle Zellen in der Spalte Risikokategorie anwenden.

An diesem Punkt müssen Sie sich an zwei Dinge erinnern -

• Sie müssen die Kriterien für die maximale Anzahl von Zellen festlegen, die verwendet werden können. In unserem Beispiel kann es je nach Verwendungszweck des Arbeitsblatts zwischen 10 und 100 variieren.

• Sie sollten die Kriterien nicht für unerwünschte Zellenbereiche oder für die gesamte Spalte festlegen. Dies erhöht die Dateigröße unnötig. Dies wird als übermäßige Formatierung bezeichnet. Wenn Sie ein Arbeitsblatt von einer externen Quelle erhalten, müssen Sie die überschüssige Formatierung entfernen, die Sie im Kapitel über Anfragen in diesem Lernprogramm erfahren .

Befolgen Sie die unten angegebenen Schritte -

• Legen Sie die Validierungskriterien für 10 Zellen unter Risikokategorie fest.
• Sie können dies einfach tun, indem Sie auf die rechte untere Ecke der ersten Zelle klicken.
• Halten Sie das angezeigte + -Symbol gedrückt und ziehen Sie es nach unten.

Die Datenüberprüfung wird für alle ausgewählten Zellen festgelegt.

Klicken Sie auf die letzte ausgewählte Spalte und überprüfen Sie sie.

Die Datenvalidierung für die Spalte Risikokategorie ist abgeschlossen.

Festlegen von Validierungswerten für die Risikoquelle

In diesem Fall haben wir nur zwei Werte - Intern und Extern.

• Klicken Sie in die erste Zelle unter der Spalte Risikoquelle (I6).
• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEN
• Klicken Sie in der Gruppe Datentools auf Datenüberprüfung
• Wählen Sie Datenvalidierung… aus der Dropdown-Liste.

Das Dialogfeld Datenüberprüfung wird angezeigt.

• Klicken Sie auf die Registerkarte Einstellungen.
• Wählen Sie unter Validierungskriterien in der Dropdown-Liste Zulassen: die Option Liste aus.
• Geben Sie im angezeigten Feld Quelle: Intern, Extern ein.
• Aktivieren Sie die Kontrollkästchen Leere und In-Cell-Dropdown ignorieren, die angezeigt werden.

Eingabemeldung für Risikoquelle festlegen.

Fehlerwarnung für Risikoquelle festlegen.

Für die ausgewählte erste Zelle unter Risikoquelle -

• Datenvalidierungskriterien sind festgelegt
• Eingangsnachricht ist gesetzt
• Fehlerwarnung ist gesetzt

Jetzt können Sie Ihre Einstellungen überprüfen.

Klicken Sie in die Zelle, für die Sie Datenüberprüfungskriterien festgelegt haben. Die Eingabemeldung wird angezeigt. Die Dropdown-Schaltfläche wird auf der rechten Seite der Zelle angezeigt.

Die Eingabemeldung wird korrekt angezeigt.

• Klicken Sie auf die Dropdown-Pfeilschaltfläche rechts in der Zelle. Eine Dropdown-Liste mit den Werten, die ausgewählt werden können, wird angezeigt.

• Überprüfen Sie, ob die Werte mit den eingegebenen übereinstimmen - Intern und Extern.

Beide Wertesätze stimmen überein. Wählen Sie einen Wert aus der Dropdown-Liste. Es erscheint in der Zelle.

Sie können sehen, dass die Auswahl gültiger Werte einwandfrei funktioniert. Versuchen Sie abschließend, einen ungültigen Eintrag einzugeben und die Fehlerwarnung zu überprüfen.

Geben Sie Financial in die Zelle ein und drücken Sie die Eingabetaste. Die für die Zelle festgelegte Fehlermeldung wird angezeigt.

• Überprüfen Sie die Fehlermeldung. Sie haben die Datenüberprüfung für die Zelle erfolgreich festgelegt.

• Legen Sie gültige Kriterien für die Spalte Risikoquelle fest

• Wenden Sie die Datenvalidierungskriterien auf die Zellen I6 - I15 in der Spalte Risikoquelle an (dh denselben Bereich wie in der Spalte Risikokategorie).

Die Datenüberprüfung wird für alle ausgewählten Zellen festgelegt. Die Datenvalidierung für die Spalte Risikoquelle ist abgeschlossen.

Legen Sie die Validierungswerte für den Status fest

• Wiederholen Sie dieselben Schritte, die Sie zum Festlegen der Validierungswerte für die Risikoquelle verwendet haben.

• Stellen Sie die Listenwerte auf Offen, Geschlossen ein.

• Wenden Sie die Datenvalidierungskriterien auf die Zellen K6 - K15 in der Spalte Status an (dh denselben Bereich wie in der Spalte Risikokategorie).

Die Datenüberprüfung wird für alle ausgewählten Zellen festgelegt. Die Datenüberprüfung für den Spaltenstatus ist abgeschlossen.

Legen Sie Validierungswerte für die Wahrscheinlichkeit fest

Die Werte für den Risikowahrscheinlichkeitswert liegen im Bereich von 1 bis 5, wobei 1 niedrig und 5 hoch ist. Der Wert kann eine beliebige Ganzzahl zwischen 1 und 5 sein, beide einschließlich.

• Klicken Sie in die erste Zelle unter der Spalte Risikoquelle (I6).
• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEN.
• Klicken Sie in der Gruppe Datentools auf Datenüberprüfung.
• Wählen Sie Datenvalidierung… aus der Dropdown-Liste.

Das Dialogfeld Datenüberprüfung wird angezeigt.

• Klicken Sie auf die Registerkarte Einstellungen.
• Wählen Sie unter Validierungskriterien in der Dropdown-Liste Zulassen: die Option Ganze Zahl aus.

• Wählen Sie zwischen unter Daten:
• Geben Sie 1 in das Feld unter Minimum ein:
• Geben Sie 5 in das Feld unter Maximum ein:

Eingabemeldung auf Wahrscheinlichkeit einstellen

Stellen Sie die Fehlerwarnung für die Wahrscheinlichkeit ein und klicken Sie auf OK.

Für die ausgewählte erste Zelle unter Wahrscheinlichkeit:

• Datenvalidierungskriterien sind festgelegt.
• Eingangsnachricht ist gesetzt.
• Fehlerwarnung ist gesetzt.

Jetzt können Sie Ihre Einstellungen überprüfen.

Klicken Sie auf die Zelle, für die Sie Datenüberprüfungskriterien festgelegt haben. Die Eingabemeldung wird angezeigt. In diesem Fall gibt es keine Dropdown-Schaltfläche, da die Eingabewerte in einem Bereich und nicht in der Liste liegen.

Die Eingabemeldung wird korrekt angezeigt.

Geben Sie eine Ganzzahl zwischen 1 und 5 in die Zelle ein. Es erscheint in der Zelle.

Die Auswahl gültiger Werte funktioniert einwandfrei. Versuchen Sie abschließend, einen ungültigen Eintrag einzugeben und die Fehlerwarnung zu überprüfen.

Geben Sie 6 in die Zelle ein und drücken Sie die Eingabetaste. Die Fehlermeldung, die Sie für die Zelle festgelegt haben, wird angezeigt.

Sie haben die Datenüberprüfung für die Zelle erfolgreich festgelegt.

• Legen Sie gültige Kriterien für die Wahrscheinlichkeitsspalte fest.

• Wenden Sie die Datenvalidierungskriterien auf die Zellen E6 - E15 in der Spalte Wahrscheinlichkeit an (dh denselben Bereich wie in der Spalte Risikokategorie).

Die Datenüberprüfung wird für alle ausgewählten Zellen festgelegt. Die Datenvalidierung für die Spalte Wahrscheinlichkeit ist abgeschlossen.

Legen Sie Validierungswerte für die Auswirkung fest

Wiederholen Sie zum Festlegen der Validierungswerte für Impact dieselben Schritte, die Sie zum Festlegen der Validierungswerte für die Wahrscheinlichkeit verwendet haben.

Wenden Sie die Datenvalidierungskriterien auf die Zellen F6 - F15 in der Spalte Auswirkung an (dh denselben Bereich wie in der Spalte Risikokategorie).

Die Datenüberprüfung wird für alle ausgewählten Zellen festgelegt. Die Datenüberprüfung für die Spalte Auswirkung ist abgeschlossen.

Stellen Sie das Spaltenrisiko mit berechneten Werten ein

Das Risiko wird als Produkt aus Risikowahrscheinlichkeit und Risikoauswirkung berechnet.

Risikoexposition = Wahrscheinlichkeit * Auswirkung

Geben Sie = E6 * F6 in Zelle G6 ein und drücken Sie die Eingabetaste.

In der Zelle G6 wird 0 angezeigt, da E6 und F6 leer sind.

Kopieren Sie die Formel in die Zellen G6 - G15. In den Zellen G6 - G15 wird 0 angezeigt.

Da die Spalte Risikoposition für berechnete Werte gedacht ist, sollten Sie die Dateneingabe in dieser Spalte nicht zulassen.

• Wählen Sie die Zellen G6-G15 aus

• Klicken Sie mit der rechten Maustaste und wählen Sie in der angezeigten Dropdown-Liste die Option Zellen formatieren. Das Dialogfeld Zellen formatieren wird angezeigt.

• Klicken Sie auf die Registerkarte Schutz.

• Aktivieren Sie die Option Locked.

Dies soll sicherstellen, dass die Dateneingabe in diesen Zellen nicht zulässig ist. Dies wird jedoch erst wirksam, wenn das Arbeitsblatt geschützt ist. Dies ist der letzte Schritt, nachdem das Arbeitsblatt fertig ist.

• OK klicken.
• Schattieren Sie die Zellen G6-G15, um anzuzeigen, dass es sich um berechnete Werte handelt.

Seriennummernwerte formatieren

Sie können es dem Benutzer überlassen, die Spalte S. Nr. Auszufüllen. Wenn Sie jedoch die S. No.-Werte formatieren, sieht das Arbeitsblatt ansehnlicher aus. Außerdem wird angezeigt, für wie viele Zeilen das Arbeitsblatt formatiert ist.

Geben Sie = row () - 5 in die Zelle B6 ein und drücken Sie die Eingabetaste.

1 wird in Zelle B6 angezeigt. Kopieren Sie die Formel in die Zellen B6-B15. Die Werte 1-10 werden angezeigt.

Schattieren Sie die Zellen B6-B15.

Einpacken

Sie sind fast fertig mit Ihrem Projekt.

• Spalte M ausblenden, die Datenkategoriewerte enthält.
• Formatrahmen für die Zellen B6-K16.

• Klicken Sie mit der rechten Maustaste auf die Registerkarte Arbeitsblatt.
• Wählen Sie im Menü die Option Blatt schützen.

Das Dialogfeld Blatt schützen wird angezeigt.

• Aktivieren Sie die Option Arbeitsblatt und Inhalt gesperrter Zellen schützen.
• Geben Sie unter Passwort zum Aufheben des Schutzes des Blattes ein Passwort ein -
  • Das Passwort unterscheidet zwischen Groß- und Kleinschreibung
  • Das geschützte Blatt kann nicht wiederhergestellt werden, wenn das Passwort vergessen wurde
  • Es wird empfohlen, irgendwo eine Liste mit Arbeitsblattnamen und Kennwörtern aufzubewahren
• Aktivieren Sie unter Allen Benutzern dieses Arbeitsblatts erlauben: das Kontrollkästchen Entsperrte Zellen auswählen.

Sie haben die gesperrten Zellen in der Spalte Risikoexposition vor Dateneingabe geschützt und den Rest der entsperrten Zellen bearbeitbar gehalten. OK klicken.

Das Confirm Password Das Dialogfeld wird angezeigt.

• Geben Sie das Passwort erneut ein.
• OK klicken.

Ihr Arbeitsblatt mit dem Datenüberprüfungssatz für ausgewählte Zellen ist einsatzbereit.