Hallo smat!

Zwecks Gestaltung der Formel wäre es noch interessant z.B. nur bei Milch ab welchen Mengen welche Preise gelten bzw. und oder in welchen Zellen der Datentabelle die einzelnen mengenabhängigen Milch-Preise stehen.

Vorab ein Beispiel von mir frei erfunden:
Menge bis 99 = € 2,--
Menge ab 100 = € 1,50
Menge ab 150 = € 1,20

Die Mengen stehen z.B. in Spalte C1 bis C3

Die Formel steht in D1:

=SVERWEIS(C1;{0.0;1.2;100.1,5;150.1,2};2)

runterkopieren bis D3

Der Bezug auf Zellen der Datentabelle wäre mit der obigen Formel gegenstandslos.


schöne Grüße

Paul1

Hallo Smat!

Nachtrag!
Nach genauerer Betrachtung Deiner Frage könnte das eine Lösungsvariante sein:

Mit einer SVERWEIS Formel

In der Aufstellung der Listenpreise (ohne die Spalten gezählt zu haben)
z.B. in Spalte A stehen
Milch
Zucker
Eier
Mehl

sagen wir z.B.
A1=Milch
in Spalte C1 steht die Menge
In Spalte D1 wird die Formel für Milch eingegeben:

=SVERWEIS(C1;{0.0;1.2;100.1,5;150.1,2};2)

die Preise gestaffelt wie in der Vorantwort.

Wenn Du jetzt in der Zeile Mich in C1 die Menge eingibst, erscheint der richtige Preis in D1.

Der gleiche Vorgang für Zucker A2 bis D2 usw.(eigene Formel in D2 eingeben)
usw.....

Mit einer Wenn-Formel, sollten die gestaffelten Milchpreise z.B. in H1 bis H3 stehen:
H1 = 2
H2 = 1,5
H3 = 1,2

Für Milch in D1 eingeben:

=WENN(C1>=150;$H$3;WENN(C1>=100;$H$2;WENN(C1>0;$H$1;"")))

Für Zucker eigene Formel in D2

usw....

Alle Produkte wie in der Frage angeführt mit insgesamt 24 verschiedenen Preisen in einer einzigen Formel als Einzeiler kann ich mir eher nicht vorstellen.

Sicher gibt es noch andere Lösungsmöglichkeiten, aber es sollte eine davon sein.

mfg.

Paul1

Hallo,

ich habe hier glaube ich eine Universallösung für dich. Dafür müsstest du nur die Überschriften in der Datentabelle anpassen von
"up to 50" auf 50 , von "up to 100" auf 100 usw.

Ansonsten ist die Tabelle so aufgebaut wie von dir beschrieben, du müsstest die Formeln theoretisch kopieren können:

In der Ausgabematrix setzt du in B2 folgende Formel ein:
=WENN(C2<=50;50;WENN(C2<=100;100;WENN(C2<=250;250;WENN(C2<=500;500;WENN(C2<=1000;1000;2500)))))

Damit wird dir der passende Bereich ausgegeben wenn in Anzahl etwas eingetragen wird.
Aus A2-A4 habe ich Listenfelder gemacht, ist aber reine Geschmackssache.

In Listenpreis setzt du folgende Formel:
=WVERWEIS(B2;$J$1:$O$5;VERGLEICH(A2;$I$2:$I$5);FALSCH)

Damit vergleicht Excel den Wert in "up to" mit der Datentabelle und geht dann in die Zeile wo der Wert steht.
Das heißt bei A2=Zucker und B2 = 100 geht er in die Spalte K und
in die Zeile 3.

Hoffe das funktionert.

Gruß
Folkert

Hallo Folkert!

Wenn Du es getestet hast und es funktioniert, freut mich das für Dich.
Mir fehlt beim Testen in der Spalte D oder sonst wo der Preis!,wenn ich die Menge in C oder sonst wo eingebe. .
Konkrete Angaben was wo steht in der Ausgabe- und Datentabelle wären für mich unbedingt erforderlich um das ganze korrekt nachvollziehen zu können.
Die Wenn-Formel legt nur fest ab welcher Menge, welche Mengenstaffelung in Frage kommt.
In welcher Zelle soll die WVERWEIS-Formel stehen, eventuell in D?

Für mich sehr viele offene Fragen, daher bleibe ich vorläufig bei meinen Varianten, die sind getestet und funktionieren.


Gruß
Paul1

Hallo Paul,

Ausgabe: (Zeile 1 enthält Überschriften)
A2= Warengruppe (Milch/Eier/etc)
B2= Wenn-Formel
C2=Anzahl
D2= Wverweis

Hier hast du Recht, da hat sich ein Fehler eingeschlichen:
Der Vergleich gibt einen zu niedrigen Wert aus. Daher noch +1
=WVERWEIS(B2;$J$1:$O$5;VERGLEICH(A2;$I$2:$I$5)+1;FALSCH)


Daten:
I2-I5= Milch-Mehl
J1= 50
K1= 100
L1= 250
M1= 500
N1=1000
O1=2500

Sollte ja auch deine Lösung nicht obsolet machen sondern nur eine m. E. einfachere Alternative zu der Array-Formel bieten.

Gruß
Folkert

Hallo Folkert!

Habs getestet (Excel 2003)

Deine Lösung ist super bis auf die nachfolgenden Fehlermeldungen bei 2 Produkten.

Ausgabe:

A2 bis D2

Milch 50,00 50,00 2,00
Zucker 50,00 50,00 1,00
Eier 50,00 50,00 #NV
Mehl 50,00 50,00 #NV

Daten:

I1 bis O5


50,00 100,00 250,00 500,00 1000,00 2500,00
Milch 2,00 1,50 1,20 1,00 0,80 0,50
Zucker 1,00 0,95 0,90 0,85 0,80 0,75
Eier 2,00 1,50 1,20 1,00 0,80 0,50
Mehl 1,00 0,95 0,90 0,85 0,80 0,75

In D2 gab ich ein:
=WVERWEIS(B2;$J$1:$O$5;VERGLEICH(A2;$I$2:$I$5)+1;FALSCH)
runterkopiert bis D5

In B2 gab ich ein:
=WENN(C2<=50;50;WENN(C2<=100;100;WENN(C2<=250;250;WENN(C2<=500;500;WENN(C2<=1000;1000;2500)))))

runterkopiert bis B5

Komischerweise für Eier und Mehl gibts keinen Preis (#NV!!).
Milch und Zucker funktioniert aber.
Oder hab ich was falsch gemacht.
Warum nicht alles geht versteh ich nicht, die Bezüge stimmen, ich konnte keinen Fehler sehen (außer die Formel selbst!?)

Es wäre für meine Begriffe alles logisch bis auf das +1, da weiß ich leider nicht wie das in der Formel wirkt.

Vielleicht findest Du die Ursache.


Gruß
Otto

Hab die Lösung, kann aber nicht nachvollziehen, WARUM es nur so so funktioniert:

=WVERWEIS(B2;$J$1:$O$5;VERGLEICH(A2;$I$2:$I$5;0)+1;FALSCH)

Wichtig ist hier im Formelteil "Vergleich" hinter Bezug und Matrix den wie Excel es nennt "Vergleichstyp" mit 0 oder -1 einzusetzen also so: Vergleich(Bezug;Matrix;0) . Dann geht es.


Die +1 sorgt dafür, dass der WVerweis die richtige Zeile nimmt.
Vergleich(a2;I2:I5;0) = 1
Da würde der Wverweis die Spaltenüberschrift nehmen. Durch die +1 geht er in das Feld welches Rechts von der Produktgruppe steht.

Gruß
Folkert