Utilisation de liste au lieu de données issues de la formule

[gdcobra91]

Bonjour,

Me revoila avec un pb encore non résolu.

Je voudrai remplacer la formule suivante:
=SI(NB.SI(C3;"*renault*");"*renault* - ";"") & SI(NB.SI(C3;"*citroen*");"*citroen* - ";"") & SI(NB.SI(C3;"*feu*vert*");"*centre*auto* - ";"") & SI(NB.SI(C3;"*concession*auto*");"*concession*auto* - ";"")

Au lieu de saisir les valeurs recherchées et les valeurs retournées, je souhaiterai que la formule utilisée ces 2 listes:
- l'une avec la liste des mots à rechercher
_ l'autre avec la liste des valeurs à écrire si le mot recherché est trouvé

La colonne "Données brutes" contient les données à traiter.
La colonne "Résultat" est le résultat souhaité avec la formule actuelle.

Je vous joints un fichier pour une meilleure compréhension.

Merci de votre aide.

 

[gdcobra91]

Re : Utilisation de liste au lieu de données issues de la formule

Petite précision: Je souhaiterai que la liste puisse être mise à jour (ajout/suppression d'éléments) sans que la formule soit changée.

Merci.

 

[ricard678]

Re : Utilisation de liste au lieu de données issues de la formule

Bonjour,

Pourquoi ne pas utiliser la fonction recherche?

PS : Je ne trouve pas le fichier

 

[gdcobra91]

Re : Utilisation de liste au lieu de données issues de la formule

Je ne sais pas l'utiliser

Le fichier est joint dans le précédent message.

Avez-vous toujours un pb d'accès à mon fichier?

 

[ricard678]

Re : Utilisation de liste au lieu de données issues de la formule

non j'ai bien récuperer le fichier.

Par contre je regarde comment résoudre ton Problème car il me semble que la fonction recherche fonctionne avec la valeur de la cellule stricte.
Dans ton cas si j'ai bien compris tu cherche si la cellule contient par exemple le mot feu et le mot vert mais il peux y avoir d'autre mot.

 

[ROGER2327]

Re : Utilisation de liste au lieu de données issues de la formule

Bonsoir à tous

Deux propositions avec des formules personnalisées et des plages nommées dynamiques.
​

ROGER2327
#4601

Lundi 23 As 138 (Saint Quincey, critique d'art, SQ)
5 Frimaire An CCXIX
2010-W47-4T19:44:49Z

 

[gdcobra91]

Re : Utilisation de liste au lieu de données issues de la formule

Merci pr ton analyse, ricard678.

Je confirme : une cellule peut répondre à plusieurs critères de recherche.

 

[gdcobra91]

Re : Utilisation de liste au lieu de données issues de la formule

Parfait ROGER2327. Merci pr tes 2 solutions. Du coup, je vais les utiliser toutes les 2 ;-)

 

[ricard678]

Re : Utilisation de liste au lieu de données issues de la formule

re,

voici le fichier que j'ai fait avec le nomage des zone de recherche et de resultat

 

[gdcobra91]

Re : Utilisation de liste au lieu de données issues de la formule

ROGER2327: Peux-tu m'expliquer ta fonction Decaler stp. Je ne comprends pas mes arguments que tu as utilisé.

Merci.

 

[ROGER2327]

Re : Utilisation de liste au lieu de données issues de la formule

Re…

[COLOR=DarkSlateGray][B]MAX((Feuil1!$F$2:$F$1002<>"")*LIGNE([/B][/COLOR][COLOR=DarkSlateGray][B]Feuil1![/B][/COLOR][COLOR=DarkSlateGray][B]$1:$1001))-1[/B][/COLOR]

renvoie la longueur de la liste liste des mots recherchés, soit 9 dans le classeur d'exemple.
Pour l'adapter à une autre liste, il suffit de modifier Feuil1!$F$2:$F$1002.
Pour le reste, un coup d'œil dans l'aide sur la fonction DECALER devrait suffire.
​

ROGER2327
#4607

Mardi 24 As 138 (Saint Berbiguier, martyr, SQ)
6 Frimaire An CCXIX
2010-W47-5T10:47:30Z

 

[gdcobra91]

Re : Utilisation de liste au lieu de données issues de la formule

Merci pour cette précision, c'est justement cette partie que je ne comprenais pas.

Pour autant, j'ai du mal à comprendre la fonction Max. Pourquoi tu utilises la multiplication?

Si tu n'as pas le tps de me répondre, ce n'est pas grave je vais chercher.

En revanche, j'aurai une autre requete à faire sur ce site. Serait-il possible d'associer un bouton macro afin d'éviter que Excel recalcule en permanence dès lors que l'on change le contenu de la liste.

Merci par avance.

 

[gdcobra91]

Re : Utilisation de liste au lieu de données issues de la formule

Avec bcp de données, le fichier recalcul a chaque modification de la liste. Meme pire, il recalcule en modifiant une cellule qui ne devrait pas engendrer de recalcul.

Pouvez-vous m'aider svp.

Merci.

 

[ROGER2327]

Re : Utilisation de liste au lieu de données issues de la formule

Re...

[COLOR=DarkSlateGray][B]MAX((Feuil1!$F$2:$F$1002<>"")*LIGNE(Feuil1!$1:$1001))-1[/B][/COLOR]

Cette formule est matricielle. Ce qu'il faut savoir, c'est qu'elle équivaut à

[COLOR=DarkSlateGray][B]MAX((Feuil1!$F$2<>"")*LIGNE(Feuil1!$1:$1);(Feuil1!$F$3<>"")*LIGNE(Feuil1!$2:$2);(Feuil1!$F$4<>"")*LIGNE(Feuil1!$3:$3);  ... ;(Feuil1!$F$[/B][I](n+1)[/I][B]<>"")*LIGNE(Feuil1!$[/B][I]n[/I][B]:$[/B][I]n[/I][B]); ...  ;(Feuil1!$F$23<>"")*LIGNE(Feuil1!$1001:$1001);(Feuil1!$F$1002<>"")*LIGNE(Feuil1!$1001:$1001))-1[/B][/COLOR]

qui serait beaucoup trop longue à écrire en entier (il faudrait répéter mille et une fois

(Feuil1!$F$(n+1)<>"")*LIGNE(Feuil1!$n:$n);
​

en remplaçant n par 1, 2, 3, ... , 1000, 1001).

Lorsque, pour n variant de 1 à 1001, Feuil1!$F$(n+1)<>"" renvoie VRAI, (Feuil1!$F$(n+1)<>"")*LIGNE(Feuil1!$n:$n) renvoie n.
Lorsque, pour n variant de 1 à 1001, Feuil1!$F$(n+1)<>"" renvoie FAUX, (Feuil1!$F$(n+1)<>"")*LIGNE(Feuil1!$n:$n) renvoie 0.
Donc MAX((Feuil1!$F$2:$F$1002<>"")*LIGNE(Feuil1!$1:$1001)) renvoie la plus grande valeur de n telle que Feuil1!$F$(n+1)<>"" renvoie VRAI.

Si, par exemple, la dernière cellule différente de "" est F502, cette valeur est 501. Comme on compte le titre en F2 comme un des items, cela veut dire que le la liste compte 500 items. Ce pourquoi on écrit MAX((Feuil1!$F$2:$F$1002<>"")*LIGNE(Feuil1!$1:$1001))-1.
Si la liste est vide, à l'exception de la ligne de titre, seul Feuil1!$F$2<>"" renvoie VRAI, donc MAX((Feuil1!$F$2:$F$1002<>"")*LIGNE(Feuil1!$1:$1001)) renvoie 1, et MAX((Feuil1!$F$2:$F$1002<>"")*LIGNE(Feuil1!$1:$1001))-1 renvoie 0.

Si on se limitait à une liste contenant au plus quatre valeurs, on pourrait écrire aussi bien
MAX((Feuil1!$F$2:$F$6<>"")*LIGNE(Feuil1!$1:$5))-1 (formule matricielle)
ou
MAX((Feuil1!$F$2<>"")*LIGNE(Feuil1!$1:$1);(Feuil1!$F$3<>"")*LIGNE(Feuil1!$2:$2);(Feuil1!$F$4<>"")*LIGNE(Feuil1!$3:$3);(Feuil1!$F$5<>"")*LIGNE(Feuil1!$4:$4);(Feuil1!$F$6<>"")*LIGNE(Feuil1!$5:$5))-1 (formule "ordinaire")
ou même, plus simplement,
MAX((Feuil1!$F$2<>"")*1;(Feuil1!$F$3<>"")*2;(Feuil1!$F$4<>"")*3;(Feuil1!$F$5<>"")*4;(Feuil1!$F$6<>"")*5)-1 (formule "ordinaire")
ou son équivalent matriciel
MAX((Feuil1!$F$2:$F$6<>"")*{1;2;3;4;5})-1
Ce dernier exemple est illustré dans le classeur joint.

Pour ce qui est de la question sur le recalcul, essayez ceci :

• Supprimez

  [COLOR=DarkSlateGray][B]Application.Volatile[/B][/COLOR]

  dans les fonctions.

• Ajoutez un bouton avec la ligne de code

    [COLOR=DarkSlateGray][B]Application.CalculateFull[/B][/COLOR]

ROGER2327
#4609

Mardi 24 As 138 (Saint Berbiguier, martyr, SQ)
6 Frimaire An CCXIX
2010-W47-5T14:39:22Z

 

[gdcobra91]

Re : Utilisation de liste au lieu de données issues de la formule

Merci pour vos explications, ROGER2327.

Pour l'utilisation du bouton, dois-je écrire ce code:
"
Private Sub CommandButton2_Click()
Application.CalculateFull
recherche1 (r)
recherche2 (r)
End Sub
"
?

Mais cela ne marche pas. En incorporant les fonctions à l'intérieur:
"
Private Sub CommandButton2_Click()
Application.CalculateFull
Function recherche1(r)
Application.Volatile
Dim i&, tmp$
For i = 1 To Range("Liste").Count
If LCase(CStr(r & " ")) Like LCase(Range("Liste").Cells(i, 1).Value) Then tmp = tmp & "*" & Range("Valeur").Cells(i, 1).Value & "* - "
Next i
recherche1 = tmp
End Function
End Sub
"
Ca ne marche pas non plus?

Pouvez-vous m'aider svp.

Rappel au cas où: je souhaite que le recalcul effectué par Excel ne se fasse uniquement lors que j'appuie sur le bouton.