Pivot: Zusammenfassungsfunktion "Anzahl" modifizieren
Frank Vellner
wie kann ich innerhalb von Pivot-Tabellen die Anzahl unterschiedlicher
Elemente für eine Auswahl anzeigen?
Beispielsweise wähle ich einen bestimmten Datumsbereich zur Anzeige aus
und möchte wissen, wie viele unterschiedliche Werte (Spalte 3) die
Kategorie "klein" (Spalte 2) in diesem Zeitraum (Spalte 1) hatte. Die
Zusammenfassungsfunktion Anzahl zeigt mit die Zahl der Werte
insgesamt - ich möchte aber nur die einmaligen gezählt haben.
Beispiel in Zahlen
Datum|Kategorie|Wert
01.01.2007|gross|1
01.01.2007|klein|2
01.01.2007|gross|2
01.01.2007|klein|3
01.01.2007|gross|1
01.01.2007|klein|1
01.01.2007|gross|2
01.01.2007|klein|2
01.01.2007|gross|1
01.01.2007|klein|1
02.01.2007|gross|2
02.01.2007|gross|2
02.01.2007|klein|3
02.01.2007|gross|1
02.01.2007|klein|1
02.01.2007|gross|2
02.01.2007|klein|2
03.01.2007|klein|3
04.01.2007|gross|1
Hier wäre die Anzahl unterschiedlicher Werte am 1.1. für "gross" 2
(nämlich 1 und 2) und für "klein" 3 (nämlich 1,2, 3).
Im englischen habe ich u.a. nach "Count unique column values" etc.
gesucht und fand als Lösungsansatz die Optionen der Feldeigenschaften.
Hier solle man als Anzahl zusammenfassen und die Daten als Index
zeigen. http://www.mrexcel.com/archive2/69000/79979.htm
Das geht aber bei mir nicht. Hat noch jemand eine Idee? Ich würde auch
Hilfsspalten in die Rohdaten einbauen o.ä. weil ich einfach an diesem
Punkt nicht weiterkomme. Abseits von diesem Beispiel interessiert mich
in der Realität eigentlich nur, ob es mehr als 1 unterschiedliches
Element im jeweils selektierten Zeitraum gibt.
Viele Grüße
Frank
Peter Eichler
ich helfe mir auch immer mit einer Hilfsspalte in den Rohdaten. Dazu
sortiere ich die Werte nach dem Unterscheidungskriterium und schreibe in die
Zählspalte quasi nur rein: WENN(A1=A2;0;1) und kopiere die entsprechend
runter. Dann zählt er halt immer dann, wenn in der Spalte A ein neuer Wert
auftaucht. In der PT nehme ich dieses Feld dann mit der Zusammenfassung als
"SUMME". Problematisch ist halt, wenn jamand die Daten anders sortiert.
Alternativ könnte man ein die Prüfspalte ein ZÄHLENWENN einbauen. Da ist
dann die Sortierung egal. In der PT gehört dann natürlich nicht Summe
sondern Maximum als Zusammenfassungskriterium. Ist deutlich sicherer gegen
den "Sortierfehler" aber eben auch deutlich langsamer.
Wenn du was Besseres findest - sach ma Bescheid.
Gruß
Peter
Frank Vellner
vielen Dank für deine Antwort. Das klingt zumindest mal wie ein
gangbarer "workaround", auch wenn ich jetzt noch nicht zum testen
gekommen bin. Ich bin davon überzeugt, dass es auch irgendwie mit
PT-Bordmitteln gehen muss...
Mein Verdacht sind die geschickte Verwendung der Optionen der
Feldeigenschaften. Aber diese Geschichte mit "Index" dort hat bei mir
noch nie etwas sinnvolles angezeigt. Die Hilfe hilft in diesem Fall
auch nicht wirklich weiter...
Vielleicht gibt es auch noch einen Lösungsansatz über die Summe der
Kehrwerte. So lösen die Matrix-Formel-Profis das "Anzahl
unterschiedlicher Werte"-Problem ausserhalb von PTs.
Das ärgerliche daran: Excel hat die Info eigentlich schon ausgerechnet.
Für die Zusammenstellung der Elemente bei den Pulldowns der Felder sind
alle Werte ja ohnehin schon auf "Einmaligkeit" selektiert. Nur kommt
man halt nicht ran an die Info :-(
Ich forsche weiter und melde mich, wenn ich etwas finde.
Viele Grüße
Frank
Thomas Ramel
Frank Vellner schrieb am 28.06.2007
> vielen Dank für deine Antwort. Das klingt zumindest mal wie ein
> gangbarer "workaround", auch wenn ich jetzt noch nicht zum testen
> gekommen bin. Ich bin davon überzeugt, dass es auch irgendwie mit
> PT-Bordmitteln gehen muss...
Das wage ich doch stark zu bezweifeln - ohne externe Formeln wie
ZÄHLENWENN() ist mir bisher keine Weg bekannt die Anzahl unterschiedlicher
Elemente zu zählen
> Mein Verdacht sind die geschickte Verwendung der Optionen der
> Feldeigenschaften. Aber diese Geschichte mit "Index" dort hat bei mir
> noch nie etwas sinnvolles angezeigt.
Ja, da stimme ich zu, ich kann das Beispiel aus dem angeführten Beitrag
auch nicht wirklich nachvollziehen - vermutlich klappt das eher bei
einstufigen Pviot-Tabellen.
> Vielleicht gibt es auch noch einen Lösungsansatz über die Summe der
> Kehrwerte. So lösen die Matrix-Formel-Profis das "Anzahl
> unterschiedlicher Werte"-Problem ausserhalb von PTs.
Das wäre auch in der PT mit externen Formeln denkbar.
> Das ärgerliche daran: Excel hat die Info eigentlich schon ausgerechnet.
Die PT aggregiert, sprich fasst zusammen - die *Anzahl* unterschiedlicher
Felder ist ihr eigentlich recht egal.
Gegebenenfalls müsstest Du diese per VBA aus den Quelldatenoder der
aufbereiteten Pivot-Tabelle herausholen.
> Ich forsche weiter und melde mich, wenn ich etwas finde.
Ja, gerne, das interessierte mich auch.
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-2]
Microsoft Excel - Die ExpertenTipps
Frank Vellner
> Das wage ich doch stark zu bezweifeln - ohne externe Formeln wie
> ZÄHLENWENN() ist mir bisher keine Weg bekannt die Anzahl
> unterschiedlicher Elemente zu zählen
vielleicht ist meine Anforderung auch ein kleines bischen anders - ich
will nicht wissen, wieviele es sind sondern nur, ob es mehr als 1 sind.
> eher bei einstufigen Pviot-Tabellen.
äh, einstufig? Was bedeutet das?
> Das wäre auch in der PT mit externen Formeln denkbar.
du meinst solche "Kehrwert-Formeln" als berechnetes Feld? Oder eine
Matrixformel die neben der PT steht?
> Die PT aggregiert, sprich fasst zusammen - die *Anzahl*
> unterschiedlicher Felder ist ihr eigentlich recht egal.
ja, aber jedes Feld wird eben genau einmal als Element aufgeführt - und
dieses "genau einmal" entspricht irgendwie meiner Anforderung - auch
mir ist die exakte Anzahl egal.
> Ja, gerne, das interessierte mich auch.
schaun wir mal...
Viele Grüße
Frank
Thomas Ramel
"Frank Vellner" schrieb:
> > Das wage ich doch stark zu bezweifeln - ohne externe Formeln wie
> > ZÄHLENWENN() ist mir bisher keine Weg bekannt die Anzahl
> > unterschiedlicher Elemente zu zählen
>
> vielleicht ist meine Anforderung auch ein kleines bischen anders - ich
> will nicht wissen, wieviele es sind sondern nur, ob es mehr als 1 sind.
OK - dazu musst Du aber dennoch die Anzahl ermitteln, damit Du vergleichen
kannst ob diese grösser 1 ist.
> > eher bei einstufigen Pviot-Tabellen.
>
> äh, einstufig? Was bedeutet das?
...solche die nur *eine* Ausprägung, sprich nur ein Feld im Zilenbereich
haben.
> > Das wäre auch in der PT mit externen Formeln denkbar.
>
> du meinst solche "Kehrwert-Formeln" als berechnetes Feld? Oder eine
> Matrixformel die neben der PT steht?
Nicht mal Matrixformeln, solange die PT einstfig ist (siehe oben)
Für deine Anforderung z.B.:
Datum und Kategorie in den Seitenbereich - hier kannst Du dann deine Auswahl
treffen
Wert in den Zeilenbereich *und* in den Datenbereich (aggregierungsfunktion
ist egal).
Dann in einer Zelle dir Formel =ANZAHL(F:F) wobei F die Spalte mit den
Ergebnissen ist.
Bei jeder Veränderung im Seitenbereich wird die Anzahl dann angepasst.
--
Mit freundlichen Grüssen
Thomas Ramel (@Web-Interface)
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003]
Frank Vellner
> Dann in einer Zelle dir Formel =ANZAHL(F:F) wobei F die Spalte mit
> den Ergebnissen ist.
>
> Bei jeder Veränderung im Seitenbereich wird die Anzahl dann
> angepasst.
aha, interessanter Ansatz um die Anzahl unterschiedlicher Werte
ausserhalb der PT darzustellen. Das geht doch eigentlich auch, wenn ich
die Werte nur in die Zeilenfelder tue und dann in der Spalte die
Einträge zähle. Ganz ohne Datenfeld :-)
Bequemer wird es sogar noch, wenn man Wert als erstes und Datum an
zweites Zeilenfeld definiert, denn da ist die Selektion
unterschiedlicher Datumsbereiche einfacher zu machen. Funktioniert auch
ohne Datenfeld.
Für das ursprüngliche Problem innerhalb der PT für einen bestimmten
Datumsbereich nur diejenigen Werte überhaupt angezeigt zu bekommen, wo
mehr als 1 unterschiedlichcher Wert existiert, werde ich jetzt erst
einmal den sortierten Hilfsspalten-Vorschlag testen.
Viele Grüße
Frank
Frank Vellner
die Idee war zwar gut, funktioniert aber in meinem Fall auch nicht
richtig. Für den Gesamt-Zeitraum ist die Sache prima. Sobald ich aber
eine Teilmenge vom Gesamt-Zeitraum betrachte, geht es nicht mehr.
Angenommen der Zeitraum ist ein Jahr und es gab 3 unterschiedliche
Werte. Das zeigt die PT mit deiner Formel korrekt an. Lasse ich aber
auf Monatsebene darstellen, stimmen die Angaben nicht mehr, denn es
erscheint nur eine 2, wenn gerade in dem Monat der Wechsel stattfand.
Anderen Monaten wird eine 1 gezeigt, obwohl ja eigentlich 2
unterschiedliche Werte vorhanden sind...
Ich suche mal weiter.
Viele Grüße
Frank
Frank Vellner
>> vielleicht ist meine Anforderung auch ein kleines bischen anders -
>> ich will nicht wissen, wieviele es sind sondern nur, ob es mehr als
>> 1 sind.
>
> OK - dazu musst Du aber dennoch die Anzahl ermitteln, damit Du
> vergleichen kannst ob diese grösser 1 ist.
ich denke, dass das zählen nicht zwingend notwendig ist. Die Werte, um
die es geht sind hohe (genau 7 stellige) Zahlen. Deshalb würde es z.b.
ausreichen, für jeden ausgewählten Zeitraum die Summe der Werte zu
bilden und durch irgendeinen der Einzelwerte zu teilen. Kommt dabei
eine "Kommazahl" (d.h. nicht aus der Zahlenmene N, natürliche Zahlen)
heraus, sind es mit Sicherheit mehr als 1 Wert. Mehr muss ich nicht
wissen.
Kommt dagegen eine Zahl aus N heraus, ist es mit extrem hoher
Wahrscheinlichkeit genau 1 unterschiedlicher Wert im betrachteten
Zeitraum.
Vielleicht würden einem Menschen mit mehr mathematischer Bildung als
ich sie habe, noch einfacherer Möglichkeiten einfallen. Mein Ansatz
wäre zwar ausreichend, aber ich wüsste trotzdem nicht, wie ich ihn
innerhalb einer PT realisiere.
Ich suche weiter
Viele Grüße
Frank
Thomas Ramel
Frank Vellner schrieb am 01.07.2007
>>> vielleicht ist meine Anforderung auch ein kleines bischen anders -
>>> ich will nicht wissen, wieviele es sind sondern nur, ob es mehr als
>>> 1 sind.
>>
>> OK - dazu musst Du aber dennoch die Anzahl ermitteln, damit Du
>> vergleichen kannst ob diese grösser 1 ist.
>
> ich denke, dass das zählen nicht zwingend notwendig ist. Die Werte, um
> die es geht sind hohe (genau 7 stellige) Zahlen.
Das ist natürlich 'etwas anderes', sprich, hier tun sich dann wirklich
Möglichkeiten auf.
> Deshalb würde es z.b.
> ausreichen, für jeden ausgewählten Zeitraum die Summe der Werte zu
> bilden und durch irgendeinen der Einzelwerte zu teilen. Kommt dabei
> eine "Kommazahl" (d.h. nicht aus der Zahlenmene N, natürliche Zahlen)
> heraus, sind es mit Sicherheit mehr als 1 Wert. Mehr muss ich nicht
> wissen.
> Kommt dagegen eine Zahl aus N heraus, ist es mit extrem hoher
> Wahrscheinlichkeit genau 1 unterschiedlicher Wert im betrachteten
> Zeitraum.
>
> Vielleicht würden einem Menschen mit mehr mathematischer Bildung als
> ich sie habe, noch einfacherer Möglichkeiten einfallen. Mein Ansatz
> wäre zwar ausreichend, aber ich wüsste trotzdem nicht, wie ich ihn
> innerhalb einer PT realisiere.
Nimm den Mittelwert zum aggregieren - wenn da eine Zahl mit Nachommastellen
rauskommt ist die Wahrscheinlichkeit hoch, dass es nur ein Wert ist.
Mit freundlichen Grüssen
Thomas Ramel (@Laptop)
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]
Frank Vellner
> Nimm den Mittelwert zum aggregieren - wenn da eine Zahl mit
> Nachommastellen rauskommt ist die Wahrscheinlichkeit hoch, dass es
> nur ein Wert ist.
stimmt, ich hatte mir irgendwie in den Kopf gesetzt, dass ich die Summe
durch den Wert eines Elements teilen muss, aber mit dem Mittelwert geht
es genauso. Bleibt noch das Problem der Auswahl: Wie bringe ich die PT
dazu, mir nur die Zeilen mit Nachkommastellen anzuzeigen? Oder danach
zu sortieren, also alle Werte mit Nachkommastellen oben oder unten
anzuzeigen. Fällt dir dazu auch noch ein Trick ein?
Ein anderer Ansatz der noch zuverlässiger funktionieren müsste wäre
Max - Min (Differenz von Maximum und Minimum) Gibt es genau ein Element
ist die Differenz immer 0. Gibt es mehr, ist sie anders. Das wäre dann
auch ein prima Sortierkritierium, weil die 0er ja alle zusammen
bleiben. Vielleicht liegt der Schlüssel ja in den berechneten
Elementen, aber dabei bekomme ich nur Fehlermeldungen.
Viele Grüße
Frank
Thomas Ramel
Frank Vellner schrieb am 03.07.2007
>> Nimm den Mittelwert zum aggregieren - wenn da eine Zahl mit
>> Nachommastellen rauskommt ist die Wahrscheinlichkeit hoch, dass es
>> nur ein Wert ist.
>
> stimmt, ich hatte mir irgendwie in den Kopf gesetzt, dass ich die Summe
> durch den Wert eines Elements teilen muss, aber mit dem Mittelwert geht
> es genauso. Bleibt noch das Problem der Auswahl: Wie bringe ich die PT
> dazu, mir nur die Zeilen mit Nachkommastellen anzuzeigen? Oder danach
> zu sortieren, also alle Werte mit Nachkommastellen oben oder unten
> anzuzeigen. Fällt dir dazu auch noch ein Trick ein?
Vielleicht ein berechnetes Feld, das vom Mittelwert den ganzzahligen Anteil
abzieht?
Das ergäbe dann immer eine 0 wenn keine Nachkommastellen vorhanden sind.
> Ein anderer Ansatz der noch zuverlässiger funktionieren müsste wäre
> Max - Min (Differenz von Maximum und Minimum) Gibt es genau ein Element
> ist die Differenz immer 0. Gibt es mehr, ist sie anders. Das wäre dann
> auch ein prima Sortierkritierium, weil die 0er ja alle zusammen
> bleiben. Vielleicht liegt der Schlüssel ja in den berechneten
> Elementen, aber dabei bekomme ich nur Fehlermeldungen.
Fehler habe ich hier zwar nicht erhalten, aber leider auch keine
verwertbaren Ergebnisse :-(
Vielleicht fällt uns ja später noch was brauchbares ein.
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
Peter Eichler
> Vielleicht fällt uns ja später noch was brauchbares ein.
> Mit freundlichen Grüssen
> Thomas Ramel
schön, dass du diese sachen auch so langfristig ernst nimmst!
hochachtungsvoll
peter
O.Steffan
ich war eigentlich auf der Suche nach etwas anderem, und habe nur durch
Zufall dieses Problem mit verfolgt, da ich auch schon vor ähnlichen Aufgaben
stand. Ich habe das also etwas, na ja, "russisch" gelöst, aber es
funktioniert.
Auf das Beispiel oben bezogen würde ich zuerst eine neue Spalte erstellen
mit dem gleichen Inhalt wie [Datum] (also z.B. [Datum2]). Warum das notwendig
ist, werde ich noch erläutern(ist ja aber kein großer Aufwand). Nun die erste
"normale" PT erstellen mit: Group[Datum], Group[Kategorie], Group[Datum2],
Anzahl([Wert]) as Anz. Diese PT dient, nach diversen "Schönheitsperationen"
bei der Formatierung (keine Zwischensummen usw.) als Datengrundlage für die
zweite PT. Nun ist auch klar, warum die Verdoppelung der ersten Spalte
notwendig wurde, denn sonst entstehen im ersten Gruppierungsfeld "Löcher",
die dann das Ergebnis verfälschen würden. Aber so habe ich nun drei saubere
Spalten für die zweite PT. Wird hier nun nach [Datum2] und Kategorie
gruppiert, kann man die Anzahl der Kategorien quasi als DISTINCT der
Ausgangstabelle ermitteln. Über die Spalte [Anz] aus PT1 kann man sogar die
Anzahl der Gesamtdaten aus der Ausgangstabelle summieren.
Na ja, man muss u.U. noch Filter setzen zwecks leerer Felder usw., aber das
Ergbnis passt und kann nach Anpassung der Daten in der Ausgangstabelle auch
ganz schnell aktualisiert werden.
Viele Grüße
Olaf
Marion Löwe
"Frank Vellner" schrieb:
Schau dir mal dieses Beispiel an. Ich denke, es entspricht deinen
Vorstellungen.
Falls du Fragen dazu hast, mail mich an.
http://www.gmx.de/mc/N2LekFq9cnMl6WUjgEYqrq8aFboul2
Hinweis: Der Link für den Gastzugang zum GMX MediaCenter ist bis zum
25.11.2007 gültig.
Du kannst auch in die PT klicken und den Assistenten aufrufen und auf diese
Weise die Einstellungen nachvollziehen.
Schöne Grüße aus Hannover
Marion
Frank Vellner
> Schau dir mal dieses Beispiel an. Ich denke, es entspricht deinen
> Vorstellungen.
> Falls du Fragen dazu hast, mail mich an.
>
> http://www.gmx.de/mc/N2LekFq9cnMl6WUjgEYqrq8aFboul2
>
> Hinweis: Der Link für den Gastzugang zum GMX MediaCenter ist bis zum
> 25.11.2007 gültig.
ich habe leider deinen geposteten Link nicht mehr testen können. Da der
Thread für mich im Juli abgeschlossen war, habe ich nicht mehr
reingeschaut.
Gerade zufällig wieder, aber da war dein Link schon kaputt. Kannst du
den noch renovieren?
Viele Grüße
Frank
Marion Löwe
"Frank Vellner" schrieb:
http://www.gmx.de/mc/BJVR7AOfGsatTYkdiFQU54tEKy9V3T
Hinweis: Der Link für den Gastzugang zum GMX MediaCenter ist bis zum
2.1.2008 gültig.
Rechtsklick in die Tabelle, den PT-Assistenten wählen und im 3. Schritt von
3 *glaub* auf Button 'Layout' klicken und gucken, wie ich es gemacht habe,
Fenster wieder schließen und den Button Optionen klicken, auch gucken und
dann selbst probieren. Viel Erfolg. Falls du nicht klarkommst, meld dich
einfach noch mal.
Gruß
Marion
Thomas Ramel
"Marion Löwe" schrieb:
> http://www.gmx.de/mc/BJVR7AOfGsatTYkdiFQU54tEKy9V3T
>
> Rechtsklick in die Tabelle, den PT-Assistenten wählen und im 3. Schritt von
> 3 *glaub* auf Button 'Layout' klicken und gucken, wie ich es gemacht habe,
> Fenster wieder schließen und den Button Optionen klicken, auch gucken und
> dann selbst probieren. Viel Erfolg. Falls du nicht klarkommst, meld dich
> einfach noch mal.
Wenn ich die ganze Diskussion von 'damals' richtig verstanden hatte, wollte
Frank *direkt* aus den Daten die Informataion wie viele *unterschiedliche*
Einträge zu einem bestimmten Datum und zur Ausprägung 'klein' oder 'gross'
vorkommen.
Es interessierte nicht welches diese Einträge sind, sondern nur wie viele
unterschiedliche es gibt - eine Ausgabe also in der folgenden Form
(Festbreitenschriftart einstellen):
Datum Kategorie Anzahl unterschiedliche
01.01.2007 gross 2
klein 3
02.01.2007 gross 2
klein 3
03.01.2007 klein 1
04.01.2007 gross 1
Dies ist IMO mit der PT direkt nicht möglich, das ist auch in dem verlinkten
Beispiel so.
--
Mit freundlichen Grüssen
Thomas Ramel (@Web-Interface)
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003]
Frank Vellner
> Wenn ich die ganze Diskussion von 'damals' richtig verstanden hatte,
> wollte Frank *direkt* aus den Daten die Informataion wie viele
> *unterschiedliche* Einträge zu einem bestimmten Datum und zur
> Ausprägung 'klein' oder 'gross' vorkommen.
> Es interessierte nicht welches diese Einträge sind, sondern nur wie
> viele unterschiedliche es gibt
yep, das ist völlig korrekt.
Viele Grüße
Frank
Frank Vellner
> Rechtsklick in die Tabelle, den PT-Assistenten wählen und im 3.
> Schritt von 3 *glaub* auf Button 'Layout' klicken und gucken, wie
> ich es gemacht habe, Fenster wieder schließen und den Button
> Optionen klicken, auch gucken und dann selbst probieren. Viel
> Erfolg. Falls du nicht klarkommst, meld dich einfach noch mal.
vielen Dank für deine Mühe. Ich schaue mir die Sache einmal gründlich
an und melde mich ggf. noch einmal hier.
Viele Grüße
Frank