XL 2013 Créer un moteur de recherche sur un fichier

[Edmond_Dantes]

Bonjour à tous,

Je sais que le sujet est souvent évoqué et j'ai passé une bonne dizaines d'heures de recherche sur la toile mais toujours sans succès.

Contexte:
Je travaille dans la ressource humaine dans l'armée.
On utilise beaucoup de tableaux Excel dans tous les sens et sans aucune connaissances je me suis lancé dans la création d'un Dashboard pour simplifier mon travail et celui de mes collègues.
Je suis assez satisfait du résultat mais je bute sur un problème majeur:

Dans le fichier en pj (Exemple EPH) je souhaiterai pouvoir créer, dans un autre onglet, un formulaire de recherche.
Le but est de pouvoir faire une recherche sur la colonne QUALIF (AQ), mais idéalement le top serait qu'on taperai une suite de caractères, par exemple le nom d'un marin, et sa ligne apparaitrait (en simplifié on ne mettrait pas toutes les colonnes dans les résultats de la recherche.
Idem si je tape en recherche par exemple: RECOM (colonne AK: spécialité), le résultat m'afficherai tous les marins de cette spécialité. Etc.
Je ne sais pas si mon explication est assez claire mais n'hésitez pas à me demander des précisions, je reste disponible pour les bonnes âmes qui voudraient bien m'aider.

Nota:
- Pour des raisons évidentes j'ai dû rendre les informations anonymes
- Le tableau en PJ est un fichier à part normalement. Mon Dashboard est dans un fichier séparé avec des TCD pointant vers le fichier EPH (qui est hébergé sur le réseau informatique de la marine)
- Je souhaiterai pouvoir intégrer le champ de recherche sur le Dashboard à terme (je devrais pouvoir me dépatouiller tout seul pour l'intégration une fois que le moteur de recherche est fonctionnel)

En vous remerciant d'avance pour votre aide !

 

[Usine à gaz]

Bjr Cousinhub
Merci d'avoir répondu à ce fil.
Perso, je n'utilise pas Power Query = donc qui c'est celui-là ? lol
Important : Edmond_Dantes est Mac.

 

[Cousinhub]

Bjr Cousinhub
Merci d'avoir répondu à ce fil.
Perso, je n'utilise pas Power Query = donc qui c'est celui-là ? lol
Important : Edmond_Dantes est Mac.

Hello Lionel
Power Query est une fonctionnalité qui existe sur Excel depuis 2016 en natif, et qui permet tout plein de chose...
Et pour le MAC, si effectivement, il l'utilise, par contre, je sais qu'au boulot, il a 2016
Bonne soirée

 

[Edmond_Dantes]

Bonjour,
Une alternative, utilisant Power Query (en natif depuis Excel 2016) - établie en partenariat avec un ami (merci à lui pour le code de recherche et de restitution des critères)
La plage de la base de données a été transformée en Tableau Structuré.
Dans l'onglet "Travail", j'ai pour le moment extrait les différentes années de DFA, les différentes Qualifs, les spés (de la personne, et non spés nominales) et les noms (ainsi que les colonnes sur lesquelles seront effectuées les recherches)
Ces données seront bien sûr adaptables à tes besoins.
Dans l'onglet "Extract", tu as un tableau de recherche en haut (pour le moment limité à 2 critères), tu sélectionnes la colonne de recherche en D, le genre de recherche en E, la valeur en F et le critère "et/ou" en G.
Tu cliques sur le bouton "Cherche", et tu obtiens l'extraction de tes filtres.
J'ai sélectionné quelques colonnes "intéressantes" à obtenir dans cette extraction, mais on peut bien évidemment modifié cette sélection (ajout, suppression...)
Le bouton "Raz" sert à ...(devine...)
Tu me dis si c'est la bonne voie, auquel cas, quelles adaptations tu voudrais.
Bonne apm, et bon W-E

Bon et merci pour cette option !
Je regarde tout de suite. Cependant ma base de donnée ne pourra pas être transformée en tableau structuré, j'ai peur que ce soit bloquant

 

[Hasco]

Bonsoir,
Hello @Cousinhub dit Bh² (ou l'inverse)

Cependant ma base de donnée ne pourra pas être transformée en tableau structuré, j'ai peur que ce soit bloquant

Ce ne sera bloquant que si le tableau résultat ne peut être lui non plus un tableau structuré.
Dans le fichier joint j'ai simplement re transformer la base en Plage de cellules que j'ai renommée "DataSource", les requêtes font référence à ce nom.

Il n'est peut-être pas utile de conserver toutes les options de la liste des modes de recherches si les validations de données des cellules de valeurs à chercher en restreintl'utilisation.

Par exemple on ne peut pas choisir "Commence par" ou "Contient" comme mode de recherche sur le champ "Nom" puisque la cellule de la valeur à chercher est contrainte au nom entier!
Idem si on veut chercher quelqu'un qui possèderait plusieurs QUALIF, sauf à créer plusieurs lignes de recherches pour un unique champ comme dans l'exemple du fichier joint.

Soit il faut faire des validations qui le permettent, soit ne pas l'autoriser en supprimant l'option de la liste des Modes de recherche possibles.

A+

 

[Cousinhub]

Hi,
Salut L'ami
Un petit chef d’œuvre...
@ Edmond
Il est vrai que j'avais oublié que vous travaillez en réseau, et que ce fichier est partagé (d'où l'impossibilité de le transformer en TS, je présume...)
Donc, pour les listes de validation, comme on utilise aussi les TS, faudra revoir ce mode...
Je ne sais donc pas si l'option PQ est viable...(aussi bien pour la restitution, que pour la requête en elle-même..)
Chauffage de neurones...

 

[Edmond_Dantes]

Bonsoir,
Hello @Cousinhub dit Bh² (ou l'inverse)


Ce ne sera bloquant que si le tableau résultat ne peut être lui non plus un tableau structuré.
Dans le fichier joint j'ai simplement re transformer la base en Plage de cellules que j'ai renommée "DataSource", les requêtes font référence à ce nom.

Il n'est peut-être pas utile de conserver toutes les options de la liste des modes de recherches si les validations de données des cellules de valeurs à chercher en restreintl'utilisation.

Par exemple on ne peut pas choisir "Commence par" ou "Contient" comme mode de recherche sur le champ "Nom" puisque la cellule de la valeur à chercher est contrainte au nom entier!
Idem si on veut chercher quelqu'un qui possèderait plusieurs QUALIF, sauf à créer plusieurs lignes de recherches pour un unique champ comme dans l'exemple du fichier joint.

Soit il faut faire des validations qui le permettent, soit ne pas l'autoriser en supprimant l'option de la liste des Modes de recherche possibles.

A+

Bonjour Hasco et merci pour t'être penché sur le sujet.
Le résultat pourra être mis dans un tableau structuré, ça ce n'est pas gênant.
Pour les critères dans la maquette de mon Dashboard j'ai 3 emplacements pour des champs donc on doit pouvoir garder COLONNE / MODE et VALEUR sans soucis (je ne suis pas sûr d'avoir bien compris le champ lien)

 

[Hasco]

Re,

Le champ Lien sert à lier les critères de la ligne en cours avec ceux de la ligne suivante.
Par exemple je veux les enregistrements (lignes) dont la colonne "QUALIF" contient les Valeurs "EML2 ANDGLAIS" et "M APTE MANELEC"

le et pourrait être un OU , ce qui changerait tout

En enlevant la validation sur la colonne valeur, vous pourriez exprimer d'autre critère plus pointus,
par exemple mettre "ANGLAIS" ou "ELEC" qui renverraient toutes les lignes dont QUALIF contient ces chaînes de caractères.

Avec "Liste contient certains", vous pourriez chercher ceux dont certains mots de la liste sont contenu dans la QUALIF

Avec "Liste contient tous", vous auriez ceux dont la QUALIF contient tous les mots de la liste,
Pour accorder ces derniers avec vos séparateurs de liste "\, " (anti-slash, virgule, espace) je verrai ça demain*

*[Edit] Voilà qui est fait dans le fichier joint[/Edit]
vous donnera toutes les lignes dont QUALIF contient certains des mots (chaînes de caractères) contenus dans Valeur.
La liste des mots accepte la virgule, l'anti-slash ou l'espace comme séparateur.

Colonne	Mode	Valeur	Lien
QUALIF	liste contient certains	ANGLAIS\ELEC

 

[Edmond_Dantes]

Merci encore pour ces éléments de réponse, je comprends tout à présent.
Je pense qu'il n'est pas nécessaire d'aller trop loin au final et votre travail rempli déjà tous mes besoins !
J'aurai encore 2 dernières questions stp:
- Est-il possible que lorsqu'on clique sur RAZ ça n'affiche pas tous les résultats possibles mais que la zone des résultats reste vide ? (le tableau source contient plus de 1000 lignes...)
- Peux-tu m'orienter pour l'adapter à mon Dashboard ? J'ai regardé le VBA il est assez court je pense passer à côté de quelque chose, notamment comment lier le moteur de recherche au fichier externe.

Encore un grand merci à tous les 3 pour votre temps, c'est vraiment extra !

 

[Cousinhub]

Hello,
Pour la première question, on peut modifier le code "raz" ainsi :

Sub raz()
Application.ScreenUpdating = False
With Range("T_ParamsRecherche").ListObject.DataBodyRange
    .ClearContents
    .Item(1, 1) = "spécialité": .Item(1, 2) = "contient": .Item(1, 3) = "Vide"
End With
Range("T_Result").ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

(On effectue une recherche de spécialité égale à "vide")
Pour la 2ème question, je ne comprends pas "fichier externe"???
Est-ce à dire que le fichier que tu vas consulter est donc sur le réseau, et peut être déjà en consultation et/ou modification par quelqu'un d'autre?
Bonne journée
Bonne journée copain

 

[Cousinhub]

Re-,
Sinon, sans changer le code VBA, mais en modifiant la dernière étape dans la requête...
Bonne apm

 

[Edmond_Dantes]

Hello,
Pour la première question, on peut modifier le code "raz" ainsi :

Sub raz()
Application.ScreenUpdating = False
With Range("T_ParamsRecherche").ListObject.DataBodyRange
    .ClearContents
    .Item(1, 1) = "spécialité": .Item(1, 2) = "contient": .Item(1, 3) = "Vide"
End With
Range("T_Result").ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

e
(On effectue une recherche de spécialité égale à "vide")
Pour la 2ème question, je ne comprends pas "fichier externe"???
Est-ce à dire que le fichier que tu vas consulter est donc sur le réseau, et peut être déjà en consultation et/ou modification par quelqu'un d'autre?
Bonne journée
Bonne journée copain

Bonjour !
Oui c'est ça, le fichier est sur le réseau. Seulement 3 personnes le consultent et rarement en même temps mais oui ça peut arriver.
Si ça devient bloquant je peux voir à créer un dossier "DASHBOARD" par exemple, puis dedans un sous-dossier EPH (la bDD) où on ferai un CC du fichier à chaque modification.
C'est pas le l'idéal mais un CTRL C / V prend 2s donc c'est pas la mort non plus

 

[Cousinhub]

Re-,
A dire vrai, je ne sais pas si on peut utiliser une requête (i-e : aller récupérer les infos) dans un classeur en réseau, si celui-ci est ouvert (je suppose que le mode partagé est actif?)
Si un pro du réseau peut répondre..
Quoiqu'il arrive, si cela fonctionne, la requête ne récupérera que ce qui est enregistré (s'il y a eu des modifs par un collègue, et qu'il n'a pas enregistré, tu ne les verras pas)
Et il va falloir modifier le chemin du tableau dans la source de la requête, si ton "dashbord" ne contient pas ce tableau, mais ne fait que lire...
Bref, un peu de travail d'adaptation
PS, si, comme évoqué, le tableau de données se situe "en ligne", comment s'appelle l'onglet contenant ce tableau?

 

[Cousinhub]

Re-,
Plus propre, pour effacer la requête via le bouton raz (toujours, sans modifier la macro d'origine)
Le code que tu peux voir via l'éditeur avancé ressemble à ça, il suffit de modifier la dernière ligne "Filtr"

let
    Source = Excel.CurrentWorkbook(){[Name="DataSource"]}[Content],
    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"spécialité", type text}, {"DFA", type date}, {"Nom", type text}}),
    SupprCol = Table.SelectColumns(#"Type modifié",{"Unité Mère", "Unité Fille", "Numéro CREDO du poste", "Emploi organique", "Grade nominal", "Brevet nominal", "Spécialité#(lf)métier#(lf)nominal", "spécialité", "Nom", "QUALIF", "DFA"}),
    An = Table.AddColumn(SupprCol, "Année", each Date.Year([DFA])),
    TypeAn = Table.TransformColumnTypes(An,{{"Année", type text}}),
    Filtr = if Conditions<>"" then Table.SelectRows( TypeAn,   Expression.Evaluate( "each " & Conditions,#shared)) else Table.AlternateRows(TypeAn,0,0,0)
in
    Filtr

Notamment après le "else"
PS, je n'ai trouvé que cet artifice, je ne sais pas si c'est le top, mais ça fonctionne...
Bonne apm

 

[Hasco]

Hello,

Sinon il y a :

if .... else Table.RemoveRows(TypeAn,0,Table.RowCount(TypeAn))

Qui videra la table.
Peut-être que tester le résultat de "Conditions" pourrait être fait en amont et ne continuer la requête QUE s'il y a des critères de recherche.

Cordialement

 

[Hasco]

Re,

voici la requête avec embranchement de sortie :

et
    Source = Excel.CurrentWorkbook(){[Name="DataSource"]}[Content],
    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Suite = if Conditions<>""
        then 
            let 
                #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"spécialité", type text}, {"DFA", type date}, {"Nom", type text}}),
                SupprCol = Table.SelectColumns(#"Type modifié",{"Unité Mère", "Unité Fille", "Numéro CREDO du poste", "Emploi organique", "Grade nominal", "Brevet nominal", "Spécialité#(lf)métier#(lf)nominal", "spécialité", "Nom", "QUALIF", "DFA"}),
                An = Table.AddColumn(SupprCol, "Année", each Date.Year([DFA])),
                TypeAn = Table.TransformColumnTypes(An,{{"Année", type text}}),
                Filtr = Table.SelectRows( TypeAn,   Expression.Evaluate( "each " & Conditions,#shared)) 
            in Filtr
        else 
            let
                // Récupérer le nom des champs de la table 
                Champs = Record.FieldNames(#"En-têtes promus"{0}),
                // créer une liste de valeurs nulles de même longueur que la liste des champs
                Valeurs = List.Transform(Champs, each null)
            // et en faire une table.
            in #table(Champs,{Valeurs})

     
in
  Suite

En fait dans la structure if on pourrait aussi avoir 2 requêtes indépendantes au lieu de mettre tout dans la même.

if Conditions<>"" requête 1 else requête 2