EXCEL, VBA : Agrégation automatisée de données comptables de filiales au sein d’un groupe (« reporting »)

Publié le 15 juillet 2011 par Benoît-René RIVIERE

La remontée des informations comptables (chiffre d’affaires, résultats…), financières, commerciales (carnet de commande, satisfaction client…), etc. des filiales vers la holding s’opère généralement à l’aide de tableaux de bord mensuels standardisés ; cette opération est communément appelée reporting. L’agrégation de toutes ces données permet d’effectuer des comparaisons (entre filiales, par rapport au prévisionnel…) et de prendre des mesures correctrices le cas échéant. L’informatique décisionnelle revêt ici tout son sens.

Parmi les solutions les plus pertinentes, celle du VBA offre de sérieux atouts :

- Rapidité d’exécution : quelques secondes suffisent pour récupérer des données disséminées dans des dizaines de feuilles de calcul,

- Flexibilité : en cas de changement dans la présentation 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…

- Sécurité : lors de l’entrée d’une nouvelle filiale dans le groupe, elle est intégrée automatiquement dans le processus d’agrégation de données sans qu’il soit nécessaire de modifier quelque ligne de code que ce soit.

Exemple : remontée des tableaux de bord d’un groupe de distributeurs automobiles PEUGEOT

Afin d’illustrer notre propos, nous allons prendre le cas d’un groupe de distributeurs automobiles. Tous les mois, les concessionnaires doivent produire au constructeur, Peugeot, un tableau de bord standardisé « REFECO ». Ce tableau de bord (diffusé sous forme de fichier EXCEL) mentionne différentes informations commerciales par activité (vente de véhicules neufs (VN), de véhicules d’occasion (VO), activité atelier (APV)…).

Un groupe réunissant en son sein plusieurs concessionnaires de marque PEUGEOT pourra souhaiter « consolider » ces tableaux. C’est précisément cette « consolidation » que réalise l’application que je vous propose ci-après.

REFECO - Activité VN

Ce code source, écrit en VBA, reprend le chiffre d’affaires de l’activité VN (en K€ et en quantité) du tableau de bord de chaque société et effectue quelques calculs (comparaison N/N-1, prix de vente moyen au VN…).

La « consolidation » de l’activité VN apparaît dans le tableau reproduit ci-après :

Code source de l’application :

‘CentralREFECO

‘=> Agrégation des données des REFECO dans un tableau « groupe »

‘

‘Programmé par Benoît-René RIVIERE, www.auditsi.eu, benoit@auditsi.eu (06/2011)

Option Explicit

‘Déclaration des constantes & variables

‘—Fichiers

Const ExtXLS = « xls »

Const NomDossierREFECO = « REFECO »

‘—Général

Const NomOngletGal = « 00a »

Const AdresseNomEntité = « C4″

‘—Activité VN

Const NomOngletVN = « 10a »

Const AdresseQtéVN_N = « K11″

Const AdresseQtéVN_N1 = « P11″

Const AdresseCAVN_N = « K13″

Const AdresseCAVN_N1 = « P13″

‘Déclaration des variables

‘—Chemin

Dim chemin As String

‘—Objets dossier et fichier

Dim ObjFSO, ObjDossier, ObjFichier

‘—Classeurs & onglets

Dim NomClasseurREFECOEnCours As String

Dim ClasseurREFECO As Workbook

Dim ClasseurAgrégat As Workbook

Dim NomEntité As String

Dim NumEntité As Integer

Dim NbFichiers As Integer

Function FormatCellule(ColCell As Integer)

Select Case ColCell

Case 1, 2, 4, 5, 7, 8

FormatCellule = « # ### »

Case 3, 6, 9

FormatCellule = « #.00% »

End Select

End Function

Sub TraitementREFECOEnCours()

Dim ligneencours As Integer

Dim i As Integer

Dim s As String

Dim c As Variant

Dim c1 As Variant

‘Ouverture du REFECO

Set ClasseurREFECO = Workbooks.Open(chemin & NomClasseurREFECOEnCours)

‘Agrégation des données

ligneencours = 3

With ClasseurAgrégat.Sheets(1)

If NumEntité = 1 Then

.Range(« A1:L100″).ClearContents

For i = 1 To 9

Select Case i

Case 1

s = « CA VN N »

Case 2

s = « CA VN N-1″

Case 3

s = « VAR° CA VN % »

Case 4

s = « Qté VN N »

Case 5

s = « Qté VN N-1″

Case 6

s = « VAR° Qté VN % »

Case 7

s = « CA moy au VN N »

Case 8

s = « CA moy au VN N-1″

Case 9

s = « VAR° CA moy »

End Select

.Cells(ligneencours, 4 + i – 1).Value = s

Next i

End If

ligneencours = ligneencours + NumEntité

NomEntité = ClasseurREFECO.Sheets(NomOngletGal).Range(AdresseNomEntité).Value

.Cells(ligneencours, 1).Value = NomEntité

For i = 1 To 9

Select Case i

Case 1

c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseCAVN_N).Value

Case 2

c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseCAVN_N1).Value

Case 3, 6, 9

c1 = .Cells(ligneencours, 4 + i – 1 – 1).Value

If c1 <> 0 Then

c = (.Cells(ligneencours, 4 + i – 1 – 2).Value – c1) / c1

Else: c = 0

End If

Case 4

c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseQtéVN_N).Value

Case 5

c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseQtéVN_N1).Value

Case 7, 8

c1 = .Cells(ligneencours, 4 + i – 1 – 3).Value

If c1 <> 0 Then

c = .Cells(ligneencours, 4 + i – 1 – 6).Value / c1 * 1000

Else: c = 0

End If

End Select

With .Cells(ligneencours, 4 + i – 1)

.NumberFormat = FormatCellule(i)

.Value = c

End With

Next i

End With

‘Fermeture du REFECO

ClasseurREFECO.Close SaveChanges:=False

End Sub

Sub Totaux()

Dim i As Integer

For i = 1 To 9

With ClasseurAgrégat.Sheets(1)

If i = 1 Then .Cells(5 + NumEntité, 1).Value = « TOTAUX »

With .Cells(5 + NumEntité, 4 + i – 1)

.NumberFormat = FormatCellule(i)

Select Case i

Case 1, 2, 4, 5, 7, 8

.FormulaLocal = « =somme( » & Chr(4 + 64 + i – 1) & « 4: » & Chr(4 + 64 + i – 1) & 4 + NumEntité – 1 & « ) »

Case 3, 6, 9

.FormulaLocal = « =( » & Chr(4 + 64 + i – 3) & 5 + NumEntité & « - » & Chr(4 + 64 + i – 2) & 5 + NumEntité & « )/ » & Chr(4 + 64 + i – 2) & 5 + NumEntité

End Select

End With

Next i

End Sub

Sub Exploitation_REFECO()

NumEntité = 0

Set ClasseurAgrégat = ActiveWorkbook

chemin = ThisWorkbook.Path & « \ » & NomDossierREFECO & « \ »

Set ObjFSO = CreateObject(« Scripting.FileSystemObject »)

Set ObjDossier = ObjFSO.GetFolder(chemin)

NbFichiers = ObjDossier.Files.Count

If NbFichiers > 0 Then

For Each ObjFichier In ObjDossier.Files

If (InStr(1, ObjFichier.Name, ExtXLS, 1) > 0) Then

NomClasseurREFECOEnCours = ObjFichier.Name

NumEntité = NumEntité + 1

TraitementREFECOEnCours

End If

Next

Totaux

End If

End Sub

Les grands groupes disposent d’outils permettant de centraliser l’information de manière automatisée. Par contre, les groupes de PME ne disposent généralement pas de ce genre d’outils. L’agrégation des données des filiales est fréquemment effectuée à la main ou à l’aide de nombreuses formules liant des feuilles de calcul, générant des risques d’erreurs notamment en cas de création de nouvelles filiales…

	*CA VN N en K€*	*CA VN N-1 en K€*	*VAR° CA VN %*	*Qté VN N*	*Qté VN N-1*	*VAR° Qté VN %*	*CA moy au VN N en €*	*CA moy au VN N-1 en €*	*VAR° CA moy*
Concession n°1	35 731	30 402	17,53%	2 560	2 272	12,68%	13 957	13 381	4,30%
Concession n°2	13 120	12 013	9,21%	885	834	6,12%	14 825	14 405	2,92%
Concession n°3	6 180	6 543	-5,55%	435	458	-5,02%	14 207	14 286	-,55%
Concession n°4	15 644	16 667	-6,14%	1 081	1 175	-8,00%	14 472	14 185	2,02%
Concession n°5	7 077	6 475	9,29%	480	465	3,23%	14 743	13 925	5,87%
Concession n°6	7 264	7 154	1,54%	492	503	-2,19%	14 765	14 222	3,81%
Concession n°7			-100,00%			,00%			,00%
Concession n°8	11 665	9 687	20,41%	784	674	16,32%	14 879	14 373	3,52%
Concession n°9			,00%			,00%			,00%
Concession n°10	11 590	9 278	24,91%	818	670	22,09%	14 168	13 848	2,31%

TOTAUX	108 270	98 220	10,23%	7 535	7 051	6,86%	116 015	112 625	3,01%