☰

Vergleich mehrerer Spalten, um den häufigsten Wert zurückzugeben

tag-icon • tag-icon microsoft-excel
Vergleich mehrerer Spalten, um den häufigsten Wert zurückzugeben

Ich habe 6 Spalten mit Zeichenfolgen und suche nach der am häufigsten vorkommenden Zeichenfolge in allen 6 Spalten.

Für jede Hilfe wäre ich sehr dankbar.

Spalte1 Spalte2 Spalte3 Spalte4 Spalte5 Spalte6
Apfel Orange Banane Kiwi Brownie Brokkoli
Orange Banane Kiwi Brownie Brokkoli
Banane Kiwi Brownie Brokkoli
Kiwi Brownie Brokkoli
Brownie Brokkoli
Brokkoli

Das Ergebnis wäre Brokkoli. Wenn in Spalte 1, Zeile 6 Brokkoli nicht vorhanden wäre, wäre das Ergebnis Brownie/Brokkoli.

Spalte1 Spalte2 Spalte3 Spalte4 Spalte5 Spalte6
Arrowroot Artischocke Rucola Spargel Bambussprossen Bohnen
Rüben Möhren Sellerie Brownie Brokkoli
Banane Kiwi Möhren Knoblauch
Schokoladenstückchen Brot Käse
Saft Pop
Chips

Das Ergebnis wären Karotten.

Antwort1

Folgendes würde funktionieren:

=LET(Source,A2:F7,
     ShortList,UNIQUE(FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN(",",TRUE,Source),",","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner")),
     Occurrences,COUNTIF(Source,ShortList),

 TEXTJOIN("/",TRUE,SORT(IF(Occurrences=MAX(Occurrences),ShortList,""))))

Es verwendet es TEXTJOIN(), um die gesamte Liste zusammenzusetzen und Leerzeichen zu verlieren. Dann wird FILTERXML()es mit einem Trick in HTML umgewandelt und in ein Array aufgeteilt, das Excel als solches erkennt. UNIQUE()Anschließend wird eine Liste mit einzelnen Instanzen aller vorhandenen Werte erstellt.

Anschließend COUNTIF()wird es verwendet, um die Anzahl der einzelnen eindeutigen Elemente zu ermitteln, MAX()den höchsten Wert aus der Liste dieser Anzahlen zu ermitteln und die IF()Anzahl der einzelnen eindeutigen Elemente mit dem Maximalwert zu vergleichen, um qualifizierte Ergebnisse zu finden. SORT()Diese qualifizierten Ergebnisse werden in alphabetischer Reihenfolge angeordnet.

Schließlich TEXTJOIN()nimmt es die qualifizierten Ergebnisse und formatiert sie für die gewünschte Ausgabezeichenfolge.

(Wenn Sortieren nicht erwünscht (oder nicht notwendig) ist, editieren Sie diese Funktion einfach raus. Ich nahm an, dass es wünschenswert wäre (da ich mir dachte, dass die Zeichenfolge „Brownie/Broccoli“ nicht in Stein gemeißelt war, sondern nur eine schnelle Anzeige des gewünschten Ergebnisses) und dachte, dass es klarer ist, sie einzufügen und sie dann rauszueditieren, als sie nicht einzufügen und einfach zu sagen „Dann sortieren Sie es“ und es Ihnen zu überlassen, das herauszufinden.)

Das LET()ist einfach und logisch aufgebaut. „Einfach“ insofern, als der zu bearbeitende Bereich direkt am Anfang steht und nur dort liegt, sodass er leicht zu bearbeiten ist. In der vorliegenden Version gibt es keine weiteren Änderungen, daher geht es weiter mit der Zwischenberechnung. Namen in einer Bottom-up-Darstellung (vielleicht besser beschrieben als „von innen nach außen“, um ihre Vorrangstellung in der Formel zu beschreiben). Und schließlich die resultierende Arbeitsformel.

Antwort2

Sie können dies auch mit einer benutzerdefinierten Funktion tun, die in VBA geschrieben ist

Die Eingabe dieser UDF ist einfach:

Um diese benutzerdefinierte Funktion (UDF) einzugeben,

  • <alt-F11>öffnet den Visual Basic-Editor.
  • Stellen Sie sicher, dass Ihr Projekt im Fenster „Projekt-Explorer“ hervorgehoben ist.
  • Wählen Sie dann im oberen MenüInsert => Module
  • Fügen Sie den untenstehenden Code in das sich öffnende Fenster ein.

Um diese benutzerdefinierte Funktion (UDF) zu verwenden, geben Sie eine Formel wie =mostFrequent(A1:F6)in eine beliebige Zelle ein.

Option Explicit
Function mostFrequent(r As Range) As Variant()
    Dim arr As Variant, dict As Object
    Dim v
    Dim result(1)
    
'read range into vba array for faster processing
arr = r
Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = TextCompare
    
'read into dictionary and get the count of each item
For Each v In arr
    If Len(v) > 0 Then
        If Not dict.Exists(v) Then
            dict.Add Key:=v, Item:=1
        Else
            dict(v) = dict(v) + 1
        End If
    End If
Next v

'find max count
For Each v In dict.Keys
    If dict(v) > result(1) Then
        result(0) = v
        result(1) = dict(v)
    End If
Next v

'return most frequent string and it's count
mostFrequent = result
    
End Function

Bildbeschreibung hier eingeben

Algorithmus

  • Lesen Sie den Bereich zur schnellsten Verarbeitung in ein VBA-Array
  • Tragen Sie jede Zeichenfolge in ein Wörterbuch ein, in dem
    • Schlüssel = die Zeichenfolge
    • Wert = die Anzahl dieser Zeichenfolge
  • Gibt die Zeichenfolge mit der höchsten Anzahl zurück
  • Im folgenden Code geben wir tatsächlich ein 2-Element-Array zurück, wobei das zweite Element die Anzahl der Elemente ist
    • Sie können dies optional mithilfe der dynamischen Array-Funktion oder der Indexfunktion zurückgeben, abhängig von Ihrer Excel-Version.

verwandte Informationen