Anzahl unterschiedlicher Vorkommen in einem Bereich - Eleganz und Erklärung


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:

A B C D 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)

A B C D E 1           2   Werte Vorkommen Kehrwert   3   5 3 0,333333333   4   2 4 0,25   5   2 4 0,25   6   2 4 0,25   7   3 2 0,5   8   5 3 0,333333333   9   4 1 1   10   5 3 0,333333333   11   2 4 0,25   12   3 2 0,5   13           14     Summe 4   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.

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ß: 

A B C D E F 1             2   3 3 3 3   3   2 2 3 1   4   1 4 3 3   5   1 3   -1   6   1 3 2 3   7   3 3 5 3   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!