Textes les plus fréquents selon critères

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Textes les plus fréquents selon critères

A des fins statistiques, il est intéressant de connaître les informations les plus souvent répétées dans une base de données. A ce titre, nous connaissons déjà la fonction Excel Mode. Elle permet d'extraire la valeur la plus fréquente dans une plage de cellules. Mais elle n'agit que sur des données numériques. Cependant, judicieusement imbriquée dans un calcul matriciel, elle permet d'extraire l'information textuelle la plus redondante et ce, en croisant des critères.



Dans l'exemple illustré par la capture, nous travaillons sur une base de données des idées de sorties. Elles sont notamment référencées avec leur département, ville et activité. Sur la droite de cette source d'informations, un tableau de bord se propose. L'utilisateur peut actionner deux leviers par le biais de deux listes déroulantes reliées entre elles. Il s'agit de choisir un département puis une ville lui appartenant. Dès lors, nos formules matricielles doivent dévoiler la ville la plus fréquemment répétée dans le département ainsi que l'activité la plus récurrente pour cette ville.

Source et présentation
Pour réaliser ces travaux, il est nécessaire de récupérer cette base dedonnées.

• Télécharger le classeur textes-frequents-criteres.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Le département de l'Ardèche est déjà défini en cellule I5. Pour ce département, la ville d'Alba la Romaine est choisie en cellule I6.

• Déployer la liste déroulante de la cellule I5,
• Puis, choisir le département : 83-Var,
• Déployer alors la liste déroulante de la cellule I6,
• Puis, choisir la ville d'Agay,

Comme vous le constatez, ces deux listes déroulantes sont effectivement reliées entre elles. Seules les villes du département défini en amont sont proposées.

• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Comme vous pouvez le voir, chaque colonne du tableau est identifiée avec un nom. Ce nom correspond à son titre de champ. Ces noms seront précieux pour simplifier la construction de la formule matricielle destinée à extraire les informations les plus fréquentes.

Ville la plus fréquente
Premièrement, pour réaliser l'extraction de la ville la plus fréquente dans le département choisi, la fonction Excel Index est nécessaire. Elle doit bien sûr agir sur la matrice des villes reconnue par son nom. Ensuite, pour la fréquence de répétition, c'est la fonction Mode qui est à l'honneur. Mais, comme elle ne réagit que sur des valeurs numériques, nous devons la combiner avec la fonction Equiv. Celle-ci doit retourner les positions des villes identifiées. Comme une fonction de recherche renvoie la première occurrence trouvée, à chaque fois qu'une ville identique est décelée, c'est cette première position qui est renvoyée. La fonction Mode n'aura plus qu'à extraire la position la plus fréquemment retournée.

• Sélectionner la cellule I8 et taper le symbole égal (=) pour débuter la formule matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

Dans le cas où le département n'est pas stipulé, le calcul d'extraction conduira nécessairement à une erreur. A toutes fins utiles, nous choisissons donc de la neutraliser.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la matrice des villes par son nom, soit : Ville,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,

En guise d'indice, nous devons repérer la position de la ville la plus fréquemment répétée pour le département spécifié.

• Inscrire la fonction de fréquence suivie d'une parenthèse, soit : Mode(,

Ce repérage de redondance est donc soumis à condition. Comme nous le répétons, la ville doit dépendre du département. Une condition doit être posée sur ce dernier.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la matrice des départements par son nom, soit : Département,
• Taper le symbole égal (=) pour annoncer le critère à vérfiier,
• Désigner le département choisi en cliquant sur sa cellule I5,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,

Lorsque cette condition sur les départements est honorée, nous devons repérer la première position de la ville la plus récurrente. Pour cela et dans un raisonnement matriciel, il suffit de faire agir la fonction de recherche Equiv sur la plage des villes.

• Inscrire la fonction de recherche, suivie d'une parenthèse, soit : Equiv(,
• En guise de valeur cherchée, indiquer la matrice des villes par son nom, soit : Ville,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner de nouveau la matrice des villes par son nom, soit : Ville,

Ainsi, en respectant la condition sur le département, chaque ville restante sera concordante dans sa propre plage. Mais à chaque répétition, c'est la position de la première occurrence trouvée qui sera retournée. La fonction Mode n'aura plus qu'à déceler cette position la plus fréquente. Ainsi transmise à la fonction Index, l'extraction de la ville la plus souvent sollicitée sera réalisée.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Si,

En effet, nous ne prenons pas le soin de renseigner la branche Sinon de la fonction Si. Les cas non concordants ne nous intéressent pas et ne doivent pas être traités.

• Fermer la parenthèse de la fonction Mode,
• Puis, fermer la parenthèse de la fonction Index,

Nous ne renseignons donc pas son troisième argument sur l'indice de colonne de la valeur à extraire. En premier argument, nous lui avons passé la matrice des villes. Celle-ci est en fait un vecteur, soit une matrice d'une seule colonne. La fonction Index sait donc pertinemment que l'extraction doit être réalisée dans cette première et unique colonne.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour ignorer l'anomalie si elle intervient,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

La première ville la plus fréquente pour le département mentionné s'affiche instantanément. Et si vous changez de département avec la liste déroulante en cellule I5, vous notez que cette ville correspond souvent au chef-lieu. Le résultat produit par la formule matricielle semble donc parfaitement cohérent :

{=SIERREUR(INDEX(Ville; MODE(SI(Département=I5; EQUIV(Ville; Ville; 0)))); '')}

Activité la plus récurrente
Désormais, nous devons procéder à l'extraction de l'activité la plus récurrente pour une ville choisie dans le département sélectionné. La formule doit donc recouper deux conditions. La première consiste à vérifier la concordance des départements. La seconde consiste à vérifier la concordance des villes dans ce département. Cependant la fonction Et permettant de croiser les conditions est inopérante dans un raisonnement matriciel. L'astuce consiste à concaténer les matrices des départements et villes et à comparer ligne à ligne la correspondance avec le département concaténé à la ville choisie.

• En cellule I9, adapter la précédente syntaxe comme suit :

=SIERREUR(INDEX(Activité; MODE(SI(Département&Ville=I5&I6; EQUIV(Activité; Activité; 0)))); '')

• Puis, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

L'activité la plus fréquente pour la ville choisie dans le département sélectionné apparaît aussitôt. Tout d'abord, nous réalisons l'extraction sur la matrice des activités désormais. Ensuite, nous vérifions la double condition par concaténation du département et de la ville (Département&Ville=I5&I6). Puis, sur les lignes restantes, nous cherchons les indices de ligne les plus fréquents sur les activités recoupées (EQUIV(Activité; Activité; 0)).

• En cellule I5, choisir le département 83-Var,
• En cellule I6, choisir la ville d'Agay,

Ce choix n'est pas fortuit. Il s'agit de la première ville listée dans la base de données. De fait, il est d'autant plus simple de vérifier la cohérence des résultats. Cette ville est présente à trois reprises. Et c'est bien l'activité extraite (Hôtel) qui y domine.