Formule ArrayFormula
Bonjour,
J'ai voulu ajouter ArrayFormula à une formule pour pouvoir l'adapter à la taille du tableau plutôt que de tirer la formule à chaque fois, la taille du tableau pouvant varier.
Malheureusement, si en tirant la formule ça fonctionne bien, avec ArrayFormula ça ne fonctionne plus.
Formule sur chaque ligne =SI(A2=11;A2;SI(ET(N1<>"";A2<>"";B2<>"");N1;"")) avec ArrayFormula =ArrayFormula(SI(A2:A=11;A2:A;SI(ET(N1:N<>"";A2:A<>"";B2:B<>"");N1:N;"")))
Y a-t-il un moyen de généraliser la formule sur chaque ligne du tableau (dont la taille change quotidiennement) sans être obligé de tirer la formule ?
Merci
Bonjour,
cas assez classique où les matrices n'ont pas la même taille : forcément N1:N est plus grand que A2:A
essaie d'utiliser N2:N en lui appliquant un decaler/offset https://support.google.com/docs/answer/3093379?hl=fr
mets un lien vers une copie de ton projet pour aller plus loin
Bonjour,
En plus, il y a un SI ET encapsuler dans le ARRAYFORMULA
cas déjà vu dernièrement voir ce fil https://forum.excel-pratique.com/sheets/formule-si-et-en-matricielle-157284
faire une imbrication de SI au lieu d'utiliser la fonction ET
un truc dans ce genre
=ArrayFormula(SI(A2:A=11;A2:A;SI(N2:N<>"";SI(A2:A<>"";SI(B2:B<>"";N2:N;"");"");"")))
Une autre solution est de remplacer les SI OU ET par une fonction de multiplication genre SUMPRODUCT
Attendons le lien vers le fichier ...
En M2 ma formule ligne à ligne fonctionne
=SI(OU(A2=11;A2=12;A2=13;A2=14;A2=21;A2=22);A2;SI(ET(M1<>"";A2<>"";B2<>"");M1;""))En N2 avec ArrayFormula, je pensais que la formule ne fonctionnait pas
= ArrayFormula (SI ((A2:A = 11) + (A2:A = 12) + (A2:A = 13) + (A2:A = 14) + (A2:A = 21) +(A2:A = 22) ; A2:A; SI ((N1:N <> "") * (A2:A <> "") * (B2:B <> ""); (N1:N); "")))mais en fait elle met beaucoup de temps à se mettre à jour.
Un moyen de l'accélérer ???
https://docs.google.com/spreadsheets/d/1semfWULFCQ8HzjACb-6lvOiXfiJ8p3XefxL0pwWGxrM/edit?usp=sharing
Ce qui prend énormément de temps c'est la seconde partie IF ((N1:N <> "") * (A2:A <> "") * (B2:B <> ""); (N1:N); "")
Du reste, j'étais étonné qu'elle fonctionne, j'ai décomposé en 2 , en O2 et P2 , et effectivement le fichier tend à rajouter indéfiniment une ligne ... parce que les matrices A2:A et N1:N ne sont pas de même hauteur ! Soit on passe par offset si cela fonctionne, soit on passe par une multiplication matricielle.
Ce qu'il faut retenir, c'est que arrayformula ne peut pas admettre des tailles différentes de plages ... et je viens de me relire, c'est bien ce que j'avais indiqué lundi.
edit : offset ne fonctionne pas non plus, il faut tenter une multiplication matricielle, ce que je ferais dans la journée.
je n'ai pas trouvé non plus la recette miracle avec mmult !
j'ai tenté ceci
= ArrayFormula (IF ((A2:A1000 = 11) + (A2:A1000 = 12) + (A2:A1000 = 13) + (A2:A1000 = 14) + (A2:A1000 = 21) +(A2:A1000 = 22) ; A2:A1000; IF ((N1:N999 <> "") * (A2:A1000 <> "") * (B2:B1000 <> ""); (N1:N999); "")))au moins cela ne bogue plus mais la vitesse n'est pas fulgurante !
reste aussi la solution script en emmenant toutes les données d'un seul coup
je ne sais pas si Gilbert a des idées ...
en P2
= ArrayFormula (IF ((A2:A = 11) + (A2:A = 12) + (A2:A = 13) + (A2:A = 14) + (A2:A = 21) +(A2:A = 22) ; A2:A ; if(B2:B="";" ";) ))en Q2
=ArrayFormula(iferror(lookup(row(P2:P);row(P2:P)/if(P2:P<>"";1;0);P2:P);))c 'est instantané
Bonjour,
Je n'ai pas mieux que l'imbrication de la fonction SI comme je l'ai suggéré plus haut pour résoudre le disfonctionnement du ET et du OU avec ARRAYFORMULA...
Bonjour,
Effectivement, les formules en P2 et Q2 fonctionnent mais je ne comprends pas la formule en Q2
=ArrayFormula(iferror(lookup(row(P2:P);row(P2:P)/if(P2:P<>"";1;0);P2:P);))
ce n'est pas l'équivalent de
=iferror(lookup(row(P2);row(P2)/if(P2<>"";1;0);P2);))ligne à ligne.
Et pourtant si on tire la formule, ça ne fonctionne pas (colonne R)
Non, c'est le genre de formules qui ne peut être que globale et non ligne à ligne. Pourquoi ? parce qu'il s'agit d'une recherche dans une liste de nombres triés qui sont ici les lignes renseignées.
L'explication de la formule est que l'on crée une erreur sur le n° de ligne quand la cellule est vide en divisant par 0 ... lookup prend alors la valeur la plus faible
J'ai du mal à visualiser cette formule
=ArrayFormula(iferror(lookup(row(P2:P);row(P2:P)/if(P2:P<>"";1;0);P2:P);))Si en Q on va chercher la valeur en P, je comprends pour la ligne 288 P=11 alors Q=11 mais pour les lignes 289 à 291 P est vide et Q=11, là, j'ai du mal !!!
Après ça semble fonctionner.
Attention, une chose un peu sibylline : il y a des cellules vides, mais il y a aussi des cellules avec un simple espace en P pour que Q fonctionne
Pour rendre cela plus visible, tu peux mettre en P
= ArrayFormula (IF ((A2:A = 11) + (A2:A = 12) + (A2:A = 13) + (A2:A = 14) + (A2:A = 21) +(A2:A = 22) ; A2:A ; if(B2:B="";"_";) ))j'ai remplacé l'espace à la fin de la formule par _ ... temporairement !
Maintenant on voit mieux pour les lignes 289 à 291 ... comme P est vide et vraiment vide là, je vais diviser le n° de ligne par 0
row(P2:P)/if(P2:P<>"";1;0)ce qui crée une erreur du coup ! et à chaque fois que cela se produit, LOOKUP ira chercher la dernière valeur sans erreur. C'est une "extension" de la remarque
Si l'argument clé_recherche est introuvable, l'élément utilisé dans la recherche est la valeur immédiatement inférieure dans la plage indiquée. Par exemple, si l'ensemble de données contient les chiffres 1, 3, 5 et que clé_recherche est égal à 2, le chiffre 1 est utilisé pour la recherche.
du lien suivant https://support.google.com/docs/answer/3256570?hl=fr
J'avais remarqué la subtilité entre cellules vides et cellules avec un espace mais j'étais bloqué sur la particularité de LOOKUP.
Ils sont pas très claire chez Google dans leur remarque, il y a tout de même une différence entre la valeur immédiatement inférieure et la dernière valeur sans erreur !!!
En tout cas, merci pour votre aide.
J'ai rajouté la fonction maintenant() pour accélérer la mise à jour.....c'est un peu plus rapide.
= ArrayFormula (SI (MAINTENANT();SI((A2:A = 11) + (A2:A = 12) + (A2:A = 13) + (A2:A = 14) + (A2:A = 21) +(A2:A = 22) ; A2:A; SI ((N1:N <> "") * (A2:A <> "") * (B2:B <> ""); (N1:N); ""));""))Ils sont pas très claire chez Google dans leur remarque, il y a tout de même une différence entre la valeur immédiatement inférieure et la dernière valeur sans erreur !!!
Il ne faut pas le voir comme cela. Je pense plutôt que le cas "erreur" n'était pas prévu au développement et n'est donc pas documenté, mais c'est in fine une astuce qui peut être utile.
Tu peux aussi utiliser cette formule si elle te convient mieux
=ArrayFormula(VLOOKUP(ROW(P2:P);FILTER({ROW(P2:P)\P2:P};P2:P<>"");2;TRUE))je l'ai mise en R2 de ton fichier.
Bonjour,
De retour après avoir déconnecté pendant 3 semaines, j'ai maintenant l'embarras du choix.
Merci à tous pour toutes ces solutions
Une dernière solution qu'on m'a proposé, si ça peut aider d'autres personnes:
=ArrayFormula(if(A2:A = "";; if((row(A2:A) >= ifna(vlookup(row(A2:A); filter({row(A2:A)\ A2:A}; ifna(match(A2:A; {11\12\13\14\21\22};0)) >0); 1);9^99)) *
(ifna(vlookup(row(A2:A); filter(row(A2:A); (A2:A = "") + (B2:B = ""); ifna(match(A2:A; {11\12\13\14\21\22};0)) =0); 1)) < ifna(vlookup(row(A2:A); filter({row(A2:A)\ A2:A}; ifna(match(A2:A; {11\12\13\14\21\22};0)) >0); 1);9^99))
;vlookup(row(A2:A); filter({row(A2:A)\ A2:A}; ifna(match(A2:A; {11\12\13\14\21\22};0)) >0); 2);)))Apparemment, ça fonctionne mais j'ai un peu de mal à comprendre la formule !!!