Fixer une condition OU dans formule matricielle (condition sur texte)

[ED31]

Bonjour,

Une formule matricielle doit récupérer les numéros de lignes d'un tableau pour lequel le champ "Ch_tags" contient des chaînes de caractères, Val1 et/ou Val2. Val1 et Val2 sont des cellules nommées, où se trouve les valeurs de critères de sélection.
Je prévois deux versions
- version condition ET :

=GRANDE.VALEUR(ESTNUM(CHERCHE(Val1;Ch_tags;1))*ESTNUM(CHERCHE(Val2;Ch_tags;1))*(LIGNE(Ch_tags));LIGNE()). (avec accolades, bien sûr)

Fonctionne bien : renvoie les lignes satisfaisant au critère pour lequel les chaînes Val1 et Val2 sont trouvées dans la cellule de Ch_tags correspondante. Les x lignes sont renvoyées par ordre décroissant dans les x lignes de la plage où est entrée la formule matricielle.

- version condition OU :

Les choses se corsent !
J'ai essayé diverses formules pour évaluer la présence de Val1 OU de Val2 dans le vecteur Ch_tags.
Sans succès.
=GRANDE.VALEUR((ESTNUM(CHERCHE(Val1;Ch_tags;1))+ESTNUM(CHERCHE(Val2;Ch_tags;1)))*(LIGNE(Ch_tags));LIGNE()).

Problème: si un des membres de la somme renvoie une erreur, la somme n'est pas évaluée comme VRAI et la condition OU est mal traduite. Il faudrait renvoyer 1 au moins si une des deux chaînes est présente.

Autre idée, mais ne marche pas :
=GRANDE.VALEUR((MAX(SI(ESTNUM(CHERCHE(Val1;Ch_tags;1));1;0);SI(ESTNUM(CHERCHE(Val2;Ch_tags;1));1;0)))*(LIGNE(Ch_tags));LIGNE())

Auriez-vous une idée de la façon d'exprimer la condition OU ?
A moins qu'en amont, il y ait une autre façon de construire la fonction matricielle !
Je sais que les fonctions matricielles seront très lentes (tableau de 20 000 lignes) et que VBA serait plus adapté.
Mais j'aimerais trouver une solution.
Merci à tous,

 

[Modeste]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Bonsoir ED31, le forum,

ED31, je t'assure, quand on dit qu'avec un fichier exemple, c'est plus facile:

1. de comprendre
2. de faire des tests avant de proposer une piste

... c'est tout à fait sérieux, pas une blague de potache!

Si c'est le n° de la dernière ligne où figure Val1 ou Val2, tu peux essayer:

=GRANDE.VALEUR(SI(ESTNUM(CHERCHE(VAL_1;Ch_tags;1))+ESTNUM(CHERCHE(VAL_2;Ch_tags;1));LIGNE(Ch_tags));LIGNES($1:1))

Valider avec Ctrl+Shift+Enter, puisque formule matricielle.

Les plages, je les ai nommées VAL_1 et VAL_2 (sans le'_' ça correspond à une référence de cellule!?)

Il est, comme tu le disais, peut-être possible de modifier ... mais comme on ne sait absolument pas quelles sont les valeurs contenues dans Ch_tags, on peut difficilement imaginer autre chose (si tu vois ce que je veux dire)

 

[mapomme]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Bonne année ED3, Modeste

Modeste (que je salue) a tiré plus vite que mapomme . J'ai construit un fichier tout comme lui et tout comme Modeste j'ai appelé les variables à rechercher Val_1 et Val_2 . Un fichier exemple fourni nous aurai évité de prendre du temps pour en construire un .

La formule matricielle (à valider par Ctrl+Shift+Enter) utilisée en F1 et à tirer vers le bas est la suivante :

=GRANDE.VALEUR(((ESTNUM(CHERCHE(Val_1;Ch_tags;1))+ESTNUM(CHERCHE(Val_2;Ch_tags;1)))>0)*(LIGNE(Ch_tags));LIGNE())

 

[ED31]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Merci, Modeste et Mapomme pour vos réponses !

Je m'excuse mais j'avais des contraintes pour sortir un fichier exemple et je n'ai pas Excel d'où je vous écris.
Précisions :
Ch_tags est une plage verticale nommée qui contient des chaînes de textes (succession de mots-clés maison récupérés depuis un outil de bookmarking), d'environ 20 0000 lignes (Ca va pédaler !).
Extraits :
Dollar; inflation; MAR_MAT; US_ECO, US_DEMO
Rouble; inflation; MAR_MAT; US_ECO, dollar, RU_ECO, Putine
Dollar, chômage, FR_ECO
.......................................


Je souhaite rechercher les lignes des cellules de Ch_tags qui comprennent les deux chaînes :
Val_1 = "Rouble" OU Val_2 = "Dollar"

J'arrive facilement à récupérer les lignes des cellules de Ch_tags
Val_1 = "Rouble" ET Val_2 = "Dollar". (J'ai même testé la formule en imposant la présence de quatre valeurs , .... Val_3, Val_4).

Merci pour votre appui bienveillant.
Je vais tester la formule.

Bonne année ED3, Modeste

Modeste (que je salue) a tiré plus vite que mapomme . J'ai construit un fichier tout comme lui et tout comme Modeste j'ai appelé les variables à rechercher Val_1 et Val_2 . Un fichier exemple fourni nous aurai évité de prendre du temps pour en construire un .

La formule matricielle (à valider par Ctrl+Shift+Enter) utilisée en F1 et à tirer vers le bas est la suivante :

=GRANDE.VALEUR(((ESTNUM(CHERCHE(Val_1;Ch_tags;1))+ESTNUM(CHERCHE(Val_2;Ch_tags;1)))>0)*(LIGNE(Ch_tags));LIGNE())

 

[mapomme]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Re ED31,

Si les temps de calcul sont trop longs (vous nous direz), on pourrait envisager d'étudier une solution en VBA.

 

[Modeste]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Bonjour ED31,
Salut mapomme

Même si ça "en jette" un peu moins, on pourrait aussi utiliser une colonne supplémentaire, pour éviter les formules matricielles.

 

[ED31]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Merci Modeste !

Ta formule marche bien.
J'en étais très proche et cela ne fonctionnait pas.

Je suis étonné par la présence de blancs entre Grande.Valeur et les parenthèses +1 et +2. et +3
Effectivement, sans les blancs, je ne peux éditer la formule chez moi (Curieux: cf formule ET qui fonctionne sans ces blancs !)

Ta formule:
=GRANDE.VALEUR( ( ( ESTNUM(CHERCHE(Val_1;Ch_tags;1))+ESTNUM(CHERCHE(Val_2;Ch_tags;1)) ) > 0)*(LIGNE(Ch_tags));LIGNE())

J'ai encore à régler : si pas de ligne trouvée (deux valeurs texte recherchées absentes l'une et l'autre du champ, il renvoit zéro (normal). Mais il trouve une valeur pour le champ Z correspondant à ligne trouvée (obtenu par INDEX, le no de ligne étant alors de zéro !). Bizarre

Qu'entends-tu par colonnne supplémentaire pour éviter le matriciel ?

Bonjour ED31,
Salut mapomme

Même si ça "en jette" un peu moins, on pourrait aussi utiliser une colonne supplémentaire, pour éviter les formules matricielles.

 

[Modeste]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Re,

Je suis étonné par la présence de blancs entre Grande.Valeur et les parenthèses +1 et +2. et +3

Cette formule-là, c'est celle de ... mapomme (non je ne suis pas schizophrène: je parle du mapomme en un mot!)
Dans son fichier, tu observeras que le format des cellules où cette formule figure est un format personnalisé!

En ce qui concerne la proposition de ma pomme (oui, là c'est bien de moi qu'il s'agit ) une colonne supplémentaire permettrait d'afficher les n° de ligne où figurent Val_1 ou Val_2; les autres fomules (qui permettent d'afficher la liste des lignes) ne devraient plus être des formules matricielles ... et donc le temps de recalcul serait moins impacté (me semble-t-il)
... mais si tu veux une illustration, fournis-nous un bout de fichier (je ne vais pas profiter honteusement de travail de mapomme tu vois lequel?)

 

[ED31]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Bonjour à Ma Pomme et à Modeste (excuses pour la confusion tout à l'heure).
Voici un extrait de fichier (1000 lignes)
La méthode que vous me proposez permettrait-elle d'aller plus vite pour récupérer les données : car j'aurai environ 4 tableau (formules matricielles sur une vingtaine de cellules) qui filtreront une base de, disons, 20 000 enregistrements.

En fait, ma priorité actuelle c'est d'actualiser en temps réel cette base dans le fichier EXCEL, en lançant une procédure depuis la page web que je consulte (sous Chrome). Pas coton ! Actuellement, je dois faire une exportation manuelle depuis le logiciel de bookmarking. Bon, là il va falloir sortir d'Excel (pas possible d'appeler Excel depuis une page Web, surtout sous Chrome semble-t-il.

Merci encore pour votre coup de main.

 

[ED31]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Voici le fichier que j'ai oublié !

 

[Modeste]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Bonsoir,

Je laisserai le soin à mapomme de proposer une solution éventuelle en VBA Il fera ça mieux que moi (comme les formules avec des espaces entre les parenthèses, associées à un format personnalisé !)
Ce que je proposais (mais c'est à tester, bien sûr!) c'est ce qui suit:
...

• en G11 (par exemple) de la feuille Data:

  =SI(ESTNUM(CHERCHE(Val_1;C11))+ESTNUM(CHERCHE(Val_2;C11));LIGNE();"")

  ... à recopier vers le bas
  ...
• en F13 (toujours par exemple) de la feuille Filtre:

  =INDEX(Data!C:C;GRANDE.VALEUR(Data!G:G;LIGNES($1:1)))

  ... valider "normalement" et recopier vers le bas aussi

Attention: le résultat obtenu sera différent du tien (mais je pense que l'erreur est dans ta formule: comme dernier paramètre de GRANDE.VALEUR, tu as utilisé LIGNE() ... or ta première formule, en colonne C est en ligne 13. Le résultat est donc la 13[SUP]e[/SUP] plus grande valeur, puis la 14[SUP]e[/SUP], etc.!!)

... À tester, j'insiste (puis laisser le temps à une proposition alternative de venir s'ajouter)

 

[ED31]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Bonjour Modeste,

Merci pour votre proposition qui fonctionne parfaitement.
Je l'ai inséré dans mon classeur en nommant les plages.

Mais j'ai encore des points qui me tracassent,

Dans la feuille Data, j'ai essayé des alternatives*pour la plage déterminant les no de lignes à récupérer et traiter par Grande.valeur (voir fichier),
-Insérer le vecteur de recherche à partir de la formule de test que vous proposez (addition des ESNUM(Val) comme formule matricielle*: pas de problème.
-Une évaluation avec OU (Esnum(Val);Esnum(Val)*: ne fonctionne pas correctement sous forme matricielle. Pourquoi*?
- Enfi, j'ai testé une évaluation avec des Si imbriqués (pas très joli!), sous forme matricielle qui donne le même résultat que la formule additive.

Ainsi, et logiquement, dans l'autre feuille, on pourra constater que ma formule matricielle (complètement matricielle sans faire créer une plage d'évaluation, utilisant ce dernier test sur la colonne des tags) donne les mêmes résultats que votre formule.

Rassurant, mais je suis troublé !
Quand aucune des chaînes recherchées n'est présente (exemple: Val_1=»Marcel» et Val_2=»Cerdan», votre formule renvoie une erreur NOMBRE*! Normal*; on doit pouvoir tester et renvoyer un blanc, mais ce n'est pas un problème*!

Par contre, mon évaluation donne zéro dans ce cas (normal).
Mais l'index sur ce numéro de ligne, soit zéro, pour récupérer le champ «*tags donne une valeur*, ce qui est aberrant... et inquiétant

Le classeur doit être surchargé de formules de calcul et arriver à saturation... comme moi !

Vous aurez sûrement des éclaircissements à m'apporter.
Merci

Sorry: je travaille en L1C1, addict depuis Multiplan. J'espère que cela ne pose pas de problèmes.

Bonsoir,

Je laisserai le soin à mapomme de proposer une solution éventuelle en VBA Il fera ça mieux que moi (comme les formules avec des espaces entre les parenthèses, associées à un format personnalisé !)
Ce que je proposais (mais c'est à tester, bien sûr!) c'est ce qui suit:
...

• en G11 (par exemple) de la feuille Data:

  =SI(ESTNUM(CHERCHE(Val_1;C11))+ESTNUM(CHERCHE(Val_2;C11));LIGNE();"")

  ... à recopier vers le bas
  ...
• en F13 (toujours par exemple) de la feuille Filtre:

  =INDEX(Data!C:C;GRANDE.VALEUR(Data!G:G;LIGNES($1:1)))

  ... valider "normalement" et recopier vers le bas aussi

Attention: le résultat obtenu sera différent du tien (mais je pense que l'erreur est dans ta formule: comme dernier paramètre de GRANDE.VALEUR, tu as utilisé LIGNE() ... or ta première formule, en colonne C est en ligne 13. Le résultat est donc la 13[SUP]e[/SUP] plus grande valeur, puis la 14[SUP]e[/SUP], etc.!!)

... À tester, j'insiste (puis laisser le temps à une proposition alternative de venir s'ajouter)

 

[Modeste]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Sorry: je travaille en L1C1, addict depuis Multiplan. J'espère que cela ne pose pas de problèmes.

Du tout: c'est par habitude que je suis repassé en notation A1, en simple adepte des bons plans
Par contre, je ne suis pas certain (est-ce lié ou non?) que tu aies utilisé les formules que je proposais?
En L11C4 de la feuille Data, ma proposition devenait

=SI(ESTNUM(CHERCHE(Val_1;LC(-1)))+ESTNUM(CHERCHE(Val_2;LC(-1)));LIGNE();"")

En feuille Filtre, quelque part en ligne 11

=SIERREUR(INDEX(Data!C(1);GRANDE.VALEUR(Nolig;LIGNES(L1:L(-10))));"")

Cette version-ci empêcherait l'apparition d'un message d'erreur, en cas de non-concordance.

Vous aurez sûrement des éclaircissements à m'apporter

Alors là, mon pauvre ami, j'ai des doutes: ce que je peux affirmer sans (trop) craindre de me tromper c'est qu'une formule matricielle n'est à utiliser que si c'est nécessaire (à éviter, comme tu le pressentais au départ, sur des volumes importants). Le temps de recalcul dans ton dernier classeur m'en est témoin
Je ne comprends donc pas bien tes tests: il vaudrait mieux faire des essais pour vérifier si on peut obtenir un résultat identique sans formule matricielle que l'inverse!?

Ta version avec un OU ne fonctionne pas sous forme matricielle ... parce que le OU ne renvoie pas une matrice de résultats. Pour t'en convaincre, sélectionne, dans la barre de formule, la partie de formule commençant par OU, jusqu'à la parenthèse fermante de cette même fonction et appuie sur F9: le résultat est un seul VRAI (puisqu'il suffit qu'il y en ait un)

Attention que la validation matricielle sur un ensemble de cellules ne produit pas nécessairement le même résultat que la validation appliquée à une formule, qu'on recopie ensuite.

 

[mapomme]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Bonjour ED31, Modeste

(...) Je laisserai le soin à mapomme de proposer une solution éventuelle en VBA Il fera ça mieux que moi (comme les formules avec des espaces entre les parenthèses, associées à un format personnalisé !)

Je réponds donc à la provocation (amicale certes) de Modeste .

Voici un essai basé sur du VBA. Les hypothèses sont les suivantes:

 

[Staple1600]

Re : Fixer une condition OU dans formule matricielle (condition sur texte)

Bonjour à tous

Très belle proposition VBA, mapomme (lol)
Surtout très exhaustive et très détaillée.

EDITION:
mapomme: meilleurs vœux en option explicit