Excel-TippSeltensten Wert in einer Spalte ermitteln

Mit der Standardfunktion MODALWERT() können Sie den am häufigsten vorkommenden Eintrag (Text oder Zahl) in einem bestimmten Bereich ermitteln. Wie aber gehen Sie vor, wenn Sie den am seltensten vorkommenden Eintrag ermitteln müssen? Hierfür gibt es (noch) keine Standardfunktion in Excel.

Sie können aber die folgende Formel einsetzen (aufgrund der Länge der Formel ist sie hier umgebrochen):

=INDEX(A1:A100;
   VERGLEICH(
      KKLEINSTE(
         ZÄHLENWENN(A1:A100;A1:A100);
         ANZAHLLEEREZELLEN(A1:A100)+1);
   ZÄHLENWENN(A1:A100;A1:A100);0)
)

Die Formel geht hierbei davon aus, dass Sie den seltensten Wert im Bereich A1:A100 suchen. Diesen Bereich passen Sie jeweils an Ihren gewünschten Suchbereich an.

Da es sich bei der Formel um eine Matrixformel handelt, bestätigen Sie die Eingabe nicht mit Enter, sondern mit der Tastenkombination Strg + Umschalttaste + Enter. Durch diese Tastenkombination wird die Formel automatisch in geschweifte Klammern eingerahmt.

Hinweis: Wenn es mehrere „seltenste Werte“ in Ihrer Liste gibt, dann ermitteln Sie den seltensten Wert, der als Erstes in der Liste eingetragen ist. Alle weiteren Werte, die gleich oft wie der seltenste Wert auftauchen, werden nicht angezeigt.

Im Beispiel ist 2 der seltenste Wert. Er kommt in der Liste A1:A20 zweimal vor. Der Wert 6 kommt ebenfalls nur zweimal vor; taucht in der Liste aber erst nach der 2 auf.

Wie ist die Formel für den seltensten Wert aufgebaut?

Was mit der Formel zur Berechnung des seltensten Werts im Einzelnen berechnet wird, soll in den folgenden Schritten erläutert werden:

• Zunächst wird mit ZÄHLENWENN(A1:A100;A1:A100) eine Liste erzeugt, in der für jeden Wert der Liste angegeben ist, wie häufig er vorkommt.
• Dann wird aus dieser Liste mit KKLEINSTE(ZÄHLENWENN(A1:A100;A1:A100); ANZAHLLEEREZELLEN(A1:A100)+1) der kleinste Wert ermittelt, der nicht 0 ist; leere Einträge ergeben als Häufigkeit des Vorkommens 0, was ausgeschlossen werden soll.
• Die Funktion VERGLEICH() sucht dann die Position des kleinsten Werts in der Liste der Häufigkeiten.
• Mit dieser Position gibt die Funktion INDEX() dann den Wert in der ursprünglichen Liste A1:A100 aus, der an der berechneten Position steht.

Wofür Sie diese Berechnung gebrauchen können

Wann benötigt man den oder die seltensten Werte? In einer sehr langen Liste von Bestellungen oder Produktverkäufen können Sie mit dieser Berechnung ermitteln, welche Produkte Sie am seltensten verkaufen. Damit lassen sich beispielsweise Ladenhüter ermitteln.

Gleiches gilt für die Identifizierung und Analyse von:

• Kunden, die nur sehr selten bei Ihnen kaufen.
• Lagerteilen, die kaum gebraucht werden.
• Fahrzeuge, die selten genutzt werden.
• Störfälle, die so gut wie nie vorkommen.