Erreur INDIRECT & SOMMEPROD + DECALER avec la fonction LIGNE()

Version imprimable

15/04/2020, 18h16
Dinytro

Erreur INDIRECT & SOMMEPROD + DECALER avec la fonction LIGNE()

Bonjour,

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

Code:

=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 :

Code:

=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

Code:

=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
16/04/2020, 11h01
papouclo

Bonjour,
Quelle est la version d'Excel utilisée ?
Essayer de valider la formule initiale en formule matricielle par Ctrl + Maj + Entrée.
Cordialement
Claude
16/04/2020, 12h08
Dinytro

Merci pour votre réponse
Je travaille avec Excel 2016
La formule matricielle ne change rien (j'avais oublié de le préciser mais j'avais déjà tenté)
18/04/2020, 15h08
papouclo

Bonjour

Citation:

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)))

Etes-vous sûr que cette formule fonctionne et que vous n'avez pas comme retour d'Excel l'indication d'erreur #REF!.
En effet, DECALER(INDIRECT("B" & 1);-1;4;-10) renvoie à une plage F-9:F0 qui n'existe pas dans la feuille.
La formule
=SOMMEPROD((DECALER(INDIRECT("B" & LIGNE());-1;4;-10))*(DECALER(INDIRECT("B" & LIGNE());-1;15;-10)))
ne renverra une réponse numérique à la place du message d'erreur que pour LIGNE() >10.
Ceci étant, vous avez compliqué les indications des plages intervenant comme paramètres de la fonction SOMMEPROD.
Sauf erreur de ma part, DECALER(INDIRECT("B" & LIGNE());-1;4;-10) et DECALER(INDIRECT("B" & LIGNE());-1;15;-10) désignent respectivement des plages des colonnes F et Q.
En se plaçant par exemple en ligne 15, pourquoi ne pas écrire plus simplement :

Code:

=SOMMEPROD(DECALER($F15;-1;0;-10)*DECALER($Q15;-1;0;-10))

Les références mixtes $F15 et $Q15 (colonnes absolues, ligne relative) s'incrémenteront automatiquement pour la ligne lorsque vous recopierez la formule en changeant de ligne.
Cordialement
Claude