komplizierter SVERWEIS

Question

Hallo,
ich habe folgendes Problem und benötige eure Hilfe..
Ich habe eine Ausgabetabelle und eine Datentabelle.
In meiner Ausgabetabelle soll folgendes stehen:
A B C D
1 Produkt Up to Anzahl Listenpreis
2 Milch 50 45
3 Zucker 100 99
4 Eier 150 100
5 Mehl 50 23

In meiner Datentabelle (im gleichen Blatt) stehen die folgenden Werte:
I J K L M N O
1 Produkt Up to 50 Up to 100 Up to 250 Up to 500 Up to 1000 Up to 2500
2 Milch 2,00 1,50 1,20 1,00 0,80 0,50
3 Zucker 1,00 0,95 0,90 0,85 0,80 0,75
4 Eier 2,00 1,50 1,20 1,00 0,80 0,50
5 Mehl 1,00 0,95 0,90 0,85 0,80 0,75

In der Ausgabetabelle sollen die Werte in Spalte D automatisch angezeigt werden, wenn ich das Produkt auswähle, z.B.:
WENN Produkt = Milch UND Up to = 50 dann nimm den Preis 2,00 ; WENN Produkt = Milch UND Up to = 100 dann nimm den Preis 1,50 ; etc.

Das soll dann mit allen Produkten so möglich sein.
Ich habe schon mehrere WENN UND Formeln ausprobiert aber ich bekomme das leider nicht hin..

Kann mir jemand helfen?

Vielen Dank im voraus!!

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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 &quot;Vergleich&quot; hinter Bezug und Matrix den wie Excel es nennt &quot;Vergleichstyp&quot; 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