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

Signaler
-
Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
-
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!!


5 réponses

Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 305
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.
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 63550 internautes nous ont dit merci ce mois-ci

Bonjour gbinforme,

Merci de votre réponse, j'apprécie, j'ai essayé votre formule, et je n'ai pas réussi à la faire fonctionner comme je le voulais.
J'ai préparé un fichier excel exemple, afin de donner les résultats que j'ai obtenu.

http://2e49u8.cjoint.net/

Est ce que vous pourriez me dire où est mon erreur svp?

Merci!

po4419

PS, désolé, il semble que le fichier ne donne pas accès direct, je ne comprends pas pkoi, mais avec un copier coller, ca fonctionne bien.
Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 305
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 ?
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 63550 internautes nous ont dit merci ce mois-ci

Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 305
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.
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 63550 internautes nous ont dit merci ce mois-ci

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
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
Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 305
Bonjour po4419,

Merci de ton message et surtout de la manière avec laquelle tu as intégré le fonctionnement : l'on ne peux utiliser la puissance d'excel que si l'on a compris et intégré la combinaison possible des différentes fonctions comme ici avec INDIRECT et LIGNE.

Je m'en sert dans plusieurs cas, un peu à tous les jours, : c'est la méthode parfaite pour progresser dans la maitrise du logiciel en l'utilisant sans relâche.

Bonne continuation à toi.