Office: Formel gesucht - wenn-dann-sonst, summe...

Hallo zusammen,
ich komme einfach nicht weiter. Im Tabellenblatt "Formeln" in den Zellen B3 bis B8 würde ich mir gerne die Anzahl der offenen Stücke anzeigen lassen, die zum jeweiligen Datum (Zellen A3 bis A8) bestanden haben. Die Daten dazu liefert der Report aus dem Tabellenblatt "Basisdaten". Heißt, wenn A3 vor dem Datum aus "geliefert am" ist, war das Teil zu dem Zeitpunkt A3 noch offen. Auch ist es offen, wenn die Zelle in "geliefert am" leer ist. Die Anzahl der offenen Stücke soll dann addiert werden. Diese zu addiereden Zahlen müssen dann aus den Spalten "offen" bzw. "geliefert" ausgelesen werden. Hat einer eine Idee, wie sich das realisieren lässt???

Vielen Dank für die Hilfe!

Gruß
Mox

 

Hallo!

Klingt nach SUMMENPRODUKT:

	A	B
2		offen
3	17.07.2009	95
4	16.07.2009	95
5	15.07.2009	95
6	14.07.2009	95
7	13.07.2009	95
8	12.07.2009	95

Zelle	Formel
B3	=SUMMENPRODUKT(((A3<Basisdaten!C$2:C$999)+(Basisdaten!C$2:C$999=""))*Basisdaten!D$2:D$999)
A4	=A3-1
B4	=SUMMENPRODUKT(((A4<Basisdaten!C$2:C$999)+(Basisdaten!C$2:C$999=""))*Basisdaten!D$2:D$999)
A5	=A4-1
B5	=SUMMENPRODUKT(((A5<Basisdaten!C$2:C$999)+(Basisdaten!C$2:C$999=""))*Basisdaten!D$2:D$999)
A6	=A5-1
B6	=SUMMENPRODUKT(((A6<Basisdaten!C$2:C$999)+(Basisdaten!C$2:C$999=""))*Basisdaten!D$2:D$999)
A7	=A6-1
B7	=SUMMENPRODUKT(((A7<Basisdaten!C$2:C$999)+(Basisdaten!C$2:C$999=""))*Basisdaten!D$2:D$999)
A8	=A7-1
B8	=SUMMENPRODUKT(((A8<Basisdaten!C$2:C$999)+(Basisdaten!C$2:C$999=""))*Basisdaten!D$2:D$999)

 

Mhmm... da stimmt aber etwas nicht, da ja am z.B. 12.7. keine Stücke offen waren... Die 95 offenen wurden ja erst ein Tag später am 13.7. angelegt. Eine weitere Idee? Oder Modifikation?

Gruß, Mox


edit: richtig wäre:
17.07.2009 127
16.07.2009 428
15.07.2009 428
14.07.2009 428
13.07.2009 428
12.07.2009 0

 

Hallo!

Also fließt der Bereich Basisdaten!B2:B999 auch in die Berechnung ein?
Das war aus deiner bisherigen Beschreibung nicht ersichtlich.

=SUMMENPRODUKT((A3>=Basisdaten!B$2:B$999)*((A3<Basisdaten!C$2:C$999)+(Basisdaten!C$2:C$999=""))*Basisdaten!D$2:D$999)

 

Leider immernoch nicht... Jetzt bekomme ich als Ergebnis immer 95 und einmal 0. Mir ist aufgefallen, dass du gar nicht Spalte E abfragst und damit die Lieferungen die später als das Datum "A3" sind, nicht mit als offen addierst. Die in meinem letzten Beitrag erwähnten Ergebnisse müssten sich so ergeben.

Gruß
Mox

 

Hallo!

Ich befürchte, ich verstehe die Aufgabenstellung immer noch nicht...
Kannst du's mir mal konkret am Beispiel 17.07.09 (=127) erklären?
Wie kommst du auf 127?
Welche Zahlen (Zellbereiche) werden hier addiert/subtrahiert und warum?

 

Ganz einfach:
Alle erzeugten Bestellungen kleiner oder gleich 17.7., die offen sind = 95
Alle erzeugten Bestellungen kleiner oder gleich 17.7., die erst nach dem 17.7. beliefert wurden (also am 18.7., 23.8., etc.), waren ja am 17.7. noch offen = 32

95+32=127

Ich hoffe, das hilft dir weiter.

Gruß

 

Hallo... hat denn keiner eine Idee, wie sich das in Excel umsetzen lässt???

 

Hallo!

Dann vielleicht so?

	A	B
2		offen
3	17.07.2009	127
4	16.07.2009	428
5	15.07.2009	428
6	14.07.2009	428
7	13.07.2009	428
8	12.07.2009	0

Zelle	Formel
B3	=SUMMEWENN(Basisdaten!B:B;"<="&A3;Basisdaten!D:D)+SUMMENPRODUKT((Basisdaten!B$2:B$999<=A3)*(Basisdaten!C$2:C$999>A3)*Basisdaten!E$2:E$999)
A4	=A3-1
B4	=SUMMEWENN(Basisdaten!B:B;"<="&A4;Basisdaten!D:D)+SUMMENPRODUKT((Basisdaten!B$2:B$999<=A4)*(Basisdaten!C$2:C$999>A4)*Basisdaten!E$2:E$999)
A5	=A4-1
B5	=SUMMEWENN(Basisdaten!B:B;"<="&A5;Basisdaten!D:D)+SUMMENPRODUKT((Basisdaten!B$2:B$999<=A5)*(Basisdaten!C$2:C$999>A5)*Basisdaten!E$2:E$999)
A6	=A5-1
B6	=SUMMEWENN(Basisdaten!B:B;"<="&A6;Basisdaten!D:D)+SUMMENPRODUKT((Basisdaten!B$2:B$999<=A6)*(Basisdaten!C$2:C$999>A6)*Basisdaten!E$2:E$999)
A7	=A6-1
B7	=SUMMEWENN(Basisdaten!B:B;"<="&A7;Basisdaten!D:D)+SUMMENPRODUKT((Basisdaten!B$2:B$999<=A7)*(Basisdaten!C$2:C$999>A7)*Basisdaten!E$2:E$999)
A8	=A7-1
B8	=SUMMEWENN(Basisdaten!B:B;"<="&A8;Basisdaten!D:D)+SUMMENPRODUKT((Basisdaten!B$2:B$999<=A8)*(Basisdaten!C$2:C$999>A8)*Basisdaten!E$2:E$999)

 

Super Sache Schatzi, funktioniert einwandfrei! Danke.
Jetzt habe ich fast eine Woche versucht mir eine zusätliche Abfrage einzubauen, wieviele von diesen offenen Stücken zu spät waren am jeweiligen Tag... Leider habe ich es nicht geschafft. Kannst du (oder vielleicht weiß es auch jemand anders) mir hier weiterhelfen. Ich stelle mir das gar nicht so schwierig vor, komme aber absolut nicht weiter. Datei anbei.

Gruß
Mox

 

Hallo zusammen,
da dieses Thema für mich relativ dringend wird, habe ich zur Hilfe die Zahlen, die ich als Ergebnis bekommen möchte mal manuell errechnet. Ein Hinweis ist vielleicht zusätzlich noch praktisch: Wenn das Lieferdatum leer ist und das SOLL Datum in der Vergangenheit oder die Gegenwart ist, ist das Stück zu spät. Sollte das SOLL Datum dagegen in der Zukunft liegen, ist das Stück noch als pünktlich anzusehen.

Hier die manuell errechneten Ergebnisse:

offen davon zu spät
17.07.2009 127 127
16.07.2009 428 104
15.07.2009 428 104
14.07.2009 428 104
13.07.2009 428 104
12.07.2009 0 0

Vielleicht lässt sich ja die Formel von Schatzi um diese Bedingung erweitern??

Gruß
Mox

 

Hallo!

Hier mein Vorschlage, der allerdings am 13.07. von deiner Erwartung abweicht:

	A	B	C
1
2		offen	davon zu spät
3	17.07.2009	127	127
4	16.07.2009	428	104
5	15.07.2009	428	104
6	14.07.2009	428	104
7	13.07.2009	428	95
8	12.07.2009	0	0

Zelle	Formel
B3	=SUMMEWENN(Basisdaten!B:B;"<="&A3;Basisdaten!D:D)+SUMMENPRODUKT((Basisdaten!B$2:B$999<=A3)*(Basisdaten!C$2:C$999>A3)*Basisdaten!E$2:E$999)
C3	=SUMMEWENN(Basisdaten!B:B;"<="&A3;Basisdaten!D:D)+SUMMENPRODUKT((Basisdaten!B$2:B$999<=A3)*(Basisdaten!C$2:C$999>A3)*Basisdaten!E$2:E$999*(Basisdaten!F$2:F$999<=A3))
A4	=A3-1
A5	=A4-1
A6	=A5-1
A7	=A6-1
A8	=A7-1

 

Hi,
du hast recht, die Abweichung war mein Fehler. Kannst du mir kurz erläutern, was diese Formel macht, damit ich sei nachvollziehen kann:

=SUMMEWENN(Basisdaten!B:B;"<A3>>> kein Problem kenne ich, verstehe ich!

Aber:

+SUMMENPRODUKT((Basisdaten!B$2:B$999<A3>A3)*Basisdaten!E$2:E$999*(Basisdaten!F$2:F$999<=A3))

Lassen sich hier einzelne Stücke auseinander nehmen?

Wäre gut, wenn du das kurz erläutern könntest. Die Excel-Hilfe hilft mir bei deiner Summenproduktkombination überhaupt nicht weiter.

Ich erhoffe mir, dass ich nicht immer nachfragen muss, sondern es selbst erweitern könnte, wenn indikatoren hinzukommen.

Gruß
Moxos

 

Hallo!

SUMMENPRODUKT((Basisdaten!B$2:B$999<=A3)*(Basisdaten!C$2:C$999>A3)*Basisdaten!E$2:E$999*(Basisdaten!F$2:F$999<=A3))

checkt 3 Bedingungen:
1. Sind die Werte aus Spalte B kleiner-gleich A3
2. Sind die Werte aus Spalte C größer als A3
3. Sind die Werte aus Spalte F kleiner-gleich A3
Treffen alle drei Bedingungen (gleichzeitig) zu, dann werden die entsprechenden Werte aus Spalte E addiert.

Die Multiplikation entspricht hier dem logischen UND. Ein logisches ODER würde hier einer Addition entsprechen.

 

Hi,
jetzt habe ich doch noch einen Fehler gefunden... Ich vermute SUMMEWENN darf nicht verwendet werden, da ich dann sofort alle Stücke ohne Lieferung als zu spät gezählt bekomme. Ich habe das nun so versucht:

=SUMMENPRODUKT((Basisdaten!B$2:B$999<A4>A4)+ISTLEER(Basisdaten!C$2:C$999))*Basisdaten!E$2:E$999*(Basisdaten!F$2:F$999<=A4))

Leider Misserfolg. (Das "+" wollte ich als "oder" verwenden)

Ideen??