28 Octobre

Bonjour, chers lecteurs du blog ! Aujourd'hui, nous parlerons de régressions non linéaires. Solution régressions linéaires peut être consulté via LIEN.

Cette méthode principalement utilisé dans modélisation économique et les prévisions. Son objectif est d'observer et d'identifier les dépendances entre deux indicateurs.

Les principaux types de régressions non linéaires sont :

• polynôme (quadratique, cubique);
• hyperbolique;
• calme;
• démonstratif;
• logarithmique

Diverses combinaisons peuvent également être utilisées. Par exemple, pour l'analyse de séries chronologiques dans les études bancaires, d'assurance et démographiques, la courbe de Gompzer est utilisée, qui est un type de régression logarithmique.

Dans la prévision par régressions non linéaires, l'essentiel est de connaître le coefficient de corrélation, qui nous montrera s'il existe ou non une relation étroite entre deux paramètres. En règle générale, si le coefficient de corrélation est proche de 1, alors il existe un lien et la prévision sera assez précise. Un de plus élément important les régressions non linéaires sont l'erreur relative moyenne ( UN ), si c'est dans l'intervalle<8…10%, значит модель достаточно точна.

C'est ici que nous terminerons probablement le bloc théorique et passerons aux calculs pratiques.

Nous avons un tableau des ventes de voitures sur une période de 15 ans (notons-le X), le nombre d'étapes de mesure sera l'argument n, nous avons aussi des revenus pour ces périodes (notons-le Y), nous devons prédire quoi les revenus seront dans le futur. Construisons le tableau suivant :

Pour l’étude, nous devrons résoudre l’équation (dépendance de Y à X) : y=ax 2 +bx+c+e. Il s’agit d’une régression quadratique par paires. Dans ce cas, nous appliquons la méthode des moindres carrés pour découvrir les arguments inconnus - a, b, c. Cela conduira à un système d’équations algébriques de la forme :

Pour résoudre ce système, nous utiliserons par exemple la méthode de Cramer. On voit que les sommes incluses dans le système sont des coefficients pour les inconnues. Pour les calculer, nous ajouterons plusieurs colonnes au tableau (D,E,F,G,H) et signerons selon le sens des calculs - dans la colonne D nous placerons x au carré, en E nous le cuberons, en F nous allons multiplier les exposants x et y, dans H nous mettons x au carré et multiplions par y.

Vous obtiendrez un tableau du formulaire rempli avec les éléments nécessaires pour résoudre l’équation.

Formons une matrice UN système constitué de coefficients pour les inconnues sur les côtés gauches des équations. Plaçons-le dans la cellule A22 et appelons-le " UNE=". Nous suivons le système d'équations que nous avons choisi pour résoudre la régression.

Autrement dit, dans la cellule B21, nous devons placer la somme de la colonne où nous avons élevé l'indicateur X à la quatrième puissance - F17. Faisons simplement référence à la cellule - "=F17". Ensuite, nous avons besoin de la somme de la colonne où X a été cubique - E17, puis nous suivons strictement le système. Ainsi, nous devrons remplir toute la matrice.

Conformément à l'algorithme de Cramer, nous taperons une matrice A1, similaire à A, dans laquelle, à la place des éléments de la première colonne, il faudra placer les éléments des côtés droits des équations système. Autrement dit, la somme de la colonne X au carré multipliée par Y, la somme de la colonne XY et la somme de la colonne Y.

Nous aurons également besoin de deux autres matrices - appelons-les A2 et A3 dans lesquelles les deuxième et troisième colonnes seront constituées des coefficients des membres droits des équations. L'image sera comme ça.

Suivant l'algorithme choisi, nous devrons calculer les valeurs des déterminants (déterminants, D) des matrices résultantes. Utilisons la formule MOPRED. Nous placerons les résultats dans les cellules J21:K24.

On calculera les coefficients de l'équation selon Cramer dans les cellules en face des déterminants correspondants à l'aide de la formule : un(dans la cellule M22) - « =K22/K21 » ; b(dans la cellule M23) - « =K23/K21 » ; Avec(dans la cellule M24) - « =K24/K21 ».

Nous obtenons notre équation souhaitée de régression quadratique appariée :

y=-0,074x2 +2,151x+6,523

Évaluons l'étroitesse de la relation linéaire à l'aide de l'indice de corrélation.

Pour calculer, ajoutez une colonne J supplémentaire au tableau (appelons-la y*). Le calcul sera le suivant (d'après l'équation de régression que nous avons obtenue) - "= 22 $ M$*B2*B2+$M$23*B2+$M$24." Plaçons-le dans la cellule J2. Il ne reste plus qu'à faire glisser le marqueur de remplissage automatique vers la cellule J16.

Pour calculer les sommes (moyenne Y-Y) 2, ajoutez les colonnes K et L au tableau avec les formules correspondantes. Nous calculons la moyenne de la colonne Y à l'aide de la fonction MOYENNE.

Dans la cellule K25, nous placerons la formule de calcul de l'indice de corrélation - « =ROOT(1-(K17/L17)) ».

Nous voyons que la valeur de 0,959 est très proche de 1, ce qui signifie qu’il existe une relation non linéaire étroite entre les ventes et les années.

Il reste à évaluer la qualité de l'ajustement de l'équation de régression quadratique résultante (indice de détermination). Il est calculé à l'aide de la formule de l'indice de corrélation au carré. Autrement dit, la formule dans la cellule K26 sera très simple : « =K25*K25 ».

Le coefficient de 0,920 est proche de 1, ce qui indique une grande qualité d'ajustement.

La dernière étape consiste à calculer l'erreur relative. Ajoutons une colonne et y entrons la formule : « =ABS((C2-J2)/C2), ABS - module, valeur absolue. Dessinez le marqueur vers le bas et dans la cellule M18, affichez la valeur moyenne (AVERAGE), attribuez le format de pourcentage aux cellules. Le résultat obtenu - 7,79% se situe dans les valeurs d'erreur acceptables<8…10%. Значит вычисления достаточно точны.

Si le besoin s’en fait sentir, nous pouvons construire un graphique en utilisant les valeurs obtenues.

Un fichier d'exemple est joint - LIEN !

Catégories :// du 28/10/2017

La droite de régression est un reflet graphique de la relation entre les phénomènes. Vous pouvez très clairement construire une droite de régression dans Excel.

Pour ce faire, vous avez besoin de :

1.Ouvrez Excel

2.Créez des colonnes de données. Dans notre exemple, nous allons construire une ligne de régression, ou une relation, entre l'agressivité et le doute de soi chez les élèves de première année. 30 enfants ont participé à l'expérience, les données sont présentées dans le tableau Excel :

1 colonne - numéro de sujet

2 colonnes - agressivité en points

3 colonnes - méfiance en points

3.Ensuite, vous devez sélectionner les deux colonnes (sans le nom de la colonne), cliquez sur l'onglet insérer , choisir place , et choisissez le tout premier parmi les mises en page proposées pointer avec des marqueurs .

4. Nous avons donc un modèle pour la droite de régression - ce qu'on appelle - nuage de points. Pour accéder à la droite de régression, cliquez sur le chiffre obtenu et appuyez sur la touche de tabulation constructeur, trouver sur le panneau présentations de graphiques et choisissez M UN ket9 , ça dit aussi f(x)

5. Nous avons donc une droite de régression. Le graphique montre également son équation et le carré du coefficient de corrélation

6. Il ne reste plus qu'à ajouter le nom du graphique et le nom des axes. Aussi, si vous le souhaitez, vous pouvez supprimer la légende, réduire le nombre de lignes de quadrillage horizontales (onglet mise en page , alors filet ). Les modifications et paramètres de base sont effectués dans l'onglet Mise en page

La droite de régression a été construite dans MS Excel. Vous pouvez maintenant l'ajouter au texte de l'œuvre.

L'analyse de régression et de corrélation sont des méthodes de recherche statistique. Ce sont les manières les plus courantes de montrer la dépendance d’un paramètre à une ou plusieurs variables indépendantes.

Ci-dessous, à l’aide d’exemples pratiques précis, nous examinerons ces deux analyses très populaires parmi les économistes. Nous donnerons également un exemple d'obtention de résultats en les combinant.

Analyse de régression dans Excel

Montre l'influence de certaines valeurs (indépendantes, indépendantes) sur la variable dépendante. Par exemple, dans quelle mesure le nombre de personnes économiquement actives dépend-il du nombre d'entreprises, des salaires et d'autres paramètres. Ou encore : comment les investissements étrangers, les prix de l’énergie, etc. affectent-ils le niveau du PIB.

Le résultat de l'analyse permet de mettre en évidence les priorités. Et sur la base des principaux facteurs, prévoir, planifier l'évolution des domaines prioritaires et prendre des décisions de gestion.

La régression se produit :

• linéaire (y = a + bx) ;
• parabolique (y = a + bx + cx 2) ;
• exponentiel (y = a * exp(bx));
• puissance (y = a*x^b) ;
• hyperbolique (y = b/x + a);
• logarithmique (y = b * 1n(x) + a);
• exponentielle (y = a * b^x).

Examinons un exemple de création d'un modèle de régression dans Excel et d'interprétation des résultats. Prenons le type de régression linéaire.

Tâche. Dans 6 entreprises, le salaire mensuel moyen et le nombre de salariés qui quittent l'entreprise ont été analysés. Il est nécessaire de déterminer la dépendance du nombre de salariés qui quittent leur emploi par rapport au salaire moyen.

Le modèle de régression linéaire ressemble à ceci :

Y = une 0 + une 1 x 1 +…+une k x k.

Où a sont des coefficients de régression, x sont des variables d'influence, k est le nombre de facteurs.

Dans notre exemple, Y est l’indicateur de départ d’employés. Le facteur d'influence est le salaire (x).

Excel possède des fonctions intégrées qui peuvent vous aider à calculer les paramètres d'un modèle de régression linéaire. Mais le module complémentaire « Analysis Package » le fera plus rapidement.

Nous activons un outil analytique puissant :

Une fois activé, le module complémentaire sera disponible dans l'onglet Données.

Faisons maintenant l'analyse de régression elle-même.

Tout d’abord, nous prêtons attention au R-carré et aux coefficients.

R-carré est le coefficient de détermination. Dans notre exemple – 0,755, ou 75,5 %. Cela signifie que les paramètres calculés du modèle expliquent 75,5% de la relation entre les paramètres étudiés. Plus le coefficient de détermination est élevé, meilleur est le modèle. Bon - supérieur à 0,8. Mauvais – moins de 0,5 (une telle analyse peut difficilement être considérée comme raisonnable). Dans notre exemple – « pas mal ».

Le coefficient 64,1428 montre ce que sera Y si toutes les variables du modèle considéré sont égales à 0. Autrement dit, la valeur du paramètre analysé est également influencée par d'autres facteurs non décrits dans le modèle.

Le coefficient -0,16285 montre le poids de la variable X sur Y. Autrement dit, le salaire mensuel moyen dans ce modèle affecte le nombre d'abandons avec un poids de -0,16285 (il s'agit d'un faible degré d'influence). Le signe « - » indique un impact négatif : plus le salaire est élevé, moins il y a de démissions. Ce qui est juste.



Analyse de corrélation dans Excel

L'analyse de corrélation permet de déterminer s'il existe une relation entre les indicateurs dans un ou deux échantillons. Par exemple, entre la durée de fonctionnement d'une machine et le coût des réparations, le prix du matériel et la durée de fonctionnement, la taille et le poids des enfants, etc.

S'il existe un lien, une augmentation d'un paramètre entraîne-t-elle une augmentation (corrélation positive) ou une diminution (négative) de l'autre. L'analyse de corrélation aide l'analyste à déterminer si la valeur d'un indicateur peut être utilisée pour prédire la valeur possible d'un autre.

Le coefficient de corrélation est noté r. Varie de +1 à -1. La classification des corrélations pour différents domaines sera différente. Lorsque le coefficient est égal à 0, il n’existe pas de relation linéaire entre les échantillons.

Voyons comment trouver le coefficient de corrélation à l'aide d'Excel.

Pour trouver des coefficients appariés, la fonction CORREL est utilisée.

Objectif : Déterminer s'il existe une relation entre la durée de fonctionnement d'un tour et le coût de son entretien.

Placez le curseur dans n'importe quelle cellule et appuyez sur le bouton fx.

1. Dans la catégorie « Statistique », sélectionnez la fonction CORREL.
2. Argument « Tableau 1 » - la première plage de valeurs – temps de fonctionnement de la machine : A2 : A14.
3. Argument « Tableau 2 » - deuxième plage de valeurs – coût de réparation : B2:B14. Cliquez sur OK.

Pour déterminer le type de connexion, il faut regarder le nombre absolu du coefficient (chaque domaine d'activité a son propre barème).

Pour l'analyse de corrélation de plusieurs paramètres (plus de 2), il est plus pratique d'utiliser « Data Analysis » (le module complémentaire « Analysis Package »). Vous devez sélectionner la corrélation dans la liste et désigner le tableau. Tous.

Les coefficients résultants seront affichés dans la matrice de corrélation. Comme ça:

Analyse de corrélation et de régression

En pratique, ces deux techniques sont souvent utilisées ensemble.

Exemple:

Désormais, les données de l'analyse de régression sont devenues visibles.

Le package MS Excel vous permet d'effectuer la plupart du travail très rapidement lors de la construction d'une équation de régression linéaire. Il est important de comprendre comment interpréter les résultats obtenus. Pour construire un modèle de régression, vous devez sélectionner Outils\Analyse des données\Régression (dans Excel 2007, ce mode se trouve dans le bloc Données/Analyse des données/Régression). Copiez ensuite les résultats dans un bloc pour analyse.

Donnée initiale:

Les résultats d'analyse

Inclure dans le rapport
Calcul des paramètres de l'équation de régression
Matériel théorique
Équation de régression à l'échelle standard
Coefficient de corrélation multiple (indice de corrélation multiple)
Coefficients d'élasticité partielle
Évaluation comparative de l'influence des facteurs analysés sur la caractéristique résultante (d - coefficients de détermination séparée)

Vérification de la qualité de l'équation de régression construite
Signification des coefficients de régression b i (statistiques t. Test de Student)
Signification de l'équation dans son ensemble (statistiques F. Test de Fisher). Coefficient de détermination
Tests F partiels

Niveau de signification 0.005 0.01 0.025 0.05 0.1 0.25 0.4

DANS Exceller Il existe un moyen encore plus rapide et plus pratique de tracer une régression linéaire (et même les principaux types de régressions non linéaires, comme indiqué ci-dessous). Cela peut être fait comme suit:

1) sélectionnez les colonnes avec des données X Et Oui(ils devraient être dans cet ordre !) ;

2) appeler Assistant Graphique et sélectionnez dans le groupe Taper – Place et appuyez immédiatement sur Prêt;

3) sans désélectionner le schéma, sélectionnez l'élément du menu principal qui apparaît Diagramme, dans lequel vous devez sélectionner l'élément Ajouter une ligne de tendance;

4) dans la boîte de dialogue qui apparaît Ligne de tendance dans l'onglet Taper choisir Linéaire;

5) dans l'onglet Possibilités vous pouvez activer l'interrupteur Afficher l'équation dans le diagramme, ce qui vous permettra de voir l'équation de régression linéaire (4.4), dans laquelle les coefficients (4.5) seront calculés.

6) Dans le même onglet vous pouvez activer le switch Placez la valeur de fiabilité d'approximation (R ^ 2) sur le diagramme. Cette valeur est le carré du coefficient de corrélation (4.3) et montre dans quelle mesure l'équation calculée décrit la dépendance expérimentale. Si R. 2 est proche de l’unité, alors l’équation de régression théorique décrit bien la dépendance expérimentale (la théorie s’accorde bien avec l’expérience), et si R. 2 est proche de zéro, alors cette équation n'est pas adaptée pour décrire la dépendance expérimentale (la théorie n'est pas d'accord avec l'expérience).

Après avoir effectué les actions décrites, vous obtiendrez un diagramme avec un graphique de régression et son équation.

§4.3. Principaux types de régression non linéaire

Régression parabolique et polynomiale.

Parabolique dépendance de la valeur Oui de la taille X s'appelle une dépendance exprimée par une fonction quadratique (parabole du 2ème ordre) :

Cette équation s'appelle équation de régression parabolique Y sur X. Possibilités UN, b, Avec sont appelés coefficients de régression parabolique. Le calcul des coefficients de régression parabolique est toujours fastidieux, il est donc recommandé d'utiliser un ordinateur pour les calculs.

L'équation (4.8) de régression parabolique est un cas particulier d'une régression plus générale appelée polynomiale. Polynôme dépendance de la valeur Oui de la taille X s'appelle une dépendance exprimée par un polynôme n-ème ordre :

où sont les chiffres et moi (je=0,1,…, n) sont appelés coefficients de régression polynomiale.

Régression de puissance.

Pouvoir dépendance de la valeur Oui de la taille X est appelée une dépendance de la forme :

Cette équation s'appelle équation de régression de puissance Y sur X. Possibilités UN Et b sont appelés coefficients de régression de puissance.

ln = ln un+b· dans X. (4.11)

Cette équation décrit une droite sur un plan avec des axes de coordonnées logarithmiques ln X et ln. Par conséquent, le critère d'applicabilité de la régression de puissance est l'exigence que les points des logarithmes des données empiriques ln x je et ln et jeétaient les plus proches de la ligne droite (4,11).

Régression exponentielle.

Indicatif(ou exponentiel) dépendance de la valeur Oui de la taille X est appelée une dépendance de la forme :

(ou ). (4.12)

Cette équation s'appelle équation exponentielle(ou exponentiel) régression Y sur X. Possibilités UN(ou k) Et b sont appelés coefficients exponentiels(ou exponentiel) régression.

Si nous prenons le logarithme des deux côtés de l’équation de régression de puissance, nous obtenons l’équation

ln = X dans un+ln b(ou ln = k x+ln b). (4.13)

Cette équation décrit la dépendance linéaire du logarithme d'une quantité ln sur une autre quantité X. Par conséquent, le critère d’applicabilité de la régression de puissance est l’exigence que les points de données empiriques de même valeur x je et logarithmes d'une autre quantité ln et jeétaient les plus proches de la ligne droite (4,13).

Régression logarithmique.

Logarithmique dépendance de la valeur Oui de la taille X est appelée une dépendance de la forme :

=un+b· dans X. (4.14)

Cette équation s'appelle équation de régression logarithmique Y sur X. Possibilités UN Et b sont appelés coefficients de régression logarithmique.

Régression hyperbolique.

Hyperbolique dépendance de la valeur Oui de la taille X est appelée une dépendance de la forme :

Cette équation s'appelle équation de régression hyperbolique Y sur X. Possibilités UN Et b sont appelés coefficients de régression hyperbolique et sont déterminés par la méthode des moindres carrés. L'application de cette méthode conduit aux formules :

Dans les formules (4.16-4.17) la sommation est effectuée sur l'indice je de un au nombre d'observations n.

Malheureusement, dans Exceller il n'existe aucune fonction qui calcule les coefficients de régression hyperbolique. Dans les cas où l'on ne sait pas que les quantités mesurées sont liées par une proportionnalité inverse, il est recommandé de rechercher une équation de régression en puissance au lieu de l'équation de régression hyperbolique, donc dans Exceller il existe une procédure pour le trouver. Si une dépendance hyperbolique est supposée entre les grandeurs mesurées, alors ses coefficients de régression devront être calculés à l'aide de tables de calcul auxiliaires et d'opérations de sommation à l'aide de formules (4.16-4.17).