Statistik leicht gemacht: Den häufigsten Wert ermitteln 13

Artikelbild-58
Mit ein paar einfachen Excel-Funktionen lassen sich die häufigsten Werte in einer Tabelle ermitteln
 

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()

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

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

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?

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

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

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

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!

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

13 Gedanken zu “Statistik leicht gemacht: Den häufigsten Wert ermitteln

    • Avatar-Foto
      Martin Weiß

      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:
      Häufigster Texteintrag

      Aber das ist halt auch nur eine Krücke.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Nils

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Daniel Trocker

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Funke

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Alex

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Thomas Cramer

    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

    • Avatar-Foto
      Martin Weiß

      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