Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 08:56:40
Über alle Spalten nur €-Werte zählen
26.04.2024 08:08:24
Pivot Vormonatsveränderung
26.04.2024 07:54:11
nur den ersten Eintrag in spalte zählen
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1832to1836
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
1832to1836
1832to1836
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Pivottabelle-Filtermöglichkeiten

Pivottabelle-Filtermöglichkeiten
20.05.2021 14:22:14
Lars
Hallo zusammen,
ich habe folgende Grundtabelle vorliegen:
Spalte A: Kunde (Nike,Adidas,Puma usw.); Spalte B: Auftrag (verschiedene beispielhafte Auftragsnummern A1, A2, N1, N2 usw.); Spalte C: Code (0, 1, 2)
Die Pivottabelle soll nun Folgendes abbilden: Für jeden Kunden soll die diskrete Anzahl der verschiedenen Auftragsnummer im Wertebereich angezeigt werden, da es Auftragsnummern gibt, die mehrfach genannt werden, aber nur einmal gezählt werden sollen. Daneben sollte die Anzahl der Codes mit dem Wert "0" angezeigt werden.
Wenn ich den Code in den Berichtsfilter ziehe und nach 0 filtere, verändert sich leider automatisch die diskrete Anzahl der Auftragsnummern, so dass nur diejenigen Aufträge gezählt werden, die auch den Code "0" haben.
Ich möchte allerdings eine Gegenüberstellung der diskreten Anzahl verschiedener Aufträge und den Anteil derjenigen mit dem Code 0.
Vielen Dank im Voraus
VG
Lars

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Pivottabelle-Filtermöglichkeiten
20.05.2021 16:09:48
ChrisL
Hi Lars
Ich bin nicht sicher, ob ich dich richtig verstanden habe, aber hier mal eine Beispieldatei:
https://www.herber.de/bbs/user/146296.xlsx
Vorausgesetzt mein Verständnis war korrekt, müsste m.E. ein neues Datenfeld/Spalte erzeugt werden, da sich der Filter auf die ganze Datenmenge bezieht und somit wie festgestellt auch das gesamte Resultat beeinflusst.
Ich habe es in Power-Query gelöst.
- Menü Daten, Aus Tabelle
- Duplikate entfernen
- Abfrage duplizieren und bei der Hilfsabfrage die Nullen filtern
- Beide Abfragen "Gruppieren nach" und so die Anzahl ermitteln
- Hilfsabfrage zur Hauptabfrage hinzufügen.
Im Prinzip könnte man die Power-Query Abfrage direkt so als Resultat ausgeben.
Ich habe die Hauptabfrage (nur Verbindung) aber stattdessen ins Datenmodell geladen und auf dem Datenmodell die Pivot erstellt. Als Aggregation in der Pivot pro-forma "Summe" gewählt, aber eigentlich wurde das Endresultat bereits in der Power-Query Abfrage ermittelt.
cu
Chris
Anzeige
AW: Pivottabelle-Filtermöglichkeiten
20.05.2021 17:40:34
Luschi
Hallo ChrisL,
Dein Beispiel ist interessant, hat aber einen kleinen Schönheitsfehler:
- in Basis!A12 hat sich am Ende 1 Leerzeichen eingeschliechen
- und kann in PQ beim Entdecken von Duplikaten zu Problemen führen
- deshalb Trimme ich in PQ die Text-Spalten vor Table.Distinkt(...)
Gruß von Luschi
aus klein-Paris
AW: ich hatte es in den Quelldaten gelöscht owT
20.05.2021 19:06:18
neopa
Gruß Werner
.. , - ...
AW: ich hatte es in den Quelldaten gelöscht owT
20.05.2021 20:25:54
Luschi
Hallo Werner,
bei mehreren Tausend Zeilen wird das aber sehr zeitaufwändig:
- und die Fehlerquote sollte dabei nicht gerade nahe '0' sein
- bei Datenaktualisierung ist das Problem doch sicher wieder da
Gruß von Luschi
aus klein-Paris
Anzeige
AW: sehe ich etwas anders ...
21.05.2021 09:47:17
neopa
Hallo Luschi,
... warum trimmen? Da müsste man in PQ erst nach der entsprechenden Funktion auf Suche gehen (als Menüfunktion hab ich diese in meiner XL2016er Version jedenfalls nicht gefunden) und wenn, dann sollte es da ja auch eine vergleichbare Funktion wie GLÄTTEN() geben, oder?
Ersatzweise bietet sich in PQ für den konkreten Fall (wo innerhalb den Datenwerten keine Leerzeichen vorhanden sind) die Funktion Werte ersetzen an.
Allerdings würde ich auch das Glätten trotzdem schon in der Quelldaten tun. Natürlich nicht einzeln sondern einfach mit SUCHEN und ERSETZEN und das ist schnell genug auch für tausende Datensätze.
Interessant ist allerdings: Pivotauswertung macht offensichtlich automatisch ein GLÄTTEN vor seiner Auswertung. Da stellt sich mir die Frage, warum nur da?
Gruß Werner
.. , - ...
Anzeige
AW: sehe ich etwas anders ...
21.05.2021 10:49:23
ChrisL
Hi Werner
Klar ist es schöner, wenn die Daten bereits "sauber" daher kommen, so dass nicht bereinigt werden muss. Aber wenn du die Quelle nicht beeinflussen kannst z.B. externer Bezug, dann ist es schon recht schick, dies innerhalb von PQ zu erledigen. Hierfür gibt es im PQ-Editor mit rechter Maustaste oder Menü Transformieren/Format die Optionen "bereinigen" und "kürzen".
Und ja, Pivot scheint tatsächlich automatisch zu glätten, aber nur die Leerzeichen am Ende. In PQ wäre dies, analog der automatischen Typenkonvertierung, ebenfalls praktisch. Andere Anwendungen fallen mir hingegen spontan nicht ein (z.B. für Formeln m.E. nicht sinnvoll).
cu
Chris
Anzeige
AW: in XL2016 gibt es diese Funktionalität ...
21.05.2021 12:03:00
neopa
Hallo Chris,
... im Kontextmenü nicht. Aber dank Deiner Info habe ich sie jetzt unter "Format" auch gefunden. Dort hatte ich einfach nicht nachgesehen :-(
Interessant ist nun wiederum, dass BEREINIGEN offensichtlich der Formelfunktion GLÄTTEN() entspricht und die Pivotauswertung führende Leerzeichen als Textwert beibehält. Daran kann man erkennen, dass bei der PQ-Entwicklung niemand von den Pivot-Entwicklern beteiligt gewesen sein dürfte.
Gruß Werner
.. , - ...
AW: in XL2016 gibt es diese Funktionalität ...
21.05.2021 13:21:07
ChrisL
Hi Werner
Lustig, wie sich dein XL2016 immer mal wieder von meinem XL 2016 unterscheidet. :-))
(zugegeben meine Wegbeschreibung, war wenig ausformuliert)
PQ Bereinigen (Text.Clean): Entfernen Sie nicht druckbare Zeichen
PQ Kürzen (Text.Trim): Entfernen Sie die führenden und nachfolgenden Leerzeichen
Formel GLÄTTEN (TRIM): Löscht Leerzeichen in einem Text, die nicht als jeweils einzelne zwischen Wörtern stehende Trennzeichen dienen.
Formel KÜRZEN (TRUNC): Schneidet die Kommastellen der Zahl ab und gibt als Ergebnis eine ganze Zahl zurück.
DAX TRIM: Wie Formel GLÄTTEN
Die unterschiedliche Verwendung der Begriffe ist verwirrend. PQ tanzt in der Bezeichnung und Funktion definitiv aus der Reihe. Das automatische Abschneiden von Leerzeichen am Ende in Pivot kommt unerwartet (Bug oder Feature?), aber erfüllt noch nicht die vollständige Funktion von DAX TRIM.
cu
Chris
Userbild
Anzeige
AW: das liegt wohl daran ...
21.05.2021 14:16:34
neopa
Hallo Chris,
... dass meine XL2016 "nur" eine Home and Business 2016 Version ist.
Die "Versionspolitik"von MS würde ich also nicht als "lustig" sondern eher traurig bezeichnen. Ob da überhaupt noch jemand genau unterscheiden kann, was in welcher Version und welchen Update an Funktionalitäten überhaupt freigeschaltet sind und welche nicht. Da braucht man da wohl schon ein eigenes Tool dafür.
Das PQ "aus der Reihe tanzt" liegt sicherlich daran, dass dies von MS mW ja nur einkauft wurde.
Wo ist denn "DAX TRIM" implementiert? Die/das gibt es bei auch nicht.
Das automatische Abschneiden von nach geführten Leerzeichen in Pivot würde ich als "nützlichen Bug" ;-) bezeichnen.
Gruß Werner
.. , - ...
Anzeige
AW: das liegt wohl daran ...
21.05.2021 14:23:56
ChrisL
Hi Werner
"nützlicher Bug", der war gut. :-)
Ich glaube unser Power-Query unterscheidet sich infolge Home/Business nicht. Hingegen ist DAX TRIM aus Power-Pivot und somit nur in der Business-Version verfügbar.
cu und ein schönes Wochenende
Chris
AW: ja, da hatte ich was überlesen ...
21.05.2021 14:42:46
neopa
Hallo Chris,
... ich hatte im Kontextmenü nach Deinen Beitrag lediglich direkt nach "bereinigen" und "kürzen" Ausschau, aber überlesen, dass diese als Unterfunktion von da Transformieren angeboten wird. Sorry, mein Fehler.
Power Pivot und damit DAX TRIM hab ich nun wirklich nicht.
Danke für Deine Information.
Gruß Werner
.. , - ...
Anzeige
AW: Pivottabelle-Filtermöglichkeiten
21.05.2021 08:07:25
ChrisL
Hi Luschi
Guter Hinweis. Danke für den Input.
cu
Chris
AW: Pivottabelle-Filtermöglichkeiten
21.05.2021 13:19:09
Lars
Hallo zusammen,
vielen Dank für die schnellen und vor allem hilfreichen Antworten.
@Chris: deine Variante mit PQ funktioniert super. Das Beispiel war natürlich von der Komplexität und Datenmenge her sehr vereinfacht dargestellt. Ich konnte deine PQ-Lösung dann aber einwandfrei auf die Datenmenge mit mehreren tausend Zeilen anwenden.
Dankeschön.
VG
Lars
AW: ob es in Deiner XL-Version möglich ist ...
20.05.2021 16:12:13
neopa
Hallo Lars,
... weiß ich nicht.
In meiner älteren XL-Version (2010/2016) hab ich dafü+r keine direkte Möglichkeit mit Pivot gefunden. Ich würde es bei einer viel größeren Datenmenge mit einer PowerQuery -Abfrage lösen.
Bei einer derartigen geringen Datenmenge wie im Beispiel, könnte man auch eine kombinierte Hilfsspalten-Formel- & Pivotauswertung vornehmen. Dazu in Basis!D2 folgende Formel: =--(ZÄHLENWENNS(A$2:A3;A3;B$2:B3;B3;C$2:C3;0)=1) und diese nach unten kopieren. In D1 dann noch eine entsprechende Überschrift zuweisen und in der Pivotauswertung diese Spalte mit Summe zusammenfassen lassen.
Gruß Werner
.. , - ...
Anzeige
AW: ob es in Deiner XL-Version möglich ist ...
21.05.2021 13:21:39
Lars
Hallo Werner,
danke für deine Antwort. Ich hatte keine passende Möglichkeit gefunden das Problem direkt über die Pivot-Optionen zu lösen.
Mit Hilfsspalten habe ich es vorübergehend auch gelöst gehabt.
Die Power-Query Variante von Chris passt genau auf meine Anforderungen.
Viele Grüße
Lars
AW: unklar ist aber noch ... nachgefragt ...
21.05.2021 14:33:30
neopa
Hallo Lars,
... warum Du eingangs nur darauf verwiesen hattest, dass Du mit PIvot nicht die "diskrete Anzahl für Code = 0) ermitteln konntest, nicht aber das die von Dir mit Pivot ermittelte "diskrete Anzahl für alle Auftrage" nach deiner jetzigen Aussagen falsche Werte ergaben. Jedenfalls unterscheiden diese sich ja teilweise von den der PQ-Lösung von Chris mit Deinen angegebenen Werten. Wenn diese wirklich jetzt so von Dir gewollt sein sollen, dann hatte ich Dich ja auch schon darauf hingewiesen, wie es mit meiner ähnlichen PQ-Lösung auch erzielt werden hätte können. Aber als Pivotlösung müsste man da auch ganz anders vorgehen als bisher aufgezeigt.
Waren also Deine mit Pivot ermittelte "diskrete Anzahl für alle Auftrage" falsch oder richtig?
Gruß Werner
.. , - ...
Anzeige
AW: 2 Variant. mit Pivot und (m)eine PQ-Lösung ...
20.05.2021 19:01:46
neopa
Hallo Lars,
... hatte eben festgestellt, dass in meine eingestellten Hilfsspaltenformel nicht ganz korrekt war. Die hab ich jetzt in beigefügter Datei korrigiert und gleichzeitig noch eine 2. Pivot-Formel-Variante aufgezeigt, die keiner Hilfsspalte in Basis benötigt sondern wo das Ergebnis für Code=0 neben der Pivotauswertung ermittelt wird. Sieh dazu hier: https://www.herber.de/bbs/user/146300.xlsx
Zusätzlich hab ich darin meine PQ-Auswertung erstellt, mit welcher die gleiche Ergebniswerte ermittelt werden, wie mit meiner Pivotauswertung, die ich Deiner Vorgabe angepasst hatte. Die PQ_Auswertung von Chris ergibt für Erima und Reebok andere Werte. Diese sind allerdings dann die richtigen, wenn für die Anzahl aller verschiedener Aufträge auch die verschiedenen Codes berücksichtigt werden sollen. Um diese zu ermitteln müsste ich in meiner PQ-Auswertung lediglich einen "Schritt" entfernen.
Nur Du kannst wohl entscheiden, was Du wirklich für Ergebniswerte anstrebst.
Gruß Werner
.. , - ...
AW: kennzeichne den thread als noch offen owT
20.05.2021 19:12:39
neopa
Gruß Werner
.. , - ...
AW: wieder als offen gekennzeichnet owT
21.05.2021 09:50:18
neopa
Gruß Werner
.. , - ...

305 Forumthreads zu ähnlichen Themen

Pivot-Tabellen mit gefilterten Werten möglich?
Andreas

Hallo Forum,
ich habe mal eine Frage bezüglich Pivot-Tabellen. Kann eine solche Tabelle schon vorher gefilterte Werte auswerten, ohne dass die herausgefilterten Zellen beachtet werden?
MFG Andreas
10.11.2008 11:29:00
Thread-ID: 1023155
5 Beiträge
Anzeige
Pivot Tabellen weiter auswerten
Christoph

Hallo,
ich werte meine Dienstleistungen, die aktenzeichenbezogen in eine Liste eingegeben werden mit Hilfe einer Pivottabelle aus. D.h. ich gebe in einer fortlaufenden Tabelle (mit inzwischen 6000 Zeilen !!!) Datum, Aktenzeichen, Modul und Zeit (01:30) ein. Insgesamt gibt es 5 Beratungsmod...
26.02.2009 14:18:54
Thread-ID: 1054384
3 Beiträge
Pivot-Tabelle Datumsfilter in Spalte
MBorn

Hallo,
in meiner Pivot-Tabelle gibt es in Spalte A Datumswerte.
Weiß jemand, wie es gelingt, Datumswerte von/bis (zwischen zwei Grenzdaten) zu filtern?
Soweit ich sehe bietet der Filter nur eine Variation, entweder größer als oder
kleiner als....
Noch schöner wäre es wenn ic...
08.05.2009 16:57:27
Thread-ID: 1073949
2 Beiträge
Anzeige
Pivot Tabelle auswerten mit Problemen
DieterL

Hallo Excelfreunde,
Ich habe eine ganz normale Pivot Tabelle und möchte mir das Ergebnis aus der rechten unteren Zelle zusätzlich in einer anderen Zelle oberhalb anzeigen lassen. Aber egal was ich mache, nach dem verlinken erscheint immer eine null. Auch die Daten aus dem Pivot Datenbereich...
03.09.2009 11:55:32
Thread-ID: 1100292
6 Beiträge
Pivottabelle Spalten berechnen
karsten

Hallo Zusammen,
ich habe folg.Frage.:
Ich habe eine Tabelle mit 2 Spalten.
1.Spalte: DATUM
2.Spalte: CODE
DATUM CODE
01.01.2009 A
08.02.2009 M
23.02.2009 C
09.08.2009 F
Wie kann ich in einer Pivottabelle die Datumsangaben bereits in Monaten eingeteilt dar...
23.09.2009 11:15:14
Thread-ID: 1104846
10 Beiträge
Pivot Tabelle in Tabelle mit Spalten umwandeln??
okgi

Hallo liebe Excel Gemeinde!!
Meine Frage ist: wie kann ich eine Pivot Tabelle in eine Tabelle mit Spalten automatisiert überführen?
Zum Beispiel:
Pivot Tabelle
________KST_____________
Konto_.......1...........2...........3..
__A___.100,00................31,99
__B___...
15.12.2009 17:35:51
Thread-ID: 1124143
3 Beiträge
Pivottabelle bei mehreren Spalten
RolandE

Hallo,
ich möchte eine Pivottabelle aus 3 Spalten erstellen. Zahl1 und Zahl2 sollen je nach ID zusammengezählt werden.
Leider schreibt Excel die Summen von Zahl1 und Zahl2 untereinander. Ich möchte sie jedoch nebeneinaner haben, wie auf dem Blatt "Ziel" angedeutet.
https://www.herbe...
23.01.2010 13:58:57
Thread-ID: 1132305
5 Beiträge
Pivot Tabelle / beliebige Zwischensummen/Subtotals
Benjamin

Hallo Forum,
ich versuche (und zweifel langsam schon an mir...), in einer Pivot Tabelle beliebige Zwischensummen einzufügen. Der Umgang mit den "normalen" Zwischensummmen ist mir ansonsten geläufig, sprich Zwischensummen unter einer Kategorie...
Ich komme an folgender Stelle nicht weite...
28.01.2010 13:34:36
Thread-ID: 1133552
3 Beiträge

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
19
2
1
2
5
9
2
4
2
1
2
1
4
7
1
2
3
4
1
2
1
9
3
5