Comprendre les formats de nombres personnalisés

Excel fournit un mécanisme robuste pour contrôler les formats de nombres. Vous êtes probablement familier avec les formats numériques tels que la devise et la comptabilité, mais vous avez beaucoup plus de contrôle sur le formatage que simplement les formats numériques intégrés. Dans les sections suivantes, je décris comment créer un format de nombre personnalisé, y compris la modification de la couleur de la police et l’ajout de conditions.

Les quatre sections d’un format

Chaque format de nombre a entre une et quatre sections séparées par des points-virgules. Chaque section régit le format d’une condition spécifique du nombre, comme s’il est positif, négatif ou zéro. Si le format ne comporte qu’une seule section, il s’applique à toutes les conditions. La figure 5.1 montre comment le format 0,00 s’applique à divers nombres et n’a aucun effet sur le texte.

Figure 1: Un format de nombre à une section s’applique à toutes les conditions numériques et n’affecte pas le texte.

Pour les formats à deux sections, la première section s’applique aux nombres positifs et zéro et la deuxième section s’applique aux nombres négatifs. Comme un format avec une section, un format en deux sections n’affecte pas le texte. La figure 2 montre comment le format 0,00 ; 0,00- met le signe négatif à droite pour les nombres négatifs.

Figure 2: La deuxième section régit les nombres négatifs.

Lorsque vous ajoutez une troisième section à un format de nombre, il contrôle la mise en forme de zéro. Dans ce cas, la première section ne contient que des nombres positifs et la deuxième section ne contient que des nombres négatifs. Le format 0,00 ; 0,00-; “zéro” remplace les valeurs nulles par le mot zéro, comme le montre la figure 3.

Figure 3: La troisième section contrôle les valeurs nulles.

Enfin, la quatrième section, si elle est incluse, contrôle la façon dont le texte est affiché. Le symbole @ est l’espace réservé pour le texte qui est réellement tapé dans la cellule et vous pouvez y ajouter plus de caractères. La figure 4 montre comment le format 0,00; 0,00 -; “zéro”; @ “et plus” affecte une entrée de texte.

Figure 4: La quatrième section affecte les entrées de texte.

Vous pouvez omettre l’une des quatre sections pour n’afficher aucune valeur pour cette condition.

Mais vous devez toujours inclure le point-virgule pour qu’Excel sache que vous l’omettez. Par exemple, le format ; -0,00 affichera uniquement les nombres et le texte négatifs. Les cellules avec des nombres positifs et des zéros apparaîtront vides (mais ce n’est vraiment pas le cas). À cette fin, vous pouvez masquer tout ce qu’un utilisateur tape dans une cellule au format ;;;; . Vous pouvez toujours voir la valeur dans la barre de formule, ce n’est donc pas une mesure de sécurité.

Caractères spéciaux

Les formats de numéro de client sont principalement composés de caractères spéciaux. Autrement dit, les caractères représentent autre chose que leurs valeurs littérales. Les rassembler tous dans un seul format peut rendre la lecture difficile. Mais avec un peu de pratique, vous serez en mesure de déchiffrer n’importe quel format de nombre personnalisé.

Espaces réservés de chiffres

Les deux caractères les plus élémentaires sont 0 et #. Le caractère 0 représente n’importe quel chiffre. S’il n’y a pas de chiffre où le 0 est, un 0 s’affiche. Le nombre 25,62 s’affiche sous la forme 0025,620 au format 0000,000. Comme il n’y a pas de chiffres entre les milliers, les centaines et les millièmes, un zéro y est affiché. Le caractère # représente également un seul chiffre. S’il n’y a pas de chiffre à cet endroit, rien ne s’affiche. La figure 5 montre plusieurs nombres formatés avec # ## 0,00.

Figure 5: Le caractère # affiche uniquement les chiffres existants.

Dans l’exemple de la figure 5, je souhaite inclure un espace pour les nombres supérieurs à mille. Pour ce faire, j’ai besoin d’un espace réservé pour tous les chiffres que je souhaite afficher. Si je devais utiliser un caractère 0, les nombres inférieurs à mille ou cent sembleraient étranges. En utilisant le caractère #, je peux montrer les chiffres là où ils existent et ne pas leur montrer où ils n’existent pas.

Espaces et périodes

L’espace est un caractère spécial qui divise les nombres en milliers. Dans l’exemple de la section précédente, j’ai utilisé le caractère # comme espaces réservés et un espace pour séparer les milliers et les centaines d’endroits.

Même si je n’ai inclus qu’un espace dans ce format numérique, Excel interprète cela comme signifiant que je veux un espace entre tous les trois chiffres. Le même format numérique, # ## 0,00, met en forme un nombre plus grand que 1 225 225,62 en mettant un espace partout où vous vous attendez.

Une utilisation courante des espaces dans les formats numériques est d’afficher des nombres plus grands en milliers ou en millions plutôt que d’afficher chaque chiffre. Le format numérique # ## 0, affiche 1 000 000 comme 1 000. Notez qu’il n’y a pas d’espace réservé après l’espace à la fin. Pour afficher un nombre en millions, incluez deux espaces, comme # ## 0. Ce format affiche 1 000 000 comme 1. Lorsque vous mettez un nombre à l’échelle de cette façon, assurez-vous de le noter afin que les lecteurs ne soient pas confus. Il est typique d’inclure une parenthèse comme (en milliers) quelque part sur le document pour noter l’échelle.

Le point est un autre caractère spécial utilisé pour diviser les nombres. Comme vous vous en doutez probablement, il divise les nombres entre les parties entières et fractionnaires.

La première période de votre format numérique divisera le nombre entre entier et décimal, et toutes les autres périodes s’afficheront simplement sous forme de période.

Il n’est pas courant d’utiliser plus d’un point dans un format numérique.

Si vous mettez à l’échelle un nombre avec des espaces, la période divisera le nombre à cette échelle. Le format de nombre 0,0 affichera le nombre 1,225,225,62 comme 1,2. Tout d’abord, le nombre est mis à l’échelle en millions à cause des deux virgules à la fin, puis le point sépare le nombre entier de la décimale.

Texte

Vous pouvez inclure n’importe quel texte dans un format numérique en le mettant entre guillemets. Par exemple, le format 0,0  “million” affichera 1,225,225,62 comme 1,2 million. Vous pouvez également inclure du texte au début d’un numéro. Le format “” Un excédent de “# ## 0;” Un déficit de “# ## 0 sera ajouté une chaîne de texte devant un nombre positif et une autre devant un nombre négatif.

Vous pouvez inclure plusieurs caractères sans les mettre entre guillemets, notamment +, -, $ et parenthèses. Ce sont des caractères tellement couramment utilisés que Microsoft a dû décider de vous permettre de les utiliser sans guillemets doubles pour rendre les formats plus lisibles. Vous constaterez qu’il y a d’autres caractères qui ne figurent pas sur la liste officielle mais qui ne nécessitent pas non plus de guillemets doubles. Le format # ## 0 k affiche 123456 comme 123k. Le K n’a pas besoin de guillemets doubles car il n’a pas d’autre signification.

Mais d’autres caractères alphabétiques ont une signification, et si vous ne les mettez pas entre guillemets, ils renverront des résultats indésirables. Essayez de formater le nombre 2021 comme l’année 2021. Vous pouvez le faire avec le code de format “L’année” 0. Cependant, si vous n’incluez pas les guillemets, vous obtiendrez une erreur indiquant qu’Excel ne comprend pas le format. Si vous utilisez simplement le format L’année, le résultat sera T01905 051905ar. Cette sortie étrange comprend les caractères T, a, r et espace qui n’ont pas d’autre signification et sont simplement affichés. Mais les caractères h, e et y signifient respectivement l’heure, l’année à quatre chiffres et l’année à deux chiffres.

Souligner

Le caractère de soulignement crée un espace blanc afin que vous puissiez aligner les valeurs de différentes sections du format numérique. L’utilisation la plus courante est lorsque la section négative du format numérique comprend des parenthèses pour indiquer que la valeur est inférieure à zéro. La figure 5.6 montre les effets du format # ## 0; (# ## 0) qui utilise des parenthèses pour les valeurs négatives et aucun trait de soulignement. Les virgules ne s’alignent pas comme vous vous y attendez.

Figure 6: Les caractères dans un format numérique peuvent empêcher les nombres de s’aligner.

En modifiant le format en # ## 0_); (# ## 0), vous indiquez à Excel d’inclure la même quantité d’espace blanc à droite d’un nombre positif qu’une parenthèse fermante utiliserait. De cette façon, lorsque vous avez à la fois des nombres positifs et négatifs, les virgules et les points s’alignent, comme le montre la figure 7.

Figure 7: Le caractère de soulignement permet d’aligner les valeurs.

Vous pouvez ajouter autant d’espace blanc que nécessaire en répétant le caractère de soulignement. Si vous vouliez, par exemple, afficher vos nombres négatifs avec l’abréviation neg à la fin, comme 1,024 neg, alors vous pouvez utiliser le format # ## 0_ _n_e_g; # ## 0 “neg” pour garder les nombres positifs et négatifs alignés. Il y a quatre traits de soulignement suivis de l’espace des caractères, n, e et g.

Astérisque

Tout caractère qui suit un astérisque est répété dans la cellule pour remplir l’espace disponible. Le format * – remplira une cellule avec des tirets quel que soit le nombre entré. Si vous modifiez la largeur de la colonne, le nombre de tirets augmente ou diminue pour remplir la nouvelle largeur. L’utilisation la plus courante de l’astérisque consiste à aligner un symbole monétaire, comme un signe dollar, pour différentes longueurs de valeurs. La figure 5.8 montre comment trois nombres différents sont affectés par le format $ * # ## 0. L’espace suivant l’astérisque est répété pour remplir l’espace disponible entre le signe dollar et le début des chiffres, en alignant les signes dollars sur la gauche.

Figure 8: Utilisez un astérisque pour aligner un symbole monétaire.

Échapper aux caractères spéciaux

Il existe deux façons d’utiliser un caractère spécial pour représenter sa propre valeur plutôt que sa valeur spéciale. Vous pouvez mettre le caractère entre guillemets comme je l’ai expliqué dans une section précédente avec le format «L’année» 0. L’autre méthode consiste à faire précéder le caractère d’une barre oblique inverse. Le caractère \ indique à Excel d’afficher littéralement le caractère suivant et de ne pas essayer de l’interpréter comme spécial.

Le format T \ h \ e \ y \ e \ a \ r 0 produit les mêmes résultats que “L’année” 0.

Au lieu de mettre toute la chaîne entre guillemets, les caractères spéciaux h, e et y sont échappés par une barre oblique inverse. Les caractères a et r ne sont pas des caractères spéciaux, mais Excel les échappe inexplicablement avec une barre oblique inverse, que vous l’ayez saisi ou non. Généralement, si vous avez un ou deux caractères à échapper, utilisez la barre oblique inverse, sinon les guillemets sont plus faciles à lire.

Le format du numéro comptable

Maintenant que vous connaissez les bases des formats de nombres personnalisés, vous pouvez commencer à lire les formats intégrés d’Excel et comprendre leur fonctionnement. Le format de comptabilité utilise plusieurs des caractères spéciaux de cette section.

Sélectionnez une cellule et appuyez sur Ctrl + 1 pour ouvrir la boîte de dialogue Format de cellule. Dans l’onglet Nombre, sélectionnez Comptabilité, puis sélectionnez Personnalisé. La première catégorie que vous sélectionnez changera le format, et sélectionner Personnalisé après cela vous montrera que le format intégré est construit. La figure 9 montre la catégorie personnalisée après la première sélection de la comptabilité

Figure 9: Sélectionnez la catégorie Personnalisée pour voir comment une catégorie intégrée est construite.

Le format _ ($ * # ## 0,00 _); _ ($ * (# ## 0,00); _ ($ * “-” ?? _); _ (@_)

contient les quatre sections pour les valeurs positives, négatives, nulles et textuelles. Chaque section commence par _ (pour créer un espace blanc égal à la largeur d’une parenthèse ouvrante. Normalement, cette opération est effectuée pour permettre une parenthèse ouvrante réelle pour un nombre négatif. Mais le caractère le plus à gauche dans la section négative est un signe dollar, donc cet espace blanc supplémentaire est probablement juste pour l’esthétique.

Le caractère suivant est un signe dollar, qui s’affiche comme lui-même sans échappement nécessaire. La combinaison astérisque-espace vient ensuite pour pousser le signe dollar à gauche de la cellule afin qu’ils soient alignés quelle que soit la longueur du nombre. Après les espaces répétitifs se trouvent les caractères d’espace réservé # et 0 avec une virgule et un point inclus. La séquence de caractères finale dans la section positive est _) et crée un espace blanc sur le côté droit égal à la largeur d’une parenthèse fermante.

La section négative ressemble beaucoup à la section positive. Une différence est la parenthèse ouvrante entre le signe dollar et le chiffre le plus à gauche du nombre. La parenthèse ouvrante est un autre caractère spécial qui ne nécessite pas d’échappatoire. L’autre différence est qu’au lieu de créer un espace blanc sur la droite, une parenthèse fermante réelle est affichée.

La section zéro affiche un tiret sous la virgule décimale. À l’heure actuelle, vous pouvez probablement déjà voir qu’un espace blanc et un signe dollar sont alignés à gauche, qu’il y a un espace blanc à droite et que le tiret est entre guillemets.

Cette section pourrait également avoir été écrite sous la forme _ ($ * – ?? _) ou _ ($ * \ – ?? _) car le tiret ne nécessite pas d’échappatoire. Le point d’interrogation est un autre caractère d’espace réservé numérique. Alors que le caractère # n’affiche rien s’il n’y a pas de chiffre et que le caractère 0 affiche un zéro s’il n’y a pas de chiffre, le? caractère affiche un espace lorsqu’il n’y a pas de caractère. Cela pousse le tiret vers la gauche de deux chiffres. C’est le même que le format _ ($ * -_0_0_).

Formats de date et d’heure

Excel utilise les caractères m, d et y pour représenter les mois, les jours et les années de diverses manières. Le tableau 1 montre les codes d’affichage des parties d’une date.

Tableau 1: Codes de format pour afficher des parties d’une date

CODE DE FORMAT DESCRIPTION EXEMPLE POUR LE 9 JANV 2021
m Affiche le numéro du mois sous la forme un ou deux 1
mm chiffre 01
mmm   Affiche le numéro du mois sous forme de deux chiffres, y compris le zéro non significatif si nécessaire Jan
mmmm Affiche une abréviation de trois lettres du nom du mois   Janvier
mmmmm   Affiche le nom complet du mois J
d   Affiche une abréviation d’une lettre du nom du mois 9
dd   Affiche le numéro du jour sous la forme d’un ou deux chiffres 09
ddd   Affiche le numéro du jour sous forme de deux chiffres, y compris le zéro non significatif si nécessaire Sam
dddd   Affiche une abréviation à trois lettres du nom du jour Samedi
y   Affiche le nom complet du jour   21
yy   Affiche l’année sous forme de deux chiffres 2021

 

Vous pouvez inclure des barres obliques ou des tirets pour séparer les parties de date. Par exemple, mm / jj / aaaa renvoie 01/09/2021 et md-yyyy renvoie 1-9-2021 pour le 9 janvier 2021. Bien sûr, vous pouvez utiliser n’importe quel caractère pour les séparer, mais les barres obliques et les tirets sont les le plus typique.

Dans les graphiques, il est utile de pouvoir rendre les dates aussi courtes que possible, afin qu’elles s’adaptent toutes lorsque vous en avez beaucoup sur un axe. Un autre format de nombre personnalisé courant pour les dates consiste à définir une période. Par exemple, le format numérique «Pour l’année terminée» m / j / aaaa renvoie Pour l’année finie 31/12/2021 pour le 31 décembre 2021 et peut être utilisé dans le titre d’un graphique ou dans une note.

Pour les heures, Excel utilise les caractères h, m et s pour les heures, les minutes et les secondes, respectivement. Pour le caractère m, Excel utilise sa position dans le format numérique pour déterminer s’il s’agit d’un mois ou d’une minute. Par défaut, cela signifie mois, mais s’il apparaît après un caractère h ou avant un deux-points, Excel l’interprète comme une minute. Les minutes ne peuvent être que de deux caractères au maximum, donc Excel interprétera plus de deux m caractères comme un mois quel que soit son contexte.

Comme pour les dates, un seul caractère h, m ou s affichera cette partie avec un ou deux chiffres. Deux caractères, tels que hh, mm ou ss, affichent deux chiffres et incluent un zéro non significatif si nécessaire. Si vous mettez le code de format à un caractère entre crochets, Excel affiche le temps écoulé plutôt que l’heure de la journée. Par exemple, le code de format [h] renvoie 1060920 pour le 15 janvier 2021 car c’est le nombre d’heures écoulées depuis le 1er janvier 1900, date à partir de laquelle Excel commence à compter. Ceci est utile lorsque vous travaillez avec des Données de paie ou d’autres Données basées sur le temps qui ne correspondent pas à l’heure de la journée.

Incluez le code AM / PM, am / pm, A / P ou a / p après vos codes temporels pour afficher l’heure au format 12 heures. Par exemple, le code de format m / j / aaaa h: mm AM / PM affiche 1/15/2021 1:35 PM si vous l’appliquez au 15/01/2021 13:35 entré dans une cellule.

Formats de nombres personnalisés conditionnels

Par défaut, les quatre sections d’un format numérique sont des nombres positifs, des nombres négatifs, zéro et du texte. Cependant, vous pouvez redéfinir deux de ces sections pour utiliser des conditions différentes. Les deux autres sections définissent le texte et le format par défaut pour tout nombre qui ne remplit pas les conditions. Pour définir une condition, placez un opérateur de comparaison et un nombre entre crochets avant le format numérique.

Par exemple, la condition de format [> = 1000000] 0.0    “M”; [<1000000] ## 0  “K” affichera les nombres supérieurs ou égaux à un million sous la forme d’un nombre mis à l’échelle en millions avec une seule décimale suivie d’un M et les nombres inférieurs à un million sous la forme d’un nombre mis à l’échelle en milliers suivi de a K. Avec ce format, le nombre 1 010 576 est affiché comme 1,0 M et le nombre 795 422 est affiché comme 795 Ko.

Une utilisation courante des formats de nombres conditionnels consiste à contrôler la couleur. Les formats numériques personnalisés prennent en charge huit couleurs nommées et 56 couleurs au total. Les huit couleurs nommées sont le noir, le vert, le blanc, le bleu, le magenta, le jaune, le cyan et le rouge.

Pour changer la couleur d’un nombre, incluez la couleur nommée entre crochets au début du format de nombre. Le format [Bleu] 0 formatera les nombres sans décimales et les colorera en bleu.

Pour rendre les nombres négatifs rouges, incluez la couleur nommée au début de la section négative. Le format #  ## 0,00; [Red] #  ## 0,00 colorera les nombres positifs et zéro; cependant, le formatage de la cellule est défini (noir, par défaut) et les nombres négatifs seront rouges.

Si vous souhaitez utiliser une couleur autre que l’une des huit couleurs nommées, ajoutez le numéro d’index de la couleur au mot Couleur et placez-le entre crochets. Les huit couleurs nommées vont de Color1 à Color8. Color9 à Color56 peuvent être utilisés dans un format numérique tel que, par exemple, [Color9]. Les 56 couleurs correspondent à la palette de couleurs d’origine d’Excel qui a été supprimée de l’interface utilisateur après Excel 2003, mais qui perdure dans des formats numériques. Vous ne pouvez pas voir cette palette dans Excel, vous devez donc expérimenter les nombres pour voir quelles couleurs ils représentent.

Vous pouvez désormais combiner des conditions et des couleurs dans un format. La couleur vient en premier, puis le conditionnel, puis le format numérique. Par exemple, le format [Bleu] [> = 20] 0; [Magenta] [> = 10] 0; [Vert] 0 colorera tout nombre supérieur ou égal à 20 en bleu, les nombres supérieurs à 10 en jaune et tout autre nombre en vert. Le texte sera coloré en fonction de la couleur de la cellule car aucune quatrième section n’a été incluse.

Lorsque vous travaillez dans des cellules d’une feuille de calcul, la fonctionnalité de mise en forme conditionnelle d’Excel (Accueil ➪ Mise en forme conditionnelle sur le ruban) est un moyen plus simple de contrôler la couleur de la police. Mais si vous souhaitez contrôler conditionnellement la couleur de la police dans un graphique, vous devez créer le format de nombre personnalisé correct.

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

Fonctions Excel

Macro VBA Utiles

Excel Pratique

Programmation VBA

Sur Facebook

Sur YouTube

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