HERBERS Excel-Forum - das Archiv
Thema: Pivottabelle mit statischer Tabelle kombinieren
Pivottabelle mit statischer Tabelle kombinieren
Helle
Hallo zusammen,
gibt eine Möglichkeit eine Pivottabelle mit einem statischen Teil einer Tabelle zu kombinieren?
Ich würde gerne den blauen Teil der Tabelle als Pivottabelle darstellen und den grünen Teil als statischen Teil dessen Berechnung sicher wiederum auf die Daten der Pivottabelle beziehen.
Hintergrund ist, dass ich gerne die "Details anzeigen" Funktion der Pivottabelle nutzen würde.
Gibt es eine Möglichkeit, die durch "Details anzeigen" generierten Tabellenblätter beim schließen der Datei automatisch und ohne Makros zu löschen?
Ich danke Euch für Eure Unterstützung!
Viele Grüße
Tobias
Anbei noch die Beispieldatei:
https://www.herber.de/bbs/user/180322.xlsx
gibt eine Möglichkeit eine Pivottabelle mit einem statischen Teil einer Tabelle zu kombinieren?
Ich würde gerne den blauen Teil der Tabelle als Pivottabelle darstellen und den grünen Teil als statischen Teil dessen Berechnung sicher wiederum auf die Daten der Pivottabelle beziehen.
Hintergrund ist, dass ich gerne die "Details anzeigen" Funktion der Pivottabelle nutzen würde.
Gibt es eine Möglichkeit, die durch "Details anzeigen" generierten Tabellenblätter beim schließen der Datei automatisch und ohne Makros zu löschen?
Ich danke Euch für Eure Unterstützung!
Viele Grüße
Tobias
Anbei noch die Beispieldatei:
https://www.herber.de/bbs/user/180322.xlsx
AW: Pivottabelle mit statischer Tabelle kombinieren
Yal
Hallo Tobias,
jein. Pivottabelle sind per se "Ergebnisse", also das Ende von einer Verarbeitungskette. Es lässt sich schlecht wiederverwenden.
Aber diese Verarbeitung basiert aber Basisdaten (bei Dir sogar "Datenbasis" benannt). Du musst die beiden Quelle kombinieren und erst dann eine Pivottable gestalten.
Markiere den Bereich A1:E8 in "Demo" (eigentlich nur A1:B8, da der Rest Berechnung sind) und klicke in Menü "Start" "Als Tabelle formatieren" (Arbeitsblätter haben Namen wie "Tabelle1", "Tabelle2", sind aber keine Tabellen, sondern Arbeitsblätter).
In dem gerade angezeigte Kontextmenü "Tabellenentwurf" ändere den Namen der Tabelle in "tblZiel".
Im Blatt "Datenbasis", gehe auf Zelle A1 und "Als Tabelle formatieren", ändere den Namen in "tblDaten".
Ändere übrigens die "Vertriebseinheiten" in tblZiel, sodass diese "0", "400", "402", usw sodass die Daten der beiden Tabelle miteinander verknüpft werden können.
Rechtsklicke auf der Tabelle "tblZiel" und wähle "Daten aus Tabelle/Bereich abrufen..."
Du bist in Power Query Editor und bekommst eine Abfrage "tblZiel".
Rechtsklicke diese Abfrage in der Abfrageliste links und wähle "Duplizieren"
klicke den Duplikat an, ändere rechts in Abfrageneigenschaften den Namen in "tblDaten",
direkt unten, rechtsklicke den 2te Schritt "Typ ändern" und lösche ihn,
der erste Schritt ist aktiv, in der Bearbeitungsleiste ("Formelleiste"), ändere die Quelle von "tblZiel" in "tblDaten" (Achtung: case sensitiv)
Dann im Menü "Transformieren", "Datentyp erkennen" anklicken. Der Schritt "Typ ändern" ist wieder da, aber auf Basis der Daten aus "tblDaten". Prüfe, dass die Spalte "Abschluss am" als Datum erkannt wurde. Wenn nicht rechtsklicke den Überschrift dieser Spalte, "Datentyp ändern", "Datum" wählen.
Gehe im Menü "Start", "Abfragen zusammenführen" ("Join")
wähle im Mittlere Menü die zweite Abfrage "tblZiel",
klicke in beiden Vorschau die Überschrift "Vertriebseinheiten" an,
Join-Art ist bereits korrekt. ok.
In der neue Spalte, den Symbol mit Doppelpfeil anklicken, alle Haken weg (auch bei Präfix) und Haken bei "Ziel" wieder einsetzen.
Menü "Datei", "schliessen und laden in...", "PivotTable-Bericht" wählen.
Du bist wieder in Excel klassisch. Pivot gestalten. Achtung: wenn "Ziel" ins Quadrant "Werte" gebracht wird, muss die Aggregation "min", "max" oder "Durchschnitt" sein. "Summe" wäre falsch.
"Abschluss am" ist ein Datum und wird, wenn ins Quadrant "Zeile" oder "Spalte" versschoben, automatisch in Monate und Jahre aggregiert.
Die Zielerreichung (ZE) wird als Berechnetes Feld im Menü "PivotTable-Analyse", "Felder, Elemente und Gruppen" eingerichtet.
Das Herunterbrechen der ZE auf Monate anhand der Arbeitstage pro Monat kann auch in Power Query berechnet und zugeordnet werden. Die Anzahl von AT pro Monat muss in einer Tabelle abgelegt werden und in Power Query einbezogen und gejoint werden.
VG
Yal
jein. Pivottabelle sind per se "Ergebnisse", also das Ende von einer Verarbeitungskette. Es lässt sich schlecht wiederverwenden.
Aber diese Verarbeitung basiert aber Basisdaten (bei Dir sogar "Datenbasis" benannt). Du musst die beiden Quelle kombinieren und erst dann eine Pivottable gestalten.
Markiere den Bereich A1:E8 in "Demo" (eigentlich nur A1:B8, da der Rest Berechnung sind) und klicke in Menü "Start" "Als Tabelle formatieren" (Arbeitsblätter haben Namen wie "Tabelle1", "Tabelle2", sind aber keine Tabellen, sondern Arbeitsblätter).
In dem gerade angezeigte Kontextmenü "Tabellenentwurf" ändere den Namen der Tabelle in "tblZiel".
Im Blatt "Datenbasis", gehe auf Zelle A1 und "Als Tabelle formatieren", ändere den Namen in "tblDaten".
Ändere übrigens die "Vertriebseinheiten" in tblZiel, sodass diese "0", "400", "402", usw sodass die Daten der beiden Tabelle miteinander verknüpft werden können.
Rechtsklicke auf der Tabelle "tblZiel" und wähle "Daten aus Tabelle/Bereich abrufen..."
Du bist in Power Query Editor und bekommst eine Abfrage "tblZiel".
Rechtsklicke diese Abfrage in der Abfrageliste links und wähle "Duplizieren"
klicke den Duplikat an, ändere rechts in Abfrageneigenschaften den Namen in "tblDaten",
direkt unten, rechtsklicke den 2te Schritt "Typ ändern" und lösche ihn,
der erste Schritt ist aktiv, in der Bearbeitungsleiste ("Formelleiste"), ändere die Quelle von "tblZiel" in "tblDaten" (Achtung: case sensitiv)
Dann im Menü "Transformieren", "Datentyp erkennen" anklicken. Der Schritt "Typ ändern" ist wieder da, aber auf Basis der Daten aus "tblDaten". Prüfe, dass die Spalte "Abschluss am" als Datum erkannt wurde. Wenn nicht rechtsklicke den Überschrift dieser Spalte, "Datentyp ändern", "Datum" wählen.
Gehe im Menü "Start", "Abfragen zusammenführen" ("Join")
wähle im Mittlere Menü die zweite Abfrage "tblZiel",
klicke in beiden Vorschau die Überschrift "Vertriebseinheiten" an,
Join-Art ist bereits korrekt. ok.
In der neue Spalte, den Symbol mit Doppelpfeil anklicken, alle Haken weg (auch bei Präfix) und Haken bei "Ziel" wieder einsetzen.
Menü "Datei", "schliessen und laden in...", "PivotTable-Bericht" wählen.
Du bist wieder in Excel klassisch. Pivot gestalten. Achtung: wenn "Ziel" ins Quadrant "Werte" gebracht wird, muss die Aggregation "min", "max" oder "Durchschnitt" sein. "Summe" wäre falsch.
"Abschluss am" ist ein Datum und wird, wenn ins Quadrant "Zeile" oder "Spalte" versschoben, automatisch in Monate und Jahre aggregiert.
Die Zielerreichung (ZE) wird als Berechnetes Feld im Menü "PivotTable-Analyse", "Felder, Elemente und Gruppen" eingerichtet.
Das Herunterbrechen der ZE auf Monate anhand der Arbeitstage pro Monat kann auch in Power Query berechnet und zugeordnet werden. Die Anzahl von AT pro Monat muss in einer Tabelle abgelegt werden und in Power Query einbezogen und gejoint werden.
VG
Yal
AW: Pivottabelle mit statischer Tabelle kombinieren
Helle
Hallo nochmal zusammen,
wenn es eine andere Möglichkeit ohne Pivot gibt, wie man die Detaildatensätze anzeigen lassen kann,
bin ich auch für andere Tipps offen und andere Ideen dankbar!
Danke vorab und viele Grüße
Tobias
wenn es eine andere Möglichkeit ohne Pivot gibt, wie man die Detaildatensätze anzeigen lassen kann,
bin ich auch für andere Tipps offen und andere Ideen dankbar!
Danke vorab und viele Grüße
Tobias
AW: Pivottabelle mit statischer Tabelle kombinieren
Helle
Hall Yal,
vielen, vielen herzlichen Dank für Deine ausführliche Anleitung.
Ich hab jetzt mehrfach versucht, Schritt für Schritt durchzugehen. Aber ich komme letztlich nicht zum gewünschten Ergebnis.
Den Stand den ich nachbauen kann habe ich einmal hochgeladen, aber irgendetwas mach ich trotz Deiner ausführlichen Beschreibung falsch?!?
https://www.herber.de/bbs/user/180323.xlsx
vielen, vielen herzlichen Dank für Deine ausführliche Anleitung.
Ich hab jetzt mehrfach versucht, Schritt für Schritt durchzugehen. Aber ich komme letztlich nicht zum gewünschten Ergebnis.
Den Stand den ich nachbauen kann habe ich einmal hochgeladen, aber irgendetwas mach ich trotz Deiner ausführlichen Beschreibung falsch?!?
https://www.herber.de/bbs/user/180323.xlsx
AW: Pivottabelle mit statischer Tabelle kombinieren
Yal
Hallo Tobias,
es ist nur eine Kleinigkeit, aber mit großer Wirkung:
in der Abfrage "tblDaten" (Power Query Editor mit Alt+F12 öffnen) im Schritt "Geänderter Typ" steht
Für "tblZiel" stand
Dann funktioniert der Join. Ein Join setzt voraus, dass die verbindende Daten dieselben Typ haben.
Ändere noch bei dem Schritt "Geänderter Typ" in der Abfrage "tblDaten" den Datum-Typ von
Verlasse den Power Query Editor mit Menü "Datei", "Schliessen & laden".
Ausgabe gestalten:
Wieder in Excel, mache die Abfragen über Menü "Daten", "Abfragen und Verbindungen" sichtbar.
Gehe auf die Abfrage "tblZiel", rechtsklicke drauf, wähle "Laden in...", setze auf "nur Verbindung erstellen.
Gehe auf die Abfrage "tblDaten", rechtsklicke drauf, wähle "Laden in...", setze auf "PivotTable-Bericht.
Es sieht dann so aus:
Also die Ziel auf Jahresebene sind nicht granular genug, um eine Aussage pro Monat zu machen. Man musste diese unterteilen:
Starte den PQ-Editor mit Alt+F12
gehe auf die Abfrage tblZiel
Menü "Spalten hinzufügen", "Benutzerdefinierte Spalte"
Spaltenname "Monatsziel" eingeben,
Formel
auf die Abfrage "tblDaten" wechseln, auf dem Rädchen links vom letzten Schritt "erweiterte tblZiel" klicken,
Haken beim "Ziel" wegnehmen, Haken bei "Monatsziel" setzen.
Fertig. Falls noch nicht gemacht, den Punkt "Ausgabe gestalten" durchgehen.
https://www.herber.de/bbs/user/180325.xlsx
Ein Problem bleibt weiterhin, dass die Daten nicht auf die gleiche Granularität liegen. Es führt dazu, dass der Join eine Vermehrung der Monatswert auf jede einzelne Tag. Ein Lösung dafür wäre, dass die Abfrage "angefügt" anstatt "zusammengeführt" werden.
VG
Yal
es ist nur eine Kleinigkeit, aber mit großer Wirkung:
in der Abfrage "tblDaten" (Power Query Editor mit Alt+F12 öffnen) im Schritt "Geänderter Typ" steht
..., {"Vertriebseinheit", Int64.Type} ...Für "tblZiel" stand
... {"Vertriebseinheit Nummer", type any} ...Dann funktioniert der Join. Ein Join setzt voraus, dass die verbindende Daten dieselben Typ haben.
Ändere noch bei dem Schritt "Geänderter Typ" in der Abfrage "tblDaten" den Datum-Typ von
{"Abschluss am", type datetime}{"Abschluss am", type date}Verlasse den Power Query Editor mit Menü "Datei", "Schliessen & laden".
Ausgabe gestalten:
Wieder in Excel, mache die Abfragen über Menü "Daten", "Abfragen und Verbindungen" sichtbar.
Gehe auf die Abfrage "tblZiel", rechtsklicke drauf, wähle "Laden in...", setze auf "nur Verbindung erstellen.
Gehe auf die Abfrage "tblDaten", rechtsklicke drauf, wähle "Laden in...", setze auf "PivotTable-Bericht.
Es sieht dann so aus:
Also die Ziel auf Jahresebene sind nicht granular genug, um eine Aussage pro Monat zu machen. Man musste diese unterteilen:
Starte den PQ-Editor mit Alt+F12
gehe auf die Abfrage tblZiel
Menü "Spalten hinzufügen", "Benutzerdefinierte Spalte"
Spaltenname "Monatsziel" eingeben,
Formel
=[Ziel]/12auf die Abfrage "tblDaten" wechseln, auf dem Rädchen links vom letzten Schritt "erweiterte tblZiel" klicken,
Haken beim "Ziel" wegnehmen, Haken bei "Monatsziel" setzen.
Fertig. Falls noch nicht gemacht, den Punkt "Ausgabe gestalten" durchgehen.
https://www.herber.de/bbs/user/180325.xlsx
Ein Problem bleibt weiterhin, dass die Daten nicht auf die gleiche Granularität liegen. Es führt dazu, dass der Join eine Vermehrung der Monatswert auf jede einzelne Tag. Ein Lösung dafür wäre, dass die Abfrage "angefügt" anstatt "zusammengeführt" werden.
VG
Yal
AW: Pivottabelle mit statischer Tabelle kombinieren
Helle
Hallo,
vielen Dank für die wirklich sehr ausführliche Beschreibung. Deine Ausführungen konnte ich leider nicht ganz nachbauen.
In der Zeile "Gehe auf die Abfrage "tblDaten", rechtsklicke drauf, wähle "Laden in...", setze auf "PivotTable-Bericht." war mein Ergebnis nicht mehr wie in Deinen Screenshots.
Deine Beispieldatei habe ich mir auch angeschaut. Vielen Dank. Ich denke die Darstellung wie ich mir sie wünsche Sheet "Demo" ist mit den Pivottabelle nicht darstellbar und damit auch nicht der Drilldown mittels "Details anzeigen"
Ist es denn irgendwie möglich die Funktion "Details anzeigen" aus Pivot Tabellen auch ohne Pivot Tabellen hinzubekommen?
Danke an Yal und danke an alle die mich mit Ihrer Zeit unterstützen!
Tobias
vielen Dank für die wirklich sehr ausführliche Beschreibung. Deine Ausführungen konnte ich leider nicht ganz nachbauen.
In der Zeile "Gehe auf die Abfrage "tblDaten", rechtsklicke drauf, wähle "Laden in...", setze auf "PivotTable-Bericht." war mein Ergebnis nicht mehr wie in Deinen Screenshots.
Deine Beispieldatei habe ich mir auch angeschaut. Vielen Dank. Ich denke die Darstellung wie ich mir sie wünsche Sheet "Demo" ist mit den Pivottabelle nicht darstellbar und damit auch nicht der Drilldown mittels "Details anzeigen"
Ist es denn irgendwie möglich die Funktion "Details anzeigen" aus Pivot Tabellen auch ohne Pivot Tabellen hinzubekommen?
Danke an Yal und danke an alle die mich mit Ihrer Zeit unterstützen!
Tobias