Calcul du mode dans Excel

La moyenne vous donne une mesure de la tendance centrale en prenant en compte toutes les valeurs réelles d’un groupe. La médiane mesure différemment la tendance centrale, en vous donnant le point médian d’un groupe de valeurs classé. Le mode prend encore un autre bord : il vous indique laquelle de plusieurs valeurs se produit le plus fréquemment.Alors que la moyenne et la médiane nécessitent certains calculs, une valeur de mode peut être trouvée simplement en comptant le nombre de fois que chaque valeur se produit.

Par exemple, le mode de l’ensemble de valeurs {1, 2, 2, 3, 4, 6} est 2. Dans Microsoft Excel, vous pouvez calculer un mode en utilisant la fonction du même nom, la fonction MODE. Pour notre exemple d’ensemble de données, la formule est la suivante :

=MODE(C2:C8)

Dans les situations où il y a deux modes ou plus dans votre ensemble de données, la fonction Excel MODE renverra le mode le plus bas.

1 Déterminer le mode des données nominales à partir des graphiques croisés dynamiques

Mais la fonction MODE ()d’Excel ne fonctionne pas avec les données nominales. Si vous lui présentez, en tant qu’argument, une plage qui contient exclusivement des données textuelles telles que des noms, MODE ()renvoie la valeur d’erreur # N/A. Si une ou plusieurs valeurs de texte sont incluses dans une liste de valeurs numériques, MODE ( )ignore simplement les valeurs de texte.
La figure suivante montre quelques solutions au problème avec MODE ( ). 

La distribution de fréquence de la figure précédente est plus informative qu’un tableau croisé dynamique représenté, où une seule valeur dépasse les autres car elle se produit deux fois au lieu d’une fois. Vous pouvez voir que Ford, la valeur modale, conduit Toyota par une mince marge et GM par un peu plus.

Remarque
Certaines des étapes suivantes sont similaires, voire identiques, aux étapes prises pour créer un tableau croisé dynamique. Elles sont répétées ici, en partie par commodité et en partie pour que vous puissiez vous habituer à voir comment les tableaux croisés dynamiques et les tableaux croisés sont construit. Plus important peut-être, les valeurs sur l’axe horizontal dans le présent exemple sont mesurées sur une échelle nominale. Puisque vous recherchez simplement le mode, aucun ordre n’est impliqué, et la forme de la distribution est arbitraire.
Pour créer un graphique croisé dynamique qui ressemble à celui de la figure précédente, procédez comme suit :
1. Disposez vos données brutes dans un format de liste Excel : le nom du champ dans la première colonne (comme A1) et les valeurs dans les cellules sous le nom du champ (comme A2: A21). Il est préférable que toutes les cellules immédiatement adjacentes à la liste soient vides.
2. Sélectionnez une cellule dans votre liste.
3. Cliquez sur l’onglet Insertion du ruban, puis cliquez sur le bouton Graphique croisé dynamique dans le groupe Graphiques. La boîte de dialogue illustrée à la figure suivante apparaît.

4. Si vous avez pris l’étape 2 et sélectionné une cellule dans votre liste avant de cliquer sur le bouton Graphique croisé dynamique, Excel a automatiquement fourni l’adresse de la liste dans la zone d’édition Tableau / Plage. Sinon, identifiez la plage qui contient vos données brutes en les faisant glisser avec le pointeur de votre souris, en tapant son adresse de plage ou en tapant son nom s’il s’agit d’un tableau ou d’une plage nommée. L’emplacement des données doit maintenant apparaître dans la zone d’édition Tableau/Plage.

5. Si vous souhaitez que le tableau croisé dynamique et le graphique croisé dynamique apparaissent dans la feuille de calcul active, cliquez sur le bouton Feuille de calcul existante et cliquez dans la zone d’édition Emplacement. Cliquez ensuite dans une cellule de feuille de calcul contenant plusieurs colonnes vides à sa droite et plusieurs lignes vides en dessous. Cela empêche Excel de vous demander si vous souhaitez que le tableau croisé dynamique remplace les données existantes. Cliquez sur OK pour obtenir la disposition illustrée à la figure suivante.

6. Dans le volet Champs de graphique croisé dynamique, faites glisser le ou les champs qui vous intéressent vers le bas de la liste et dans la zone appropriée en bas. Dans cet exemple, vous devez faire glisser vers le bas dans la zone Axe (catégories) et le faire glisser dans la zone Valeurs.


Le graphique croisé dynamique est basé sur les deux mises à jour dès que vous avez déposé un champ dans une zone dans le volet Champs de tableau croisé dynamique.

2. Quelques commentaires sur cette analyse
Le mode est une statistique assez utile lorsqu’il est appliqué à des catégories : partis politiques, marques grand public, jours de la semaine, états d’une région, etc. Excel devrait vraiment avoir une fonction de feuille de calcul intégrée qui renvoie le mode pour les valeurs de texte. Mais ce n’est pas le cas, et la section suivante vous montre comment écrire votre propre formule de feuille de calcul pour le mode, celle qui fonctionnera pour les deux valeurs numériques et texte.
Lorsque vous n’avez que quelques catégories distinctes, envisagez de créer un tableau croisé dynamique pour montrer le nombre d’instances de chaque catégorie. Un tableau croisé dynamique qui montre le nombre d’instances de chaque catégorie est un moyen attrayant de présenter vos données à un public. (Il n’y a pas de type de tableau qui communique bien quand il y a plusieurs catégories à prendre en compte: le fouillis visuel obscurcit le message Dans ce genre de situation, pensez à combiner des catégories ou en omettre certaines.)
Les graphiques Excel standard ne montrent pas le nombre d’instances par catégorie sans un travail préliminaire. Vous devriez obtenir un compte de chaque catégorie avant de créer le graphique, et c’est le but du tableau croisé dynamique qui sous-tend le tableau croisé dynamique. Le tableau croisé dynamique, basé sur le tableau croisé dynamique, est simplement un moyen plus rapide de compléter l’analyse que de créer votre propre table pour compter l’appartenance à une catégorie, puis de baser un graphique Excel standard sur cette table.
Le mode est la seule mesure sensible de la tendance centrale lorsque vous travaillez avec des données nominales telles que les noms de catégories. La médiane exige que vous classiez les choses d’une certaine façon: du plus court au plus grand, du moins cher au plus dispendieux ou du plus lent au plus rapide. En termes de types d’échelle introduits dans le chapitre 1, vous avez besoin d’au moins une échelle ordinale pour obtenir une médiane, et de nombreuses catégories sont nominales, et non ordinales. Les variables représentées par des valeurs telles que Ford, GM et Toyota n’ont ni moyenne ni médiane.

3. Obtenir le mode de catégories avec une formule
La fonction MODE ()d’Excel ne fonctionne pas lorsque vous lui fournissez des valeurs de texte comme arguments. Voici une méthode pour obtenir le mode en utilisant une formule de feuille de calcul. Il vous indique quelle valeur de texte apparaît le plus souvent dans votre ensemble de données. Vous verrez également comment entrer une formule qui vous indique le nombre d’instances du mode existant dans vos données.
Si vous ne voulez pas utiliser un graphique pivot pour obtenir le mode d’un groupe de valeurs de texte, vous pouvez obtenir leur mode avec la formule
= INDEX (A2: A21 ; MODE (EQUIV (A2: A21 ; A2: A21 ; 0)))
en supposant que les valeurs de texte sont en A2: A21. (La plage pourrait occuper une seule colonne, comme dans A2: A21, ou une seule ligne, comme dans A2: Z2, elle ne fonctionnera pas correctement avec une plage multi-colonnes, comme A2: Z21.)
Si vous êtes un peu novice avec Excel, cette formule n’aura aucun sens pour vous. Je l’ai structuré, j’utilise fréquemment Excel depuis longtemps, et je dois encore regarder la formule et y réfléchir avant de voir pourquoi elle renvoie le mode. Donc, si la formule semble déroutante, ne vous inquiétez pas à ce sujet. Il deviendra clair dans le temps, et en attendant vous pouvez l’utiliser pour obtenir la valeur modale pour n’importe quel ensemble de valeurs de texte dans une feuille de calcul. Remplacez simplement l’adresse de plage A2: A21 par l’adresse de la plage qui contient vos valeurs de texte.
En bref, les composants de la formule fonctionnent comme suit:
• La fonction EQUIV ()renvoie la position dans le tableau de valeurs où chaque valeur individuelle apparaît en premier. Le troisième argument de la fonction EQUIV (), 0, indique à Excel que, dans chaque cas, une correspondance exacte est requise et que le tableau n’est pas nécessairement trié. Ainsi, pour chaque instance de Ford dans le tableau de valeurs dans A2: A21, EQUIV ()renvoie 1; pour chaque instance de Toyota, il renvoie 2; pour chaque instance de GM, il renvoie 4.
• Les résultats de la fonction EQUIV () sont utilisés comme argument de MODE (). Dans cet exemple, il y a 20 valeurs pour MODE ()à évaluer: certaines égalent 1, d’autres égalent 2, et d’autres égales à 4. MODE () renvoie le plus fréquent de ces nombres.
• Le résultat de MODE ()est utilisé comme deuxième argument de INDEX (). Son premier argument est le tableau à examiner. Le deuxième argument indique à quelle distance dans le tableau à regarder. Ici, il regarde la première valeur dans le tableau, qui est Ford. Si, disons, GM avait été la valeur de texte la plus fréquente, MODE ()aurait retourné 4 et INDEX ()aurait utilisé cette valeur pour trouver GM dans le tableau.
4. Utilisation d’une formule de tableau pour compter les valeurs
Avec la valeur modale (Ford, dans cet exemple) en main, nous voulons toujours savoir combien il y a d’instances de ce mode. Cette section décrit comment créer la formule de tableau qui compte les instances.
La figure suivante montre également, dans la cellule C2, le compte du nombre d’enregistrements qui appartiennent à la valeur modale.

Cette formule fournit ce compte:
= SOMME (SI (A2: A21 = C1 ; 1 ; 0))
La formule est une formule matricielle et doit être saisie à l’aide de la séquence de touches spéciale Ctrl + Maj + Entrée. Vous pouvez dire qu’une formule a été saisie en tant que formule matricielle si vous voyez des accolades autour de celle-ci dans la boîte de formule.

Voici comment fonctionne la formule: Comme le montre la figure, la cellule C1 contient la valeur Ford. Ainsi, le fragment suivant de la formule matricielle teste si les valeurs dans la plage A2: A21 sont égales à la valeur Ford:
A2: A21 = C1
Comme il y a 20 cellules dans la plage A2: A21, le fragment renvoie un tableau de valeurs VRAI et FAUX: VRAI lorsqu’une cellule contient Ford et FAUX sinon. Le tableau ressemble à ceci:
{VRAI; FAUX; VRAI; FAUX; FAUX; FAUX; VRAI; FAUX; VRAI; VRAI; FAUX; FAUX; FAUX; VRAI; VRAI; FAUX; FAUX; VRAI; FAUX; FAUX} Spécifiquement, la cellule A2 contient Ford, et ainsi elle passe le test: La première valeur dans le tableau est donc VRAI. La cellule A3 ne contient pas Ford, et donc il échoue le test: La deuxième valeur dans le array est donc FAUX – et ainsi de suite pour les 20 cellules.
Remarque
Le tableau des valeurs VRAI et FAUX est un résultat intermédiaire de cette formule de tableau (et de beaucoup d’autres, bien sûr). En tant que tel, il n’est pas systématiquement visible par l’utilisateur, qui n’a normalement besoin que de voir le résultat final de la formule. Si vous voulez voir des résultats intermédiaires comme celui-ci, utilisez l’outil Audit de formules. Voir «Regarder à l’intérieur d’une formule», plus loin dans ce chapitre, pour plus d’informations.
Maintenant, sortez de ce fragment, qui, comme nous venons de le voir, se résout à un tableau de valeurs VRAIES et FAUX. Le tableau est utilisé comme premier argument de la fonction SI (). La fonction SI () d’Excel prend trois arguments:
• Le premier argument est une valeur qui peut être VRAI ou FAUX. Dans cet exemple, c’est chaque valeur du tableau qui vient d’être affichée, retournée par le fragment A2: A21 = C1.
• Le deuxième argument est la valeur que vous voulez que la fonction SI () renvoie lorsque le premier argument est VRAI. Dans l’exemple, ceci est 1.
• Le troisième argument est la valeur que vous voulez que la fonction SI () renvoie lorsque le premier argument est FAUX. Dans l’exemple, c’est 0.
La fonction SI () examine chacune des valeurs dans le tableau pour voir si c’est une valeur VRAI ou une valeur FAUX. Quand une valeur dans le tableau est VRAI, la fonction SI () renvoie, dans cet exemple, un 1 et un 0 sinon. Par conséquent, le fragment SI (A2: A21 = C1, 1,0) retourne un tableau de 1 et 0 qui correspond au premier tableau de valeurs VRAI et FAUX. Ce tableau ressemble à ceci:
{1; 0; 1; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 1; 1; 0; 0; 1; 0; 0}
A 1 correspond à une cellule dans A2: A21 qui contient la valeur Ford, et un 0 correspond à une cellule dans la même gamme qui ne contient pas Ford. Enfin, le tableau de 1 et 0 est présenté à la fonction SUM (), qui totalise les valeurs dans le tableau. Ici, ce total est de 8.

Récapitulation de la formule de tableau
Pour examiner comment la formule matricielle compte les valeurs pour la catégorie modale de Ford, considérez ce qui suit:
• L’objectif de la formule est de compter le nombre d’instances de la catégorie modale, Ford, dont le nom figure dans la cellule C1.
• Le fragment le plus interne de la formule, A2: A21 = C1, renvoie un tableau de 20 valeurs VRAI ou FAUX, selon que chacune des 20 cellules de A2: A21 contient la même valeur que celle trouvée dans la cellule C1.
• La fonction SI () examine le tableau VRAI / FAUX et renvoie un autre tableau contenant 1s où le tableau VRAI / FAUX contient VRAI, et 0 où le tableau VRAI / FAUX contient FAUX.
• La fonction SOMME ()totalise les valeurs du tableau de 1 et de 0. Le résultat est le nombre de cellules dans A2: A21 qui contient la valeur dans la cellule C1, qui est la valeur modale pour A2: A21.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x