Verweisfunktionen (3) -> das Ergebnis zwischen zwei SVERWEIS-Werten interpolieren (441)

Aufgabe
Wird bei einer SVERWEIS-Abfrage das Suchkriterium nicht gefunden, kann nur der darunter oder der darüberliegende Wert ausgegeben werden.
Dieser soll interpoliert werden.
Z.B. Suchkriterium 4,25 >>> Wert bei 4: 50 und Wert bei 5: 80.
gewünschtes Ergebnis: 57,5 (80-50 ist 30 und davon 0,25 ist 7,5)

A               B               C               D               E               F        1 1  5    8  10  4,25   2 2  10    2  25    3 3  20    6  5    4 4  50    2,5  30    5 5  80    7  90    6 6  100    3  80    7 7  120    10  70    8 8  140    9  80    9 9  160    9,5  100    10             11   57,5      48,75

Lösung
Das Suchkriterium steht in F1.

einfache Lösung Spalte A (aufsteigend mit Inkrement 1) und Spalte B:

=INDEX(B:B;VERGLEICH(F1;A:A))+REST(F1;1)*(INDEX(B:B;VERGLEICH(F1+1;A:A))-INDEX(B:B;VERGLEICH(F1;A:A)))


komplizierte Lösung Spalte D (unsortiert mit variablen Inkrementen) und Spalte E:

{=INDEX(E:E;VERGLEICH(MAX((D1:D99<=F1)*D1:D99);D:D;0))+(F1-MAX((D1:D99<=F1)*D1:D99))/MAX((MIN(WENN(D1:D99>=F1;D1:D99))-MAX((D1:D99<=F1)*D1:D99));1/9^9)*(INDEX(E:E;VERGLEICH(MIN(WENN(D1:D99>=F1;D1:D99));D:D;0))-(INDEX(E:E;VERGLEICH(INDEX(D:D;VERGLEICH(MAX((D1:D99<=F1)*D1:D99);D:D;0));D:D;0))))}

Erläuterung
Wie wird in diesem (wohl praxisfremden) Beispiel gerechnet?
Das kleinste unter dem Suchkriterium vorhandene Kriterium ist 3 mit Wert 80
Das nächste darüberliegende Kriterium ist 6 mit Wert 5
Die Differenz beträgt -75
6-3=3 und 4,25-3=1,25 >> das ergibt den Faktor 1,25/3 = 0,41666
-75*0,41666 = -31,25 >> 80-31,25 = 48,75


zurück

gestaltet by Biene Maya