Excel-Thermometer-Diagramm - Kostenloser Download und Erstellung

In diesem Tutorial wird gezeigt, wie Sie ein Thermometerdiagramm in allen Excel-Versionen erstellen: 2007, 2010, 2013, 2016 und 2022.

Ein Thermometerdiagramm ist eine spezielle Modifikation eines gestapelten Säulendiagramms. Die Säulen sind übereinander angeordnet und ähneln optisch einem Thermometer, das sich mit zunehmendem Fortschritt auf Ihr Ziel hin füllt.

Jedes Mal, wenn Sie den Fortschritt in Richtung eines Ziels verfolgen müssen – sei es Finanzierung, Umsatz, Umsatz oder andere relevante Kennzahlen – bietet dieses Diagramm eine einfache und effektive Möglichkeit, diese Daten zu visualisieren.

Aber ein einfaches Thermometer-Diagramm reicht einfach nicht mehr aus (komm schon, das ist 2022!).

In dieser ausführlichen Schritt-für-Schritt-Anleitung erfahren Sie, wie Sie ein bemerkenswert vielseitiges Thermometer-Zieldiagramm mit einem ausgefallenen Etikett erstellen, das auch dann funktioniert, wenn Ihre Ergebnisse ein festgelegtes Ziel überschreiten.

Schau es dir einfach an:

Wie Sie gleich sehen werden, erfordert das Erstellen eines beeindruckenden Thermometerdiagramms jede Menge Aufwand. Aus diesem Grund haben wir das Chart Creator Add-In entwickelt, ein Tool zum Erstellen erweiterter Excel-Diagramme mit nur wenigen Klicks.

Kommen wir also zum Wesentlichen.

Einstieg

Stellen Sie sich zur Veranschaulichung vor, Sie haben nebenbei ein E-Commerce-Geschäft eröffnet und möchten ein Thermometerdiagramm erstellen, um die Leistung des Geschäfts im Vergleich zu Ihrem angegebenen Umsatzziel zu verfolgen. Im Jahr 2022 hatten Sie einen ziemlich guten Lauf.

Das Geschäft erzielte einen Umsatz von 21.734 US-Dollar, was Ihr ursprüngliches Ziel leicht übertraf (Sie können später eine Flasche Champagner knallen lassen).

Sehen Sie sich jedoch den Datensatz für das Diagramm an:

Bevor wir mit dem Erstellen des Diagramms fortfahren, müssen wir jeden Datenpunkt aufschlüsseln, damit Sie die Schritte mithilfe Ihrer Daten leicht nachvollziehen können.

Der Datensatz hat zwei Spalten:

• Originale Daten: Diese Spalte stellt Ihre tatsächlichen Daten dar, was auch immer es sein mag. Lass es einfach so wie es ist.
• Diagrammeingaben: Hier passiert die ganze Magie. Die Tabelle enthält alle Sonderwerte, die Sie zum Erstellen des Diagramms verwenden. Außerdem ein wichtiger Hinweis: die Kategorien müssen genau so angeordnet werden, wie auf dem Screenshot oben gezeigt!

Sie fragen sich vielleicht, woher alle Werte in der zweiten Tabelle stammen. Nun, Sie müssen sie tatsächlich mit den unten aufgeführten speziellen Excel-Formeln berechnen (Überraschung, Überraschung).

Leider gibt es keinen anderen Weg, aber die folgenden Anweisungen sollten es ziemlich einfach machen, sie zu befolgen.

Die Reise beginnt also!

Schritt 1: Richten Sie die Hilfstabelle ein.

Als Dreh- und Angelpunkt, der alles zusammenhält, hat jedes der Elemente der zweiten Tabelle seinen Zweck. Lassen Sie uns diese bösen Jungs genauer behandeln.

Zielumsatz: Dieser Datenpunkt sollte mit dem Zielwert übereinstimmen (B7). So einfach ist das.

Formel für den Gesamtumsatz: Diese IF-Funktion verhindert, dass die Datenmarker falsch übereinander gestapelt werden, falls das Ergebnis das Ziel überschreitet:

1	=WENN(B6<=B7, B6, B7)

Hier ist die dekodierte Version basierend auf den Originaldaten:

1	=WENN({Gesamt}<={Ziel}, {Gesamt}, {Ziel})

Übersetzung in die menschliche Grundsprache: Wenn das Ergebnis (B6) ist kleiner oder gleich dem Ziel (B7), gibt die Formel den Ergebniswert (B6) – in unserem Fall alles, was unter die Marke von 20.000 USD fällt. Umgekehrt, wenn das Ergebnis (B6) überschreitet das Ziel (B7), gibt die Formel den Zielwert (B7).

Kurzer Tipp: Klicken Sie auf „Buchhaltungsnummernformat“ Taste (Registerkarte "Startseite" > Nummerngruppe), um die Funktionsausgabe als Währung anzuzeigen.

Übererfüllt: Dies WENN-Funktion berechnet, ob Ihr Ziel übererfüllt ist oder nicht. Wann immer dies geschieht, subtrahiert die Formel das Ziel (B7) aus dem Ergebnis (B6) und gibt den Rest zurück. Andernfalls zeigt die Formel einfach Null an:

1	=WENN(B7<=B6, B6-B7, 0)

Die entschlüsselte Version:

1	=WENN({Ziel}<={Gesamt}, {Gesamt}-{Ziel}, 0)

Fortschritt: Dieser ist ziemlich selbsterklärend. Wir müssen den Fortschritt als Prozentsatz des Ziels für unser ultra-fancy-Label messen. Teilen Sie einfach das Ergebnis (B6) durch das Ziel (B7) und formatieren Sie die Ausgabe als Prozentsatz - klicken Sie auf das „Prozent Stil“ Taste (Registerkarte "Startseite" > Nummerngruppe).

1

=B6/B7

Entschlüsselte Version:

1	={Gesamt}/{Ziel}

Etikett: Das i-Tüpfelchen. Dieser kombiniert die numerischen und prozentualen Werte, die den Fortschritt in Richtung Ihres Ziels darstellen, und formatiert sie ordentlich. Um es durchzuziehen, wenden wir uns der TEXT-Funktion zu (Excel-Neulinge, auf Schlag gefasst):

1	=TEXT(B6, "$#0.00") & " (" & TEXT(E5, "##0%") & ")"

Und die entschlüsselte Version:

1	=TEXT({Gesamt}, "{Wert als Währung formatieren}") & " (" & TEXT({Fortschritt}, "{Wert als Prozentwert formatieren}") & ")"

Wenn das entmutigend erscheint, lesen Sie den oben verlinkten Artikel, der die Funktion behandelt.

So sieht es in der Praxis aus:

Puh, das Schlimmste liegt hinter uns. Um das Ganze zusammenzufassen, werfen Sie einen zweiten Blick auf die Formeln:

Karteneinstellung	Formel
Formel für den Gesamtumsatz	=WENN(B6<=B7, B6, B7)
Übererfüllt	=WENN(B7<=B6, B6-B7, 0)
Fortschritt	=B6/B7
Etikett	=TEXT(B6, „$#0.00″) & „(“ & TEXT(E5, „##0%“) & „)“

Dort angekommen, fahren Sie mit dem nächsten Schritt fort.

Schritt #2: Erstellen Sie ein gestapeltes Säulendiagramm.

1. Markieren Sie den Zellbereich E2: E4 in dem Wert
2. Wähle aus Einfügung
3. Drücke den "Säulen- oder Balkendiagramm einfügen" Symbol.
4. Wählen "Gestapelte Spalte.”

Schritt #3: Stapeln Sie die Datenreihen übereinander.

Sobald das Diagramm angezeigt wird, ordnen Sie die Datenreihen neu an:

1. Klicken Sie auf das Diagramm.
2. Wechseln Sie zum Entwurf
3. Wählen "Zeile/Spalte wechseln.”

Schritt 4: Ändern Sie die Farben der Datenmarkierungen.

Um das Diagramm in ein Thermometer zu verwandeln, ändern Sie zunächst die Farbe der Datenmarkierungen. Erstellen Sie zuerst die Datenmarkierung für Serie 1 "Zielumsatz" (E2) transparent und setzen Sie Randlinien darum.

Klicken Sie mit der rechten Maustaste auf den Datenmarker, der den „Zielumsatz“ darstellt (E2-der untere Abschnitt des Diagramms) und wählen Sie „Datenreihen formatieren.”

Gehen Sie im angezeigten Aufgabenbereich wie folgt vor:

1. Navigieren Sie zum Füllen & Linie
2. Unter "Füllen," auswählen "Keine Füllung.”
3. Unter "Grenze," wählen "Durchgezogene Linie.”
4. Unter "Grenze," drücke den Füllfarbe Symbol, um die Farbpalette zu öffnen und auszuwählen rot.
5. Stellen Sie die Breite Wert zu 2 pt.

Färben Sie die anderen Datenmarker wie folgt ein:

1. Für Serie 2 „Gesamtumsatzformel“ (E3):
   1. Wählen Sie die Datenmarkierung (E3) mit dem Datenreihen formatieren Aufgabenbereich noch geöffnet.
   2. Unter "Füllen," wählen "Feste füllung.”
   3. Drücke den Füllfarbe Symbol und wählen Sie rot.
2. Für Serie 3 „Übererfüllt“ (E4):
   1. Wählen Sie die Datenmarkierung (E4) mit dem Datenreihen formatieren Aufgabenbereich noch geöffnet.
   2. Unter "Füllen," wählen "Feste füllung.”
   3. Drücke den Füllfarbe Symbol und wählen Sie hellgrün.

Schritt #5: Fügen Sie das Datenlabel zum Diagramm hinzu.

Jetzt ist es an der Zeit, das schöne Datenetikett (E6), die uns so viel Blut, Schweiß und Tränen gekostet haben.

Klicken Sie mit der rechten Maustaste auf Series 2 „Total Revenue Formula“ (der mit roter Füllung) und wähle "Datenlabels hinzufügen.”

Schritt #6: Ändern Sie den Wert des Datenlabels.

Verknüpfen Sie das Label mit dem Wert in der zweiten Tabelle.

1. Doppelklicken Sie auf das Etikett.
2. Typ “=” in die Formelleiste.
3. Wählen Sie den Labelwert (E6).

Jetzt müssen wir es ein wenig aufpeppen. Machen Sie das Etikett fett, färben Sie es weiß und ändern Sie die Schriftgröße, um das Element ästhetisch zu verbessern (Registerkarte "Startseite" > Schriftgruppe).

Schritt #7: Positionieren Sie das Datenetikett neu.

Nachdem Sie das Etikett eingerichtet haben, schieben Sie es an das obere Ende des zugehörigen Datenmarkers. Klicken Sie mit der rechten Maustaste auf das Datenlabel und wählen Sie „Datenetiketten formatieren.”

Dann im Etikettenoptionen Registerkarte, unter Etikettenposition, wählen "Inneres Ende.”

Schritt 8: Verschieben Sie „Target Revenue“ der Serie 1 auf die sekundäre Achse.

Um das Diagramm in eine Thermometerform zu formen, müssen Sie Serie 1 „Zieleinnahmen“ (E2) in der richtigen Position.

1. Klicken Sie mit der rechten Maustaste auf Serie 1 „Zieleinnahmen“ (die transparente ganz unten).
2. Wählen "Datenreihen formatieren.”

Im angezeigten Aufgabenbereich unter Plotserie an, wählen "Sekundäre Achse.”

Schritt #9: Entfernen Sie die Sekundärachse.

Die Nebenachse hat ihren Job gemacht, wir brauchen sie also nicht mehr.

Klicken Sie mit der rechten Maustaste auf die Nebenachse (die Zahlenspalte rechts) und wählen Sie „Löschen.”

Schritt #10: Ändern Sie die Skalenbereiche der Primärachse.

Wie Sie vielleicht bemerkt haben, sieht die Skala der primären Achse etwas aus. Lass uns das ändern.

Klicken Sie mit der rechten Maustaste auf den Maßstab der Primärachse und wählen Sie „Achse formatieren.”

Stellen Sie die Mindestgrenzen Wert zu -10000. Wieso den? Dadurch wird unten etwas Platz für die Thermometerbirne frei.

Dieser Wert hängt von Ihren Diagrammdaten ab. Eine gute Faustregel lautet: Verwenden Sie Ihre Zielzahl und teilen Sie sie durch zwei.

Angenommen, Ihr Umsatzziel beträgt 100.000 US-Dollar. Dann bist du Mindestgrenzen Wert wäre -50000.

Schritt #11: Ändern Sie das Zahlenformat der Primärachsenskala und fügen Sie Teilstriche hinzu.

Diese negativen Zahlen helfen uns hier nicht weiter. Glücklicherweise können wir sie mit benutzerdefinierten Zahlenformaten leicht entfernen.

Mit dem Achse formatieren Aufgabenbereich noch geöffnet, unter Achsenoptionen, navigieren Sie zu Zahlen Abschnitt und gehen Sie wie folgt vor:

1. Zum "Kategorie," auswählen ""
2. Typ "$#,##0.00;;$0“ in die Formatcode Feld und klicken Sie Hinzufügen.

Auch unter Häkchen, wählen "Innerhalb" von dem "Haupttyp" Dropdown-Menü.

Wechseln Sie nun zum Füllen & Linie Tab. Ändern Sie die Farbe der Teilstriche in Schwarz und stellen Sie die Breite Wert zu 1,5 pt.

Schritt #12: Entfernen Sie den Diagrammtitel, die Gitternetzlinien und die horizontale Achse.

Entfernen Sie die Diagrammelemente, die keinen praktischen Wert haben: Diagrammtitel, Gitternetzlinien und horizontale Achse. Klicken Sie mit der rechten Maustaste auf jedes Element und wählen Sie „Löschen.”

Schritt #13: Ändern Sie die Lückenbreite der Datenreihe auf der Sekundärachse.

Wenn Sie Ihrem Diagramm den letzten Schliff geben, machen Sie das Thermometerrohr schlanker. Beginnen Sie mit der Serie, die auf der Nebenachse platziert ist (Serie 1 “Zielumsatz”).

Klicken Sie mit der rechten Maustaste auf Serie 1 „Zielumsatz“ (E2), öffne das Datenreihen formatieren Aufgabenbereich, und stellen Sie die Spaltbreite Wert zu 250%. Erhöhen Sie den Prozentsatz, wenn Sie ihn weiter abnehmen möchten.

Schritt #14: Ändern Sie die Lückenbreite der Datenreihe auf der Hauptachse.

Klicken Sie danach erneut auf den Rand des roten Röhrchens, um Serie 2 auszuwählen.Formel für den Gesamtumsatz” (E3), auf der Hauptachse platziert. Gleiches einstellen Spaltbreite Wert (250%).

Schritt #15: Fügen Sie die Thermometerbirne zum Diagramm hinzu.

Erstellen Sie als letzte Anpassung die Thermometerbirne und fügen Sie sie dem Diagramm hinzu.

1. Wechseln Sie zum Einfügung
2. Wählen Sie das "Formen" Taste.
3. Wählen "Oval.”

Runter halten Schicht beim Zeichnen, um einen perfekten Kreis zu erstellen. Klicken Sie dann mit der rechten Maustaste auf den Kreis und ändern Sie im angezeigten Menü die Formumriss und Formfüllung Farben zu rot.

Nur noch eine letzte Sache. Kopieren Sie den Kreis, indem Sie ihn auswählen und drücken Strg + C. Wählen Sie dann das Diagramm aus und drücken Sie Strg + V um den Kreis in das Thermometerdiagramm einzufügen. Ändern Sie schließlich die Größe und die Position der Glühbirne, damit sie in das Bild passt.

Da hast du es also. Sie sind jetzt bereit, atemberaubende Thermometerdiagramme in Excel zu erstellen!