Consolider les données filtrées avec 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 les données filtrées

En imbriquant la fonction Excel Filtre dans une fonction classique de calcul, il est possible très simplement de livrer à la volée des valeurs de synthèse sur des données filtrées.



Sur l'exemple illustré par la capture, l'utilisateur choisit une équipe par sa lettre (C ici), avec une liste déroulante. Instantanément et juste en-dessous, tous les résultats de synthèse sont livrés pour l'équipe en question. Il s'agit du nombre total de victoires, de la moyenne ou encore du plus grand nombre ou du plus petit par personne.

Classeur Excel à télécharger
Pour la mise en place de cette nouvelle astuce, nous suggérons de télécharger un classeur spécifique.

• Télécharger le classeur consolidations-sur-filtres.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 le tableau des équipes. L'une d'entre elles peut être désignée avec une liste déroulante en cellule F4. En fonction de ce choix et comme nous l'avons vu précédemment, les opérations filtrées doivent être réalisées dans les cellules respectives G7, G8, G9 et G10.

Sommer les données filtrées
Pour additionner les victoires en fonction du choix de l'équipe, la fonction filtre doit réaliser son extraction sur la colonne D selon un critère à émettre sur l'équipe en colonne C. Dès lors, la fonction Somme n'a plus qu'à additionner les informations restreintes et résultantes.

• Sélectionner la cellule G7 du total des victoires à calculer,
• Taper la symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Filtre(,
• A gauche de la barre de formule, cliquer sur le bouton de l'assistant fonction (fx),
• Dans la zone Tableau, désigner la colonne des victoires à filtrer, soit la plage D4:D15,
• Cliquer ensuite dans la zone Inclure pour activer le critère à construire,
• Désigner les équipes, soit la plage de cellules C4:C15,
• Puis, taper le critère suivant : =F4,

Comme vous pouvez le voir, l'assistant fonction réagit aussitôt sur la droite des zones de construction.



La première matrice, en regard de la zone intitulée Tableau, retranscrit toutes les victoires dans le même ordre que la colonne D. La deuxième est le fruit du critère émis sur la colonne C. Les booléens VRAI repèrent les positions des lignes correspondant à l'équipe choisie. C'est ainsi, en bas de la boîte de dialogue, que l'assistant livre son verdict. Il fournit la matrice restreinte aux victoires de l'équipe. Comme ce filtre est engagé dans la fonction Somme, nous allons obtenir le cumul des victoires pour l'équipe désignée avec la liste déroulante.

• Cliquer dans la barre de formule à la fin de la syntaxe,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider le calcul par la touche Entrée du clavier,

La somme livre le résultat de 13 victoires pour l'équipe C, soit exactement le cumul des valeurs restreintes par la matrice résultante. D'ailleurs, la mise en forme conditionnelle prédéfinie qui se déclenche sur le tableau d'origine, vient corroborer ces résultats. Naturellement, si vous changez d'équipe avec la liste déroulante en cellule F4, le cumul des victoires s'actualise aussitôt en parfaite cohérence.

La moyenne des victoires
Toutes les équipes n'ont pas forcément concouru le même nombre de fois, d'où l'intérêt de calculer la moyenne des victoires. Le principe est bien sûr strictement identique. C'est simplement la fonction Moyenne qui doit remplacer la fonction Somme pour englober le filtre.

• En cellule G8, adapter la précédente formule comme suit :

=MOYENNE(FILTRE(D4:D15; C4:C15=F4))

Max et Min des données filtrées
Bien entendu, le principe demeure identique pour les deux derniers calculs, seule la fonction change.

En cellule G9: =MAX(FILTRE(D4:D15; C4:C15=F4)).
En cellule G10 : =MIN(FILTRE(D4:D15; C4:C15=F4)).

Enfin, si vous souhaitez obtenir les noms des personnes ayant réalisé ces scores aux extrémités, il suffit simplement d'étendre la plage étudiée en premier argument de la fonction Filtre.