La première fois que j’ai compris comment connecter Excel à une base de données, j’ai eu l’impression qu’un nouvel univers s’ouvrait à moi. J’avais l’excitation d’un enfant le matin de Noël.
Avec du recul, je trouve cette engouement totalement mérité. C’est une des connaissances qui m’a fait gagner le plus de temps dans mon quotidien de contrôleur de gestion sur Microsoft Excel. Par bien des aspects, ça surpasse la création d’une macro.
Si vous êtes soucieux(se) d’optimiser votre temps sur votre tableur préféré et d’automatiser vos travaux, je vais vous expliquer dans cet article comment connecter Excel à une base de données.
C’est quoi une base de données ?
C’est l’entreprôt de votre logiciel. Question suivante ?
Je rigole. 😁 Je vais rentrer un peu plus dans le sujet pour que vous puissiez avoir un minimum de connaissances.
Une base de données est le lieu de stockage des informations saisies dans votre logiciel. Votre logiciel est constitué d’un applicatif qui est l’interface graphique avec l’utilisateur et d’une base de données. L’applicatif aura pour objectif de rendre la navigation intuitive et de mettre des contrôles sur les données que vous allez saisir. L’applicatif est le videur de la boite de nuit base de données.
Votre base de données peut être stockées sur un serveur interne de votre entreprise, dans le cloud (serveur externe), sur votre poste (très rare). Nous nous concentrerons ici sur les bases de données stockées dans un serveur de votre entreprise.
Votre base de données est segmentée en tables. Une table est un tableau où chaque colonne a un format bien précis. Les tables sont séparées de façon logique. Pour un logiciel de paie, on trouvera une table avec les matricules et l’identité des salariés, une table pour les comptes bancaires des salariés, une table pour les différents services de l’entreprise,…
Les tables sont connectées entre elles par des clés. Si nous reprenons l’exemple de notre logiciel de paie, les tables affectation des salariés par service et services seront reliées par l’identifiant du service.
La base de données peut être composée de vues. Une vue est un agglomérat synthétique de plusieurs tables sur lesquels nous avons mis des conditions de sélection et de filtre. Les données ne sont pas stockées dans les vues mais uniquement dans les tables. Une vue est un requête d’une ou plusieurs tables.
Je vous explique tout ça parce que généralement les tables sont peu intéressantes seules. Le plus intéressant est d’effectuer des requêtes plus ou moins complexes pour récupérer des données facilement intelligibles.
Pourquoi connecter Excel à une base de données ?
La majorité du temps, pour alimenter nos reportings, nous effectuons des exports… beaucoup d’exports. Nous avons même créé de belles procédures pour expliquer où se connecter, quoi saisir dans les champs, quelles dates indiquer.
Ces exports proviennent de nos logiciels préférés. Pour ma part, il s’agissait de logiciels comptables comme SAGE / CEGID, d’outils de suivi des temps, de logiciels de paie ou d’ERP aux noms exotiques.
Ce travail est chronophage et sans grande valeur ajoutée. Il est également source d’erreurs. Lors de la mise en place d’un développement spécifique, les exports peuvent être modifiés. Avec nos difficultés à prendre de la hauteur, nous ne nous rendons pas compte des impacts des modifications sur nos tableaux et graphiques.
Connecter Excel à notre base de données offre plusieurs avantages.
Le premier est le gain de temps important une fois la connexion établie. Vos reportings Excel seront actualisables par un simple clic.
La qualité de votre travail augmentera. Jusqu’à présent, les exports étaient source d’erreurs. Il ne suffit pas de grand chose pour que vous saisissiez le mauvais choix dans la liste déroulante de votre logiciel.
La taille de vos fichiers réduira ayant pour intérêt d’accélérer Microsoft Excel. Si vous mettez dans Excel de nombreux exports et que vous créez tableaux, calculs, graphiques et tableaux croisés dynamiques, vous pouvez vous rendre compte que la puissance d’Excel (et de votre ordinateur) provoque des lenteurs. Passer par une connexion permettra d’alléger tout ça.
Le dernier point identifié est à double tranchant. Mettre en place des connexions Excel à des bases de données m’a permis d’augmenter significativement ma connaissance du fonctionnement interne du logiciel. Par contre, ça requiert du temps. Avec le recul, je suis content d’avoir mobilisé ce temps. Je comprenais davantage les impacts des développements demandés aux intégrateurs de nos solutions logiciels. C’est donc, selon moi, un bon point.
Maintenant que je vous ai présenté les avantages de connecter Excel à une base de données, je vous montre comment ça fonctionne.
Comment connecter Excel à une base de données ?
Pour connecter Excel à une base de données, tout se passera dans la section Données de votre ruban supérieur.
Etape 1 – Connecter Excel au serveur
Ouvrez le fichier Excel sur lequel vous voulez connecter votre base de données. Ensuite, rendez-vous dans l’onglet Données puis Obtenir des données. Dans notre exemple, nous allons récupérer une base de données du type SQL Server.
De mon expérience, les bases SQL Server sont parmis les plus courantes en entreprise.
Une fenêtre va s’ouvrir vous invitant à indiquer le nom du serveur ainsi que la base de données sur laquelle se connecter. Pour cette étape, n’hésitez pas à demander de l’aide auprès d’un membre du service informatique. Ils installent généralement les bases de données sur des serveurs dédiés à ce type d’usage.
Comme expliqué en préambule, ce tutoriel suppose que votre base de données est stockée sur les serveurs internes à l’entreprise. En mode cloud, la connexion est moins évidente puisque, pour des raisons de sécurité, les accès sont très restreints.
Dans la fenêtre suivante, on vous demandera vos informations d’identification. Cela va dépendre de la configuration du serveur. Généralement, vos identifiants classiques suffiront. Si ce n’est pas le cas, j’espère que vous avez gardé votre homologue informatique à vos côtés parce qu’il vous sera d’un grand secours.
Etape 2 – Récupérer les tables et/ou vues souhaitées
A présent, vous devriez voir s’afficher la liste des tables et des vues. Si vous n’avez jamais vu des tables ou des vues par le passé, leur structure et l’organisation des données vous laissera perplexe.
Sélectionnez les tables et/ou vues qui vous semblent intéressantes. Vous aurez le temps de les manipuler et de les étudier plus tard. Dès que vous êtes prêt(e), cliquez sur Modifier.
Etape 3 – Modifier et lier vos tables dans Power Query
Nous sommes à présent dans Power Query. C’est le super moteur de traitement des données d’Excel. Ce module est également présent dans d’autres solutions de la suite Office comme Power BI.
A partir de cet écran, vous avez la possibilité de supprimer des colonnes, d’en ajouter, d’effectuer des traitements comme la suppression des doublons ou le fractionnement de colonnes. Pour les plus aventureux, vous pourrez lier les tables entre elles et obtenir des états plus synthétiques.
C’est dans cet écran que j’ai pu passer des heures à analyser la structure des données des logiciels métiers afin de comprendre comment ils organisaient l’information.
Une fois que vous avez opéré les modifications souhaitées, cliquez sur Fermer et charger.
Etape 4 – Charger vos données dans Excel.
Vous voilà avec vos données dans votre onglet Excel. Vous pouvez les manipuler comme vous le faites habituellement.
Je vous conseille d’éviter d’ajouter des colonnes dans votre onglet mais de vous habituer à le faire directement dans Power Query. La puissance de calcul de votre ordinateur vous remerciera.
Le plus beau c’est que si vous construisez votre reporting uniquement sur ce type d’export Power Query, vous pourrez l’actualiser avec un seul clic. En effet, pour mettre à jour les sources de données, rendez vous dans Données et cliquez sur Actualiser tout. Tous vos exports ont été mis à jour.
Un gain de temps énorme dans votre quotidien ! 🤩
Et après ?
A présent, vous avez la possibilité de construire l’ensemble de vos reportings avec des requêtes Power Query.
Je vous conseille notamment de stocker dans des fichiers Excel bien définis vos données référentielles. Il s’agit des données qui alimentent plusieurs reportings. Par exemple, il s’agira du plan analytique avec les différentes sections, de la liste du personnel ou des nouveaux entrants, de la liste des machines,…
Plutôt que de maintenir les listes dans chaque fichier, vous aurez un seul fichier à maintenir qui deviendra la référence pour le service. Avec Power Query, vous pourrez lier vos reportings à ce fichier référentiel.
Conclusion
A présent, vous avez toutes les cartes en main pour connecter Excel à une base de données. Les données extraites de vos logiciels seront peut être peu claires dans un premier temps. Laissez-vous du temps. Mobilisez les ressources pour comprendre comment votre logiciel et sa base de données fonctionnent. Cette nouvelle connaissance vous permettra d’augmenter votre niveau de compétence sur les flux transitant dans le système d’information.
