Wie findet man nicht-gesperrte Zellen in Excel?

Zwei Möglichkeiten, um in Excel Zellen zu erkennen, die keinen Zellschutz haben.
 

Wer seine Excel-Tabellen Kollegen oder anderen Anwendern zur Bearbeitung zur Verfügung stellt, verwendet sicherlich gerne den Blattschutz in Kombination mit dem Zellschutz.

Damit wird verhindert, dass versehentlich Formeln überschrieben werden oder Eingaben an unerwünschten Stellen erfolgen.

Muss die Tabelle später jedoch angepasst werden, kommen hinterher Fragen auf: Wie erkennt man, welche Zellen geschützt sind und welche nicht? Habe ich den Schutz auch bei keiner wichtigen Zelle vergessen?

In diesem Artikel zeige ich dir, wie du auch in großen Tabellen Klarheit zu diesen Fragen erlangst.

Nehmen wir also an, wir haben uns eine schöne Zeiterfassungstabelle erstellt, in der die Mitarbeiter ihre Arbeitszeiten eintragen sollen:

Beispiel: Zeiterfassungstabelle

Beispiel: Zeiterfassungstabelle

Um die Tabelle möglichst flexibel zu nutzen und die Eingaben zu erleichtern, sind einige Formeln hinterlegt, die z.B. Datum, Kalenderwoche und die jeweilige Arbeitszeit berechnen. Damit die Anwender diese Formeln nicht überschreiben, haben wir die frei zugänglichen Zellen entsperrt und dann den Blattschutz aktiviert:

Zellschutz entfernen

Zellschutz entfernen

Blattschutz aktivieren

Blattschutz aktivieren

Doch wie es der Teufel will, hat ein besonders findiger Kollege doch eine Formel aus einer Zelle gelöscht, die wir beim Zellschutz offensichtlich übersehen haben. Daher wollen wir etwaige weitere Schlupflöcher identifizieren und stopfen.

Möglichkeit 1: Die Such-Funktion

Um sich einen schnellen Überblick zu verschaffen, welche Zellen keinen Schutz haben, reicht die normale Suchfunktion in Excel aus. Hier erweitern wir das Suchfenster zunächst über die Schaltfläche “Optionen”:

Suchfenster aufrufen

Suchfenster aufrufen

Die gewünschte Funktion verbirgt sich dann hinter der Schaltfläche “Format…”:

Suchen nach Formaten

Suchen nach Formaten

Im Register “Schutz” entfernt man nun das Attribut “Gesperrt”, um nach ungeschützten Zellen zu suchen (standardmäßig haben alle Zellen das Attribut “Gesperrt”):

Gesperrt-Haken entfernen

Gesperrt-Haken entfernen

Dann schließen wir das Format-Fenster mit OK und starten die Suche über die Schaltfläche “Alle suchen”. Damit erhalte ich eine Liste aller Zellen, die nicht gesperrt sind:

Suche starten

Suche starten

Suchergebnisse

Suchergebnisse

Wie man sieht, enthält diese Liste auch die Zelleninhalte, so dass man etwaige Formeln sofort erkennt. Durch Anklicken der Zeile wird die betroffene Zelle ausgewählt und ich kann bei weiterhin geöffnetem Suchfenster für diese Zelle per Rechtsklick das “Zelle formatieren”-Fenster aufrufen und dort den Zellschutz aktivieren:

Korrektur "on-the-fly"

Korrektur “on-the-fly”

Anschließend kehrt man in das Suchergebnisfenster zurück und kann sich so Zeile für Zeile durch die Liste arbeiten und bei Bedarf aktiv werden.

Möglichkeit 2: Bedingte Formatierung

Über eine bedingte Formatierung ist es ebenfalls ein Kinderspiel, sämtliche nicht gesperrte Zellen zu finden und z.B. farbig zu hinterlegen. Dazu markieren wir den betreffenden Bereich (oder notfalls auch die komplette Tabelle) und rufen die bedingte Formatierung auf:

Bedingte Formatierung aufrufen

Bedingte Formatierung aufrufen

Nun legen wir eine neue Formatierungsregel an, die folgende Formel enthält:
=NICHT(ZELLE("Schutz";A1))

Formatierungsregel anlegen

Formatierungsregel anlegen

Wichtig: Sollte bei dir eine andere Zelle als A1 die gerade aktive Zelle sein, muss diese Zelladresse in der Formel verwendet werden!

Mit Hilfe der ZELLE-Funktion lassen sich alle möglichen Informationen über eine Zelle abfragen, eine davon ist eben der Zellschutz. Zurückgegeben wird der logische Wert WAHR, wenn die Zelle gesperrt ist oder FALSCH, wenn sie nicht gesperrt ist. Da wir nur die nicht gesperrten hervorheben wollen, kehren wir das Ergebnis mit der NICHT-Funktion um.

Und schon werden sämtliche ungeschützte Zellen eingefärbt:

Ungeschützte Zellen werden markiert

Ungeschützte Zellen werden markiert

Zum Schluß noch ein Hinweis:
Vor allem, wenn man diese Formatierungsregel auf das gesamte Arbeitsblatt angewendet hat empfiehlt es sich sehr, die Regel hinterher wieder zu entfernen. Grund: Jede in einer bedingten Formatierung eingesetzte Funktion wird automatisch zu einer sogenannten volatilen Funktion. Insbesondere bei großen Tabellen verbraucht das unnötige Rechenleistung und kann Excel unter Umständen merklich ausbremsen.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.


Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.