Moyenne d'un groupe de cellules variable

Résolu/Fermé
Signaler
-
 Fab -
Bonjour,
je souhaite effectuer différents calculs dans excel soit via un TCD soit directement sur la base de donnée.
Le problème que je rencontre est que la base de donnée est volumineuse, et change chaque année, il me faut donc automatiser les calculs.
la base de donnée est de type:
mesure 1 mesure 2 mesure 3
context 1 element 1 date 1 rep1: donnée 1 donnée 2 donnée 3
context 1 element 1 date 1 rep2: " " "
context 1 element 1 date 2 rep1: " " "
context 1 element 2 date 1 rep1: " " "
ETC...
context 2 element 1 date 1 rep 1: " " "
ETC...
Il me faut donc faire une moyenne pour l'ensemble des données relatives au critère étudié (context, element...), hors via un TCD je n'arrive quà calculer une moyenne de moyenne, ce qui statistiquement parlant ne me convient pas du tout.
De plus le nombre de context ou d'éléments variant eux aussi, cela me pose problème si je souhaite automatiser les calculs .
Le but est donc en fonction d'un menu déroulant ou en tappant l'élément ou le context souhaité obtenir directement le résultat, sans avoir a refaire pour une enième fois le travail en manuel . en gros que excel reconnaisse dans l'ensemble des données celles qui correspondent au critère étudié.
j'espère avoir été clair, car perso je n'y arrive pas, que ce soit par une fct° recherche ou autre (d'autant plus que l'excel en question est un recent, qui est en anglais...)
merci par avance pour ceux qui s'y interesseraient! ^^

10 réponses

Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 588
Bonjour,

L'exposé de tes données n'est pas très facile à comprendre mais je pense qu'en utilisant la fonction SOMMEPROD, tu devrais pouvoir obtenir ta moyenne sur l'élément/contexte choisi.
Si tu pouvais nous mettre un exemple de structure des données, sans éléments personnels, se serait plus facile de t'aider.
Pour communiquer ton classeur tu le télécharges sur https://www.cjoint.com/ (mode d'emploi) et tu mets ici le lien obtenu.
0
http://www.cjoint.com/c/EJoh2S15EQq

voice un exemple,
la fonction sommeprod pourrait etre une solution mais comment la rendre adaptable au changement de la base de donnée?
0
Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 588
Merci pour ton exemple et voici les formules :

https://www.cjoint.com/c/EJoiPQYSRQl

la première ligne donne le résultat sur tes données et la seconde te donne la façon de rendre le résultat indépendant de la taille de ta base avec les formules nommées.
0
Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 588
Merci ne n'avoir même pas regardé le classeur réponse...
0
je viens de voir votre réponse, je ne l'avais pas vu, je vous remercie car elle solutionne le problem énoncé, j'ai essayé d'autres methodes sans succes car il faut bien que je fasse ces calculs.
Cependant elle marche sur l'énoncé donné, mais une fois sur ma base de donnée le résulltat de la moyenne n'est plus le meme, ne sachant pour le moment pas prk. Deplus il y a aussi des cases vides.
Je souhaite au final collecter les données afin d'établir un graphique "boite à moustache" et là avec cette formule je ne vois d'ailleurs pas comment faire..
J'ai essayé de developer une solution à partir d'un TCD mais meme là ca ne passé pas.
je vous remercie tout de meme pour l'aide apportée, je n'avais pas vu votre réponse pensant d'ailleurs que vous n'y avez pas donné suite.
0
Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 588
Bonsoir,

Je t'ai créé un tcd qui fait tes moyennes, maximums et minimums :

http://www.cjoint.com/c/EJrvmCVW8al

Le tcd basé sur une formule nommée, s'agrandit selon ta base.

Pour cela je t'ai rajouté des colonnes avec formules qui formatent tes données "eval".
Pour le graphique "boite à moustache" - que je ne connais pas ! - j'en ai mis un, à toi de choisir le bon ;-)
Avec un tcd, ensuite tu peux choisir ta région, ta présentation, etc
à toi de dire
0
merci mais le problème est tjrs présent car, pour la moyenne par exemple, si je souhaite avoir le résultat concernant l'ensemble des données relevées lors des eval d'un objet, ce dernier est faux car il est fonction d'une seul ligne et non de l'ensemble des lignes dont l'objet est le même.je ne sait pas si je me suis fait comprendre mais en gros, un objet est évalué sur plusieurs jours avec plusieurs eval et ce, sur 2 rep, ce qui inclus plusieurs données sur plusieurs lignes, d'où mon problème....
0
Messages postés
17814
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
15 janvier 2022
4 913
Bonjour,

plusieurs possibilités, mais comme tu sembles connaitre SOMMEPROD, pour la moyenne

=SOMMEPROD((A2:A17=51)*(E2:G17))/SOMMEPROD((A2:A17=51)*(E2:G17<>""))


=SOMMEPROD((A2:A17=51)*(C2:C17="moi")*(E2:G17))/SOMMEPROD((A2:A17=51)*(C2:C17="moi")*(E2:G17<>""))


par contre tu peux mettre tes critères de recherche dans des cellules exemple la zone en J1 et l'identité en JK1 ce qui donnera

=SOMMEPROD((A2:A17=J1)*(E2:G17))/SOMMEPROD((A2:A17=J1)*(E2:G17<>""))
et
=SOMMEPROD((A2:A17=J1)*(C2:C17=K1)*(E2:G17))/SOMMEPROD((A2:A17=J1)*(C2:C17=K1)*(E2:G17<>""))



A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.
0
c'est ce que j'ai fait, j'ai attribué les critères sur des cases.
Parcontre , je souhaite aussi effectuer d'autres stats, quartiles min et max pour des representation graphiques ensuite.
De plus si le volume du tableau change, il me faut aussi changer la formule, et en voulant n'y écrire que les colonnes la formule ne répond plus,
merci de m'aider en tout cas, car ça me dépasse^^
0
Messages postés
17814
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
15 janvier 2022
4 913
Re,

pour la valeur MAX en fonction de la zone

=SOMMEPROD(MAX((A2:A17=J1)*(E2:G17)))

ou MIN

=SOMMEPROD(MIN((A2:A17=J1)*(E2:G17)))

pour un tableau variable je regarde, le tableau est il appelé à avoir plus de lignes ou plus de colonnes, si plus de colonnes quel type des EVAL !

A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.
0
ce serra plus de ligne
0
Messages postés
17814
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
15 janvier 2022
4 913
Re,

alors pour les deux première formules, il suffit de remplacer les références de ta plage exemple pour A2:A17 par indirect("A2:A"&EQUIV(MAX(A:A);A:A))
et idem pour la plage E2:G2 indirect("E2:E"&EQUIV(MAX(A2:A24);A1:A24))

ce qui te donne avec les critères en J1 et K1

=SOMMEPROD((INDIRECT("A2:A"&EQUIV(MAX(A:A);A:A))=J1)*(INDIRECT("E2:G"&EQUIV(MAX(A:A);A:A))))/SOMMEPROD((INDIRECT("A2:A"&EQUIV(MAX(A:A);A:A))=J1)*(INDIRECT("E2:G"&EQUIV(MAX(A:A);A:A))<
>""))

et

=SOMMEPROD((INDIRECT("A2:A"&EQUIV(MAX(A:A);A:A))=J1)*(INDIRECT("C2:C"&EQUIV(MAX(A:A);A:A))=K1)*(INDIRECT("E2:G"&EQUIV(MAX(A:A);A:A))))/SOMMEPROD((INDIRECT("A2:A"&EQUIV(MAX(A:A);A:A))=J1)*(INDIRECT("C2:C"&EQUIV(MAX(A:A);A:A))=K1)*(INDIRECT("E2:G"&EQUIV(MAX(A:A);A:A))<>""))


et pour la valeur MAX

=SOMMEPROD(MAX((INDIRECT("A2:A"&EQUIV(MAX(A:A);A:A))=J1)*(INDIRECT("E2:G"&EQUIV(MAX(A:A);A:A)))))


pour la valeur MIN

=SOMMEPROD(MIN((INDIRECT("A2:A"&EQUIV(MAX(A:A);A:A))=J1)*(INDIRECT("E2:G"&EQUIV(MAX(A:A);A:A)))))


si tu gardes la structure colonne de ton fichier tu peux ajouter autant de lignes souhaitées
0
c'est parfait, je vous remercie vraiment pour votre aide car je plantait dessus depuis un moment , je vais tester tout ca voir ce que ca donne mais ca me semble parfait pour ce que je souhaite faire,
encore merci !
0
Messages postés
17814
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
15 janvier 2022
4 913
Re,

si tes attentes sont satisfaites, confirme le nous que l'on passe le statut de la discussion en résolu
0
code erreur N/A.... lorsque je rentre la formule par rapport à la vrai base de donnée, dans l'exemple fourni je n'avai que 3 colonnes de données, hors j'en ai bien plus(8) ca peut engendrer ce code d'erreur? j'ai changé le nom des formules, (anglais) remi la formule complete par rapport au dimensions de mon tableau et ca ne marche pas.
=SUMPRODUCT((INDIRECT("E2:E"&MATCH(MAX(E:E);E:E))=AA2)*(INDIRECT("G2:o"&MATCH(MAX(E:E);E:E))))/SUMPRODUCT((INDIRECT("A2:A"&MATCH(MAX(E:E);E:E))=AA2)*(INDIRECT("G2:o"&MATCH(MAX(E:E);E:E))<>""))
il y a un probleme de syntaxe?
0
Messages postés
17814
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
15 janvier 2022
4 913
Re,

la première colonne de ton tableau commence en colonne A ou en colonne E

et donne moi l'index de ta dernière colonne

dans ta formule tu as écrit o à la place de 0 (zéro)
0
la premiere en colonne A mais la colonne qui m'intéresse pour le critère est en colonne e
0
deernière colonne en x, et pour le o/zero il ne s'agissait pas d'un zero a la base
0
Messages postés
17814
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
15 janvier 2022
4 913
Re,

poste moi ton fichier réel avec simplement quelques lignes anonymisées que je vois sa structure
0
http://www.cjoint.com/c/EJqoepQoxdq

Après plusieurs autres tentatives tjrs pas de résultat probant, le but étant a partir de ces données d'établir un graph boite a moustache.
Merci pr votre aide en tout cas
0
Messages postés
17814
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
15 janvier 2022
4 913
Re,

Alors toujours pour un nombre de ligne variable, en W1 la région, dans ton exemple bourgogne et en X1 l'objet poink

pour avoir la moyenne en fonction de la région
=SOMMEPROD((INDIRECT("A2:A"&EQUIV(RECHERCHE("zz";A:A);A:A))=W1)*(INDIRECT("F2:K"&EQUIV(RECHERCHE("zz";A:A);A:A))))/SOMMEPROD((INDIRECT("A2:A"&EQUIV(RECHERCHE("zz";A:A);A:A))=W1)*(INDIRECT("F2:K"&EQUIV(RECHERCHE("zz";A:A);A:A))<>""))


pour avoir la moyenne en fonction de la région et de l'objet
=SOMMEPROD((INDIRECT("A2:A"&EQUIV(RECHERCHE("zz";A:A);A:A))=W1)*(INDIRECT("D2:D"&EQUIV(RECHERCHE("zz";A:A);A:A))=X1)*(INDIRECT("F2:K"&EQUIV(RECHERCHE("zz";A:A);A:A))))/SOMMEPROD((INDIRECT("A2:A"&EQUIV(RECHERCHE("zz";A:A);A:A))=W1)*(INDIRECT("D2:D"&EQUIV(RECHERCHE("zz";A:A);A:A))=X1)*(INDIRECT("F2:K"&EQUIV(RECHERCHE("zz";A:A);A:A))<>""))


le MAX pour la région bourgogne
=SOMMEPROD(MAX((INDIRECT("A2:A"&EQUIV(RECHERCHE("zz";A:A);A:A))=W1)*(INDIRECT("F2:K"&EQUIV(RECHERCHE("zz";A:A);A:A)))))


Le MAX en fonction de la région et de l'objet
=SOMMEPROD(MAX((INDIRECT("A2:A"&EQUIV(RECHERCHE("zz";A:A);A:A))=W1)*(INDIRECT("D2:D"&EQUIV(RECHERCHE("zz";A:A);A:A))=X1)*(INDIRECT("F2:K"&EQUIV(RECHERCHE("zz";A:A);A:A)))))

A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.
0
erreur N/A , que veut dire "zz"?
0
c'est bon la solution a été trouvé par gbinforme, surement que la votre marche mais je n'arrive pas à l'appliquer.
Merci pour votre aide à vous deux!
0