Anzahl aller Mehrfachen/Duplikate anzeigen

[Henning M]

Hallo zusammen.

In Spalte 1 kommen verschiedene Werte vor. Manche Werte kommen nur einmalig vor, andere doppelt, dreifach und öfter. Angezeigt bekommen möchte ich die Gesamtzahl der Werte, die in Spalte 1 öfter als einmal vorkommen. Ich möchte nicht wissen, wie oft jeder dieser mehrfachen Werte vorkommt. Im Beispiel unten kommen in Spalte 1 die Werte a, c und d jeweils mehrfach vor. Es gibt also drei Mehrfache. Das möchte ich angezeigt bekommen. Über VBA oder mit Hilfsspalte bekomme ich das selbst hin. Es dürfen aber weder Makros in der Arbeitsmappe verwendet werden noch darf eine Hilfsspalte verwendet werden.

Beispiel mit Hilfsspalte:
(1 = erstes Vorkommen des Wertes; 0 = wiederholtes Vorkommen des Wertes; 2 = Wert kommt nicht mehr als einmal vor)

	A	B
1	Spalte 1	Hilfsspalte
2	a	1
3	a	0
4	f	2
5	b	2
6	c	1
7	c	0
8	d	1
9	d	0
10	d	0

Zelle	Formel
B2	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)
B3	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)
B4	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)
B5	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)
B6	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)
B7	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)
B8	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)
B9	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)
B10	=(ZÄHLENWENN($A$2:[@[Spalte 1]];[@[Spalte 1]])=1)+(ZÄHLENWENN([Spalte 1];[@[Spalte 1]])=1)

	D
1	3

Zelle	Formel
D1	=ZÄHLENWENN(Tabelle2[Hilfsspalte];"1")

Ich versuche nun schon seit Stunden, die Formel der Hilfsspalte mit der Formel in D1 zu verwursten, aber es will nicht gelingen. Gibt es einen Weg ohne Hilfsspalte (und ohne Makros)?

[schauan]

Hallöchen,
Edit: Antwort war Quatsch

[BoskoBiati]

Hi,

Code:

=SUMMENPRODUKT((ZÄHLENWENN(A1:A9;EINDEUTIG(A1:A9))>1)*1)

[Henning M]

Astrein, Tag gerettet. Danke schön!

(Da war ich ja schon recht nah dran. Summenprodukt und Zählenwenn hatte ich schon versucht. Nur das Eindeutig hatte ich absichtlich nicht angefaßt, weil es ja eigentlich das Gegenteil ist. Aber klar, das kann man ja auch umdrehen …)

[HKindler]

Hi,

weitere Varianten:

=SUMME(--(ZÄHLENWENN(A1:A9;EINDEUTIG(A1:A9))>1))
=ZEILEN(EINDEUTIG(A1:A9))-ZEILEN(EINDEUTIG(A1:A9;;WAHR))
=ZEILEN(EINDEUTIG(VSTAPELN(EINDEUTIG(A1:A9);EINDEUTIG(A1:A9;;WAHR));;WAHR))

[Henning M]

=SUMME(--(ZÄHLENWENN(A1:A9;EINDEUTIG(A1:A9))>1))

Auch gut, die ist sogar noch kürzer. Danke.

[Luschi]

Hallo Henning M,

es kommt nicht darauf an, daß
- die Formel kürzer oder länger ist
- sondern das die Formel das richtige Ergebnis liefert
- und der Anwender (also Du) diese Formel auch versteht
- und da habe ich so meine Zweifel
- das Motto: Hauptsache es funktioniert
- kann nicht das Argument sein, diese oder jene Formel zu verwenden!

Gruß von Luschi
aus klein-Paris

[Henning M]

Es kommt sehr wohl auch auf die Länge und die Komplexität der Formel an. Je kürzer bzw. je weniger komplex die Formel ist, desto einfacher ist sie zu handhaben. Manche mögen es so kompliziert wie möglich, ich mag es so einfach wie möglich, weil dann auch die Formelpflege einfacher ist. Denn ich habe keine Zeit zu verschenken. Darüber hinaus prüfe ich auch, ob die Formel für meine Zwecke geeignet ist. Bevor ich auf HKindlers Beitrag antwortete, habe ich auch diese drei Varianten geprüft. Sie liefern das richtige Ergebnis. (Gibt es tatsächlich so doofe Menschen, die eine vorgeschlagene Formel ohne sie vorher zu überprüfen einfach übernehmen, weil sie schön kurz ist, und sich dann die Katze im Sack einbauen? – Rhetorische Frage.) Ich habe lediglich angemerkt, daß eine von HKindlers Formeln noch kürzer ist. Daß Du daraus ein „Hauptsache, es funktioniert“ konstruierst, ist Deine Erwartungshaltung, keine von mir getätigte Aussage. Ich habe gar nicht geschrieben, daß ich jetzt die kürzere Formel verwende. Im übrigen ist nicht nur wichtig, daß die Formel das richtige Ergebnis liefert, sondern auch daß sie für den Anwendungsfall geeignet ist. Z. B. kann es vorkommen, daß jemand in den Daten herumfuhrwerkt, einen Datensatz löscht und dann eine leere Zeile hinterläßt. Alle vorgenannten Formeln können damit umgehen und geben keinen Fehler aus. Auch das habe ich geprüft. Kannst Du das wissen? – Nein, kannst Du nicht, weil Du mich nicht kennst.

Ich brauchte einen Denkanstoß für eine Formel, weil ich nicht jede Funktion auswendig kenne. Ich verwende Excel beruflich, nicht in meiner Freizeit. Ich bekomme es schon nicht bezahlt, in meiner Freizeit in einem Forum nach Formeln zu fragen, weil mein Arbeitgeber keine umfangreichen Lehrgänge bezahlen will. Dazu kommt, daß es für meine Arbeit viel zu selten vorkommt, daß ich mal über ein bißchen ANZAHL und ZÄHLENWENN hinausgehende Formeln benutzen muß. Das heißt, Funktionen, die ich mir aneigne, geraten mangels regelmäßiger Nutzung auch mal in Vergessenheit. Dann brauche ich wieder einen Denkanstoß. – So wie das hier der Fall war. Das (und auch die Anmerkung über die kürzere Formel) bedeutet nicht, daß ich die angebotenen Formeln nicht verstehe. Kannst Du das wissen? – Nein, kannst Du nicht, weil Du mich nicht kennst.

Bist Du ein sympathischer Mensch, den ich mag, wenn ich ihn kenne? – Keine Ahnung. Kann ich nicht wissen, weil ich Dich nicht kenne. Erhobene Zeigefinger machen aber unsympathisch.

[RPP63]

Bist Du ein sympathischer Mensch, den ich mögen würde, falls ich ihn kenne?
Prinzipiell gebe ich Dir Recht, aber welcher Teufel hat Dich geritten, folgendes zu schreiben?

Ich bekomme es schon nicht bezahlt, in meiner Freizeit in einem Forum nach Formeln zu fragen, weil mein Arbeitgeber keine umfangreichen Lehrgänge bezahlen will.

Wer, glaubst Du, gibt in solchen Foren Antwort?
Richtig! Es sind Leute, die in ihrer Freizeit unentgeltlich helfen!
Vielleicht erwägt Dein Arbeitgeber ja eine kleine Spende an dieses gemeinnützige(!) Forum …

[Henning M]

Prinzipiell gebe ich Dir Recht, aber welcher Teufel hat Dich geritten, folgendes zu schreiben?

Der Satz war eine Kritik an meinem Arbeitgeber. Er paßt womöglich besser hinter die Sätze
„Manche mögen es so kompliziert wie möglich, ich mag es so einfach wie möglich, weil dann auch die Formelpflege einfacher ist. Denn ich habe keine Zeit zu verschenken.“
des ersten Absatzes.
Welche Kritik hast Du denn genau an meiner Aussage?