XL 2016 simplification de fonction

cakojo · 20 Février 2020

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 · 20 Février 2020

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

CISCO · 20 Février 2020

Bonjour

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

Code:

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

Code:

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 · 20 Février 2020

Bonjour à tous,

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

JHA

laurent3372 · 20 Février 2020

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) :

VB:

=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):

Code:

=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 · 20 Février 2020

Rebonjour

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

Code:

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 · 20 Février 2020

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 · 20 Février 2020

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

CISCO · 21 Février 2020

Bonjour

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

Code:

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 · 21 Février 2020

sylvanu à dit:
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 · 21 Février 2020

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 · 21 Février 2020

JHA à dit:
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 · 21 Février 2020

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

Code:

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 :

Code:

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 · 21 Février 2020

sylvanu à dit:
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	Euro	Facture	102655 SCM PO2 POLE OISE OPHTALMOLOGIE Installation logiciel CR1	installation	Absent
43537,60347	43537,61042	11	Euro	Facture	103754 Luc ernod Aide réinstallation eyesuite: tout fonctionne	installation	installation
43523,475	43523,48472	15	Euro	Facture	103995 SCM CENTRE D'OPHTALMOLOGIE L'adresse ip de leur poste biomètre avait changé, tout refonctionne	IP	Absent

cakojo · 21 Février 2020

sylvanu à dit:

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

Code:

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 :

Code:

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

XL 2016 simplification de fonction

cakojo

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

CISCO

XLDnaute Barbatruc

JHA

XLDnaute Barbatruc

laurent3372

XLDnaute Impliqué

CISCO

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

cakojo

XLDnaute Nouveau

CISCO

XLDnaute Barbatruc

cakojo

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

cakojo

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

cakojo

XLDnaute Nouveau

cakojo

XLDnaute Nouveau

Discussions similaires