{"id":7998,"date":"2019-02-09T17:42:34","date_gmt":"2019-02-09T16:42:34","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=7998"},"modified":"2021-01-17T15:26:30","modified_gmt":"2021-01-17T14:26:30","slug":"excel-tableau-en-milliers-ke-ou-millions-deuros-me-sans-formules","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=7998","title":{"rendered":"EXCEL : afficher un tableau en milliers (K\u20ac) ou millions d\u2019euros (M\u20ac) sans modifier les donn\u00e9es source"},"content":{"rendered":"<p style=\"text-align: justify;\">Des tableaux comportant de nombreuses donn\u00e9es chiffr\u00e9es gagnent en lisibilit\u00e9 \u00e0 \u00eatre pr\u00e9sent\u00e9s en K\u20ac ou en M\u20ac.<\/p>\n<p style=\"text-align: justify;\">L\u2019utilisation des formats personnalis\u00e9s d\u2019Excel permet de modifier l\u2019affichage des donn\u00e9es sans pour autant modifier les donn\u00e9es sources.<\/p>\n<div id=\"attachment_7999\" style=\"width: 656px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=7999\" rel=\"attachment wp-att-7999\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-7999\" class=\"size-full wp-image-7999\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9.png\" alt=\"Les donn\u00e9es brutes (en euros)\" width=\"646\" height=\"295\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9.png 646w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-300x137.png 300w\" sizes=\"auto, (max-width: 646px) 100vw, 646px\" \/><\/a><p id=\"caption-attachment-7999\" class=\"wp-caption-text\">Les donn\u00e9es brutes (en euros)<\/p><\/div>\n<p style=\"text-align: justify;\">Pour ce faire, s\u00e9lectionner les donn\u00e9es \u00e0 formater. Ouvrir la fen\u00eatre Format Nombre (ou clic droit puis&nbsp;Format de cellule, Nombre).<\/p>\n<p style=\"text-align: justify;\">Dans la zone de saisie intitul\u00e9e Type, saisir :<\/p>\n<p style=\"text-align: center;\"># ##0,0<\/p>\n<p style=\"text-align: justify;\">Le&nbsp;symbole di\u00e8se (#) permet de d\u00e9finir la forme sous laquelle l&#8217;utilisateur souhaite repr\u00e9senter ses donn\u00e9es chiffr\u00e9es. Ici, les donn\u00e9es sont affich\u00e9es avec un s\u00e9parateur de milliers (un espace entre les deux di\u00e8ses) et une d\u00e9cimale. Le signe 0 force l&#8217;affichage des z\u00e9ros.<\/p>\n<div id=\"attachment_8000\" style=\"width: 715px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8000\" rel=\"attachment wp-att-8000\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-8000\" class=\"size-full wp-image-8000\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-milliers.png\" alt=\"Format personnalis\u00e9 : milliers\" width=\"705\" height=\"579\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-milliers.png 705w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-milliers-300x246.png 300w\" sizes=\"auto, (max-width: 705px) 100vw, 705px\" \/><\/a><p id=\"caption-attachment-8000\" class=\"wp-caption-text\">Format personnalis\u00e9 : milliers<\/p><\/div>\n<p style=\"text-align: justify;\">En ajoutant un espace \u00e0 la suite du format num\u00e9rique, l&#8217;affichage de la donn\u00e9e chiffr\u00e9e est divis\u00e9 par mille :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8002\" rel=\"attachment wp-att-8002\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8002\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-milliers.png\" alt=\"Format personnalis\u00e9 - Donn\u00e9es en milliers\" width=\"644\" height=\"292\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-milliers.png 644w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-milliers-300x136.png 300w\" sizes=\"auto, (max-width: 644px) 100vw, 644px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">En ajoutant un deuxi\u00e8me espace, l&#8217;affichage est de nouveau divis\u00e9 par mille (soit au total par un million). L&#8217;ajout d&#8217;espaces est \u00e0 reproduire autant que n\u00e9cessaire.<\/p>\n<div id=\"attachment_8001\" style=\"width: 574px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8001\" rel=\"attachment wp-att-8001\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-8001\" class=\"size-full wp-image-8001\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-millions.png\" alt=\"Format personnalis\u00e9 : millions\" width=\"564\" height=\"465\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-millions.png 564w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-millions-300x247.png 300w\" sizes=\"auto, (max-width: 564px) 100vw, 564px\" \/><\/a><p id=\"caption-attachment-8001\" class=\"wp-caption-text\">Format personnalis\u00e9 : millions<\/p><\/div>\n<p style=\"text-align: justify;\">Les m\u00eames donn\u00e9es pr\u00e9sent\u00e9es en millions&nbsp;:<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8005\" rel=\"attachment wp-att-8005\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8005\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-millions.png\" alt=\"Format personnalis\u00e9 - Donn\u00e9es en millions\" width=\"644\" height=\"292\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-millions.png 644w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-millions-300x136.png 300w\" sizes=\"auto, (max-width: 644px) 100vw, 644px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Pour afficher l&#8217;unit\u00e9 de mesure (ici M\u20ac), il suffit d&#8217;ajouter \u00e0 la suite du format num\u00e9rique M\u20ac entre guillemets (les guillemets d\u00e9limitent une cha\u00eene de caract\u00e8res) : &#8221; M\u20ac&#8221; (ne pas oublier d&#8217;ins\u00e9rer un espace avant M\u20ac pour s\u00e9parer les donn\u00e9es chiffr\u00e9es de l&#8217;unit\u00e9 de mesure).<\/p>\n<div id=\"attachment_8006\" style=\"width: 574px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8006\" rel=\"attachment wp-att-8006\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-8006\" class=\"size-full wp-image-8006\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-millions-deuros.png\" alt=\"Format personnalis\u00e9 : avec M\u20ac\" width=\"564\" height=\"465\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-millions-deuros.png 564w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-millions-deuros-300x247.png 300w\" sizes=\"auto, (max-width: 564px) 100vw, 564px\" \/><\/a><p id=\"caption-attachment-8006\" class=\"wp-caption-text\">Format personnalis\u00e9 : avec M\u20ac<\/p><\/div>\n<p>Les donn\u00e9es affich\u00e9es en M\u20ac :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8007\" rel=\"attachment wp-att-8007\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8007\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-millions-deuros.png\" alt=\"Excel format personnalis\u00e9 - Donn\u00e9es en millions d'euros\" width=\"645\" height=\"327\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-millions-deuros.png 645w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/02\/Excel-format-personnalis\u00e9-Donn\u00e9es-en-millions-deuros-300x152.png 300w\" sizes=\"auto, (max-width: 645px) 100vw, 645px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">L&#8217;unit\u00e9 de mesure (M\u20ac) est affich\u00e9e mais les donn\u00e9es sont toujours en format num\u00e9rique (il est possible d&#8217;effectuer des calculs avec).<\/p>\n<p style=\"text-align: justify;\">Ces op\u00e9rations sont automatisables \u00e0 l&#8217;aide du langage VBA, cf <a href=\"https:\/\/www.auditsi.eu\/?p=6370\">Formater l&#8217;affichage des nombres en VBA<\/a>.<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"http:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles 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%20afficher%20un%20tableau%20en%20milliers%20%28K%E2%82%AC%29%20ou%20millions%20d%E2%80%99euros%20%28M%E2%82%AC%29%20sans%20modifier%20les%20donn%C3%A9es%20source\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Des tableaux comportant de nombreuses donn\u00e9es chiffr\u00e9es gagnent en lisibilit\u00e9 \u00e0 \u00eatre pr\u00e9sent\u00e9s en K\u20ac ou en M\u20ac. L\u2019utilisation des formats personnalis\u00e9s d\u2019Excel permet de modifier l\u2019affichage des donn\u00e9es sans pour autant modifier les donn\u00e9es sources.<\/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%20afficher%20un%20tableau%20en%20milliers%20%28K%E2%82%AC%29%20ou%20millions%20d%E2%80%99euros%20%28M%E2%82%AC%29%20sans%20modifier%20les%20donn%C3%A9es%20source\";<\/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":[48,1326],"tags":[2003,927,1633,2002,1999,2000,2001,1840,1662],"class_list":["post-7998","post","type-post","status-publish","format-standard","hentry","category-excel","category-maitriser-excel","tag-decimales","tag-excel","tag-format-de-cellule","tag-format-personnalise","tag-ke","tag-me","tag-nombres","tag-separateur-de-milliers"],"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\/7998","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=7998"}],"version-history":[{"count":7,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/7998\/revisions"}],"predecessor-version":[{"id":9460,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/7998\/revisions\/9460"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7998"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7998"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7998"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}