Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Verweisfunktionen - BEITRAG: das Ergebnis zwischen zwei SVERWEIS-Werten interpolieren


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   10  4,25  
2 10    25   
3 20    6  5   
4 4  50    2,5  30   
5 5  80    90   
6 100    3  80   
7 120    10  70   
8 140    80   
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über liegende 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