{"id":1475,"date":"2012-09-23T12:38:31","date_gmt":"2012-09-23T10:38:31","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=1475"},"modified":"2014-05-06T22:03:09","modified_gmt":"2014-05-06T20:03:09","slug":"excel-2010-les-calculs-en-trois-dimensions","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=1475","title":{"rendered":"EXCEL : les calculs en trois dimensions"},"content":{"rendered":"<p style=\"text-align: justify;\">EXCEL est dot\u00e9e d&#8217;une fonction tr\u00e8s puissante : le calcul de sommes en trois dimensions.<\/p>\n<p style=\"text-align: justify;\">Concr\u00e8tement, cela signifie que l&#8217;on peut additionner des montants contenus dans diff\u00e9rentes feuilles (onglets) d&#8217;un m\u00eame classeur tr\u00e8s simplement \u00e0 l&#8217;aide de la fonction SOMME().<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/09\/SOMME-EN-3D.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2037 aligncenter\" title=\"Somme en 3D sous Excel 2010\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/09\/SOMME-EN-3D.png\" alt=\"Somme en 3D sous Excel 2010\" width=\"481\" height=\"445\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/09\/SOMME-EN-3D.png 481w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/09\/SOMME-EN-3D-300x277.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/09\/SOMME-EN-3D-1x1.png 1w\" sizes=\"auto, (max-width: 481px) 100vw, 481px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Prenons, l&#8217;exemple d&#8217;une entreprise qui a trois magasins. Un tableau de bord sous forme de classeur Excel permet de suivre le chiffre d&#8217;affaires (CA) par type de produits (lignes), par mois (colonne) et par magasin (onglets).<\/p>\n<p style=\"text-align: justify;\">Pour \u00e9tablir une consolidation du CA au niveau de l&#8217;entreprise selon la m\u00eame structure de tableau (par type de produits en lignes et par mois en colonnes), la formule de calcul est g\u00e9n\u00e9ralement r\u00e9dig\u00e9e ainsi :<\/p>\n<p style=\"text-align: center;\">=Magasin1!B5+Magasin2!B5+Magasin3!B5<\/p>\n<p style=\"text-align: justify;\">Si cette formule est parfaitement fonctionnelle, elle souffre toutefois d&#8217;un inconv\u00e9nient majeur : en pr\u00e9sence d&#8217;un nombre d&#8217;onglets (ici magasins) important ou en cas d&#8217;ajout d&#8217;onglets nouveaux, la r\u00e9daction ou la mise \u00e0 jour des formules s&#8217;av\u00e8re rapidement fastidieuse et le risque d&#8217;erreurs cro\u00eet.<\/p>\n<p style=\"text-align: justify;\">La t\u00e2che peut \u00eatre nettement simplifi\u00e9e \u00e0 l&#8217;aide des calculs en 3D puisqu&#8217;il suffit de pr\u00e9ciser l&#8217;\u00e9tendue des onglets (ici : Magasin1:Magasin3) ainsi que la plage de cellules concern\u00e9es par la sommation. La formule sera donc :<\/p>\n<p style=\"text-align: center;\">=SOMME(Magasin1:Magasin3!B5)<\/p>\n<p style=\"text-align: justify;\">L&#8217;ajout de nouveaux onglets entre Magasin1 et Magasin3 mettra \u00e0 jour automatiquement les calculs de la consolidation.<\/p>\n<p style=\"text-align: justify;\">A noter, seuls les onglets compris &#8220;physiquement&#8221; entre le premier onglet mentionn\u00e9 (Magasin1) et le dernier (Magasin3) sont pris en compte dans les calculs ; ainsi si, par exemple, l&#8217;onglet Magasin 2 est situ\u00e9 \u00e0 droite de Magasin3, il ne sera donc pas additionn\u00e9.<\/p>\n<p style=\"text-align: justify;\"><a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"http:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d&#8217;articles Ma\u00eetriser Excel<\/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=\"EXCEL%20%3A%20les%20calculs%20en%20trois%20dimensions\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>EXCEL est dot\u00e9e d&#8217;une fonction tr\u00e8s puissante : le calcul de sommes en trois dimensions. Concr\u00e8tement, cela signifie que l&#8217;on peut additionner des montants contenus dans diff\u00e9rentes feuilles (onglets) d&#8217;un m\u00eame classeur tr\u00e8s simplement \u00e0 l&#8217;aide de la fonction SOMME(). Prenons, l&#8217;exemple d&#8217;une entreprise qui a trois magasins. Un tableau de bord sous forme de &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=1475\" class=\"more-link\">Continue reading &lsquo;EXCEL : les calculs en trois dimensions&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=\"EXCEL%20%3A%20les%20calculs%20en%20trois%20dimensions\";<\/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,48,1326],"tags":[1069,1633,1144,292,375,1139],"class_list":["post-1475","post","type-post","status-publish","format-standard","hentry","category-cas-pratiques","category-excel","category-maitriser-excel","tag-3d","tag-excel","tag-formule-de-calcul","tag-somme","tag-tableau-de-bord","tag-totalisation"],"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\/1475","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=1475"}],"version-history":[{"count":15,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/1475\/revisions"}],"predecessor-version":[{"id":4814,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/1475\/revisions\/4814"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}