XL 2016 ERREUR #VALEUR AVEC SOMMEPROD, DECALER, INDIRECT ET LIGNE()

[Toniomeyer]

Bonjour,

Je bloque sur une problématique liée à l'utilisation combinée de SOMMEPROD, DECALER, INDIRECT et LIGNE()

=SOMMEPROD((DECALER(INDIRECT("B" & LIGNE());-1;4;-10))*(DECALER(INDIRECT("B" & LIGNE());-1;15;-10)))

Avec ce code, j'ai une erreur #VALEUR

Je me dis alors que la fonction SOMMEPROD génère cette erreur car ce code fonctionne sans soucis :

=SOMME(DECALER(INDIRECT("B" & LIGNE());-1;4;-10))

En faisant des tests, j'ai remarqué que c'était l'utilisation de la fonction LIGNE() dans le SOMMPROD qui posait soucis puisque ce code fonctionne très bien

=SOMMEPROD((DECALER(INDIRECT("B" & 1);-1;4;-10))*(DECALER(INDIRECT("B" & 1);-1;15;-10)))

Sauriez vous m'aider à construire le premier argument de la fonction DECALER (référence) à partir de la ligne en cours ?
Merci

 

[CISCO]

Bonjour

Si tu pouvais mettre en pièce jointe la partie du fichier concernée par cette formule, nous pourrions certainement être plus efficaces.

@ plus

 

[Toniomeyer]

J'ai mis un fichier d'exemple simplifié

En partant de A1, j'écris 1,2,3,4,5,6 en colonne
En partant de A2, j'écris 2,3,4,5,6,7 en colonne

en D1, je mets la formule suivante :

=SOMMEPROD(A1:A6*B1:B6)

résultat : 112

en E1 je mets la formule suivante :

=SOMMEPROD(DECALER(C1;0;-2;6)*DECALER(C1;0;-1;6))

résultat : 112

en F1 je mets la formule suivante :

=SOMMEPROD(DECALER(INDIRECT("C" & LIGNE());0;-2;6)*DECALER(INDIRECT("C" & 1);0;-1;6))

résultat : #VALEUR

par contre, cette formule fonctionne

=SOMMEPROD(DECALER(INDIRECT("C" & 1);0;-2;6)*DECALER(INDIRECT("C" & 1);0;-1;6))

 

[CISCO]

Bonsoir

J'ai déjà constaté ce genre de problème avec la fonction LIGNE(). Il faut faire avec LIGNES($1:1).

@ plus

P.S : Pour voir les calculs intermédiaires, Formule --> Évaluation de formules

 

[Toniomeyer]

Merci pour ta réponse
Malheureusement, ça ne va pas m'aider car mon objectif est bien d'inclure la ligne en cours sans me soucier de son numéro. Ecrire LIGNES($1:1) ou garder le numéro de la ligne en dur c'est pareil...

 

[CISCO]

Bonjour

Qu'est-ce que tu veux faire exactement avec cette formule ? Pourquoi est-ce que tu ne peux pas travailler avec LIGNES ?

Par exemple, ne pourrais-tu faire directement avec

SOMMEPROD(DECALER(C1:C$6;0;-2)*DECALER(C1:$C6;0;-1))

pour n'utiliser que la partie basse du tableau, à partir et en dessous de la ligne en cours ?

@ plus

 

[Toniomeyer]

Je veux que le premier paramètre de DECALER soit dynamique en fonction de la ligne en cours tout simplement (et même si un copier / coller de la fomule fonctionnerait de ligne en ligne, ce n'est pas ce que je cherche, je souhaite que le premier paramètre de DECALER soit lié à la ligne en cours sans avoir besoin de préciser celle ci "en dur"

 

[CISCO]

Bonjour

Si tu fais avec DECALER(C1:C$6;...) (ou DECALER(C10:C$16 si tu veux commencer la plage intéressante ailleurs), la plage utilisée sera dynamique, puisqu'il ni a pas de $ devant le 1.

@ plus

 

[Toniomeyer]

Je connais bien ce principe de base du "$" (je travaille avec Excel depuis un certain nombre d'année pour le boulot) mais ce n'est dynamique que si on copie la cellule et qu'on la colle ailleurs oui. Par contre, si tu colles le texte de la formule dans un autre classeur ça ne fonctionne plus.

Je peux aussi l'appliquer en VBA très facilement avec du RC[-1] mais ce n'est pas ce que je cherche à faire en priorité.

 

[CISCO]

Bonjour

Et comme ça, est-ce que cela te va mieux ? J'ai l'impression que le copier-coller d'un classeur vers un autre ne pose pas de problème.

@ plus

P.S : J'ai utilisé la fonction LIRE.CELLULE qui ne peut être utilisée que dans le gestionnaire de noms, et avec une extension prenant en compte les macros. C'est du XL4, un langage utilisé avant le VBA...