{"id":8972,"date":"2020-08-23T02:26:13","date_gmt":"2020-08-23T00:26:13","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=8972"},"modified":"2020-08-05T23:32:57","modified_gmt":"2020-08-05T21:32:57","slug":"creer-un-tableau-de-bord-dynamique-avec-excel-en-quelques-clics-2eme-partie-creer-un-tableau-croise-dynamique-tcd-connecte-a-une-source-de-donnees","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=8972","title":{"rendered":"Cr\u00e9er un tableau de bord dynamique avec Excel en quelques clics &#8211; 2\u00e8me partie : cr\u00e9er un tableau crois\u00e9 dynamique (TCD) connect\u00e9 \u00e0 une source de donn\u00e9es"},"content":{"rendered":"<p style=\"text-align: justify;\">Les <a href=\"https:\/\/www.auditsi.eu\/?p=8969\">tableaux de bord dynamiques<\/a> (ou interactifs) ont la particularit\u00e9 de se mettre \u00e0 jour automatiquement en fonction des changement op\u00e9r\u00e9s sur les donn\u00e9es sources ou des crit\u00e8res d\u00e9finis par l&#8217;utilisateur.<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9007\" rel=\"attachment wp-att-9007\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9007\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s2.png\" alt=\"Excel - TCD achats non stock\u00e9s\" width=\"750\" height=\"365\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s2.png 750w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s2-300x146.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s2-730x355.png 730w\" sizes=\"auto, (max-width: 750px) 100vw, 750px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Dans la pr\u00e9c\u00e9dente partie, a \u00e9t\u00e9 abord\u00e9 la <a href=\"https:\/\/www.auditsi.eu\/?p=8971\">cr\u00e9ation du connecteur de donn\u00e9es<\/a>. La pr\u00e9sente partie s&#8217;attachera \u00e0 traiter de la mise en oeuvre de <a href=\"https:\/\/www.auditsi.eu\/?tag=tcd\">tableaux crois\u00e9s dynamiques ou TCD<\/a> connect\u00e9s \u00e0 la source de donn\u00e9es.<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8986\" rel=\"attachment wp-att-8986\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8986\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Insertion-Tableaux.png\" alt=\"Excel - Insertion Tableaux\" width=\"220\" height=\"122\"><\/a><\/p>\n<p style=\"text-align: justify;\">Pour ce faire, rendez-vous dans le menu Insertion puis Tableau crois\u00e9 dynamique :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8987\" rel=\"attachment wp-att-8987\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8987\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Insertion-Cr\u00e9er-un-TCD.png\" alt=\"Excel - Insertion Cr\u00e9er un TCD\" width=\"406\" height=\"346\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Insertion-Cr\u00e9er-un-TCD.png 406w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Insertion-Cr\u00e9er-un-TCD-300x256.png 300w\" sizes=\"auto, (max-width: 406px) 100vw, 406px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Cocher le radio &#8220;Utiliser une source de donn\u00e9es externes&#8221; puis cliquer sur le bouton Choisir a connexion&#8230; :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8981\" rel=\"attachment wp-att-8981\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8981\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Donn\u00e9es-connexions-existantes.png\" alt=\"Excel - Donn\u00e9es (connexions existantes)\" width=\"486\" height=\"493\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Donn\u00e9es-connexions-existantes.png 486w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Donn\u00e9es-connexions-existantes-296x300.png 296w\" sizes=\"auto, (max-width: 486px) 100vw, 486px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">S\u00e9lectionner la connexion &#8220;Source TB dynamique&#8221; (cr\u00e9\u00e9e dans la 1\u00e8re partie de cet article) puis cliquer sur le bouton ouvrir. Le nom de la connexion est mis \u00e0 jour.<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8989\" rel=\"attachment wp-att-8989\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8989\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Insertion-Cr\u00e9er-un-TCD-nom-connexion.png\" alt=\"Excel - Insertion Cr\u00e9er un TCD (nom connexion)\" width=\"406\" height=\"346\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Insertion-Cr\u00e9er-un-TCD-nom-connexion.png 406w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-Insertion-Cr\u00e9er-un-TCD-nom-connexion-300x256.png 300w\" sizes=\"auto, (max-width: 406px) 100vw, 406px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Indiquer l&#8217;emplacement o\u00f9 ins\u00e9rer le TCD (ici : Feuil1!$A$5) puis fermer la fen\u00eatre (bouton Ok).<\/p>\n<p style=\"text-align: justify;\">Le TCD appara\u00eet \u00e0 l&#8217;\u00e9cran :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8990\" rel=\"attachment wp-att-8990\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8990\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-vierge.png\" alt=\"Excel - TCD vierge\" width=\"882\" height=\"732\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-vierge.png 882w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-vierge-300x249.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-vierge-768x637.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-vierge-730x606.png 730w\" sizes=\"auto, (max-width: 882px) 100vw, 882px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Il reste \u00e0 glisser les champs de donn\u00e9es dans les diff\u00e9rentes zones :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9005\" rel=\"attachment wp-att-9005\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9005\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s.png\" alt=\"Excel - TCD achats non stock\u00e9s\" width=\"1233\" height=\"733\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s.png 1233w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s-300x178.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s-1024x609.png 1024w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s-768x457.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s-730x434.png 730w\" sizes=\"auto, (max-width: 1233px) 100vw, 1233px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Un des int\u00e9r\u00eats du TCD (et qui contribue \u00e0 rendre le tableau de bord dynamique) est qu&#8217;en double-cliquant sur un des montants chiffr\u00e9s, le d\u00e9tail constituant ce montant appara\u00eet instantan\u00e9ment dans un nouvel onglet.<\/p>\n<p style=\"text-align: justify;\">Pour finir <strong>quelques astuces<\/strong> pour <a href=\"https:\/\/www.auditsi.eu\/?p=8997\">parfaire la pr\u00e9sentation du TCD<\/a>.<\/p>\n<p>Le classeur Excel du tableau de bord est t\u00e9l\u00e9chargeable en bas de la page.<\/p>\n<p style=\"text-align: justify;\">Dans la prochaine partie, sera abord\u00e9e la mise en oeuvre de formats conditionnels sur un TCD en vue de mettre en lumi\u00e8re les informations qu&#8217;il contient :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9019\" rel=\"attachment wp-att-9019\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9019\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s-avec-barres-de-donn\u00e9es.png\" alt=\"Excel - TCD achats non stock\u00e9s avec barres de donn\u00e9es\" width=\"752\" height=\"368\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s-avec-barres-de-donn\u00e9es.png 752w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s-avec-barres-de-donn\u00e9es-300x147.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/07\/Excel-TCD-achats-non-stock\u00e9s-avec-barres-de-donn\u00e9es-730x357.png 730w\" sizes=\"auto, (max-width: 752px) 100vw, 752px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">A suivre\u2026<\/p>\n<p style=\"text-align: justify;\">D\u00e9couvrir <a href=\"https:\/\/www.auditsi.eu\/?cat=2265\">tous les articles de cette s\u00e9rie<\/a>.<\/p>\n<p style=\"text-align: justify;\">___<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a href=\"https:\/\/www.auditsi.eu\/?p=8893\">Elaborer un tableau de bord avec des \u00e9l\u00e9ments visuels percutants<\/a> \/ <a href=\"https:\/\/www.auditsi.eu\/?glossary=excel\">Tout savoir sur Excel<\/a> \/ <a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"https:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles Ma\u00eetriser Excel<\/a>&nbsp;\/ En savoir plus sur l\u2019<a href=\"https:\/\/www.auditsi.eu\/?tag=analyse-de-donnees\">analyse de donn\u00e9es<\/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=\"Cr%C3%A9er%20un%20tableau%20de%20bord%20dynamique%20avec%20Excel%20en%20quelques%20clics%20-%202%C3%A8me%20partie%20%3A%20cr%C3%A9er%20un%20tableau%20crois%C3%A9%20dynamique%20%28TCD%29%20connect%C3%A9%20%C3%A0%20une%20source%20de%20donn%C3%A9es\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Les tableaux de bord dynamiques (ou interactifs) ont la particularit\u00e9 de se mettre \u00e0 jour automatiquement en fonction des changement op\u00e9r\u00e9s sur les donn\u00e9es sources ou des crit\u00e8res d\u00e9finis par l&#8217;utilisateur. Dans la pr\u00e9c\u00e9dente partie, a \u00e9t\u00e9 abord\u00e9 la cr\u00e9ation du connecteur de donn\u00e9es. La pr\u00e9sente partie s&#8217;attachera \u00e0 traiter de la mise en oeuvre &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=8972\" class=\"more-link\">Continue reading &lsquo;Cr\u00e9er un tableau de bord dynamique avec Excel en quelques clics &#8211; 2\u00e8me partie : cr\u00e9er un tableau crois\u00e9 dynamique (TCD) connect\u00e9 \u00e0 une source de donn\u00e9es&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=\"Cr%C3%A9er%20un%20tableau%20de%20bord%20dynamique%20avec%20Excel%20en%20quelques%20clics%20-%202%C3%A8me%20partie%20%3A%20cr%C3%A9er%20un%20tableau%20crois%C3%A9%20dynamique%20%28TCD%29%20connect%C3%A9%20%C3%A0%20une%20source%20de%20donn%C3%A9es\";<\/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":[50,48,1326,2265],"tags":[166,2119,1633,2303,2267,68,182],"class_list":["post-8972","post","type-post","status-publish","format-standard","hentry","category-audit-de-donnees","category-excel","category-maitriser-excel","category-tableaux-de-bord-dynamiques","tag-analyse-de-donnees","tag-connecteur-de-donnees","tag-excel","tag-interactif","tag-menu-insertion","tag-tableau-croise-dynamique","tag-tcd"],"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\/8972","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=8972"}],"version-history":[{"count":12,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8972\/revisions"}],"predecessor-version":[{"id":9094,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8972\/revisions\/9094"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8972"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8972"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8972"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}