Effectuer une analyse de régression dans Excel 2007
Craig Stinson a été un journaliste de l'industrie depuis 1981, servant de rédacteur du magazine PC et auteur de plusieurs éditions du best-seller exécutant Microsoft Windows®. En plus d'être co-auteur sur l'exécution de Microsoft Excel, il est le co-auteur de l'exécution de Microsoft Windows NT Workstation, version 4. Il a également écrit des critiques de musique pour ces publications bien connues comme Billboard, le Boston Globe, et le Christian Science Monitor .
Dans cet article
La compréhension de régression linéaire et exponentielle
Excel comprend plusieurs fonctions de réseau pour effectuer une régression linéaire (DROITEREG, TENDANCE, prévision, la pente et STEYX) et pour effectuer une régression exponentielle (LOGEST et croissance). Vous entrez dans ces fonctions formules de tableau, et ils produisent des résultats du tableau. Vous pouvez utiliser chacune de ces fonctions avec une ou plusieurs variables indépendantes. La liste suivante définit les différents types de régression:
régression exponentielle produit une courbe exponentielle qui correspond le mieux à un ensemble de données que vous soupçonnez ne change pas de façon linéaire avec le temps. Par exemple, une série de mesures de croissance de la population sera presque toujours mieux représentée par une courbe exponentielle que par une ligne.
La régression multiple est l'analyse de plus d'un ensemble de données, qui produit souvent une projection plus réaliste. Vous pouvez effectuer à la fois multiples analyses de régression linéaire et exponentielle. Par exemple, supposons que vous voulez projeter le prix approprié pour une maison dans votre région basée sur la superficie, le nombre de salles de bains, la taille du lot et de l'âge. En utilisant une formule de régression multiple, vous pouvez estimer un prix, basé sur une base de données des informations recueillies auprès des maisons existantes.
Régressant dans l'avenir?
Le concept de régression peut paraître étrange parce que le terme est généralement associé à un mouvement vers l'arrière, alors que dans le monde des statistiques, la régression est souvent utilisé pour prédire l'avenir. Autrement dit, la régression est une technique statistique qui trouve une expression mathématique qui décrit le mieux un ensemble de données.
Souvent, les entreprises tentent de prédire l'avenir en utilisant des projections et des ventes pour cent-de-vente basé sur l'histoire. Une technique simple pour cent-de-vente identifie les actifs et passifs qui varient ainsi que les ventes, détermine la proportion de chacun, et leur attribue des pourcentages. Bien que l'utilisation pour cent-de-vente des prévisions est souvent suffisante pour une croissance lente ou à court terme stable, la technique perd la précision que la croissance accélère.
L'analyse de régression utilise des équations plus sophistiquées pour analyser les grands ensembles de données et les traduit en coordonnées sur une ligne ou courbe. Dans le pas si lointain, l'analyse de régression n'a pas été largement utilisé en raison du grand volume de calculs impliqués. Les applications de tableur, comme Excel, a commencé à offrir fonctions intégrées de régression, l'utilisation de l'analyse de régression est devenue plus répandue.
Le calcul de régression linéaire
L'équation y = mx + b décrit algébriquement une droite, d'un ensemble de données avec une variable indépendante, où x est la variable indépendante, y est la variable dépendante, m représente la pente de la ligne, et b représente l'ordonnée à l'origine. Si une ligne représente un certain nombre de variables indépendantes dans une analyse de régression multiple à un résultat attendu, l'équation de la droite de régression prend la forme
dans laquelle y est la variable dépendante, x1 à x n sont n variables indépendantes, m 1 à m n sont les coefficients de chaque variable indépendante, et b est une constante.
La fonction DROITEREG
La fonction DROITEREG utilise cette équation plus générale pour renvoyer les valeurs de m 1 à m n et la valeur de b. étant donné un ensemble de valeurs connues pour y et un ensemble connu de valeurs pour chaque variable indépendante. Cette fonction prend la forme = LINEST (y_connus, x_connus, const, statistiques).
L'argument de l'y_connus est l'ensemble des valeurs y que vous connaissez déjà. Cet argument peut être une seule colonne, une rangée ou une plage rectangulaire de cellules. Si y_connus est une seule colonne, chaque colonne dans l'argument de la x_connus est considérée comme une variable indépendante. De même, si y_connus est une seule rangée, chaque rangée dans l'argument de la x_connus est considérée comme une variable indépendante. Si y_connus est une plage rectangulaire, vous pouvez utiliser une seule variable indépendante; x_connus dans ce cas devrait être une gamme de la même taille et la forme que y_connus. Si vous omettez Excel utilise la séquence 1, 2, 3, 4 l'argument du x_connus, et ainsi de suite.
Les arguments const et les statistiques sont en option. Si l'est inclus, il doit être une constante soit logique VRAI ou FAUX. (Vous pouvez remplacer 1 pour TRUE et 0 pour FAUX.) Les paramètres par défaut pour const et les statistiques sont VRAI et FAUX, respectivement. Si vous définissez const à FAUX, les forces Excel b (le dernier terme de l'équation linéaire) à 0. Si vous définissez les statistiques sur TRUE, le tableau retourné par LINEST comprend les statistiques de validation suivantes:
SE1 par sen
La figure 1 montre un exemple simple de l'utilisation de LINEST avec une variable indépendante. Les entrées dans la colonne B de cette feuille représentent une demande de produit mensuel pour une petite entreprise. Les chiffres dans la colonne A représentent les mois de la période. Supposons que vous voulez calculer la pente et ordonnée à l'origine de la droite de régression qui décrit le mieux la relation entre la demande et les mois. En d'autres termes, vous voulez décrire la tendance des données. Pour ce faire, sélectionner la gamme F6: G6, entrez la formule = LINEST (B2: B19, A2: A19). et appuyez sur Ctrl + Maj + Entrée. Le nombre résultant en F6 cellulaire est 20,613, la pente de la droite de régression; le nombre de G6 cellulaire est 4002,065, l'ordonnée à l'origine de la ligne.
Figure 1 La fonction LINEST calcule la pente et l'ordonnée à l'origine d'une ligne de régression.
Le LINEST et les fonctions LOGREG retournent seulement l'axe des y Coordonnées utilisés pour le calcul de lignes et de courbes. La différence entre les deux est que les projets DROITEREG une ligne droite et LOGEST projette une courbe exponentielle. Vous devez être prudent pour correspondre à la fonction appropriée à l'analyse à portée de main. La fonction DROITEREG pourrait être plus approprié pour les projections de ventes, et la fonction LOGREG pourrait être plus approprié pour des analyses statistiques ou les tendances de la population.
A l'intérieur un réel (immobilier) demande de régression
Cette application utilise la fonction DROITEREG pour analyser les statistiques dans la zone de données d'entrée et de générer un tableau de résultats en fonction des statistiques similaires dans la région ce que vous voulez. Le tableau LINEST est en fait situé dans les lignes cachées en dessous de la zone visible de la feuille de calcul, comme indiqué ci-après. La première rangée de valeurs dans la matrice de données LINEST est utilisé par la formule de prix estimé pour extrapoler une valeur estimée.
Habituellement, dans ce classeur, têtes de ligne et colonne sont cachés, les lignes 25 à 37 sont cachés, la protection de la feuille de calcul est activée, et les cellules sont verrouillées avec entrées autorisées uniquement dans les zones d'entrée désignés. conseil immobilier: Comme la note sur la feuille de calcul implique, vous pouvez utiliser les prix des maisons cotées pour arriver à un prix estimé, mais les prix de vente réels sont plus réalistes, si vous pouvez les obtenir.
La fonction TREND
DROITEREG renvoie une description mathématique de la ligne droite qui correspond le mieux les données connues. TENDANCE trouve des points qui se situent le long de cette ligne et qui tombent dans la catégorie inconnue. Vous pouvez utiliser les chiffres renvoyés par TREND pour tracer une courbe de tendance-ligne droite qui contribue à rendre le sens des données réelles. Vous pouvez également utiliser TREND pour extrapoler. ou faire des suppositions intelligentes sur, les données futures basées sur les tendances exposées par des données connues. (Attention. Bien que vous puissiez utiliser TREND pour tracer la ligne droite qui correspond le mieux à des données connues, TREND ne peut pas vous dire si cette ligne est un bon indicateur de l'avenir. Les statistiques de validation récuperés LINEST peut vous aider à faire cette évaluation. ) la fonction d'évolution prend la forme = TREND (y_connus, x_connus, x_nouveaux, const).
Les deux premiers arguments représentent les valeurs connues de vos variables dépendantes et indépendantes. Comme dans LINEST, l'argument de la y_connus est une seule colonne, une rangée ou une plage rectangulaire. L'argument de l'x_connus suit également le schéma décrit pour LINEST. Les troisième et quatrième arguments sont facultatifs. Si vous omettez x_nouveaux, la fonction TENDANCE considère x_nouveaux être identique à x_connus. Si vous incluez const, la valeur de cet argument doit être VRAI ou FAUX (1 ou 0). Si const est VRAI, les forces TREND b à 0.
Pour calculer les points de données de ligne de tendance qui correspondent le mieux à vos données connues, omettre simplement les troisième et quatrième arguments de cette fonction. Le tableau des résultats sera la même taille que la plage du x_connus. Dans la figure 2, nous avons utilisé TREND pour trouver la valeur de chaque point sur la ligne de régression qui décrit l'ensemble des données de l'exemple de la figure 1. Pour créer ces valeurs, nous avons sélectionné la gamme C2: C19 et entrâmes = TREND (B2: B19 , A2: A19) sous la forme d'une formule matricielle en utilisant Ctrl + Maj + Entrée.
Pour extrapoler à partir des données existantes, vous devez fournir une gamme pour x_nouveaux. Vous pouvez fournir autant ou aussi peu de cellules pour x_nouveaux que vous le souhaitez. Le tableau de résultat sera la même taille que la plage du new_x. Dans la figure 3, nous avons utilisé TREND pour calculer la demande pour les 19, 20, et 21 mois. Pour arriver à ces valeurs, nous avons tapé 19 à 21 dans A21: A23, choisi C21: C23, et entrâmes = TREND (B2: B19, A2: A19, A21: A23) comme une formule de tableau en appuyant sur Ctrl + Maj + Entrée.
Le calcul de régression exponentielle
Contrairement à la régression linéaire, qui trace les valeurs le long d'une ligne droite, la régression exponentielle décrit une courbe en calculant la matrice de valeurs nécessaires pour tracer elle. L'équation qui décrit une courbe de régression exponentielle est la suivante:
y = b * m1x1 * m2x2 *. * mnxn
Si vous avez une seule variable indépendante, l'équation est la suivante:
La fonction LOGEST
La fonction LOGREG fonctionne comme LINEST, à l'exception de l'utiliser pour analyser les données qui est non linéaire, et il renvoie les coordonnées d'une courbe exponentielle au lieu d'une ligne droite. LOGREG renvoie des valeurs de coefficient pour chaque variable indépendante et d'une valeur de la constante b. Cette fonction prend la forme = LOGEST (y_connus, x_connus, const, statistiques).
LOGEST accepte les mêmes arguments que la fonction DROITEREG et retourne un tableau de résultat de la même façon. Si vous définissez l'argument des statistiques en option à TRUE, la fonction retourne également des statistiques de validation.
Remarque: Le LINEST et les fonctions LOGREG retournent seulement l'axe y des coordonnées pour le calcul de lignes et de courbes. La différence entre les deux est que les projets DROITEREG une ligne droite et LOGEST projette une courbe exponentielle. Vous devez être prudent pour correspondre à la fonction appropriée à l'analyse à portée de main. La fonction DROITEREG pourrait être plus approprié pour les projections de ventes, et la fonction LOGREG pourrait être plus adapté à des applications telles que des analyses statistiques ou les tendances de la population.
La fonction CROISSANCE
Lorsque la fonction LOGREG renvoie une description mathématique de la courbe de régression exponentielle qui correspond le mieux à un ensemble de données connus, la fonction de croissance trouve des points qui se situent le long de cette courbe. La fonction de croissance fonctionne comme son homologue linéaire, la tendance, et prend la forme = CROISSANCE (y_connus, x_connus, x_nouveaux, const).