Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 23:30:16
Anzahl Jahre Zählen aus Datumreihe
16.04.2024 19:22:16
Von exceltabelle in word per steuerelement temarker
16.04.2024 18:50:53
Fehlermeldung bei Code - Excel speichern mit Bez. aus Zelle
16.04.2024 17:18:01
Projektplan-Tabelle oder Gantt ohne Balken
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1880to1884
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1880to1884
1880to1884
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Autofilter über Pivot, mit VBA steuern

Autofilter über Pivot, mit VBA steuern
13.05.2022 10:59:14
Andrea
Hallo an die Experten,
ich möchte über eine Pivot-Tabelle einen Autofilter legen und diesen per Makro steuern. Konkret möchte ich, dass alle Zeilen herausgefiltert werden, die in der Summenspalte den Wert 0,00 haben.
Den Autofilter entsprechend per Makro zu steuern, ist mir in einer reinen Excel-Tabelle (s. Beispieldatei, Tabelle "Daten") mit folgendem Makro gelungen:

Sub Daten_ohne_Nullsummen()
ActiveSheet.Range("$A$1:$D$13").AutoFilter Field:=4, Criteria1:="0"
End Sub
Nun habe ich auf der Basis dieser reinen Excel-Tabelle in der Tabelle "Pivot" (s. Beispieldatei) eine Pivot-Tabelle erstellt und dort einen Autofilter eingefügt.
Bediene ich den Autofilter manuell, kann ich ebenso wie in der reinen Excel-Tabelle alle Nullwerte aus der Summenspalte ausblenden.
Wende ich dagegen das Makro an (Pivot_ohne_Nullsummen), kommt eine Fehlermeldung!? Warum?
https://www.herber.de/bbs/user/153047.xlsm
Viele Grüße
Andrea

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Autofilter über Pivot, mit VBA steuern
13.05.2022 11:32:33
Yal
Hallo Andrea,
es leigt daran, dass eine Pivottabelle keine standardtabelle ist.
Du musst den Makrorekoder anstossen, in der PT Januar rechtsklicken, "Filter" > "Werte.." dann "Summe von A" - "entspricht nicht" - "0"
Makrorekorder stoppen.
Du hast dann einen Code der so aussieht:

Sub Makro1()
ActiveSheet.PivotTables("PivotTable2").PivotFields("Monat").PivotFilters.Add2 _
Type:=xlValueDoesNotEqual, _
DataField:=ActiveSheet.PivotTables("PivotTable2").PivotFields("Summe von Wert A"), _
Value1:=0
End Sub
Also ganz anders als der Standardfilter.
Achtung: Du hast den Sonderfall, dass überall wo "Summe von A" null ist, "Summe von B" auch null ist. Setzt Du aber den A von April von 58 auf 0, dann verschwindet den ganzen April, auch wenn B und C nicht null sind.
Idealerweise setzst Du den Filter auf "Summe von Summe" entspricht nicht 0 (Da Du keine negative Werte hast).
VG
Yal
Anzeige
AW: Autofilter über Pivot, mit VBA steuern
13.05.2022 12:02:16
Andrea
Hallo Yal,
danke.
Die speziellen Pivot-Filter kenne ich natürlich und sie funktionieren in der Tabelle die ich mitgeschickt hatte auch. Leider komme ich bei der Pivot-Tabelle, die mir von der Firmensoftware zur Verfügung gestellt wird so nicht zum Ziel :-(
Ich hatte nicht die Originaldatei mitgeschickt, aus Datenschutzgründen und weil ihr sie ja dann auch nicht aktualisieren könnt. Um das Problem zu verdeutlichen habe ich jetzt mal die Daten etwas verdichtet, so dass man die Einzelwerte nicht mehr sieht, sondern nur noch je Standort, was auch dazu führt, dass ich gar keine Nullzeilen mehr habe. Vom Prinzip kann man aber an dieser schön sehen, dass man die Spalte Gesamtergebnis leider über die Pivot-Filter überhaupt nicht erreicht :-(
Hier die Firmen-Pivot: https://www.herber.de/bbs/user/153050.xlsx
Deshalb war ich ja auf die Idee mit dem drübergelegten Autofilter gekommen, der übrigens auch bei der Firmen-Pivot funktioniert, WENN man ihn manuell bedient.
Gibt es nicht noch eine andere Möglichkeit den VBA-Code anzupassen, damit er mit dem Autofilter genau das tut, was er auch bei manueller Anwendung macht?
Danke und Gruß Andrea
Anzeige
AW: Autofilter über Pivot, mit VBA steuern
13.05.2022 12:49:11
Yal
Hmm... es kommt aus einem Microsoft Analysis Services Cube. Da musst Du dich an den Spezialisten wenden. Es gäbe sicher die Möglichkeit, die Abfrage zu beschränken.
Eventuell kannst Du anstatt direkt auf dem Cube über Power Query Abfrage filtern.
Als letzte Lösung: lege eine Formel ausserhalb der Pivot, die die Werte der Spalte Gesamtergebnis übernimmt und filtere diese Spalte. Diese Spalte könnte man aus ausblenden.
VG
Yal
AW: Autofilter über Pivot, mit VBA steuern
13.05.2022 17:41:27
Andrea
Hallo Yal,
danke für den Tipp mit der Spalte außerhalb der Pivot! Damit hab ich mir jetzt was gebastelt.
VG Andrea
AW: Autofilter über Pivot, mit VBA steuern
13.05.2022 15:14:09
ChrisL
Hi Andrea
https://www.herber.de/bbs/user/153055.xlsx
Variante 1 mit Measures
N.b. Es reicht nicht, die FILTER-Funktion nur auf dem Total anzuwenden, weil die Null-Werte in Spalte A und B sind trotzdem noch da. Es müssen also sämtliche Ergebnis-Werte "vernichtet" werden.

SummeA =SUM(Tabelle1[Wert A])
SummeB =SUM(Tabelle1[Wert B])
SummeTotal =Tabelle1[SummeA]+Tabelle1[SummeB]
FilterA =CALCULATE(Tabelle1[SummeA];FILTER(Tabelle1;Tabelle1[SummeA]0))
FilterB =CALCULATE(Tabelle1[SummeB];FILTER(Tabelle1;Tabelle1[SummeB]0))
FilterTotal =CALCULATE(Tabelle1[SummeTotal];FILTER(Tabelle1;Tabelle1[SummeTotal]0))
Variante 2 mit berechnetem Feld
Direkt im Power-Pivot Datenmodell ein neues berechnetes Feld einfügen. Das Feld wird dann wie eine "normale Spalte" behandelt d.h. der normale Pivot-Filter kann angewendet werden.

=Tabelle1[Wert A]+Tabelle1[Wert B]
cu
Chris
Anzeige
AW: Autofilter über Pivot, mit VBA steuern
13.05.2022 17:53:27
Andrea
Hallo Chris,
vielen Dank für deine Mühe. Das hört sich sehr interessant an, ich fürchte allerdings, ich habe zu wenig Kenntnisse, um in Gänze nachzuvollziehen, was du vorschlägst :-(
Wahrscheinlich kommen die Lösungen für mich aber nicht in Frage, da ich die mir von der Firmensoftware zur Verfügung gestellte Pivot (s. Beispieldatei in meiner ersten Antwort an Yal) wahrscheinlich gar nicht entsprechend anpassen kann. Aber ich wüsste auch gar nicht wo und wie ich das anstellen sollte.
Viele Grüße Andrea
AW: Autofilter über Pivot, mit VBA steuern
13.05.2022 19:35:16
ChrisL
Hi Andrea
Passt schon. Nur für den Fall, dass du doch noch tiefer abtauchen möchtest.
1. Power-Pivot aktivieren
https://support.microsoft.com/de-de/office/starten-des-power-pivot-add-ins-f%C3%BCr-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8
2. Menü Power-Pivot, Bereich/Quelldaten dem Datenmodell hinzufügen
Aufgrund der Cube-Geschichte würde ich annehmen, dass die Daten bereits im Modell sind. Dann kann man diesen Schritt überspringen.
a) Im gleichen Menü steht die Auswahl "Measure" (eine Art Formel-Editor) zur Verfügung
b) Oder du gehst auf im gleichen Menü auf "Verwalten", Power-Pivot-Editor öffnet und "hinzufügen neue Spalte", wo du in der Befehlszeile die Formel für das berechnete Feld eingeben/bestätigen kannst.
cu
Chris
Anzeige
AW: Autofilter über Pivot, mit VBA steuern
16.05.2022 18:16:15
Andrea
Hallo Chris,
das ist sehr interessant und in deiner selbst erstellten Pivot aus den in Excel bereit gestellten Ursprungsdaten, kann ich das auch nachvollziehen.
Offensichtlich ist es in unserer Firma aber irgendwie anders!?
Wenn ich hier eine Pivot auf der Grundlage der Firmendatenbank erstelle kommt beim Befehl „Zu Datenmodell hinzufügen“ folgende Fehlermeldung:

Der ausgewählte Bereich ist ungültig. Wählen Sie einen gültigen Bereich aus. Eine Tabelle kann einen Bereich, der einen PivotTable-Bericht, ein Abfrageergebnis, eine andere Tabelle oder geschützte Zellen enthält, nicht überlappen.
Beim Befehl „Neues Measure …“ kommt:

Sie verfügen über kein Modell, bzw. das Modell ist leer. Measures können nicht erstellt oder verwaltet werden.
Gehe ich in einer leeren Tabelle auf Pivot -> Verwalten -> Externe Daten abrufen und gebe dort unserer Datenbank an, kommt:

MDX-Abfrage angeben!
Und da hört es dann leider bei mir auf :-(
Ich befürchte, unser Softwarehersteller hat die Möglichkeiten der Pivot-Nutzung irgendwie beschränkt. Kann das sein?
Viele Grüße Andrea
Anzeige
AW: Autofilter über Pivot, mit VBA steuern
16.05.2022 19:25:23
ChrisL
Hi Andrea
Da bin ich jetzt ehrlich gesagt auch überfragt. MDX lese ich zum ersten mal, gibt aber einen Hinweis auf die verwendete Technik:
https://support.microsoft.com/de-de/office/mdx-abfrage-designer-f%C3%BCr-analysis-services-power-pivot-30ab91f9-82a6-4f95-a5ec-2b6b7ab5cbcf
Soweit ich das auf die Schnelle verstehe, greift MDX direkt auf das externe Datenmodell zu und importiert die bereits bestehenden Measures. Das Datenmodell existiert also bereits und wird nicht in Excel verwaltet. Anders ausgedrückt, man müsste in die bestehende MDX-Query eingreifen. Selbst wenn dies vielleicht möglich/erlaubt wäre, würde ich aufgrund der Komplexität (erfordert Verständnis des Datenmodells) eher nicht in den MDX-Abfrage-Designer eingreifen und die Syntax der Abfragesprache unterscheidet sich zu dem von mir aufgezeigten. Ich würde mich also eher an den Softwarehersteller bzw. den Verwalter der Daten-Cube wenden, damit dieser neue gefilterte Measure zur Verfügung stellt. Damit wäre dann auch die Wartung/Updates gewährleistet.
Eine interessante neue (Daten-)welt, aber ich denke das sollte man dann doch besser den Profis überlassen. :-)
cu
Chris
Anzeige
AW: Autofilter über Pivot, mit VBA steuern
17.05.2022 10:14:11
Andrea
Hallo Chris,
vielen Dank für deine zahlreichen Hinweise.
Mich an den Hersteller wenden, tue ich laufend. Nur meistens mit wenig Erfolg :-(
Deshalb suche ich ja permanent nach Wegen, die auszutricksen!
Viele Grüße
Andrea

308 Forumthreads zu ähnlichen Themen

VBA- Autofilter - leere Zeilen löschen
Asits

Hallo,
ich habe in der Spalte "A" Kundennummern stehen.
Ich will per Makro / Autofilter die kompletten Zeilen
derjehnigen Kunden löschen, die noch keine Kundennummer haben.
wie mache ich das ?
Vielen Dank vorab!
17.09.2008 08:48:00
Thread-ID: 1010225
5 Beiträge
Anzeige
Autofilter mit VBA
Steffan

Hallo ihr Lieben,
ich hab ein Problem mit einer Filter Routine. In einer Mitgliederliste soll Excel beim Aufruf der Datei überprüfen, ob Mitglieder ihr Austrittsdatum erreicht haben. In Spalte I stehen diese Daten im Format 31.12.2008.
Aus dem Archiv hab ich mir folgenden Code herausges...
12.01.2009 19:15:39
Thread-ID: 1038988
4 Beiträge
Autofilter per VBA aufrufen
Armin

Hallo,
ich habe folgendes Problem mit VBA.
Ich habe in einer Liste den Autofilter eingebunden. Per VBA selektiere ich Daten nach Datumswerten mit folgendem Code:
Zunächst lese ich das in Zelle A1 vorhandene Datum "01.01.2009" aus und lege dieses in der Variable "datVon" ab. Diese Va...
21.01.2009 15:45:00
Thread-ID: 1042249
4 Beiträge
Anzeige
Problem mit Autofilter über VBA
Antsche


Wunderschönen guten Tag,
ich habe ein Problem bei der Nutzung des Autofilters über VBA.
In meinem Excel-File gibt es Tabellenblätter in denen Datensätze hinterlegt sind. Diese sollen über Eingaben in eine UserForm (ComboBoxen) gefiltert werden. Hier die Datei dazu:
https://www...
18.06.2009 16:53:36
Thread-ID: 1082687
4 Beiträge
VBA: Autofilter auf "0" setzen
David

Hallo zusammen.
Wenn in der Datei ein Autofilter gesetzt ist und auch in einem beliebigen Feld ein Kriterium gesetzt ist, wie bekomme ich per VBA die Anzeige ALLER Elemente hin.
Mein Versuch mit Range(XYZ).Autofilter
löscht diesen komplett, was aber nciht gewollt ist. Und .Autofi...
27.08.2009 09:53:08
Thread-ID: 1098941
3 Beiträge
Autofilter Doppelnennungen filtern über VBA
Emil

Hallo zusammen,
ich habe folgendes Problem innerhalb eines größeren Makros:
Ich habe eine Tabelle aus der ich mittels eines Makros Werte in ein Diagramm übertrage.
Hierzu werden schon Spalten über den Autofilter nach bestimmten Auswahlkriterien gefiltert, das funktioniert alles wund...
03.09.2009 17:33:39
Thread-ID: 1100285
2 Beiträge
Blattschutz - Zugriff auf Autofilter -VBA-
Wolfgang

Hallo,
mit nachstehendem Code wird in allen Tabellenblättern der Blattschutz eingeschaltet. Nun mußte ich feststellen, dass AutoFilter sich nicht bedienen lässt, so dass ich hierzu die nachstehende Ergänzung eingebaut habe. Allerdings ohne Erfolg, es tut sich nichts.- Wie müßte ich den Cod...
26.10.2009 19:52:57
Thread-ID: 1112334
5 Beiträge
Ohne VBA: Wenn Autofilter, dann anderen Zellwert
sunni

Hallo alle zusammen,
gibt es eine Möglichkeit über Excel (ohne VBA) zu sehen, ob ein Autofilter gesetzt ist?
Hintergrund: ich habe eine Formel bei gesetztem Autofilter. Wenn aber kein Autofilter gesetzt ist, soll hier in der Zelle eine 0 oder "" erscheinen.
Geht das?
Vielen Dank...
08.01.2010 12:35:47
Thread-ID: 1128216
8 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
2
3
3
4
3
2
1
4
7
1
2
8
8
3
9
11
4
2
4
4
5
3
2
4