Trois zones de critères

Supposons que nous souhaitions filtrer dans notre base toutes les personnes âgées de 28 à 30 ans. Il y a en fait, comme le montrent les blocs H1:I2, K1:K4 et M1:M2, trois façons de procéder assez différentes (surtout la troisième !).

Dans le cas du bloc H1:I2, nous utilisons le fait que chaque colonne d’une zone de critères correspond à un ET au sens booléen du terme.

Dans le cas du bloc K1:K3, nous utilisons le fait que chaque ligne d’une zone de critères correspond à un OU au sens booléen du terme.

Dans le cas du bloc M1:M2, nous utilisons un critère calculé dont nous rappelons qu’il doit satisfaire à deux contraintes : d’une part le nom du champ doit être différent des noms des champs de la base, d’autre part il doit faire référence à la première fiche de la base.

Comparaison des trois solutions

Les trois zones de critères donnent le même résultat avec cinq personnes sélectionnées. En fait, la troisième zone – correspondant au critère calculé – est à la fois la plus compacte et la moins facile à comprendre par d’autres utilisateurs.

Pour cette raison, nous vous conseillons donc de ne pas utiliser de critère calculé tant que ce n’est pas vraiment nécessaire !

La zone de critères

Pour répondre à la question posée à la fin de l’article précédent, il faut utiliser ce que l’on appelle une zone de critères. Avant l’arrivée des tableaux croisés dynamiques, tous les bons utilisateurs d’Excel les connaissaient. Comme je le constate souvent chez mes clients, ce n’est plus le cas aujourd’hui.

Une zone de critères comporte au minimum deux cellules voisines dans la même colonne. La première porte le nom d’un champ de la base et la seconde une condition, par exemple respectivement Age au-dessus et >27 en dessous.

Une zone de critères peut comporter plus de deux lignes et plus d’une colonne. Dans ce cas, chaque ligne correspond à un OU (au sens Booléen du terme, et chaque colonne à un ET.

La copie d’écran ci-dessous montre la solution au problème posé à la fin de l’article précédent : lister à la fois les garçons de 27 ans et les filles de 26 ans. Nous avons inséré quelques lignes pour loger la zone de critères. Nous constatons qu’il y a 6 gagnants, 4 garçons et 2 filles.

Un critère calculé

On peut aller plus loin encore en utilisant des critères calculés. Un critère calculé est un critère où le nom du champ à tester est tout sauf celui d’un nom de champ existant : tout sauf Sexe, Prénom, Nom, Age, Salaire ou Pays.

La condition indiquée sous le nom du champ doit faire référence à la première fiche de la base, donc à la première ligne placée sous les noms des champs.

Entrons par exemple Test en F1 et =ou(et(A6="m";D6=27);et(A6="f";D6=26)) en F2. Si nous refiltrons la base en identifiant F1:F2 comme zone de critères, nous obtenons exactement le même résultat !

Remarque 1 – Comme vous pourrez le constater en F2, la cellule affiche le résultat VRAI car il se trouve que notre condition est vraie pour la ligne 6.

Remarque 2 – Dans la formule reproduite ci-dessus, ainsi qu’en C2 et C3, nous avons utilisé une minuscule pour identifier le sexe. Ce n’est pas gênant : à l’instar des fonctions Recherche, Index et Equiv, la capitalisation des lettres n’est pas prise en compte dans les zones de critères. Les lettres accentuées, en revanche, sont significatives !

Pour télécharger le fichier original :

https://drive.google.com/file/d/1j8YYPwgvE8rJrYiwrdqgFEoXQgeKajSu/view?usp=sharing

Une base Excel, à l’ancienne…

Les utilisateurs d’Excel ont tellement l’habitude à ce jour d'utiliser des tableaux croisés dynamiques qu’ils en ont oublié ce qu’était une base dans Excel et ce que l’on pouvait faire avec.

Tout d’abord, il faut savoir que, dans une base Excel, les noms des champs sont toujours dans la première ligne. Chaque ligne suivante liste les données correspondant à un enregistrement.  Il n’est donc pas possible d’utiliser une base transposée où les noms des champs seraient listés dans la première colonne.

Une base Excel n’est en fait pas une base de données. Techniquement, on devrait plutôt parler de table. Une véritable base de données met en fait en relation plusieurs tables.

Position de la base

Une base peut être placée n’importe où dans une feuille de calcul. Ceci dit, il est absolument déconseillé que, sur l’une quelconque de ses quatre bordures, elle soit en contact avec des cellules non vides. Je me permets d’insister sur ce point car j’ai souvent vu chez mes clients des cas où cette règle fondamentale n’était pas respectée.

Pour le vérifier, c’est simple. Cliquez dans une cellule quelconque de la base puis faites [Ctrl]-a. Après cela, si seule votre base est sélectionnée, tout va bien. Sinon, vous avez du nettoyage à faire…

Si je vous dis cela, c’est parce qu’Excel, dans son travail avec les bases, effectue ce [Ctrl]-a sans rien vous demander.

Cliquez en dehors de la base, par exemple en H5, et utilisez la commande Filtrer du ruban Données. Cela ne marche pas, et un message vous dit pourquoi. Cliquez à présent dans une quelconque des cellules de la base, et vous obtenez dans la ligne 1 les filtres, comme nous le voyons dans la copie d’écran.

Excel reconnaît aussitôt si la colonne est de nature textuelle ou numérique, comme nous pouvons le constater dans la partie droite de la copie d’écran.

Les filtres sont cumulatifs

Si nous filtrons dans la colonne Sexe pour ne garder que les garçons, puis dans la colonne Age pour ne garder que les 27, nous obtenons au final quatre personnes, les garçons de 27 ans. Autrement dit, chaque nouveau filtre se cumule avec les filtres antérieurs.

Comment faites-vous, sans passer par un tableau croisé dynamique, pour sélectionner à la fois les garçons de 27 ans et les filles de 26 ans ? Si vous séchez, rassurez-vous, nous répondrons à cette question dans le prochain article.