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

Einführung: Warum es wichtig ist, Diagramme mit Tabellen in PowerPoint auszurichten

Kürzlich hatte ich Kontakt zu einem Marktforschungsunternehmen, das über einen großen Folienbestand verfügt. Während die meisten ihrer Folien sofort mit SlideFab automatisiert werden konnten, schien ein Folienlayout schwierig. Tatsächlich war es nicht auf Anhieb möglich, die Datenpunkte eines Diagramms dynamisch genau in der Mitte einer Tabellenzeile auszurichten. Die Aufgabe besteht also darin, ein vertikales Liniendiagramm an einer Tabelle auszurichten. Im Grunde genommen handelt es sich um ein sehr ähnliches Layout wie das aus einem früheren Blogbeitrag über die Automatisierung von Likert-Diagrammen:

Der Hauptunterschied bestand jedoch darin, dass die Anzahl der Zeilen dynamisch sein und dennoch die Folienhöhe ausfüllen sollte. Während dies für das Diagramm keine Herausforderung darstellt, muss die Datentabelle eine variable Zeilenhöhe haben. Daher wurde mit SlideFab v.10.0 die Möglichkeit eingeführt, die Tabelle auf ihre ursprüngliche Höhe zu strecken, nachdem leere Zeilen entfernt wurden. In diesem Tutorial erfahren Sie, wie diese Funktion es ermöglicht, ein vertikales Liniendiagramm perfekt an eine Tabelle anzupassen. Perfekt bedeutet hier vor allem, dass jeder Datenpunkt vertikal in der Mitte einer Tabellenzeile liegt. Nur so sieht es anständig aus, wenn die Zeilen abwechselnde Farben als Hintergrund oder Rahmen zwischen den einzelnen Zeilenpaaren haben.

Übersicht

Dieses Tutorial beginnt mit Verkaufsdaten, die aus der fiktiven AdventureWorks-Datenbank stammen. Zunächst werden wir das Diagramm und die Datentabelle in Excel vorbereiten. Zweitens erstellen wir eine PowerPoint-Vorlage mit einer ersten Tabelle und einem verknüpften Diagramm. Schließlich werden wir SlideFab verwenden, um Excel mit PowerPoint zu verbinden und die Erstellung dieser Folien mit ausgerichteten vertikalen Liniendiagrammen und Tabellen zu automatisieren.

Dieses Tutorial ist recht lang, da es sehr ausführlich auf die Erstellung der Excel-Arbeitsmappe eingeht.

Was Sie für dieses Tutorial über das Ausrichten eines vertikalen Liniendiagramms an einer Tabelle in PowerPoint benötigen und wie Sie es hinsichtlich der Anzahl der Zeilen dynamisch gestalten können

Natürlich brauchen wir SlideFab. Für dieses Beispiel ist die kostenlose Version SlideFab Lite ausreichend.

Eine PowerPoint-Präsentation dient als Vorlage

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

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

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

Die für dieses Lernprogramm verwendete Excel-Arbeitsmappe ist schlank. Sie enthält zum Beispiel 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 für dieses Lernprogramm

Laden Sie die Dateien des Tutorials herunter

Um die Kombinationsfolien mit vertikalem Liniendiagramm und Tabelle mit SlideFab zu erstellen, folgen Sie diesen Schritten.

Dieses Lernprogramm 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 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 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(Data[Product.Category])

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(Data[Margin abs],Data[Product.Category],B4#) / SUMIFS(Data[Sales Amount],Data[Product.Category],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(Data[Margin abs],Data[Product.Category],G4,Data[Product.Subcategory],J4#) / SUMIFS(Data[Sales Amount],Data[Product.Category],G4,Data[Product.Subcategory],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.

    

Zusammenfassung dieses Tutorials zum Ausrichten eines vertikalen Liniendiagramms an einer Tabelle in PowerPoint

In diesem Tutorial wird erklärt, wie Sie ein vertikales Liniendiagramm in PowerPoint an einer Tabelle ausrichten können. Der wichtigste Aspekt dabei war die Skalierung der Y-Achse von 0 Minimum bis 1 Maximum anstelle einer automatischen Anpassung. Die automatische Anpassung hätte unvorhersehbare Skalierungen der Y-Achse zur Folge gehabt. Stattdessen erlaubten die festen Grenzen von 0 und 1, jeden Datenpunkt in der Mitte seiner Tabellenzeile zu positionieren. Außerdem bietet die neue Funktion, die Tabelle auf ihre ursprüngliche Höhe zu strecken, die Flexibilität, dass die Tabelle und das Diagramm die Folie weiterhin ausfüllen.

FAQ

In diesem Tutorial wird ein Streudiagramm verwendet, um das vertikale Liniendiagramm zu zeichnen. Ist dies auch mit einem Balkendiagramm möglich?

Nein, ein Balkendiagramm hat Kategorien (Beschriftungen) für jede Zeile. Es gibt keine Möglichkeit, die Position der Y-Achse festzulegen.

Liste der SlideFab-TutorialsEine wichtige Anmerkung vorweg: Die Umsatzdaten befinden sich in einer Tabelle namens Data. Wenn Sie in einer Formel auf Spalten in 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 mit dem Namen Data.