Comment créer une plage définie dynamique dans une feuille de calcul Excel

  • Article
  • S’applique à:
    Excel 2013, Excel 2010, Excel 2007, Excel 2003

Résumé

Dans Microsoft Excel, vous pouvez avoir une plage nommée qui doit être étendue pour inclure de nouvelles informations. Cet article décrit une méthode pour créer un nom dynamique défini.

Remarque

La méthode décrite dans cet article suppose qu’il n’y a pas plus de 200 lignes de données. Vous pouvez modifier les noms définis afin qu’ils utilisent le nombre approprié et reflètent le nombre maximal de lignes.

Comment utiliser la formule OFFSET avec un nom défini

Pour ce faire, procédez comme suit, en fonction de la version d’Excel que vous exécutez.

Microsoft Office Excel 2007, Microsoft Excel 2010 et Microsoft Excel 2013

  1. Dans une nouvelle feuille de calcul, entrez les données suivantes.

    Nombre A B
    1 Month Ventes
    2 Jan 10
    3 Fév 20
    4 Mars 30

  2. Cliquez sur l’onglet Formules.

  3. Dans le groupe Noms définis , cliquez sur Gestionnaire de noms.

  4. Cliquez sur Nouveau.

  5. Dans la zone Nom , tapez Date.

  6. Dans la zone Fait référence à , tapez le texte suivant, puis cliquez sur OK :

    =OFFSET($A $2,0,0,COUNTA($A$2 :$A$200),1)

  7. Cliquez sur Nouveau.

  8. Dans la zone Nom , tapez Sales.

  9. Dans la zone Fait référence à , tapez le texte suivant, puis cliquez sur OK :

    =OFFSET($B$2,0,0,COUNT($B$2 :$B$200),1)

  10. Cliquez sur Fermer.

  11. Effacez la cellule B2, puis tapez la formule suivante :

    =RAND()*0+10

    Remarque

    Dans cette formule, COUNT est utilisé pour une colonne de nombres. COUNTA est utilisé pour une colonne de valeurs de texte.

    Cette formule utilise la fonction RAND volatile. Cette formule met automatiquement à jour la formule OFFSET utilisée dans le nom défini « Sales » lorsque vous entrez de nouvelles données dans la colonne B. La valeur 10 est utilisée dans cette formule, car 10 est la valeur d’origine de la cellule B2.

Microsoft Office Excel 2003

  1. Dans une nouvelle feuille de calcul, entrez les données suivantes :

    Nombre A B
    1 Month Ventes
    2 Jan 10
    3 Fév 20
    4 Mars 30

  2. Dans le menu Insertion , pointez sur Nom, puis cliquez sur Définir.

  3. Dans la zone Noms dans le classeur , tapez Date.

  4. Dans la zone Fait référence à , tapez le texte suivant, puis cliquez sur OK :

    =OFFSET($A $2,0,0,COUNTA($A$2 :$A$200),1).

  5. Cliquez sur Ajouter.

  6. Dans la zone Noms dans le classeur , tapez Sales.

  7. Dans la zone Fait référence à , tapez le texte suivant, puis cliquez sur Ajouter :

    =OFFSET($B$2,0,0,COUNT($B$2 :$B$200),1)

  8. Cliquez sur OK.

  9. Effacez la cellule B2, puis tapez la formule suivante :

    =RAND()*0+10

    Remarque

    Dans cette formule, COUNT est utilisé pour une colonne de nombres. COUNTA est utilisé pour une colonne de valeurs de texte.

    Cette formule utilise la fonction RAND volatile. Cette formule met automatiquement à jour la formule OFFSET utilisée dans le nom défini « Sales » lorsque vous entrez de nouvelles données dans la colonne B. La valeur 10 est utilisée dans cette formule, car 10 est la valeur d’origine de la cellule B2.