pivot, datenbereich automatisch aktualisieren?
michael himmelstoss
ich arbeite mit zwei arbeitsmappen.
die erste enthält die rohdaten in einem blatt, die zweite enthält ca
15 pivottabellen die alle auf den rohdaten der ersten mappe basieren.
jeden tag wird die rohdatentabelle mit den tagesaktuellen daten
erweitert, durch anhaengen der betreffenden zeilen.
mein problem:
der bezug des datenbereichs der pivottabellen aktualisiert sich nicht
selbst, d.h ich muss in jeder pivot ins layout gehen und den bezug um
die zellen erweitern, die neu hinzugekommen sind. ich habe auch schon
versucht, zwei zeilen mehr in die auswahl zu uebernehmen. funktioniert
auch nicht.
gibt es eine möglichkeit dies zu automatisieren, oder sonstwie zu
vereinfachen.
vielen dank
michael
Thomas Ramel
michael himmelstoss schrieb:
Du könntest einen Bereichsnamen verwenden, um den Datenbereich dynamisch zu
erfassen. Als Datenquelle für die Pivot-Tabelle(n) gibst Du dann diesen
Namen an:
Menü Einfügen --> Namen --> Definieren --> Namen in der Arbeitsmappe:
'Daten' --> Bezieht sich auf:
=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);2)
--> [Hiunzufügen]
Im obigen Beispiel ist A1 die linke obere Ecke des Datenbereiches;
ANZAHL2(A:A) zählt die Anzahl enthaltener Einträge in Splate A; 2 ist die
Anzahl Spalten, die der Datenbereich umfasst.
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für Microsoft-Excel -
[Win 2000Pro SP-4 / xl2000 SP-3]
michael himmelstoss
hallo Thomas,
>
> Du könntest einen Bereichsnamen verwenden, um den Datenbereich dynamisch zu
> erfassen. Als Datenquelle für die Pivot-Tabelle(n) gibst Du dann diesen
> Namen an:
>
> Menü Einfügen --> Namen --> Definieren --> Namen in der Arbeitsmappe:
> 'Daten' --> Bezieht sich auf:
>
> =BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);2)
ich habe in der mappe mit den pivottabellen einen namen vergeben mit
deiner formel. das funktioniert.
allerdings kann ich beim zuweisen des datenbereichs fuer die pivot
diesen namen nicht verwenden. entweder kommt die fehlermeldung
"Verweis ist ungültig", wobei excel mir dann den bereich
eigenartigerweise anzeigt.
oder, bei direkter eingabe der formel in den datenbereich sagt mir
excel "Pivot Table Quelldatei BEREICH.VERSCHIEBEN(.....) kann nicht
geöffnet werden", obwohl die mappe geoeffnet ist.
was mach ich falsch?
viele gruesse
michael
Thomas Ramel
michael himmelstoss schrieb:
>
>>Du könntest einen Bereichsnamen verwenden, um den Datenbereich dynamisch zu
>>erfassen. Als Datenquelle für die Pivot-Tabelle(n) gibst Du dann diesen
>>Namen an:
>>
>>Menü Einfügen --> Namen --> Definieren --> Namen in der Arbeitsmappe:
>>'Daten' --> Bezieht sich auf:
>>
>>=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);2)
>
> ich habe in der mappe mit den pivottabellen einen namen vergeben mit
> deiner formel. das funktioniert.
Das ist ja schon mal gut...
> allerdings kann ich beim zuweisen des datenbereichs fuer die pivot
> diesen namen nicht verwenden. entweder kommt die fehlermeldung
> "Verweis ist ungültig", wobei excel mir dann den bereich
> eigenartigerweise anzeigt.
...das allerdings weniger...
> oder, bei direkter eingabe der formel in den datenbereich sagt mir
> excel "Pivot Table Quelldatei BEREICH.VERSCHIEBEN(.....) kann nicht
> geöffnet werden", obwohl die mappe geoeffnet ist.
Hmm, der letzte Nachsatz lässt darauf schliessen, dass die Daten und die
Pivot-Tabelle *nicht* in derselben Mappe sind?
Dann wirds in der Tat schwieriger - Du kannst dich nicht in Mappe A auf
einen Namen beziehen, der in Mappe B definiert wurde.
Definiere den Namen in der Mappe mit der Pivot-Tabelle und gib dann bei den
Bereichen jeweils den Bezug zur anderen Mappe mit an.
Dazu die Formel wider in einer Zelle zusammenbauen, dann kannst Dumit dem
Assistenten die Bereiche markieren und die Bezüge zur anderen Mappe müssten
korrekt eingefügt werden.
Mit diesem Namen müsste es dann klappen - ansonsten verlege die
Pivottabelle in dieselbe Mappe, die auch die Daten enthält.
michael himmelstoss
> Hmm, der letzte Nachsatz lässt darauf schliessen, dass die Daten und die
> Pivot-Tabelle *nicht* in derselben Mappe sind?
stimmt, wie ich im ersten satz meines postings gleich herausgestellt
habe.
SCNR ;-).
> Dann wirds in der Tat schwieriger - Du kannst dich nicht in Mappe A auf
> einen Namen beziehen, der in Mappe B definiert wurde.
>
> Definiere den Namen in der Mappe mit der Pivot-Tabelle und gib dann bei den
> Bereichen jeweils den Bezug zur anderen Mappe mit an.
> Dazu die Formel wider in einer Zelle zusammenbauen, dann kannst Dumit dem
> Assistenten die Bereiche markieren und die Bezüge zur anderen Mappe müssten
> korrekt eingefügt werden.
ich habs versucht mit der oben beschriebenen methode.
den namen habe ich wie folgt definiert:
=BEREICH.VERSCHIEBEN(gp_nov_nw!$A$1;;;ANZAHL2(gp_nov_nw!$A:$A);36).
aber da erhalte ich ebenso eine der fehlermeldungen
> Mit diesem Namen müsste es dann klappen - ansonsten verlege die
> Pivottabelle in dieselbe Mappe, die auch die Daten enthält.
das hatte ich anfangs so und bin dann umgestiegen auf die zwei mappen
loesung, da ich mittlerweile zuviele pivots habe und die mappe dann zu
gross wird. ich muss die datentabelle oefter schliessen und wieder
oeffnen, vom server auf verschiedenen arbeitsplaetzen.
ich habe auch versucht die tabelle mittels bereich.verschieben und
auch mit einem einfachen bezug in die pivotmappe zu "kopieren". leider
rechnet sich excel dann bei jeder operation zu tode.
trotzdem vielen dank fuer deine hilfe. vielleicht faellt dir ja noch
was ein, wenn nicht muss ich damit leben.
viele grusse
michael
Thomas Ramel
michael himmelstoss schrieb:
>
>>Hmm, der letzte Nachsatz lässt darauf schliessen, dass die Daten und die
>>Pivot-Tabelle *nicht* in derselben Mappe sind?
>
> stimmt, wie ich im ersten satz meines postings gleich herausgestellt
> habe.
> SCNR ;-).
Danke für den sanften Hinweis :-)
Schon oft hat jemand von zwei Mappen gesprochen und in Wirklichkeit zwei
Tabellenblätter in derselben Mappe gemeint - ich war da wohl etwas zu sehr
vorgespurt ;-)
>>Definiere den Namen in der Mappe mit der Pivot-Tabelle und gib dann bei den
>>Bereichen jeweils den Bezug zur anderen Mappe mit an.
>>Dazu die Formel wider in einer Zelle zusammenbauen, dann kannst Dumit dem
>>Assistenten die Bereiche markieren und die Bezüge zur anderen Mappe müssten
>>korrekt eingefügt werden.
>
> ich habs versucht mit der oben beschriebenen methode.
>
> den namen habe ich wie folgt definiert:
> =BEREICH.VERSCHIEBEN(gp_nov_nw!$A$1;;;ANZAHL2(gp_nov_nw!$A:$A);36).
> aber da erhalte ich ebenso eine der fehlermeldungen
Da müsste aber zumindest der Name der Quellmappe noch mit drin sein - wobei
der Test bei mir ebenfalls den Fehler 'Verweis ungültig' ergeben hat.
Die Pivot-Tabelle erträgt offenbar nur einen direkten Bezug zur gleichen
Mappe oder dann einen Bezug zu esternen Daten, der bereits in der
Einstigesmaske des Assistenten vorgegeben werden muss.
>>Mit diesem Namen müsste es dann klappen - ansonsten verlege die
>>Pivottabelle in dieselbe Mappe, die auch die Daten enthält.
>
> das hatte ich anfangs so und bin dann umgestiegen auf die zwei mappen
> loesung, da ich mittlerweile zuviele pivots habe und die mappe dann zu
> gross wird. ich muss die datentabelle oefter schliessen und wieder
> oeffnen, vom server auf verschiedenen arbeitsplaetzen.
An sich ist diese Idee gut...
> ich habe auch versucht die tabelle mittels bereich.verschieben und
> auch mit einem einfachen bezug in die pivotmappe zu "kopieren". leider
> rechnet sich excel dann bei jeder operation zu tode.
Dannn werden alle Daten kopiert oder zumindest auf ihre Aktualität geprüft,
was dann schon einiges an Zeit in Anspruch nehemn kann, je nachdem wie
gross der Datenbereich ist.
> trotzdem vielen dank fuer deine hilfe. vielleicht faellt dir ja noch
> was ein, wenn nicht muss ich damit leben.
Wie schaut es dann aus, wenn Du den Datenbereich nach unten entsprechend
grösser ausdehnst? Du erhältst dann zwar einen zusätzlichen Eintrag namens
'Leer', den Du aber ausblenden kannst.
Des weiteren könntest Du *eine* Pivot-Tabelle direkt mit den Daten
verknüpfen und die restlichen dann basierend auf dieser ersten aufbauen -
das spart dann IMO jeweils etwas Zeit beim aktualisieren und Du musst nur
den Datenbereich der ersten Pivot-Tabelle anpassen, wenns mal eine Änderung
gibt.
michael himmelstoss
hallo Thomas,
> Wie schaut es dann aus, wenn Du den Datenbereich nach unten entsprechend
> grösser ausdehnst? Du erhältst dann zwar einen zusätzlichen Eintrag namens
> 'Leer', den Du aber ausblenden kannst.
wie immer sind die simpelsten loesungen die besten :-). ja, das ist
eine moeglichkeit, die ich wahrscheinlich nutzen werde.
ich habs in der zwischenzeit per "bastel"makro geloest. ein makro,
dass alle pivot blaetter abklappert, und den bereich der pivot
definiert.
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'\[gp_nov_joint.xls]gp_nov_nw'!R1C1:R11182C36"
da muss ich "nur" noch einmal die zeilenzahl anpassen und dann funzt
es. damit kann ich leben.
> Des weiteren könntest Du *eine* Pivot-Tabelle direkt mit den Daten
> verknüpfen und die restlichen dann basierend auf dieser ersten aufbauen -
> das spart dann IMO jeweils etwas Zeit beim aktualisieren und Du musst nur
> den Datenbereich der ersten Pivot-Tabelle anpassen, wenns mal eine Änderung
> gibt.
das ist mir als loesung auch eingefallen, aber da haette ich 15 pivots
neu aufbauen muessen, mit allen formatierungen etc. da war ich zu faul
:-(.
also, vielen dank nochmal fuer die inputs. hast mir sehr
weitergeholfen.
viele gruesse
michael
Thomas Ramel
michael himmelstoss schrieb:
>
>>Wie schaut es dann aus, wenn Du den Datenbereich nach unten entsprechend
>>grösser ausdehnst? Du erhältst dann zwar einen zusätzlichen Eintrag namens
>>'Leer', den Du aber ausblenden kannst.
>
> wie immer sind die simpelsten loesungen die besten :-). ja, das ist
> eine moeglichkeit, die ich wahrscheinlich nutzen werde.
Fein, es freut mich, wenn Du damit weiterkommst.
Auc ich bin ein Freund der 'einfachen' Lösung - mit den enstprechenden
Kenntinssen der Randbedingungen fällt es so auch leichter eine solche zu
finden.
> ich habs in der zwischenzeit per "bastel"makro geloest. ein makro,
> dass alle pivot blaetter abklappert, und den bereich der pivot
> definiert.
>
> ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
> "'\[gp_nov_joint.xls]gp_nov_nw'!R1C1:R11182C36"
>
> da muss ich "nur" noch einmal die zeilenzahl anpassen und dann funzt
> es. damit kann ich leben.
Auch das ist eine Möglichkeit.
>>Des weiteren könntest Du *eine* Pivot-Tabelle direkt mit den Daten
>>verknüpfen und die restlichen dann basierend auf dieser ersten aufbauen -
>>das spart dann IMO jeweils etwas Zeit beim aktualisieren und Du musst nur
>>den Datenbereich der ersten Pivot-Tabelle anpassen, wenns mal eine Änderung
>>gibt.
>
> das ist mir als loesung auch eingefallen, aber da haette ich 15 pivots
> neu aufbauen muessen, mit allen formatierungen etc. da war ich zu faul
> :-(.
Tja; Design ist auch in Excel bei umfassenderen Projekten von Wichtigkeit -
aber auch hier ist es meist so, dass einiges erst hinzuwächst, während das
Projekt gedeiht.
Wenn Du mal eine neue Auswertung aufbaust, kannst Du es entsprechend
berücksichtigen.
> also, vielen dank nochmal fuer die inputs. hast mir sehr
> weitergeholfen.
Wie gesagt - es freut mich, wenn ich helfen konnte.
Danke auch für die Rückmeldung(en) :-)