KPI-Karten und bedingte Hervorhebungen

Eine KPI-Karte ist eine einzelne, hervorgehobene Kennzahl, die dem Leser genau das Wichtigste mitteilt: Gesamtumsatz, Bestellungen heute oder durchschnittlicher Bestellwert. Dashboards beginnen meist mit einer Reihe solcher Karten.

Eine gute Karte besteht aus drei Teilen: einer eindeutigen Bezeichnung, der großen Zahl aus einer Formel und oft einem kleinen Vergleich, etwa der Veränderung gegenüber dem Vorzeitraum.

In dieser Lektion erstellen Sie KPI-Zahlen mit Aggregationsformeln, fügen Vergleiche zwischen Zeiträumen hinzu und verwenden bedingte Logik, um Werte hervorzuheben, die Aufmerksamkeit benötigen.

Beginnen Sie mit der Kernkennzahl. Der Gesamtumsatz aus dem Arbeitsblatt Sales ist einfach eine SUM-Berechnung über die Betragsspalte.

Platzieren Sie diese Formel in der Wertzelle der Karte:

Für eine Bestellungsanzahl verwenden Sie COUNTA für eine Spalte, die immer einen Wert enthält, etwa eine Bestell-ID. Die Bezeichnung der Karte, eine einfache Textzelle darüber, erklärt die Bedeutung der Zahl. Beschränken Sie jede Karte auf eine Zahl, damit sie sofort erfassbar ist.

Viele KPIs sind Verhältniszahlen. Der durchschnittliche Bestellwert ergibt sich aus dem Gesamtumsatz geteilt durch die Anzahl der Bestellungen. Erstellen Sie ihn aus zwei Aggregationen.

Eine kompakte Möglichkeit ist die direkte Verwendung von AVERAGE:

Wenn Sie den Wert lieber ableiten möchten, dividieren Sie den Gesamtwert Ihrer Karte durch den Wert Ihrer Zählkarte. In beiden Fällen zeigt diese Karte nun die typische Verkaufsgröße an und wird aktualisiert, sobald sich die Daten ändern.

Eine KPI ist neben einem Ziel aussagekräftiger. Angenommen, Ihr monatliches Umsatzziel steht in Zelle B1. Berechnen Sie, wie weit Sie über oder unter dem Ziel liegen.

Die Abweichung ergibt sich aus dem aktuellen Wert minus dem Zielwert:

Sie können den Wert auch als Prozentsatz des Ziels ausdrücken: =SUM(Sales!C2:C500)/B1. Der Wert 1,12 zeigt dem Leser, dass Sie 112 Prozent des Ziels erreicht haben. Der Vergleich verwandelt eine einfache Zahl in eine aussagekräftige Geschichte.

Leser sehen gerne, ob sich etwas entwickelt. Berechnen Sie die prozentuale Veränderung gegenüber einer vorherigen Periode. Nehmen wir an, die Summe der aktuellen Periode steht in D2 und die der letzten Periode in D3.

Die Wachstumsformel lautet:

Wenn der aktuelle Wert 120 und der vorherige 100 beträgt, erhalten Sie 0,2, also ein Wachstum von 20 Prozent. Formatieren Sie die Zelle als Prozentsatz. Ein kleiner Aufwärts- oder Abwärtshinweis wie dieser macht aus einer statischen Karte auf einen Blick einen Trend.

Mit IF können Sie die Richtung durch Wörter oder Symbole anzeigen. Lesen Sie die Änderungszelle aus und wählen Sie eine Bezeichnung.

Dies gibt einen Aufwärts- oder Abwärtshinweis zusammen mit dem Prozentsatz zurück:

Wenn die Änderung in E1 positiv ist, sehen Sie einen Aufwärtshinweis; andernfalls einen Abwärtshinweis. Durch das Verbinden von Text und einer formatierten Zahl bleibt die Karte kompakt und gut lesbar, ohne dass ein Diagramm erforderlich ist.

Bedingte Formatierung ändert die Farbe einer Zelle anhand einer Regel, sodass wichtige Werte sofort auffallen. Wählen Sie Ihre KPI-Zellen aus, öffnen Sie die bedingte Formatierung und fügen Sie eine Regel hinzu, die von einer Formel gesteuert wird.

Um jede Karte, die unter dem Ziel liegt, rot zu kennzeichnen, verwenden Sie eine Formelregel wie:

Zellen, für die die Regel TRUE ergibt, erhalten das von Ihnen gewählte Format. So wird ein Dashboard rot, wenn der Umsatz das Ziel verfehlt, und grün, wenn er es übertrifft, ohne dass jemand die Zahlen genau lesen muss.

Manchmal soll die Kennzeichnung als Text in der Zelle selbst erscheinen und nicht nur durch eine Farbe. Mit IF und Vergleichsoperatoren können Sie ein Statuswort erzeugen.

Um die Leistung im Vergleich zum Ziel zu kennzeichnen:

Bei erreichtem Ziel wird Im Plan angezeigt, bei einer Unterschreitung Im Rückstand. Eine solche Statusspalte lässt sich gut mit einer bedingten Formatierung kombinieren, die das Wort einfärbt und so sowohl ein visuelles als auch ein schriftliches Signal liefert.

In echten KPIs gibt es oft mehr als zwei Zustände: gut, Warnung und kritisch. Die Funktion IFS prüft Bedingungen der Reihe nach und gibt den ersten Treffer zurück. Dadurch ist sie übersichtlicher als verschachtelte IF-Funktionen.

Um die Leistung in drei Stufen einzuordnen:

IFS prüft die Bedingungen von oben nach unten. Setzen Sie daher die strengste Bedingung an die erste Stelle. Kombinieren Sie jede Stufe mit einer Farbre gel, und Ihre Karten vermitteln den Status auf einen Blick.

Über einzelne Regeln hinaus färben Farbskalen einen Zahlenbereich entlang eines Farbverlaufs ein, sodass die höchsten und niedrigsten Werte ohne zusätzliche Formel hervorstechen. Sie eignen sich ideal für eine Spalte mit regionalen Summen oder Tageswerten.

Wählen Sie den Bereich aus, öffnen Sie die bedingte Formatierung und wählen Sie eine Farbskala, etwa von Grün bis Rot.

• Die größten Werte werden grün hervorgehoben, die kleinsten rot, und die mittleren Werte gehen dazwischen ineinander über.
• Die Skala wird automatisch neu berechnet, sobald sich die Daten ändern.

Farbskalen ergänzen KPI-Karten besonders gut: Die Karten liefern die zentrale Kennzahl, während die eingefärbte Spalte zeigt, wo jeder Wert innerhalb der Verteilung liegt.

Ordnen Sie Ihre Karten oben im Dashboard in einer übersichtlichen Zeile an. Jede Karte ist ein kleiner Block: eine Beschriftungszelle, eine Wertformel und darunter ein Vergleich oder Status.

• Karte 1: Gesamtumsatz, =SUM(Sales!C2:C500), mit einer Zielabweichung.
• Karte 2: Bestellungen, =COUNTA(Sales!A2:A500).
• Karte 3: Durchschnittlicher Bestellwert, =AVERAGE(Sales!C2:C500), mit einem Wachstumsprozentsatz.

Die bedingte Formatierung färbt die Vergleichszellen ein. Die Zeile liefert eine sofortige Zusammenfassung, bevor der Leser zu den Details scrollt.

Sie haben die zentrale Kennzahlenebene eines Dashboards erstellt:

• Aggregationsformeln wie SUM, COUNTA und AVERAGE erzeugten die großen KPI-Zahlen.
• Abweichungs- und Wachstumsformeln fügten Vergleichsinformationen zu einem Zielwert und einem vorherigen Zeitraum hinzu.
• IF und IFS verwandelten Zahlen in Statuswörter wie Im Plan, Beobachten und Kritisch.
• Bedingte Formatierung mit Formelregeln färbte Zellen automatisch ein, um wichtige Werte zu kennzeichnen.

Zusammen mit den Zusammenfassungstabellen, Formel-Pivots und interaktiven Dropdown-Listen aus den vorherigen Lektionen können Sie nun ein vollständiges, live aktualisiertes, formelgesteuertes Dashboard erstellen.