Automatiser des formules décalées selon un repère

[FaridP]

Bonjour à Tous,

Je suis sur un véritable casse tête : chaque mois je dois recopier et modifier les formules du document joint (Plage CH3:ED42) vers le bas et vers la droite selon l'endroit où se situe le repère.

La seule chose qui est totalement arbitraire, c'est l'endroit où je dois placer le repère (actuellement en cellule "CF32").
Du coup, quand le repère descend je dois étirer mes formule vers le bas pour chaque colonne et modifier également chaque formule située entre "CH42" et "ED42".

Je ne trouve pas le moyen de mettre des formules adaptées une bonne fois pour toute et qui me permettraient de n'avoir qu'à placer le repère selon mes besoins. Le comble c'est que je n'ai même pas trouver un autre moyen que de laisser 1 colonne sur 2 vides pour me "simplifier" la vie.

Je ne vous demande pas de le faire ce travail à ma place mais je sais qu'il y a parmi vous des As d'Excel donc si quelqu'un a une idée pour que ce calvaire mensuel ne soit plus qu'un mauvais souvenir, je prends !

Merci pour votre aide et votre soutien et je vous souhaite un excellent week-end,

Farid

 

[hoerwind]

Re : Automatiser des formules décalées selon un repère

Bonjour,

Essaie cette formule en CH42 :

=SOMMEPROD((D3:D40)*(CH3:CH40))/SOMME(INDIRECT("D3:D"&EQUIV("Repère";CF1:CF40)))

elle renvoie le même résultat que celui obtenu avec ta formule, et plus besoin d'ajustements.

Édition 1 :
Il faut cocher le calcul automatique ou appuyer sur le touche clavier F9 pour que le calcul s'effectue !

Édition 2 :
Si "Repère" se situe toujours sur la même ligne que la dernière valeur de CH3:CH40, cette indication est inutile, le numéro de la ligne peut être repris automatiquement sous la formule.

 

[FaridP]

Re : Automatiser des formules décalées selon un repère

Bonjour Hoerwind,

Merci beaucoup !

Effectivement, ça fonctionne pour CH42 mais en revanche, si je la recopie sur la droite, elle ne renvoie pas le bon résultat.

Mais c'est déjà une nette avancée, encore merci !

Farid

 

[FaridP]

Re : Automatiser des formules décalées selon un repère

Re,

Je suis un peu tarte par moment, en fait il faut simplement adapter comme ça :
=SOMMEPROD(($D3:$D40)*(CH3:CH40))/SOMME(INDIRECT("$D3:$D"&EQUIV("Repère";CF1:CF40)-1)).

Est ce que tu as une astuce pour que je n'ai pas à rentrer dans chaque cellule pour rajouter -1, -2, -3, ... Dans la fonction EQUIV ?

Je ne sais pas comment tu as fait pour pondre une telle formule mais c'est vraiment top !

Encore merci,

Farid

 

[hoerwind]

Re : Automatiser des formules décalées selon un repère

Re,

Si tu avais répondu à mon deuxième Édit j'aurai pu te proposer une solution, car la simple copie vers la droite n'est pas possible, étant donné que la colonne CJ a une valeur de moins que la colonne CH, idem pour CL et les suivantes.

Tu connais ton fichier, nous pas, nous ne pouvons que nous fier à ce qu'on voit ... et interpréter.

Édition :
Pas lu ton dernier message avant de poster le mien.
J'attends ta réponse avant de poursuivre.

 

[FaridP]

Re : Automatiser des formules décalées selon un repère

En tout cas, tu m'as sauvé !

Je dois maintenant chercher comment incrémenter automatiquement de -1 quand je me décale vers la droite et surtout trouver une solution pour toutes ces formules au dessus de sorte que je puisse les recopier en tenant compte du décalage de 2 colonnes à chaque fois.

Pas simple mais je suis tellement euphorique grâce à ta formule que je ne veux pas lacher.

Merci pour ton aide précieuse,

Farid

 

[hoerwind]

Re : Automatiser des formules décalées selon un repère

Re,

Explique pourquoi tu mets "Repère" sur telle ou telle ligne, à mon avis c'est à hauteur de la dernière valeur de la colonne CH.

Si c'est cela c'est assez simple à résoudre, mais si ce n'est pas cela, ce n'est pas plus compliqué.
Mais ce ne sera plus pour aujourd'hui (je dois m'absenter), patience jusque demain matin au saut du lit.

 

[FaridP]

Re : Automatiser des formules décalées selon un repère

Re,

"Repère" est mis arbitrairement... Généralement il correspond à la dernière valeur de la colonne CH mais ce n'est pas une règle absolue.

Aucun souci, je patiente même jusqu'à mardi s'il le faut et je continue d'essayer d'utiliser mon cerveau :d.

Bonne soirée à toi,

Farid

 

[hoerwind]

Re : Automatiser des formules décalées selon un repère

Bonjour,

Pour incrémenter une valeur numérique en copiant une formule vers la droite le plus simple est de se référer au numéro de la colonne.
Pour ton fichier, deux formules au choix, à placer en colonne CH :
=(COLONNE()-86)/2
=MOD(COLONNE();86)/2
En colonne CH elles renvoient 0, 1 en CJ, 2 en CL, ...
Dans ces formules 86 peut être remplacé par : COLONNES($A:$CH)

Ce qui sous la formule initiale devient :

=SOMMEPROD(($D3:$D40)*(CH3:CH40))/SOMME(INDIRECT("D3:D"&EQUIV("Repère";$CF1:$CF40)-(COLONNE()-86)/2))

=SOMMEPROD(($D3:$D40)*(CH3:CH40))/SOMME(INDIRECT("D3:D"&EQUIV("Repère";$CF1:$CF40)-MOD(COLONNE();86)/2))

Si la position de "Repère" n'est pas aléatoire mais égale à la dernière valeur de la colonne, la formule est bien plus simple :

=SOMMEPROD(($D3:$D40)*(CH3:CH40))/SOMME(DECALER($D3;;;NB(CH3:CH40)))

Dis-nous si ces propositions te conviennent.

 

[hoerwind]

Re : Automatiser des formules décalées selon un repère

Re,

Petit oubli :
CH43 : =SOMME($CH42:CH42)
à copier en CJ43, CL43, ...

 

[FaridP]

Re : Automatiser des formules décalées selon un repère

Bonjour Hoerwind,

C'est parfait, tu as réglé le souci pour ma ligne 43 et c'est juste ROYAL !
Mon problème persiste pour les colonnes CH, CJ, CL ... Et mes tentatives hier soir assistées par Google n'y ont rien changé.

Je t'explique : si je copie la formule de CH32 en CH33, je suis obligé de rentrer dans la formule pour décaler de 2 colonnes à savoir : remplacer BL33 par BK33.

Je te pose la question seulement au cas où tu aies une solution sans avoir à te casser la tête sur ce problème ; tu en as déjà fait beaucoup pour moi.

Merci pour tes lumières et bon week-end à toi.

Farid

 

[FaridP]

Re : Automatiser des formules décalées selon un repère

Re,

J'ai pu adapter la formule "simplifiée" :

=SOMMEPROD(($D3:$D40)*(CH3:CH40))/SOMME(DECALER($D3;;;NB.SI(CH3:CH40;">9%")))

Mais si je remplace la 1ère parti :

SOMMEPROD(($D3:$D40)*(CH3:CH40))

Par:

SOMMEPROD(($D3:$D40)*(INDIRECT("CH3:CH"&NB.SI(CH3:CH40;">9%")))

Ca ne fonctionne plus et je ne capte pas pourquoi...

T'es bombé sur un relou, je sais donc si ça te saoule, n'hésite pas à me le dire, je comprendrais parfaitement

Farid

 

[Tibo]

Re : Automatiser des formules décalées selon un repère

Bonjour,

Dans une formule basée sur la fonction SOMMEPROD, chaque plage de cellules doit avoir la même dimension.

Dans la formule que tu proposes, la première plage va de la ligne 3 à 40, et celle construite avec INDIRECT va de la ligne 3 à 30.

C'est la raison pour laquelle ta formule ne fonctionne pas.

@+

 

[FaridP]

Re : Automatiser des formules décalées selon un repère

Bonjour Tibo,

C'est bien là le problème car la formule doit être flottante, je ne dois pas définir une plage fixe.

Y a-t-il une variante à SOMMEPROD ?

Bonne journée,

Farid

 

[hoerwind]

Re : Automatiser des formules décalées selon un repère

Bonjour,

Réponse à la question sous le message #11
En pièce jointe proposition de formule en CH3, qui a été copiée vers le bas et en CJ
Je te laisse faire pour les autres colonnes.

Réponse à la question sous le message #12
Tibo t'a donné la raison du #N/A
Pour pouvoir t'aider il faudrait que tu expliques ce que tu souhaites faire, en détaillant le calcul que tu fais manuellement et en précisant la réponse à obtenir.
Essaie quand même cette formule qui répond à ce que j'ai compris :

=SOMMEPROD(($D3:$D40)*(CH3:CH40>9%))