Création, enregistrement et fusion de format personnalisés sous excel

I. Introduction

La mise en forme des tableaux est une opération importante dans la lisibilité des informations affichées.
Pour faire ce travail nous disposerons de boutons à accès rapide dans la barre d'outils "Format" et d'une boîte de dialogue qui nous permettra de personnaliser les masques de format prédéfinis.

II. Les Formats Prédéfinis

II-A. La barre d'Outils "Format"

II-A-1. Présentation

Cette barre d'outils, affichée par défaut, est située juste au dessus de la barre de formule.
Cependant, si celle-ci est masquée, il suffit de la réafficher par :

Clic droit sur la barre de menus. Clic sur "Format" dans la liste des barres disponibles.

II-A-2. Les Outils de mise en forme directe

L'outil "Monétaire" : permet d'ajouter le symbole monétaire par défaut de Windows. Il insère un séparateur de milliers et arrondit à deux décimales. Ainsi le nombre 12345,678 sera affiché 12 345,68 €

Pour changer la monnaie par défaut, il faut intervenir dans le Panneau de Configuration au niveau des "Options régionales et linguistiques" (autrefois appelé : Paramètres Régionaux).

Procédure

• Clic sur le bouton "DEMARRER".
• Entrer dans le Panneau de Configuration.
• Double clic sur l'icône "Options régionales et linguistiques".
• Clic sur l'onglet "Symbole monétaire".
• Dans la liste déroulante, choisir le symbole de monnaie concernée ou saisir le symbole à appliquer.

L'outil "Pourcentage" : permet d'ajouter le symbole "%". Il multiplie le nombre par 100 et arrondit à l'entier. Ainsi le nombre : 0.055 s'affichera 6%.

L'outil "Séparateur de milliers" : agit de la même manière que l'outil Monétaire. Il n'affiche cependant pas de symbole monétaire. Ainsi le nombre 12345,678 sera affiché 12 345,68.

L'outil "Ajouter un décimale" : permet d'ajouter une décimale à chaque clic. Il ajoute un 0 s'il n'y a plus de décimales au nombre à mettre en forme. Ainsi le nombre 0.196 affecté du style pourcentage s'affichera 20%. Après 2 clics successifs sur l'outil "Ajouter un décimale", il s'affichera 19.60%.

L'outil "Supprimer une décimale" enlève une décimale à chaque clic tout en respectant les règles de l'arrondi. Ainsi le nombre 6.559 s'affichera 6.56.

II-B. La boîte de dialogue

Pour afficher la boite de dialogue de "Format de Cellule", il faut cliquer droit sur la cellule concernée ou sur la plage de cellules préalablement sélectionnée. Pour utiliser ou créer une mise en forme prédéfinie on trouvera l'onglet "Nombre". A partir de là, il nous sera possible de paramétrer au sein de catégories les formats à appliquer.

III. Personnaliser les formats

III-A. Présentation

La boite de dialogue "Format de cellule" nous offre 6 onglets. L'onglet "Format" nous donne accès aux différentes catégories de Format. On y trouve particulièrement une catégorie "Personnalisée". En cliquant sur cette catégorie on trouve la boite ci-contre :

Identification des repères

1. Affiche le résultat du format
2. Zone permettant de créer notre règle de format
3. Liste des masques de format existants

La zone n° 2 nous est donc dévolue et nous pouvons créer notre propre règle en modifiant ou en complétant les masques existant en utilisant des opérateurs.

III-B. Les Codes Formats

III-B-1. Le code "0"

Ce code représente un chiffre mais permet d'afficher un 0 si, par rapport au masque de format, le chiffre fait défaut.
Ainsi le nombre 35 au format 000 sera affiché 035

III-B-2. Le code "#"

Ce code affiche un chiffre mais contrairement au 0 n'affiche rien si, par rapport au format, le chiffre fait défaut.
Ainsi le nombre 35 au format ### sera affiché 35

III-B-3. Le code "?"

Ce code représente un chiffre du diviseur ou du dividende dans une fraction.
Ainsi le nombre 1.75 au format # ?/? sera affiché 1 3/4

III-B-4. Les codes de format de date

Une date est un nombre entier. Le compteur de date d'Excel commence au 1 janvier 1900.
Il incrémente son compteur de 1 chaque jour écoulé.

III-B-4-a. Le code "j"

Ce code affiche le quantième du jour dans une date.
Le nombre de code "j" contenu dans le format influence l'affichage du quantième.

• j : affiche le quantième du jour sur un chiffre ou deux chiffres si le quantième est supérieur à 10.
• jj: affiche le quantième du jour sur deux chiffres (c'est-à-dire avec un 0 non significatif si le quantième est inférieur à 10).
• jjj : affiche les 3 premières lettres du jour de la semaine.
• jjjj : affiche le jour de la semaine en entier.

III-B-4-b. Le code "m"

Ce code concerne le quantième du mois. Il se comporte de la même manière que le code "j".

• m : affiche le quantième du mois sur un chiffre ou deux chiffres si le quantième est supérieur à 10.
• mm: affiche le quantième du mois sur deux chiffres (c'est-à-dire avec un 0 non significatif si le quantième est inférieur à 10).
• mmm : affiche les 3 premières lettres du mois.
• mmmm : affiche le mois en entier.

III-B-4-c. Le code "a"

Ce code concerne l'affichage de l'année.

• aa : affiche l'année sur 2 chiffres.
• aaa ou aaaa : affiche l'année sur 4 chiffres.

III-B-4-d. Quelques exemples de l'utilisation du format de dates

Afin de parfaire vos connaissances sur les fonctions de date rendez-vous sur le tutoriel de Maxence HUBICHE

III-B-5. Les codes de format de temps

Le temps est un nombre décimal compris entre 0 et 1. Pour afficher un nombre au format horaire, il sera nécessaire d'utiliser des codes de format spécifiques.

III-B-5-a. Le code "h"

Ce code permet d'extraire la fraction horaire du nombre décimal concerné.
Ainsi le nombre 0,4579854 au format h sera affiché 11.

On pourra comme pour les codes "j" et "m" doubler le code afin d'afficher un zéro non significatif pour faciliter les alignements avec des valeurs inférieures à 10.

III-B-5-b. Le code "m"

Ce code permet d'extraire la fraction minute du nombre décimal concerné.
Cependant, il ne peut être utilisé seul dans un format. En effet, Excel l'assimilerait au code "m" permettant d'afficher le mois d'une date.
Pour l'utiliser seul, afin d'afficher une durée en minutes, il faudra combiner le format avec l'opérateur [](crochets).
Ainsi la durée 1:05:00 au format m affichera 1 tandis que le format [m] affichera 65.

On pourra, comme pour les codes "j" et "m", doubler le code afin d'afficher un zéro non significatif pour faciliter les alignements.

III-B-5-c. Le code "s"

Ce code permet d'extraire la fraction seconde du nombre décimal concerné.
Il réagit de la même façon que le code "m". Ainsi, le nombre 0,0416666666666667 qui représente 1 heure, au format [s] sera affiché 3600

III-C. Les opérateurs dans les formats

III-C-1. L'opérateur "" (guillemets)

Cet opérateur permet d'ajouter un texte personnalisé à la suite du nombre.
Ainsi le format : # ##0" bonbons" affichera, dans les cellules sélectionnées, le mot "bonbons" à coté de chaque nombre formaté.

Procédure de création du format Sélectionner les cellules concernées. Clic droit sur la sélection Clic : Format de cellule Clic onglet : Nombre Clic Catégorie : Nombre Clic Personnalisée Dans la liste des formats prédéfinis (3), choisir le masque de format le plus proche du format à réaliser. Cliquer dans la zone Type (2) et taper immédiatement après le masque (sans insertion d'espace), le texte à afficher en l'encadrant de guillemets. Le résultat du format s'affiche dans la zone "Exemple" (1).

On peut remarquer, dans l'image, qu'au niveau de la barre de formules, SEUL le nombre est affiché mais dans la feuille le texte apparaît après le nombre.

III-C-2. L'opérateur Espace

Le fait de mettre un espace à droite du format a pour effet de diviser la représentation du nombre par 1000.
Ceci permet de créer des multiples (exemple : k€ ou encore kW).

Chaque espace divise la nouvelle représentation du nombre par 1000

Procédure de création du format Sélectionner les cellules concernées. Clic droit sur la sélection. Clic : Format de cellule. Clic onglet : Nombre. Clic Catégorie : Nombre. Clic Personnalisée. Dans la liste des formats prédéfinis (1), choisir le masque de format le plus proche du format à réaliser. Cliquer dans la zone Type (2) et taper après le masque autant d'espaces que de tranche multiple à créer et ajouter le texte à afficher en l'encadrant de guillemets.

Exemples de mise en forme avec l'utilisation de l'opérateur "Espace". On remarquera que le nombre d'espaces influence l'affichage du nombre.       - 1er exemple : pas d'espace       - 2nd exemple : 1 espace       - 3ème exemple : 2 espaces       - 4ème exemple : 3 espaces

III-C-3. L'opérateur "*" (étoile)

Cet opérateur permet de répéter un même caractère afin de remplir l'espace inoccupé d'une cellule (exemple : créer des points de suite).

Procédure de création du format Sélectionner les cellules concernées. Clic droit sur la sélection. Clic : Format de cellule. Clic onglet : Nombre. Clic Catégorie Texte. Clic Personnalisée. Dans la zone Type (loupe) Excel affiche le symbole @. Ce caractère symbolise le texte contenu dans la cellule. Cliquez dans la zone "Type" et taper après le symbole le caractère à répéter.

Exemples de mise en forme avec l'utilisation de l'opérateur "Etoile".       - 1er exemple : répétition caractère "."       - 2nd exemple : répétition caractère "_"       - 3ème exemple : répétition caractère "-"       - 4ème exemple : répétition caractère "/"

III-C-4. L'opérateur "_" (underscore)

Cet opérateur permet de simuler un espace d'une largeur particulière.
(La largeur de cet espace correspondant à la largeur du caractère utilisé dans le masque de format et placé après l'opérateur).

Procédure de création du format Sélectionner les cellules concernées. Clic droit sur la sélection. Clic : Format de cellule. Clic onglet : Nombre. Clic Personnalisée. Dans la liste des masques (1), choisir le masque de format le plus proche de celui à réaliser. Cliquer dans la zone "Type" (2) et taper entre guillemets le texte à afficher. Puis taper le tiret bas (underscore) suivi du caractère correspondant à la largeur de l'espace à créer.

Remarquez l'alignement des nombres est préservé ainsi que l'alignement du texte (le "s" dans le second exemple étant remplacé par un espace).

III-C-5. L'opérateur [] "crochets"

Cet opérateur permet, comme il a été montré ci-dessus (Codes "m" et "s" du format Temps), d'afficher les temps en minutes et en secondes. Il permettra également d'afficher le cumul des heures lorsque celui-ci passe la limite de 24 heures.

III-C-6. L'opérateur "\" (anti-slash)

Cet opérateur permet d'afficher le caractère qui le suit.
Ainsi, si on applique le code format # ##0,00\ _€ et le format # ##0,00 € à deux nombres d'une même colonne, ceux-ci restent alignés :
En effet, la combinaison "\ _€" permet d'occuper dans la cellule le même espace que la combinaison " €".

III-D. Structure d'une règle de format

Une règle de format peut se décomposer en 4 arguments, chaque argument étant séparé par un ";". Le premier concerne la mise en forme des nombres positifs. Le second concerne la mise en forme des nombres négatifs. Le troisième concerne la mise en forme des cellules contenant une valeur 0. Le quatrième concerne la mise en forme des cellules texte.

La règle de format ci-dessus appliquée aux cellules ci-contre montre le résultat qui sera affiché.

On vient de montrer qu'un format pouvait se composer de 4 arguments.
Les deux premiers peuvent se diviser en 3 éléments.
Deux éléments sont optionnels et seront précisés entre crochets.
On aura donc la structure suivante

Le paramétrage de la couleur Il suffira d'indiquer entre crochets le nom de la couleur à appliquer (noir, blanc, bleu, rouge, jaune, vert, cyan, magenta) ou le numéro de la couleur souhaitée. (Ce numéro dépendant de la position de la couleur dans la palette (Outils/Options/Couleur). Exemple de paramétrage d'une couleur : [rouge] ou [Couleur6] Voir ci-contre les codes couleurs disponibles

Le paramétrage de la condition
Pour paramétrer les conditions, on utilisera les opérateurs de comparaison traditionnels :
   - Plus grand que : >
   - Plus petit que : <
   - Plus grand ou égal : >=
   - Plus petit que : <=
   - Différent de : <>
   - Egal : =

Exemple de paramétrage d'une condition : [> 100]
Exemple de mise en forme complète d'un argument : [>100][Bleu]# ##0,00 ou [>100][Couleur5]# ##0,00

Cette mise en forme ne s'appliquera que sur les cellules dont le contenu est strictement supérieur à 100. Il est donc à noter que, dès qu'une condition sera posée, il faudra nécessairement créer un format pour les valeurs ne répondant pas à la condition posée. La mise en forme complète sera donc : [>100][Bleu]# ##0,00;# ##0,00

La création d'un format conditionnel de cette façon avec tous les opérateurs définis ci-dessus ouvre certaines possibilités que le nouveau format conditionnel ne permet pas. Voici quelques exemples ci-contre :

Afin de masquer le contenu d'une cellule on pourra alors utiliser le format personnalisé suivant : ;;;

Pour avoir de plus amples informations sur les formats conditionnels, rendez vous ici

III-E. Supprimer un format personnalisé

On disposera de deux méthodes :
   Revenir au format standard : Utiliser le raccourci clavier CTRL + R afin d'annuler une mise en forme de nombre quel qu'elle soit.
   Supprimer un format personnalisé définitivement :
       - Ouvrir la boite de dialogue "Format cellule"
       - A partir de l'onglet "Nombre".
       - Choisir la catégorie "personnalisée".
       - Sélectionner le format à supprimer en le recherchant à la fin de la liste des masques de formats.
       - Enfin cliquer sur le bouton "SUPPRIMER".

IV. Les styles

Les formats personnalisés sont mémorisés uniquement dans le classeur où ils ont été créés.
Il est néanmoins possible de récupérer un format personnalisé d'un autre classeur.
Il faudra alors passer par les styles.

IV-A. Définition

Mise en forme personnalisée, utilisable à tout moment et pouvant être récupérée dans un autre classeur.

IV-B. Créer un style

IV-B-1. Ajouter la liste des styles dans la barre d'outils

Clic droit sur une barre d'outils. Clic sur "Personnaliser". Ouvrir l'onglet "Commandes". Choisir la catégorie : "Format". Clic maintenu et glissez la liste déroulante "Style" dans la barre d'outils "Format".

IV-B-2. Créer un style à partir d'un format personnalisé

Après avoir créé le format personnalisé (exemple : @*. Qui va générer des points de suite), l'appliquer sur une cellule. Cliquez alors dans la liste des styles et saisir un nom évocateur. Un fois le style créé, il sera alors possible de l'appliquer à un ensemble de cellules sélectionnées dans le classeur ou de fusionner ce style avec les styles d'un autre classeur.

Il est nécessaire de valider par "Entrée" pour valider la création du nom

IV-C. Fusionner un style

Ouvrir le classeur contenant les styles à fusionner (Classeur Source).
Ouvrir le classeur destinataire des nouveaux styles (Classeur Cible).
A partir du classeur Cible, cliquez Format / style…
Dans la boite de dialogue cliquez alors sur "Fusionner".
Choisir le classeur Source. Les nouveaux styles sont alors disponibles dans le nouveau classeur.

IV-D. Généraliser un style

Qu'entendre par "Généraliser un style" ?
En fait, il s'agit là d'avoir les styles toujours disponibles même lors de la création d'un nouveau classeur.
La fusion des styles est une méthode simple et efficace qui a néanmoins un petit coté rébarbatif. Il faut le refaire pour chaque création de classeur.
Ne serait-il pas plus intéressant d'avoir systématiquement les styles à disposition dans un nouveau classeur.
Il faut donc créer un modèle de classeur de défaut.

Dans ce cas ouvrir d'un classeur vierge.
Par le principe de la fusion décrit ci-dessus, il faut récupérer tous les styles à globaliser dans notre modèle.
La fusion étant réalisée, enregistrer le classeur vierge sous le nom de "Classeur" au format "Modèle" dans le dossier XLStart.
On pourra en même temps enregistrer un classeur d'une seule feuille sous le nom de "Feuille" sous les mêmes conditions de type et de destination.
En prenant un nouveau classeur, Excel ouvrira notre classeur par défaut et nos styles seront alors disponibles.
En cas d'insertion d'une nouvelle feuille dans le classeur, Excel fera alors appel à notre classeur "Feuille.xlt" et insèrera une feuille contenant les styles.

On retrouvera le dossier XLStart par le chemin : C:\Program Files\Microsoft Office\Office\XLStart

V. Le format personnalisé dans le VBA

V-A. Généralités

La question que l'on pourrait se poser maintenant :
"Peut-on utiliser un format personnalisé avec le code VBA ?"
En fait, les formats prédéfinis sont utilisables dans Excel via le VBA par l'utilisation de la propriété NumberFormat de l'objet Range.
L'aide VBA d'Excel nous dit :
"Le code de format est une chaîne identique au contenu de la zone Type de la boîte de dialogue Format de Cellule." On pourra donc appliquer nos règles de format par une ligne de code :

' Création des points de suite. Selection.NumberFormat = "@*." ' Création d'un format avec espace personnalisé et affichage de "!" sous condition. Selection.NumberFormat = "[>1]0"" boites"";[Red]"" !""* 0"" boite""_s" ' Création d'un format avec Couleur indexée sachant que intCouleur peut être initialisée lors du déroulement de la procédure. Dim intCouleur As Integer intCouleur = 8 Selection.NumberFormat = "[color" & intCouleur & "]# ##0,00" ' Instruction permettant de récupérer le code couleur après avoir affecté une couleur de police à une cellule Debug.Print ActiveCell.Font.ColorIndex

On fera trois remarques au niveau du code :      - Les guillemets sont doublés dans l'expression.      - Le nom couleur est à saisir en anglais (White, Black, Blue, Yellow, Green, Magenta, Cyan).      - Le code couleur peut être indexé lors du déroulement de la procédure.

Nous venons de voir qu'il était possible d'intégrer dans le code de la mise en forme automatisée d'un tableau, un instruction contenant un format personnalisé.
Comment serait-il possible d'utiliser les formats personnalisés créés sans avoir à générer un Classeur par défaut ?
Nous disposons de deux possibilités : le classeur Perso.xls et la Macro Complémentaire.

V-B. Le classeur "Perso.xls"

V-B-1. Définition

Classeur est ouvert masqué dès le lancement de l'application Excel.
Avantage :
      - Les macros contenues dans ce classeur sont immédiatement accessibles.
Inconvénient :
      -Les macros ne sont accessibles que pour le poste titulaire du classeur Perso.

V-B-2. Création du Classeur "Perso.xls"

Ce classeur est généré automatiquement dès lors que l'on enregistre une macro.      1) Saisir un nom de Macro (sans espace).      2) Prévoir un raccourci clavier pour lancer la macro (Attention : Si le raccourci affecté est déjà utilisé par Excel, le raccourci de la macro prend le dessus).      3) Choisir la destination. (En fait le classeur de macros personnelles correspond au classeur Perso.xls).      4) Saisir un petit commentaire descriptif de la macro.      5) Enregistrer la macro ...

Complément d'informations sur les différentes options de destination de la macro :

Destination	Objet
Classeur de macros personnelles	Contiendra de petites macros simples générales accessibles uniquement par l'utilisateur du poste. Cependant, on évitera une utilisation excessive de ce classeur difficilement gérable.
Ce Classeur	La macro ne sera accessible que lorsque le classeur sera ouvert. En conséquence : on prendra ce choix si la macro est développé par rapport au tableau qui est contenu dans le classeur     - si le tableau est unique, le classeur sera alors enregistré en tant que fichier ".xls"     - si le classeur contient une trame de tableau qui sert de structure pour d'autres fichiers, celui-ci sera alors enregistré en tant que modèle ".xlt"
Nouveau classeur	On utilisera ce choix si l'objectif est de distribuer la macro. Dans ce cas, le classeur sera vierge de toute trme de tableau. Il sera alors enregistré en tant que macro complémentaire ".xla".

V-C. La macro complémentaire "Fichier.xla"

V-C-1. Définition

Fichier Macros qui a pour objet de compléter l'application Excel avec des fonctionalités ou des fonctions de calcul personnelles.
Le fichier sera ainsi distribué et installé par les divers utilisateurs via le menu Outils/Macros Complémentaires/Parcourir...

Il sera important de garder la source (fichier.XLS) du fichier XLA qui n'est pas modifiable en direct.

V-C-2. Création d'une macro complémentaire

Après avoir mis au point la macro,
      - Enregistrer le fichier au format .xls.
      - puis Enregistrer sous ... en précisant dans le type de fichier : "Macro complémentaire Microsoft Office Excel".
Par défaut, Excel se positionne dans le dossier "Macros Complémentaires". Ce dossier n'est pas obligatoire. Il est possible de choisir une autre destination.

Afin d'avoir une petite description de l'objet de la macro complémentaire, on pourra avant l'enregistrement modifier les propriétés du fichier. Fichier / Propriétés / Onglet : Résumé (voir ci-contre). Saisir dans la zone "Commentaires" le texte qui paraitra dans la zone "Description" de la macro complémentaire.

V-C-3. Installation d'une macro complémentaire

A partir d'un classeur vierge ou d'un fichier ouvert, cliquer sur Outils/Macros Complementaires.       1) Cliquer "Parcourir" pour rechercher le fichier concerné.       2) Une fois installée, le nom de la macro complémentaire s'affiche dans la liste.       3) Le texte saisi dans la zone "Commentaire" des propriétés du fichier, s'affiche.

V-C-4. Modifier une macro complémentaire

1) Désactiver la macro complémentaire.
    - Outils / Macro complémentaires
    - Décocher la macro concernée par la modification.
2) Ouvrir le fichier source (.xls) de la macro complémentaire.
3) Apporter la modification, enregistrer le fichier .xls et par "Enregistrer sous...", enregistrer la macro complémentaire.
4) Recocher la macro par Outils/ Macro Complémentaires. (Elle redevient active)

VI. Conclusion

Il est sûr que la souplesse et la facilité de création de formats personnalisés fera que très vite vos tableaux fleuriront de nouveaux formats et que ceux-ci, grâce à la création du fichier Classeur.xlt ou du fichier.xla, seront utilisés dans tout votre environnement de travail.