mosprepaPréparez votre certification
Microsoft Office Specialist

MS Excel 2013 et +

Utiliser des formats numériques personnalisés

Dans Excel, si on écrit dans une cellule l'expression "19 kilos" et que l'on veuille multiplier ce contenu par un nombre (le prix du kilo) pour obtenir le prix total, on reçoit un message d'erreur. Ce retour d'erreur de la part de Excel est tout à fait normal car l'écriture "19 kilos" est considérée comme du texte sur lequel on a essayé d'effectuer des opérations de calcul, ce qui n'est pas acceptable. Cependant, il y a des contextes où l'on aimerait annexer les nombres d'un texte indiquant les unités de vente sans pour autant rencontrer de message d'erreur.

La solution à ce problème, et à bien d'autres de ce type, réside dans l'utilisation des Formats des nombres. Excel propose un ensemble de formats prêts à être utilisés mais aussi donne à l'utilisateur la possibilité de créer ses propres formats.

C'est le sujet traité dans ce chapitre à travers les points qui suivent.

  1. Les différents formats prédéfinis
  2. L'analyse de l'expression d'un format Nombre
    1. Les sections d'un format
    2. Comprendre les codes
  3. La création de formats personnalisés
    1. Personnaliser les formats Nombres
    2. Personnaliser les formats Heures
    3. Personnaliser les formats Dates
    4. Personnaliser les formats Comptabilité
    5. Formats conditionnels
    6. Utilisation des couleurs
  4. Les options de l'incrémentation des listes
    1. Séries numériques
    2. Séries chronologiques
    3. Séries alphanumériques
    4. Les listes personnalisées

Recevez ce cours au format PDF en envoyant un message ici

Important

Avant de commencer il faut bien garder à l'esprit que le fait d'appliquer un format à un nombre, n'affecte en rien sa valeur. Le format n'est qu'un habillage qui change l'apparence du nombre sans toucher à sa valeur.

Les différents formats prédéfinis

Excel met à la disposition des utilisateurs des formats prêts à être utilisés. Ces formats sont disponibles dans le ruban sous l'onglet Accueil dans le groupe Nombre.

Excel, Les formats prédéfinis

Ainsi, un même nombre peut prendre des aspects différents selon le format qu'on lui applique, tout en gardant sa valeur réelle intacte qui apparait dans la barre de formule.

Excel, Les différents formats appliqués.

Encore plus de possibilités de paramétrage de formats sont accessibles via la boite de dialogue Format de cellule que l'on peut ouvrir en cliquant sur le bouton lanceur de boite de dialogue, situé en bas à droite du groupe Nombre.

Excel, La boite de dialogue pour plus de paramétrages.

Cette même boite est aussi accessible par le clic droit sur la cellule concernée puis la commande Format de cellule

Excel, Format de cellule par clic droit.

Dans l'onglet Nombre de la boite ouverte, les différents formats proposés par Excel, sont groupés en catégories : Standard, Nombre, Monétaire, Comptabilité, Date etc. En cliquant sur une catégorie, les formats qui lui sont associés sont listés dans la zone Type, il suffit d'en choisir un pour voir dans la zone Exemple un aperçu de l'effet qu'il aura sur la cellule(s) sélectionnée(s). Si le résultat est satisfaisant, on le valide par le bouton OK sinon, on choisit un autre format.

Dans le catégorie Spécial, on trouve des formats qui respectent des masques de saisie particuliers et sur lesquels on n'est pas sensé effectuer des opérations arithmétiques, tels que : les Codes postaux, les Numéros de téléphone, les Numéros de sécurité sociale etc.

Excel, Appliquer le format 'Code postal' à une cellule.

Remarquez que, bien que le format Code postal est appliqué à la cellule, avec une partie entière formée de cinq chiffres et pas de décimales, le nombre n'a pas perdu sa valeur réelle qui est affichée dans la barre de formule.

L'analyse de l'expression d'un format Nombre

Avant de pouvoir créer nos propres formats, nous devons d'abord décortiquer une expression afin de comprendre de quoi elle est faite et comprendre la signification des différents symboles utilisés. Prenons comme exemple l'écriture suivante qui correspond à un format de nombre.

Excel, Expression d'un format.

À première vue, cette expression parait être du baragouin et incompréhensible, mais allons-y pas à pas et on finira par comprendre (du moins je l'espère).

Les sections d'un format

Commençons d'abord par discerner les différentes parties constituant cette expression. Comme le montre le schéma explicatif suivant, on distingue quatre (4) sections séparées par des points-virgules (;). Chacune de ces sections indique le format que doit prendre une cellule lorsqu'elle reçoit une donnée.

Excel, Les quatre sections d'un format.

  • Section 1 : Le format que doit prendre la cellule si elle reçoit un nombre positif
  • Section 2 : Le format de la cellule en cas d'un nombre négatif
  • Section 3 : Le format si le contenu de la cellule est zéro (0)
  • Section 4 : Le format si c'est du texte

Résultat de cette expression sur le contenu des cellules auxquelles elle est appliquée :

Excel, Le résultat de cette expression sur des contenus différents.

  • Nombre positif
    • → Une espace à gauche
    • → Une espace à droite
    • → Deux chiffres après la virgule
    • → Séparateur des milliers
    • → Des espaces blanches de remplissage en cas de besoin
  • Nombre négatif
    • → Symbole moins (-) aligné à gauche de la cellule
    • → Une espace à droite
    • → Deux chiffres après la virgule
    • → Séparateur des milliers
    • → Des espaces blanches de remplissage en cas de besoin
  • Nombre nul
    • → Une espace à gauche
    • → Une espace à droite
    • → Aucun chiffre après la virgule
    • → Un tiret pour représenter le zéro (0)
    • → Des espaces de remplissage en cas de besoin
  • Texte
    • → Une espace à gauche
    • → Une espace à droite

Notez que l'effet du format défini dans la section 1 est l'effet appliqué par défaut. Je m'explique. Si le format de l'une des autres sections n'est pas exprimé franchement, alors c'est le format de la section 1 qui se manifeste dans la section manquante. De ce fait, il n'est pas obligatoire d'écrire toutes les sections.

Si une section de format est laissée vide, le contenu correspondant à cette section est masqué dans la cellule à laquelle elle est appliquée, sans qu'il soit perdu ; la preuve est que ce contenu est affiché dans la barre de formule une fois la cellule activée.

Excel, Le contenu texte de la cellule est masqué.

Comprendre les codes

L'expression d'un format numérique contient un ensemble de symboles et de caractères spéciaux ayant chacun sa signification. Dans le tableau qui suit, on trouve l'explication des symboles utilisés dans la formule précédente avec des exemples à l'appui.

SymboleDescription
0 Le zéro (0) représente des chiffres et oblige le nombre à respecter le masque. Les chiffres manquants sont remplacés par des zéros (0)
Exemple : L'écriture 000,00 signifie 3 chiffres dans la partie entière et 2 chiffres dans la partie décimale, ainsi le nombre 5 sous ce format s'affiche 005,00
# Le dièse (#) représente lui aussi des chiffres mais avec ce code les zéros (0) non significatifs ne sont pas affichés.
Exemple : Le nombre 005 au format ### s'affiche 5
_ (le tiret de 8) Le tiret de 8 (_) demande à Excel de laisser une espace qui correspond à la largeur du caractère qui le suit.
Exemple 1 : Le nombre 5 au format # _€ affiche "5 " avec une espace de la largeur du symbole € après le 5
Exemple 2 : Le nombre 12 au format _- # affiche " 12" avec une espace de la largeur du symbole "-" avant le 12
* (l'étoile) L'étoile (*) demande à Excel d'insérer le caractère qui le suit autant de fois qu'il le faut pour remplir les espaces vides jusqu'au premier chiffre du nombre.
Exemple : Le nombre 12 au format *. # affiche "......12" avec des caractères de suite (les points dans cet exemple) avant le 12
? Le point d'interrogation (?) est utilisé pour insérer une espace à la place des décimales manquantes, ce symbole est utilisé pour assurer l'alignement des nombres sur la virgule décimale
Exemple : l'expression _-* # ##0,0?_€;-* # ##0,0?_€; donne l'affichage montré dans la capture suivante :
Excel, Les nombres sont alignés sur la virgule décimale
@ L'arobase (@) représente le contenu texte de la cellule. Ce caractère n'est accepté que dans la section 4 qui correspond au contenu texte.
Exemple : l'expression _-@_- dans la section 4, donne l'affichage montré dans la capture suivante, après avoir saisi le texte.
Excel, Format Texte avec espaces
Les espaces sont matérialisées par les zones roses.
L'espace
(comme séparateur des milliers)
Une espace utilisée entre les caractères # ou les caractères 0, permet d'ajouter une espace entre les chiffres d'un nombre comme par exemple le séparateur des milliers.
Exemple : le nombre 45678 au format # ### est affiché 45 678
L'espace
(pour diviser par 1000)
Une espace utilisée à droite du dernier caractère #, ou bien à droite du dernier caractère 0 permet de diviser le nombre en affichage uniquement, par 1000 sans que le nombre perde sa valeur initiale.
Exemple : Le nombre 12345 au format 0,00 " k€" (une espace juste après le dernier 0) est affiché 12,34 k€
Deux espaces permettent de diviser par 1 000 000 et ainsi de suite

La création de formats personnalisés

Pour créer un format personnalisé on peut le construire de toutes pièces en partant de zéro, ou bien se baser sur un format préexistant et lui apporter des modifications pour l'adapter à nos besoins. Pour ce faire, on procède selon les étapes suivantes :

  1. Ouvrir la boite de dialogue Format de cellule
  2. Sous l'onglet Nombre, on choisit dans la zone Catégorie, la ligne Personnalisée
  3. Dans la zone Type, on fait l'une des opérations suivantes :
    • On efface le contenu de la zone et on saisit la formule exprimant le format à créer
    • On choisit dans la liste l'expression qui se rapproche le plus du format désiré et on apporte les modifications nécessaires
  4. Valider

Excel, Créer un format numérique personnalisé.

Personnaliser les formats Nombres

Avec ce qu'on vient de voir jusqu'ici, je pense qu'on est capable de créer les formats numériques demandés dans les exercices qui suivent :

Travail à faire 1

Créez le format numérique personnalisé montré dans la capture suivante

  • Pour les valeurs positives, le symbole + affiché et aligné à gauche de la cellule
  • Pour les valeurs négatives, le symbole - affiché et aligné à gauche de la cellule
  • Pour les valeurs nulles, le texte "nul" est affiché à la place de 0

Excel, Créer un format numérique personnalisé.

Travail à faire 2

Créez un format numérique personnalisé qui n'affiche que les valeurs strictement positives (les valeurs négatives, nulles et du texte sont masqués)

Excel, Créer un format numérique personnalisé.

Travail à faire 3

Créez un format numérique personnalisé qui affiche les nombres positifs, négatifs et nuls sur 5 chiffres avec leurs signes si la valeur n'est pas nulle.

Excel, Créer un format numérique personnalisé.

Personnaliser les formats Dates

Les dates sous Excel sont considérées comme des nombres entiers. Ces nombres représentent le nombre de jours écoulés depuis le 1er Janvier 1900, un jour correspond donc à 1 unité de temps. Ainsi, la valeur 44691 indique le nombre de jours écoulés depuis 01/01/1900 jusqu'au 10/05/2022, mais personne ne reconnaitrait ce nombre comme une Date, d'où la nécessité de lui donner un format Date. Le format d'une date longue est formé de quatre (4) parties comme le montre l'illustration suivante.

Excel, Le format Date.

  • Le nom du jour formé d'une suite de lettres j suivie d'une espace
    • jjjj (4 lettres j), pour écrire le nom complet du jour. Exemple : mercredi
    • jjj (3 lettres j), pour écrire le nom du jour en abrégé (3 lettres). Exemple : mer
  • Le numéro du jour dans le mois formé d'une suite de lettres j
    • jj (2 lettres j), pour écrire le numéro du jour sur deux chiffres. Exemple : 03
    • j (1 seule lettre j), pour écrire le numéro du jour sur un seul chiffre si c'est inférieur à 10. Exemple : 4
  • Le nom ou le numéro du mois formé d'une suite de lettres m
    • m (1 seule lettre m), pour écrire le numéro du mois sur un seul chiffre si c'est inférieur à 10. Exemple : 3 pour le mois Mars
    • mm (2 lettres m), pour écrire le numéro du mois sur deux chiffres. Exemple : 09 pour le mois Septembre
    • mmm (3 lettres m), pour écrire le nom du mois en abréviation sur trois lettres. Exemple : sep pour septembre
    • mmmm (4 lettres m), pour écrire le nom du mois en toutes lettres. Exemple : septembre
    • mmmmm (5 lettres m), pour écrire l'initiale du nom du mois (la première lettre du nom du mois). Exemple : s pour septembre
  • L'année formé d'une suite de lettres a
    • aa (2 lettres a), pour écrire l'année sur deux chiffres. Exemple : 22 pour l'année 2022
    • aaaa (4 lettres a), pour écrire l'année sur quatre chiffres. Exemple : 2022 pour l'année 2022

Les différentes parties sont séparées par des caractères tels que le slash (/), l'espace, le point (.), le tiret (-) ou autres

Le format présenté dans l'illustration précédente affiche le nombre 44691 de la manière suivante : mardi 10/mai/2022.

Notez que dans un format Date, vous pouvez utiliser uniquement la partie dont vous avez besoin et ignorer les autres.

Travail à faire 4

Créez le calendrier suivant avec les formats correspondants aux différentes cellules

Excel, Créer des formats Date personnalisés.

Personnaliser les formats Heures

Les Heures, les Minutes et les Secondes sont des fractions du jour. Ainsi 1 heure = 1/24ème du jour, soit 0,04166667 ; 1 minute est égale à 1/(24*60)ème du jour, soit 0,000694444 et 1 seconde équivaut 1/(24*60*60)ème du jour, ce qui donne 0,0000115741.

Les heures, les minutes et les secondes correspondent donc au temps écoulé depuis minuit, c'est un nombre décimal compris dans l'intervalle [0;1[. L'affichage de l'heure est régi par les formats Heures, prédéfinis par Excel ou bien créés par l'utilisateur.

Excel, Format Heure complet.

  • Le nombre d'heures formé d'une suite de lettres h suivie de deux points (:)
    • hh (2 lettres h), pour écrire les heures sur 2 chiffres. Exemple : 09 pour 9 heures
    • h (1 seul h), pour écrire l'heure sur un seul chiffre lorsque c'est inférieur à 10. Exemple : 6 pour 6 heures
  • Les minutes formées d'une suite de lettres m suivie de deux points (:)
    • mm (2 lettres m), pour écrire les minutes sur 2 chiffres. Exemple : 01 pour 1 minute
    • m (1 seul m), pour écrire les minutes sur un seul chiffre lorsque c'est inférieur à 10. Exemple : 3 pour 3 minutes
  • Les secondes formées d'une suite de lettres s
    • ss (2 lettres s), pour écrire les secondes sur 2 chiffres. Exemple : 07 pour 7 secondes
    • s (1 seul s), pour écrire les secondes sur un seul chiffre lorsque c'est inférieur à 10. Exemple : 7 pour 7 secondes
  • Les millièmes de seconde. Après les secondes, on met une virgule suivie de trois 0 pour forcer l'affichage sur 3 chiffres

En appliquant le format exprimé ci-dessus au nombre décimal 0,7535678 on obtient l'affichage suivant :

Excel, Un décimal au format Heure complet.

Utilisez AM/PM pour un affichage basé sur 12 heures au lieu de 24 heures. Ainsi au lieu de 16:51 par exemple, avec le format hh:mm AM/PM ou bien le format h:m AM/PM, vous obtiendrez le résultat illustré dans la capture suivante.

Excel, Format Heure AM/PM.

Les crochets [ ] dans un format Heure permettent d'afficher le cumule des heures, minutes et secondes écoulées.

Excel, Format Heure avec cumule.

Travail à faire 5

Le tableau ci-dessous liste les heures supplémentaires effectuées par un fonctionnaire durant les cinq premiers mois de l'années. Corrigez l'erreur d'affichage du total des heures supplémentaires dans la cellule B7, vous devriez avoir 61:20:00 au lieu de 13:20:00 !

Excel, Erreur d'affichage du total des heures supplémentaires.

Personnaliser les formats Comptabilité

L'affichage des symboles monétaires est le format le plus utilisé dans le domaine de la comptabilité, mais aussi la mise en évidence des valeurs négatives, les séparateurs de milliers pour une meilleure lisibilité des grands nombres, l'utilisation des unités de mesure ou de vente (le kg, le m, la pièce, le paquet, etc.) et l'affichage des nombres avec les multiples de l'unité par exemple afficher 1 k€ à la place de 1000€.

Ces formats d'affichage ne sont pas toujours disponibles dans Excel, ce qui nécessite de les créer par l'utilisateur.

Les symboles monétaires

Le symbole monétaire est le code qui représente la devise d'un pays ou bien d'une zone économique, tel que le symbole $ pour le dollar, le symbole pour l'euro, le symbole £ pour la livre, le MAD pour le Dirham Marocain, etc. Le symbole monétaire utilisé par défaut dans Excel, est défini dans les réglages des Paramètres régionaux de votre panneau de configuration. Vous pouvez modifier ce code dans la fenêtre Personnaliser le format représentée ci-dessous dans le panneau de configuration.

Excel, Les paramètres régionaux du panneau de configuration.

L'utilisation de ces symboles monétaires proposés par défaut est accessible dans le groupe Nombre de l'onglet Accueil via le bouton Format Nombre Comptabilité

Excel, Le bouton Format Comptabilité.

Pour choisir une devise autre que celles proposées par défaut, vous devez passer par la boite de dialogue Format de cellule dans la catégorie Monétaire ou bien Comptabilité. Dans la liste Symbole, Excel propose les devises utilisées dans différents pays, vous n'avez qu'à faire votre choix et valider.

Excel, Choisir un symbole monétaire autre que ceux proposés par défaut.

ATTENTION

Pour utiliser un symbole monétaire il faut toujours passer par un format, prédéfini ou bien personnalisé. Ne jamais saisir le code de la devise au clavier.

Le séparateur des milliers

Le séparateur des milliers facilite énormément la lisibilité des grands nombres. Le caractère de séparation est lui aussi prédéfini dans les paramètres régionaux du panneau de configuration, mais vous pouvez le personnaliser en utilisant un autre caractère. Pour ce faire, vous pouvez utiliser la boite de dialogue Format de cellule comme le montre la capture suivante

Excel, L'espace utlisée comme séparateur des milliers.

.. ou bien passer par les options avancées de Excel où vous pouvez décocher la case Utiliser les séparateurs système et définir votre séparateur personnalisé.

Excel, Modifier le séparateur des milliers.

Mettre en évidence les valeurs négatives

Dans le format Standard, les nombres négatifs sont signalés par le symbole moins (-) à gauche du nombre, mais dans le format Monétaire vous pouvez les mettre en évidence autrement comme par exemple les mettre entre parenthèses ou bien les afficher en Rouge. Cette manipulation peut se faire à travers le fenêtre Format de cellule

Excel, Mettre les valeurs négatives en rouge.

.. ou bien en passant par les paramètres régionaux du système d'exploitation dans la boite de dialogue Personnaliser le format sous l'onglet Symbole monétaire. Ouvrez la liste Format négatif et choisissez l'affichage qui vous convient puis validez.

Excel, Mettre en évidence les valeurs négatives via les paramètres régionaux.

La troisième méthode pour mettre en évidence des valeurs numériques négatives est, comme vu dans un paragraphe précédent, de personnaliser le nouveau format dans la section 2.

Excel, Les quatre sections d'un format.

Travail à faire 6

Modifiez le format suivant pour mettre les nombres strictement négatifs entre parenthèses sans laisser d'espaces vides entre le signe moins (-) et le nombre.

Excel, Modifiez ce format pour afficher les valeurs strictement négatives entre parenthèses.

Afficher un texte à côté d'un nombre

Annexer un nombre d'une unité est parfois nécessaire pour expliquer de quoi il s'agit comme c'est le cas des quantités vendues ou bien stockées d'une marchandise. Si l'ajout de ce texte explicatif est indispensable, il ne faut pas qu'il affecte la valeur réelle du nombre. Pour cela, il faut éviter de saisir les unités au clavier, il faut toujours passer par le biais d'un format personnalisé.

Insérer une unité est très facile, puisqu'il suffit d'ajouter le texte de l'unité entre guillemets juste après le dernier zéro (0) du format.

Exemple : # ##0" kg"

Pensez à insérer une espace après le premier guillemet pour ne pas voir le symbole de l'unité collé au nombre.

Notez que l'on peut mettre un texte explicatif (une unité de mesure ou autre) à droite et/ou à gauche d'un nombre comme le montre la capture suivante

Excel, Ajouter un texte explicatif.

Travail à faire 7

Créez le format personnalisé suivant contenant un texte explicatif indiquant le sens du mouvement de l'argent dans une caisse. Le texte Sortie pour un décaissement (valeur négative) et le texte Entrée pour un encaissement (valeur positive).

Excel, Ajouter un texte explicatif.

Afficher sous forme de multiples

Parfois on rencontre des difficultés à lire les grands nombres et il est plus pratique de diviser ces nombres par 1000 et de les faire suivre d'une unité multiple de mille comme K€ (Kilo euro), M€ (Méga euro), G€ (Giga euro), etc. Ce format d'affichage rend la lecture plus aisée comme le montre la capture suivante.

Excel, Ajouter deux espaces pour diviser l'affichage par 1 000 000.

Pour obtenir cet affichage, on utilise un format numérique personnalisé en ajoutant des espaces suivies de l'unité entre guillemets, juste après le dernier zéro (0) du format. À chaque espace ajoutée, l'affichage est divisé par mille sans que le nombre perde sa valeur initiale.

Excel, Ajouter deux espaces pour diviser l'affichage par 1 000 000.

Pour l'exemple de la capture, remarquez les points suivants :

  • Deux espaces utilisées, par conséquent l'affichage est divisé deux fois par 1000 (soit par 1 000 000)
  • L'unité utilisée est le M€ (1 000 000 €)
  • Seul l'affichage est concerné par le format, la valeur réelle du nombre est restée intacte comme le montre la barre de formule

Formats conditionnels

Le fait d'exprimer les quatre sections d'un format, c'est déjà l'application de formats conditionnels puisque le format de chacune de ces sections est appliqué uniquement si une condition est réalisée.

  • Le format de la section 1 pour les nombres strictement positifs
  • Le format de la section 2 pour les nombres strictement négatifs
  • Le format de la section 3 pour les valeurs nulles
  • Le format de la section 4 pour le contenu texte

Ces expressions, on peut les perfectionner pour cibler des valeurs numériques qui répondent à une condition encore plus précise. Par exemple, on voudrait diviser par 1OOO les valeurs qui sont supérieures à 1000 et les faire suivre de l'unité K€, sinon les valeurs sont suivies de l'unité comme c'est montré dans l'illustration suivante.

Excel, Format conditionnel.

Dans les formats, les conditions sont entourées de deux crochets [ ] et les opérateurs de comparaison utilisés sont les symboles habituels (<, >, <=, >=, =, etc.).

Excel, Format conditionnel.

On peut utiliser plusieurs conditions dans un format, une condition pour chaque section selon la syntaxe suivante :

Excel, Format à plusieurs conditions.

Travail à faire 8

Créez le format personnalisé avec conditions multiples de la colonne D pour afficher les CA>=1 000 000 en M€, les CA>=1 000 en K€ et les CA<1 000 en .

Excel, Format à plusieurs conditions.

Utilisation des couleurs

Vous pouvez aussi ressortir des valeurs en utilisant des formats personnalisés en couleur. La palette avec ces 56 couleurs n'est pas très riche, mais c'est assez pour mettre en évidence des valeurs numériques qui répondent à une certaine condition. Dans la syntaxe du format personnalisé, vous pouvez utiliser les noms des couleurs standards ou bien leurs indices qui peuvent aller de 1 jusqu'à 56. Que vous utilisez le nom ou l'indice d'une couleur, vous devez toujours le mettre entre crochets [ ].

Exemple, la syntaxe suivante met les valeurs strictement positives en Vert et les valeurs strictement négatives en Rouge.

Excel, Format les valeurs positives en vert et les valeurs négatives en rouge.

Comme c'est dit auparavant, au lieu des noms des couleurs vous pouvez utiliser leurs indices. Le tableau suivant donne les indices des 56 couleurs supportées par les formats personnalisés de Excel.

Excel, Les indices des couleurs

Le format personnalisé suivant utilise la couleur d'indice 43 pour les valeurs positives et la couleur d'indice 44 pour les valeurs négatives. Le format de la valeur nulle est resté standard.

Excel, Format avec des indices de couleurs

Travail à faire 9

Créez le format personnalisé de la capture suivante, Les valeurs positives en vert, et les valeurs négatives en rouge, entre parenthèses et sans le signe moins(-).

Excel Format, Positif en vert, Négatif en rouge, entre parenthèses et sans le signe moins(-).

Les options de l'incrémentation des listes

Une liste (ou série) est une suite de valeurs numériques, chronologiques ou alphanumériques, qui obéit dans sa progression à une logique régulière. Si au cours de la progression de la série, la valeur des items augmente, on parle d'une Incrémentation ; dans le cas contraire, si ça diminue on parle d'une Décrémentation. La valeur utilisée pour incrémenter ou décrémenter une série, est appelée le Pas.

Séries numériques

La création d'une série numérique dans Excel est très facile et peut se faire de plusieurs manières.

Série à partir d'une valeur initiale

Une première méthode à partir d'une valeur initiale, elle se fait en deux temps :

  1. Dans un premier temps, tapez la première valeur de la série dans une cellule puis faites glisser la poignée de recopie vers les autres cellules vers le bas (ou bien vers le haut, vers la gauche ou vers la droite), vous obtenez la même valeur recopiée dans les autres cellules.
  2. Déroulez le bouton Options de recopie incrémentée qui apparait et cliquez sur la ligne Incrémenter une série

Excel, Créer une série incrémentée.

Résultat, une série incrémentée avec le pas = 1 c'est à dire pour passer d'un élément à l'élément qui le suit, la valeur augmente d'une unité.

Astuce :

On obtient le même résultat (une série incrémentée) si au lieu de choisir l'option Incrémenter une série, on appuie sur la touche ctrl du clavier avant de lâcher le bouton de la souris.

Notez que si on crée une série vers le bas ou bien vers la droite, on obtient une série incrémentée (Pas = 1), si la création se fait vers la gauche ou bien vers le haut, la série est décrémentée (Pas = -1).

Excel, Créer une série incrémentée/décrémentée.

Série à partir de deux valeurs de départ

En utilisant deux valeurs numériques sélectionnées au départ, Excel calcule automatiquement le pas. Celui-ci est égal à la différence entre la deuxième valeur et la première. L'animation suivante montre quelques exemples.

Excel, Créer une série incrémentée/décrémentée.

Utiliser l'assistant "Série de données"

La boite de dialogue de l'assistant "Série de données" est accessible en passant par le groupe Édition de l'onglet Accueil. Cette fenêtre permet un paramétrage plus avancé pour créer des séries. Utilisons cet outil pour créer dans une colonne, la suite des multiples de 5 allant de 0 jusqu'à 100 en suivant les étapes montrées dans la capture qui suit.

  1. Dans une cellule, on saisit la valeur de départ, ici c'est 0
  2. Sous l'onglet Accueil dans le groupe Édition, on ouvre le bouton liste Remplissage
  3. On choisit dans la liste qui apparait, la ligne Série..., ce qui ouvre la boite de dialogue Série de données
  4. Dans la section Série en, on choisit l'option adéquate, l'option En colonne crée une série verticale dans une colonne et l'option En ligne crée une série horizontale sur une ligne.
  5. Les options de la section Type, permettent de définir la nature de la série à créer. Dans le cas présent on veut une série linéaire, c'est à dire on ajoute à chaque fois la même valeur pour passer à l'élément suivant de la série.
  6. Pour la Valeur du pas, on saisit la valeur 5. De cette manière les éléments de la série augmenteront à chaque fois de 5 unités. Il s'agit ici d'une suite Linéaire appelée aussi Suite arithmétique
  7. Dans la champ Dernière valeur, on saisit la valeur où s'arrêtera la série, pour cet exemple on met 100
  8. En dernière étape, on valide le paramétrage en cliquant sur le bouton OK.

Excel, Utiliser l'assistant pour créer une série.

Le résultat de ce paramétrage est la liste des multiples de 5 allant de 0 jusqu'à 100.

Excel, Série linéaire avec 5 comme valeur du pas.

Séries chronologiques

Une série chronologique est une suite de nombres au format Date ou bien Heure, qui évolue d'une façon incrémentée ou bien décrémentée en respectant une certaine logique. Pour créer une série chronologique, on saisit d'abord dans une cellule la valeur de départ (une Date ou bien une Heure) puis dans la boite de dialogue Série de données on valide le paramétrage suivant :

  • Choisir le type Chronologique
  • Sélectionner l'unité de temps adéquate
  • Définir le pas
  • Renseigner la Dernière valeur

Excel, Paramétrer et créer une série chronologique.

Pour mettre en pratique ces réglages, on va voir deux exemples.

Exemple 1 : Créer la suite des jours ouvrés

Dans ce premier exemple, on veut lister les jours ouvrés c'est à dire les jours de travail en excluant les Samedis et les Dimanches, pour l'année 2023. La capture suivante montre les différents réglages.

Excel, Liste des jours ouvrés de l'année 2023.

Exemple 2 : Définir des rendez-vous à intervalles réguliers

Dans un atelier de mesure des potentialités des machines, on évalue les performances d'un appareil à intervalles de temps réguliers toutes les 17 minutes pendant 6 heures. Pour ce faire, on doit dresser un tableau d'évaluation sur une feuille Excel à remplir au fur et à mesure du déroulement des tests. L'expérience débute à 08:30:00 et finira 6 heures après, c'est à dire à 14:30:00. Le pas ici c'est une fraction du jour : 17 minutes soit 17x1Jour/(24x60) ce qui donne 0,0118056. Comme ça, nous avons toutes les informations nécessaires pour paramétrer la série chronologique.

Excel, Heures des mesures des performances.

Astuce :

Pour une série chronologique, au lieu de saisir un nombre décimal compliqué comme 0,0118056 pour la valeur du pas, on peut utiliser sa valeur horaire ici c'est 17 minutes, 00:17 ce qui est plus convivial.

Travail à faire 10

Une série géométrique est une suite de nombres dont la valeur augmente d'une façon exponentielle, c'est à dire chaque terme autre que le premier, est égal à celui qui le précède multiplié par une valeur constante appelée pas ou raison.

Utilisez l'assistant Série de données pour remplir le tableau suivant qui représente l'évolution d'une colonie de bactéries dans un milieu favorable. On suppose que la population des bactéries double toutes les 20 minutes

Excel, Évolution de la population d'une colonie de bactéries.

Séries alphanumériques

Une série alphanumérique est une donnée texte composée d'un texte constant suivi ou précédé d'un indice qui s'incrémente. Exemples : Produit 1, Produit 2, Produit 3.

L'obtention d'une série alphanumérique se fait en un seul cliqué-glissé puisqu'il suffit de saisir le premier terme de la série dans une cellule puis de cliquer-glisser la poignée de recopie (le coin inférieur droit de la cellule) vers les autres cellules. Le résultat : une liste avec des indices incrémentés comme le montre la capture suivante.

Excel, Créer une série alphanumérique.

La création d'une série alphanumérique à partir d'un élément initial, peut aussi être créée en utilisant l'assistant Série de données avec l'option Recopie incrémentée. Avant de lancer l'assistant, il faut d'abord définir la plage de cellules sur laquelle on veut étaler la série.

Excel, Créer une série alphanumérique.

Notez que, avec la méthode de cliqué-glissé ou bien celle de l'assistant, on peut créer des séries avec des valeurs de pas autres que 1. Pour ce faire, on saisit d'abord les deux premiers éléments de la série qui serviront de modèle pour calculer le pas, puis procéder à la création de la série. Dans l'animation qui suit, on crée une série alphanumérique avec la valeur du pas 5.

Excel, Créer une série alphanumérique avec un pas égal à 5.

Les listes personnalisées

Vous utilisez souvent la liste des jours de la semaine et/ou la liste des mois, et à chaque fois vous êtes obligé de saisir au clavier les éléments de ces listes un à un ? Si vous travaillez de la sorte, c'est que vous n'avez pas encore découvert les listes personnalisées.

Excel propose des listes personnalisées qui vous dispensent de ces tâches répétitives. Ainsi pour créer la liste des jours de la semaine ou bien la liste des mois, vous n'avez qu'à saisir d'un élément de la liste dans une cellule puis de prendre cette cellule par la poignée de recopie et de la faire glisser dans un des quatre sens (en bas, en haut, à gauche, à droite) pour obtenir les autres éléments de la liste (les jours ou bien les mois).

Excel, Listes personnalisées des jours et des mois.

Remarquez les points suivants :

  • Lorsqu'on arrive au dernier élément, la liste reprend depuis le début, de cette manière on peut obtenir une liste aussi longue qu'on le désire.
  • On peut débuter avec n'importe quel élément de la liste
  • On a le choix entre les noms complets des jours et des mois, et leurs noms écourtés sur trois lettres pour les jours et quatre lettres pour les mois.
Dévoiler le secret des listes personnalisées

À première vue, le fait d'obtenir la liste des jours et des mois à partir d'un seul item nous parait comme un coup de magie de la part de Excel ; et le moins curieux des utilisateurs aimerait savoir comment Excel est-il parvenu à imaginer la suite des éléments de la liste ?

Pour révéler la manipulation qui se cache derrière ce tour de passe-passe, il faut faire un tour dans les options avancées de Excel. Là, en cliquant sur le bouton Modifier les listes personnalisées, on découvre les coulisses de ces listes magiques.

Excel, Options avancées, Listes personnalisées.

Comme le montre cette capture d'écran, Excel propose ces listes stockées en arrière-plan sur lesquelles il se base pour reconstituer les éléments à la demande de l'utilisateur. Pas que ça. En plus de ces listes natives, Excel donne à l'utilisateur la possibilité de créer ses propres listes personnalisées.

Créer ses propres listes personnalisées

La création des listes personnalisées peut se faire de deux manières différentes :

Première méthode : La saisie directe des éléments.

Si la liste à créer n'est pas trop longue, on peut saisir ses items un à un dans la zone Les entrées de la liste puis cliquer sur le bouton Ajouter en procédant de la manière suivante dans la fenêtre Listes personnalisées

  1. Dans la zone Listes personnalisées, activer la première ligne Nouvelle liste
  2. Saisir les items de la liste, un par ligne, dans la zone Les entrées de la liste
  3. Une fois toutes les entrées sont saisies, on clique sur le bouton Ajouter pour incorporer ces éléments à la zone de gauche

Excel, Créer une liste personnalisée par saisie des éléments.

Deuxième méthode : L'importation d'une liste.

Dans le cas d'une liste longue dont les items sont déjà saisis dans une plage de cellules, l'importation est plus pratique. L'opération nous fait gagner du temps et nous évite les erreurs de saisie. L'animation suivante montre le déroulement de l'opération.

Excel, Créer une liste personnalisée par importation.

Modifier une liste personnalisée

Les listes personnalisées créées par les utilisateurs, sont éditables et modifiables. Il suffit de sélectionner la liste en question dans la partie Listes personnalisées pour afficher ses éléments dans la zone Les entrées de la liste puis d'apporter les modifications désirées : modifier une entrée, Supprimer ou Ajouter des lignes. Pour valider ces modifications, on clique sur le bouton Ajouter puis sur les boutons OK autant de fois que nécessaire pour fermer les boites de dialogue qui ont été ouvertes.

Seules les listes personnalisées créées par l'utilisateur peuvent être modifiées ou supprimées. Les listes natives de Excel ne sont ni modifiables ni supprimables.

Recevez ce cours au format PDF en envoyant un message ici

MOS Prepa