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,

 

[ED31]

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

Merci Modeste !

- Je suis tombé dans L1C1 quand j'étais petit. Partout je ne vois que A$1. Les petit jeunes qui jouent du coude pour déloger les vieux croûtons me laissent des fichiers dans ce format... Grr... Suis-je le dernier des Mohicans ? Prêt au débat et à être convaincu. Matière à débat ?
- J'ai bien appliqué tes formules en recourant systématiquement à des plages nommées, ma marotte. Elles fonctionnent bien.
- Le test d'erreur que tu proposes est bienvenu.
- Je voulais tester des alternatives à l'évaluation par Ou par la somme des évaluations par ESNUM du résultat de chaque recherche : Je constate effectivement que la formule OU ne fonctionne pas en matriciel. les Si imbriqués ne sont pas jojo, je concède, ils donnent le résultat souhaité.
- Dernier point : quand une de mes formules GrandeValeur avec condition renvoie un zéro, la fonction INDEX utilisant le no de ligne résultat de la formule, soit zéro, renvoie une valeur ??? Je vais alléger le fichier pour voir si cela vient d'un recalcul incomplet.
Merci pour votre appui qui m'a permis d'aller plus loin dans la connaissance du monde matriciel et de ses limites.

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.


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)

Mes meilleurs voeux pour cette année nouvelle Staple1600

Bonjour à tous

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

Certes, ni exhaustive ma proposition, encore moins détaillée, mais en aucun cas, tu ne pourras nier son caractère épuré

 

[mapomme]

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

[FONT=&amp]Bonjour ED31, Modeste [/FONT][FONT=&amp]
[/FONT]

[FONT=&amp]Il fera ça mieux que moi (comme les formules avec des espaces entre les parenthèses, associées à un format personnalisé !) [/FONT]

[FONT=&amp]

(...) Je laisserai le soin à mapomme de proposer une solution éventuelle en VBA

[/FONT][FONT=&amp]
[/FONT][FONT=&amp]Je réponds donc à la provocation (amicale certes) de Modeste [/FONT][FONT=&amp].

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

• [FONT=&amp]la plage des TAG de la feuille "Data" doit être nommée Ch_tags[/FONT]
• [FONT=&amp]les valeurs Val_1 et Val_2 ne sont plus nommées. Elles sont repérées par leur position dans la feuille "Filtre". Par défaut en B6 et B8. Ces deux emplacements sont modifiables dans les constantes de la macro[/FONT]
• [FONT=&amp]l'opérateur (soit ET soir OU) est paramétrable et il est aussi sur la feuille "Filtre" par défaut en B7. Cet emplacement est modifiable dans les constantes de la macro[/FONT]
• [FONT=&amp]la feuille "Data" est la source par défaut. Si elle porte un autre nom, on peut le modifier dans les constantes de la macro.[/FONT]
• [FONT=&amp]la cellule à partir de laquelle on affiche les résultats est D6 de la feuille "Filtre". Cet emplacement est modifiable dans les constantes de la macro[/FONT]
• [FONT=&amp]On peut dupliquer la feuille, pour avoir plusieurs filtres (dans ce cas, les paramètres pour chaque feuille devront être, bien sûr, aux mêmes emplacements)[/FONT]

[FONT=&amp]
Comment faire ?[/FONT]

• [FONT=&amp]aller sur une feuille Filtre[/FONT]
• [FONT=&amp]saisir une valeur pour Val_1 et/ou saisir une valeur pour Val_2[/FONT]
• [FONT=&amp]choisir l'opérateur[/FONT]
• [FONT=&amp]cliquer sur le bouton rechercher[/FONT]

[FONT=&amp]
Nota 1 : si Val_1 est vide ou bien soit Val_2 est vide, alors on ne recherche que sur le critère renseigné.
Nota 2 : si Val_1 et Val_2 sont vides, alors on affiche tout.
[/FONT][FONT=&amp][FONT=&amp]
Nota 3 : le code nécessite une référence à la bibliothèque [/FONT][/FONT]Microsoft Scripting Runtime[FONT=&amp]. Théoriquement cette référence est activée à l'ouverture du classeur (dans la macro évènementielle Workbook_Open() ). Si vous avez un message d'erreur au lancement de la macro, activer la référence à la mano:[/FONT]

• se placer dans l'éditeur VBA (touches Alt+F11)
• sélectionner le menu "Outils / Références..."
• dans la boîte de dialogue, chercher "Microsoft Scripting RunTime"
• cocher la case correspondante (si ce n'est pas déjà le cas) puis cliquer sur "OK"

 

[mapomme]

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

Bonjour à tous,

La bonne version du fichier a été remise dans le précédent message ICI

 

[ED31]

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

Merci Mapomme !

J'ai hâte d'essayer ton programme. Je dois m'équiper d'Excel à la maison, mais j'ai cru lire que les versions "home" n'incluaient pas VBA ??

 

[mapomme]

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

Bonsoir ED31,

(...) J'ai hâte d'essayer ton programme. Je dois m'équiper d'Excel à la maison, mais j'ai cru lire que les versions "home" n'incluaient pas VBA ??

J'ai du mal à me retrouver dans toutes les versions 2013. Se priver de VBA ne me semble pas une bonne chose. Mais tu peux toujours poser la question sur le forum. Tu auras certainement des réponses. Personnellement, depuis que j'ai appris que la version PC de Excel 2013 n'incluait pas une aide en local pour VBA mais seulement une aide en ligne, je conserve mon MSoffice 2010.

 

[Modeste]

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

Salut à tout le monde,

Voici un essai basé sur du VBA

Aaaahh ben tu vois, quand tu veux

Quoi, ce sont des dicos de dicos? Me semble que c'est déjà dans un code à toi que j'avais vu la même chose il y a peu.
Je reconnais que j'aime assez

 

[mapomme]

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

Bonsoir Modeste ,

(...) Aaaahh ben tu vois, quand tu veux

C'était si gentiment demandé...

(...) Quoi, ce sont des dicos de dicos? Me semble que c'est déjà dans un code à toi que j'avais vu la même chose il y a peu. (...)

J'avais tenté il y a quelque temps d'affecter des tableaux à des items d'un dico : une clef -> un tableau. Ça fonctionnait mais quand il s'agissait de rajouter un élément dans l'item tableau en augmentant son nombre d'éléments et tout en vérifiant qu'il n'y serait pas en doublon, c'était la galère. C'est à ce moment là que j'ai pensé à remplacer le tableau par un dictionary. Et à priori ça marche bien; le dico étant fait pour croître et gérer les doublons. En plus le code n'est pas très compliqué (il faut juste ne pas se mélanger les pinceaux et ne pas chercher à visualiser). Maintenant, il ne me reste plus qu'à tenter le dico de dico de dico pour rentrer dans la sixième dimension (je crois que je vais laisser cela à d'autres)

En rédigeant ce post, je me suis aperçu qu'un commentaire dans mon fichier était faux.
Au lieu de lire:

  ' construction du dico des tag
  '    clef= un TAG
  '    item = un dictionary de clef le TAG et de item
  '    la liste des lignes de ce tag (item = dictionary)

il faut lire:

  ' construction du dico des tag
  '    clef= un TAG
  '    item = un dictionary dont les clefs sont les n° de lignes dont le tag est égal à TAG
  '    (en effet plusieurs lignes ont le même TAG)

En toute honnêteté, un dico de dico n'est pas indispensable dans le cas du post. J'étais parti sur une autre idée (mais qui ne correspondait pas à ce que désirait ED31( à savoir recherché des bouts quelconques de tag).
L'idée était de séparer les tags en mots (séparateur = virgule). Pour chaque mot, en faire une clef du dico maître et pour l'item correspondant à ce mot, faire un dictionary des numéros de lignes comprenant ce mot dans leur tag. La liste des clefs du dico maitre aurait été prise comme liste de validation pour Val_1 et Val_2.
Une fois Val_1 et Val_2 choisis au moyen des listes déroulantes, pour trouver les lignes impliquées, il aurait suffit de ne chercher que deux éléments du dico maitre (celui correspondant à Val_1 et l'autre correspondant à Val_2) pour avoir toutes les lignes concernées.

 

[ED31]

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

Merci Mapomme,

Bien testé la macro.
C'est du beau travail.


Je vais maintenant essayer de mettre les sélecteurs en arborescence pour pouvoir étendre les recherche :
exemple: hybride
compact, sont fils de "photo"...

Ou en sens inverse choisir "Photo" et pouvoir choisir un niveau inférieur; Compact, hybride, réflexe..

Bonne soirée

[FONT=&amp]Bonjour ED31, Modeste [/FONT][FONT=&amp]
[/FONT]
[FONT=&amp]

[/FONT][FONT=&amp]
[/FONT][FONT=&amp]Je réponds donc à la provocation (amicale certes) de Modeste [/FONT][FONT=&amp].

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

• [FONT=&amp]la plage des TAG de la feuille "Data" doit être nommée Ch_tags[/FONT]
• [FONT=&amp]les valeurs Val_1 et Val_2 ne sont plus nommées. Elles sont repérées par leur position dans la feuille "Filtre". Par défaut en B6 et B8. Ces deux emplacements sont modifiables dans les constantes de la macro[/FONT]
• [FONT=&amp]l'opérateur (soit ET soir OU) est paramétrable et il est aussi sur la feuille "Filtre" par défaut en B7. Cet emplacement est modifiable dans les constantes de la macro[/FONT]
• [FONT=&amp]la feuille "Data" est la source par défaut. Si elle porte un autre nom, on peut le modifier dans les constantes de la macro.[/FONT]
• [FONT=&amp]la cellule à partir de laquelle on affiche les résultats est D6 de la feuille "Filtre". Cet emplacement est modifiable dans les constantes de la macro[/FONT]
• [FONT=&amp]On peut dupliquer la feuille, pour avoir plusieurs filtres (dans ce cas, les paramètres pour chaque feuille devront être, bien sûr, aux mêmes emplacements)[/FONT]

[FONT=&amp]
Comment faire ?[/FONT]

• [FONT=&amp]aller sur une feuille Filtre[/FONT]
• [FONT=&amp]saisir une valeur pour Val_1 et/ou saisir une valeur pour Val_2[/FONT]
• [FONT=&amp]choisir l'opérateur[/FONT]
• [FONT=&amp]cliquer sur le bouton rechercher[/FONT]

[FONT=&amp]
Nota 1 : si Val_1 est vide ou bien soit Val_2 est vide, alors on ne recherche que sur le critère renseigné.
Nota 2 : si Val_1 et Val_2 sont vides, alors on affiche tout.
[/FONT][FONT=&amp][FONT=&amp]
Nota 3 : le code nécessite une référence à la bibliothèque [/FONT][/FONT]Microsoft Scripting Runtime[FONT=&amp]. Théoriquement cette référence est activée à l'ouverture du classeur (dans la macro évènementielle Workbook_Open() ). Si vous avez un message d'erreur au lancement de la macro, activer la référence à la mano:[/FONT]

• se placer dans l'éditeur VBA (touches Alt+F11)
• sélectionner le menu "Outils / Références..."
• dans la boîte de dialogue, chercher "Microsoft Scripting RunTime"
• cocher la case correspondante (si ce n'est pas déjà le cas) puis cliquer sur "OK"

 

[ED31]

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

Bonjour, J'ai repris mon fichier et ma base de données. Tout fonctionne bien.
Merci encore à :
- Mapomme pour sa belle macro qui m'a initié au joies du dictionary (j'espère pouvoir l'appliquer) même si je ne comprends pas tout,
- A Modeste, pour ses belles formules, matricielles ou pas, qui m'ont éclairé sur l'utilisation des opérateurs logiques (et de l'utilité parfois de se faire aider par un calcul dans un vecteur intermédiaire).
Au plaisir de vous retrouver sur le forum !