Formater les nombres en milliers et en millions dans les rapports Excel

#,##0.00,, "m"

24,65 mètres

Utilisez la fonction TEXTE pour formater les nombres en milliers, millions ou milliards

La fonction TEXTE d'Excel est extrêmement utile pour formater les nombres et les dates dans des formats particuliers.

Dans ce cas, vous pouvez l'utiliser pour formater vos nombres en milliers, millions ou milliards. Le résultat sera converti en valeur texte, ce n’est donc pas une option que vous souhaiterez utiliser si vous devez effectuer d’autres calculs avec les nombres.

Syntaxe de la fonction TEXTE

TEXTE ( valeur, format )

• value est la valeur numérique que vous souhaitez formater.
• format est une chaîne de texte qui représente la règle de format pour la sortie.

La fonction TEXTE prend un nombre et renvoie une valeur de texte qui a été formatée.

Exemple avec la fonction TEXTE

Dans cet article, la fonction TEXTE sera utilisée pour ajouter des séparateurs par des virgules, masquer les milliers de chiffres et ajouter un  k jusqu'à la fin pour signifier que le résultat est en milliers.

Dans cet exemple, le grand nombre se trouve dans la cellule B3 et la formule TEXT est utilisée dans la cellule C3.

= TEXTE ( B3, "#,##0, " ) & "k"

La formule ci-dessus affichera le nombre de la cellule B3 sous forme de valeur de texte en milliers avec un k pour indiquer que le nombre est en milliers.

Vous remarquez que les résultats sont alignés à gauche ? Cela indique que les résultats sont des valeurs textuelles et non des nombres.

Le symbole dièse (#) est utilisé pour fournir des espaces réservés pour les chiffres des nombres dans la chaîne de formatage. Le symbole virgule (,) est utilisé pour fournir un symbole de séparation entre les groupes de 3 chiffres.

Le zéro est inclus, de sorte que si le nombre est inférieur à 1 000, cela affichera un 0 au lieu de rien. S'il ne s'agissait que d'un espace réservé (#), alors la cellule n'afficherait rien au lieu d'un 0 lorsque le nombre dans B3 est inférieur à 1 000.

La dernière virgule (,) est nécessaire pour indiquer que les nombres inférieurs à 1 000 resteront masqués car il n'y a aucun espace réservé après cette virgule.< /span>

Notez que même lorsque le nombre dans la cellule B3 dépasse le million, les espaces réservés et les virgules ne doivent être fournis que pour un groupe de 3 chiffres.

Le nombre dans la cellule C3 est beaucoup plus facile à lire que le nombre brut dans la cellule B3.

= TEXTE ( B3, "#,##0,, " ) & "M"

Utilisez la formule ci-dessus si vous souhaitez afficher les nombres en millions.

= TEXTE ( B3, "#,##0,,, " ) & "B"

Utilisez la formule ci-dessus si vous souhaitez afficher les nombres en milliards. Une virgule supplémentaire suffit pour masquer chaque ensemble de 3 chiffres dans les résultats et n'importe quel caractère peut être ajouté à la fin si nécessaire.

Utiliser la mise en forme conditionnelle pour formater les nombres en milliers, millions ou milliards

Peut également formater les nombres en fonction de leur valeur. Cela peut être fait en créant une règle de mise en forme conditionnelle pour une cellule ou une plage de cellules de telle sorte que ce qui suit se produise.

• Affiche le nombre comme d'habitude s'il est inférieur à 1k
• Affiche le nombre en milliers entre 1k et 1M
• Affiche le nombre en millions lorsqu'il dépasse 1 million

Suivez ces étapes pour créer ce format conditionnel.

Sélectionnez la cellule ou la plage de cellules que vous souhaitez mettre en forme.

Accédez à l'onglet Accueil dans le ruban.

Cliquez sur la commande Mise en forme conditionnelle trouvée dans Styles groupe.

Sélectionnez Nouvelle règle parmi les options.

Sélectionnez l'option Formater uniquement les cellules qui contiennent située dans la liste Sélectionner un type de règle.

Sous les paramètres Formater uniquement les cellules avec , sélectionnez Valeur de cellule et supérieur ou égal à dans les menus déroulants puis saisissez 1000 dans la zone de saisie.

Cliquez sur le bouton Format pour choisir le formatage à afficher lorsque les conditions sont remplies. Cela ouvrira une version allégée du Format Menu Cellules avec seulement un Nombre, Police, Bordure et Remplir onglet.

Accédez à l'onglet Numéro .

Sélectionnez Personnalisé dans les Catégorie options disponibles : #,##0, "k"

Saisissez la chaîne ci-dessus dans la section Type . Si vous avez déjà utilisé cette mise en forme personnalisée ailleurs, vous pourrez également la sélectionner parmi les options prédéfinies sous le champ Type .

Appuyez sur le bouton Ok .Cela fermera le  Le menu Formater les cellules et le menu Nouvelle règle de formatage seront à nouveau mis au point. Vous devriez maintenant voir un aperçu du formatage qui sera appliqué lorsque la formule sera vraie.

Appuyez sur le bouton OK pour fermer la Nouvelle règle de formatage et enregistrez votre nouvelle règle de mise en forme conditionnelle.

Vous verrez désormais tous les nombres de votre sélection apparaître normalement lorsqu'ils sont inférieurs à 1 000 et apparaître en milliers lorsqu'ils sont supérieurs à 1 000.

Vous répétez les étapes ci-dessus pour ajouter une autre règle de mise en forme conditionnelle afin de prendre en charge les nombres supérieurs à 1 000 000.

Le processus est exactement le même qu'avant, avec de légères modifications apportées au Formater uniquement les cellules avec entrée et Format personnalisé utilisé.

À étape 6 entrez la valeur 1000000 dans le champ zone de saisie.

#,##0,, "M"

À étape 10 utilisez le format personnalisé ci-dessus.

Vous verrez maintenant vos nombres apparaître avec un mélange de formatage en fonction de la valeur. Les nombres inférieurs à 1 000 semblent normaux, tandis que les nombres supérieurs à 1 000 mais inférieurs à 1 million sont affichés en milliers, et les nombres supérieurs à 1 million sont affichés en millions.

Pour les nombres inférieurs à 1k, le format général par défaut est adéquat puisqu'il n'y a pas besoin de séparateurs par virgules.

Si vous attendez des nombres négatifs, vous devrez répéter le processus pour ajouter des règles pour les nombres inférieurs ou égaux à -1 000 et inférieurs ou égaux à -1 000 000.