Comment définir une plage de valeurs dans Excel

Lors de l'utilisation de Microsoft Excel, très souvent, l'utilisateur ne sait pas à l'avance la quantité d'informations qui en résultera dans le tableau. Par conséquent, nous ne comprenons pas dans toutes les situations quelle plage doit être couverte. Après tout, un ensemble de cellules est un concept variable. Pour s'affranchir de ce problème, il est nécessaire d'automatiser la génération de la plage afin qu'elle se base uniquement sur la quantité de données saisies par l'utilisateur.

Modification automatique des plages de cellules dans Excel

L'avantage des plages automatiques dans Excel est qu'elles facilitent grandement l'utilisation des formules. De plus, ils permettent de simplifier considérablement l'analyse de données complexes qui contiennent un grand nombre de formules, qui incluent de nombreuses fonctions. Vous pouvez donner un nom à cette plage, puis elle sera mise à jour automatiquement en fonction des données qu'elle contient.

Comment faire un changement de plage automatique dans Excel

Supposons que vous êtes un investisseur qui a besoin d'investir dans un objet. Par conséquent, nous souhaitons obtenir des informations sur le montant total que vous pouvez gagner pendant toute la durée pendant laquelle l'argent fonctionnera pour ce projet. Néanmoins, afin d'obtenir ces informations, nous devons surveiller régulièrement le profit total que cet objet nous rapporte. Faites le même rapport que dans cette capture d'écran.

À première vue, la solution est évidente : il vous suffit de faire la somme de toute la colonne. Si des entrées y apparaissent, le montant sera mis à jour indépendamment. Mais cette méthode présente de nombreux inconvénients :

1. Si le problème est résolu de cette manière, il ne sera pas possible d'utiliser les cellules incluses dans la colonne B à d'autres fins.
2. Un tel tableau consommera beaucoup de RAM, ce qui rendra impossible l'utilisation du document sur des ordinateurs faibles.

Par conséquent, il est nécessaire de résoudre ce problème par des noms dynamiques. Pour les créer, vous devez effectuer la séquence d'actions suivante :

1. Allez dans l'onglet "Formules", qui se trouve dans le menu principal. Il y aura une section "Noms définis", où il y a un bouton "Attribuer un nom", sur lequel nous devons cliquer.
2. Ensuite, une boîte de dialogue apparaîtra dans laquelle vous devrez remplir les champs comme indiqué dans la capture d'écran. Il est important de noter que nous devons appliquer la fonction = DÉPLACEMENT avec la fonction COMMENCERpour créer une plage de mise à jour automatique. 
3. Après cela, nous devons utiliser la fonction SUM, qui utilise notre plage dynamique comme argument. 

Après avoir terminé ces étapes, nous pouvons voir comment la couverture des cellules appartenant à la plage "revenu" est mise à jour à mesure que nous y ajoutons de nouveaux éléments.

Fonction OFFSET dans Excel

Regardons les fonctions que nous avons enregistrées dans le champ "range" plus tôt. Utilisation de la fonction ÉLIMINATION nous pouvons déterminer la quantité de plage compte tenu du nombre de cellules de la colonne B remplies. Les arguments de la fonction sont les suivants :

1. Cellule de départ. Avec cet argument, l'utilisateur peut indiquer quelle cellule de la plage sera considérée en haut à gauche. Il rapportera vers le bas et vers la droite.
2. Plage décalée par lignes. En utilisant cette plage, nous définissons le nombre de cellules par lequel un décalage doit se produire à partir de la cellule supérieure gauche de la plage. Vous pouvez utiliser non seulement des valeurs positives, mais aussi zéro et moins. Dans ce cas, le déplacement peut ne pas se produire du tout, ou il sera effectué dans le sens opposé.
3. Plage décalée par colonnes. Ce paramètre est similaire au précédent, sauf qu'il vous permet de définir le degré de décalage horizontal de la plage. Ici, vous pouvez également utiliser des valeurs nulles et négatives.
4. La quantité de plage en hauteur. En fait, le titre de cet argument nous fait comprendre ce qu'il signifie. Il s'agit du nombre de cellules dont la plage doit augmenter.
5. La valeur de la plage en largeur. L'argument est similaire au précédent, sauf qu'il concerne les colonnes.

Vous n'avez pas besoin de spécifier les deux derniers arguments si vous n'en avez pas besoin. Dans ce cas, la valeur de la plage ne sera qu'une cellule. Par exemple, si vous spécifiez la formule =DECALAGE(A1;0;0), cette formule fera référence à la même cellule que celle du premier argument. Si le décalage vertical est défini sur 2 unités, dans ce cas, la cellule fera référence à la cellule A3. Décrivons maintenant en détail ce que signifie la fonction COMMENCER.

Fonction COUNT dans Excel

Utilisation de la fonction COMMENCER nous déterminons combien de cellules de la colonne B nous avons remplies au total. Autrement dit, en utilisant deux fonctions, nous déterminons combien de cellules de la plage sont remplies et, en fonction des informations reçues, nous déterminons la taille de la plage. La formule finale sera donc la suivante : =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Voyons comment comprendre correctement le principe de cette formule. Le premier argument indique où commence notre plage dynamique. Dans notre cas, il s'agit de la cellule B2. D'autres paramètres ont des coordonnées nulles. Cela suggère que nous n'avons pas besoin de décalages par rapport à la cellule supérieure gauche. Tout ce que nous remplissons est la taille verticale de la plage, dont nous avons utilisé la fonction comme COMMENCER, qui détermine le nombre de cellules contenant des données. Le quatrième paramètre que nous avons renseigné est l'unité. Ainsi, nous montrons que la largeur totale de la plage doit être d'une colonne.

Ainsi, en utilisant la fonction COMMENCER l'utilisateur peut utiliser la mémoire aussi efficacement que possible en ne chargeant que les cellules contenant certaines valeurs. En conséquence, il n'y aura pas d'erreurs supplémentaires dans le travail associées aux mauvaises performances de l'ordinateur sur lequel la feuille de calcul fonctionnera.

En conséquence, afin de déterminer la taille de la plage en fonction du nombre de colonnes, vous devez effectuer une séquence d'actions similaire. Dans ce cas, vous devez spécifier l'unité dans le troisième paramètre et la formule dans le quatrième. COMMENCER.

Nous voyons qu'avec l'aide des formules Excel, vous pouvez non seulement automatiser les calculs mathématiques. Ce n'est qu'une goutte dans l'océan, mais en fait, ils vous permettent d'automatiser presque toutes les opérations qui vous viennent à l'esprit.

Graphiques dynamiques dans Excel

Ainsi, dans la dernière étape, nous avons pu créer une plage dynamique dont la taille dépend entièrement du nombre de cellules remplies qu'elle contient. Désormais, sur la base de ces données, vous pouvez créer des graphiques dynamiques qui changeront automatiquement dès que l'utilisateur apportera des modifications ou ajoutera une colonne ou une ligne supplémentaire. La séquence d'actions dans ce cas est la suivante:

1. On sélectionne notre gamme, après quoi on insère un graphique de type « Histogramme avec regroupement ». Vous pouvez trouver cet élément dans la section "Insérer" dans la section "Graphiques - Histogramme".
2. Nous faisons un clic gauche de la souris sur une colonne aléatoire de l'histogramme, après quoi la fonction =SERIES() sera affichée dans la ligne de fonction. Dans la capture d'écran, vous pouvez voir la formule détaillée. 
3. Après cela, certaines modifications doivent être apportées à la formule. Vous devez remplacer la plage après "Sheet1!" au nom de la gamme. Cela se traduira par la fonction suivante : =LIGNE(Feuille1!$B$1;;Feuille1!Revenu;1)
4. Il reste maintenant à ajouter un nouvel enregistrement au rapport pour vérifier si le graphique est mis à jour automatiquement ou non.

Jetons un coup d'œil à notre schéma.

Résumons comment nous l'avons fait. Dans l'étape précédente, nous avons créé une plage dynamique dont la taille dépend du nombre d'éléments qu'elle contient. Pour ce faire, nous avons utilisé une combinaison de fonctions COMMENCER и ÉLIMINATION. Nous avons nommé cette plage, puis nous avons utilisé la référence à ce nom comme plage de notre histogramme. La plage spécifique à choisir comme source de données à la première étape n'est pas si importante. L'essentiel est de le remplacer par le nom de la gamme plus tard. De cette façon, vous pouvez économiser beaucoup de RAM.

Gammes nommées et leurs utilisations

Parlons maintenant plus en détail de la façon de créer correctement des plages nommées et de les utiliser pour effectuer les tâches définies pour l'utilisateur Excel.

Par défaut, nous utilisons des adresses cellulaires régulières afin de gagner du temps. Ceci est utile lorsque vous devez écrire une plage une ou plusieurs fois. S'il doit être utilisé tout le temps ou s'il doit être adaptatif, des plages nommées doivent être utilisées. Ils facilitent grandement la création de formules et il ne sera pas si difficile pour l'utilisateur d'analyser des formules complexes comprenant un grand nombre de fonctions. Décrivons quelques-unes des étapes impliquées dans la création de plages dynamiques.

Tout commence par nommer la cellule. Pour ce faire, sélectionnez-le simplement, puis écrivez le nom dont nous avons besoin dans le champ de son nom. Il est important qu'il soit facile à retenir. Certaines restrictions doivent être prises en compte lors de la dénomination :

1. La longueur maximale est de 255 caractères. C'est bien suffisant pour attribuer un nom que votre cœur désire.
2. Le nom ne doit pas contenir d'espaces. Par conséquent, s'il contient plusieurs mots, il est alors possible de les séparer à l'aide du caractère de soulignement.

Si plus tard, sur d'autres feuilles de ce fichier, nous devons afficher cette valeur ou l'appliquer pour effectuer d'autres calculs, il n'est pas nécessaire de passer à la toute première feuille. Vous pouvez simplement écrire le nom de cette cellule de plage.

L'étape suivante consiste à créer une plage nommée. La procédure est fondamentalement la même. Vous devez d'abord sélectionner une plage, puis spécifier son nom. Après cela, ce nom peut être utilisé dans toutes les autres opérations de données dans Excel. Par exemple, les plages nommées sont souvent utilisées pour définir la somme des valeurs.

De plus, il est possible de créer une plage nommée à l'aide de l'onglet Formules à l'aide de l'outil Définir le nom. Après l'avoir sélectionné, une fenêtre apparaîtra dans laquelle nous devrons choisir un nom pour notre gamme, ainsi que spécifier la zone à laquelle elle s'étendra manuellement. Vous pouvez également spécifier où cette plage fonctionnera : dans une seule feuille ou dans tout le livre.

Si une plage de noms a déjà été créée, pour l'utiliser, il existe un service spécial appelé gestionnaire de noms. Il permet non seulement de modifier ou d'ajouter de nouveaux noms, mais aussi de les supprimer s'ils ne sont plus nécessaires.

Il convient de garder à l'esprit que lors de l'utilisation de plages nommées dans des formules, puis après leur suppression, les formules ne seront pas automatiquement remplacées par les valeurs correctes. Par conséquent, des erreurs peuvent se produire. Par conséquent, avant de supprimer une plage nommée, vous devez vous assurer qu'elle n'est utilisée dans aucune des formules.

Une autre façon de créer une plage nommée consiste à l'obtenir à partir d'une table. Pour ce faire, il existe un outil spécial appelé "Créer à partir de la sélection". Comme nous le comprenons, pour l'utiliser, vous devez d'abord sélectionner la plage que nous allons modifier, puis définir l'endroit où nous avons les en-têtes. En conséquence, sur la base de ces données, Excel traitera automatiquement toutes les données et les titres seront automatiquement attribués.

Si le titre contient plusieurs mots, Excel les séparera automatiquement par un trait de soulignement.

Ainsi, nous avons compris comment créer des plages nommées dynamiques et comment elles vous permettent d'automatiser le travail avec de grandes quantités de données. Comme vous pouvez le voir, il suffit d'utiliser plusieurs fonctions et les outils de programme intégrés à la fonctionnalité. Il n'y a rien de compliqué du tout, même si cela peut sembler être le cas pour un débutant à première vue.