Wie heißt es doch: Traue keiner Statistik, die Du nicht selbst gefälscht hast…
Excel bietet eine Vielzahl von statistischen Funktionen. Wer bei diesem Begriff jetzt zusammenzuckt und mit Schrecken an gruselige Statistik-Vorlesungen im Studium zurückdenkt, den kann ich beruhigen: So schlimm wird es nicht.
Einige dieser Funktionen sind durchaus alltagstauglich, wie z.B. MAX oder MIN zur Bestimmung des größten oder kleinsten Wertes einer Tabelle.
Wie ermittle ich jedoch den oder die am häufigsten vorkommenden Wert(e) in einer Liste? Auch hierbei läßt Excel einen nicht im Stich:
- MODALWERT
- MODUS.EINF
- MODUS.VIELF
Und so geht’s:
Der häufigste Wert
Nehmen wir also an, ich habe eine große Matrix mit vielen Werten und möchte wissen, welcher davon am häufigsten vorkommt. Das könnte z.B. eine Liste mit Schulnoten aus einer Klassenarbeit sein, eine Alterstabelle, eine Einkommenstabelle, was weiß ich.
Ich habe eine kleine Beispieldatei vorbereitet, die du dir gerne hier herunterladen kannst. Der Einfachheit halber habe ich mit Hilfe der Funktion ZUFALLSBEREICH() eine Tabelle erstellt, welche – wie der Name der Funktion verrät – zufällige Werte zwischen 10 und 100 enthält:
=ZUFALLSBEREICH(10;100)
Beispieltabelle mit ZUFALLSBEREICH()
Hinweis:
Mit jeder Eingabe in der Tabelle bzw. beim Drücken der Funktionstaste F9 werden automatisch neue Zufallswerte berechnet.
Nun möchte ich gerne wissen, welche Zahl am häufigsten vorkommt. Dazu verwende ich die Funktion MODUS.EINF:
=MODUS.EINF(Zahl1;Zahl2;...)
Aus Kompatibilitätsgründen mit alten Excel-Versionen existiert alternativ auch noch die Funktion MODALWERT, die den gleichen Zweck erfüllt:
=MODALWERT(Zahl1;Zahl2;...)
Anstelle einer Liste von Zahlen kann natürlich auch ein Zellenbezug angegeben werden:
Den häufigsten Wert ermitteln
Zur besseren Übersicht habe mit Hilfe der bedingten Formatierung die häufigste Zahl farblich hervorgehoben:
Regel für die bedingte Formatierung
Wer jetzt noch wissen möchte, wie oft die häufigste Zahl vorkommt, kann das ganz leicht mit der ZÄHLENWENN-Funktion herausfinden:
Wie oft kommt der häufigste Wert vor?
Die häufigsten Werte
Was ist, wenn es nicht nur eine Zahl gibt, die am häufigsten vorkommt, sondern gleich mehrere, die gleich häufig sind? Für diesen Fall verwende ich die folgende Excel-Funktion:
=MODUS.VIELF(zahl1;Zahl2;...)
Auch hier benutze ich zur Verdeutlichung wieder die bedingte Formatierung:
Die häufigsten Werte berechnen
Was hier zu beachten ist:
Es handelt sich um eine Matrix-Funktion. Da ich ja mehrere Werte ausgeben möchte, muss ich zunächst den Eingabebereich mit der Maus markieren (in meinem Beispiel also C24:C26), dann die Funktion eingeben und mit der Tastenkombination STRG+Umschalt+ENTER abschließen.
Excel schließt die Matrix-Funktion automatisch in geschweifte Klammern ein (diese dürfen nicht von Hand eingegeben werden, sondern nur über die oben genannte Tastenkombination).
Noch zwei Hinweise zum Schluß:
Sollte nur eine Zahl die häufigste sein, so wird diese in allen drei Zellen dargestellt:
Nur eine Zahl ist die häufigste
Sollten nur zwei Zahlen am häufigsten vorkommen, wird in der dritten Zelle nur der Fehlerwert #NV angezeigt.
Nur zwei Zahlen sind die häufigsten
Zur Klarstellung: Es können natürlich durchaus noch andere Zahlen mehrfach in der Tabelle vorhanden sein, aber eben keine mehr, die genauso oft vorkommt, wie die häufigste(n).
Mit diesem Wissen sollte deine nächste Auswertung ein Kinderspiel sein!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Super – wie funktioniert das Ganze wenn wir keine Zahlen sondern Text in den Zellen haben ?
Hallo Daniel,
eine einfache Lösung gibt es dafür nicht, da alle statistischen Funktionen nur mit Zahlenwerten arbeiten. Denkbar wäre ein Konstrukt über eine Hilfsspalte, in der mit ZÄHLENWENN die Anzahl eines jeden Textwertes ermittelt wird und dann über eine Kombination aus KGRÖSSTE und INDEX der dazu passende Wert ausgegeben wird:
Aber das ist halt auch nur eine Krücke.
Schöne Grüße,
Martin
Lieber Martin,
vielen Dank für deine hilfreichen Tipps. Leider konnte ich bisher keine Lösung für folgendes Problem finden:
Ich habe eine Spalte mit Werten, von denen einige mehrfach und zum Teil auch gleichhäufig vorkommen können. Meine Aufgabe ist nun, den häufigsten Wert zu finden. Sobald allerdings zwei oder mehr Werte mit der gleichen (und maximalen) Häufigkeit auftreten, soll aber nicht der oberste ausgegeben werden, sondern der größte.
Als Beispiel möchte ich folgendes Anführen: Folgende Werte stehen untereinander in einer Spalte:
2
3
2
2
3
3
4
1
1
Ich suche eine Funktion (oder Kombination von Funktionen, am liebsten ohne Hilfsspalte) die mir ermittelt welche Zahl am häufigsten auftritt und falls mehrere gleichhäufig sind (hier: 2 und 3) die größte von ihnen ausgibt (hier: 3).
Ich hoffe Du kannst mir bei meinem Problem helfen.
Vielen Dank und viele Grüße
Nils
Ich suche eine Möglichkeit MODUS.EINF auch mit „Teilergebnis“ zu verbinden. Hat jemand eine Idee?
Hallo Picco,
im Zusammenhang mit der TEILERGEBNIS-Funktion ist MODUS.EINF nicht möglich. Aber du könntest dir stattdessen die AGGREGAT-Funktion ansehen. Hier gibt es als Parameter auch MODUS.EINF, vielleicht hilft das ja weiter (AGGREGAT ist erst seit Excel 2010 verfügbar).
Schöne Grüße,
Martin
Hallo Martin,
gibt es in Excel auch eine Formel für folgendes Problem:
SpalteA: Postleitzahl
SpalteB: Strassenname
SpalteC: Kundennummer
SpalteD: Verkäufer
Ich möchte wissen, welcher Verkäufer in Postleitzahl „xxxxx“ in der Strasse „XY“ anzahlmässig die meisten Kunden hat.
Also Suche nach den meisten Treffern mit 2 Kriterien.
Vielen Dank im Voraus
Hallo Daniel,
mit einer Formel dürfte das vermutlich schwer zu lösen sein, zumal es ja auch sein könnte, dass es vielleicht mehrere Treffer gibt. Ich würde stattdessen eine Pivot-Tabelle verwenden: PLZ und Straße in den Filterbereich, Verkäufer in den Zeilenbereich, Anzahl Kunden in den Wertebereich. Wenn es sehr viele Verkäufer gibt, kannst du dann noch einen Top-10-Wertefilter darüberlegen und auf den höchsten Wert filtern.
Schöne Grüße,
Martin
=INDEX(D1:D100;MAX(WENN(MAX(ZÄHLENWENNS(A1:A100;A1:A100;B1:B100;B1:B100))=ZÄHLENWENNS(A1:A100;A1:A100;B1:B100;B1:B100);ZEILE(A1:A100);)))
Hallo lieber Martin,
Danke schon mal für den guten Text oben, nun stellt sich mir ein kleines Problem. Wie bekomme ich mehrere häufige Werte in einer Liste auf meinem Excel. Du erklärst nur, wie ich mehrere gleichhäufige Werte bekommen kann. Aber was ist nun wenn ich die 10 häufigsten Werte will? (also z.b. nr.1 330 mal, nr. 2 250 mal etc..)
Lg Lukas
Hallo Lukas,
wenn die Daten in einer Liste vorliegen, würde ich eine Pivot-Tabelle einsetzen und im Wertebereich mit der Anzahl arbeiten. Anschließend kannst du ganz bequem einen Top-10-Filter setzen.
Schöne Grüße,
Martin
Hallo Martin,
ich habe eine Excel-Tabelle in der ich eine Marktübersicht über den Amazon Bücher-Markt habe. In der Liste befinden sich Daten wie zum Beispiel Titel, Preis, Verlag, Autor, etc. Es handelt sich dabei um ca. 5000 Datensätze. Nun möchte ich gerne wissen, welche 5 Autoren am häufigsten in der Liste vorkommen. Ich habe aber leider keine Ahnung, wie ich das herausfinden kann. Ich habe es schon mit der Modalwertfunktion getestet, jedoch ist diese bei mir immer im #NV geendet. Bei dem Auto handelt es sich ja nicht um einen numerischen Wert. Funktioniert das trotzdem?
Beste Grüße
Alex
Hallo Alex,
die einfachste Möglichkeit ist eine Pivot-Tabelle. Dann brauchst du dich nämlich überhaupt nicht mit irgendwelchen Formeln herumzuschlagen:
Zieh den Autor in den Zeilenbereich und irgendein Textfeld (z.B. Titel) in den Wertebereich. Damit werden alle Titel pro Autor gezählt. Danach kannst du die Pivot-Tabelle entweder nach der Anzahl sortieren oder alternativ einen Top-Ten-Filter darüberlegen.
Schöne Grüße,
Martin
Hallo lieber Martin,
das zeigt mir mal wieder, dass es in Excel immer wieder was Neues zu lernen gibt. Diese Funktionen hatte ich bisher noch gar in auf dem Schirm!
Vielen Dank
Thomas
Hallo Thomas,
das ist natürlich auch nicht gerade die gängigste Funktion, aber manchmal ist es gut zu wissen, dass es so etwas gibt 🙂
Schöne Grüße
Martin