Régression linéaire dans Google Sheets (+ exemples)

Google Sheets propose des fonctions pour de nombreuses méthodes d'analyse de données, dont la régression linéaire. Cette méthode sert souvent à quantifier la relation entre une variable dépendante et une variable indépendante. Autrement dit, si vous avez repéré une tendance linéaire dans vos données, vous pouvez prévoir des valeurs futures avec la régression linéaire. La fonction LINEST de Google Sheets permet d'effectuer des régressions linéaires simples et multiples à partir des valeurs connues de vos variables. Vous obtenez rapidement la pente et l'ordonnée à l'origine, ainsi que d'autres statistiques de régression.

Dans cet article, vous allez découvrir la fonction LINEST et sa syntaxe, et voir comment l'utiliser pour une analyse de régression dans Google Sheets. Des exemples montrent son utilisation avec une seule variable explicative (régression simple) et avec plusieurs variables explicatives (régression multiple).

Syntaxe de la fonction LINEST

La fonction LINEST a quatre paramètres, dont seul le premier est obligatoire.

=LINEST(known_data_y, [known_data_x], [calculate_b], [verbose])

• known_data_y* (obligatoire) : Les valeurs connues de la variable de réponse ou dépendante (y).
• known_data_x (facultatif) : Les valeurs connues de la variable explicative ou indépendante (x).
• calculate_b (facultatif) : Indique si l'ordonnée à l'origine (b) doit être calculée. La valeur par défaut est "TRUE", ce qui convient pour une régression linéaire.
• verbose (facultatif) : Indique si vous voulez voir des statistiques de régression supplémentaires ou seulement la pente et l'ordonnée à l'origine. La valeur par défaut est "FALSE".

Vous pouvez donc l'utiliser pour calculer la tendance des valeurs de votre variable dépendante même sans variable indépendante. Vous pouvez aussi l'utiliser lorsque plusieurs variables indépendantes doivent être prises en compte. C'est une fonction très souple et très utile.

Dans les sections suivantes, vous allez apprendre à utiliser la fonction pour des régressions linéaires simples et multiples.

Comment utiliser la fonction LINEST dans Google Sheets (régression simple)

Suivez les instructions ci-dessous pour effectuer une régression linéaire simple dans Google Sheets. Dans cet exemple, j'utilise le montant des ventes comme variable de réponse ou dépendante. La variable explicative ou indépendante est le budget consacré à la publicité payante.

1. Ouvrez le fichier Google Sheets contenant les données des variables explicative et de réponse.

2. Tapez "=LINEST(" dans une cellule vide pour afficher l'aide. Sélectionnez la plage de cellules contenant les valeurs connues de la variable de réponse, "sales_amount".

3. Après la virgule, sélectionnez la plage de valeurs connues de la variable indépendante, "paid_ads".

4. Je règle les deux derniers paramètres sur "TRUE", car je veux que "b" soit calculé et voir d'autres statistiques de régression que la pente et l'ordonnée à l'origine. N'oubliez pas de fermer la parenthèse et d'appuyer sur Entrée.

5. C'est fait. Toutes les statistiques de régression sont dans votre feuille de calcul, mais laquelle est laquelle ? À l'étape suivante, j'ajoute un libellé à chacune.

6. Ci-dessous, j'ai identifié les statistiques renvoyées par la formule lorsque le paramètre "verbose" vaut "TRUE". S'il vaut "FALSE" ou reste vide, seules la "pente" et l'"ordonnée à l'origine" sont renvoyées.

Comment effectuer une régression linéaire multiple dans Google Sheets

Suivez les instructions ci-dessous pour effectuer une régression linéaire multiple dans Google Sheets. Je reprends l'exemple précédent en ajoutant une seconde variable indépendante. La variable dépendante reste le montant des ventes, mais les variables explicatives sont désormais le budget de publicité payante et le montant des salaires commerciaux.

1. Ouvrez le fichier Google Sheets contenant les données de la variable de réponse et des deux variables explicatives.

2. Dans une cellule vide, tapez "=LINEST(" pour afficher l'aide. Sélectionnez la plage de cellules contenant les valeurs connues de la variable de réponse, "sales_amount".

3. Après la virgule, sélectionnez la plage de valeurs connues des variables indépendantes, "paid_ads" et "sales_salaries".

4. Le troisième paramètre vaut "TRUE", car je veux que "b" soit calculé. Je veux aussi voir toutes les statistiques de régression, donc le dernier paramètre vaut également "TRUE". N'oubliez pas de fermer la parenthèse et d'appuyer sur "Entrée".

5. C'est fait. Les statistiques de la régression multiple sont dans votre feuille de calcul. À l'étape suivante, je les identifie avec des libellés.

6. Ci-dessous, "s.error" désigne l'erreur type. Vous pouvez ignorer les cellules "#N/A" : il ne s'agit pas d'une erreur, ces cellules ne sont simplement pas utilisées.

Comment lire le tableau de résultats de LINEST

Avec "verbose" réglé sur "TRUE", LINEST renvoie cinq lignes de statistiques. Les lignes 1 et 2 contiennent une colonne par variable indépendante, plus une pour l'ordonnée à l'origine. Les lignes 3 à 5 n'utilisent que les deux premières colonnes.

• Ligne 1: la pente de chaque variable indépendante, puis l'ordonnée à l'origine (b).
• Ligne 2: l'erreur type de chaque pente et de l'ordonnée à l'origine.
• Ligne 3: le coefficient de détermination (r²) et l'erreur type de l'estimation de y.
• Ligne 4: la statistique F et les degrés de liberté, nécessaires pour vérifier si la relation est significative.
• Ligne 5: la somme des carrés de la régression et la somme des carrés des résidus.

Dans l'exemple simple, la pente est d'environ 9.37 : chaque dollar supplémentaire dépensé en publicité payante s'accompagne d'environ 9.37 $ de ventes en plus. Un r² de 0.93 signifie que la droite explique 93 % de la variation des ventes.

Dans une régression multiple, LINEST affiche les pentes dans l'ordre inverse. La première valeur, 6.19, correspond à sales_salaries, la dernière colonne x, et 2.80 à paid_ads. Les cellules #N/A des lignes 3 à 5 sont simplement les colonnes que ces lignes n'utilisent pas.

Pour une régression simple, r² est aussi le carré de la valeur obtenue avec la fonction CORREL appliquée aux mêmes données. Pour voir la droite ajustée, représentez les données dans un diagramme de dispersion et ajoutez une courbe de tendance. Si vous préférez passer par un menu plutôt que par une formule, le module complémentaire XLMiner Analysis ToolPak effectue la même régression.

Conclusion

Comme vous l'avez vu, une régression linéaire avec la fonction LINEST de Google Sheets est simple à réaliser. Avec une ou plusieurs variables indépendantes, vous obtenez rapidement la pente et l'ordonnée à l'origine.

Vous pourriez aussi les obtenir avec les fonctions SLOPE et INTERCEPT. La différence, c'est que LINEST fournit aussi de nombreuses statistiques de régression supplémentaires pour une analyse complète : erreurs types de la pente, de l'ordonnée à l'origine et de l'estimation de y, coefficient de détermination, statistique F, degrés de liberté, et sommes des carrés de la régression et des résidus.

Vous savez maintenant effectuer des régressions linéaires simples et multiples dans Google Sheets avec la fonction LINEST. Vous savez aussi identifier les statistiques renvoyées par la fonction pour interpréter rapidement les résultats. Pour en savoir plus sur l'analyse de données dans Google Sheets, consultez notre article sur comment réaliser une analyse de scénarios dans Google Sheets.

Pour prédire une valeur future à partir du même type de données, consultez ce guide : Fonction FORECAST de Google Sheets. Elle ajuste une droite à vos données et renvoie la valeur de y pour une nouvelle valeur de x.