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
Bonjour,

J'ai du passer d'une base où 1 ligne = 1 virement de 1 personne à une base où 1 ligne = 1 personne.
chaque virement était caractérisé par des variables catégorielles, par exemple, sur son origine (1, 2, 3=petite moyenne grande entreprise)

J'ai fait un tableau croisé dynamique pour récupérer ces infos, mais je me rends compte que des moyennes non pondérées ont en fait assez peu de sens:

Jean 1
nom - taille boite - valeur
jean - 1 - 10$
jean - 1 - 10$
jean - 3 - 3 $millions (grosse entreprise, bcp de sous)

= moyenne basse, mais imaginez que la grosse entreprise ait filé 100 fois plus que les 3 autres réunies --> non sens d'avoir une moyenne basse, le mec dépend totalement de la grosse boite. Il faut pondérer.

Je suis donc à la recherche d'une formule qui me permette de faire la moyenne pondérée de ces variables catégorielles, par personne. Je vais ensuite rajotuer tout ça dans ma base de données.

Je vous donne un exemple ici, ça sera plus simple:

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


en vous remerciant,



A voir également:

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
bonjour

essaie en n'utilisant pas des colonnes entières
3
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
c'est à dire ?
0
michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 311 > TOT127 Messages postés 898 Date d'inscription mercredi 8 avril 2009 Statut Membre Dernière intervention 7 août 2018
Modifié par michel_m le 6/07/2016 à 11:24
par exemple au lieu de A:A
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
0
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/07/2016 à 12:35
hum, je comprends. disons, par 1000 lignes ou 2000 peut-être.

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
0
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
5 août 2016 à 07:00
"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"
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 ...
0
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
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é ...)

2
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
je vais travailler à un exemple et je reviens dans le forum rapidement
merci!
0
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
bonjour ! j'ai fait un exemple ici, j'espère que c'est un peu plus clairhttp://www.cjoint.com/c/FGfjwEwMMRX
0
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
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 ?
2
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
(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.
0
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
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.
0
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
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)
0
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
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 ?
0
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
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)
0
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
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
1
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
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
0
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
classeur neuf: tu me conseilles de copier tout mon classeur en valeur dans un autre neuf, peut etre ?
0
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
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.
0
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
je vais tenter, merci:)
0
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
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 ?
0

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
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:
=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?
1
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
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à.
0
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
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
0
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
bonjour, je regarde dès que j'ai l'ordi. merci!
0
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
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 !
0