ich habe in Excel folgende Daten in einer Arbeitsmappe:
Menge in m 50 100 150 200 250 300
Kosten in € 230 224 219 215 208 200
Eingabe m xx
Es wird also im Feld „Eingabe m“ ein beliebiger Wert zwischen 50 und 300 eingegeben (nehmen wir bspw. 170). Nun soll excel mir vier Werte ausgeben, nämlich die beiden Mengenwerte, zwischen denen die 170 liegt (also 150 und 200) und die dazugehörigen Kosten (also 219 und 215).
Wie kann ich das machen? Meine echte Tabelle ist um einiges größer, habe es mit WENN(und(kleiner/größer… probiert, aber ich fürchte ab einer gewissen Formelgröße macht excel schlapp. Gibt es eine andere Möglichkeit? Verweisfunktion liefert mir nur einen der vier Werte, um aber nachher interpolieren zu können, benötige ich unbedingt alle vier Werte.
da kannst du mit Formeleingaben nicht viel machen und wenn, dann äußerst kompliziert.
Gehe über: „Einfügen“ - „Funktion“ - Dropdown „Statistik“ - Funktion „Schätzer“
Im neu geöffneten Fenster gibst du bei „X“ die Zelle an, in der du den Suchwert einträgst/eintragen willst. Dann in das Feld „Y-Werte“ gehen und markiere den Bereich wo deine „Kosten“ stehen und im Feld „X-Werte“ den Bereich markieren wo deine „km“ stehen - und Enter.
Wenn du jetzt in deinem Suchfeld den Wert „200“ eingibst dann wirst du erkennen, das deine Tabelle nicht so ganz im linearen Trend liegt aber die Interpolation hast du dir gespart.
die gewünschten Werte kannst du mit INDEX und VERGLEICH ermitteln. Zusätzlich muss noch auf den MAX und MIN-Wert geprüft werden, um die Gültigkeit der Eingaben zu prüfen.
Gruß
Franz
Tabellenblattname: Tabelle1
A B C D E F G H
1 Wert: 170 m 217,40 €
2 Wert 1 Wert 2
3 Menge 150 m 200 m
4 Kosten 219 € 215 €
5
6 Menge m 50 100 150 200 250 300
7 Kosten € 230 224 219 215 208 200
Benutzte Formeln:
B3: =WENN(ODER($B$1MAX($C$6:blush:H$6));"out of Range";
INDEX(C6:H6;VERGLEICH($B$1;$C$6:blush:H$6;1)))
B4: =WENN(ODER($B$1MAX($C$6:blush:H$6));"out of Range";
INDEX(C7:H7;VERGLEICH($B$1;$C$6:blush:H$6;1)))
D1: =WENN(B3="out of range";"out of range";WENN(B1=MAX($C$6:blush:H$6);B4;B4+(B1-B3)/(D3-B3)\*(D4-B4)))
D3: =WENN(ODER($B$1MAX($C$6:blush:H$6));"out of Range";
INDEX(C6:H6;VERGLEICH($B$1;$C$6:blush:H$6;1)+1))
D4: =WENN(ODER($B$1MAX($C$6:blush:H$6));"out of Range";
INDEX(C7:H7;VERGLEICH($B$1;$C$6:blush:H$6;1)+1))
SVerweis liefert tatsächlich nur den ersten der gesuchten Werte - und das auch nur dann, wenn die Tabelle sortiert ist (und die Zahlen in einer transponierten Tabelle stehen, also von oben nach unten). Wenn sie umgekehrt sortiert ist, liefert es den zweiten Wert.
Wenn man die ersten beiden Werte hat, sind die letzten beiden Werte recht einfach zu finden - z.B. wieder über SVerweis.
Wenn die Basiswerte wie im Beispiel immer den gleichen Abstand haben, kann man das ganze noch viel einfacher machen - per Rundung! Der untere Wert wäre hier:
=KÜRZEN(ZS(-1)/50)*50
Den oberen Wert erhält man so:
=(KÜRZEN(ZS(-1)/50)+1)*50
wenn Du mit dem SVerweis nicht weiterkommst, weil Du 4 Werte benötigst, würde ich eine Krücke bauen und es mit der Funktion „Verketten“ versuchen. Man kann ja die entsprechenden Spalten später ausblenden.
Wenn Du dann Werte ausgibst, würde ich mit den Funktionen „Links“ und/oder „Rechts“ arbeiten.
Hallo Sandra,
folgender Link könnte Dir helfen. Statt der im Beispiel verwendeten Zahl 12 brauchen Deine 2 Formeln für die Menge den Zellbezug zu Deiner Eingabezelle. Die beiden Formeln für die Kosten würde ich dann als W-Verweis setzen: http://www.excelformeln.de/formeln.html?welcher=204
Gruß Maria
Hallo Sandra182,
ich hoffe, Du kannst mit meiner Antwort noch was anfangen:
Angenommen, die Menge in m liegt in den Zellen B12 bis B17, die Kosten liegen in den Zellen C12 bis C17 und die Eingabe xx liegt in Zelle E8, dann liegen in den folgenden Zellen die folgenden Formeln:
Zelle E12: =WENN(UND($E$8>=B12;$E$8B12;$E$8B12;$E$8=B12;$E$8B12;C13;"")))
Zelle H12: =WENN(F12="";"";WENN(UND(E12=B12;F12=B12);C12;WENN(F12>B12;C13;"")))
Diese Formeln kopiert man relativ bis in Zeile 17.
Dann müssen in den folgenden Zellen die Ergebnis Formeln rein:
Zelle E11: =MIN(E12:E17)
Zelle F11: =MAX(F12:F17)
Zelle G11: =MIN(G12:G17)
Zelle H11: =MIN(H12:H17)