Urlaubsplaner

[Hobbit]

Moin,

vielen Dank für die tolle Hilfe.

Ich hoffe mal das wir so bald wie möglich 365 bekommen.

Eine schöne Restwoche euch.

MFG Jens

[Egon12]

Hallo Edgar,

ich muss noch mal bei deinen Erfahrungen mit LET() zum Thema Begrenzungen von Verschachtelung anklopfen. Weißt du dazu einen Link.

Des Weiteren hatte ich dich leider mit der UDF Formel Ausgabe in die falsche Richtung gelenkt.

Es macht ja eigentlich nur Sinn die beantragten Zeiträume für das eingestellte Kalenderjahr auszugeben. 

Da habe deine Formel geschnappt und versucht die Variablen direkt passend zu füttern, so dass die Liste durch x Jahre genutzt werden kann. Da gab es dann Probleme mit der Verschachtelungstiefe. 

Letztendlich hab ich mich dann doch ganz anders rantasten können. 

Einzig geblieben ist, dass wenn noch keine Einträge im Vorjahr in der Tabelle Abwesenheiten standen natürlich die volle Anzahl Urlaub als Resturlaub berechnet wird.
Dem kann man entweder mit entsprechenden Dummy Einträgen für 1. vergangene Jahr begegnen oder ignoriert dies kurzerhand im ersten Nutzungsjahr.

Jetzt habe ich auch begriffen, warum du die Listobjekte aufgehoben hast. Die verschlechtern bei solchen komplexen Sachen die Performance drastisch (man lösche da mal am Ende eine Zeile).

Anbei mal noch die Datei. Eigentlich sollte ich es mit den Bedingungen richtig gemacht haben - hoffe ich mal. Wenn du einen besseren Weg weißt wäre ich interessiert dran.

  Urlaubsplaner neu gebaut.xlsx (Größe: 668,83 KB / Downloads: 12)

Gruß Uwe

[Egon12]

Hallo Edgar,
 
anbei die Datei. Bei allen Funktionen, welche in O2024 vorhanden sind (im Dropdown angezeigt werden) habe ich einen Haken in neuer Spalte gesetzt.
 
Was nicht im Dropdown angezeigt wurde habe ich noch nicht überprüft, ob die Funktion nicht doch vorhanden ist (Beispiel: DATEDIF). Das kommt noch.
Deshalb habe ich (bei den nicht im Dropdown angezeigten Funktionen) noch keine Sperrscheibe gesetzt.

  Alle_Funktionen_Version_4.xlsx (Größe: 206,92 KB / Downloads: 13)

Gruß Uwe

[BoskoBiati]

Hallo Uwe,

danke für die Erweiterung. 
Bei den noch offenen Funktionen gilt erstmal Folgendes:
DATEDIF ist eine nicht dokumentierte Funktion, die in allen Versionen vorkommt, aber mangels Dokumentation wohl nicht aufgeführt ist.
ASC, ISO.OBERGRENZE, OBERGRENZE.GENAU und UNTERGRENZE.GENAU sind Standardfunktionen, die seit 2016 existieren.

ich muss noch mal bei deinen Erfahrungen mit LET() zum Thema Begrenzungen von Verschachtelung anklopfen

keine Erfahrungen diesbezüglich.

so dass die Liste durch x Jahre genutzt werden kann

das könnte man vielleicht durch eine Lambda-Funktion erschlagen, allerdings habe ich damit so meine Probleme. Das wäre eher was für Exl121150, ich weiß aber nicht ob der hier aktiv ist.

[Egon12]

Hallo Edgar,

beim Abgleich der offen gelassenen Zellen (für Test der betreffenden Funktionen ) bin ich zum selben Ergebnis gekommen. Der Rest REGEX... gibt es in O2024 noch nicht.

Gruß Uwe

[Egon12]

Hallo Miteinander,
 
Die etwas zusammengebastelte Formel zur Ausgabe der Resturlaubstage in der Tabelle "Basisdaten für das Kalenderjahr ..." in Spalte "Rest Vorjahr"
habe ich nun mal entsprechend umgebaut und einen Rechenfehler um einen Tag beseitigt. Des Weiteren ist die Formel um eine zusätzliche Bedingung "wenn im Vorjahr kein Urlaub beantragt wurde dann Ausgabe 0" erweitert.

Damit sind die Startbedingungen für die Erstverwendung dieser Datei sinnvoll.
in J3 und runterziehen:

Code:

=LET(
  xa; FILTER(Tbl_Abwesenheit[[Mitarbeiter]:[Grund]]; (Tbl_Abwesenheit[Mitarbeiter]=G3)*(Tbl_Abwesenheit[Grund]="U"));
  xGu;$I3;
  xJahr; Ausgabe!$B$1 - 1;
  xVon; INDEX(xa;;2);
  xBis; INDEX(xa;;3);
  xd; FILTER(rng_AFt; rng_GFT=1);
  xb; ANZAHL(xVon);
  berechnet; WENN(
    xb = 0;
    0;
    SUMME(
      MAP(
        xVon; xBis;
        LAMBDA(a;b;
          WENNFEHLER(
            WENN(
              JAHR(a) = xJahr;
              NETTOARBEITSTAGE.INTL(a; WENN(JAHR(b) > xJahr; DATUM(xJahr; 12; 31) - 1; b); 1; xd);
              WENN(
                (JAHR(a) < xJahr) * (JAHR(b) >= xJahr);
                NETTOARBEITSTAGE.INTL(DATUM(xJahr; 1; 1); WENN(JAHR(b) > xJahr; DATUM(xJahr; 12; 31) - 1; b); 1; xd);
                0
              )
            );
            0
          )
        )
      )
    )
  );
  xErgebnis; MAX(0; xGu - berechnet);
  WENN(xErgebnis = xGu; 0; xErgebnis+1))

Einzig die Verwendung von Listobjekten (formatierte Tabelle) in Verbindung mit den neuen Funktionen macht Probleme, wenn man Zeilen aus einem Listobjekt löschen will. Da stehen sich diese beiden Sachen im Wege rum. Eine Zeile löschen aus Tbl_Abwesenheit dauert ca. 12 Sekunden.
 
Ebenso per VBA eine neue Zeile einfügen dasselbe Spiel. Händisch eine neue Zeile eintragen funktioniert zügig

Gruß Uwe

[EA1950]

Hallo @BoskoBiati,

so dass die Liste durch x Jahre genutzt werden kann

das könnte man vielleicht durch eine Lambda-Funktion erschlagen, allerdings habe ich damit so meine Probleme. Das wäre eher was für Exl121150, ich weiß aber nicht ob der hier aktiv ist.

weil du meinen Namen erwähnt hast: Ein Vorschlag mit Array-Funktionen.

Die maximale Mitarbeiteranzahl ist momentan auf 498 begrenzt - umstellbar durch Ändern der Bereichsformel
rng_MA_Basisdaten: =ABSCHNBEREICH(Rohdaten!$G$3:$J$500;2;0)

In Rohdaten!K3 ist eine Formel für die letzten beiden Spalten ("Beantragt"/"Noch offen") enthalten.
In Ausgabe!B6 ist die Datumsformel für die Tagesauflistung (wie gehabt) enthalten.
In Ausgabe!A12 ist die Formel für die Mitarbeiter-Auflistung enthalten.
In Ausgabe!B12 ist die Formel für die Detailanzeige der Abwesenheitsgründe enthalten.
In Ausgabe!B8 ist die Formel für die tagesweise Aufsummierung der Abwesenheitsgründe enthalten.

Ferner sind im Namensmanager 3 benutzerdef. LAMBDA-Funktionen enthalten:
IstFeiertag(dt)
IstArbeitstag(dt)
AbwesenheitGrund(AktMA;Datum_Grund)

Die Liste der Jahre kann beliebig erweitert werden, indem der Tabelle "Tbl_Jahre" noch weitere hinzugefügt werden.

Wie schnell (bzw. langsam) das Ganze ist, weiß ich nicht - da müsste man den Echtdatenumfang kennen.

[Egon12]

Hallo Anton,
 
ich vermute mal TRIMRANGE() gibt es leider auch nur in O365.
 
Ich denke mal, da aus der LET() einige dynamische Sachen nicht bei Änderung neu berechnet werden, sondern im Namensmanager stattfinden, sollte dies schon Performance deutlich verbessern. 
Wie verhält sich deine Lösung, wenn der Urlaubszeitraum über den Jahreswechsel liegt?
 
Die Sache war es auf jeden Fall mal Wert etwas gezielter in diese Formelwelt abzubiegen. Das ist schon interessant, was damit inzwischen geht.

Anbei mal das was ich zu Ende gesponnen habe für das ausgewählte Kalenderjahr: beantrage Tage Urlaub/Tage Resturlaub aus dem Vorjahr/Tage Krank/Tage Zeitabbau.

  Urlaubsplaner neu gebaut(1).xlsx (Größe: 673,02 KB / Downloads: 7)
 
Gruß Uwe

[BoskoBiati]

Hallo Anton,

schön von Dir zu hören. Ich wusste, Du kriegst das hin. Da werde ich mich mal mit der Formel befassen.
@Uwe,

Ersatz für TRIMRANGE (ABSCHNBEREICH):

rng_MA_Basisdaten:  =INDEX($G$3:INDEX($J$1:$J$600;VERWEIS(9;1/($G$1:$G$600<>"");ZEILE($G$1:$G$600)));;)

[EA1950]

Hallo @Egon12,

Wie verhält sich deine Lösung, wenn der Urlaubszeitraum über den Jahreswechsel liegt?

da hatte ich eine Inkonsistenz zwischen der Detailanzeige der Abwesenheiten (Blatt "Ausgabe") und der Anzeige der beantragten Urlaubstage (Blatt "Rohdaten"). In der Detailanzeige wurden solche Tage nur bis zum Jahresende angezeigt, bei den beantragten Tagen aber das ganze Intervall.

In der beiliegenden Datei habe ich das in der Formel von Zelle Rohdaten!K3 geändert, dass auch dort nur die Tage bis zum Jahresende berücksichtigt werden. Tage fürs nächste Jahr müssen im nächsten Jahr wieder erfasst werden.

PHP-Code:

=LET(
    MA; rng_MA_Basisdaten;
    Ftg; rng_Feiertage;
    Ultimo; DATUM(JAHR(INDEX(Ftg; 1; 1)); 12; 31);
    Beantragt; NACHZEILE(
        SPALTENWAHL(MA; 1);
        LAMBDA(AktMA;
            LET(
                xa; AbwesenheitGrund(AktMA; "U");
                xb; INDEX(xa; ; 2);
                xc; INDEX(xa; ; 3);
                WENNFEHLER(
                    SUMME(MAP(xb; xc; LAMBDA(a; b; NETTOARBEITSTAGE.INTL(a; MIN(b; Ultimo); 1; Ftg))));
                    0
                )
            )
        )
    );
    nochOffen; INDEX(MA; 0; 3) + INDEX(MA; 0; 4) - Beantragt;
    HSTAPELN(Beantragt; nochOffen)
)