Datum in Datumsbereich suchen

Bild

Betrifft: Datum in Datumsbereich suchen
von: Mario
Geschrieben am: 06.08.2015 16:05:06

Hallo Forumsgemeinde,
stecke da ein wenig in einem Dilemma bei dem ich nicht mehr weiter weiß.
Grob gesagt habe ich einen Kontrakt in Tabellenblatt A mit einem bestimmten Datum. Habe den selben Kontrakt in Tabellenblatt B allerdings mit unterschiedlichen Datumsbereichten. Nun möchte ich mit dem Datum aus A den Datumsbereich in B suchen und einen Preis dazu ausgeben.
Weiters gibt es auch noch Staffelpreise also kommt noch dazu wenn der Datumsbereich gefunden wurde sollte noch ermittelt werden welcher Staffelpreis korrekt ist.
Habe eine Beispieldatei angehängt, da es relativ komplex ist das zu erklären.
https://www.herber.de/bbs/user/99366.xlsx
Ich wäre ja schon froh wenn ich zumindest für die Suche im Datumsbereich eine Antwort bekäme, da stehe ich nämlich echt komplett an. Danke für eure Hilfe.
mfg
Mario

Bild

Betrifft: mit INDEX() und AGGREGAT() ...
von: der neopa C
Geschrieben am: 06.08.2015 17:05:20
Hallo Mario,
... kann man den Grundpreis so ermitteln:

 G
28,23
37,63
410

Formeln der Tabelle
ZelleFormel
G2=WENNFEHLER(INDEX(Kontrakte!A:Z;AGGREGAT(15;6;ZEILE(A$1:A$99)/(Kontrakte!A$1:A$99<=B2)/(Kontrakte!B$1:B$99<=C2)/(Kontrakte!D$1:D$99<=E2)/(Kontrakte!E$1:E$99>=E2); 1); AGGREGAT(14;6;{7.9.11.13}/(F2>={0.12.24.36})/(Kontrakte!G2:J2>0)/(Kontrakte!H$1:H$99>0); 1)); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Zu den Staffelpris bedarf es noch ein paar Angaben Deinerseits. Sind es immer die gleichen Staffelmengen? Können die wirklich teilweise entfallen wie in Zeile 5.
Mehr dazu Morgen mehr. Muss jetzt gleich offline gehen.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: mit INDEX() und AGGREGAT() ...
von: Mario
Geschrieben am: 07.08.2015 08:44:45
Hallo Werner,
danke vielmals für deine Hilfe. Bezüglich Datumsbereich bin ich gestern noch spätabends zu einer Lösung gekommen. Geht im Prinzip ganz einfach über Summenprodukt. Da muss man aber halt auch erst mal draufkommen :-)
Mal umgelegt auf die angehängte Datei
SUMMENPRODUKT(($b2=Kontrakte!$a$2:$a$10)*(c2=Kontrakte!$b$2:$b$10)*($e2>=Kontrakte!$d$2:$d$10)*($e2<=Kontrakte!$e$2:$e$10)*(Kontrakte!$G$2:$G$10))
Damit habe ich zumindest mal die richtige Zeile und ggf. den richtigen Grundpreis.
Zur Erklärung Staffelpreis:
Es gibt Positionen bei denen es einen Staffelpreis gibt. Wenn nicht gilt der Grundpreis (den hab ich jetzt ja schon). Ob es einen Staffelpreis gibt oder nicht kann ich im Prinzip über Summenoprodukt auf Spalte F ermitteln (habe das ja/nein einfach auf 1/0 umgeschrieben)
Wenn es also jetzt einen Staffelpreis gibt dann muss immer der Preis der nächstkleinsten Menge verwendet werden.
Also wenn ich jetzt einen Staffelpreis habe zB 15 Stück müsste der Preis aus Spalte K gezogen werden.
Gibt es eine einfachere Möglichkeit als eine verschachtelte Wenn-Formel (es gibt nämlich bis zu 6 Staffelpreise) aus der Menge im Übersichtsblatt, in dem Fall halt 15 Stück im Bereich H:J in der richtigen Zeile die nächstkleinere Menge zu ermitteln und davon ausgehend den richtigen Preis?
Kontrakte

 ABCDEFGHIJKLM
1KontraktPosKonditionGültig abGültig bisStaff. Vorh.GrundpreisStaffmeng1Staffmeng2Staffmeng3Staffbetrag1Staffbetrag2Staffbetrag3
2555500010118.02.201405.05.201517,631224367,637,487,32
3555500010206.05.201531.12.201517,541224367,547,397,23
4555500010301.01.201631.12.999917,631224367,637,487,32
5456400010301.01.201531.12.2015010,00      


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Danke für deine/eure Hilfe.
mfg
Mario

Bild

Betrifft: dazu merke ich an ...
von: der neopa C
Geschrieben am: 07.08.2015 13:39:49
Hallo Mario,
... im speziellen Fall kann SUMMENPRODUKT() zum gewünschten Ergebnis führen. Ich rate trotzdem von dessen Anwendung ab. Es kann bei entsprechender Datenkonstellation zu fehlerhaften Ergebnissen führen.
Bzgl. der Staffelpreise hast Du meine Fragen von gestern noch nicht beantwortet. Natürlich geht es ohne WENN(). Doch dazu wäre es zumindest einfacher, wenn ich die Antwort auf meine Fragen kenne.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dazu merke ich an ...
von: Mario
Geschrieben am: 10.08.2015 10:51:00
Hallo Werner,
sorry, das hab ich übersehen. Ja es gibt unterschiedliche Staffelmengen mit unterschiedlichen Staffelpreisen, das variiert je nach Material und ja es gibt auch Materialien ohne Staffelpreis (ist eigentlich der großteil).
Grundsätzlich scheint das mit dem Summenprodukt zu funktionieren. Darüber finde ich ja auch ob ein Staffelpreis vorhanden oder nicht. Jetzt würde es eigentlich nur mehr darum gehen bei einer Staffel die richtige Menge und den dazugehörigen Preis auf einfache Art rauszubekommen.
Danke.
mfg
Mario

Bild

Betrifft: meine Fragen sind noch offen ...
von: ... neopa C
Geschrieben am: 10.08.2015 12:23:27
Hallo Mario,
... dazu in meinen entsprechenden Beitrag von vergangener Woche.
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Datum in Datumsbereich suchen"