Urlaubsplaner

[Egon12]

Hallo Anton,
 
ja dann muss man, wenn es über den Jahreswechsel liegt 2 Datensätze anlegen damit es passt.
 
Mein ursprünglicher gedanklicher Vorschlag an den TO in #4 war, dass die Tabelle Basisdaten für auch noch Auswertungszwecke dienen soll (Timeline kurz zusammengefasst).
 
Nach diesem Prinzip: Man wählt ein Kalenderjahr aus und kann in der Tabelle Basisdaten (besserer Name war mir da auch nicht gleich in den Sinn gekommen) prüfen wie viel Tage Urlaub als Überhang aus vorherigem Jahr es gab / beantragte Urlaubstage gültig für das ausgewählte Jahr / offene Urlaubstage. Des Weiteren noch Anzahl Tage der Krankschreibung sowie Tage Überstundenabbau (Zeitabbau).
 
Ich habe da auch ziemlich drüber grübeln müssen bis die Ausgaben korrekt waren. Mit VBA ist das eher simpel zu lösen. Mit den neuen Funktionen habe ich mich doch ziemlich belesen und durchkämpfen müssen. Spaß hats trotzdem gemacht.
 
Eine Sache würde mir noch unter den Nägeln brennen: Kennst du die Ursache warum Listobjekte und die Arrayformel nicht gut harmonieren?
  
Gruß Uwe

[Hobbit]

Moin,

vielen dank für die tolle Hilfe.

Die Angehängten Dateien kann ich leider erst Testen, wenn wir O365 haben.

Vielen dank für die Mühen.

MFG Jens.

[Egon12]

Hallo Jens,

die von uns im Bauzustand hochgeladenen Dateien sind unfertig. Diese enthalten auch einige Denkfehler. Es war auch nicht unser Ziel da was Fertiges hochzuladen, sondern Lernimpulse zu geben bzw. auch ich selbst dazu zu lernen. 

Ich selbst habe da auch eine Menge für mich mitnehmen können und dabei auch die Erkenntnis gewonnen, dass das von so Manchen gern ins Nirvana geschickte VBA doch auch weiter seine Berechtigung hat. 

Erstaunt war ich, über die katastrophale Zusammenspiel und miserable Performance der neuen Funktionen im Zusammenhang mit Manipulationen in Listobjekten (Zeilen/Spalten erzeugen/Löschen) zumindest in Office 2024. Ohne an den Listobjekten was zu manipulieren bleibt alles unauffällig.


Naja, dass nur am Rande. Ich möchte damit aber um Himmelswillen keinen auf die Füße treten. 


Gruß Uwe

[Egon12]

@ Anton,

ich habe jetzt mal ausgehend von Hobbit (Jens) seiner Vorlage alles so wie in #4 die in 2 Versionen aus Interesse mal fertig gebaut.
Die VBA basierte Lösung habe ich, da diese problemlos mit größeren zu verarbeitenden Datenmengen klarkommt, als Basis für eine Formellösung herangezogen um mich mit den neuen Funktionen zu beschäftigen.

Mit der Performance sieht es schon brauchbar aus, hinkt aber immer noch etwas hinter deinen ursprünglichen Vorschlägen hinterher. Einer der Gründe liegt im kaskadenhaften wiederholten Anstoß von Berechnungen, welche eigentlich besser im Namensmanager aufgehoben wären.

Nun nutze ich Office 2024 und da gibt es ein paar neue Funktionen wie ABSCHNBEREICH() noch nicht.

Hauptbremse scheint die Funktion FILTER() zum Füllen der Variablen in LET() usw. zu sein. 
Meine Versuche die in den Namensmanager zu verbannen endeten kläglich. Irgendwas habe ich da wohl noch nicht richtig verstanden.

Wenn du Lust hast kannst du dir die Sachen mal anschauen. 

Hier mal beide Dateien:

  Abwesenheitkalender mit kleiner Auswertung für Forum.xlsx (Größe: 142,45 KB / Downloads: 6)

  Abwesenheitkalender mit kleiner Auswertung via VBA für Forum.xlsm (Größe: 177,37 KB / Downloads: 7)

Gruß Uwe

[BoskoBiati]

Hi,

wird auch nicht schneller, arbeitet aber mit begrenzten Bereichen:

Code:

=LET(xa;ANZAHL2(System!$AF:$AF)+11;xb;$A$12:INDEX($A:$A;xa);
xMa;FILTER(xb;xb<>"");xDt;FILTER(B6:NC6;B6:NC6<>"");xRow;ZEILEN(xMa);xSp;SPALTEN(xDt);
MATRIXERSTELLEN(xRow;xSp;LAMBDA(z;s;LET(aktMA;INDEX(xMa;z);aktDt;INDEX(xDt;s);roh;Basisdaten!A3:E199;xFi;WENNFEHLER(FILTER(roh;INDEX(roh;;1)=aktMA);"");
WENN(ANZAHL(xFi)=0;"";LET(von;INDEX(xFi;;2);bis;INDEX(xFi;;3);xKü;INDEX(xFi;;4);gültig;(von<=aktDt)*(bis>=aktDt)*(REST(aktDt;7)>1)*(ZÄHLENWENNS(System!H3:H28;aktDt;System!I3:I28;1)=0);WENNFEHLER(INDEX(FILTER(xKü;gültig);1);"")))))))

Auf die tatsächlich vorhandenen MA begrenzt:

Code:

=LET(xa;WENN(B4="Alle";ANZAHL2(System!$AF:$AF);ZÄHLENWENN(System!AF:AF;B4&"*"))+11;xb;$A$12:INDEX($A:$A;xa);
xMa;FILTER(xb;xb<>"");xDt;FILTER(B6:NC6;B6:NC6<>"");xRow;ZEILEN(xMa);xSp;SPALTEN(xDt);
MATRIXERSTELLEN(xRow;xSp;LAMBDA(z;s;LET(aktMA;INDEX(xMa;z);aktDt;INDEX(xDt;s);roh;Basisdaten!A3:E199;xFi;WENNFEHLER(FILTER(roh;INDEX(roh;;1)=aktMA);"");
WENN(ANZAHL(xFi)=0;"";LET(von;INDEX(xFi;;2);bis;INDEX(xFi;;3);xKü;INDEX(xFi;;4);gültig;(von<=aktDt)*(bis>=aktDt)*(REST(aktDt;7)>1)*(ZÄHLENWENNS(System!H3:H28;aktDt;System!I3:I28;1)=0);WENNFEHLER(INDEX(FILTER(xKü;gültig);1);"")))))))

Hier mal eine Variante mit Filter durch Namen ersetzt:

Code:

=LET(xa;WENN(Ausgabe!B4="Alle";Ber_A;Ber_MA);xMa;Ausgabe!$A$12:INDEX(Ausgabe!$A:$A;xa);xRow;ZEILEN(xMa);xSp;SPALTEN(xDt);MATRIXERSTELLEN(xRow;xSp;LAMBDA(z;s;LET(aktMA;INDEX(xMa;z);aktDt;INDEX(xDt;s);roh;Basisdaten!A3:E199;xFi;WENNFEHLER(FILTER(roh;INDEX(roh;;1)=aktMA);"");WENN(ANZAHL(xFi)=0;"";LET(von;INDEX(xFi;;2);bis;INDEX(xFi;;3);xKü;INDEX(xFi;;4);gültig;(von<=aktDt)*(bis>=aktDt)*(REST(aktDt;7)>1)*(ZÄHLENWENNS(System!H3:H28;aktDt;System!I3:I28;1)=0);WENNFEHLER(INDEX(FILTER(xKü;gültig);1);"")))))))

Namen dazu:
Ber_A --> =ANZAHL2(System!$AF:$AF)+11
Ber_MA --> =ZÄHLENWENN(System!AF:AF;Ausgabe!B4&"*")+11
xDt --> =FILTER(Ausgabe!B6:NC6;Ausgabe!B6:NC6<>"")

[Egon12]

Hallo Edgar,
 
Danke für die Tipps - Problem verstanden. 
 
Eine kleine Verbesserung habe ich schon erreicht. Ich bau mal weiter. Mal sehen, was ich da rausquetschen kann. Vielleicht kommt es doch in die Nähe von meiner VBA-Lösung. 
 
Wenn das praktikabel ist lade ich es für die Allgemeinheit hoch.
 
 
Gruß Uwe

[Egon12]

Hallo Edgar,

ich habe jetzt mal die Laufzeiten der Neuberechnung (Application.CalculateFullRebuild) gemessen und festgestellt, dass bei Auslagerung in den Namensmanager sich die Neuberechnung erhöht. 

Die zuletzt (gestern) von mir hochgeladene Datei ist mit 0,484 Sek. die schnellste.

Wenn ich allerdings in dieser Datei das Listobjekt konvertiere schmiert Excel ab. Dies passiert in der Datei, wo ich diverse Teile der Formeln in den Namensmanager ausgelagert habe nicht.

Für Interessierte beide Dateien. Die Namen im Namensmanager, welche ohne Funktion (für VBA nötig und Tests) waren, habe ich aus dem Namensmanager entfernt.
Dies hat auch noch minimal die Performance verbessert hat.

  Abwesenheitkalender mit kleiner Auswertung für Forum 0,484sek.xlsx (Größe: 142,31 KB / Downloads: 8)

  Abwesenheitkalender mit kleiner Auswertung für Forum 0,926sek.xlsx (Größe: 142,65 KB / Downloads: 4)

Gruß Uwe

[Egon12]

Hallo Jens,

anbei noch die VBA-Version eingerichtet für 100.000 Datensätze in der Liste Abwesenheiten. Dies dürfte ab Office 2021 laufen.
Kann es leider nicht mehr testen, da ich auf beiden Rechnern O2024 installiert habe.

  Abwesenheitkalender mit kleiner Auswertung via VBA für Forum.xlsm (Größe: 173,41 KB / Downloads: 4)
 
 Gruß Uwe

[Egon12]

Hallo,

Hier die finale Version.

Einige Sache habe ich optimiert und einen unerklärbaren Fehler beseitigt, der mit Application.Match() in Verbindung mit der Filterausgabe in Spalte A Tabelle1 passierte (damit meine ich nicht den "Fehler2042", - es knallt einfach wenn =Filter() ab dem 2. Wert "Kommis" ausgibt).
Das habe ich mit .Find gelöst.

Für interessierte hier die Datei:

  Abwesenheitkalender mit kleiner Auswertung via VBA für Forum.xlsm (Größe: 175,58 KB / Downloads: 5)

Gruß Uwe