Live-Forum - Die aktuellen Beiträge
Datum
Titel
14.10.2025 09:39:44
VBA - Excel speichern und schließen (Laufzeitfehler 91)
13.10.2025 19:31:31
VBA Formel mit Bezug auf Überschrift anstatt Spalte
13.10.2025 15:10:17
Überlappende Zeiträume über mehrere Tabellen mit leeren Zell
13.10.2025 13:34:56
PQ Abfrage mit verschiedenen Werten ausführen + Protokoll
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1660to1664
1660to1664
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Rangliste der häufigsten Einträge eines Bereiches

Forumthread: Rangliste der häufigsten Einträge eines Bereiches

Rangliste der häufigsten Einträge eines Bereiches
11.12.2018 17:23:55
Toljan
Hallo zusammen,
ich bräuchte dringend Hilfe für folgendes Problem:
Ich möchte für einen Bereich mit x Spalten und y Zeilen (in jeder Zelle steht genau ein Wort) ein Ranking für die häufigsten Nennungen erstellen.
Für eine Spalte funktioniert dies mit Hilfe folgender Matrixformel schon sehr gut:
{=WENN(ANZAHL2(A:A)=SUMME(ZÄHLENWENN(A$1:A$200;B$1:B1));ZEICHEN(160);INDEX(A:A; VERGLEICH(KGRÖSSTE(ZÄHLENWENN(A$1:A$200;A$1:A$200);1+SUMME(ZÄHLENWENN(A$1:A$200;B$1:B1))); WENN(ZÄHLENWENN(B$1:B1;A$1:A$200)=0;ZÄHLENWENN(A$1:A$200;A$1:A$200));0))) }
Dies gibt mir in Spalte B ein Top 5 Ranking der meist genannten Wörter in Spalte A Zeile 1-200 wieder.
Kann mir jemand weiterhelfen wie ich diese Formel erweitern kann auf x-Spalten?
Alle meine bisherigen versuche haben nicht zum Ziel geführt.
Vielen Dank schon einmal im Voraus.
Toljan
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit einer Matrixfunktion(alität)sformel ...
11.12.2018 19:53:28
neopa
Hallo Toljan.
... könntest Du das evtl. ermitteln. Und zwar wenn nur die TOP 5 zu ermitteln sind (also nur 5 Formeln), der auszuwertende Bereich wirklich nicht größer ist als max 200 Zeilen und max auch ca. 20 Spalten (besser jeweils viel weniger).
Anderenfalls wird die Berechnung zu langsam. Wenn Dir das so nicht reicht, solltest Du nach einer VBA-Lösung oder auch nach einer PowerQuery-Lösung nachfragen.

Unter vorgenannten Bedingungen für Daten in A1:X200 folgende Formel z.B. in Z2 (Z1 leer sein oder ein beliebigen anderen Text beinhalten):
=INDEX(A:X;
AGGREGAT(15;6;ZEILE(A$1:A$200)/
(AGGREGAT(14;6;(ZÄHLENWENN(A$1:X$200;A$1:X$200)-ZEILE(A$1:A$200)%%-SPALTE(A1:X1)%)*(ZÄHLENWENN(Z$1:Z1;A$1:X$200)=0);1)=ZÄHLENWENN(A$1:X$200;A$1:X$200)-ZEILE(A$1:A$200)%%-SPALTE(A1:X1)%);1);
AGGREGAT(15;6;SPALTE(A$1:X$1)/
(AGGREGAT(14;6;(ZÄHLENWENN(A$1:X$200;A$1:X$200)-ZEILE(A$1:A$200)%%-SPALTE(A1:X1)%)*(ZÄHLENWENN(Z$1:Z1;A$1:X$200)=0);1)=ZÄHLENWENN(A$1:X$200;A$1:X$200)-ZEILE(A$1:A$200)%%-SPALTE(A1:X1)%);1))
und Formel 4 Zellen nach unten kopieren. Diese werden dann wohl ein paar Sekunden zu rechnen haben.
Gruß Werner
.. , - ...
Anzeige
Es geht auch mit Fmln einfacher, ...
12.12.2018 02:31:25
Luc:-?
…Toljan,
zB so:
Userbild
Diese Auswertungsdaten basieren auf einer BspMatrix mit 20 Zeilen und 10 Spalten, die die 27 Buchstaben­Namen des griechischen RepräsentativZiffernAlfabets in zufälliger Anordnung und Anzahl enthält. Die pluralen MatrixFmln in den 4 Spalten lauten (unsicht­bare grau):
V1:V27: {=SVERWEIS(X1:X27;W1:Y27;3;0)}
W1:W27: {=ZÄHLENWENN($A$18:$J$37;Y1:Y27)+(27-ZEILE(X1:X27))/100}
X1:X27: {=KGRÖSSTE(W1:W27;ZEILE(W1:W27))}
Y1:Y27: {=MTRANS(VSplit(VJoin($A$18:$J$37;;-1)))}
mit Format (V1:W27): Standard
und Format (X1:Y27): # ##0
Die letzte Fml basiert auf der Anwendung 2er VBA-basierter Standard-UDFs aus dem Archiv, die in den neuesten Xl-Versionen inkl Abo-Version 365 ggf durch neue Xl-Fktt ersetzt wdn könnten.
Um den PlatzBedarf zu minimieren und gleichzeitig ein weiteres Bsp für die sinnvolle Anwendung von VerbundZellen zu liefern, wur­den die beiden nicht informationsrelevanten Hilfsspalten jeweils als verborgene 2.SpaltenZelle der beiden zeilenweisen Verbund­Zellen angelegt. Um das zu erreichen, muss für jeden Verbund zuerst ein vorformatiertes leeres Muster per Verbinden über (Verbin­den-Menü!) angelegt wdn. Dieses wird dann mit dem FormatPinsel auf den jeweiligen ErgebnisBereich übertragen.
Feedback nicht unerwünscht! Morhn, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …
Anzeige
AW: Rangliste der häufigsten Einträge eines Bereiches
12.12.2018 11:51:32
Daniel
Hi
bei VBA-gut folgender Vorschlag:
kopiere dir diesen Code in ein allgemeines Modul: 
Function HäufigsteWörter(Bereich) As Variant
Dim dic As Object
Dim X
Dim Worte
Dim Anzahl, A
Dim i As Long
Dim Erg() As String
Set dic = CreateObject("Scripting.dictionary")
'--- Häufigkeiten pro Wort ermitteln
For Each X In Bereich
X = CStr(X)
dic(X) = dic(X) + 1
Next
Worte = dic.keys
Anzahl = dic.Items
'--- Häufigkeit eindeutig machen
For i = 0 To UBound(Anzahl)
Anzahl(i) = Anzahl(i) + 1 / (i + 2)
Next
'--- Ergebnisausgabe
ReDim Erg(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
For i = 1 To WorksheetFunction.Min(UBound(Erg), dic.Count)
A = WorksheetFunction.Large(Anzahl, i)
Erg(i, 1) = Worte(WorksheetFunction.Match(A, Anzahl, 0) - 1)
If UBound(Erg, 2) > 1 Then Erg(i, 2) = Int(A)
Next
HäufigsteWörter = Erg
End Function
dir steht dann die Funktion "HäufigsteWörter" zur Verfügung.
zur Eingabe:
für die Top5 markiere einen Zellbereich der 5 Zeilen hoch und 2 Spalten breit ist
gib als Formel ein: =HäufigsteWörter(A1:X200)
schließe die Eingabe mit STRG+SHIFT+ENTER ab.
der Zellbereich wird dann mit den Ergebnissen ausgefüllt.
in der ersten Zeile steht das häufigste Wort, in der zweiten das zweithäufigste usw.
in der Spalte daneben steht dann die jeweilige Anzahl.
wenn du die nicht brauchst dann markiere den Zellbereich für die Ergebnisausgabe nur eine Spalte breit.
Diese Formel sollte auch bei größeren Datenmengen zügig berechnet werden.
Gruß Daniel
Anzeige
Hat dir Bernds UDF nicht gefallen? :-] owT
12.12.2018 16:09:21
Luc:-?
:-?
welche von den gezeigten meinst du?
12.12.2018 23:31:50
den
hast du mal versucht, mit seinen UDFs die Aufgabe: Anzeige der 5 häufigsten Werte (nicht alle Werte)
zu erledigen?
AW: Hat dir Bernds UDF nicht gefallen? :-] owT
14.12.2018 18:37:51
Toljan
Hallo Daniel,
vielen Dank für die Hilfe! Diese Option kannte ich noch nicht.
Perfekte Lösung für das Problem!!
Gruß Toljan
Anzeige
;

Forumthreads zu verwandten Themen

Unterschiedliche Werte mit Bedingung
kerberus

Hallo liebes Forum
Ich benötige mal wieder eure Hilfe. Das Ziel: eine Formel die mir die Anzahl unterschiedliche Eintragungen liefert. Folgender Code konnte ich bis jetzt erstellen:
Function ZUnterschiede(Kriterium As String, Kriteriumbereich As Range, Wertebereich As Range) _ As D...
22.08.2008 10:34:00
Thread-ID: 1003771
3 Beiträge
Zelleninhalt (Text) in 2 Spalten teilen
El.

Hallo!
Habe eine Spalte in der Artikelname und Artikelmerkmale, durch drei Punkte getrennt "...", zusammen stehen. Beispiel: Lecker Whisky 21 Jahre ... Lieferung in hochwertiger Schatulle
Wie krieg ich nun den Text links der Punkte in eine extra Spalte und rechts der Punkte in eine extr...
03.09.2008 16:35:00
Thread-ID: 1007155
4 Beiträge
Spaltentrennzeichen nach speichern in Text-Datei
Michael

Hallo
ich habe ein Problem. Vielleicht ist es schwierig, vielleicht aber auch nicht.
Ich habe eine Text-Datei mit Spaltentrennzeichen "|" (besser als ";" der Übersichtlichtlichkeit halber) und verschiedenen Längen der Spalten.
Ich will diese in Excel überarbeiten und als Text-Dat...
11.09.2008 09:28:00
Thread-ID: 1008978
3 Beiträge
Text in Spalten
Simone

Hallo zusammen,
ich hab ein wohl eher triviales Problem, komme aber trotzdem einfach nicht weiter.
Ich füge in ein Arbeitsblatt Daten aus einem anderen Programm ein. Jetzt habe ich mehrmals die Funktion 'Text in Spalten' verwendet / ausprobiert, und wenn ich jetzt wieder Daten in A1 ein...
16.09.2008 13:54:00
Thread-ID: 1010092
3 Beiträge
Werte zählen mit Bedingung
Hugo

Hallo,
in Spalte K sollen die in Spalte H aufeinderfolgenden Werte unter Bedingung gezählt werden. Habe händisch eingetragen wie gezählt werden soll. Auch ein kommentar beschreibt wie ich mir denken könnte wie es zu definieren ist. Andere Ideen sind natürlich willkommen.
Danke und Grus...
02.10.2008 18:53:00
Thread-ID: 1013949
4 Beiträge
Anzeige
TextBox Werte in Spalte Q ersetzen ?
Selma

Hallo Leute,
ich habe eine Frage zu UserForm. Beim Klick auf CommandButton1 sollen die Zahlen aus TextBox1 mit Werten in Spalte Q ab Zeile 12 ersetzt werden. Dies soll nur dann erfolgen, wenn in TextBox1 die Zahlen stehen bzw. der TextBox gefüllt ist.
Ersetzt sollen nur die vorhandene W...
13.10.2008 15:42:00
Thread-ID: 1016083
3 Beiträge
Text in Spalten
Tom

Hallo,
ich möchte mit der Funktion Text in Spalten erreichen, dass alle Zeilen, die mit einer Zahl beginnen, nach der Zahl in eine neue Spalte geschoben werden - nur finde ich hierfür nur die Funktionen nach Leerzeichen, Komma usw...
Wer hat ne Lösung?
In A1: 1020 Maier Huber Mülle...
20.10.2008 14:09:42
Thread-ID: 1017654
7 Beiträge
Pivot - Text in Spalte überführen
Binu

Hallo
Ich hab in der Beispieldatei die Spalte "Zahl", welche ich in einer Pivotdatei ebenfalls als Spalte neben der Spalte "TExt" hinzufügen will. Nur kriege ich das nicht hin. Kann mir da jemand helfen? Besten Dank!
Gruss Albin
https://www.herber.de/bbs/user/56492.xls
04.11.2008 11:04:36
Thread-ID: 1021516
4 Beiträge
Liste auswerten mit Bedingungen
Bernd

Hallo zusammen,
ich würde gerne mal wieder eine lange Excel auswerten mit verschiedenen Bedingungen. Die relevanten Datensätze, auf die die Suchbedingung zutreffen, sollten in einem Extra-Registerblatt chronologisch aufgelistet werden. Wie kann ich das Problem vorzugsweise mit Excel-Boardmi...
19.11.2008 10:01:00
Thread-ID: 1025891
6 Beiträge
Text in Spalte nicht Spalte sondern Bereich
mehmet

Hallo Forum,
ich habe mit dem Makrorekorder "Text in Spalte" aufgezeichnet.
Wollte diese nun ändern für ein Bereich (Q4:Q60) statt Spalte (Q:Q).
Warum geht das nicht?
Private Sub CommandButton1_Click() ' Makro1 Makro ' Makro am 20.11.2008 von pc aufgezeichnet Sheets("Tabelle4"...
20.11.2008 17:10:38
Thread-ID: 1026492
3 Beiträge
Text autom. in Excel-Spalten einsortieren
Annalena49

Hallo,
habe eine (für mich) ungewöhnliche Datendatei, ähnlich CSV ist aber keine CSV. Hier ein Beispiel einer Excel-Zeile:
10153-8158 10194 1023007 1025250000 102950600 1030LP 1032EUR 1029880000 1032EUA 11010100001 1102UM 110105586724 1102KN 11019783815860847 1102EN 11013815860849 110...
05.12.2008 20:54:02
Thread-ID: 1030586
3 Beiträge
Text in einer Spalte formatieren
Martin

Hallo
in einer Zelle habe ich sehr viel Text hineingeschrieben. Möchte für den Ausdruck die Spalte aber nur ca. 5 cm breit formatieren. Der Text soll dann automatisch Linie für Linie untereinander zu stehen kommen. Keine Ahnung wie das möglich ist. Wenn ich die Spalte schmaler formatiere bl...
04.01.2009 10:57:00
Thread-ID: 1036687
5 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Rangliste der häufigsten Einträge in Excel erstellen

Schritt-für-Schritt-Anleitung

Um in Excel eine Rangliste der häufigsten Einträge eines Bereiches zu erstellen, kannst Du verschiedene Methoden nutzen. Hier ist eine Schritt-für-Schritt-Anleitung, die eine Matrixformel sowie VBA-Optionen beschreibt.

  1. Vorbereitung der Daten: Stelle sicher, dass Deine Daten in einem zusammenhängenden Bereich stehen, z.B. A1:X200.

  2. Matrixformel für die häufigsten Werte:

    • Gib in Zelle B1 folgende Matrixformel ein (beachte, dass Du die Formel mit STRG + SHIFT + ENTER bestätigen musst): 
      {=WENN(ANZAHL2(A:A)=SUMME(ZÄHLENWENN(A$1:A$200;B$1:B1));ZEICHEN(160);INDEX(A:A;VERGLEICH(KGRÖSSTE(ZÄHLENWENN(A$1:A$200;A$1:A$200);1+SUMME(ZÄHLENWENN(A$1:A$200;B$1:B1)));WENN(ZÄHLENWENN(B$1:B1;A$1:A$200)=0;ZÄHLENWENN(A$1:A$200;A$1:A$200));0)))}
    • Kopiere diese Formel vier weitere Zellen nach unten (B2 bis B5), um die Top 5 häufigsten Wörter zu ermitteln.

  3. VBA-Methode:

    • Öffne den VBA-Editor (ALT + F11), füge ein neues Modul ein und kopiere folgenden Code: 
      Function HäufigsteWörter(Bereich) As Variant
 Dim dic As Object
 Dim X
 Dim Worte
 Dim Anzahl, A
 Dim i As Long
 Dim Erg() As String
 Set dic = CreateObject("Scripting.dictionary")
 For Each X In Bereich
     X = CStr(X)
     dic(X) = dic(X) + 1
 Next
 Worte = dic.keys
 Anzahl = dic.Items
 For i = 0 To UBound(Anzahl)
     Anzahl(i) = Anzahl(i) + 1 / (i + 2)
 Next
 ReDim Erg(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
 For i = 1 To WorksheetFunction.Min(UBound(Erg), dic.Count)
     A = WorksheetFunction.Large(Anzahl, i)
     Erg(i, 1) = Worte(WorksheetFunction.Match(A, Anzahl, 0) - 1)
     If UBound(Erg, 2) > 1 Then Erg(i, 2) = Int(A)
 Next
 HäufigsteWörter = Erg
End Function
    • Du kannst die Funktion verwenden, indem Du in einen Zellbereich der Größe 5x2 die folgende Formel eingibst: 
      =HäufigsteWörter(A1:X200)
    • Bestätige die Eingabe wieder mit STRG + SHIFT + ENTER.

Häufige Fehler und Lösungen

  • Fehler bei der Matrixformel: Wenn Du eine Fehlermeldung erhältst, stelle sicher, dass Du die Eingabe mit STRG + SHIFT + ENTER abgeschlossen hast.
  • Langsame Berechnungen: Bei großen Datenmengen kann die Berechnung sehr langsam werden. Reduziere den Bereich (z.B. auf 100 Zeilen) oder verwende die VBA-Methode.
  • Falsche Ergebnisse: Stelle sicher, dass alle Zellen im Bereich keine leeren Werte enthalten, da dies das Ergebnis verfälschen kann.

Alternative Methoden

  1. Power Query: Eine weitere Möglichkeit, häufigste Werte in Excel zu ermitteln, ist die Verwendung von Power Query. Du kannst die Daten importieren, transformieren und dann eine Abfrage erstellen, um die häufigsten Einträge zu zählen.

  2. Pivot-Tabellen: Erstelle eine Pivot-Tabelle, um die Häufigkeit der Einträge zu zählen und dann die Ergebnisse nach Häufigkeit zu sortieren.

Praktische Beispiele

  • Beispiel 1: Angenommen, Du hast in Spalte A Namen von Teilnehmern einer Veranstaltung. Mit der Formel in Zelle B1 kannst Du den häufigsten Namen ermitteln.
  • Beispiel 2: In einem Bereich mit Verkaufszahlen kannst Du den häufigsten Verkaufsartikel mit der VBA-Funktion HäufigsteWörter ermitteln.

Tipps für Profis

  • Nutze die Funktion AGGREGAT in Kombination mit KGRÖSSTE, um flexibler mit den Daten umzugehen.
  • Bei großen Datenmengen, ziehe in Betracht, Deine Daten in kleinere Bereiche aufzuteilen, um die Leistung zu steigern.
  • Halte Deine Formeln so einfach wie möglich, um die Lesbarkeit und Wartbarkeit zu verbessern.

FAQ: Häufige Fragen

1. Wie kann ich den häufigsten Wert in einer Spalte ermitteln? Verwende die Formel =INDEX(A:A;VERGLEICH(MAX(ZÄHLENWENN(A:A;A:A));ZÄHLENWENN(A:A;A:A);0)).

2. Was ist der Unterschied zwischen der Matrixformel und der VBA-Methode? Die Matrixformel ist direkt in Excel anwendbar, während die VBA-Methode mehr Flexibilität und eine benutzerdefinierte Funktion bietet, die sich besser für große Datenmengen eignet.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige