So erstellen Sie eine Wärmekarte in Excel: statisch und dynamisch

Diese Schritt-für-Schritt-Anleitung führt Sie durch das Erstellen einer Wärmekarte in Excel mit praktischen Beispielen.

Microsoft Excel wurde entwickelt, um Daten in Tabellen zu präsentieren. In einigen Fällen sind Visuals jedoch viel einfacher zu verstehen und zu verdauen. Wie Sie wahrscheinlich wissen, hat Excel eine Reihe von eingebauten Funktionen zum Erstellen von Grafiken. Leider ist eine Wärmekarte nicht an Bord. Glücklicherweise gibt es eine schnelle und einfache Möglichkeit, eine Wärmekarte in Excel mit bedingter Formatierung zu erstellen.

• Was ist eine Wärmekarte?
• So erstellen Sie eine Wärmekarte in Excel
• Machen Sie eine Heatmap mit benutzerdefinierter Farbskala
• Excel -Wärmekarte ohne Zahlen
• Wärmekarte mit quadratischen Zellen
• Wie man eine Wärmekarte in Pivotsable erstellt
• Erstellen Sie eine dynamische Wärmekarte mit CheckBox
• Erstellen Sie eine dynamische Wärmekarte ohne Zahlen

Was ist Wärmekarte in Excel?

Eine Wärmekarte (auch bekannt als Heatmap ) ist eine visuelle Interpretation numerischer Daten, bei der verschiedene Werte durch verschiedene Farben dargestellt werden. In der Regel werden Warm-Cool-Farbschemata verwendet, sodass Daten in Form von heißen und kalten Flecken dargestellt werden.

Im Vergleich zu Standard -Analyseberichten erleichtern Wärmemaps es viel einfacher, komplexe Daten zu visualisieren und zu analysieren. Sie werden von Wissenschaftlern, Analysten und Vermarktern ausgiebig zur vorläufigen Analyse von Daten und zur Entdeckung generischer Muster verwendet.

Hier sind einige typische Beispiele:

• Lufttemperaturwärmekarte - wird verwendet, um Lufttemperaturdaten in einem bestimmten Bereich zu visualisieren.
• Geografische Wärmekarte - Zeigt einige numerische Daten über einen geografischen Bereich mit verschiedenen Farbtönen an.
• Risikomanagement -Wärmekarte - zeigt unterschiedliche Risiken und ihre Auswirkungen auf visuelle und präzise Weise.

In Excel wird eine Wärmekarte verwendet, um einzelne Zellen in verschiedenen Farbcodes basierend auf ihren Werten darzustellen.

Zum Beispiel können Sie aus der folgenden Heatmap die feuchtesten (in Grün hervorgehoben) und die trockensten (in rotem) Regionen und Jahrzehnte auf einen Blick erkennen:

So erstellen Sie eine Wärmekarte in Excel

Wenn Sie über manuell über den Wert von jeder Zelle nachdenken würden, geben Sie diese Idee auf, da dies eine unnötige Zeitverschwendung wäre. Zunächst würde es große Anstrengungen erfordern, um einen geeigneten Farbschatten entsprechend dem Rang des Werts anzuwenden. Und zweitens müssten Sie jedes Mal, wenn sich die Werte ändern, die Farbcodierung wiederholen. Excel bedingte Formatierung überwindet beide Hürden effektiv.

Um eine Wärmekarte in Excel zu erstellen, verwenden wir die bedingte Formatierungsfarbskala. Hier sind die Schritte zur Ausführung:

1. Wählen Sie Ihren Datensatz aus. In unserem Fall ist es B3: M5.

   

2. Klicken Sie auf der Registerkarte " Start" in der Styles -Gruppe auf bedingte Formatierung > Farbskalen und dann auf die gewünschte Farbskala. Wenn Sie die Maus über eine bestimmte Farbskala bewegen, zeigt Excel Ihnen die Live -Vorschau direkt in Ihrem Datensatz.

   In diesem Beispiel haben wir rot - gelb - grüne Farbskala ausgewählt:

   

   Im Ergebnis haben Sie die hohen Werte in rot, in gelbem und grünem Middle Middle und Green hervorgehoben. Die Farben werden automatisch angepasst, wenn sich die Zellwerte ändern.

   

Tipp. Damit die bedingte Formatierungsregel automatisch für neue Daten angewendet werden kann, können Sie Ihren Datenbereich in eine voll funktionsfähige Excel-Tabelle umwandeln.

Machen Sie eine Wärmemap mit einer benutzerdefinierten Farbskala

Bei der Anwendung einer voreingestellten Farbskala zeigt sie die niedrigsten, mittleren und höchsten Werte in den vordefinierten Farben (grün, gelb und rot). Alle verbleibenden Werte erhalten unterschiedliche Farben der drei Hauptfarben.

Wenn Sie alle Zellen, die in einer bestimmten Farbe niedriger/höher als eine gegebene Zahl unabhängig von ihren Werten sind, hervorheben möchten, konstruieren Sie dann eine eigene Farbskala, die Ihre eigene erstellen, anstatt Ihre eigene zu verwenden. Hier erfahren Sie, wie das geht:

1. Klicken Sie auf der Registerkarte " Start" in der Styles -Gruppe auf Bedingte Formatierung > Farbskalen> Weitere Regeln.

   

2. Führen Sie im Dialogfeld "Neue Formatierungsregel" Folgendes aus:
   • Wählen Sie eine 3-Farben-Skala aus der Dropdown-Liste der Formatstil .
   • Wählen Sie für den minimalen und/oder maximalen Wert die Zahl im Drop -Down -Typ -Typ und geben Sie die gewünschten Werte in die entsprechenden Kästchen ein.
   • Für Mittelpunkt können Sie entweder die Anzahl oder das Perzentil festlegen (normalerweise 50%).
   • Weisen Sie jedem der drei Werte eine Farbe zu.

   In diesem Beispiel haben wir die folgenden Einstellungen konfiguriert:

   

   In dieser maßgefertigten Hitzemap werden alle Temperaturen unter 45 ° F im gleichen Grünton und in allen Temperaturen über 70 ° F im gleichen Rotschatten hervorgehoben:

   

Erstellen Sie eine Wärmekarte in Excel ohne Zahlen

Die Wärmekarte, die Sie in Excel erstellen, basiert auf den tatsächlichen Zellwerten, und das Löschen der Wärmekarte würde die Wärmekarte zerstören. Verwenden Sie, um die Zellwerte zu verbergen, ohne sie aus dem Blatt zu entfernen, die benutzerdefinierte Zahlenformatierung. Hier sind die detaillierten Schritte:

1. Wählen Sie die Wärmekarte.
2. Drücken Sie Strg 1, um den Dialogfeld "Formatzellen" zu öffnen.
3. Wählen Sie auf der Registerkarte Nummer unter Kategorie benutzerdefinierte .
4. In der Type Typ 3 Semikolons (;;;;).
5. Klicken Sie auf OK, um das benutzerdefinierte Zahlenformat anzuwenden.

   

Das war's! Jetzt zeigt Ihre Excel-Wärme-Karte nur die Farbcodes ohne Zahlen an:

Excel -Wärmekarte mit quadratischen Zellen

Eine weitere Verbesserung, die Sie an Ihrer Heatmap vornehmen können, sind perfekt quadratische Zellen. Nachfolgend finden Sie den schnellsten Weg, dies ohne Skripte oder VBA -Codes zu tun:

1. Säulenhochgeber vertikal ausrichten . Um zu verhindern, dass Säulenkopfzeile abgeschnitten werden, ändern Sie ihre Ausrichtung auf vertikal. Dies kann mit Hilfe der Orientierungstaste auf der Registerkarte Start in der Ausrichtungsgruppe erfolgen:

   

   Weitere Informationen finden Sie unter dem Ausrichten von Text in Excel.

2. Setzen Sie die Spaltenbreite . Wählen Sie alle Spalten aus und ziehen Sie die Kante eines Spaltenkopfs, um sie breiter oder schmaler zu machen. In diesem Fall wird ein Tooltip angezeigt, der eine genaue Pixelzahl zeigt - denken Sie an diese Nummer.

   

3. Zeilenhöhe einstellen . Wählen Sie alle Zeilen aus und ziehen Sie die Kante eines Zeilenkopfes an denselben Pixelwert wie Spalten (in unserem Fall 26 Pixel).

   

   Erledigt! Alle Zellen Ihrer Hutkarte sind jetzt quadratisch geformt:

   

Wie man eine Wärmekarte in Excel PivotTable erstellt

Im Wesentlichen ist das Erstellen einer Heatmap in einer Pivot -Tabelle dieselbe wie in einem normalen Datenbereich - unter Verwendung einer bedingten Formatierungs -Farbskala. Es gibt jedoch eine Einschränkung: Wenn der Quellentabelle neue Daten hinzugefügt werden, gilt die bedingte Formatierung nicht automatisch für diese Daten.

Zum Beispiel haben wir Luis Umsatz an die Quelltabelle hinzugefügt, den Pivottable aktualisiert und sehen, dass Luis Zahlen noch außerhalb der Wärmekarte liegen:

Wie man eine Pivotsable Wärmekarte dynamisch macht

Um eine Excel Pivot -Tabellen -Wärmekarte zu erzwingen, um automatisch neue Einträge einzuschließen, sind hier die Schritte zur Ausführung:

1. Wählen Sie eine beliebige Zelle in Ihrer aktuellen Wärmekarte.
2. Klicken Sie auf der Registerkarte Startseite in der Styles -Gruppe auf bedingte Formatierung > Regeln verwalten…
3. Wählen Sie im Regeln Manager für bedingte Formatierung die Regel aus und klicken Sie auf die Schaltfläche "Regel" bearbeiten .
4. Wählen Sie im Dialogfeld "Formatierungsregel bearbeiten" unter Anwenden der Regel die dritte Option. In unserem Fall lautet es: Alle Zellen, die "Summe der Verkaufszahlen" für "Reseller" und "Produkt" zeigen .
5. Klicken Sie zweimal auf OK, um beide Dialogfenster zu schließen.

   

Jetzt ist Ihre Wärmekarte dynamisch und aktualisiert automatisch, wenn Sie neue Informationen im Back -End hinzufügen. Denken Sie daran, Ihren Pivotsable zu aktualisieren :)

So erstellen Sie eine dynamische Wärmekarte in Excel mit CheckBox

Wenn Sie nicht möchten, dass eine Wärmekarte ständig da ist, können Sie sie nach Ihren Anforderungen verstecken und zeigen. Um eine dynamische Wärmekarte mit einem Kontrollkästchen zu erstellen, sind dies die folgenden Schritte:

1. Fügen Sie ein Kontrollkästchen ein . Fügen Sie neben Ihrem Datensatz ein Kontrollkästchen (Formularsteuerung) ein. Klicken Sie hierfür auf die Registerkarte Entwickler > Einfügen > Formularsteuerungen > Kontrollkästchen . Hier sind die detaillierten Schritte, um ein Kontrollkästchen in Excel hinzuzufügen.
2. Verknüpfen Sie das Kontrollkästchen mit einer Zelle . Um ein Kontrollkästchen mit einer bestimmten Zelle zu verknüpfen, klicken Sie mit der rechten Maustaste auf das Kontrollkästchen, klicken Sie auf Formatsteuerung , wechseln Sie zur Registerkarte Steuerung , geben Sie eine Zelladresse in das Feld "Zellenlink" ein und klicken Sie auf OK.

   In unserem Fall ist das Kontrollkästchen mit der Zelle O2 verknüpft. Wenn das Kontrollkästchen ausgewählt ist, zeigt die verknüpfte Zelle true an, ansonsten - false.

   

3. Einrichten der bedingten Formatierung . Wählen Sie den Datensatz aus, klicken Sie auf bedingte Formatierung > Farbskalen> Weitere Regeln und konfigurieren Sie eine benutzerdefinierte Farbskala auf diese Weise:
   • Wählen Sie in der Dropdown-Liste des Formatstils die 3-Farben-Skala aus.
   • Wählen Sie unter minimalem Mittelpunkt und Maximum die Formel aus der Dropdown-Liste Typ .
   • Geben Sie in den Wertschachteln die folgenden Formeln ein:

     Für Minimum:

     =IF($O$2=TRUE, MIN($B$3:$M$5), FALSE)

     Für Mittelpunkt:

     =IF($O$2=TRUE, AVERAGE($B$3:$M$5), FALSE)

     Für maximal:

     =IF($O$2=TRUE, MAX($B$3:$M$5), FALSE)

     Diese Formeln verwenden die min-, durchschnittlichen und maximalen Funktionen, um die niedrigsten, mittleren und höchsten Werte im Datensatz (B3: M5) zu erarbeiten, wenn die verknüpfte Zelle (O2) wahr ist, dh wenn das Kontrollkästchen ausgewählt ist.

   • Wählen Sie in den Dropdown-Boxen der Farbe die gewünschten Farben aus.
   • Klicken Sie auf die Schaltfläche OK.

     

   Jetzt wird die Wärmekarte nur angezeigt, wenn das Kontrollkästchen ausgewählt ist und den Rest der Zeit verborgen ist.

   

Tipp. Um den wahren / falschen Wert aus der Ansicht zu entfernen, können Sie das Kontrollkästchen mit einer Zelle in einer leeren Spalte verknüpfen und diese Spalte ausblenden.

Wie man eine dynamische Wärmekarte in Excel ohne Zahlen erstellt

Um Zahlen in einer dynamischen Wärmekarte auszublenden, müssen Sie eine weitere bedingte Formatierungsregel erstellen, die ein benutzerdefiniertes Zahlenformat anwendet. So wie: wie:

1. Erstellen Sie eine dynamische Wärmekarte, wie im obigen Beispiel erläutert.
2. Wählen Sie Ihren Datensatz aus.
3. Klicken Sie auf der Registerkarte Startseite in der Styles -Gruppe auf neue Regel > eine Formel, um zu bestimmen, welche Zellen zu formatieren sind .
4. Geben Sie in den Formatwerten, bei denen diese Formel ein wahres Feld ist , diese Formel ein:

   =IF($O$2=TRUE, TRUE, FALSE)

   Wo O2 Ihre verknüpfte Zelle ist. In der Formel soll die Regel nur dann angewendet werden, wenn das Kontrollkästchen überprüft wird (O2 ist wahr).

   

5. Klicken Sie auf das Format… Schaltfläche.
6. Wechseln Sie im Dialogfeld "Formatzellen" zur Registerkarte Nummer , wählen Sie in der Kategorieliste benutzerdefiniert , geben Sie 3 Semikolons (;;;;) im Feld Typ ein und klicken Sie auf OK.

   

7. Klicken Sie auf OK, um das Dialogfeld "Neue Formatierungsregel" zu schließen.

Durch die Auswahl des Kontrollkästchens werden die Wärmekarte angezeigt und Zahlen ausblenden:

Um zwischen zwei verschiedenen Heatmap -Typen (mit und ohne Zahlen) zu wechseln, können Sie drei Optionsfelder einfügen. Und dann konfigurieren Sie 3 separate bedingte Formatierungsregeln: 1 Regel für die Wärmekarte mit Zahlen und 2 Regeln für die Wärmekarte ohne Zahlen. Oder Sie können eine gemeinsame Farbskala -Regel für beide Typen erstellen, indem Sie die oder Funktion verwenden (wie in unserem Beispiel -Arbeitsblatt unten).

Im Ergebnis erhalten Sie diese schöne dynamische Wärmekarte:

Um besser zu verstehen, wie dies funktioniert, können Sie unser Beispielblatt herunterladen. Hoffentlich hilft Ihnen dies dabei, Ihre eigene erstaunliche Excel -Heat -Kartenvorlage zu erstellen.

Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Üben Sie die Arbeitsmappe zum Download

Wärmekarte in Excel - Beispiele (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonSo erstellen Sie eine Wärmekarte in Excel: statisch und dynamisch. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!