Excel : 5 trucs pour éviter les problèmes de fichiers liés

Un client a récemment posé la question suivante : “Dans un contexte où plusieurs départements travaillent sur un budget, nous avons séparé notre modèle en plusieurs fichiers différents… Avec les difficultés que ça implique, i.e. formules plus compliquées et liaisons entre les fichiers à entretenir. Est-ce que d’autres fonctionnent autrement?“. Certes, les problèmes de fichiers Excel liés surviennent surtout dans les entreprises qui ont un processus budgétaire décentralisé.

La galère des fichiers Excel liés

Bien qu’Excel puisse toujours fournir une réponse à tous les problèmes (de par sa nature d’extrême flexibilité), le concept de gestion de fichiers liés constitue sans nul doute son talon d’Achille. C’est d’ailleurs cette faiblesse qui incite plusieurs à se tourner vers des “systèmes” de budgétisation/prévision. Ces derniers offrent des interfaces collaboratives et éliminent les problématiques de fichiers indépendants. Mais ces systèmes, souvent coûteux, sont-ils réellement la solution ultime à tout problème de fichiers liés? Cet article vise à proposer des alternatives simples et à très faible budget, mais souvent méconnues des utilisateurs d’Excel.

Quand les utilisateurs d’Excel créent-ils des fichiers liés?

En théorie, il ne faudrait JAMAIS diviser un modèle Excel en de multiples fichiers, sauf si :

• Vous êtes plus d’un usager à devoir utiliser le modèle en même temps et que l’option de travail collaborative incluse dans Excel ne suffit plus
• Différentes parties du modèle doivent être envoyées à différents récipiendaires
• Votre modèle est trop volumineux

À quoi s’expose les créateurs de fichiers liés?

La gestion de fichiers liés dans Excel expose les utilisateurs à plusieurs problèmes potentiels :

• Pour consulter les données à jour, dans un fichier qui s’alimente à un autre fichier, les deux fichiers doivent être ouverts
• Quand un utilisateur modifie le fichier source, notamment quand il supprime ou modifie des plages de données, sans ouvrir le modèle (le fichier Excel qui s’y alimente), l’utilisateur du modèle se retrouvera souvent soit avec des formules qui ne sont plus tout à fait justes (référence aux mauvaises plages de données) ou qui retournent carrément des message d’erreurs #REF! (référence à des plages de données qui ont été supprimées)
• Si le nom des feuilles et des fichiers sont le moindrement longs, ceci alourdira considérablement les formules et donc le poids du modèle, augmentant ainsi la possibilité de rencontrer des problèmes de performance

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Travailler adéquatement avec les fichiers Excel externes

Quand les différents fichiers ont été divisés pour permettre aux différents usagers de mettre à jour différentes sections et que l’option de travail collaboratif incluse dans Excel n’a pas été retenue, voici les alternatives à mettre en place. Parmi elles, les deux dernières sont certainement les plus intéressantes, surtout la toute dernière.

1. Utiliser les formules 3D pour éliminer les fichiers liés

Chaque responsable de budget pourrait remplir son propre budget dans un fichier Excel type indépendant, qui serait identique pour chaque responsable. On pourrait ensuite simplement copier le contenu de ces pages, entre des bornes de début et de fin, d’un fichier de consolidation, comprenant des formules 3D, qui s’automatiseraient donc au fur et à mesure qu’on ajouterait ces pages. Bien sûr, ceci ne serait pas une option “dynamique”. Il faudrait donc compenser en créant une page de documentation, gardant en mémoire les dates de chaque dernière version de ces pages ainsi copiées. Pour que ce processus soit efficace, un certain contrôle serait donc nécessaire. Si vous pouvez éliminer ce travail “manuel”, ce sera toujours à privilégier. Il faudrait alors choisir une autre des options présentées ci-dessous.

2. Utiliser les champs nommés dans vos fichiers sources

Utilisez les “champs nommés” pour les champs du fichier source qui sont utilisés dans le modèle. Cette option, bien que simple à mettre en place, entraîne des avantages et des inconvénients. Si bien utilisée, elle permettra de bien préserver les formules liées, mais si les utilisateurs se mettent à copier les informations d’une feuille Excel avec champs nommés dans d’autres fichiers Excel, ils pourraient ainsi se mettre à créer des liaisons entre des fichiers qui ne devraient pas être liés. Il faut donc être extra vigilants avec cette option. Si vous pouvez prioriser une autre des options suivantes, ce sera toujours préférable de le faire.

3. Utiliser des feuilles “miroir” dans vos fichiers liés

Créer une une feuille “Importation de données” dans le modèle et une feuille “Exportation de données” dans le fichier source. Chacune de ces feuilles doit être un miroir l’une de l’autre. Ainsi, au lieu de créer une formule dans le modèle, qui se réfère à des cellules d’un fichier externe, on créera plutôt des formules liées aux cellules de la feuille importation du même fichier (donc lien interne). Seule la feuille importation aura des liens vers le fichier source. Ainsi, dans la cellule F10 de la feuille 1 du classeur 1, on retrouvera une formule qui fera référence au contenu de la cellule F10 de la feuille 1 du classeur 2.  Ceci facilitera largement la gestion et la mise à jour de tels fichiers.

Exemple de création de feuilles miroir

Par exemple, ici, nous avons les données du fichier d’origine (classeur 2) :

Et ici, nous avons notre feuille d’importation de notre modèle (classeur 1). Remarquez que dans la cellule C8, on a simplement une formule = qui réfère exactement à la même cellule, soit C8 mais du deuxième classeur.

Pour remplir l’onglet du modèle qui sert à calculer les ventes du gestionnaire A, il faudrait éviter de créer une formule qui réfère directement au fichier source et cela pour toutes les raisons mentionnées plus haut. Notez ici que plus le nom du classeur avec les données sources et le nom des onglets de ce classeurs seront longs, plus la formule sera lourde, autant en termes de poids que de compréhension.

Pour remplir cette section, il faudrait plutôt simplement faire référence à un calcul dans l’onglet Importation.

En plus d’alléger les formules, si jamais une personne supprimait ou modifiait des cellules dans le classeur 2, sans ouvrir le classeur 1, elle serait sans doute en mesure de voir les erreurs créées dans l’onglet Exportation et les corriger avant d’enregistrer son fichier. Ainsi, à l’ouverture du classeur 1,  les formules continueraient de rapporter les bonnes valeurs.

4. Utiliser la programmation VBA pour remplacer les fichiers liés

Il serait également possible de programmer une macro VBA qui permettrait, à chaque fois qu’un usager cliquerait sur un bouton, d’importer les données du ou des fichiers sources dans le modèle et cela, en valeurs (donc pas de problème de performance lié à des formules trop gourmandes). Il faut simplement trouver quelqu’un dans votre entreprise qui sait programmer en VBA! Mais si c’est possible, c’est une solution plutôt efficace!

5. Utiliser Power Query (notre option privilégiée… et de loin!) pour remplacer les fichiers liés

L’option ultime demeure pour moi, l’utilisation de Power Query, qui permet à tous les utilisateurs le moindrement dégourdis dans Excel, d’importer les données de fichiers externes via des requêtes qui n’auront qu’à être actualisées pour aller chercher les valeurs les plus récentes. Contrairement au langage VBA, de telles requêtes sont plutôt simples à rédiger puisque, même si  Power Query repose sur un langage de programmation, le langage M,  il a été conçu par Microsoft dans une optique de solution “low code“. Autrement dit, les utilisateurs sont en mesure de créer des requêtes, sans avoir à apprendre à coder. Ils peuvent simplement cliquer sur différents boutons et menus pour construire leurs requêtes et cela génèrera automatiquement le code pour eux. N’est-ce pas génial?

Exemple d’utilisation de Power Query

Importer les données du fichier plutôt que de s’y lier

Par exemple, ici, il s’agira d’abord, depuis le classeur 1, d’importer les données du classeur 2.

Ensuite, il faudra choisir l’onglet du classeur 2 que l’on désire importer dans le classeur 1.

Créer une requête de transformation

Finalement, en cliquant sur plusieurs boutons et fonctionnalités, on se trouvera à transformer les données en table bien structurée et ces étapes de transformation seront enregistrées à travers les étapes appliquées (panneau de droite). Cela signifie qu’à l’actualisation, la requête repassera à travers toutes les étapes de transformation et s’il y a donc de nouvelles données ou des données modifiées dans le classeur 2, le tout sera intégré aisément au classeur 1.

Récupérer les données importées dans un onglet

Il sera ensuite possible de récupérer les données dans un tableau Excel, de créer un tableau croisé dynamique ou un graphique croisé dynamique basé sur ces données ou encore d’envoyer ces données dans Power Pivot pour créer un cube virtuel en mettant en relation cette table avec d’autres tables.

Créer une requête alternative pour arriver au même résultat que précédemment

Normalement, avec Power Query, on cherche à reconstituer des bases de données avec les données “non calculées”. On utilise plutôt les mesures en DAX, dans Power Pivot, ou les tableaux croisés dynamiques, pour faire les calculs. Mais au besoin, il serait aussi possible, dans Power Query, d’obtenir un tableau avec les ventes, plutôt qu’un tableau avec les prix et les nombres d’unités. Ceci nous ramènerait réellement au résultat visé dans notre exemple.

Bien comprendre l’avantage des données importées par rapport aux données obtenues par liaison

Ce qu’il faut surtout comprendre ici, c’est que l’utilisateur de Power Query ne travaille plus avec des données LIÉES mais plutôt avec des données IMPORTÉES, qui sont actualisées sur demande. Ce faisant, on élimine complètement les problématiques des fichiers liés. De même, on améliore au passage largement le poids du fichier, qui ne requiert plus de formules complexes et qui compresse les données.

Plus aucune raison de ne pas utiliser Power Query

Power Query est désormais disponible dans la grande majorité des versions Excel gratuitement. Il faut ajouter un complément pour les version 2010 et 2013 mais il est disponible dans toutes les autres versions ultérieures. Power Query a complètement changé la façon de travailler avec Excel, optimisant le temps requis pour effectuer des tâches manuelles mais en réglant aussi des problématiques de longue date, comme la problématique des fichiers liés!

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Afin d’approfondir vos connaissances avec Excel, particulièrement les tâches qui touchent à l’importation, la consolidation et la transformation de données, nous vous recommandons notre formation Excel – Introduction à Power Query et au langage M.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.