XL 2016 Incrémentation d'une formule horizontalement en faisant référence à une plage verticale.

Bonsoir @nel27,

La formule suivante en E7 à tirer vers la droite :

Bonjour @mapomme,

un grand merci pour cette formule. Mais si je n'abuse pas de trop, pourrais tu m'expliquer le cheminement de celle-ci? Je ne vois pas où elle va chercher la colonne C de mes valeurs variables. J'utilise Excel depuis peu.

Merci.

=SI(MOD(COLONNES($E1:E1);3)=1;ARRONDI.SUP(DECALER($C11;ENT(COLONNES($E1:E1)/3);0)/$C$8;3);"")

COLONNES($E1:E1) compte le nombre de colonnes depuis la colonne fixe $E1 (d'où la présence du $) jusqu'à la colonne E1.
Quand on tire, cette formule depuis la cellule E1 vers la droite, cette formule va donner (depuis le cellule E1) la suite de nombre 1,2,3,4,5,6,7,...
On s’aperçoit qu'on veut un résultat que quand cette formule donne 1, 4, 7,10,13,...
Pour cela on calcule le reste de la division par 3 de la suite 1,2,3,4,5,6,7 (on utilise la fonction MOD())
cela donne 1,2,0,1,2,0,1,2,0,1,2,0,...
On a bien le reste qui vaut 1 pour les valeurs 1, 4, 7, 10...
C'est justement les cellules pour lesquelles on veut un résultat.
On arrive donc à la formule intermédiaire:
Si le reste de la division de nombre de colonnes depuis E1 est égal à 1 alors on fait le calcul désiré sinon on renvoie la chaine vide, soit :
=si( MOD(COLONNES($E1:E1);3)=1; la formule de calcul ; "")

Pour la suite (la formule), attendre cet après-midi...

mapomme à dit:

ARRONDI.SUP(DECALER($C11;ENT(COLONNES($E1:E1)/3);0)/$C$8;3)Pour la suite (la formule), attendre cet après-midi...

Cliquez pour agrandir...

pour la formule en E1, on écrit :
ARRONDI.SUP(C11/3);0)/$C$8;3)

Il faut que toutes les trois cellules, on descende d'une cellule dans la colonne C à partir de C11.
Pour désigner toujours la colonne C quand on tire la formule vers la droite, on fixe la colonne C avec un "$"
ARRONDI.SUP($C11/3);0)/$C$8;3)

Comme dans le message ci-dessus, on va utiliser la quantité variable quand on se déplace vers la droite : COLONNES($E1:E1)

Mais cette fois ci, on ne va pas prendre le reste de la division par 3, mais la partie entière de la division par 3.
On a donc pour COLONNES($E1:E1), la suite: 1 2 3 4 5 6 7 8 9 10 ...
On divise par 3, ce qui nous donne: 0,333 0,667 1 1,333 1,667 2 2,333 2,667 3 3,333
On prend la partie entière, ce qui nous donne: 0 0 1 1 1 2 2 2 3 3

Je rappelle que les cellules (voir précédent message) qui nous intéressent sont à la position 1, 3, 7 , 10, ...
Ce qui nous donne, pour ces positions, dans la suite des parties entières, les nombres: 0, 1, 2 , 3, ...
On remarque que c'est exactement le nombre de cellules qu'on doit décaler à partir de $C11.
Pour E7, on doit décaler de 0 vers le bas
Pour E10, on doit décaler de 1 vers le bas
Pour E13, on doit décaler de 2 vers le bas...

Le formule de calcul du décalage vers le bas est donc: DECALER($C11;ENT(COLONNES($E1:E1)/3);0)
La formule complète est donc:ARRONDI.SUP(DECALER($C11;ENT(COLONNES($E1:E1)/3);0)/$C$8;3)

Bonsoir ma pomme. Désolé de ne reprendre le fil de la conversation que maintenant.
Les explications sont au top et franchement il m'aurait été impossible de trouver cette formule tout seul.
C'est super de pouvoir compter sur des personnes aussi compétentes.
Encore un grand merci.