NBVAL et ArrayFormula (résolu)

Bonjour,

J'ai un tableau dans Google sheet, qui est relié à un formulaire, dont il enregistre les réponses.
Dans ce tableau, créé automatiquement par forms, j'ai rajouté des colonnes pour pouvoir faire des totaux.

Afin de ne pas avoir à ressaisir la formule (ou en faire un copier-coller) chaque fois qu'un enregistrement se fait, j'utilise un arrayformula en tête de colonne.
Par exemple pour additionner les cellules de la colonne A et celle de la colonne B, en A1, je mets cette formule:

=ArrayFormula(A2:A+B2:B)

ainsi à chaque ligne, le total des cellules correspondantes s'affiche automatiquement.

Là où ça se complique c'est que dans chaque ligne j'ai une série de cellules mitoyennes que je veux compter si elles contiennent une valeur, et je me sers de cette valeur pour faire un calcul dans une autre cellule.
Par exemple la formule de comptage pour la ligne 2, sera =NBVAL(H2:O2).
Ça cela fonctionne.

Le problème arrive lorsque je veux faire avec ArrayFormula, comme pour les sommes: théoriquement ça devrait être une formule du type
=ArrayFormula(NBVAL (H2:H:O2:O))

Mais ça ne fonctionne pas et je n'arrive pas à trouver la bonne syntaxe pour que cela fonctionne...

Alors il y a sûrement une question de calcul matriciel ou quelque chose comme ça, mais j'avoue que les matrices, je n'y comprends pas grand-chose déjà au lycée c'était pas mon truc) et ce que j'ai pu trouver sur des forums anglophones ne fonctionne pas chez moi: même si je reproduis exactement les cas dont ils parlent ça me met une erreur au niveau de la formule.
Par exemple cette formule ( que j'avoue ne pas comprendre) devrait donner ce que je veux:
=ArrayFormula({"COUNTIF";mmult(IF(H2:O<>"",1,0),transpose(column(H2:O)^0))})
Mais tout ce qu'elle me donne, c'est une #Erreur....

Bref je ne sais pas trop quoi faire: en attendant, je me dépanne en faisant un copier-coller de ma formule "classique" à chaque enregistrement, mais c'est contraignant.

Bonjour,
Voir sur cette page
de ce que j'ai pu observer, il ne faut pas d'autres formules dans la colonne de l'ARRAYFORMULA
Si je reprends le fichier qui est dans le lien on peut transformer la formule en
=ARRAYFORMULA(IF(B2:B<>"";B2:B*C2:C;""))
ainsi si on ajoute une ligne, la multiplication s'effectue
Il faut partager ton fichier pour voir comment il se présente

Bonjour,
Comme je le disais, il y a le pendant de VBA dans Sheets Voici une vidéo de première importance pour commencer à coder et ainsi se libérer des formules abscondes
Elle montre comment récupérer les valeurs des cellules et la fameuse possibilité de détecter la dernière ligne d'un tableau

Bonjour,

Oui, je sais qu'il ne faut aucune données en dessous de l'arrayformula, d'ailleurs, s'il y en a Sheets signale qu'il n'a pas mis les résultats afin de ne pas écraser ce qui s'y trouve
...et je connais ce site, que j'ai déjà épluché

Bonjour,

oui, il a un site aussi ce Derien, et en général c'est bien exxpliqué
Mais donc je voudrais éviter de me servir de Apps script, pour ce que je veux faire là.
Plus tard par contre, sur mon fichier, je vais faire un formulaire de saisie et là, par contre, obligé, j'utiliserai Apps Script, comme ici

Bonjour,
Tu dois imbriquer les fonctions =ARRAYFORMULA(SOMME(H2:H)+SOMME(O2:O))
ou plutôt =ARRAYFORMULA(NBVAL(H2:H)+NBVAL(O2:O))
Quand on doit élaborer des formules un peu plus compliquées il faut décomposer temporairement les actions, donc faire un =NBVAL(H2:H) on voit que ça fonctionne, on fait pareil pour la colonne O et ensuite on reprend les 2 formules et on passe à l'addition, ARRAYFORMULA nous évite en fait de devoir recopier la formule dans toutes les cellules qui se trouvent en dessous. Et si le résultat est concluant, on peut alors supprimer ce que l'on a fait temporairement

Bonjour,
J'ai déjà essayé ça, mais malheureusement, ça ne fait que donner le nombre total de toutes les lignes et non pas les totaux ligne par ligne

Je t'ai envoyé un mp avec le lien vers une copie de mon fichier, que tu dois pouvoir ouvrir...

Bonjour,
bin un total par ligne, il ne faut pas chercher midi à quatorze heures, tu fais simplement =H2+O2 et tu tires la formule à la souris sur autant de ligne que nécessaire comme les adresses ne sont pas absolues, elles vont s'incrémenter tout seules, il ne faut pas chercher nécessairement la difficulté
Ensuite tu protèges les cellules pour que l'on ne vienne pas effacer tes formules
mais pourtant, ça fonctionne =ARRAYFORMULA(SI(H2:H+O2:O=0;"";H2:H+O2:O))
regarde la cellule Q2 de ce fichier
J'ai ajouté un "IF" pour éviter de voir tous les zéros sur les lignes vides

Attention au départ, tu as parlé de NBVAL ce qui vérifie si une valeur se trouve dans la cellule ou non, cela ne fait pas un total donc sur 2 colonnes tu ne pouvais retrouver que les valeurs 0, 1 ou 2 soit rien dans les 2 colonnes, seulement 1 colonne remplie par un chiffre, soit 2 colonnes remplies chacune par un chiffre

Bonjour,

oui oui, je sais bien qu'en recopiant la formule, ça me donne le résultat ligne par ligne: c'est d'ailleurs comme ça que je fais actuellement pour cette colonne-là.

Seulement, ce fichier est destiné à être manipulé par des gens timorés par l'informatique, pour qui, moins ils auront à faire de modif, mieux ce sera. Déjà, rien que de devoir saisir le paiement, ça leur pose presque un problème ! Alors devoir recopier une formule ou cliquer sur un bouton pour le faire, oups, ils ont trop peur de se tromper !
Peut-être même que je vais le relier à un Google Forms pour saisir un nouvel adhérent, donc on a tout intérêt à ce que les colonnes se calculent toutes seules sans avoir à faire de manœuvre, comme dans les colonnes G F, C et Q

Dans ma feuille, si tu as bien regardé

- en Q, la formule fait apparaître en clair le nom des activités renseignées:
=ARRAYFORMULA(SI(H5:H<>"";"Chant ";"")&SI(I5:I<>"";"Couture ";"")&SI(J5:J<>"";"Danse ";"")&SI(K5:K<>"";"Marche ";"")&SI(L5<>"";"Parole ";"")&SI(M5:M<>"";"Peinture ";"")&SI(N5:N<>"";"QiQong ";"")&SI(O5:O<>"";"Yoga ";""))

- en G, la cotisation de base est calculée selon que l'activité de la colonne J est renseignée ou non, et seule renseignée ou non (le SI tient compte si la colonne B , qui contient le nom de l'adhérent, est vide ou non):
=ArrayFormula(SI(ESTVIDE(B5:B200);;SI(ESTVIDE(J5:J200);$G$1;SI(P5:P200>1;$G$1;$G$1/2))))

- en F, la cotisation totale par adhérent se calcule en fonction de la cotisation de base + les éventuelles cotisations d'activités des colonnes H à O)
=ArrayFormula(SI(ESTVIDE(B5:B200);"";G5:G+H5:H+I5:I+J5:J+K5:K+L5:L+M5:M+N5:N+O5:O))

- en C, le solde dû se calcule en fonction de la colonne F (total cotisation) et de la D où on saisit le paiement.
=ArrayFormula(SI(F5:F="";"";F5:F-D5:D))

Pour ces colonnes-là, dès que le nom et les activités sont renseignés, ça se remplit tout seul.

La colonne P, compte le nombre d'activités par adhérent avec la formule: =SI(NON(ESTVIDE(B5));NBVAL(H5:O5);"")
et je voulais de même que pour les autres, ne pas avoir besoin de la recopier à chaque ligne

d'où ma demande

Bonjour,

Finalement, vu que les calculs matriciels avec accolades ne semblent pas fonctionner chez moi, mais uniquement les ArrayFormula (et je ne sais pas pourquoi) j'ai trouvé une solution un peu lourde mais qui fonctionne:

- j'ai créé un nombre de colonnes égal à celui de celles dont il m'intéressait de compter les cellules non vides (colonnes H à O soit 8 col. => création des colonnes Q à X soit 8 col.)

- Dans la 1ère cellule de la colonne Q (donc en Q5), j'ai mis la formule:

=ArrayFormula(SI(ESTVIDE(B5:B);"";(SI(NON(ESTVIDE(H5:O));1;0))))

Celle-ci m'a donné un tableau de valeurs 0 ou 1, qui m'a rempli les colonnes de Q à X sur toutes les lignes

- Il m'a suffit ensuite de mettre une formule qui calcule le total de chaque ligne de ces colonnes et l'affiche, soit
=ArrayFormula(SI(ESTVIDE(B5:B);"";Q5:Q+R5:R+S5:S+T5:T+U5:U+V5:V+W5:W+X5:X))

et mon nombre d'activité par ligne se calcule tout seul !

enfin, j'ai masqué les colonnes Q à X

Bonjour,
je suis d'accord que certains utilisateurs ne soient pas à l'aise avec l'informatique, c'est pour cela que j'ai parlé de protéger tes formules ou alors, pour ne pas avoir de formules dans les feuilles, il faut tout programmer soit en macros, soit avec App Script, l'idée du formulaire de saisie est bonne aussi
maintenant tes soucis sont ils résolus à ce stade ?

Bonjour,

Avec Google sheet, pour ne pas avoir de formules dans une feuille, on peut aussi importer le résultat d'une feuille dans une autre don't on donne le lien aux gens, ainsi il n'y a que des données non modifiables directement: s'ils veulent modifier qq chose, on peut faire en sorte que les gens soient obligés de passer par un Google Form, et ainsi ils n'ont pas du tout accès à la feuille de base où il y a les calculs.

Mes soucis du moment sont résolus, sauf que je ne sais pas pourquoi les formules matricielles avec accolades ne fonctionnent pas chez moi...

Heu... Je ne me rappelle plus comment on passe un sujet en résolu ?

Bonjour,
on vient de me donner une solution beaucoup plus simple:

=ByRow(H2:O50; LAMBDA(ligne; NBVAL(ligne)))

J'ai encore des tas de choses à apprendre...