Wärmebild-Diagramm in Excel erstellen

In Excel sieht ein Wärmebild-Diagramm wie eine Tabelle aus, die eine visuelle Darstellung bietet und einen vergleichenden Überblick über einen Datensatz zeigt. Wenn Sie einen großen Datensatz in Ihrem Arbeitsblatt haben, ist es wirklich schwierig, auf einen Blick die niedrigeren oder höheren Werte zu identifizieren. Im Wärmebild wird der Zellenwert jedoch in einem unterschiedlichen Farbmuster dargestellt, sodass wir größere oder kleinere Daten schnell und einfach erkennen können, wie im folgenden Screenshot gezeigt.

• Erstellen eines einfachen Wärmebild-Diagramms mit bedingter Formatierung
• Dynamisches Wärmebild-Diagramm in Excel erstellen
• Beispiel 1: Dynamisches Wärmebild mit Scrollleiste erstellen
• Beispiel 2: Dynamisches Wärmebild mit Optionsfeldern erstellen
• Beispiel 3: Dynamisches Wärmebild mit Kontrollkästchen erstellen
• Beispieldatei für Wärmebild-Diagramm herunterladen
• Video: Wärmebild-Diagramm in Excel erstellen

Erstellen eines einfachen Wärmebild-Diagramms mit bedingter Formatierung

Es gibt kein direktes Hilfsprogramm für Wärmebild-Diagramme in Excel, aber mit der leistungsstarken Funktion der bedingten Formatierung können Sie schnell ein Wärmebild erstellen. Gehen Sie dazu wie folgt vor:

1. Wählen Sie den Datenbereich aus, auf den Sie die bedingte Formatierung anwenden möchten.

2. Klicken Sie dann auf Start > Bedingte Formatierung > Farbskalen und wählen Sie dann einen Stil aus dem erweiterten Dropdown-Menü rechts aus (in diesem Fall wähle ich Grün – Gelb – Rot Farbskala), siehe Screenshot:

3. Nun ist das Wärmebild erstellt, das die Zellen basierend auf ihren Werten hervorhebt. Grün steht für die höchsten Werte und Rot für die niedrigsten Werte, während die restlichen Werte eine Farbverlauf zwischen Grün und Rot zeigen. Siehe Screenshot:

4. Wenn Sie die Zahlen ausblenden und nur die Farben anzeigen möchten, wählen Sie den Datenbereich aus und drücken Sie Strg + 1, um das Dialogfeld Zellen formatieren zu öffnen.

5. Im Dialogfeld Zellen formatieren klicken Sie unter dem Register Zahl auf die Option Benutzerdefiniert in der linken Kategorieliste und geben dann ;;; in das Textfeld Typ ein, siehe Screenshot:

6. Klicken Sie dann auf OK, und alle Zahlen sind wie im folgenden Screenshot ausgeblendet:

Hinweis: Um die Zellen mit anderen Farben Ihrer Wahl hervorzuheben, wählen Sie den Datenbereich aus und klicken Sie dann auf Start > Bedingte Formatierung > Regeln verwalten, um zum Dialogfeld Regelmanager für bedingte Formatierung zu gelangen.

Doppelklicken Sie dann auf die vorhandene Regel, um das Dialogfeld Formatierungsregel bearbeiten zu öffnen, und setzen Sie die Regel nach Bedarf zurück, siehe Screenshot:

Dynamisches Wärmebild-Diagramm in Excel erstellen

Beispiel 1: Dynamisches Wärmebild mit Scrollleiste erstellen

Wenn Sie mehrere Spaltendaten in Ihrem Arbeitsblatt haben, diese aber in einem begrenzten Raum anzeigen möchten, können Sie in diesem Fall eine Bildlaufleiste in das Arbeitsblatt einfügen, um das Wärmebild dynamisch zu gestalten, wie in der folgenden Demo gezeigt.

Um diesen Typ eines dynamischen Wärmebild-Diagramms zu erstellen, gehen Sie wie folgt vor:

1. Fügen Sie ein neues Arbeitsblatt ein und kopieren Sie dann die erste Spalte Monate vom Originalblatt in dieses neue Blatt.

2. Klicken Sie dann auf Entwickler > Einfügen > Bildlaufleiste, siehe Screenshot:

3. Ziehen Sie dann die Maus, um eine Bildlaufleiste unter den kopierten Daten zu zeichnen, und klicken Sie mit der rechten Maustaste auf die Bildlaufleiste und wählen Sie Objekt formatieren, siehe Screenshot:

4. Im Dialogfeld Objekt formatieren, unter dem Register Steuerelement, legen Sie den Minimalwert, Maximalwert, Inkrement, Seitenvorschub und eine verknüpfte Zelle basierend auf Ihrem Datenbereich fest, wie im folgenden Screenshot gezeigt:

5. Klicken Sie dann auf OK, um dieses Dialogfeld zu schließen.

6. Geben Sie nun in Zelle B1 dieses neuen Blatts die folgende Formel ein und drücken Sie die Eingabetaste, um das erste Ergebnis zu erhalten:

Hinweis: In der obigen Formel ist data1!$B$1:$I$13 das Originalblatt mit dem Datenbereich ohne die Zeilenüberschrift (Monate), $I$1 ist die Zelle, mit der die Bildlaufleiste verknüpft ist, $B$1:B1 ist die Zelle, in der Sie die Formel ausgeben.

7. Ziehen Sie dann diese Formelzelle in die restlichen Zellen. Wenn Sie nur 3 Jahre im Arbeitsblatt anzeigen möchten, ziehen Sie die Formel von B1 bis D13, siehe Screenshot:

8. Wenden Sie dann die Farbskala der Funktion bedingte Formatierung auf den neuen Datenbereich an, um das Wärmebild zu erstellen. Wenn Sie nun die Bildlaufleiste ziehen, bewegt sich das Wärmebild dynamisch, siehe Screenshot:

Beispiel 2: Dynamisches Wärmebild mit Optionsfeldern erstellen

Sie können auch ein dynamisches Wärmebild mit Optionsfeldern erstellen. Die Auswahl eines Optionsfeldes hebt die größten n Werte hervor, und die Auswahl eines anderen Optionsfeldes hebt die kleinsten n Werte hervor, wie in der folgenden Demo gezeigt:

Um dieses dynamische Wärmebild zu erstellen, gehen Sie wie folgt vor:

1. Klicken Sie auf Entwickler > Einfügen > Optionsfeld (Formularsteuerelement), ziehen Sie dann die Maus, um zwei Optionsfelder zu zeichnen, und bearbeiten Sie den Text nach Bedarf, siehe Screenshot:

2. Nachdem Sie die Optionsfelder eingefügt haben, klicken Sie mit der rechten Maustaste auf das erste und wählen Sie Objekt formatieren. Im Dialogfeld Objekt formatieren, unter dem Register Steuerelement, wählen Sie eine Zelle aus, die mit dem Optionsfeld verknüpft ist, siehe Screenshot:

3. Klicken Sie auf OK, um das Dialogfeld zu schließen, und wiederholen Sie dann den obigen Schritt (Schritt 2), um das zweite Optionsfeld ebenfalls mit derselben Zelle (Zelle M1) zu verknüpfen.

4. Wenden Sie dann die bedingte Formatierung auf den Datenbereich an. Wählen Sie den Datenbereich aus und klicken Sie auf Start > Bedingte Formatierung > Neue Regel, siehe Screenshot:

5. Wählen Sie im Dialogfeld Neue Formatierungsregel die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen, aus der Liste Regeltyp auswählen, und geben Sie dann diese Formel ein: =WENN($M$1=1;WENN(B2>=GROßE($B$2:$I$13;15);WAHR;FALSCH)) in das Textfeld Formatieren, wenn diese Formel wahr ist, und klicken Sie dann auf Formatieren, um eine Farbe auszuwählen. Siehe Screenshot:

6. Klicken Sie auf OK, dies hebt die größten 15 Werte rot hervor, wenn Sie das erste Optionsfeld auswählen.

7. Um die kleinsten 15 Werte hervorzuheben, behalten Sie die Daten ausgewählt und gehen Sie in das Dialogfeld Neue Formatierungsregel, und geben Sie dann diese Formel ein: =WENN($M$1=2;WENN(B2<=KLEINSTE($B$2:$I$13;15);WAHR;FALSCH)) in das Textfeld Formatieren, wenn diese Formel wahr ist, und klicken Sie auf Formatieren, um eine andere Farbe auszuwählen. Siehe Screenshot:

Hinweis: In den obigen Formeln ist $M$1 die Zelle, die mit den Optionsfeldern verknüpft ist, $B$2:$I$13 ist der Datenbereich, auf den Sie die bedingte Formatierung anwenden möchten, B2 ist die erste Zelle des Datenbereichs, die Zahl 15 ist die spezifische Anzahl, die Sie hervorheben möchten.

8. Klicken Sie auf OK, um das Dialogfeld zu schließen. Nun werden beim Auswählen des ersten Optionsfeldes die größten 15 Werte hervorgehoben, und beim Auswählen des zweiten Optionsfeldes werden die kleinsten 15 Werte hervorgehoben, wie in der folgenden Demo gezeigt:

Beispiel 3: Dynamisches Wärmebild mit Kontrollkästchen erstellen

In diesem Abschnitt stelle ich ein dynamisches Wärmebild-Diagramm mit einem Kontrollkästchen vor, das Ihnen helfen kann, das Wärmebild je nach Bedarf anzuzeigen oder auszublenden. Wenn Sie das Kontrollkästchen aktivieren, wird das Wärmebild angezeigt, wenn Sie es deaktivieren, wird es sofort ausgeblendet, siehe die folgende Demo:

1. Zuerst sollten Sie Ihren Datenbereich in ein Tabellenformat konvertieren, das Ihnen hilft, die bedingte Formatierung automatisch anzuwenden, wenn Sie eine neue Datenzeile einfügen. Wählen Sie den Datenbereich aus und drücken Sie dann gleichzeitig Strg + T, um das Dialogfeld Tabelle erstellen zu öffnen, siehe Screenshot:

2. Klicken Sie auf OK, um das Dialogfeld zu schließen, und klicken Sie dann auf Entwickler > Einfügen > Kontrollkästchen (Formularsteuerelement), ziehen Sie dann die Maus, um ein Kontrollkästchen zu zeichnen und bearbeiten Sie den Text nach Bedarf, wie in den folgenden Screenshots gezeigt:

3. Klicken Sie dann mit der rechten Maustaste auf das Kontrollkästchen und wählen Sie Objekt formatieren. Im Dialogfeld Objekt formatieren, unter dem Register Steuerelement, wählen Sie eine Zelle aus, die mit dem Kontrollkästchen verknüpft ist, siehe Screenshot:

4. Klicken Sie auf OK, um das Dialogfeld zu schließen, wählen Sie dann den Datenbereich aus, für den Sie das Wärmebild erstellen möchten, und klicken Sie auf Start > Bedingte Formatierung > Neue Regel, um zum Dialogfeld Neue Formatierungsregel zu gelangen.

5. Führen Sie im Dialogfeld Neue Formatierungsregel die folgenden Aktionen aus:

• Wählen Sie die Option Alle Zellen basierend auf ihren Werten formatieren aus der Liste Regeltyp auswählen;
• Wählen Sie 3-Farben-Skala aus der Dropdown-Liste Formatstil aus;
• Wählen Sie Formel in den Typ-Feldern unter den Dropdown-Listen Minimum, Mittelpunkt und Maximum separat aus;
• Geben Sie dann die folgenden Formeln in die drei Wert-Textfelder ein:
• Minimum: =WENN($M$1=WAHR;MIN($B$2:$I$13);FALSCH)
• Mittelpunkt: =WENN($M$1=WAHR;MITTELWERT($B$2:$I$13);FALSCH)
• Maximum: =WENN($M$1=WAHR;MAX($B$2:$I$13);FALSCH)
• Legen Sie dann die Hervorhebungsfarben im Farbabschnitt nach Bedarf fest.

Hinweis: In den obigen Formeln ist $M$1 die Zelle, die mit dem Kontrollkästchen verknüpft ist, $B$2:$I$13 ist der Datenbereich, auf den Sie die bedingte Formatierung anwenden möchten.

6. Klicken Sie nach dem Abschluss der Einstellungen auf OK, um das Dialogfeld zu schließen. Nun wird das Wärmebild angezeigt, wenn Sie das Kontrollkästchen aktivieren, andernfalls wird es ausgeblendet. Siehe folgende Demo:

Beispieldatei für Wärmebild-Diagramm herunterladen

Video: Wärmebild-Diagramm in Excel erstellen