Interpolation des points de données en Excel
je suis sûr que c'est le genre de problème que d'autres ont résolu plusieurs fois auparavant.
un groupe de personnes va faire des mesures (consommation D'énergie domestique pour être exact). Tous le feront à des moments et à des intervalles différents.
donc ce que je vais obtenir de chaque personne est un ensemble de {date, Valeur} paires où il y a des dates manquantes dans l'ensemble.
ce dont j'ai besoin est un ensemble complet de {date, Valeur} paires où pour chaque date avec la gamme une valeur est connu (mesuré ou calculé). J'attends qu'une simple interpolation linéaire suffirait pour ce projet.
Si je suppose que cela doit être fait dans Excel. Quelle est la meilleure façon d'interpoler dans un tel ensemble de données (donc j'ai une valeur pour chaque jour) ?
Merci.
NOTE: lorsque ces ensembles de données sont terminés, je vais déterminer la pente (c.-à-d. l'utilisation par jour) et à partir de cela, nous pouvons commencer à faire des comparaisons maison-à-Maison.
information supplémentaire après la première quelques suggestions: Je ne veux pas comprendre manuellement où sont les trous dans mon ensemble de mesure (trop d'ensembles de mesure incomplets!!). Je cherche quelque chose (existant) automatique pour le faire pour moi. Donc, si mon entrée est
{2009-06-01, 10}
{2009-06-03, 20}
{2009-06-06, 110}
Puis-je m'attendre à obtenir automatiquement
{2009-06-01, 10}
{2009-06-02, 15}
{2009-06-03, 20}
{2009-06-04, 50}
{2009-06-05, 80}
{2009-06-06, 110}
Oui, je peux écrire un logiciel qui fait cela. J'espère juste que quelqu'un a déjà un logiciel "prêt à exécuter" (Excel) pour ce problème (plutôt générique).
7 réponses
la façon la plus facile de le faire est probablement la suivante:
Télécharger Excel add-on ici: Xlxtrfun™ fonctions supplémentaires pour Microsoft Excel
utiliser la fonction intepolate (). = Interpolate($a$1:$A$3,$B$1:$B$3,D1,FALSE,FALSE)
les colonnes A et B devraient contenir vos entrées, et la colonne G devrait contenir toutes vos valeurs de date. La formule va dans la colonne E.
je suis tombé sur ce et a été réticents à utiliser un complément, car il est difficile de partager la feuille avec des gens qui n'ont pas l'add-in installé.
mon officemate a conçu une formule propre qui est relativement compacte (au prix de l'utilisation d'un peu de magie).
remarques:
La formule fonctionne:
MATCHfonction pour trouver la ligne dans leinputsgamme juste avant la valeur recherché (par exemple, 3 est la valeur juste avant 3.5)- en utilisant
OFFSETS pour sélectionner le carré de cette ligne et le suivant (en violet clair) - en utilisant
FORECASTpour construire une interpolation linéaire en utilisant seulement ces deux points, et obtenir le résultat
cette formule ne peut pas faire d'extrapolations; assurez-vous que votre valeur de recherche se situe entre les paramètres (je le fais dans l'exemple ci-dessous en ayant extrême valeur.)
Je ne suis pas sûr que ce soit trop compliqué pour les gens; mais il a eu l'avantage d'être très portable (et plus simple que beaucoup de solutions alternatives).
Si vous voulez copier-coller de la formule, c'est:
=FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1
(inputs avoir une plage nommée)
Il y a deux fonctions, la fonction DROITEREG et TREND, que vous pouvez essayer pour voir qui vous donne les meilleurs résultats. Ils prennent tous les deux des ensembles de XS et Ys connus avec une nouvelle valeur de X, et calculent une nouvelle valeur de Y. La différence est que LINEST fait une régression linéaire simple, tandis que TREND va d'abord essayer de trouver une courbe qui correspond à vos données avant de faire la régression.
une belle façon graphique de voir comment vos résultats interpolés s'adaptent:
prenez votre date, les paires de valeurs et les graphes en utilisant le graphique XY dans Excel (pas le graphique linéaire). Cliquez avec le bouton droit de la souris sur la ligne résultante du graphique et cliquez sur "Ajouter trendline". Il y a beaucoup d'options différentes pour choisir quel type de raccord courbe est utilisé. Ensuite, vous pouvez aller aux propriétés de la ligne de tendance nouvellement créée et d'afficher l'équation et la valeur R-au carré.
assurez-vous que lorsque vous formatez l'étiquette D'équation trendline, vous définissez le format numérique pour avoir un haut degré de précision, de sorte que tous les chiffres significatifs des constantes d'équation sont affichés.
la réponse ci-dessus de YGA ne gère pas les cas de fin de gamme où la valeur X désirée est la même que la valeur X de la gamme de référence. En utilisant L'exemple donné par YGA, la formule excel retournerait #DIV/0! erreur si une valeur interpolée à 9999 a été demandé. C'est évidemment en partie la raison pour laquelle YGA a ajouté les valeurs extrêmes de 9999 et -9999 à la plage de données d'entrée, et suppose ensuite que toutes les valeurs prévues se situent entre ces deux nombres. Si tel rembourrage est indésirable ou pas possible, une autre façon d'éviter un #DIV/0! l'erreur est de vérifier pour un exact de la valeur d'entrée de match à l'aide de la formule suivante:
=IF(ISNA(MATCH(F3,inputs,0)),FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1)),OFFSET(inputs,MATCH(F3,inputs)-1,1,1,1))
où F3 est la valeur interpolée résultats sont recherchés.
Note: j'aurais simplement ajouté ceci comme commentaire au post YGA original, mais je n'ai pas encore assez de points de réputation.
alternativement.
=INDEX(yVals,MATCH(J7,xVals,1))+(J7-MATCH(J7,xVals,1))*(INDEX(yVals,MATCH(J7,xVals,1)+1)-INDEX(yVals,MATCH(J7,xVals,1)))/(INDEX(xVals,MATCH(J7,xVals,1)+1)-MATCH(J7,xVals,1))
où j7 est la valeur de x.
xvals est une plage de valeurs de x
yvals est la gamme de valeurs de y
plus facile à mettre en code.
vous pouvez trouver quelle formule correspond le mieux à vos données, en utilisant la fonction "Ligne de tendance" D'Excel. En utilisant cette formule, vous pouvez calculer y pour tout x
- Créer un linéaire de nuages de points (XY) pour le (Insérer => Scatter);
- Créer Polynominal ou Moyenne mobile de la ligne de tendance, cochez la case "Afficher l'Équation sur tableau" (clic droit sur la série => Ajouter une Ligne de Tendance);
- Copier l'équation dans la cellule et remplacer
x'S avec votre x désiré valeur
sur la capture d'écran ci-dessous A12:A16 tient x's, B12:B16 holds y's, et C12 contient la formule qui calcule y pour tout x.
j'ai d'abord posté une réponse ici, mais plus tard trouvé cette question