notwendige Bestellmenge errechnen

Hallo allerseits,
es ist manchmal ein Kreuz mit dem Köpfchen. Mir erscheint mein Problem banal, aber lösen kann ich es dennoch nicht.
Ich habe in Spalte A tägliche Verbräuche von Produkt A in Mengeneinheiten, in Spalte B die möglichen Belieferungstage 0/1-codiert. In Spalte C die gelieferte Menge und in Spalte D den Bestand. Nun möchte ich rechtzeitig die erforderlichen Mengen (Spalte C) bis zum nächsten Liefertag liefern lassen, so dass der Verbrauch genau gedeckt wird. Manuell kann ich das ganz toll, per Formel leider nicht. In der Beispieldatei ist die erste Lieferung besonders, weil sie den ersten gelisteten Verbrauch mitdecken muss. https://www.herber.de/bbs/user/69385.xls
Kann mir jemand mit seinen grauen Zellen helfen? Das wäre klasse.
Viele Grüße
Jan

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Hallo Jan,
die Berechnung der Liefermenge kriegst du leicht über eine Kombination aus VERGLEICH und BEREICH.VERSCHIEBEN hin, umständlicher sind schon die ganzen "WENNs". So sollte es aber klappen:
Tabelle1

	A	B	C	D
1	Verbrauch	Lieferung ja/nein	gelieferte Menge	Bestand
2	50	1	130	80
3	70		0	10
4	10	1	220	220
5	90		0	130
6	70		0	60
7	40		0	20
8	20	1	160	160
9	60		0	100
10	50		0	50
11	10		0	40
12	40		0	0

verwendete Formeln
Zelle	Formel
C2	=WENN(B2=1;SUMME(BEREICH.VERSCHIEBEN(A2;WENN(ISTTEXT(A1);0;1);;WENN(ISTNV(VERGLEICH(1;B3:$B$12;0));99;VERGLEICH(1;B3:$B$12;0))+WENN(ISTTEXT(A1);1;0)));"")
D2	=C2-A2
C3	=WENN(B3=1;SUMME(BEREICH.VERSCHIEBEN(A3;WENN(ISTTEXT(A2);0;1);;WENN(ISTNV(VERGLEICH(1;B4:$B$12;0));99;VERGLEICH(1;B4:$B$12;0))+WENN(ISTTEXT(A2);1;0)));)
D3	=D2-A3+C3
C4	=WENN(B4=1;SUMME(BEREICH.VERSCHIEBEN(A4;WENN(ISTTEXT(A3);0;1);;WENN(ISTNV(VERGLEICH(1;B5:$B$12;0));99;VERGLEICH(1;B5:$B$12;0))+WENN(ISTTEXT(A3);1;0)));)
D4	=D3-A4+C4
C5	=WENN(B5=1;SUMME(BEREICH.VERSCHIEBEN(A5;WENN(ISTTEXT(A4);0;1);;WENN(ISTNV(VERGLEICH(1;B6:$B$12;0));99;VERGLEICH(1;B6:$B$12;0))+WENN(ISTTEXT(A4);1;0)));)
D5	=D4-A5+C5
C6	=WENN(B6=1;SUMME(BEREICH.VERSCHIEBEN(A6;WENN(ISTTEXT(A5);0;1);;WENN(ISTNV(VERGLEICH(1;B7:$B$12;0));99;VERGLEICH(1;B7:$B$12;0))+WENN(ISTTEXT(A5);1;0)));)
D6	=D5-A6+C6
C7	=WENN(B7=1;SUMME(BEREICH.VERSCHIEBEN(A7;WENN(ISTTEXT(A6);0;1);;WENN(ISTNV(VERGLEICH(1;B8:$B$12;0));99;VERGLEICH(1;B8:$B$12;0))+WENN(ISTTEXT(A6);1;0)));)
D7	=D6-A7+C7
C8	=WENN(B8=1;SUMME(BEREICH.VERSCHIEBEN(A8;WENN(ISTTEXT(A7);0;1);;WENN(ISTNV(VERGLEICH(1;B9:$B$12;0));99;VERGLEICH(1;B9:$B$12;0))+WENN(ISTTEXT(A7);1;0)));)
D8	=D7-A8+C8
C9	=WENN(B9=1;SUMME(BEREICH.VERSCHIEBEN(A9;WENN(ISTTEXT(A8);0;1);;WENN(ISTNV(VERGLEICH(1;B10:$B$12;0));99;VERGLEICH(1;B10:$B$12;0))+WENN(ISTTEXT(A8);1;0)));)
D9	=D8-A9+C9
C10	=WENN(B10=1;SUMME(BEREICH.VERSCHIEBEN(A10;WENN(ISTTEXT(A9);0;1);;WENN(ISTNV(VERGLEICH(1;B11:$B$12;0));99;VERGLEICH(1;B11:$B$12;0))+WENN(ISTTEXT(A9);1;0)));)
D10	=D9-A10+C10
C11	=WENN(B11=1;SUMME(BEREICH.VERSCHIEBEN(A11;WENN(ISTTEXT(A10);0;1);;WENN(ISTNV(VERGLEICH(1;B12:$B$12;0));99;VERGLEICH(1;B12:$B$12;0))+WENN(ISTTEXT(A10);1;0)));)
D11	=D10-A11+C11
C12	=WENN(B12=1;SUMME(BEREICH.VERSCHIEBEN(A12;WENN(ISTTEXT(A11);0;1);;WENN(ISTNV(VERGLEICH(1;B$12:$B13;0));99;VERGLEICH(1;B$12:$B13;0))+WENN(ISTTEXT(A11);1;0)));)
D12	=D11-A12+C12

Tabellendarstellung in Foren Version 4.11

Der Einfachheit halber habe ich in B2 noch eine 1 reingeschrieben, da ja dort eine Lieferung erfolgt. Man könnte das sicher auch anderes lösen, aber das halte ich nur für notwendig, wenn du sagst, dass dort auf keinen Fall eine 1 stehen darf.
Die Option, dass die erste Zeile mitgezählt wird, habe ich über die Abfrage realisiert, dass in der darüberliegenden Zeile Text steht (im Sinne der Überschrift). Sollte das auf deine Echt-Tabelle nicht zutreffen, müsste man sich noch was anderes einfallen lassen.
Gruß
David

Beliebteste Forumthreads (12 Monate)

notwendige Bestellmenge errechnen

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread