Thema: Einsatzplan aus Matrix erstellen

Hallo zusammen,

ich habe eine Tabelle zur Personaleinsatzplanung und möchte aus den Rohdaten einen übersichtlicheren Plan je nach Einsatz erstellen.

In Tabelle 1 in Spalte A stehen die Namen der Mitarbeiter. Jeder Mitarbeiter hat in seiner Zeile seine Einsätze mit verschiedenen Kürzeln aufgeführt.
In Tabelle 2 möchte ich aus dieser Matrix heraus einen Plan erstellen, in dem in Spalten die jeweiligen Einsätze mit den Namen der Mitarbeitenden ausgefüllt werden.
Der Dienst 1 hat beispielsweise das Kürzel "C-D", der Dienst 2 das Kürzel "R-D".

Ich habe es mit folgender Formel versucht:

=INDEX(Tabelle1!A4:A56;VERGLEICH("C-D";Tabelle1!D4:D56;0);1)

Das funktioniert für diese eine Zelle auch, aber ich kann die Formel nicht automatisch fortsetzen.

Ich bin ziemlich neu in der Materie und ich weiß auch nicht, ob das der richtige Ansatz ist, daher wäre ich für jede Hilfe dankbar!
Da es insgesamt sehr viele verschiedene Kürzel gibt, brauche ich eine Lösung, in der ich in der Formel nur das Kürzel ändere, der Rest aber automatisch angepasst wird.

Hier ist die Beispieldatei:

https://www.herber.de/bbs/user/169143.xlsx

Vielen Dank schon mal für eure Hilfe!

Für den Fall, dass ich mich nicht deutlich genug ausgedrückt habe:

Mein Wunsch wäre es, dass aus der gesamten Matrix für ein entsprechendes Kürzel (C-D, C-XD, R-D, etc) der Name des entsprechenden Mitarbeiters zu dem zugehörigen Datum eingetragen wird.

Vielen Dank und viele Grüße,
David

Hallo

ich habe mal eine Beispieldatei mit VBA Lösung hochgeladen. Die Lösung ist garnicht so einfach!
Mir fiel als erstes auf, das zu jedem Datum mehrere Mitarbeiter aufgelistet werden.
Mein erster Versuch war, alle Mitarbeiter mit Anfangsbuchstabe "C-" und "R-" aufzulisten.
Aber auch da gibt es pro Tag mehrere Mitarbeiter, mit jeweils einem eigenen Kürzel.
https://www.herber.de/bbs/user/169148.xls

Wie soll bei diesem Umfang die Aufgabe in der Praxis gelöst werden?? Das ist mir noch unklar.
Neben jeden Namen auch eine eigene Spalte für das Kürzel?? Es gibt insgesamt 18 Kürzel.
Werden die alle benötigt, und was ist mit dem Kürzel "X" und dem Kürzel "o"??

mfg Piet

Hallöchen,

nur mal dazu:
Da es insgesamt sehr viele verschiedene Kürzel gibt, brauche ich eine Lösung, in der ich in der Formel nur das Kürzel ändere, der Rest aber automatisch angepasst wird.

Wenn Dein Kürzel in einer Zelle steht, kannst Du darauf Bezug nehmen, also z.B. bei D2 und brauchst das Kürzel nicht in der Formel ändern.

=INDEX(Tabelle1!A4:A56;VERGLEICH(D2;Tabelle1!D4:D56;0);1)

Du kannst natürlich auch Suchen & Ersetzen ...

Du kannst natürlich auch eine Liste der Dienste und Kürzel erstellen und dann z.B. per Verweis das zum Dienst passende Kürzel automatisch setzen.

HI

1. schreibe in die Überschrift nicht nur "Dienst 1", "Dienst 2" usw, sondern in einer eigenen Zeile (z.B. Zeile 2) auch die dazugehörige Dienstbezeichnung ("C-D", "R-D" usw) und referenziere für den Suchwert auf diese Zelle.
damit solltest du die Formel nach rechts ziehen können

2. damit du die Formel auch nach unten ziehen kannst, musst du die spalte entsprechend des Tages auswählen. Da du lückenlose Kalender hast, geht das auch einfach.

3. damit die Formel einfach in andere Zellen kopiert werden kann, müssen die Zellbereiche, die sich dabei nicht verändern dürfen, fixiert werden (das machen die $-Zeichen vor Zeilen- oder Spaltennummer)

4. mit WENNFEHLER kann man den Fehler vermeiden, fall ein Dienst an einem Tag nicht vergeben ist und sich stattdessen einen anderen Wert ausgeben lassen:

das ganze sieht dann so aus, hier für die oberste Zelle, alos D4 in Tabelle2, die Formel kann dann nach unten und rechts kopiert werden:

=WENNFEHLER(INDEX(Tabelle1!$A$4:$A$56;VERGLEICH(D$2;INDEX(Tabelle1!$B$4:$AE$56;0;TAG($C4));0);1);"")

Gruß Daniel

Wow super! Vielen Dank für die schnelle und hilfreiche Antwort, es hat hervorragend funktioniert!

Ich hätte noch eine weitere Frage:

Ich möchte perspektivisch noch ein zusätzliches Blatt erstellen, in dem die einzelnen Tage geplant werden können.

Dazu würde ich dann die entsprechenden Dienste aus Tabelle2 übernehmen, bräuchte aber noch eine Liste aller Mitarbeitenden, die in Tabelle1 an dem jeweiligen Tag einen Eintrag haben der ein "x" enthält. Also auch "XTZ", "C-XD", "C-XD2" etc.

Ich habe schon hin und her überlegt und es mit SVERWEIS und WENN probiert, bin aber krachend gescheitert...

Gibt es dazu wohl auch eine elegante Lösung?

Vielen vielen Dank noch mal!!

Ich habe es jetzt mal so probiert:

=WENN(ISTFEHLER(FINDEN("X";INDEX(Einsatzplan!$E$2:$AH$67;0;TAG(Tagesverteiler!$H$3))));"";Einsatzplan!$A5)

Das Problem ist, dass ich so jede Menge leere Zellen generiere, bei all den Mitarbeitern, die kein "X" im Kürzel haben. Gibt es eine Möglichkeit diese leeren Zellen zu vermeiden?

Danke!!

Hi
das geht prinziell mit dieser Formel:

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$A$4:$A$56)/ISTZAHL(SUCHEN("x";Tabelle1!$D$4:$D$56));ZEILE(A1)));"")

die Formel musst du soweit nach unten ziehen, wie du maximal Ergebnisse haben kannst.
der fette Teil ist die Spalte mit den Einträgen, die musst du, wie in der anderen Formel mit Index und dem Tageswert dynamisch machen, ich hab mir das hier aus faulheit und der besseren Übersicht wegen erspart.
wenn Groß/Kleinschreibung des "x" eine Rolle spielt, dann nimm FINDEN statt SUCHEN.
Eleganter gehts meines Wissens nach mit deiner Excelversion nicht.
Ab Excel 2022 oder 365 wirds dann interessant.

Gruß Daniel

Wow, das ist Magie.

Also ich bin schwer beeindruckt. Klappt hervorragend, aber ich verstehe absolut nichts mehr in dieser Formel...

Nur aus Interesse und da ich privat 365 nutze: wie könnte man so etwas da lösen? Vielleicht kommt die Firma ja auch irgendwann noch aus dem Quark =)

Hi
mit einer einzigen Formel in einer Zelle:

=Filter(Namesspalte;istzahl(suchen("x";Dienstspalte));"---")

(die dienstspalte wie bisher ermitteln)

der Vorteil von 365 ist, man jetzt auch Formeln schreiben kann, die mehrere Werte als Ergebnis haben und diese dann in die darunter oder daneben liegenden Zellen ausbreiten, ohne dass man die Formel da explizit eintragen muss. Mann muss sich auch nicht mehr darum kümmern, wieviele Ergebnisse die Formel haben könnte, das geht automatisch, es müssen nur genügend Zellen frei sein.
dh alles was man früher nur über das Menü konnte, nämlich Filtern, Sortieren, Duplikate entfernen, kann man jetzt auch innerhalb einer Formel machen.

Gruß Daniel