{"id":639,"date":"2011-06-09T14:14:05","date_gmt":"2011-06-09T12:14:05","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=639"},"modified":"2015-01-25T01:41:47","modified_gmt":"2015-01-24T23:41:47","slug":"excel-vba-agregation-automatisee-de-donnees-comptables-de-filiales-au-sein-d%e2%80%99un-groupe-%c2%ab-reporting-%c2%bb","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=639","title":{"rendered":"EXCEL, VBA : Agr\u00e9gation automatis\u00e9e de donn\u00e9es comptables de filiales au sein d\u2019un groupe (\u00ab reporting \u00bb)"},"content":{"rendered":"<p style=\"text-align: justify;\">La remont\u00e9e des informations comptables (chiffre d\u2019affaires, r\u00e9sultats\u2026), financi\u00e8res, commerciales (carnet de commande, satisfaction client\u2026), etc. des filiales vers la holding s\u2019op\u00e8re g\u00e9n\u00e9ralement \u00e0 l\u2019aide de tableaux de bord mensuels standardis\u00e9s ; cette op\u00e9ration est commun\u00e9ment appel\u00e9e <em>reporting<\/em>. L\u2019agr\u00e9gation de toutes ces donn\u00e9es permet d\u2019effectuer des comparaisons (entre filiales, par rapport au pr\u00e9visionnel\u2026) et de prendre des mesures correctrices le cas \u00e9ch\u00e9ant. L\u2019informatique d\u00e9cisionnelle rev\u00eat ici tout son sens.<\/p>\n<p style=\"text-align: justify;\">Parmi les solutions les plus pertinentes, celle du VBA offre de s\u00e9rieux atouts :<\/p>\n<ul>\n<li style=\"text-align: justify;\"><span style=\"font-family: Calibri; font-size: small;\">Rapidit\u00e9 d\u2019ex\u00e9cution : quelques secondes suffisent pour r\u00e9cup\u00e9rer des donn\u00e9es diss\u00e9min\u00e9es dans des dizaines de feuilles de calcul,<\/span><\/li>\n<li style=\"text-align: justify;\"><span style=\"font-family: Calibri; font-size: small;\">Flexibilit\u00e9 : en cas de changement dans la pr\u00e9sentation des tableaux de bord, il suffit de changer quelques lignes de code VBA et non pas toutes les formules de calcul de chacune des feuilles\u2026<\/span><\/li>\n<li style=\"text-align: justify;\"><span style=\"font-family: Calibri; font-size: small;\">S\u00e9curit\u00e9 : lors de l\u2019entr\u00e9e d\u2019une nouvelle filiale dans le groupe, elle est int\u00e9gr\u00e9e automatiquement dans le processus d\u2019agr\u00e9gation de donn\u00e9es sans qu\u2019il soit n\u00e9cessaire de modifier quelque ligne de code que ce soit.<\/span><\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><strong><em><span style=\"font-size: small;\">Exemple : remont\u00e9e des tableaux de bord d\u2019un groupe de distributeurs automobiles PEUGEOT<\/span><\/em><\/strong><\/p>\n<p style=\"text-align: justify;\">Afin d\u2019illustrer notre propos, nous allons prendre le cas d\u2019un groupe de distributeurs automobiles. Tous les mois, les concessionnaires doivent produire au constructeur, Peugeot, un tableau de bord standardis\u00e9 \u00ab REFECO \u00bb. Ce tableau de bord (diffus\u00e9 sous forme de fichier EXCEL) mentionne diff\u00e9rentes informations commerciales par activit\u00e9 (vente de v\u00e9hicules neufs (VN), de v\u00e9hicules d\u2019occasion (VO), activit\u00e9 atelier (APV)\u2026).<\/p>\n<p style=\"text-align: justify;\">Un groupe r\u00e9unissant en son sein plusieurs concessionnaires de marque PEUGEOT pourra souhaiter \u00ab consolider \u00bb ces tableaux. C\u2019est pr\u00e9cis\u00e9ment cette \u00ab consolidation \u00bb que r\u00e9alise l\u2019application que je vous propose ci-apr\u00e8s.<\/p>\n<div id=\"attachment_730\" style=\"width: 491px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2011\/06\/REFECO-VN.jpg\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-730\" class=\"size-full wp-image-730\" title=\"REFECO - VN\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2011\/06\/REFECO-VN.jpg\" alt=\"REFECO - Activit\u00e9 VN\" width=\"481\" height=\"359\" \/><\/a><p id=\"caption-attachment-730\" class=\"wp-caption-text\">REFECO &#8211; Activit\u00e9 VN<\/p><\/div>\n<p>Ce code source, \u00e9crit en VBA, reprend le chiffre d\u2019affaires de l\u2019activit\u00e9 VN (en K\u20ac et en quantit\u00e9) du tableau de bord de chaque soci\u00e9t\u00e9 et effectue quelques calculs (comparaison N\/N-1, prix de vente moyen au VN\u2026).<\/p>\n<p style=\"text-align: justify;\">La \u00ab consolidation \u00bb de l\u2019activit\u00e9 VN appara\u00eet dans le tableau reproduit ci-apr\u00e8s (extrait) :<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2011\/06\/R\u00e9sultatCentralREFECO.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1092\" title=\"R\u00e9sultatCentralREFECO\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2011\/06\/R\u00e9sultatCentralREFECO.jpg\" alt=\"\" width=\"325\" height=\"272\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2011\/06\/R\u00e9sultatCentralREFECO.jpg 325w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2011\/06\/R\u00e9sultatCentralREFECO-300x251.jpg 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2011\/06\/R\u00e9sultatCentralREFECO-1x1.jpg 1w\" sizes=\"auto, (max-width: 325px) 100vw, 325px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><em><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Code source de l\u2019application :<\/span><\/span><\/em><\/p>\n<pre class=\"lang:default decode:true\">'CentralREFECO\r\n'=&gt; Agr\u00e9gation des donn\u00e9es des REFECO dans un tableau \"groupe\"\r\n'\r\n'Programm\u00e9 par Beno\u00eet-Ren\u00e9 RIVIERE, www.auditsi.eu, benoit@auditsi.eu (06\/2011)\r\n \r\nOption Explicit\r\n \r\n'D\u00e9claration des constantes &amp; variables\r\n'---Fichiers\r\nConst ExtXLS = \"xls\"\r\nConst NomDossierREFECO = \"REFECO\"\r\n'---G\u00e9n\u00e9ral\r\nConst NomOngletGal = \"00a\"\r\nConst AdresseNomEntit\u00e9 = \"C4\"\r\n'---Activit\u00e9 VN\r\nConst NomOngletVN = \"10a\"\r\nConst AdresseQt\u00e9VN_N = \"K11\"\r\nConst AdresseQt\u00e9VN_N1 = \"P11\"\r\nConst AdresseCAVN_N = \"K13\"\r\nConst AdresseCAVN_N1 = \"P13\"\r\n \r\n'D\u00e9claration des variables\r\n'---Chemin\r\nDim chemin As String\r\n'---Objets dossier et fichier\r\nDim ObjFSO, ObjDossier, ObjFichier\r\n'---Classeurs &amp; onglets\r\nDim NomClasseurREFECOEnCours As String\r\nDim ClasseurREFECO As Workbook\r\nDim ClasseurAgr\u00e9gat As Workbook\r\nDim NomEntit\u00e9 As String\r\nDim NumEntit\u00e9 As Integer\r\nDim NbFichiers As Integer\r\n \r\nFunction FormatCellule(ColCell As Integer)\r\n  Select Case ColCell\r\n    Case 1, 2, 4, 5, 7, 8\r\n      FormatCellule = \"# ###\"\r\n    Case 3, 6, 9\r\n      FormatCellule = \"#.00%\"\r\n  End Select\r\nEnd Function\r\n \r\nSub TraitementREFECOEnCours()\r\n  Dim ligneencours As Integer\r\n  Dim i As Integer\r\n  Dim s As String\r\n  Dim c As Variant\r\n  Dim c1 As Variant\r\n  'Ouverture du REFECO\r\n  Set ClasseurREFECO = Workbooks.Open(chemin &amp; NomClasseurREFECOEnCours)\r\n  'Agr\u00e9gation des donn\u00e9es\r\n  ligneencours = 3\r\n  With ClasseurAgr\u00e9gat.Sheets(1)\r\n    If NumEntit\u00e9 = 1 Then\r\n      .Range(\"A1:L100\").ClearContents\r\n      For i = 1 To 9\r\n        Select Case i\r\n          Case 1\r\n            s = \"CA VN N\"\r\n          Case 2\r\n            s = \"CA VN N-1\"\r\n          Case 3\r\n            s = \"VAR\u00b0 CA VN %\"\r\n          Case 4\r\n            s = \"Qt\u00e9 VN N\"\r\n          Case 5\r\n            s = \"Qt\u00e9 VN N-1\"\r\n          Case 6\r\n            s = \"VAR\u00b0 Qt\u00e9 VN %\"\r\n          Case 7\r\n            s = \"CA moy au VN N\"\r\n          Case 8\r\n            s = \"CA moy au VN N-1\"\r\n          Case 9\r\n            s = \"VAR\u00b0 CA moy\"\r\n        End Select\r\n        .Cells(ligneencours, 4 + i - 1).Value = s\r\n      Next i\r\n    End If\r\n    ligneencours = ligneencours + NumEntit\u00e9\r\n    NomEntit\u00e9 = ClasseurREFECO.Sheets(NomOngletGal).Range(AdresseNomEntit\u00e9).Value\r\n    .Cells(ligneencours, 1).Value = NomEntit\u00e9\r\n    For i = 1 To 9\r\n      Select Case i\r\n        Case 1\r\n          c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseCAVN_N).Value\r\n        Case 2\r\n          c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseCAVN_N1).Value\r\n        Case 3, 6, 9\r\n          c1 = .Cells(ligneencours, 4 + i - 1 - 1).Value\r\n          If c1 &lt;&gt; 0 Then\r\n            c = (.Cells(ligneencours, 4 + i - 1 - 2).Value - c1) \/ c1\r\n          Else: c = 0\r\n          End If\r\n        Case 4\r\n          c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseQt\u00e9VN_N).Value\r\n        Case 5\r\n          c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseQt\u00e9VN_N1).Value\r\n        Case 7, 8\r\n          c1 = .Cells(ligneencours, 4 + i - 1 - 3).Value\r\n          If c1 &lt;&gt; 0 Then\r\n            c = .Cells(ligneencours, 4 + i - 1 - 6).Value \/ c1 * 1000\r\n          Else: c = 0\r\n          End If\r\n      End Select\r\n      With .Cells(ligneencours, 4 + i - 1)\r\n        .NumberFormat = FormatCellule(i)\r\n        .Value = c\r\n      End With\r\n    Next i\r\n  End With\r\n  'Fermeture du REFECO\r\n  ClasseurREFECO.Close SaveChanges:=False\r\nEnd Sub\r\n \r\nSub Totaux()\r\n  Dim i As Integer\r\n \r\n  For i = 1 To 9\r\n    With ClasseurAgr\u00e9gat.Sheets(1)\r\n      If i = 1 Then .Cells(5 + NumEntit\u00e9, 1).Value = \"TOTAUX\"\r\n      With .Cells(5 + NumEntit\u00e9, 4 + i - 1)\r\n        .NumberFormat = FormatCellule(i)\r\n        Select Case i\r\n          Case 1, 2, 4, 5, 7, 8\r\n            .FormulaLocal = \"=somme(\" &amp; Chr(4 + 64 + i - 1) &amp; \"4:\" &amp; Chr(4 + 64 + i - 1) &amp; 4 + NumEntit\u00e9 - 1 &amp; \")\"\r\n          Case 3, 6, 9\r\n            .FormulaLocal = \"=(\" &amp; Chr(4 + 64 + i - 3) &amp; 5 + NumEntit\u00e9 &amp; \"-\" &amp; Chr(4 + 64 + i - 2) &amp; 5 + NumEntit\u00e9 &amp; \")\/\" &amp; Chr(4 + 64 + i - 2) &amp; 5 + NumEntit\u00e9\r\n        End Select\r\n      End With\r\n    End With\r\n  Next i\r\nEnd Sub\r\n \r\nSub Exploitation_REFECO()\r\n  NumEntit\u00e9 = 0\r\n  Set ClasseurAgr\u00e9gat = ActiveWorkbook\r\n  chemin = ThisWorkbook.Path &amp; \"\\\" &amp; NomDossierREFECO &amp; \"\\\"\r\n  Set ObjFSO = CreateObject(\"Scripting.FileSystemObject\")\r\n  Set ObjDossier = ObjFSO.GetFolder(chemin)\r\n  NbFichiers = ObjDossier.Files.Count\r\n  If NbFichiers &gt; 0 Then\r\n    For Each ObjFichier In ObjDossier.Files\r\n      If (InStr(1, ObjFichier.Name, ExtXLS, 1) &gt; 0) Then\r\n        NomClasseurREFECOEnCours = ObjFichier.Name\r\n        NumEntit\u00e9 = NumEntit\u00e9 + 1\r\n        TraitementREFECOEnCours\r\n      End If\r\n    Next\r\n    Totaux\r\n  End If\r\nEnd Sub<\/pre>\n<p style=\"text-align: justify;\">Les grands groupes disposent d\u2019outils permettant de centraliser l\u2019information de mani\u00e8re automatis\u00e9e. Par contre, les groupes de PME ne disposent g\u00e9n\u00e9ralement pas de ce genre d\u2019outils. L\u2019agr\u00e9gation des donn\u00e9es des filiales est fr\u00e9quemment effectu\u00e9e \u00e0 la main ou \u00e0 l\u2019aide de nombreuses formules liant des feuilles de calcul, g\u00e9n\u00e9rant des risques d\u2019erreurs notamment en cas de cr\u00e9ation de nouvelles filiales\u2026<\/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%2C%20VBA%20%3A%20Agr%C3%A9gation%20automatis%C3%A9e%20de%20donn%C3%A9es%20comptables%20de%20filiales%20au%20sein%20d%E2%80%99un%20groupe%20%28%C2%AB%20reporting%20%C2%BB%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>La remont\u00e9e des informations comptables (chiffre d\u2019affaires, r\u00e9sultats\u2026), financi\u00e8res, commerciales (carnet de commande, satisfaction client\u2026), etc. des filiales vers la holding s\u2019op\u00e8re g\u00e9n\u00e9ralement \u00e0 l\u2019aide de tableaux de bord mensuels standardis\u00e9s ; cette op\u00e9ration est commun\u00e9ment appel\u00e9e reporting. L\u2019agr\u00e9gation de toutes ces donn\u00e9es permet d\u2019effectuer des comparaisons (entre filiales, par rapport au pr\u00e9visionnel\u2026) et de &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=639\" class=\"more-link\">Continue reading &lsquo;EXCEL, VBA : Agr\u00e9gation automatis\u00e9e de donn\u00e9es comptables de filiales au sein d\u2019un groupe (\u00ab reporting \u00bb)&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%2C%20VBA%20%3A%20Agr%C3%A9gation%20automatis%C3%A9e%20de%20donn%C3%A9es%20comptables%20de%20filiales%20au%20sein%20d%E2%80%99un%20groupe%20%28%C2%AB%20reporting%20%C2%BB%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,40,544,215],"tags":[392,370,391,377,414,378,1631,249,393,98,372,179,382,383,374,380,1633,236,387,228,330,164,386,593,258,373,263,376,381,379,388,385,389,375,261,390,1641,384],"class_list":["post-639","post","type-post","status-publish","format-standard","hentry","category-cas-pratiques","category-controle-des-comptes","category-distribution-automobile-secteurs-dactivite","category-vba","tag-files-count","tag-formulalocal","tag-sheets","tag-agregation-de-donnees","tag-bi","tag-business-intelligence","tag-cas-pratiques","tag-cells","tag-chr","tag-code-source","tag-concessionnaire","tag-consolidation","tag-const","tag-dim","tag-distribution-automobile","tag-ecart-previsionnelrealisation","tag-excel","tag-for-each-in-next","tag-for-to-next","tag-function-end-function","tag-groupe","tag-informatique-decisionnelle","tag-integer","tag-intelligence-daffaires","tag-open","tag-peugeot","tag-range","tag-refeco","tag-remontee-dinformations","tag-reporting","tag-select-case","tag-string","tag-sub-end-sub","tag-tableau-de-bord","tag-value","tag-variant","tag-vba","tag-workbook"],"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\/639","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=639"}],"version-history":[{"count":2,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/639\/revisions"}],"predecessor-version":[{"id":5791,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/639\/revisions\/5791"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=639"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=639"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=639"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}