Einzelwerte durch Teamwerte ersetzen

[Robi]

Liebe Excel-Gurus,
da ich hier neu bin, begrüsse ich euch alle erst einmal herzlich.

Für mein Problem kann man sich ein "Mannschafts-Einzelzeitfahren" in einem Radrennen vorstellen, wobei jedem Fahrer anstelle seiner selbst erreichten Zeit die Zeit des schlechtesten Fahrers seines Teams zugewiesen werden soll.

Beispiel: (die Zeiten sind hier einfach durch Zahlen ersetzt, da es in meinem "richtigen" Problem natürlich nicht um ein Radrennen geht)

1. Fahrer aus Team A: 120 ----> 130
2. Fahrer aus Team A: 115 ----> 130
1. Fahrer aus Team B: 119 ----> 125
1. Fahrer aus Team C: 121 ----> 121
3. Fahrer aus Team A: 130 ----> 130
4. Fahrer aus Team A: 125 ----> 130
2. Fahrer aus Team B: 125 ----> 125
2. Fahrer aus Team C: 117 ----> 121

Hinter allen Fahren des Teams A soll in einer Kolonne der Wert 130 stehen; "verursacht" durch den 3. Fahrer des Teams A,
hinter allen Fahren des Teams B soll in einer Kolonne der Wert 125 stehen; "verursacht" durch den 2. Fahrer des Teams B ...
hinter allen Fahren des Teams C soll in einer Kolonne der Wert 121 stehen; "verursacht" durch den 1. Fahrer des Teams C ...

Randbedingingen meines "echten" Problems:
- Es gibt maximal 20 Fahrer (in dem Falle halt nur ein einziges Team), also auch maximal 20 Teams (in dem Falle bestehend dann aus nur je einem Fahrer).
- Es dürfen keine Array-Formeln verwendet werden!

Ich habe bereits eine Lösung, die mit 2x20 Hilfskolonnen auskommt. Irgendwie habe ich den Eindruck, dass ich mir auf dem Schlauch stehe und es eine raffinierte Kombination mit nur einer einzigen Kolonne (dem Resultat selber) geben könnte.

Vielen Dank und schöne Grüsse,
Robi

[WillWissen]

Hi Robi,

um dir adäquat helfen zu können und unnötige Rückfragen nicht gestellt werden müssen, bitte ich dich, eine Beispieltabelle, die deinem Originalproblem gleicht, hochzuladen. Daten können anonymisiert werden.

Und so funktioniert das Hochladen einer Datei: Dateien zur Verfügung stellen

Alternativ kann auch ein relevanter Tabellenausschnitt gepostet werden: Tabellenausschnitt posten

[shift-del]

Hallo Robi

Es dürfen keine Array-Formeln verwendet werden!

Warum nicht?

Ich habe bereits eine Lösung, die mit 2x20 Hilfskolonnen auskommt.

Die solltest du auch mal zeigen.

[Robi]

Hallo shift-del,

Es dürfen keine Array-Formeln verwendet werden!
Warum nicht?

Die Tabelle muss später noch sortiert werden können.

Die Excel-Datei ist jetzt (hoffentlich) angehängt.
In Spalte A sind die Teams (Buchstaben A bis S sind möglich, da max. 20 Fahrer oder Teams vorkommen).
In Spalte B sind die Einzelwerte der Fahrer.
Über der gelben Matrix stehen die möglichen Team-Buchstaben A bis S.
In der gelben Matrix steht die Zeit der einzelnen Fahrer in der entsprechenden Spalte; andernfalls Null.
Über der gelben Matrix stehen nochmals die möglichen Team-Buchstaben A bis S.
In der grünen Matrix steht der Maximalwert der Kolonne des Teams, falls der Einzelwert nicht Null ist.
In Spalte AQ steht dann das Maximum der grünen Reihe, also der Teamwert.

Die Spalten C bis AP kann ich natürlich verstecken.
Mit dieser Variante können die 20 Reihen beliebig sortiert werden, ohne eine Effekt auf das Resultat zu haben.

Vielen Dank und Gruss,
Robi

[Rabe]

Hi zusammen,

Frage an die Formel-Experten:

Es dürfen keine Array-Formeln verwendet werden!
Warum nicht?

Die Tabelle muss später noch sortiert werden können.

können Array- oder Matrix-Formeln echt nicht sortiert werden?

[Robi]

Hi Rabe,

du kannst in meinem oben angehängten Teamwerte.xlsm spasseshalber die 20 Werte der grünen Spalte A, also von W2:W21 selektieren, in den Editor-Modus gehen und mit shift-ctrl-enter abschliessen. Es wird dann überall der Wert 130 stehen, aber darum geht es nicht. Versuche dann, die Tabelle nach Spalte A (den Teams) zu sortieren. Geht nicht.

Gruss,
Robi

[shift-del]

Hallo Robi

du kannst in meinem oben angehängten Teamwerte.xlsm spasseshalber die 20 Werte der grünen Spalte A, also von W2:W21 selektieren, in den Editor-Modus gehen und mit shift-ctrl-enter abschliessen.

Damit kopierst du die Formel aus W2 in die anderen Zellen.

	A	B	C
1	TeamName	Einzelwerte	Teamwert
2	A	120	130
3	A	115	130
4	B	119	125
5	C	121	121
6	A	130	130
7	A	125	130
8	B	125	125
9	C	117	121

Zelle	Formel
C2	{=MAX(WENN($A$2:$A$21=A2;$B$2:$B$21))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Robi]

Hallo shift-del,

perfekte Lösung! (Matrixformeln in einzelnen Zellen sind auch kein Problem beim Sortieren).

Vielen Dank,
Robi

[Rabe]

Hallo Robi,

perfekte Lösung! (Matrixformeln in einzelnen Zellen sind auch kein Problem beim Sortieren).

ich verstehe dein Problem immer noch nicht:

Nur weil Du die Tastenkombination SHIFT-STRG-ENTER drückst, machst Du aus einer Formel nicht immer eine Matrix-Formel. Vor allem, weil Du bei einem solchen "Entern" in einem markierten Bereich nicht die in jeder Zelle stehende Formel in eine Matrix-Formel umwandelst, sondern nur die Formel aus Zelle AQ2 in die anderen Zellen 1:1 reinkopierst. Wenn da zufällig eine Matrix-Formel drin steht, ist halt dann die identische Formel (mit identischen Zellbezügen) in allen Zellen drin. Das hat Dir ja auch Detlef schon erklärt (wobei statt W2 die Zelle AQ2 gemeint war):

Damit kopierst du die Formel aus W2 in die anderen Zellen.

Darum steht da dann natürlich auch immer der identische Wert.

Wenn Du einen Bereich mit einer Matrix-Formel haben willst, mußt Du die erste Zelle des Bereichs in eine Matrix umwandeln und dann die somit erhaltene Matrix-Formel in die restlichen Zellen AQ3 bis AQx kopieren (mit STRG-C und STRG-V, oder mit STRG-ENTER), dann werden auch die Bezüge angepasst.