[Résolu]calculer somme sur une zone variable

[eric63]

Bonjour,

sur une feuille j’ai x tableaux qui sont rassemblés par groupe en vertical et GROUPÉS PAR TROIS par critères en horizontal,

ces tableaux sont toujours construit de la même manière mais ont des formats différents de taille en nb de lignes et nb de colonnes
ils sont aussi suceptible d’être augmenter ou diminuer au cours des années
voir le fichier fournit
ces différents tableaux ne sont pas complètement séparés d’ou ma difficulté pour trouver les bonnes formules

je cherche à avoir le NOMBRE DE CELLULES de chaque tableau en vert
la somme des cellules valides DE CHAQUE LIGNE DU TABLEAU en violet
et la SOMME DES CELLULES VALIDES TOTALES DU TABLEAU (soit par un chiffre >0 ou une coche une lettre ou un signe) en jaune
normalement je n’ai pas prévu que les cellules contiennent autre chose que des 1 éventuellement des chiffres >1 mais comme je ne maitrise pas cette facette je préfère anticiper aussi avec des signes

je pensais utiliser les fonctions nbval et sommeprod mais je n’arrive pas à mettre les choses en place
et je n’ai pas trouvé dans le forum de solution adapté à ma recherche.

CALCUL GRILLE30.ods

[Jeff]

Bonjour,

Pas de version AOO sous la main pour tester avec ton fichier joint, si tes cellules sont volontairement colorées via un style, j'aurais tendance à t'orienter vers ce fil :
https://forum.openoffice.org/fr/forum/v ... countstyle

Sinon, pour obtenir des définitions de plages variables, ce serait plutôt vers ce fil :
https://forum.openoffice.org/fr/forum/v ... 29&t=42550

A +

[zeguedon]

Bonjour,

si tes cellules sont volontairement colorées via un style, j'aurais tendance à t'orienter vers ce fil :

Pas sûr que l'extension puisse correctement fonctionner vu les styles trouvés dans le fichier :

@+

[Jeff]

Les noms des formats devraient passer, mais c'est sûr que ça ne supportera pas un enregistrement en xls

[eric63]

ben les formats de couleurs vous pouvez les ignorer (c’était plus pour localiser les points qui me chagrine)
ce qui m'importe c'est une formule en fonction des 3 cellules qui puissent être convertible éventuellement en xls aussi et je ne pensais pas que cela aurait pu poser problème pour transformer en fichier xls (ce fichier est retransmis en mairie)

[OOotremer971]

Bonjour,

Je ne comprends pas ce que tu recherches. Normalement lorsqu'une plage est nommée, le fait d'y insérer ligne ou colonne adapte les formules faisant référence à cette plage non ? ou il y a un truc qui m'échappe ? Merci de préciser.

Après tu peux bricoler :
Dans le fichier joint, une solution très simpliste qui devrait s'exporter facilement dans l'autre format (pas sûr à 100% je ne connais pas l'autre )

Dans l'angle inférieur droit du tableau, une formule pour repérer sa position (en J22 dans le fichier) :

Code : Tout sélectionner

=ADRESSE(LIGNE()-1;COLONNE()-1) 

La cellule qui contient cette formule est nommée Limite : menu Feuille>Plage ou expression nommée>Définir (ou raccourci clavier Ctrl+F3 (touche de fonction F3))
La ligne 22 et la colonne J sont volontairement masquées.

La plage s'obtient avec la fonction INDIRECT() :

Code : Tout sélectionner

INDIRECT("A1:"&Limite)

Par exemple en L1 :

Code : Tout sélectionner

=SOMME(INDIRECT("A1:"&Limite))

Si on insère des lignes ou des colonnes dans la plage, la valeur de la cellule nommée Limite s'adapte et si l'on ajoute des valeurs dans ces nouvelles cellules la somme s'adapte .

A+

[eric63]

donc désolé c’est presque ça
mais je ne peux pas calculer a partir de c4 par exemple
mais je n’utilise pas de plages nommées qui m'oblige à nommer chaque tableau ce que je ne souhaites pas faire
les tableaux finaux sont bien plus nombreux et ont la même structure de base

chaque ligne est groupée suivant un critère en colonne A puis est suivie par le nom de l’assocs en colonne B puis d’un espace vide en colonne C
ensuite vient les tableaux eux même (il y en a 3 obligatoires pour un même groupe mais je veux pouvoir modifier le nombre de tableaux en + ou en -)

un tableau1 avec en entête la ligne remplie de 1 pour savoir la valeur maxi de la ligne x par le nombre de lignes du tableau1 en haut de la fin de colonne
suivi d’un tableau2 avec entête remplie de 1 (pas forcément identique en nombre de colonnes)
suivi d’un tableau3 avec entête remplie de 1 (pas forcément identique en nombre de colonnes)

la colonne qui sépare chaque tableau est une zone de calcul sauf la colonne C qui est vide (pour l’instant mais qui pourrait contenir une formule éventuellement)
chaque tableau doit contenir
a - dans la cellule du haut ( en vert) doit apparaitre le nombre maxi de colonnes valides (qui ont un 1 en entête: c‘est une sorte de validation de la colonne)
b - les cellules en dessous (en violet) doivent faire la somme des cellules de la ligne du tableau considéré
c - la dernière cellule en dessous (en jaune) doit contenir la somme effective du tableau (toutes les cellules du tableau considéré contenant un 1)

il y a une ligne vide entre les lignes basse et haute de chaque groupe (sauf en fin de tableau) car les cellules a et c se touchent

mon problème est que je n’arrive pas à calculer a b et c
je cherche d’abord à définir les limites de chaque tableau
cellule début ligne et cellule début colonne
cellule fin de ligne et cellule fin de colonne

ce sont ces limites que je ne sais pas calculer
j’ai une vague idée de m’aider des colonnes ou des lignes contenant une formule pour limites droite et gauche ou haute et basse

et nommer des plages ou des cellules pour les tableaux m’entraine à modifier le tableau ajouté avec de nouveau nommage ajouté

[OOotremer971]

Bonjour,

Une éventuelle solution dans le fichier joint, basée sur la fonction DECALER() comme te le suggérait Jeff (que je salue au passage) dans sa précédente réponse.
Je ne l'ai mise en place que pour un seul tableau, mais j'ai volontairement étendu les formules de la colonne C au delà pour te permettre de mieux comprendre comment le chiffre s'incrémente automatiquement lorsqu'on passe au tableau suivant.
A+

[eric63]

merci ooootremer

je comprends bien ton tableau et il m’apporte de nouvelles façons de voir la chose
J'ai depuis mon dernier envoi potasser et triturer le truc un peu dans tous les sens dans l’espoir d’avoir au final une seule formule pour pour couvrir les 3 sortes de formule de la colonne N sans trouver le graal

donc j’ai obtenu quelques résultats moyennant quelques changements (importants) qui modifient la donne de mes questions
je ne me sert plus des 1 bleus de validation
j’ai en C3 (coin gauche du tableau) une cellule qui me calcule le nombre d’assocs du même groupe (sports)

Code : Tout sélectionner

=NB.SI($A:$A;$A4)

je n’ai plus qu’une ligne de cellule vide de séparation entre deux tableaux (pour être conforme à un autre tableau qui rassemble les sommes calculés sur ce tableau)
pour ça j‘ai remonté la formule qui me calculait la somme du tableau en bas dans la cellule du maximum en haut
j’ai donc une cellule en haut à droite du tableau qui contient 2 infos comme ceci 7/130 soit un total de 7 sur un maximum de 130

Code : Tout sélectionner

=SI(ESTVIDE($C$4);SOMME(N4:INDIRECT(ADRESSE(LIGNE()+$C3;COLONNE())))&"/"&(((COLONNES(($D3:N3)))-(SOMME($D3:L3)/$C3)-1)*$C3);NB($D$4:N4))

avantages:
si j’ajoute une colonne ou supprime une colonne (celle qui touche les sommes) en bout de tableau j’ai les comptes maxi qui suivent pour tous les tableaux
et les sommes sont respectées si j’ajoute un 1 dans la colonne rajoutée pour peu que la cellule somme rajoutée soit valide

Code : Tout sélectionner

=NBVAL($D4:N4)-1

pour la première somme (le champ couvre la cellule de somme sans mettre la cellule en erreur ce que ne permet pas somme ou décaler visiblement)
pour une colonne ajoutée j’ai juste à remplir le critère et de remplir de 1 ou pas
pour une ligne ajoutée il faut insérer une ligne au dessous copier coller la ligne du dessus et modifier le groupe, l’assocs, effacer les 1 ou copier coller spécial format et formule seulement

donc la j’ai gagné 1 formule, j’en ai unifié 2 autres (mais extraire les 2 données de la même cellule se fait facilement)
j’ai essayé de fusionner les 3 mais sans résultat autre que des erreurs 522

CALCUL GRILLE30-1.ods

[OOotremer971]

Bonjour,

avoir au final une seule formule pour pour couvrir les 3 sortes de formule de la colonne N sans trouver le graal

Par exemple en N3 à étirer vers le bas :

Code : Tout sélectionner

=CHOISIR(ESTVIDE(A3)+1;NBVAL($D3:N3)-1;NBVAL(DECALER($C3;1;1;NB.SI(A:A;$A4);(COLONNES($D3:N3)-1)))&"/"&(COLONNES($D3:N3)-1)*NB.SI(A:A;$A4))

Plus besoin de renseigner la colonne C du coup.

On peut encore améliorer

A+

[eric63]

ha oui ça me plait comme formule ça
ça fonctionne même si on ajoute ou supprime des colonnes ou des lignes
je me suis rendu compte que ca ne fonctionne pas si tu supprime la première ligne de données (ligne 4)
mais c’est pas de ton fait car mes modèles de formules avait ce problème aussi,
je m’en suis rendu compte sur mon fichier de travail bien sur et j’ai passé quasi une journée entière à trouver et corriger ces foutus formules
mais c’est loin d’être parfait. En gros la formule est basée sur la ligne de formule et pas sur les lignes suivantes mais j’ai ajouté une variable en N2 pour le nombre de colonnes (critère) utiles et je n’ai retenu que le premier chiffre de la cellule N3 ce qui me permet de faire des comptes plus «propre»

je ne sais comment faire pour les deux autres groupe de colonnes pour calculer leurs nombre de colonnes respectifs

je vais décortiquer ta formule pour comprendre comment elle marche
et essayer de l’adapter aux deux autres colonnes U et AA
qui dans mon graal ont la même formule et suivent les ajout ou suppression de colonnes
mais à cette heure ci je réfléchis moins bien voir plus du tout

[OOotremer971]

je ne sais comment faire pour les deux autres groupe de colonnes pour calculer leurs nombre de colonnes respectifs

C'est pas compliqué je reviendrai avec une proposition (manque cruel de temps libre).

je me suis rendu compte que ca ne fonctionne pas si tu supprime la première ligne de données (ligne 4)

Avec cette nuance tu peux la supprimer. Le fait de supprimer la ligne, supprime aussi la référence à cette ligne dans la formule. Avec la fonction DECALER() pour indiquer le critère à NB.SI() ça fonctionne mieux

Code : Tout sélectionner

=CHOISIR(ESTVIDE(A3)+1;NBVAL($D3:N3)-1;NBVAL(DECALER($C3;1;1;NB.SI(A:A;DECALER($A3;1;0));(COLONNES($D3:N3)-1)))&"/"&(COLONNES($D3:N3)-1)*NB.SI(A:A;DECALER($A3;1;0)))

Merci

A+

[eric63]

oui avec cette nouvelle formule intégrant décaler je peux supprimer la 1ere ligne ou la 1ere colonne (qui produisait aussi le même problème)
c’est vraiment topissime et y a pas d’émoticones pour cela
et j’avoue que je suis loin de ton niveau pour comprendre les «nuances» et surtout résoudre ces problèmes de nuances.
J’enregistre certaines nuances à force d’erreurs qui forment mon expérience et j’ai surtout pour l’instant plus d’erreurs que d’expérience au gré de mes avancées du tableur.

C'est pas compliqué je reviendrai avec une proposition (manque cruel de temps libre).

j’adore ta façon de voir la chose parce que c’est absolument pas évident pour moi et je me réjouis qu’une solution (unique ? ce serais vraiment le graal des graaux) soit viable
j’attendrais en essayant de trouver de mon coté
tu as résolu la partie la plus urgente des tableaux car ma femme mon pseudo employeur (j’suis à la retraite) me presse de trouver un tableau qui soit présentable au sein d’une commission de mairie d’ici la semaine dernière
donc j’ai gagné une semaine de délai

[OOotremer971]

je me réjouis qu’une solution (unique ? ce serais vraiment le graal des graaux) soit viable

La voici :

Code : Tout sélectionner

=CHOISIR(ESTVIDE($A3)+1;NBVAL(DECALER($A3;0;(COLONNE()-_NbCol)-1;1;_NbCol));NBVAL(DECALER($A3;1;(COLONNE()-_NbCol)-1;_NbLignes;_NbCol))&"/"&_NbCol*_NbLignes)

Pour simplifier lecture et maintenance cette formule fait appel à 2 formules nommées.
Une formules nommées est un raccourci vers une autre formule stockée dans la zone de nom (Ctrl+F3)

_NbCol (adressage relatif de la colonne ; positionné en D3): calcule le nombre de colonne de chaque tableau (ce que tu appelles les colonnes valides)

Code : Tout sélectionner

(-4)+SOMMEPROD(PETITE.VALEUR(SI(NON(ESTVIDE($A$3:D$3));COLONNE($A$3:D$3));NBVAL($A$3:D$3)))-SIERREUR((-3)+(SOMMEPROD(PETITE.VALEUR(SI(NON(ESTVIDE($A$3:D$3));COLONNE($A$3:D$3));NBVAL($A$3:D$3)-1)));0)

_NbLignes : calcul de nombre de ligne de chaque tableau

Code : Tout sélectionner

NB.SI($A:$A;DECALER($A3;1;0))

J'ignore si les formules nommées s'exportent correctement au format xls. Le cas échéant, il faudra remplacer toutes les occurrences des raccourcis par la formule qu'ils invoquent.

A+

[eric63]

impressionnant j’ai pas d’autre mot
je testerai sur mon fichier de travail après avoir essayer de décortiquer tes formules pour les comprendre.
*déjà que les formules précédentes m’ont pris plus d’une demi journée pour les analyser …
ça va me prendre quelques heures voir jours

j’aurais voulu savoir comment tu arrives à générer une formule aussi longue (_NbCol) sans t’emmêler les neurones et sans te faire de nœuds.
Rien que la formule nommée _NbLignes=NB.SI($A:$A;DECALER($A9;1;0)) je n’aurais absolument pas penser à utiliser DECALER

truc non prévu encore hier matin
J’ai suis obligé en l’état actuel de mes connaissance et de mon fichier de rajouter manuellement une formule dans les cellules vides au dessus des assocs colonne B qui reporte le groupe en B3 =A4 (valable pour toutes les cellules vides rencontrées)
et tu fais référence à ces cellules dans ta formule (A3:D3) ce qui à pour effet de comptabiliser une ligne supplémentaire en première incidence (il y en a peut être d’autres mais j’ai pas eut le temps de m’en apercevoir encore)
y a t il un moyen de décompter cette ligne ?

merci pour le temps que tu consacre ta patience et tes retours dictaticiels

[OOotremer971]

Bonjour,

obligé en l’état actuel de mes connaissance et de mon fichier de rajouter manuellement une formule dans les cellules vides au dessus des assocs colonne B qui reporte le groupe en B3 =A4 (valable pour toutes les cellules vides rencontrées).et tu fais référence à ces cellules dans ta formule (A3:D3)

Pa sûr d'avoir bien compris. Regarde si ça convient. J'ai décalé le comptage pour qu'il commence à la colonne C.

A+

[eric63]

comment dire en deux mots
PAR FAIT
je considère comme résolu et ne pas t’embêter plus
je potasse la formule pour comprendre la structure et passerait une fois compris en résolu.
et poser mes interrogations si je comprends pas certains trucs
Ca risque de prendre quelque jours

Grand merci en tout cas

[eric63]

bon hum désolé
mais je me penche sur la formule tirée de la dernière colonne des tableaux que je comprends et qui est clair par contre le champ nommé _NbCol qui contient

Code : Tout sélectionner

(-4)+SOMMEPROD(PETITE.VALEUR(SI(NON(ESTVIDE($C$3:AA$3));COLONNE($C$3:AA$3));NBVAL($C$3:AA$3)))-SIERREUR((-3)+(SOMMEPROD(PETITE.VALEUR(SI(NON(ESTVIDE($C$3:AA$3));COLONNE($C$3:AA$3));NBVAL($C$3:AA$3)-1)));0)

j’ai carrément plus de mal à la décrypter
et cerise sur le gâteau il faut que j’intercale une colonne vide ou qui restera vide après chaque colonne des formules.
cela sert pour faire un copier uniquement des 3 fois 2 colonnes qui resterons visibles
et de faire un coller multiple dans une autre feuille qui reporte uniquement les résultats des formules et de la colonne vide conjointe
cette colonne vide sera remplie par une formule qui dépendra des données de la formule et de données de la nouvelle feuille (valeurs du point en € suivant groupe d’appartenance)
Je suis fort contrarié de ne pouvoir résoudre moi même cette nouvelle donnée à l’équation

[OOotremer971]

Bonjour,

j’ai carrément plus de mal à la décrypter

Code : Tout sélectionner

SI(NON(ESTVIDE($C$3:AA$3));COLONNE($C$3:AA$3))

Cette partie de la formule retourne une matrice contenant le numéro de toutes les colonnes occupées (non vides) de la ligne 3. La fonction PETITE.VALEUR() permet donc de savoir où se trouve la première colonne de formules, puis la deuxième etc.., et par soustraction de l'une à l'autre, connaître le nombre de colonnes constituant un tableau. Le rang de PETITE.VALEUR() est obtenu par NBVAL($C$3:AA$3) ; plus on s'éloigne de $C$3, vers la droite du tableau, plus la quantité augmente.
Actuellement la première parie de la formule démarre avec un décalage de (-4) et la seconde avec un décalage de (-3) parce qu’il y a une colonne d'écart entre la fin d'un tableau et le début du suivant.
Si tu dois insérer une nouvelle colonne vide à droite des colonnes contenant la formule (c'est la même formule pour toutes les colonnes de formules), il suffit d'augmenter le décalage entre (-4) et (-3) en remplaçant (-3) par (-2) dans la formule nommée _NbCol.
Mais n'espère pas pouvoir ajouter des formules ou des valeurs sur la ligne 3, autre que les formules déjà existantes.

Code : Tout sélectionner

(-4)+SOMMEPROD(PETITE.VALEUR(SI(NON(ESTVIDE($C$3:AG$3));COLONNE($C$3:AG$3));NBVAL($C$3:AG$3)))-SIERREUR((-2)+(SOMMEPROD(PETITE.VALEUR(SI(NON(ESTVIDE($C$3:AG$3));COLONNE($C$3:AG$3));NBVAL($C$3:AG$3)-1)));0)

A+

[eric63]

j’avais trouvé une demi solution en modifiant 1 terme dans _NbCol et dans 1 terme dans la formule pour que le résultat de colonne soit cohérent et en mettant un «ne rien inscrire dans la colonne vide» car elle était inclue dans le comptage
mais ta solution est évidemment bien meilleure
et en plus j’ai une explication de ce qui ce passe dans cette formule nommée que j’étais bien loin de décrypter

un grand merçi