Statistiques de fréquences et de redondances Excel

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


Sujets que vous pourriez aussi aimer :


Statistiques de fréquences

Pour dresser des bilans riches d'enseignements, il est intéressant de pouvoir connaître les informations les plus souvent exploitées dans une base de données. A ce titre, Excel offre la fonction Mode. Cette dernière est capable d'extraire la donnée la plus fréquente dans une plage de cellules ou dans un tableau. Mais elle n'agit que sur des valeurs numériques. Et pourtant, tout l'enjeu consiste bien à faire ressortir les informations textuelles les plus redondantes.



La capture ci-dessus illustre la solution finalisée. Un tableau de bord permet d'émettre des contraintes. Il agit sur une base de données des idées de sorties référencées par activité, ville et département. En fonction de conditions recoupées sur le département et la ville, des statistiques sont calculées pour fournir l'activité la plus récurrente avec sa fréquence.

Source et présentation du concept
Pour initier ces travaux, nous avons besoin d'agir sur des données denses que nous proposons de réceptionner.

• Télécharger le classeur informations-frequentes.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é,

La feuille de ce classeur héberge une base de données conséquente. Les informations y sont détaillées entre les colonnes B et F. L'énumération débute à partir de la ligne 4 et se poursuit jusqu'à la ligne 1000.

Un tableau de bord est présent sur la droite de cette source d'information. En J5, une liste déroulante permet de définir un département. En J6, une liste déroulante dépendante permet de spécifier une ville associée au département mentionné en amont.

Ces travaux nécessaires sont offerts afin de concentrer notre étude sur les calculs de fréquences. En effet, nous avons démontré ces techniques à maintes reprises. Un calcul intermédiaire de repérage est bâti en colonne A. Il insère des numéros incrémentés en regard des enregistrements concordants avec le choix émis sur le département, selon la syntaxe suivante :

=SI(ET(D4=$J$5; NB.SI($F$3:F3;F4)=0); MAX($A$3:A3)+1; '')

Ces numéros sont alors exploités un peu plus loin dans la feuille, plus précisément en colonne Q. L'extraction des villes associées est réalisée grâce à l'imbrication des fonction Index et Equiv, selon la syntaxe suivante :

=SIERREUR(INDEX(F:F; EQUIV(LIGNE(A1); A:A; 0)); '')



C'est alors une plage de cellules nommée Villes retravaillée avec la fonction Decaler qui sert de source de données à la liste déroulante dépendante. Remarque : En déployant la zone Nom, vous notez que toutes les colonnes du tableau de la feuille Frequences ont été nommées en fonction de leurs titres. Ce sont ces noms que nous exploiterons dans les calculs.

Calcul de fréquences
Nous l'avons évoqué, c'est la fonction Excel Mode qui extrait la donnée la plus fréquente sur une plage de cellules qui lui est passée en paramètre : =Mode(Plage_de_cellules).



Pour la tester, il est judicieux de prévoir une plage numérique avec des redondances ainsi qu'une plage de textes avec des répétitions. Et comme l'illustre l'exemple de la capture ci-dessus, dans le cas de nombres, la valeur la plus fréquente est bien extraite. Dans le cas de textes en revanche, la fonction retourne une erreur.

Fréquence de répétitions sur des textes
Pour contourner le problème, l'astuce consiste à exploiter la fonction Mode dans un calcul matriciel. L'idée consiste à déceler sur la plage de cellules recoupée des activités, le plus grand nombre de répétitions. Englobée dans la fonction d'extraction Index, la formule retournera la première ligne de l'activité la plus fréquente. Pour la mise en place de cette solution, nous proposons de débuter par le calcul le plus simple. En J10, il s'agit de dénombrer l'activité la plus récurrente, toutes villes et tous départements confondus. Ce calcul n'est donc soumis à aucune contrainte.

• Sur la feuille Frequences, sélectionner la cellule J10,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,

Ce calcul est particulier. L'assistant va nous permettre de mieux comprendre le raisonnement et de suivre les résultats au fil de la construction.

• Dans la boîte de dialogue qui suit, sélectionner la première proposition et cliquer sur Ok,

Il s'agit en effet des arguments correspondant à la fonction d'extraction que nous souhaitons exploiter. A validation, l'assistant pour la fonction Index apparaît.

• Dans la zone Matrice, taper le nom de la plage de cellules d'extraction, soit : Activité,

Aussitôt sur la droite de la boîte de dialogue, vous voyez apparaître la liste de ces dernières, dans l'ordre où elles sont énumérées dans la colonne Activité.

• Dans la zone No_lig, taper la fonction pour la fréquence suivie d'une parenthèse, soit : Mode(,
• Saisir la fonction donnant la ligne d'une valeur cherchée, suivie d'une parenthèse, soit : Equiv(,
• Saisir de nouveau le nom de la plage des valeurs cherchées, soit : Activité,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Saisir une fois de plus le nom de la plage de cellules, soit : Activité,

C'est ainsi que nous imposons un raisonnement matriciel. En guise de valeur cherchée, nous transmettons une plage de cellules à scruter sur cette même plage de cellules. Toutes les lignes vont être passées en revue et croisées. Comme cette fonction Equiv est imbriquée dans la fonction Mode, il va en résulter la première ligne de l'activité la plus souvent dénombrée.

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

Aussitôt, l'assistant fonction livre un nouveau résultat. Il indique que la ligne de l'activité la plus fréquemment recensée est la deuxième. Il s'agit bien de l'activité Hôtel/Restaurant dévoilée en avant-première en bas à droite de la boîte de dialogue. En effet, nous n'avons pas eu besoin de renseigner le dernier argument pour la fonction Index. Comme la recherche est effectuée sur une seule colonne, sa valeur est implicitement exploitée. Nous proposons néanmoins de la renseigner.

• Dans la zone No_col, taper le chiffre 1,
• Enfin, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

C'est ainsi que nous la transformons en calcul matriciel. De la sorte, toutes les activités cherchées sous forme de matrice le sont respectivement dans cette même colonne des activités. C'est la raison pour laquelle nous ne devons pas valider cette formule par le bouton Ok de l'assistant. D'ailleurs, si vous consultez la barre de formule de la cellule du résultat, vous notez la présence d'accolades encadrant la syntaxe :

{=INDEX(Activité; MODE(EQUIV(Activité; Activité; 0)); 1)}

Elles renseignent explicitement sur la nature matricielle du calcul. Sans surprise, le résultat sur l'activité la plus souvent recensée est restitué en cellule J10. Il s'agit de l'activité Hôtel/Restaurant.

Désormais, à ce résultat, nous souhaitons associer la valeur numérique du dénombrement. En d'autres termes, nous souhaitons connaître le nombre de répétitions de l'activité ainsi extraite. Le calcul est trivial. Il consiste à exploiter la fonction de dénombrement conditionnel Nb.Si:

=Nb.Si(Plage_de_cellules; Critère)

Le critère à compter est l'activité extraite. Ce décompte doit être réalisé sur la plage de cellules des activités.

• En cellule J11, construire et valider la formule suivante : =NB.SI(Activité;J10),

Sur une base de données de presque 1000 enregistrements, cette activité est dénombrée 234 fois. La proportion avoisine les 25 pourcents. Ce résultat confirme donc la parfaite cohérence du calcul matriciel pour extraire la donnée la plus fréquente.

Repérer les enregistrements concordants
La problématique se complique pour l'autre fréquence à trouver en cellule J8. Il s'agit de connaître l'activité la plus fréquemment représentée dans le département et la ville mentionnés à l'aide des listes déroulantes, en cellules respectives J5 et J6.

A l'extrémité de la base de données, il existe une colonne vierge pour l'instant. Elle se nomme Rep. Nous devons y repérer, par des numéros incrémentés, tous les enregistrements concordants avec les choix émis sur le département et la ville. Ces numéros serviront à délimiter la plage d'action car en connaissance de cause, les enregistrements ont été triés dans l'ordre croissant sur le champ de la ville. Ils se suivront donc tous et c'est tant mieux. En conséquence, notre précédent calcul matriciel doit être adapté sur une plage réajustée. Et avant de pouvoir la déterminer, nous devons réaliser les repérages incrémentés. Tout d'abord, les contraintes doivent être posées.

• En cellule J5, choisir le département 13-Bouches du Rhone avec la liste déroulante,
• En cellule J6, choisir la ville Aix en provence avec la liste déroulante,
• Sélectionner alors la cellule G4, soit le point de départ des repérages à réaliser,
• Taper le symbole égal (=) pour débuter le calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Taper la fonction pour recouper les conditions, suivie d'une parenthèse, soit : Et(,
• Sélectionner le premier département en cliquant sur sa cellule D4,
• Taper le symbole égal (=) pour annoncer la condition à satisfaire,
• Sélectionner le département choisi en cliquant sur sa cellule J5,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $J$5,

En effet, nous allons répliquer ce calcul sur les lignes du dessous de manière à passer en revue tous les départements de la base de données. Mais chacun d'entre eux doit immuablement être comparé à ce choix émis en cellule J5. Cette dernière ne doit pas suivre le déplacement, donc nous la figeons.

• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Sélectionner la première ville en cliquant sur sa cellule F4,
• Taper le symbole égal (=) pour annoncer le nouveau critère à honorer,
• Désigner la ville choisie en cliquant sur sa cellule J6,
• Puis, la figer avec la touche F4 du clavier pour les mêmes raisons que précédemment,
• Fermer la parenthèse de la fonction Et,
• Puis, taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,

Lorsque les deux contraintes sont respectées, nous devons marquer la ligne avec un numéro qui s'incrémente à chaque occasion. Pour cela, nous allons mettre en place une technique que nous connaissons bien désormais et que nous avions notamment démontrée au travers de la formation Excel pour extraire les données recoupées. Il s'agit d'exploiter la fonction Max sur une plage de cellules qui grandit en même temps que le calcul est répliqué sur les lignes du dessous. Elle repèrera ainsi la dernière valeur inscrite, soit la plus grande qu'il suffira d'incrémenter.

• Saisir le nom de la fonction suivi d'une parenthèse, soit : Max(,
• Sélectionner la cellule juste au-dessus du calcul, soit G3,
• Enfoncer le symbole deux points (:) pour générer la plage de cellules G3:G3,
• Cliquer sur la première des deux références pour y placer le point d'insertion,
• Enfoncer la touche F4 du clavier pour figer la borne supérieure, ce qui donne : $G$3:G3,

Ainsi, la borne inférieure reste libre de se déplacer en même temps que le calcul est répliqué. La plage grandira donc dans le même temps permettant l'inscription successive des numéros incrémentés.

• Cliquer à la fin de la syntaxe pour y replacer le point d'insertion,
• Fermer la parenthèse de la fonction Max,
• Ajouter une unité à ce calcul, soit : +1,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas de non correspondance,
• Fermer la parenthèse de la fonction Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette astuce permet de conserver active la cellule du calcul afin de l'exploiter dans l'enchaînement.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Comme vous pouvez le voir, des chiffres effectivement incrémentés s'inscrivent en regard des enregistrements recoupant les deux conditions émises depuis le tableau de bord. La syntaxe complète de la formule que nous avons construite est la suivante :

=SI(ET(D4=$J$5; F4=$J$6); MAX($G$3:G3)+1; '')

Borner une plage de cellules dynamique
Pour les raisons que nous évoquions, nous devons appliquer le précédent raisonnement matriciel sur une plage de cellules qui correspond strictement aux activités des lignes repérées dynamiquement. Ainsi et ensuite, nous pourrons extraire l'activité la plus fréquemment recensée et répondant au département et à la ville choisis.

Nous pourrions directement inclure ce calcul dans la formule matricielle finale. Mais pour la bonne compréhension, nous choisissons de la bâtir dans une cellule arbitraire indépendante.

Comme vous le savez, c'est la fonction Adresse qui permet de recomposer les coordonnées d'une cellule. Pour cela, nous devons lui indiquer l'indice de ligne et l'indice de colonne :

=Adresse(numéro_ligne; numéro_colonne)

Et nous devons l'utiliser deux fois, concaténée au symbole des deux points (:) pour produire la plage dynamique stricte. La colonne concernée est celle des activités, soit la colonne E. Son indice est donc le 5. La ligne quant à elle est variable. Pour la borne inférieure, il s'agit de repérer la position du chiffre 1. Pour la borne supérieure, il s'agit de repérer la position de la plus grande valeur incrémentée. Et comme vous le savez, c'est la fonction Excel Equiv qui permet de rechercher ces données afin de retourner leurs positions.

• Sélectionner par exemple la cellule I15,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir la fonction donnant les coordonnées, suivie d'une parenthèse, soit : Adresse(,
• Taper la fonction donnant l'indice de ligne suivie d'une parenthèse, soit : Equiv(,
• Saisir le chiffre 1 pour repérer la position du premier numéro,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la colonne complète, soit : G:G,

En raison de la présence des cellules fusionnées, il est préférable de saisir ces coordonnées.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0 pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Adresse,
• Saisir le chiffre 5 désignant la colonne E des activités,
• Fermer la parenthèse de la fonction Adresse,
• Saisir le caractère de concaténation suivi du symbole deux points entre guillemets, soit : & ':',

Dans une plage, les deux cellules aux extrémités sont en effet délimitées par ce symbole.

• Inscrire un nouveau symbole de concaténation, soit : &,
• Saisir de nouveau la fonction pour les coordonnées suivie d'une parenthèse, soit : Adresse(,
• Inscrire la fonction pour trouver la ligne, suivie d'une parenthèse, soit : Equiv(,
• Saisir la fonction donnant la plus grande valeur, suivie d'une parenthèse, soit : Max(,
• Désigner l'intégralité de la colonne des numéros, soit : G:G,
• Fermer la parenthèse de la fonction Max,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner de nouveau la colonne des numéros incrémentés, soit : G:G,
• Taper un point-virgule suivi du chiffre zéro, soit ;0 pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Adresse,
• Saisir den nouveau le chiffre 5 pour pointer sur la colonne des activités,
• Fermer la parenthèse de la fonction Adresse et valider le calcul par la touche Entrée du clavier,

Comme vous pouvez le voir, grâce à ce calcul, la plage de cellules d'action est directement bornée et identifiée par ses coordonnées.



L'astuce a consisté à repérer les indices de ligne aux deux extrémités. Une première fonction Equiv a trouvé la position de la borne supérieure en recherchant l'emplacement du numéro 1. Une seconde fonction Equiv a trouvé la position de la borne inférieure en cherchant l'emplacement du numéro le plus grand, avec l'appui de la fonction Max. La syntaxe de la formule que nous avons bâtie est la suivante :

=ADRESSE(EQUIV(1; G:G; 0); 5) & ':' & ADRESSE(EQUIV(MAX(G:G); G:G; 0); 5)

Calcul conditionnel de fréquence
Désormais la voie est ouverte pour réaliser l'extraction de l'activité la plus fréquente pour le département et la ville choisis. Il suffit de répliquer la syntaxe du précédent calcul matriciel. Cependant, la plage d'extraction et de décompte (Activité) doit être remplacée par cette nouvelle plage dynamique fraîchement calculée. N'oublions pas néanmoins que la formule doit interpréter le contenu de cette plage. Elle ne doit pas se contenter de prélever ses coordonnées. Et pour cela, nous devons l'englober dans la fonction Excel Indirect.

• Sélectionner la cellule du précédent calcul matriciel, soit J10,
• Dans la barre de formule, sélectionner l'intégralité de la syntaxe du calcul,
• La copier par le raccourci clavier CTRL + C,
• Valider de nouveau le calcul par le raccourci clavier CTRL + MAJ + Entrée pour ne pas l'altérer,
• Sélectionner alors la cellule J8 pour le calcul de la fréquence conditionnelle,
• Dans sa barre de formule, coller la syntaxe prélevée par le raccourci clavier CTRL + V,
• En lieu et place, remplacer trois fois la plage Activité par la plage dynamique : Indirect(I15),
• Puis, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

En consultant le résultat fourni, vous constatez qu'il est parfaitement cohérent. Pour la ville d'Aix en Provence dans les Bouches du Rhône, il n'existe que trois références. Deux d'entre elles sont des activités de Loisir/Sport, soit la plus fréquente et donc celle qui est restituée.

La syntaxe de la formule matricielle que nous avons adaptée est la suivante :

=INDEX(INDIRECT(I15); MODE(EQUIV(INDIRECT(I15); INDIRECT(I15); 0)); 1)

Bien entendu, si vous modifiez le département et adaptez la ville associée, les repérages incrémentés se déplacent pour déceler les bornes de la nouvelle plage d'action. De fait, la formule matricielle livre son nouveau verdict de fréquence. Lorsqu'aucun résultat n'est trouvé ou que les fréquences sont identiques, le calcul matriciel retourne une erreur (#N/A). Pour parlier l'anomalie, il convient de l'encapsuler dans la fonction SiErreur :

=SIERREUR(INDEX(INDIRECT(I15); MODE(EQUIV(INDIRECT(I15); INDIRECT(I15); 0)); 1); '')

Le dernier calcul consiste à réaliser un dénombrement conditionnel multicritère de cette activité. La fonction Nb.Si.Ens est dédiée : =NB.SI.ENS(Activité; J8; Département; J5; Ville; J6).