Tutorial: Wie man in PowerPoint automatisch eine Heatmap erstellt

Einführung

Eine Datentabelle ist oft recht nüchtern, wenn sie nur schwarz auf weiß ist. Um zusätzliche Informationen zu vermitteln, ist eine Einfärbung hilfreich. Dies wird dann Heatmap genannt. In diesem Tutorial erfahren Sie, wie Sie eine Heatmap automatisch erstellen können. Die Idee ist, bedingte Formate aus Excel auf eine Tabelle in PowerPoint anzuwenden.

Übersicht

Dieses Tutorial beginnt mit Verkaufsdaten aus der fiktiven AdventureWorks-Datenbank. Zunächst werden wir die Heatmap in Excel vorbereiten. Danach werden wir eine PowerPoint-Vorlage für diese Heatmap erstellen. Schließlich werden wir SlideFab verwenden, um Excel mit PowerPoint zu verbinden und dann die Heatmap auf der Folie programmatisch zu erstellen. In diesem Tutorial zeigen wir zwei verschiedene Ansätze. Bei der einen wird eine Tabelle als Ziel in PowerPoint verwendet, bei der anderen werden einzelne Formen als Tabellenzellen verwendet.

Dieses Tutorial enthält keine Vervielfachung von Folien mit Hilfe der Wiederholungsschleife oder der Folien-Schleife. Dies wurde absichtlich ausgelassen, um dieses Tutorial nicht zu umfangreich werden zu lassen.

Was wird für dieses Tutorial “Wie man in PowerPoint automatisch eine Heatmap erstellt” benötigt?

Natürlich ist SlideFab erforderlich. Für dieses Beispiel ist die kostenlose Version SlideFab Lite nur für den ersten Ansatz ausreichend. Nämlich die, die eine Tabelle verwendet. Für den Ansatz, der (viele) Formen verwendet, benötigen Sie eine Vollversion von SlideFab.

Eine PowerPoint-Präsentation dient als Vorlage

Dieses Tutorial beginnt mit einer eher leeren PowerPoint-Präsentation mit nur einer Folie. Die Vorbereitungsschritte werden später gezeigt.

Dies ist die für dieses Tutorial verwendete PowerPoint-Vorlage

Eine Excel-Arbeitsmappe wird mit der PowerPoint-Präsentationsvorlage verbunden

Die für dieses Tutorial verwendete Excel-Arbeitsmappe ist schlank. Sie enthält nur ein Arbeitsblatt mit einer Datentabelle. Diese Daten beziehen sich auf den Umsatz nach Kategorie und Land im Laufe der Zeit.

Dies ist die Excel-Arbeitsmappe, die für dieses Tutorial verwendet wird

Laden Sie die Dateien des Tutorials herunter

Um die Heatmap in PowerPoint automatisch mit SlideFab zu erstellen, diesen Schritten folgen.

Dieses Tutorial umfasst eine größere Anzahl von Schritten. Die meisten dieser Schritte beziehen sich jedoch auf die Erstellung der Excel-Arbeitsmappe und der PowerPoint-Vorlage. SlideFab kommt erst gegen Ende ins Spiel.

Zeitbedarf: 30 Minuten.

1. TEIL 1: Erstellen der Excel-Arbeitsmappe mit der Heatmap-Berechnung und Farbgebung

   Eine wichtige Anmerkung vorweg: Die Verkaufsdaten befinden sich in einer Tabelle namens Data. Wenn Sie in einer Formel auf Spalten innerhalb dieser Tabelle verweisen, setzt Excel die Spaltennamen in Klammern. So bezieht sich z. B. Data[SalesTerritory.Country] auf alle Daten aus der Spalte SalesTerritory.Country in der Tabelle Data.

2. Die Länderliste berechnen

   - Die Excel-Arbeitsmappe öffnen.
   - Ein neues Arbeitsblatt erstellen.
   - Die Länderliste mit Hilfe der unten stehenden Formel hinzufügen.

   =SORT(UNIQUE(Data[SalesTerritory.Country]))

3. Die Monatsliste berechnen

   Die Spaltenüberschrift muss die letzten 13 Monate enthalten.

   Um die Daten zu sammeln, helfen UNIQUE (EINDEUTIG) und SORT (SORTIEREN) dabei, die Datumseinträge aus der Spalte Order Date.MonthKey in absteigender Reihenfolge abzurufen (das Neueste zuerst). Die SEQUENCE Funktion (SEQUENZ) erstellt eine Liste mit den Zahlen von 1 bis 13. Dann ruft INDEX alle Datumseinträge von allen Positionen der Liste 1,…,13 ab. Für die Heatmap soll jedoch der älteste Monat an erster Stelle stehen, so der andere SORT die Reihenfolge in aufsteigend abändert.

   =SORT(INDEX(UNIQUE(SORT(Data[Order Date.MonthKey],,-1)), SEQUENCE(1,13)),,,TRUE)

   Diese Formel dem Arbeitsblatt hinzufügen, um die Datumseinträge zu ermitteln.

4. Die Monatsliste formatieren

   Mit ein wenig Formatierung sehen diese Datumsangaben viel besser aus. Diese Formel verwendet DATE, um ein echtes Datum zu erstellen. Da der tatsächliche Tag keine Rolle spielt, wird lediglich eine 1 verwendet. Dann formatiert TEXT dieses Datum als MMM JJ, zum Beispiel JAN 25.

   =TEXT(DATE(LEFT(C2#,4),RIGHT(C2#,2),1),"MMM YY")

   Dem Arbeitsblatt diese Formel hinzufügen, um die Datumseinträge zu formatieren.

5. Die Marge-%-Berechnung hinzufügen

   Wenn die Spalten- und Zeilenüberschriften vorhanden sind, rücken die eigentlichen Daten in den Mittelpunkt. In der Tabelle wird die Marge in Prozent angegeben. Es handelt sich also um die absolute Marge geteilt durch den Gesamtumsatz. Da die Tabelle dies für jedes Land und jeden Monat tut, übernehmen die SUMIFS-Funktionen (SUMMEWENN) diese Aufgabe.

   =SUMIFS(Data[Margin abs],Data[SalesTerritory.Country],'Export to Table'!B4#,Data[Order Date.MonthKey],'Export to Table'!C2#) / SUMIFS(Data[Sales Amount],Data[SalesTerritory.Country],'Export to Table'!B4#,Data[Order Date.MonthKey],'Export to Table'!C2#)

   Diese Formel in das Arbeitsblatt einfügen, um die Marge in % pro Land und Monat zu berechnen.

6. Die Marge-% formatieren

   - Den gesamten Bereich mit den Ergebnissen der Marge % auswählen.
   - Die Einträge als Prozentsätze formatieren.

7. Erstellen der bedingten Formate für die Heatmap-Einfärbung

   So weit ist die Berechnung der Heatmap abgeschlossen. Was noch fehlt, ist etwas Farbe. Bedingte Formate werden die Farben anwenden. In diesem Beispiel reicht eine einzige Regel mit einer 3-Farben-Skala aus. Aber das funktioniert natürlich auch mit mehreren Regeln.

   - Den gesamten Bereich mit den Ergebnissen der Marge % auswählen.
   - Eine neue Regel für bedingte Formate hinzufügen.

8. Die bedingten Formatierung für die Farbgabe der Heatmap spezifizieren

   - Die 3-Farben-Skala mit dem Typ “Alle Zellen anhand ihrer Werte formatieren” auswählen.
   - Vernünftige Schwellenwerte festlegen.

9. So weit, so gut. Die Excel-Arbeitsmappe ist fertig und enthält eine funktionierende Heatmap.

   

10. TEIL 2: Verwendung einer Tabelle auf einer PowerPoint-Folie für die Heatmap

    In diesem Abschnitt befindet sich eine PowerPoint-Tabelle, die als Ziel für die Heatmap dient. Diese ist einfach zu verwenden und auch recht flexibel.

11. Einrichten der Vorlage-Folie

    Für dieses Beispiel ist ein statischer Titel in Ordnung.

    Einen Titel für die Folie der Vorlage hinzufügen.

12. Der Vorlagenfolie eine Tabelle mit 14 Spalten (für den Ländernamen + 13 Monate) und 2 Zeilen (1 für die Überschrift und 1 für die Daten) hinzufügen.

    Wenn es mehr Daten als Zeilen gibt, dann repliziert SlideFab die letzte Zeile, damit sie passt.

13. Die Zieltabelle für die Heatmap formatieren

    - Den Zellen der Tabelle etwas Blindtext hinzufügen, um die Formatierung zu erleichtern.
    - Die Tabelle wie gewünscht formatieren, indem z. B. die Spaltenbreite, die Textgröße und die Schriftattribute festgelegt wird.

14. Verknüpfung der Vorlagenpräsentation mit der Excel-Arbeitsmappe für die Heatmap

    - Die Registerkarte SlideFab in der Multifunktionsleiste öffnen.
    - Auf die kleine Schaltfläche mit Winkel nach unten klicken, auf der “Excel: Bereit” steht.
    - Einen der ersten drei Einträge auswählen
    — Mit „Excel-Datei auswählen und öffnen“ die Festplatte nach der Excel-Datei durchsuchen.
    — Mit „Excel-Datei öffnen“ wird eine bereits vorhandene Datei erneut geöffnet, sofern eine solche bereits vorhanden war. Ist dies nicht der Fall, hat es die gleiche Wirkung wie das zuvor genannte.
    — Mit „Versuche die derzeit geöffnete Excel-Arbeitsmappe zu verwenden“ wird versucht, eine Verbindung mit der zuletzt aktiven Excel-Arbeitsmappe herzustellen.

15. Dann richtet SlideFab Excel neben PowerPoint aus und signalisiert, dass die Verbindung mit dem grünen, geöffneten Ordnersymbol „Excel: Offen“ hergestellt ist.

    

16. Den Excel-Bereich der Heatmap mit der Tabelle auf der Folie der PowerPoint-Vorlage verbinden

    - Den gesamten Bereich auswählen, der die Heatmap enthält (mit Kopfzeile und Kopfspalte).
    - Die Zieltabelle in PowerPoint auswählen
    - Auf das Fadenkreuz-Symbol im „Excel-Quellbereich“ klicken, um die Verknüpfung zu definieren. SlideFab wird die Adresse des ausgewählten Bereichs in das Kontrollfeld schreiben.

17. Ausführen der Automatisierung und Feinabstimmung

    - Auf die Schaltfläche „Folien erzeugen“ auf der Registerkarte SlideFab im PowerPoint-Menüband klicken, um das entsprechende Fenster zu öffnen.
    - Auf die Schaltfläche „Start“ klicken, dann führt SlideFab die Automatisierung aus. Sie funktioniert auch ohne Wiederholungsschleife und verwendet den aktuellen Zustand der Excel-Arbeitsmappe.

18. SlideFab hat die Heatmap erstellt. Der Text wird jedoch innerhalb der Kopfzeile umgebrochen.

    Um dies zu beheben, kann Excel unangetastet bleiben. Die Einstellung von Spaltenbreiten in Excel hat keine Auswirkungen auf die Spaltenbreite in PowerPoint. Aber das Ändern des Zellrands in der Tabelle der PowerPoint-Vorlage erfüllt die Aufgabe.

19. Bei ausgewählter Vorlagentabelle auf die Registerkarte Tabellenlayout gehen und den Zellenrand verringern.

    

20. Die Automatisierung erneut starten, wie oben beschrieben.

    Perfekt. Die erstellte Heatmap in PowerPoint sieht sehr gut aus.

21. TEIL 3: Verwendung vieler Formen auf einer PowerPoint-Folie für die Heatmap

    Eine Alternative zur Verwendung einer Tabelle als Ziel in PowerPoint sind Formen. Jede Menge Formen. Während bisher der gesamte Heatmap-Bereich mit einer ganzen Tabelle verbunden war, wird dieses Mal jede Zelle der Heatmap in Excel mit einer Form verknüpft.

    Dieser Abschnitt ist kürzer gehalten, um die Wiederholung grundlegender Schritte von oben zu vermeiden.

    - Eine neue Folie in der Vorlagenpräsentation erstellen.
    - Formen für die Heatmap hinzufügen und formatieren. 14 Spalten und 7 Zeilen (1 für die Kopfzeile, 6 für die Länder).

22. Die neue Folie in der Template-Präsentation erstellen

    - Eine der Zellen der Heatmap auswählen
    - Die entsprechende Zielform in PowerPoint auswählen
    - Auf das Fadenkreuz-Symbol im „Excel-Quellbereich“ klicken, um die Verknüpfung zu definieren. SlideFab wird die Adresse der ausgewählten Zellen in das Kontrollfeld schreiben.
    - Wiederholen der Schritte, bis alle Zellen der Heatmap verbunden sind.

23. Die Automatisierung erneut starten, wie oben beschrieben.

    Die erstellte Ausgabefolie sieht gut aus. SlideFab hat die Heatmap in PowerPoint automatisch mit Hilfe von Formen erstellt.

    Die Verwendung von Formen anstelle einer Tabelle scheint auf den ersten Blick etwas albern zu sein. Es erfordert mehr Aufwand, alle Zellen einzeln zu verknüpfen. Außerdem ist es nicht so flexibel, was mehr oder weniger Zeilen und Spalten angeht. Die Shapes haben jedoch auch Vorteile. Je individueller die Ausgabefolien am Ende aussehen sollen, desto größer ist der Vorteil von separaten Formen. Das separate Formatieren und Verknüpfen von Zellen ist zwar aufwändiger, bietet dafür aber auch mehr Freiheit und Kontrolle.
    

Was könnte als nächstes kommen?

Wenn die Heatmap erstellt ist, können verschiedene Richtungen eingeschlagen werden

Dynamische Gestaltung der Heatmap mit Wiederholungsschleifen

In diesem Tutorial ging es um statische Heatmaps. Aber die Kombination von Heatmaps mit Wiederholungsschleifen ist ein äußerst leistungsfähiges Szenario, das Thema des nächsten Tutorials sein wird.

Flexiblere Gestaltung der Heatmap mit Formen durch Form-Bedingungen

Wenn sich die Anzahl der Zeilen ändert, ist der Heatmap-Ansatz mit Formen nicht so flexibel. Dies kann verbessert werden. Mit Formen-Bedingungen ist es möglich, nicht verwendete Formen bei der Erstellung von Folien zu löschen. Dies funktioniert natürlich auch in Kombination mit Wiederholungsschleifen.

Einfärben der Zellen auf der Grundlage einer zweiten Berechnung

In diesem Beispiel wurde die Marge % sowohl für die Tabelleneinträge als auch als Grundlage für das bedingte Format verwendet. Noch cooler wäre es, die absolute Marge als Tabelleneinträge zu verwenden und gleichzeitig die bedingten Formate auf der Basis von Marge % beizubehalten. Mit einer Änderung im Excel-Arbeitsblatt ist dies möglich.

Liste der SlideFab-Tutorials

Gehen Sie zum nächsten Tutorial in dieser Serie