ATTENTION : Cette page n'est pas un "Tuto"...
Il s'agit d'un "recueil de notes techniques" autour de la la fonction =Filtre()
+ la capacité d'obtenir une recherche en temps réel (au fur et à mesure de la saisie)
La fonction =Filtre() est une variation des filtres habituels d'excel... :
Elle affiche les résultats filtrés dans une autre plage de cellules
Les données sources restent intactes
Mise à jour automatique en cas de modification des données source
offre une lecture des données (pas de modif)
N'hésitez pas à demander à être accompagné.
La fonction Filtre permet de retourner des résultats selon un critère, comme le ferait l'usage d'un filtre de colonne :
Sa structure est la suivante : = FILTRE ( Base de donnée ; critère ; "si pas de résultat" )
Ci dessus la mise en place d'une fonction FILTRE simple :
=FILTRE ( base ; base[Structure]=B2 ; "pas de résultat" )
La cellule A6 contient la formule qui se comprend comme :
" affiche les lignes du du tableau base où dans la colonne 'Structure' il y a la valeur 'Lamaze' " (s'il n'y a rien affiche pas de résultat)
La formule FILTRE se met dans 1 seule cellule (A6)... elle s'étend seule...
La formule est perfectionnée pour rechercher une partie seulement d'une cellule (une cellule qui contient...)
=FILTRE(base; ESTNUM(CHERCHE(B2; base[Structure])); "Aucun résultat")
La fonction filtre ci-dessous
base[Structure]=B2 se transforme en ...
"cherche les caractères de B2 dans la colonne Structure"
=> CHERCHE ( B2 ; base[Structure] )
"verifie si le resultat est numérique"
=> ESTNUM ( CHERCHE ( B2 ; base[Structure] ) )
la condition ainsi créée est réintégrée dans la fonction FILTRE
= > FILTRE ( base ; ESTNUM(CHERCHE(B2; base[Structure])) ; "Aucun résultat")
FILTRE peut aussi contenir 2 critères (ou plus encore)
Signe ' * ' => ET
=FILTRE(base; (base[Structure] = "Lamaze") * (base[Etat] = "oui") ; "aucun résultat trouvé")
La fonction est semblable à l'exemple 1, mais utilise ' * ' (étoile) pour demander 2 critères...
("résultats de 'Base" où 'Structure' = Lamaze ET etat = 'Oui')
Signe ' + ' => OU
=FILTRE(base; (base[Structure] = "Lamaze") + (base[Etat] = "oui") ; "aucun résultat trouvé")
utilise ' + ' (signe plus ) pour demander l'un OU l'autre...
("résultats de 'Base" où 'Structure' = Lamaze OU etat = 'Oui')
Voici un exemple complexe de = FILTRE ( base de données ; critères ; "si pas de resultat" )
=FILTRE ( baseADL ; (ESTNUM(CHERCHE(critere;baseADL[code]))+ESTNUM(CHERCHE(critere;baseADL[Contact])))*(baseADL[Type]=N1) ;"pas de resultat")
Le critère utilise 3 critères de... il cherche les résultats dans Champ 1 OU Champ 3 ET impose qu'1 champ 3 soit EXACTEMENT celui d'une cellule...
( ESTNUM(CHERCHE(critere;baseADL[code]) ) + ESTNUM(CHERCHE(critere;baseADL[Contact]) ) ) * (baseADL[Type]=N1)
IMPORTANT : Les 2 colonnes 'au choix' sont entre parenthèse.... et le 3eme critère
Cà dire : "les résultats qui contiennent OU ceci OU cela .... ET en plus la valeur N1 a un autre endroit"
( champ1 + champ2 ) * ( champ3 )
Remis en couloir, voici comment lire la formule complexe initiale...
= FILTRE ( base donnees ; ( champ1 + champ2 ) * ( champ3 ) ; "pas de résultats")
La technique ci dessous verra le tableau se filtrer en direct, à chaque caractère saisi... :-)
En temps réel... en "live"...
Elle utilise l'exemple 2 de la formule Filtre 'Contient' ci-dessus :
=FILTRE(base; ESTNUM(CHERCHE(B2; base[Structure])); "Aucun résultat")
Elle ajoute un champ "active X" (ATTENTION : le ruban Développement doit être affiché dans Excel)
Voici les étapes :
Insérer un champ de saisie active X de type 'Zone de texte',
puis tracez le champ sur la feuille...
Clic-droit / Propriétés sur le champ tracé
LinkedCell = entrer ici la référence de la cellule qui recevra la saisie en temps réel... (G2 dans l'exemple)
MultiLine = 'False'
Fermer le panneau des propriétés
Fermer le mode Création
Saisir enfin la formule de filtre qui ira piocher dans la cellule G2 là ou vous souhaitez vos résultats...
=FILTRE(base; ESTNUM(CHERCHE(G2; base[Structure])); "Aucun résultat")
ASTUCE : Pour un résultat plus esthétique
=> Placer le champ de saisie SUR la cellule G2 pour la masquer :-)
Il est possible qu'Excel tente de vous faire enregistrer le document comme s'il contenait des macros.
Vous pouvez laisser le document SANS macros :-)
(Active X n'a pas forcément besoin de macro pour fonctionner )
Il est parfaitement possible de combiner les 2 fonctionnalités de la manière suivante :
=RechercheV (ou RechercheX) peut opérer sur le résultat de =Filtre...
Schématiquement on à alors :
= RECHERCHEV( "Claire" ; FILTRE ( A2:A5 ; B2:B5 = "RH" ) ; FILTRE ( C2:C5; B2:B5="RH") )
= RechercheV ( valeur_cherchée ; base_cherchée ; valeur_retournée )
- base_cherchée sera ici non pas un tableau, mais le résultat d'un filtre
- valeurs_retournées sera ici une autre partie du tableau
La logique peut être difficile à apprivoiser, mais rend les fonctions RechercheV et X encore plus puissantes et dynamiques...
Dans la base de données vous pouvez AJOUTER 1 colonne "Cherche" (ou autre) qui se charge de concatener les champs à chercher et voir s'ils répondent à une condition
= ESTNUM ( CHERCHE( A1 ; CONCATENER([@code];[@[Nom usuel]];;[@liaison]))1
Explication
on CHERCHE le contenu de A1 dans une chaine CONCATENER
si 1 résultat est trouvé, une position (nombre) s'affiche
on teste si CHERCHE est un nombre, ce qui donne
si ESTNUM cette reCHERCHE, ca affiche VRAI
Cette méthode permet d'alléger la =FILTER, qui plus qu'à lire les résultats qui affichent VRAI dans 1 seule colonne de la base :-)
=FILTRE ( baseADL ; baseADL[Cherche]=VRAI ; "" )
A l'ouverture du document, Excel peut bloquer les fonctionnalités Active X (ou des macros) pour des raisons de sécurité...
Si un Avertissement de sécurité s'affiche en haut de l'écran, il faut le valider pour utiliser le fichier comme initialement conçu.
Ne valider un message de Sécurité QUE lorsque vous êtes certain qu'il s'agit d'un document de confiance.