Pivot Felder automatisch auswählen

[Frank Vellner]

Liebe Gruppe,

ich muss öfter in einer PT aus einem Seitenfeld mit vielen Elementen
unterschiedliche Sets von Elementen auswählen. Weil das etwas aufwändig
und fehleranfällig ist, suche ich nach einem Weg, ein solches Set
ausserhalb der PT (in Einzelzelle mit Trenner zwischen den Elementen
oder als ein Element pro Zelle oder mit Indikatorzelle neben Elementen)
im selben Tabellenblatt zu definieren, um es per Klick in das
Seitenfeld zu übernehmen.

Anders ausgedrückt wäre das die Umkehrung zu der von T. Ramel
vorgestellten Lösung zum Auslesen von Elementen eines Feldes. (s.
Thread "Felder mit Formel auslesen" vom 31.1.07, Function
Pivot_Items(rngZelle As Range) ) . Jetzt löse ich die Sache teilweise
so, dass ich aus dem Seitenfeld kurzfristig ein Reihenfeld mache, weil
dort wenigstens Checkboxen in einem Pulldown sind - das geht einfacher
als die Selektion bei Reihenfeldern.

Viele Grüße
Frank

[Thomas Ramel]

Grüezi Frank

Post by Frank Vellner
ich muss öfter in einer PT aus einem Seitenfeld mit vielen Elementen
unterschiedliche Sets von Elementen auswählen. Weil das etwas aufwändig
und fehleranfällig ist, suche ich nach einem Weg, ein solches Set
ausserhalb der PT (in Einzelzelle mit Trenner zwischen den Elementen
oder als ein Element pro Zelle oder mit Indikatorzelle neben Elementen)
im selben Tabellenblatt zu definieren, um es per Klick in das
Seitenfeld zu übernehmen.
Jetzt löse ich die Sache teilweise
so, dass ich aus dem Seitenfeld kurzfristig ein Reihenfeld mache, weil
dort wenigstens Checkboxen in einem Pulldown sind - das geht einfacher
als die Selektion bei Reihenfeldern.

IMO ist dies anders auch nicht möglich - das Seitenfeld einer PT ist dazu
gedacht aus der Auswahl *ein* Element zu wählen - auch per VBA kannst Du im
Seitenfeld keine Mehrfach-Auswahl vornehmen.

Anders sieht es im Zeilen/Spaltenfeld aus - hier kannst Du aus der Menge der
Items die dich interessierenden auswählen.

Dass ein Zeilenfeld, das nach der Mehrfach-Auswahl zurück in den
Seitenbereich gescohben wird, diese Merhfach-Auswahl behält ist ein IMO nicht
dokumentiertes 'Feature'.

Du könntest also die Items in einer Spalte auflisten und dann per VBA, das
betreffende Seitenfeld in den Zeilenbereich verschieben - hier die Asuwahl
gemäss deiner Liste vornehmen und das Feld wieder zurück schieben.
Mit dem Makro-Recorder kommst Du an die benötigten Methoden und deren
Symtax; der Rest ist Fleissarbeit beim Umsetzen einer Schleife über alle
gewünschten Items.

[Frank Vellner]

Moin Thomas,

Post by Thomas Ramel
IMO ist dies anders auch nicht möglich - das Seitenfeld einer PT ist
dazu gedacht aus der Auswahl *ein* Element zu wählen - auch per VBA
kannst Du im Seitenfeld keine Mehrfach-Auswahl vornehmen.

hmm, vielleicht habe ich mich da falsch ausgedrückt. Ich wähle ständig
aus den Seitenfeldern einige aus bzw. genauer gesagt: Ich wähle für
viele das "ausblenden". Die die nicht ausgeblendet sind - und das kann
eines oder mehrere sein - werden halt angezeigt. Die angezeigten habe
ich als ausgewählt bezeichnet.

Post by Thomas Ramel
Anders sieht es im Zeilen/Spaltenfeld aus - hier kannst Du aus der
Menge der Items die dich interessierenden auswählen.

ja, aber im Seitenfeld eben auch. Nur halt über den Umweg des
Ausblendens der nicht betrachteten.

Post by Thomas Ramel
Dass ein Zeilenfeld, das nach der Mehrfach-Auswahl zurück in den
Seitenbereich gescohben wird, diese Merhfach-Auswahl behält ist ein
IMO nicht dokumentiertes 'Feature'.

geht aber per schlichten Anklicken nach Doppelklick auf ein Seitenfeld
ebenso.

Post by Thomas Ramel
Du könntest also die Items in einer Spalte auflisten und dann per
VBA, das betreffende Seitenfeld in den Zeilenbereich verschieben -
hier die Asuwahl gemäss deiner Liste vornehmen und das Feld wieder
zurück schieben.
Mit dem Makro-Recorder kommst Du an die benötigten Methoden und deren
Symtax; der Rest ist Fleissarbeit beim Umsetzen einer Schleife über
alle gewünschten Items.

beim Aufzeichnen mit dem Makro-Recorder sehe ich exakt das selbe, egal
ob ich ein Zeilenfeld oder ein Seitenfeld bearbeite. Nur die
Feldbezeichnung ist natürlich anders. Dort kommt soetwas wie:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Keyword")
.PivotItems("Keyword1").Visible = False
.PivotItems("Keyword2").Visible = False
.PivotItems("Keyword3").Visible = False

usw.

Dabei ist mir nicht klar, wie ich verifizieren kann, was für Excel die
PivotTable1 ist.

Mit der Schleife bekomme ich es nicht sinnig hin. Ich versuche, dem
Makro zu sagen: Nimm alle unterhalb der aktuellen Curserposition
befindlichen Zellen (=zusammenhängender Bereich ohne Leerzeichen etc.)
und nutze deren Inhalt, um das identische Element im Pivot-Feld mit
.Visible = False zu versehen.

Viele Grüße
Frank

[Thomas Ramel]

Grüezi Frank

Frank Vellner schrieb am 18.02.2007

Post by Frank Vellner

Post by Thomas Ramel
IMO ist dies anders auch nicht möglich - das Seitenfeld einer PT ist
dazu gedacht aus der Auswahl *ein* Element zu wählen - auch per VBA
kannst Du im Seitenfeld keine Mehrfach-Auswahl vornehmen.

hmm, vielleicht habe ich mich da falsch ausgedrückt. Ich wähle ständig
aus den Seitenfeldern einige aus bzw. genauer gesagt: Ich wähle für
viele das "ausblenden". Die die nicht ausgeblendet sind - und das kann
eines oder mehrere sein - werden halt angezeigt. Die angezeigten habe
ich als ausgewählt bezeichnet.

OK....

Post by Frank Vellner

Post by Thomas Ramel
Dass ein Zeilenfeld, das nach der Mehrfach-Auswahl zurück in den
Seitenbereich gescohben wird, diese Merhfach-Auswahl behält ist ein
IMO nicht dokumentiertes 'Feature'.

geht aber per schlichten Anklicken nach Doppelklick auf ein Seitenfeld
ebenso.

...das hier habe ich soeben dazugelernt :-)
Ich hatte mich zwar immer gewundert, dass ich Items im Zeilenfeld
ausblenden konnte und diese dann nach dem Zurückschieben ins Seitenfeld
auch ausgeblendet blieben - auf die Idee mit dem Doppelklick im Seitenfeld
bin ich aber nicht gekommen.

Post by Frank Vellner
beim Aufzeichnen mit dem Makro-Recorder sehe ich exakt das selbe, egal
ob ich ein Zeilenfeld oder ein Seitenfeld bearbeite.

Ja, das ist demanch wieder klar und so zu erwarten.

Post by Frank Vellner
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Keyword")
.PivotItems("Keyword1").Visible = False
.PivotItems("Keyword2").Visible = False
.PivotItems("Keyword3").Visible = False
usw.
Dabei ist mir nicht klar, wie ich verifizieren kann, was für Excel die
PivotTable1 ist.

Diesen Namen kannst Du in den Optionen der Pivot-Tabelle ganz oben
festlegen.

Post by Frank Vellner
Mit der Schleife bekomme ich es nicht sinnig hin. Ich versuche, dem
Makro zu sagen: Nimm alle unterhalb der aktuellen Curserposition
befindlichen Zellen (=zusammenhängender Bereich ohne Leerzeichen etc.)
und nutze deren Inhalt, um das identische Element im Pivot-Feld mit
.Visible = False zu versehen.

Ich würds andersrum machen - alle .PivotItems durchlaufen und mit z.B.
.CountIf im betreffenden Bereich suchen lassen. Wenn das Item dort vorhaden
ist, dann ausblenden und zum nächsten gehen.
Dabei darauf achten, dass immer mindestens *ein* Pivot-Item eingeblendet
sein muss.
Wenn Du dem Bereich mit den auszublendenden Items einen dynamischen
Bereichsnamen 'verpasst' ist es auch egal wo diese stehen.
Ansernfalls kannst Du den Code gut auch auf .ActiveCell anpassen und den
unterhalb liegenden Bereich im Code selber einmal erfassen und damn mit
.CountIf prüfen ob das Item vorhanden ist.


Mit freundlichen Grüssen
Thomas Ramel

[Thomas Ramel]

Grüezi Frank nochmals

Thomas Ramel schrieb am 18.02.2007

Post by Thomas Ramel
Frank Vellner schrieb am 18.02.2007

Post by Frank Vellner
beim Aufzeichnen mit dem Makro-Recorder sehe ich exakt das selbe, egal
ob ich ein Zeilenfeld oder ein Seitenfeld bearbeite.

Ja, das ist demanch wieder klar und so zu erwarten.

Post by Frank Vellner
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Keyword")
.PivotItems("Keyword1").Visible = False
.PivotItems("Keyword2").Visible = False
.PivotItems("Keyword3").Visible = False
Mit der Schleife bekomme ich es nicht sinnig hin. Ich versuche, dem
Makro zu sagen: Nimm alle unterhalb der aktuellen Curserposition
befindlichen Zellen (=zusammenhängender Bereich ohne Leerzeichen etc.)
und nutze deren Inhalt, um das identische Element im Pivot-Feld mit
.Visible = False zu versehen.

Ich würds andersrum machen - alle .PivotItems durchlaufen und mit z.B.
.CountIf im betreffenden Bereich suchen lassen. Wenn das Item dort vorhaden
ist, dann ausblenden und zum nächsten gehen.
Dabei darauf achten, dass immer mindestens *ein* Pivot-Item eingeblendet
sein muss.
Wenn Du dem Bereich mit den auszublendenden Items einen dynamischen
Bereichsnamen 'verpasst' ist es auch egal wo diese stehen.
Ansernfalls kannst Du den Code gut auch auf .ActiveCell anpassen und den
unterhalb liegenden Bereich im Code selber einmal erfassen und damn mit
.CountIf prüfen ob das Item vorhanden ist.

Sodele, hier nun mal ein Code, der dein Seitenfeld entsprechend der Stelle
der aktiven Zelle 'bearbeitet' (für Robert habe ich hier auch meine
Signatur angepast ;-)):

Sub Set_Seitenfeld()
Dim rngPItems As Range
Dim pItem As PivotItem
Set rngPItems = Range(ActiveCell, ActiveCell.End(xlDown))

For Each pItem In ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Keyword").PivotItems
If Application.WorksheetFunction.CountIf(rngPItems, pItem) > 0 Then
pItem.Visible = False
Else
pItem.Visible = True
End If
Next pItem
End Sub


'Mit freundlichen Grüssen _
Thomas Ramel

[Frank Vellner]

Moin Thomas,

Post by Thomas Ramel
Sub Set_Seitenfeld()
Dim rngPItems As Range
Dim pItem As PivotItem
Set rngPItems = Range(ActiveCell, ActiveCell.End(xlDown))
For Each pItem In ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Keyword").PivotItems
If Application.WorksheetFunction.CountIf(rngPItems, pItem) > 0
Then pItem.Visible = False
Else
pItem.Visible = True
End If
Next pItem
End Sub

ja, supi, vielen Dank. Sollte - entgegen dem Namen - eigentlich für
alle Pivot-Felder-Typen funktionieren. Das werde ich gleich mal testen.

Es ist immer wieder Klasse, wie du - und die andern VBA-Profis - ein
Problem mit 13 Codezeilen (eigentlich nur 7, ohne Namen, Dims und
Zeilenumbruch) löst, wo ich mit Aufzeichnungs-Code-Bearbeitung auf 30
Zeilen komme, die sehr langsam und nicht wirklich flexibel sind...

Ein grundsätzliches Problem, was sich auch auf das Makro auswirken
wird, habe ich mit der Sache noch: Ich begreife nicht, wie und wann
Excel die Items in Pivot-Feldern aktualisiert. Wenn ich beispielsweise
den "Bezugsbereich einer PT" ändere, habe ich trotzdem in den Feldern
die gleichen Items (obwohl vielen eigentlich nicht mehr vorhanden sein
sollten). Dann probiere ich: Feld aus und einblenden, PT aktualisieren,
dein PivotCacheLöschen(), Excel schließen und wieder öffnen - hilft
alles nix. Nur ganze PT löschen und wieder neu machen klappt natürlich.

Manchmal sehe ich plötzlich, dass sich zumindest die Reihenfolge der
Items angepasst hat (wenn auch die Anzahl gleich geblieben ist), aber
selbst das kann ich noch nicht reproduzieren ;-(

Viele Grüße
Frank

[Thomas Ramel]

Grüezi Frank

Frank Vellner schrieb am 18.02.2007

Post by Frank Vellner
ja, supi, vielen Dank. Sollte - entgegen dem Namen - eigentlich für
alle Pivot-Felder-Typen funktionieren. Das werde ich gleich mal testen.

Ja, das stimmt - Namen sind ja eh nur Schall und Rauch; wichtig ist der
Inhalt.

Post by Frank Vellner
Es ist immer wieder Klasse, wie du - und die andern VBA-Profis - ein
Problem mit 13 Codezeilen (eigentlich nur 7, ohne Namen, Dims und
Zeilenumbruch) löst, wo ich mit Aufzeichnungs-Code-Bearbeitung auf 30
Zeilen komme, die sehr langsam und nicht wirklich flexibel sind...

Danke für die Blumen :-)

Post by Frank Vellner
Ein grundsätzliches Problem, was sich auch auf das Makro auswirken
wird, habe ich mit der Sache noch: Ich begreife nicht, wie und wann
Excel die Items in Pivot-Feldern aktualisiert. Wenn ich beispielsweise
den "Bezugsbereich einer PT" ändere, habe ich trotzdem in den Feldern
die gleichen Items (obwohl vielen eigentlich nicht mehr vorhanden sein
sollten). Dann probiere ich: Feld aus und einblenden, PT aktualisieren,
dein PivotCacheLöschen(), Excel schließen und wieder öffnen - hilft
alles nix. Nur ganze PT löschen und wieder neu machen klappt natürlich.

Auch da gibt es 'Abhilfe :-)
Die alten Pivot-Items werden, wenn sie aus der Datenquelle verschwunden
sind leider nicht automatisch aus der Liste entfernt.
Doch die folgende Prozedur macht das für alle Pivot-Tabellen der aktiven
Mappe:

Sub DeleteOldPivotItemsWB()
'löschen von nicht mehr verwendeten Einträgen in Pivot-Tabellen
'basierend auf MSKB (Q202232)
Dim wS As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
For Each wS In ActiveWorkbook.Worksheets
For Each pt In wS.PivotTables

pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
'Oder als Alternative ab xl2002
' For Each wS In ActiveWorkbook.Worksheets
' For Each pt In wS.PivotTables
' pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
' Next pt
' Next wS

End Sub





Mit freundlichen Grüssen
Thomas Ramel

[Frank Vellner]

Moin Thomas,

Post by Thomas Ramel
Auch da gibt es 'Abhilfe :-)
Die alten Pivot-Items werden, wenn sie aus der Datenquelle
verschwunden sind leider nicht automatisch aus der Liste entfernt.
Doch die folgende Prozedur macht das für alle Pivot-Tabellen der
Sub DeleteOldPivotItemsWB()

ach stimmt, hatte ich doch schon mal irgendwo gesehen, aber da wusste
ich noch nicht, dass es das Problem überhaupt gibt...

Übrigens, bei mir funktioniert unter XL2003 nur der vordere Code-Teil -
die "Alternative" macht nix. Das war nur zur Info an dich - für mich
ist nur wichtig, dass es funktioniert ;-)

Vielen Dank
Frank

[Thomas Ramel]

Grüezi Frank

Frank Vellner schrieb am 19.02.2007

Post by Frank Vellner
Moin Thomas,

Post by Thomas Ramel
Auch da gibt es 'Abhilfe :-)
Die alten Pivot-Items werden, wenn sie aus der Datenquelle
verschwunden sind leider nicht automatisch aus der Liste entfernt.
Doch die folgende Prozedur macht das für alle Pivot-Tabellen der
Sub DeleteOldPivotItemsWB()

ach stimmt, hatte ich doch schon mal irgendwo gesehen, aber da wusste
ich noch nicht, dass es das Problem überhaupt gibt...

Ist doch schön, dass Probleme gelöst sind noch bevor man deren Existenz
kennt ;-)

Post by Frank Vellner
Übrigens, bei mir funktioniert unter XL2003 nur der vordere Code-Teil -
die "Alternative" macht nix. Das war nur zur Info an dich - für mich
ist nur wichtig, dass es funktioniert ;-)

Ja, ich hab den zweiten Teil so auch nicht zum rennen gebracht. Ich habe
die Zeilen aus einem NG-Beitrag und zur Vervollständigung einfach mal
reinkopiert - es gibt also da noch Aktionsbedarf für mich.



Mit freundlichen Grüssen
Thomas Ramel (@work)

[Frank Vellner]

Moin Thomas,

Post by Thomas Ramel

Post by Frank Vellner
geht aber per schlichten Anklicken nach Doppelklick auf ein
Seitenfeld ebenso.

...das hier habe ich soeben dazugelernt :-)

wow, das gibt ein Häkchen im Kalender, am 18.2.2007 habe ich dem
Pivot-Papst etwas gesagt, was er nicht wusste!

Post by Thomas Ramel

Post by Frank Vellner
Dabei ist mir nicht klar, wie ich verifizieren kann, was für Excel
die PivotTable1 ist.

Diesen Namen kannst Du in den Optionen der Pivot-Tabelle ganz oben
festlegen.

Stimmt, hätte ich auch selber drauf kommen können...

Post by Thomas Ramel
Ich würds andersrum machen - alle .PivotItems durchlaufen und mit
z.B. .CountIf im betreffenden Bereich suchen lassen. Wenn das Item
dort vorhaden ist, dann ausblenden und zum nächsten gehen.

ja, so rum ist es schlauer

Post by Thomas Ramel
Dabei darauf achten, dass immer mindestens *ein* Pivot-Item
eingeblendet sein muss.
Wenn Du dem Bereich mit den auszublendenden Items einen dynamischen
Bereichsnamen 'verpasst'

dynamische Bereichsnamen sind die, die sich über Formeln wie
ANZAHL(A:A) auf die gefüllten Zellen beziehen? (das was du für deine
Beispiel-Datei für dynamische Diagramme verwendet hast)

Post by Thomas Ramel
ist es auch egal wo diese stehen.
Ansernfalls kannst Du den Code gut auch auf .ActiveCell anpassen und
den unterhalb liegenden Bereich im Code selber einmal erfassen und
damn mit .CountIf prüfen ob das Item vorhanden ist.

ok, klingt logisch.

Vielen Dank
Frank

[Thomas Ramel]

Grüezi Frank

Frank Vellner schrieb am 18.02.2007

Post by Frank Vellner

Post by Thomas Ramel

Post by Frank Vellner
geht aber per schlichten Anklicken nach Doppelklick auf ein
Seitenfeld ebenso.

...das hier habe ich soeben dazugelernt :-)

wow, das gibt ein Häkchen im Kalender, am 18.2.2007 habe ich dem
Pivot-Papst etwas gesagt, was er nicht wusste!

Wir sind doch hier im Club der immer Lernenden - ich gehöre da genauso dazu
wie alle anderen auch :-)

Post by Frank Vellner

Post by Thomas Ramel
Wenn Du dem Bereich mit den auszublendenden Items einen dynamischen
Bereichsnamen 'verpasst'

dynamische Bereichsnamen sind die, die sich über Formeln wie
ANZAHL(A:A) auf die gefüllten Zellen beziehen? (das was du für deine
Beispiel-Datei für dynamische Diagramme verwendet hast)

Exakt - ich dachte da an dieselbe Technik


Mit freundlichen Grüssen
Thomas Ramel