Tutorial: Wie man mehrere Heatmaps mit unterschiedlichen bedingten Formatierungen in PowerPoint erstellt

Einführung

Im vorigen Tutorial haben wir uns mit der Erstellung einer Heatmap in PowerPoint auf der Grundlage von Excel beschäftigt. Hier werden wir größer denken. Eine Wiederholungsschleife wendet die bedingte Formatierung in PowerPoint auf verschiedene Heatmaps an, eine nach der anderen.

Übersicht

Dieses Lernprogramm baut auf dem vorherigen Lernprogramm auf. Hier ging es um die Erstellung einer Heatmap in PowerPoint für Verkaufsdaten. Wir haben die Marge in % nach Land und Monat dargestellt. In diesem Tutorial werden wir die Excel-Berechnung erweitern, um die Produktkategorie und die Unterkategorie zu berücksichtigen. Dementsprechend wird es für jede Unterkategorie eine eigene Heatmap mit unterschiedlichen Zahlen und unterschiedlichen bedingten Formatierungen in PowerPoint geben. Die wichtigste Funktion, die uns dabei hilft, ist die Wiederholungsschleife, die wir bereits in einem früheren Tutorial vorgestellt haben.

Was Sie für dieses Tutorial über das Erstellen mehrerer Wärmekarten mit unterschiedlichen bedingten Formatierungen in PowerPoint benötigen

Natürlich ist SlideFab erforderlich. Für dieses Beispiel ist die kostenlose Version SlideFab Lite für die tabellenbasierte Heatmap ausreichend. Für die Heatmaps mit mehreren Formen benötigen Sie hingegen eine Vollversion von SlideFab.

Die PowerPoint-Vorlage aus dem vorherigen Tutorial ist der Ausgangspunkt

Im letzten Tutorial haben wir zwei verschiedene Folienvorlagen für die Heatmap erstellt, die auf bedingter Formatierung basieren. Eine mit einer einzelnen PowerPoint-Tabelle und eine mit Formen.

Dies ist die erstellte PowerPoint-Vorlage aus dem letzten Tutorial. Sie ist unser Ausgangspunkt hier.

Die Excel-Arbeitsmappe mit der Heatmap-Berechnung aus dem vorherigen Tutorial ist unsere Grundlage

Im vorherigen Tutorial haben wir nur eine Heatmap auf der Grundlage der beispielhaften Umsatzdaten erstellt. Sie enthielt die Gewinnspanne in % für jede Kombination von Land und Monat für die letzten 13 Monate. Die Zahlen wurden mit SUMIFS (SUMMEWENNS) berechnet und die Farben wurden mit bedingter Formatierung angewendet.

Dies ist die Excel-Arbeitsmappe aus dem letzten Tutorial, die nun als Grundlage dient.

Laden Sie die Dateien des Tutorials herunter

Um die Wiederholungsschleife für die Erstellung mehrerer Heatmaps in PowerPoint mit SlideFab einzurichten folgendermaßen vorgehen

In diesem Tutorial werden teilweise Schritte wiederholt, die in früheren Tutorials erklärt wurden. Daher ist es etwas knapper gehalten.

Zeitbedarf: 5 Minuten.

1. TEIL 1: Dynamisierung der Excel-Arbeitsmappe, die die bedingte Formatierung für PowerPoint enthält

   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 eckige Klammern. So bezieht sich z. B. Data[SalesTerritory.Country] auf alle Daten aus der Spalte SalesTerritory.Country in der Tabelle Data.

2. Berechnen des Kandidatenbereichs

   Die Wiederholungsschleife soll für jede Unterkategorie eine Folie erstellen. Da es Unterkategorien aus verschiedenen Kategorien mit demselben Namen geben kann, ist ein eindeutiger Bezeichner unerlässlich. Die Kombination aus Kategorie und Unterkategorie ist eindeutig. Diese Formel verknüpft Kategorie und Unterkategorie mit einem Ausrufezeichen in der Mitte, um eine spätere Aufteilung zu ermöglichen.

   =SORT(UNIQUE(Data[Product.Category]&"!"&Data[Product.Subcategory]))

   Dies wird als Kandidatenbereich dienen, der alle Einträge enthält, die SlideFab bei der Erstellung der Folien durchläuft.

   - Die resultierende Excel-Arbeitsmappe aus dem vorherigen Tutorial öffnen.
   - Die kombinierte Kategorie + Unterkategorie-Liste mit Hilfe der Formel von oben erstellen.

3. Definieren und Arbeiten mit der Master-Zelle

   Die Masterzelle ist eine Zelle in Excel, in die SlideFab während den Wiederholungen den aktuellen Eintrag aus dem Kandidatenbereich schreibt. Formeln in anderen Zellen können auf die Master-Zelle verweisen, um z. B. weitere Daten nachzuschlagen.

   Eine Zelle als Master-Zelle definieren, einen der verketteten Einträge aus dem Kandidatenbereich kopieren und in die Master-Zelle einfügen. (Letzteres ist nicht erforderlich, aber es ist bequemer, als die Master-Zelle beim Erstellen der Arbeitsmappenlogik leer zu lassen. Welcher Eintrag es ist, spielt keine Rolle, SlideFab überschreibt ihn ohnehin, wenn der Kandidatenbereich zur Folienerstellung durchlaufen wird.)

4. Hilfsberechnung hinzufügen um aktuelle Kategorie und Unterkategorie zu ermitteln

   Die Verwendung des verketteten Inhalts in der Master-Zelle für Suchvorgänge ist umständlich. Daher ist es sinnvoll, ihn mit TEXTSPLIT in seine Komponenten aufzuteilen, nämlich Kategorie und Unterkategorie. Die Funktion TRANSPOSE (MTRANS) wird hier verwendet, um das Ergebnis als Zeilen statt als Spalten zu erhalten. Dies ist kein Muss.

   =TRANSPOSE(TEXTSPLIT(S4,"!"))

   Fügen Sie dem Arbeitsblatt diese Formel hinzu, um den Inhalt der Master-Zelle in Kategorie und Unterkategorie aufzuteilen.

5. Anreicherung der Formel zur Berechnung der Marge

   Wenn die aktuelle Kategorie und Unterkategorie berechnet sind, muss die Berechnung der Marge in % aktualisiert werden. Dafür einfach die Bedingungen für Kategorie und Unterkategorie zu den SUMIFS-Funktionen (SUMMEWENNS) hinzufügen, und die Arbeit ist getan. Die Heatmap zeigt nun die Zahlen und Farben für den aktuellen Master Cell-Eintrag an.

   Es überrascht nicht, dass nicht alle Länder über Daten für alle Kategorien und Unterkategorien verfügen. Dies führt dazu, dass die absolute Marge Null durch Null Umsatz geteilt wird. Um die hässliche Fehlermeldung #DIV/0! zu vermeiden, gibt die Funktion IFERROR (WENNFEHLER) stattdessen einen leeren Text zurück.

   =IFERROR(SUMIFS(Data[Margin abs],Data[SalesTerritory.Country],'Export to Table'!B4#,Data[Order Date.MonthKey],'Export to Table'!C2#,Data[Product.Category],'Export to Table'!S8,Data[Product.Subcategory],'Export to Table'!S9) / SUMIFS(Data[Sales Amount],Data[SalesTerritory.Country],'Export to Table'!B4#,Data[Order Date.MonthKey],'Export to Table'!C2#,Data[Product.Category],'Export to Table'!S8,Data[Product.Subcategory],'Export to Table'!S9),"")

   Die bestehende Formel zur Berechnung der Marge in % pro Land und Monat für die aktuelle Kategorie/Unterkategorie in der Master-Zelle aktualisieren.

6. Berechnen eines dynamischen Folientitels

   Im Gegensatz zum vorherigen Tutorial werden wir viele Heatmap-Folien erstellen. Daher müssen wir Kategorie und Unterkategorie in den Folientitel schreiben. Glücklicherweise erledigt eine einfache Formel, die einen festen Titel mit den Informationen aus der Master-Zelle verknüpft, diese Aufgabe. Eine weitere Alternative ist es, die Kategorie und die Unterkategorie in separate Formen in der Folienvorlage zu schreiben. Dies würde jedoch nicht funktionieren, da SlideFab Lite nur 2 Formen pro Folienvorlage zulässt. Daher ist es für dieses Tutorial sinnvoller, sie in den Titel zu schreiben.

   ="Margin % by Country over time " & S8 & ": " &S9

   Diese Formel dem Arbeitsblatt hinzufügen, um einen dynamischen Folientitel zu erstellen.

7. TEIL 2: Hinzufügen der neuen Funktionen zur bestehenden PowerPoint-Vorlage Präsentation

   In diesem Stadium ist es an der Zeit, die PowerPoint-Präsentation erneut mit Excel zu verknüpfen. Dies geschieht über die Schaltfläche mit der Aufschrift “Excel: Geschlossen” auf der Registerkarte SlideFab in der Multifunktionsleiste, wie in der vorherigen Anleitung erklärt.

8. Hinzufügen der Verbindung zwischen der Titelzelle in Excel und der Titelform in PowerPoint

   Die folgenden drei Schritte sind für beide Vorlagen die gleichen. Der Kürze halber werden sie nur einmal erklärt.
   - In Excel die Zelle mit der Titelformel markieren.
   - Die Zielform 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.

9. Einrichten der Wiederholungsschleife: Definieren der Master-Zelle für die aktuelle Kategorie/Unterkategorie-Kombination

   - Die Master-Zelle auswählen, in die SlideFab die aktuelle Kategorie / Unterkategorie während der Wiederholungsschleife schreibt.
   - Auf das Fadenkreuz-Symbol im Steuerelement „Schleifen-Master-Zelle“ klicken, um die Verknüpfung zu definieren. SlideFab schreibt die Adresse der ausgewählten Zelle in das Kontrollfeld.

10. Einrichten der Wiederholungsschleife: Definieren des Kandidatenbereichs mit allen Kombinationen von Kategorien und Unterkategorien

    - Die Spalte mit den verketteten Kategorien und Unterkategorien auswählen, die als Kandidatenbereich dient.
    - Auf das Fadenkreuz-Symbol im Steuerelement „Schleifen-Kandidaten-Bereich“ klicken, um die Verknüpfung zu definieren. SlideFab schreibt die Adresse der ausgewählten Zellen in das Kontrollfeld.

11. Ausschließen ausgeblendeter Folien

    Von den beiden Vorlagefolien (mit Tabellen bzw. Formen) wird nur eine benötigt. Um die Erstellung ausgeblendeter Folien zu vermeiden, muss das Kontrollkästchen „Ausgeblendete Folien ausschließen“ auf der Registerkarte „Erweiterte Einstellungen“ von SlideFab aktiviert werden.

12. 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, um SlideFab die Automatisierung ausführen zu lassen.

    Die Erstellung all dieser Folien dauert nur ein paar Minuten und die Ausgabe ist völlig in Ordnung. Für jede Kategorie/Unterkategorie-Kombination zeigen die Folien die richtigen Heatmaps.
    

13. Ausgabe überprüfen

    Der Screenshot zeigt die Ausgabe für die Vorlage-Folie mit Formen. Für Tabellen funktioniert es auch gut.

Ein weiteres interessantes Thema im Anschluss ist auch die automatische Ausrichtung von Tabellenzeilen zu den Datenpunkten in vertikalen Liniencharts.

Liste der SlideFab-Tutorials