Excel: Créer une base de données dynamique sans l’option Mettre sous forme de tableau

Publié le 18 mars 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Sum offset

Excel: Créer une base de données dynamique sans l’option Mettre sous forme de tableau

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é 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.).

.

La formule dynamique en question!

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).

Sum offset

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.

Sum offset

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.

Sum offset.

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!

.

Vous voulez apprendre d’autres trucs comme celui-là?

Je vous invite à participer à ma formation Excel – Modélisation financière (niveau 1).

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

1 réflexion sur “Excel: Créer une base de données dynamique sans l’option Mettre sous forme de tableau”

  1. Ping : Excel: Créer une base de données ...

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top