Question Comment interpoler des valeurs intermédiaires pour des données arbitraires dans Excel 2013


J'ai des tables de données comme cet exemple, neuf entrées en A1: B9 dans ce cas:

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

Ce qui précède représente neuf mesures d'une variable physique croissante non linéaire en B, la tension par exemple, et A représente exactement chacune des neuf minutes rondes pendant lesquelles la mesure a été effectuée.

Je veux créer une deuxième table, colonnes E et F, avec une quantité de lignes correspondant au "nombre entier suivant" pour la valeur la plus élevée dans la colonne B. Dans ce cas, B9 = 36,7, donc il aura 37 lignes. La colonne F1: F37 contient les entiers 1 à 37, la colonne E doit avoir des valeurs numériques correspondant à F, dans la même relation entre les colonnes A à B. En d'autres termes, interpoler les valeurs de la colonne E correspondant aux valeurs de la colonne F.

Par exemple, A3 = 3 et B3 = 7. Dans ce cas, F7 = 7 et E7 = 3 car B comprenait déjà le nombre entier 7 et a une valeur correspondante dans la colonne A. Cependant, F8 = 8, qui est une valeur intermédiaire non contenue dans la colonne B. E8 se situera donc entre 3 et 4, sur la base des données d'origine, et doivent être interpolées.

L'idée est que lorsque vous tracez un graphique, A1: B9 aura la même forme que E1: F37. Dans cet exemple, je vais étendre le tableau de données à 37 résultats entiers qui se seraient produits au cours des mesures initiales, et je verrais à quelle heure (dans la colonne E, avec les décimales), ces valeurs se seraient produites.

Ce que j'ai essayé

En essayant de résoudre ce problème moi-même, j'ai pu trouver une formule qui prend du temps (notez que dans ma tentative, mes colonnes E et F sont inversées par rapport à ce que j'ai décrit ci-dessus).

  1. J'ai créé une colonne (K) contenant la différence entre les éléments de la colonne B. K5 = B5-B4. C'est le déplacement Y pour chaque incrément X.
  2. La colonne E contiendra autant de nombres entiers séquentiels (37), commençant à 1, que la valeur entière suivante du plus grand élément de B. Dans ce cas, B9 contient 36,7, donc 37.
  3. On F1: F37 Je saisis la formule suivante.

La cellule F1 contient:

=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)))))))))  

Ça marche plutôt bien. Mais ce n'est pas une formule automatisée; il faut entrer autant de "IF" que d’éléments dans les colonnes A + B (X + Y). J'ai testé des diagrammes de dispersion avec des lignes de A1: B9 et E1: F37 (inversées pour une séquence X / Y correcte) et elles ont généré exactement la même forme de courbe, donc cela fonctionne.

Mais ce n’est pas une solution efficace car elle nécessite un processus manuel fastidieux et personnalisé pour chaque ensemble de données. Je cherche un moyen de le faire de manière plus automatisée avec des fonctionnalités intégrées à Excel, ou du moins une approche plus générique utilisant des formules.


4
2017-10-11 00:32


origine


Ceci est un duplicata de votre question ultérieure. Pour interpoler, vous devez décider de la relation entre les valeurs. Dans votre question, un polynôme de quatrième ordre (ou sur-ajustement) des données; dans votre prochaine question, il existe une relation exponentielle entre les valeurs. Une fois que vous décidez de la relation, il s'agit d'appliquer la formule appropriée. Pour cet ensemble de données, vous devez insérer une colonne de valeurs équidistantes dans les lignes 1..36 allant de 1..9, puis utiliser une variante de la TREND fonction pour calculer les valeurs Y - Ron Rosenfeld
Pour vos deux questions, l'activité souhaitée est la REGRESSION, veuillez modifier la formulation de vos questions. Il n'y a pas de solution intégrée universelle pour cela, mais vous pouvez trouver des éléments utiles sur le Web en utilisant les bons mots clés. - Máté Juhász
@ MátéJuhász: Sans valeurs X, ce n'est pas non plus la régression. :-) Mais la régression est en fait un peu différente des exigences ici. La régression est une approximation basée sur toutes les données, qui peuvent ne pas passer par un point de données. Ce que l'OP veut, c'est l'interpolation entre les points réels. Il ne spécifie pas la nature de celle-ci, donc l'interpolation linéaire répondrait à l'exigence, mais la réponse de Gary's Student sur l'autre question utilise la courbe exponentielle réelle. - fixer1234
Monsieur, j'ai eu le malheur de produire la deuxième question, en choisissant une donnée exponentielle de 2 n. La plupart des réponses se situaient au-dessus du foyer 2 ^ n. Ça ne devrait pas. C'est pourquoi je reviens ici, avec les données d'origine: Neuf éléments, X = 1 à 9, Y = 2.9, 5.06, 7, 8.84, 10.87, 13.24, 16.22, 20.25, 36. Comme l'élément Y le plus élevé est 36, Je voudrais étendre X à 36 éléments et évidemment X1 = 1, X12 = 7, X36 = 36, mais je dois remplir les blancs de Y par rapport à X. - Wagner Lip
J'ai d'abord trois problèmes. ma langue maternelle n'est pas l'anglais, deuxièmement; Je n'ai pas de solides connaissances en mathématiques et troisième; il semble qu'il n'y ait pas de solution pour mon exercice de tableau Excel. Je n'abandonne pas les questions, je suis juste en train de suivre la recommandation pour éditer le premier message. De plus, il est possible que j'échoue complètement à utiliser ce forum. Merci. - Wagner Lip


Réponses:


Réponse courte

L'interpolation est basée sur une équation qui relie les valeurs X et Y. Si vous connaissez l’équation réelle, vous pouvez directement calculer les valeurs intermédiaires souhaitées. Si vous ne le faites pas, vous interpolez en utilisant une approximation. La qualité de l'approximation détermine la précision de vos valeurs intermédiaires. L'interpolation linéaire sera grossière si vous approchez d'une courbe avec un nombre limité de points. Il existe plusieurs autres approches qui vous donneront de meilleurs résultats et des outils d'analyse intégrés qui feront la majeure partie du travail.

Longue réponse

Vous recherchez une "formule générale" ou une solution qui automatise l'interpolation des valeurs intermédiaires. Vous pouvez utiliser une interpolation linéaire pour pratiquement toutes les données, mais les résultats seront grossiers s'il y a un nombre limité de points de données et une courbure significative dans la forme des données. Il n'y a pas de solution "taille unique" si vous voulez de la précision. La meilleure solution pour un jeu de données donné dépendra des caractéristiques des données.

L'équation

Peu importe comment vous le faites, l’interpolation est réalisée à l’aide d’une équation qui définit la relation entre X et Y. L’équation sera soit la relation réelle, soit une estimation. S'il s'agit d'une estimation, il existe différentes approches fondées sur la nature des données et sur ce que vous devez accomplir.

Dans votre autre question, vous avez utilisé des données basées sur l'équation Y=2^X. Lorsque vous avez l'équation réelle, vous pouvez interpoler exactement. Choisissez une nouvelle valeur pour l'un ou l'autre X ou Y et l'équation vous donne l'autre valeur. Si vous ne connaissez pas l’équation réelle, vous devez en trouver une qui l’estime. J'utiliserai cette réponse pour me concentrer sur les approches d'interpolation. Celles-ci utilisent généralement des outils d'analyse intégrés qui effectuent la majeure partie du travail. Si vous avez besoin de plus de détails sur la mécanique de l’utilisation d’un outil spécifique ou d’une approche plus automatisée, nous pourrons approfondir cette question dans une autre réponse.

Essayez de trouver l'équation réelle

La meilleure solution est de voir si vous pouvez déterminer l’équation réelle. Si vous connaissez le processus qui a généré les données, cela peut vous indiquer la nature de l’équation. De nombreux processus, dans des conditions contrôlées, avec une seule variable de conduite et aucun bruit aléatoire, suivent une courbe simple pour laquelle le type d'équation est connu. Donc, une première étape consiste à examiner la forme des données et à voir si celles-ci sont similaires.

Un moyen simple consiste à représenter graphiquement les données et à ajouter une courbe de tendance. Excel a un certain nombre de courbes communes disponibles pour essayer de s'adapter.

trend menu

Essayons ceci avec le 2^N données de votre autre question. Si vous n'avez pas reconnu le modèle de numéro et que vous avez essayé l'approche de la ligne de tendance, vous verriez les icônes des différentes courbes. La courbe exponentielle a la même forme générale et cela vous donnerait ceci:

2^N 

Excel utilise e plutôt que 2 comme base, qui n'est qu'une traduction (e0.693 est 2). Visuellement, vous pouvez voir que la ligne de tendance suit exactement les données. Le r2 vous dit aussi cela. R2 est une mesure statistique de la variation des données que vous comptabilisez dans votre équation. La valeur 1 signifie que l'équation représente 100% de la variation, ou un ajustement parfait.

L'exemple de cette question a également une forme exponentielle. Si vous essayez la même approche, vous obtenez ce résultat:

exp fit

Donc, ces données ne sont pas exponentielles. Nous pouvons essayer un polynôme, qui décrit certains processus naturels et peut imiter une variété de courbes (j'en parlerai plus tard):

poly 3

Comme une approximation du processus derrière les données, ce n'est pas un bon ajustement. Au troisième ordre (une équation contenant des puissances de X jusqu'à X ^ 3), il a plus de points d'inflexion majeurs que les données et ne correspond toujours pas. Donc, l'équation sous-jacente ne ressemble pas à une courbe simple et commune, ce qui signifie que l'équation devra être approximée.

Interpolation linéaire

C'est l'approche que vous décrivez dans vos commentaires. C'est simple, en utilisant une formule simple et assez facile à automatiser. Cela peut être suffisant si vous avez beaucoup de points et que les lignes droites entre eux sont assez proches. Sur de nombreuses courbes, les segments courts de certaines zones seront proches des lignes droites. Cependant, il s'agit d'une mauvaise approximation pour une ligne courbe et vos résultats seront inexacts dans les zones présentant une courbure significative. Dans votre exemple, la zone entre les valeurs X de 7 et 8 aurait beaucoup de courbure. Dans cette zone, une ligne droite par rapport à la courbe réelle ressemblerait à ceci:

straight vs curve

Vous recherchez une solution générale applicable à toutes les données. Vous pouvez constater que l'interpolation linéaire est trop brute pour certaines données.

Régression

Les gens ont suggéré la régression comme une approche, ici et dans d'autres postes. Cela peut être fait en utilisant les lignes de tendance ou leurs fonctions de feuille de calcul sous-jacentes, ou les outils d'analyse (je pense que cela pourrait être dans Analysis Toolkit, qui pourrait nécessiter le chargement de cette option dans Excel).

La régression tente d'ajuster une courbe à vos données dans le but de minimiser l'erreur totale entre les données et la courbe. Dans son utilisation normale, ce n'est pas le bon outil pour cette tâche (c'est la méthode utilisée pour ajuster les lignes de tendance, et vous avez vu comment cela se comparait à ce dont vous avez besoin).

  • Il est destiné aux situations où votre objectif est de modéliser le processus derrière les données. Les données sont supposées inexactes et la régression suggère ce qu'elle est réellement supposée être. La courbe trouvée par la régression peut ne traverser aucun des points de données réels. Dans votre cas, les données sont données et supposées exactes. La courbe doit traverser chaque point.

  • La régression tente d'ajuster une seule équation à toutes les données. Cela ne sera pas efficace si le processus qui a créé les données n’est pas décrit par les types d’équations disponibles. Avec beaucoup de points de données, l'interpolation linéaire de chaque segment peut être une meilleure approximation qu'une courbe de régression pour toutes les données.

Cependant, plutôt que de l’utiliser de la manière habituelle, la régression peut être «abusée» en tant que solution de contournement pour ce que vous voulez, et cela fonctionnera généralement. Lorsque vous essayez de modéliser un processus, la formule la plus simple est généralement évaluée (le rasoir d'Occam). Par contre, avec une équation suffisamment complexe, vous pouvez adapter n'importe quoi. Vous pouvez toujours dessiner un gribouillage qui passera par chaque point. Avec N points, vous pouvez trouver un N-1 équation polynomiale d'ordre qui passera par tous les points (scénario le plus défavorable).

Je dis "habituellement" parce que dans certains cas, c'est une ligne assez torturée qui ne servirait à rien. Et notez que cette approche ne «modélise» pas vraiment quelque chose dans le sens où l’équation résultante prédirait un comportement en dehors de la plage des données.

Voici une analyse de vos données en utilisant la régression polynomiale avec des équations successives (la première capture d'écran inclut les ordres 3 à 5):

poly 3-5

(Cliquez sur l'image pour obtenir une taille lisible.) Notez que l'outil d'analyse inclut le type d'interpolation que vous souhaitez effectuer. il a généré les valeurs intermédiaires. Pour chaque analyse, le a(n) les valeurs sont les coefficients de l'équation trouvée. a(0) est une constante, a(1) est le coeficient pour le terme X ^ 1, etc. Il montre le R2 valeur de l'ajustement. Il doit être virtuellement 1 être assez proche de votre objectif.

J'ai mis en évidence les valeurs de données originales avec les plus grandes différences. Dans cette gamme d'ordres, l'ajustement devient un peu meilleur à chaque commande successive, mais les points spécifiques décrits plus précisément peuvent changer. Voici un tableau de ces trois:

poly 3-5 chart

Quand on arrive au polynôme du 6ème et 7ème ordre, ça ressemble à ceci:

poly 6-7

poly 6-7 chart

Si nous passions à un polynôme d'ordre 8 pour vos 9 valeurs, ce serait parfait, mais le 7ème ordre est probablement assez proche. Pour perspective, notez que l'équation du 7ème ordre a un R2 de 99999 et n'est toujours pas parfait.

L'utilisation de l'outil d'analyse de régression pour trouver un ajustement adéquat (dans ce cas, l'équation du 7ème ou du 8ème ordre) produirait les valeurs intermédiaires souhaitées. Mais c'est une bonne idée de tracer le résultat et de regarder la courbe pour s'assurer que ce n'est pas un gribouillage.

Splines

Si vous tracez vos données et sélectionnez l'option pour les lignes lisses, ce qu'Excel utilise pour produire des splines. En fait, presque toutes les applications de l'infographie (y compris les définitions de polices) sont basées sur des splines pour des courbes et des transitions de courbes fluides. Il porte le nom de la règle flexible utilisée par les dessinateurs pour connecter des points arbitraires à une courbe.

Les splines créent la courbe pour chaque section, une section à la fois, en tenant compte des points adjacents. La courbe passe à travers chaque point et il n'y a pas de changements brusques de chaque côté du point, tels que vous obtenez lorsque vous connectez les points avec des lignes droites.

Les équations utilisées pour les splines ne tentent pas de modéliser le processus qui a produit les données. c'est strictement beau. Cependant, la plupart des processus suivent une sorte de courbe continue et lisse. Lorsque vous traitez avec un seul segment de courbe, de nombreuses équations différentes qui produisent des courbes de forme générale similaire produiront des valeurs très similaires dans le segment. Donc, dans la plupart des cas, les splines produiront une bonne approximation de ce que vous voulez (et elles passent naturellement par chaque point, contrairement à la régression, qui doit être forcée à travers chaque point).

Encore une fois, je dis "la plupart des cas". Les splines fonctionnent parfaitement pour les données qui sont assez uniformes et régulières et suivent les "règles" pour une courbe. Il peut faire des choses inattendues avec des données inhabituelles. Par exemple, un question précédente était sur cet étrange "dip" négatif dans le graphique Excel produit des données:

dip

Les splines sont un peu comme Jello. Imaginez un gros morceau de Jello, et vous contraignez des endroits spécifiques où vous le souhaitez. Le reste du Jello se gonflera par endroits. Une équation peut définir certains types de courbes. Si vous forcez la courbe à travers des points spécifiques, la même chose se produit. Avec les splines, l'effet est limité à un bombement impair ou à un segment de courbe non naturel; les équations de régression d'ordre élevé peuvent suivre un chemin sauvage.

Voici comment les splines représentent la courbe de vos données:

spline

spline chart

Si vous comparez cela avec les courbes de régression d'ordre élevé, les splines sont plus "sensibles" aux variations locales.

J'ai fait cette analyse en utilisant LibreOffice Calc, qui a un complément d'analyse qui inclut des splines. Comme vous pouvez le voir, cela produit également pour les splines, les résultats interpolés que vous recherchez. Je n'ai pas facilement accès à la boîte à outils d'analyse d'Excel, donc je ne sais pas si Excel inclut des splines. Sinon, LO Calc fonctionnera sous Windows et sera gratuit.

Bottom Line

Cela couvre les approches que vous pouvez utiliser pour interpoler les valeurs intermédiaires. Il se peut que différentes approches fonctionnent mieux avec des données différentes. Ou, vos besoins pourraient être tout à fait approximatifs, rapides et faciles. Décidez du type d'interpolation dont vous avez besoin. Si vous avez besoin de plus de détails sur la façon de le faire, nous pouvons aborder la mécanique dans une autre réponse.


7
2017-10-17 18:06



Cher Fixer1234, je ne peux même pas commencer à vous remercier. J'ai beaucoup plus que ce à quoi je m'attendais. Maintenant, j'ai du matériel à traiter et à jouer pendant longtemps, à apprendre et à améliorer. Vous êtes un enseignant parfait à bien des égards. Nous avons besoin de plus de gens comme vous dans le monde. Merci pour votre dévouement, votre temps et vos connaissances. Wagner. - Wagner Lip
@WagnerLip, vous êtes les bienvenus. Heureux de vous aider. À un moment donné, vous le ferez peut-être pour quelqu'un d'autre. :-) - fixer1234
Fixer1234, merci encore. Je participe activement à un grand forum électronique depuis 2002, aidant le plus possible. C'est très gratifiant d'aider les personnes à la recherche de connaissances. En fait, ce numéro d’Excel vise à développer un moyen d’aider les utilisateurs du forum à produire de meilleures échelles (angles et marques) de boutons dans les équipements électroniques. - Wagner Lip


En parcourant vos commentaires et vos révisions à la question, vous voudrez faire certaines choses qui ne sont pas vraiment couvertes dans ma réponse précédente. Cette réponse portera sur ces éléments, et j'ai inclus une présentation étape par étape de la façon dont vous accompliriez le processus d'interpolation.

Données inexactes

Vous décrivez le processus qui a généré les données en prenant des lectures à un intervalle de temps, et les nombres sont des temps arrondis. L'équation ne vaut que pour les données. Dans votre analyse actuelle, vous devez utiliser les chiffres les plus précis disponibles (peut-être que vous avez simplement simplifié votre exemple en affichant des heures arrondies).

Cependant, les données que vous affichez ne correspondent pas exactement au type de courbe que vous voyez généralement pour un processus physique. Les courbes théoriques sont généralement lisses lorsqu'il n'y a qu'une seule variable de conduite et aucun bruit. Si vous utilisez un équipement très précis pour déclencher une lecture à un intervalle prédéfini et pour fournir une mesure précise, vous pouvez accepter les résultats avec précision. Cependant, si vous chronométrez manuellement la lecture et prenez manuellement la lecture, le X les valeurs peuvent être à des moments imprécis même si les lectures elles-mêmes sont exactes. Individu changeant X Les valeurs un peu dans un sens ou dans l'autre introduiront le type de petites irrégularités que vous voyez dans la courbe de vos données (à moins que l'exemple ne soit que des chiffres que vous avez créés pour un exemple).

Si tel est le cas, vous pouvez tirer parti de la régression pour estimer le meilleur ajustement.

Utiliser Y comme X

Dans votre problème, vous souhaitez définir des valeurs pour Y (valeurs entières de 1 à 37 dans cet exemple) et recherchez les valeurs X associées. C'était assez facile à faire dans votre Y=2^X problème parce que cette équation simple peut facilement être inversée à X=log(Y)/log(2), et vous pouvez directement calculer toute valeur que vous voulez. Si l'équation n'est pas quelque chose de simple, il n'y a souvent pas de moyen pratique de l'inverser. L'approche de régression "abusée" dans ma réponse précédente vous donne une équation d'ordre élevé, mais elle est "à sens unique", souvent peu pratique à résoudre pour l'équation inverse.

L'approche la plus simple consiste simplement à inverser X et Y dès le début. Cela vous donne une équation que vous pouvez utiliser avec les valeurs entières que vous introduisez (l'analyse vous donne les coefficients de l'équation décrits dans la réponse précédente).

Il n'est jamais mauvais de voir si une courbe simple fonctionnera. Voici les données inversées, et vous pouvez voir qu’il n’ya pas de correspondance utile:

log

Alors, essayez un ajustement polynomial. Cependant, c'est un cas comme je l'ai décrit dans la réponse précédente. Les valeurs de 1 à 8 correspondent bien, mais 9 lui donne une indigestion. Un polynôme d'ordre 3 vous donne une bosse:

poly3

Il devient progressivement plus "intéressant" à mesure que l'ordre de l'équation augmente. Par le 7ème ordre, vous obtenez ceci:

poly7

Il passe presque exactement à chaque point, mais la courbe entre 8 et 9 n'est pas utile. Une solution serait de faire avec une interpolation linéaire entre 8 et 9. Dans ce cas, cependant, vous pourriez obtenir de meilleures valeurs en incorporant des splines pour la partie supérieure. L'option splines offre une bonne apparence et une courbe plus logique entre 8 et 9:

splines

Malheureusement, les équations splines sont un peu compliquées et les équations ne sont pas fournies. Cependant, vous pouvez effectuer une interpolation linéaire sur les valeurs intermédiaires fournies par l’analyse, ce qui devrait vous permettre de vous rapprocher des nombres correspondant à une courbe raisonnable.

Extrapolation ou Interpolation

Dans cet exemple, votre premier Y la valeur est 2.9. Vous voulez produire des valeurs pour 1 et 2, qui sont en dehors de la plage des données. Cela nécessite une extrapolation plutôt qu'une interpolation, ce qui est une exigence très différente.

  • Si l'équation est connue, comme votre Y=2^X Par exemple, vous pouvez calculer n'importe quelle valeur.

  • Si le processus générant les données est connu pour suivre une courbe simple et que vous êtes certain de l’ajustement, vous pouvez projeter des valeurs en dehors de la plage de données et même obtenir un intervalle de confiance significatif pour la plage combien il y a de variation entre les données et la courbe dans la plage de données).

  • Si vous ajustez une équation de haut niveau aux données, les projections en dehors de la plage de données n'ont généralement pas de sens.

  • Si vous utilisez des splines, il n'y a pas de base pour la projection en dehors de la plage de données.

Quelles que soient les projections que vous effectuez en dehors de la plage de vos données, elles ne valent que l'équation que vous utilisez, et si vous n'utilisez pas une équation exacte, plus vous obtenez de vos données, plus elles seront inexactes.

En regardant la courbe de log dans le premier graphique, vous pouvez voir que cela va projeter une valeur très différente de celle à laquelle vous vous attendriez.

Pour les équations polynomiales, le coefficient de puissance zéro est une constante, et c'est la valeur qui serait produite pour un X valeur de 0. Donc, c'est un moyen simple de voir où la courbe irait dans cette direction.

zero vals

Notez que par le 4ème ou le 5ème ordre, les points 1 à 8 sont assez précis. Mais une fois que vous sortez de la gamme, les équations peuvent se comporter très différemment.

Extrapolation en utilisant des données limitées

Une des façons d'améliorer les choses consiste à ne placer que les points à cette extrémité et à inclure autant de points successifs que la forme de la courbe à cette extrémité. Le point 9 est manifestement sorti. Il y a plusieurs inflexions dans la courbe avant celle-ci, une autour du point 5 ou 6, donc des points plus élevés que ceux qui suivent une courbe différente. En utilisant uniquement les points 1 à 5, vous vous approchez d'un ajustement parfait avec un polynôme du 3ème ordre. Cette équation projeterait un point zéro de 0.12095 (comparé au tableau ci-dessus), et pour un X valeur de 1, 0.3493.

Que se passe-t-il si vous ajustez simplement une ligne droite aux cinq premiers points:

straight

Cela projette un point zéro de -0,5138 et pour un X de 1, -0.0071.

Cette plage de résultats possibles indique le niveau d'incertitude en dehors de la plage de vos données. Il n'y a pas de bonne réponse. Et c'était à la fin "bien comporté" de votre courbe. le Y valeur pour un X de 9 est 36.7. Vous voulez aller à 37. Les splines suggèrent que la courbe est asymptotique à 9. La projection d'une ligne droite dans les données brutes produirait une valeur légèrement supérieure à 9 (même avec un polynôme du 4ème ordre). Un polynôme du 3ème ordre suggère une valeur inférieure à 9 (comme le font les 5ème et 6ème ordres). Un polynôme d'ordre 7 suggère une valeur sensiblement supérieure 9. Donc tout ce qui est en dehors de la plage de données est une supposition, ou tout ce que vous voulez qu'il soit.

Mettre tous ensemble

Voyons donc à quoi ressemblerait la solution actuelle. Nous supposerons que vous avez déjà essayé de trouver une équation exacte et testé des courbes courantes en utilisant une ligne de tendance. La prochaine étape consisterait à essayer la régression car cela vous donne la formule de la courbe et vous pouvez brancher vos valeurs entières.

Je n'ai pas facilement accès à Excel 2013 ou à la boîte à outils d'analyse. J'utiliserai LibreOffice Calc pour illustrer cela. Ce n'est pas identique, mais il est assez proche pour que vous puissiez le suivre dans Excel. Dans LO Calc, il s'agit en réalité d'une extension gratuite qui doit être chargée. j'utilise CorelPolyGUI, qui peut être téléchargé ici. Si je me souviens bien de l’Analyse, c’est qu’elle n’incluait pas les splines. Si c'est toujours le cas et que vous voulez le faire dans Excel, je suis tombé sur ce complément gratuit (que je n'ai pas testé). Une alternative serait d'utiliser LO Calc, qui fonctionnerait sous Windows et est gratuit.

step 1

Ici, j'ai saisi les valeurs X et Y (inversées) dans les colonnes A et B et ouvert la boîte de dialogue d'analyse. Surligner les valeurs X et cliquer sur le bouton X charge les plages de données et j'ai sélectionné un polynôme.

step 2

Dans l'onglet suivant, je spécifie que je veux utiliser 0 à 7 degrés (un polynôme du 7ème ordre avec tous les ordres).

step 3

Pour spécifier la sortie, je sélectionne C1 et cliquez sur Colonnes, et il enregistre les colonnes nécessaires à la sortie. Je choisis que je souhaite que les données originales, les résultats calculés soient sortis et que j'ai sélectionné trois points intermédiaires entre chaque point de données original. Et je le dis, je veux un graphique des résultats sur un nouveau graphique. Ensuite, allez dans le menu de calcul et cliquez sur calculer.

step 4

Et voilà. Si vous regardez les valeurs calculées, vous remarquerez peut-être un problème. Cela deviendra apparent à l'étape suivante.

step 5

Ici, j'ai ajouté le 1 par 37 valeurs. À ce stade, nous ne voulons traiter que de l'interpolation, j'ai donc ajouté une formule pour calculer uniquement les valeurs. 3 par 36. La formule ne fait que développer les coefficients indiqués dans les résultats (les valeurs a (n)). La formule dans I2 est la suivante:

=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

Ceci est juste chaque coefficient multiplié par la puissance associée de la valeur X. Faites glisser ceci vers le bas et vous avez vos résultats. Bien pas tout à fait; vous devez le regarder pour voir s'il réussit le test d'intégrité. Nous savions qu'il y avait un problème entre 8 et 9, mais cela s'avère être la moitié des valeurs que vous voulez. Nous pourrions utiliser les valeurs de 3 par 20, mais il n'y a aucun sens à combiner autant de valeurs d'une autre méthode. Donc, utilisons juste des splines pour le tout.

step 6

Ouvrez à nouveau la boîte de dialogue d'analyse et changez la méthode en "splines" sur l'onglet d'entrée (non illustré ici). Donnez-lui une nouvelle plage de sortie et dites-lui de calculer. C'est tout ce qu'il faut.

step 7

Nous avons de nouveaux résultats avec lesquels travailler. En divisant la plage de données en autant de segments, chaque segment reste court, de sorte que l'interpolation linéaire devrait être assez bonne (bien mieux que de l'utiliser sur les données d'origine).

step 8

Le processus d'ajustement de courbe ou d'interpolation implique la création de points de données. en utilisant votre propre jugement sur ce que la courbe "devrait" (ou ne devrait pas) ressembler (la régression suppose que même les données originales sont imprécises).

Donner à ces données une vérification de la santé mentale montre que même les splines produisent une courbe de connexion avec un renflement; une valeur est légèrement dépassée 9, qui est probablement un artefact plutôt que le reflet du processus que vous avez mesuré. Dans ce cas, une courbe asymptotique à 9 est plus probable, alors j'ai attribué arbitrairement le point haut une valeur qui est un cheveux moins que 9 en le regardant. L'hypothèse n'est pas que ma valeur est précise, mais seulement que c'est une amélioration. Pour cette illustration, j'ai créé une nouvelle colonne avec les valeurs qui seront utilisées.

J'ai ajouté une colonne avec vos numéros 1 par 37. De la discussion précédente, nous n'avons pas de base fiable pour projeter des valeurs pour 1 et 2, alors je les ai laissés vierges. Pour 37, Je suis allé avec l'hypothèse asymptotique et je l'ai fait 9. Les valeurs pour 3 par 36 se trouvent par interpolation linéaire (et c'est une formule que vous pouvez adapter à d'autres données). La formule de Q3 est la suivante:

=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)

La fonction TREND interpole simplement lorsque la plage est de deux points. La syntaxe est la suivante:

TREND(Y_range, X_range, X_value)  

La fonction OFFSET est utilisée pour chaque plage. Dans chaque cas, il utilise la fonction MATCH pour trouver la première ligne de la plage contenant la valeur cible. le -1 les valeurs sont parce que ce sont des décalages plutôt que des emplacements; une correspondance dans la première ligne est un décalage de 0 de la ligne de référence. Et notez que le Y la colonne est compensée par 2, dans ce cas, car j'ai ajouté une colonne supplémentaire pour ajuster manuellement une valeur. Les paramètres OFFSET sélectionnent la colonne contenant les valeurs Y ou X et sélectionnent une hauteur de plage de 2, qui vous donne les valeurs inférieures et supérieures à la cible.

Le résultat:

result

L'assistant d'analyse fait le gros du travail, et que vous utilisiez une régression polynomiale ou des splines, une seule formule suffit pour générer le résultat.


3
2017-10-18 21:42