LET Funktion | Wo ist der/die Fehler

[Driver]

Hallo zusammen,

irgendwie funktioniert eine LET Funktion nicht. Diese sieht so aus:
=LET(
    e; EINDEUTIG(HSTAPELN(JAHR('time_card | Tag | ohne 0'!A2:A4000); TEXT('time_card | Tag | ohne 0'!A2:A4000; "MMMM"); 'time_card | Tag | ohne 0'!D2:D4000; 'time_card | Tag | ohne 0'!C2:C4000; 'time_card | Tag | ohne 0'!L2:L4000));
    VSTAPELN(
        {"Jahr"."Monat"."Name"."Kategorie"."Tagessatz"."Stunden"."Kosten"};
        HSTAPELN(
            e;
            SUMMEWENNS(
                'time_card | Tag | ohne 0'!I2:I4000;
                JAHR('time_card | Tag | ohne 0'!A2:A4000); INDEX(e;;1);
                TEXT('time_card | Tag | ohne 0'!A2:A4000; "MMMM"); INDEX(e;;2);
                'time_card | Tag | ohne 0'!D2:D4000; INDEX(e;;3);
                'time_card | Tag | ohne 0'!C2:C4000; INDEX(e;;4)
            );
            SUMMEWENNS(
                'time_card | Tag | ohne 0'!I2:I4000;
                JAHR('time_card | Tag | ohne 0'!A2:A4000); INDEX(e;;1);
                TEXT('time_card | Tag | ohne 0'!A2:A4000; "MMMM"); INDEX(e;;2);
                'time_card | Tag | ohne 0'!D2:D4000; INDEX(e;;3);
                'time_card | Tag | ohne 0'!C2:C4000; INDEX(e;;4)
            ) * INDEX(e;;5) / 8
        )
    )
)

Idee ist:
nun möchte ich unter Blatt 'Blatt 2' eine Tabelle die mir anzeigt:
Spalte 1 Jahr
Spalte 2 Monat
Spalte 3 Benutzer
Spalte 4 Kategorie
Spalte 5 Tagessatz
Spalte 6 Stunden (alle in diesem Monat abgeleisteten Stunden von dem Benutzer aufsummiert)
Spalte 7 Kosten (berechnet nach Anzahl der Stunden in dem Monat * den Tagessatz /8)  Erklärung ein Arbeitstag hat 8 Stunden. 

anbei auch das Excel (genutzt wird Excel 365 deutsch)

Viele Grüße und einen schönen Abend

Driver

[ralf_b]

hier mal eine Idee. Nicht die eleganteste Variante. Mach was draus.

Code:

=LET(
    zeil; SEQUENZ(ANZAHL2('time_card | Tag | ohne 0'!A2:A4000));
    spA; INDEX('time_card | Tag | ohne 0'!A2:A4000; zeil; 1);
    spD; INDEX('time_card | Tag | ohne 0'!D2:D4000; zeil; 1);
    spL; INDEX('time_card | Tag | ohne 0'!L2:L4000; zeil; 1);
    spI; INDEX('time_card | Tag | ohne 0'!I2:I4000; zeil; 1);
    bnutzer; EINDEUTIG(FILTER(spD; spD <> ""); FALSCH; FALSCH);
    mont; MONAT(HEUTE());
    std; NACHZEILE(
        bnutzer;
        LAMBDA(usr; SUMME(WENN((MONAT(spA) = mont) * (spD = usr); spI)))
    );
    kst; NACHZEILE(
        bnutzer;
        LAMBDA(usr; SUMME(WENN((MONAT(spA) = mont) * (spD = usr); spI * spL)))
    );
    VSTAPELN(
        {"Monat". "Name". "Stunden". "Kosten"};
        HSTAPELN(SEQUENZ(ZEILEN(bnutzer); ; mont; 0); bnutzer; std; kst)
    )
)

[oee]

Und wo ist die Wunschlösung?

[oee]

Du kannst doch nicht innerhalb der Definition von Variable e diese Variable e benutzen - sie ist ja noch gar nicht zu Ende definiert. Das wäre ein klassischer "Zirkelbezug".

[BoskoBiati]

Hi oee,

Du kannst doch nicht innerhalb der Definition von Variable e diese Variable e benutzen - sie ist ja noch gar nicht zu Ende definiert. Das wäre ein klassischer "Zirkelbezug".

das ist nicht das Problem, der Teil ist korrekt! Problem ist, dass SUMMEWENNS nicht mit Jahr(Bereich);Monat(Bereich) und Tag(Bereich) umgehen kann!

@Driver,

so sollte es passen:

Code:

=LET(e;EINDEUTIG(HSTAPELN(JAHR('time_card | Tag | ohne 0'!A2:A4000);TEXT('time_card | Tag | ohne 0'!A2:A4000;"MMMM");'time_card | Tag | ohne 0'!D2:D4000;'time_card | Tag | ohne 0'!C2:C4000;'time_card | Tag | ohne 0'!L2:L4000));VSTAPELN({"Jahr"."Monat"."Name"."Kategorie"."Tagessatz"."Stunden"."Kosten"};HSTAPELN(e;SUMMEWENNS('time_card | Tag | ohne 0'!I2:I4000;'time_card | Tag | ohne 0'!A2:A4000;">=1."&INDEX(e;;2)&"."&INDEX(e;;1);'time_card | Tag | ohne 0'!A2:A4000;"<="&MONATSENDE("1."&INDEX(e;;2)&"."&INDEX(e;;1);0);'time_card | Tag | ohne 0'!D2:D4000;INDEX(e;;3);'time_card | Tag | ohne 0'!C2:C4000;INDEX(e;;4));SUMMEWENNS('time_card | Tag | ohne 0'!I2:I4000;'time_card | Tag | ohne 0'!A2:A4000;">=1."&INDEX(e;;2)&"."&INDEX(e;;1);'time_card | Tag | ohne 0'!A2:A4000;"<="&MONATSENDE("1."&INDEX(e;;2)&"."&INDEX(e;;1);0);'time_card | Tag | ohne 0'!D2:D4000;INDEX(e;;3);'time_card | Tag | ohne 0'!C2:C4000;INDEX(e;;4))*INDEX(e;;5)/8)))

[Driver]

Hallo, passt perfekt!!! Vielen Dank :) Schönes Wochenende

[oee]

Du hast Recht, das war doch keine Definition - ich hatte das "HSTAPELN" übersehen.

[maninweb]

Hallo,

in der Beispieltabelle wird Ralf Rabi im selben Monat (Feb. 23) einmal mit einem Tagessatz von 60 und einmal mit 65 aufgeführt.
Meiner Ansicht nach müsste somit auch nach Tagessatz gesplitted werden, so denn Tagessatz als Spalte im Ergebnis aufgeführt wird.

Die Formel sieht dann bei wie mir folgt aus, die zudem ein anderes Ergebnis in Summe liefert, als die Formeln hier oben im Thread.

PHP-Code:

=LET(vnDaten; FILTER('time_card | Tag | ohne 0'!A2:L4000; INDEX('time_card | Tag | ohne 0'!A2:L4000; 0; 1) > 0); 
     vnAuswahl; HSTAPELN(JAHR(INDEX(vnDaten; 0; 1)); 
                         MONAT(INDEX(vnDaten; 0; 1)); 
                         INDEX(vnDaten; 0; 4); 
                         INDEX(vnDaten; 0; 3); 
                         INDEX(vnDaten; 0; 12); 
                         INDEX(vnDaten; 0; 9); 
                         WENNFEHLER(INDEX(vnDaten; 0; 9) * INDEX(vnDaten; 0; 12) / 8; 0); 
                         JAHR(INDEX(vnDaten; 0; 1)) & "-" & MONAT(INDEX(vnDaten; 0; 1)) & "-" & INDEX(vnDaten; 0; 12) & "-" & INDEX(vnDaten; 0; 4)); 
     vnMatrix; SORTIERENNACH(vnAuswahl; INDEX(vnAuswahl; 0; 1); 1; INDEX(vnAuswahl; 0; 2); 1; INDEX(vnAuswahl; 0; 3); 1); 
     vnUnikate; EINDEUTIG(INDEX(vnMatrix; 0; SPALTEN(vnMatrix))); 
     vnSpalten; SEQUENZ(1; SPALTEN(vnMatrix)); 
     vnGruppierung; WEGLASSEN(REDUCE(""; SEQUENZ(ZEILEN(vnUnikate)); LAMBDA(A;V; 
                    LET(F; FILTER(vnMatrix; INDEX(vnMatrix; 0; SPALTEN(vnMatrix)) = INDEX(vnUnikate; V; 1)); 
                    VSTAPELN(A; MAP(INDEX(F; 1; 0); vnSpalten; LAMBDA(X;Y; WENN((Y <> 2) * (Y <> 6) * (Y <> 7); X; 
                    WENN(Y <> 2; SUMME(INDEX(F; 0; Y)); TEXT(DATUM(2000; X; 1); "MMMM"))))))))); 1; -1); 
     VSTAPELN({"Jahr"."Monat"."Benutzer"."Kategorie"."Tagessatz"."Stunden"."Kosten"}; vnGruppierung)) 


Wenn man jetzt nur auf Stunden und Kosten aggregieren würde, könnte man den Tagessatz im Ergebnis weglassen,
was dann wie folgt aussieht:

PHP-Code:

=LET(vnDaten; FILTER('time_card | Tag | ohne 0'!A2:L4000; INDEX('time_card | Tag | ohne 0'!A2:L4000; 0; 1) > 0); 
     vnAuswahl; HSTAPELN(JAHR(INDEX(vnDaten; 0; 1)); 
                         MONAT(INDEX(vnDaten; 0; 1)); 
                         INDEX(vnDaten; 0; 4); 
                         INDEX(vnDaten; 0; 3); 
                         INDEX(vnDaten; 0; 9); 
                         WENNFEHLER(INDEX(vnDaten; 0; 9) * INDEX(vnDaten; 0; 12) / 8; 0); 
                         JAHR(INDEX(vnDaten; 0; 1)) & "-" & MONAT(INDEX(vnDaten; 0; 1)) & "-" & INDEX(vnDaten; 0; 4)); 
     vnMatrix; SORTIERENNACH(vnAuswahl; INDEX(vnAuswahl; 0; 1); 1; INDEX(vnAuswahl; 0; 2); 1; INDEX(vnAuswahl; 0; 3); 1); 
     vnUnikate; EINDEUTIG(INDEX(vnMatrix; 0; SPALTEN(vnMatrix))); 
     vnSpalten; SEQUENZ(1; SPALTEN(vnMatrix)); 
     vnGruppierung; WEGLASSEN(REDUCE(""; SEQUENZ(ZEILEN(vnUnikate)); LAMBDA(A;V; 
                    LET(F; FILTER(vnMatrix; INDEX(vnMatrix; 0; SPALTEN(vnMatrix)) = INDEX(vnUnikate; V; 1)); 
                    VSTAPELN(A; MAP(INDEX(F; 1; 0); vnSpalten; LAMBDA(X;Y; WENN((Y <> 2) * (Y <> 5) * (Y <> 6); X; 
                    WENN(Y <> 2; SUMME(INDEX(F; 0; Y)); TEXT(DATUM(2000; X; 1); "MMMM"))))))))); 1; -1); 
     VSTAPELN({"Jahr"."Monat"."Benutzer"."Kategorie"."Stunden"."Kosten"}; vnGruppierung)) 


Gruß

[EA1950]

Hallo,

um den schrecklichen Arbeitsblattnamen in der Formel zu beseitigen und den Datenbereich dynamisch zu gestalten, habe ich aus den Quelldaten eine strukturierte (intelligente) Tabelle erzeugt mit Namen "tbDaten".
Ferner habe ich für die Jahr/Monats-Gruppierung nur 1 Spalte verwendet und erst die Ausgabe im Arbeitsblatt gesplittet auf 2 Spalten.

Meine Formel sieht dann so aus:

PHP-Code:

=LET(
    m; HSTAPELN(
        DATUM(JAHR(tbDaten[Datum]); MONAT(tbDaten[Datum]); 1);
        tbDaten[Benutzer];
        tbDaten[Kategorie];
        tbDaten[Tagessatz]
    );
    e; EINDEUTIG(m);
    s; NACHZEILE(
        e;
        LAMBDA(zl;
            SUMME(
                tbDaten[Arbeitszeit] * (INDEX(m; ; 1) = INDEX(zl; ; 1)) *
                    (INDEX(m; ; 2) = INDEX(zl; ; 2)) * (INDEX(m; ; 3) = INDEX(zl; ; 3)) *
                    (INDEX(m; ; 4) = INDEX(zl; ; 4))
            )
        )
    );
    k; s * INDEX(e; ; 4) / 8;
    VSTAPELN(
        {"Jahr". "Monat". "Name". "Kategorie". "Tagessatz". "Stunden". "Kosten"};
        HSTAPELN(JAHR(INDEX(e; ; 1)); e; s; k);
        HSTAPELN(""; ""; ""; "Gesamt:"; ""; SUMME(s); SUMME(k))
    )
) 


Falls die Funktion "GRUPPIERENNACH" bereits zur Verfügung steht:

PHP-Code:

=LET(
    g; GRUPPIERENNACH(
        HSTAPELN(
            DATUM(JAHR(tbDaten[Datum]); MONAT(tbDaten[Datum]); 1);
            tbDaten[Benutzer];
            tbDaten[Kategorie];
            tbDaten[Tagessatz]
        );
        tbDaten[Arbeitszeit];
        SUMME;
        0;
        0
    );
    ko; HSTAPELN("Jahr"; "Monat"; "Name"; "Kategorie"; "Tagessatz"; "Stunden"; "Kosten");
    ww; HSTAPELN(JAHR(INDEX(g; ; 1)); g; INDEX(g; ; 4) * INDEX(g; ; 5) / 8);
    su; HSTAPELN(""; ""; ""; "Gesamt:"; ""; SUMME(INDEX(ww; ; 6)); SUMME(INDEX(ww; ; 7)));
    VSTAPELN(ko; ww; su)
)