Monsieur Excel
Pour tout savoir faire sur Excel !

27 juin 2007

La recopie de critères calculés

Parfois, on a une zone de critères dans laquelle il y a de nombreuses lignes consécutives (des « OU », donc). Dans ce cas, si l’un des critères est un critère calculé, la création de la zone de critères peut devenir fastidieuse.

Considérons l’exemple ci-dessus, où le premier critère est une sélection de pays, et où le second est un critère calculé dont la formule est reproduite en commentaire.

Si l’on tire la formule de C2 vers le bas, elle deviendra =et(D13>27;D13<=30) dans la cellule C3, ce qui n’est pas bon puisque le critère calculé doit toujours faire référence à la ligne de la première fiche de la base, la ligne 12 dans notre cas.

La solution que j’utilise pour résoudre ce problème est simple, mais quand même astucieuse. Il suffit d’entrer en C2 la formule =et($D$12>27;$D$12<=30), que l’on recopie ensuite vers le bas. Il ne reste plus, en fin de course, qu’à effectuer dans la colonne un remplacement de $D$12 par D12, et le tour est joué !

Remarque 1 – Nous aurions aussi pu entrer la formule =et(D$12>27;D$12<=30), mais cela aurait pris plus de temps car il aurait fallu utiliser quatre fois la touche [F4] au lieu de deux fois !

Remarque 2 – Il y a d’autres cas encore où le Rechercher/Remplacer, appliqué à des formules, permet de résoudre esthétiquement certains problèmes…

23 juin 2007

Pour clore les zones de critères…

Les « $ » dans un critère calculé

Il faut faire bien attention dans les critères calculés : seules se déclinent les coordonnées qui sont relatives dans la formule.

Ainsi, quand nous utilisons le critère calculé =E7>E8, nous sélectionnons dans la base toutes les personnes dont le salaire est supérieur au salaire de la personne suivante dans la base.

Si en revanche nous entrons =E7>$E$8, nous sélectionnons alors tous ceux dont le salaire est supérieur à 2.200 € !

Plusieurs façons d’atteindre le même objectif

Les trois zones de critères A1:A4, C1:D2 et F1:F2 visent exactement le même objectif, la sélection de toutes les personnes ayant de 28 à 30 ans. La première zone de critères utilise des « OU », la seconde des « ET », et la troisième un critère calculé.

Je répète que, si l’on peut se débrouiller sans avoir recours à un critère calculé, le modèle gagnera en lisibilité. Ne les utilisez donc que s’ils représentent la seule solution possible !

Remarque – Une zone de critères peut inclure à la fois des critères simples et des critères calculés.

18 juin 2007

La puissance du critère calculé

Peu d’utilisateurs d’Excel maîtrisent les critères calculés, une fonctionnalité très puissante des « bases de données » Excel.

Avec un critère calculé, on peut formuler en une seule cellule une condition éventuellement complexe. Un critère calculé doit absolument respecter deux contraintes :
- le nom du critère calculé peut être n’importe quel texte autre que le nom d’un champ de la base ;
- le critère calculé doit absolument être formulé en fonction de la première fiche de la base, dans notre cas la ligne 6.

Pour sélectionner, comme dans mon dernier message, les hommes de 25 ans et les femmes de 26 ans, un simple critère calculé suffit. Nous l’avons entré en E1:E2, la formule de la cellule E2 étant affichée dans la copie d'écran ci-dessus en commentaire.

Remarque 1 – La cellule E2 affiche « faux » car, pour Georges, la condition n’est pas respectée.

Remarque 2 – C’est bien pour cela que le nom du critère calculé ne peut pas être le nom d’un champ existant, sinon on chercherait les personnes ayant
« vrai » dans ce champ…

Le résultat est bien entendu exactement le même que celui obtenu avec la zone de critères B1:C3, avec maintenant une zone de critères plus compacte mais – il faut bien l’avouer – moins facile à comprendre.

Je ne vous conseille pas d’utiliser les critères calculés quand – comme c’est ici le cas – on peut parvenir au même résultat avec des critères simples : le gain de place, dans ce cas, n’est pas compensé par la perte en lisibilité.

Là où les critères calculés sont précieux, c’est quand, sans les utiliser, on devrait ajouter une colonne à la base pour effectuer la sélection voulue. Si par exemple vous vouliez sélectionner les personnes dont le salaire mensuel est inférieur à 35 fois l’âge, il vous faudrait un critère calculé pour éviter de devoir ajouter une colonne à la base. Ce critère se formulerait alors, dans notre cas : =E6<35*d6.

14 juin 2007

Utilisation d'une zone de critères

Le filtre simple, comme nous l’avons vu dans les deux derniers messages, permet de sélectionner, à partir de la sélection courante (donc, en cumulant les contraintes) tel ou tel enregistrement.

On peut ainsi sélectionner les hommes, puis 25 ans, pour afficher tous les hommes de 26 ans. Si l’on voulait afficher les femmes de 26 ans, il faudrait d’abord réafficher toute la base, puis sélectionner le sexe et l’âge voulus.

Pour afficher simultanément les hommes de 25 ans et les femmes de 26 ans, il faut utiliser le filtre élaboré et une zone de critères, ce que vous avons fait ci-dessous où l’on voit le résultat de la sélection :

Une zone de critères se compose au minimum de deux cellules avec, au-dessus, le nom du champ et, en dessous, la condition à appliquer : une valeur, un texte (avec ou sans jokers), une condition simple utilisant l’un des codes suivants : =, <, >, >=, <= ou <>, le dernier code signifiant « différent
de ».

Dans une zone de critères :
- chaque ligne représente un « OU » logique ;
- chaque colonne représente un « ET » logique.

Notre zone de critères en B1:C3 signifie donc que les bonnes réponses sont ET(age=25;sexe=״m״) OU ET(age=26;sexe=״f״)

Remarque 1 – Comme c’est le cas avec les fonctions Recherche() et Equiv(), les majuscules ne sont pas significatives ; il revient donc au même de chercher « m » ou « M ».

Remarque 2 – Si la cellule B3 avait été vide, nous aurions sélectionné les hommes de 25 ans et toutes les femmes, quel que soit leur âge.

09 juin 2007

Le bug du filtre automatique

Il y a depuis des années un bug du filtre automatique, enfin annoncé dans l’aide d’Excel – cela n’a pas toujours été le cas – mais bien embêtant quand même, c’est que le menu déroulant du filtre n’affiche que les 1.000 premières valeurs différentes, les 1.000 premières « modalités », pour reprendre le terme du dernier article.

Il ne s’agit pas d’un bug de programmation, sinon on peut espérer qu’il aurait été déjà résolu, mais d’un bug de conception. On se demande d’ailleurs d’où vient cette limite de 1.000, puisque 1024, une puissance de 2, semblerait plus logique… M’enfin, comme disait Gaston Lagaffe !

Nous avons, pour illustrer cela, créé une liste avec 1.100 valeurs et collé à droite les copies d’écran du haut et du bas du menu déroulant. Nous constatons que nous démarrons bien avec « Nom 0101 » et que la liste s’arrête à « Nom 1100 », soit précisément à la 1.000ème valeur.

Remarque – Heureusement, cette limitation ne touche pas les menus déroulants obtenus par la commande Données – Validation – Liste !

05 juin 2007

Le filtre automatique des BdD

Les bases de données (BdD) d’Excel ne sont en fait que de simples tables ; c’est d’ailleurs pour cette raison que Microsoft, aujourd’hui, ne les appelle plus que « listes ». Les noms des champs sont listés dans la première ligne, et chaque ligne – ensuite – correspond à une fiche (ou enregistrement).

Les BdD, dans Excel, proposent un mélange permanent – et étrange – de fonctionnalités astucieuses et de fonctionnalités douteuses, pour ne pas dire foireuses. Nous en verrons divers exemples aujourd’hui ainsi que dans les messages suivants.

Intéressons-nous aujourd’hui au filtre automatique. On active une cellule quelconque de la base et on utilise la commande Données – Filtrer – Filtre automatique. Aussitôt, un menu déroulant apparaît dans les cellules de la première ligne, celle des noms de champs.

Une fonctionnalité astucieuse

Ce qui est astucieux ici, c’est que les menus de ces listes déroulantes s’adaptent à la colonne concernée. Si par exemple il n’y a aucun vide dans cette colonne, les commandes « Vides » et « Non vides » n’apparaissent pas en bas du menu.

Une fonctionnalité douteuse

Là où Excel n’est pas performant du tout, c’est que la commande « 10 premiers » ne devrait pas apparaître quand le champ n’est pas numérique. En effet, s’il ne l’est pas, cette commande est totalement inopérante : elle aurait pourtant pu l’être en affichant alors les 10 premières cellules, selon l’ordre alphabétique… Mais c'est un choix de Microsoft...

Il serait bien plus logique de ne pas afficher cette commande du tout, comme n’était le cas pour « Vides » et « Non vides », quand elle est inutile. Ou, au pire, de la griser…

En outre, quand on a utilisé cette commande, par exemple pour afficher les
« 3 dernières cellules », le nom de la commande – au lieu de s’adapter – reste « 10 premiers ». Il faut donc réactiver la commande pour voir quelle sélection on avait opéré.

La couleur du menu déroulant

Une idée astucieuse est que chaque menu déroulant ayant servi à opérer une sélection voit sa couleur passer du noir au bleu. On peut donc voir à tout instant quels sont les champs sur lesquels une sélection est en cours. Rappelons au passage que ces sélections sont cumulatives.

Ce qui est totalement idiot, en revanche, c’est d’avoir choisi le bleu comme couleur. De nombreux utilisateurs n’ont d’ailleurs jamais remarqué cette différence de couleur, tant elle est faible. Il aurait été plus inspiré de prendre une couleur plus différente du noir, par exemple le rouge, le jaune ou le vert…

01 juin 2007

Calcul du nombre de modalités

Les fonctions de base de données, les fonctions BD…() d’Excel, sont assez nombreuses. On peut ainsi calculer le max, le min, la moyenne, l’écart-type, la variance... des fiches d’une base qui correspondent à des critères spécifiques. Il en manque cependant une, celle qui calculerait le nombre de
« modalités » d’une colonne, c’est-à-dire le nombre d’éléments différents qu’elle contient.

Nous vous proposons deux solutions, toutes les deux sous la forme de formules matricielles, donc validées avec la combinaison [Ctrl]-[Maj]-[Entrée].

La formule entrée en D1 est la suivante :
=somme(si(equiv(A2:A14;A:A;0)=ligne(A2:A14);1;0))

Le principe en est en fait assez simple : les valeurs originales sont celles dont le numéro de ligne est celui de la première ligne où l’on trouve cette valeur dans la colonne.

Remarque – La formule précédente peut être simplifiée en ôtant le si(). En effet, quand on multiplie VRAI par 1, on obtient le résultat 1. Voici donc la formule simplifiée, entrée en D2 :
= somme(1*(equiv(A2:A14;A:A;0)=ligne(A2:A14)))