Excel

Formats de números personalitzats d'Excel

Excel Custom Number Formats

Introducció

Els formats de número controlen com es mostren els números a Excel. L’avantatge clau dels formats numèrics és que canvien l’aspecte d’un número sense canviar cap dada. Són una bona manera d’estalviar temps a Excel, ja que realitzen una gran quantitat de format automàticament. Com a bonificació, fan que els fulls de treball siguin més consistents i professionals.



Vídeo: Què és un format numèric

Què podeu fer amb formats de número personalitzats?

Els formats de número personalitzats poden controlar la visualització de números, dates, hores, fraccions, percentatges i altres valors numèrics. Mitjançant formats personalitzats, podeu fer coses com formatar dates per mostrar només els noms dels mesos, formatar nombres grans en milions o milers i mostrar números negatius en vermell.





Mostra formats de números personalitzats

On podeu utilitzar formats de número personalitzats?

Moltes àrees d'Excel admeten formats numèrics. Els podeu utilitzar a taules, gràfics, taules dinàmiques, fórmules i directament al full de treball.



  • Quadre de diàleg de format de full de càlcul
  • Taules dinàmiques: mitjançant la configuració del camp de valor
  • Gràfics: etiquetes de dades i opcions d’eixos
  • Fórmules: mitjançant el fitxer Funció TEXT

Què és un format numèric?

Un format numèric és un codi especial per controlar com es mostra un valor a Excel. Per exemple, la taula següent mostra 7 formats de número diferents aplicats a la mateixa data, l’1 de gener de 2019:

Entrada Codi Resultat
1 de gener de 2019 aaaa 2019
1 de gener de 2019 ii 19
1 de gener de 2019 mmm Gener
1 de gener de 2019 mmmm Gener
1 de gener de 2019 d 1
1 de gener de 2019 ddd El vostre
1 de gener de 2019 dddd Dimarts

El més important a entendre és que els formats numèrics canvien la forma en què són els valors numèrics es mostra , però ells no canviis els valors reals.

On podeu trobar formats numèrics?

A la pestanya d'inici de la cinta, trobareu un menú amb formats numèrics incorporats. A sota d’aquest menú a la dreta, hi ha un petit botó per accedir a tots els formats numèrics, inclosos els formats personalitzats:

Menú de format numèric a la pestanya Inici de la cinta

Aquest botó obre el quadre de diàleg Format de cel·les. Trobareu una llista completa de formats numèrics, organitzats per categories, a la pestanya Nombre:

Format de cel·les quadre de diàleg a la pestanya Número

Nota: podeu obrir el quadre de diàleg Format de cel·les amb la drecera Control + 1.

General és el valor per defecte

Per defecte, les cel·les comencen amb el format General aplicat. La visualització de números amb el format de número general és una mica 'fluïda'. Excel mostrarà tants decimals com l'espai ho permetrà i arrodonirà els decimals i utilitzarà el format de nombre científic quan l'espai sigui limitat. La pantalla següent mostra els mateixos valors a les columnes B i D, però D és més estreta i Excel fa ajustos sobre la marxa.

Format de número general en columna estreta

Com canviar els formats numèrics

Podeu seleccionar formats de número estàndard (General, Número, Moneda, Comptabilitat, Data curta, Data llarga, Hora, Percentatge, Fracció, Científic, Text) a la pestanya inicial de la cinta mitjançant el menú Format de número.

Nota: a mesura que introduïu dades, de vegades Excel canviarà automàticament els formats numèrics. Per exemple, si introduïu una data vàlida, Excel canviarà al format 'Data'. Si introduïu un percentatge com el 5%, Excel canviarà a Percentatge, etc.

Dreceres per als formats numèrics

Excel proporciona una sèrie de dreceres de teclat per a alguns formats habituals:

Format Drecera
Format general Ctrl Maj +
Format de moneda Ctrl Maj $
Format percentual Ctrl Maj%
Format científic Ctrl Maj ^
Format de data Ctrl Maj #
Format d’hora Ctrl Maj @
Formats personalitzats Control +1

Vegeu també: 222 Dreceres d'Excel per a Windows i Mac

On introduir formats personalitzats

A la part inferior dels formats predefinits, veureu una categoria anomenada personalitzada. La categoria Personalitzada mostra una llista de codis que podeu utilitzar per a formats de número personalitzats, juntament amb una àrea d’entrada per introduir codis manualment en diverses combinacions.

Categoria personalitzada al quadre de diàleg de cel·les de format

Quan seleccioneu un codi de la llista, veureu que apareix al quadre d'entrada Tipus. Aquí podeu modificar el codi personalitzat existent o introduir els vostres propis codis des de zero. Excel mostrarà una petita previsualització del codi aplicat al primer valor seleccionat a sobre de l'àrea d'entrada.

Nota: Els formats de número personalitzats es mostren en un llibre, no en general a Excel. Si copieu un valor formatat amb un format personalitzat d’un llibre a un altre, el format de número personalitzat es transferirà al llibre de treball juntament amb el valor.

Com es crea un format de número personalitzat

Per crear un format de número personalitzat, seguiu aquest senzill procés de 4 passos:

  1. Seleccioneu les cel·les amb els valors que vulgueu formatar
  2. Control + 1> Números> Personalitzat
  3. Introduïu codis i mireu l'àrea de previsualització per veure el resultat
  4. Premeu D'acord per desar i aplicar

Consell: si voleu basar el vostre format personalitzat en un format existent, primer apliqueu el format base i, a continuació, feu clic a la categoria 'Personalitzat' i editeu els codis que vulgueu.

Com editar un format de número personalitzat

Realment no es pot editar un format de número personalitzat per si mateix. Quan canvieu un format de número personalitzat existent, a nou es crea el format i apareixerà a la llista de la categoria Personalitzada. Podeu utilitzar el botó Suprimeix per suprimir formats personalitzats que ja no necessiteu.

Advertiment: no hi ha cap 'desfer' després de suprimir un format de número personalitzat.

Estructura i referència

Els formats de números personalitzats d'Excel tenen una estructura específica. Cada format de número pot tenir fins a quatre seccions, separades amb punts i coma de la següent manera:

Estructura de format de número personalitzat

Aquesta estructura pot fer que els formats de número personalitzats tinguin un aspecte aclaparador. Per llegir un format de número personalitzat, apreneu a detectar els punts i coma i analitzeu mentalment el codi en aquestes seccions:

  1. Valors positius
  2. Valors negatius
  3. Valors zero
  4. Valors de text

No es requereixen totes les seccions

Tot i que un format numèric pot incloure fins a quatre seccions, només cal una secció. Per defecte, la primera secció s'aplica als números positius, la segona secció s'aplica als números negatius, la tercera secció s'aplica als valors zero i la quarta secció s'aplica al text.

  • Quan només es proporciona un format, Excel utilitzarà aquest format per a tots els valors.
  • Si proporcioneu un format numèric amb només dues seccions, la primera secció s’utilitzarà per a nombres positius i zeros i la segona secció s’utilitzarà per a nombres negatius.
  • Per ometre una secció, incloeu un punt i coma a la ubicació adequada, però no especifiqueu cap codi de format.

Personatges que es mostren de forma nativa

Alguns caràcters apareixen normalment en un format numèric, mentre que altres requereixen un maneig especial. Es poden utilitzar els caràcters següents sense cap maneig especial:

Personatge Comenta
$ Dòlar
+ - A més, menys
() Parèntesis
{} Tirants arrissats
Menys de, major que
= Igual
: colon
^ falta
' Apostrofació
/ Barra endavant
! Punt d'exclamació
& Ampersand
~ tilde
Personatge espacial

Personatges que s’escapen

Alguns caràcters no funcionaran correctament en un format de número personalitzat sense escapar-se. Per exemple, els caràcters asterisc (*), hash (#) i percentatge (%) no es poden utilitzar directament en un format de número personalitzat; no apareixeran al resultat. El caràcter d'escapament en formats de número personalitzats és la barra invertida (). Si col·loqueu la barra invertida abans del caràcter, podeu utilitzar-los en formats de número personalitzats:

Valor Codi Resultat
100 # 0 # 100
100 * 0 * 100
100 \% 0 % 100

Espais reservats

Alguns caràcters tenen un significat especial als codis de format de número personalitzat. Els caràcters següents són blocs bàsics:

com crear taules a Excel
Personatge Propòsit
0 Mostra zeros insignificants
# Mostra dígits significatius
? Mostra els decimals alineats
. Punt decimal
, Separador de milers
* Repetiu el dígit
_ Afegiu espai
@ Marcador de posició per al text

Zero (0) s'utilitza per forçar la visualització de zeros insignificants quan un número té menys dígits que els zeros del format. Per exemple, el format personalitzat 0,00 mostrarà zero com 0,00, 1,1 com 1,10 i, 5 com 0,50.

Exemples de marcador de posició zero

Rètol de lliura (#) és un marcador de posició per als dígits opcionals. Quan un número té menys dígits que els símbols # en el format, no es mostrarà res. Per exemple, el format personalitzat #. ## mostrarà 1,15 com a 1,15 i 1,1 com a 1,1.

Exemples de marcador de posició de lliures

Signe d'interrogació (?) s’utilitza per alinear dígits. Quan un signe d'interrogació ocupa un lloc que no és necessari en un número, s'afegirà un espai per mantenir l'alineació visual.

Exemples de marcador d'espai d'interrogació

Període (.) és un marcador de posició per al punt decimal d'un número. Quan s'utilitza un punt en un format de número personalitzat, sempre es mostrarà, independentment de si el número conté valors decimals.

Coma (,) és un marcador de posició per als separadors de milers del número que es mostra. Es pot utilitzar per definir el comportament dels dígits en relació amb els milers o milions de dígits.

Exemples de marcador de posició de coma

Asterisc (*) s’utilitza per repetir caràcters. El caràcter immediatament posterior a un asterisc es repetirà per omplir l'espai restant d'una cel·la.

Exemples de marcador de posició d'Asterisk

Subratllat (_) s'utilitza per afegir espai en un format numèric. El caràcter immediatament posterior a un caràcter de subratllat controla la quantitat d'espai que cal afegir. Un ús comú del caràcter de subratllat és afegir espai per alinear valors positius i negatius quan un format de número afegeix parèntesis només als números negatius. Per exemple, el format numèric '0 _) (0)' afegeix una mica d'espai a la dreta dels números positius perquè es mantinguin alineats amb els números negatius, que es troben entre parèntesis.

Subratllar exemples de marcador de posició

A (@) - marcador de posició per al text. Per exemple, el format numèric següent mostrarà els valors del text en blau:

 
000[Blue]@

Vegeu a continuació per obtenir més informació sobre l’ús del color.

Arrodoniment automàtic

És important entendre que l'Excel realitzarà un 'arrodoniment visual' amb tots els formats de número personalitzats. Quan un número té més dígits que marcadors de posició a la part dreta del punt decimal, el número s'arrodoneix al nombre de marcadors de posició. Quan un número té més dígits que marcadors de posició a la part esquerra del punt decimal, es mostren dígits addicionals. Aquest és un efecte visual només els valors reals no es modifiquen.

Formats numèrics per a TEXT

Per mostrar tant el text com els números, inclogueu el text entre cometes dobles (''). Podeu utilitzar aquest enfocament per afegir o avançar cadenes de text en un format de número personalitzat, tal com es mostra a la taula següent.

Valor Codi Resultat
10 'Unitats' generals 10 unitats
10 0,0 'unitats' 10,0 unitats
5.5 0,0 peus 5,5 peus
30000 0 'peus' 30000 peus
95.2 'Puntuació': 0,0 Puntuació: 95,2
1 de juny 'Data:' mmmm d Data: 1 de juny

Formats numèrics per a DATES

Les dates a Excel només són números, de manera que podeu utilitzar formats numèrics personalitzats per canviar la forma de visualització. Excel molts codis específics que podeu utilitzar per mostrar components d'una data de diferents maneres. La pantalla següent mostra com Excel mostra la data a D5, el 3 de setembre de 2018, amb diversos formats de número personalitzats:

Codis de format numèric per a les dates

Formats numèrics per a TIME

Les hores a Excel són parts fraccionades del dia. Per exemple, les 12:00 PM són 0,5 i les 18:00 són 0,75. Podeu utilitzar els codis següents en formats d’hora personalitzats per mostrar components d’una hora de diferents maneres. La pantalla següent mostra com Excel mostra l'hora en D5, 9:35:07 AM, amb una gran varietat de formats de número personalitzats:

Codis de format numèric per a hores

Nota: m i mm no es poden utilitzar sols en un format de número personalitzat, ja que entren en conflicte amb el codi de número de mes dels codis de format de data.

Formats numèrics per a TEMPS DEPASSAT

El temps transcorregut és un cas especial i necessita un tractament especial. Mitjançant l’ús de claudàtors, Excel proporciona una manera especial de mostrar les hores, els minuts i els segons transcorreguts. La pantalla següent mostra com Excel mostra el temps transcorregut en funció del valor de D5, que representa 1,25 dies:

Codis de format numèric per al temps transcorregut

Formats numèrics per a COLORS

Excel proporciona suport bàsic per als colors en formats numèrics personalitzats. Els vuit colors següents es poden especificar per nom en un format numèric: [negre] [blanc] [vermell] [verd] [blau] [groc] [magenta] [cian]. Els noms de colors han d’aparèixer entre claudàtors.

Format de número personalitzat amb colors

convertir bytes a gb a excel

Colors per índex

A més dels noms de colors, també és possible especificar els colors per un número d'índex (Color1, Color2, Color3, etc.) Els exemples següents utilitzen el format de número personalitzat: [ColorX] 0 '▲ ▼', on X és un número entre l'1 i el 56:

 
[Color1]0'▲▼' // black [Color2]0'▲▼' // white [Color3]0'▲▼' // red [Color4]0'▲▼' // green etc.

Els símbols del triangle només s’han afegit per facilitar la visió dels colors. La primera imatge mostra els 56 colors sobre un fons blanc estàndard. La segona imatge mostra els mateixos colors sobre un fons gris. Tingueu en compte que els primers 8 colors que es mostren corresponen a la llista de colors anomenada anterior.

Colors de format de número personalitzat sobre blanc

Colors de format de número personalitzat sobre fons gris

Apliqueu formats numèrics en una fórmula

Tot i que la majoria de formats numèrics s’apliquen directament a les cel·les d’un full de treball, també podeu aplicar formats numèrics dins d’una fórmula amb la funció TEXT. Per exemple, amb una data vàlida a A1, la fórmula següent només mostrarà el nom del mes:

 
= TEXT (A1,'mmmm')

El resultat de la funció TEXT sempre és text, de manera que podeu concatenar el resultat de TEXT a altres cadenes:

 
='The contract expires in '& TEXT (A1,'mmmm')

La pantalla següent mostra els formats numèrics de la columna C que s’apliquen als números de la columna B mitjançant la funció TEXT:

Aplicació de formats numèrics amb la funció TEXT

Condicionals

Els formats de números personalitzats també poden contenir fins a dues condicions, que s’escriuen entre claudàtors com [> 100] o [<=100]. When you use conditionals in custom number formats, you override the standard [postive][negative][zero][text] structure. For example, to display values below 100 in red, you can use:

[Xarxa] [<100]00

Per mostrar valors superiors o iguals a 100 en blau, podeu ampliar el format així:

[Xarxa] [= 100] 0

Condicions amb formats de número personalitzats

Per aplicar més de dues condicions o per canviar altres atributs de cel·la, com ara el color de farciment, etc., haureu de canviar a Format condicional , que pot aplicar el format amb molta més potència i flexibilitat mitjançant fórmules.

Etiquetes de text plural

Podeu utilitzar condicionals per afegir una 's' a etiquetes superiors a zero amb un format personalitzat com aquest:

[= 1] 0 'dia' 0 'dies'

Format de número personalitzat per a text en plural

Telèfons

Els formats de número personalitzats també es poden utilitzar per a números de telèfon, tal com es mostra a la pantalla següent:

Format de número personalitzat per a números de telèfon

Fixeu-vos que el tercer i quart exemples utilitzen un format condicional per comprovar si hi ha números que contenen un prefix. Si teniu dades que contenen números de telèfon amb puntuació codificada (parèntesis, guions, etc.), haureu de fer-ho primer netegeu els números de telèfon de manera que només contenen números.

Amaga tot el contingut

De fet, podeu utilitzar un format de número personalitzat per amagar tot el contingut d’una cel·la. El codi és simplement tres punt i coma i res més

Per tornar a revelar el contingut, podeu utilitzar la drecera de teclat Control + Maj + ~, que aplica el format General.

Altres recursos

  • El desenvolupador Bryan Braun va construir un bonica eina interactiva per crear formats de números personalitzats
Autor Dave Bruns


^