Excel formation - Page de vente TCd - 11 Découvrez la méthode complète pour analyser les données efficacement et sans effort avec les tableaux croisés dynamiques, en cliquant ici

Comment simplifier les formules avec l’automatisation des plages nommées d’Excel

Aujourd’hui je vous propose un tutoriel dans lequel je vais vous montrer comment utiliser les plages nommées pour faciliter la création de formules sur Excel. Nous verrons ainsi comment les plages nommées permettent de facilité la création de formules, et ensuite comment automatiser l’affectation de nom sur l’ensemble des lignes et des colonnes d’un tableau.

 

Téléchargement

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

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

1. Présentation

Pour illustrer ce que nous allons voir dans ce chapitre, nous allons utiliser un petit tableau qui reprend les ventes annuelles réalisées par une chaîne de magasins :

Excel formation - nommer cellules automatiquement - 01

Nous retrouvons ainsi les ventes réalisées par chaque magasin, chacun d’entre eux étant regroupé par région, ainsi que par trimestre.

À partir de ce tableau, nous allons souhaiter faire ressortir quelques informations clés telles que :

  • Le montant des ventes réalisées par le magasin de Lille,
  • Le pourcentage des ventes réalisées par les magasins de la région Sud pour le premier trimestre,
  • La variation des ventes entre le premier et le second trimestre pour l’ensemble des magasins

Dans un premier temps, nous allons étudier la méthode classique.

Pour récupérer le montant des ventes réalisées par le magasin de Lille, la méthode est très simple, il suffit en effet de taper le symbole d’égalité pour préciser à Excel que nous souhaitons saisir une formule.

Ensuite nous cliquons sur la cellule correspondante pour créer une référence à cette même cellule afin qu’Excel reprenne la valeur de la cellule en tant que valeur :

 

 =F8 

Excel formation - nommer cellules automatiquement - 02

Le pourcentage des ventes réalisées par le magasin de la région Sud par rapport au total des ventes sur le premier trimestre s’obtient de la même manière, mais en effectuant une division entre les deux cellules :

 

 =B17/B23 

Excel formation - nommer cellules automatiquement - 03

Et pour finir, la variation entre deux périodes s’obtient en effectuant une différence entre les deux :

 

 =C23-B23 

Excel formation - nommer cellules automatiquement - 04

 

 

2. Pourquoi nommer des cellules ?

Ces formules sont évidemment très simples, mais hors contexte, celles-ci sont absolument incompréhensibles :

 

 =F8
=B17/B23
=C23-B23 

Il est donc nécessaire de remonter jusqu’au cellules sources pour comprendre

Pour que la compréhension en soit simplifiée, Excel propose d’affecter un nom directement à une ou plusieurs cellules !

Pour cela, c’est très simple, il suffit de sélectionner les cellules en question, puis de saisir le nom correspondant dans la zone des noms, située en haut à gauche, juste à côté de la barre des formules (puis d’appuyer sur la touche [Entrée] pour valide) :

Excel formation - nommer cellules automatiquement - 05

Nous pourrons alors reconstruire les formules en utilisant les noms correspondants :

Excel formation - nommer cellules automatiquement - 06

 

 =_ventesTotalesLille
=_ventesT1RegionSud/_ventesT1Totales
=_ventesT2Totales-_ventesT1Totales 

Note 1 : Comme vous pouvez le constater j’ai pour habitude de toujours suffixer les noms de cellules avec un tiret « _ ».

Cette astuce permet de retrouver tous les noms du classeurs réunis les uns en dessous des autres au moment de la saisie de la formule.

Il suffit en effet de taper le tiret pour que la saisie assistée nous les présente :

Excel formation - nommer cellules automatiquement - 07

Note 2 : Pour retrouver une liste des noms disponibles sur le classeur, rendez-vous dans le menu Formule, puis dans le groupe Noms définis de dérouler le menu Dans une formule :

Excel formation - nommer cellules automatiquement - 08

De cette manière, lorsque nous cliquons sur un des noms disponibles, Excel va immédiatement l’insérer dans la formule en cours de saisie.

Bien entendu, ici nous n’avons renommé que des cellules uniques, mais il est également possible de renommer des plages constituées de plusieurs cellules :

Excel formation - nommer cellules automatiquement - 09

En combinant des groupes de plages de cellules nommées avec la fonction SOMME(), nous pouvons ainsi utiliser une astuce que nous avons déjà eu l’occasion d’utiliser dans un tutoriel précédent : l’opérateur d’intersection :

 

 =somme(_CA _ventesLille) 

Excel formation - nommer cellules automatiquement - 10

Ici, l’espace permet de récupérer la ou les cellules qui se trouvent à l’intersection des plages de cellules, que nous intégrons ensuite dans une fonction SOMME().

Le seul problème de fonctionner ainsi c’est qu’il sera alors nécessaire de nommer toutes les lignes et colonnes du tableau pour pouvoir créer toutes sortes de fonctions !

 

3. Nommer automatiquement les lignes et colonnes d’un tableau

Heureusement pour nous, Excel propose une fonctionnalité de nommage automatique des cellules qui va alors nous permettre de donner un nom spécifique à toutes les lignes et colonnes d’un tableau !

Pour cela, nous commençons par sélectionner les cellules du tableau :

  • Soit directement à la souris s’il s’agit d’un petit tableau comme celui de notre exemple,
  • Soit si le tableau contient davantage de cellules en sélectionnant l’un des cellules de ce tableau, puis en appuyant sur les touches [Ctrl]+[A] :

Excel formation - nommer cellules automatiquement - 11

Puis, nous nous rendons dans le menu Formules afin de cliquer sur le bouton Depuis sélection, toujours dans le groupe Noms définis :

Excel formation - nommer cellules automatiquement - 12

Excel nous présente alors une fenêtre afin de définir à quel endroit du tableau se trouve les en-têtes qui seront alors de référence pour la création des noms affectés aux plages de cellules :

Excel formation - nommer cellules automatiquement - 13

Ici, s’agissant d’un tableau classique, nous utilisons la ligne du haut ainsi que la colonne de gauche pour créer les noms automatiquement.

Nous pouvons ensuite valider la création des noms en cliquant sur le bouton [OK].

Les noms sont maintenant créés, nous pouvons les retrouver :

  • Soit en déroulant le menu d’affectation de nom ; ce qui permettra alors de sélectionner les cellules correspondantes :

Excel formation - nommer cellules automatiquement - 14

  • Soit en déroulant le menu [Dans une formule] ; ce qui permettra alors d’insérer le nom correspondant dans une formule en cours de rédaction

Excel formation - nommer cellules automatiquement - 15

Nous pouvons maintenant utiliser ces noms pour construire des formules qui seront alors facilement compréhensible, notamment avec la fonction somme :

 

 =SOMME(CA Lille) 
=SOMME(CA_T1 Région_Sud)/SOMME(CA_T1 Total) 
=SOMME(CA_T2 Total)-SOMME(CA_T1 Total) 

 

Excel formation - nommer cellules automatiquement - 16

 

4. Le tableau de données d’Excel

Bien entendu, si vous avez déjà eu l’occasion de suivre le cours dédié à la découverte des tableaux de données d’Excel vous devez vous demander quel est l’intérêt de cette fonctionnalité ?

En effet, ces deux options permettent d’attribuer automatiquement un nom aux cellules d’un tableau, mais les usages sont bien différents :

  • Le tableau de données permet d’ajouter de nombres fonctionnalités au tableau (mise en forme élaborée, recopie de formule automatique sur toutes les cellules d’une colonne, …) et en plus de cela, les coordonnées du tableau vont s’adapter automatiquement lorsque nous allons ajouter de nouvelles lignes ou colonnes au tableau,
  • L’affectation automatique des noms de lignes et colonnes permet quant à elle de créer une relation entre un nom et des cellules à un instant T, sans avoir le côté dynamique. En revanche l’avantage de cette solution vient du fait que même les lignes vont avoir un nom attribué, ce qui n’est pas possible avec les tableaux de données !

Mais le plus intéressant, c’est que ces deux fonctionnalités sont complémentaires et peuvent être utilisées simultanément afin de pouvoir tirer le meilleur des deux montres !

Excel formation - nommer cellules automatiquement - 17

 

 



Articles qui pourraient vous intéresser

Comment compléter les cellules vides en 1 clic sur Excel
Comment calculer la TVA sur Excel
Comment envoyer la sélection par mail en 1 clic avec VBA sur Excel
Comment obtenir les cellules à l’intersection de deux plages en VBA sur Excel
Comment convertir un fichier Excel en PDF Excel2pdf
Comment compter les valeurs uniques (dénombrement sans doublon) sur Excel
Comment simplifier les formules avec l’automatisation des plages nommées d’Excel
Comment ajouter automatiquement une ligne lors d’un changement de valeur dans une colonne Excel
Comment tirer une lettre aléatoire pour créer un mot de passe incrackable – La fonction CAR() d’Excel
Comment créer une zone d’impression dynamique et intelligente sur Excel
Comment supprimer les lignes vides ou avec des cellules vides d’un tableau sur Excel
Comment géolocaliser une adresse sur Google Maps en 1 clic avec Excel

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.