Autor: Gary Knott

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, 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.

Gewinnprozent, prozentuale Veränderungen und Durchschnittspreise - alle drei sind für Geschäfte sehr nützlich, aber für jedes gibt es zwei Berechnungsmethoden, so dass das Risiko von Verwirrung und Fehlern besteht.

In diesem Blog gehe ich auf alle drei ein und erkläre jeweils die beiden Berechnungsmethoden und empfehle, welche zu verwenden ist.

Los geht's...

 

1. Bruttomarge % oder Aufschlag % ?

Beide Zahlen sind mit dem Bruttogewinn verwandt, dienen aber unterschiedlichen Zwecken und werden unterschiedlich berechnet.

Bruttomarge in %

• Die Bruttomarge gibt an, welcher Anteil Ihres Umsatzes als Bruttogewinn übrig bleibt.
• Sie ist nützlich für Vergleiche, z. B. zwischen zwei verschiedenen Produkten, zwei Geschäftsbereiche oder Unternehmen, zwei Jahren oder zwischen Plan- und Ist-Zahlen. Sie kann Ihnen auch sagen, um wie viel Sie die Verkaufspreise senken können und trotzdem einen Bruttogewinn erzielen.
• Die Berechnung ist Bruttogewinn / Umsatz.
• Im obigen Beispiel sind dies 400 / 1000 = 40 %.
  Das bedeutet, dass pro 100 Euro Umsatz 40 Euro Gewinn übrig bleiben (d.h. 40 %).
  
  

Aufschlag %

• Der Aufschlag hingegen gibt an, wie viel Sie zu Ihren Herstellungskosten - d. h. den Kosten für den Kauf oder die Herstellung der von Ihnen verkauften Produkte oder Dienstleistungen - hinzurechnen müssen, um Ihren Verkaufspreis zu erhalten.
• Sie ist nützlich für die Berechnung oder Bewertung von Verkaufspreisen und wird in der Regel auf einzelne Produkte oder Produktgruppen angewandt.
• Die Berechnung ist Bruttogewinn / Herstellungskosten; wenn Ihre Herstellungskosten als negative Zahl angezeigt wird, können Sie ein Minuszeichen zu Ihrer Berechnung hinzufügen, damit das Ergebnis eine positive Zahl ist.
• Im obigen Beispiel sind dies 400 / 600 = 67%.
  Das bedeutet, dass Sie für je 100 Euro Kosten 67 Euro aufschlagen, um Ihren Verkaufspreis zu erhalten. Bei 600 Euro Kosten würden Sie 600 * 67 % = 400 Euro addieren, was Ihrem Bruttogewinn entspricht.
  
  

Empfehlung

Im Allgemeinen empfehle ich die Marge in %. Die Verkaufspreise werden häufig im Vergleich zu den Wettbewerbern (marktbasierte Preisgestaltung) oder zu dem Wert, den Sie für Ihre Kunden sehen (wertbasierte Preisgestaltung), festgelegt. Das Konzept des Aufschlags ist dann nicht relevant, aber natürlich sollten Sie trotzdem Ihre Kosten berücksichtigen.

Die Bruttomarge in % steht auch im Einklang mit einer Analyse der "Einheits-Gewinn- und Verlustrechnung (GuV)", bei der alle GuV-Posten als Prozentsatz des Umsatzes berechnet werden. Dies erweitert den Ansatz für den Vergleich von z. B. zwei Jahren oder zwei Unternehmen vom Vergleich nur des Bruttogewinns auf den Vergleich aller GuV-Positionen.

 

2. Veränderung in Prozentpunkten oder Prozent?

Eine prozentuale Veränderung wird häufig verwendet, um z. B. Kosten zu planen oder zu bewerten. Wenn Sie monetäre Werte analysieren, gibt es in der Regel keine Verwirrung. Wenn die Kosten von 1.000 Euro auf 1.200 Euro gestiegen sind, haben wir einen Anstieg von 20% = 200 Euro Anstieg / 1.000 Euro.

Die Sache kann jedoch verwirrend werden, wenn man die Veränderung einer Prozentzahl vergleicht, wie z.B. (i) die Herstellungskosten als Prozentsatz des Umsatzes oder (ii) die Zinssätze, wie im folgenden Beispiel gezeigt. Hier ist der Zinssatz von 10% p.a. auf 12% p.a. gestiegen (unrealistische, aber einfache Zahlen zur Veranschaulichung).

Hinweis: Dieses Beispiel habe ich von der Website www.mathsisfun.com übernommen, vielen Dank dafür!

Hier können wir die Veränderung entweder in Prozentpunkten oder in Prozent berechnen und darüber sprechen, und wir müssen uns darüber im Klaren sein, was wir verwenden.

Veränderung in Prozentpunkten

• Der Zinssatz ist von 10 % auf 12 % gestiegen, so dass die Veränderung einfach 12 % - 10 % = 2 % beträgt, d. h. 2 Prozentpunkte und nicht 2 %.
  
  

Veränderung in Prozent

• Die prozentuale Veränderung beträgt jedoch 20 %, denn die Erhöhung um 2 %-Punkte kommt zu dem alten Satz von 10 % hinzu = 2 % / 10 % = 20 %.
• Betrachtet man die Zinskosten, so sind diese von 1.000 Euro p.a. auf 1.200 Euro gestiegen, also haben wir auch hier eine 20%ige Steigerung = 200 Euro Steigerung / 1.000 Euro.
  
  

Empfehlung

Beide Zahlen sind korrekt, aber Sie müssen sich darüber im Klaren sein, welche Sie verwenden. In der Regel sind wir an den monetären Auswirkungen interessiert, was uns die Veränderung in Prozent zeigt.

Auf der mathisfun-Website heißt es: "Im Zweifelsfall verwenden Sie beides. Zum Beispiel: "Die Zinssätze sind heute um 2 Prozentpunkte gestiegen, was einen Anstieg der Zinszahlungen um 20 % bedeutet.'"

 

3. Einfacher oder gewichteter Durchschnitt?

In diesem letzten Beispiel verkaufen wir zwei Produkte: ein kleines Widget und ein großes Widget. Die kleine Version wird für 10 Euro pro Stück und die große Version für 20 Euro pro Stück verkauft. Wie hoch ist der durchschnittliche Verkaufspreis?

Einfacher Durchschnitt

• Wenn Sie keine Informationen über die Anzahl der verkauften Einheiten haben (oder diese ignorieren), können Sie den einfachen Durchschnitt berechnen = (10 + 20) / 2 = 15 Euro
• Dies ist nur dann der Fall, wenn Sie von jedem Produkt die gleiche Anzahl von Einheiten verkaufen, was wahrscheinlich nicht der Fall ist.
• In den meisten Fällen vermittelt der einfache Durchschnitt ein falsches Bild des Durchschnittspreises, da wir die Anzahl der verkauften Einheiten jedes Produkts nicht berücksichtigt haben. Wenn wir diese berücksichtigen, kann dies dramatische Auswirkungen auf den Durchschnitt haben, wie wir jetzt sehen werden.
  
  

Gewichteter Durchschnitt

• Die korrekte Methode zur Berechnung des durchschnittlichen Verkaufspreises besteht darin, den Gesamtumsatz durch die Gesamtzahl der verkauften Einheiten zu teilen.
• In diesem Fall haben wir einen Gesamtumsatz von 1.000 Euro geteilt durch 95 verkaufte Einheiten, was einen gewichteten durchschnittlichen Verkaufspreis von 10,53 Euro ergibt.
• Diese Zahl ist viel niedriger als der einfache Durchschnitt von 15 Euro, weil sie nach der Anzahl der verkauften Einheiten gewichtet wurde: Wir haben viel mehr kleine Widgets als große Widgets verkauft, so dass der gewichtete Durchschnittspreis auf den Preis von 10 Euro für kleine Widgets hin gewichtet wird.
  
  

Empfehlung

Verwenden Sie den gewichteten Durchschnitt. Um diesen zu berechnen, müssen Sie die Gesamtzahlen verwenden, wie im obigen Beispiel gezeigt.

Wenn Sie nicht genügend Daten haben, um den gewichteten Durchschnitt zu berechnen, geben Sie an, dass es sich bei der von Ihnen angegebenen Zahl um einen einfachen Durchschnitt handelt. Und verwenden Sie ihn nicht für Berechnungen.

Standard-Excel

In Excel können Sie ganz einfach eine Liste von Monaten erstellen, indem Sie den ersten Monat eingeben und dann die Zelle mit Hilfe des kleinen Quadrats (Griff) unten rechts in der Zelle nach unten (oder nach rechts) ziehen. Dies funktioniert auch für abgekürzte Monatsnamen (die ersten drei Buchstaben des Namens) sowie für Wochentage. Dies ist möglich, da Excel für solche Zwecke geeignete Listen bereithält. Sie können Daten auch nach Spalten sortieren, die Tage oder Monate enthalten.

Benutzerdefiniertes Excel

Wäre es nicht toll, wenn Sie das Gleiche mit einer benutzerdefinierten Liste von z.B. Marken oder Regionen oder Prioritäten (hoch, mittel und niedrig) tun könnten, die in Ihrem Unternehmen verwendet werden?

Nun, das können Sie!

Einrichtung

Tippen Sie zunächst Ihre benutzerdefinierte Liste in Excel ein und gehen Sie dann auf Datei, Optionen, Erweitert, Allgemein, Benutzerdefinierte Listen bearbeiten...

Dann können Sie Ihre benutzerdefinierte Liste importieren. Alternativ können Sie Ihre Liste auch direkt in das Feld "Listeneinträge" eingeben.

Verwendung

Zurück in Excel können Sie ein Element in Ihre benutzerdefinierte Liste eingeben und dann den Griff ziehen, um die Liste zu vervollständigen.

Sie können auch anhand Ihrer benutzerdefinierten Liste sortieren.

1. Wählen Sie die Spalte in Ihren Daten aus, die die Einträge in Ihrer benutzerdefinierten Liste enthält.

2. Klicken Sie dann auf das Hauptsymbol für Sortieren.

3. Wählen Sie unter "Reihenfolge" die Option "Benutzerdefinierte Liste...". 
Wählen Sie schließlich die entsprechende Liste aus und klicken Sie auf OK.

Ihre Daten sind jetzt nach Ihrer benutzerdefinierten Liste sortiert.

Fantastisch!