Création, enregistrement et fusion de formats personnalisés

Image non disponible

Dans ce tutoriel, vous allez apprendre à créer, enregistrer et fusionner des formats personnalisés. Vous allez apprendre à apporter une touche personnelle dans la présentation de vos tableaux.

Commentez Donner une note  l'article (5)

Article lu   fois.

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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

Image non disponible

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 :

Image non disponible

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

Image non disponible

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

  • cliquer sur le bouton « DEMARRER » ;
  • entrer dans le Panneau de Configuration ;
  • double cliquer sur l'icône « Options régionales et linguistiques » ;
  • cliquer sur l'onglet « Symbole monétaire » ;
  • dans la liste déroulante, choisir le symbole de la monnaie concernée ou saisir le symbole à appliquer.

Image non disponible

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%.

Image non disponible

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

Image non disponible

L'outil « Ajouter une 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 une décimale », il s'affichera 19.60%.

Image non disponible

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

Image non disponible

Pour afficher la boîte 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 ».
À 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

Image non disponible

  La boîte 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 boîte ci-contre.

Identification des repères :

  1. zone d’affichage du 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 codes « 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

Image non disponible

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 formats 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 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 » à côté de chaque nombre formaté.

Image non disponible

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).

   
   

Image non disponible

On peut remarquer que dans l'image, au niveau de la barre de formules, SEUL le nombre est affiché, mais que 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

Image non disponible

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 tranches multiples à créer et ajouter le texte à afficher en l'encadrant de guillemets.

   
   

Image non disponible

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.
      - 2d exemple : 1 espace.
      - 3e exemple : 2 espaces.
      - 4e 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).

Image non disponible

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.
Cliquer dans la zone « Type » et taper après le symbole le caractère à répéter.

   
   

Image non disponible

Exemples de mise en forme avec l'utilisation de l'opérateur « étoile ».
      - 1er exemple : répétition caractère « . »
      - 2d exemple : répétition caractère « _ »
      - 3e exemple : répétition caractère « - »
      - 4e 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).

Image non disponible

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.

   
   

Image non disponible

Remarquez l'alignement des nombres 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)

Image non disponible

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 « \ » (antislash)

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

Image non disponible

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.

   
   

Image non disponible

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

   
   

Image non disponible

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 ci-contre

   
   

Image non disponible

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 couleur 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 : <>
- égal : =

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

Image non disponible

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 quelle qu'elle soit.
Supprimer un format personnalisé définitivement :
- ouvrir la boîte de dialogue « Format cellule »
- à 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

Un style est une 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

Image non disponible

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é

Image non disponible

Après avoir créé le format personnalisé (exemple : @*. qui va générer des points de suite),
l'appliquer sur une cellule. Cliquer alors dans la liste des styles et saisir un nom évocateur.
Une 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).
À partir du classeur Cible, cliquer sur Format / style…
Dans la boîte de dialogue, cliquer 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'entend-on 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 côté rébarbatif. Il faut la 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 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 à travers 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 :

 
Sélectionnez
' 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"" boîtes"";[Red]"" !""* 0"" boîte""_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 une instruction contenant un format personnalisé dans le code de la mise en forme automatisée d'un tableau.
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

Ce 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 »

Image non disponible

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'information 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ée 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 trame 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.xla » est un fichier Macros qui a pour objet de compléter l'application Excel avec des fonctionnalité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 directement.

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 choisir « 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.

Image non disponible

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

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

Image non disponible

À partir d'un classeur vierge ou d'un fichier ouvert, cliquer sur Outils/Macros Complémentaires.
      1) cliquer « Parcourir » pour rechercher le fichier concerné ;
      2) une fois la macro complémentaire installée, son nom 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 :
- sélectionner le menu 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… »afin de sauvegarder la macro complémentaire.
4) Cocher à nouveau 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 feront 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.

VII. Remerciements

Je remercie vivement
RideKick, Lou Pitchoun et escartefigue pour leur relecture avisée.
Maxence Hubiche et SilkyRoad pour leurs idées permettant de compléter l'article

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2008 Jean BALLAT. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.