Kategorie: Blog DE

Hier sind meine sechs besten Tipps. Sie können sie hier als Checkliste herunterladen.

1. Sicherstellen, dass die Benutzer wissen, welche Datei sie verwenden müssen (Versionskontrolle)

Vorher

Nachher

• Benennen Sie Dateien eindeutig mit Datum oder Versionsnummer.
• Verschieben Sie alte Dateien in Unterordner, so dass der Hauptordner nur noch die endgültige Version enthält. Verschieben Sie z.B. frühere Versionen, die verschickt oder in Besprechungen verwendet wurden, in "Archiv" und Backups in "zzz alt".

 

2. Beschränken Sie den Zugriff nur auf autorisierte Benutzer

• Speichern Sie Dateien auf einem Server mit eingeschränktem Zugriff, z.B. nur für die Finanzabteilung.
• Fügen Sie ggf. ein Passwort zum Öffnen oder Ändern hinzu: Datei, Speichern unter, Weitere Optionen (unter Dateiname und -typ), Extras (neben der Schaltfläche Speichern), Allgemeine Optionen...
  

 

3. Entwickeln Sie Arbeitsmappen nach Best-Practice-Regeln für Klarheit und Benutzerfreundlichkeit

• Fügen Sie ein Deckblatt hinzu, auf dem die Kontaktperson, der Status der Arbeitsmappe und der Zweck angegeben sind, sowie eine Liste der Blätter mit kurzen Beschreibungen , damit die Benutzer einen guten Überblick erhalten und wissen, an wen sie sich bei Fragen wenden können.
• Verwenden Sie eine klare Struktur der Arbeitsmappe mit separaten Abschnitten für Eingaben, Berechnungen und Ausgaben , damit die Benutzer die Struktur verstehen und sich leicht zurechtfinden können.
• Fügen Sie ein Anleitungsblatt hinzu, damit die Benutzer wissen, wie sie die Arbeitsmappe verwenden können.
• Siehe meinen separaten Blog zum Thema 'Garys goldene Grundregeln' für weitere Informationen.

 

4. Markieren Sie Eingabezellen deutlich, damit Benutzer wissen, was sie ändern können

• Verwenden Sie eine einheitliche Formatierung, z.B. hellgrau mit weißem Rand, deaktivierter Schutz.
• Definieren und verwenden Sie Formatvorlagen (im Menüband Start) für eine einfache und effiziente Nutzung.
  
  • Definieren: Klicken Sie auf den Dropdown-Pfeil unten rechts und wählen Sie 'Neue Zellenformatvorlage...'.
  • Verwenden: Wählen Sie die zu formatierenden Zellen aus und klicken Sie dann auf die entsprechende Zellenformatvorlage im Menüband.

 

5. Verwenden Sie die Datenüberprüfung, um Eingaben auf gültige Einträge zu beschränken

• Eingaben einschränken, z.B.
  • auf Dezimalzahlen (inkl. Prozentsätze)
  • positive (>=0) oder negative Zahlen (<=0)
  • ganze Zahlen, z.B. zwischen 1 und 12 für Monate (s. Screenshot oben)
  • eine Liste (Auswahlliste)
  • benutzerdefiniert z.B. Datum ist ein Montag.
• Siehe mein Video für Details.

 

6. Arbeitsblätter schützen, so dass nur Eingabezellen geändert werden können

• Verwenden Sie Makros für mehr Effizienz.
  • Makro #1: Alle Blätter beim Öffnen der Datei schützen
  • Makro #2: Entfernen Sie den Blattschutz durch Eingabe eines Passworts, um Änderungen zu ermöglichen
• Siehe meinen separaten Blog für weitere Informationen.
• Fügen Sie den Makros einen Kennwortschutz hinzu, um zu verhindern, dass unbefugte Benutzer das Kennwort 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.

 

Nützliche Links

• Hier sehen Sie mein einstündiges Webinar-Video (auf Englisch), in dem alle sechs Tipps erklärt werden.
• Oder nutzen Sie diesen Link zu Plum Solutions (die freundlicherweise das Webinar organisiert haben), um auch Zugang zu den Webinar-Downloads zu erhalten.
• Für weitere Details zur Vermeidung von Fehlern aller Art lesen Sie bitte mein Buch "Vermeiden Sie Excel-Horrorgeschichten", erhältlich bei Amazon.

 

Es ist wieder Weihnachtszeit, die Zeit der Freude. Sie können ein wenig festliche Fröhlichkeit auf Ihren PC bringen, indem Sie mit der Magie von Zufallszahlen in Kombination mit bedingter Formatierung Weihnachts-Adventskerzen simulieren. Warum probieren Sie es nicht selbst aus?

Ich werde Ihnen zeigen, wie Sie in wenigen Schritten zwei Versionen entwickeln können. Alternativ können Sie die fertige Version auch herunterladen: Weihnachtskerzen v3.

Basisversion

Erläuterung: Alle vier Kerzen sind immer an.

Schritt 1 - Malen Sie Ihre Kerzen

Machen Sie die Spalten schmal und fügen Sie dann Hintergrundfarben für den Sockel, die Kerzen und die Dochte hinzu. Fügen Sie dann eine Nachricht am unteren Rand sowie festliche Symbole und Formen (mit dem Menüband Einfügen) hinzu, wenn Sie möchten.

Schritt 2 - Formeln hinzufügen

Geben Sie diese Formeln in Zellen ein, die Flammen zeigen sollen:

=ZUFALLSBEREICH(50, 100) für hellere Flammenfarben
=ZUFALLSBEREICH(1, 50) für dunklere Flammenfarben in der Mitte, oberhalb des Dochts

Diese Formeln erzeugen bei jeder Berechnung Zufallszahlen.  Wir werden diese Zahlen verwenden, um die Farben der Flammen festzulegen.

Schritt 3 - Bedingte Formatierung hinzufügen

Wählen Sie einen Zellenblock aus, der die "Flammenzellen" enthält, und fügen Sie eine bedingte Formatierung wie folgt hinzu.

Schritt 4 - Aufräumen

Für ein saubereres Aussehen...

• Nicht verwendete Zeilen und Spalten markieren und ausblenden (Rechtsklick, Ausblenden)
• Entfernen Sie die Gitternetzlinien (Menüband "Ansicht", Abschnitt "Anzeigen", deaktivieren Sie das Feld "Gitternetzlinien")
• Blatt schützen (Menüband Überprüfen) und deaktivieren Sie alle Optionen, um die Zellauswahl auszublenden.
  
  

Schritt 5 - Animieren

Drücken Sie F9, um die Farben zu ändern.  Oder halten Sie F9 gedrückt, um zu animieren.

 

Version für Adventssonntage

Erläuterung: Jede Kerze leuchtet nur, wenn der entsprechende Adventssonntag erreicht ist.

Schritt 1 - Erstellen oder Kopieren der Basisversion

Heben Sie gegebenenfalls den Schutz des Blattes auf (Menüband "Überprüfen"), markieren Sie alle Spalten und blenden Sie sie wieder ein.

Schritt 2 - Daten hinzufügen

• Heute: Formel eingeben =HEUTE()
• Adventssonntage: Geben Sie die vier Daten ein
  
  

Schritt 3 - Flammenformeln ändern

Die Formeln müssen nun prüfen, ob das Datum des jeweiligen Adventssonntags erreicht ist. Jede Flamme testet also ein anderes Adventsdatum, z.B.

=WENN($AR$10>=$AR$11; ZUFALLSBEREICH(50;100); 0) für hellere Flammenfarben in der ersten Flamme.

Ist das entsprechende Datum noch nicht erreicht, wird der Zellenwert auf Null gesetzt.

Schritt 4 - Bedingte Formatierung anpassen

Fügen Sie ein zweites bedingtes Format hinzu (Tipp: Duplizieren Sie die erste Regel und ändern Sie sie).

Ist der Wert der Zelle gleich Null, wird die Farbe so formatiert, dass sie dem Hintergrund entspricht, so dass keine Flamme sichtbar ist.

Aufräumen und animieren

Wie in der Basisversion.

 

Version mit abbrennenden Kerzen

In der Download-Datei finden Sie auch eine weitere Version, in der die Kerzen allmählich abbrennen!

Ich habe diese Version auf der Grundlage (einer Kopie) der Version für die Adventssonntage erstellt und die Funktion BEREICH.VERSCHIEBEN verwendet, um das Abbrennen auf der Grundlage der Anzahl der Tage, die jede Kerze gebrannt hat, und der Eingabe, wie viele Tage jede Zeile des Kerzenbildes dauert (z.B. 4 Tage), zu simulieren, d.h. die "Standardkerze" um eine Anzahl von Zeilen versetzt.

 

Viel Spaß!

 

 

Hier sind zwei tolle Ideen, um dynamische Outputs z.B. für Ihre Dashboards zu erstellen: Kennzahl-Karten und dynamische Diagrammtitel, die sich automatisch aktualisieren, wenn sich Ihre Daten ändern!

 

KENNZAHL-KARTE

Eine Kennzahl-Karte zeigt einen einzelnen Schlüsselwert an, z.B. den Umsatz oder eine Kennzahl - einfach, aber großartig! Sie werden häufig in Power BI verwendet, aber Sie können sie auch in Excel erstellen. So geht's...

• Fügen Sie ein Textfeld (Einfügen, Textfeld) oder eine Form (Einfügen, Form) ein - ich mag abgerundete Rechtecke - und formatieren Sie es nach Ihren Wünschen.
• Wählen Sie die Form aus und geben Sie in der Formelleiste =(Zellbezug).
• Wenn es sich um eine Zahl handelt, ist das Format im Textfeld oder in der Form dasselbe wie in der Quellzelle 😊.
• Extra-Tipp: Sie können dann Text um die Zahl herum hinzufügen, indem Sie in die Form doppelklicken und tippen!

 

GRAFIKTITEL

Fügen Sie einem Diagramm einen dynamischen Titel hinzu, z.B. mit Datum. Anstatt den Standarddiagrammtitel zu akzeptieren oder manuell einen eigenen Titel einzugeben, können Sie einen Diagrammtitel mit einer Zelle verknüpfen.

• Markieren Sie einfach das Feld Diagrammtitel und geben Sie in der Formelleiste =(Zellbezug) ein.
• Sie können hier weder eine Formel erstellen noch Funktionen verwenden. Das können Sie aber in der Quellzelle tun…

• Im gezeigten Beispiel möchte ich das neueste Datum in den Diagrammtitel aufnehmen. Sie können dies automatisch mit der Funktion MAX für die Datumsspalte in Ihren Quelldaten erreichen oder (wie hier gezeigt) eine Eingabezelle (Zelle B25) verwenden.
• Ich habe einen dynamischen Titel (in Zelle C25) unter Verwendung des Datums (in Zelle B25) erstellt, der dann im Diagrammtitel erscheint. Fantastisch!
  • Sie können diesen Text in Teilen aufbauen und sie mit & verbinden.
  • Wenn Sie sich auf eine Zahl oder ein Datum beziehen, müssen Sie dies mit der Funktion TEXT formatieren.
  • Um das richtige Format zu erhalten, markieren Sie zunächst eine Zelle mit dem gewünschten Format, gehen Sie in die Zelle formatieren (Shortcut Strg 1); wählen Sie auf der Registerkarte „Zahl“ die Option "Benutzerdefiniert" und kopieren Sie dann den Formatierungscode aus dem Feld Typ.
    
  • Fügen Sie diesen Code in Ihre TEXT-Formel innerhalb der Anführungszeichen ein.
  • In dem gezeigten Beispiel lautet die endgültige Formel
    ="Umsatz Monat bis heute (EURk), zum " & TEXT(B25; "TT.MM.JJJJ")
• Extra-Tipp: Sie können Ihre dynamische Titelzelle hinter dem Chart verstecken, wenn Sie das möchten.

 

DAS ENDERGEBNIS

Bereichsnamen sind einfach einzurichten und zu verwenden und können die Nutzung Ihrer Spreadsheets erleichtern.

Bereichsnamen erstellen

Wählen Sie eine Zelle oder einen Zellbereich aus und geben Sie dann einen kurzen, aber aussagekräftigen Bereichsnamen in das Namensfeld (über Zelle A1, neben der Formelleiste) ein, z.B. Startdatum oder Steuersatz. Der Name darf keine Leerzeichen enthalten (verwenden Sie ggf. einen Unterstrich) und bestimmte Namen sind nicht zulässig, z.B. J5, da dies wie ein Zellbezug aussieht.

 

Verwenden Sie Ihre Bereichsnamen

Hier sind drei praktische Anwendungen.

1. Vereinfachen Sie Formeln

Geben Sie einfach den Bereichsname in Ihre Formel ein, und er wird in der Liste zusammen mit den Funktionsnamen angezeigt (s. Screenshot). Alternativ können Sie auch F3 drücken, um eine Liste zur Auswahl zu erhalten. Dies kann die Lesbarkeit Ihrer Formeln verbessern. In dem gezeigten Beispiel kann das Zahlungsdatum nicht in der Vergangenheit liegen, d.h. vor dem Startdatum des Tools.

 

2. Wählen Sie ganz einfach einen Bereich zum Kopieren aus

Hier habe ich einen Bereich namens AR definiert, der alle Einträge im AR-Bericht abdeckt. Ich kann diesen benannten Bereich über das Namensfeld (über Zelle A1, neben der Formelleiste) auswählen. Diesen kann ich dann (manuell oder mit Hilfe eines Makros) z. B. in einen anderen Bereich AR_alt kopieren, um Vergleiche anstellen zu können.

 

3. Erleichtern Sie die Navigation

(i) Verwenden Sie das Namensfeld

Verwenden Sie einfach das Dropdown-Menü des Namensfeldes (über Zelle A1, neben der Formelleiste) und wählen Sie einen Bereichsnamen aus, um zu ihm zu springen.

 

(ii) Erstellen Sie einen Hyperlink

Alternativ können Sie einen Hyperlink erstellen, indem Sie Einfügen, Link, Link einfügen… (am Ende der Liste), Aktuelles Dokument, einen Bereichsnamen auswählen (unter "Festgelegte Namen") --> ein anklickbarer Hyperlink wird erstellt. Klicken Sie einfach darauf, um zum Bereich zu springen!

Bonustipp: Sie können diesen Hyperlink bei Bedarf an andere Stellen in Ihrer Arbeitsmappe kopieren.

 

Bearbeiten und verwalten Sie Ihre Bereichsnamen

Wenn Sie Ihre Bereiche überprüfen oder anpassen möchten, verwenden Sie einfach den Namensmanager.

 

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: (1) Zellen schützen und (2) den Schutz einschalten.

1. ZELLEN SCHÜTZEN

Alle Zellen, in denen Änderungen verhindert werden (d. h. Änderungen sind nicht zulässig), müssen als "gesperrt" formatiert werden, und alle Zellen, in denen Änderungen zulässig sind (Eingabezellen und Dropdowns), müssen als nicht "gesperrt" formatiert werden. Um den Status einer bestimmten Zelle zu sehen, markieren Sie sie, formatieren Sie die Zellen (Tastenkombination Strg 1) und wählen Sie die Registerkarte "Schutz", wie in der folgenden Abbildung gezeigt.

Gesperrt: Standardmäßig sind alle Zellen in einer neuen Arbeitsmappe gesperrt. Das bedeutet, dass Sie alle Eingabezellen, einschließlich aller Dropdowns, entsperren müssen. Am besten tun Sie dies, während Sie Ihre Kalkulationstabelle entwickeln, da das spätere Entsperren von Eingabezellen ein zeitaufwändiger und fehleranfälliger Prozess ist - Sie werden zwangsläufig einige Zellen übersehen, und gemäß Murphys Gesetz werden dies genau die Zellen sein, die der Benutzer ändern möchte.

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 einer Tabellenkalkulation empfehle ich diese Option nicht.

 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. SCHUTZ EINSCHALTEN

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 betreffende Arbeitsblatt aus, navigieren Sie zur Multifunktionsleiste "Schützen" 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.

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.

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.