Autor: Peter Haserodt  --- Aus Excel Standard - Gruppe: Häufige Fragen

Anzahl unterschiedlicher Vorkommen

Autor: Peter Haserodt - Erstellt: ?      - Letzte Revision: ?
Anzahl unterschiedlicher Vorkommen in einem Bereich - Eleganz und Erklärung
WICHTIGER HINWEIS
So schön wie es ist, beachten Sie bitte, dass dies nur für kleinere Bereiche sinnvoll ist.
Bereiche mit mehr als 1000 Zellen können sehr langsam werden bei der Berechnung. Sehr große Bereiche können auch dazu führen, dass Excel sich verabschiedet


Die nachfolgende Formel, um die Anzahl von unterschiedlichen Werten in einem Bereich zu ermitteln, ist keine Unbekannte.
Sie ist nur etwas schwer am Anfang zu verstehen, deswegen wollen wir sie näher beleuchten.

Aber zuerst das Beispiel:

 
 ABCD
1    
2 Werte  
3 5  
4 2  
5 2  
6 2  
7 3  
8 5  
9 4  
10 5  
11 2  
12 3  
13    
14 Verschiedene
Vorkommen
4 
15    
Formeln der Tabelle
C14 : {=SUMME(1/ZÄHLENWENN(B3:B12;B3:B12))}

Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
 


Wenn man sich diese Matrixformel ansieht, kann man wieder mal denken:
Mann, was ist denn dies schon wieder!

Tatsächlich ist es nur ein wenig Mathematik gepaart mit Matrix!
Bevor ich aber lange hier erkläre, einfach die Auflösung der Formel, damit sollte sie schnell verständlich werden.
In der nachfolgenden Tabelle sind für den Bereich C3:D12 nur die Formeln für die Zeilen 3 und 4 ausgegeben, die anderen sind entsprechende (runterziehen)
 Peter Haserodt
  • Formelhilfe
  • Makroentwicklung
  • VBA-Programmierung
  • + + + + + + + + +
 
 ABCDE
1     
2 WerteVorkommenKehrwert 
3 530,333333333 
4 240,25 
5 240,25 
6 240,25 
7 320,5 
8 530,333333333 
9 411 
10 530,333333333 
11 240,25 
12 320,5 
13     
14  Summe4 
15     
Formeln der Tabelle
C3 : =ZÄHLENWENN($B$3:$B$12;B3)
D3 : =1/C3
C4 : =ZÄHLENWENN($B$3:$B$12;B4)
D4 : =1/C4
D14 : =SUMME(D3:D12)
 


Dies ist die obige Matrixformel aufgelöst mit Hilfsspalten.
In Spalte C ermitteln wir jeweils das Vorkommen des Wertes in Spalte B.
Z.B. kommt die 5 dreimal vor und so steht neben einer 5 jeweils der Wert 3.

Damit können wir aber nichts anfangen, der Wert muss so dargestellt werden, dass er in der Summe 1 ergibt.
Und dies können wir ganz einfach mit dem Kehrwert erreichen.
Also in der Spalte D wird der Wert in Spalte C umgewandelt in seinen Kehrwert. (1/Wert)

Jetzt wird die Sache schon klarer:
Wenn etwas z.B. 4 * vorkommt habe ich immer die Zahl 4. Davon der Kehrwert 0,25 , dies summiert: 1
Kommt etwas 20 mal vor , habe ich immer die Zahl 20, davon der Kehrwert 0,05 und summiere ich 20 mal 0,05 erhalte ich wieder die 1.

Ich denke, jetzt sollte die ganze Sache verständlich werden.
Die eingangs gezeigte Matrixformel ist nichts anderes, als das was wir zuletzt gemacht haben, in einen Schritt zusammenzuführen.
Sie sehen: Die Formel ist wahrlich elegant aber es liegt keine dunkle Magie hinter ihr. sm3 (1K)

Um die Matrixformel an sich nachvollziehen zu können, empfehle ich ihnen das Studium des Matrixtutorials

Es gibt auch Situationen, in welchen man Leerzeichen ausschließen muss (sonst Div/0) , Nullen nicht zählen, nur bestimmte Größen etc... auswerten will.
Nachfolgendes Beispiel zum Abschluss als Gedankenanstoß: 

 
 ABCDEF
1      
2 3333 
3 2231 
4 1433 
5 13 -1 
6 1323 
7 3353 
8      
9  3   
10      
Formeln der Tabelle
C9 : {=SUMME(WENN((B2:E7<>"")*(B2:E7<>0)*(B2:E7<3);1/ZÄHLENWENN(B2:E7;B2:E7);0))}

Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
 

Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben