2018 – April (Excel)

Anfangs des Monats habe ich auf Anraten meines Sohnes einen Youtube-Kanal erstellt und die ersten Excel-VBA-Videos erstellt. War am Anfang nicht ganz einfach und hier muss ich mich noch verbessern, aber inhaltlich sollten die Videos in Ordnung sein. Wenn Sie einen Blick auf meine Videos werfen wollen, dann sind Sie herzlich eingeladen, dies zu tun. Der Link zu meinem Kanal lautet:

https://www.youtube.com/channel/UCj4eJx1EE259Eh2Xjz9pi4Q

In der April-Ausgabe werden die folgenden Themen behandelt:

  • Kosten sparen: Weniger Farbverbrauch beim Ausdruck
  • Alles unter Kontrolle: Eingaben nur zulassen, wenn..
  • Fehler abfangen mit WENNFEHLER
  • Daten schützen: Der alternative Formelschutz

Kosten sparen: Weniger Farbverbrauch beim Ausdruck

Während einer Excel-Grundlagen-Inhouse-Schulung im letzten Jahr fragte mich ein Kursteilnehmer, ob es denn möglich sei, eine voll formatierte Tabelle für den Ausdruck zu entfärben. Seither würde er eben immer die Tabelle kopieren und in der Kopie die Farben entfernen.

Klar, das ist möglich und sogar ohne den Einsatz eines Makros. Schauen Sie sich dazu einmal die folgende Abbildung an.

 

 

 

 

 

 

 

 

 

 

 

Werfen Sie einen Blick in die Zelle B2. Dort ist ein Schalter integriert. In dieser Zelle wird eine Gültigkeitsliste integriert, aus der die Einträge „Ja“ und „Nein“ ausgewählt werden können. Dazu gehen Sie wie folgt vor:

  1. Setzen Sie den Mauszeiger in Zelle B2.
  2. Klicken Sie im Ribbon Daten auf das Symbol Datenüberprüfung.
  3. Im Dialog Datenüberprüfung stellen Sie im Dropdown Zulassen den Eintrag Liste
  4. Erfassen Sie im Feld Quelle die beiden möglichen Einträge Ja und Nein, getrennt mit einem Semikolon.
  5. Bestätigen Sie mit OK.

Wenn aus diesem neu angelegten Zellendropdown der Eintrag Ja ausgewählt wird, dann sollen alle Farben im Bereich A4:G14 verschwinden. Um diese Funktionalität einzubauen, gehen Sie wie folgt vor:

  1. Markieren Sie den Zellenbereich A4:G14
  2. Klicken Sie im Ribbon Start auf die Schaltfläche Bedingte Formatierung / Neue Regel.
  3. Klicken Sie im Listenfeld Regeltyp auswählen auf den Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden.
  4. Erfassen Sie die Formel =$B$2=“Ja“
  5. Klicken Sie die Schaltfläche Formatieren.
  6. Wechseln Sie auf die Registerkarte Ausfüllen.
  7. Klicken Sie das Symbol Keine Farbe
  8. Bestätigen Sie zweimal mit OK.
  9. Stellen Sie in Zelle B2 den Eintrag Ja

 

 

 

 

 

 

 

 

 

 

 

Wie Sie sehen können, kann man die Bedingte Formatierung in Excel auch für das Entfärben von Zellen einsetzen.

Alles unter Kontrolle: Eingaben nur zulassen, wenn…

Bei der nächsten Aufgabenstellung geht es um das Thema, wie Sie bedingt verhindern können, dass in einer Tabelle Änderungen vorgenommen werden dürfen. Schauen Sie sich dazu einmal die folgende Abbildung an:

 

 

 

 

 

 

 

 

 

 

 

Der Bereich B2:B13 darf nur dann geändert werden, wenn in Zelle F1 der Eintrag Ja eingestellt ist. Um diese Funktionalität in Excel einzupflanzen können Sie die Gültigkeitsprüfung von Excel wie folgt einsetzen:

  1. Markieren Sie den Zellenbereich B2:B13
  2. Klicken Sie im Ribbon Daten auf das Symbol Datenüberprüfung.
  3. Im Dialog Datenüberprüfung stellen Sie im Dropdown Zulassen den Eintrag Benutzerdefniert
  4. Erfassen Sie die Formel =$F$1=“Ja“
  5. Bestätigen Sie mit OK.
  6. Geben Sie einmal testweise in Zelle F1 den Text Nein
  7. Versuchen Sie einen Eintrag im Bereich B2:B13 zu ändern.

Fehler abfangen mit WENNFEHLER

Seit der Excel-Version 2007 gibt es die Tabellenfunktion WENNFEHLER. Diese Funktion erlaubt es Ihnen auf eine relativ schlanke Weise, Fehler in Excel abzufangen. Schauen Sie sich dazu einmal die Ausgangssituation in der folgenden Abbildung an.

 

 

 

 

 

 

 

 

 

 

 

Das es den Tarif 8 nicht gibt liefert die Tabellenfunktion SVERWEIS einen Fehlerwert #NV. Das NV steht hierbei für „Nicht vorhanden“. Das ist ja auch erst einmal in Ordnung. Stattdessen soll jedoch der Text „Tarif nicht vorhanden“ angezeigt werden. Ändern Sie dazu die bisherige Formel:

=SVERWEIS(B2;Tabelle1;2;FALSCH)

Wie folgt ab:

=WENNFEHLER(SVERWEIS(B2;Tabelle1;2;FALSCH);“Tarif nicht vorhanden!“)

Daten schützen: Der alternative Formelschutz

Der Tabellenschutz in Excel ist ja bekanntermaßen nicht unbedingt sicher. Daher kann man sich überlegen, ob man seine Daten (vor allem Formeln) nicht auf eine andere Art und Weise schützen möchte. In der folgenden Abbildung sollen alle Zellen mit Formeln geschützt werden.

Der Trick dabei ist, dass Sie ein sogenanntes Ereignis einsetzen, um den Datenschutz einzustellen. Immer wenn der Anwender dann versucht, auf eine Zelle, die eine Formel hat, zuzugreifen, automatisch der Mauszeiger in Zelle B1 z versetzen.Um dieses kurze Ereignis zu integrieren, gehen Sie wie folgt vor:

  1. Klicken Sie mit der rechten Maustaste auf den Tabellenreiter „Formel schützen“ und wählen den Befehl Code anzeigen aus dem Kontextmenü.
  2. Erfassen Sie das folgende Ereignis:

Immer, wenn Sie jetzt in Ihrer Tabelle eine Formelzelle selektieren, wird blitzschnell der Mauscursor in die Zelle B1 versetzt. Das bedeutet, dass Sie Ihre Formeln am Besten schützen können, wenn Sie verhindern, dass diese Formelzellen selektiert werden können. Und genau das macht dieses Ereignis: Es reagiert darauf, wenn der Cursor in der Tabelle versetzt wird. Über die Eigenschaft HasFormula wird ermittelt, ob die jeweilige Zelle eine Formel enthält. Wenn ja, dann erfolgt der Sprung in Zelle B1.

Hinweis: Damit dieses Ereignis dauerhaft einsetzbar ist, verwenden Sie als Dateityp Excel-Arbeitsmappe mit Makros (*.xlsm).