Fonction Excel Filtre avec critères ET et OU

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


Sujets que vous pourriez aussi aimer :


Fonction Filtre avec critères ET et OU

La fonction Excel Filtre regorge décidément de possibilités. Dans les volets précédents, nous avons appris à réaliser des extractions très précises en recoupant des conditions sur des matrices. Nous allons voir ici qu'il est possible d'aller plus loin en additionnant des matrices conditionnelles pour simuler l'opérateur OU sur l'ensemble des données.



Sur l'exemple illustré par la capture, nous travaillons de nouveau sur un tableau des équipes relatant les victoires individuelles. Sur la droite, l'utilisateur peut émettre trois conditions. Il désigne deux équipes par leurs lettres et fixe un quota minimum de victoires, en valeur numérique juste en-dessus. Dans un tableau annexe, il en résulte l'extraction des membres de ces deux équipes ayant forcément remporté un nombre de victoires au moins égal au critère numérique imposé.

Classeur Excel à télécharger
Le classeur représentant ces équipes est proposé au téléchargement.

• Télécharger le classeur filtre-et-ou.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 tableau des équipes se présente sur la gauche de la feuille. L'utilisateur peut choisir d'extraire deux de ces équipes grâce à des listes déroulantes en cellules F4 et H4. Mais dans le même temps, il doit émettre un critère numérique en cellule G6. Seuls les membres des deux équipes ayant dépassé ou atteint ce seuil de victoires seront retenus.

Extraire sur trois conditions - Et - Ou
Pour retenir les deux équipes à la fois, nous devons raisonner comme nous le ferions avec l'opérateur OU. Pour recouper ces résultats avec la contrainte numérique, nous devons raisonner comme nous le ferions avec l'opérateur ET.

• Sélectionner la première case de l'extraction à fournir en cliquant sur la cellule F9,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Inscrire le nom de la fonction d'extraction suivi d'une parenthèse, soit : Filtre(,
• Désigner l'intégralité du tableau à filtrer en sélectionnant la plage de cellules B4:D15,
• Taper un point-virgule (;) pour passer dans l'argument du critère,

C'est ici que doivent intervenir les trois matrices conditionnelles soit à additionner soit à recouper.

• Ouvrir deux parenthèses pour accueillir l'union des deux premières conditions,
• Désigner la colonne des équipes en sélectionnant la plage de cellules C4:C15,
• Taper le symbole égal (=) pour annoncer le premier critère à honorer,
• Puis, désigner la première équipe choisie par l'utilisateur en cliquant sur sa cellule F4,
• Dès lors, fermer la parenthèse de la matrice conditionnelle,

A ces résultats sur le choix de la première équipe, nous devons ajouter ceux correspondant au choix de la seconde équipe.

• Taper le symbole Plus (+) pour simuler l'opérateur Ou et ainsi additionner une condition,
• Ouvrir une nouvelle parenthèse pour accueillir une nouvelle matrice conditionnelle,
• Désigner de nouveau la colonne des équipes en sélectionnant la plage de cellules C4:C15,
• Taper le symbole égal (=) pour annoncer le deuxième critère à honorer,
• Puis, désigner la seconde équipe choisie par l'utilisateur en cliquant sur sa cellule H4,
• Dès lors, fermer la parenthèse de cette deuxième matrice conditionnelle,
• Puis, fermer la parenthèse qui englobe la syntaxe de l'addition,

A ce stade, nous sommes censés extraire tous les membres des deux équipes choisies, sans restriction. Et justement, une condition à recouper est à observer cette fois. Seuls les membres ayant atteint ou dépassé le nombre de victoires stipulé en cellule G6 doivent être conservés. Les autres ne doivent pas être extraits.

• Taper le symbole de l'étoile (*) pour annoncer la condition à recouper,
• Ouvrir une parenthèse pour accueillir une troisième matrice conditionnelle,
• Cette fois, désigner la colonne des points en sélectionnant la plage de cellules D4:D15,

C'est en effet dans cette rangée que les victoires sont décomptées. C'est donc sur cette nouvelle plage que le dernier critère doit être vérifié.

• Taper le symbole supérieur suivi du symbole égal, soit : >=,

Ainsi, nous annonçons l'inégalité à suivre et à respecter sur l'ensemble de la matrice et à recouper avec les résultats livrés par les deux précédentes matrices conditionnelles additionnées.

• Désigner le critère numérique en cliquant sur sa cellule G6,
• Fermer la parenthèse de cette troisième matrice conditionnelle,
• Fermer la parenthèse de la fonction Filtre,
• Puis, valider la formule par la touche Entrée du clavier,

Comme vous le constatez, ce sont bien les deux équipes réunies qui sont extraites. Et seuls les membres répondant favorablement au critère numérique sont gardés. Bien sûr, si vous modifiez l'une ou l'autre équipe avec les listes déroulantes et que vous changez la condition numérique, l'extraction chirurgicale s'actualise aussitôt.

Trier les résultats extraits
Pour une meilleure lecture des résultats extraits, il apparaît opportun de les réorganiser à la volée. L'idée est de les trier du plus grand au plus petit sur le nombre de victoires et pour un même nombre, de les trier en deuxième clé, de façon croissante sur la lettre de l'équipe. Pour cela, il suffit de faire intervenir la fonction Trier sur notre calcul, avec une astuce matricielle succulente.

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

=TRIER(FILTRE(B4:D15; ((C4:C15=F4) + (C4:C15=H4)) * (D4:D15>=G6)); {3.2}; {-1; 1})

Nous imbriquons donc la fonction Filtre dans la fonction Trier. Le deuxième argument de cette dernière permet de définir la colonne de tri. Or, nous souhaitons opérer un tri hiérarchique sur deux colonnes, d'abord sur celle des victoires et ensuite sur celle des lettres des équipes. L'astuce consiste donc à lui passer une matrice horizontale repérant les positions de ces colonnes dans le tableau ({3.2}). Le troisième argument permet par défaut de définir l'ordre du tri sur la colonne choisie en amont. De la même façon, nous engageons une matrice horizontale ({-1;1}) pour définir un tri décroissant sur les points (-1) puis un tri croissant sur les équipes (1) en seconde clé.



Si vous validez la formule par la touche Entrée du clavier, vous avez le plaisir de constater que seuls subsistent les membres des équipes choisies atteignant le score voulu et dans le même temps, une réorganisation est effectivement opérée dans un premier temps sur le nombre de victoires et dans un second, sur la lettre de l'équipe.

Concernant les matrices que nous avons passées à la fonction Trier, nous aurons l'occasion de revenir sur ces subtilités avec des séries consistant à démontrer aussi les prouesses de cette fonction apparue avec Office 365.