Comment Créer une analyse de distribution dans Excel - Guide Pratique

Apprenez à créer une analyse de distribution complète dans Excel en organisant les données en distributions de fréquences, en calculant les mesures statistiques et en visualisant les tendances avec des histogrammes. Cette technique avancée identifie les anomalies et la forme de la distribution—essentielle pour l'analyse financière et le contrôle qualité.

Pourquoi c'est important

L'analyse de distribution révèle les tendances et la variabilité des données essentielles pour la prise de décision en finance et recherche. Elle détecte les anomalies et évalue la normalité pour les tests statistiques.

Prérequis

•Maîtrise des formules Excel (SOMME, MOYENNE, ECARTYPE)
•Compréhension des statistiques de base
•Expérience de création de graphiques
•Connaissance de l'organisation des données

Instructions étape par étape

Préparer et Trier les Données

Importez votre ensemble de données en commençant à la cellule A1 avec en-têtes à la ligne 1. Sélectionnez toutes les données (Ctrl+A), puis allez à Données > Trier pour organiser les valeurs en ordre croissant.

Déterminer les Intervalles de Classes (Bins)

Calculez l'étendue (MAX-MIN) et le nombre de bins avec la formule de Sturges : k = 1 + 3,322*LOG10(effectif). Créez des limites dans la colonne D; par exemple, pour une étendue 0-100 avec 8 bins, utilisez 0-12,5, 12,5-25, etc.

Calculer la Distribution de Fréquence

Dans la colonne E, utilisez la fonction FREQUENCE : =FREQUENCE(plage_données, plage_bins) entrée comme formule matricielle (Ctrl+Maj+Entrée). Cela compte les valeurs dans chaque intervalle.

Calculer les Statistiques Descriptives

Dans une nouvelle section, calculez : moyenne (=MOYENNE), médiane (=MÉDIANE), mode (=MODE.SIMPLE), écart-type (=ECARTYPE.S), asymétrie (=ASYMETRIE) et aplatissement (=KURTOSIS).

Créer des Histogrammes et Graphiques d'Analyse

Sélectionnez bins et fréquences, puis Insertion > Graphiques > Histogramme pour afficher la forme de distribution. Ajoutez un graphique cumulatif avec la formule SOMME cumulative pour visualiser les pourcentages.

Méthodes alternatives

Utiliser l'Utilitaire d'Analyse ToolPak

Activez l'Utilitaire d'Analyse (Fichier > Options > Compléments > Gérer les compléments > Accéder > Utilitaire d'Analyse), puis Données > Utilitaire d'Analyse > Histogramme pour automatiser la création de bins.

Utiliser COUNTIFS pour des Bins Personnalisés

Au lieu de FREQUENCE, utilisez COUNTIFS : =COUNTIFS(plage,">="&limite_inf,plage,"<"&limite_sup) pour plus de flexibilité avec des intervalles personnalisés.

Tableau Croisé Dynamique pour l'Analyse

Créez un Tableau Croisé Dynamique (Insertion > Tableau Croisé Dynamique) et groupez les données pour générer automatiquement la distribution de fréquence.

Astuces et conseils

✓Utilisez 5-15 bins selon la taille de l'échantillon; la formule de Sturges est un bon point de départ.
✓Incluez les colonnes de fréquence relative (pourcentage) pour une meilleure interprétation.
✓Appliquez une mise en forme conditionnelle pour mettre en évidence les zones de fréquence élevée et faible.
✓Créez une section de référence séparée avec toutes les formules pour la transparence.

Astuces avancées

★Normalisez vos données (Z-score) avant l'analyse pour comparer plusieurs ensembles de données.
★Utilisez des plages nommées pour les formules FREQUENCE pour plus de lisibilité.
★Combinez l'analyse avec la détection des anomalies en utilisant la méthode IQR.
★Créez des bins dynamiques avec la fonction SEQUENCE (Excel 365) pour ajustement automatique.
★Testez la normalité avec NORM.S.DIST pour déterminer si les tests statistiques paramétriques sont appropriés.

Résolution de problèmes

FREQUENCE retourne 0 ou erreurs #N/A

Vérifiez que les données et bins sont numériques et triés correctement. Assurez-vous que FREQUENCE est entrée comme formule matricielle. Vérifiez que le tableau bins contient les valeurs limites supérieures en ordre croissant.

L'histogramme affiche des écarts ou des barres mal alignées

Clic droit sur les barres > Format de la série > Largeur de l'intervalle à 0%. Vérifiez que les intervalles sont contigus. Assurez-vous que les étiquettes d'axe affichent correctement les limites.

Les statistiques de distribution semblent incorrectes

Recherchez les valeurs non numériques, espaces ou texte. Vérifiez que les formules font référence à la plage correcte. Recalculez la feuille (F9).

Les valeurs aberrantes déforment la forme de distribution

Identifiez les anomalies avec la méthode IQR. Créez deux analyses : une avec toutes les données et une en excluant les valeurs extrêmes pour comparaison.

Formules Excel associées

FREQUENCY COUNTIFS AVERAGE STDEV.S MEDIAN MODE.SNGL SKEW KURT PERCENTILE QUARTILE NORM.S.DIST

Questions fréquentes

Quelle est la différence entre la fréquence et la fréquence relative?

La fréquence est le nombre de valeurs dans chaque bin, tandis que la fréquence relative est le pourcentage (fréquence/total*100). La fréquence relative permet de comparer des ensembles de données de tailles différentes.

Comment choisir le nombre optimal de bins?

Utilisez la formule de Sturges comme point de départ, mais ajustez visuellement. Trop peu de bins masquent les tendances; trop de bins créent du bruit. Typiquement 5-15 bins conviennent.

Puis-je créer une analyse de distribution pour les données catégoriques?

Oui, mais utilisez des méthodes différentes. Pour les données catégoriques, utilisez COUNTIF pour compter les occurrences, puis créez des graphiques en barres. L'analyse de distribution est principalement pour les données numériques continues.

Que me dit l'asymétrie sur ma distribution?

L'asymétrie mesure l'asymétrie : asymétrie positive signifie queue à droite, asymétrie négative à gauche, zéro indique la symétrie. Cela révèle si les données se regroupent vers des valeurs basses ou hautes.

Comment interpréter l'aplatissement dans l'analyse de distribution?

L'aplatissement mesure le poids des queues : aplatissement positif (>3) signifie queues lourdes avec anomalies, négatif (<3) signifie queues légères. La distribution normale a un aplatissement de 3.