|
|
Auteur
|
Message
|
1
|
|
|
|
Bonjour,
J'aimerai savoir s'il est possible de créer une fonction avec excel qui me permettrai de faire ceci :
J'aimerai avoir sur une feuille 1 dans la colonne B, la moyenne de la colonne B de la feuille 2 qui correspond à la même date et que cela se fasse automatiquement. Je pense qu'il faut programmer en VSB mais comme je n'y connais rien je vous demander conseil. J'ai décrit ci-dessous un petit exemple que que voudrais faire.
Feuille 1 :
..........A..........B........
1..01/01/2008...2
2..02/01/2008...5
Feuille 2 :
..........A..........B........
1..01/01/2008...1...
2..01/01/2008...2...
3..01/01/2008...3...
4..02/01/2008...4...
5..02/01/2008...5...
6..02/01/2008...6...
En case B1 de la feuille 1 je devrai trouver la moyenne des cases (B1,B2,B3) de la feuille 2 (ce qui correspond à la date 01/01/2008) : B1(Feuil1) = 2.
En case B2 de la feuille 1 je devrai trouver la moyenne des cases (B4,B5,B6) de la feuille 2 (ce qui correspond à la date 02/01/2008) : B2(Feuil1) = 5.
Peut-on rendre ce calcul automatique car j'ai des données qui remplit un tableau de plus 5000 lignes et je n'ai pas envie de le faire ligne par ligne ?
Merci d'avance pour vos réponses.
-->Message édité par mongrapin le 03/05/2008 09:47:50<--
-------
Olive
|
|
|
|
|
Salut mongrapin et le forum
Je pense qu'il faut programmer en VSB
Je suppose que tu parles de VBA (Visual Basic for Application, les macros quoi).
Non, on peut le faire par formule, si j'ai bien compris le problème. Ce que j'ai compris :
feuil2 :
Colonne A : les dates
Colonne B : les nombres
Feuil1 :
Colonne A : les dates de références
Colonne B : la moyenne des nombres en B de Feuil2 dont la colonne A de feuil2 = colonne A de Feuil1
si c'est bien ça, le calcul à faire pour la cellule B1 de Feuil1 est :
(la somme de toutes les valeurs de la colonne B de Feuil2 dont la colonne A = A1 de Feuil1) divisée par (le nombre de ligne de la Feuil2 dont la colonne A = A1 de Feuil1)
si la colonne B peut être vide alors que la colonne A affiche une date dans la feuil2, il faudra tester que la cellule B ne soit pas vide, mais ça me semble illogique, donc je ne traite pas, sauf information contraire)
donc en B1 de Feuil1, on aura la formule :
=SOMMEPROD((Feuil2!$A$1:$A$65535=Feuil1!A1)*Feuil2!$B$1:$B$65535)/SOMMEPROD((Feuil2!$A$1:$A$65535=Feuil1!A1)*1)
expliaction de la formule
SOMMEPROD((Feuil2!$A$1:$A$65535=Feuil1!A1)*Feuil2!$B$1:$B$65535)
Pour simplifier la lecture, je vais remplacer le nom des feuille Feuil1, Feuil2, par F1 et F2 donc on a
SOMMEPROD((F2!$A$1:$A$65535=F1!A1)*F2!$B$1:$B$65535)
Sommeprod fait la somme de la multiplication d'une ou plusieurs plages, ce qui revient à écrire en le développant :
((F2!$A$1=F1!A1)*F2!$B$1)+((F2!$A$2=F1!A1)*F2!$B$2)+((F2!$A$3=F1!A1)*F2!$B$3)+...+((F2!$A$65535=F1!A1)*F2!$B$65535)
- pourquoi on s'arrête à 65535 : si tu mets la dernière ligne (65536), Excel converti la plage en colonne et la formule renvoie une erreur (#nombre).
((F2!$A$1=F1!A1)*F2!$B$1)
(F2!$A$1=F1!A1) ; s'il y a égalité, Excel renvoie un 1, s'il n'y a pas l'égalité Excel renvoie 0. Plus exactement il renvoie les informations VRAI ou FAUX, mais comme c'est multiplié, il considére que ce n'est pas l'information logique qui doit être renvoyée, mais l'information nombre.
on a donc
1×F2!$B$1 en cas dégalité des 2 dates soit F2!$B$1
0×F2!$B$1 si les dates sont différentes, soit 0
et comme l'opération se répète pour toutes les lignes, avec une addition des résultats, on a la somme de toutes les cellules B dont les dates corresponddent à celle en référence en A de Feuil1
Dans la seconde partie de la formule, on multiplie par 1, ce qui donne le nombre de fois ou la date en A est égale à celle de A de Feuil1
Comme il faut pouvoir recopier facilement B1, sur B2, B3, etc et avoir la bonne moyenne, le tableau de Feuil2 correspondant aux plages à traiter doit rester le même (A1:B65535) on utilise la référence Absolue ($)
la cellule A de Feuil1 doit évoluer en fonction de la ligne où se touve la cellule B correspondante, on utilise donc la référence relative pour la ligne. pour éviter de se mélanger, on utilise l'adresse relative pour la colonne aussi (pas de $ devant la colonne ou la ligne)
A+
|
|
|
|
|
Bonjour Gorfael,
Un grand merci pour tes explications, c'est tout à fait ce que je voulais faire et ça marche super bien.
Encore merci pour ces informations.
A+
-------
Olive
|
|
|
1
|
|
|
|
