Pivot ohne doppelte Werte

[Rabe]

Hi atilla und Peter,

Hier die Pivotauswetung nach Duplikate entfernen:
[...]
Du musst die Spalten Bearbeiter und Kdn_Ref_1 angehackt lassen und alle anderen Überschriften abwählen.

ok, jetzt haben alle drei Pivot-Auswertungen dasselbe Ergebnis.

Ich dachte, beim "Duplikate entfernen" muß ich die Spalten anhaken, aus denen ich die Doppelten entfernt haben will. Dann hatte ich befürchtet, daß da nur noch je ein PR und BE übrigbliebe. Aber anscheinend ist das dann die Kombination aus Bearbeiter und Referenz.

Wenn jetzt noch das Ergebnis der manuellen Auswertung damit übereinstimmen würde, wäre ich sehr zufrieden.

[Peter]

Hallo,

Wenn jetzt noch das Ergebnis der manuellen Auswertung damit übereinstimmen würde, wäre ich sehr zufrieden.

Ich habe mal folgendes probiert:

In der PT wird für das Jahr 2011 und den Bearbeiter BE ein Wert von 178 angegeben.

In deiner Datei hast du im Tabellenblatt manuell in der Spalte Q einen Wert von 205 (Q3).

Ich habe die Spalte Q und die PT aufsteigend sortiert und dann einfach mal ein Zählenwenn() darüber laufen lassen.

Die Werte die in Spalte R eine 1 haben sind uninteressant. Die Werte die eine 2 oder eine 0 haben solltest du dir mal näher anschauen. Da stecken die Abweichungen drin.

Beispiel: Im Tabellenblatt manuell ist ein Herr Laubengeiger. In der PT fehlen diese Werte. Auf die Schnelle habe ich gesehen, dass dieser Name in 2010 erstmalig vorkommt, aber auch in 2011 und dort dann in der PT aufgrund der Formel nicht mehr berücksichtigt wird, du diesen Wert aber bei der manuellen Erstellung noch drin hast.

Da würde ich mit der Prüfung mal ansetzen.

Ich habe die Datei mal bis auf diese zwei Tabellenblätter abgespeckt um das zu verdeutlichen.

Datei entfernt!
Moderator [Bild: smilie.php?smile_ID=1810]

[Rabe]

Beispiel: Im Tabellenblatt manuell ist ein Herr Laubengeiger. In der PT fehlen diese Werte. Auf die Schnelle habe ich gesehen, dass dieser Name in 2010 erstmalig vorkommt, aber auch in 2011 und dort dann in der PT aufgrund der Formel nicht mehr berücksichtigt wird, du diesen Wert aber bei der manuellen Erstellung noch drin hast.

Da würde ich mit der Prüfung mal ansetzen.

aaaah, genau das ist die Ursache der Unterschiede!

Da er ja in 2010 erstmalig auftritt, nimmt ihn die Formel in 2011 und 2012 raus. Deswegen sind es bei "manuell" mehr Werte (3) als in der PT (1). So wie in "manuell" ist es dann richtig.

Also muß ich die Summenproduktformel um den Abgleich der Jahreszahl erweitern. Ich füge eine Spalte "Jahr" ein und prüfe auf unterschiedliche Jahre.

=WENN(SUMMENPRODUKT(($B$2:$B2=B2)*($D$2:$D2=$D2)*($E$2:$E2=E2))>1;0;1)

Tabelle6

	B	C	D	E	F
1	Bearbeiter	AuftragFertig	Jahr	Kdn_Ref_1	Doppelt
2	PR	07. Jan 10	2010	'65801	1
3	PR	07. Jan 10	2010	'65801	0
4	PR	08. Jan 11	2011	'65801	1

verwendete Formeln
Zelle	Formel	Bereich	N/A
D2: D4	=JAHR(C2)
F2:F4	=WENN(SUMMENPRODUKT(($B$2:$B2=B2)*($D$2:$D2=$D2)*($E$2:$E2=E2))>1;0;1)

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.19 einschl. 64 Bit

Nun stimmt auch das Ergebnis manuell gezählt mit der PT überein (beide haben 2064):
   

PS: Ich lösche die Beispieldateien!

[Peter]

Hallo Ralf,

eine separate Spalte für das Jahr ist nicht notwendig, die folgende Formel sollte genügen:

WENN(SUMMENPRODUKT(($B$2:$B2=B2)*(Jahr($D$2:$D2)=Jahr($D2))*($E$2:$E2=E2))>1;0;1)

[Rabe]

Hi Peter,

eine separate Spalte für das Jahr ist nicht notwendig, die folgende Formel sollte genügen:

WENN(SUMMENPRODUKT(($B$2:$B2=B2)*(Jahr($D$2:$D2)=Jahr($D2))*($E$2:$E2=E2))>1;0;1)

aah, ich wußte nicht, daß das für die ganze Spalte auch geht.

[atilla]

Hallo Ralf,

bei den Datenmengen könnte es mit Summenprodukt zu Performance Problemen kommen,
deshalb hier noch zwei Lösungswege.

1. Du führst, wie Du vorhattest, das Jahr in einer Hilfsspalte auf und nimmst dann Duplikate entfernen,
dann aus den verbleibenden Daten die Pivot. In diesem Fall müssen dann drei Spalten angehackt und
alle anderen abgewählt sein. Das entspräche in etwa dem unteren Lösungsweg mit Formelverknüpfung.

2. Du arbeitest mit zwei Hilfsspalten, wie folgt:

Arbeitsblatt mit dem Namen 'alle Positionen'
	B	C	F	G	H
1	Bearbeiter	AuftragFertig	Kdn_Ref_1	Id	Anzahl
2	PR	07-Jan-10	65801	PR201065801	1
3	PR	07-Jan-10	65801	PR201065801	0

Zelle	Formel
G2	=B2&JAHR(C2)&F2
G3	=B3&JAHR(C3)&F3
H3	=WENN(ZÄHLENWENN($G$2:G2;G3);0;1)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

und erstellst aus diesen Daten die Pivot.

So sähe dann die Pivot aus:

Arbeitsblatt mit dem Namen 'Auswertung'
	F	G	H	I	J	K	L	M
44	Summe von Anzahl	Spaltenbeschriftungen
45	Zeilenbeschriftungen	2010	2011	2012	2013	2014	2015	Gesamtergebnis
46	PR	288	243	206	183	101		1021
47	BE	194	205	204	219	221	1	1044
48	Gesamtergebnis	482	448	410	402	322	1	2065

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Du siehst, dass ich noch eine Abweichung im Vergleich zu Deiner manuellen Berechnung habe (siehe rot Markierte Zelle)

[Peter]

Hallo,

bei den Datenmengen könnte es mit Summenprodukt zu Performance Problemen kommen,

wo du Recht hast hast du Recht, hier würde sich eine Lösung ohne Summenprodukt vermutlich positiv auf die Performance auswirken.

[Rabe]

Hi Atilla und Peter,

bei den Datenmengen könnte es mit Summenprodukt zu Performance Problemen kommen

ja, das habe ich gemerkt. Ich benutze am Montag dann nur noch die "Duplikate entfernen". Formel verwenden, nur damit eine Formel drin steht, ist mir nicht so wichtig. Performance eher.

Das Ergebnis zählt!