{"id":8408,"date":"2019-10-31T15:38:53","date_gmt":"2019-10-31T14:38:53","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=8408"},"modified":"2019-10-31T22:31:47","modified_gmt":"2019-10-31T21:31:47","slug":"calculer-une-moyenne-ponderee-exemple-applique-au-cump","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=8408","title":{"rendered":"Calculer une moyenne pond\u00e9r\u00e9e (exemple appliqu\u00e9 au CUMP)"},"content":{"rendered":"<p style=\"text-align: justify;\">Le calcul de moyennes pond\u00e9r\u00e9es est d&#8217;usage courant \u00e0 l&#8217;image de la moyenne de notes \u00e0 un examen (du DEC par exemple) ou du co\u00fbt unitaire moyen pond\u00e9r\u00e9 (CUMP) de r\u00e9f\u00e9rences en stock.<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8410\" rel=\"attachment wp-att-8410\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8410\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Calcul-CUMP.png\" alt=\"Calcul CUMP\" width=\"301\" height=\"198\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Calcul-CUMP.png 301w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Calcul-CUMP-300x197.png 300w\" sizes=\"auto, (max-width: 301px) 100vw, 301px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Le calcul de la moyenne arithm\u00e9tique (obtenue par la formule : totalisation \/ d\u00e9nombrement) est assez simple \u00e0 obtenir. Excel propose une fonction attitr\u00e9e : <em>MOYENNE(plage de cellules)<\/em>. Dans notre exemple : 5 + 3 + 2 = 10 \u20ac \/ 3, soit une moyenne de 3,33 \u20ac<\/p>\n<p style=\"text-align: justify;\">La moyenne pond\u00e9r\u00e9e se calcule en additionnant la multiplication op\u00e9r\u00e9e entre les valeurs (ici la quantit\u00e9) et leur coefficient de pond\u00e9ration (ici le prix unitaire), cette totalisation \u00e9tant ensuite divis\u00e9e par la somme des valeurs (ici les quantit\u00e9s). Dans notre exemple, la moyenne pond\u00e9r\u00e9e se d\u00e9termine ainsi : (18 x 5 \u20ac + 15 x 3 \u20ac) \/45&#8230; soit une moyenne pond\u00e9r\u00e9e (ou CUMP) de 3,53 \u20ac.<\/p>\n<p style=\"text-align: justify;\">Excel n&#8217;offre aucune fonction d\u00e9di\u00e9e mais l&#8217;usage de la fonction <em>SOMMEPROD(plage de cellules;plage de cellules 2)<\/em> associ\u00e9e \u00e0 <em>SOMME(plage de cellules)<\/em> r\u00e9sout ais\u00e9ment ce calcul. Dans le cas qui nous occupe, la formule de calcul du CUMP est r\u00e9dig\u00e9e ainsi :<\/p>\n<p style=\"text-align: center;\">=SOMMEPROD(M7:M9;L7:L9)\/SOMME(L7:L9)<\/p>\n<p style=\"text-align: justify;\">La fonction <em>SOMMEPROD<\/em> renvoie la somme des produits des plages ou matrices sp\u00e9cifi\u00e9es (plus d&#8217;informations sur cette fonction sur le <a href=\"https:\/\/support.office.com\/fr-fr\/article\/SOMMEPROD-SOMMEPROD-fonction-16753E75-9F68-4874-94AC-4D2145A2FD2E\" target=\"_blank\" rel=\"noopener noreferrer\">site de Microsoft<\/a>). Dans notre exemple, la formule calcule la somme des produits (<em>SOMMEPROD<\/em>) entre la plage <em>M7:M9<\/em> (prix unitaires) et <em>L7:L9<\/em> (quantit\u00e9s) divis\u00e9e par la somme des quantit\u00e9s (<em>SOMME(L7:L9)<\/em>).<\/p>\n<p style=\"text-align: justify;\">Ce m\u00eame calcul de CUMP peut \u00eatre obtenu \u00e0 partir d&#8217;une requ\u00eate SQL :<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT ACHATS.R\u00e9f, Sum([prix]*[quantit\u00e9]\/[totalqt\u00e9]) AS CUMP\r\nFROM (SELECT ACHATS.R\u00e9f, Sum(ACHATS.Quantit\u00e9) AS TotalQt\u00e9\r\nFROM ACHATS\r\nGROUP BY ACHATS.R\u00e9f) AS TOTALQTE INNER JOIN ACHATS ON TOTALQTE.R\u00e9f = ACHATS.R\u00e9f\r\nGROUP BY ACHATS.R\u00e9f;<\/pre>\n<p style=\"text-align: justify;\">La sous-<a href=\"https:\/\/www.auditsi.eu\/?p=6913\">requ\u00eate regroupement<\/a> <em>(SELECT ACHATS.R\u00e9f, Sum(ACHATS.Quantit\u00e9) AS TotalQt\u00e9<\/em> calcule la totalisation des quantit\u00e9s (ici 45) puis la requ\u00eate d\u00e9termine le CUMP \u00e0 l&#8217;aide de la formule suivante :<\/p>\n<p style=\"text-align: center;\">Sum([prix]*[quantit\u00e9]\/[totalqt\u00e9]) AS CUMP<\/p>\n<p style=\"text-align: justify;\">La requ\u00eate SQL renvoie le m\u00eame CUMP que la formule Excel, soit 3,53 \u20ac :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8412\" rel=\"attachment wp-att-8412\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8412\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Calcul-CUMP-SQL.png\" alt=\"Calcul CUMP SQL\" width=\"732\" height=\"280\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Calcul-CUMP-SQL.png 732w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Calcul-CUMP-SQL-300x115.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Calcul-CUMP-SQL-730x279.png 730w\" sizes=\"auto, (max-width: 732px) 100vw, 732px\" \/><\/a><\/p>\n<p>Approfondir le sujet : <a href=\"https:\/\/www.auditsi.eu\/?p=633\">EXCEL, VBA : Valoriser un stock selon la m\u00e9thode du PEPS (FIFO) \u00e0 partir des mouvements d\u2019une r\u00e9f\u00e9rence donn\u00e9e<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Calculer%20une%20moyenne%20pond%C3%A9r%C3%A9e%20%28exemple%20appliqu%C3%A9%20au%20CUMP%29\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Le calcul de moyennes pond\u00e9r\u00e9es est d&#8217;usage courant \u00e0 l&#8217;image de la moyenne de notes \u00e0 un examen (du DEC par exemple) ou du co\u00fbt unitaire moyen pond\u00e9r\u00e9 (CUMP) de r\u00e9f\u00e9rences en stock. Le calcul de la moyenne arithm\u00e9tique (obtenue par la formule : totalisation \/ d\u00e9nombrement) est assez simple \u00e0 obtenir. Excel propose une &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=8408\" class=\"more-link\">Continue reading &lsquo;Calculer une moyenne pond\u00e9r\u00e9e (exemple appliqu\u00e9 au CUMP)&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Calculer%20une%20moyenne%20pond%C3%A9r%C3%A9e%20%28exemple%20appliqu%C3%A9%20au%20CUMP%29\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[39,181,48,1326,1820,282],"tags":[2078,1633,698,1862,2079,1960,57,701,1643,367],"class_list":["post-8408","post","type-post","status-publish","format-standard","hentry","category-cas-pratiques","category-cycle-stocks","category-excel","category-maitriser-excel","category-requetes-sql","category-sql","tag-cump","tag-excel","tag-moyenne","tag-moyenne-arithmetique","tag-moyenne-ponderee","tag-requete-regroupement","tag-requete-sql","tag-sommeprod","tag-sql","tag-valorisation-des-stocks"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8408","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8408"}],"version-history":[{"count":7,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8408\/revisions"}],"predecessor-version":[{"id":8417,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8408\/revisions\/8417"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}