Consolider, sommer et trier par formule Python 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 :


Consolider, sommer et trier

Python propose une fabuleuse et puissante méthode pour consolider les données. Elle se nomme groupby. On pourrait bien se croire en SQL mais non, il s'agit bien de Python.



Sur l'exemple illustré par la capture, nous travaillons à partir d'un tableau des candidats ayant obtenu des points individuellement, mais appartenant à des équipes. Sur la droite et grâce à une seule formule Python, nous consolidons les équipes, sommons les points pour chacune d'entre elles et livrons les résultats dans un ordre décroissant, de la meilleure à la moins bonne.

Classeur Excel à télécharger
Nous proposons de baser l'étude sur un classeur Excel existant.

• Télécharger le fichier consolider-sommer-trier.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é,

Nous découvrons bien le tableau des candidats entre les colonnes B et D. Sur la droite, une petite grille d'extraction est en attente des résultats de synthèse à livrer.

Consolider et sommer
Nous souhaitons une analyse statistique sur ces équipes pour connaître celle qui a obtenu le plus de points cumulés.

• Cliquer sur la cellule F3 pour la sélectionner,
• Réaliser le raccourci clavier CTRL + ALT + MAJ + P pour activer Python,
• Sélectionner l'intégralité des points et des équipes avec les entêtes, soit la plage C3:D33,

Nous obtenons la syntaxe suivante : xl("C3:D33", headers=True). Nous venons de créer un DataFrame, soit un objet Python encapsulant et représentant ces données. Sur cet objet, nous devons appliquer la méthode Python groupby pour réunir ces équipes.

• Taper un point (.) pour appeler la méthode à suivre,
• Puis, inscrire la méthode groupby comme suit : groupby(["Equipes"]),

De cette manière, nous commandons une consolidation des informations sur les noms des équipes.

• Taper un nouveau point (.) pour appeler la nouvelle méthode à suivre,
• Inscrire la fonction d'agrégation suivante : agg("sum"),

Avec le paramètre sum, sur ces équipes recoupées et regroupées, nous demandons d'effectuer les sommes des valeurs numériques attenantes, soit celles des points.

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

Nous obtenons un DataFrame encapsulant ces données de synthèse.

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

Les résultats sont désormais transformés sous forme de tableau parfaitement visible.



Et comme vous pouvez le constater, avec une formule triviale qui aurait été difficile à construire avec les habituelles fonctions Excel, nous obtenons une consolidation et une synthèse précises et précieuses. L'équipe Rouge, au travers de ses membres, est celle qui a clairement obtenu le plus grand nombre de points. Il aurait été très compliqué de se faire une opinion aussi directe à la simple lecture du tableau d'origine.

Trier les données consolidées et sommées
La solution est très intéressante mais pas totalement aboutie. En effet, imaginez que nous travaillions sur un tableau voire une base de données dense avec d'innombrables catégories. La synthèse sur recoupement serait intéressante mais fouillée et donc difficile à analyser à l'oeil humain. L'idée consiste à trier dans l'ordre décroissant, du plus grand score au plus petit, toutes ces informations pour les positionner dans un ordre évident. Dès lors l'interprétation directe tomberait telle une sentence. Et c'est ce que nous allons accomplir avec une dernière méthode en cascade.

• Sélectionner la cellule F3,
• Dans la barre de formule, cliquer à la toute fin de la syntaxe,
• Taper un point pour appeler la méthode à suivre,
• Puis, inscrire la fonction de tri comme suit : sort_values(by=["Points"], ascending=False),

Nous commandons un tri, sur ces données recoupées et sommées, sur la colonne des points (by=["Points"]) dans l'ordre décroissant (ascending=False).

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

Vous avez le plaisir de découvrir une synthèse on ne peut plus explicite et opérationnelle, les équipes recoupées avec leurs scores cumulés s'affichent fièrement dans l'ordre décroissant, de la meilleure à la moins bonne. Il ne reste plus qu'à exploiter.