Office 2010: Ermitteln des richtigen Preises mit verschachtelter wenn Formel?

[hiro1990]

Hallo zusammen,

ich brüte hier schon eine Weile über einer Formel...
Ich habe eine Tabelle mit zig tausend Datensätzen.

Die Spalten um die es geht sind folgende:
1) Einkaufspreis alt in €; 2) Einkaufspreis neu in €; 3) Einkaufpreis neu gültig ab Datum
Bsp. A)
1) 2,50€; 2) 3,00; 3) 01.01.2016
Bsp. B)
1) 500€; 2) 550€; 3) 01.09.2011
Bsp. C)
1) 10€; 2) 12€; 3) 06.04.2016

Soweit so gut.

Frage: Nun möchte ich für jeden Monat aus dem Jahr 2015 und 2016 (bis Monat Okt.) herausbekommen (jeder Monat ist also eine Spalte -> hier soll die Formel rein), welcher Preis in dem spezifischen Monat gegolten hat.

Nehmen wir einfach mal den Monat 01.2015. Wenn ich das dann mal auf die obigen Bsp. übertrage sollte in den Zellen stehen:
Bsp. A)  2,5€; Bsp. B) 550€; Bsp. C) 10€
Nehmen wir einmal den 01.2016:
Bsp. A)  3,0€; Bsp. B) 550€; Bsp. C) 10€
Nehmen wir einmal den Monat 08.2016:
Bsp. A)  3,0€; Bsp. B) 550€; Bsp. C) 12€

Ich hoffe ihr versteht was ich meine...

Kann ich das irgendwie mit einer verschachtelten Wenn Formel lösen?

Ich hatte oben ja exakte Datumstage angegeben, diese brauchen aber nicht Taggenau sein, Monatsgenau ist völlig ausreichend...

Danke für eure Antworten

MfG

[steve1da]

Hola,

hier wäre einem Beispieldatei hilfreich.

Gruß,
steve1da

[hiro1990]

Hola zurück,
gute Idee... evtl. war meine Beschreibung ein wenig zu verzwickt 
Siehe anbei.
LG

[gmg-cc]

Moin,
2 Dinge gefallen mir gar nicht in deiner Musterdatei:

• E2:AB2 sind Texte und keine kalendarischen Daten. Da gehört das "ecte" Datum rein, welches du dann mit einem benutzerdefinierten Zahlenformat zu diesem etwas ungewöhnlichen Aussehen bringen kannst.
• Die Reihenfolge der Daten in Zeile zwei ist auch falsch, die gesamte Reihenfolge muss mit 2015 beginnen und mit Dezember 2016 enden.

Danach ist eine Auswertung mit Formeln durchaus denkbar. Allerdings: so ganz verstehe ich noch nicht, was du wirklich erreichen willst. Aber stelle uns doch erst einmal die neu formatierte Tabelle ein...

[hiro1990]

Moin,

auch dir danke für deine Hinweise.. habe deine Punkte angepasst und  mal eine zweite (ein wenig erweiterte) Version eingestellt..
(zur Betrachtungsweise erst Jahr 2015 und dann Jahr 2016 -> mein Gedanke war, zuerst dieses Jahr aufzuzeigen, weil aktueller als das Vorjahr..
sei es drum. m.E. gehopst wie gesprungen..  )

Nun, was ich damit will ist Folgendes:
ich habe von jedem der angegebenen Monate die Einkäufsumsätze von allen Artikeln in Stck vorliegen..
(Bsp. für den Jan 15: Artikel A = 4 Stck.; Artikel B = 1 Stck.; Artikel C = 25 Stck. usw.)

Nun will ich rückwirkend für jeden Monat schauen wie hoch die Einkaufsumsätze in € sind
(also am Ende: Eingekaufte Stck. x Einkaufspreis in € -> in unserem Bsp. für Jan15 bei Artikel A wäre das dann: 4 Stck. x 2,5 € =  10 €)..

...und um das zu ermitteln muss ich wissen welcher Preis für Produkt XYZ in welchem Monat gegolten hat..
(in unserem obigen Bsp. ob ich mit dem "alten" Preis von 2,5€ kalkuliere, oder mit dem "neuen" Preis von 3€, welcher am 01.01.2016 in Kraft getreten ist...??)

Ich habe dieses auch mal exemplarisch für den Monat Jan15 in der Excel versucht aufzuzeigen 

Hoffe das kam verständlich rüber. Bei Fragen einfach melden..

Vielen Dank schonmal für Antworten

[maninweb]

Hallo,

wenn ich das richtig verstehe, möchtest Du aus H bis AD den Wert zu einem bestimmten Monat ermitteln, richtig?

Angenommen, in Zelle G1 steht ein Datum (also echtes Datum), z.B. 1.8.2016, dann kannst Du den Wert aus
den Monatsspalten wie folgt ermitteln, in dem Fall für Zeile 4:

=SUMMENPRODUKT(($H$2:$AD$2>DATUM(JAHR($G$1);MONAT($G$1);1)-1)*($H$2:$AD$2<DATUM(JAHR($G$1);MONAT($G$1)+1;1))*($H4:$AD4))

oder kürzer:

=INDEX($H4:$AD4;1;HÄUFIGKEIT($H$2:$AD$2;$G$1))

Erste Formel mag jedoch keine X-e in den Werten in Spalte H bis AD. Zweite Formel ignoriert die X-e bzw.
liefert auch diese zurück. Zweite Formel ist irgendwie cooler :-)

Gruß

[hiro1990]

Hi,

mhhh. Danke für deinen Gehirnschmalz..
Aber ich glaube ich wurde noch nicht richtig verstanden...

Wenn du dir die von mir eingestellte Excel einmal zur Hand nimmst, versuche ich es anhand eines Bsp. nochmal zu erklären.
Einfach mal Zelle E4 betrachten (also Monat Jan-15).
Dort habe ich "manuell" die Verlinkung (hier in E4 soll eigentlich die von mir gesuchte Formel drin stehen!) zur Zelle B4 gemacht, weil in B4 die 2.5€ stehen.
Die 2,5€ ist ja der korrekte Einkaufspreis welcher im Jan-15 gegolten hat
(Bezug zur Zelle D4 wo ja 01.01.2016 drin steht -> der neue Preis gilt erst ab diesem Datum! deshalb ist ja die 3€ aus Zelle C4 falsch und die 2,5€ richtig).

Im Prinzip geht es immer nur darum für jeden einzelnen Artikel (und es sind tausende..) in Spalte E (und hinterher auch Spalte H, I, J usw. -> fuer alle Monate) einfach zwischen dem Einkaufspreis "alt" (Spalte B) und Einkaufspreis "neu" (Spalte C) zu wählen und das für quasi jeden Monat.
Und die Grundlage dieser Auswahlentscheidung ist ja Spalte D (wo das Datum steht ab dem der neue Einkaufspreis gegolten hat) und Zeile 2 wo ja die einzelnen Monate stehen..

Ich hoffe ihr habt mich jetzt verstanden   

Danke für eure Antworten

[gmg-cc]

Moin,

Im Prinzip geht es immer nur darum für jeden einzelnen Artikel (und es sind tausende..)

Im Prinzip habe ich solch eine Konstellation schon ziemlich oft verwirklicht. Allerdings mit dem "kleinen" Unterschied, dass ich eine adäquate Software (meistens MS Access) verwendet habe. Solche Abfragen sind dort einfach an der Tagesordnung ...

Prinzipiell wäre das auch unter Excel möglich. Dann aber mit einer getrennten Tabelle, wo die ArtikelNr, das Datum der Preisänderung (inkl. des ersten Preises) und der dann jeweils aktuelle Preis drin steht. Diese Tabelle ist nach Artikelnummer und Datum geordnet. Dort kann dann mit einer Formel (oder mit VBA) der passende Preis gefunden werden.

[maninweb]

Hallo,

wenn D4 das Referenzdatum ist, dann ginge auch E4=INDEX($H4:$AD4;1;HÄUFIGKEIT($H$2:$AD$2;D4)) und AutoAusfüllen.

Aber (!) die Werte in Spalte D müssen ein Datum sein und kein Text.

Gruß

[hiro1990]

Hi,

@gmg-cc: leider kein Access. Kann gerne mit Hilfstabellen oder Hilfspalten o.ä. arbeiten.. Kein Problem. Wie würdest du diese denn aufbauen? Und wie lautet die Formel?

@maninweb: ok. Spalte D als Datum definieren leuchtet mir ein...
Deine vorgeschlagene Formel mit "Häufigkeiten" ist glaube nicht das was ich suche..

Ich glaube ich wurde noch nicht richtig verstanden.. denke in der Excel waren zuviele Spalten (und Zeilen) die nur verwirren. Ich habe mal eine neue, simplifiziertere Excel eingestellt mit nur einem Bsp.

Siehe anbei. Hoffe das Ganze wird nun klarer welche Formel ich suche (wie gesagt, wenn mit Hilfstabellen oder Spalten gearbeitet werden muss, dann ist das eben so..).

Danke

[maninweb]

Hallo,

Ok, dann hoffe ich mal, dass ich dann nachvollziehen kannst, was Du möchtest. Also, ich verstehe das jetzt so: pro Zeile soll
nachgeschaut werden, ob das Datum in E2 (welcher Preis war gültig im Monat = Jan 15) kleiner bzw. älter ist, als das angege-
bene Datum in Spalte D. Ist das der Fall, soll Einkaufspreis Alt verwendet werden, ansonsten dann der Einkaufspreis Neu.

Dann hat das tatsächlich nichts mit den Spalten rechts (aus der Vorversion) zu tun und Du kannst das mit einer einfachen
Wenn-Abfrage machen:

Formel in E4=WENN($E$2<D4;B4;C4)

Die Formel setzt voraus, dass in Spalte D auch Datumswerte stehen und keine Texte. Hast Du nur dort Texte, kannst
Du die Formel versuchen:

=WENN($E$2<DATWERT(D4);B4;C4)

Besser ist aber definitiv aus den Text-Datumswerten echte Datumswerte zu machen.

Gruß

[hiro1990]

Exactamente. Genau das ist es. Gracias.