Hilfstabelle mit einer Funktion erstellen

[GuJaNi]

Hallo zusammen,

ich habe eine Mastertabelle mit Kundendaten und Essensbestellungen.

Die Tagesblätter Montag bis Samstag werden in dem Reiter Tagesbestellungen mit einer Hilfstabelle erstellt.

Im Reiter Mastertabelle sind die Satmmdaten + die Essensbestellungen

Im Reiter Tagesbestellung sollen die einzelnen Tage (Reihe A-H) mit Daten aus der Hilfstabelle (Reihe J-L) dargestellt werden.

Die Hilfstabelle würde ich gerne mit einer Funktionsformel anstatt mit 6 einzelnen Blöcken zusammenfassen.

Hat jemand eine Lösung hierfür?

[Egon12]

Hallo,

auf die Schnelle ginge das so:

Code:

=LAMBDA(Wochentag;
LET(
xA;LET(start;3;offset;7;reps;6;kunden;ANZAHL2(Mastertabelle!A:A)/2;INDEX(Mastertabelle!A:A;start+GANZZAHL(SEQUENZ(kunden*reps;1;0;1)/reps)*offset));
xB;FILTER(Mastertabelle!B3:C1000;Mastertabelle!B3:B1000<>"");
xC;HSTAPELN(xA;xB);SPALTENWAHL(FILTER(xC;INDEX(xC;;2)=Wochentag);1;3))

Dies wäre für Montag. Eine Lambda für den Namensmanager kommt noch nach.

  Testtabelle(2).xlsx (Größe: 20,75 KB / Downloads: 6)


Gruß Uwe

---

Statt der Foremeln in den Zellen nun noch die LAMBDA.

Im Namensmanager:

Name: EssenZuordnen
Formel:

Code:

=LAMBDA(Wochentag;
    LET(
        xA; LET(
                start; 3;
                offset; 7;
                reps; 6;
                kunden; ANZAHL2(Mastertabelle!$A:$A)/2;
                INDEX(
                    Mastertabelle!$A:$A;
                    start + GANZZAHL(SEQUENZ(kunden*reps;1;0;1)/reps)*offset
                )
            );
        xB; FILTER(Mastertabelle!$B$3:$C$1000; Mastertabelle!$B$3:$B$1000<>"");
        xC; HSTAPELN(xA; xB);
        SPALTENWAHL(FILTER(xC; INDEX(xC;;2)=Wochentag); 1; 3)
    )
)

Aufruf für Montag in Zelle A3: =EssenZuordnen(A1)

Im Namensmanager gibt es diverse defekte Bezüge. Die musst du ggf. reparieren oder entfernen.

Gruß Uwe

[slowboarder]

Hi

wenn du die Hilfstabelle J:L nur zum füttern der Tagestabellen A:H benötigst, dann reicht diese Formel in J1 (oder auch J2, wenn du selber noch die Überschriften dazu tun willst)

PHP-Code:

=LET(
a;Mastertabelle!A.:.A;
b;Mastertabelle!B.:.B;
c;Mastertabelle!C.:.C;
x;SEQUENZ(ZEILEN(b));
aa;INDEX(a;UNTERGRENZE(x-1;7)+1);
HSTAPELN(b;aa;c))


die Formel macht jetzt nichts anderes, als in der Mastertabelle den Namen bei jedem Wochentag ergänzen, aber das reicht aus für deine Formeln in A:H

Gruß Daniel

[GuJaNi]

Funktioniert prima, Danke.

Schön wäre noch, wenn nicht die zusätzliche Spalte mit Kunde ohne Tag und Essen am Ende von jedem Block erstellt würde.

[Klaus-Dieter]

Hallo,

Schön wäre noch, wenn nicht die zusätzliche Spalte mit Kunde ohne Tag und Essen am Ende von jedem Block erstellt würde.

sicher meinst du die zusätzliche Zeile?

[Egon12]

Hallo GuJaNi,

du brauchst doch die Hilfstabelle gar nicht. Wenn schon O365 denn schon mit diesen Möglichkeiten (bei mir leider mit den Möglichkeiten O2024) auf direktem Wege.
Sollte es aber unbedingt mit der Hilfstabelle sein, dann musst du aus der LET nur die FILTER() Funktion rausnehmen.
so:

Code:

=LET(xA;LET(start;3;offset;7;reps;6;kunden;ANZAHL2(Mastertabelle!A:A)/2;INDEX(Mastertabelle!A:A;start+GANZZAHL(SEQUENZ(kunden*reps;1;0;1)/reps)*offset));xB;FILTER(Mastertabelle!B3:C1000;Mastertabelle!B3:B1000<>"");xC;HSTAPELN(xA;xB);SPALTENWAHL(xC;2;1;3))

Besser ist das doch mit LAMBDA im Namensmanager.

  Testtabelle(UDF via Lambda).xlsx (Größe: 20,5 KB / Downloads: 1)


Gruß Uwe

[GuJaNi]

Ja genau, Sorry.

---

Hallo GuJaNi,

du brauchst doch die Hilfstabelle gar nicht. Wenn schon O365 denn schon mit diesen Möglichkeiten (bei mir leider mit den Möglichkeiten O2024) auf direktem Wege.
Sollte es aber unbedingt mit der Hilfstabelle sein, dann musst du aus der LET nur die FILTER() Funktion rausnehmen.
so:

Code:

=LET(xA;LET(start;3;offset;7;reps;6;kunden;ANZAHL2(Mastertabelle!A:A)/2;INDEX(Mastertabelle!A:A;start+GANZZAHL(SEQUENZ(kunden*reps;1;0;1)/reps)*offset));xB;FILTER(Mastertabelle!B3:C1000;Mastertabelle!B3:B1000<>"");xC;HSTAPELN(xA;xB);SPALTENWAHL(xC;2;1;3))

Besser ist das doch mit LAMBDA im Namensmanager.



Gruß Uwe


Da hast du recht, dein Code funktioniert Super, das Problem hierbei ist, das ich mich immer an die korrekten Wochennamen halten mus.

Mo, Di Mi... usw.

Bei der anderen Formel ordnet er die Essen auch noch richtig zu, wenn ich z.B. Mon onder Montag schreibe.

Selbst wenn ich Mo+Fr in der Mastertabelle in Spalte B schreibe, wird das in der Tagesbestellung richtig bei Montag und Freitag zugeordnet. 

Das ist bei deinem Code leider nicht so.

---

Hallo,


sicher meinst du die zusätzliche Zeile?

Ja stimmt, Sorry.

[LCohen]

Datenerzeugung mit einer Formel kann z.B. sinnvoll sein, wenn man sie ausdrucken möchte, etwa den 23-Spalten-Kalender

=LET(
a;2026;
z;SEQUENZ(37;;0);
s;SEQUENZ(;23);
c;TEXT(z;"TTT");
b;TEXT(DATUM(a;s;1);"MMM");
d;--TEXT(DATUM(a;s;z+1)-REST(DATUM(a;s;1);7);"T");
e;WENN((z<6)*(d>6)+(z>26)*(d<10);"";d);
HSTAPELN(VSTAPELN(a;c);VSTAPELN(b;e)))

Warum 23 Spalten? Er reicht von Jan26-Dez26 über Feb26-Jan27 ... bis zu Dez26-Nov27. Dafür blendet man die ungewollten Spalten aus und bekommt dann z.B. ein Schuljahr von Aug-Jul.

[Egon12]

Hallo GuJaNi,

das kann man ganz einfach ändern, dass dies mit Teilstring funktioniert. Schau einfach im Netz unter: excel =FILTER() Teilstring.
Die Kombi FILTER / SUCHEN oder FINDEN / ISTZAHL musst du dann nur noch in die LAMBDA einbauen.

Unternimm einfach mal den Versuch dies selbst zu lösen. So schwer ist das nicht. Wenn du es wirklich nicht schaffst melde dich dann dazu noch einmal.

Gruß Uwe

[slowboarder]

Funktioniert prima, Danke.

Schön wäre noch, wenn nicht die zusätzliche Spalte mit Kunde ohne Tag und Essen am Ende von jedem Block erstellt würde.

Variante 1)
verzichte in der Mastertabelle auf die Leerzeile zwischen den Kunden und hänge die direkt aneinander
in der Formel musst du dann noch aus der 7 eine 6 machen.

PHP-Code:

=LET(
a;Mastertabelle!A.:.A;
b;Mastertabelle!B.:.B;
c;Mastertabelle!C.:.C;
x;SEQUENZ(ZEILEN(b));
aa;INDEX(a;UNTERGRENZE(x-1;6)+1);
HSTAPELN(b;aa;c))


Variante 2)
baue hier noch einen Filter ein:

PHP-Code:

=LET(
a;Mastertabelle!A.:.A;
b;Mastertabelle!B.:.B;
c;Mastertabelle!C.:.C;
x;SEQUENZ(ZEILEN(b));
aa;INDEX(a;UNTERGRENZE(x-1;7)+1);
FILTER(HSTAPELN(b;aa;c);b<>0))


Gruß Daniel