Aide Map + Lambda

Bonjour,

Je galère sur une formule...

J'ai 2 onglets dans un fichier sheet :

ONGLET 1 :

ONGLET 2 :

Mon objectif est dans l'onglet 1, indiquer à quelle référence est liée chaque ligne en fonction de la DATE et du SIRET.

Je sais le faire à l'aide de Query : Sierreur(query(onglet2!A:Z;"select A where B = '"&B2&"' and C = '"&A2&"');"") 

mais, dans ce cas, Arrayformula ne fonctionne pas...or ces données proviennent d'une API et donc sont dynamiques (et je ne souhaite pas faire un script qui remet la formule pour chaque ligne).

Je voulais utiliser MAP et LAMBDA qui semblent pouvoir répondre à ce besoin, mais je ne comprends pas encore trop le fonctionnement de LAMBDA, des idées afin de régler mon problème ?

Bonjour Pierre,

J'ai commencé avec QUERY mais je n'ai pas trouvé ça très pratique dans ce cas, donc je suis parti dans quelque chose de complètement différent :

=BYROW(FILTER(A2:B;A2:A<>"");LAMBDA(ligne;SIERREUR(FILTER(onglet2!A:A;onglet2!B:B=INDEX(ligne;1;2);onglet2!C:C=INDEX(ligne;1;1)))))

S'il peut y avoir parfois plusieurs références par recherche, choisis plutôt cette version avec TRANSPOSE en plus :

=BYROW(FILTER(A2:B;A2:A<>"");LAMBDA(ligne;SIERREUR(TRANSPOSE(FILTER(onglet2!A:A;onglet2!B:B=INDEX(ligne;1;2);onglet2!C:C=INDEX(ligne;1;1))))))

Cordialement,

Bonjour !

Je galère un peu à adapter la formule à mon fichier réel, je résolus dès que c'est bon

Bonjour,

Je l'ai adapté, mais ça ne fonctionne pas, voici comment est mon fichier en vrai :

Onglet 1

Onglet 2

Donc, ce que je veux c'est dans l'onglet 1, à l'aide du siret et de la date, trouver quelle est la référence dans l'onglet 2, j'ai appliqué ta formule ainsi :

=BYROW(FILTER({H2:H;B2:B};A4:A<>"");LAMBDA(ligne;SIERREUR(FILTER('onglet2'!A:A;onglet2!=INDEX(ligne;1;2);'onglet2'!AQ:AQ=INDEX(ligne;1;8)))))

En vain, je me suis dit que ça pouvait venir du format de date, donc j'ai tenté un =CNUM puis un =TEXTE dessus, sans succès.

Essaie avec :

=BYROW(FILTER(B2:H;A2:A<>"");LAMBDA(ligne;SIERREUR(FILTER(onglet2!A:A;onglet2!D:D=INDEX(ligne;1;1);onglet2!AQ:AQ=INDEX(ligne;1;7)))))

Erreur

Le résultat doit être une ligne unique.

Peux-tu me décrire le déroulé de cette formule ? Afin que je voie ce qui génère cette erreur ? Car autant en script, je me débrouille, autant les formules autres que le sacro-saint Query je ne les maitrise pas.

J'ai rajouté un INDEX pour limiter les résultats à 1 par ligne, ça devrait régler ton problème :

=BYROW(FILTER(B2:H;A2:A<>"");LAMBDA(ligne;SIERREUR(INDEX(FILTER(onglet2!A:A;onglet2!D:D=INDEX(ligne;1;1);onglet2!AQ:AQ=INDEX(ligne;1;7));1;1))))

Peux-tu me décrire le déroulé de cette formule ?

Cette fonction filtre de la plage B2:H (en fonction de la colonne A) pour ignorer les lignes vides :

FILTER(B2:H;A2:A<>"")

Donc BYROW va parcourir ici les lignes non vides de la plage B2:H :

BYROW(FILTER(B2:H;A2:A<>"");

Pour chaque ligne, LAMBDA va donc effectuer ce calcul :

SIERREUR(INDEX(FILTER(onglet2!A:A;onglet2!D:D=INDEX(ligne;1;1);onglet2!AQ:AQ=INDEX(ligne;1;7));1;1))

Si on retire SIERREUR que tu connais bien et INDEX qui limite le résultat à 1, il reste :

FILTER(onglet2!A:A;onglet2!D:D=INDEX(ligne;1;1);onglet2!AQ:AQ=INDEX(ligne;1;7))

Donc là la colonne A est filtrée en fonction de la colonne D et AQ (d'où un résultat sur plusieurs lignes possible si les combinaisons ne sont pas uniques dans ta feuille).

Quant aux INDEX(ligne;1;7), ce sont les valeurs du tableau "ligne" (BYROW parcourt la plage en premier argument ligne par ligne).

Car autant en script, je me débrouille

Si tu utilises parfois les méthodes de tableau en Apps Script, tu devrais rapidement être à l'aise avec ce type de fonction (la fonction MAP par exemple c'est l'équivalent de la méthode map). Ce type de fonction peut te faire progresser à la fois en formules et en code.

Juste pour l'exemple, s'il avait fallu créer une fonction personnalisée en utilisant ce type de méthodes, elle aurait pu s'écrire ainsi :

=POUR_PIERRE(A2:H;onglet2!A2:AQ)

function POUR_PIERRE(p1, p2) {
  return p1.filter(i => i[0]).map(i => (p2.filter(j => j[3] * 1 == i[1] * 1 && j[42] == i[7])[0] ?? [''])[0]);
}