Base de données (d)

Nous avons introduit dans le dernier article la notion de critère calculé.

Un nouveau critère calculé

Supposons que nous mettions à présent en F2 la formule suivante : =E6>E7.

Cela signifie que le salaire de George est supérieur à celui d’Alper. Sans surprise, nous constatons que la cellule affiche le résultat FAUX. En effet, les deux salaires étant égaux, la condition n’est pas satisfaite.

Question – A votre avis, quelles seront les personnes qui resteront affichées dans la base ? Essayez honnêtement de répondre avant de lire la suite…

Eh bien, ce seront les personnes de la base qui gagnent strictement plus la personne qui les suit dans la base. Vous le vérifierez rapidement. Pour vérifier cela, la solution est simple :

·         Filtrez la liste sur place

·         Mettez toutes les lignes qui restent affichées en police rouge

·         Arrêtez le filtre

Vous verrez alors que les « gagnants » sont en rouge et les perdants en bleu.

Avec la condition =E6>E7, nous avons gardé tous ceux qui gagnaient strictement plus que la personne suivante dans la base.

Si la condition avait été =E6>E$7, nous aurions gardé tous ceux qui gagnaient plus de 2.200 €.

Un exemple bien réaliste

L’exemple suivant est tiré d’une application que j’ai mis au point pour un de mes clients. Il s’agissait de sélectionner dans une base tous les employés de la société qui étaient salariés pendant une année donnée.

La troisième et la quatrième colonne servaient à vérifier que l’employé en question était bien salarié par la société durant l’année en cours. Pour cela, il fallait que sa date d’entrée dans la société soit antérieure à la fin de l’année en cours, et que sa date de sortie ne soit pas renseignée, ou qu’elle soit postérieure à la fin de l’année en cours.

En guise de conclusion

Quand je vois comment mes clients utilisent les bases de données avec des zones de critères, je constate que la plupart d’entre eux ignorent les critères calculés.

En conséquence, ils sont obligés d’ajouter à la base une colonne supplémentaire qui calcule la condition à appliquer et ils créent alors un critère normal en fonction de cette nouvelle colonne.

Cette solution fonctionne, mais elle alourdit inutilement la base…

Base de données (c)

Nous avons vu dans le dernier article comment utiliser une zone de critères. Une telle zone comporte au moins deux lignes et une colonne. La ligne du haut contient le nom des champs concernés et la ligne du bas les conditions à respecter.

Quand il y a plusieurs lignes et plusieurs colonnes, chaque ligne représente un « OU » et chaque colonne un « ET ».

On peut aller plus loin en utilisant aussi des critères calculés.

Un critère calculé

Pour utiliser un critère calculé, il faut absolument respecter deux critères :

• Un nom de champ différent de ceux des champs de la base
•  La condition doit porter sur la ligne de la première fiche de la base

Pour respecter la seconde condition, nous devons donc la formuler en fonction de George.

C’est le fait que le nom du critère n’est pas celui d’un champ de la base qui indique à Excel qu’il s’agit d’un critère calculé et non d’un critère naturel.

Remarque 1 – Si nous voulions créer un critère calculé en fonction du salaire, nous n’avons pas le droit de lui donner le nom « Salaire » mais rien en nous empêche de lui donner un nom avec ce même texte précédé et suivi d’un espace ! Ce ne sera alors plus le nom d’un champ de la base : )

La copie d’écran suivante montre comment l’on doit créer la zone de critères, en F1:F2. Nous avons reproduit en G2 le contenu de la formule en F2.

Remarque 2 – La zone de critères en F1:F2 joue exactement le même rôle que la zone A1:B3, en étant 3 fois plus compacte avec 2 cellules au lieu de 6. 

Ceci dit, nous ne vous conseillons pas d’utiliser un critère calculé quand un critère naturel est possible. En effet, les critères calculés sont moins « lisibles » (moins facilement "auditables") et risquent donc de troubler les utilisateurs.

De l'écologie pour changer...

Je ne résiste pas à l'envie de vous proposer pour une fois quelque chose qui n'a rien à voir avec Excel, mais qui est à mes yeux un des meilleurs arguments en faveur de l'écologie que j'aie jamais vus.

C'est un dessin publié aujourd'hui dans Les Echos.

Preuve que je ne lis pas que les publications scientifiques et techniques...

Modélisation avec Excel

Je vous propose dans deux semaines une formation « Modélisation avec Excel ». Vous pouvez voir le programme détaillé de cette formation en cliquant dans le gros bouton « Prochaines formations » dans le bandeau à droite de ce blog. Comme je fais surtout des formations intra ces derniers temps, je n’ai pas actualisé ce fichier, mais la description de la formation est toujours d’actualité.

Modélisation avec Excel (2 jours) : vendredi 24 mai et mardi 28 mai.

Lieu : Logma – 12, rue d’Anjou – 78000 Versailles

Heures : 9H00-17H00

Avec la formation « Modélisation avec Excel », un utilisateur moyen d’Excel passe en deux journées au niveau des 5% des meilleurs utilisateurs Excel. J’anime cette formation depuis des années et aucun participant n’a jamais contesté cela.

Vous découvrirez plein de choses sympathiques, comme la façon des réaliser des graphes élastiques avec des titres dynamiques, comment faire des listes déroulantes hiérarchiques, ou des listes déroulantes interactives, qui se modifient en fonction de caractères déjà saisis…

Toutes mes formations peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Ne manquez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en plus de 10 pays.

Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.

Pour toute question, écrivez-moi à thiriez@hec.fr

Base de données (b)

Pour résoudre le problème que je posais dans le dernier article, c’est-à-dire filtrer de façon à obtenir à la fois les garçons de 27 ans et les filles de 26 ans, il faut utiliser une zone de critères. Hélas, depuis que les tableaux croisés dynamiques ont été introduits, de nombreux utilisateurs d’Excel ont perdu la connaissance des zones de critères.

Une zone de critères

La zone de critère la plus élémentaire possible est formée de deux cellules placées l’une au-dessus de l’autre. La cellule du haut contient le nom d’un champ de la table, donc le texte de l’une des cellules de la première ligne, par exemple Age. La cellule du bas contient une condition, par exemple 27. Mais on pourrait aussi utiliser des conditions telles que >=27 ou <>26 (en langage Excel, « <> » signifie « différent de »).

Une zone de critères peut contenir plusieurs lignes et plusieurs colonnes. Quand c’est le cas, chaque ligne représente un OU (au sens booléen du terme) et chaque colonne un ET (idem).

Remarque 1 – Comme pour les fonctions de recherche dans Excel, les majuscules ne sont pas significatives, mais les lettres accentuées le sont. Donc, ici, « m » ou « M » jouent le même rôle !

Garçons de 27 ans et filles de 26 ans

Pour sélectionner à la fois les garçons de 27 ans et les filles de 26 ans, il faut donc utiliser la zone de conditions représentée en A1:B3 dans la copie d’écran ci-dessous. Cette zone signifie « (M et 27) ou (F et 26) ».

Pour obtenir le résultat escompté, il ne nous reste plus qu’à cliquer n’importe où dans la table, à demander un filtre avancé et à renseigner le dialogue qui apparaît alors…

Remarque 2 – Si vous oublier de cliquer préalablement à l’intérieur de la table, le champ « Plages » ne sera pas renseigné par défaut.

Nous voyons dans la copie d’écran ci-dessous une photo du dialogue qui nous a permis de sélectionner les garçons et les filles ainsi que le résultat sur notre table. Toutes les lignes de la table ne satisfaisant pas aux conditions sont masquées.

Si la cellule B3 avait été vide, nous aurions gardé les garçons de 27 ans et les filles de tous âges !

Remarque 3 – Ce qui est particulièrement intéressant et que nous utiliserons dans un article ultérieur, c’est que tous les changements que nous apporterons dorénavant à la table, tant que ce filtre restera actif, ne concerneront que ses lignes visibles !