Valeur la plus fréquente selon critères

Comment rechercher la valeur la plus fréquente d’une liste ? Il existe la fonction MODE qui permet de le faire. Mais si je recherche la valeur la plus fréquente selon les critères d’autres colonnes. Comment faire ?

Par exemple, j’ai une colonne Produits et une colonne Tarifs, comme ci-dessous:

MODE 00

Comment connaître pour chaque produit le tarif le plus fréquent ?

Voici deux méthodes qui vont permettre de répondre à cette question. Bien évidemment, il doit exister d’autres façons de faire. N’hésitez pas à enrichir avec vos commentaires.

Méthode 1: Tableau Croisé Dynamique

La méthode consiste à créer un Tableau Croisé Dynamique (TCD) qui fera apparaître pour chaque produit, le tarif le plus fréquent.

Tout d’abord, nous allons insérer un TCD

MODE 01

Remplissez les champs du TCD:

Dans Lignes: Produits puis Tarifs

Dans Valeurs: Produits (le but est de compter le nombre de produits).

MODE 02

Trier et Filtrer

Positionnez-vous sur un tarif (n’importe lequel). Ensuite il faut trier les données en choisissant l’option « Options de tri supplémentaire » (comme dans la vidéo ci-dessus) puis « Descendants » et « Nombre de Produits ».

Le premier tarif de chaque produit est donc le plus fréquent. Mais il nous faut afficher uniquement LE tarif le plus fréquent.

Nous allons donc filtrer.

Choisissez « Filtre s’appliquant aux valeurs » puis « 10 premiers« . Afficher uniquement 1 élément.

Vous avez donc votre tableau qui répond aux besoins.

Méthode 2: Fonctions Mode et Decaler

Cette méthode, plus compliquée peut être très pratique pour récupérer une valeur plus fréquente en dehors d’un TCD.

Trier le tableau

Pour utiliser la fonction DECALER, il faut dans notre cas commencer par trier la colonne Produit (croissant ou décroissant).

MODE 03

Créer la formule

Vous pouvez à présent créer la formule qui permet de récupérer cette valeur.

Dans une cellule (dans l’exemple, en I11), écrivez le produit dont il faut rechercher la valeur la plus fréquente (ici: Produit1).

Puis dans la cellule à droite, tapez la formule suivante:

=MODE(DECALER(Tableau1[[#En-têtes];[Produit]];EQUIV(I11;Tableau1[Produit];0);1;NB.SI(Tableau1[Produit];I11);1))

Nous allons la décomposer pour mieux la comprendre.

Fonction MODE: elle permet de rechercher la valeur la plus fréquente d’une plage donnée en paramètre. Ici, la plage est dynamique puisqu’elle dépend du produit à rechercher.

La plage est matérialisée grâce à la fonction DECALER. (voir article Liste déroulante dynamique où la fonction est décrite et profitez-en pour voir le commentaire de Paco qui donne une astuce toute simple pour la liste déroulante dynamique).

Pour rappel:

DECALER(réf, lignes, colonnes, [hauteur], [largeur])

Ici la référence est le titre de la colonne Produit de notre tableau.

Pour définir notre plage, nous allons partir de la référence et se décaler de EQUIV(I11;Tableau1[Produit];0) lignes. Cette fonction permet de trouver la position où se trouve la première occurrence de I11 (« Produit1 ») dans la plage (Colonne Produit du tableau).

Nous décalons d’une colonne sur la droite car nous cherchons un tarif.

La hauteur de la plage correspond au nombre de cellules qui contiennent la valeur « Produit1 »)

Enfin, la largeur de notre plage est 1, car nous cherchons uniquement dans une seule colonne: la colonne Tarifs.

Et voici le résultat:

MODE 04

 

J’espère que ces deux astuces vous seront aussi utiles qu’à moi car cela m’a aidé dans plusieurs cas concrets.

Télécharger le fichier

Leave a reply:

Your email address will not be published.

Site Footer

Sliding Sidebar

Inscrivez-vous et recevez ce livre gratuitement (cliquez sur l'image)