Fonctions statistiques Excel : didacticiel avec exemples, troisième partie

Excel fournit une large gamme de fonctions statistiques qui effectuent des calculs allant de la moyenne à des fonctions de distribution statistique et de ligne de tendance plus complexes.

Dans cet article, nous approfondirons les fonctions statistiques d'Excel pour calculer les fonctions de ligne de tendance.

Veuillez noter que certaines fonctions statistiques ont été introduites dans les versions récentes d'Excel et ne sont donc pas disponibles dans les anciennes versions.

Temps de lecture estimé : 12 minuti

Fonctions de ligne de tendance

Forecast

La fonction Prévisions d'Excel prédit un point futur sur une ligne de tendance linéaire adaptée à un ensemble donné de valeurs x et y.

syntaxe

= FORECAST( x, known_y's, known_x's )

sujets

• x : valeur x numérique pour laquelle vous souhaitez prédire une nouvelle valeur y.
• known_y's: Un tableau de valeurs y connues
• known_x's: Un tableau de valeurs x connues

Notez que la longueur du tableau de known_x doit être le même que celui de known_y et la variance de known_x il n’est pas nécessaire qu’il soit nul.

Exemple

Dans la feuille de calcul suivante, la fonction FORECAST Excel est utilisé pour prédire un point supplémentaire le long de la ligne droite la mieux ajustée à travers une série de valeurs x et y connues (stockées dans les cellules F2 : F7 et G2 : G7).

Comme le montre la cellule F7 de la feuille de calcul, la fonction permettant de calculer la valeur y attendue à x = 7 est :=FORECAST( 7, G2:G7, F2:F7 )

Cela donne le résultat 32.666667 .

Intercept

Parmi les fonctions de prévision d'Excel, nous trouvons le Intercept. La fonction Interception d'Excel calcule l'ordonnée à l'origine (la valeur à l'intersection de l'axe y) de la ligne de régression linéaire sur un ensemble donné de valeurs x et y.

syntaxe

= INTERCEPT( known_y's, known_x's )

sujets

• known_y's: Un tableau de valeurs y connues
• known_x's: Un tableau de valeurs x connues

Notez que la longueur du tableau de known_x doit être le même que celui de known_y et la variance de known_x il n’est pas nécessaire qu’il soit nul.

Exemple

La feuille de calcul suivante montre un exemple de la fonction Intercept d'Excel utilisé pour calculer le point où la ligne de régression linéaire passant par le known_x et known_y (répertorié dans les cellules F2:F7 et G2:G7) coupe l'axe y.

Le known_x et known_y sont tracées sur le graphique dans la feuille de calcul.

Comme indiqué dans la cellule F9 de la feuille de calcul, la formule de la fonction Intercepter est :=INTERCEPT( G2:G7, F2:F7 )

ce qui donne le résultat 2.4 .

Slope

Une autre fonction de prédiction très intéressante est la Pente (Slope) Excel calcule la pente de la droite de régression linéaire à travers un ensemble donné de valeurs x et y.

La syntaxe de la fonction est :

syntaxe

= SLOPE( known_y's, known_x's )

sujets

• known_y's: Un tableau de valeurs y connues
• known_x's: Un tableau de valeurs x connues

Notez que la longueur du tableau de known_x doit être le même que celui de known_y et la variance de known_x il n’est pas nécessaire qu’il soit nul.

Exemple

La feuille de calcul suivante montre un exemple de la fonction Slope (pente) d'Excel utilisée pour calculer la pente de la droite de régression linéaire passant par known_x et le known_y, dans les cellules F2:F7 et G2:G7.

Le known_x et known_y sont tracées sur le graphique dans la feuille de calcul.

Comme indiqué dans la cellule F9 de la feuille de calcul, la formule de la fonction Intercepter est :=SLOPE( G2:G7, F2:F7 )

ce qui donne le résultat 4.628571429.

Trend

Une fonction de prévision Excel très intéressante est la TENDANCE Excel (Trend) calcule la ligne de tendance linéaire à travers un ensemble donné de valeurs y et (éventuellement) un ensemble donné de valeurs x.

La fonction étend ensuite la ligne de tendance linéaire pour calculer des valeurs y supplémentaires pour un ensemble supplémentaire de nouvelles valeurs x.

La syntaxe de la fonction est :

syntaxe

= TREND( known_y's, [known_x's], [new_x's], [const] )

sujets

• known_y's: Un tableau de valeurs y connues
• [known_x's]: Un ou plusieurs tableaux de valeurs x connues. Il s'agit d'un argument facultatif qui, s'il est fourni, doit avoir la même longueur que l'ensemble des éléments known_y's. En cas d'omission, l'ensemble de [known_x's] prend la valeur {1, 2, 3, …}.
• [nouveau_x]: Un argument facultatif, fournissant un ou plusieurs tableaux de valeurs numériques représentant un ensemble de nouvelles valeurs x, pour lesquelles vous souhaitez calculer les nouvelles valeurs y correspondantes. Chaque tableau de [nouveau_x] doit correspondre à un tableau de [known_x's]. Si l'argument [nouveau_x] est omis, il est égal à [known_x's] .
• [coût]: Un argument logique facultatif spécifiant si la constante 'b', dans l'équation linéaire y = m x + b , doit être forcé à être égal à zéro. Soi [sables moins coûteux] est VRAI (ou si cet argument est omis) la constante b est traitée normalement ;
• Soi [sables moins coûteux] est FAUX, la constante b est fixée à 0 et l'équation de la droite devient y = mx .

Exemple

Dans la feuille de calcul suivante, la fonction Excel Trend est utilisée pour étendre une série de valeurs x et y situées sur la ligne droite y = 2x + 10. Les valeurs x et y connues sont stockées dans les cellules A2-B5 de la feuille de calcul et sont également affichés dans le graphique de la feuille de calcul.

Notez qu'il n'est pas essentiel que les points donnés s'ajustent exactement le long de la droite y = 2x + 10 (bien que dans cet exemple c'est le cas). La fonction Tendance d'Excel trouvera la ligne la mieux adaptée à tout ensemble de valeurs que vous fournissez.

La fonction Tendance utilise la méthode des moindres carrés pour trouver la ligne la mieux ajustée, puis l'utilise pour calculer les nouvelles valeurs y pour les nouvelles valeurs x fournies.

Dans cet exemple, les valeurs de [nouveau_x] sont stockés dans les cellules A8-A10, et la fonction Tendance d'Excel a été utilisée, dans les cellules B8-B10, pour trouver les nouvelles valeurs y correspondantes. Comme indiqué dans la barre de formule, la formule est := TENDANCE (B2:B5, A2:A5, A8:A10)

Vous voyez que la fonction Tendance dans la barre de formule est entourée d'accolades { }. Cela indique que la fonction a été saisie comme formule matricielle .

Growth

Parmi les fonctions de prévision d'Excel, nous trouvons le Growth. La fonction Growth Excel calcule la courbe de croissance exponentielle à travers un ensemble donné de valeurs y et (facultatif), un ou plusieurs ensembles de valeurs x. La fonction étend ensuite la courbe pour calculer des valeurs y supplémentaires pour un ensemble supplémentaire de nouvelles valeurs x.

La syntaxe de la fonction est :

syntaxe

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

sujets

• known_y's: Un tableau de valeurs y connues
• [known_x's]: Un ou plusieurs tableaux de valeurs x connues. Il s'agit d'un argument facultatif qui, s'il est fourni, doit avoir la même longueur que l'ensemble des éléments known_y's. En cas d'omission, l'ensemble de [known_x's] prend la valeur {1, 2, 3, …}.
• [nouveau_x]: Un ensemble de nouvelles valeurs x, pour lequel la fonction calcule les nouvelles valeurs y correspondantes. En cas d'omission, on suppose que l'ensemble de [nouveau_x] est égal à celui de [known_x's] et la fonction renvoie les valeurs y qui se trouvent sur la courbe de croissance exponentielle calculée.
• [coût]: Un argument logique facultatif spécifiant si la constante 'b', dans l'équation linéaire y = b * m^x , doit être forcé pour être égal à 1. Si [sables moins coûteux] est VRAI (ou si cet argument est omis) la constante b est traitée normalement ; Soi [sables moins coûteux] est FAUX, la constante b est fixée à 1 et l'équation de la droite devient y = mx .

Exemple

Dans la feuille de calcul suivante, la fonction de croissance Excel est utilisée pour étendre une série de valeurs x et y qui se trouvent sur la courbe de croissance exponentielle y = 5 * 2^x. Ceux-ci sont stockés dans les cellules A2-B5 de la feuille de calcul et apparaissent également dans le graphique de la feuille de calcul.

La fonction de croissance calcule la courbe de croissance exponentielle qui correspond le mieux aux valeurs x et y connues fournies. Dans cet exemple simple, la courbe la mieux adaptée est la courbe exponentielle y = 5 * 2^x.

Une fois qu'Excel a calculé l'équation de la courbe de croissance exponentielle, il peut l'utiliser pour calculer les nouvelles valeurs y pour les nouvelles valeurs x fournies dans les cellules A8-A10.

Dans cet exemple, les valeurs de [new_x's] sont stockés dans les cellules A8-A10 et la fonction Growth d'Excel a été inséré dans les cellules B8-B10. Comme indiqué dans la barre de formule, la formule pour cela est :=Growth(B2:B5, A2:A5, A8:A10)

Vous pouvez voir que la fonction Croissance dans la barre de formule est entourée d'accolades { }. Cela indique que la fonction a été saisie comme formule matricielle .

Notez que même si les points de l'exemple ci-dessus s'ajustent exactement le long de la courbe y = 5 * 2^x, cela n'est pas essentiel. La fonction Growth Excel trouvera la courbe la mieux adaptée à tout ensemble de valeurs que vous fournissez.

Fonctions financières

d'Entourage

la fonction Effect Excel renvoie le taux d'intérêt annuel effectif pour un taux d'intérêt nominal donné et un nombre donné de périodes de composition par an.

Taux d’intérêt annuel effectif

Le taux d'intérêt annuel effectif est une mesure des intérêts qui intègre la capitalisation des intérêts et est souvent utilisé pour comparer des prêts financiers avec des conditions de capitalisation différentes.

Le taux d’intérêt annuel effectif est calculé à l’aide de l’équation suivante :

où nominal_rate est le taux d'intérêt nominal e npery est le nombre de périodes de composition par an.

La syntaxe de la fonction est :

syntaxe

= EFFECT( nominal_rate, npery )

sujets

• nominal_rate: Le taux d'intérêt nominal (doit être une valeur numérique comprise entre 0 et 1)
• npery: Le nombre de périodes de composition par an (doit être un entier positif).

Exemple

La feuille de calcul suivante montre trois exemples de la fonction Excel Effect :

Si le résultat de la fonction Effect s'affiche sous forme décimale ou affiche 0 %, ces deux problèmes sont probablement dus au formatage de la cellule contenant la fonction Effect.

Le problème peut donc être résolu en formatant la cellule en pourcentage, avec des décimales.

Pour faire ça:

1. Sélectionnez les cellules à formater en pourcentage.
2. Ouvrez la boîte de dialogue « Formater les cellules » en utilisant l'une des méthodes suivantes :
   • Cliquez avec le bouton droit sur la cellule ou la plage sélectionnée et sélectionnez l'option Formater les cellules… depuis le menu contextuel ;
   • Cliquez sur le lanceur de boîte de dialogue dans le groupe Numéro de l'onglet Accueil Ruban Excel ;
   • Utilisez le raccourci clavier CTRL-1 (c'est-à-dire sélectionnez la touche CTRL et, en la maintenant enfoncée, sélectionnez la touche « 1 » (un)).
3. Dans la boîte de dialogue « Formater les cellules » :
   • Assurez-vous que la carte Numero en haut de la boîte de dialogue est sélectionné.
   • sélectionner Pourcentage de la liste Catégorie sur le côté gauche de la boîte de dialogue .Cela fera apparaître des options supplémentaires sur le côté droit de la case à cocher, vous permettant de sélectionner le nombre de décimales que vous souhaitez afficher.
   • Une fois que vous avez sélectionné le nombre de décimales que vous souhaitez afficher, cliquez sur OK .

Nominal

la fonction Nominal Excel renvoie le taux d'intérêt nominal pour un taux d'intérêt effectif donné et un nombre donné de périodes de composition par an.

La syntaxe de la fonction est :

syntaxe

= NOMINAL( effect_rate, npery )

sujets

• effect_rate: Le taux d’intérêt effectif (une valeur numérique comprise entre 0 et 1).
• npery: Le nombre de périodes de composition par an (doit être un entier positif).

Exemple

Dans la feuille de calcul suivante, la fonction Nominal d'Excel est utilisé pour calculer le taux d'intérêt nominal de trois prêts avec des conditions différentes.

Ercole Palmeri