Critères dynamiques sur des données consolidées dans Excel

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


Sujets que vous pourriez aussi aimer :


Critères sur des données consolidées

Dans le volet précédent, nous avons appris grâce à des formules Python dans Excel, à consolider des données sur des tableaux denses et à réaliser des opérations statistiques sur ces résultats. Dans ce nouveau chapitre, nous allons apprendre à émettre des contraintes sur ces analyses afin de ne conserver que les résultats qui sont dignes d'intérêts. C'est ainsi que nous allons considérablement simplifier l'interprétation des données denses, prouesses qui étaient autrefois réservées à des gestionnaires de bases de données comme Access.

Classeur Excel à télécharger
Pour la découverte de ces requêtes par formules Excel, nous suggérons d'appuyer les travaux sur les résultats aboutis à l'occasion du volet précédent.

• Télécharger le fichier 07-consolider-compter-critere.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 dans le bandeau de sécurité,

Nous découvrons le tableau des activités de sorties sur lequel nous avons travaillé précédemment. Sur la droite, dans une cellule grisée en H2, nous avons encapsulé certaines informations de ce tableau dans un DataFrame Python : dv=xl("bdd", headers=True)[["Département", "Ville"]], pour ne conserver que les données sur les colonnes Département et Ville.

• En bas de la fenêtre Excel, cliquer sur l'onglet Syntheses pour activer sa feuille,

Nous découvrons deux analyses statistiques. Toutes deux agissent sur ce DataFrame nommé dv. La première, à partir de la cellule B2, effectue un regroupement sur les noms des départements et pour chacun, elle comptabilise les activités de sorties recensées :

lim = dv.groupby(["Département"]).agg("count").sort_values("Ville", ascending=False)

Cet objet résultant, nous l'avons nommé lim. Nous exploiterons ce nom en dernière instance de ces démonstrations. La seconde prend effet à partir de la cellule E2. Elle effectue un regroupement sur les villes dans chaque département. Et pour chacune, elle comptabilise les activités qui y sont recensées :

synth = dv.groupby(["Département", "Ville"]).size().reset_index(name='counts').sort_values("counts", ascending=False)



Ce second objet, nous l'avons nommé synth, bien entendu dans le but de l'exploiter aussi par formule Python.

Critères statiques
Nous allons maintenant découvrir la méthode query qui permet d'émettre des réserves sur les données consolidées de manière à ne conserver que les résultats les plus intéressants. L'objectif est comme toujours de faciliter l'interprétation des informations de synthèse.

• Cliquer sur la cellule B2 du premier calcul de consolidation statistique,
• Dans sa barre de formule, cliquer à la toute fin de la syntaxe pour y placer le point d'insertion,
• Taper un point (.) pour appeler la nouvelle méthode Python à suivre,
• Inscrire la méthode query comme suit : query("Ville > 15"),

Dans cette manière, sur le champ Ville qui a été généré par la formule Python en deuxième colonne du tableau de synthèse, nous indiquons ne vouloir conserver que les lignes pour lesquelles les activités recensées dépassent les 15 unités.

• Valider la formule Python par le raccourci clavier CTRL + Entrée,

Comme vous pouvez le constater, les données sont restreintes fidèlement à nos attentes.

Sur la deuxième formule en cellule E2, bien qu'elle exploite deux colonnes clés, le principe est le même. En fin de syntaxe, vous pouvez par exemple ajouter la méthode query comme suit : .query("counts>=15"), afin de ne conserver que les lignes pour lesquelles les villes consolidées proposent au moins 15 activités de sorties.

Critères dynamiques
Lorsque le critère se veut dynamique, c'est-à-dire lorsqu'il fait référence à une valeur contenue dans une cellule, une contrainte se dresse. Il est question de décomposer le résultat pour appliquer dans de nouvelles formules la méthode query sur ces objets lim et synth. C'est la raison de la présence de la troisième feuille nommée Dynamiques. Mais tout d'abord il convient de rétablir les formules Python d'origine.

• En cellule B2, supprimer la syntaxe que nous avons ajoutée : .query("Ville > 15"),
• Puis, valider par CTRL + Entrée,
• En cellule E2, supprimer la syntaxe que nous avons ajoutée : .query("counts >=15"),
• Puis, valider par CTRL + Entrée,
• En bas de la fenêtre Excel, cliquer sur l'onglet Dynamiques pour activer la troisième feuille,

Vous notez la présence de deux grilles d'extraction mais aussi de deux zones de critères.



Un premier critère dynamique est énoncé en cellule C3. Il est encapsulé en cellule voisine D3 comme suit : crit1=xl("C3"). Cette valeur dynamique est désormais reconnue sous le nom d'objet crit1. C'est ce nom que nous devons utiliser dans la formule d'extraction à appliquer sur l'objet lim, celui de la synthèse par département.

Une seconde condition dynamique est posée en cellule H3. Elle est aussi encapsulée dans un DataFrame dans la cellule voisine I3 comme suit : crit2=xl("H3"). Son nom représentatif est donc crit2. Nous devons l'utiliser comme critère dans la formule Python de limitation à appliquer sur l'objet synth, représentant la synthèse par ville.

• Cliquer au-dessus de la première grille d'extraction pour sélectionner la cellule C5,
• Réaliser le raccourci clavier CTRL + ALT + MAJ + P pour activer Python,
• Bâtir la syntaxe suivante : lim.query(f"Ville > {crit1}") et valider par CTRL + Entrée,

Nous obtenons un DataFrame que nous devons transformer en données exploitables.

• A gauche de la barre de formule, cliquer sur la flèche dirigée vers le bas,
• Dans les propositions, choisir Valeur Excel,

Nous obtenons bien l'extraction statistique répondant favorablement au premier critère dynamique. La lettre f devant l'expression du critère signifie qu'il s'agit d'une f-string, c'est-à-dire d'une expression embarquant un critère dynamique représenté par un objet Python.

• Cliquer au-dessus de la seconde grille d'extraction pour sélectionner la cellule G5,
• Réaliser le raccourci clavier CTRL + ALT + MAJ + P pour activer Python,
• Bâtir la syntaxe suivante : synth.query(f"counts > {crit2}") et valider par CTRL + Entrée,
• A gauche de la barre de formule, cliquer sur la flèche dirigée vers le bas,
• Dans les propositions, choisir Valeur Excel,

Là encore l'analyse statistique sur critère dynamique est un succès.



Désormais si vous changez les valeurs des critères en cellules C3 et H3, vous avez le plaisir de constater l'actualisation automatique des synthèses. Nos analyses statistiques dynamiques fonctionnent à merveille.