Depuis l’arrivée de l’option Mettre sous forme de tableau (Format as table) dans Excel, la grande majorité des tableaux de bord et des outils de gestion que j’élabore dans Excel, comprennent une ou plusieurs bases de données, mises sous forme de tableaux. Les avantages d’utiliser cette fonctionnalité afin d’obtenir une base de données dynamique sont nombreux. Si vous désirez les revoir, je vous invite à consulter les articles suivants:
Toutefois, dans certaines situations, il devient plutôt difficile de transformer sa base de données sous forme de tableau. Voici notamment 3 situations où on ne peut pas utiliser convenablement cette option:
- Utilisation de versions d’Excel antérieures à 2007
- Utilisation de l’option d’affichages personnalisés dans un fichier utilisant l’option Mettre sous forme de tableau
- Utilisation d’un tableau qui n’est pas à proprement parler une base de données (voir article suivant pour comprendre à quoi devrait ressembler réellement une base de données) : Excel – Un truc puissant qui vous fera économiser bien du temps
Dans toutes ces situations, il existe une autre approche, qui fournit non pas un résultat identique mais un résultat semblable, en ce sens qu’il permet de rendre la base de données sous-jacente dynamique (ou élastique si vous préférez) et donc de la laisser croître vers la droite et vers le bas, sans affecter tout ce qui y est rattaché (formules, graphiques, tableaux croisés dynamiques, etc.).
Base de données dynamique via une formue Excel !
En supposant que vous débutiez votre base de données dans la cellule A1 de votre fichier Excel et que votre formule se trouve dans la même feuille que votre base de données, vous devriez inscrire la formule suivante:
=offset($A$1,0,0,counta($A:$A),counta($1:$1))
=decaler($A$1;0;0;nbval($A:$A);nbval($1:$1))
Le résultat sera nécessairement #VALUE si vous n’effectuez pas d’opération comme telle sur cette formule puisque le résultat renvoie une plage de cellules et il est impossible d’afficher une plage de cellules dans une seule cellule. Vous pourriez donc décider, pour tester votre nouvelle formule, d’ajouter une somme devant.
=sum(offset($A$1,0,0,counta($A:$A),counta($1:$1)))
=somme(decaler($A$1;0;0;nbval($A:$A);nbval($1:$1)))
Dans l’exemple ci-bas, notre somme donnerait un résultat de 630, soit la somme des 9 dans les celulles A1 à E14 (5*14*9).
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
Ensuite, si nous ajoutons une colonne de 7 dans la colonne F, sans modifier la formule, on peut voir que la formule s’ajuste pour donner une somme de 728.
De même, si on ajoute une ligne de 7, en-dessous de la base de données d’origine, on voit là encore que la somme s’ajuste pour donner 770. Tout cela, en n’utilisant qu’une seule formule.
Comment cette formule fonctionne?
Pour bien comprendre comment fonctionne cette formule, il faut d’abord bien comprendre la fonction Offset / Decaler. Si cette fonction ne vous est pas familière, je vous invite à lire l’article: Excel: Fonction decaler/offset. On comprend donc qu’Excel va toujours déterminer l’étendue de la base de données en partant de la cellule A1 et va récupérer une plage de x cellules vers le bas (x représente le nombre de cellules non vides dans la colonne A) et y cellules vers la droite (y représente le nombre de cellules non vides sur la ligne 1). Il faut donc éviter de mettre d’autres données dans la colonne A et dans la ligne 1, que les données de la base de données elle-même!
Formation complémentaire
Pour une introduction aux principes de base et aux meilleures pratiques d’affaires en modélisation financière dans Excel et vous familiariser avec les fonctions et les fonctionnalités qui permettent de faire des prévisions financières, des analyses de sensibilité et des analyses de scénarios, suivez notre formation Excel – Modélisation financière niveau 1.
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 1 :
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é.
Ping : Excel: Créer une base de données ...