Monsieur Excel
Pour tout savoir faire sur Excel !

20 octobre 2005

Les formats personnalisés d'Excel

Les formats personnalisés d'Excel possèdent de nombreuses ressources mal connues. Sachez tout d'abord qu'un format personnalisé peut être défini selon deux syntaxes différentes :
- positif;négatif;nul;texte
- [condition 1]format1;[condition 2]format2;format3

Ainsi, le format suivant affichera tout nombre positif comme "Salut !", tout nombre négatif comme "0", tout nombre nul comme "-1234" et tout texte comme "123,45" ! Mais cela n'empêchera pas pour autant Excel d'utiliser dans ses calculs la véritable valeur de la cellule concernée.

Voici ce format : "Salut !";"0";"-1 234";"123,45"


Vous en voyez un exemple dans les lignes 1 et 2, qui contiennent les mêmes valeurs mais où la ligne 2 utilise ce format personnalisé. La seule "tricherie" dont nous sommes coupables est d'avoir cadré à droite la cellule D2, pour quelle fasse mieux illusion en tant que "valeur".

La ligne 5 contient les mêmes valeurs que la ligne 4, mais elle utilise un format personnalisé avec des conditions :

Voici ce format : [>100]# ###" €";[<1]0,0%;0,00

Pour les conditions, on ne peut utiliser que les opérateurs >, =, <=, >= ou <> ; en outre, les formules ou les références aux cellules sont interdites.

En conclusion, avec les trois possibilités de formats personnalisés utilisant des conditions et les quatre possibilités offertes par les formats conditionnels, vous avez en fait douze façons de formater une cellule en fonction de son contenu, même si les attributs modifiables par les formats personnalisés et par les formats conditionnels ne sont pas les mêmes...

36 Commentaire(s):

  • Bonjour

    C'est très bien ce que vous nous montrez, comment afficher des nombres et du texte ? par exemple:
    100 " Paquets de pates"

    By Anonymous Anonyme, sur 10:47 AM  

  • 0 " 'Paquets de pâtes'"

    On ne peut pas avoir de vrais guillemets à l'intérieur du format, mais on peut mettre des apostrophes...

    By Blogger Hervé Thiriez, sur 9:19 AM  

  • Bonjour,

    La question de Anonyme n'est pas répondue...

    Comment avoir une cellule qui affiche, par exemple: 100 jp (des jours-personnes) mais qui demeure utilisable pour un calcul?

    Merci

    Roger Mathieu

    By Blogger Roger Mathieu, Société Conseil Lambda, sur 9:19 PM  

  • Il me semble au contraire y avoir parfaitement répondu...

    Pour répondre à votre question, entrez la valeur 100 dans la cellule et donnez-lui tout simplement le format 0" jp".

    Voili voilou :)

    By Blogger Hervé Thiriez, sur 7:39 AM  

  • Bonjour,

    Je souhaite convertir le contenu de mes cellules en "pour mille", comment faire?

    By Anonymous Anonyme, sur 2:23 PM  

  • Si vous voulez seulement montrer le format "pour mille", vous pouvez utiliser 0" 0/00".

    Si vous voulez que cela divise aussi la valeur affichée par 1.000, utilisez alors le format 0 " 0/00".

    By Blogger Hervé Thiriez, sur 6:45 PM  

  • Bonjour,

    J'aurais voulu savoir comment faire pour que tout mes nombres est un format 6 chiffes (rajout de 0 après le nombre)

    ex : 392 -> 392000

    Merci.

    By Blogger Alban, sur 5:32 PM  

  • Le format 0"000" permet d'afficher 392 sous la forme 392000. Ceci dit, la valeur de la cellule restera 392.

    By Blogger Hervé Thiriez, sur 6:42 PM  

  • Ok mais je voudrais que ca marche en même temps pour par exemple 51 -> 510000, 511 -> 511000 et 5111 -> 511100, ...

    By Blogger Alban, sur 6:50 PM  

  • Votre problème était donc formulé de façon incomplète. Un format ne peut pas faire ce que vous demandez.

    En revanche, on obtient le résultat voulu sous forme de texte avec =GAUCHE(valeur&"000000";6) !

    By Blogger Hervé Thiriez, sur 7:03 PM  

  • Et si je désire afficher les chiffres alignés en colonne mais sans le séparateur de décimales lorsqu'il n'y en n'a pas, comme dans :
    1
    2,25
    34

    J'ai essayé [=0]#;##0,??
    mais je vois toujours la virgule à droite des nombres entiers. Existe-t-il une solution ?

    By Anonymous Anonyme, sur 5:55 PM  

  • L'alignement n'est pas bon dans mon exemple précédent, alors je le reproduis en remplaçant les espaces par un symbole _

    _1
    _2,25
    34

    By Anonymous Anonyme, sur 5:59 PM  

  • Bonjour,

    pour reprendre votre exemple:

    "Le format 0"000" permet d'afficher 392 sous la forme 392000. Ceci dit, la valeur de la cellule restera 392."

    mais si l'on souhaite justement que la cellule prenne la valeur 392000? dans mon cas je souhaite enregistrer une table excel sous csv, et cela enregistre 392 alors que je voudrais 392000

    By Anonymous Anonyme, sur 10:04 PM  

  • Si vous voulez changer la valeur de la cellule en la multipliant par 1.000, cela n'a plus rien à voir avec des formats !

    Vous pouvez par exemple copier la valeur 1.000, sélectionner une colonne à "corriger" et faire un collage spécial des valeurs avec multiplication...

    By Blogger Hervé Thiriez, sur 6:37 PM  

  • je me suis mal exprimé: j'ai une colonne avec des nombres composés de 1 à 4 chiffres, et je voudrais obtenir uniquement des nombres composés de 4 chiffres.
    par exemple 392 deviendrait 0392, 39 deviendrait 0039.
    et je souhaite enregistrer cela dans un csv, j'ai donc besoin de modifier la valeur elle même et non pas seulement l'affichage

    By Anonymous Anonyme, sur 5:49 PM  

  • La valeur "0392" est identique à la valeur "392", on n'y peut rien !

    Vous pouvez utiliser la formule =texte(valeur;"0000"). Cela créera un texte avec l'apparence désirée, mais pas une valeur en soi...

    By Blogger Hervé Thiriez, sur 8:16 AM  

  • c'est tout à fait ce que je cherchais, merci beaucoup!

    By Anonymous Anonyme, sur 3:07 PM  

  • Quand le service client MS fait défaut, il est toujours bon de savoir qu'on peut compter sur monsieur-excel ! Encore félicitations.

    Un ancien élève à vous

    By Anonymous Meemo, sur 7:04 PM  

  • Bonjour,
    Je cherche comment afficher un nombre en k€, avec un espace pour séparer les milliers.
    ex : 27135558, je voudrais afficher 27 136.
    J'ai réussi à diviser par 1000(# ) mais je n'arrive pas à intégrer l'espace!

    By Anonymous Anonyme, sur 2:46 PM  

  • Réponse :
    # ##0 " K€"

    By Blogger Hervé Thiriez, sur 7:09 PM  

  • Bonjour,
    Je souhaite pour mettre dans une cellule le texte suivant pré-rempli : "à l'attention de M. ", afin de ne pas avoir à le retaper à chaque fois que je rentre le nom de l'interlocuteur.
    Avec le format de cellule personnalisé, je sais rentrer des signes avant ou après un chiffre et/ou un nombre (ex : ###" à la livraison") mais comment faire s'il ne s'agit que de texte ?
    La cellule affichée devrait ressembler à ça : A l'attention de M. Paul Dupont

    Merci d'avance pour votre aide.

    Kapricorne

    By Anonymous Anonyme, sur 1:31 PM  

  • "A l'attention de M. "@

    L'arobase indique que c'est à cette position qu'apparaît le texte...

    By Blogger Hervé Thiriez, sur 11:03 PM  

  • Bonjour,

    Est il possible de personnaliser le format d'excel selon la valeur d'une autre cellule.

    Si A1 < 0 alors format personnalisé -0.0;+0.0;0.0
    Si A1>0 alors format personnalisé +0.0;-0.0;0.0

    Merci d'avance de votre aide
    R.

    By Anonymous Anonyme, sur 6:01 PM  

  • Non, un format personnalisé ne peut dépendre que du contenu de la cellule dans laquelle il est défini.

    By Blogger Hervé Thiriez, sur 7:35 PM  

  • Bonjour,

    Auriez vous une solution, via format cellule, pour afficher les multiplicateurs (M pour mega, k pour kilo, m pour mili, µ pour micro ...) en fonction du nombre ecrit dans la cellule?

    Merci

    By Blogger fabien, sur 3:04 PM  

  • On ne peut le faire qu'avec trois cas, par exemple :
    [>1000000]0,0 "M€";[>1000]0,0 "K€";0,00 €

    By Blogger Hervé Thiriez, sur 6:39 PM  

  • Merci grace à vous j'ai enfin pu faire une personnalisation de cellule correcte!
    Je voulais mettre entre crochets "[]" un texte dans une cellule. J'ai donc fait:

    "";"";"";"["@"]"

    Le @ servant a indiquer la place du texte present dans la cellule!
    merci beaucoup!

    By Anonymous Anonyme, sur 2:59 PM  

  • Je voudrais taper : l041l0992 ou m098h8765

    Et que sa note : L041 - L0992 ou M098 - H8765


    Est - ce possible ?

    By Anonymous Anonyme, sur 2:06 PM  

  • Faut quand même pas rêver !
    Ce que vous voulez doit se faire dans une cellule différente, dans une formule avec des fonctions comme stxt,gauche, droite, majuscule,...

    By Blogger Hervé Thiriez, sur 7:44 PM  

  • Bonjour
    Est-il possible de formater une cellule pour obtenir le format suivant:
    01 01 12345 PAP
    si je tape 010112345PAP
    ?
    Merci d'avance de votre aide précieuse

    By Anonymous Anonyme, sur 10:15 AM  

  • Il n'y a aucun moyen de séparer dans un format la partie numérique et la partie texte de la cellule active.

    By Blogger Hervé Thiriez, sur 8:41 AM  

  • Bonjour vous savez comment transformer une date au format nombre de type : 20121203160728
    qui représente aaaammjjhhmmss dans un format un peu plus lisible de type 2012-12-03 16:07:28
    je me bats avec les formats de cellule mais je galère pas mal ^^
    merci

    By Anonymous Anonyme, sur 12:12 PM  

  • Si vous aviez bien lu toutes mes réponses aux commentaires, vous auriez trouvé la réponse :

    0000"-"00"-"00" "00":"00":"00

    By Blogger Hervé Thiriez, sur 8:36 PM  

  • Bonjour,
    Moi par contre, je voudrais que le code multiple la valeur affichée par 1000, puis affiche le symbole ‰.Merci de m'aider.

    By Anonymous Anonyme, sur 2:19 AM  

  • Comment décaler le texte d'une cellule à gauche et compléter avec des espaces à droite, afin d'avoir toujours la même longeur?
    Merci

    By Anonymous Anonyme, sur 12:22 PM  

  • Désolé, mais je ne comprends pas votre objectif...

    By Blogger Hervé Thiriez, sur 5:55 PM  

Enregistrer un commentaire

<< Accueil