Um das Risiko von Benutzerfehlern zu verringern, können Sie Ihre Arbeitsblätter so schützen, dass Benutzer nur Eingabezellen ändern und bestimmte andere eingeschränkte Aktionen durchführen können, z. B. Zellen auswählen und Filter verwenden. Um dies zu ermöglichen, ist ein zweistufiger Prozess erforderlich, der im Folgenden erläutert wird.
1. Stellen Sie sicher, dass Nicht-Eingabezellen gesperrt und Eingabezellen entsperrt sind
🔒 Nicht-Eingabezellen wie Formeln und Beschriftungen
Standardmäßig haben alle Zellen den Status "gesperrt" (geschützt). Wenn Sie das überprüfen möchten, markieren Sie alle relevanten Zellen, wählen Sie Zellen formatieren (Shortcut Strg 1), wählen Sie die Registerkarte "Schutz" und kontrollieren Sie, ob neben der Eigenschaft "Gesperrt" ein Häkchen vorhanden ist.
Ausgeblendet: Die Registerkarte "Schutz" bietet die zusätzliche Option "Ausgeblendet". Damit können Sie die Zellformel in der Formelleiste ausblenden, wenn die Zelle ausgewählt ist. Im Interesse der Transparenz und des Verständnisses der Formeln empfehle ich diese Option nicht.
🔓 Eingabezellen
Sie müssen die Eingabezellen (einschließlich der Zellen mit Datenüberprüfung) entsperren. Wählen Sie die Eingabezellen aus.
Wenn Sie nicht sicher sind, welche Zellen Eingaben sind, verwenden Sie Gehe zu (Shortcut F5). Wählen Sie "Spezial" und "Konstanten", "Zahlen" und dann ok. Alle Eingabezellen mit Inhalt sind jetzt ausgewählt!
Wählen Sie Zellen formatieren (Shortcut Strg 1), wählen Sie die Registerkarte "Schutz" und entfernen Sie das Häkchen neben der Eigenschaft "Gesperrt".
Bonustipp #1: Formatvorlagen verwenden
Die einfachste Methode zum Entsperren von Eingabezellen ist die Verwendung von Formatvorlagen. Formatieren Sie eine Eingabezelle, wobei "Gesperrt" deaktiviert ist. Gehen Sie dann zum Menüband Start und klicken Sie auf den Abwärtspfeil am Ende des Bereichs Formatvorlagen.
Wählen Sie "Neue Formatvorlage..." und speichern Sie das Format z.B. als "01 Inputs" (die Nummerierung stellt sicher, dass Ihre Zellenvorlagen in der gewünschten Reihenfolge erscheinen).
Wenn Sie jetzt eine Eingabe- oder Dropdown-Zelle in Ihrem Modell formatieren möchten, markieren Sie einfach die Zelle(n) und klicken Sie auf den Namen der entsprechenden Formatvorlage im Feld Formatvorlagen in der Multifunktionsleiste Home. Die Zellen erhalten dann die entsprechende Farbe und werden automatisch freigeschaltet... einfach und effektiv!
Ich verwende in der Regel einen Zellstil für Eingaben und einen anderen für Dropdowns mit einer anderen Farbe, die beide nicht gesperrt sind, d.h. das Optionsfeld "gesperrt" ist nicht markiert.
Siehe Microsoft für mehr Details zum Thema Zellenformate.
Bonustipp #2: Aktivieren Sie die Fehlerüberprüfung im Hintergrund, um fehlenden Schutz zu erkennen
Unter "Datei", "Optionen", "Formeln", "Regeln für die Fehlerüberprüfung" können Sie die Hintergrund-Fehlerüberprüfung für "Nicht gesperrte Zellen, die Formeln enthalten" einschalten. Excel markiert dann automatisch alle ungeschützten Zellen mit einer grünen Ecke, und Sie können dann die Zellformatierung wie oben beschrieben korrigieren.
2. Schalten Sie den Schutz für jedes Arbeitsblatt ein
Auf diesen Schritt weist Excel in dem Hinweis unter den Schutzeinstellungen hin: „Das Sperren von Zellen ... hat keine Wirkung, bis Sie das Arbeitsblatt schützen.“ Dies ist ganz einfach zu bewerkstelligen.
🔐 Wählen Sie das entsprechende Arbeitsblatt aus, navigieren Sie zur Multifunktionsleiste Überprüfen und klicken Sie auf 'Blatt schützen'.
Wählen Sie dann aus, was Sie zulassen wollen.
Die Optionen "Auswählen gesperrter Zellen" und "Entsperrte Zellen auswählen" sind standardmäßig erlaubt. Das ist im Allgemeinen sinnvoll, die Optionen können aber abgewählt werden. Alle anderen Optionen sind standardmäßig abgewählt, aber Sie können in jedem Fall ein Optionsfeld ankreuzen. Eine Option, die ich in der Regel zulasse, ist "Autofilter verwenden", insbesondere für Datenanalysedateien oder in Modellen mit großen Eingabeblättern.
Der letzte Schritt ist die Eingabe eines Kennworts (falls gewünscht) und schon sind Sie fertig. Natürlich müssen Sie sich das Kennwort merken, was zu einem Problem werden kann, wenn Sie viele geschützte Tabellenblätter verwenden. Sie können daher erwägen, das Arbeitsblatt ohne Kennwort zu schützen, um Probleme zu vermeiden.
Wiederholen Sie diesen Vorgang für alle weiteren Blätter des Modells, die Sie schützen möchten. Wenn Sie ein Kennwort verwenden, empfehle ich dringend, dasselbe Kennwort für alle geschützten Arbeitsblätter in einer einzigen Arbeitsmappe zu verwenden, da sonst der Aufwand für die Verwaltung der Kennwörter oder das Risiko, ein Kennwort zu vergessen, zu groß wird.
🔓🔐 Wenn Sie den Schutz des Arbeitsblatts aufheben, um eine Änderung vorzunehmen, müssen Sie es anschließend erneut schützen!
Leider gibt es keine Möglichkeit, den Schutz für alle Arbeitsblätter gleichzeitig zu aktivieren (oder zu deaktivieren), so dass diese Aufgabe schnell zeitraubend wird, es sei denn, Sie verwenden ein Makro...
Makro zur Rettung!
Ich verwende ein Makro, um alle Arbeitsblätter in einer Arbeitsmappe zu schützen, und ein anderes, um den Schutz aufzuheben. Um dies zu erhalten, laden Sie bitte meine kostenlosen Bonusmaterial für mein Buch „Avoid Excel Horror Stories“ herunter. Der VBA-Makrocode befindet sich in der Hauptdatei 'how2excel - example calculations and macros'.
Das Makro "protect sheets" löst auch ein weiteres Problem mit geschützten Blättern: Standardmäßig können Sie Zeilen- und Spaltengruppierungen auf geschützten Blättern nicht öffnen und schließen (ein Excel-Fehler), was frustrierend sein kann. Das mitgelieferte Makro schützt nicht nur Blätter, sondern ermöglicht auch das Öffnen und Schließen von Zeilen- und Spaltengruppierungen.
Bonus Tipp: Fügen Sie den Makros einen Kennwortschutz hinzu, um zu verhindern, dass unbefugte Benutzer das Kennwort im Makrocode herausfinden: Klicken Sie im Visual Basic-Editor mit der rechten Maustaste auf Makros, wählen Sie VBAProjekt-Eigenschaften, Registerkarte Schutz, Projekt für die Anzeige sperren, geben Sie zweimal das Kennwort ein.
Zusätzlicher Schutz: Struktur der Arbeitsmappe
Auf der Registerkarte "Schützen" befindet sich neben "Blatt schützen" ein Symbol für "Arbeitsmappe schützen". Damit können Sie verhindern, dass Benutzer die Struktur eines Arbeitsblatts ändern (z. B. Verschieben, Umbenennen, Löschen oder Hinzufügen von Arbeitsblättern), was eine Überlegung wert sein kann.
ERGEBNIS
In dem oben gezeigten, entsprechend geschützten Arbeitsblatt können die Benutzer nur in den grauen Eingabezellen Werte eingeben und ändern. Wenn sie versuchen, andere Zellen zu ändern, erhalten sie eine Fehlermeldung.
