Filtrer les données avec des cases à cocher Excel : la technique MAGIQUE que 95% des utilisateurs ne connaissent pas encore !
Dans ce tutoriel, je vais vous montrer comment créer un système de filtrage interactif qui va transformer votre façon de travailler avec Excel.
Imaginez pouvoir filtrer instantanément vos données d'un simple clic, sans jamais toucher aux menus de filtres traditionnels, et voir vos tableaux se mettre à jour en temps réel comme par magie.
Cette technique utilise les toutes nouvelles cases à cocher intégrées d'Excel, une fonctionnalité que beaucoup d'utilisateurs ne connaissent pas encore, et qui va rendre vos tableaux de bord non seulement professionnels mais aussi incroyablement intuitifs pour vos collègues.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les ventes d'une boutique de vêtements en ligne.
Ce tableau contient les commandes de la semaine avec la date, le client, le type de produit vendu, la taille, la couleur, la quantité et le montant total.
L'objectif est de rendre ce tableau interactif en permettant à n'importe quel utilisateur de cocher ou décocher des cases pour afficher uniquement les T-shirts, les Pantalons, les Robes, ou n'importe quelle combinaison de ces catégories.
Cette approche est bien plus intuitive que les filtres classiques d'Excel, surtout pour des personnes qui ne maîtrisent pas parfaitement le logiciel.
2. Création de la zone de contrôle avec les cases à cocher
Commençons par préparer la structure de notre système de filtrage.
Nous retrouvons juste à droite une zone dédiée dans laquelle nous placerons nos cases à cocher et les étiquettes correspondantes.
Passons maintenant à l'insertion des cases à cocher, qui constituent la nouveauté d'Excel 365 que nous exploitons dans ce tutoriel.
Contrairement à l'ancienne méthode qui nécessitait d'activer l'onglet Développeur et de manipuler des contrôles de formulaire, les nouvelles cases à cocher sont directement intégrées dans les cellules.
Nous sélectionnons la plage de cellules J7:J9 en cliquant sur J7 et en glissant jusqu'à J9 tout en maintenant le bouton de la souris enfoncé.
Une fois cette plage sélectionnée, nous cliquons sur l'onglet « Insertion » dans le ruban Excel, puis nous repérons le bouton qui affiche une case à cocher et nous cliquons dessus.
Instantanément, Excel insère une case à cocher dans chacune des trois cellules que nous avions sélectionnées.
Le fonctionnement de ces cases est très intuitif.
Lorsque nous cliquons sur une case pour la cocher, la cellule qui contient cette case prend automatiquement la valeur VRAI.
Si nous cliquons à nouveau pour la décocher, la valeur devient FAUX.
Pour vérifier cela, nous cochons la première case en J7, puis nous cliquons sur cette cellule et nous regardons la barre de formule en haut de l'écran.
Nous voyons bien que la cellule contient maintenant la valeur VRAI.
Si nous décochons la case, la barre de formule affiche FAUX.
Une astuce pratique à connaître est que nous pouvons également cocher ou décocher une case en utilisant le clavier : nous sélectionnons une cellule contenant une case à cocher et nous appuyons simplement sur la barre d'espace [Espace]. La case se coche ou se décoche instantanément.
Cette méthode est très rapide quand nous voulons modifier plusieurs cases d'affilée, car nous pouvons nous déplacer avec les touches fléchées et utiliser [Espace] pour chaque case sans jamais toucher la souris.
3. Génération de la formule FILTRE
Maintenant que nos cases à cocher sont en place et fonctionnelles, nous en arrivons à la partie centrale de notre système : la création de la formule FILTRE qui va extraire automatiquement les lignes correspondant aux cases cochées.
Pour gagner du temps, nous allons générer cette formule complexe avec plusieurs critères, en utilisant mon nouvel outil IA Excelformation.fr qui crée automatiquement des formules Excel à partir d'instructions en langage naturel.
Nous sélectionnons la cellule de destination, par exemple la cellule L7, puis nous nous rendons dans le menu « IA Excelformation.fr » afin de cliquer sur le bouton « Créer une formule » :
Sur la boîte de dialogue qui s’affiche, nous saisissons la demande suivante pour expliquer ce que nous voulons accomplir :
« Je veux filtrer le tableau A7:G16 en fonction de la colonne produits, pour afficher les produits cochés dans les cellules J7 à J9. Si aucun critère n'est sélectionné, afficher le message 'Aucune donnée' ».
L'intelligence artificielle analyse notre demande et génère automatiquement la formule :
L'outil nous propose la formule suivante :
=SI(NB.SI($J$7:$J$9;VRAI)=0;"Aucune donnée";FILTRE($A$7:$G$16;((( $C$7:$C$16=$I$7)*($J$7))+(( $C$7:$C$16=$I$8)*($J$8))+(( $C$7:$C$16=$I$9)*($J$9)))>0))
Décomposons maintenant cette formule pour bien comprendre sa logique, car elle combine plusieurs fonctions imbriquées de manière intelligente, qui au premier regard, peuvent sembler intimidantes.
La formule commence par une fonction SI qui gère le cas où aucune case n'est cochée. Le test logique « NB.SI($J$7:$J$9;VRAI)=0 » compte combien de cellules dans la plage J7:J9 contiennent la valeur VRAI, c'est-à-dire combien de cases sont cochées. Si ce nombre est égal à zéro, cela signifie qu'aucune case n'est cochée, et dans ce cas, la formule affiche simplement le message « Aucune donnée ». Cette vérification préalable évite que la fonction FILTRE ne génère une erreur quand aucun critère n'est actif.
Si au moins une case est cochée, nous passons à la partie principale de la formule qui est la fonction FILTRE.
- Cette fonction prend en premier argument la plage « $A$7:$G$16 » qui représente notre tableau source complet avec toutes les colonnes de données que nous voulons filtrer. Nous remarquons que toutes les références sont en dollars, ce qui les rend absolues et empêche leur modification si nous copions la formule ailleurs.
- Le deuxième argument de FILTRE est la condition de filtrage, qui est la partie la plus sophistiquée de notre formule. Nous avons trois blocs identiques qui sont additionnés : « (($C$7:$C$16=$I$7)($J$7)) + (($C$7:$C$16=$I$8)($J$8)) + (($C$7:$C$16=$I$9)*($J$9)) ». L'expression « $C$7:$C$16=$I$7 » compare chaque cellule de la colonne Produit avec le texte dans I7, qui contient « T-shirt ». Cette comparaison génère un tableau de valeurs VRAI ou FAUX. Ensuite, cette comparaison est multipliée par « $J$7 », qui est notre case à cocher.
Cette multiplication fonctionne ici de manière binaire. Si la case en J7 est cochée, elle contient VRAI qui vaut 1 en calcul, donc la multiplication conserve les résultats de la comparaison. Si la case n'est pas cochée, elle contient FAUX qui vaut 0, donc toute la multiplication donne 0, ce qui annule ce critère. C'est une astuce élégante pour activer ou désactiver chaque critère selon l'état de sa case à cocher.
Les trois blocs sont additionnés avec des signes plus, ce qui crée une condition OU logique. Une ligne sera affichée si elle correspond au premier critère OU au deuxième OU au troisième. Le « >0 » à la fin convertit le résultat en valeurs booléennes VRAI ou FAUX que la fonction FILTRE peut interpréter. Si la somme des trois blocs est supérieure à zéro pour une ligne donnée, cela signifie qu'au moins un critère correspond, et la ligne sera incluse dans le résultat filtré.
Cette formule générée automatiquement par l'IA est plus robuste que celle que nous aurions pu créer manuellement avec une approche plus simple, car elle gère intelligemment le cas des cellules vides et utilise la multiplication pour activer ou désactiver dynamiquement chaque critère selon l'état réel des cases à cocher.
Et surtout, nous l’avons obtenue en une fraction de seconde, là où il aurait fallut plusieurs minutes pour la rédiger manuellement.
Maintenant que notre formule est en place, testons le système pour nous assurer qu'il fonctionne correctement dans toutes les situations.
Commençons par décocher toutes les cases si certaines sont cochées.
Nous observons que le tableau filtré affiche le message « Aucune donnée », ce qui confirme que notre formule gère correctement le cas où aucun critère n'est sélectionné.
Cochons maintenant uniquement la case « T-shirt ».
Le tableau filtré se met instantanément à jour et affiche uniquement les quatre lignes contenant des T-shirts :
