Liste des modalités d’une colonne

Dans une base de données, parfois même aussi sans que l’on travaille sur une base de données, il arrive que l’on ait besoin de la liste des « modalités » d’une colonne, c’est-à-dire de la liste des valeurs différentes de cette colonne.

Par exemple, avec la base avec laquelle nous travaillons depuis deux semaines, nous aimerions avoir la liste des pays représentés par les personnes inscrites dans la base.

Rien de plus simple !

Il suffit pour cela de coller en H5 le nom du champ concerné, « Pays » dans notre cas, puis de faire une extraction de la base « sans doublon », en prenant la cellule H5 comme plage de destination. On voit ci-dessous le résultat obtenu en colonne H :
Remarque 1 – Bien entendu, cela suppose qu’il n’y ait rien ci-dessous dans la colonne H. En effet, rappelez-vous que toute la zone H6:H1048576 (H6:H65535 avec Excel 2003 ou XP) est effacée avant l’extraction.

Remarque 2 – N’oubliez pas non plus de vider la zone de critères avant de lancer l’extraction. Sinon, vous n’extrairez que la liste des pays différents pour la sélection effectuée par la zone de critères.

Remarque 3 – N’oubliez pas que cette extraction – comme toute autre extraction – n’est qu’une photo de la base à un moment donné. Si, par la suite, vous modifiez la base, il faudra refaire l’extraction pour qu’elle soit de nouveau adéquate.

Remarque 4 – Comme toute extraction débute par la destruction de tout ce qui se trouve au-dessous de la destination (quand elle comporte une seule ligne), il est inutile d’effacer une ancienne extraction avant d’en lancer une nouvelle. Les filtres automatiques se cumulaient les uns aux autres ; les filtres avancés sont indépendants les uns des autres !

Les limites de l’extraction de base

Il y a plusieurs limites aux extractions depuis une base de données. Nous en avons vu la première lors de l’article précédent : elle détruit sans rémission tout ce qui se trouve en dessous de la liste des noms des champs à extraire.

Destruction de tout ce qui se trouve en dessous

Il y a une parade à cette destruction, qui consiste à ne pas sélectionner seulement une liste de noms de champs à extraire, mais aussi une zone de réception pour le résultat, comme nous le faisons ici avec B35:D37 :

Dans ce cas, Excel n’extrait que les deux premiers résultats, puis vous demande si vous voulez la suite, en indiquant – il ne l’avait pourtant pas dit quand la zone d’extraction était B35:D35 ! – que, si vous poursuivez, tout ce qui se trouve en dessous sera détruit…

Si vous voulez tout extraire, il faut donc annuler, insérer le nombre de lignes nécessaire (mais on ne sait pas combien il en manque !) au-dessus de la ligne 41, puis recommencer l’opération. Comme je l’ai annoncé dans l’article précédent, cette parade n’est donc que partielle.

Extraction sur une autre feuille

Une autre limitation d’Excel est l’impossibilité dans laquelle on se trouve d’extraire le résultat sur une autre feuille. Ceci est d’autant plus mal venu que, tous les experts en bases de données vous le diront, la feuille contenant la base ne devrait contenir que la base, pour des raisons évidentes de sécurité.

C’est la raison pour laquelle, quand je développe une application pour mes clients, je préfère mettre en place une macro qui filtre la liste sur place, puis la copie, et la colle enfin à l’endroit voulu sur une autre feuille.

Ceci dit, rien n'interdit de placer la zone de critères et la zone de réception sur une autre feuille, et d'extraire alors sur cette autre feuille, en faisant référence, pour l'identification de la base, à sa feuille d'origine...

L’extraction sans doublon

Cette case à cocher permet d’éviter des doublons dans le résultat de l’extraction. Si par exemple on extrait seulement le nom et l’âge, deux jumeaux engendreraient alors deux lignes identiques dans l’extraction.

Pour ma part, j’aurais programmé Excel pour que cette case à cocher soit grisée quand l’option « Filtrer la liste sur place » est active. En effet, une base propre ne contient jamais deux lignes identiques : cette case à cocher devrait donc être totalement inutile si l’on filtre sur place.

M’enfin, comme dirait Gaston, cela permettra peut-être aux mauvais gestionnaires de nettoyer leur base :)

Extraction depuis une base

Dans nos deux derniers articles, nous n’avons utilisé le filtre avancé que pour filtrer la liste sur place. Nous allons à présent découvrir son utilisation pour
« copier vers un autre emplacement ». Vous pouvez voir ce que nous avons demandé, dans la première image ci-dessous, puis le résultat obtenu, juste en dessous de la première image.

L’extraction s’est parfaitement bien réalisée et nous retrouvons les quatre personnes obtenues déjà dans le filtrage d’il y a quatre jours. Mais il y a un gros problème…

En effet, ce qui était très important – en B41:D41 – a été totalement détruit ! Pire encore, c’est tout le bloc débutant en B41:D41, jusqu’à la ligne 1.048.576 (65.535, avec Excel 2003 ou XP), qui a été brutalement détruit.

On pense alors : « Ce n’est pas grave, il suffit d’annuler avec [Ctrl]-z ! ». Que nenni ! Il est impossible d’annuler quoi que ce soit. Imaginez seulement que vous ayez programmé votre Excel pour une sauvegarde automatique toutes les 10 minutes et qu’elle survienne juste à ce moment-là…

Nous verrons dans le prochain article quelle parade – partielle seulement hélas – peut être apportée à ce problème.

Remarque 1 – Vous avez probablement remarqué que l’ordre des champs d’extraction peut être différent de l’ordre des champs de la base.

Remarque 2 – L’extraction ne se contente pas d’extraire les contenus, elle prend aussi les formats – ici, les barres verticales rouges – qu’il y avait dans la base.

Utilisation du critère calculé

Peu d’utilisateurs d’Excel connaissent l’existence du critère calculé.

Un critère calculé possède deux caractéristiques :
● le nom du champ est différent des noms des champs de la base ;
● la condition est une formule s’appliquant à la première fiche de la base.

Supposons que l’on souhaite sélectionner toutes les personnes dont le salaire est inférieur à 160 fois l’âge divisé par 3. Si l’on ne connaissait pas l’existence des critères calculés, on serait obligé d’ajouter une colonne à la base qui calculerait le résultat du test pour ensuite sélectionner en fonction de ce résultat.

Ici, avec la zone de critères en F1:F2 et la formule de F2 visible dans la barre de formule, on pourra sélectionner toutes les personnes dont le salaire est inférieur à 160 fois l’âge divisé par 3.


Remarque 1 – La cellule F2 affiche FAUX. C’est tout à fait normal puisque nous avons formulé la condition en fonction de George et que, pour lui, c’est faux.

Remarque 2 – La cellule F1 doit afficher un nom différent des noms des champs de la base. C’est tout à fait logique car, si l’on avait entré « Salaire » en F1, nous chercherions alors toutes les personnes ayant VRAI dans la colonne
« Salaire » et l’on n’en trouverait bien évidemment aucune !

Remarque 3 – A l’extrême – mais je ne vous le conseille pas ! – la cellule F1 pourrait être vide. En revanche, la zone de critères doit quand même rester F1:F2 et ne pas se limiter à F2 : en effet, au minimum, une zone de critères est un bloc de deux cellules verticales, champ et condition.

Remarque 4 – Une zone de critères constituée de plusieurs colonnes peut, tout à fait logiquement d’ailleurs, comporter un mélange de critères normaux et de critères calculés.

Remarque 5 – Le critère calculé qu’il faudrait entrer en F2 pour obtenir le même résultat avec la zone F1:F2 qu’avec la zone B1:C3 est : =ou(et(A6="m";D6=26);et(A6="f";D6=25). Ceci, dit, il est toujours préférable – quand c’est possible – d’utiliser un critère naturel plutôt qu’un critère calculé : le modèle est alors plus compréhensible !

Utilisation du filtre « Avancé »

Le filtre avancé, dont le bouton se trouve en bas à droite du bouton « Filtrer », permet d’utiliser une « zone de critères » et – au choix – de filtrer le résultat sur place (en masquant les lignes ne correspondant pas aux critères) ou d’extraire de la base seulement certains champs pour le résultat.

Nous voyons ci-dessous la mise en place d'un filtre avancé et – en bas de l’image – le résultat obtenu :

Une zone de critères contient au minimum deux cellules : au-dessus, le nom du champ concerné, et en dessous la condition à appliquer. Quand il y a plusieurs lignes et colonnes, chaque colonne représente un ET booléen, et chaque ligne un OU.

La zone de critères en B1:C3 signifie donc : (sexe="m" ET age=26) OU (sexe="f" ET age=25).

Remarque 1 – Notez bien que les critères ne différencient pas entre majuscules et minuscules. En revanche – et cela fonctionne aussi comme pour la fonction Recherche() – les lettres accentuées et les espaces comptent.

Remarque 2 – Par sécurité, il est donc préférable de ne pas taper au clavier les noms des champs de la zone de critères mais de les copier/coller depuis la première ligne de la base : on est alors sûr qu’ils seront identiques.

Remarque 3 – Dans le champ « Plages », le dialogue n’affiche pas le nom de la feuille ; en revanche, il l’affiche dans le champ « Zone de critères ». Cela indique clairement qu’ils ne se trouvent pas nécessairement sur la même feuille.

La commande « Filtrer » de 2007

Pour utiliser l’outil « Filtrer » de l’onglet «Données», il faut préalablement activer une cellule de la base, puis cliquer sur l’outil « Filtrer ». Voici ce que l’on obtient avec, respectivement, un champ numérique et un champ textuel :

Remarque – Un champ est considéré comme textuel quand au moins 50% des informations de sa colonne sont des textes.

Les nouveautés de la version 2007

Couleur – Avec Excel 2003 ou XP, on ne pouvait utiliser pour le filtrage ni la couleur de fond, ni la couleur de police. Maintenant, les deux fonctionnent, y compris pour les résultats de la mise en forme conditionnelle.

Modalités – Avant la version 2007, on ne pouvait pas cocher ou décocher telle ou telle modalité. Il n’était donc pas possible, sauf à utiliser une zone de critères, de sélectionner 3 modalités ou plus (on pouvait en retenir 2 via le filtre personnalisé).

Une amélioration majeure d’Excel 2007 !

L’amélioration la plus notable apportée au filtre par la version 2007 est la disparition d’un bug que l’on traînait depuis la toute première apparition des filtres dans Excel. En effet, avec Excel 2003 ou XP, seules les 1.000 premières modalités de la colonne apparaissaient dans la liste affichée par le menu déroulant du filtre.

Vous le vérifierez rapidement en créant une colonne « Produit » affichant de
« Prod 0015 » à « Prod 1100 » : avec Excel 2003 ou XP, le menu déroulant du filtre s’arrête à « Prod 1014 » !

Groupage de nos cinq objects

Le splendide graphe avec ses deux menus déroulants qui a été développé au cours des trois derniers articles présente cependant une lacune : si l’on déplace ce graphe, les deux menus déroulants et les deux carrés de couleur ne suivent hélas pas le déplacement. Il faut donc les grouper : nous allons voir comment obtenir ce résultat avec Excel 2003 ou XP, puis avec Excel 2007.

Avec Excel 2003 ou Excel XP

Pour groupes les cinq objets avec Excel 2003 ou Excel XP, rien de plus simple !

Faites un [Ctrl]-clic sur le premier carré de couleur, puis sur le premier menu déroulant, puis sur le second carré de couleur, puis sur le second menu déroulant et enfin dans le graphe : vous constatez alors que les cinq objets sont bien tous sélectionnés ensemble.

Il ne vous reste plus qu’à faire un clic droit dans la sélection, puis « Groupe » puis « Grouper » pour rendre les cinq objets totalement solidaires.

Remarque 1 – Bien que tous les objets soient solidaires, cela ne vous empêche pas de dérouler le premier ou le second menu !

Remarque 2 – Si vous souhaitez ultérieurement modifier par exemple le type ou la couleur d’une série, il faudrait dégrouper, faire la modification désirée, puis ensuite tout regrouper.

Avec Excel 2007

Faites un [Ctrl]-clic sur le premier carré de couleur, puis sur le second carré de couleur, et enfin dans le graphe : vous constatez alors que les trois objets sont bien tous sélectionnés ensemble.

Il ne vous reste plus qu’à faire un clic droit dans la sélection, puis « Groupe » puis « Grouper » pour rendre les trois objets totalement solidaires.

Remarque 3 – Hélas, vous aurez beau essayer, vous ne parviendrez pas avec Excel 7 à grouper ces trois objets avec celui que l’on peut former en groupant les deux menus déroulants !

Remarque 4 – Même quand on ouvre avec Excel 2007 le graphe tout groupé créé sous Excel 2003 ou XP, le bloc des deux menus déroulants nous arrive dégroupé du bloc formé par le graphe et les carrés de couleur !

Et la légende, dans tout cela ?

Un inconvénient dans notre beau modèle du 28 novembre, avec ses deux menus déroulants, est que l’on ne voit pas très bien – faute de légende – quelle est la série représentée en bleu et quelle est celle représentée en rouge.

Nous ne pouvons hélas pas corriger le tir en mettant en bleu la police du premier menu déroulant et en rouge celle du premier : en effet, la zone de liste déroulante de la barre d’outils « Formulaires » ne peut être affichée en couleur. Il aurait fallu pour cela utiliser la zone de liste de la barre d’outils « Boîte à outils Contrôles », mais hélas – dans ce dernier cas – rien ne pourrait se faire sans code VBA.

La solution est tout simplement de placer juste à gauche de chaque menu déroulant un carré affichant la couleur associée à la série, afin d’obtenir le résultat ci-dessous :

Pour tracer un carré, il suffit de sélectionner l’objet Rectangle de la barre d’outils Dessin, et de maintenir la touche [Maj] enfoncée pour que le rectangle devienne un carré.

Remarque – De la même façon, quand la touche [Maj] est enfoncée, toute ellipse que vous tracerez devient automatiquement un cercle parfait.