Office: (Office 365) Wie kann ich die x häufigsten Ausdrücke aus einer Spalte ermitteln?

Nach umfangreicher Recherche und einigem Probieren konnte ich meine Anforderung noch nicht erfüllen. Nun hoffe ich auf Eure Unterstützung.

Für ein Tippspiel importieren wir regelmäßig aus einer CSV eine kontinuierlich größer werdende Anzahl von Daten. Diese enthalten z.B. in einer Spalte A die vorhergesagte Anzahl von Toren für Team1 und in Spalte B die Anzahl von Toren für Team2. Daraus berechne ich auf einem Tabellenblatt eine Spalte mit dem zusammengesetzten Ergebnis. Aus 3 (Team1) und 1 (Team2) wird also der Ausdruck "3:1". Die Formel hierfür:

Code:

=WENNFEHLER(TEXTKETTE(tippspiel[@[Tore für Team1]];":";tippspiel[@[Tore für Team2]]);"")

Aus dieser Spalte möchte ich die 5 häufigsten Ausdrücke ermitteln.

Gelungen ist mir, den 1 häufigsten Ausdruck zu ermitteln, wenn ich den Bereich manuell eingrenze:

Code:

{=INDEX(D1:D500;VERGLEICH(MAX(ZÄHLENWENN(D1:D500;D1:D500));ZÄHLENWENN(D1:D500;D1:D500);0))}

Setze ich den oberen Wert sehr hoch an (z.B. D5000), ist das Ergebnis leer. Gebe ich die gesamte Spalte D:D an, ist das Ergebnis 0 (null).

Wie aber kann ich die 5 häufigsten Ausdrücke ermitteln, ohne die Anzahl der Zeilen aus der CSV bzw. der nicht leeren Ausdrücke in der berechneten Spalte zu kennen?

 

Hi,

welche Excelversion hast Du denn im Einsatz?

 

Hi {Boris}, ich verwende M365 (der neueste Name ist "Microsoft 365 Copilot"). Insofern war die für den Thread zur Auswahl stehende Angabe "Office 365" nicht wirklich exakt. Sorry dafür.

 

Moin!
Spalte D
Einfügen, PivotTable
Ergebnis in Zeilen und Werte
Werte (automatisch ist Anzahl gewählt) absteigend sortieren.

Gruß Ralf

 

Auch wenn er weg ist, der @tabulator
Hier mal 2 Formellösungen für Excel 365.
Ich habe bewusst das Trennzeichen "-" für das Ergebnis gewählt, weil Excel standardmäßig das ":" als Trennzeichen für eine Zeit interpretiert.

	A	B	C	D
1	Ergebnis		Ergebnis	Top_5
2	1-3		0-3	4
3	2-1		2-1	3
4	0-3		3-3	2
5	3-3		1-1	2
6	2-1		2-3	2
7	1-1
8	3-3		Ergebnis	Top_5
9	0-3		0-3	4
10	2-3		2-1	3
11	0-3		0-0	2
12	1-0		1-0	2
13	0-1		1-1	2
14	3-2
15	2-0
16	2-1
17	1-0
18	1-1
19	2-0
20	0-2
21	0-0
22	3-1
23	1-2
24	2-3
25	0-0
26	0-3

Zelle	Formel
C2	=LET(ex;EINDEUTIG(A2:A26);ZEILENWAHL(SORTIEREN(HSTAPELN(ex;ZÄHLENWENN(A2:A26;ex));2;-1);SEQUENZ(5)))
C9	=ZEILENWAHL(SORTIEREN(GRUPPIERENNACH(A2:A26;A2:A26;ANZAHL2;0;0);2;-1);SEQUENZ(5))

 

Hi @RPP63_neu , der Tabulator ist nicht weg, aber krank . Danke für Deine Vorschläge. Sie sehen für mich besser aus als die Pivot-Lösung. Ich werde sie in den nächsten heute oder morgen mal auf meine Daten anwenden und hier Feedback geben, versprochen

 

...da hätte ich auch noch was...
Richtig sichtbar wird das, wenn man tatsächlich mehr als nur 5 Erstplazierte hat. Mit 3 6ern, 4 Fünfern usw... die will man ja wahrscheinlich alle wissen....

Nachteil von PQ gegenüber Formeln: Man muss die sofortige Aktualisierung bei Änderungen anstoßen. Hab sie hier auf minütliche Aktualisierung gesetzt. Wem 1 Minute zu lang ist, der muss manuell aktualisieren, wem 1 Min. ausreicht, muss sich halt solange gedulden...
Oder ein kleines Makro schreiben....

 

Tatsächlich gehe ich mit @R J , vielen Dank. Ich verwende ohnehin schon Power Query, um die aus der DB als CSV exportierten Tippspiel-Daten auszuwerten. Sehr performant, leicht anpassbar und funktioniert mit der automatischen Aktualisierung einwandfrei.

 

Yepp, deswegen ja auch mein erster Vorschlag mit der "ungeliebten" Pivot.
Da werden dann alle Ergebnisse absteigend nach Häufigkeit gelistet.
Es gab in der gesamten Bundesliga-Historie lediglich 74 verschiedene Ergebnisse.

Quelle:
Fußball: Vom Zauber der Zahlen: Welches Ergebnis gibt es wie häufig?

[break und offtopic]
Ich habe mal ein wenig mit Google Sheets experimentiert.
Dort gibt es SORTN(), welche "besser" als SORTIERENNACH() in Excel ist.

Ergebnisse in Spalte A ab A2 (A1 Überschrift)
Folgende Formel in C2:

Code:

=LET(
uni;UNIQUE(A2:INDEX(A:A;COUNTA(A:A)));
anzErg;ARRAYFORMULA(COUNTIF(A:A;uni));
temp;HSTACK(uni;anzErg);
SORTN(temp;D1;1;2;0))

ergibt für die Top 4:

 

Oder:
=LET(x;SORTIEREN(GRUPPIERENNACH(Tabelle1[Ergebnis];Tabelle1[Ergebnis];ANZAHL2;0;0;;;1);2;-1;0);FILTER(x;INDEX(x;;2)>=KGRÖSSTE(INDEX(x;;2);5)))

 

...uups... Dank HSV's Lösung ist mir erst aufgefallen, dass ich die Abfolge der Abfrageschtitte falsch hatte. Die beiden letzten Abfrageschritte müssen natürlich getauscht werden! Danke @HSV!