wenn 2 Kriterien stimmen, Wert einer 3.Spalte ausgeben

[schauan]

Hallöchen,

hab irgendwie "morgen" verpasst

Life wurde anfangs nicht angezeigt weil er nur in Gruppe F war.

Wenn ich irgendwie in der Tabelle deutlich sichtbar differenzieren soll, wer wann in welcher Gruppe war, müsste man die Tabelle bei den Wechseln teilen und die Namen neu setzen? Ansonsten kann man die Namen auch gleich alle fest eintragen? Wenn Deine Tabelle mal ein Jahr umfasst, waren vielleicht alle mal in Gruppe A, B, usw.

Ansonsten kann man die Formel für die Zahlen erst mal so erweitern (in J22 eintragen und rüber und runter ziehen):

=WENNFEHLER(VERWEIS(2;1/((Basis_f_Geschenke!$C$6:$C$100=J$21)*(Basis_f_Geschenke!$B$6:$B$100<$D22)*(Basis_f_Geschenke!$F$6:$F$100=$D$2));Basis_f_Geschenke!$E$6:$E$100);0)

[ImC]

Grüsse dich lieber,

das ist in Ordnung

In welcher Gruppe jemand ist, steht in Gruppenzuordnung .    Bitte nicht die Gruppe in Basis_f_Geschenke nehmen.  Vergiss die mal, die ist dort nur für einen schnelleren Ablauf angezeigt.    Wo jemand zu einem Zeitpunkt ist, steht in Gruppenzuordnung

Wenn ich irgendwie in der Tabelle deutlich sichtbar differenzieren soll, wer wann in welcher Gruppe war, müsste man die Tabelle bei den Wechseln teilen und die Namen neu setzen? Ansonsten kann man die Namen auch gleich alle fest eintragen? Wenn Deine Tabelle mal ein Jahr umfasst, waren vielleicht alle mal in Gruppe A, B, usw.

Nein, die Namen (J21) sollen nicht differenzierbar nach Datum dargestellt werden, nur die Werte (J22).  D.h. der Name kann oben stehen, aber nur 1x  (nicht doppelt)

Es kann z.B. so sein:   Die Formel für die Namen (J21) wird einfach einen Bereich rübergezogen  (z.B. J21-W21), so dass bei Zugängen die entspechenden Namen erscheinen. Der Name sollte eben nur 1x erscheinen, nicht mehrfach.   Ist jemand ganz raus, d.h. auch nicht in einer anderen Gruppe, kann die Spalte ausgeblendet werden. Daher gilt es wenn wir das haben F22 noch zu optimieren.  Aber zunächst mal hier weiter mit J21/22.

Man könnte natürlich sehen, ist jemand schon in Zeile ab J22, dann muss er nicht aufgeführt werden.  z.B. Nicht J22 bis  W22;
Bei Simandhar ist es dann so, dass er nur 1x aufgeführt ist,  die Werte aber nur in dem Zeitraum wo er auch in A ist.  NICHT vom 30.1.-25.3., in dieser Zeit kann der Name  durchaus  in Zeile 22 stehen,  aber die Werte in dieser Zeit sind 0.


Es sollte doch gehen, wenn in Gruppe A (in Gruppenzuordnung) zu dem Datum D22, dann größtes Datum vor dem Datum D22 , von dieser Zeile Spalte E in Basis_f_Geschenke.


J22, die Werte:
hab mal folgende Formel eingegeben:
=WENNFEHLER(VERWEIS(2;1/((Basis_f_Geschenke!$C$6:$C$100=J$21)*(Basis_f_Geschenke!$B$6:$B$100<=$D22)*(Gruppenzuordnung!$B$5:$B$99>=$D22)*(Gruppenzuordnung!$D$5:$D$99=$D$2));Basis_f_Geschenke!$E$6:$E$100);0)

Diese Formel bringt nicht den Wert mit dem jüngsten Datum vor D22, sondern für Simandhar: 150, 150 dann nur noch 600;  Es sollten sein  200, 200, 600, 700;    Verweis sollte doch den jüngsten bringen.     Das ist seltsam.


LICHTvolle Nacht


LIEBE
Claudia

[schauan]

Hallöchen,

ich habe jetzt die Formel aus dem anderen Blatt in J22 eingearbeitet

Arbeitsblatt mit dem Namen 'A_Zwischenstand_Verlauf_Gruppe'
	D	E	F	G	H	I	J	K	L	M	N
21	Datum	Stand incl. Zusatz-Geschenke	Basis=Wert ges. Gruppe bis Datum		Basis manuell eingeg. Diese Werte sollten in F sein		Simandhar Swami	Liebe	Venus	Lion
22	04.01.2020	780	400		550		150	0	250	0	0
23	05.01.2020	1170	450		650		200	0	250	0	0
24	06.01.2020	1800	450		1050		200	0	250	0	0
25	07.01.2020	2500	850		1250		600	0	250	0	0
26	08.01.2020		950		1500		700	0	250	0	0
27	09.01.2020	2750	950		1500		700	0	250	0	0
28	10.01.2020		1050		1500		700	0	350	0	0

Zelle	Formel
F22	=SUMME(J22:N22)
J22	=WENNFEHLER(VERWEIS(2;1/((Basis_f_Geschenke!$C$6:$C$100=J$21)*(Basis_f_Geschenke!$B$6:$B$100<$D22)*(ZEICHEN(SUMMENPRODUKT((Gruppenzuordnung!$C$5:$C$15=J$21)*($D22>=Gruppenzuordnung!$A$5:$A$15)*($D22<=Gruppenzuordnung!$B$5:$B$15)*Gruppenzuordnung!$E$5:$E$15))=$D$2));Basis_f_Geschenke!$E$6:$E$100);0)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

[ImC]

Grüsse dich lieber,
 wir kommen näher 


Bringt die richtigen Werte, jedoch wenn der Bereich erweitert wird,  stimmt es nicht mehr.  Es sieht so aus als ob Zeichen und/oder Summenprodukt  Leerzellen nicht mag.   Bei der Zeichenformel in Basis_f_Geschenke kommt wenn der Bereich Leerzellen enthält  #Wert.

J22  bringt bei dieser Formel  (Bereich erweitert),  von Beginn an   0,  also Fehler:
=WENNFEHLER(VERWEIS(2;1/((Basis_f_Geschenke!$C$6:$C$100=J$21)*(Basis_f_Geschenke!$B$6:$B$100<=$D22)*(ZEICHEN(SUMMENPRODUKT((Gruppenzuordnung!$C$5:$C$900=J$21)*($D22>=Gruppenzuordnung!$A$5:$A$900)*($D22<=Gruppenzuordnung!$B$5:$B$900)*Gruppenzuordnung!$E$5:$E$900))=$D$2));Basis_f_Geschenke!$E$6:$E$100);0)

Gibt es Möglichkeiten wo Leerzellen in einem Bereich o.k. sind?  Womöglich eine andere Funktion, da das mit Zeichen eh so eine Sache ist, weil  nur 1 Zeichen möglch ist.


Jetzt geht es also noch darum, dass der Name Zeile 21 nur 1x angezeigt wird und dass der Bereich für die Werte erweitert in eine Formel eingegeben werden kann.


Vielen DANK.

LIEBE
Claudia

[ImC]

Ihr Lieben,

Jetzt geht es also noch darum, dass der Name Zeile 21 nur 1x angezeigt wird und dass der Bereich für die Werte erweitert in eine Formel eingegeben werden kann.

Wie kann hierfür eine Lösung aussehen?

Vielen DANK.

LIEBE
Claudia

[schauan]

Hallöchen,

so bekommst Du erst mal die Namen der gewählten Gruppe nur 1x. Man könnte die Formel jetzt noch um eine Bedingung für ein Datum erweitern, im Prinzip

*(Gruppenzuordnung!$B1:$B$99<=MAX($D$22:$D$220)

Nächster Step wäre dann der Bereich. Welcher soll den flexibel sein?

Arbeitsblatt mit dem Namen 'A_Zwischenstand_Verlauf_Gruppe'
	J	K	L	M	N
21	Simandhar Swami	Liebe	Venus	Lion

Zelle	Formel
J21	{=WENNFEHLER(INDEX(Gruppenzuordnung!$C:$C;VERGLEICH(1;(ZÄHLENWENN($I21:I21;Gruppenzuordnung!$C1:$C99)=0)*(Gruppenzuordnung!$C1:$C99<>"")*(Gruppenzuordnung!$C1:$C99<>"Name")*(Gruppenzuordnung!$D1:$D$99=$D$2);0));"")}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

[ImC]

Lieber Andre,

yiiipiiee   die funktioniert .    HEZRLICHEN DANK  

Wenn die Ergänzung  *(Gruppenzuordnung!$B1:$B$99<=MAX($D$22:$D$220))   eingefügt ist, kommt allerdings ist die Zelle Leer und die anderen Spalten werden nach rechts gerückt.  Die Ergänzung wurde vor   (Gruppenzuordnung!$D1:$D$99=$D$2)   eingefügt.  

Die Ergänzung ist wohl dafür, dass nur die aktuellen gezeigt werden.     Sollte die Ergänzung in der Formel woanders hin?


Zu dem flexiblen Bereich:

J22:    da sollte der Bereich in Gruppenzuordnung Spalte A, B, C  nicht nur bis Zeile 15 gehen sondern bis 999 und so, dass auch Leerzellen o.k. sind.

Vielen DANK.

LIEBE
Claudia

[schauan]

Hallöchen,

in der letzten Formel hatte ich ja schon statt 15 die 99 drin. wenn es nicht reicht, die 99 an allen Stellen auf 999 erhöhen. Beim Ändern aufpassen, dass nur J21 gewählt ist und dann rüber ziehen.

Hier mal noch mit dem Zusatz der Datumsprüfung.

Ich vergleiche hier übrigens das Datum $D$22:$D$220 - so weit ist noch nicht ausgefüllt - mit dem Bis-Datum auf der Gruppenzuordnung. Wenn Du mehr Daten erfasst, müsstest Du statt 220 dann entsprechend mehr eingeben.

Arbeitsblatt mit dem Namen 'A_Zwischenstand_Verlauf_Gruppe'
	J	K	L	M	N
21	Simandhar Swami	Venus	Lion
22	150	250	0	0	0
23	200	250	0	0	0
24	200	250	0	0	0
25	600	250	0	0	0
26	700	250	0	0	0
27	700	250	0	0	0
28	700	350	0	0	0
29	700	500	0	0	0
30	700	500	0	0	0

Zelle	Formel
J21	{=WENNFEHLER(INDEX(Gruppenzuordnung!$C:$C;VERGLEICH(1;(ZÄHLENWENN($I21:I21;Gruppenzuordnung!$C1:$C99)=0)*(Gruppenzuordnung!$C1:$C99<>"")*(Gruppenzuordnung!$C1:$C99<>"Name")*(Gruppenzuordnung!$D1:$D$99=$D$2)*(Gruppenzuordnung!$B1:$B$99<=MAX($D$22:$D$220));0));"")}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

[ImC]

Lieber Andre,
Zeile 21  geht mit Matrixformel super. HERZlichen DANK.

Die Erweiterung des Bereichs bezog sich auf J22 bzw. später dann auf F22, also die Werte  ab Zeile 22. Wenn dort der Bereich eingegeben wird wo in Gruppenzuordnung noch nicht ausgefüllt ist, kommt als Ergebnis 0, statt der aktuelle Wert mit dem Datum in Spalte D.

Vielen DANK.

LIEBE
Claudia

[schauan]

Hi Claudia,

Wenn dort der Bereich eingegeben wird wo in Gruppenzuordnung noch nicht ausgefüllt ist, kommt als Ergebnis 0, statt der aktuelle Wert mit dem Datum in Spalte D.

wenn dort nix steht kann ich doch nicht einfach festlegen, dass der an dem Datum zur gefragten Gruppe gehört.