Eine Heatmap in Excel ist eine visuelle Darstellung, die Ihnen schnell eine vergleichende Ansicht eines Datensatzes zeigt.
Im folgenden Datensatz kann ich beispielsweise leicht erkennen, in welchen Monaten die Verkäufe im Vergleich zu anderen Monaten niedrig waren (rot hervorgehoben).
Im obigen Datensatz werden die Farben basierend auf dem Wert in der Zelle zugewiesen. Die Farbskala reicht von Grün über Gelb bis Rot, wobei hohe Werte die grüne Farbe und niedrige Werte die rote Farbe erhalten.
Erstellen einer Heatmap in Excel
Sie können in Excel eine Heatmap erstellen, indem Sie die Zellen manuell farblich codieren. Sie müssen es jedoch wiederholen, wenn sich die Werte ändern.
Anstelle der manuellen Arbeit können Sie die bedingte Formatierung verwenden, um Zellen basierend auf dem Wert hervorzuheben. Wenn Sie die Werte in den Zellen ändern, aktualisiert die Farbe/das Format der Zelle die Heatmap automatisch basierend auf den vordefinierten Regeln in der bedingten Formatierung.
In diesem Tutorial erfahren Sie, wie Sie:
- Erstellen Sie schnell eine Heatmap in Excel mit bedingter Formatierung.
- Erstellen Sie eine dynamische Heatmap in Excel.
- Erstellen Sie eine Heatmap in Excel-Pivot-Tabellen.
Lass uns anfangen!
Erstellen einer Heatmap in Excel mit bedingter Formatierung
Wenn Sie einen Datensatz in Excel haben, können Sie Datenpunkte manuell hervorheben und eine Heatmap erstellen.
Dies wäre jedoch eine statische Heatmap, da sich die Farbe nicht ändert, wenn Sie den Wert in einer Zelle ändern.
Daher ist die bedingte Formatierung der richtige Weg, da sich die Farbe in einer Zelle ändert, wenn Sie den Wert darin ändern.
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Hier sind die Schritte zum Erstellen einer Heatmap mit diesen Daten:
- Wählen Sie den Datensatz aus. In diesem Beispiel wäre es B2:D13.
- Gehen Sie zu Home -> Bedingte Formatierung -> Farbskalen. Es zeigt verschiedene Farbkombinationen, mit denen die Daten hervorgehoben werden können. Die gebräuchlichste Farbskala ist die erste, bei der Zellen mit hohen Werten grün und niedrigen Werten rot hervorgehoben werden. Beachten Sie, dass Sie die Live-Vorschau im Datensatz sehen können, wenn Sie mit der Maus über diese Farbskalen fahren.
Dadurch erhalten Sie eine Heatmap wie unten gezeigt:
Standardmäßig weist Excel dem niedrigsten Wert die rote Farbe und dem höchsten Wert die grüne Farbe zu, und alle verbleibenden Werte erhalten eine Farbe basierend auf dem Wert. Es gibt also einen Farbverlauf mit unterschiedlichen Schattierungen der drei Farben basierend auf dem Wert.
Was ist, wenn Sie keinen Farbverlauf wünschen und nur Rot, Gelb und Grün anzeigen möchten. Sie möchten beispielsweise alle Werte kleiner als 700 rot hervorheben, unabhängig vom Wert. 500 und 650 erhalten beide dieselbe rote Farbe, da sie weniger als 700 beträgt.
Um dies zu tun:
- Gehen Sie zu Home -> Bedingte Formatierung -> Farbskalen -> Weitere Optionen.
- Wählen Sie im Dialogfeld „Neue Formatierungsregel“ im Dropdown-Menü „Formatstil“ die Option „3-Farbskala“.
- Jetzt können Sie den Minimal-, Mittelpunkt- und Maximalwert angeben und ihm die Farbe zuweisen. Da wir alle Zellen mit einem Wert unter 700 rot hervorheben möchten, ändern Sie den Typ in Zahl und den Wert in 700.
- OK klicken.
Jetzt erhalten Sie das Ergebnis wie unten gezeigt. Beachten Sie, dass alle Werte unter 700 den gleichen Rotton erhalten.
Ein Wort der Warnung: Während die bedingte Formatierung ein wunderbares Werkzeug ist, ist sie leider flüchtig. Dies bedeutet, dass bei jeder Änderung im Arbeitsblatt die bedingte Formatierung neu berechnet wird. Während die Auswirkungen bei kleinen Datensätzen vernachlässigbar sein können, kann dies bei der Arbeit mit großen Datensätzen zu einer langsamen Excel-Arbeitsmappe führen.
Erstellen einer dynamischen Heatmap in Excel
Da die bedingte Formatierung vom Wert in einer Zelle abhängt, wird die bedingte Formatierung neu berechnet und geändert, sobald Sie den Wert ändern.
Dadurch ist es möglich, eine dynamische Heatmap zu erstellen.
Sehen wir uns zwei Beispiele für das Erstellen von Heatmaps mit interaktiven Steuerelementen in Excel an.
Beispiel 1: Heatmap mit Bildlaufleiste
Hier ist ein Beispiel, bei dem sich die Heatmap ändert, sobald Sie die Bildlaufleiste verwenden, um das Jahr zu ändern.
Diese Art von dynamischen Heatmaps können in Dashboards verwendet werden, in denen Sie Platzbeschränkungen haben, aber dennoch möchten, dass der Benutzer auf den gesamten Datensatz zugreifen kann.
Klicken Sie hier, um die Heatmap-Vorlage herunterzuladen
Wie erstelle ich diese dynamische Heatmap?
Hier ist der vollständige Datensatz, der verwendet wird, um diese dynamische Heatmap zu erstellen.
Hier sind die Schritte:
- Geben Sie in einem neuen Blatt (oder im selben Blatt) die Monatsnamen ein (einfach kopieren und aus den Originaldaten einfügen).
- Gehen Sie zu Entwickler -> Steuerelemente -> Einfügen -> Bildlaufleiste. Klicken Sie nun auf eine beliebige Stelle im Arbeitsblatt, und es wird eine Bildlaufleiste eingefügt. (Klicken Sie hier, wenn Sie den Entwickler-Tab nicht finden können).
- Klicken Sie mit der rechten Maustaste auf die Bildlaufleiste und klicken Sie auf Format Control.
- Nehmen Sie im Dialogfeld Formatsteuerung die folgenden Änderungen vor:
- Mindestwert: 1
- Maximalwert 5
- Zellenverknüpfung: Blatt1!$J$1 (Sie können auf das Symbol rechts klicken und dann manuell die Zelle auswählen, die Sie mit der Bildlaufleiste verknüpfen möchten).
- OK klicken.
- Geben Sie in Zelle B1 die Formel ein: =INDEX(Sheet1!$B$1:$H$13,ROW(),Sheet1!$J$1+COLUMNS(Sheet2!$B$1:B1)-1)
- Ändern Sie die Größe und platzieren Sie die Bildlaufleiste am unteren Rand des Datensatzes.
Wenn Sie nun die Bildlaufleiste ändern, ändert sich der Wert in Sheet1!$J$1, und da die Formeln mit dieser Zelle verknüpft sind, wird sie aktualisiert, um die richtigen Werte anzuzeigen.
Da die bedingte Formatierung flüchtig ist, wird sie auch aktualisiert, sobald sich der Wert ändert.
Video ansehen - Dynamische Wärmekarte in Excel
Beispiel 2: Erstellen einer dynamischen Heatmap in Excel mithilfe von Optionsfeldern
Hier ist ein weiteres Beispiel, bei dem Sie die Heatmap ändern können, indem Sie eine Optionsschaltfläche auswählen:
In diesem Beispiel können Sie die oberen/untersten 10 Werte basierend auf der Auswahl des Optionsfelds/der Optionsschaltfläche hervorheben.
Klicken Sie hier, um die Heatmap-Vorlage herunterzuladen
Erstellen einer Heatmap in einer Excel-Pivot-Tabelle
Die bedingte Formatierung in Pivot-Tabellen funktioniert genauso wie bei allen normalen Daten.
Aber es gibt etwas Wichtiges, das Sie wissen müssen.
Lassen Sie mich ein Beispiel nehmen und es Ihnen zeigen.
Angenommen, Sie haben eine Pivot-Tabelle wie unten gezeigt:
So erstellen Sie eine Heatmap in dieser Excel-Pivot-Tabelle:
- Wähle die Zellen aus (B5:D14).
- Gehen Sie zu Home -> Bedingte Formatierung -> Farbskalen und wählen Sie die Farbskala aus, die Sie anwenden möchten.
Dadurch würde sofort die Heatmap in der Pivot-Tabelle erstellt.
Das Problem bei dieser Methode besteht darin, dass die bedingte Formatierung nicht auf die neuen Daten angewendet wird, wenn Sie im Backend neue Daten hinzufügen und diese Pivot-Tabelle aktualisieren.
Wenn ich beispielsweise im Backend neue Daten hinzugefügt, die Quelldaten angepasst und die Pivot-Tabelle aktualisiert habe, sehen Sie, dass keine bedingte Formatierung darauf angewendet wird.
Dies geschieht, da wir die bedingte Formatierung nur auf die Zellen B5:D14 angewendet haben.
Wenn Sie möchten, dass diese Heatmap dynamisch ist, sodass sie aktualisiert wird, wenn neue Daten hinzugefügt werden, gehen Sie wie folgt vor:
- Wähle die Zellen aus (B5:D14).
- Gehen Sie zu Home -> Bedingte Formatierung -> Farbskalen und wählen Sie die Farbskala aus, die Sie anwenden möchten.
- Gehen Sie erneut zu Home -> Bedingte Formatierung -> Regeln verwalten.
- Klicken Sie im Manager für bedingte Formatierungsregeln auf die Schaltfläche Bearbeiten.
- Wählen Sie im Dialogfeld Formatierungsregel bearbeiten die dritte Option: Alle Zellen mit den Werten „Umsatz“ für „Datum“ und „Kunde“.
Jetzt würde die bedingte Formatierung aktualisiert, wenn Sie die Backend-Daten ändern.
Notiz: Die bedingte Formatierung verschwindet, wenn Sie die Zeilen-/Spaltenfelder ändern. Wenn Sie beispielsweise das Datumsfeld entfernen und erneut anwenden, geht die bedingte Formatierung verloren.
