Fusion RECHERCHEV + SOMMEPROD
r1gazgaz
Messages postés
11
Statut
Membre
-
r1gazgaz Messages postés 11 Statut Membre -
r1gazgaz Messages postés 11 Statut Membre -
Bonjour,
Je viens vers vous afin d'obtenir un peu d'aide de la part d'experts qui parcourent ces lieux.
D'habitude je fais des macros, mais là je n'arrive pas à coder ce que je désire, du coup je me tourne vers les formules excel. En parcourant un peu internet j'ai trouvé une partie des réponses seulement.
Voici ma demande.
J'ai un tableau de données, dans la 1ère colonne ce sont des dates, dans les 7 autres ce sont des données sous forme de chiffres. Je désire que pour une date que je décide et entre dans une case x (en l'occurrence R1 ici), ça me calcule la moyenne des 10 plus grandes valeurs sur les 30 dernières de la colonne 7 (donc la colonne n°8 en comptabilisant celle des dates).
Exemple : si je rentre la date 30/10/15, ça doit me calculer la moyenne des 10 plus grandes valeurs sur les 30 dernières, donc du 30/09/15 au 29/10/15. Si je rentre le 10/10/15 cela doit prendre en compte la période du 10/09/15 au 10/10/15 etc...
Sur le net j'ai trouvé 2 formules mais que je n'arrive pas à combiner :
1) recherche de la date désirée dans la 1ère colonne du tableau de données : =RECHERCHEV(R1;GV805:HC828;1) avec GV805:HC828 étant la plage comprenant les 30 jours précédents
2) calcul de la moyenne des 10 plus grandes valeurs sur les 30 dernières pour une plage de données définie : =SOMMEPROD(GRANDE.VALEUR(GV805:HC828;LIGNE(1:10)))/10
Comment faire pour combiner les deux et obtenir ce que je désire ?
Merci d'avance pour votre aide
R1gazgaz
--
Je viens vers vous afin d'obtenir un peu d'aide de la part d'experts qui parcourent ces lieux.
D'habitude je fais des macros, mais là je n'arrive pas à coder ce que je désire, du coup je me tourne vers les formules excel. En parcourant un peu internet j'ai trouvé une partie des réponses seulement.
Voici ma demande.
J'ai un tableau de données, dans la 1ère colonne ce sont des dates, dans les 7 autres ce sont des données sous forme de chiffres. Je désire que pour une date que je décide et entre dans une case x (en l'occurrence R1 ici), ça me calcule la moyenne des 10 plus grandes valeurs sur les 30 dernières de la colonne 7 (donc la colonne n°8 en comptabilisant celle des dates).
Exemple : si je rentre la date 30/10/15, ça doit me calculer la moyenne des 10 plus grandes valeurs sur les 30 dernières, donc du 30/09/15 au 29/10/15. Si je rentre le 10/10/15 cela doit prendre en compte la période du 10/09/15 au 10/10/15 etc...
Sur le net j'ai trouvé 2 formules mais que je n'arrive pas à combiner :
1) recherche de la date désirée dans la 1ère colonne du tableau de données : =RECHERCHEV(R1;GV805:HC828;1) avec GV805:HC828 étant la plage comprenant les 30 jours précédents
2) calcul de la moyenne des 10 plus grandes valeurs sur les 30 dernières pour une plage de données définie : =SOMMEPROD(GRANDE.VALEUR(GV805:HC828;LIGNE(1:10)))/10
Comment faire pour combiner les deux et obtenir ce que je désire ?
Merci d'avance pour votre aide
R1gazgaz
--
1 réponse
Bonjour
Pour ma part je procéderais plutôt avec un DECALER à partir de la date choisie, la ligne de la cellule de la valeur correspondante étant trouvée avec un INDEX EQUIV et sa référence recomposée avec INDIRECT
Exemple avec retour des 4 plus grandes valeurs sur les 10 dernières dates :
http://www.cjoint.com/c/EKclimJjicX
Cdlmnt
Via
Pour ma part je procéderais plutôt avec un DECALER à partir de la date choisie, la ligne de la cellule de la valeur correspondante étant trouvée avec un INDEX EQUIV et sa référence recomposée avec INDIRECT
Exemple avec retour des 4 plus grandes valeurs sur les 10 dernières dates :
http://www.cjoint.com/c/EKclimJjicX
Cdlmnt
Via
Cela me marque dans l'évaluation de l'erreur de la formule : la prochaine évaluation entrainera une erreur GRANDE.VALEUR(DECALER($HB$6;;;-10)1)
J'ai pourtant simplement remplacé les colonne par les miennes :
R1 étant la date de recherche
HB la colonne de recherche des données
GV la colonne de dates
et 1 la plus grande valeur (j'avais l'intention de mettre 10 fois cette formule dans la case de calcul excel en changeant à chaque fois ce chiffre 1 par 2 puis 3 jusqu'à 10 pour les 10 dernières plus grandes valeurs, puis diviser le résultat par 10 pour avoir ma moyenne)
=GRANDE.VALEUR(DECALER(INDIRECT("HB"&1+INDEX(HB$564:HB$1048576;EQUIV($R$1;GV$564:GV$1048576;0)));;;-10);1)
Lorsque je remplace vos chiffres par les miens dans votre fichier excel, cela entraine un bug.
Exemple :
à partir du 05/10/15 jusqu'au 31/10/15 j'ai les chiffres suivants
6
6
7
8
7
5
6
6
4
7
5
8
8
7
6
8
8
7
4
6
6
7
11
6
5
9
5
E
Effectivement, et je ne sais pas pourquoi ! Ca a l'air d'être lié à DECALER
En se passant de la fonction DECALER, ça a l'air de fonctionner, remplaces dans mon exemple la formule en L3 par
=GRANDE.VALEUR(INDIRECT("H"&EQUIV($K$1;G:G;0)&":H"&-9+EQUIV($K$1;G:G;0));K3)
Cdlmnt
Via
Je change juste le -9 par -30 pour que cela fasse une recherche sur les 30 dernières et non pas les 10 dernières valeurs, et c'est bon.
On a donc la formule suivante :
=GRANDE.VALEUR(INDIRECT("H"&EQUIV($K$1;G:G;0)&":H"&-30+EQUIV($K$1;G:G;0));K3)
Merci pour votre aide !
Cordialement
R1gazgaz
Cdlmnt
Via