Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1700to1704
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
Inhaltsverzeichnis

Pivot-Tabelle mit Filter "bis Heute"

Pivot-Tabelle mit Filter "bis Heute"
10.07.2019 18:06:43
Rainer
Hallo Excel-Freunde (und ganz speziell Freunde der Pivot-Tabellen),
https://www.herber.de/bbs/user/130822.xlsx
In der Tabelle wird aus der "Liste" eine Pivot-Auswertung vorgenommen. Ziel ist es, die Einträge mit Status "OFFEN", "in Arbeit" oder "Wartend" zu erfassen, aber nach 3 verschiedenen Zeiträumen: "Bis Heute", "Morgen bis Heute + 7 Tage", "Morgen bis Heute + 28 Tage".
Mit Zählenwenns geht dies, ich habe es im Blatt "Übersicht" dargestellt wie es aussehen soll. Da gibt es im Original noch ein Makro, welches die Namen aktualisiert.
Wie kann ich nun eine vergleichbare Pivot-Tabelle erzeugen, also mit Filter "Datum von bis", aber ohne Darstellung der einzelnen Tage? Es wird nur das "Gesamtergebnis" gesucht für die beiden Filter "Datum" und "Status".
Gruß,
Rainer
(Level Excel gut, aber Pivot bescheiden...)

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nur mit einer Hilfsspalte ...
10.07.2019 19:21:10
neopa
Hallo Rainer,
... in Liste!E2 würde ich z.B. folgende Formel einsetzen:

=WENN(ISTZAHL(VERGLEICH(B2;{"offen";"wartet";"in Arbeit"};0));WENN(D2&lt=HEUTE();"Bis Heute";
WENN(D2&lt=HEUTE()+7;"nächsten 7 Tage";WENN(D2&lt=HEUTE()+28;"nächsten 28 Tage";"später")));"")

und diese nach unten kopieren und in E1 z.B "Abschluß" als Überschrift schreiben.
Die Datenquelle er Pivotauswertung entsprechend um "Abschluß" erweitern und in die Spaltenbeschriftung würde ich das "Zieldatum" entfernen (bei Bedarf in Berichtsfilter schieben) und "Abschluß" vor "Status" anordnen.
Nun kannst Du z.B. auch nach "Bis Heute" filtern.
Gruß Werner
.. , - ...
Anzeige
AW: nur mit einer Hilfsspalte ...
10.07.2019 22:07:44
Rainer
Hallo Werner,
danke für deine Hilfe.
Ich habe eigentlich die Option "Erweitern/Reduzieren - Ganzes Feld reduzieren" gesucht. Wie gesagt, Pivot bescheiden...
So sieht der VBA Code aus:
    .PivotFields("Status").ShowDetail = False
Dann habe ich mich noch ein wenig mit den Updates der Pivot-Tabelle beschäftigt. Das ist auch sehr gruselig.
Es braucht eine Änderung am Datenrange, setzen der Filter (weil HEUTE() nicht möglich ist muss man das Datum jeden Tag neu setzen), Ausblenden der Tage (die ploppen zuweilen wieder auf?) und sortieren der Namensliste, falls neue eingeben werden.
Bei 4 Pivot-Tabellen (Alle, bis heute, Nächste Woche, Nächster Monat) dauert das schon über 10 Sekunden.
Da ist die verschachtelte "ZÄHLENWENNS" deutlich schneller!

With Sheets("Pivot").PivotTables("PivotBisHeute")
.ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=Datenquelle, _
Version:=6)
.PivotFields("Zieldatum").ClearAllFilters
.PivotFields("Monate").ClearAllFilters
.PivotFields("Zieldatum").PivotFilters. _
Add2 Type:=xlDateBetween, _
Value1:="1.1.2000", _
Value2:=Format(Date - 1, "DD.MM.YYYY")
.PivotFields("Status").ShowDetail = False
.PivotFields("Verantwortlich").AutoSort _
xlAscending, "Verantwortlich"
End With

Anzeige
AW: kann ich so nicht nachvollziehen ...
11.07.2019 14:28:30
neopa
Hallo Rainer,
... mit VBA beschäftige ich mich prinzipiell nicht aber eine Pivotauswertung ist bei Massendatenauswertung immer schneller als eine Formelauswertung.
Warum erstellst Du eigentlich 4 Pivotauswertungen. Eine ist doch ausreichend, die lediglich nur entsprechend gefiltert werden muss/kann.
Gruß Werner
.. , - ...
AW: kann ich so nicht nachvollziehen ...
11.07.2019 16:09:50
Rainer
Hallo Werner,
Ziel der Übung ist, eben keine Nutzereingaben machen zu müssen und sofort beim Aufruf des Blattes "Pivot" die aktuellen Werte zu sehen.
Das klappt aber eben nicht (oder ich bediene es falsch) mit dem Klick auf "Aktualisieren".
Es fehlt dann:
1. Der Datenbereich wächst und muss überprüft werden
2. Neue Namen werden immer am Ende dargestellt und nicht automatisch alphabetisch sortiert
3. Der Datumsfilter muss jeden Tag neu gesetzt werden, weil keine Heute()-Formel benutzt werden kann
Die 4 Tabellen bestehen auch aus diesem Grund, es muss nichts mehr geklickt werden um andere Informationen zu sehen. Aber um es zu beschleunigen habe ich es so realisiert, dass nun 4 Buttons 1 Pivottabelle nutzen und jeweils den Datumsfilter ändern können.

Sub alle()
Start = "01.01.2000"
Ende = "01.01.3000"
Pivotupdate Start, Ende
End Sub
Sub Bisheute()
Start = "01.01.2000"
Ende = Format(Date - 1, "DD.MM.YYYY")
Pivotupdate Start, Ende
End Sub
Sub NaechsteWoche()
Start = Format(Date, "DD.MM.YYYY")
Ende = Format(Date + 7, "DD.MM.YYYY")
Pivotupdate Start, Ende
End Sub
Sub NaechsterMonat()
Start = Format(Date, "DD.MM.YYYY")
Ende = Format(Date + 28, "DD.MM.YYYY")
Pivotupdate Start, Ende
End Sub
Sub Pivotupdate(Start, Ende)
Application.EnableEvents = False
ThisWorkbook.RefreshAll
Application.EnableEvents = True
LastRow = WorksheetFunction.Max(Sheets("Liste").Range("A:A")) + 1
Datenquelle = "Liste!" & Range("$A$1:$D$" & LastRow).Address(ReferenceStyle:=xlR1C1)
With Sheets("Pivot").PivotTables("PivotTabelle1")
.ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=Datenquelle, _
Version:=6)
.PivotFields("Zieldatum").ClearAllFilters
.PivotFields("Monate").ClearAllFilters
.PivotFields("Zieldatum").PivotFilters. _
Add2 Type:=xlDateBetween, _
Value1:=Start, _
Value2:=Ende
.PivotFields("Status").ShowDetail = False
.PivotFields("Verantwortlich").AutoSort _
xlAscending, "Verantwortlich"
End With
End Sub
Gruß, Rainer
Anzeige
AW: dazu kann ich nur wieder feststellen ...
11.07.2019 17:14:20
neopa
Hallo Rainer,
... dass ich es so nicht nachvollziehen kann. Stell doch mal Deine aktuelle Datei ein.
Und wenn es ein VBA-Problem sein sollte, stellen wir danach den thread für VBA kundige offen.
Gruß Werner
.. , - ...
AW: dazu kann ich nur wieder feststellen ...
11.07.2019 18:36:10
Rainer
Hallo Werner,
immer noch diese Datei: https://www.herber.de/bbs/user/130822.xlsx
Schreibe einfach eine neue Zeile darunter in der Liste mit deinem Namen und Status "Offen".
In der Pivot-Tabelle erscheint er erst nach Bereichsanpassung.
Gruß, Rainer
AW: warum dies? ...
11.07.2019 19:33:59
neopa
Hallo Rainer,
... ich hatte Dir eine andere Pivotauswertung vorgeschlagen. Hast Du es mit dieser getestet?
Gruß Werner
.. , - ...
Anzeige
AW: warum dies? ...
11.07.2019 20:09:23
Rainer
Hallo Werner,
ja, habe ich.
https://www.herber.de/bbs/user/130848.xlsx
Hat aber auch das Problem mit dem Datenbereich. Hier in diesem Beispiel findet die Pivot-Tabelle nicht den Eintrag "Nr 34, OFFEN, Werner". Nach händischer Anpassung des Bereiches taucht der Werner aber am Ende auf und nicht da, wo er sortiert hingehört? Man muss nochmal von Hand sortieren.
Gruß, Rainer
AW: dazu folgende Feststellungen und Hinweise ...
12.07.2019 13:45:18
neopa
Hallo Rainer,
... die einige Deiner Probleme teilweise erklären könnten:
1.) Deine Datei hat mE einen "Knacks" weg, so dass ich in dieser aus Deinen Daten in Liste keine "intelligente" Tabelle formatieren lässt. Eine Ursache dafür könnte evtl. sein, dass Du in dieser Datei mal eine PIVOTauswertung gelöscht hast. Jedenfalls konnte ich so die bezeichnete Fehlfunktion nachstellen.
2.) Es gibt in Deinem Namensmanager jede Menge Definitionen, die eine Fehlermeldung aufweisen, was eine enorme Bremse sein könnte. Solche solltest Du löschen.
Der Sinn der verbleibenden definierten Namen erschließt sich mir nicht. Wenn Du (in einer neuen Datei) die Listen-Datenwerte übernimmst und aus diesen eine "intelligente" Tabelle formatierst, sind diese Namensdefinitionen zumindest für die Pivotauswertung auch nicht notwendig.
3.) Bedingte Formatierungen sollte man keinesfalls wie bei Dir über den gesamten Zeilenbereich (also z.B. D:D) definieren. sondern nur in dem vorhandenen notwendigen Datenbereich, denn derartige bed. Formatierungen sind eine Performancebremse.
Bedingte Formatierungen in einer "intelligenten" Tabelle erweitern sich analog der der Formel in "Abschluß" bei einer Datenerweiterung automatisch.
4.) Wenn Du für die Pivotauswertung als Datenbereich die "intelligente" Tabelle nutzt, passt sich diese auch automatisch bei Datenerweiterung in der Datenliste dieser an (natürlich erst nach Betätigung des Aktualisierungsbuttons).
Eine automatische alphabetische Einsortierung wird allerdings nicht vorgenommen. Dazu bedarf es wirklich eines weiteren Mausklick.
Diese Sortierung könntest Du alternativ zu VBA auch mit Aufbereitung der Daten und Pivotauswertung auch mit Powerquery vornehmen (welches für Dich natürlich auch erst einmal einiges an notwendigen Lernaufwand nachziehen würde).
Gruß Werner
.. , - ...
Anzeige
AW: dazu folgende Feststellungen und Hinweise ...
12.07.2019 15:27:34
Rainer
Hallo Werner,
danke für deine Hilfe.
Die "Sauerei" im Namensmanager kam vom Import. Jedesmal vor dem Speichern importiert er die Datei und prüft, ob in der Zwischenzeit noch jemand in der Datei geschrieben hat. Da war mir garnicht aufgefallen, dass ZUSÄTZLICH zur Datenverbindung auch ein Feld im Namensmanager angelegt wird. Die Datenverbindung muss ich jedesmal wieder löschen, aber den Teil im Namensmanager hatte ich übersehen.
Die intelligente Tabelle konnte ich ohne Probleme erstellen, nachdem ich die Filter entfernt habe.
Viele Grüße, Rainer
AW: dazu folgende Feststellungen und Hinweise ...
12.07.2019 18:41:25
Rainer
Hallo Werner,
ein "Problem" ergibt sich noch bei den bedingten Formatierungen. Die sind stur Spaltenabhängig, aber die Pivot-Tabelle ist da eher flexibel bei mehreren Kriterien für die Spalten.
Ich vermute, es gibt da aber keine "intelligenten Pivot-Tabellen", wo man die Zuordnung an den Spaltennamen hängt?
https://www.herber.de/bbs/user/130874.xlsm
Wenn man in der Datei die Spalten erweitert, dann ist die bedingte Formatierung hinüber. Habe zum Test nur eine Bedingung eingefügt.
Gruß, Rainer
Anzeige
AW: bedingte Formatierung in Pivotauswertung ...
13.07.2019 08:47:31
neopa
Hallo Rainer,
... muss man natürlich durch entsprechende Formeln selbst dynamisch halten.
Also für Dein Beispiel steht ja "Gesamtergebnis" für "Abschluß" in Zeile 4, nur die Spalte könnte eine andere sein und die Auswertung ab Zeile 6 beginnen.
Dann folgende bed. Formatierungsformel: =INDEX(6:6;VERGLEICH("Gesamt*";$4:$4;0))=0
Gruß Werner
.. , - ...
AW: bedingte Formatierung in Pivotauswertung ...
13.07.2019 15:59:37
Rainer
Hallo Werner,
ja, ich habe sogar noch eine andere Opion entdeckt. Man kann bei der bedingten Formatierung innerhalb einer Pivot-Tabelle die Option "Alle Zellen mit ... Werten" wählen, in zwei Modi, einmal mit Zusammenfassung, einmal ohne.
Der Vorteil ist, es wird automatisch auf neue Zeilen übertragen, wenn die Pivot wächst.
Nachteilig (zumindest in meinem Fall) ist, dass ich diese beiden Auswahloptionen eher beknackt finde. Ich würde lieber für jedes Kriterium (bis heute, nächste Woche, nächster Monat) eigene Formatierungen anwenden. Außerdem lassen sich so auch nicht die Namen einfärben.
Irgendwas ist doch immer. Also doch wieder der Pivot mit etwas VBA auf die Sprünge helfen.
Nochmals vielen Dank für deine Hilfe,
Rainer
Anzeige
AW: bitteschön owT
13.07.2019 18:59:48
neopa
Gruß Werner
.. , - ...

312 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige