Pondérer moyennes sur excel
Résolu/Fermé
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
-
Modifié par TOT127 le 3/07/2016 à 00:40
TOT127 Messages postés 898 Date d'inscription mercredi 8 avril 2009 Statut Membre Dernière intervention 7 août 2018 - 7 août 2016 à 21:10
TOT127 Messages postés 898 Date d'inscription mercredi 8 avril 2009 Statut Membre Dernière intervention 7 août 2018 - 7 août 2016 à 21:10
A voir également:
- Pondérer moyennes sur excel
- Comment calculer la moyenne sur excel - Guide
- Liste déroulante excel - Guide
- Si et excel - Guide
- Comment aller à la ligne sur excel - Guide
- Trier sur excel - Guide
6 réponses
michel_m
Messages postés
16603
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
16 décembre 2023
3 311
6 juil. 2016 à 09:07
6 juil. 2016 à 09:07
bonjour
essaie en n'utilisant pas des colonnes entières
essaie en n'utilisant pas des colonnes entières
Raymond PENTIER
Messages postés
58849
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
5 février 2025
17 278
3 juil. 2016 à 01:30
3 juil. 2016 à 01:30
Bonjour.
J'ai examiné ta question ; désolé, je n'ai aucune solution !
(surtout parce que je ne crois pas avoir compris ce que tu veux obtenir.
Si tu avais fait un calcul manuel sur un exemple, ça m'aurait aidé ...)
J'ai examiné ta question ; désolé, je n'ai aucune solution !
(surtout parce que je ne crois pas avoir compris ce que tu veux obtenir.
Si tu avais fait un calcul manuel sur un exemple, ça m'aurait aidé ...)
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
3 juil. 2016 à 01:59
3 juil. 2016 à 01:59
je vais travailler à un exemple et je reviens dans le forum rapidement
merci!
merci!
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
5 juil. 2016 à 11:23
5 juil. 2016 à 11:23
bonjour ! j'ai fait un exemple ici, j'espère que c'est un peu plus clairhttp://www.cjoint.com/c/FGfjwEwMMRX
Raymond PENTIER
Messages postés
58849
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
5 février 2025
17 278
5 juil. 2016 à 16:23
5 juil. 2016 à 16:23
Salut, TOT127.
Nous n'avons apparemment pas le même type de logique, car je ne parviens pas à adhérer à ton raisonnement ...
Le principe d'une moyenne, c'est que le total des moyennes donne 1 (soit 100%) ; or 0,87+0,95+0,42= 2,24 !
Ce que tu as calculé en H:H, c'est, pour chaque nom en A:A, le pourcentage des dons pointés "big" en C:C par rapport au total par personne en F:F.
Ainsi, PIERRE a perçu 11000 € pointés taille 1 sur les 11600 € collectés,
ce qui représente bien 95%.
Est-ce bien ce que tu demandes, depuis le début ?
Nous n'avons apparemment pas le même type de logique, car je ne parviens pas à adhérer à ton raisonnement ...
Le principe d'une moyenne, c'est que le total des moyennes donne 1 (soit 100%) ; or 0,87+0,95+0,42= 2,24 !
Ce que tu as calculé en H:H, c'est, pour chaque nom en A:A, le pourcentage des dons pointés "big" en C:C par rapport au total par personne en F:F.
Ainsi, PIERRE a perçu 11000 € pointés taille 1 sur les 11600 € collectés,
ce qui représente bien 95%.
Est-ce bien ce que tu demandes, depuis le début ?
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
Modifié par TOT127 le 5/07/2016 à 17:48
Modifié par TOT127 le 5/07/2016 à 17:48
(je lis ta réponse, j'y travaille et je reviens, merci bcp !).
edit:
A priori, sauf erreur de ma part, le colonne H, moyenne pondérée big n'a pas vocation à donner 1 si on somme mais à informer sur le niveau de dépendance de chaque mec vis à vis d'un type de don (les big) avec la pondération. mes poids, eux, somment à 1= 0.87+0.02.0.01
En fait, le résultat (moyenne pondérée,) pour moi, c'est un peu: de chacun des euros reçu par une personne y, combien proviennent de boites de la catégorie 1.
edit:
A priori, sauf erreur de ma part, le colonne H, moyenne pondérée big n'a pas vocation à donner 1 si on somme mais à informer sur le niveau de dépendance de chaque mec vis à vis d'un type de don (les big) avec la pondération. mes poids, eux, somment à 1= 0.87+0.02.0.01
En fait, le résultat (moyenne pondérée,) pour moi, c'est un peu: de chacun des euros reçu par une personne y, combien proviennent de boites de la catégorie 1.
Raymond PENTIER
Messages postés
58849
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
5 février 2025
17 278
>
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
Modifié par Raymond PENTIER le 5/07/2016 à 19:18
Modifié par Raymond PENTIER le 5/07/2016 à 19:18
Nous sommes donc tout-à-fait d'accord sur la signification de ton calcul ; c'est l'expression "moyenne pondérée" qui n'est pas bien choisie.
Ta formule sera donc
=SOMME.SI.ENS($B$2:$B$14;$A$2:$A$14;A:A;$C$2:$C$14;1) /
SOMME.SI($A$2:$A$14;A:A;$B$2:$B$14)
et si tu as pris la précaution de définir le nom "Qui" pour la plage B2:B14, le nom "Val" pour la plage B2:B14 et le nom "Taye" pour C2:C14, la formule se réduit à
=SOMME.SI.ENS(Val;Qui;A:A;Taye;1) / SOMME.SI(Qui;A:A;Val)
Très cordialement.
Ta formule sera donc
=SOMME.SI.ENS($B$2:$B$14;$A$2:$A$14;A:A;$C$2:$C$14;1) /
SOMME.SI($A$2:$A$14;A:A;$B$2:$B$14)
et si tu as pris la précaution de définir le nom "Qui" pour la plage B2:B14, le nom "Val" pour la plage B2:B14 et le nom "Taye" pour C2:C14, la formule se réduit à
=SOMME.SI.ENS(Val;Qui;A:A;Taye;1) / SOMME.SI(Qui;A:A;Val)
Très cordialement.
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
Modifié par TOT127 le 5/07/2016 à 23:59
Modifié par TOT127 le 5/07/2016 à 23:59
bonjour, en effet, cela fonctionne ! merci infiniment !
avec cette formule, pas besoin de faire une colonne "totaldep" et "% que chaque don représente dans le total"
j'espère juste qu'elle n'est pas trop lourde et me permettra d'ouvrir et de continuer à travailler sur mon fichier... J'ai l'impression que excel passe son temps à calculer. Quels membres de la formule puis je remplacer par "total de chaque dep" (colonne en valeur) ?
car là excel n'enregistre meme pas tellement c'est lourd je crois.. :p (j'ai quand même 35 colonnes, 55000 lignes)
avec cette formule, pas besoin de faire une colonne "totaldep" et "% que chaque don représente dans le total"
j'espère juste qu'elle n'est pas trop lourde et me permettra d'ouvrir et de continuer à travailler sur mon fichier... J'ai l'impression que excel passe son temps à calculer. Quels membres de la formule puis je remplacer par "total de chaque dep" (colonne en valeur) ?
car là excel n'enregistre meme pas tellement c'est lourd je crois.. :p (j'ai quand même 35 colonnes, 55000 lignes)
Raymond PENTIER
Messages postés
58849
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
5 février 2025
17 278
>
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
6 juil. 2016 à 03:29
6 juil. 2016 à 03:29
Ce n'est pas Excel qui rame ; c'est l'ordinateur !
Est-il assez puissant ?
La mémoire vive est-elle suffisante ?
Est-ce que tu n'as pas plusieurs logiciels (et même plusieurs fichiers) ouverts en même temps ?
As-tu fait le ménage dans tes cookies et tes messages temporaires ?
Est-il assez puissant ?
La mémoire vive est-elle suffisante ?
Est-ce que tu n'as pas plusieurs logiciels (et même plusieurs fichiers) ouverts en même temps ?
As-tu fait le ménage dans tes cookies et tes messages temporaires ?
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
6 juil. 2016 à 10:55
6 juil. 2016 à 10:55
j'utilise tantôt un mac, 2,5 GHz Intel Core i5 16 Go de RAM, tantôt un PC avec des caractéristiques équivalentes, et les deux disques durs ont environ 2 mois (un informaticien les a remplacés, ils sont rapides etc). Je n'ai pas fait le ménage (sur mac, je ne sais pas faire je vais m'informer)
eriiic
Messages postés
24603
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
15 décembre 2024
7 251
5 août 2016 à 10:11
5 août 2016 à 10:11
Bonjour,
un essai qui sera plus léger en calculs pour excel.
Le tableau doit être trié par nom.
Par contre à refaire sur un classeur neuf. Le tien semble un peu vérolé, à l'enregistrement il demande en permanence à le recalculer alors qu'il l'est...
https://www.cjoint.com/c/FHfijfXFOUC
eric
un essai qui sera plus léger en calculs pour excel.
Le tableau doit être trié par nom.
Par contre à refaire sur un classeur neuf. Le tien semble un peu vérolé, à l'enregistrement il demande en permanence à le recalculer alors qu'il l'est...
https://www.cjoint.com/c/FHfijfXFOUC
eric
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
5 août 2016 à 10:42
5 août 2016 à 10:42
le mien est vérolé, surtout que je t'en ai envoyé un petit, mais le mien en vrai fait 55000 lignes et 40 colonnes lol. Je crois que le problème est dans le mode de calcul: excel ne veut pas calculer la formule (lourde) dans ce classeur
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
5 août 2016 à 10:49
5 août 2016 à 10:49
classeur neuf: tu me conseilles de copier tout mon classeur en valeur dans un autre neuf, peut etre ?
eriiic
Messages postés
24603
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
15 décembre 2024
7 251
Modifié par eriiic le 5/08/2016 à 12:21
Modifié par eriiic le 5/08/2016 à 12:21
oui, et tester ma proposition avec 2 colonnes intermédiaires.
Le calcul devrait être plus accessible à excel et surtout beaucoup plus rapide si tu as beaucoup de noms.
Le calcul devrait être plus accessible à excel et surtout beaucoup plus rapide si tu as beaucoup de noms.
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
5 août 2016 à 13:28
5 août 2016 à 13:28
je vais tenter, merci:)
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
5 août 2016 à 17:33
5 août 2016 à 17:33
salut ! j'ai fait ta méthode, effectivement c'est beaucoup plus léger pour excel qui y arrive très bien.
Néanmoins, je ne comprends pas une chose: les résultats par ta méthode diffèrent des résultats de la formule somme prod, faite avec sommeprod: http://www.cjoint.com/c/FHfpFNHagrj
Je veux simplement avoir une mesure de "à quel point un mec dépend des financements des grandes entreprises, codées 1, 2, 3" (3: les 100 plus grandes, 2, 100-500, 3, les 500-1500 plus grandes).
comment comprendre ça ?
Néanmoins, je ne comprends pas une chose: les résultats par ta méthode diffèrent des résultats de la formule somme prod, faite avec sommeprod: http://www.cjoint.com/c/FHfpFNHagrj
Je veux simplement avoir une mesure de "à quel point un mec dépend des financements des grandes entreprises, codées 1, 2, 3" (3: les 100 plus grandes, 2, 100-500, 3, les 500-1500 plus grandes).
comment comprendre ça ?
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
JvDo
Messages postés
1978
Date d'inscription
mercredi 27 juillet 2005
Statut
Membre
Dernière intervention
28 septembre 2020
858
6 août 2016 à 17:15
6 août 2016 à 17:15
Bonjour à tous,
Je suis parti du fichier de eriiic et j'ai essayé différentes formules sur 100 000 lignes au départ. Recalcul en 8mn....
J'ai réduit à 55 000 lignes et sensiblement 500 noms, j'ai modifié mes DECALER() et le recalcul se fait en 1s.
C'est mieux....

J'ai effacé les colonnes AI:AK pour éviter des recalculs inutiles (enfin, redondants).
La formule en AR2 à recopier sur AR2:AU55000:
Une précision : AR1:AU1 ne contiennent que les chiffres 0 à 3. L'apparence alphabétique n'est que de la mise en forme.
J'ai limité la zone DECALER() à 201 cellules. S'il y a des noms qui apparaissent plus de 201 fois, il faut modifier cette valeur dans les formules.
J'ai supposé que les noms étaient dans l'ordre alpha.
Cordialement
PS : pourquoi n'as-tu pas testé les TCD?
Je suis parti du fichier de eriiic et j'ai essayé différentes formules sur 100 000 lignes au départ. Recalcul en 8mn....
J'ai réduit à 55 000 lignes et sensiblement 500 noms, j'ai modifié mes DECALER() et le recalcul se fait en 1s.
C'est mieux....

J'ai effacé les colonnes AI:AK pour éviter des recalculs inutiles (enfin, redondants).
La formule en AR2 à recopier sur AR2:AU55000:
=SI($A2=$A1;"";SOMMEPROD((DECALER($A2;;;201;1)=$A2)*DECALER($AF2;;;201;1)*(DECALER($AQ2;;;201;1)=AR$1))/SOMMEPROD((DECALER($A2;;;201;1)=$A2)*DECALER($AF2;;;201;1)))
Une précision : AR1:AU1 ne contiennent que les chiffres 0 à 3. L'apparence alphabétique n'est que de la mise en forme.
J'ai limité la zone DECALER() à 201 cellules. S'il y a des noms qui apparaissent plus de 201 fois, il faut modifier cette valeur dans les formules.
J'ai supposé que les noms étaient dans l'ordre alpha.
Cordialement
PS : pourquoi n'as-tu pas testé les TCD?
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
Modifié par TOT127 le 6/08/2016 à 18:19
Modifié par TOT127 le 6/08/2016 à 18:19
Salut ! merci de ta réponse!
je suis entrain de voir ça. Pourrais tu éventuellement mettre le fichier sur cijoint ?
Pour les TCD, j'y ai pensé. Ca serait plus facile car je maitrise plutôt bien, mais je me demande comment le faire là. Je crois qu'il faudrait faire bcp de retraitements pour passer par là.
je suis entrain de voir ça. Pourrais tu éventuellement mettre le fichier sur cijoint ?
Pour les TCD, j'y ai pensé. Ca serait plus facile car je maitrise plutôt bien, mais je me demande comment le faire là. Je crois qu'il faudrait faire bcp de retraitements pour passer par là.
JvDo
Messages postés
1978
Date d'inscription
mercredi 27 juillet 2005
Statut
Membre
Dernière intervention
28 septembre 2020
858
6 août 2016 à 20:02
6 août 2016 à 20:02
Bonsoir à tous,
Sauf si je n'ai rien compris à ta demande, je ne vois pas de retraitement à faire pour le TCD.
Voilà le fichier https://www.cjoint.com/c/FHgsahc4bql avec les 2 approches : formules et TCD.
Naturellement, j'ai fait maigrir le fichier pour l'envoyer.
Cordialement
Sauf si je n'ai rien compris à ta demande, je ne vois pas de retraitement à faire pour le TCD.
Voilà le fichier https://www.cjoint.com/c/FHgsahc4bql avec les 2 approches : formules et TCD.
Naturellement, j'ai fait maigrir le fichier pour l'envoyer.
Cordialement
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
7 août 2016 à 10:44
7 août 2016 à 10:44
bonjour, je regarde dès que j'ai l'ordi. merci!
TOT127
Messages postés
898
Date d'inscription
mercredi 8 avril 2009
Statut
Membre
Dernière intervention
7 août 2018
96
7 août 2016 à 21:10
7 août 2016 à 21:10
bonjour !
Bon, ça a fonctionné, et j'ai "découvert" aussi que l'un des problèmes des autres formules est qu'excel refuse de les calculer et met des 0.
Cette formule par exemple:
=SOMME.SI.ENS(Val;Qui;A:A;Taye;1) / SOMME.SI(Qui;A:A;Val)
Elle marche pas dans mon grand fichier, et ne donne que des 0.
J'ai fait un fichier neuf avec 10 lignes, appliqué la formule sur les 10: ça marche. J'ai rajouté par copié collé mes 50.000 lignes en dessous: je descends la formule: ça a marché.
Étonnant non ?
Le mode de calcul est en automatique. Je l'ai mis en manuel pour faire ce copié collé etc, et remis en automatique.
Là je regarde un peu sur internet pour comprendre un peu.
ça t'inspire quoi ça ?
merci et bonne soirée !
Bon, ça a fonctionné, et j'ai "découvert" aussi que l'un des problèmes des autres formules est qu'excel refuse de les calculer et met des 0.
Cette formule par exemple:
=SOMME.SI.ENS(Val;Qui;A:A;Taye;1) / SOMME.SI(Qui;A:A;Val)
Elle marche pas dans mon grand fichier, et ne donne que des 0.
J'ai fait un fichier neuf avec 10 lignes, appliqué la formule sur les 10: ça marche. J'ai rajouté par copié collé mes 50.000 lignes en dessous: je descends la formule: ça a marché.
Étonnant non ?
Le mode de calcul est en automatique. Je l'ai mis en manuel pour faire ce copié collé etc, et remis en automatique.
Là je regarde un peu sur internet pour comprendre un peu.
ça t'inspire quoi ça ?
merci et bonne soirée !
6 juil. 2016 à 10:55
Modifié par michel_m le 6/07/2016 à 11:24
A2:A14
mais sur 55000 lignes, c'est normal que ca rame!
en effet "somme si ens" parcourt les 55000 lignes autant de fois (3 conditions=55000*55000*55000) qu'il y a de conditions et les vérifies;
d'ailleurs, il aurait judicieux de la préciser dans ta premièredemande: cela aurait éviter à Raymond de passer du temps pour rien!
sur un tel nombre de lignes, il faut passer par VBA avec des variables-tableaux
comme tu as un MAc, je crois, je ne peux pas t'aider
Modifié par TOT127 le 6/07/2016 à 12:35
Je travaille aussi sur PC en fait, avec ce même fichier (inutile par exemple d'espérer faire certaines manoeuvres avec mon mac, je les fais sur PC), donc peut-être que je peux faire la manoeuvre VBA sur PC ?
Au pire, je fais la formule, et je la met en valeur, pour garder que le résultat. Le problème, c'est pas de faire et descendre la formule. ca, ça marche. Mais si je change la source des 1 et 0 (en changeant un nom d'entreprise), ça se répercute sur une colonne qui est prise en compte dans la formule...et là, c'est mort
5 août 2016 à 07:00
Alors là, je ne comprends plus rien ! Peux-tu donner un exemple chiffré précis ?
Parce que les formules, par définition, sont faites pour calculer des résultats avec des données qui sont variables ...