La « validation de données »

Nous avons déjà donné – dans ce blog – de très nombreux exemples de validation de données.

Pour les utilisateurs d’Excel 2003 ou XP

Les utilisateurs d’Excel 2003 ou XP trouveront un tutorial assez complet sur la validation de données dans les articles « La validation de cellule » des 13 et 18 janvier 2007.

Pour les utilisateurs d’Excel 2007

Avec Excel 2007, il faut chercher la commande « Validation des données » dans le bloc « Outils de données » de l’onglet « Données » :

Le menu déroulant de cette commande propose trois commandes : « Validation des données », « Entourer les données non valides » et « Effacer les cercles de validation ». Vous activez donc la première de ces trois commandes et vous vous retrouvez alors exactement dans la même situation que si – avec Excel 2003 ou XP – vous utilisez la commande « Validation » du menu « Données ».

Pour savoir ce que vous pouvez faire ensuite, vous pouvez à présent lire les articles « La validation de cellule » des 13 et 18 janvier 2007 !

Pour les utilisateurs de toutes les versions d’Excel !

Que vous utilisiez Excel 2003/XP ou Excel 2007, vous pourrez lire avec intérêt l’article « Une protection par validation » du 22 janvier 2007.

Formules pour les statistiques

Nous avons indiqué, dans le dernier article, qu’il était regrettable que l’utilitaire d’analyse ne renvoie que des constantes. Ce serait bien mieux s’il renvoyait des formules, afin que les calculs soient dynamiques.

Voici donc les formules que vous devez utiliser pour obtenir – de façon dynamique – les mêmes résultats que ceux proposés par les statistiques descriptives de l’utilitaire d’analyse :

La seule formule que l’on ne peut pas lire entièrement ici est celle de la cellule D16. La voici : =loi.student.inverse(0,05;D13-1)*D5/racine(D13).

Avec ces formules, plus besoin de faire effectuer cette étude par l’utilitaire d’analyse !

Les statistiques descriptives

Voici les résultats que nous donne la commande « Statistiques descriptives » pour nos 36 données, avec les réglages présentés dans le dernier article.

Remarque 1 – Les résultats en colonne D ne sont hélas que des constantes : si vous avez le malheur de modifier quoi que ce soit dans la colonne A, il faudra redemander une analyse pour actualiser les résultats !

Remarque 2 – Le terme « Erreur-type » n'est pas un terme statistique français. Au lieu de « Nombre d’échantillons », il faudrait lire « Nombre de valeurs ». Notez aussi que « asymétrie » et « Kurtosis » sont mal orthographiés : avec Excel 2003/XP comme avec Excel 2007, ces deux mots comportent un « s » superflu. Merci à l’asshole (avec deux « s ») qui a traduit, et bravo au contrôle de qualité de Microsoft !

Remarque 3 – Enfin, les termes « Maximum(4) » et « Minimum(4) » seraient mieux appelés « Plus grande valeur (4ème) » et « Plus petite valeur (4ème) ».

Les stats de l’utilitaire d’analyse

Quand l’utilitaire d’analyse est installé, des fonctions et fonctionnalités fort nombreuses sont ajoutées à Excel, en particulier dans le domaine statistique.

Ainsi, quant on déroule la commande « Utilitaire d’analyse » qui apparaît alors en bas du menu « Outils », on découvre 19 traitements statistiques possibles (cf. premier encart ci-dessous) :

Quand on sélectionne « Statistiques descriptives », on obtient le second encart ci-dessus, dont nous commenterons les résultats dans notre prochain article. Nous avons paramétré ici cet écran pour afficher les résultats appliqués aux données en A1:A36, et ce à partir de la cellule C1 de la feuille active.

Activation de l'utilitaire d'analyse avec Excel 2007

Si vous utilisez Excel 2007, il faut activer le complément « Analysis ToolPak » en passant par le bouton Office, puis « Options Excel » et « Compléments ». Vous le trouverez ensuite dans le ruban « Données », dans le bloc « Analyse ».

Nos autres articles relatifs à l’utilitaire d’analyse

« L’utilitaire d’analyse est-il chargé en mémoire ? », le 25 février 2007.
« Date & Heure : les fonctions de l’utilitaire d’analyse », le 17 août 2007.
« Les fonctions financières de l'utilitaire d’analyse », les 17 et 21 juin 2008.

Une régression descendante...

Il y a grosso modo deux façons de procéder pour la régression multiple :
▪ la méthode ascendante, selon laquelle on ajoute à chaque itération la variable la plus significative, jusqu’au moment où la dernière variable ajoutée rend les résultats moins bons ;
▪ la méthode descendante, selon laquelle on ôte à chaque itération la variable la moins significative, jusqu’au moment où la dernière variable ôtée rend les résultats moins bons.

Quand on utilise Excel, à condition de mettre la bonne formule en I1:N1, il devient très pratique de mettre en œuvre la méthode descendante. Il suffit d’éliminer la colonne de données de la variable à ôter pour obtenir le nouveau tableau de résultat.

La formule que j’ai saisie en I1 à cet effet n’est franchement pas simple, mais elle fonctionne. Si un lecteur trouve une solution plus légère, je suis preneur. Voici cette formule :
=si(colonne()>2*nb(7:7)+2;"";si(colonne()=2*nb(7:7)+2;"Constante";index($1:$1;2*nb(7:7)-colonne()+3)))

Si l’on décide par exemple d’ôter la variable « Expér. », celle qui a la plus grosse probabilité en ligne 8, on obtient ainsi le tableau suivant :

Remarque – Comme on a ôté une colonne, la zone I1:N1 est naturellement devenue H1:M1…

La régression linéaire multiple

Passons maintenant à un problème de régression linéaire multiple !

On cherche à exprimer le chiffre d’affaires (CA) en fonction de cinq variables explicatives relatives aux commerciaux (critères en B1:F1). Nous disposons des données de 30 commerciaux. Voici une partie des données et le tableau de corrélation obtenu :

L’interprétation du tableau est la même que pour la régression simple : valeurs des coefficients dans la première ligne, écart-type estimé dans la seconde, et les mêmes informations qu'avant dans les trois lignes suivantes.

Formule matricielle entrée en I2:N6 : =droitereg(A2:A31;B2:F31;vrai;vrai)

Formule de I8 : =loi.student(abs(I2/I3);$J$5;2)

Les résultats de la ligne 8 indiquent que, très probablement, les deux facteurs « Age » et « Aptitude » seront considérés comme significatifs et gardés dans la formule finale.

Remarque 1 – Les cellules K6:N6 engendrent inévitablement des erreurs #N/A! que nous avons masquées grâce à un format conditionnel en I2:N6 affichant une police de couleur blanche quand la formule est =esterreur(I2) est évaluée en VRAI.

Remarque 2 – Notez bien que l’ordre des variables est inversé dans le tableau de résultat. C’est la première variable (Aptitude) qui apparaît en dernier !

La régression linéaire simple

Le modèle ci-dessous, appliqué à un nouvel exemple, illustre la mise en œuvre de la régression linéaire simple, c’est-à-dire de l’approximation d’un nuage de points (x;y) par une droite de régression y=ax+b.

La fonction utilisée – entrée sous forme matricielle ! – est la fonction DROITEREG(y_connus;x_connus;constante;statistiques).

Si l'argument « constante » est FAUX, « b » est égal à 0 et la valeur « a » est ajustée de sorte que y = ax.

Si l'argument « statistiques » est FAUX ou omis, la fonction DROITEREG renvoie uniquement la pente « a » de la droite de régression et la constante « b ».

Nous avons listé en H5:I9 les étiquettes identifiant les valeurs affichées en E5:F9.

Remarque – On peut récupérer directement tel ou tel élément du tableau de résultat par une simple formule. Ainsi, pour récupérer directement le coefficient de détermination, il suffit d’utiliser la formule : =index(droitereg(B2:B11;A2:A11; vrai;vrai);3;1).