Office: (Office 365) Zelle aus Bereich auslesen, Wert aus nebenstehender Zelle in andere Zelle kopieren.

Moin Ihr Spezialisten.
Ich habe mit eine Exceltabelle gebaut in der ich jedes Jahr für ein großes Schützenfest den Sitzplan für die Vereine eintrage.
Nun möchte ich den Plan automatisieren, was in Teilen auch schon geklappt hat, aber in anderen Teilen eben nicht.
Info:
Die Beispieldatei ist viel kleiner als der Original Plan. Im Original reden wir über
- 7 Zelte
- ca 750 Tische a 8 Plätze
- ca 70 Vereine
- ca 6000 Schützen die sitzen möchten.

Im Bereich B2:J9 (Zelt 1) und L2:T9 (Zelt 2) sind durch die Nummern 1 bis 6 die Tische den Vereinen zugeordnet.
Die Namen der Vereine werden von Hand in der Liste jeweils darunter eingetragen.
Die Platznummern in Spalte B und L stehen fest.
Diese Konstellation welcher Verein wo sitzt ändert sich jährlich.
Die Rechnung, wieviele Sitzplätze jeder Verein hat habe ich per Formel in F und P gelöst.

Jetzt komme ich an meine Grenzen.
Jeder Verein gehört zu einer festen Gruppe, an der sich nichts ändert. (Spalte Z)
Diese Spalte trage ich von Hand ein. Ich möchte das in Spalte AC in der Zelle hinter jedem Verein in den einzelnen Gruppen automatisch die zugewiesene Platznummer eingetragen wird.
für Verein 6 sollte die Formel also in Zelle AC2 automatisch aus dem Bereich B10:T15 die Zelle C15 ermitteln und dann der Wert aus B15 angezeigt werden.

noch komlizierter wird es bei der Marschfolge (SpalteAH). Die obersten 5 Vereine wandern jedes Jahr ans Ende der Liste. Die Liste rotiert also. Diese Spalte (AI) trage ich von Hand ein. Die Meldungen der Vereine mit wievielen Schützen sie kommen trage ich in Spalte AL ein.
Ich möchte das aus dem bereich B10:T15 die zugewiesene Sitzplatzanzahl in Spalte AM und Platznummern in Spalte AN eingetragen wird.
Ich weiss da nicht weiter, und hoffe auf Eure Hilfe.

 

Hallo Jaeger72,

als erstes ändere in Deiner eingestellten Datei in Spalte AC und AN von Text in Standard!
Dann (vorausgesetzt Du hast zumindest XL2010 in Einsatz) folgende Formel in AC2:

=WENN(Z2="";"";INDEX(A:L;AGGREGAT(15;6;ZEILE(C$10:C$15)/(C$10:N$15=Z2);1);AGGREGAT(15;6;SPALTE(B$1:L$1)/(C$10:N$15=Z2);1)))

diese ziehend nach unten kopieren.

In AM2:

=INDEX(A:P;AGGREGAT(15;6;ZEILE(C$10:C$15)/(C$10:N$15=AI2);1);AGGREGAT(15;6;SPALTE(F$1:P$1)/(C$10:N$15=AI2);1))

und in AN2:

=INDEX(A:L;AGGREGAT(15;6;ZEILE(C$10:C$15)/(C$10:N$15=AI2);1);AGGREGAT(15;6;SPALTE(B$1:L$1)/(C$10:N$15=AI2);1))

und diese nach unten kopieren.

Gruß Werner
.. , - ...

 

Moin

Die Vereine sind nur drei mal aufgelistet? Doch geht doch sicher mehr, oder?
Hier mal eine ganz aufwändige Lösung. Nur für professionelle Profis.

 

Hallo Detlef,

Du hast im Tabellenblatt1 meine Formeln übernommen. Daran dass diese mit {} geklammert sind , erkenne ich in meiner XL2016er Version, dass Du XL365 im Einsatz hast, wo die {} automatisch gesetzt werden, was in älteren XL-Versionen nicht der Fall ist, weil nicht notwendig.

Woran erkenne ich im hiesigen Forum, mit welcher XL-Version der TE arbeitet?
Meine Formeln setzen 1:1 das um, was der TE gefragt hat.
Die Umsetzung für seine ca. 6000 Teilnehmer erfordert dann natürlich noch entsprechende Anpassung.

 

Wenn es die Fragestellerin angegeben hat oben im Kopfbereich der Seite.

 

Hallo,

wenn @Jaeger72 die aktuellste Version von Excel365 (Excel365 Insider Beta) hätte, würde sich das Problem wesentlich vereinfachen:
Es wären dann insgesamt genau 3 Formeln einzugeben:
In Zelle AC2:
=LET(x;$B$10:$J$15; y;$L$10:$T$15; z;VSTAPELN(x;y);
XVERWEIS($Z$2:$Z$15;INDEX(z;;2);INDEX(z;;1);"";0;1))
In Zelle AM2:
=LET(x;$B$10:$J$15; y;$L$10:$T$15; z;VSTAPELN(x;y);
XVERWEIS($AI$2:$AI$13; INDEX(z;;2); INDEX(z;;5);"";0;1))
In Zelle AN2:
=LET(x;$B$10:$J$15; y;$L$10:$T$15; z;VSTAPELN(x;y);
XVERWEIS($AI$2:$AI$13; INDEX(z;;2); INDEX(z;;1);"";0;1))
wobei die kritische Funktion VSTAPELN(x;y) ist, die ein vertikales Stapeln von mehreren Matrizen/Arrays ermöglicht, sodass man quasi auf ein vereinigtes Array (nämlich z) zugreifen kann.
Ist diese Funktion noch nicht vorhanden (wegen Insider Beta), kann man sie mit folgender LAMBDA-Funktion im Namensmanager (mit dem Namen VSTAPELN) nachbilden:
=LAMBDA(m;n; LET(
zm;ZEILEN(m); j;SEQUENZ(;MAX(SPALTEN(m);SPALTEN(n))); i;SEQUENZ(zm+ZEILEN(n)); WENNFEHLER(WAHL((i>zm)+1;INDEX(m;i;j);INDEX(n;i-zm;j));"")))

Obige 3 Formeln vereinfachen sich nochmals, falls man für die beiden Arrays x und y 2 benannte Bereiche im Namensmanager definiert, zB. ZeltX und ZeltY:
in Zelle AC2: =LET(z;VSTAPELN(ZeltX;ZeltY); XVERWEIS($Z$2:$Z$15;INDEX(z;;2);INDEX(z;;1);"";0;1))
usw.

 

Hallo Anton,

ich konnte inzwischen feststellen, dass der TE XL365 im Einsatz hat. Dagegen ist meine XL2016 "uralt". Ich hab aber natürlich schon mitbekommen, dass man durch die Vielzahl von neuen Funktionen in XL365 ganz tolle Möglichkeiten hat, die die Formeldefinitionen vereinfachen und gar überhaupt erst ermöglichen können. Aber auch die Spillfunktionalität finde ich schon toll. Aber momentan reicht mir meine XL2016 Version noch.

Persönlich würde ich jedoch, Lösungen mit Funktionen aus Betaversionen, auch dann wenn sie mir zur Verfügung stünden, Fragestellern nur dann aufzeigen, wenn sie das wollen.

Deine aufgezeigte Lösungsformeln mit einer LAMBDA()-Formel als Ersatz für die Beta-Version VSTABELN(), stellt für mich hier allerdings noch keine wirkliche Vereinfachung gegenüber meinen aufgezeigten Formeln (wofür es auch noch andere Alternativen gäbe) dar. Aber das kann sicherlich individuell verschieden gesehen werden.

Schön aber, dass der Fragesteller jetzt noch eine weite Formellösungsmöglichkeit hat und sich dadurch auch mit den Möglichkeiten seiner XL-Version vertraut(er) machen kann.

 

Guten Morgen Werner,

vielen Dank für die Formeln!!!
Funktioniert perfekt!!!!

DANKESCHÖÖÖN

 

Bitteschön

 

Ich habe Dir eine Nachricht geschickt Werner