Optimiser vos fichiers Excel pour gagner en efficacité !
Pour optimiser un fichier Excel, il faut respecter des règles mais surtout il faut comprendre comment fonctionne l’application pour l’utiliser efficacement.
Pour commencer, je vais prendre un exemple “simple” rencontré chez un client.
Dans un onglet d’un fichier Excel, on procède à la récupération d’une balance comptable (50 000 lignes), dans l’onglet suivant on récupère le plan de compte avec les agrégats de comptes (par exemple le compte 706000 va dans la rubrique “prestations de services”. Puis on procède via des fonctions RechercheV à l’ajout de colonnes dans l’onglet de la balance, pour ajouter des informations telles que les libellés des comptes, les rubriques… ceci conduit à plus de 200 000 fonctions RechercheV…. et un temps de calcul de +/-30 secondes. En choisissant une fonction plus efficace, on est arrivé à un temps de calcul proche du temps réel… !
Ceci illustre de manière simple que le choix, notamment d’une formule, peut impacter de manière forte les temps de calculs et donc le confort d’utilisation d’un modèle Excel. Cet article vise a expliquer en quelques minutes les points à connaître pour utiliser Excel le plus efficacement.
Le choix de l’extension
En simplifiant, depuis la version 2010, Excel offre de nombreux format de fichier. Voici une rapide comparaison entre les 4 principaux formats de fichiers utilisés :
Quand on lit ce tableau, on a tendance à imaginer que le format xlsb est le plus performant… oui mais si vous devez par exemple utiliser un fichier Excel avec Power Query ou Power BI. Je vous conseil vivement de prendre un format xml donc un fichier xlsx ou xlsm. Ces derniers offrirons de meilleurs temps de réponses si vous les utilisés avec les Power Query et Power BI.
Structurer les onglets du classeur
L’ordre des onglets joue un rôle important dans la manière dont Excel traite l’information. L’application va commencer par calculer la première feuille et ses antécédents (c’est-à-dire les feuilles permettant le calcul de celle-ci), la seconde et ainsi de suite. Il est donc important, dans le cas de récupération de données externes pour produire un tableau de bord, que la première feuille se consacre à la récupération. La seconde, au tableau de bord.
Ajouter un onglet de présentation expliquant les autres onglets. Ce dernier devant servir à expliquer brièvement l’objectif, le fonctionnement et l’organisation du fichier. Ceci permet de garantir une plus simple appropriation par une personne n’ayant pas participé à la création du fichier Excel.
Employer un code couleurs simple pour les onglets. Celui-ci doit pour permettre de comprendre le rôle de chacun.
Vous avez des onglets est masqués. Posez-vous la question de son utilité dans la structure des calculs. LE cas échéant, supprimer le.
Organiser l’information à l’intérieur d’un onglet
Au niveau d’un onglet, Excel va traiter l’information dans un certain ordre. Ainsi si vous avez des saisies impactant des calculs, indiquer ces dernières dans les premières lignes et colonnes de la feuille puis placer les calculs en dessous. En effet, Excel calcule de gauche à droite puis de bas en haut.
Utiliser un code couleurs simple et explicite permettant visuellement de comprendre quelles cellules attendent des informations de la part des utilisateurs et quelles cellules contiennent des calculs (non modifiables).
Verrouiller les cellules. Ceci permet notamment de garantir la structure algorithmique en place et limite la saisie des utilisateurs aux zones prévues. Mais éviter de masquer les formules… ceci fait souvent “effet boîte noire”.
Nommer les cellules et les plages de cellules devant être utiliser par des formules au sein de l’onglet actif ou des autres onglets. Vous gagnerez en lecture et traçabilité !.
Limiter les formats conditionnels, ces derniers sont pratiques et fréquemment utilisés mais ils consomment des ressources au même titre que les formules de calculs. Donc plus il y existe de formats conditionnels au sein d’un onglet et d’un classeur, plus le temps de traitement va s’allonger. Dans la mesure du possible, utiliser les formats de nombres. Ces derniers offrent eux aussi de grandes possibilités sans ajouter de complexité aux calculs.
Utiliser les meilleurs fonctions
Utiliser les bonnes fonctions est un enjeu majeur pour optimiser les temps de calculs. En effet si la grande majorité des fonctions existantes prennent en compte le calcul « multi-cœur » de nos ordinateurs, certaines n’en sont pas capables et ralentissent donc les temps de traitement. Eviter par exemple au maximum les formules : Indirect() | Decaler().
N’utiliser la fonction Sommeprod() que pour des calculs très précis et non pour réaliser des sommes conditionnelles. Pour ces dernières, utiliser la fonction Somme.si.ens() permettant de mettre autant de conditions que souhaité.
Cette partie est la plus complexe car nous avons souvent tendance à utiliser les mêmes fonctions. il faut donc rester en alerte ! Si le temps de calcul de votre modèle Excel est long, c’est qu’il doit exister une manière de l’optimiser !
Regarder par ici notre top 10 des fonctions à connaitre !
Optimiser les tableaux croisés dynamiques
Si vous utilisez plusieurs tableaux permettant des récupérations des données, (par exemple un tableau qui récupère la balance, un sur le plan de comptes et un dernier sur l’organisation des agences) vous pouvez lorsque vous analysez via un TCD, les mettre en relation via la gestion d’un modèle de données. Celui-ci évite de créer des liens entre les tableaux via des fonctions de recherche qui alourdissent le fonctionnement du fichier.
Supprimer les liens directs entre les fichiers
Les liens entre les fichiers sont nocifs pour les fichiers Excel… on est jamais certain que le fichier se met correctement à jour. Si vous ne pouvez pas vous passer de liens, utilisez Power Query, cet outil vous permet de faire une requête sur un fichier pour récupérer des valeurs précises. Plus de liens = fichier optimisé !
Ce sont ici les grandes lignes en matière d’optimisation. Si vous rencontrer des problématiques d’optimisation, n’hésitez pas à nous contacter. Nous mettrons à plat vos processus gérer sous Excel pour vous aider à bâtir une solution optimale. A la clef, gain de temps et confort d’utilisation !