Tutorial: Wie man ein vertikales Liniendiagramm in PowerPoint an einer Tabelle ausrichtet und es hinsichtlich der Anzahl der Zeilen dynamisch macht

Benötigte Zeit: 30 Minuten

1. TEIL 1: Erstellen der Excel-Arbeitsmappe mit den Diagramm- und Tabellenberechnungen

   Eine wichtige Anmerkung vorweg: Die Umsatzdaten befinden sich in einer Tabelle namens Daten. Wenn Sie in einer Formel auf Spalten in dieser Tabelle verweisen, setzt Excel die Spaltennamen in Klammern. So bezieht sich z.B. Data[Verkaufsgebiet.Land] auf alle Daten aus der Spalte Verkaufsgebiet.Land in der Tabelle mit dem Namen Data

2. Abrufen der Länderliste

   – Öffnen Sie die Excel-Arbeitsmappe.
   – Erstellen Sie ein neues Arbeitsblatt.
   – Fügen Sie die Kategorieliste mithilfe der folgenden Formel hinzu.
   
   =UNIQUE(Daten[Produkt.Kategorie])

3. Ermitteln der Anzahl der Unterkategorien für jede Kategorie

   Dieser Schritt hilft Ihnen dabei, die maximale Anzahl der Zeilen für die Tabelle in PowerPoint zu bestimmen. Diese Anleitung lässt den Fall aus, dass es mehr Daten als Zeilen gibt. Für diese Fälle gibt es eine Erklärung, wie Sie die Daten auf mehrere Folien verteilen können.
   
   Verwenden Sie die folgende Formel, um die Anzahl der Unterkategorien pro Kategorie zu ermitteln:
   
   =BYROW(B4#,LAMBDA(category,COUNTA(UNIQUE(FILTER(Data[Product.Subcategory],Data[Product.Category]=category)))))
   
   Es funktioniert wie folgt. Die Funktion BYROW wird auf die Liste der Kategorien angewendet. Danach wird die Funktion LAMBDA auf jede Kategorie in dieser Liste angewendet. Innerhalb der LAMBDA-Funktion zählt ein einfaches COUNTA die Anzahl der UNIQUE-Einträge der Unterkategorien. Die Funktion FILTER sorgt dafür, dass nur Unterkategorien aus der aktuellen Kategorie (in Bezug auf die Funktion LAMBDA ) gezählt werden.

4. Berechnen der relativen Marge pro Kategorie

   Nachdem die Kategorieliste erstellt wurde, rücken die eigentlichen Daten in den Mittelpunkt. Insbesondere soll das Liniendiagramm die Kategorie-Marge in Prozent zum Vergleich anzeigen. Wir teilen also die absolute Marge durch den Gesamtumsatz. Die SUMIFS-Funktionen erledigen diese Aufgabe für jede Kategorie.
   
   =
SUMIFS(Daten[Marge abs],Daten[Produkt.Kategorie],B4#)
/SUMIFS(Daten[Umsatzbetrag],Daten[Produkt.Kategorie],B4#))

   Fügen Sie diese Formel dem Arbeitsblatt hinzu, um die Marge % pro Land und Monat zu berechnen.

5. Definieren der Masterzelle

   Für die Iterationsschleife über die Kategorien wird die Masterzelle die aktuelle Kategorie enthalten. Daher geben wir hier nur eine der Kategorien ein, um die spätere Erstellung der Arbeitsmappe zu erleichtern.

6. Nachschlagen der relativen Marge für die aktuelle Kategorie

   Wir werden lediglich die relative Marge aus dem zuvor berechneten Bereich nachschlagen:
   
   =XLOOKUP(G4,B4#,D4#)
   
   Natürlich ist es eine ebenso gute Idee, die Marge mit SUMIFS auf der Grundlage der Rohdaten hier zu berechnen.

7. Abrufen der Unterkategorienliste für die aktuelle Kategorie

   Da das Ziel darin besteht, die Marge % pro Unterkategorie anzuzeigen, suchen wir zunächst die Unterkategorien:
   
   =SORT(UNIQUE(FILTER(Data[Product.Subcategory],Data[Product.Category]=G4)))

8. Berechnen der relativen Marge pro Unterkategorie

   Mit der Liste der Unterkategorien ist es einfach, die relative Marge jeder einzelnen Kategorie zu ermitteln:
   
   =
SUMIFS(Daten[Marge abs],Daten[Produkt.Kategorie],G4,Daten[Produkt.Unterkategorie],J4#)
/SUMIFS(Daten[Umsatzbetrag],Daten[Produkt.Kategorie],G4,Daten[Produkt.Unterkategorie],J4#)
   
   Dies geschieht in ähnlicher Weise wie zuvor für die Kategorie unter Verwendung von SUMIFS.

9. Erstellen der Diagrammdaten, zunächst eine hilfreiche Zeilennummer

   Als erstes wird die Zeilennummer der Unterkategorie für die angegebene Kategorie berechnet. Dies ist optional, da sie von keiner anderen Formel in diesem Tutorial verwendet wird.
   
   =SEQUENCE(ROWS(J4#))

10. Berechnung der vertikalen Position jedes Datenpunktes der Unterkategorie im vertikalen Liniendiagramm

    Dies ist ein sehr wichtiger Schritt des Tutorials, da hier die vertikale Position für jeden Datenpunkt zwischen den Werten 0 und 1 berechnet wird. Der Grund dafür ist, dass die Y-Achse des Diagramms ihre untere und obere Grenze anhand der tatsächlichen Datenwerte bestimmt. Da dies jedoch nicht vorhersehbar ist, ist es unvermeidlich, die untere und obere Grenze auf konstante Werte festzulegen. Wir haben uns hier für die Werte 0 und 1 entschieden, weil es so einfacher zu berechnen ist. 
    
    =LET(countOfRows,ROWS(J4#),SEQUENCE(countOfRows,,1/(countOfRows)*0.5,1/(countOfRows))
    
    Diese Formel berücksichtigt auch, dass sowohl der erste als auch der letzte Datenpunkt in der Mitte einer Tabellenzeile liegen werden.

11. Die relativen Ränder pro Unterkategorie von vorher als X-Achsenwerte erhalten.

    Für den Diagrammdatenbereich erhalten wir lediglich die relativen Ränder pro Kategorie, wie sie zuvor berechnet wurden.

12. Abrufen der Y-Position für die Vergleichslinie der Kategorien.

    Für die Vergleichslinie können Sie die Y-Werte wie zuvor verwenden. Dies könnte jedoch seltsam aussehen, da die Datenpunkte für die Unterkategorienreihen in der Mitte jeder Zeile positioniert werden sollen. Stattdessen möchten wir eine vertikale Vergleichslinie haben, die vom obersten Bereich des Diagramms bis zum untersten reicht. Dementsprechend erstellen wir die Werte der Y-Achse ganz einfach, indem wir bei Y=0 beginnen und bis Y=1 reichen:
    
    =LET(countOfRows,ROWS(J4#),SEQUENCE(countOfRows,,0,1/(countOfRows-1)))

13. Ermitteln der relativen Marge der Kategorie als X-Achsenwerte.

    Für die Vergleichslinie benötigen wir denselben X-Wert für alle Zeilen. So stellen Sie sicher, dass die Vergleichslinie vertikal verläuft:
    
    =SEQUENCE(ROWS(J4#),,H4,0)
    
    
    WICHTIG (wie im Screenshot nicht gezeigt). Die Kopfzeile (im Screenshot Zelle R3) enthält eine Verkettung von Text und dem aktuellen Kategorienamen:
    
    ="Kategorie: " & G4

14. Fügen Sie einige Hilfszahlen hinzu, um den Datenbereich für das spätere Streudiagramm einfach zu erstellen.

    Dieser Schritt ist optional. Er hilft dabei, klar zu erkennen, über wie viele Zeilen sich die Daten für das Diagramm erstrecken könnten.

15. Erstellen eines Punktdiagramms mit Linien und Markierungen

    

16. Einstellen der X- und Y-Wertebereiche für die Unterkategorienreihen

    Um die Serien für die Unterkategorien zu erstellen, richten wir die Serien wie folgt ein. Die berechneten Werte der Y-Achse müssen als Y-Werte der Serie und die relativen Ränder als X-Werte der Serie festgelegt werden.

17. Einstellen der Datenbereiche für die X- und Y-Werte für die Kategorievergleichslinie

    Auch für die Kategorie-Vergleichslinie richten wir die Serie auf dieselbe Weise ein. Folglich müssen die berechneten Werte der Y-Achse als Serie Y-Werte und die Kategorienränder als Serie X-Werte eingestellt werden.

18. Einrichten der Y-Achse des Diagramms

    Wie oben erläutert, berechnen wir die Werte der Y-Achse in einem Intervall zwischen 0 und 1. Dementsprechend muss die automatische Berechnung der Grenzen diese Werte anstelle der automatischen Einstellung widerspiegeln. Außerdem müssen wir die Y-Achse umkehren, da die erste Zeile den Wert 0 (und nicht 1) hat. Die letzte Einstellung, die wir hier vornehmen, ist die Einstellung der Achsenüberquerung auf den maximalen Achsenwert anstelle von Automatisch. Dadurch wird sichergestellt, dass sich die Beschriftungen der X-Achse am unteren Rand des Diagramms befinden.

19. Feinabstimmung des Chart-Bereichs für das richtige Aussehen

    Bisher wurden die Werte der X-Achse als Dezimalzahlen angezeigt. Da der Rand ein Prozentwert ist, muss dies auch hier geändert werden. Außerdem ist es notwendig, die Markierungen von der Vergleichslinie auszublenden und am oberen Rand des Diagramms etwas Platz für eine hilfreiche Datenbeschriftung zu schaffen (siehe unten).

20. Erstellen des Datenetiketts für den obersten Datenpunkt

    Wählen Sie den obersten Datenpunkt aus, klicken Sie mit der rechten Maustaste und wählen Sie Datenetikett hinzufügen.

21. Formatierung der Datenbeschriftung

    Das Datenetikett positionieren wir über dem Datenpunkt und schalten es auf Serienname statt auf einen Wert um. Auf diese Weise ist immer klar, welche Bedeutung die vertikale Vergleichslinie hat. 
    
    Hinweis: Die Einstellung des Datenetiketts für den obersten Datenpunkt ist die einzige Möglichkeit, die hier Sinn macht. Der oberste Datenpunkt wird immer vorhanden sein, egal wie viele Zeilen (natürlich mindestens 1) das Diagramm anzeigt. Damit sich die Datenbeschriftung unten befindet, darf die Y-Achse nicht umgekehrt sein und die Werte der Y-Achse müssen von 1 bis 0 abnehmen.

22. Erstellen der Beschriftungen für die Datentabelle

    Die erste Spalte der Datentabelle soll die Unterkategorien enthalten. Wir übernehmen einfach die Beschriftungen von vorher.

23. Abrufen und Formatieren der Marge % pro Unterkategorie für die Datentabelle

    Mit Hilfe der TEXT-Formel können Sie die relative Marge als Prozentsatz formatieren: 
    
    =TEXT(K4#, "0.0%")

24. Fügen Sie einige Hilfszahlen hinzu, um den Bereich für die Datentabelle später leichter auswählen zu können.

    Dieser Schritt ist optional. Er hilft dabei, klar zu erkennen, über wie viele Zeilen sich die Daten für die Tabelle erstrecken könnten.
    
    Mit diesem Schritt ist die Excel-Arbeitsmappe abgeschlossen.

25. TEIL 2: Erstellen der Kombination aus perfekt ausgerichteter Tabelle und vertikalem Liniendiagramm in PowerPoint

26. Das Ziel für die Automatisierung ist eine Folie in einer PowerPoint-Präsentation mit einem festen Titel.

    

27. Hinzufügen einer Tabelle zur Vorlagefolie

    – Wählen Sie die Registerkarte „Einfügen“ in der Multifunktionsleiste von PowerPoint
    – Klicken Sie auf „Tabelle einfügen“
    – Erstellen Sie eine Tabelle mit 3 Spalten und 15 Zeilen. Da 14 die maximale Anzahl von Datenpunkten / Zeilen für dieses Beispiel ist, ist die 15. Zeile für die Überschrift vorgesehen. Die 3 Spalten hier sind für den Kategorienamen, die Marge % und den reinen Hintergrund für das Diagramm.

28. Zellränder auf schmal einstellen, um die Zeilenhöhe zu verringern

    

29. Entfernen Sie alle Zellrahmen, da diese aufgrund der gestreiften Zeilen der Tabelle überflüssig sind.

    

30. Einstellen der vertikalen Ausrichtung, damit die Texte in der Tabelle richtig an den Datenpunkten des Diagramms ausgerichtet sind.

    

31. Verknüpfung der PowerPoint-Präsentationsvorlage mit der Excel-Arbeitsmappe

    – Öffnen Sie die Registerkarte SlideFab in der Multifunktionsleiste.
    – Klicken Sie auf die kleine Schaltfläche mit dem Chevron (Winkel nach unten), wo „Excel: Geschlossen“.
    – Wählen Sie einen der ersten drei Einträge: 
    — „Excel-Datei auswählen und öffnen“ lässt Sie die Festplatte nach der Excel-Datei durchsuchen.
    — „Excel-Datei öffnen“ öffnet eine bereits vorhandene Datei erneut, falls es bereits eine gab. Ist dies nicht der Fall, hat es die gleiche Wirkung wie die zuvor genannte.
    — „Versuchen, die aktuell geöffnete Excel-Arbeitsmappe zu verwenden“ versucht, eine Verbindung mit der zuletzt aktiven Excel-Arbeitsmappe herzustellen.

32. Kopieren und Einfügen des vertikalen Liniendiagramms aus Excel in die PowerPoint-Folie

    Auf diese Weise hat das eingefügte Diagramm immer noch die Verbindung zu den Excel-Daten.

33. Ausrichten des vertikalen Liniendiagramms und der Datentabelle

    – Stellen Sie das vertikale Liniendiagramm vor die Tabelle (stellen Sie sicher, dass das Diagramm ganz vorne steht)
    – Verwenden Sie die Y-Achsenbegrenzungen von 0 und 1, um sie perfekt an der oberen und unteren Kante der ersten und letzten Datenzeile auszurichten.

34. Y-Achse und horizontale Linien aus dem Diagramm entfernen

    

35. Manuelle Tabellenüberschriften hinzufügen und das Datenetikett des Diagramms entsprechend einfärben

    

36. Verbinden des Excel-Tabellenbereichs mit der Tabelle auf der Folie der PowerPoint-Vorlage

    – Wählen Sie den Bereich, der die Tabellendaten enthält, bis hinunter zur letzten Zeile, in der wir Daten erwarten.
    – Wählen Sie die Zieltabelle in PowerPoint
    – Klicken Sie auf das Fadenkreuzsymbol im „Excel-Quellbereich“, um die Verknüpfung zu definieren. SlideFab schreibt die Adresse des ausgewählten Bereichs in das Kontrollfeld.
    — Setzen Sie „Start Row in PPT table“ auf 2. Dadurch bleibt die Kopfzeile in der PowerPoint-Tabelle erhalten.
    — Aktivieren Sie „Remove Empty Rows“.
    — Aktivieren Sie die Tabellendehnung mit der Einstellung „Start row and below“, um sicherzustellen, dass SlideFab nur Datenzeilen dehnt. Die Kopfzeile in Zeile 1 bleibt wie definiert.

37. Nur zur Erinnerung: Das eingefügte Diagramm in PowerPoint ist bereits für die Automatisierung eingerichtet.

    

38. TEIL 3. Einrichten der Iterationsschleife

    Bisher haben wir die Excel-Arbeitsmappe und die Folie der PowerPoint-Vorlage erstellt. Wir haben auch das vertikale Liniendiagramm und die Tabelle verknüpft. Das Einzige, was noch fehlt, ist die Einrichtung der Automatisierung für die Erstellung mehrerer kombinierter vertikaler Liniendiagramme mit Tabellen.

39. Einrichten des Kandidatenbereichs für die Kategoriezeilen

    – Wählen Sie die Spalte mit eindeutigen Bezeichnern aus (hier ist der Kategoriename ein eindeutiger Bezeichner für jedes Arbeitsblatt).
    – Öffnen Sie den Seitenbereich Iterationsschleife über das Menüband.
    – Klicken Sie auf das Fadenkreuz-Symbol im Steuerelement „Schleifen-Kandidatenbereich“, um die Verknüpfung zu definieren. SlideFab wird die Adresse der ausgewählten Zellen in das Kontrollfeld schreiben.

40. Einrichten der Masterzelle für den aktuellen Kategorienamen

    – Wählen Sie die Zelle aus, in die ein Wert (z.B. der Kategoriename) aus dem Kandidatenbereich geschrieben werden soll.
    – Klicken Sie auf das Fadenkreuz-Symbol im Steuerelement „Schleife Masterzelle“, um die Verknüpfung zu definieren. SlideFab wird die Adresse der ausgewählten Zelle in das Kontrollfeld schreiben.

41. Ausführen der Automatisierung

    – Klicken Sie auf die Schaltfläche „Folien erstellen“ auf der Registerkarte SlideFab im Powerpoint-Ribbon, um das entsprechende Fenster zu öffnen.
    – Wenn Sie auf die Schaltfläche „Start“ klicken, führt SlideFab die Automatisierung aus. Sie funktioniert auch ohne Iterationsschleife und verwendet den aktuellen Stand der Excel-Arbeitsmappe.

42. Warten Sie einige Augenblicke, bis die Folienautomatisierung abgeschlossen ist.

    

43. Führen Sie die Automatisierung der Folienerstellung wie oben beschrieben erneut aus und überprüfen Sie die Ausgabe.

    Perfekt. Die erstellten Kombinationsfolien mit vertikalem Liniendiagramm und Tabelle sind präzise ausgerichtet.

44. Die Nahaufnahme einer dieser Folien zeigt, dass die Datenpunkte genau in der Mitte jeder Zeile liegen.