Wie ermitteln Sie in Excel die zweithäufigste Zahl oder den zweithäufigsten Text?

Diese Lösung nutzt eine Matrixformel, um die zweithäufigste Zahl in einem begrenzten Bereich zu ermitteln. Matrixformeln sind ideal, wenn Sie mit einer einzigen Formel direkt ein Ergebnis erhalten möchten – sie funktionieren jedoch zuverlässig nur bei numerischen Werten. Für Textwerte ist eine andere Methode erforderlich. Beachten Sie, dass Matrixformeln je nach Excel-Version (vor Office 365 und Excel 2019) ggf. mit Strg + Umschalt + Enter (statt nur Enter) bestätigt werden müssen.

Wählen Sie eine leere Zelle aus, in der das Ergebnis erscheinen soll, und geben Sie die folgende Formel ein:

=MODE(IF(A2:A31<>MODE(A2:A31),A2:A31,""))

Drücken Sie Strg + Umschalt + Enter, um die Formel als Matrixformel zu bestätigen. Excel fügt bei erfolgreicher Eingabe automatisch geschweifte Klammern um die Formel ein – ein klares Zeichen dafür, dass sie korrekt als Matrixformel erkannt wurde.

In der Zelle wird dann die zweithäufigste Zahl bzw. der zweithäufigste Wert aus Ihrem Bereich angezeigt. Dieser Ansatz eignet sich effizient für numerische Listen, funktioniert jedoch nicht bei Textwerten und berücksichtigt zudem keine eindeutige Logik zur Auflösung von Gleichständen, falls mehrere Werte dieselbe Häufigkeit aufweisen.

Tipps & Hinweise: Stellen Sie sicher, dass Ihr Bereich (im Beispiel A2:A31) Ihren tatsächlichen Daten entspricht. Falls weniger als zwei unterschiedliche Zahlen in der Liste vorhanden sind, kann diese Formel entweder einen Fehler oder unerwartete Ergebnisse liefern. Wenn Sie mit Office 365, Excel 2019 oder einer neueren Version arbeiten, können Sie gegebenenfalls dynamische Matrixformeln verwenden und müssen lediglich die Eingabetaste drücken.

Diese Methode führt Sie Schritt für Schritt durch die Zählung der Vorkommen jedes Elements, sodass Sie mithilfe des Tools „Erweiterte Zeilen zusammenführen“ von Kutools für Excel die zweithäufigsten Elemente ermitteln und anschließend die Vorkommen sortieren können, um den zweithäufigsten Textwert zu finden.

1. Markieren Sie die Spalte mit dem Text oder den Zahlen, die Sie analysieren möchten, sowie eine leere benachbarte Spalte zur Erfassung der Vorkommen. Wechseln Sie anschließend zu Kutools > Inhalt > Erweiterte Zeilen zusammenführen.
Hinweis: Vergeben Sie für bessere Übersichtlichkeit eine aussagekräftige Überschrift wie „Vorkommen“ für die Hilfsspalte.

2. Legen Sie im Dialogfeld „Erweiterte Zeilen zusammenführen“ Ihre gewählte Spalte als Primärschlüssel fest und konfigurieren Sie in der Hilfsspalte die Option zum Zählen der Vorkommen. Klicken Sie auf OK, um zu bestätigen.

3. Wählen Sie die Spalte mit den Vorkommenszahlen aus und gehen Sie dann zu Daten > Sortieren von größten zu kleinsten, um die Werte absteigend nach ihrer Häufigkeit anzuordnen.

4. Falls eine Sortierwarnung erscheint, wählen Sie unbedingt Auswahl erweitern und klicken Sie auf Sortieren. Dadurch werden Ihre Zeilen so organisiert, dass das zweithäufigste Element in der dritten Zeile erscheint.

Vorteile: Diese Methode eignet sich hervorragend sowohl zum Aufteilen nach Text und Zahl und ermöglicht es Ihnen, nicht nur den zweit-, sondern auch den dritt-, viert- und so weiter häufigsten Wert zu ermitteln.

Das Sortieren von Werten nach Häufigkeit mithilfe integrierter Tools oder Add-Ins wie Kutools für Excel ist eine der einfachsten und anschaulichsten Methoden, um den zweithäufigsten Wert zu ermitteln – besonders bei großen Datensätzen.

1. Wählen Sie Ihre Zieldatenspalte aus und öffnen Sie dann Enterprise > Erweitertes Sortieren.

2. Konfigurieren Sie das Dialogfeld „Erweitertes Sortieren“: Wählen Sie Ihre Spalte aus der Dropdown-Liste „Spalte“ aus, legen Sie Häufigkeit als Sortierkriterium fest und stellen Sie die Reihenfolge auf Z bis A ein.

3. Klicken Sie auf OK. Die Werte werden automatisch von den häufigsten zu den seltensten sortiert – so erkennen Sie den zweithäufigsten Wert sofort in der Liste. Diese Methode funktioniert nahtlos sowohl für Zahlen als auch für Text, vereinfacht den Vergleich und kommt ganz ohne Formeln aus.

Für Anwender, die repetitive Aufgaben automatisieren oder manuelle Einrichtung vermeiden möchten, liefert ein benutzerdefiniertes VBA-Makro effizient den zweithäufigsten Wert aus jedem beliebigen Bereich – unabhängig davon, ob dieser Zahlen, Text oder eine Kombination aus beidem enthält. VBA-Lösungen sind besonders praktisch, wenn Sie diese Analyse regelmäßig über mehrere Arbeitsblätter hinweg durchführen oder auf Hilfsspalten verzichten möchten.

1. Klicken Sie im Excel-Menüband auf Entwicklertools > Visual Basic. Wählen Sie im neu geöffneten Fenster Microsoft Visual Basic für Applikationen die Option Einfügen > Modul, und kopieren Sie den folgenden Code in das Modul:

Sub SecondMostFrequentValue()
    Dim dict As Object
    Dim rng As Range
    Dim cell As Range
    Dim freqArr As Variant
    Dim i As Integer
    Dim j As Integer
    Dim keyArr As Variant
    Dim tempF As Variant
    Dim tempK As Variant
    
    Set dict = CreateObject("Scripting.Dictionary")
    xTitleId = "KutoolsforExcel"
    
    On Error Resume Next
    Set rng = Application.Selection
    Set rng = Application.InputBox("Range", xTitleId, rng.Address, Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            dict(cell.Value) = dict(cell.Value) + 1
        End If
    Next
    
    If dict.Count < 2 Then
        MsgBox "Not enough unique values.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    freqArr = dict.Items
    keyArr = dict.Keys
    
    For i = 0 To dict.Count - 2
        For j = i + 1 To dict.Count - 1
            If freqArr(j) > freqArr(i) Then
                tempF = freqArr(i)
                tempK = keyArr(i)
                freqArr(i) = freqArr(j)
                keyArr(i) = keyArr(j)
                freqArr(j) = tempF
                keyArr(j) = tempK
            End If
        Next j
    Next i
    
    MsgBox "Second most frequent value: " & keyArr(1) & vbCrLf & "Times: " & freqArr(1), vbInformation, xTitleId
End Sub

2. Klicken Sie auf die Schaltfläche (das grüne Dreieck), um die Ausführung zu starten. Anschließend erscheint ein Dialogfeld, in dem Sie Ihren Zielbereich auswählen sollen. Markieren Sie Ihre Daten und bestätigen Sie – der zweithäufigste Wert sowie seine Vorkommensanzahl werden dann angezeigt.

Ein PivotTable ist eine äußerst flexible und benutzerfreundliche Möglichkeit, große Datensätze in Excel zu analysieren und zusammenzufassen. Damit zählen Sie mühelos die Vorkommen jedes Werts, sortieren sie und ermitteln im Handumdrehen den ersten, zweiten oder sogar dritten häufigsten Wert – ganz gleich, ob Ihre Daten numerisch oder textbasiert sind. Diese Methode ist ideal für Anwender, die auf integrierte Excel-Funktionen setzen möchten, ohne Formeln eingeben oder Add-Ins verwenden zu müssen.

1. Wählen Sie eine beliebige Zelle in Ihrem Datensatz aus und klicken Sie dann auf Einfügen > PivotTable. Legen Sie im Dialogfeld Ihren gewünschten Bereich fest und wählen Sie aus, wo die PivotTable platziert werden soll (z. B. ein neues Arbeitsblatt).

2. Ziehen Sie Ihre Datenspalte (z. B. „Produktname“ oder „Werte“) sowohl in den Bereich Zeilen als auch in den Bereich Werte des Feldbereichs der PivotTable. Ändern Sie den „Werte“-Bereich in „Anzahl von [Spaltenname]“.

3. Klicken Sie auf den Dropdown-Pfeil der Spalte „Zeilenbeschriftungen“ innerhalb der PivotTable und sortieren Sie sie von größter zu kleinster Häufigkeit. Der häufigste Wert erscheint ganz oben, der zweithäufigste direkt darunter.

Tipp: Mit diesem Ansatz visualisieren Sie die Häufigkeitsverteilung kinderleicht. Die Tabelle lässt sich zudem jederzeit filtern oder automatisch aktualisieren, sobald sich Ihre Daten ändern. Beachten Sie: Leere Einträge werden ebenfalls mitgezählt – filtern Sie diese gegebenenfalls heraus, um präzisere Ergebnisse zu erhalten.

Vergleich: Im Gegensatz zu formelbasierten Lösungen benötigt die PivotTable-Methode keine komplexe Syntax oder fortgeschrittene Excel-Kenntnisse und eignet sich hervorragend für dynamische, wachsende Datensätze.

Den am häufigsten vorkommenden Wert finden aus einer Liste in Excel

Den Modus für Textwerte aus einer Liste/Spalte in Excel ermitteln