Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Pivot Tabellen per VBA erweitern

Pivot Tabellen per VBA erweitern
13.03.2006 09:38:02
Scherer
Hallo Zusammen,
vieleicht kann mir ja einer Helfen, suche die Möglichkeit eine Pivot-Tabelle dynamisch zu erweitern. Das bedeutet es kommt immer wieder ein neuer Datensatz hinzu und den möchte ich Automatisch in die Pivot einpflegen lassen.
Danke für die Hilfe.
Gruß Rene´
Anzeige

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Pivot Tabellen per VBA erweitern
13.03.2006 10:16:34
Jutta
Hallo Rene,
ich lege den Bereich immer gleich um eine größere Anzahl von Zeilen an.
Dann brauche ich mich nicht mehr darum zu kümmern..
LG
Jutta
AW: Pivot Tabellen per VBA erweitern
13.03.2006 13:38:28
Scherer
Hallo,
danke für die schnelle Antwort, da habe ich nur das Problem wenn ich nach Monaten Gruppiere dies dann nicht mehr funktioniert. Deswegen würde ich den Bereich gerne per VBA erweitern. Um die Gruppierung weiterhin zu ermöglichen.
Gruß Rene´
Anzeige
AW: Pivot Tabellen per VBA erweitern
13.03.2006 14:53:39
Jutta
Hallo,
kann ich nicht ganz verstehen, ob manuelle oder Erweiterung durch VBA müsste doch das selbe bewirken. Bzw. kannst Du ja erst den Bereich ordentlich erweitern (das kann man schon schätzen) und dann erst gruppieren...
Aber ich habe das hier gefunden:
Pivot-Tabellen dynamisch aktualisieren
Versionen: Excel 97, 2000 und 2002/XP
FRAGE: Ich habe zwei Excel-Dateien angelegt. Die erste dient als Datenbank und in der zweiten befinden sich zehn Tabellenblätter mit Pivot-Tabellen. Das Problem besteht darin, dass die Datenbank laufend aktualisiert wird, was wiederum dazu führt, dass sich die Datenbasis der Pivot-Tabellen jedes Mal ändert. - Ich habe schon versucht, die Datenquellen der Pivot-Tabellen von vornherein auf einen "überdimensionierten" Bereich einzustellen (zum Beispiel A1:Z1000 obwohl nur die Zellen A1:Z432 mit Daten gefüllt sind). Dadurch erhalte ich aber unnötige und irreführende Leereinträge. Wissen Sie, wie sich die Aktualisierung der Pivot-Tabellen vereinfachen lässt?
ANTWORT: Für diese Aufgabe eignen sich "dynamische Bereichsnamen", die wir vor einiger Zeit schon einmal vorgestellt hatten. Hier noch einmal kurz eine Zusammenfassung der Funktionsweise:
1. Wechseln Sie zum Tabellenblatt mit Ihrer Datenbank. Dabei muss es sich um einen zusammenhängenden Listenbereich ohne leere Zellen handeln.
2. Definieren Sie einen neuen Bereichsnamen, indem Sie Einfügen-Name(n)-Festlegen (Excel 97) bzw. Definieren (Excel 2000/2002) anwählen und als erstes eine Bezeichnung Ihrer Wahl eingeben - beispielsweise "Pivotbasis".
3. Anstelle jetzt aber eine feste Bereichsadresse zuzuweisen, tragen Sie im Feld Bezieht sich auf eine Formel ein, die folgendes Muster hat:
=BEREICH.VERSCHIEBEN(&lt1. Datenbankzelle>0;
0;ANZAHL2(&ltBereich der 1. Datenbankspalte);
&ltSpaltenanzahl der Datenbank&gt)
Wenn Ihre Datenbank (inklusive Überschriften) etwa in Zelle A3 beginnt und in der Breite bis Spalte Z reicht (= 26 Spalten), geben Sie folgende Formel ein:
=BEREICH.VERSCHIEBEN($A$3;0;0;ANZAHL2($A$3:$A$65536);26)
• Bestätigen Sie den neuen Namen mit Ok.
Mit dem so definierten Bereichsnamen sprechen Sie automatisch die komplette Datenbank an, da sich die Höhe aufgrund der ANZAHL2-Funktion dynamisch an die Anzahl der Datensätze anpasst. Wenn Sie diesen Namen als Basis für Ihre Pivot-Tabelle(n) verwenden, brauchen Sie sich in Zukunft nicht mehr um entsprechende Anpassungen zu kümmern. Bei Pivot-Tabellen, die Sie direkt in der Arbeitsmappe mit der Datenbank anlegen, genügt die Angabe des Bereichsnamens.
Falls Sie die Pivot-Tabelle in einer separaten Datei einrichten wollen, müssen Sie erst die Datei mit der Datenbank öffnen. Excel ist ansonsten nicht in der Lage, den dynamischen Bereichsnamen aufzulösen (offensichtlich kann die BEREICH.VERSCHIEBEN-Funktion bei einer geschlossenen Arbeitsmappe nicht ausgewertet werden). Im Pivot-Tabellen-Assistenten geben Sie als Basisbereich anschließend den Dateinamen der Datenbankdatei, gefolgt von einem Ausrufezeichen sowie dem dynamischen Bereichsnamen ein - zum Beispiel:
DBMappe.xls!Pivotbasis
Was Ihnen diese Methode nicht abnimmt, ist die Aktualisierung der Pivot-Tabellen nach Eingabe neuer Datensätze in der Datenbankdatei. Immerhin genügt es, in den einzelnen Pivot-Tabellenblättern auf das Aktualisierungssymbol in der Symbolleiste PivotTable zu klicken.
Sofern mehrere Pivot-Tabellen auf einer identischen Datenbasis beruhen, wenn sie beispielsweise immer auf die komplette Datenbank mit dem dynamischen Namen "Pivotbasis" zurückgreifen, gibt es noch einen Trick, der die Arbeit erleichtert. Dann brauchen Sie in Zukunft nur noch eine Pivot-Tabelle dieser Gruppe manuell zu aktualisieren.
Sie erreichen das, indem Sie die zweite, dritte usw. Pivot-Tabelle auf der ersten basieren lassen. Hier die Verfahrensweise:
1. Richten Sie wie gewohnt eine Pivot-Tabelle ein, die auf dem dynamischen Datenbankbereich beruht.
2. Bei allen folgenden Pivot-Tabellen starten Sie ebenfalls den Assistenten aus dem Menü Daten. Auf die Frage, worauf die Pivot-Tabelle basieren soll bzw. welche Daten Sie analysieren möchten, antworten Sie aber mit der Option anderer Pivot-Tabelle (Excel 97), Weitere PivotTable oder PivotChart (Excel 2000) bzw. Anderen PivotTable-Bericht oder PivotChart-Bericht (Excel 2002).
3. Im nächsten Schritt des Assistenten wählen Sie die passende Pivot-Tabelle als Basis aus.
4. Danach fahren Sie wie gewohnt fort.
Der Vorteil dieses Verfahrens wird deutlich, wenn Sie die Datenbank erweitert haben und die Änderungen nun in Ihren Pivot-Tabellen analysieren möchten. Sie müssen jetzt nicht mehr alle Tabellenblätter einzeln aktualisieren. Es genügt, die Pivot-Tabelle, die tatsächlich auf der Datenbank basiert, auf den neuesten Stand zu bringen. Alle anderen Tabellen richten sich nach dem ersten Pivot-Bereich und sind somit automatisch aktuell.
Vielleicht hilft es etwas..
Gruß
Jutta
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Pivot-Tabellen dynamisch erweitern mit VBA


Schritt-für-Schritt-Anleitung

Um eine bestehende Pivot-Tabelle dynamisch zu erweitern, kannst Du VBA verwenden. Hier sind die Schritte:

  1. Öffne den VBA-Editor:

    • Drücke ALT + F11 in Excel.
  2. Füge ein neues Modul hinzu:

    • Rechtsklicke im Projekt-Explorer auf "VBAProject (DeineDatei.xlsx)" und wähle Einfügen > Modul.
  3. Füge den folgenden Code ein:

    Sub PivotTabelleErweitern()
       Dim pvt As PivotTable
       Dim ws As Worksheet
       Dim datenbereich As Range
    
       Set ws = ThisWorkbook.Sheets("Datenblatt") ' Ändere den Namen des Datenblatts
       Set pvt = ThisWorkbook.Sheets("PivotSheet").PivotTables("PivotTable1") ' Ändere den Namen des Pivot-Tabellenblatts und der Pivot-Tabelle
    
       ' Dynamischer Datenbereich
       Set datenbereich = ws.Range("A1").CurrentRegion ' Ändere den Bereich je nach Bedarf
    
       ' Pivot-Tabelle aktualisieren
       With pvt
           .ChangePivotCache ThisWorkbook.PivotCaches.Create( _
               SourceType:=xlDatabase, _
               SourceData:=datenbereich)
           .RefreshTable
       End With
    End Sub
  4. Passe den Code an:

    • Ändere die Namen des Datenblatts und der Pivot-Tabelle entsprechend Deinen Einstellungen.
  5. Führe das Makro aus:

    • Gehe zurück zu Excel, drücke ALT + F8, wähle PivotTabelleErweitern und klicke auf Ausführen.

Häufige Fehler und Lösungen

  • Fehler: Pivot-Tabelle wird nicht aktualisiert
    Lösung: Stelle sicher, dass die Namen im VBA-Code korrekt sind und der Datenbereich vollständig ist.

  • Fehler: Leere Zeilen in der Pivot-Tabelle
    Lösung: Überprüfe, ob Dein Datenbereich leere Zeilen enthält. Die CurrentRegion-Methode sollte dies vermeiden, aber es ist wichtig, die Daten zu bereinigen.


Alternative Methoden

Falls Du kein VBA verwenden möchtest, kannst Du auch den Datenbereich der Pivot-Tabelle manuell erweitern:

  1. Markiere die Pivot-Tabelle.
  2. Gehe auf die PivotTable-Analyse in der Menüleiste.
  3. Wähle "Datenquelle ändern" und passe den Bereich an.

Eine andere Möglichkeit ist die Verwendung von dynamischen Bereichsnamen, wie sie im Forum beschrieben werden. Diese ändern sich automatisch, wenn neue Daten hinzugefügt werden.


Praktische Beispiele

  • Pivot-Tabelle mit dynamischen Bereichsnamen: Du kannst den dynamischen Bereichsnamen "Pivotbasis" wie folgt definieren:

    =BEREICH.VERSCHIEBEN($A$3;0;0;ANZAHL2($A$3:$A$65536);26)
  • Beispiel für VBA: Verwende den oben angegebenen VBA-Code für eine einfache Aktualisierung Deiner Pivot-Tabelle.


Tipps für Profis

  • Nutze dynamische Pivot-Tabellen, um sicherzustellen, dass Deine Datenquelle immer aktuell ist, ohne dass Du manuell eingreifen musst.
  • Bei großen Datenmengen kann es hilfreich sein, die Pivot-Tabelle in separate Blätter zu unterteilen, um die Übersichtlichkeit zu erhöhen.
  • Erwäge, die Pivot-Tabelle um Spalten zu erweitern, um zusätzliche Analysen zu ermöglichen.

FAQ: Häufige Fragen

1. Wie kann ich die Datenquelle einer bestehenden Pivot-Tabelle erweitern?
Du kannst den Bereich manuell anpassen oder VBA verwenden, um den Bereich dynamisch zu erweitern.

2. Was sind dynamische Pivot-Tabellen?
Dynamische Pivot-Tabellen passen sich automatisch an, wenn Du neue Daten hinzufügst, sodass Du nicht jedes Mal die Datenquelle aktualisieren musst.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige