Comment identifier la cellule qui se répète le plus de fois sur Excel ? (Fonction MODE)
Dans ce tutoriel, je vais vous montrer comment trouver la valeur qui se répète le plus de fois dans une liste de cellules Excel.
Nous prendrons l'exemple d'une entreprise qui enregistre les ventes réalisées auprès de ses clients dans une base de données.
Nous découvrirons ainsi la fonction MODE d'Excel qui permet d’identifier et d’extraire la valeur la plus courante parmi un ensemble de données.
Nous découvrirons également une méthode pour étendre son champ d’applications aux valeurs textuelles.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Introduction
Pour illustrer ce tutoriel, nous allons prendre l'exemple d'une entreprise qui enregistre les ventes réalisées auprès de ces clients dans une base :
Dans ce tableau, nous avons une liste de clients et de leurs achats avec le montant du chiffre d'affaires TTC.
Cette liste contient des doublons de noms de clients, car plusieurs clients ont effectué des achats auprès de l'entreprise durant la même semaine.
L’objectif sera d’extraire le nom du client qui a réalisé le plus grand chiffre d'affaires.
2. La fonction MODE() d’Excel
Avant de pouvoir extraire le nom du client, nous allons devoir découvrir la fonction MODE d’Excel que nous utiliserons par la suite.
La fonction MODE permet en effet de trouver la valeur la plus courante dans une plage de données passée en argument.
Elle peut être utilisée avec des nombres ou avec des textes.
La fonction MODE est très simple à utiliser, puisqu’il suffit de lui passer en argument la liste que nous souhaitons analyser, de la manière suivante :
=MODE(plage)
Où plage est la plage de cellules sur lesquelles la fonction va être appliquée.
Si plusieurs valeurs sont identiques et sont les plus courantes, la fonction MODE renvoie la première valeur rencontrée dans la plage.
De plus, la plage utilisée doit contenir au moins deux valeurs.
Ces valeurs peuvent être numériques ou textuelles, mais si la plage contient des valeurs non numériques, elles sont ignorées.
Dans notre, exemple, la fonction MODE nous permettra par exemple d’obtenir le jour durant lequel l’entreprise a réalisé le plus de ventes, en passant en argument les cellules de la colonne « Date » du tableau :
=MODE(TableauVentes[Date])
En revanche, si nous l’utilisons sur la colonne « Client », alors celle-ci nous retournera une erreur #N/A :
3. Trouvez la valeur la plus courante dans une liste avec une formule Excel
La fonction MODE que nous venons de découvrir permet donc le nombre le plus courant contenu dans une liste, mais cette fonction ne fonctionne pas avec les chaînes de texte.
Pour extraire la valeur la plus courante parmi des valeurs textuelles, nous intercalerons cette fonction au sein des fonctions INDEX et EQUIV comme ceci :
=INDEX(TableauVentes[Client];MODE(EQUIV(TableauVentes[Client];TableauVentes[Client];0)))
Voici la décomposition de cette formule.
Tout d’abord, nous retrouvons l’utilisation de la fonction EQUIV :
EQUIV(TableauVentes[Client];TableauVentes[Client];0)
Cette fonction EQUIV permet d’obtenir la position d'une valeur au sein d’une plage de cellules.
Elle prend trois arguments : la valeur recherchée, la plage dans laquelle effectuer la recherche et le type de recherche.
Dans notre exemple, la formule permet de rechercher la position de chaque valeur dans la colonne "Client".
L'argument « 0 » en dernière position indique à la fonction EQUIV de rechercher une correspondance exacte, c'est-à-dire une valeur identique à celle recherchée.
La fonction EQUIV renvoie une matrice verticale d'une seule colonne contenant les positions de chaque occurrence de la valeur recherchée dans la plage de données.
Cette matrice est ensuite utilisée comme argument de la fonction MODE, pour trouver la valeur la plus fréquente dans la colonne "Client" de la plage de données.
MODE(EQUIV(TableauVentes[Client];TableauVentes[Client];0))
Il ne reste plus qu’à l’inclure dans la fonction INDEX pour extraire la valeur qui se trouve à la position correspondante.
4. Alternatives à la fonction MODE (MODE.SIMPLE et MODE.MULTIPE)
Lorsque nous appelons la fonction MODE depuis la barre des formules, nous pouvons constater qu'Excel nous affiche un panneau d'avertissement sur celle-ci.
Il faut en effet savoir que cette fonction MODE a été dépréciée par Microsoft depuis Excel 2010.
Cela signifie qu'elle n'est plus recommandée pour une utilisation régulière.
Pour la remplacer, Microsoft a introduit deux alternatives : MODE.SIMPLE et MODE.MULTIPLE.
- La fonction MODE.SIMPLE remplace la fonction MODE. Elle lui est identique, mais elle nécessite une durée de traitement plus courte, car elle est davantage optimisée.
- La fonction MODE.MULTIPLE permet elle aussi de trouver les valeurs les plus courantes dans une plage de données. Contrairement aux deux autres fonctions, elle ne retournera pas uniquement la première des valeurs les plus courantes, mais toute une matrice avec ces valeurs, s’il y en a plusieurs.
Pour illustrer l’utilisation de la fonction MODE.MULTIPLE, nous allons modifier la formule saisie dans la cellule F7 :
=MODE.MULTIPLE(TableauVentes[Date])
Ici, le résultat est identique.
Par contre, si nous modifions la date de la première ligne pour la passer au 09/04/2023, la fonction que nous venons de saisir va nous retourner une erreur #PROPAGATION (sur les versions récentes d’Excel) :
La raison est très simple : ici la formule ne renverra pas un simple résultat dans une cellule unique, mais une matrice qui doit donc être saisie dans plusieurs cellules.
Or, la cellule F8 qui se trouve dans la zone de retour identifiée par les pointillés bleus contient déjà un résultat.
Pour obtenir la valeur souhaitée, nous pouvons soit :
- Soit, encapsuler la fonction MODE.MULTIPLE dans une fonction INDEX pour n’en conserver que le premier résultat : « =INDEX(MODE.MULTIPLE(TableauVentes[Date]);1) » (mais dans ce cas-là, autant utiliser directement la fonction MODE.SIMPLE) :
- Soit l’encapsuler dans la fonction TRANSPOSE, pour que la matrice verticale soit convertie en matrice horizontale : « =TRANSPOSE(MODE.MULTIPLE(TableauVentes[Date])) »
- Soit saisir la formule sur une plage de cellule vide :
Dans tous les cas, il est recommandé d'utiliser ces alternatives à la fonction MODE, car elles sont plus performantes et plus flexibles.