excel liste déroulante

Excel: Comment créer une liste déroulante facilement

cours excelCet article a pour objectif de vous montrer comment créer facilement une liste déroulante dans Excel. Une liste déroulante permet de faciliter la saisie de données qui se répètent fréquemment tout en vous donnant la possibilité de choisir des entrées dans une liste au lieu de les taper directement. Voir la technique en vidéo

 

excel liste déroulante

En fait, une liste déroulante n’a pas uniquement l’avantage de faciliter la saisie, mais aussi elle peut jouer le rôle d’un contrôle de saisie ; c’est-à-dire d’empêcher d’autres personnes à entrer des données qui ne sont pas valides ou autorisées dans le cas où vous confier la saisie à quelqu’un d’autre.

On va voir dans un premier temps, une technique basique qu’utilise la majorité des gens, et après on va voir une technique un peu plus efficace et plus professionnelle.

Technique basique pour créer une liste déroulante :

On va considérer la petite base de données ci-dessous, composée de trois colonnes : numéro de facture, l’état de la facture (payée, encours ou annulés) et le mode de paiement (par chèque ou espèce)

Cette base de données étant crée dans la première feuille d’Excel qu’on va nommer «BD »

liste-deroulante-excel

La première chose à faire c’est d’abord de préparer la liste, donc on va préparer une liste qui va contenir les entrées de la colonne « Etats » qui sont : payée, encours et annulée.

Dans une nouvelle feuille, qu’on va nommer « Listes », taper les entrées : les modalités que peut prendre la colonne « Etat ».

Une fois la liste préparée je reviens à la base de données sur la feuille Excel appelée BD.

Il faut d’abord sélectionner la colonne dans laquelle on va saisir ces données, puis je me mets sur l’onglet Données et puis dans le groupe outils de données, je vais choisir la commande Validation de données.

liste-déroulante-excel

1-Dans la boîte de dialogue qui apparaît, je vais sur la liste de choix Autoriser, et je vais autoriser Liste,

liste-déroulante-excel

2- Dans la zone Source, cliquer, puis aller chercher la liste sur la feuille nommée « liste ». Sélectionner les entrées de la liste

3-Cliquer sur le bouton OK de la boite de dialogue.

C’est bon, comme vous l’aurez constaté une liste déroulante s’est insérée dans cette colonne, en cliquant dessus, on pourrait choisir l’une des modalités soit payée, soit annulée, soit encours.

Technique professionnelle pour créer une liste déroulante :

La première méthode qu’on a vue est la plus simple mais n’est pas la plus efficace. On va voir pourquoi :

Lorsqu’il y’aura des éventuels changements sur cette liste on aurait un petit problème, si on ajoute une autre entrée dans cette liste par exemple « Reportée », Elle ne sera pas ajoutée dans la liste.

Comment faire alors pour que cette liste soit dynamique, c’est-à-dire qu’elle puisse prendre en en compte toutes les entrées qu’on pourrait ajouter ultérieurement.

Pour faire de façon à ce que ma liste s’agrandit au fur et à mesure qu’on ajoute de nouvelles entrées, la méthode consiste à convertir cette liste en tableau.

Pour convertir une liste en tableau c’est très simple : se positionner dans une cellule dans la liste et allez soit dans l’onglet Accueil, et cliquer sur le bouton Mettre sous forme de tableau, puis choisir le style qui vous convient.

liste-deroulante-excel

Ou bien sur l’onglet Insertion, vous cliquez sur le bouton Insérer un Tableau.

Et n’oubliez pas de cocher « mon tableau comporte un entête.

liste-deroulante-excel-7

Et voilà les données se sont convertis sous forme de tableau. il ne vous reste qu’à lui donner un nom.
Comme vous voyez ici Excel lui a donné le nom « Tableau 1 » mais ce n’est pas tout à fait significatif. L’onglet Création toujours activé, Donnez-lui un nouveau nom comme par exemple « liste ».

liste-deroulante-excel-8

Voilà maintenant il ne me reste qu’à appeler ce tableau qui sera considéré comme source de la liste déroulante.

De la même façon avec laquelle on a créé notre première liste :

  1. Revenir à la BD et sélectionner la colonne concernée.
  2. Sur l’onglet Données, cliquer sur le bouton validation de données;
  3. Autoriser une liste.
  4. Dans la zone « source » tapez ceci : =INDIRECT(‘’liste‘’)
  5. Validez par Ok.

Maintenant vous allez voir que la liste est affichée correctement et l’essentiel c’est qu’elle est devenue dynamique.

Vous pouvez ajouter de nouvelles entrées et tester son fonctionnement.

NB :

Si vous avez à faire une petite liste déroulante inchangeable C-à-D dont les items ne changent pas tout le temps, par exemple les titres de civilité : madame, monsieur, Mlle.

Vous n’avez pas à faire toutes les manipulations précédentes pour créer cette liste. Il faut simplement aller sur l’onglet Données puis cliquer sur le bouton Validation de données, ensuite autoriser une Liste et vous allez taper directement les items voulus séparés par un point-virgule comme ceci :  Madame; Monsieur ; Mlle.
Et le tour est joué et la liste est créée.

Comment modifier une liste déjà créée ?

Tout en étant sur une cellule de la colonne qui contient la liste déroulante, allez dans l’onglet Données puis Validation de données. Et dans la boite de dialogue qui s’ouvre, modifier ou corrigez ce que vous voulez, puis validez par Ok.

Comment supprimer une liste déjà créée ?

La question Maintenant : comment supprimer une liste dont vous n’avez plus besoin.
Tout simplement, sélectionner la colonne qui contient cette liste, et aller toujours sur l’onglet Données, puis Validation de données, et dans la boite qui s’affiche, cliquez cette fois sur le bouton Effacer tout.

liste-deroulante-excel-9

Comme ça, votre liste disparaît.

Comment supprimer une liste et aussi tout le contenu déjà entré ?

Dans l’onglet Accueil, allez sur la droite et cliquer sur la commande Effacer tout.
Comme ça vous allez effacer le contenu et la règle de validation.

liste-deroulante-excel-10

Pour créer une autre liste pour la deuxième colonne « Type » ou mode de payement, visionnez cette vidéo pour voir plus d’astuces pratiques :

calcul-age-excel

Excel: Calcul âge

excelExcel : comment calculer l’âge à partir d’une date de naissance et trouver la classe d’âge correspondante

Situation :

J’ai une liste de dates de naissance sur une colonne Excel à partir de laquelle je voudrais calculer les âges respectivement. Ensuite, dans une autre colonne et pour chaque âge de cette liste, je dois trouver la classe d’âge correspondantes.

Solution :

Première méthode : la soustraction

Il s’agit de calculer la durée écoulée entre deux dates (la date de naissance) et (la date d’aujourd’hui) ;

Prenant l’exemple ci-dessous :

Dans la cellule A2, tapez la date de naissance et dans B2, insérez la date d’aujourd’hui.

NB : Excel doit reconnaitre ces cellules entant que Date, Pour vous en assurer, regardez-en haut dans le Ruban, groupe Nombre, Format doit afficher « Date » comme illustré sur cette figure :

calcul-age-excel

Astuce : Pour insérer la date d’aujourd’hui sur Excel, tapez tout simplement Ctrl+ ; (maintenez la touche Ctrl et tapez point-virgule)

1. On calcule d’abords la différence entre les deux dates dans la cellule C2.

Pour cela, on applique une simple soustraction :

Allez dans la cellule C2 et tapez =B2-A2 (attention : on commence par la date la plus récente)

Excel nous donne la différence en nombre de jours.

2.  Pour calculer la différence en nombre d’années, on divise le nombre de jours par 365,25

(0.25 pour compenser les années bissextiles)

Dans la cellule D2, tapez =ENT(C2/365,25)

(La fonction ENT permet d’avoir un résultat en entier : pas de virgule)

Deuxième méthode : la fonction ‘DateDif’

Dans l’exemple ci-dessous, nous avons les dates de naissance dans la colonne A.

Pour calculer l’âge en nombre d’années,

Dans la cellule B2, tapez cette formule : =DATEDIF(A2;AUJOURDHUI(); »y »)

La fonction DATEDIF calcule la différence entre deux dates et implique trois arguments (la 1ère date ; la deuxième date ; le format)

La fonction AUJOURDHUI() est utilisée pour désigner la date d’aujourd’hui dans une formule.

La lettre « Y » veut dire Year en anglais (année)

Vous pouvez calculer l’âge en mois ou encore en jours en utilisant la même formule et en remplaçant le « Y » par « m » (mois) ou par « D » (jours)

calcul-age-excel1

Copiez et collez la formule dans les cellules en bas pour l’appliquer sur toute la colonne.

Maintenant, pour calculer l’âge exact, c’est-à-dire, l’âge complet (années, mois et jours), on utilise la formule suivante :

=DATEDIF(A2;AUJOURDHUI(); « y » )& « ans » &DATEDIF(A2;AUJOURDHUI(); « ym » )& « mois » &DATEDIF(A2;AUJOURDHUI(); « md » )& « jours »

Copiez et collez la formule dans les cellules en bas pour l’appliquer sur toute la colonne.

Trouver la classe (le groupe) d’âge correspondant à un âge:

Dans cet exercice, on va utiliser les mêmes données du tableaux précédent (la colonne B : âges en années)

Dans la colonne C on va calculer la borne inférieure de la classe, et dans la colonne D on va déduire la classe.

Pour calculer la borne inférieure de la classe, on divise l’âge par l’amplitude de la classe: 5 par exemple et on multiplie le résultat Entier par l’amplitude 5.

calcul-age-excel-2

Donc, la formule à taper dans la cellule C2 (borne inférieure) est la suivante : =ENT(B2/5)*5

On peut déduire la classe, dans la colonne D tapez : =C2 & C2+4

Vous pouvez améliorer l’affichage en ajoutant les crochets comme ça :

 = « [ » & C2& » – « &C2+4 & « [« 

Copiez et collez la formule dans les cellules en bas pour l’appliquer sur toute la colonne.

Télécharger le fichier d’exemple.

excel graphique secteur de secteur

Excel 2016 : Créer un graphique secteurs de secteur ou barres de secteur

excelDécouvrir la méthode la plus simple pour concevoir un graphique en double secteurs sur Excel

Situation :

J’ai créé un graphique en secteur (camembert) sur Excel pour montrer des proportions de ventes, cependant le graphique semble trop encombré. Puis-je isoler les pourcentages minimes dans une portion nommée «Autre» que je pourrais représenter dans un secteur à part.

Solution :

Un graphique en secteur (camembert) est utilisé généralement pour représenter un ensemble de variables selon leurs proportions respectivement par rapport au total général.

Un graphique Secteurs de secteur vous permet de représenter vos données dans un graphique en secteur (plus grand) avec un autre graphique en secteur (plus petit) pour regrouper de petites valeurs.  Cela vous permet :

  • Soit d’éviter l’encombrement dans un seul graphique à cause de quelques proportions plus petites
  • Soit de mettre en évidence un ensemble de modalités dans un autre graphique à côté du premier

On pourrait réaliser ce type de graphiques selon 2 manières : Secteurs de secteur ou Barres de secteurs :

excel graphique secteur de secteur

Pour créer un graphique de type Secteurs de secteur, suivez ces étapes :

  • Ouvrez Microsoft Excel et tapez les données si ce n‘est pas encore fait (vous pouvez utiliser les données de l’exemple en téléchargent notre fichier en bas de cette page)
  • Sélectionner les données, puis activer l’onglet Insérer, et allez à graphique en secteur puis choisir Secteurs de secteur (comme dans la figure)

Excel graphique secteur de secteur

Excel vous crée un graphique instantanément comme ceci :

graphique excel secteur

Pour le mettre en forme, il suffit de lui appliquer une disposition.

Le graphique étant sélectionné, allez au menu Dispositions rapides, et choisissez la première en haut par exemple.

Excel disposition de graphique

La disposition est appliquée, mais ce n’est pas fini !

Excel a isolé seulement les 2 dernières modalités dans le petit graphique, moi je voudrais mettre en évidence les trois dernières modalités (DIU, Inject et Implant) sachant que ce sont les méthodes dites de longue durée (MLD). On voudrait montrer combien représentent ces MLD par rapport à l’ensemble des méthodes.

  • Cliquez sur le petit graphique à l’aide du bouton droit de la souris
  • Dans le menu contextuel, choisissez Mettre en forme une série de données
  • Dans Valeur du second tracé, tapez 3 au lieu de 2 (voir figure)

excel graphique secteur

Et le tour est joué.

Vous pouvez aussi créer un graphique de Barres de secteur

  • Sélectionnez le graphique en double-cliquant sur son bord,
  • L’onglet Création du Ruban Excel doit être activé
  • Allez sur la commande Modifier le type de graphique
  • Puis choisissez le graphique Barres de secteur

graphique excel secteur de secteur

Votre graphie est maintenant créé.

Excel graphique barre de secteur

On dit qu’une image vaut mille mots, combien vaut alors un vidéo !

Vous pouvez suivre tous ces manipulations sur cette vidéo :

Télécharger ici le fichier d’exemples

Tutoriel en vidéo sur YouTube:

excel graphique à 2 axes

Comment créer un graphique à deux axes verticaux (deux ordonnées)

excelComment créer un graphique à deux axes verticaux (deux ordonnées)

Certes, vous savez comment faire des graphiques avec Excel à une, deux ou même à plusieurs variables
Le problème c’est que parfois l’une de vos variables possède des valeurs largement différentes des autres.Résultat ; un graphique difficile à lire!

Prenons l’exemple suivant :
Un tableau qui présente les quantités vendues et les quantités initiales de chaque produit. Avec en plus le pourcentage de vente.

Excel graphique-2-axes-1

Lorsqu’on voudrait faire un graphique représentant ces données, on obtient cela :

Excel graphique a deux axes

Comme vous pouvez le constater, la série représentant le pourcentage est presque invisible.
On va faire en sorte qu’elle soit bien apparente.

Pour cela il faut créer un autre axe des ordonnées ou sont affichés les pourcentages:

  • Sur le graphique, sélectionnez la série en question (ici en gris)
  • Cliquez avec le bouton droit de la souris et choisissez « Mettre en forme la série de données »
  • Sur le volet de droite (voir figure), cochez « Axe secondaire »

excel axe secondaire

Vous constatez maintenant la série ‘pourcentage’ possède un axe à droite.
Il ne vous reste à présent qu’à changer l’apparence de cette série.

Sélectionnez une autre fois la série en question sur le graphique puis activez le menu contextuel avec le bouton droit de la souris et choisissez « Modifier le type de graphique de cette série de données»

Excel graphique a deux axes

Dans la fenêtre qui s’affiche, allez à la troisième variable (%) et changez le type de graphique en courbe avec marque comme sur la figure et le tour est joué.

Pour plus de lisibilité, vous pouvez afficher les étiquette en cliquant sur le + vert à côté du graphique, puis cochez « Etiquettes de données »

excel graphique 2 axes

Et avec un peu de couleur vous pouvez avoir ça :

excel graphique a 2 axes verticaux

Ou même ça :

excel 2016 graphique a 2 axes

Nota benne :

A partir de la version 2013 d’Excel, le graphique à deux axes est proposé automatiquement dans l’onglet Insertion– Graphiques recommandés. Mais si vous n’êtes pas satisfait de ce qui est proposé, vous pouvez procéder comme nous avons fait ici.

> Télécharger le fichier d’exemple (excel-2axes)

La vidéo sur YouTube: