Office: (Office 365) Arbeitsplatzbelegung

Hallo zusammen
Ich habe folgendes Problem
Ich habe einen Monatskalender auf dem Links eine Spalte mit den Daten des Monats sind

01.10.2022
02.10.2022
...

in der zweiten Spalte ist der Arbeitsplatz mit Nummer

Auf einem anderen Blatt habe ich eine Tabelle mit:
Name | Startdatum | Enddatum | Arbeitsplatz

Nun ist die Aufgabe wie folgt
im Monatskalender soll nun neben dem Datum der Monatstabelle der Name des Mitarbeiters stehen und zwar an allen Daten zwischen Startdatum und Enddatum

Die Tabelle sieht dann am Schluss in etwa aus wie folgt:

Mitarbeiter Tabelle
Wasabi | 03.10.2022 | 05.10.2022 | AP1
Markus| 01.10.2022 | 05.10.2022 | AP2
Arbeitsplatztabelle
Datum | AP1 | AP2
01.10.2022 |Markus
02.10.2022 |Markus
03.10.2022 | Wasabi | Markus
04.10.2022 | Wasabi |Markus
05.10.2022 | Wasabi |Markus
06.10.2022

ich denke das muss eine Mischung zwischen SVERWEIS und Wenn ABER UND sein .. keine Ahnung stehe einfach an ;)

Danke für Tipps und Hinweise

 

Schau dir mal Pivot-Tabellen an.

Wenn du da nicht weiter kommst, baue eine Tabelle die
genauso aufgebaut ist wie deine mit Beispieldaten und lade sie hoch.

Pan

 

Danke - Ich lade das mal hoch
Die Idee ist dass farblich ersichtlich wird wer wo ist UND was frei ist ... das einzige was fehlt ist der Datumbsbereich Wer wie lange wo arbeitet

 

Hallo wasabi-wolf,

dazu reicht eine Formel 8welche auch in älteren XL-Versionen - ab XL2010 einsetzbar ist) und auch ganz ohne WENN(). In D6:

=WENNFEHLER(INDEX(Teilnehmer!$C:$C;AGGREGAT(15;6;ZEILE(Teilnehmer!C$4:C$99)/(INDEX(Teilnehmer!$D$4:$E$99;;REST(SPALTE();2)+1)=D$5)/(Teilnehmer!$F$4:$F$99<=$C6)/(Teilnehmer!$G$4:$G$99>=$C6);1)/(REST($C6;7)>1));"")

Und diese nach rechts und unten ziehend kopieren. Diese Formel berücksichtigt auch gleich die Wochenenden, d.h. die Personen werden dort erst gar nicht einem Arbeitsplatz zugeteilt. Eine Erweiterung für Feiertage ist so auch noch möglich.

Deine Formel der bedingten Formatierung für die Wochenenden kannst Du auch reduzieren auf: =REST($C6;7)<2

 

Hallo,

da du angegeben hast, Excel365 zu besitzen, habe ich dir für Zelle D6 im Arbeitsblatt "Monat" folgende Formel erstellt:
=FILTER(tbTN[Teilnehmer]; ((tbTN[Vormittag]=D$5)+(tbTN[Nachmittag]=D$5) )* (tbTN[Start]<=$C6) * (tbTN[End]>=$C6) * (REST($C6;7)>1); "")
Diese Formel mittels Formelkopieren in die restlichen Zellen des Bereiches D6:S36 kopieren.

Sie setzt voraus, dass du die Daten im Arbeitsblatt "Teilnehmer" in die strukturierte Tabelle "tbTN" umwandelst.

Datei liegt bei.

 

Falls Jemand sowas brauchen kann stell ich die aktuelle Version hier rauf. Nicht perfekt aber für das was ich brauche gut genug ...

 

Hallo,

darf ich auf ein kleines Problem im Arbeitsblatt "Feiertage" aufmerksam machen. Die Osterformel, die du in Zelle A8 verwendest:
=DATUM(B1;3;28) + REST(24-REST(B1;19)*10,63;29) - REST(KÜRZEN(B1*5/4)+REST(24-REST(B1;19)*10,63;29)+1;7)
ist nur für die Jahre 1900-2078 und 2080-2099 korrekt. Im Jahr 2079 weicht sie um 7 Tage von der derzeit gültigen Gregorianischen Osterfestberechnung ab.
Falls dir dieser Umstand bekannt ist, kannst du mein Posting in die Rundablage entsorgen.

Folgende Formel berechnet auch das Jahr 2079 korrekt und ist darüber hinaus für den Zeitraum 1900-2203 gültig:
=7*RUNDEN((4&-B1)/7+REST(19*REST(B1;19)-7;30)*0,14;)-6
Weitere Formeln sind zB. zu finden unter folgendem Link:
Ermittlung des Ostersonntags und aller beweglichen Feiertage (herber.de)

 

Hallo Anton,

im Jahr 2079 wird wohl niemand mehr eine derartige Auswertung benötigen.

 

Danke an alle mit Ihren Inputs
So nun noch eine Optimierung:

Wenn jemand bei Teilnehmer ein Datumsbereich eingibt (Start / End) wäre es super, wenn man mögliche (ungebuchte) Platzvorschläge bekommen würde ODER wenn man einen bereits belegten Platz einträgt dieser rot Markiert wird als "Fehler/nicht möglich"
Danke <3

 

Hallo,

ich habe dir eine Formellösung eingebaut - ob du damit glücklich wirst, weiß ich nicht.
Ich habe die Tabelle im Arbeitsblatt "Teilnehmer" in eine strukturierte Tabelle namens "tbTN" umgewandelt und in die Spalte "Doppelbelegung" folgende Formel eingefügt:
=LET(b;VSTAPELN(Belegg2022;Belegg2023); Dt;SPALTENWAHL(b;1);
spV;WENNNV(VERGLEICH([@Vormittag];ArbPlatz;0)+1;-1); spN;WENNNV(VERGLEICH([@Nachmittag];ArbPlatz;0)+1;-1);
dtDoppArb;FILTER(Dt; (ISTFEHLER(SPALTENWAHL(b;spV)) + ISTFEHLER(SPALTENWAHL(b;spN))>0) * (Dt>=[@Start]) * (Dt<=[@End])); WENNFEHLER(TEXTVERKETTEN("|";1;TEXT(dtDoppArb;"TT.MM."));""))
Sie listet alle Doppelbelgungen von Arbeitsplätzen/Datumsangaben/Teilnehmern auf. Da deine Datenstruktur alles andere als einfach zu handhaben ist, musste ich ein paar der neuesten Funktionen, die in Excel365 Insider Beta enthalten sind, einsetzen (dunkelblau,fett geschrieben).
Darüber hinaus habe ich 3 benannte Bereiche in den Namensmanager eingefügt (orange geschrieben).

Wird ein neuer Teilnehmer in Zelle A11 (und folgende) eingetragen, wird der Zellbereich A11:F11 automatisch als neue Tabellenzeile an die Tabelle "tbTN" angefügt. Aufgrund der Eingaben in die Tabellenspalten "Vormittag", "Nachmittag", "Start", "Ende" werden die Zellbereiche "Belegg2022" bzw. "Belegg2023" in den Arbeitsblättern "2022" bzw. "2023" ausgefüllt. Auch die (obige) Formel in der Spalte "Doppelbelegung" kopiert sich automatisch in eine neu angefügte Tabellenzeile.

Kollidieren in diesen Zellbereichen mehrere Eintragungen, entsteht in diesen Zellen der Fehlerwert "#ÜBERLAUF!". Genau die Zellen mit diesem Fehlerwert werden von obiger Funktion abgefragt und aufgelistet.
Werden die Mehrfachbelegungen aufgrund der Eingaben in die Tabellenspalten beseitigt, verschwinden auch die "#ÜBERLAUF!"-Zellen und damit auch die Datumsauflistungen.in der Spalte "Doppelbelegung".

 

Danke <3 schau ich mir gerne an