| Este artigo é uma extensão do artigo de Formatos de Números. |
Neste post serão tratados os seguintes tópicos:
Na tela de Formatar Células, na aba “Número”, temos uma última opção de categoria “Personalizado”.
Ao selecionar esta categoria, você deve digitar o código do formato no campo “Tipo”.
Dependendo da sua versão do Excel instalada, você poderá adicionar entre 200 e 250 formatos de números personalizados.
Veja neste post como criar e utilizar estes códigos de formatação.
Caracteres especiais
Alguns caracteres reservados são interpretados de uma forma específica pelo Excel. Veja a tabela abaixo:
| Caracteres reservados | Breve descrição |
|---|---|
| , | Separador decimal. |
| . | Separador de milhar. |
| .. | Escalonar valores em milhares. |
| 0 | Quantidade de dígitos obrigatórios, se não existir, exibe zero. |
| ? | Quantidade de dígitos obrigatórios, se não existir, exibe espaço. |
| # | Quantidade de dígitos exibidos caso exista. |
| ?/? | Fração. |
| % | Número percentual. |
| R$ | Moeda. |
| $ + ( : ^ ‘ { < = – / ) ! & ~ | Exibidos da mesma forma. |
| \ ou “” | Exibe o próximo caractere. Útil para exibir um dos caracteres reservados, por exemplo, para exibir # utilize \# ou “#”. |
| _ | Adicionar espaço do tamanho do caractere seguinte. |
| * | Repetir próximo caractere, até ocupar todo espaço visível da célula. |
| @ | Repete o texto contido na célula. Não tem efeito sobre números. |
| “” | Incluir textos na exibição |
Números
Quando utilizamos o 0 antes da vírgula (valores inteiros): caso tenha menos dígitos, o Excel completa com zeros, mas caso tenha mais dígitos, não muda nada.
Para alterar as casas decimais exibidas de um número, limitando a uma determinada quantidade, utilizamos a vírgula , como separador e os caracteres 0, ? ou #.
Obs.1: A formatação de números altera apenas a exibição, mas o número continua com o valor original, apesar de não estar visível.
Obs.2: Caso o número de casas decimais exibidas for menor, o Excel utiliza o arredondamento matemático.
Utilizamos zero 0 após a vírgula para determinar a quantidade exata de casas decimais serão exibidas. Caso tenha mais casas decimais, limita a quantidade determinada. Caso tenha menos casas decimais, completa com zeros
Utilizamos tralha ou cerquilha # após a vírgula para determinar uma quantidade máxima de casas decimais. Caso tenha mais casas decimais, limita a quantidade determinada. Caso tenha menos casas decimais, exibe apenas as que tem.
Utilizamos interrogação ? após a vírgula para determinar o espaço exato reservado as casas decimais. Usamos quando precisamos garantir o alinhamento do separador decimal. Caso tenha mais casas decimais, limita a quantidade determinada. Caso tenha menos casas decimais, completa com espaços.
Quando utilizarmos # e ?, é sugerido que pelo menos a primeira casa decimal seja obrigatória usando 0, para os casos onde tenham números inteiros não seja exibida a vírgula sem nada após.
Utilizamos o ponto . para que seja exibido o separador de milhar. É necessário colocar algum caractere após o ponto. Podemos usá-lo combinado ou não com os códigos de casas decimais visto acima.
Caso use o ponto . sem nenhum código a direita, o número será abreviado em milhares. Utilizando dois pontos .. será abreviado para milhões, três pontos ... para bilhões e assim por diante. O Excel também utilizará o arredondamento matemático para exibir casas decimais menores que do número original.
Existe um caso específico para representar frações. Usamos ?/? para exibir um número todo como fração e o código # ?/? para mostrar a parte inteira como número e a parte decimal como fração.
Para incluirmos um espaço no tamanho de algum caractere específico, basta utilizarmos o “underline“, ou sublinhado, _ seguida do caractere.
Para ocupar todos os espaços visíveis da célula com um determinado caractere, basta usar * seguido do caractere que deseja repetir.
Datas
| Valor original | Código de formatação | Como será exibido | Descrição |
|---|---|---|---|
| 01/02/2003 04:05:06 | dd/mm/aaaa hh:mm:ss | 01/02/2003 04:05:06 | Na data, use como separadores / e nas horas, use como separadores : |
| 01/02/2003 04:05:06 | dd-mm-aaaa hh:mm:ss | 01-02-2003 04:05:06 | Também pode ser usado o – como separador na data |
| 01/02/2003 04:05:06 | hh:mm:ss | 04:05:06 | Exibe hora, minuto e segundo |
| 01/02/2003 04:05:06 | d | 1 | Exibe o dia com um dígito quando possível (de 0 a 31) |
| 01/02/2003 04:05:06 | dd | 01 | Exibe o dia com dois dígitos (de 00 a 31) |
| 01/02/2003 04:05:06 | ddd | sáb | Exibe o dia abreviado (de dom a sáb) |
| 01/02/2003 04:05:06 | dddd | sábado | Exibe o dia com um nome completo (de domingo a sábado). |
| 01/02/2003 04:05:06 | m | 2 | Exibe o mês com um dígito quando possível (de 0 a 12) |
| 01/02/2003 04:05:06 | mm | 02 | Exibe o mês com dois dígitos (de 00 a 12) |
| 01/02/2003 04:05:06 | mmm | fev | Exibe o mês abreviado (de jan a dez) |
| 01/02/2003 04:05:06 | mmmm | fevereiro | Exibe o mês com nome completo (de janeiro a dezembro). |
| 01/02/2003 04:05:06 | mmmmm | f | Exibe o mês com uma única letra (de j a d). |
| 01/02/2003 04:05:06 | a | 03 | Igual aa |
| 01/02/2003 04:05:06 | aa | 03 | Exibe o ano como um número de dois dígitos. |
| 01/02/2003 04:05:06 | aaa | 2003 | Igual aaaa |
| 01/02/2003 04:05:06 | aaaa | 2003 | Exibe o ano como um número de quatro dígitos. |
| 01/02/2003 04:05:06 | d-mmm | 1-fev | Diferentes combinações |
| 01/02/2003 04:05:06 | mmm-aa | fev-03 | Diferentes combinações |
| 01/02/2003 04:05:06 | dd “de” mmmm “de” aaaa | 01 de fevereiro de 2003 | Diferentes combinações |
| 01/02/2003 04:05:06 | d\~mm | 1~02 | Para usar outro separador, use \ ou “” |
| 01/02/2003 04:05:06 | h “hora(s) e” m “minuto(s)” | 4 hora(s) e 5 minuto(s) | Use “” para incluir textos |
| 01/02/2003 04:05:06 | h”h” m”min” | 4h 5min | Use “” para incluir textos |
| 01/02/2003 04:05:06 | “hoje é” dddd | hoje é sábado | Use “” para incluir textos |
| 26:00:00 | hh | 02 | Exibe somente o que exceder a contagem de dias inteiros |
| 26:12:34 | hh:mm:ss | 02:12:34 | Exibe somente o que exceder a contagem de dias inteiros |
| 26:00:00 | [hh] | 26 | Exibe contagem em horas, mesmo quando ultrapassa 24 horas |
| 26:12:34 | [hh]:mm:ss | 26:12:34 | Exibe contagem em horas, seguido dos minutos e segundos |
| 26:12:34 | hh:mm:ss,00 | 02:12:34,10 | Inclui centésimos de segundos |
| 13:15 | hh:mm AM/PM | 01:15 PM | Exibe hora no padrão AM/PM |
| 13:15 | hh:mm A/P | 01:15 P | Exibe hora no padrão AM/PM |
| 13:15 | hh:mm a/p | 01:15 p | Exibe hora no padrão AM/PM |
| 13:15:13 | ss | 13 | Exibe apenas os segundos |
| 13:15:13 | [ss] | 47713 | Exibe contagem em segundos |
Divisão do código
A primeira coisa a saber é que este código pode ser dividido em até 4 partes, separadas por ponto-vírgula, sendo cada parte dedicada a um tipo de valor. A primeira parte é o formato para números positivos, a segunda para números negativos, a terceira para se o valor for igual a zero, e a última parte para se não for um número:
| <positivo> ; <negativo> ; <zero> ; <texto> |
Você pode ter visto que muitos formatos não têm essa separação. Isso acontece porque estas partes não são obrigatórias.
Caso utilize somente a primeira parte, o Excel vai formatar os números negativos e o zero da mesma forma que os números positivos, e os textos não serão formatados.
Caso utilize somente a primeira e a segunda, o Excel vai formatar o zero da mesma forma que os números positivos, e os textos não serão formatados.
Cores
Podemos também utilizar códigos entre colchetes [] para determinar as cores da fonte. São ao todo 56 cores disponíveis, utilizando os códigos de [cor1] até [cor56]. O curioso é que as 8 primeiras também podem ser escritas usando o nome da cor. Essas 8 cores são: [preto], [branco], [vermelho], [verde], [azul], [amarelo], [magenta] e [ciano].
Veja todas as cores disponíveis neste post sobre Todas as cores disponíveis para a formatação de números personalizada.
Condicionais
E finalmente, podemos usar colchetes [] também para criar formatos condicionados. Por exemplo, podemos condicionar a formatação de um determinado número, caso ele seja inferior a um determinado valor. Podemos colocar até duas condições, mais e uma terceira formatação para os casos que não se enquadrarem nas duas condições determinadas. Veja o modelo de código abaixo aplicado em células com os valores:
Perceba que o código foi digitado somente com condição e cor, sem nenhum formato. O Excel irá acrescentar automaticamente a formatação Geral em todas as partes, ficando assim: [Cor3][<50]Geral;[Cor46][<70]Geral;[Cor10]Geral. Substitua pelo código que desejar, conforme visto acima.
.
IMPORTANTE:
- Não conseguimos colocar uma terceira condição. Sendo assim, qualquer outro número será formatado daquela forma. Se for necessário incluir novas condições, será necessário utilizar a Formatação condicional.
- Com apenas duas condições, o resultado poderá fugir ao controle. Por exemplo, se isso fossem notas de uma prova, valores errados acima de 100 ainda estaria verdes e valores errados abaixo de 0 (negativos) estaria vermelho. Caso necessário limitar os valores possíveis para o intervalo entre 0 e 100, veja como usar a ferramenta de Validação de Dados.
- Sempre que usar um condicional, lembre-se de colocar a formatação sem condição, para o caso do valor não se enquadrar. Ou seja, nunca use
[<50][cor3]somente, pois as células com valores maiores ou iguais a 50 apresentação o erro de visualização “#########”. Utilize[<50][cor3];Geral