Les noms de plages dans l’analyse des données commerciales avec Microsoft excel

■ Je veux les ventes totales en Arizona, en Californie, au Montana, à New York et au New Jersey. Puis-je utiliser une formule pour calculer le total des ventes sous une forme telle que AZ + CA + MT + NY + NJ au lieu de SOMME (A21:A25) et toujours obtenir la bonne réponse?

■ Que fait une formule telle que Moyenne (A:A)?

■ Quelle est la différence entre un nom avec une portée de classeur et un avec une portée de feuille de calcul?

■ J’apprécie vraiment les noms de plage. J’ai commencé à définir des noms de plage pour de nombreux classeurs que j’ai développés au bureau. Cependant, les noms de plage n’apparaissent pas dans mes formules. Comment faire apparaître des noms de plage récemment créés dans des formules créées précédemment?

■ Comment puis-je coller une liste de tous les noms de plage (et les cellules qu’ils représentent) dans ma feuille de calcul?

■ Je calcule les revenus annuels projetés comme un multiple des revenus de l’an dernier. Existe-t-il un moyen de faire ressembler la formule (1 + croissance) * l’an dernier?

■ Pour chaque jour de la semaine, nous recevons le salaire horaire et les heures travaillées. Peut-on calculer le salaire total pour chaque jour avec la formule salaire * heures?

Vous avez probablement travaillé avec des feuilles de calcul qui utilisent des formules telles que SOMME (A5000: A5049). Ensuite, vous devez découvrir ce qui est contenu dans les cellules A5000:A5049. Si les cellules A5000: A5049 contiennent des ventes dans chaque État américain, la formule SOMME (USAVentes) ne serait-elle pas plus facile à comprendre? Ici, vous apprendrez à nommer des cellules individuelles ou des plages de cellules et à utiliser des noms de plage dans les formules.

Comment créer des plages nommées?

Il existe trois façons de créer des plages nommées:

■ En saisissant un nom de plage dans la zone Nom

■ En cliquant sur Créer à partir de la sélection dans le groupe Noms définis de l’onglet Formules

■ En cliquant sur Gestionnaire de noms ou Définir un nom dans le groupe Noms définis de l’onglet Formules

Utilisation de la zone Nom pour créer un nom de plage

La zone Nom (illustrée à la figure 1) est située directement au-dessus du libellé de la colonne A. (Pour afficher la zone Nom, cliquez sur la barre de formule.) Pour créer un nom de plage dans la zone Nom, sélectionnez simplement la cellule ou la plage des cellules que vous souhaitez nommer, cliquez dans la zone Nom, puis tapez le nom de plage que vous souhaitez utiliser. Appuyez sur Entrée et vous avez créé le nom de la plage. Cliquez sur la flèche déroulante dans la zone Nom pour afficher les noms de plage définis dans le classeur actuel. Vous pouvez afficher tous les noms de plage dans un classeur en appuyant sur la touche F3 pour ouvrir la boîte de dialogue Coller le nom. Lorsque vous sélectionnez un nom de plage dans la zone Nom, Microsoft Excel sélectionne les cellules correspondant à ce nom de plage. Cela vous permet de vérifier que vous avez choisi la cellule ou la plage que vous souhaitez nommer.

Figure 1 Vous pouvez créer un nom de plage en sélectionnant la plage de cellules que vous souhaitez nommer, puis en tapant le nom de la plage dans la zone Nom.

Par exemple, supposons que vous souhaitiez nommer la cellule F3 “Est” et la cellule F4 “Ouest”. Voir la Figure 2 et. Sélectionnez la cellule F3, tapez est dans la zone Nom, puis appuyez sur Entrée. Sélectionnez la cellule F4, tapez ouest dans la zone Nom et appuyez sur Entrée. Si vous référencez maintenant la cellule F3 dans une autre cellule, vous voyez = est au lieu de = F3. Cela signifie que chaque fois que vous voyez la référence à l’est dans une formule, Excel insère tout ce qui se trouve dans la cellule F3.

Figure 2: Nommez la cellule F3 est et la cellule F4 ouest.

Supposons que vous souhaitiez affecter une plage rectangulaire de cellules (telles que A1: B4) au nom Données.

Sélectionnez la plage de cellules A1: B4, saisissez Données dans la zone Nom et appuyez sur Entrée. Maintenant, une formule telle que = MOYENNE (Données) ferait la moyenne du contenu des cellules A1:B4. Voir le fichier  et la figure ci-dessus.

 

Parfois, vous souhaitez nommer une plage de cellules composée de plusieurs plages rectangulaires non contiguës. Par exemple, dans la figure   ci-dessous, vous pouvez attribuer le nom noncontig à la plage composée des cellules B3:C4, E6:G7 et B10:C10. Pour attribuer ce nom, sélectionnez l’un des trois rectangles constituant la plage (B3:C4 pour l’instant). Maintenez la touche Ctrl enfoncée, puis sélectionnez les deux autres plages (E6:G7 et B10:C10). Relâchez maintenant la touche Ctrl, saisissez le nom noncontig dans la zone Nom et appuyez sur Entrée. L’utilisation de noncontig dans n’importe quelle formule fera désormais référence au contenu des cellules B3: C4, E6:G7 et B10:C10. Par exemple, entrer la formule = MOYENNE (noncontig) dans la cellule E11 donne 4,75 (car les 12 nombres de votre plage totalisent 57 et 57/12 = 4,75).

 

Création de plages nommées en utilisant “Depuis sélection”

La feuille de calcul suivante contient les ventes en mars pour chacun des 50 États américains. La figure ci-dessous montre un sous-ensemble de ces données. Vous souhaitez nommer chaque cellule de la plage B6:B55 avec l’abréviation d’Etat. Pour ce faire, sélectionnez la plage A6:B55 et cliquez sur “Dépuis sélection” dans le groupe “Noms définis” de l’onglet Formules, puis cochez la case Colonne de gauche, comme indiqué dans la Figure .

En nommant les cellules qui contiennent des ventes d’état avec des abréviations d’Etat, vous pouvez utiliser l’abréviation plutôt que la lettre de colonne de la cellule et le numéro de ligne lorsque vous vous référez à la cellule.

Figure: Sélectionnez Créer à partir de la sélection.

Figure: Cochez la case Colonne de gauche.

Excel sait désormais associer les noms de la première colonne de la plage sélectionnée aux cellules de la deuxième colonne de la plage sélectionnée. Ainsi, B6 se voit attribuer le nom de plage AL, B7 est nommé AK, etc. La création de ces noms de plage dans la zone Nom aurait été incroyablement fastidieuse! Cliquez sur la flèche déroulante dans la zone Nom pour vérifier que ces noms de plage ont été créés.

Création de noms de plage à l’aide de “Définir un nom”

Si vous choisissez Gestionnaire de noms dans l’onglet Formules et sélectionnez Définir un nom dans le menu illustré à la figure ci-dessous, la boîte de dialogue Nouveau nom illustrée à la figure suivante s’ouvre.

Supposons que vous souhaitiez affecter la plage de noms à la plage de cellules A2:B7. Tapez rangel dans la zone Nom, puis pointez sur la plage ou tapez = A2:B7 dans la zone Fait référence à. La boîte de dialogue Nouveau nom ressemblera maintenant à la figure ci-dessous. Cliquez sur OK et vous avez terminé.

FIGURE La boîte de dialogue Nouveau nom ressemble à ceci après avoir créé un nom de plage.

Si vous cliquez sur la flèche Étendue, vous pouvez sélectionner Classeur ou n’importe quelle feuille de calcul dans votre classeur. Cette décision est discutée plus loin dans ce chapitre, donc pour l’instant, choisissez simplement la portée par défaut de classeur. Vous pouvez également ajouter des commentaires pour l’un des noms de vos plages.

Gestionnaire de noms

Si vous cliquez maintenant sur la flèche Nom, la plage de noms (et toute autre plage que vous avez créée) apparaît dans la zone Nom. Dans Excel, il existe un moyen simple de modifier ou de supprimer vos noms de plage. Ouvrez le Gestionnaire de noms en sélectionnant l’onglet Formules, puis cliquez sur Gestionnaire de noms dans le menu illustré à la figure précédente. Vous voyez maintenant une liste de tous les noms de plage. Par exemple, pour le fichier , la boîte de dialogue Gestionnaire de noms ressemblera à la figure ci-dessous.

Pour modifier un nom de plage, double-cliquez sur le nom de la plage ou sélectionnez le nom de la plage et cliquez sur Modifier; vous pouvez ensuite modifier le nom de la plage, les cellules auxquelles la plage fait référence ou l’étendue de la plage.

Pour supprimer un sous-ensemble de noms de plage, sélectionnez d’abord les noms de plage que vous souhaitez supprimer. Si les noms de plage sont répertoriés consécutivement, sélectionnez le premier nom de plage dans le groupe que vous souhaitez supprimer, maintenez la touche Maj enfoncée et sélectionnez le dernier nom de plage dans le groupe. Si les noms de plage ne sont pas répertoriés consécutivement, vous pouvez sélectionner n’importe quel nom de plage que vous souhaitez supprimer, puis maintenir la touche Ctrl enfoncée pendant que vous sélectionnez les autres noms de plage à supprimer. Appuyez ensuite sur la touche Supprimer pour supprimer les noms de plage sélectionnés.

Regardez maintenant quelques exemples spécifiques de la façon d’utiliser les noms de plage.

FIGURE : Il s’agit de la boîte de dialogue Gestionnaire de noms.

Je veux les ventes totales en Arizona, en Californie, au Montana, à New York et au New Jersey. Puis-je utiliser une formule pour calculer le total des ventes sous une forme telle que AZ + CA + MT + NY + NJ au lieu de SOMME (A21: A25) et toujours obtenir la bonne réponse?

Revenez à l’image , dans lequel vous avez attribué l’abréviation de chaque état comme nom de plage pour les ventes de l’état. Si vous souhaitez calculer les ventes totales en Alabama, en Alaska, en Arizona et en Arkansas, vous pouvez clairement utiliser la formule SOMME (B6:B9). Vous pouvez également pointer vers les cellules B6, B7, B8 et B9, et la formule sera entrée comme “= AL + AK + AZ + AR”. Cette dernière formule se décrit d’elle-même.

Le tableau suivant qui contient les pourcentages de rendement annuels sur les actions, les bons du Trésor et les obligations. (Certaines lignes sont masquées dans cette figure; les données se terminent à la ligne 89.)

FIGURE: Cette figure montre les données d’investissement historiques.

Sélectionnez la plage de cellules B8:D89, puis choisissez Formules et Créer à partir de la sélection. Pour cet exemple, les noms sont créés dans la ligne supérieure de la plage. La plage B8:B89 s’appelle Stocks, la plage C8:C89 T.Bills et la plage D8:D89 T.Bonds. Désormais, vous n’avez plus besoin de vous rappeler où se trouvent vos données. Par exemple, dans la cellule B91, après avoir tapé = MOYENNE (, vous pouvez appuyer sur F3 et la boîte de dialogue Coller le nom s’ouvre, comme illustré à la figure. Vous pouvez également afficher une liste de noms de plage pouvant être collés dans des formules si vous commencez à taper et cliquez sur Utiliser dans la formule sur le  l’onglet formules.

Vous pouvez sélectionner Stocks dans la liste Coller le nom et cliquez sur OK. Après avoir entré la parenthèse fermante, la formule = MOYENNE (Stocks) calcule le rendement moyen des actions (11,28%). La beauté de cette approche est que même si vous ne vous souvenez pas où se trouvent les données, vous pouvez travailler avec les données de retour de stock n’importe où dans le classeur!

Si vous commencez à taper = Moyenne (T…), Excel vous montre une liste de noms de plage et de fonctions commençant par T, et vous pouvez simplement double-cliquer sur T.Bills pour compléter la saisie du nom de plage.

Que fait une formule telle que Moyenne (A: A)?

Si vous utilisez un nom de colonne (sous la forme A:A, C:C, etc.) dans une formule, Excel traite une colonne entière comme une plage nommée. Par exemple, la saisie de la formule = MOYENNE (A:A) fait la moyenne de tous les nombres de la colonne A. L’utilisation d’un nom de plage pour une colonne entière est très utile si vous entrez fréquemment de nouvelles données dans une colonne. Par exemple, si la colonne A contient les ventes mensuelles d’un produit, à mesure que de nouvelles données sur les ventes sont saisies chaque mois, votre formule calcule une moyenne des ventes mensuelles à jour. Soyez prudent, cependant, car si vous entrez la formule = MOYENNE (A:A) dans la colonne A, vous obtiendrez un message de référence circulaire car la valeur de la cellule contenant la formule moyenne dépend de la cellule contenant la moyenne. De même, la saisie de la formule = MOYENNE (1: 1) fait la moyenne de tous les nombres de la ligne 1.

Quelle est la différence entre un nom de plage avec une portée de classeur et un nom de plage avec une portée de feuille de calcul?

Le fichier  vous aidera à comprendre la différence entre les noms de plage qui ont une portée de classeur et les noms de plage qui ont une portée de feuille de calcul. Lorsque vous créez des noms avec la zone Nom, les noms correspondent par défaut à la portée du classeur. Par exemple, supposons que vous utilisez la zone Nom pour attribuer les Ventes comme nom à la plage de cellules E4:E6 dans la feuille Sheet3, et ces cellules contiennent les nombres 1, 2 et 4, respectivement. Si vous entrez une formule telle que = SOMME (Ventes) dans n’importe quelle feuille de calcul, vous obtenez une réponse de 7 car la zone Nom crée des noms avec la portée du classeur, donc n’importe où dans le classeur vous faites référence au nom Ventes (qui a la portée du classeur), le nom fait référence aux cellules E4:E6 de la feuille Sheet3. Dans n’importe quelle feuille de calcul, si vous entrez maintenant la formule = SOMME (Ventes), vous obtiendrez 7 car n’importe où dans le classeur, Excel lie les Ventes aux cellules E4:E6 de Feuille Sheet3.

Supposons maintenant que vous tapez 4, 5 et 6 dans les cellules E4:E6 de la feuille Sheet1 et 3, 4 et 5 dans les cellules E4: E6 de la feuille Sheet2, puis que vous ouvrez le Gestionnaire de noms, attribuez le nom jam aux cellules E4: E6 de Sheet1 et définissez la portée de ce nom comme Sheet1. Vous passez ensuite à Sheet2, ouvrez le Gestionnaire de noms, attribuez le nom aux cellules E4: E6 et définissez la portée de ce nom en tant que Sheet2. La boîte de dialogue Gestionnaire de noms ressemble maintenant à la figure .

FIGURE: La boîte de dialogue Gestionnaire de noms affiche les noms des feuilles de calcul et des classeurs.

Maintenant, que se passe-t-il si vous entrez la formule = SOMME (jam) dans chaque feuille? Dans la feuille Sheet1, = SOMME (jam) totalisera les cellules E4: E6 de la feuille Sheet1. Étant donné que ces cellules contiennent 4, 5 et 6, vous obtenez 15. Dans Sheet2, = SOMME (jam) totalisera les cellules E4: E6 de Sheet2, ce qui donnera 3 + 4 + 5 = 12. Dans Sheet3, cependant, le = SOMME (jam) donnera une erreur #NAME?  car aucune plage n’est nommée jam dans Feuille3. Si vous entrez la formule = SOMME (Sheet2!Jam) n’importe où dans Sheet3, Excel reconnaîtra le nom de niveau feuille de calcul qui représente la plage de cellules E4: E6 de Sheet2 et produira un résultat de 3 + 4 + 5 = 12. Ainsi, en faisant précéder un nom de niveau feuille de calcul par son nom de feuille suivi d’un point d’exclamation (!), vous pouvez faire référence à une plage de niveau feuille de calcul dans une feuille de calcul autre que la feuille dans laquelle la plage est définie.

J’apprécie vraiment les noms de plage. J’ai commencé à définir des noms de plage pour de nombreux classeurs que j’ai développés au bureau. Cependant, les noms de plage n’apparaissent pas dans mes formules. Comment faire apparaître des noms de plage récemment créés dans des formules créées précédemment?

Regardez la figure ci-dessous.

FIGURE: Cette figure montre comment appliquer des noms de plage aux formules.

Le prix d’un produit a été entré dans la cellule F3 et la demande de produit = 10000-300 * F3 dans la cellule F4. Le coût unitaire et le coût fixe sont entrés dans les cellules F5 et F6, respectivement, et le bénéfice est calculé dans la cellule F7 avec la formule = F4 * (F3-F5) -F6. Dans cet exemple, les formules, Créer à partir de la sélection, puis la colonne de gauche sont utilisées pour nommer le prix de la cellule F3, la demande de la cellule F4, le coût unitaire de la cellule F5, le coût fixe de la cellule F6 et le bénéfice de la cellule F7. Vous souhaitez que ces noms de plage apparaissent dans les formules des cellules F4 et F7. Pour appliquer les noms de plage, sélectionnez d’abord la plage à laquelle vous souhaitez appliquer les noms de plage (dans ce cas, F4: F7). Ouvrez maintenant le groupe Noms définis sous l’onglet Formules, cliquez sur la flèche Définir le nom, puis choisissez Appliquer les noms. Mettez en surbrillance les noms que vous souhaitez appliquer, puis cliquez sur OK. Notez que la cellule F4 contient désormais la formule de prix = 10000-300 * et que la cellule F7 contient la formule = demande * (prix-unité_coût) – coût fixe, comme  tu voulais.

Soit dit en passant, si vous souhaitez que les noms de plage s’appliquent à la feuille de calcul entière, sélectionnez la feuille de calcul entière en cliquant sur le bouton Sélectionner tout à l’intersection des en-têtes de colonne et de ligne.

Comment puis-je coller une liste de tous les noms de plage (et les cellules qu’ils représentent) dans ma feuille de calcul?

Appuyez sur F3 pour ouvrir la zone Coller le nom, puis cliquez sur le bouton Coller la liste. (Voir la figure 12.) Une liste de noms de plage et les cellules auxquelles chacun correspond sera collée dans votre feuille de calcul, en commençant à l’emplacement actuel de la cellule.

Je calcule les revenus annuels projetés comme un multiple des revenus de l’an dernier. Existe-t-il un moyen de faire ressembler la formule (1 + croissance) * l’an dernier?

Comme le montre la figure suivante, vous souhaitez calculer les revenus pour 2012-2018 qui augmentent de 10% par an sur un niveau de base de 300 millions de dollars en 2011.

FIGURE: Créez un nom de plage pour l’année précédente.

Pour commencer, utilisez la zone Nom pour nommer la croissance de la cellule B3. Maintenant vient la bonne partie! Déplacez le curseur sur B7 et ouvrez la boîte de dialogue Nouveau nom en cliquant sur Définir le nom dans le groupe Noms définis sous l’onglet Formules. Remplissez la boîte de dialogue Nouveau nom, comme illustré à la figure .

FIGURE Dans n’importe quelle cellule, ce nom fait référence à la cellule au-dessus de la cellule active.

Étant donné que vous êtes dans la cellule B7, Excel interprète toujours ce nom de plage pour faire référence à la cellule au-dessus de la cellule actuelle. Cela ne fonctionnerait pas si, dans la référence de cellule B6, les 6 étaient signés en dollars parce que la signature en dollars des 6 empêcherait la référence de ligne de changer pour récupérer la ligne directement au-dessus de la cellule active. Si vous entrez la formule = précédente * (1 + croissance) dans la cellule B7 et copiez-la dans le B8: B13

plage, chaque cellule contiendra la formule souhaitée et multipliera 1,1 par le contenu de la cellule directement au-dessus de la cellule active.

Pour chaque jour de la semaine, on nous donne le salaire horaire et les heures travaillées. Peut-on calculer le salaire total pour chaque jour avec la formule salaire * heures?

Comme le montre la figure la ligne 12 contient les taux de salaire quotidiens et la ligne 13 contient les heures travaillées chaque jour.

FIGURE  Dans n’importe quelle cellule, ce nom fait référence à la cellule au-dessus de la cellule active.

Vous pouvez sélectionner la ligne 12 (en cliquant sur le 12) et utiliser la zone Nom pour saisir le nom salaire. Sélectionnez la ligne 13 et saisissez le nom heures dans la zone Nom. Si vous entrez maintenant la formule de salaire * heures dans la cellule F14 et copiez cette formule dans la plage G14: L14, vous pouvez voir qu’Excel trouve les valeurs de salaire et d’heures et les multiplie dans chaque colonne.

Remarques

■ Excel ne vous permet pas d’utiliser les lettres r et c comme noms de plage.

■ Les seuls symboles autorisés dans les noms de plage sont les points (.) Et les traits de soulignement (_).

■ Si vous utilisez Créer à partir de la sélection pour créer un nom de plage et que votre nom contient des espaces,

Excel insère un trait de soulignement (_) pour remplir les espaces. Par exemple, le produit 1 est créé en tant que produit_1.

■ Les noms de plage ne peuvent pas commencer par des nombres ou ressembler à une référence de cellule. Par exemple, 3Q et A4 ne sont pas autorisés comme noms de plage. Étant donné qu’Excel 2013 compte plus de 16 000 colonnes, un nom de plage tel que cat1 n’est pas autorisé car il existe une cellule CAT1. Si vous essayez de nommer une cellule CAT1, Excel vous indique que le nom n’est pas valide. Votre meilleure alternative est probablement de nommer la cellule cat1_.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x
()
x