Matching selon Filter, textjoin et Cie
Hello tout le monde !
J'essaie d'avancer sur une requête type matching (query) provenant de 2 feuilles au sein du même Sheet.
En feuille 1 nommée STOCK, j'ai : une référence, une désignation, un stock (d'où le nom
En feuille 2, nommé TENDANCE, j'ai : une référence, une désignation, une catégorie et sous-catégorie ainsi qu'une note, par rapport à sa tendance (ici entre 8 et 10) sur plusieurs semaines. Plus la note est haute, plus c'est tendance. Cette feuille 2 diffère : Catégorie et sous-catégorie ne sont plus seulement des numéros mais leurs sont rattachés du texte.
En feuille 3 je souhaiterais restituer un matching des références avec trois critères :
- une référence TENDANCE dont le stock est supérieur à [X] (imaginons une cellule où je saisis la valeur, 3 par exemple) et à côté la désignation (reprise de la feuille 1 par exemple)
- une note supérieure à [X] (idem une cell ou checkbox qui reprend les valeurs, 9 par exemple)
- Une catégorie et/ou une sous-catégorie sélectionnée (genre uniquement catégorie 1, 9) avec le principe de checkbox par exemple.
Cela ressemblerait à ceci :
Et je joins un fichier d'exemple, bon le matching est pas OP si vous m'avez suivi :-)
https://docs.google.com/spreadsheets/d/1BuQDnozu5oWEn2TwPIpLfQfQqipxKfBwCh6d5Ka_fds/copy
Merci pour votre regard éclairé. 🙏🏻
Bonjour,
c'est simple ... et complexe !! parce qu'effectivement il faut décomposer catégories et sous-catégories pour en tirer le nombre, relier cela en concaténant avec la base de références et ajouter les infos
le mieux serait d'abord de constituer justement cet agglomérat de données sur lequel on mettra les critères
... d'ici demain matin sans doute !! il vaut mieux être au calme
Haha je comprends
Un début, on peu agréger les données comme ceci
=arrayformula({STOCK!C3:E\STOCK!A3:A\iferror(vlookup(STOCK!A3:A&"~"&STOCK!B3:B;{REGEXEXTRACT(TENDANCE!B2:B;"\d+") &"~"& REGEXEXTRACT(TENDANCE!C2:C;"\d+")\REGEXEXTRACT(TENDANCE!B2:B;"\d+")\REGEXEXTRACT(TENDANCE!C2:C;"\d+")\TENDANCE!B2:I};{3\4\5\6\7\8\9\10\11};0))})Ce n'est pas très complexe, mais il faut être rigoureux ..
REGEXEXTRACT>> extrait de la donnée les valeurs numériques avec le pattern\d+&"~"&>> concatène des données en insérant le tilde (ou un autre caractère non numérique pour bien séparer les données qui vont constituer l'identifiant)\>> juxtapose des donnéesvlookup>> fait une recherche, avec ici les colonnes multiples en 3ème paramètres{3\4\...}
Au final, cela me donne ceci
=query({
arrayformula({STOCK!C3:E\STOCK!A3:A\iferror(vlookup(STOCK!A3:A&"~"&STOCK!B3:B;{REGEXEXTRACT(TENDANCE!B2:B;"\d+") &"~"& REGEXEXTRACT(TENDANCE!C2:C;"\d+")\REGEXEXTRACT(TENDANCE!B2:B;"\d+")\REGEXEXTRACT(TENDANCE!C2:C;"\d+")\TENDANCE!B2:I};{3\4\5\6\7\8\9\10\11};0))})};"select Col1,Col2 where Col3>="&D2&" and Col4 matches'"&textjoin("|";;filter(F2:F;G2:G=TRUE))&"' and
Col5 matches'"&textjoin("|";;filter(H2:H;I2:I=TRUE))&"' and (Col8="&E2&" or Col9="&E2&" or Col10="&E2&" or Col11="&E2&") ";0)
Ouahh Canon !
J'ai une erreur (erreur de , vs ; du à la version gsheet ?)
avec en F2=UNIQUE(STOCK!A2:A) et H2=UNIQUE(STOCK!B2:B)
je pense que les données ne sont pas exactement aux mêmes endroits
https://docs.google.com/spreadsheets/d/1nyKYYva0k0crk7Ei09Ma0UkC13TcgDzPOdqHfOFGsIM/copy
Yes nickel. Merci à toi. That's sounds good 🤘.
J'ai retravaillé la qualité/typologie de la donnée d'entrée et je me rends compte (dans le scénario que je branche derrière) que les règles de gestion n'étaient pas précises. La finalité reste toujours le matching en feuille n°3 des deux premières feuilles (croiser du stock et de la tendance), avec quelques filtres différenciants.
La logique :
1. Le matching porte uniquement sur la zone 129 - VERT (en "B1" sur la feuille 1) car je compare le stock et la tendance de cette zone uniquement.
2. Les conditions sont : [ une note > à 8 (en feuille 2) ]+ [ un Top différent de 1 (en feuille 1) ou un stock < ou = à 3 (feuille 1) ]
Un exemple de matching où l'on retrouverait ces dimensions (catégorie, sous-catégorie, Référence, désignation, Top, Stock) :
Je remets le lien de la démo :
https://docs.google.com/spreadsheets/d/1BuQDnozu5oWEn2TwPIpLfQfQqipxKfBwCh6d5Ka_fds/copy
Merci pour votre aide et regard avisé 🙏🏻
je n'ai pas compris si tu attendais encore quelque chose de ma part !
Je veux bien un coup de main
1. Le matching porte uniquement sur la zone 129 - VERT (en "B1" sur la feuille 1) car je compare le stock et la tendance de cette zone uniquement.
2. Les conditions sont : [ une note > à 8 (en feuille 2) ]+ [ un Top différent de 1 (en feuille 1) ou un stock < ou = à 3 (feuille 1) ]
il faut juxtaposer à la feuille 1 certaines colonnes de la feuille 2 puis appliquer un query
- il faut adapter la formule plus haut https://forum.excel-pratique.com/sheets/matching-selon-filter-textjoin-et-cie-171927#p1066376 à tes nouvelles données (je te laisse faire sachant que beaucoup de choses ont changé : la position des données, le nom des feuilles, ...)
- autre méthode = remettre les données comme à l'origine, reprendre la formule pour éviter un nouveau casse-tête, puis re-déplacer les données dans la configuration que tu souhaites
Bonsoir,
Merci pour ce retour et les éléments de réponse. J'ai suivi vos conseils et ai (re)cleané les deux feuilles du sheet, afin d'avoir le même nombre de colonnes, les mêmes dimensions ou valeurs que les fichiers sources finaux. J'ai modifié la query pour tenter de la faire correspondre aux critères cités précédemment. Cependant, je vois que la modification des données en E2 ou G2 ne charge pas de résultats logiques (pas toujours). De la même façon, les 2 autres conditions que je souhaiterais ajouter (cellule A1 en feuille 1 et TOP) ainsi que les rayons et sous-rayon à afficher fonctionnent à partir d'une query "simple" (where, select, etc), mais le matching entre deux feuilles me dépasse...
Merci de votre regard éclairé 😇.
https://docs.google.com/spreadsheets/d/1BuQDnozu5oWEn2TwPIpLfQfQqipxKfBwCh6d5Ka_fds/copy
Le plus clair, serait de créer une base de travail qui va compiler les 2 feuilles comme ceci
=arrayformula({
STOCK!F3:I\STOCK!B3:E\
IFERROR(VLOOKUP(
STOCK!B3:B&"~"&STOCK!C3:C;
{REGEXEXTRACT(TENDANCE!B2:B;"\d+")&"~"®EXEXTRACT(TENDANCE!C2:C;"\d+")\REGEXEXTRACT(TENDANCE!B2:B;"\d+")\REGEXEXTRACT(TENDANCE!C2:C;"\d+")\TENDANCE!B2:U};
{3\4\5\6\7\8\9\10\11\12\13\14\15\16\17};
0))
})
et ensuite de faire appel à celle-ci en adaptant les critères (notamment le rang x des colonnes dans Colx)
=query( {base!A:V} ;"select Col1,Col2 where .................. ";0)Top merci pour ce retour. J'ai repris la arrayformula en feuille "Base", et dans autre feuille une query de ce type :
=query( {Base!A2:V} ;"select Col5, Col1, Col2, Col3, Col8 where Col8=2 or Col3<=3";0)
Il y a juste un souci pour le report en Base : Il manque manque des colonnes de "notations" à la suite de I. Pourtant je suis bien en TENDANCE!B2:U en toute fin ?
C'est une erreur du forum que je vais signaler à l'administrateur ... le REG de REGEXEXTRACT disparait s'il est accolé à & !!!!!!!!!!!!!
Donc j'ai ajouté un espace
=arrayformula({
STOCK!F3:I\STOCK!B3:E\
IFERROR(VLOOKUP(
STOCK!B3:B&"~"&STOCK!C3:C;
{ REGEXEXTRACT(TENDANCE!B2:B;"\d+") &"~"& REGEXEXTRACT(TENDANCE!C2:C;"\d+")\ REGEXEXTRACT(TENDANCE!B2:B;"\d+")\ REGEXEXTRACT(TENDANCE!C2:C;"\d+")\TENDANCE!B2:U};
{3\4\5\6\7\8\9\10\11\12\13\14\15\16\17};
0))
})en effet, si je mets & puis REG accolé
®c'est transformé
si je mets & REG
& REGc'est bon !
Bonjour,
C'est un problème lié à l'éditeur pour lequel je ne peux malheureusement pas faire grand chose
Car ça a une signification particulière en HTML :
Cordialement,
Argh mon message part plus...Bug
Merci beaucoup pour ces retours 🙏🏻 J'avais noté le IFERROR / SIERREUR, le RECHERCHEV et le coup du copyright, mais je n'avais pas percuté pour le REGEXEXTRACT. Ça marche très bien du coup 👍🏻.
J'ai créé la query sur une feuille "Matchpoint" tout y est, je vais pouvoir monitorer derrière.
Il me reste juste à parfaire la query de Matchpoint avec deux conditions :
• Ne pas pas faire apparaître dans le matching les références présentes sur la feuille "STOCK" ET pas "TENDANCE" (en gros, je l'ai en stock mais pas tendance donc pas d'alerte), c'est chose faite avec une condition dans la query de note (car si pas note, alors pas présent) --> OK
• Faire apparaître les références qui sont dans "TENDANCE", mais pas du tout dans "STOCK" (en gros, c'est tendance et c'est pas du tout dans le stock. C'est peut-être le plus important, c'est le côté "non matching" du truc). ça par contre je bloque. KO
Je ne vois pas si c'est quelque chose à renseigner dans la feuille finale "Matchpoint" ou dans "Base" plus en amont. En sachant qu'il faudrait avoir en "Matchpoint" le rayon, la référence et la désignation.
Exemple avec clavier, qui est dans tendance pas mais pas en stock :
https://docs.google.com/spreadsheets/d/1BuQDnozu5oWEn2TwPIpLfQfQqipxKfBwCh6d5Ka_fds/copy
• Ne pas pas faire apparaître dans le matching les références présentes sur la feuille "STOCK" ET pas "TENDANCE" (en gros, je l'ai en stock mais pas tendance donc pas d'alerte), c'est chose faite avec une condition dans la query de note (car si pas note, alors pas présent) --> OK
il suffit alors de mettre
"select Col5, Col1, Col2, Col3, Col8 where Col9 is not null"