XL 2016 simplification de fonction

[cakojo]

bonjour a tous,
je cherche une fonction simple qui me permettra de chercher differents mots contenus dans une colonne excel d'un onglet 2 dans une sequence de texte contenue dans une colonne d'un onglet 1 Mais de faire apparaitre en resultat le texte rechercher dans l'onglet2

j'ai trouver emis la formule suivante mais c'est super fastidieux et la liste des mots de recherche risque de s'allonger dans le temps

=SI(ESTERREUR(CHERCHE(Feuil1!$A$2;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$3;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$4;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$5;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$6;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$7;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$8;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$9;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$10;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$11;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$12;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$13;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$14;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$15;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$16;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$17;'analyse pmad'!J387));SI(ESTERREUR(CHERCHE(Feuil1!$A$18;'analyse pmad'!J387));"abs";Feuil1!A$18);Feuil1!A$17);Feuil1!A$16);Feuil1!A$15);Feuil1!A$14);Feuil1!A$13);Feuil1!A$12);Feuil1!A$11);Feuil1!A$5);Feuil1!A$10);Feuil1!A$8);Feuil1!A$7);Feuil1!A$6);Feuil1!A$5);Feuil1!A$4);Feuil1!A$3);Feuil1!A$2)


voici le fichier en question

merci d'avance

 

[sylvanu]

Bonjour Cakojo,
Un essai en PJ avec une fonction perso. C'est beaucoup plus lisible ! ,

 

[CISCO]

Bonjour

Tu peux faire dans K2, avec les formules matricielles suivantes (à valider avec Ctrl+maj+entrer)

SIERREUR(INDEX(Feuil1!A$1:A$17;EQUIV(VRAI;NBCAR(SUBSTITUE(MINUSCULE('analyse pmad'!J2);MINUSCULE(Feuil1!A$1:A$17);""))<NBCAR(J2);0));"")

ou encore

SIERREUR(INDEX(Feuil1!A$1:A$17;EQUIV(VRAI;ESTNUM(CHERCHE(Feuil1!A$1:A$17;'analyse pmad'!J2));0));"")

@ plus

P.S : Si le texte dans la cellule Jx contient plusieurs mots de la plage Feuil1!A1:A17, ces formules ne ramènent que le premier.
P.S : Ces formules raméneront peut-être parfois installation alors que le texte contient réinstallation. Si c'est le cas, il faudrait faire en plus avec des " "&. A peaufiner donc.

 

[JHA]

Bonjour à tous,

Avec un code VBA, lancer la macro en cliquant sur le bouton.

JHA

 

[laurent3372]

Bonjour,

J'ai un peu remanié la feuille. J'ai recopié (avec transposition) le tableau des mots-clé en L1:AB1
dans les lignes en-dessous, j'ai mis la formule(étirable) :

=NON(ESTERREUR(CHERCHE(L$1;$J2)))

qui renvoie VRAI si le mot-clé a été trouvé en colonne J.
En K2, j'ai mis la formule (étirable aussi):

=SIERREUR(INDEX($L$1:$AB$1;EQUIV(VRAI;$L2:$AB2;0));"abs")

qui renvoie le libellé du premier mot-clé trouvé.
On n'a plus besoin de la feuille Feuil1.
Les colonnes L à AB peuvent être masquées pour améliorer la lisibilité du tableau.

Cordialement,
--
LR

 

[CISCO]

Rebonjour

Une autre solution mettant en évidence au max 3 mots, et pas uniquement le premier

SIERREUR(INDEX(Feuil1!A$1:A$17;MIN(SI(NBCAR(SUBSTITUE(MINUSCULE('analyse pmad'!J2);MINUSCULE(Feuil1!A$1:A$17);""))<NBCAR(J2);LIGNE($1:$17);1000)));"")
&" / "&SIERREUR(INDEX(Feuil1!A$1:A$17;PETITE.VALEUR(SI(NBCAR(SUBSTITUE(MINUSCULE('analyse pmad'!J2);MINUSCULE(Feuil1!A$1:A$17);""))<NBCAR(J2);LIGNE($1:$17);1000);2));"")
&" / "&SIERREUR(INDEX(Feuil1!A$1:A$17;PETITE.VALEUR(SI(NBCAR(SUBSTITUE(MINUSCULE('analyse pmad'!J2);MINUSCULE(Feuil1!A$1:A$17);""))<NBCAR(J2);LIGNE($1:$17);1000);3));"")

toujours en matriciel

@ plus

 

[mapomme]

Bonsoir @cakojo et bienvenue sur XLD ,

Un point important => Votre fichier n'est pas anonymisé . Il comporte des données personnelles comme des noms et prénoms de personnes. Ceci est contraire à la charte du forum et au RGPD.

Il vaut mieux supprimer votre fichier et le remplacer par un fichier où ne figurera aucune donnée personnelle.

A+

 

[cakojo]

Merci à tous pour votre aide
Vais jeter un œil dessus sur un pc
Bonne soiree

 

[CISCO]

Bonjour

Et pour ramener au max 3 mots, on peut utiliser la formule matricielle suivante

SIERREUR(INDEX(Feuil1!A$1:A$17;PETITE.VALEUR(SI(ESTNUM(CHERCHE(Feuil1!A$1:A$17;'analyse pmad'!J2));LIGNE($1:$17);"");1));"")
&" / "&SIERREUR(INDEX(Feuil1!A$1:A$17;PETITE.VALEUR(SI(ESTNUM(CHERCHE(Feuil1!A$1:A$17;'analyse pmad'!J2));LIGNE($1:$17);"");2));"")
&" / "&SIERREUR(INDEX(Feuil1!A$1:A$17;PETITE.VALEUR(SI(ESTNUM(CHERCHE(Feuil1!A$1:A$17;'analyse pmad'!J2));LIGNE($1:$17);"");3));"")

@ plus

 

[cakojo]

Bonjour Cakojo,
Un essai en PJ avec une fonction perso. C'est beaucoup plus lisible ! ,

merci c'est super!
la liste de rerchercheest elle limitée a la colonne ou a un nobre d'arguments specifiques?

 

[sylvanu]

Bonjour,
Au démarrage, je prends la liste et la range dans un array. Donc pas de limitation.
Mais plus elle sera grande plus ça sera lent !
Je viens de tester avec une liste de 200 mots, cela ne pose pas de problème.

 

[cakojo]

Bonjour à tous,

Avec un code VBA, lancer la macro en cliquant sur le bouton.

JHA

merci que fait la comande vba en fait?
elle cherche des mot et les fait aparraitre a cote?
est il possible d'implementer la liste des mots recherchés?

 

[sylvanu]

A l'ouverture du fichier ou par appui sur le bouton de mise à jour, on fait :

Public ListeTravaux()   ' Déclare un array en Public pour être disponible en permanence
Sub ConstruitListe()
' Calcule le nombre d'éléments dans la liste
     Taille = ThisWorkbook.Sheets("Listes").Range("A:A").End(xlDown).Row
' Redimensionne l'array en fonction du nombre d'éléments trouvés
     ReDim ListeTravaux(Taille)
' La taille est mis dans Array(0) pour accélérer les calculs ( inutile de recalculer à chaque fonction )
     ListeTravaux(0) = Taille   ' La taille de la liste est en 0, les mot cherchés en 1...x
' Remplit l'array avec tous les mots trouvés.
     For i = 1 To Taille
        ListeTravaux(i) = [Liste].Cells(i, 1)
     Next i
End Sub

Ensuite la fonction CherchePmad(A) fait :

Function CherchePmad(A)
' On positionne le retour à Absent, ce qui évite ensuite le Else, donc plus rapide.
CherchePmad = "Absent"
' Pour chaque mot de la liste
For i = 1 To ListeTravaux(0)
' On regarde si le mot est présent dans la phrase "A" passé en paramètre de la fonction.
    If A Like "*" & ListeTravaux(i) & "*" = True Then
' Si elle est présente, on renvoie le mot de la liste
        CherchePmad = ListeTravaux(i)
' Et on sort car c'est fini. Inutile de perdre du temps à aller jusqu'au bout.
        Exit For
    End If
' Tout ça jusqu'au dernier mot de la liste.
Next i
End Function

est il possible d'implementer la liste des mots recherchés?

Vous voulez dire implémenter dans le VBA ?
Oui bien sur on peut le faire, mais ça vous oblige à aller modifier le VBA lorsque vous voulez agrandir la liste.
Il suffit de remplir l'array "à la main" plutôt que d'aller le lire.
On gagne une feuille, mais c'est plus complexe à updater. Question de choix.

 

[cakojo]

Bonjour Cakojo,
Un essai en PJ avec une fonction perso. C'est beaucoup plus lisible ! ,

il y a un truc de bizarre car par moment le mot "installation" est trouvé et pas d'autre fois

43553,65139​	43553,65972​	13​	0​	Euro	Facture	102655 SCM PO2 POLE OISE OPHTALMOLOGIE Installation logiciel CR1	installation		Absent
43537,60347​	43537,61042​	11​	0​	Euro	Facture	103754 Luc ernod Aide réinstallation eyesuite: tout fonctionne	installation		installation
43523,475​	43523,48472​	15​	0​	Euro	Facture	103995 SCM CENTRE D'OPHTALMOLOGIE L'adresse ip de leur poste biomètre avait changé, tout refonctionne	IP		Absent

 

[cakojo]

A l'ouverture du fichier ou par appui sur le bouton de mise à jour, on fait :

Public ListeTravaux()   ' Déclare un array en Public pour être disponible en permanence
Sub ConstruitListe()
' Calcule le nombre d'éléments dans la liste
     Taille = ThisWorkbook.Sheets("Listes").Range("A:A").End(xlDown).Row
' Redimensionne l'array en fonction du nombre d'éléments trouvés
     ReDim ListeTravaux(Taille)
' La taille est mis dans Array(0) pour accélérer les calculs ( inutile de recalculer à chaque fonction )
     ListeTravaux(0) = Taille   ' La taille de la liste est en 0, les mot cherchés en 1...x
' Remplit l'array avec tous les mots trouvés.
     For i = 1 To Taille
        ListeTravaux(i) = [Liste].Cells(i, 1)
     Next i
End Sub

Ensuite la fonction CherchePmad(A) fait :

Function CherchePmad(A)
' On positionne le retour à Absent, ce qui évite ensuite le Else, donc plus rapide.
CherchePmad = "Absent"
' Pour chaque mot de la liste
For i = 1 To ListeTravaux(0)
' On regarde si le mot est présent dans la phrase "A" passé en paramètre de la fonction.
    If A Like "*" & ListeTravaux(i) & "*" = True Then
' Si elle est présente, on renvoie le mot de la liste
        CherchePmad = ListeTravaux(i)
' Et on sort car c'est fini. Inutile de perdre du temps à aller jusqu'au bout.
        Exit For
    End If
' Tout ça jusqu'au dernier mot de la liste.
Next i
End Function

Vous voulez dire implémenter dans le VBA ?
Oui bien sur on peut le faire, mais ça vous oblige à aller modifier le VBA lorsque vous voulez agrandir la liste.
Il suffit de remplir l'array "à la main" plutôt que d'aller le lire.
On gagne une feuille, mais c'est plus complexe à updater. Question de choix.

ok merci pour les explication
la modification de la liste se fera ds l'array