Pondérer moyennes sur excel

Résolu
TOT127 -  
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,

6 réponses

Résumé de la discussion

La discussion porte sur la nécessité de calculer une moyenne pondérée des contributions issues de catégories d’entreprises (1, 2, 3) lorsque l’on passe d’un modèle 1 ligne par virement à 1 ligne par personne. Des contributeurs avertissent que les moyennes non pondérées peuvent devenir trompeuses lorsque des grandes sources dominent les montants, et préconisent d’intégrer une pondération reflétant l’importance réelle de chaque source. Plusieurs approches Excel sont discutées, notamment le tableau croisé dynamique et SOMMEPROD avec DECALER, afin d’obtenir une moyenne par personne tout en conservant les pondérations, et d’étudier la dépendance vis‑à‑vis des financements importants.

Bobot (l’IA à votre service)
  1. michel_m Contributeur
    bonjour

    essaie en n'utilisant pas des colonnes entières
    3
    1. TOT127 Auteur
      c'est à dire ?
      0
    2. michel_m Contributeur
      @TOT127par 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
    3. TOT127 Auteur
      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
    4. Raymond PENTIER
      @TOT127"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
  2. Raymond PENTIER
    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
    1. TOT127 Auteur
      je vais travailler à un exemple et je reviens dans le forum rapidement
      merci!
      0
    2. TOT127 Auteur
      bonjour ! j'ai fait un exemple ici, j'espère que c'est un peu plus clairhttp://www.cjoint.com/c/FGfjwEwMMRX
      0
  3. Raymond PENTIER
    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
    1. TOT127 Auteur
      (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
    2. Raymond PENTIER
      @TOT127Nous 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
    3. TOT127 Auteur
      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
    4. Raymond PENTIER
      @TOT127Ce 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
    5. TOT127 Auteur
      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
  4. eriiic Contributeur
    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
    1. TOT127 Auteur
      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
    2. TOT127 Auteur
      classeur neuf: tu me conseilles de copier tout mon classeur en valeur dans un autre neuf, peut etre ?
      0
    3. eriiic Contributeur
      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
    4. TOT127 Auteur
      je vais tenter, merci:)
      0
    5. TOT127 Auteur
      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
  6. JvDo
    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
    1. TOT127 Auteur
      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
  7. JvDo
    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
    1. TOT127 Auteur
      bonjour, je regarde dès que j'ai l'ordi. merci!
      0
    2. TOT127 Auteur
      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