Office: (Office 365) Hilfe bei Erstellung Formel SVerweis oder ähnliches gesucht

Hallo liebe Gemeinde,

ich habe ein Problem mit der Erstellung einer Formel. ich muss dazu sagen, ich bin ein Anwender, kenne mich ein wenig aus, aber bin kein Profi

Ich hänge mein Problem als Datei an. ich habe in der Spalte T geschrieben, was ich erreichen möchte.

Ich vermute, das es mit Index und Vergleich möglich ist, aber vielleicht gibt es Möglichkeiten, um dies besser zu erreichen.3 Sverweise wäre auch zu viel oder?

hat jemand eine Idee, wie ich vorgehen kann?

 

falsche Datei Tabelle "Tabelle Gesamt(Monatsmenge)" nicht da.

 

hab es gerade bemerkt, danke für den Hinweis. Ich habe die korrekte nun angehangen. viele Grüße, Patrick

 

gut ich muss Blind sein, ichg sehe sie nicht
Ich bin dann raus,
Viel Erfolg.

Gruß Hajo

 

Hallo Hajo, ich habe meine Ursprungspost geändert und dort die Datei geändert.

 

Hallo,

ich habe deine 3 Tabellen in ein anderes Arbeitsblatt verschoben, um zu zeigen, dass sich dadurch die folgenden Formeln nicht ändern. Außerdem habe ich sie in strukturierte Tabellen umgewandelt.
Die Array-Formel in Zelle R2 (mit Resultat in R2:S2):
=WENNS(
N2="M";XVERWEIS($D2; TbMonatsmengen[Gesamt (Monatsmenge)]; TbMonatsmengen[[Arbeitsgang 1]:[Arbeitsgang 2]]; 0;0;1);
N2="W";XVERWEIS("KW"&$E2; TbWochenmengen[Gesamt (Wochenmenge)]; TbWochenmengen[[Arbeitsgang 1]:[Arbeitsgang 2]]; 0;0;1);
N2="T";XVERWEIS($F2; TbTagesmengen[Gesamt (Tagesmenge)]; TbTagesmengen[[Arbeitsgang 1]:[Arbeitsgang 2]]; 0;0;1);
WAHR;{0.0})
Diese habe ich in die folgenden Zeilen hinunterkopiert.
Datei liegt bei.

 

Hallo,

man kann die Array-Formel in Zelle R2 (mit Resultat in R2:S2) verkürzen zu:
=LET(f; LAMBDA(m;k; XVERWEIS(k; SPALTENWAHL(m;2); SPALTENWAHL(m;-2;-1);0;0;1));
WENNS($N2="M";f(TbMonatsmengen;$D2); $N2="W"; f(TbWochenmengen;"KW"&$E2); $N2="T"; f(TbTagesmengen;$F2); WAHR;{0.0}))
und diese wie gehabt in die folgenden Zeilen hinunterkopieren.

 

Hallo,

vielen Dank vorab für Eure Unterstützung. ich schaue es mir gleich mal an und versuche, die Formeln zu verstehen.

viele Grüße,
Patrick

 

Hallo Anton,

vielen lieben Dank. Die erste Formel verstehe ich (google hilft mir ein wenig), die 2. Formel verstehe ich nicht. Kannst Du mir diese erklären? Was wäre, wenn ich 10 Arbeitsgänge habe? oder mehr oder weniger?

Grüße,
Patrick

 

Hallo,

diese enthält eine benutzerdefinierte Funktion mit dem Namen f und den beiden Parametern m und k:
Mathematiker würden sie in etwa so schreiben:
f(m;k) = XVERWEIS(k; SPALTENWAHL(m;2); SPALTENWAHL(m;-2;-1);0;0;1)
in Excel365 dagegen wird das gleiche so geschrieben:
f; LAMBDA(m;k; XVERWEIS(k; SPALTENWAHL(m;2); SPALTENWAHL(m;-2;-1);0;0;1));

f ist einfach der Name, den ich der Funktion gegeben habe, ich hätte sie zB. auch tbVERWEIS nennen können, um damit ihre Funktionsweise besser zu umschreiben. Sie besitzt 2 Parameter:
m ist der 1. Parameter. An diesen wird eine strukturierte Tabelle übergeben, zB. die Tabelle TbMonatsmengen
k ist der 2. Parameter: An diesen wird das Suchkriterium übergeben, zB. der Zellwert $D2 (="Jan").
Dadurch lautet dann der Funktionsausdruck, der den Wert von f(m;k) berechnen soll:
XVERWEIS("Jan"; SPALTENWAHL(TbMonatsmengen;2), SPALTENWAHL(TbMonatsmengen;-2;-1); 0;0;1)
Die Funktion SPALTENWAHL(TbMonatsmengen;2) liefert dabei die 2. Spalte der Tabelle TbMonatsmengen - ich hätte in diesem Fall auch schreiben können INDEX(TbMontsmengen;;2) - während die Funktion SPALTENWAHL(TbMonatsmengen;-2;-1) die vorletzte und die letzte Spalte von TbMonatsmengen liefern.
Ich hätte, um deine 2.Frage vorwegzunehmen, auch folgenden Funktionsausdruck einsetzen können: XVERWEIS("Jan"; SPALTENWAHL(TbMonatsmengen;2); ÜBERNEHMEN(TbMonatsmengen;;-2); 0;0;1)

Die Funktion XVERWEIS macht nun Folgendes: Sie sucht, in welcher Tabellenzeile das Kriterium "Jan" in SPALTENWAHL(TbMonatsmengen;2) vorkommt und gibt daraufhin die Werte der gleichen Zeile aus SPALTENWAHL(TbMonatsmengen;-2;-1) zurück (das müssen 2 Werte sein für die 2 Arbeitsgänge =(38734;37234)). Diese beiden Werte liefert die Formel auch zurück in den Zellen R2:S2 - falls Zelle N2="M" ist, denn dann ist in WENNS($N2="M",..) der Vergleich $N2="M" WAHR und es wird daher auch der nachfolgende Ausdruck f(TbMonatsmengen;$D2) berechnet.

Falls zB. $N3="W" gilt, so wird der Ausdruck f(TbWochenmengen;"KW"&$E3) berechnet, dh. f(TbWochenmengen;"KW1") bzw. XVERWEIS("KW1";SPALTENWAHL(TbWochenmengen;2); SPALTENWAHL(TbWochenmengen;-2;-1); 0;0;1), was aus Zeile 1 von TbWochenmengen die beiden Werte (5000;8000) für die beiden Arbeitsgänge ergibt.

Falls schließlich zB. $N4="T" gilt, wo wird der Ausdruck f(TbTagesmengen; 3.1.2023) bzw. f(TbTagesmengen;44929) berechnet, dh. f(TbTagesmengen;44929) bzw. XVERWEIS(44929;SPALTENWAHL(TbTagesmengen;2); SPALTENWAHL(TbTagesmengen;-2;-1); 0;0;1), was aus Zeile 3 von TbTagesmengen die beiden Werte (6346;3450) für die beiden Arbeitsgänge ergibt.

Ferner gibt es dann noch in der WENNS(...)-Funktion die beiden letzten Parameter, lautend auf ..., WAHR, {0.0} die nur in Aktion treten, falls in der N-Spalte ein falscher Wert steht, der weder "M", "W" noch "T" ist.

Um das zu realisieren, ist es vermutlich am einfachsten, eine LET(..)-Variable ag=10 einzuführen und, da in den Mengentabellen die Arbeitsgang-Spalten jeweils die letzten Spalten sind, diese letzten Spalten mit der ÜBERNEHMEN(..)-Funktion auszuwählen (der negative ag-Wert heißt, dass die letzten ag Spalten und nicht die ersten ag Spalten genommen werden):
=LET(ag;10;
f; LAMBDA(m;k; XVERWEIS(k; SPALTENWAHL(m;2); ÜBERNEHMEN(m;;-ag);0;0;1));
WENNS($N2="M";f(TbMonatsmengen;$D2); $N2="W"; f(TbWochenmengen;"KW"&$E2); $N2="T"; f(TbTagesmengen;$F2); WAHR;SEQUENZ(1;ag;0;0)))

 

Danke für die sehr detaillierte Erklärung. Jetzt wird es ein wenig klarer. Ich führe mir dies in Ruhe zu Gemüte