C’était vraiment prise de tête pour générer dynamiquement des données avec des filtres.
Cela nécessitait du temps voire des connaissances poussées d’Excel notamment dans le langage Virtual Basic (VBA).
Depuis la version 2016 (à jour) et l’abonnement 365, l’utilisateur peut réaliser cela à l’aide d’une simple fonction : FILTRE.
Syntaxe
La fonction FILTRE se présente de la manière suivante :
=FILTRE(tableau;inclure;[si_vide])
avec les arguments:
- tableau : plage de données qui sera affichée dans nos résultats
- inclure : il s’agit de la (des) règle(s) de filtrage
- [si_vide] : argument facultatif pour afficher un texte si aucune correspondance n’est trouvée.
Données filtrées avec un filtre
Découvrons cette fonction à l’aide d’un exemple.
J’ai listé les livraisons effectuées par mon entreprise. J’ai créé un formulaire pour filtrer mes données en fonction du client livré.
étape 1 : créer ma liste déroulante dynamique
Comme nous l’avons vu dans l’article dédié, nous allons créer un liste déroulante dynamique grâce aux fonctions TRIER et UNIQUE.
Dans ma cellule H2, je rentre la formule :
=TRIER(UNIQUE(Tabl_ventes[Client]))
Maintenant, on crée notre liste déroulante dynamique.
- Sur la cellule J1, Allez dans Données > Validations de données.
- Sélectionnez Liste puis saisissez dans le champ Source =$H$2#
Nous avons fini cette étape, passons à la suite.
étape 2 : récupérer les données filtrées
- Je veux afficher l’ensemble du tableau soit Tabl_ventes.
- Ma règle de filtrage est que les résultats doivent être uniquement pour le client sélectionné dans ma liste déroulante : Tabl_ventes[Client]=J1
- Si je n’ai aucun résultat, j’affiche le message « Aucune vente trouvée ».
On obtient la formule :
=FILTRE(Tabl_ventes;Tabl_ventes[Client]=J1;"Aucune vente trouvée")
Le message « Aucune vente trouvée » s’affiche. C’est normal, nous n’avons pas encore sélectionné de client dans la liste déroulante.
Choisissons un client. Et voilà, des résultats sont trouvés. Il suffit de choisir un autre client pour que mon tableau s’adapte en fonction des résultats trouvés.
Données filtrées avec plusieurs règles
La fonction FILTRE permet d’aller encore plus loin en déterminant plusieurs règles de filtrage.
Dans l’argument inclure, il suffit d’écrire chaque règle entre des parenthèses et lier chaque condition avec :
- un astérisque * équivaut au ET pour que toutes les conditions soient respectées
- un plus + équivaut au OU pour qu’une condition soit bonne
(règle1)*(régle2)*(règle3).... ou (règle1)+(règle2)+(règle3)
Continuons avec notre exemple. Je décide de mettre des filtres sur :
- le client :
- le produit
- la date
- la paiement
- clients : les données se trouvent dans la colonne Client du tableau Tabl_ventes2 et le client sélectionné dans H4
Tabl_ventes2[Client]=H4
- produits : les produits se trouvent dans la colonne Produit du tableau Tabl_ventes2
Tabl_ventes2[Produit]=J4
- dates : pour extraire le mois d’une donnée, il existe la fonction MOIS. on obtient :
MOIS(Tabl_ventes2[Date Livraison])>=H7et
MOIS(Tabl_ventes2[Date Livraison])<=J7
- Paiement : les données se trouvent dans la colonne Paiement du tableau Tabl_ventes2
Tabl_ventes2[Paiement]=H10On a maintenant toutes nos règles. Passons à la formule complète :
=FILTRE(Tabl_ventes2;(Tabl_ventes2[Client]=H4)*(Tabl_ventes2[Produit]=J4)*(MOIS(Tabl_ventes2[Date Livraison])>=H7)*(MOIS(Tabl_ventes2[Date Livraison])<=J7)*(Tabl_ventes2[Paiement]=H10);"Aucune vente trouvée")
Ping : Excel : plusieurs résultats en une cellule - May6