Excel 2007: copie de formule??... Résolu/Fermé

Question

Bonjour, 

J'ai un problème que je cherche à résoudre depuis longtemps, je vais essayer de me faire le plus clair possible:

Exemple:

A1    Quantité
A2    2
A3    5
A4    4
A5    5
A6    8
A7    2
A8    4
A9    6
...

Je veux transposé des sommes de 3 cellules en horizontales sur une autre feuille, j'entre cette formule:
                           =somme(feuil1!A2:A4)
sur la deuxième ligne, j'entre à la main:
                           =somme(feuil1!A5:A7)
et ainsi de suite pour plusieurs données...

Lorsque je répète les formules (à l'aide de ma souris en cliquant dans le coin inférieur droit de la cellule et déroulant la copie des formules vers les cellules du bas), la troisième formules sera donc : 
                           =somme(feuil1!A6:A8)
mais je voudrais que ce soit
                           =somme(feuil1!A8:A10)

Est ce que quelqu'un aurait une idée de la facon de faire SVP?

Merci!!


Configuration: Windows 7 / Firefox 10.0.2

gbinforme · Answer

bonjour,

Si dans ta seconde feuille tu commences en ligne 2, tu peux mettre cette formule :

=SOMME(INDIRECT("Feuil1!A"&((LIGNE()-1)*3)-1&":A"&((LIGNE()-1)*3)+1))
En "tirant ta formule vers le bas, cela devrait te donner le résultat attendu.

gbinforme · Answer

bonjour,

Dans ta question tu dis :
j'entre cette formule: =somme(feuil1!A2:A4)
sur la deuxième ligne, j'entre à la main: =somme(feuil1!A5:A7) 
Dans ton classeur tu veux :
=SOMME(Feuil1!A1:A3)
=SOMME(Feuil1!A4:A6)
Après, cela se résume à de l'arithmétique élémentaire :

((LIGNE()-1)*3)-1 en ligne 2 équivaut à 
(2 -1) x 3 donc 3 et l'on soustrait 1 pour avoir le 2 de "A2" 
car la fonction  (LIGNE() fournit le numéro de la ligne où elle est située.
Pour avoir 1 comme dans ton classeur, il faut soustraire 2 :

=SOMME(INDIRECT("Feuil1!A"&((LIGNE()-1)*3)-2&":A"&((LIGNE()-1)*3)))
Tu peux transposer les explications sur la seconde partie de la formule pour la borne supérieure de la plage :
((LIGNE()-1)*3)) en ligne 2 équivaut à 
(2 -1) x 3 donc 3 ce que tu souhaites pour avoir le 3 de "A3".

Lorsque tu tires vers le bas ta formule, tu obtiens en ligne 3 :
- (3 -1) x 3 donc 6 et l'on soustrait 2 pour avoir le 4 de "A4" 
- (3 -1) x 3 donc 6 ce que tu souhaites pour avoir le 6 de "A6"
et ainsi de suite.

As-tu compris comment fonctionne la formule ?

po4419 · Answer

Merci gbinforme,

Wow! Je pensais que j'étais bon en calcul mathématique, mais là tu viens de me prouver que j'en ai encore à apprendre... ;)

Je comprends là, cela a du sens!

Il me reste maintenant à l'adapter à ce que je veux vraiment faire dans mon fichier excel. 
J'essaie de trouver une facon simple de trouver les bons chiffres à la bonne place dans la formule. 
Car, si je choisi de faire la somme de 7 cellules, au lieu de 3, et de commencer à la 8e ligne de la feuil1 (source) dans la colonne "i" et de commencer la formule à la 7e ligne de la feuil2 (résultats), ma formule sera donc:

=SOMME(INDIRECT("feuil1!i"&((LIGNE()-5)*7)-6&":i"&((LIGNE()-5)*7)))

De cette façon, ça fonctionne! Par contre, je dois tenir compte que si j'insère ou je supprime une ligne ou/et une colonne, dans une des deux feuilles. Je dois donc recommencer la formule et trouver les bons chiffres.

Une chose certaine, est que le "*7" correspond au nb de ligne inclut dans chaque somme.

Pour ce qui est des autres chiffres, je n'ai pas trouvé de manière précise de fonctionner. Excepté, faire quelques test, jusqu'à trouver les bons chiffres. 

Crois tu qu'il est possible de modifier la formule, de façon à pouvoir insérer ou supprimer des lignes et colonnes dans les feuilles en question, de manière à ce que la formule s'ajuste par elle-même, comme dans la plupart des formules sur excel?

Merci encore! Tes informations me sont grandement utiles.

Po4419

gbinforme · Answer

bonjour,

Tu sais le pire calcul mathématique a toujours été le problème des intervalles (c'est ton souci n'est-ce pas ?) et lorsque tu poses un grillage ce n'est pas simple de savoir combien il te faut de piquets : bon la plupart en achète "assez" et une fois posés, il en reste pour refaire un calcul.

Ici ton but c'est d'avoir une référence de plage "feuil1!A2:A4" par exemple et donc INDIRECT te permets de mettre "feuil1!A" et ":A" qui sont fixes, en texte et il ne te reste donc qu'à trouver la ligne début et la ligne fin.

Tu as vu que la multiplication *3  te permet de définir l'intervalle et donc, il te faut trouver le calcul qui en multipliant la ligne sur laquelle tu veux mettre ta formule (fonction LIGNE), te fournisse le bon chiffre :
- pour obtenir 2 en ligne 2 : ((LIGNE()-1)*3)-1 > 2 -1 >1 multiplié par 3 > 3 il faut enlever 1 pour avoir 2
- pour obtenir 2 en ligne 5 : ((LIGNE()-4)*3)-1 > 5 -4 >1 multiplié par 3 > 3 il faut enlever 1 pour avoir 2

Crois tu qu'il est possible de modifier la formule, de façon à pouvoir insérer ou supprimer des lignes

Déjà, il n'y a pas de croyances en mathématique ou informatique. Si au lieu d'avoir des blocs de 3 lignes tu passes à des blocs de 7 il suffit de modifier le multiplicateur mais il faut aussi que tu insères partout 4 lignes, mais tu ne pourras jamais avoir tantôt 3 lignes et tantôt 7 :  attention à l'insertion de ligne, il y a beaucoup de formules qui ne fonctionnent plus.

po4419 · Answer

Bonjour gbinforme, 

Eh bien, je crois que je commence à maitriser de plus en plus ta formule! Je m'en sert dans plusieurs cas, un peu à tous les jours, dans mon nouveau fichier excel.

Je t'en remercie beaucoup. 

Po4419