Hallo zusammen,
ich habe folgendes Problem- und zwar habe ich eine Tabelle mit Personen, die, je nachdem, bis zu 4 Erkrankungen haben.
Beispielhaft:
#1 #2 #3 #4
C00 C00 C44 C55
C00 C08 C02
C01 C18
C08 C44 C55
C02 C08 C00 C55
C01 C00 C55
Nun würde ich gerne via Excel ermitteln, welche Kombinationen am häufigsten vorkommen- unabhängig von der jeweiligen Spalte.
Im Beispiel wäre das z.B.
Die Kombination C00 und C08 kommt 2x vor
Die Kombination C00 und C55 kommt 3x vor....
ect.
Weiß jemand, ob dies überhaupt mit Excel möglich ist und wenn, wie ich das am geschicktesten anstelle?
F1: C00
G1: C08
H1: =SUMME(WENNFEHLER(--NACHZEILE(A$1:D$6;LAMBDA(a;UND(VERGLEICH(F1:G1;a;))));))
Hier noch eine durchgängige Lösung in einer einzigen Formel auf Deine Frage - mit der ganzen Power von kaskadiertem NACHZEILE, die aus den sonst nur 2 möglichen Dimensionen hier 4, 5 oder 6 macht (je nach Sichtweise):
H1#:
=LET(
c;A1:D6;
d;ZEILEN(c );
e;SPALTEN(c );
x;WEGLASSEN(SORTIEREN(EINDEUTIG(INDEX(c;SEQUENZ(d*e;;e)/e;REST(SEQUENZ(d*e;;0);e)+1)));-1);
n;ZEILEN(x);
y;WAHL({1.2};INDEX(x;SEQUENZ(n^2;;n)/n);INDEX(x;REST(SEQUENZ(n^2;;n);n)+1));
z;FILTER(y;INDEX(y;;1)<INDEX(y;;2));
v;HSTAPELN(z;NACHZEILE(z;LAMBDA(b;SUMME(WENNFEHLER(--NACHZEILE(c;LAMBDA(a;UND(VERGLEICH(b;a;))));)))));
SORTIEREN(FILTER(v;INDEX(v;;3));3;-1))
mit dem Ergebnis:
C00 C55 3
C00 C02 2
C00 C08 2
C02 C08 2
C08 C55 2
C44 C55 2
C00 C01 1
C00 C44 1
C01 C18 1
C01 C55 1
C02 C55 1
C08 C44 1
Vielen Dank!
Bin ein wenig "geflashed" von dem Monstrum an Befehl
Nun ists so, dass ich, je nach Möglichkeit, nochmals deine Hilfe brauche.
Und zwar verstehe ich den Befehl in keiner Weise..
Wie sieht es aus, wenn ich nicht nur die meisten 2er Kombinationen, sondern gar 3er oder 4er Kombinationen bestimmen möchte?
Gruß ExcelAnfänger12
Die Formel hätte man bis vor ca. 12 Jahren nur via VBA gelöst, und bis vor ca. 6-12 Monaten als Alternative mit Power Query (PQ).
Und Dein Nicht-Verstehen kann hier absolut jeder nachvollziehen. Zu den einzelnen Schritten der scheinbaren Monsterformel, die aber in Wirklichkeit eine UDF wie in VBA oder PQ darstellt:
=LET(
c;A1:D6; Dieses ist die einzige Variable, die von außen kommt: Nämlich Dein flatternder Bereich.
d;ZEILEN(c ); Von dem bestimme ich nun dessen Höhe 6 ...
e;SPALTEN(c ); ... und Breite 4.
x;WEGLASSEN(SORTIEREN(EINDEUTIG(INDEX(c;SEQUENZ(d*e;;e)/e;REST(SEQUENZ(d*e;;0);e)+1)));-1);
Hier passieren 4 Dinge: 1. Der INDEX stellt alle Einträge untereinander (4x6=24 Stück) 2. Doppler werden gelöscht. 3. Die Einträge werden sortiert 4. Der Leereintrag am Ende (das ist eine 0, die übrigbleibt, wenn Dein Flattern mind. 1 Leerzelle aufwies) wird gelöscht. Du könntest natürlich diese Schritte auch in 4 eigenen Zeilen darstellen, was den Vorteil hätte, dass Du sie einzeln wiedergeben ("debuggen") könntest.
n;ZEILEN(x); Das Zwischenresultat sind 7 verschiedene Einträge.
y;WAHL({1.2};INDEX(x;SEQUENZ(n^2;;n)/n);INDEX(x;REST(SEQUENZ(n^2;;n);n)+1)); Diese stelle ich nebeneinander: 7^2=49
z;FILTER(y;INDEX(y;;1)
Damit keine Kombi 2mal vorkommt, nehme ich nur die, wo der linke Eintrag kleiner ist. Es verbleiben 21 von 49 Einträgen (n*(n-1)/2).
v;HSTAPELN(z;NACHZEILE(z;LAMBDA(b;SUMME(WENNFEHLER(--NACHZEILE(c;LAMBDA(a;UND(VERGLEICH(b;a;))));)))));
Jetzt nehme ich mir mit c wieder den Bereich A1:D6 zur Hand. Denn dessen "Zeilen" sollen ja Grundlage für das Zählen der enthaltenen 2er-Kombis sein. UND(VERGLEICH(b;a;)) liest also von UND(VERGLEICH(F1:G1;A1:D1;)) bis UND(VERGLEICH(F1:G1;A6:D6;)). Diese 6 Einträge #FEHLER! und WAHR werden mit dem inneren NACHZEILE gebildet und summiert. Danach passiert das mit F2:G2 ... F21:G21 in einer äußeren (NACHZEILE) Schleife genauso. Dieses F1:G21 habe ich gerade für Dich "erfunden": Das steckte mittlerweile nämlich schon in der Variablen z, also nur im Hauptspeicher von Excel. Es gehört gar nicht mehr, so wie noch bei der ersten Lösung, in die Zellen F1:G21. Diese 21 Summen klebe ich nun hinter die 21 Einträge, so dass ich ein 21x3-Array erhalte.
SORTIEREN(FILTER(v;INDEX(v;;3));3;-1))
In dem 21x3-Array lösche ich nun noch die Null-Zählungen (anhand Spalte 3) und sortiere nun nach Häufigkeit absteigend (wieder anhand Spalte 3). Jetzt sind wir fertig.
Das gleiche klappt auch mit 3er oder 4er Kombinationen. Dafür muss ich im Wesentlichen die Zuweisung y ändern. Und anderswo kleine Dinge ändern, die ich bei dieser gewollten weiteren Dimension auch gleich beachtet hätte.
Ich habe gleich einen Termin und bin ca. 13 Uhr zurück. Dann mache ich mich da noch mal dran - und ziehe alle Zwischenschritte auseinander, damit Du tatsächlich debuggen kannst.
Ich könnte mir vorstellen, dass bis dahin ein PQ'ler auch eine Lösung bringt.