SommeProd avec nombres de lignes variables

[MiM]

Bonjour à tous et toutes,

Ayant déjà eut pas mal de renseignements utiles sur ce site, j'aimerais soumettre un problème avec la fonction SOMMEPROD. Cette fonction est très utile, mais dans mes feuilles Excel, la longueur des lignes varient très souvent. Or je constate, que lorsque ma plage de cellules est définie pour une longueur jugée suffisante, celle-ci se réduit à rien après par exemple un effacement de cette feuille.

Exemple : au départ '=SOMMEPROD(('2006 Intervention AMEC SPIE'!$E$2:$E$600=11)*('2006 Intervention AMEC SPIE'!$I$2:$I$600="ok"))

puis après une effacement de la feuille précitée, ma formule est devenue :

'=SOMMEPROD(('2006 Intervention AMEC SPIE'!$E$2:$E$2=11)*('2006 Intervention AMEC SPIE'!$I$2:$I$2="ok"))

Je dois alors remettre manuellement le 600.

Avez-vous une idée de mon erreur de compréhension?

Merci d'avance.

 

[Gringo]

Re : SommeProd avec nombres de lignes variables

Bonjour MiM,

Le changement dans tes formules est dû au fait que tu supprimes les cellules de ta feuille. Si tu pouvais faire un clearcontents à la place, tes formules ne changeraient pas.

Si tu veux faire autrement, tu peux te faire une macro qui supprime tes lignes dans ta feuille de données et inscrire ta formule Sumprod dans ta macro. Ainsi, chaque fois que tes données seraient supprimées, ta fonction serait réinitialisée.

J'espère que ca t'aide un peu...

Gringo

 

[Monique]

Re : SommeProd avec nombres de lignes variables

Bonjour,

Tu peux éviter ça en nommant les deux plages par formule.

Pour la colonne qui démarre en E2 :
Insertion - Nom - Définir
En haut, dans le zone "Noms dans le classeur", tu tapes le nom choisi, (ZoneA par exemple)
en bas, dans la zone "Fait référence à", tu tapes :
=DECALER($E$2;;;NBVAL($E:$E))
puis tu cliques sur "Ajouter

Pour la colonne qui démarre en I2 (ZoneB par exemple) :
=DECALER($I$2;;;NBVAL($E:$E))

De cette façon, SommeProd ne tient compte que du nombre de lignes nécessaire.

La formule devient :
SOMMEPROD((ZoneA=11)*(ZoneB="ok"))
En plus, ça accélère les calculs et ça allège le fichier

 

[mjo]

Re : SommeProd avec nombres de lignes variables

Bonjour,

Préciser les plages dans deux cellules quelconques.
Dans la formule SOMMEPROD faire référence à ces cellules par la fonction INDIRECT.

mjo

 

[Gorfael]

Re : SommeProd avec nombres de lignes variables

Bonjour à tous et toutes,

Ayant déjà eut pas mal de renseignements utiles sur ce site, j'aimerais soumettre un problème avec la fonction SOMMEPROD. Cette fonction est très utile, mais dans mes feuilles Excel, la longueur des lignes varient très souvent. Or je constate, que lorsque ma plage de cellules est définie pour une longueur jugée suffisante, celle-ci se réduit à rien après par exemple un effacement de cette feuille.

Exemple : au départ '=SOMMEPROD(('2006 Intervention AMEC SPIE'!$E$2:$E$600=11)*('2006 Intervention AMEC SPIE'!$I$2:$I$600="ok"))

puis après une effacement de la feuille précitée, ma formule est devenue :

'=SOMMEPROD(('2006 Intervention AMEC SPIE'!$E$2:$E$2=11)*('2006 Intervention AMEC SPIE'!$I$2:$I$2="ok"))

Je dois alors remettre manuellement le 600.

Avez-vous une idée de mon erreur de compréhension?

Merci d'avance.

Salut

Cette fonction est très utile, mais dans mes feuilles Excel, la longueur des lignes varient très souvent.

Traduction : le nombre de lignes ?

puis après une effacement de la feuille précitée, ma formule est devenue

Effacement des données ou suppression des lignes de données ?

Rappel : Excel est Hyper-super-méga-génial, mais....... c'est un autiste : il n'interp^réte pas, il obéit. Même si c'est une connerie, on lui dit de faire, il fait.
Et comme il essaie d'aider, quand tu as une zone définie A1:E12, si tu mets un fond rouge et que dans une autre cellule tu mets la formule :
=Somme($A$1:$E$12) ou Somme(A1:E12)
Si tu supprimes les colonnes A à D et les lignes 1 à 11, tu te retrouves avec une seule cellule coloriée en rouge et Excel, pas con, il se dit : si je faisais la somme des cellules en rouge, je dois continuer. Et donc il faut :
=Somme ($A$1:$A$1) ou Somme(A1:A1), ce qu'on lui a demandé
alors que si tu effaces toutes les valeurs de A1:E12, il se dit : on change de valeurs, mais pas de formule et il garde lâ même formule

ce qui peut (pzut-être) épliquer pour ta formule évolue
Le vrai problème avec Excel, c'est que, quand on voit ce qu'il est capable de faire, on oublie qu'il ne fait qu'obéir aux ordres

A+

 

[MiM]

Re : SommeProd avec nombres de lignes variables

Je te remercie beaucoup pour cette réponse. Je vais l'appliquer demain, et bien sûr faire un test. La fonction décaler, peut-elle s'appliquer sur un nombre de lignes variables à imprimer?

 

[Monique]

Re : SommeProd avec nombres de lignes variables

Re,

On peut aussi définir une zone d'impression par la fonction Decaler()
Decaler(1ère cellule en haut à gauche;0;0;longueur : Nbval(1 des colonnes);Largeur : nb de colonnes)

Un tableau démarrant en A1, 8 colonnes, longueur variable :
Insertion - Nom - Définir
Dans la zone du haut, taper :
Zone_d_impression
avec les deux _ et sans apostrophe
Dans la zone du bas, taper :
=DECALER($A$1;;;NBVAL($A:$A);8)
Cliquer sur "Ajouter" puis "Fermer"

 

[MiM]

Re : SommeProd avec nombres de lignes variables

Merci pour cette réponse, qui répond entièrement à ce que je souhaites. Je l'applique dès demain à mon dossier.

Encore un grand merci pour cette réponse rapide et efficace.