Office: sverweis und Wochenenden in Matrix darstellen

Ok, Danke dir für den späten Einsatz. Werde ebenfalls noch versuchen etwas rumzutesten.

 

Hi,

habe nochmals ein wenig über meine Formel nachgedacht. Es reicht bereits
=WOCHENTAG(B$1&$A2&'2024'!$A$1;2)>5
Trotzdem wird der "31.November2024" nicht markiert. Wieso? Weil es ihn nicht gibt, läuft die Formel in einen Fehler und wird daher für diese Tag niemals Wahr.

Der Ausdruck AF$1&$A12&'2024'!$A$1 - wie die Formel in der Zelle AF12 lautet - führt zu dem Ergebnis "31.November2024" also einem String mit einem nicht gültigen Datum. WOCHENTAG() versucht aus einer Textangabe nach Möglichkeit ein Datum zu machen, was bei z.B. "30.November2024" auch gelingt. Beim 31. halt nicht.
In Steves Formel ergibt sich aber DATUM(2024;11;31). Damit kann Excel was anfangen und macht daraus automatisch den 01.12.2024. Aber das hat Steve ja schon erwähnt.

Auch bei ZÄHLENWENN() funktioniert dieser "Trick"
=ZÄHLENWENN(Infos!$A$2:$A$12;B$1&$A2&'2024'!$A$1)

 

Hi,

habe mir mal noch Gedanken über deine SVerweis-Orgie gemacht. Insgeamt 365 bzw. 366 (in einem Schaltjahr) Formeln, das ist ja nicht ohne. Hier wäre es besser, einmal die Zeile zu bestimmen und dann dieses Ergebnis zu verwenden.
Z.B.
A20: =XVERGLEICH(A1;'2024'!$A$2:$A$13;0;1)
B2: =INDEX('2024'!$2:$13;$A$20;SPALTE('2024'!E$2))
In A20 wird hier die Zeile bestimmt und dann in den Zellen dieser Wert in INDEX zusammen mit deiner bisherigen Spalte aus dem SVerweis verwendet.

Die Zeitersparnis ist bei den paar Mitarbeitern nicht sooo groß, aber wenn es mehr werden, merkt man dies schon.

Was mir allerdings gar nicht gefällt, ist dass du immer mit dem Februar aufpassen musst (Schaltjahr Ja/Nein?) und du auch immer darauf achten musst jeweils die richtige Spalte anzusprechen. Sprich für den ersten musst du die richtige Spalte raussuchen und darauf achten, ob du sie bis zum 28., 29., 30., oder 31. ziehen musst.

Das sollte auch einfacher gehen. Ich habe versucht das in eine einzige Spill-Formel zu packen. Leider ist mir das nicht ohne eine Hilfszelle gelungen. Nehmen wir wieder die Zelle von vorhin:
A20: =LET(a;XVERWEIS($A$1;'2024'!$A$2:$A$13;'2024'!E2:NF13);b;TEXTVERKETTEN(",";0;a);c;SEQUENZ(11);d;DATUM('2024'!$A$1;c+1;0);e;TAG(d);f;SCAN(;e;LAMBDA(x;y;x+y));g;SORTIEREN(f;;-1);h;SCAN(b;g;LAMBDA(x;y;WECHSELN(x;",";"#";y)));i;ÜBERNEHMEN(h;-1);i)
Beachte den Bereich '2024'!$A$2:$A$13;'2024'!E2:NF13 im XVerweis(). Der sorgt dafür dass bis zum 31.12. gerechnet wird und nicht nur bis Ende April.
In B2 kommt dann
B2: =TEXTTEILEN(A20;",";"#";;;"")
Das füllt deine komplette Tabelle von B2 bis AF13 aus.

Das sieht jetzt extrem wild aus, darum habe ich die Formel aus A20 mal anders formatiert (kann auch in dieser Form direkt in die Zelle kopiert werden)
=LET(
a;XVERWEIS($A$1;'2024'!$A$2:$A$13;'2024'!E2:DU13);
b;TEXTVERKETTEN(",";0;a);
c;SEQUENZ(11);
d;DATUM('2024'!$A$1;c+1;0);
e;TAG(d);
f;SCAN(;e;LAMBDA(x;y;x+y));
g;SORTIEREN(f;;-1);
h;SCAN(b;g;LAMBDA(x;y;WECHSELN(x;",";"#";y)));
i;ÜBERNEHMEN(h;-1);
i)
Was passiert hier?
a gibt die Zeile des Mitarbeiters zurück
b verknüpft die einzelnen Zellen zu einem Strng, bei dem die einzelnen Tage durch Kommas getrennt sind
c gibt die Zahlenfolge 1 bis 11 zurück. Da sind die Monate (Dez wird nicht gebraucht).
d gibt den Monatsletzten der Monate 1-11 zurück
e ergibt die Anzahl Tage im Monat
f gibt die Summenfolge, so dass man weiß welche Kommas eigentlich Monatstrenner sind
g dreht die Reihenfolge der Summen um, da im nächsten Schritt von hinten gearbeitet werden muss
h wechselt die Kommas, die am Monatsende stehen, in #
i von den vielen Strings, die zurück kommen brauchen wir nur den letzten
Das Ergebnis ist ein String, bei dem die einzelnen Tage durch Kommas und die Monate durch #-Zeichen getrennt sind.

Code:

,,,,,,,,,,,,,,,,,,,,,U,U,U,U,U,,,,,#,,,,,U,U,U,,,,,U,U,U,,,,,,,,,,,U,U,U,U#U,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,#,U,U,U,U,,,,,,,,,,,,,,,,,,,,,,,,,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Dieser String wird in B2 dann entsprechend aufgeteilt.
Leider funktioniert es nicht das TEXTTEILEN() aus B2 zum Aufspalten an den Kommas und am # auch noch in die LET()-Formel zu nehmen. Da kommt dann nur eine leere Zelle bei raus. Weshalb auch immer.