Monsieur Excel
Pour tout savoir faire sur Excel !

13 novembre 2018

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 :

08 novembre 2018

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.