Microsoft Excel

Herbers Excel/VBA-Archiv

Rangliste der häufigsten Einträge eines Bereiches


Betrifft: Rangliste der häufigsten Einträge eines Bereiches
von: Toljan
Geschrieben am: 11.12.2018 17:23:55

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

  

Betrifft: AW: mit einer Matrixfunktion(alität)sformel ...
von: neopa C
Geschrieben am: 11.12.2018 19:53:28

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
.. , - ...


  

Betrifft: AW: Rangliste der häufigsten Einträge eines Bereiches
von: Sulprobil
Geschrieben am: 11.12.2018 20:01:40

Hallo,

Ich würde VBA verwenden:
http://sulprobil.com/Get_it_done/IT/Excel_Fun/Excel_VBA/ListFreq/listfreq.html

Viele Grüße,
Bernd P


  

Betrifft: Es geht auch mit Fmln einfacher, ...
von: Luc:-?
Geschrieben am: 12.12.2018 02:31:25

…Toljan,
zB so:

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 …


  

Betrifft: AW: Rangliste der häufigsten Einträge eines Bereiches
von: Daniel
Geschrieben am: 12.12.2018 11:51:32

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


  

Betrifft: Hat dir Bernds UDF nicht gefallen? :-] owT
von: Luc:-?
Geschrieben am: 12.12.2018 16:09:21

:-?


  

Betrifft: welche von den gezeigten meinst du?
von: Daniel
Geschrieben am: 12.12.2018 23:31:50

hast du mal versucht, mit seinen UDFs die Aufgabe: Anzeige der 5 häufigsten Werte (nicht alle Werte)
zu erledigen?


  

Betrifft: AW: Hat dir Bernds UDF nicht gefallen? :-] owT
von: Toljan
Geschrieben am: 14.12.2018 18:37:51

Hallo Daniel,

vielen Dank für die Hilfe! Diese Option kannte ich noch nicht.
Perfekte Lösung für das Problem!!

Gruß Toljan