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

[nel27]

Bonjour, je vais essayer de m'expliquer clairement.

J'incrémente une formule toutes les deux colonnes ( =arrondi.sup(C11/C8;3)) C8 étant fixe. C11 faisant partie d'une plage C11:C20

Je voudrais que ma formule s'incréments à chaque fois des différentes valeurs de la plage C11:C20.

En fait je voudrais, lorsque je tire ma formule sur la même ligne vers la droite avoir : =ARRONDI.SUP(C11/C8;3) deux cellules vides puis =ARRONDI.SUP(C12/C8;3) deux cellules vides =ARRONDI.SUP(C13/C8;3)... etc
Ça fait un bout de temps que je planche la-dessus sans solution; J'ai tenté avec DECALER imbriqué dans la formule aARRONDI mais sans succès.

Si quelqu'un peut me dépatouiller de ce problème. je lui en serais très reconnaissant.

 

[mapomme]

Bonsoir @nel27,

La formule suivante en E7 à tirer vers la droite :

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

 

[nel27]

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.

 

[mapomme]

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

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

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)

 

[nel27]

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.