Bouton tournant, barre de défilement dans Microsoft Excel

■■ Je dois exécuter une analyse de sensibilité qui comporte de nombreux éléments clés, tels que les ventes de l’année 1, la croissance des ventes annuelles, le prix de l’année 1 et le coût unitaire. Comment puis-je faire varier rapidement ces entrées et voir l’effet de la variation sur le calcul de la valeur actuelle nette, par exemple?

■■ Comment puis-je configurer une simple case à cocher qui active et désactive le formatage conditionnel?

■■ Comment puis-je configurer ma feuille de calcul afin que mon personnel de la chaîne d’approvisionnement puisse cliquer sur un bouton pour choisir si nous facturons un prix élevé, bas ou moyen pour un produit?

■■ Comment puis-je créer un moyen simple pour un utilisateur de ma feuille de calcul d’entrer un jour de la semaine sans avoir à taper de texte?

Les formulaires utilisateur permettent aux personnes qui utilisent Microsoft Excel d’ajouter une variété de contrôles utiles à une feuille de calcul. Dans ce chapitre, vous verrez à quel point il est facile d’utiliser des boutons rotatifs, des barres de défilement, des cases à cocher, des boutons d’option, des zones de liste déroulante et des zones de liste. Pour accéder aux formulaires utilisateur Excel, cliquez sur l’onglet Développeur du ruban et choisissez Insérer dans le groupe Contrôles pour afficher les contrôles de formulaires (à ne pas confondre avec les contrôles ActiveX, qui sont généralement utilisés dans le langage de programmation Microsoft Visual Basic pour Applications [VBA]). ).

Remarque Pour afficher l’onglet Développeur, cliquez sur l’onglet Fichier, puis choisissez Options. Choisir

Personnalisez le ruban, puis sélectionnez Développeur dans la liste des onglets principaux.

Les formulaires utilisateur abordés dans ce chapitre sont illustrés dans les figures 1 à 6. (Voir aussi le

Fichier  dans le dossier Fichiers d’exercices de cet article.)

FIGURE 1: Il s’agit du bouton rotatif.

 FIGURE 2: Voici la barre de défilement.

FIGURE 3 :Il s’agit d’une case à cocher.

FIGURE 4 :Il s’agit d’un bouton d’option.

FIGURE 5 : Il s’agit d’une zone de liste déroulante.

FIGURE 6 : Il s’agit d’une zone de liste.

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début de cet article.

Tout d’abord, voyez comment utiliser les boutons rotatifs et les barres de défilement.

Comme indiqué à l’article 18, «Utilisation du gestionnaire de scénarios pour l’analyse de sensibilité», vous utilisez le gestionnaire de scénarios pour modifier un groupe de cellules d’entrée afin de voir comment les différentes sorties changent. Malheureusement, le gestionnaire de scénarios vous oblige à saisir chaque scénario individuellement, ce qui rend difficile la création de plusieurs scénarios. Par exemple, supposons que vous pensez que les quatre entrées clés du modèle de valeur actuelle nette (VAN) de la voiture sont les ventes de l’année 1, la croissance des ventes, le prix de l’année 1 et le coût de l’année 1. (Voir le fichier .) Vous aimeriez voir comment NPV change à mesure que les entrées changent dans ce qui suit gammes:

L’utilisation du gestionnaire de scénarios pour générer les scénarios dans lesquels les cellules d’entrée varient dans les plages données prendrait beaucoup de temps. En utilisant des boutons rotatifs, cependant, un utilisateur peut générer rapidement une multitude de scénarios qui varient chaque entrée entre sa valeur basse et sa valeur haute. Un bouton rotatif est un contrôle lié à une cellule spécifique. Lorsque vous cliquez sur la flèche vers le haut ou vers le bas du bouton de rotation, la valeur de la cellule liée change. Vous pouvez voir comment les formules d’intérêt (comme celle qui calcule la VAN d’une voiture) changent en réponse aux modifications des entrées.

Je dois exécuter une analyse de sensibilité qui comporte de nombreux éléments clés, tels que les ventes de l’année 1, la croissance des ventes annuelles, le prix de l’année 1 et le coût unitaire de l’année 1. Comment puis-je faire varier rapidement ces entrées et voir l’effet de la variation sur le calcul de la valeur actuelle nette, par exemple?

Voici comment créer des boutons rotatifs permettant de faire varier les ventes de l’année 1, la croissance des ventes, le prix de l’année 1 et le coût de l’année 1 dans les plages souhaitées. La feuille de calcul Modèle d’origine (voir le fichier  dans le dossier Modèles) est illustrée à la figure .

FIGURE  :Cette figure montre la feuille de calcul du modèle original sans aucun bouton rotatif.

Pour créer les boutons rotatifs, sélectionnez les lignes dans lesquelles vous souhaitez insérer des boutons tournants (dans cet exemple, les lignes 2 à 5), puis augmentez la hauteur des lignes en cliquant avec le bouton droit et en sélectionnant Format, Ligne et Hauteur. Une hauteur de ligne de 27 est généralement suffisante pour accueillir les flèches du bouton de la flèche. Vous pouvez également maintenir la touche Alt enfoncée pendant que vous dessinez le contrôle afin qu’il s’adapte à la cellule.

Affichez le menu Formulaires utilisateur en cliquant sur Insérer dans le groupe Contrôles de l’onglet Développeur. Cliquez sur le contrôle de formulaire du bouton spinner (illustré à la figure 1) et faites-le glisser à l’endroit où vous souhaitez qu’il apparaisse dans votre feuille de calcul (cellule D2). Vous voyez un signe plus (+). En appuyant sur les ancres du bouton gauche de la souris le bouton rotatif où vous le souhaitez, et vous pouvez dessiner la forme du bouton rotatif. Pour modifier la forme d’un contrôle de formulaire ou pour déplacer le contrôle, placez le curseur sur le contrôle et maintenez la touche Ctrl enfoncée. Lorsque le pointeur se transforme en flèche à quatre têtes, faites glisser le contrôle pour le déplacer. Lorsque le pointeur apparaît sous la forme d’une flèche à deux têtes, vous pouvez faire glisserle contrôle pour le redimensionner.

Un bouton rotatif apparaît maintenant dans la cellule D2. Après avoir configuré le bouton spinner, vous pouvez l’utiliser pour modifier la valeur des ventes de l’année 1. Cliquez avec le bouton droit sur le bouton de rotation et, dans le menu contextuel, cliquez sur Copier. Cliquez avec le bouton droit sur la cellule D3, puis cliquez sur Coller. Collez également le bouton rotatif dans les cellules D4 et D5. Vous devriez maintenant voir quatre boutons rotatifs comme illustré à la figure  ci-dessous.

FIGURE 7 Les boutons Spinner sont placés dans les cellules de la feuille de calcul.

Vous devez maintenant lier chaque bouton de spinner à une cellule d’entrée. Pour lier le bouton de rotation dans D2 à la cellule C2, cliquez avec le bouton droit sur le bouton de rotation dans la cellule D2, puis cliquez sur Contrôle du format. Remplissez la boîte de dialogue Format Control, comme illustré à la figure.

FIGURE 8 Utilisez la boîte de dialogue Format Control pour lier 1 année de ventes à un bouton rotatif.

La valeur actuelle n’est pas importante. Le reste des paramètres indique à Excel que ce bouton de rotation est lié aux valeurs de la cellule C2 (Year1sales); que chaque clic de la flèche vers le haut augmentera la valeur de C2 de 1 000; et que chaque clic de la flèche vers le bas diminuera la valeur en C2 de 1 000. Lorsque la valeur en C2 atteint 30 000, cliquer sur la flèche vers le haut ne l’augmentera pas; lorsque la valeur en C2 atteint 5 000, un clic sur la flèche vers le bas ne diminuera pas la valeur.

Utilisez la boîte de dialogue Contrôle du format pour lier le bouton de rotation dans D4 au prix Year1 (cellule C4). Pour la valeur actuelle, 9 a été utilisée. La valeur minimale est 6, la valeur maximale est 20 et la modification incrémentielle est 1. En cliquant sur les flèches du bouton rotatif dans la cellule D4, le prix Year1 varie entre 6 $ et 20 $ en Incréments de 1 $.

Pour lier le bouton de rotation dans la cellule D5 au coût de l’année 1 (cellule D5), 6 a été utilisé pour la valeur actuelle, 2 pour la valeur minimale, 15 pour la valeur maximale et 1 comme changement incrémentiel. En cliquant sur les flèches du bouton spinner dans la cellule D5, le coût de l’année 1 passe de 2 $ à 15 $ par incréments de 1 $. Il est plus difficile de lier le bouton spinner de la cellule D3 à la croissance des ventes. Vous voulez que la commande du bouton spinner modifie la croissance des ventes à 0%, 1%,. . . 50 pourcent. Le problème est que l’incrément minimum qu’une valeur de bouton de spinner peut changer est 1. Par conséquent, vous devez lier ce bouton de spinner à une valeur fictive dans la cellule E3 et placez la formule E3 / 100 dans la cellule C3. Maintenant, comme la cellule E3 varie de 1 à 50, la croissance des ventes varie entre 1% et 50%. La figure 10 montre comment lier ce spin- bouton  à la cellule E3. N’oubliez pas que la croissance des ventes dans la cellule C3 est simplement le nombre dans la cellule E3 divisé par 100.

Par ailleurs, si le curseur se trouve dans le contrôle et que vous maintenez la touche Contrôle enfoncée, vous pouvez facilement utiliser les poignées pour redimensionner un contrôle.

FIGURE 9: Il s’agit des paramètres de la boîte de dialogue Contrôle du format qui lient le bouton de rotation de la cellule D3 à la cellule E3.

En cliquant sur une flèche de bouton rotatif, vous pouvez facilement voir comment la modification d’une cellule d’entrée unique, compte tenu des valeurs des autres entrées répertoriées dans la feuille de calcul, modifie la VAN de la voiture. Pour voir l’effet des modifications, vous pouvez sélectionner la cellule F9, cliquez sur Figer les volets dans le menu Affichage, puis cliquez à nouveau sur Figer les volets. Cette commande fige les données au-dessus de la ligne 9 et à gauche de la colonne F. Vous pouvez maintenant utiliser les barres de défilement à droite de la fenêtre pour les organiser comme vous le voyez dans la figure ci-dessous.

FIGURE 10 : Vous pouvez figer les volets pour voir les résultats des calculs dans d’autres parties d’une feuille de calcul.

Compte tenu des valeurs des autres entrées, le fait de cliquer sur les flèches du bouton de croissance des ventes montre Une augmentation de 1% de la croissance des ventes vaut environ 2 000 $. (Pour ramener la feuille de calcul à son état normal, cliquez sur Figer les volets dans le menu Affichage, puis sur Dégeler les volets.) Le contrôle de la barre de défilement est très similaire au bouton spinner. La principale différence est qu’en déplaçant le curseur sur la zone grise au milieu de la barre de défilement, vous pouvez provoquer une modification continue de la valeur de la cellule liée. En sélectionnant Format Control dans le menu contextuel et en modifiant la valeur sous Page Change dans la boîte de dialogue Format Control, vous pouvez contrôler la vitesse à laquelle la cellule liée change.

Comment puis-je configurer une simple case à cocher qui active et désactive la mise en forme conditionnelle?

Une case à cocher est une forme de contrôle qui spécifie une valeur Vrai dans une cellule lorsque la case est sélectionnée et False lorsque la case est désactivée. Les cases à cocher peuvent être utilisées pour créer des interrupteurs à bascule qui fonction particulière activée ou désactivée. Par exemple, voyez comment utiliser une case à cocher pour activer ou désactiver la fonction de formatage conditionnel.

Supposons qu’une feuille de calcul contienne des ventes mensuelles et que vous souhaitiez colorer les cinq plus grandes ventes en vert et les cinq plus petites ventes en rouge. Vous entrez la formule = LARGE (Sales, 5)

dans la cellule G4, qui calcule la cinquième plus grande valeur des ventes. Dans la cellule H4, vous calculez le cinquième plus petit chiffre d’affaires

valeur avec la formule = PETITE.VALEUR (Ventes, 5). (Voir figure .)

FIGURE 11 : Utilisez une case à cocher pour activer et désactiver la mise en forme conditionnelle.

Ensuite, vous créez une case à cocher et la configurez pour entrer Vrai ou Faux dans la cellule F1. Sous l’onglet Développeur, cliquez sur Insérer et cochez la case (voir Figure ci-dessous) de a palette Forms Controls. Faites glisser la case à cocher vers la cellule G9 et modifiez son texte pour lire Activer ou désactiver le formatage. Cliquez avec le bouton droit sur la case à cocher, cliquez sur Contrôle du format et remplissez la boîte de dialogue comme illustré à la figure ci-dessous.

FIGURE  12: Il s’agit de la boîte de dialogue Format Control pour une case à cocher.

Maintenant, chaque fois que vous activez la case à cocher, Vrai est placé dans F1 et chaque fois que vous désactivez la case à cocher, False est placé dans la cellule F1.

Après avoir sélectionné la plage de cellules D4: D29, cliquez sur Mise en forme conditionnelle dans le groupe Styles sur Cliquez sur l’onglet Accueil, puis sur Nouvelle règle. Entrez les formules indiquées dans les figures ci-dessous  pour colorier les cinq premières ventes en vert et les cinq dernières ventes en rouge. Notez que la partie ET ($ F $ 1) de la formule garantit que la mise en forme peut être appliquée si la cellule F1 est Vrai. La case à cocher détermine si la cellule F1 est vraie ou fausse, donc si la case n’est pas cochée, les cellules ne deviendront pas vertes ou rouges.

Si vous le souhaitez, vous pouvez sélectionner la cellule F1 et changer la couleur de la police en blanc pour masquer Vrai et Faux.

FIGURE 13: Cette figure montre le format pour que les cinq plus grandes cellules deviennent vertes.

 FIGURE 14 : Cette figure montre le format pour virer au rouge les cinq plus petites cellules.

FIGURE 15 Cette figure montre le format pour virer au rouge les cinq plus petites cellules.
Boutons tournants, barres de défilement, boutons d’option, cases à cocher, zones de liste modifiable et zones de liste de groupe C

Comment puis-je configurer ma feuille de calcul afin que mon personnel de la chaîne d’approvisionnement puisse cliquer sur un bouton pour choisir si nous facturons un prix élevé, bas ou moyen pour un produit?

Supposons que vous puissiez facturer l’un des trois prix d’un produit: élevé, moyen ou bas. Ces prix sont répertoriés dans les cellules B7: B9 du fichier . Vous pouvez facilement utiliser une table de recherche pour imprimer le prix si un utilisateur tape Haute, Moyenne ou Basse. Cependant, il est préférable que l’utilisateur sélectionne un bouton d’option indiquant Prix élevé, Prix moyen ou Prix bas, et qu’une formule calcule automatiquement le prix. (Voir figure 16.)

Pour utiliser les boutons d’option, vous commencez par dessiner une zone de groupe (voir Figure) après avoir choisi ce contrôle dans le menu Formulaires utilisateur. Ensuite, vous faites glisser un bouton d’option pour chaque choix dans la zone de groupe. Étant donné que vous avez trois niveaux de prix, vous devez faire glisser trois boutons d’option dans la zone de groupe. Cliquez avec le bouton droit sur un bouton d’option, puis utilisez la commande Contrôle du format pour lier l’un des boutons d’option à une cellule. Le premier bouton d’option était lié à la cellule E4. Tous les boutons d’option de la zone de groupe sont désormais liés à la même cellule. La sélection du premier bouton d’option entre un 1 dans la cellule E4, la sélection du deuxième bouton d’option entre un 2 dans la cellule E4 et la sélection du troisième bouton d’option entre un 3 dans la cellule E4.

FIGURE 16 : Utilisez les boutons d’option pour sélectionner le prix du produit.

La saisie de la formule = INDEX (A7: A9, E4,1) dans la cellule E7 renvoie la description du prix correspondant au bouton d’option sélectionné. Dans la cellule F7, la formule = RECHERCHEV (E7, A7: B9,2, FAUX) calcule le prix correspondant au bouton d’option sélectionné.

Comment créer un moyen simple pour un utilisateur de ma feuille de calcul de sélectionner un jour de la semaine sans avoir à taper de texte?

Le fichier  montre comment créer une zone de liste modifiable ou une zone de liste (voir Figure ) sur laquelle un utilisateur peut cliquer pour sélectionner facilement un élément dans une liste. (La validation des données facilite également la création de listes déroulantes; voir le chapitre 40, «Validation des données».) L’objectif ici est de calculer le nombre d’heures qu’un employé a travaillé un jour donné. Les heures travaillées chaque jour sont répertoriées dans les cellules G9: G15. Vous pouvez utiliser un combo ou une zone de liste pour sélectionner une entrée dans une liste. Si la zone de liste modifiable ou la zone de liste est liée à une cellule (par Format

Contrôle), un 1 est placé dans la cellule liée si la première entrée de la case est sélectionnée; si la deuxième entrée de la liste est sélectionnée, un 2 est entré dans la cellule liée et ainsi de suite. Pour configurer la feuille de calcul, cliquez sur l’onglet Développeur, choisissez Insérer, puis faites glisser une zone de liste déroulante du menu Contrôles de formulaire vers C5 et une zone de liste vers D16. Cliquez avec le bouton droit sur la zone de liste déroulante et choisissez Contrôle du format. Sélectionnez le F9: F15 plage d’entrée (qui contient les jours de la semaine) et la cellule de liaison A8. Maintenant, cliquez avec le bouton droit sur la zone de liste et sélectionnez Contrôle du format. Sélectionnez la plage d’entrée F9: F15 et liez la cellule A13. Après ces étapes, si vous sélectionnez mardi dans la zone de liste déroulante et vendredi dans la zone de liste, par exemple, vous voyez un 2 dans A8 et un 5 dans A13.

Dans la cellule F3, la formule = INDEX (F9: F15, A8,1) répertorie le jour de la semaine correspondant à la sélection de zone de liste déroulante. Dans la cellule G3, la formule = RECHERCHEV (F3, $ F $ 9: $ G $ 15,2, Faux) localise le nombre d’heures travaillées pour le jour sélectionné dans la zone de liste déroulante. De la même manière, les formules F4 et G4 indiquent le jour de la semaine et les heures travaillées pour le jour sélectionné dans la zone de liste.

FIGURE 17: Utilisez les zones de liste déroulante et les zones de liste pour sélectionner le jour de la semaine.

Problèmes

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