{"id":7639,"date":"2018-10-01T00:11:40","date_gmt":"2018-09-30T22:11:40","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=7639"},"modified":"2018-10-01T00:11:40","modified_gmt":"2018-09-30T22:11:40","slug":"vba-excel-creer-un-tableau-croise-dynamique","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=7639","title":{"rendered":"VBA, EXCEL : cr\u00e9er un tableau crois\u00e9 dynamique"},"content":{"rendered":"<p style=\"text-align: justify;\">Le <a href=\"https:\/\/www.auditsi.eu\/?tag=tcd\">tableau crois\u00e9 dynamique (ou TCD)<\/a> affiche des donn\u00e9es structur\u00e9es pouvant comporter des centaines de milliers de lignes sous une forme synth\u00e9tique plus facile \u00e0 lire afin de les analyser et de les explorer. Le TCD est un <a href=\"https:\/\/www.auditsi.eu\/?p=2793\">outil d&#8217;audit de donn\u00e9es<\/a> et d&#8217;aide \u00e0 la d\u00e9cision par excellence au m\u00eame titre que les filtres de donn\u00e9es ou les <a href=\"https:\/\/www.auditsi.eu\/?tag=sous-total\">sous-totaux<\/a>.<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=7640\" rel=\"attachment wp-att-7640\"><img loading=\"lazy\" decoding=\"async\" width=\"1150\" height=\"381\" class=\"aligncenter size-full wp-image-7640\" alt=\"ANA-FEC - TCD Analyse coh\u00e9rence des stocks\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2018\/09\/ANA-FEC-TCD-Analyse-coh\u00e9rence-des-stocks.png\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2018\/09\/ANA-FEC-TCD-Analyse-coh\u00e9rence-des-stocks.png 1150w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2018\/09\/ANA-FEC-TCD-Analyse-coh\u00e9rence-des-stocks-300x99.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2018\/09\/ANA-FEC-TCD-Analyse-coh\u00e9rence-des-stocks-768x254.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2018\/09\/ANA-FEC-TCD-Analyse-coh\u00e9rence-des-stocks-1024x339.png 1024w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2018\/09\/ANA-FEC-TCD-Analyse-coh\u00e9rence-des-stocks-730x242.png 730w\" sizes=\"auto, (max-width: 1150px) 100vw, 1150px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">La construction d&#8217;un TCD EXCEL en VBA peut appara\u00eetre \u00e0 premi\u00e8re vue complexe mais suit en r\u00e9alit\u00e9 quelques \u00e9tapes rigides et ais\u00e9es \u00e0 mettre en application.<\/p>\n<p style=\"text-align: justify;\">Cet article s&#8217;appuiera sur l&#8217;analyse de la coh\u00e9rence des \u00e9critures de stocks tel que programm\u00e9 dans <a href=\"https:\/\/www.auditsi.eu\/?tag=ana-fec\">ANA-FEC<\/a>.<\/p>\n<p style=\"text-align: justify;\">Sur le principe, la comptabilisation des stocks et encours et de leurs d\u00e9pr\u00e9ciations suit un sch\u00e9ma d&#8217;\u00e9critures relativement simple :<\/p>\n<ul>\n<li style=\"text-align: justify;\">Stocks de marchandises : 37x D \/ 6037 C ;<\/li>\n<li>Stocks d&#8217;encours : 34x D \/ 713x ;<\/li>\n<li>D\u00e9pr\u00e9ciation des stocks de marchandises : 68173x D \/ 397x C<\/li>\n<li>&#8230;<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Le contr\u00f4le de la bonne application de ce sch\u00e9ma est aussi simple \u00e0 mettre en \u0153uvre avec un TCD. Ce contr\u00f4le est essentiel pour pr\u00e9venir toute anomalie de comptabilisation entra\u00eenant des \u00e9carts f\u00e2cheux dans le tableau des flux de tr\u00e9sorerie (TFT) ou la liasse fiscale.<\/p>\n<p style=\"text-align: justify;\">Ici, le TCD r\u00e9sume les \u00e9critures mensuelles de comptabilisation des stocks. On observe que chaque mois la comptabilisation des stocks r\u00e9pond au sch\u00e9ma d&#8217;\u00e9criture standard (avec un solde de colonne nul pour les journaux d&#8217;OD ce qui d\u00e9montre qu&#8217;aucun autre compte n&#8217;est mouvement\u00e9).<\/p>\n<p style=\"text-align: justify;\">Programmation du TCD en VBA :<\/p>\n<pre class=\"lang:vb decode:true\">    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=\"'\" &amp; NomOngletFEC &amp; \"'!R2C1:R\" &amp; NbLignes + 1 &amp; \"C32\", Version:=6).CreatePivotTable TableDestination:=\"'\" &amp; NomOngletTCDEnCours &amp; \"'!\" &amp; AdresseTCD, TableName:=\"Tableau crois\u00e9 dynamique1\", DefaultVersion:=6\r\n    Sheets(NomOngletTCDEnCours).Select\r\n    With activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"aaaamm\")\r\n        .Orientation = xlColumnField\r\n        .Position = 1\r\n    End With\r\n    With activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"JournalCode\")\r\n        .Orientation = xlRowField\r\n        .Position = 1\r\n    End With\r\n    With activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"Cpte1\")\r\n        .Orientation = xlRowField\r\n        .Position = 2\r\n    End With\r\n    With activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"Cpte6\")\r\n        .Orientation = xlRowField\r\n        .Position = 3\r\n        For i = 1 To .PivotItems.Count\r\n            If .PivotItems(i).Name Like \"3*\" Or .PivotItems(i).Name Like \"603*\" Or .PivotItems(i).Name Like \"713*\" Or .PivotItems(i).Name Like \"68173*\" Or .PivotItems(i).Name Like \"78173*\" Then\r\n                .PivotItems(i).Visible = True\r\n            Else:\r\n                .PivotItems(i).Visible = False\r\n            End If\r\n        Next i\r\n    End With\r\n    activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").AddDataField activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"Solde\"), \"Somme de Solde\", xlSum\r\n    With activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"Somme de Solde\")\r\n        .NumberFormat = \"#,##0.00\"\r\n    End With\r\n<\/pre>\n<p style=\"text-align: justify;\"><strong>Cr\u00e9ation du TCD<\/strong>&nbsp;&nbsp;(<em>.PivotCaches.Create<\/em>) \u00e0 partir d&#8217;un tableau Excel (<em>SourceType:=xlDatabase<\/em>) situ\u00e9 sur la plage de cellules <em>R2C1:R&#8221; &amp; NbLignes + 1 &amp; &#8220;C32&#8221;<\/em> dans l&#8217;onglet dont le nom est stock\u00e9 dans la variable <em>NomOngletFEC<\/em> (<em>SourceData:=&#8221;&#8216;&#8221; &amp; NomOngletFEC &amp; &#8220;&#8216;!R2C1:R&#8221; &amp; NbLignes + 1 &amp; &#8220;C32&#8221;<\/em>). Le TCD est stock\u00e9 dans l&#8217;onglet dont le nom est stock\u00e9 dans la variable <em>NomOngletTCDEnCours<\/em> (<em>TableDestination:=&#8221;&#8216;&#8221; &amp; NomOngletTCDEnCours &amp; &#8220;&#8216;!&#8221; &amp; AdresseTCD<\/em>).<\/p>\n<pre class=\"lang:vb decode:true\">    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=\"'\" &amp; NomOngletFEC &amp; \"'!R2C1:R\" &amp; NbLignes + 1 &amp; \"C32\", Version:=6).CreatePivotTable TableDestination:=\"'\" &amp; NomOngletTCDEnCours &amp; \"'!\" &amp; AdresseTCD, TableName:=\"Tableau crois\u00e9 dynamique1\", DefaultVersion:=6\r\n<\/pre>\n<p style=\"text-align: justify;\"><strong>Ajout de champs de donn\u00e9es au TCD<\/strong> (<em>.PivotFields<\/em>) :<\/p>\n<pre class=\"lang:vb decode:true\">    With activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"aaaamm\")\r\n        .Orientation = xlColumnField\r\n        .Position = 1\r\n    End With\r\n<\/pre>\n<p style=\"text-align: justify;\">Les champs peuvent \u00eatre positionn\u00e9s en filtre de page (<em>.Orientation = xlPageField<\/em>), en colonnes (<em>.Orientation = xlColumnField<\/em>)&nbsp;ou en lignes (<em>.Orientation = xlRowField<\/em>).<\/p>\n<p style=\"text-align: justify;\">La commande <em>.Position = 1<\/em> indique l&#8217;ordre de positionnement de chaque champ.<\/p>\n<p style=\"text-align: justify;\">La commande <em>.PivotItems().Visible<\/em> permet d&#8217;inclure ou d&#8217;exclure certaines valeurs du TCD.<\/p>\n<pre class=\"lang:vb decode:true\">        For i = 1 To .PivotItems.Count\r\n            If .PivotItems(i).Name Like \"3*\" Or .PivotItems(i).Name Like \"603*\" Or .PivotItems(i).Name Like \"713*\" Or .PivotItems(i).Name Like \"68173*\" Or .PivotItems(i).Name Like \"78173*\" Then\r\n                .PivotItems(i).Visible = True\r\n            Else:\r\n                .PivotItems(i).Visible = False\r\n            End If\r\n        Next i\r\n<\/pre>\n<p style=\"text-align: justify;\">Une <a href=\"https:\/\/www.auditsi.eu\/?p=1696\">boucle compteur <em>For&#8230; To&#8230;&nbsp;Next<\/em><\/a> lit le nom de chaque valeur <em>.PivotItems(i).Name<\/em> de la premi\u00e8re (<em>i = 1<\/em>) \u00e0 la derni\u00e8re (<em>To .PivotItems.Count<\/em>) et le compare (<em>Like<\/em>) aux comptes \u00e0 afficher (<em>&#8220;603*&#8221;<\/em>, <em>&#8220;713*&#8221;<\/em>&#8230;) et en fonction de la correspondance ou non entre le nom est la valeur recherch\u00e9e les rend visible (<em>.PivotItems(i).Visible = True<\/em>) ou non (<em>.PivotItems(i).Visible = False<\/em>). Etant donn\u00e9 le nombre de lignes susceptible d&#8217;\u00eatre trait\u00e9 (jusqu&#8217;\u00e0 un million sous Excel depuis la version 2007), la variable <em>i<\/em> sera de type <em>Long<\/em> (<em>dim i as Long<\/em>).<\/p>\n<p style=\"text-align: justify;\"><strong>Ajout de valeurs<\/strong> (<em>.AddDataField<\/em>) : les donn\u00e9es \u00e0 synth\u00e9tiser peuvent \u00eatre additionn\u00e9es (<em>xlSum<\/em>), d\u00e9nombr\u00e9es (<em>xlCount<\/em>)&#8230;<\/p>\n<pre class=\"lang:vb decode:true \">    activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").AddDataField activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"Solde\"), \"Somme de Solde\", xlSum<\/pre>\n<p style=\"text-align: justify;\"><strong>Formatage de l&#8217;affichage des valeurs<\/strong> (<em>.NumberFormat<\/em>) :<\/p>\n<pre class=\"lang:vb decode:true \">    With activesheet.PivotTables(\"Tableau crois\u00e9 dynamique1\").PivotFields(\"Somme de Solde\")\r\n        .NumberFormat = \"#,##0.00\"\r\n    End With<\/pre>\n<p style=\"text-align: justify;\">Le format avec s\u00e9parateur de milliers et double d\u00e9cimale est repr\u00e9sent\u00e9 ainsi :&nbsp;<em>&#8220;#,##0.00&#8221;<\/em>.<\/p>\n<p>Approfondir le sujet :&nbsp;<a title=\"Programmer en VBA\" href=\"http:\/\/www.auditsi.eu\/?cat=1569\">Programmer en VBA<\/a>, <a href=\"http:\/\/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=\"VBA%2C%20EXCEL%20%3A%20cr%C3%A9er%20un%20tableau%20crois%C3%A9%20dynamique\";<\/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 tableau crois\u00e9 dynamique (ou TCD) affiche des donn\u00e9es structur\u00e9es pouvant comporter des centaines de milliers de lignes sous une forme synth\u00e9tique plus facile \u00e0 lire afin de les analyser et de les explorer. Le TCD est un outil d&#8217;audit de donn\u00e9es et d&#8217;aide \u00e0 la d\u00e9cision par excellence au m\u00eame titre que les filtres &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=7639\" class=\"more-link\">Continue reading &lsquo;VBA, EXCEL : cr\u00e9er un tableau crois\u00e9 dynamique&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=\"VBA%2C%20EXCEL%20%3A%20cr%C3%A9er%20un%20tableau%20crois%C3%A9%20dynamique\";<\/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,39,40,181,1569,470,215],"tags":[1979,1660,1966,1971,1977,1980,1975,165,1961,166,1965,371,848,387,164,1976,286,1969,1967,1964,68,1970,182,1586,847,1641,1972,1942,1968,1973,1974,1978],"class_list":["post-7639","post","type-post","status-publish","format-standard","hentry","category-audit-de-donnees","category-cas-pratiques","category-controle-des-comptes","category-cycle-stocks","category-programmer-en-vba","category-techniques-daudit","category-vba","tag-adddatafield","tag-numberformat","tag-pivotcaches-create","tag-pivotfields","tag-pivotitems-name","tag-pivotitems-visible","tag-pivotitems-count","tag-aide-a-la-decision","tag-ana-fec","tag-analyse-de-donnees","tag-boucle-compteur","tag-controle-des-stocks","tag-false","tag-for-to-next","tag-informatique-decisionnelle","tag-like","tag-or","tag-sourcedata","tag-sourcetype","tag-stocks-et-encours","tag-tableau-croise-dynamique","tag-tabledestination","tag-tcd","tag-tft","tag-true","tag-vba","tag-xlcolumnfield","tag-xlcount","tag-xldatabase","tag-xlpagefield","tag-xlrowfield","tag-xlsum"],"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\/7639","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=7639"}],"version-history":[{"count":4,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/7639\/revisions"}],"predecessor-version":[{"id":7644,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/7639\/revisions\/7644"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7639"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7639"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7639"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}