Anzahl gleicher Werte unter Duplikatoren

[marinko]

Hallo Jörg (und auch alle anderen die mir helfen können/wollen) :)

ich habe jetzt die excel file hinzugefügt - es geht mir um die formel in Zelle P16

Da möchte ich gerne haben, dass ich die Anzahl der unterschiedlichen Trainings bekomme. Excel soll dabei den Trainingsnamen (Spalte B) und die Schicht (Spalte E) berücksichtigen.

In dieser Tabelle kommen immer wieder neue Namen und Kurse hinzu dh die Formel sollte einmal gemacht werden und dann dafür immer gelten.

Code:

=SUMMENPRODUKT((VERGLEICH(B$1:B$16&E$1:E$16;B$1:B$16&E$1:E$16;)=ZEILE(B$1:B$16))*1)-2

Ich weiß nicht ob es da eine andere Lösung gibt um die Titelzeile und leere Zellen auszuschließen (Ich habe deshalb in der Formel "-2" am Ende eingefügt.

Danke für euer Feedback

[BoskoBiati]

Hallo,

ändere doch einfach die Bereiche ab:

Arbeitsblatt mit dem Namen 'Output'
	P
14	9
15	2
16	2
17
18	6

Zelle	Formel
P14	=SUMMENPRODUKT((VERGLEICH(C$1:C$972&D$1:D$972&K$1:K$972;C$1:C$972&D$1:D$972&K$1:K$972;)=ZEILE(C$1:C$972))*(K$1:K$972=LINKS(O14;1))*($A$1:$A$972<>""))
P15	=SUMMENPRODUKT((VERGLEICH(C$1:C$972&D$1:D$972&K$1:K$972;C$1:C$972&D$1:D$972&K$1:K$972;)=ZEILE(C$1:C$972))*(K$1:K$972=LINKS(O15;1))*($A$1:$A$972<>""))
P16	=SUMMENPRODUKT((VERGLEICH(C$1:C$972&D$1:D$972&K$1:K$972;C$1:C$972&D$1:D$972&K$1:K$972;)=ZEILE(C$1:C$972))*(K$1:K$972=LINKS(O16;1))*($A$1:$A$972<>""))
P18	=SUMMENPRODUKT((VERGLEICH(B$3:B$16&E$3:E$16;B$3:B$16&E$3:E$16;)=ZEILE(B$1:B$14))*($A$3:$A$16<>""))

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

[shift-del]

Ich würde keinesfalls mit Pivot-Tabellen machen.

Arbeitsblatt mit dem Namen 'Output'
	P	Q	R	S	T	U
34	Level	Anzahl verschiedene ID		Training	Schicht	Anzahl verschiedene Training
35	A	2		G-B-Local CTO Kick-Off	(Leer)	1
36	B	9			A	1
37	P	2		G-P-Professional	(Leer)	1
38				P-A-eMIS Reports - Changeover	(Leer)	1
39				P-B-Process changes Operators	A	1
40					D	1

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

[Jockel]

Hallo Jörg,

Danke für deine Hilfe.

wegen links: den Zelleninhalt habe ich ein wenig modifiziert - so, dass nun links die richtige Formel ist, aber das ist ja nicht das Problem denke ich, hoffe ich :)

lg

Hallo, wenn du (d)eine neue (Beispiel)Datei erst später postest schon... ... woher soll man wissen, was du insgeheim "modifizierst"...? :@

[Jockel]

Ich würde keinesfalls mit Pivot-Tabellen machen...

Hallo Detlef, Recht hast du - das wäre ja fast zu einfach... :21:

[marinko]

sorry Jörg werde nichts mehr ändern ohne es vorher kommuniziert zu haben!

Jungs, ihr seid Genies ;)

Code:

=SUMMENPRODUKT((VERGLEICH(B$3:B$999&E$3:E$999;B$3:B$999&E$3:E$999;)=ZEILE(B$1:B$997))*($A$3:$A$999<>""))

Meine Frage bei der Funktion "Zeile" : warum muss diese B Zeile 1 beginnen? und darf nicht von Zeile 3 - 999 gehen zum Beispiel.

[neopa]

Hallo Marinko,

... hab jetzt nicht mehr den kompletten thread gelesen.

Deine letzte Frage kannst Du Dir nach folgenden Angaben sicherlich selbst beantworten:

Gib mal =ZEILE(B1:B5)  in eine beliebige Zelle ein und markiere diese Formel danach in der Eingabezeile und betätige danach [F9] . Was siehst Du? Sehen solltest Du = {1;2;3;4;5} Was würdest Du sehen , wenn Du =ZEILE(B3:B7)  eingeben hättest? Richtig: ={3;4;5;6;7;8}

So jetzt schau Dir die Funktion VERGLEICH() noch mal genauer in der MSO-Hilfe an. Welches Ergebnis ergibt VERGLEICH() wenn der Suchwert in der ersten angegebenen Zelle der Suchmatrix gefunden wird? Natürlich immer die 1! Wenn also bei =VERGLEICH("A";E3:E99;) das "A" in Zelle E3 gefunden wird, ist das Ergebnis der VERGLEICH()sformel 1, wenn es in der zweiten Zelle gefunden wird die 2 ...

In der Auswertungsformel wird zwischen dem VERGLEICH()-Formelteil und dem Formelteil mit ZEILE() eine "=" -Prüfung vorgenommen. Deshalb muss also der ZEILE()nformelteil wie geschrieben werden? Auch wenn Deine Auswertung anstelle bei Zeile 3 erst in Zeile 17 begonnen wird? Natürlich auch bei 1.

Beachte aber bitte nicht nur den Anfang der Bereichsangabe sondern auch das Ende! Schon bemerkt, dass das Ende kleiner ist? Wie viel? Warum ist das so? Die jeweiligen Bereiche müssen in derartigen Formeln zusätzlich gleich lang definiert sein.


Mehr zu Matrixformeln (auch wenn es ich sich bei der Auswertungsformel um keine klassische Matrixformel handelt,"funktioniert" sie doch ähnlich) sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=26 und ff.

[marinko]

Danke Allen für die großartige Hilfe!! :)