Découvrez le secret des plages relatives dynamique dans Excel

Découvrez le secret des plages nommées relatives dans Excel. Bonjour à tous, Alex d'excel-formation.fr et dans ce tutoriel, je vais vous montrer une fonctionnalité méconnue mais extrêmement puissante d'Excel. Il s'agit des plages nommées relatives dynamiques. Cette technique est effectivement très peu connue mais elle va nous permettre de créer des formules intelligentes qui vont s'adapter automatiquement à nos données sans avoir besoin de mettre en place de macro-commande en VBA. Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne alors pensez à vous abonner et surtout à activer la clochette de notification afin d'être sûr de ne manquer aucune astuce et de devenir rapidement un pro d'Excel. Comme d'habitude, si vous voulez être sûr de bien comprendre tout ce que nous allons voir dans le cadre de cette vidéo, vous pouvez télécharger le support de ce tutoriel ainsi que son corrigé en suivant le lien disponible dans la description de la vidéo ce qui vous permettra de réaliser les opérations en même temps que moi pour bien comprendre tout ce que nous allons voir dans cette vidéo. Alors comme nous venons de le voir, je vais vous montrer quel est l'intérêt d'utiliser des plages nommées relatives dans Excel et pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau de suivi des ventes d'une chaîne de magasins de sport et donc dans ce tableau, nous retrouvons le montant des ventes réalisé par ce magasin en fonction des rayons mais également en fonction des mois de l'année 2024. Et donc avant d'aborder les plages nommées relatives, faisons un rapide rappel sur les références dans Excel. En effet, il faut savoir que dans Excel, nous travaillons principalement avec deux types de références. Tout d'abord les références absolues qui utilisent le symbole dollar pour figer une cellule ou une plage de cellules. Ainsi, en écrivant A1 avec des dollars dans une formule, Excel conservera cette référence exacte même si la formule est copiée ou déplacée vers une autre cellule. La cellule référencée restera dans ce cas toujours A1. Il y a également les références relatives qui elles n'ont pas de symbole dollar et donc la référence pourra se déplacer en fonction de l'endroit où elle est copiée. Donc si nous écrivons égale A1 dans la cellule B1 et que nous copions cette formule en cellule C1, alors la référence se décalera automatiquement de manière à pointer vers la cellule B1. Et donc le mélange de ces deux types de références, la référence mixte, permet de ne figer que la colonne ou la ligne d'une cellule en fonction des besoins de différentes formules. Et donc les plages nommées relatives fonctionnent de la même manière que les références relatives, c'est-à-dire qu'elles vont pouvoir adapter leurs références en fonction de la position de la cellule qui les utilise. Maintenant, voyons comment créer une plage nommée relative qui permettra donc de simplifier nos calculs. Pour commencer, nous allons sélectionner la cellule qui va faire référence à la plage, c'est-à-dire ici la cellule B14, dans laquelle nous allons vouloir calculer la somme des ventes réalisée dans tous les rayons. Pour définir une plage de cellules, nous allons nous rendre dans le menu formule du ruban et cliquer sur gestionnaire des noms. Ensuite, nous allons cliquer sur nouveau. De cette manière, nous allons pouvoir spécifier le nom que nous voulons donner à la référence relative. Par exemple ici, nous allons l'appeler vente mensuelle. Qu'en dessous, dans le champ fait référence A, nous sélectionnons la plage des cellules que nous allons vouloir affecter à la plage nommée, donc en cliquant sur la flèche et en sélectionnant les cellules situées au-dessus, c'est-à-dire les cellules B9 jusqu'à B13. Dans la zone dans laquelle est saisie la référence, nous voyons qu'il s'agit d'une référence absolue, c'est-à-dire avec des symboles dollars. Pour convertir cette référence en référence relative, nous pouvons soit supprimer manuellement les dollars, soit utiliser le raccourci clavier F4 qui permet de passer donc ici sur une référence mixte sur lequel les numérolignes sont bloquées. Si nous appuyons une deuxième fois, ce sera la lettre des colonnes et enfin une troisième fois va nous permettre d'obtenir une référence relative. Cela permet donc de garantir que la plage va se déplacer en fonction de la cellule dans laquelle la formule est utilisée. Cela étant fait, nous appuyons sur entrée pour valider la référence puis sur OK pour créer la plage nommée Vente mensuelle. De cette manière, nous pouvons fermer maintenant le gestionnaire des noms puis dans la cellule B14, nous allons tout simplement calculer le total des ventes mensuelles en tapant tout simplement le nom Vente mensuelle ou alors en appuyant sur F3 pour afficher la boîte de dialogue qui permet donc de lister l'ensemble des noms qui existent dans notre classeur que nous allons insérer en cliquant sur OK. Ensuite, nous validons et nous voyons donc que le total est bien de 71 820 et donc, comme nous pouvons le constater, automatiquement, étant donné qu'il s'agit d'une plage nommée relative, la formule va toujours s'adapter en fonction de la cellule dans laquelle la formule a été insérée. En revanche, si nous effectuons un copier-coller de cette ligne, donc en sélectionnant ici la ligne 14, en appuyant sur CTRL-C et en la collant par exemple ici au niveau de la ligne 16, nous allons rencontrer un problème étant donné que les références de la plage nommée Vente mensuelle vont également s'adapter à cette nouvelle ligne. Cela signifie que la plage va se décaler. Ce que nous allons pouvoir constater en sélectionnant la cellule et en appuyant sur F2, nous voyons que, effectivement, cette ligne s'est décalée de deux cellules vers le bas. Donc les résultats que nous avons ici sont erronés et pour les corriger, nous allons devoir modifier les références des plages nommées afin d'utiliser des références mixtes qui, comme nous l'avons vu un petit peu plus tôt, permettent de ne ficher que la colonne ou que la ligne tout en gardant l'autre partie variable. Dans notre cas, si nous souhaitons que les lignes de la plage restent toujours les lignes 9 à 13, nous allons devoir revenir dans le gestionnaire des noms, soit à partir du menu formule en appuyant sur le gestionnaire des noms, soit pour aller plus vite, nous pouvons utiliser le raccourci clavier CTRL-F3. Ici nous voyons que la plage nommée fait référence aux cellules B11 et B15 étant donné que nous avons sélectionné juste avant la cellule B16. Pour modifier la référence, nous venons sélectionner à nouveau la plage des cellules dans laquelle se trouvent les rayons et nous allons appuyer sur F4 une seule fois afin de bloquer uniquement les numéros des lignes tout en conservant la liberté de pouvoir adapter la formule en fonction des lettres de colonne. Nous appuyons sur Entrée et ici nous validons la modification en appuyant sur la coche verte. Ensuite, lorsque nous cliquons sur Fermer, nous allons pouvoir constater que les résultats que nous avons sur les deux lignes sont les mêmes étant donné que si nous appuyons sur F2, nous voyons bien que c'est la plage des cellules qui correspond au montant des rayons qui sont bien intégrés à l'intérieur de la plage nommée relative. Nous pouvons encore une fois copier la ligne. En la sélectionnant et en la collant ici au niveau de la ligne 18, le résultat sera toujours correct. Il faut savoir que l'un des avantages des plages nommées relatives est leur capacité à s'adapter automatiquement aux nouvelles données que nous allons introduire à l'intérieur de nos tableaux. Pour illustrer ce principe, imaginons maintenant que nous souhaitions déterminer automatiquement le montant total des ventes par rayon c'est-à-dire le total de chacune des lignes de notre tableau. Pour cela, comme nous venons de le voir, nous allons tout d'abord nous placer dans la cellule G9 dans laquelle nous calculerons le total de la première ligne. Puis nous appuyons sur CTRL F3 afin d'ouvrir la boîte de dialogue gestionnaire des noms et nous cliquons sur Nouveau. Ici nous allons créer une nouvelle plage nommée que nous allons appeler Vente par Rayon et ici nous allons sélectionner la plage des cellules qui correspond au rayon Running c'est-à-dire les cellules B9 jusqu'à F9 et ici nous allons vouloir figer uniquement les numéros de colonne donc nous appuyons deux fois sur la touche F4. Ensuite nous pouvons valider en appuyant sur Entrée puis sur OK. De cette manière, pour obtenir automatiquement le montant total des ventes par rayon sur les mois de janvier à mai, nous allons pouvoir utiliser la formule égale Somme des ventes par rayon. Nous fermons la parenthèse et nouvelles dons et nous voyons donc ici que sur les cinq premiers mois de l'année 2024 le magasin a réalisé un chiffre d'affaires dans le rayon Running de 91 640 euros. Nous allons maintenant pouvoir dupliquer la formule en sélectionnant toutes les cellules de la colonne puis en appuyant sur F2 pour nous placer à l'intérieur de la barre des formules et ensuite nous allons valider en appuyant sur les touches CTRL et Entrée ce qui va permettre de valider la formule sur toutes les cellules sélectionnées. Maintenant que notre plage mixte est créée imaginons que nous souhaitions ajouter les ventes du mois de juin. Nous allons sélectionner la colonne G et appuyer sur CTRL plus afin d'insérer une nouvelle colonne dans notre base. Ici nous allons saisir la date, à savoir le 30 juin et pour simplifier nous allons supposer que nous avons réalisé 10 000 euros de ventes par rayon au cours du mois de juin. Ici nous allons pouvoir reprendre la formule. Étant donné qu'il y a cinq rayons, il y a bien 50 000 euros de réalisés au cours du mois de juin sauf que ces 50 000 euros ne sont pas ici pris en compte dans le calcul des ventes totales. Pour rendre la plage nommée mixte dynamique, nous allons devoir rendre la référence plus complexe en utilisant la fonction décaler directement au niveau de la plage nommée. Pour cela nous allons devoir rendre la plage nommée relative dynamique c'est-à-dire que nous allons vouloir que celle-ci adapte le nombre de colonnes à insérer dans la fonction SUM automatiquement en fonction de la position dans laquelle la formule est insérée. Pour cela nous appuyons sur CTRL et F3 afin de relancer la fenêtre de gestionnaire des noms puis nous allons nous placer sur la plage nommée vente par rayon et nous allons utiliser la fonction décaler qui va permettre de générer de manière dynamique la plage des cellules à récupérer. Donc ici nous allons toujours vouloir partir de la cellule B9 qui est donc la première cellule dans laquelle se trouvent les ventes du mois de janvier du rayon running en conservant uniquement un blocage au niveau de la colonne et donc en laissant le 9 sans son symbole de lard. Ensuite la fonction décaler va nous permettre soit d'effectuer un décalage de cellules à partir de cette cellule de départ en spécifiant tout d'abord le décalage en nombre de colonnes puis en nombre de lignes. Ici étant donné que nous voulons rester sur la même cellule de départ nous allons utiliser les valeurs 0 pour ces deux arguments. Ensuite nous allons pouvoir spécifier la hauteur en nombre de lignes. Nous voulons rester sur la même ligne donc la plage retournée fera une ligne de haut. En revanche pour obtenir la largeur de la plage de cellules à générer de manière dynamique nous allons pouvoir partir du numéro de la colonne dans laquelle est insérée la formule que nous allons obtenir très facilement grâce à la fonction Excel colonne qui va nous retourner ici le numéro de la colonne H et nous allons retirer deux colonnes à cette valeur afin de ne pas tenir compte de la première colonne dans laquelle est saisie la catégorie ni de la colonne dans laquelle le résultat est retourné ce qui va éviter ici d'avoir une référence circulaire. Lorsque nous avons terminé nous appuyons sur la coche verte pour valider la formule et ensuite lorsque nous cliquons sur fermer nous allons pouvoir constater que automatiquement la plage des cellules relatives que nous avons utilisé est bien dynamique et qu'elle tient compte des ventes du mois de juin. Pour nous en rendre compte nous allons insérer encore une nouvelle colonne afin de venir saisir les ventes du mois de juillet. Ici ces ventes sont de 20 000 euros par rayon que nous saisissons et que nous validons ensuite en appuyant sur CTRL ENTREZ. A ce moment là comme vous pouvez le constater nous avons effectivement les ventes du mois de juillet qui sont intégrées dans le calcul de notre fonction somme. Donc comme d'habitude vous pouvez télécharger le support de secours en suivant le lien que vous trouverez dans la description de la vidéo. Si la vidéo vous a plu n'oubliez pas de la liker et de la partager à vos collègues et amis sur l'ensemble de vos réseaux sociaux et vous pouvez également me laisser vos impressions dans l'espace des commentaires je lirai chacun d'entre eux et y répondrai avec plaisir. Evidemment si ce n'est pas encore fait abonnez-vous à la chaîne pour ne manquer aucune astuce c'est également très important pour m'aider à gagner en visibilité afin de pouvoir développer la chaîne et continuer de vous proposer des tas de nouvelles vidéos. Merci d'avoir suivi cette vidéo et à très bientôt pour un prochain tuto.