So interpolieren Sie Zwischenwerte für beliebige Daten in Excel

Question

Ich habe Datentabellen wie in diesem Beispiel, in diesem Fall neun Einträge bei A1:B9:

A    B
--   ---
1    2.9
2    5.06
3    7
4    8.84
5    10.87
6    13.24
7    16.22
8    20.25
9    36.7

Das Obige stellt neun Messungen einer nicht linear ansteigenden physikalischen Variable in B dar, z. B. Spannung, und A stellt genau jede der neun Rundminuten dar, in denen die Messung durchgeführt wurde.

Ich möchte eine zweite Tabelle erstellen, Spalten E und F, mit einer Zeilenanzahl, die der „nächsten Ganzzahl“ für den höchsten Wert in Spalte B entspricht. In diesem Fall ist B9 = 36,7, also hat sie 37 Zeilen. Spalte F1:F37 enthält die Ganzzahlen 1 bis 37, Spalte E muss numerische Werte enthalten, die F entsprechen, in derselben Beziehung wie zwischen den Spalten A und B. Mit anderen Worten, interpolieren Sie die Werte in Spalte E entsprechend den Werten in Spalte F.

Beispielsweise ist A3 = 3 und B3 = 7. In diesem Fall ist F7 = 7 und E7 = 3, da B bereits die Ganzzahl 7 enthält und einen entsprechenden Wert in Spalte A hat. Allerdings ist F8 = 8, was ein Zwischenwert ist, der nicht in Spalte B enthalten ist. Daher liegt E8 basierend auf den Originaldaten zwischen 3 und 4 und muss interpoliert werden.

Die Idee ist, dass beim Zeichnen eines Diagramms A1:B9 dieselbe Form wie E1:F37 hat. In diesem Beispiel werde ich die Datentabelle auf 37 ganzzahlige Ergebnisse erweitern, die im Verlauf der ursprünglichen Messungen aufgetreten wären, und sehen, zu welchem ​​Zeitpunkt (in Spalte E, mit Dezimalstellen) diese Werte aufgetreten wären.

Was ich versucht habe

Beim Versuch, dies selbst zu lösen, konnte ich eine zeitaufwändige Formel finden (beachten Sie, dass bei meinem Versuch meine Spalten E und F im Vergleich zu dem, was ich oben beschrieben habe, vertauscht sind).

1. Ich habe eine Spalte (K) erstellt, die die Differenz zwischen den Elementen der Spalte B enthält. K5 = B5-B4. Das ist die Y-Verschiebung für jedes X-Inkrement.
2. Spalte E enthält, beginnend bei 1, so viele aufeinanderfolgende Ganzzahlen (37) wie der nächste Ganzzahlwert des größten Elements in B. In diesem Fall enthält B9 36,7, also 37.
3. Bei F1:F37 gebe ich folgende Formel ein.

Zelle F1 enthält:

=IF(E1>$B$9,$A$9+(E1-$B$9)/$K$9,IF(E1>$B$8,$A$8+(E1-$B$8)
    /$K$9,IF(E1>$B$7,$A$7+(‌​E1-$B$7)/$K$8,IF(E1>$B$6,$A$6+(E1-$B$6)
    /$K$7,IF(E1>$B$5,$A$5+(E1-$B$5)/$K$6,IF(E1‌​>$B$4,$A$4+
    (E1-$B$4)/$K$5,IF(E1>$B$3,$A$3+(E1-$B$3)/$K$4,IF(E1>$B$2,$A$2+
    (E1-$B$2‌​)/$K$3,IF(E1>$B$1,$A$1+(E1-$B$1)/$K$2,E1/$K$1)))))))))  

Es funktioniert ziemlich gut. Aber es ist keine automatisierte Formel; man muss so viele „WENNs“ eingeben wie Elemente in den Spalten A+B (X+Y). Ich habe Streudiagramme mit Linien von A1:B9 und E1:F37 getestet (umgekehrt für die richtige X/Y-Reihenfolge), und sie haben genau die gleiche Kurvenform erzeugt, also funktioniert es.

Dies ist jedoch keine effektive Lösung, da für jeden Datensatz ein mühsamer, individueller und manueller Prozess erforderlich ist. Ich suche nach einer Möglichkeit, dies mithilfe von in Excel integrierten Funktionen automatisierter zu erreichen, oder zumindest nach einem allgemeineren Ansatz mithilfe von Formeln.

Answer 1

Kurze Antwort

Die Interpolation basiert auf einer Gleichung, die X- und Y-Werte in Beziehung setzt. Wenn Sie die eigentliche Gleichung kennen, können Sie alle gewünschten Zwischenwerte direkt berechnen. Wenn Sie sie nicht kennen, interpolieren Sie mithilfe einer Näherung. Die Qualität der Näherung bestimmt, wie genau Ihre Zwischenwerte sein werden. Die lineare Interpolation ist grob, wenn Sie eine Kurve mit einer begrenzten Anzahl von Punkten approximieren. Es gibt mehrere andere Ansätze, die Ihnen bessere Ergebnisse liefern, und integrierte Analysetools, die den Großteil der Arbeit übernehmen.

Lange Antwort

Sie suchen nach einer „allgemeinen Formel“ oder Lösung, die die Interpolation von Zwischenwerten automatisiert. Sie können die lineare Interpolation für so ziemlich alle Daten verwenden, aber die Ergebnisse werden grob sein, wenn es nur eine begrenzte Anzahl von Datenpunkten und eine signifikante Krümmung in der Form der Daten gibt. Es gibt keine „Einheitslösung“, wenn Sie Genauigkeit wollen. Die beste Lösung für einen bestimmten Datensatz hängt von den Eigenschaften der Daten ab.

Die gleichung

Egal wie Sie es machen, die Interpolation erfolgt mithilfe einer Gleichung, die die Beziehung zwischen X und Y definiert. Die Gleichung ist entweder die tatsächliche Gleichung oder eine Schätzung. Wenn es sich um eine Schätzung handelt, gibt es eine Reihe unterschiedlicher Ansätze, die von der Art der Daten und dem, was Sie erreichen möchten, abhängen.

In Ihrer anderen Frage haben Sie Daten verwendet, die auf der Gleichung basieren Y=2^X. Wenn Sie die eigentliche Gleichung haben, können Sie genau interpolieren. Wählen Sie einen neuen Wert für entweder Xoder Yund die Gleichung gibt Ihnen den anderen Wert. Wenn Sie die eigentliche Gleichung nicht kennen, müssen Sie eine finden, die sie annähert. Ich werde diese Antwort verwenden, um mich auf Interpolationsansätze zu konzentrieren. Diese verwenden im Allgemeinen integrierte Analysetools, die den Großteil der Arbeit erledigen. Wenn Sie weitere Einzelheiten zur Mechanik der Verwendung eines bestimmten Tools oder eines automatisierteren Ansatzes benötigen, können wir das in einer anderen Antwort näher erläutern.

Versuchen Sie, die eigentliche Gleichung zu finden

Die beste Lösung besteht darin, zu versuchen, die tatsächliche Gleichung zu ermitteln. Wenn Sie den Prozess kennen, der die Daten generiert hat, kann Ihnen das Aufschluss über die Art der Gleichung geben. Viele Prozesse folgen unter kontrollierten Bedingungen, d. h. wenn Sie mit einer einzigen treibenden Variable und keinem zufälligen Rauschen arbeiten, einer einfachen Kurve, deren Art der Gleichung bekannt ist. Der erste Schritt besteht also darin, sich die Form der Daten anzusehen und zu prüfen, ob sie einer dieser Gleichungen ähnelt.

Eine einfache Möglichkeit hierfür besteht darin, die Daten grafisch darzustellen und eine Trendlinie hinzuzufügen. In Excel stehen eine Reihe gängiger Kurven zur Verfügung, die Sie anpassen können.

Versuchen wir dies mit den 2^NDaten aus Ihrer anderen Frage. Wenn Sie das Zahlenmuster nicht erkannt und den Trendlinienansatz ausprobiert hätten, würden Sie die Symbole unterschiedlich geformter Kurven sehen. Die Exponentialkurve hat im Allgemeinen dieselbe Form und würde Ihnen Folgendes liefern:

Excel verwendet als Basis eund nicht , was nur eine Übersetzung ist (e ^0,693 ist ). Visuell können Sie sehen, dass die Trendlinie genau den Daten folgt. Das R ² sagt Ihnen das auch. R ² ist ein statistisches Maß dafür, wie viel der Variation in den Daten Sie mit Ihrer Gleichung berücksichtigen. Der Wert bedeutet, dass die Gleichung 100 % der Variation berücksichtigt oder eine perfekte Anpassung darstellt.221

Das Beispiel in dieser Frage hat ebenfalls eine Art Exponentialform. Wenn Sie denselben Ansatz versuchen, erhalten Sie dieses Ergebnis:

Diese Daten sind also nicht exponentiell. Wir können es mit einem Polynom versuchen, das einige natürliche Prozesse beschreibt und in der Lage ist, eine Vielzahl von Kurven nachzubilden (darüber werde ich später mehr sprechen):

Als Annäherung an den Prozess, der den Daten zugrunde liegt, ist dies keine gute Übereinstimmung. In der dritten Ordnung (eine Gleichung mit Potenzen von X bis X^3) hat sie mehr große Wendepunkte als die Daten und passt trotzdem nicht. Die zugrundeliegende Gleichung sieht also nicht wie eine einfache, gemeinsame Kurve aus, was bedeutet, dass die Gleichung angenähert werden muss.

Lineare Interpolation

Dies ist der Ansatz, den Sie in Ihren Kommentaren beschreiben. Er ist unkompliziert, verwendet eine einfache Formel und lässt sich relativ einfach automatisieren. Er kann ausreichend sein, wenn Sie viele Punkte haben und die geraden Linien zwischen ihnen nahe genug beieinander liegen. Auf vielen Kurven werden kurze Abschnitte einiger Bereiche nahe an geraden Linien liegen. Für eine gekrümmte Linie ist dies jedoch eine schlechte Annäherung, und Ihre Ergebnisse werden in Bereichen mit einer signifikanten Krümmung ungenau sein. In Ihrem Beispiel würde der Bereich zwischen den X-Werten 7 und 8 eine starke Krümmung aufweisen. In diesem Bereich würde eine gerade Linie im Vergleich zur tatsächlichen Kurve folgendermaßen aussehen:

Sie suchen nach einer allgemeinen Lösung, die auf alle Daten anwendbar ist. Möglicherweise stellen Sie fest, dass die lineare Interpolation für einige Daten zu grob ist.

Regression

Hier und in anderen Beiträgen wurde Regression als Ansatz vorgeschlagen. Dies kann mithilfe von Trendlinien oder den ihnen zugrunde liegenden Arbeitsblattfunktionen oder den Analysetools erfolgen (ich glaube, das könnte im Analyse-Toolkit enthalten sein, was möglicherweise das Laden dieser Option in Excel erfordert, da sie möglicherweise nicht standardmäßig geladen ist).

Bei der Regression wird versucht, eine Kurve an Ihre Daten anzupassen, um den Gesamtfehler zwischen den Daten und der Kurve zu minimieren. Normalerweise ist dies nicht das richtige Werkzeug für diese Aufgabe (es ist die Methode, die zum Anpassen der Trendlinien verwendet wird, und Sie haben gesehen, wie diese im Vergleich zu dem, was Sie benötigen, abschneidet).

• Es ist für Situationen gedacht, in denen Ihr Ziel darin besteht, den Prozess hinter den Daten zu modellieren. Die Daten werden als ungenau angenommen und die Regression legt nahe, was sie wirklich sein sollen. Die durch die Regression gefundene Kurve verläuft möglicherweise nicht durch einen der tatsächlichen Datenpunkte. In Ihrem Fall sind die Daten gegeben und werden als genau angenommen. Die Kurve muss durch jeden Punkt verlaufen.

• Bei der Regression wird versucht, eine einzige Gleichung auf alle Daten anzuwenden. Dies ist jedoch nicht effektiv, wenn der Prozess, der die Daten erstellt hat, nicht durch die verfügbaren Gleichungstypen beschrieben wird. Bei vielen Datenpunkten kann die lineare Interpolation jedes Segments eine bessere Annäherung sein als eine Regressionskurve für alle Daten.

Anstatt sie jedoch auf die übliche Weise einzusetzen, kann die Regression als Workaround für das, was Sie wollen, „missbraucht“ werden, und das wird normalerweise funktionieren. Wenn Sie versuchen, einen Prozess zu modellieren, ist normalerweise die einfachste Formel von Nutzen (Ockhams Rasiermesser). Andererseits können Sie mit einer ausreichend komplexen Gleichung alles anpassen. Sie können immer eine Skizze zeichnen, die durch jeden Punkt verläuft. Mit NPunkten können Sie eine N-1polynomische Gleichung der Ordnung finden, die durch alle Punkte verläuft (Worst-Case-Szenario).

Ich sage „normalerweise“, weil es in manchen Fällen eine ziemlich verdrehte Linie ist, die für Ihren Zweck unbrauchbar wäre. Und beachten Sie, dass dieser Ansatz nicht wirklich etwas „modelliert“ in dem Sinne, dass die resultierende Gleichung ein Verhalten außerhalb des Datenbereichs vorhersagen würde.

Hier ist eine Analyse Ihrer Daten mithilfe einer polynomischen Regression mit Gleichungen sukzessive höherer Ordnung (der erste Screenshot umfasst die Ordnungen 3 bis 5):

(Klicken Sie auf das Bild, um es in lesbarer Größe anzuzeigen.) Beachten Sie, dass das Analysetool die Art der Interpolation enthält, die Sie durchführen möchten; es hat die Zwischenwerte generiert. Für jede Analyse a(n)sind die Werte die Koeffizienten der gefundenen Gleichung. a(0)ist eine Konstante, a(1)ist der Koeffizient für den X^1-Term usw. Es zeigt den R ² -Wert der Anpassung. Er muss praktisch sein, 1um für Ihren Zweck nahe genug zu sein.

Ich habe die ursprünglichen Datenwerte mit den größten Unterschieden hervorgehoben. In diesem Ordnungsbereich wird die Übereinstimmung mit jeder weiteren Ordnung etwas besser, aber welche spezifischen Punkte genauer beschrieben werden, kann sich ändern. Hier ist ein Diagramm dieser drei:

Wenn wir zum Polynom 6. und 7. Ordnung kommen, sieht es folgendermaßen aus:

Wenn wir für Ihre 9 Werte ein Polynom 8. Ordnung verwenden würden, wäre es perfekt, aber die 7. Ordnung ist wahrscheinlich nah genug dran. Zur Veranschaulichung sei angemerkt, dass die Gleichung 7. Ordnung ein R ² von .99999 hat und trotzdem nicht perfekt ist.

Wenn Sie das Regressionsanalysetool verwenden, um eine geeignete Anpassung zu finden (in diesem Fall die Gleichung 7. oder 8. Ordnung), erhalten Sie die gewünschten Zwischenwerte. Es ist jedoch eine gute Idee, das Ergebnis in einem Diagramm darzustellen und die Kurve zu mustern, um sicherzustellen, dass es sich nicht um eine Skizze handelt.

Splines

Wenn Sie Ihre Daten in einem Diagramm darstellen und die Option für glatte Linien auswählen, verwendet Excel zur Erstellung dieser Linien Splines. Tatsächlich basiert fast jede Anwendung der Computergrafik (einschließlich Schriftdefinitionen) auf Splines für glatte Kurven und Kurvenübergänge. Der Name geht auf die flexible Regel zurück, die Zeichner früher verwendeten, um beliebige Punkte mit einer Kurve zu verbinden.

Splines erstellen die Kurve für jeden Abschnitt, Abschnitt für Abschnitt, unter Berücksichtigung der angrenzenden Punkte. Die Kurve verläuft durch jeden Punkt und es gibt keine abrupten Änderungen auf beiden Seiten des Punkts, wie dies beim Verbinden der Punkte mit geraden Linien der Fall ist.

Die für Splines verwendeten Gleichungen versuchen nicht, den Prozess zu modellieren, der die Daten erzeugt hat; sie dienen lediglich der Optik. Die meisten Prozesse folgen jedoch einer Art kontinuierlicher, glatter Kurve. Wenn Sie mit einem einzelnen Kurvensegment arbeiten, erzeugen viele verschiedene Gleichungen, die Kurven mit im Allgemeinen ähnlicher Form erzeugen, sehr ähnliche Werte innerhalb des Segments. In den meisten Fällen erzeugen Splines also eine gute Annäherung an das, was Sie wollen (und sie verlaufen natürlich durch jeden Punkt, im Gegensatz zur Regression, die durch jeden Punkt gezwungen werden muss).

Auch hier sage ich „in den meisten Fällen“. Splines funktionieren hervorragend bei Daten, die ziemlich einheitlich und regelmäßig sind und den „Regeln“ für eine Kurve folgen. Bei ungewöhnlichen Daten können sie einige unerwartete Dinge bewirken. Zum Beispiel einvorherige SU-Frageging es um diesen merkwürdigen negativen „Einbruch“ im von Excel aus den Daten erstellten Diagramm:

Splines sind ein bisschen wie Wackelpudding. Stellen Sie sich einen großen Klumpen Wackelpudding vor und Sie begrenzen bestimmte Stellen, wo Sie sie haben möchten. Der Rest des Wackelpuddings wölbt sich an den erforderlichen Stellen. Eine Gleichung kann bestimmte Arten von Kurven definieren. Wenn Sie die Kurve durch bestimmte Punkte zwingen, passiert dasselbe. Bei Splines ist der Effekt auf eine seltsame Wölbung oder ein unnatürlich aussehendes Kurvensegment beschränkt; Regressionsgleichungen höherer Ordnung können einem wilden Pfad folgen.

So stellen Splines die Kurve Ihrer Daten dar:

Wenn Sie dies mit den Regressionskurven höherer Ordnung vergleichen, reagieren die Splines stärker auf lokale Variationen.

Ich habe diese Analyse mit LibreOffice Calc durchgeführt, das über ein Analyse-Add-In verfügt, das Splines enthält. Wie Sie sehen, werden damit auch für Splines die interpolierten Ergebnisse erzeugt, nach denen Sie suchen. Ich habe keinen direkten Zugriff auf das Analyse-Toolkit von Excel und weiß daher nicht, ob Excel Splines enthält. Falls nicht, läuft LO Calc unter Windows und ist kostenlos.

Endeffekt

Dies umfasst die Ansätze, die Sie zum Interpolieren der Zwischenwerte verwenden können. Es kann sein, dass unterschiedliche Ansätze bei unterschiedlichen Daten besser funktionieren. Oder Ihre Anforderungen können ungefähr, schnell und einfach sein. Entscheiden Sie, welche Art von Interpolation Sie benötigen. Wenn Sie weitere Einzelheiten dazu benötigen, wie dies erreicht werden kann, können wir die Mechanik in einer anderen Antwort behandeln.

Answer 2

Wenn ich mir Ihre Kommentare und Korrekturen zu der Frage durchlese, stelle ich fest, dass Sie einige Dinge tun möchten, die in meiner vorherigen Antwort nicht wirklich behandelt werden. Diese Antwort befasst sich mit diesen Punkten und ich habe eine Schritt-für-Schritt-Anleitung beigefügt, wie Sie den gesamten Interpolationsprozess durchführen können.

Ungenaue Daten

Sie beschreiben den Prozess, der die Daten generiert hat, als das Aufzeichnen von Messwerten in einem Zeitintervall, und die Zahlen sind gerundete Zeiten. Die Gleichung ist nur so gut wie die Daten. In Ihrer tatsächlichen Analyse sollten Sie die genauesten verfügbaren Zahlen verwenden (vielleicht haben Sie Ihr Beispiel einfach gehalten, indem Sie gerundete Zeiten angegeben haben).

Die von Ihnen angezeigten Daten entsprechen jedoch nicht genau der Art von Kurve, die Sie normalerweise bei einem physikalischen Prozess sehen. Theoretische Kurven sind im Allgemeinen glatt, wenn nur eine treibende Variable und kein Rauschen vorhanden ist. Wenn Sie sehr präzise Geräte verwenden, um sowohl eine Messung in einem voreingestellten Intervall auszulösen als auch eine genaue Messung bereitzustellen, können Sie die Ergebnisse als präzise akzeptieren. Wenn Sie die Messung jedoch manuell zeitlich festlegen und manuell durchführen, Xkönnen die Werte zu ungenauen Zeitpunkten vorliegen, selbst wenn die Messungen selbst genau sind. Wenn Sie einzelne XWerte ein wenig in die eine oder andere Richtung verschieben, entstehen die kleinen Unregelmäßigkeiten, die Sie in der Kurve Ihrer Daten sehen (es sei denn, das Beispiel besteht nur aus Zahlen, die Sie zu Beispielzwecken erfunden haben).

Wenn dies der Fall ist, könnten Sie von der Verwendung einer Regression zur Schätzung der besten Anpassung profitieren.

Y als X verwenden

In Ihrem Problem möchten Sie Werte für definieren Y(in diesem Beispiel ganzzahlige Werte von 1 bis 37) und die zugehörigen X-Werte ermitteln. Das war in Ihrem Y=2^XProblem einfach genug, da diese einfache Gleichung leicht in umgekehrt werden kann X=log(Y)/log(2)und Sie jeden gewünschten Wert direkt berechnen können. Wenn die Gleichung nicht einfach ist, gibt es oft keine praktische Möglichkeit, sie umzukehren. Der „missbrauchte“ Regressionsansatz in meiner vorherigen Antwort liefert Ihnen eine Gleichung höherer Ordnung, ist aber „eindirektional“ und oft nicht praktikabel, um die umgekehrte Gleichung zu lösen.

Der einfachste Ansatz besteht darin, es einfach umzukehren Xund Yvon vorne anzufangen. Dadurch erhalten Sie eine Gleichung, die Sie mit den von Ihnen eingeführten ganzzahligen Werten verwenden können (die Analyse liefert Ihnen die Koeffizienten der Gleichung, wie in der vorherigen Antwort beschrieben).

Es schadet nie, zu testen, ob eine einfache Kurve funktioniert. Hier sind die umgekehrten Daten, und Sie können sehen, dass es keine brauchbare Anpassung gibt:

Versuchen Sie also eine polynomische Anpassung. Dies ist jedoch ein Fall wie in der vorherigen Antwort beschrieben. Die Werte von 1 bis 8 passen gut, aber 9 verursacht Verdauungsstörungen. Ein Polynom 3. Ordnung gibt Ihnen einen Schub:

Es wird zunehmend „interessanter“, je höher die Ordnung der Gleichung ist. Bei der 7. Ordnung erhalten Sie Folgendes:

Sie verläuft fast genau durch jeden Punkt, aber die Kurve zwischen 8 und 9 ist nicht sinnvoll. Eine Lösung wäre, zwischen 8 und 9 mit linearer Interpolation vorlieb zu nehmen. In diesem Fall könnten Sie jedoch bessere Werte erhalten, indem Sie Splines für das obere Ende einbauen. Die Spline-Option sorgt für eine gut aussehende Anpassung und eine Kurve, die zwischen 8 und 9 mehr Sinn ergibt:

Leider sind die Spline-Gleichungen etwas kompliziert und die Gleichungen werden nicht bereitgestellt. Sie können jedoch die lineare Interpolation auf die von der Analyse bereitgestellten Zwischenwerte anwenden, wodurch Sie sehr nahe an Zahlen gelangen sollten, die zu einer vernünftigen Kurve passen.

Extrapolation vs. Interpolation

In diesem Beispiel Yist Ihr erster Wert 2,9. Sie möchten Werte für 1und erzeugen 2, die außerhalb des Datenbereichs liegen. Dies erfordert Extrapolation statt Interpolation, was eine ganz andere Anforderung darstellt.

• Wenn die Gleichung bekannt ist, wie in Ihrem Y=2^XBeispiel, können Sie jeden gewünschten Wert berechnen.

• Wenn bekannt ist, dass der Prozess, der die Daten generiert, einer einfachen Kurve folgt, und Sie von der Anpassung überzeugt sind, können Sie Werte außerhalb des Datenbereichs projizieren und sogar ein aussagekräftiges Konfidenzintervall für den Bereich erhalten, in dem die Werte tatsächlich liegen könnten (basierend auf der Größe der Abweichung zwischen den Daten und der Kurve innerhalb des Datenbereichs).

• Wenn Sie eine Gleichung höherer Ordnung zwangsweise an die Daten anpassen, sind Projektionen außerhalb des Datenbereichs normalerweise bedeutungslos.

• Bei der Verwendung von Splines besteht keine Grundlage für eine Projektion außerhalb des Datenbereichs.

Welche Projektionen Sie auch immer außerhalb des Bereichs Ihrer Daten machen, sie sind nur so gut wie die Gleichung, die Sie verwenden. Wenn Sie keine exakte Gleichung verwenden, werden die Daten umso ungenauer, je weiter Sie von ihnen abweichen.

Wenn Sie sich die Log-Kurve im ersten Diagramm ansehen, können Sie erkennen, dass sie einen ganz anderen Wert prognostiziert als erwartet.

Bei den Polynomgleichungen ist der Null-Potenzkoeffizient eine Konstante, und das ist der Wert, der bei einem XWert von erzeugt würde 0. Das ist also eine einfache Möglichkeit, um zu sehen, wohin die Kurve in dieser Richtung verlaufen würde.

Beachten Sie, dass die Punkte 1 bis 8 bei der 4. oder 5. Ordnung ziemlich genau sind. Sobald Sie jedoch diesen Bereich verlassen, können sich die Gleichungen sehr unterschiedlich verhalten.

Extrapolation unter Verwendung begrenzter Daten

Eine Möglichkeit, die Situation zu verbessern, besteht darin, nur die Punkte an diesem Ende anzupassen und so viele nachfolgende Punkte einzuschließen, wie der Form der Kurve an diesem Ende folgen. Punkt 9 ist offensichtlich nicht geeignet. Es gibt mehrere Beugungen in der Kurve davor, eine davon um Punkt 5 oder 6, sodass Punkte darüber einer anderen Kurve folgen. Wenn Sie nur die Punkte 1 bis 5 verwenden, kommen Sie mit einem Polynom 3. Ordnung einer perfekten Anpassung nahe. Diese Gleichung würde einen Nullpunkt von 0,12095 projizieren (vergleichen Sie mit der obigen Tabelle) und für einen XWert von 1, 0.3493.

Was passiert, wenn Sie einfach eine gerade Linie an die ersten fünf Punkte anpassen:

Das projiziert einen Nullpunkt von -0,5138 und für einen Xvon 1, -0.0071.

Dieser Bereich möglicher Ergebnisse zeigt den Grad der Unsicherheit außerhalb des Bereichs Ihrer Daten an. Es gibt keine richtige Antwort. Und dies war am „gut erzogenen“ Ende Ihrer Kurve. Der YWert für einen Xvon 9ist 36.7. Sie möchten bis 37 gehen. Die Splines deuten darauf hin, dass die Kurve bei asymptotisch ist 9. Das Projizieren einer geraden Linie in die Rohdaten würde einen Wert etwas über ergeben 9(dasselbe mit einem Polynom 4. Ordnung). Ein Polynom 3. Ordnung deutet auf einen Wert unter hin 9(ebenso wie Polynome 5. und 6. Ordnung). Ein Polynom 7. Ordnung deutet auf einen Wert deutlich über hin 9. Alles außerhalb des Datenbereichs ist also eine Vermutung oder alles, was Sie wollen.

Alles zusammenfügen

Sehen wir uns also Schritt für Schritt an, wie die tatsächliche Lösung aussehen würde. Wir gehen davon aus, dass Sie bereits versucht haben, eine exakte Gleichung zu finden und gängige Kurven mithilfe einer Trendlinie getestet haben. Der nächste Schritt wäre, eine Regression auszuprobieren, da Sie dadurch die Formel für die Kurve erhalten und Ihre ganzzahligen Werte einsetzen können.

Ich habe keinen direkten Zugriff auf Excel 2013 oder das Analysis Toolkit. Ich verwende LibreOffice Calc, um dies zu veranschaulichen. Es ist nicht identisch, aber es ist nah genug dran, dass Sie es in Excel nachvollziehen können sollten. In LO Calc ist dies eigentlich eine kostenlose Erweiterung, die geladen werden muss. Ich verwendeCorelPolyGUI, die heruntergeladen werden kannHier. Soweit ich mich erinnere, waren im Analysis Toolkit keine Splines enthalten. Wenn das immer noch der Fall ist und Sie dies in Excel tun möchten, bin ich aufdieses kostenlose Add-In(was ich nicht getestet habe). Eine Alternative wäre die Verwendung von LO Calc, das unter Windows läuft und kostenlos ist.

Hier habe ich die X- und Y-Werte (umgekehrt) in die Spalten A und B eingegeben und den Analysedialog geöffnet. Durch Markieren der X-Werte und Klicken auf die Schaltfläche X werden die Datenbereiche geladen. Ich habe „Polynom“ ausgewählt.

0Auf der nächsten Registerkarte gebe ich an, dass ich zwei Grade verwenden möchte 7(ein Polynom 7. Ordnung mit allen Ordnungen).

Um die Ausgabe anzugeben, wähle ich C1 aus und klicke auf „Spalten“. Daraufhin werden die für die Ausgabe benötigten Spalten registriert. Ich wähle aus, dass die Originaldaten und die berechneten Ergebnisse ausgegeben werden sollen, und ich habe ausgewählt, dass zwischen jedem Originaldatenpunkt drei Zwischenpunkte eingefügt werden sollen. Und ich sage, dass ich eine Grafik der Ergebnisse in einem neuen Diagramm haben möchte. Dann gehe ich zum Berechnungsmenü und klicke auf „Berechnen“.

Und da ist es. Wenn Sie sich die berechneten Werte ansehen, bemerken Sie möglicherweise ein Problem. Es wird im nächsten Schritt deutlich.

1Hier habe ich die Werte bis hinzugefügt 37. An dieser Stelle wollen wir uns nur mit der Interpolation befassen, daher habe ich eine Formel hinzugefügt, um nur die Werte 3bis zu berechnen 36. Die Formel erweitert lediglich die in den Ergebnissen aufgeführten Koeffizienten (die a(n)-Werte). Die Formel in I2 lautet:

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

Dies ist einfach jeder Koeffizient multipliziert mit der zugehörigen Potenz des X-Werts. Ziehen Sie dies nach unten und Sie erhalten Ihre Ergebnisse. Naja, nicht ganz; Sie müssen es sich ansehen, um zu sehen, ob es den Plausibilitätstest besteht. Wir wussten, dass es zwischen 8und ein Problem gab, aber das stellt sich als die Hälfte der gewünschten Werte heraus. Wir könnten die Werte von bis 9verwenden , aber es macht keinen Sinn, so viele Werte aus einer anderen Methode zu kombinieren. Verwenden wir also einfach Splines für das Ganze.320

Öffnen Sie den Analysedialog erneut und ändern Sie auf der Registerkarte „Eingabe“ (hier nicht gezeigt) die Methode in „Splines“. Geben Sie ihm einen neuen Ausgabebereich und weisen Sie ihn an, zu berechnen. Das ist alles, was nötig ist.

Wir haben neue Ergebnisse, mit denen wir arbeiten können. Durch die Aufteilung des Datenbereichs in so viele Segmente bleibt jedes Segment kurz, sodass die lineare Interpolation ziemlich gut sein sollte (viel besser, als sie auf die Originaldaten anzuwenden).

Beim Prozess der Kurvenanpassung oder -interpolation werden Datenpunkte erstellt. Dabei stützen Sie sich auf Ihr eigenes Urteilsvermögen darüber, wie die Kurve aussehen „sollte“ (oder nicht aussehen sollte) (bei der Regression wird davon ausgegangen, dass sogar die Originaldaten ungenau sind).

Eine Plausibilitätsprüfung dieser Daten zeigt, dass selbst Splines eine Verbindungskurve mit einer Wölbung erzeugen; ein Wert geht leicht über 9, was wahrscheinlich eher ein Artefakt als ein Spiegelbild des von Ihnen gemessenen Prozesses ist. In diesem Fall 9ist eine asymptotische Kurve bei wahrscheinlicher, daher habe ich dem höchsten Punkt willkürlich einen Wert zugewiesen, der 9um Haaresbreite niedriger ist als der, den ich geschätzt hätte. Dabei gehe ich nicht davon aus, dass mein Wert präzise ist, sondern nur, dass er eine Verbesserung darstellt. Für diese Illustration habe ich eine neue Spalte mit den zu verwendenden Werten erstellt.

Ich habe eine Spalte mit Ihren Zahlen 1bis hinzugefügt 37. Aus der vorherigen Diskussion wissen wir nicht, dass wir eine verlässliche Grundlage für die Prognose von Werten für 1und haben 2, daher habe ich sie leer gelassen. Für 37habe ich die asymptotische Annahme getroffen und daraus gemacht 9. Die Werte für 3bis 36werden durch lineare Interpolation ermittelt (und es ist eine Formel, die Sie an andere Daten anpassen können). Die Formel in Q3 lautet:

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

Die TREND-Funktion interpoliert nur, wenn der Bereich zwei Punkte beträgt. Die Syntax lautet:

TREND(Y_range, X_range, X_value)  

Die OFFSET-Funktion wird für jeden Bereich verwendet. In jedem Fall wird die MATCH-Funktion verwendet, um die erste Zeile des Bereichs zu finden, die den Zielwert enthält. Die -1Werte sind , weil es sich hier um Offsets und nicht um Positionen handelt; eine Übereinstimmung in der ersten Zeile ist ein Offset von 0von der Referenzzeile. Und beachten Sie, dass die YSpalte 2in diesem Fall um versetzt ist, weil ich eine zusätzliche Spalte hinzugefügt habe, um einen Wert manuell anzupassen. Die OFFSET-Parameter wählen die Spalte mit den Y- oder X-Werten aus und wählen eine Bereichshöhe von 2, wodurch Sie die Werte unter und über dem Ziel erhalten.

Das Ergebnis:

Der Analyseassistent übernimmt die Schwerstarbeit, und unabhängig davon, ob Sie eine polynomische Regression oder Splines verwenden, ist nur eine Formel erforderlich, um das Ergebnis zu generieren.