Query multicritères

Bonjour tout le monde,

je vous exprime mon besoin.

Une base données en haut à droite en rouge. Un moteur de recherche en haut a gauche en vert.

Les résultats de la recherche dans le tableau vert en bas. Une formule Query dans la case violette : =QUERY(M4:U11;"SELECT*WHERE LOWER (M) contains LOWER ("""&B4&""")")

Pour le moment cette formule se concentre sur la colonne B - Catégorie. Il suffit de taper une seule lettre en B4 pour qu'il rapporte M4:M contenant cette meme lettre.

Comme vous le devinez j'aimerai pouvoir enrichir cette formule pour qu'elle puisse rapporter tout les critères du montant de recherche : Nom du produit - Référence - Fournisseur etc... en faisant en sorte qu'une seule lettre suffise, par ailleurs il y a 2 autres difficultés pouvoir gérer des dates et prix min ou max etc..
La seconde difficulté est de faire en sorte que lorsque la case est vide elle ne soit pas intégrer dans les critères de recherche, sinon ca obligerait à obligatoirement à tout remplir pour pouvoir effectuer une recherche.

Je vous partage le lien :

https://docs.google.com/spreadsheets/d/1Sy9qK8Bygk1B4Osd8-eZJU9__1SXu87Kbl0qFh5vCX0/edit?usp=sharing

Je vous remercie !

J'ai trouvé cette formule qui fait quasi le job :

=QUERY(M4:U11;"SELECT*WHERE 
LOWER (M) contains LOWER ("""&B4&""") 
AND (N) contains LOWER ("""&C4&""") 
AND (O) contains LOWER ("""&D4&""") 
AND (P) contains LOWER ("""&E4&""") 
AND (Q) contains LOWER ("""&F4&""") 
AND (R) contains LOWER ("""&G4&""") 
AND (S) > DATE '"&TEXTE(H4;"yyyy-MM-dd")&"' 
AND (T) < DATE '"&TEXTE(I4;"yyyy-MM-dd")&"'")

Elle se situe dans l'onglet "Essaie Mathieu demandeur"; il reste 2 choses à régler pour qu elle fonctionne parfaitetement : pouvoir intégrer la fourchette de prix min / max, j y arrive en entrant un chiffre dans la formule mais pas en utilisant une cellule de référence.
Et donc la dernière chose est de faire en sorte que les cellules vides de filtrage vident ne soient pas considérés.

Bonjour,

Pour les bornes mini maxi

=QUERY(M4:U11;"SELECT*WHERE 
LOWER (M) contains LOWER ("""&B4&""") 
AND (N) contains LOWER ("""&C4&""") 
AND (O) contains LOWER ("""&D4&""") 
AND (P) contains LOWER ("""&E4&""") 
AND (Q) contains LOWER ("""&F4&""") 
AND (R) contains LOWER ("""&G4&""") 
AND (S) >= DATE '"&TEXT(H4;"yyyy-MM-dd")&"'
AND (T) <= DATE '"&TEXT(I4;"yyyy-MM-dd")&"'
AND (U) >= " & J4 & "
AND (U) <= " & K4 & " 
")

Au top, merci Steelson !

Reste a trouver comment gérer les cellules de recherche non remplies.

Dans l idéal le Query doit baser sa recherche que sur les cellules avec des valeurs. Les vidéos sont ignorées.

Mais avec ma formule a coup de AND ça oblige à remplir tous les champs. Les essaie de Jordan sont peut être une solution. Sinon voir avec MATCH à la place de CONTAINS.

Si quelqu un a une idée.

Merci !

Je pense qu'il "suffit" de tester s'il y a une valeur sinon mettre vide ... je vais faire un test sur une zone.

=QUERY(M4:U11;"SELECT*WHERE 
LOWER (M) contains LOWER (""" & B4 & """) " &
IF(C4="";"";" AND (N) contains LOWER (""" & C4 & """) ") &
IF(D4="";"";" AND (O) contains LOWER (""" & D4 & """) ") &
IF(E4="";"";" AND (P) contains LOWER (""" & E4 & """) ") &
IF(F4="";"";" AND (Q) contains LOWER (""" & F4 & """) ") &
IF(G4="";"";" AND (R) contains LOWER (""" & G4 & """) ") & "
AND (S) >= DATE '"&TEXT(H4;"yyyy-MM-dd")&"'
AND (T) <= DATE '"&TEXT(I4;"yyyy-MM-dd")&"'
AND (U) >= " & J4 & "
AND (U) <= " & K4 & " 
")

Alors oui effectivement ca marche on tient le bon principe, parcontre B4 / (M) ne sont pas gérés et les critères de date et nombres non plus, on est encore obligé de les remplir, j essaie de creuser / dupliquer ce que tu as fais

Absolument, triture, triture, si problème je prendrai la main !

=QUERY(M4:U11;"SELECT*WHERE LOWER
(M) contains LOWER (""" & B4 & """) " &

SI(C4="";"";" AND (N) contains LOWER (""" & C4 & """) ") &

SI(D4="";"";" AND (O) contains LOWER (""" & D4 & """) ") &

SI(E4="";"";" AND (P) contains LOWER (""" & E4 & """) ") &

SI(F4="";"";" AND (Q) contains LOWER (""" & F4 & """) ") &

SI(G4="";"";" AND (R) contains LOWER (""" & G4 & """) ") &

SI(H4="";"";" AND (S) >= DATE '"&TEXTE(H4;"yyyy-MM-dd")&"'"&

SI(I4="";"";" AND (T) <= DATE '"&TEXTE(I4;"yyyy-MM-dd")&"'"&

SI(J4="";"";"AND (U) >= " & J4 & ""&

SI(K4="";"";"AND (U) <= " & K4 & "

")))))

Alors avec cela j'ai reussi à intégrer les fourchettes de dates et prix. Parcontre je n'ai pas résolu le B4/M pour qu'il travaille comme le reste.
L'autre soucis est que pour que le tout fonctionne on est obligé de remplir les critères dans un ordre bien précis, si par exemple on commence par la fourchette de prix ca ne fonctionne pas.
Donc quand on a tous mis et qu on supprime des critères cela fonctionne, mais quand on part de 0 ou qu'on prend qu un seul champ de recherche ca le fait pas.

Je vais voir si avec la solution Match ou la solution de l'onglet "essaie jordan" ca peut mieux se gérer.
Ou alors que dans notre voie il faudrait intégrer du OR en plus du AND.

Hott18 a écrit :

Alors avec cela j'ai reussi à intégrer les fourchettes de dates et prix. Par contre je n'ai pas résolu le B4/M pour qu'il travaille comme le reste.

L'autre soucis est que pour que le tout fonctionne on est obligé de remplir les critères dans un ordre bien précis, si par exemple on commence par la fourchette de prix ca ne fonctionne pas.

Pour B4, le problème c'est que c'est le premier critère, et s'il est absent, la séquence commence alors par AND ce qui bloque

• mettre en premier un critères obligatoire
• ou bien ajouter une colonne "bidon" avec des x et mettre x en critère (en masquant la colonne), c'est un peu de bidouille mais cela marche et éviter une longue formule ncore plus complexe à mettre au point
• je pense qu'on pourrait externaliser/oter de la formule les critères et mettre la syntaxe dans une cellule car après tout c'est juste du texte ! et là on serait plus à l'aise

Pour l'autre soucis, je ne suis pas d'accord, cela fonctionne.

Ouais j'y ai pensé aussi à la première colonne bidon. Si c'est que ça on s en sort plus bien ouais.

Parcontre je te joins cette capture, regarde la il y a le E en première colonne et juste un critère de prix max a la fin, et ça ne fonctionne pas.

Tout dépend de l ordre remplissage / de remplissage en faite. Il y a une espèce de réaction en chaîne qui se fait la où chaque cellule devrait être dépendante et se combiner avec l une et l autre si besoin.

Il doit donc y avoir un schmilblick dans la formule ...

Je vais regarder pas à pas.

Je prends la main...

edit : ce n'est pas normal qu'il y ait une succession de )

=QUERY(M4:U11;"SELECT * WHERE 
LOWER (M) contains LOWER (""" & B4 & """) " &
IF(C4="";"";" AND (N) contains LOWER (""" & C4 & """) ") &
IF(D4="";"";" AND (O) contains LOWER (""" & D4 & """) ") &
IF(E4="";"";" AND (P) contains LOWER (""" & E4 & """) ") &
IF(F4="";"";" AND (Q) contains LOWER (""" & F4 & """) ") &
IF(G4="";"";" AND (R) contains LOWER (""" & G4 & """) ") & 
IF(H4="";"";" AND (S) >= DATE '"&TEXT(H4;"yyyy-MM-dd")&"'") &
IF(I4="";"";" AND (T) <= DATE '"&TEXT(I4;"yyyy-MM-dd")&"'") &
IF(J4="";"";" AND (U) >= " & J4 & "") &
IF(K4="";"";" AND (U) <= " & K4 & ""))

ce serait moins "pointu" en mettant les critères sous les zones, je vais tester

Autre solution ... et regarde la ligne 5

=QUERY(M4:U11;"SELECT * WHERE " & CONCATENATE(B5:K5) )

reste maintenant à adapter pour faire en sorte que le premier argument puisse aussi être optionnel

j'ai aussi mis une formule en ligne 6 qui enlèvera AND au premier critère de la ligne 5

Alors balèze ! Autant jusqu'à présent j'arrivais à suivre et interpréter les formules autant la je suis largué, je n'arrive plus à associer toutes les logiques.
Malheureusement il y a toujours des failles, selon la casse (majuscule ou pas) l'ordre de saisi et tout , desfois ca fonctionne , desfois pas.
En voici un exemple :

Du coup je me permets de penser que la base à coup de "Contains" montre ses limites et nous oblige à redoubler d'astuce pour compenser.
La solution "like lower" présente dans l'onglet "Essaie Jordan" me parait plus efficace à mettre en place.

Le souci c'est que je ne lui ai pas encore intégré la gestion de valeurs numérique : poids - fourchette de dates - fourchette de prix. Je vais essayer de l'intégrer et de voir mais je me doute que de nouvelles contraintes et limites vont se poser. A voir.

Hott18 a écrit :

la je suis largué,

c 'est simple

• je n'active where que si au moins un critère est renseigné
• je mets and partout sauf au premier critère
• donc pour cela, ligne 6, je compte le nombre de critères renseignés avant la colonne en cours
• après ce ne sont que des formules texte

Hott18 a écrit :

il y a toujours des failles, selon la casse (majuscule ou pas) l'ordre de saisi et tout , desfois ca fonctionne , desfois pas.
En voici un exemple :

Je pense que ne effet contains n'est pas le plus approprié, j'ai remarqué que cela fonctionnait correctement quand il n'y avait qu'un seul caractère. C'était du reste déjà le cas avec la formule à rallonge ! Il faudrait voir du côté de like https://www.sheets-pratique.com/fr/query/where-like

Mais je n'ai pas trouvé de soucis d'ordre.

Quelques corrections dont je viens de m'apercevoir en reprenant ta formule ici https://forum.excel-pratique.com/sheets/query-multicriteres-149228#p920183 ...

On voit ici l'intérêt de décomposer la formule case par case, c'est plus facile à maintenir.

• Pour les colonnes C D E et F il vaut mieux en effet mettre like avec %

=IF(C4="";" ";IF(C6=0;"";" AND") & " LOWER (N) like LOWER (""%" & C4 & "%"") ")

en ajoutant aussi LOWER qui manquait dans ta formule

du coup, avec like on peut simplifier très nettement les formules car il reste toujours %% dans le critère et le AND ne gênera plus

=" AND LOWER (N) like LOWER (""%" & C4 & "%"")"

sauf pour le prix, le poids et la date qu'il faut maintenir comme proposé ici > je l'ai fait pour le prix, reste la date et le poids (quel critère ?)

• Enlève le 11 dans query pour adapter la formule à la taille du tableau

=QUERY(M4:U;"SELECT * " & CONCATENATE(A5:K5) )

• Je ne comprends pas pourquoi tu mets 2 dates dans le moteur de recherche !!

SI(H4="";"";" AND (S) >= DATE '"&TEXTE(H4;"yyyy-MM-dd")&"'"&
SI(I4="";"";" AND (T) <= DATE '"&TEXTE(I4;"yyyy-MM-dd")&"'"&

une seule date suffit à comparer aux dates de début et de fin de validité !

• Enfin, ce qui m'étonne c'est la formule pour le poids qui est numérique ! C'est quoi le critère ?

Oui on se rejoint sur la simplification via Like & Lower.

Pour ce qui est du poids, fourchette de date et de prix, ce tableau que j essaie de résoudre est un prototype pour répondre aux maximum de scénario.

A l'avenir j'aurai assurément besoin dans mon moteur de recherche sur un base de 1200 adhèrents pouvoir ressortir les gens en fonction d'un rang ou d'une catégorie 1 2 3 4.. Assurément que j'aurai besoin de contacter la clientele d'une tranche d'age de 15 - 25 ans pour faire une communication ciblée, aussi que j'aurai besoin de calculer les totaux de depense compris entre telle date et telle date, ou cibler les produits de telle fourchette de prix.
Bref tout un tas d'usage que je prévois pour mes anciens et futur tableaux ou j'aspire a rendre la base de données masqué et alimenté via Forms, et que le client -besogneux en informatique- aura juste à faire comme sur Google pour piocher les infos qu il veut.
En parallele du champ de recherche, je vais mettre des =MOY =SUM =NBVAL pour qu'a chaque rapport du query ca affiche les résultats souhaité : nombre et proportion d'adhèrent entre 15 et 25 ans ? -----> champ de recherche date min / date max ---> résultat. A la portée de n'importe qui.
Voila pour l'esprit du truc et donc voila cet exemple qui a pour but d'intégrer tous les scénarios possibles, une fois calé je pourrais piocher éternellement dessus.

Du coup on arrive à parfaitement gérer les valeurs lettrés mais on bloque sur la gestion et la combination avec les valeurs numérique..

Je vais essayer de voir comment combiner ça bien que je commence à etre larguer et/ou pourquoi pas voir du côté de Filter à la place Query.

En tout cas merci beaucoup pour ton aide et ton investissement Steelson. Vraiment.

Je vais regarder pour la date en début d'apm

Tu as raison, c'est un bel exercice dans lequel on pourra puiser ce qui est nécessaire, d'où a nouveau je pense l'utilité de décomposer les formules et les mettre en dessous des critères