Wie kann ich in einer Excel-Tabelle beliebige Zwischenwerte linear interpolieren lassen?
Hallo liebe Leute,
Ich habe eine Excel-Tabelle mit 8 Spalten. In dem angehängten Bild ist ein Beispiel. Nun möchte ich gerne in der grünen Zeile einen Wert zu einer beliebigen Spalte eingeben. Dieser Wert liegt irgendwo zwischen zwei Werten der Tabelle in der jeweiligen Spalte. Dann soll Excel mir die restlichen Werte der übrigen Spalten in der grünen Zeile durch lineare Interpolation ermitteln. In der angehängten Datei ist dies für a=4,87 zum Beispiel gezeigt. Ich möchte nun aber auch mal d=16,66 eingeben können und die restlichen Werte für a-h bekommen.
Hat jemand eine Idee, wie ich das in Excel umsetzen könnte?
Vielen Dank euch allen und viele Grüße!
8 Antworten
Hallo zusammen
OK, also noch ein Versuch.
In dieser Version wird wunschgemäß jeweils "nur" zwischen den benachbarten Stützstellen interpoliert. Ich habe hier den Ansatz von Iamiam zur Berechnung der Nachbarwerte übernommen, aber auf alle Spalten erweitert. Egal, in welcher Spalte ein neuer Wert eingegeben wird, die Nachbarwerte werden für alle Spalten berechnet.
Außerdem habe ich die Flexibilität eingebaut, im Bereich J2:Q2 einen beliebigen Wert in einer der 8 Spalten vorzugeben. Alle anderen Werte werden berechnet und in J6-Q6 ausgegeben.
J4:Q5 sind Hilfsberechnungen. Hier werden die benachbarten Stützstellen berechnet. Das hätte man in die Formeln zur Berechnung der neuen Werte einfügen können, aber das wäre unleserlich geworden. Ist so schon grenzwertig.
Ein Schmankerl habe ich noch eingebaut: Es ist egal, wie lang die Liste im Bereich A:H ist. Die Länge wird automatisch ermittelt und die Formeln passen sich an. Alle Adressen des Datenbereichs werden auf den Datenstart in Zelle A2 bezogen.
Um diese Flexibilität zu ermöglichen, habe ich die Auswertung neben die Datentabelle verschoben. Natürlich muss man jetzt ein bisschen aufpassen, dass man nicht versehentlich die Formelbereiche überschreibt, aber das versteht sich ja von selbst.
Das File gibt's hier: http://www.filedropper.com/lineareregressionber8spaltenv2
Gruß
Hannes
Super! dank dir! Ich bin mittlerweile den Weg über die SCHÄTZER() Funktion gegangen und würde sagen, dass dein Vorschlag noch ein wenig eleganter ist.
Hi,
Der erste File Upload hat wohl nicht funktioniert. Ich versuche es hier noch einmal.
Das File enthält drei Tabellen mit unterschiedlichem "Automatisierungsgrad". In Tabelle1 ist die Aufgabe noch viel mit Zwischenrechnungen gelöst. Tabelle3 kommt ganz ohne Zwischenrechnungen aus, dafür sind die Matrixformeln aber so unübersichtlich, dass man die Formeln nur sehr mühsam anpassen kann.
Ich habe die Datei auf Filedropper gestellt. Hier der Link:
http://www.filedropper.com/lineareregressionmal8v1
Falls jemand Tabelle3 ohne Download nachbauen möchte, hier die Formeln:
A24:
{=WENN(A23="";(INDEX(B23:H23;VERGLEICH(WAHR;B23:H23<>"";0))-ACHSENABSCHNITT(BEREICH.VERSCHIEBEN($A$2:$A$19;0;VERGLEICH(WAHR;$B$23:$H$23<>"";0));$A$2:$A$19))/STEIGUNG(BEREICH.VERSCHIEBEN($A$2:$A$19;0;VERGLEICH(WAHR;$B$23:$H$23<>"";0));$A$2:$A$19);A23)}
B24:
{=WENN($A23<>"";STEIGUNG(B2:B19;$A$2:$A$19)*$A23+ACHSENABSCHNITT(B2:B19;$A2:$A19);(INDEX($B$23:$H$23;VERGLEICH(WAHR;$B$23:$H$23<>"";0))-ACHSENABSCHNITT(BEREICH.VERSCHIEBEN($A$2:$A$19;0;VERGLEICH(WAHR;$B$23:$H$23<>"";0));$A$2:$A$19))/STEIGUNG(BEREICH.VERSCHIEBEN($A$2:$A$19;0;VERGLEICH(WAHR;$B$23:$H$23<>"";0));$A$2:$A$19)*STEIGUNG(B2:B19;$A2:$A19)+ACHSENABSCHNITT(B2:B19;$A2:$A19))}
Achtung, das sind Matrixformeln. Eingabe mit Ctrl + Shift + Return abschließen.
B24 nach rechts bis H24 ziehen.
Hoffe, die Lösung gefällt und freue mich auf Feedback.
Schönen Tag,
Hannes
ein sehr schönes Konzept, die Steigung bzw Mittelwert der gesamten Spalte zu nehmen! (ist ausserhalb meiner täglichen Praxis, mit geometrischen Formeln wie Steigung hatte ich bisher nichts am Hut...) DH!
Hi,
Du hast das völlig richtig verstanden. Meine Lösung berechnet die lineare Regression jeder einzelnen Spalte (in Abhängigkeit von Spalte a als x-Werte) und interpoliert dann linear.
Wenn ein Wert in einer der Spalten b-h vorgegeben wird, wird zunächst der x-Wert zu diesem y-Wert berechnet. Mit diesem x gehe ich in die Geradengleichungen der anderen Spalten und berechne die jeweiligen y-Werte.
Wenn das nicht passt, kann es natürlich daran liegen, dass die Daten so nicht-linear sind, dass mein Lösungsweg ungünstig ist. Kannst ja mal ein Diagramm der Daten anlegen. Dann sieht man meist schon recht gut, ob es lineare Daten sind.
Wenn Du nur zwischen den benachbarten Stützstellen interpolieren willst, ist der Weg von Iamiam richtig.
Ciao
Hannes
Als Formellösung wird das SO nicht funktionieren - nur als Makro.
In einer Excel-Zelle kann nur entweder ein Wert oder eine Formel stehen. Wenn du die Formel mit dem Wert überschreibst, ist die Formel weg. Wenn die Formel weg ist, wird die Zelle nicht mehr automatisch ausgefüllt.
Denkbar ist, dies in mehreren Zeilen zu lösen: in der ersten Zeile sind die Formeln für Eintrag in Spalte A, in der nächsten für Eintrag in B, usw.
Du hast leider keine Zeilen- und Spaltenköpfe dargestellt, so dass Du anpassen musst. Die Formel wird recht undurchschaubar, so dass ich sie in Teilschritten erkläre. Dein Wert mit 4,8,7 samt 4,85 und 4,90 stehe in Spalte A, der zuzuordnende Wert in Spalte B ff (Formel für Spalte B)
Die 4,87 stehe in A30, rechts daneben und darunter erst mal nichts.
Nun musst Du zuerst die einschließenden 4,85 und 4,90 finden: Die 4,85 mit dieser Formel:
=INDEX(A17:A29;VERGLEICH(A30;A17:A28;1))
sorry, versehentlich zu früh losgeschickt, Weiteres im Kommentar! Bis gleich!
schon mal den ersten Fehler gefunden: Formel für den Wert davor (kleinerer Wert) muss heissen
=INDEX(A1:A29;VERGLEICH(A30;A1:A29;1))
Erklärung, erst mal: =Vergleich() gibt es als
- =Vergleich(Zahl;Suchzellen;FALSCHoder0). FALSCH könnte bedeuten, dass die Formel mit un-(=falsch-)geordneten Listen zurechtkommt, sie liefert aber ebendeshalb nur exakte Treffer, kannst du also hier nicht brauchen.
- =Vergleich(Zahl;Suchzellen;WAHRoder1) findet in WAHR-geordneten und steigenden Listen den nächst niedrigeren Wert, in Deinem Fall also die 4,85 (zählt ab 1.Zelle alle Funde bis zum ersten, der größer ist - diesen dann nicht mehr).
Vergleich gibt die Zellnummer in Suchzellen zurück, fängt man den Suchbereich mit Zeile 1 an, ist das die Zeilennummer (ansonsten muss man korrigieren, noch eine Komplikation!).
Mit Index (analog mit 1 beginnend) kann man diese Zellnummer als WERT abrufen, man erhält also explizit die 4,85. Im Ggs zum SVerweis kann man nun die Zelle darunter mit Vergleich(..;..;1)+1 ansteuern, mit Index ergibt sich daraus der Wert 4,90 Also:
=INDEX(A1:A29;VERGLEICH(A30;A1:A29;1)+1) ergibt 4,90
Das geht übrigens auch mit unregelmäßigen Abständen.
die zugehörigen Werte in B bekommst Du einfach, indem Du die B-Spalte als Indexspalte wählst, also
=INDEX(B$1:B$29;VERGLEICH(A30;A$1:A$29;1)) => 6635,6
=INDEX(B$1:B$29;VERGLEICH(A30;A$1:A$29;1)+1) => 6718,0
Diese Formeln in den klassischen Vierstreckensatz eingebaut (Linie1:A-M-B, Linie 2 D-N-E, Formel: N=(M-A)/B-A)*E-D + D oder so ähnlich formuliert), liefern dann das Ergebnis, das ohne Erklärung kaum nachvollziehbar sein dürfte:
=INDEX(B1:B29;VERGLEICH(A30;A1:A29;1))+(INDEX(B1:B29;VERGLEICH(A30;$1:A29;1)+1)-INDEX(B1:B29;VERGLEICH(A30;A1:A29;1)))/(INDEX(A1:A29;VERGLEICH(A30;A1:A29;1)+1)-INDEX(A1:A29;VERGLEICH(A30;A1:A29;1)))*(A30-INDEX(A1:A29;VERGLEICH(A30;A1:A29;1)))
Ich lege mir für sowas immer Zwischenzellen mit den Teilformeln an und ersetze dann die Adressen der Zwischenzellen durch die Teilformeln darin. Sonst würde ich da auch nicht mehr durchblicken!
Hauptarbeit erledigt, Du brauchst nun aber noch die Ergebnisse für Spalte C:H
Dazu machen wir die Formel nochmals auf und setzen viiieeele $-Zeichen, damit man das einfach horizontal(für C:H) und vertikal (für weitere Zwischenwerte in zB A31, A32 usw ) kopieren kann:
=INDEX(B$1:B$29;VERGLEICH($A30;$A$1:$A$29;1))+(INDEX(B$1:B$29;VERGLEICH($A30;$A$1:$A$29;1)+1)-INDEX(B$1:B$29;VERGLEICH($A30;$A$1:$A$29;1)))/(INDEX($A$1:$A$29;VERGLEICH($A30;$A$1:$A$29;1)+1)-INDEX($A$1:$A$29;VERGLEICH($A30;$A$1:$A$29;1)))*($A30-INDEX($A$1:$A$29;VERGLEICH(A$30;$A$1:$A$29;1)))
Prinzip: für alles in Spalte A muss $A gesetzt werden, ansonsten nur die Zeilen$1:$29. Für A30 darf die Zeile nicht absolut gesetzt werden, wenn weitere Zeilen zur Verfügung stehen sollen, wohl aber die Spalte$A, damit dieser Bezug ebenfalls immer sich auf den genannten Zwischenwert verweist. Habs für Spalte C und weitere Zeilen für Zwischenwerte getestet, konnte keinen Fehler finden.
Nach soviel horizontal und vertikal begebe ich mich nun selbst in die Horizontale!
Interessanter Ansatz, DH!
Wenn ich das richtig verstehe, interpolierst Du zwischen dem nächstkleineren und dem nächstgrößeren Wert. Alle anderen Werte in der Spalte werden vernachlässigt.
Ich habe in meinem Lösungsweg über alle Werte der Spalte interpoliert. Das erschien mir genauer. Und dann ist es auch egal, ob die Werte monoton sind, steigen oder fallen. Was besser zur Fragestellung passt, muss JonnyConnor beantworten.
@Hannes: ja, genauso. Über dire ganze Spalte linearisiert kann aber bedeuten, dass das benachbarte Wertepaar "wegrutscht". Logisch nachvollziehbar, aber ich hab das in meiner Praxis der (Strom-, Wasser-, Gas-)-Zähler-Dokumentation (der interpolierte BasisWert ist das Datum) nicht so machen. Kann sicher andere Anforderungen geben!
...
Zur Formel: Hab doch noch einen Fehler entdeckt: das allerletzte Vergleich muss heissen:
...Vergleich($A30;... , also das Absolutzeichen versetzen.
Bei Verbrauchszählern halte ich Deinen Ansatz auch für besser, weil der Wasser- Strom- Gas-Verbrauch sicher nicht über das ganze Jahr hinreichend konstant ist und die lineare Regression über den gesamten Datensatz daher nicht sinnvoll ist. Da wird man besser zwischen den Nachbarwerten interpolieren.
Hängt halt von der Aufgabenstellung ab. War trotzdem eine sehr interessante Frage.
Hab jetzt mal was gebastelt, wobei ich davon ausgegangen bin, dass Spalte a die X-Werte enthält.
Für die Spalten b-h berechne ich die Koeffizienten der Geradengleichungen für die lineare Interpolation.
Eine neue Zeile dient der Eingabe eines Wertes, der für die Berechnung aller anderen verwendet werden soll.
In der nächsten Zeile werden dann alle Werte berechnet.
Theoretisch könnte man auch noch auf die Hilfsrechnungen verzichten und alles in die Ausgabezeile bauen, das wäre aber nicht mehr gut lesbar.
Ist ein bisschen zu kompliziert, um die Formeln zu posten. Wie kann ich denn die Datei hochladen?
Gruß
Hannes
sorry, im ersten Bild wird nur der Wert in der Spalte a berücksichtigt.
Ich dank dir sehr herzlich (!), aber ich glaube hier geht das nicht. Vielleicht kannst du es über www.ge.tt versuchen und den Link hier posten?
Schau mal hier.
Ich habe im drittenTabellenblatt noch den Weg komplett ohne Hilfsrechnungen eingebaut. Schön zu sehen, dass das geht, aber ich würde die Lösung nicht empfehlen, da sie nicht mehr lesbar und damit sehr wartungsunfreundlich ist. Dann lieber ein paar Hilfszellen nutzen und ggfs ausblenden.
Das war mal 'ne nette Aufgabe. Ich hoffe, Du kannst mit der Lösung etwas anfangen.
Viel Spaß damit - über Feedback würde ich mich freuen.
Hannes
kann das leider nicht downloaden, wahrscheinlich müsste ich mich da anmelden?
Dank dir dafür!
Ich habe versucht diese Vorgehensweise auf meine Tabelle mit ca. 150 Zeilen anzuwenden. Leider passen die Werte dann überhaupt nicht mehr. Verstehe ich dich richtig, dass du eine Regression aus allen Werten bildest, die du dann zur weiteren Berechnung nimmst? Das Problem ist dann wohl, dass die Werte nicht linear steigen. Weshalb ich überlegt hatte nur die zwei Zeilen linear zu interpolieren, die den gesuchten Wert einschließen. Oder bin ich gerade völlig auf dem falschen Weg?....
Grüße