Editions ENI Livres | Vidéos | e-Formations
Consolidation et partage de données
Consolidation de données diverses : description de l’exemple
Présentation de l’exemple
En informatique, la consolidation permet de regrouper les données provenant de différentes sources afin d’obtenir un rapport structuré.
L’exemple qui va suivre va permettre de consolider dans un premier temps plusieurs sources de données en une seule puis de travailler sur le tableau consolidé afin d’en extraire les informations clés.
Il s’agit d’un groupe immobilier composé de deux agences distinctes qui sont situées à Paris et à Reims. Bien que semblable dans l’organisation, la saisie des opérations en cours ne se fait pas de la même manière pour les deux agences. Elles utilisent cependant toutes les deux un fichier Excel retraçant leur activité.
L’objectif sera donc de consolider ces deux sources de données en un seul fichier regroupant l’ensemble des informations. L’agence immobilière souhaite également proposer un tableau récapitulatif de son activité.
Cet exemple ne propose pas d’interface utilisateur. Le résultat se présente sous la forme d’une source de données consolidées.
Présentation des classeurs
Cet exemple se présente avec trois classeurs distincts :
-
Le classeur Immo-Paris.xlsx contient une feuille pour l’agence parisienne comportant les données suivantes :
|
Colonne Excel |
Libellé |
Valeur exemple |
|
Colonne A |
Date |
Date de la réalisation de l’opération. Elle sera stockée sous le format Timestamp qui est très répandu en informatique : il s’agit d’un compteur numérique correspondant au nombre de secondes écoulées depuis le 1er janvier 1970. Par exemple : 1er janvier 2022 : 1641016966 Un des avantages de ce format est la facilité d’effectuer la comparaison de date, puisque cela revient à faire une différence entre deux nombres |
|
Colonne B |
Agent |
Nom de l’agent immobilier s’occupant de la vente |
|
Colonne C |
Arrondissement |
Il s’agit des arrondissements si la commune en possède. |
|
Colonne D |
Commune |
Paris, Ivry-sur-Seine… |
|
Colonne E |
Code Postal |
75013, 94200 |
|
Colonne F |
Type de bien |
Maison, Appartement, Loft, Villa |
|
Colonne... |
Consolidation de données diverses : notions de cours
Manipulation de feuilles et classeurs
La manipulation de feuilles et classeurs consiste à manipuler des variables objets.
Gestion de l’application Excel
L’import va consister à ouvrir des classeurs Excel (Reims et Paris) puis à sélectionner les feuilles nécessaires. Toutes ces informations seront stockées dans des variables de type objet.
Dim ExcApp As Excel.Application 'Variable de gestion de l'application
Dim WB As Excel.Workbook 'Variable classeur
Dim WS As Excel.Worksheet 'Variable feuille de calcul La gestion de l’application n’est pas nécessaire si l’application est déjà ouverte. En revanche, le même mécanisme s’applique pour manipuler d’autres applications comme PowerPoint.
Sélection et ouverture d’un classeur Excel
Méthode GetOpenFileName
Pour la sélection du fichier Excel, il est possible d’utiliser la méthode GetOpenFileName de la classe Application qui permet l’ouverture d’une fenêtre de sélection de fichier. Cette méthode renvoie le chemin de l’application sélectionnée, et il peut être utilisé pour ouvrir le fichier sélectionné.
Nom_Fichier = Application.GetOpenFilename("Fichiers Excel (*.xlsm),
*.xlsm") 'filtre sur les fichiers Excel
If Nom_Fichier <> False Then
‘Ouverture fichier
Else
Msgbox ("Fichier non sélectionné")
End if Méthode FileDialog
Toutefois, l’exemple proposé utilisera une autre méthode plus complète Application.FileDialog qui permet tout type d’échange avec les répertoires et fichiers :
En fonction de l’argument associé à la méthode FileDialog, la boîte de dialogue prendra une forme différente :
-
Argument msoFileDialogFilePicker : sélection de fichier.
-
Argument msoFileDialogFolderPicker : sélection de dossier.
-
Argument msoFileDialogOpen : ouverture de fichier....
Consolidation de données diverses : réalisation de l’exemple
Ouvrez le fichier Enoncé_6-ABC.xlsm qui contiendra les données consolidées. Les fichiers Immo_Paris.xlsx et Immo_Reims.xlsx seront utilisés dans l’import mais ils ne seront pas ouverts dans l’exemple.
Structure du code
Le code va être réalisé au sein d’une seule et unique procédure qui sera utilisée une seule fois pour récupérer les données des deux feuilles.
Il faut donc créer une procédure qui stockera l’ensemble du traitement et sera nommée InsertionDonnees.
Insérez un module et saisissez les lignes de code suivantes :
Option Explicit
Sub InsertionDonnees
'Le code sera inséré ici
End sub Déclaration des variables feuille et classeur
Pour réaliser cet exemple, il va falloir dans un premier temps créer les variables de type objet pour stocker et manipuler les feuilles et classeurs.
Il sera nécessaire d’avoir six variables :
-
Trois variables de types Classeur :
-
Fichier Consolidé,
-
Fichier Reims,
-
Fichier Paris.
-
Trois variables de types Feuille :
-
Feuille Consolidée : au sein du fichier consolidé Enoncé_6-ABC.xlsx, feuille contenant les données consolidées.
-
Feuille Reims : au sein du fichier Immo_Reims.xlsx, feuille contenant les données du fichier Reims.
-
Feuille Paris : au sein du fichier Immo_Paris.xlsx, feuille contenant les données du fichier Paris.
Déclarez les variables de la manière suivante :
'Définition des variables
Dim WBReims As Excel.Workbook
Dim WBParis As Excel.Workbook
Dim WBFinal As Excel.Workbook
Dim WSReims As Excel.Worksheet
Dim WSParis As Excel.Worksheet
Dim WSFinal As Excel.Worksheet La déclaration de variables peut être regroupée en une seule ligne par type de variable. Les noms de variables doivent être séparés par une virgule :
Dim WBFinal, WBReims, WBParis As Excel.Workbook
Dim WSFinal, WSReims, WSParis As Excel.Worksheet Pour affecter des valeurs aux variables, il est nécessaire d’utiliser le mot clé Set qui permet d’assigner une référence à l’objet. Dans ce cas, WBFinal aura pour référence...
Partage des données : description de l’exemple
Présentation de l’exemple
L’objectif de cet exemple est de proposer une solution permettant aux deux agences immobilières la saisie des données. La problématique est que ce fichier Excel n’a pas vocation à être maintenu par une seule agence, mais il doit être accessible et modifiable par les deux agences et peut-être à terme, par une multitude d’agences.
Présentation des classeurs et outils utilisés
Dans le cadre de cet exemple, le fichier généré dans la première partie de ce chapitre sera utilisé puisqu’il contient les données saisies dans les agences. Toutefois, le fichier a subi quelques améliorations avec notamment un onglet Paramètres. Le fichier sur lequel nous allons nous baser est le fichier Enoncé_6-DEF.xlsm.
Cet exemple va requérir un compte sur Microsoft OneDrive (https://onedrive.live.com/) et dans le cadre de la dernière partie, il est nécessaire d’avoir Outlook 2021, Outlook Microsoft 365 installé sur votre poste. Si vous ne possédez pas Outlook, le code peut être facilement adapté pour d’autres solutions.
Le compte OneDrive permettra de créer un formulaire Excel en ligne dont les valeurs seront stockées au sein d’un fichier.
L’application...
Partage des données : notions de cours
Formulaire de tableau
L’option Formulaire est une fonctionnalité d’Excel permettant d’ajouter/modifier/supprimer des données dans une série de données. Généralement utilisée avec des tableaux, cette fonctionnalité peut être utilisée également avec une simple série de données.
Avantages
L’avantage de cette fonctionnalité est de générer un formulaire de saisie et de modification de manière simple, juste via un clic. L’édition, l’ajout et la suppression sont simples d’accès et il est même possible de rechercher un élément.
Inconvénients
L’inconvénient majeur est le manque de possibilités d’aide à la saisie.
Il est impossible de qualifier la donnée à insérer. De plus, si vous avez appliqué des contraintes sur les données (onglet Données - Validation des données), vous risquez de ne pas pouvoir insérer vos données avec le formulaire. En effet, si la valeur saisie ne répond pas à la valeur attendue, l’ensemble de la ligne ne sera pas inséré.
Comment insérer le formulaire ?
Ouvrez le fichier ExempleCours_Chapitre_6.xlsx.
Vous trouverez sur la feuille deux tableaux identiques en A1:C9 et H1:J9. Chaque tableau comporte trois colonnes listant les prénoms, le sexe et le nom de l’équipe.
Le premier tableau sur la plageA1:C9 est une plage de données, non déclarée en tant que tableau Excel. Aucune case ne porte de contrainte. Le second tableau sur la plage H1:J9, est un tableau Excel. Les colonnes concernant le sexe et le nom de l’équipe sont obligatoires : l’utilisateur doit choisir une des valeurs.
Dans l’onglet Fichier, choisissez Options.
Cliquez sur Personnaliser le ruban, puis dans la zone Choisir les commandes dans les catégories suivantes, sélectionnez Toutes les commandes.
Dans la liste de gauche, sélectionnez Formulaire.
Dans la liste de droite, sélectionnez Données puis cliquez sur Nouveau groupe.
Cliquez sur Ajouter>> pour insérer le bouton Formulaire dans ce nouveau groupe de l’onglet...
Partage des données : réalisation de l’exemple
Commencez par ouvrir le fichier Enoncé_6-DEF.xlsm.
Créer un formulaire de saisie automatique pour faciliter la saisie des données
Le formulaire de saisie va permettre la saisie de données sur un tableau sans avoir à utiliser un formulaire VBA.
Afficher le formulaire
Si vous n’avez pas ajouté le bouton Formulaire, ajoutez-le à l’onglet Données (référez-vous à la section Formulaire de tableau dans la section Partage des données : notions de cours de ce chapitre).
Sélectionnez la cellule A1.
Dans l’onglet Données, cliquez sur le bouton Formulaire.
Le formulaire apparaît ainsi :
Modifier une donnée
L’agent Benoist vous informe qu’une vente n’a finalement pas eu lieu suite à un problème de dernière minute sur l’attribution du prêt. Il sait que l’offre est parue le 14/12/2021.
Cliquez sur Critères puis saisissez 14/12/2021 dans la zone Date de parution de l’offre.
Appuyez sur la touche Entrée, puis cliquez sur les boutons Précédente et Suivante pour accéder à l’opération effectuée par l’agent Benoist le 14/12/2021.
Modifiez la valeur du champOpération réussie de VRAI en FAUX puis cliquez sur Fermer.
Vérifiez la donnée avec la valeur de la cellule P1131 : elle est bien passée à FAUX.
Rechercher une donnée
L’agent Cruzel recherche une de ses ventes sur laquelle s’est glissée une erreur : le client avait un parking. Il se souvient que c’était la vente d’une villa d’environ 250 000 €. Utilisez l’outil pour effectuer la recherche
Affichez à nouveau le formulaire en cliquant sur le bouton Formulaire dans l’onglet Données. Le formulaire apparaît.
Cliquez sur le bouton Critères.
Saisissez les informations qui permettent de trouver ces deux lignes :
Après avoir vérifié que vous avez bien trouvé l’enregistrement, changez la valeur du champ Avec parking de FAUX à VRAI puis cliquez sur le bouton Fermer.
