Les formats, texte, nombre, date, heure
Une autre particularité du tableur Excel réside dans le format d'affichage des cellules.
Sommaire
← Retour à la liste des tutosDonnées pour le tuto
Pour ce tuto, le fichier exemple est disponible : Télécharger.Texte
On a vu dans le tuto précédent sur les formules que l'on pouvait changer le comportement d'Excel en ajoutant une apostrophe (') avant d'écrire.Il y a une autre possibilité qui permet de faire la même chose, en modifiant le format de la cellule.
Attention, si on change le format de la cellule après avoir inscrit la formule, le résultat de la formule reste. Il faut alors double cliquer ou appuyer sur "F2" pour rendre la cellule active, puis "Entrée".
Le format texte permet également d'afficher des "0" devant des nombres, comme on peut le voir en "B1".
On peut utiliser différentes formules pour tester le format utilisé dans les cellules, on obtient ainsi un résultat logique "VRAI" ou "FAUX" (en A4, le résultat de la formule en A3).
Nombre
Concernant les nombres, plusieurs formats sont considérés comme des nombres dans Excel.Ci dessous, des exemples avec les formats "Nombre", "Monétaire", "Pourcentage" et "Fraction" :
Les autres formats Excel considérés comme des nombres sont : "Comptabilité", "Scientifique", mais également le format de "Date" et "Heure".
On va tout de suite aborder les formats de "Date" et "Heure" pour comprendre comment Excel les interprètes.
Date
Le calendrier des dates dans Excel commence le 01/01/1900 avec le chiffre 1. Attention cependant, il existe une erreur sur le calcul des dates de l'année 1900, qui est considérée par Excel, à tort, comme une année bissextile (Microsoft l'explique ici).Il est donc déconseillé d'utiliser des dates avant le 01/03/1900.
Pour afficher le 01/03/1900, Excel utilise donc le nombre 61. Selon la règle de calcul des années bissextiles que l'on trouve ici, il faut ajouter 146 097 jours pour comptabiliser 400 ans supplémentaires (soit 365,2425 jours par an). On obtient ainsi le nombre 146 158, qui correspond à la date 01/03/2300.
Cette méthode n'est malheureusement pas parfaite, et il est préférable d'utiliser les formules mises à disposition par Excel pour faire des calculs sur les dates.
La formule suivante permet de calculer une date passée ou future sans avoir à calculer un nombre de jours, elle prend automatiquement en compte les années bissextiles :
= MOIS.DECALER ( date_départ ; mois )
Voici quelques exemples permettant de mieux comprendre le format "Date" :
- I1, I2, I3 et I5 → ce sont les nombres 1, 60, 2 958 465 et 61 au format "Date"
- I6 → formule qui commence à la date 01/03/1900 (nombre 61) à laquelle on va ajouter 400 ans (en comptant 1 an = 365,2425 jours pour prendre en compte les règles des années bissextiles)
- I8 → même principe que pour I6 afin de montrer la limite de la formule qui comptabilise 1 année sur 365,2425 jours
- I9 → C'est cette formule qu'il faut utiliser, c'est la plus fiable pour ajouter des mois ou des années à une date existante. Détail de la formule en I10
- I12, I13 → soustraction entre de dates pour retrouver le nombre de jours qui les séparent. On peut également comprendre le problème de l'année sur 365,2425 jours via le résultat obtenu en I12
- I15, I16 → Exemples d'erreurs lorsque l'on tente de dépasser les limites du calendrier Excel
Heure
Les heures, dans Excel, fonctionnent de manière semblable aux dates. Ici, les nombres qui constituent les heures, mais également les minutes et les secondes sont compris entre 0 et 1.Il est conseillé d'éviter d'utiliser le 0 ou le 1 pour la gestion des heures, car ils correspondent tous les 2 à 00:00:00.
Voici comment utiliser le format "Heure" :
- Q1 à Q5 → ce sont des nombres compris entre 0 et 1 (Q1 et Q2 sont là pour l'exemple mais à éviter en pratique) au format "Heure"
-
Q7, Q8 et Q9 → le 1 au format "Heure" correspond à 24:00:00, même s'il est affiché à 00:00:00. Donc 1/24 permet d'avoir 1 heure.
Il y a 60 minutes dans 1 heure donc 1 / (24*60) permet d'avoir 1 minute, même logique pour la seconde - Q11, Q12 et Q13 → les nombres correspondant à l'heure, la minute et la seconde
- Q15 à Q18 → différents exemples permettant de reconstruire des horaires complètes
Les formats personnalisés
Jusqu'ici, on a vu l'ensemble des formats standards dans Excel. Il y a également la possibilité de créer des formats personnalisés.L'article de Microsoft qui détail l'ensemble des possibilités offertes est disponible ici.
L'option pour créer un format personnalisé se trouve tout en bas du listing des formats standards :
Voici l'interface en question, après avoir sélectionné la cellule contenant le nombre 61,7665278 :
Ce nombre correspond à ce que l'on vient de voir sur les formats "Date" et "Heure". La partie entière correspond à la date, et la partie décimale à l'heure.
La partie entière 61 correspond à la date 01/03/1900.
La partie décimale 0,7665278 correspond à l'heure 18:23:48.
Le format personnalisé utilisé est le suivant : jj/mm/aaaa hh:mm
Ce format va donc faire apparaître :
- jj/mm/aaaa → le jour sur 2 caractères, un /, le mois sur 2 caractères, un /, l'année sur 4 caractères
- un espace pour séparer la date et l'heure
- hh:mm → l'heure sur 2 caractères, les :, et les minutes sur 2 caractères
- les 48 secondes ne seront donc pas apparentes, comme le montre le résultat dans le cadre "Exemple"
Voici quelques autres exemples d'utilisation des formats personnalisés :
- V1 → reprise de l'exemple précédent
- V2 : 0,# → permet de faire un arrondi à un seul chiffre après la virgule lorsque le cas se présente
- V3 : 0" km/h" → affiche uniquement des nombres entiers, et ajoute km/h après le chiffre
-
V5 à V8 : [Vert];[Rouge];-;@ → format personnalisé utilisant les 4 parties possibles, chaque partie est séparé d'un ";" :
- V5 : nombre positif : 5 → utilise donc le format en partie 1, soit [Vert]
- V6 : nombre négatif : -5 → utilise donc le format en partie 2, soit [Rouge]
- V7 : 0 → utilise donc le format en partie 3, soit "-"
- V8 : texte → utilise donc le format en partie 4, soit "@"
- V10 : 0 " k€" → affiche uniquement des nombres entiers après les avoir divisé par 1 000 (c'est l'espace après le 0 dans le format qui permet de faire cette division par 1 000)
- V11 : 0,# " M€" → arrondi à 1 chiffre après la virgule lorsqu'il y a besoin après avoir divisé le nombre par 1 000 000 (il y a 2 espaces après le 0 dans le format, ce qui permet de faire cette division par 1 000 000)
Les formats sont très utiles dans Excel, afin de garder la possibilité de faire des calculs sur les cellules, qui restent numériques, tout en permettant d'ajouter du texte lorsque c'est nécessaire.
Félicitations, un nouveau chapitre se termine !
