Gruppen sortieren

[Noah_M]

Hallo zusammen, 

Ich möchte aus eine Berechnung erstellen, wie aus 6 Gruppen á 4 Namen 4 Gruppen á 6 Namen werden, basierend auf einer Rangliste in der die 24 Namen aufgelistet sind.


  Testdatei.xlsx (Größe: 9,22 KB / Downloads: 13)

Ich habe hier eine Testdatei erstellt.
Aus den Gruppen A bis F in Zellen A2 bis A5, B2 bis B5 etc. soll jeweils ein Name in die unterhalb aufgeführten Gruppen 1 bis 4, in der Reihenfolge der in Spalte G aufgeführten Namen übernommen werden.
Sprich im Beispiel soll aus Gruppe A "Name 4" in Gruppe 1 (Zelle A9), "Name 10" in Gruppe 2 (Zelle B9), "Name 15" in Gruppe 3 (Zelle C9), "Name 17" in Gruppe 4 (Zelle D9) auftauchen. 

Aus Gruppen B bis F dann natürlich das gleiche. Der in Spalte G am höchsten gelistete Name in Gruppe 1 (Zellen A10 bis 14), der zweit höchste in Gruppe 2 (Zellen B10 bis 14), etc.

Ich suche nun nach einer Formel, mit der ich die Zellen A9 bis D14 berechnen lassen kann.

Meine einzige Idee ist eine verschachtelte WENN-Funktion. Da diese allerdings sehr viele WENN's haben müsste und dadurch sehr fehleranfällig wäre bräuchte ich eine Alternative.

Hat da jemand eine Idee zu?

Vielen Dannk schonmal im voraus.

LG Noah

[BoskoBiati]

Hi,

in A9, nach rechts und unten ziehen:

Code:

=INDEX($G$1:$G$24;AGGREGAT(15;6;ZEILE($G$1:$G$24)/($G$1:$G$24=MTRANS(A$2:A$5));ZEILE($A1)))

Achte bitte darauf, dass die Namen überall gleich geschrieben sind (z.B.: Name 1)!

[Noah_M]

In der Testdatei funktioniert das super - danke schonmal dafür.

Könntest du mir eventuell die Funktion noch erklären?

Ich verstehe zum Beispiel nicht, wofür das letzte ZEILE($A1) gut ist.

Die MTRANS Funktion ist ja dafür da, dass aus Spaltensortierung eine Zeilensortierung wird, oder?
Die Formel Sortiert dennoch nach Spalte - das ist aber nicht schlimm, dann sind die Gruppen 1 bis 4 eben in einer Zeile aufgelistet statt in einer Spalte. Dann könnte man das MTRANS auch weglassen, oder? Ich habe die AGGREGAT Funktion allerdings noch nicht vollständig verstanden, daher weiß ich noch nicht wie.

Vielen Dank für die Hilfe

Lg Noah

[BoskoBiati]

Hi,

sieh Dir das an:


  Testdatei(1).xlsx (Größe: 10,39 KB / Downloads: 10)

[BigJane]

Funktioniert die Formel bei BoskoBiati bei dir wirklich wie gefordert? Bei mir nicht - baut deine Gruppenmatrix falsch herum auf.

Etwas editiert:

Code:

=INDEX($G$1:$G$24;AGGREGAT(15;6;ZEILE($G$1:$G$24)/($G$1:$G$24=MTRANS(INDIREKT(ADRESSE(2;ZEILE(A9)-8)&":"&ADRESSE(5;ZEILE(A9)-8))));SPALTE(A$1)))

Und wie schon genannt müssen die Namen exakt gleich sein also ohne Leerzeichen am Ende wie bei Name 1 und Name 2 im Gruppenbereich

[BoskoBiati]

Hallo Jane,

Sieh Dir doch einfach mal meine Datei  an.
Mit Indirekt würde ich sowieso nicht arbeiten, weil die Funktion volatil ist.

@Noah,
Zeile(A1) ist einfach eine Zählvariable für die Aggregat-Funktion.
Mtrans macht aus der senkrechten Matrix eine waagerechte, sonst funktioniert die Formel nicht.

[BigJane]

Ich habe mir deine Datei angeschaut und er will Name 10 in B9 und nicht in A10 haben.

Das mit dem Indirekt ist ja auch nur ein Vorschlag.

[Noah_M]

Moin, ja genau, die Formel hatte die Matrix falsch herum aufgebaut, was aber auch nicht schlimm gewesen wäre.

Zu deiner Formel:

Die macht im Test-Dokument das was sie soll.

Nur wenn ich das jetzt auf mein reguläres Dokument übertragen möchte, und dabei lediglich die Zellbezüge ändere, erhalte ich immer die Fehlermeldung #ZAHL.

Ich bin kein Anfänger was Excel angeht, aber ich habe bisher weder mit INDEX, noch mit AGGREGAT oder MTRANS gearbeitet.

Die INDEX Formel ist relativ einfach zu verstehen, in der Verschachtelung ist sie diesem Fall dafür da, dass ein Wert pro Zelle ausgefüllt wird, und keine gesamte Matrix, korrekt?

MTRANS ist ja prinzipiell sehr einfach, das ändert einfach eine Horizontale Auflistung in eine Vertikale (oder andersrum). 

Der Rest der Formel ist die AGGREGAT Formel. Direkt zu Beginn 15 besagt, dass der Kkleinste Wert aus der später benannten Matrix (in dem Fall G1 bis G24) genommen wird (korrekt?) 
K wird dann mit welchem Wert festgelegt? Die 6 besagt, dass Fehlerwerte ignoriert werden sollen, sprich in Zelle A8 im Beispiel sollen nur die Werte aus A2 bis A5 berücksichtigt werden.

Den Rest der Formel verstehe ich allerdings (noch) nicht. Woher weiß die Formel, dass sie in Zellen A8 bis D8 die Zellen A2 bis A5 berücksichtigen soll? Dieser Bereich ist für mich ersichtlich nirgends gekennzeichnet

Besteht die Möglichkeit, dass du mir die Formel einmal aufgeschlüsselt erklärst?
Ansonsten baue ich mein reguläres Dokument um das Test-Dokument auf. Ist dann zwar nochmal etwas arbeit, aber dann ist das so.

Vielen dank und LG
Noah

[BoskoBiati]

Hi,

mach mal eine Datei, die dem Original entspricht  und ich sehe sie mir an, wenn ich zuhause bin. Ansonsten kann ich das mit der falschen Reihenfolge nicht erklären, siehe meine Datei.

[Noah_M]

Ich habe nun mal meine Originaldatei bis auf den entsprechenden Bereich geleert und die Namen "geschwärzt".

Ich habe beide eure Formeln versucht auf die Bereiche Anzupassen, die ich benötige, bekomme aber in beiden Fällen die Fehlermeldung #ZAHL

Mit der Formel von Edgar:

  TEST 1.xlsx (Größe: 12,81 KB / Downloads: 4)

Mit der Formel von Jane:

  TEST 2.xlsx (Größe: 12,98 KB / Downloads: 2)

Vielen Dank für die Hilfe.

Lg Noah