Comment automatiser vos Tableaux Excel grâce aux plages nommées dynamiques ?

Dans ce tutoriel, je vais vous montrer comment créer des plages nommées dynamiques dans Excel qui s'adaptent automatiquement lorsque vous ajoutez de nouvelles données, sans jamais avoir à modifier vos formules manuellement.

Nous allons découvrir comment intégrer ces plages dans vos graphiques pour qu'ils se mettent à jour tout seuls, et surtout comment éviter les erreurs les plus fréquentes qui peuvent faire échouer vos formules.

Cette technique va transformer votre façon de travailler avec Excel et vous faire gagner un temps considérable.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons le suivi de facturation d'une agence web.

Ce tableau nous permet de suivre le chiffre d'affaires mensuel pour différents types de prestations proposées par l'agence.

Nous avons ici des données sur cinq lignes, mais notre tableau va évoluer au fil du temps pour tenir compte de nouvelles prestations réalisées dans les mois à venir.

Nous ajouterons également de nouveaux mois, ce qui créera de nouvelles colonnes vers la droite.

C'est exactement pour gérer ce type de croissance dynamique que les plages nommées dynamiques deviennent indispensables.

2. Créer une plage dynamique pour l'ensemble du tableau avec INDEX

Commençons par créer notre première plage nommée dynamique qui va englober l'intégralité de notre tableau, en-têtes compris, et qui s'adaptera automatiquement lorsque nous ajouterons des données.

   2.1. Construction de la formule dans une cellule

Nous allons d'abord construire notre formule directement dans une cellule de la feuille de calcul avant de la transformer en nom de plage.

L'idée est simple : nous voulons créer une plage qui commence à la première cellule du tableau et qui se termine à la dernière cellule contenant des données, que nous allons déterminer dynamiquement grâce à la fonction INDEX.

Nous commençons par taper le signe égal, puis nous cliquons sur la cellule A1, qui est notre point de départ et nous transformons la référence en référence absolue en appuyant sur la touche [F4] :

 =$A$8 

Ensuite, nous tapons le symbole deux-points, qui indique à Excel que nous voulons créer une plage allant de la première cellule jusqu'à une autre cellule que nous allons maintenant définir en utilisant la fonction INDEX pour trouver automatiquement la dernière cellule de notre tableau.

Nous tapons « INDEX( » nous sélectionnons avec la souris une plage qui va bien au-delà de nos données actuelles, par exemple jusqu'à J20, ce qui nous laisse de la place pour grandir jusqu'à 11 lignes et 10 colonnes (incluant les mois jusqu'à Septembre).

Nous appuyons à nouveau sur [F4] pour rendre cette plage absolue, ce qui nous donne

 $A$8:$J$20 

Cette plage représente l'espace maximum que notre tableau pourra occuper.

Il est important que cette plage soit suffisamment grande pour anticiper la croissance future de nos données.

Maintenant, nous devons indiquer à INDEX quelle ligne nous intéresse. Nous voulons la dernière ligne contenant des données.

Pour cela, nous allons compter combien de lignes contiennent des données en utilisant la fonction NBVAL, qui va compter toutes les cellules non vides dans cette colonne.

Comme notre colonne « Prestation » contient l'en-tête plus les 5 prestations actuelles, elle retournera 6, ce qui indiquera à INDEX de prendre la sixième ligne.

Si demain nous ajoutons une nouvelle prestation, NBVAL retournera 7 automatiquement.

Nous fermons la parenthèse de NBVAL et nous ajoutons un point-virgule pour passer au dernier argument de INDEX, qui concerne le numéro de colonne.

De la même manière, nous allons compter combien de colonnes contiennent des données en tapant « NBVAL », puis en sélectionnant la première ligne de notre plage étendue, soit B8:J8.

Nous appuyons sur [F4] pour obtenir une référence absolue, puis nous fermons les deux parenthèses restantes.

Notre formule complète ressemble maintenant à ceci :

 =$A$8:INDEX($A$8:$J$20;NBVAL($A$8:$A$20);NBVAL($B$8:$J$8))  

Nous appuyons sur [Entrée] et si nous utilisons Excel 365 ou Excel 2021, nous verrons que le résultat se « déverse » sur plusieurs cellules, affichant l'ensemble de notre tableau.

Si nous utilisons une version antérieure d'Excel, nous obtiendrons une erreur #VALEUR!, mais ce n'est pas grave, c'est normal car ces versions ne supportent pas le déversement de tableaux.

   2.2. Transformer la formule en nom de plage

Maintenant que notre formule fonctionne, nous allons la transformer en nom de plage dynamique.

Nous cliquons dans la cellule contenant notre formule, puis nous cliquons dans la barre de formule et nous sélectionnons l'intégralité de la formule, que nous copions en appuyant sur [Ctrl]+[C].

Ensuite, nous nous rendons dans l'onglet « Formules » du ruban Excel, et nous cliquons sur le bouton « Définir un nom » dans le groupe « Noms définis ».

Une boîte de dialogue s'ouvre.

Dans le champ « Nom », nous saisissons un nom explicite pour notre plage, par exemple « PlageFacturation ».

Dans le champ « Fait référence à », nous supprimons tout ce qui s'y trouve, puis nous appuyons sur [Ctrl]+[V] pour coller notre formule.

Nous cliquons sur le bouton « OK » pour valider.

Notre plage dynamique est maintenant créée et nous pouvons l'utiliser dans n'importe quelle formule en tapant simplement son nom.

Pour tester que tout fonctionne, nous nous rendons dans une autre cellule vide et nous tapons :

 =NB(PlageFacturation) 

Cette formule compte le nombre de cellules contenant des valeurs numériques dans notre plage.

Nous devrions obtenir 30, ce qui correspond aux 5 prestations multipliées par les 6 mois de données.

Maintenant, pour vérifier que notre plage est vraiment dynamique, nous ajoutons une nouvelle prestation dans notre tableau.

Par exemple, nous saisissons « Consulting » dans la colonne « A », puis nous ajoutons quelques valeurs dans les mois correspondants.

Si nous regardons à nouveau le résultat de notre formule, nous constatons que le nombre a augmenté automatiquement, sans que nous ayons eu à modifier quoi que ce soit.

3. Créer une plage dynamique pour une ligne spécifique

Passons maintenant à un cas plus avancé : créer une plage dynamique qui retourne uniquement les données d'une prestation spécifique, choisie dans une liste déroulante.

Cette technique est particulièrement utile pour créer des tableaux de bord interactifs où l'utilisateur peut sélectionner ce qu'il veut analyser.

   3.1. Préparer la liste déroulante de sélection

Avant de créer notre formule, nous allons utiliser la liste déroulante en cellule I6 qui permettra de choisir la prestation à afficher.

Nous choisissons par exemple « Référencement ».

   3.2. Construction de la formule avec INDEX et EQUIV

Nous allons maintenant créer une formule qui retourne uniquement la ligne correspondant à la prestation sélectionnée.

Cette formule va utiliser INDEX pour retourner une plage, et EQUIV pour trouver quelle ligne correspond à notre sélection.

Nous nous rendons dans une cellule vide, par exemple J6, et nous commençons à construire notre formule.

L'idée est de créer une plage qui commence à la première cellule de données de la ligne (le mois de Janvier) et qui se termine à la dernière cellule (le dernier mois disponible).

Nous tapons « =INDEX( » et nous sélectionnons la plage des données de valeurs, en partant de B8 (l'en-tête « Janvier ») jusqu'à J20 pour anticiper la croissance.

Nous obtenons « B$8:$J$20 » après avoir appuyé sur [F4].

Pour l'argument du numéro de ligne, nous devons trouver sur quelle ligne se trouve la prestation sélectionnée.

Nous utilisons pour cela la fonction EQUIV.

Le premier argument est la valeur recherchée, soit notre cellule de sélection I6.

Le deuxième argument de EQUIV est la plage dans laquelle chercher.

Nous sélectionnons la colonne des prestations avec un peu de marge pour la croissance, soit A1:A20, et nous appuyons sur [F4].

Le troisième argument est 0 pour une correspondance exacte. Nous fermons la parenthèse de EQUIV.

Pour le numéro de colonne dans INDEX, nous devons indiquer 1 car nous voulons commencer par la première colonne de notre plage indexée.

Nous fermons la parenthèse de INDEX.

Maintenant, nous tapons le symbole deux-points pour créer notre plage, et nous recommençons avec INDEX pour trouver la dernière cellule, en utilisant cette fois la plage des en-têtes pour dénombrer le nombre de colonnes utilisées (la plage B8 à J8 par exemple).

Notre formule complète est donc :

=INDEX($B$8:$J$20;EQUIV($I$4;$A$8:$A$20;0);1):INDEX($B$8:$J$20;EQUIV($I$4;$A$8:$A$20;0);NBVAL($B$8:$J$8))  

Nous appuyons sur [Entrée] et nous voyons les valeurs de la prestation « Référencement » s'afficher.

Si nous changeons notre sélection dans la liste déroulante, les valeurs se mettent à jour automatiquement.

Nous pouvons ensuite définir cette formule comme un nom de plage, par exemple « PlagePrestation », en suivant la même procédure que précédemment.

Et enfin, nous pouvons l’utiliser comme argument d’une autre fonction (par exemple la fonction SOMME)