Recopier une formule qui contient une plage dans une colonne entière
Bonjour
J'ai vu qu'une technique pour recopier sur une colonne entière une formule (par exemple =SOMME(A1)), il suffisait d'utiliser le calcul matriciel (Arrayformula{}) et en changeant la cellule cible (A1-> A1:A) ce qui donne en fait la formule en A1 =Arrayformula{SOMME(A1:A)}.
Sauf que dans mon cas, je veux utiliser la fonction sommeprod qui contient une plage de cellules comme paramètres (SOMMEPROD(F2:AI2<>"") : je ne peux donc pas utiliser la manipulation précédente pour y arriver.
Quelqu'un a-t-il une astuce pour recopier sur une colonne entière une formule qui contient des plages de cellules ?
Merci d'avance
Bonjour Sébastien
J'ai lu avec intérêt ta réponse et notamment le lien : je ne connaissais pas du tout de type de fonctions.
J'ai par contre du mal à le mettre en pratique dans mon cas.
Dans les colonnes G à AJ, j'ai des données (issues d'un fichier d'export) qui contient un chiffre ou des lettres : je veux comptabiliser le nombre de cellules dans une ligne qui sont chiffrées ou qui contiennent un certain texte que je récupère dans la colonne F
J'arrive à utiliser les fonctions MAP/LAMBDA pour le test sur une colonne : MAP(G2:G;lambda(test;ESTNUM(test)))
Par contre, je ne vois pas comment je peux utiliser ces fonctions pour chercher non pas dans une seule colonne mais dans plusieurs colonnes. J'ai bien essayé MAP(G2:H;lambda(test;ESTNUM(test))) mais cela me renvoie une erreur #REF pour ne pas écraser les données de G2.
J'ai bien essayé MAP(G2:H;lambda(test;ESTNUM(test))) mais cela me renvoie une erreur #REF pour ne pas écraser les données de G2.
La formule que tu as écrite renvoie un tableau équivalent à G2:H après avoir appliqué ESTNUM(test) à chaque valeur de G2:H:
Pour t'aider plus efficacement, un fichier d'exemple anonymisé (et partagé) est le bienvenu.
Bonjour Sébastien,
Mon fichier exemple https://docs.google.com/spreadsheets/d/1j9PQ5ya1Fjvx0_zkOzwTHXo0KaU6lMliRgQGbGr8Mac/edit?usp=sharing
Merci
Merci Sébastien,
Ces fonctions sont vraiment top et très rapides à calculer !
Je viens de me rendre compte que je n'ai pas fait la bonne demande : la somme ne doit pas se faire sur la même ligne mais par colonne.
En fait, je veux récupérer le nombre de fois ou un même nom peut apparaître plusieurs fois dans une même journée (J01 par exemple) avec éventuellement les poules correspondantes : je dois donc récupérer le nombre de fois où un même nom (dans l'onglet Feuille 2, j'ai récupéré la liste unique à l'aide de la fonction sort/unique) apparaît dans l'onglet export par journée.
PS : en regardant un peu les liens que tu mettais dans tes réponses, j'ai du mal à comprendre la différence entre la fonction MAP et BYROW (dans l'exemple que tu m'as donné précédemment où je m'étais trompé dans ma demande) car j'ai l'impression que MAP fait également du ligne par ligne.
la somme ne doit pas se faire sur la même ligne mais par colonne
...
un même nom peut apparaître plusieurs fois dans une même journée
Si je comprends bien ... ça complique un peu la formule
=MAP(SEQUENCE(1;30;7);LAMBDA(i;LET(tab;QUERY({export!2:6400};"SELECT Col1, COUNT(Col"&i&") WHERE Col"&i&" > 0 GROUP BY Col1";0);MAP(SORT(UNIQUE(export!A2:A));LAMBDA(nom;SIERREUR(RECHERCHEV(nom;tab;2;0);""))))))
j'ai du mal à comprendre la différence entre la fonction MAP et BYROW (dans l'exemple que tu m'as donné précédemment où je m'étais trompé dans ma demande) car j'ai l'impression que MAP fait également du ligne par ligne.
La différence est que MAP va envoyer à LAMBDA chaque cellule du tableau séparément, tandis que BYROW va envoyer les lignes entières.
Si tu as un tableau avec une seule colonne, une ligne sera égale à une cellule, donc c'est très similaire ... Par contre si c'est un tableau de plusieurs colonnes, BYROW va envoyer pour chaque ligne un tableau contenant les cellules de la ligne.
Ou la formule all-in-one à coller en A1 :
={export!A1\export!G1:AJ1;LET(col;SORT(UNIQUE(export!A2:A));{col\MAP(SEQUENCE(1;30;7);LAMBDA(i;LET(tab;QUERY({export!2:6400};"SELECT Col1, COUNT(Col"&i&") WHERE Col"&i&" > 0 GROUP BY Col1";0);MAP(col;LAMBDA(nom;SIERREUR(RECHERCHEV(nom;tab;2;0);""))))))})}Bonjour,
J'essaie de comprendre la formule :
=MAP(SEQUENCE(1;30;7);LAMBDA(i;LET(tab;QUERY({export!2:6400};"SELECT Col1, COUNT(Col"&i&") WHERE Col"&i&" > 0 GROUP BY Col1";0);MAP(SORT(UNIQUE(export!A2:A));LAMBDA(nom;SIERREUR(RECHERCHEV(nom;tab;2;0);""))))))la première partie (SEQUENCE(1;30;7)) permet de décaler lss colonnes pour aller "chercher" les colonnes où on veut compter car les données recherchées commencent à la colonne 7 ?
la 1ère fonction lambda permet de récupérer les colonnes dans lesquelles des données sont chiffrées sont enregistrées par journée et de les afficher triées par nom
par contre, j'ai du mal à comprendre la dernière partie de la formule !
Cette fonction va générer un tableau de 30 valeurs numérotées (en commençant par 7) :
SEQUENCE(1;30;7)Ensuite la fonction MAP va parcourir chacune de ces valeurs et exécuter la fonction LAMBDA (où la variable i vaudra ce numéro)
=MAP(SEQUENCE(1;30;7);LAMBDA(i;Petite parenthèse : si tu as des bases en Apps Script / JavaScript (ou un autre langage), c'est un peu comme exécuter une boucle for :
for (let i = 7; i <= 37; i++)Donc cette valeur i va permettre de spécifier la colonne dans la requête de la fonction QUERY :
QUERY({export!2:6400};"SELECT Col1, COUNT(Col"&i&") WHERE Col"&i&" > 0 GROUP BY Col1";0)Et pour éviter de recalculer cette formule plein de fois inutilement, je l'ai enregistrée à l'aide de LET et nommé le tableau avec les résultats tab :
LET(tab;QUERY({Ensuite, une nouvelle fonction MAP va parcourir les différents noms (uniques et triés) :
MAP(SORT(UNIQUE(export!A2:A));LAMBDA(nom;Note qu'il aurait été plus optimal de mettre un LET au début de la formule pour éviter de retrier/filtrer cette plage de nombreuses fois (comme c'est le cas dans la seconde version de la formule que j'avais posté).
Et pour terminer, tu as un simple RECHERCHEV qui va rechercher le nom dans tab (qui est un tableau sur 2 colonnes avec les résultats de la fonction QUERY) :
RECHERCHEV(nom;tab;2;0)Entouré d'un SIERREUR pour éviter l'erreur de RECHERCHEV lorsqu'il n'y a pas de résultat :
SIERREUR(RECHERCHEV(nom;tab;2;0);"")