Revue MODULAD Excel'Ense, statistique avec Excel

Macros et feuilles de calcul

Politique de publication des programmes
Les macros et les feuilles de calculs publiées dans la page Excel’Ense sont des documents « ouverts », avec du code lisible, dans l’esprit des logiciels ouverts. Il s’agit de permettre au lecteur de lire comment les choses se passent, comment les calculs sont organisés. Chacun peut apporter ses modifications et ses améliorations. Dans un esprit d’échange et de coopération, les programmes pourront s’enrichir des apports des utilisateurs.
La seule condition mise sur toute réutilisation des documents est l’obligation de citer leur origine (date et numéro de la Revue MODULAD avec son URL) et indication des noms des auteurs des documents réutilisés.
Ni la Revue ni les auteurs n’engagent leur responsabilité de quelque façon que ce soit concernant l’utilisation ou les conséquences de l’utilisation des documents ou des codes de programmation mis à la disposition des lecteurs.

Macros pour l’analyse exploratoire des données
La Statistique au service des Données: Quelques macros Excel pour faire de l’analyse exploratoire des données par Jacques VAILLÉ: L'analyse en composantes principales et l'analyse des correspondances; mais aussi les boîtes de distributions, les nuages de points avec étiquettes et les graphiques de Bertin.

Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi MODULAD n°43 (2011).

La fonction d’autocorrélogramme
Fonction Autocorrélogramme par Henry AUBERT. Voici une fonction pour calculer les n - 3 coefficients d'autocorrélation d'une série, afin, en en traçant une représentation graphique, de détecter une éventuelle composante cyclique.

Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi MODULAD n°43 (2011).

Les fonctions du filtre de Buys-Ballot
Fonction Buys-Ballot par Henry AUBERT. Quand on juge que la série temporelle des observations pourrait être ajustée par un processus qui se décomposerait en une tendance linéaire, une composante cyclique et un “bruit blanc fort”, c’est-à-dire un résidu totalement indépendant du temps, la méthode des moindres carrés, qui permet d’estimer le modèle de régression linéaire simple s’applique encore, à condition de considérer les coefficients saisonniers comme autant de variables explicatives. Cette méthode est associée au nom de Buys-Ballot. Les cinq fonctions Buys-Ballot présentées ici permettent de renvoyer :
1) ce qu’on convient d’appeler la “table de Buys-Ballot” des données où les cycles sont en lignes, les saisons en colonnes, à partir de laquelle les calculs des estimations des paramètres du modèle sont facilitées.
2) les estimations des paramètres.
3) la table des valeurs ajustées par le modèle estimé.
4) la table des valeurs de la série corrigées des estimations des variations saisonnières
5) la prévision d'une valeur future selon le modèle, sa variance, et un intervalle de confiance.

Téléchargez ici au format ici le document Excel accompagné de son mode d'emploi MODULAD n°43 (2011) .

Une introduction à la démarche bayésienne
Efficace et naturelle, la démarche bayésienne est cependant difficile à aborder sans une solide formation à la statistique mathématique. Emmanuel Grenier vous propose une introduction intuitive à cette démarche dans le cas de l’estimation d’une proportion. Exprimez graphiquement votre a priori sur la valeur de la proportion. Entrez les données observées et examinez comment l’information provenant de ces données se combine à votre a priori. A l’aide des barres de défilements faites varier les paramètres et observez l’évolution de l’estimation.

Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi MODULAD n°43, (2011).

La feuille Excel mise à votre disposition vous permettra de construire des plans factoriels complets ayant jusqu'à 5 facteurs. Elle vous permettra aussi de faire le calcul des effets des facteurs et de leurs interactions. Grâce aux outils statistiques et aux graphiques incorporés vous pourrez facilement déterminer les effets principaux et les interactions qui sont influents. Les explications sont données dans la fiche (en format pdf), rédigée par Jacques Vaillé et Jacques Goupy. Cette fiche est accompagnée d’un document Excel dans lequel vous pourrez exploiter vos propres données. MODULAD n°35 (décembre 2006).

Quand on réalise un nuage de points, il peut être intéressant de faire figurer les noms correspondants sur le graphique. Excel propose bien des étiquettes, mais il ne propose que le nom de la série, la valeur de X ou de Y.
La feuille Nuageétiqueté.xls propose deux macros :
- la première réalise un nuage de points avec les étiquettes prises dans la première colonne du tableau.
- La deuxième permet de rajouter les étiquettes à une série de points déjà représentés. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi. MODULAD n°35 (décembre 2006).

Excel permet de trier (permuter) les lignes d’un tableau à partir des valeurs d’une colonne. Il ne permet pas de faire directement la même chose sur les colonnes. La fonction matricielle PERMUTE a été écrite par Jacques Vaillé, pour obtenir une image d’un tableau réordonné à partir de deux plages donnant la position des lignes et des colonnes dans le tableau résultant. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi. MODULAD n°35 (décembre 2006).

La matrice identité est une matrice carrée dont tous les termes sont nuls sauf ceux de la diagonale qui sont égaux à un. Elle intervient dans les calculs de projections ou pour chercher les valeurs propres d’une matrice. Cette fonction matricielle écrite par Jacques Vaillé, permet de la faire intervenir dans un calcul. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi. MODULAD n°35 (décembre 2006).

Voici la fonction de base des analyses multidimensionnelles : DIAGSYM. A partir d’une matrice symétrique, elle renvoie la matrice des vecteurs propres et si on le désire, les valeurs propres de cette matrice. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi fournis par Jacques Vaillé. MODULAD n°35 (décembre 2006).

Quoi de plus indispensable que l'écart-type d'une prévision, une fois qu'on a calculé la droite des moindres carrés ? Et pourtant cette formule, compliquée, à retenir et à mettre en oeuvre, ne fait pas l'objet d'une fonction standard d'Excel ! Voici cette fonction qui fournit en option cet écart-type, ou celui de la valeur sur la droite de régression. Henry P. Aubert en a profité pour ajouter la fonction qui renvoie l'estimation du coefficient de corrélation linéaire dans la population, à partir de celui observé dans l'échantillon. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi. MODULAD n°35 (décembre 2006).

Les tests de Wilcoxon sur les rangs, et sur les échantillons appariés sont bien utiles quand on doit juger de l'indépendance d'une variable quantitative, vis-à-vis d'une variable qualitative, sur un échantillon de taille insuffisant pour se trouver dans le cadre des tests statistiques classiques.
Henry P. Aubert nous fournit quatre fonctions qui renvoient les probabilités nécessaires à la réalisation de ces tests. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi. MODULAD n°35 (décembre 2006).

Lorsqu’une variable Gaussienne est censurée, on souhaite alors calculer l’espérance mathématique des valeurs non observables. C'est le cas, en particulier, lorsqu'on veut fixer le niveau de réapprovisionnement périodique d'un stock. On souhaite alors calculer la demande moyenne non satisfaite, qu'on appelle aussi rupture de stock. La formule est compliquée, les calculs longs et fastidieux. Cette fonction renvoie cette valeur. Téléchargez au format zip le document Excel accompagné de son mode d'emploi créés par Henry P. Aubert. MODULAD n°35 (décembre 2006).

Il est fastidieux de taper des libellés de classes qui soient présentables à l'édition, puis de les mettre à jour quand les bornes des classes ont été modifiées. Voici une fonction qui vous en affiche deux modèles, au choix. Elle s'utilise en accompagnement de la fonction FREQUENCE( ) d'Excel. Téléchargez au format zip le document Excel accompagné de son mode d'emploi rédigés par Henry P. Aubert. MODULAD n°35 (décembre 2006).

Comment déplacer directement des macros ou des fonctions d'un classeur Excel dans un autre

Cette procédure écrite par Henry P. Aubert, est particulièrement rapide pour s'approprier une macro ou une fonction transmise dans un classeur Excel en la déplaçant.

Le résultat renvoyé par la fonction CENTILE( ) d'Excel peut paraître surprenant, surtout si l'on a la curiosité de l'utiliser avec une série de nombres entiers, et encore plus avec des taux qui ne correspondent pas à une fraction entière de l'effectif de la série. La fonction QUANTILE( ) que Henry P. Aubert nous propose, corrige cette erreur systématique, qu'on a aussi avec les fonctions MEDIANE( ) et QUARTILE( ). Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi. MODULAD n°35 (décembre 2006)

Inverse de la loi Poisson : fonction personnalisée pour Excel

Cette fonction renvoie, pour un niveau de confiance donné, la borne supérieure de l'intervalle de confiance unilatéral à gauche d’une variable de Poisson de moyenne connue. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi réalisés par Henry P. Aubert. MODULAD n°35 (décembre 2006).

Créer et installer une macro complémentaire

La Documentation-Macro fournie par Olivier MARTIN donne en 3 pages la marche à suivre pour installer et faire fonctionner les macros complémentaires que vous téléchargez. MODULAD n°32 (janvier 2005).

Réaliser un histogramme

On sait qu'EXCEL malgré les termes utilisés ne propose pas de véritables histogrammes. Ce document comble cette lacune : à partir de vos données brutes ou de comptages, vous choisissez les bornes des classes et vous obtenez un véritable histogramme. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi. MODULAD n°34 (juillet 2006).

Calculer l'intervalle de confiance d'une probabilité

A l’aide d’une simple feuille, vous calculez l’intervalle de confiance unilatéral ou bilatéral d’une probabilité, pour un niveau de confiance donné. Téléchargez ici au format zip le document Excel accompagné de son mode d'emploi, préparés par Emmanuel GRENIER. MODULAD n°34 (juillet 2006).

Estimation de tests non paramétriques par permutation
Les tests de permutation permettent de tester une hypothèse nulle sans faire de supposition sur la distribution de la série étudiée (comme la supposition classique d’une distribution normale). Par contre, les calculs pour obtenir les probabilités sont soit spécifiques au problème étudié, soit trop longs ou trop complexes. L’utilisation de simulations avec le tableur Excel permet alors d’estimer ces probabilités. Découvrez les possibilités ainsi offertes en téléchargeant les documents préparés par Jacques VAILLE : la notice d’utilisation et les 3 feuilles de calcul qui n’attendent que vos données.

Echantillonnage au hasard : macro pour le tirage d'un échantillon stratifié
Cette macro créée par Henry AUBERT est disponible dans le fichier Tirage d'un échantillon stratifié. Elle extrait, au hasard et sans remise, un échantillon stratifié dans la population contenue dans une feuille d’un classeur Excel.

Faire des simulations (version 2)

Incontournable pour gérer des modèles complexes, la simulation probabiliste s'impose également comme outil pédagogique parce qu'elle permet d'aborder les probabilités et la statistique de manière expérimentale (voir le manuel écrit par le groupe "Le Cercle d'Excel'Ense"). Avec Excel, il est possible de simuler un nombre restreint de lois de probabilité via l'utilitaire "Génération de nombres aléatoires". La Documentation-Simulations écrite par Emmanuel GRENIER présente plusieurs méthodes: une méthode générale, la méthode des fractiles, puis des méthodes plus spécifiques qui constituent autant d'expériences à usage pédagogique. Une application est proposée dans le domaine de l'analyse du risque. Téléchargez ces Feuilles-Simulations au format Zip. MODULAD n°32 (janvier 2005) et MODULAD n°34 (juillet 2006).

Créer des macros en Visual Basic pour Excel

Henry P. AUBERT nous apprend dans une note détaillée comment créer des macros en Visual Basic pour Excel. MODULAD n°33 (juillet 2005).

Fonctions pour le Khi-2

Cinq fonctions écrites en basic pour les calculs associés aux tests du Khi-2 sont proposées par Henry P. AUBERT dans le fichier (au format Zip) Macros_Khi2. Lire la notice dans le fichier Fonctions pour les tests du Khi-2. MODULAD n°33 (juillet 2005)

Puissance d'un essai
Lorsque l’on met en place une expérimentation pour tester des traitements, il est important de savoir combien d’unités expérimentales mettre en œuvre pour espérer déceler, au niveau de la variable d’intérêt, une certaine différence entre les traitements. On peut aussi vouloir estimer la puissance de l’essai programmé. La macro Puissance d'un essai, développée par Yves-Marie CHATELIN en collaboration avec Arvalis, permet d’obtenir ces informations si l’on a une idée assez précise de la variabilité de la variable d’intérêt (lire la Notice). Dans le cas où il n’y a que deux traitements, les calculs sont basés sur le t de Student; on prend alors en compte les erreurs de première et de seconde espèce. S’il y a plus de deux traitements, on utilise un F de Fisher décentré qui fait intervenir une estimation de la moyenne de la variable d’intérêt pour chacun des traitements. Si cette estimation n’est pas possible, il faut avoir une idée des valeurs extrêmes attendues, les traitements non extrêmes se voyant attribuer la valeur moyenne des valeurs extrêmes. MODULAD n°33 (juillet 2005).

Nouvelle version (septembre 2006). La feuille de calcul ACP parue dans le Numéro 32 permettait de réaliser une petite ACP. Outre quelques problèmes si le tableau était mal placé dans la feuille, cette macro avait l’inconvénient d’utiliser le Solveur. Si les valeurs numériques étaient modifiées, il n’était pas possible de voir ce que devenaient les résultats. La nouvelle feuille macro appelée Anafact.xls ne fait plus les erreurs signalées et l’introduction d’une macro complémentaire (Diagonalisation.xla) permet d’obtenir des résultats qui se mettent à jour avec les données du tableau. Enfin on peut maintenant effectuer les deux analyses de base : l’Analyse en Composantes Principales ou ACP (normée) et pour les tableaux de contingence, l’Analyse Factorielle des Correspondances ou AFC. L’utilisation de techniques de Bertin (réorganisation des lignes et des colonnes du tableau suivant l’un des axes factoriels obtenus) permet de mieux comprendre les propriétés du tableau mises en évidence par l’analyse. La Documentation-ACP de Jacques VAILLÉ montre en détail comment faire une ACP avec individus et variables supplémentaires. Télécharger la feuille Excel et les fichiers d'exemples dans Feuilles-ACP (au format Zip). MODULAD n°32 (janvier 2005).

Faire du Bootstrap et du Jackknife

La Macro-Rééchantillonnage (format Zip) fournie par Olivier MARTIN permet de mettre en oeuvre les techniques Jackknife et Bootstrap en procédant automatiquement au rééchantillonnage et aux calculs des estimations. Une option permet également de générer, dans le cas du Bootstrap, les histogrammes de distribution empirique des estimations obtenues sur les échantillons artificiels. Lire la Documentation-Rééchantillonnage associée. MODULAD n°32 (janvier 2005)

Dessiner un graphique ternaire
La feuille de calcul Diagramme-ternaire (format Zip) proposée par Jacques VAILLÉ permet de représenter graphiquement des mélanges de 3 constituants pour les comparer visuellement (ici dans 4 catégories). La feuille est facilement personnalisable en utilisant la documentation jointe. Pour connaître le détail des étapes de la réalisation, on pourra se reporter à la fiche "Comment faire un diagramme ternaire". MODULAD n°32 (janvier 2005).