Este artigo revela segredos ocultos sobre a formatação de números do Excel. Como você pode armazenar números no Excel, mas adicionar palavras ao lado dos números. Continue lendo para obter mais dicas interessantes sobre a formatação de números.
O Excel é incrível em armazenar um número e apresentar outro número. Escolha qualquer célula e selecione o formato de moeda. O Excel adiciona um cifrão e uma vírgula e apresenta o número arredondado para duas casas decimais. Na figura abaixo, a célula D2 realmente contém 6,42452514. Felizmente, o formato de número personalizado embutido apresenta os resultados em um formato fácil de ler.
O código de formato de número personalizado em D2 é $ #, ## 0,00. Neste código, 0s são dígitos obrigatórios. Quaisquer #s são dígitos opcionais.
No entanto, os códigos de formatação podem ser muito mais complexos. O código acima tem um formato. Esse formato é aplicado a todos os valores da célula. Se você fornecer um código com dois formatos, o primeiro formato é para números não negativos e o segundo formato é para números negativos. Você separa os formatos com ponto e vírgula. Se você fornecer um código com três formatos, o primeiro é positivo, depois negativo e, em seguida, zero. Se você fornecer um código com quatro formatos, eles serão usados para positivo, negativo, zero e texto.
Mesmo se estiver usando um formato integrado, você pode ir para Formatar células, número, personalizado e ver o código usado para gerar esse formato. Aqui está o código para o formato de contabilidade:
Para criar seu próprio formato personalizado, vá para Formatar células, número, personalizado e insira o código na caixa Tipo. Verifique o exemplo na caixa Amostra para se certificar de que tudo parece correto.
No exemplo a seguir, três zonas são usadas. O texto entre aspas é adicionado ao formato de número para personalizar a mensagem.
Se você criar uma zona para zero, mas não colocar nada lá, ocultará todos os valores zero. O código a seguir usa códigos de cores para positivo e negativo. O código termina em ponto-e-vírgula, criando uma zona para valores zero. Mas, como a zona está vazia, os valores zero não são mostrados.
Você pode estender isso deixando todas as zonas em branco. Um código de formato personalizado ;;; irá ocultar os valores no visor e na impressão. No entanto, você ainda poderá ver os valores na barra de fórmulas. Se você ocultar valores tornando a fonte branca, o ;;; permanecerá oculto mesmo que as pessoas alterem a cor de preenchimento. A figura a seguir inclui alguns truques de formatação interessantes.
Em B2 e B3, se você colocar ** antes do código numérico, ele será preenchido à esquerda do número com asteriscos, como as antigas máquinas de escrever cheques fariam. Mas não há nada que diga que você deve usar asteriscos. O que quer que você coloque após o primeiro asterisco é repetido para preencher o espaço. A linha 3 usa *! para repetir pontos de exclamação.
Em B4 e B5, cada vírgula que você colocar após o zero final dividirá o número por 1.000. O código 0, K mostra números em milhares, com um K depois. Se você quiser mostrar milhões, use duas vírgulas. O código “M” deve ser colocado entre aspas, pois M já significa meses.
Em B6 coloque uma mensagem severa na quarta zona para alertar quem está inserindo dados de que deseja um número na célula. Se eles inserirem um texto acidentalmente, a mensagem será exibida.
Em B7 a B9, as zonas normais Positivas, Negativas e Zero são substituídas pelas condições que você coloca entre colchetes. Números abaixo de 70 são vermelhos. Números acima de 90 são azuis. Todo o resto é preto.
Em B10, esses _ (símbolos no formato de contabilidade estão dizendo ao Excel para deixar tanto espaço quanto um parêntese esquerdo ocuparia. Acontece que um sublinhado seguido por qualquer caractere deixará tanto espaço em branco quanto aquele caractere. Em B10, o código contém 4 zeros. Mas há diferentes quantidades de espaço entre cada um. O espaço entre 1 e 2 é a largura de 2 caracteres WW. O espaço entre 2 e 3 é a largura de um N. O espaço entre 3 e 4 é a largura de uma letra i minúscula.
A figura a seguir mostra vários códigos de formatação de data.
Observe que o formato mmmmm na linha 8 é útil para produzir rótulos de gráfico JFMAMJJASOND.
Agradecimentos a Dave Baylis, Brad Edgar, Mike Girvin e @best_excel por sugerirem este recurso.
Assistir vídeo
- A formatação de números é uma fachada: o Excel armazena uma coisa e nos mostra outra.
- Por exemplo: use decimais de diminuição para mostrar uma versão arredondada do número.
- O Excel ainda armazena todos os decimais, mas simplifica a exibição.
- Existem 11 formatos de número na lista suspensa da guia inicial.
- Clique no iniciador da caixa de diálogo para acessar a caixa de diálogo Formatar células.
- Hoje, estamos falando sobre os códigos de formatação no grupo Custom.
- Não tenho todo o conhecimento aqui hoje, então se você tiver dicas melhores, coloque nos comentários do YouTube.
- Um zero no código de formato indica que o Excel deve exibir o dígito.
- Um sinal # indica que eles podem exibir o dígito se houver precisão suficiente, mas não precisam quando não houver.
- UMA ? deixará espaço para o dígito manter a casa decimal alinhada.
- Pode haver 1, 2, 3 ou 4 formatos, separados por ponto e vírgula.
- Quando há 2 formatos, o primeiro é para zero e acima. O segundo é negativo.
- Quando existem 3 formatos, o terceiro é zero.
- O 4º formato é para texto.
- Usando o formato de número para exibir texto com número ou para exibir texto em vez de número.
- Especificando uma cor para uma zona.
- Para alterar (Cor 5), visite as opções do Excel.
- * x para repetir x até os números
- , no final, para dividir por 1000
- ,, divide por milhões
- Usando condições na zona
- _x deixará um espaço do tamanho de "x"
- m mm mmm mmmm mmmmm
- d dd ddd dddd
- (Hmm
- Agradecimentos a Dave Baylis, Brad Edgar, Mike Girvin e @best_excel por sugerirem este recurso.
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2044 - Formatting as Façade!
Tudo bem, estou enviando um podcast de todas as minhas dicas neste livro, clique no “i” no canto superior direito para acessar a lista de reprodução!
Estamos falando sobre a formatação de número personalizada hoje, então eu tenho Quantidade, Receita e estamos exibindo um monte de decimais aqui. E, você sabe, se não quisermos tocar todos esses decimais, e não quisermos usar a função ROUND, acabo de chegar aqui e uso Diminuir Decimal. ele não altera o número que está realmente armazenado nos bastidores, ele apenas exibe a maneira como o Excel nos mostra o número. Tudo bem, então estou roubando o termo de Mike Girvin, de que estamos usando formatação como fachada, certo, há um monte de decimais armazenados nessa célula, mas estamos usando formatos de números personalizados para exibi-los. E eu quero ir além dos 11 formatos aqui, e até clicar naquele iniciador de diálogo, além da maioria das coisas que estão aqui, e quero falar sobre Custom.Os formatos de números personalizados nos dão o mundo, certo?
E aqui está um monte de formatos de número personalizados que você pode digitar nesta caixa, e os resultados são mostrados à esquerda. Então, apenas Geral, você obtém todas as casas decimais, mas aqui, os zeros antes da casa decimal estão forçando o aparecimento de zeros. Portanto, se o número <100, obterei um 0 à esquerda, às vezes útil para números de peça, zeros após as casas decimais forçam os números a aparecer. A hashtag ou o sinal de libra (#) antes da casa decimal é opcional, tudo bem, então pode estar lá, não precisa estar lá. Tudo bem, então neste caso temos o número 6, mas como temos cinco zeros, ele está nos dando zeros à esquerda. Agora, se você precisa PROCV em uma seção de texto que tem 00006, isso não será uma correspondência, mas se você apenas precisava olhar certo, funcionará. OK, algumas coisas interessantes aqui,usamos muito sinais de libra ou hashtags, muito "hashsigns" Nos EUA, chamamos isso de símbolo de libra, e sei que na Inglaterra não é um símbolo de libra, £ é a moeda que você chama de hash. Tudo bem, desculpe, tenho chamado de sinal de libra durante toda a minha vida e é difícil para mim começar a chamar um hash, mas vou trabalhar nisso.
Tudo bem, então aqui embaixo o .000 indica que você vai exibir os zeros quer haja precisão ou não, e mudei esses números para 6,42 e 6,4. Agora, isso é interessante, eu nunca conheci realmente, se você colocar um? em vez do 0, ele diz que exibirá a precisão se estiver lá, mas se não, não exiba, mas não deixe as coisas fluírem para a direita. Tudo bem então aqui, vamos mudar esses números, 6,42 e 6,4, tudo bem, veja aqui, os decimais não ficam alinhados. Se quiser que os decimais fiquem alinhados, use os pontos de interrogação, se quiser forçar a presença dos zeros, use os zeros.
Agora hey, conforme avançamos aqui, há MUITOS detalhes em minúcias, mas eu não tenho o mínimo de conhecimento aqui. Se você perceber que perdi algum truque legal de formatação de números, por favor, deixe-o nos comentários do YouTube. Tudo bem agora, aqui está o próximo conceito, podemos exibir 1, 2, 3 ou 4 formatos de número na caixa Tipo, se você exibir apenas 1, é apenas o número que é usado para tudo, certo. Mas se você exibir 2 e eles estiverem separados por ponto-e-vírgula, o primeiro para números> = 0 e o segundo para números <0. Então, bem aqui eu usei dois códigos, +0; -0, e isso significa que os números negativos aparecem com um menos, mas os números positivos aparecem com um mais, mas porque eu não especifiquei o que acontece com o 0 , também aparece com um sinal de adição.
Então podemos adicionar uma 3ª zona: positiva, negativa, zero, certo, então +0; -0; 0, certo, de modo que trata os zeros. Pode até haver um quarto código do que fazer se alguém inserir um texto. Tudo bem agora, próximo conceito, no formato de número personalizado, além dos zeros, você pode colocar o texto entre aspas! Tudo bem, então vamos dar uma olhada no formato de número personalizado aqui, para números positivos, as palavras “Envie“ e depois o número, para negativo “Saldo de crédito de“ o número e “(Não pague)”. E para 0, eu nem mostro o número, só mostro “Sem Saldo”, tudo bem. Então você pode realmente usar isso para substituir o número, isto é -1, 0 e 1, e essas são fórmulas, obter a fórmula é apenas copiá-la. Todos os números negativos dirão "Perdedor!" ou “Down” ou a palavra “Minus” e o número, então colocamos -5, ainda diz “Perdedor!” ou “para baixo” ou “menos 5”.Para 0, podemos dizer "Zero" ou "Simples" ou "0" e, em seguida, para um número positivo, vamos colocá-lo em 55, "Vencedor!", "Para cima" ou "55 Positivo", tudo bem, então você pode realmente adicionar palavras. E o que é legal, então isso é 55, 55, 55, se eu somasse isso, eles são realmente números, e realmente vai funcionar, como normalmente o SUM não funcionaria com texto, mas isso realmente me dá uma resposta . Então, se você quiser manter os números lá, mas exibir as palavras nas células, esse é um ótimo truque.t trabalho com texto, mas isso realmente me dá uma resposta. Então, se você quiser manter os números lá, mas exibir as palavras nas células, esse é um ótimo truque.t trabalho com texto, mas isso realmente me dá uma resposta. Então, se você quiser manter os números lá, mas exibir as palavras nas células, esse é um ótimo truque.
Tudo bem agora, esses são os caracteres que podem ser usados no formato de número personalizado sem aspas, então se acontecer de você precisar apenas de um + ou um -, é por isso que você não precisa colocá-lo entre aspas. E há alguns acréscimos a isso, como quando eu vi essa lista, eu pensei “OK, vamos ver o que podemos fazer por aí”. Todos esses são os números 1 2 3 4, então se eu usar um formato de número personalizado de ((número> &, tudo isso será exibido. Se eu quiser separar os dígitos por travessões, este é um formato de número personalizado para obter que funcione. E tudo isso é real, então 7854, veja tudo muda, essas são todas as fórmulas que estão vindo aqui. Então eu posso mexer com o formato de número personalizado e colocar os caracteres antes dele, coloque os caracteres depois. não está na lista que eu sei que funciona é K, você pode colocar um K no final, e isso 'não está entre aspas. Mas M, porque M significa meses ou minutos, você teria que colocá-lo entre aspas ou colocar este M para fazer um M aparecer no final.
Tudo bem, próximo conceito, em cada zona você pode especificar a cor, certo, então aqui os números positivos são verdes e os números negativos são azuis. Ctrl + 1 para chegar a Formatar células, antes do formato de número personalizado, na primeira zona, a zona positiva, eu disse verde, e para números negativos disse azul. Que cor podemos usar lá? Remonta aos dias de 8 cores, então preto, verde, branco, azul, magenta, amarelo, ciano e vermelho. Mas graças ao Mike Alexander, você pode usar 56 outras cores, Cor 1, Cor 2, tem que estar entre (), assim, essas são as 56 cores. Se você precisa mudar isso, estamos indo muito fundo agora, agora estamos entrando no território infantil Charlie! Opções do Excel, Salvar e, em seguida, clique em Cores, tudo bem, e assim, se eu quiser mudar o laranja para outra coisa, talvez o roxo, clique em OK, clique em OK,clique em OK e, em um destes, não consigo dizer qual teria mudado para aquela cor. Outro truque legal, e se você quiser ocultar todos os números? Então você tem alguns segredos aqui e não quer que ninguém os veja. Ctrl + 1, coloque um formato de número personalizado, estou dizendo “Não exibir nada como positivo, nada como negativo, nada como 0”, clique em OK e tudo ficará oculto. Claro, eles ainda podem ver aqui na barra de fórmulas, então não é perfeito, certo, aqui estão alguns outros caracteres de formatação do cliente.nada para negativo, nada para 0 ”clique em OK e tudo ficará oculto. Claro, eles ainda podem ver aqui na barra de fórmulas, então não é perfeito, certo, aqui estão alguns outros caracteres de formatação do cliente.nada para negativo, nada para 0 ”clique em OK e tudo ficará oculto. Claro, eles ainda podem ver aqui na barra de fórmulas, então não é perfeito, certo, aqui estão alguns outros caracteres de formatação do cliente.
Um asterisco indica “Exibir o próximo caractere como um redator de cheques”. Então aqueles ** estão dizendo “Sabe, preencha a área até o número com asteriscos”, mas você pode mudar isso, pode fazer aquele caractere o que quiser! Assim *! vai colocar todos os pontos de exclamação lá, vamos mudar isso, Ctrl + 1, vai mudar para * X, e vou pegar todos os X até aquele ponto. Portanto, você pode criar o efeito de escrivão, mas não precisa ser com um asterisco, você pode colocar o que quiser lá. Coloque uma vírgula no final, então este é 123456, formato de número personalizado com uma vírgula após o zero diz “Exibir isso em milhares, dividido por 1000”, eu coloquei o, K aqui para milhares. Quando você quer fazer isso em milhões, então há o nosso 0,0 ,, cada vírgula diz “Exibir, mas divida por outros 1000”. Então isso está se dividindo por um milhão,coloque o “M” para exibir milhões. Eu sei que algumas pessoas usam MM lá, minha irmã Alice (?) Que trabalha para Procter & Gamble, “MM” tem que estar entre aspas.
Agora aqui, este é aquele em que estamos forçando um número, se alguém inserir algo que não seja um número, a mensagem na 4ª zona o substituirá. Então se colocarmos o número 1, é ótimo, colocar -1, é ótimo, se colocarmos “Olá”, ele grita para nós, “Digite um número!”. Tudo bem, novamente, essas são fórmulas que estão puxando tudo o que eu digito de A para B. Você pode usar condições, certo, então o positivo, o negativo, o zero, você pode sobrescrever isso. Vou dizer vermelho se for 90 e você só pode ter duas condições e uma terceira zona para coisas que não são 90, formatação condicional antiga e antiquada. Outro, aqui está outro, isso é uma loucura, está colocando um sublinhado e então qualquer caractere deixa espaço suficiente para caber naquele caractere.Você vê muito isso com _ (para manter os números positivos alinhados com os números negativos, mas você pode realmente confundir com o Excel. Portanto, W é o caractere mais largo, então isso é colocar o primeiro dígito, e espaço suficiente para 2 W's e, em seguida, o segundo dígito e espaço suficiente para um N, e então o terceiro dígito e espaço suficiente para um I e, finalmente, o quarto dígito. Isso é útil para alguma coisa? Puxa, não sei, mas é gentil de selvagem, que você pode forçar espaçamentos diferentes entre os dígitos usando os sublinhados.mas é meio selvagem, você pode forçar espaçamentos diferentes entre os dígitos usando os sublinhados.mas é meio selvagem, você pode forçar espaçamentos diferentes entre os dígitos usando os sublinhados.
Tudo bem, datas, d / m / aa, você obtém um ano de 2 dígitos, expanda para aaaa, você obtém um ano de 4 dígitos, coloque mm, ele força um 0 à esquerda antes de 7, dd força um 0 à esquerda antes de 3 . Se você quiser usar algum formato de data de cobalto antigo, não coloque nenhum /, apenas todos os caracteres, AAAAMMDD. mmm indica a abreviatura do mês, mmmm indica o mês inteiro, mmmmm fornece o formato JASON. Por que eu chamo de formato JASON? Se você olhar para o Wall Street Journal, verá que seus shorts financeiros sempre têm JFMAMJJASOND. Em 1984, quero uma cópia em vinil de "Frankie & the Knockouts - Below the Belt" para responder à pergunta trivial "O nome de que homem pode ser soletrado usando as abreviações do mês?" E porque eu estava lendo The Wall Street Journal na escola, eu sabia que Jason era o nome e ganhei isso.Provavelmente dei em uma liquidação de garagem por $ 50c, meu mal. Tudo bem, dd fornece o dígito inicial antes da data, ddd soletra a abreviação, dddd fornece o nome do dia inteiro, nome do dia da semana, ddddd não faz nada. Você pode combiná-los como quiser. dddd soletra o dia, terça-feira, coloque a palavra “the” entre aspas, o próprio dia de mmm, dá a abreviatura, certo, então você pode combiná-los como quiser.
Tempo, uma coisa estranha com o tempo então, h: mm, ou AM / PM, então se você não colocar AM / PM, ele se força para o formato de 24 horas, se você fizer isso, você terá 14:15. Se você tiver hh, ele força o zero à esquerda antes do 2, você pode até adicionar .0 após os segundos para obter milissegundos. Agora aqui está a coisa bizarra, na verdade, antes de eu chegar à coisa bizarra, eu não sei, vamos fazer a coisa bizarra primeiro. Vamos fazer = SOMA de todos esses, então todos esses são 14 horas, 6 * 14 = 84, deve ser 84 horas, mas se eu formatar como um horário, está dizendo 1:30 da tarde, certo, e aqui está a coisa. Portanto, temos um número aqui, esta manhã 09/10/2016 às 6h, e eu só tenho fórmulas copiando isso e posso substituir, lembre-se, está formatando como uma fachada. Posso dizer "Ei, mostre-me apenas os anos, mostre-me o mês, mostre-me os dias,apenas me mostre a parte do tempo disso. ” E aqui neste SUM, quando estamos pedindo um tempo, estamos dizendo "Ei, ignore os anos, os meses, os dias, apenas me mostre o tempo." e o que realmente é, são três dias + 1:30 da tarde.
Portanto, para que isso seja exibido corretamente, você pode entrar no Tempo e procurar aquele que diz 37 horas ou ir para Personalizado e verá que colocar o (h) diz “Mostre-nos o número total de horas . ” Então se eu preciso ver as horas e os minutos, (h): mm vai me mostrar que, na verdade são 85,5 horas, entendi, eu tava anotando os 15 minutos, tentando colocar o 84 na minha cabeça, estava perto. Onde mais você pode usar colchetes? Então, 6 da manhã, você pode mostrar as horas, pode mostrar apenas os minutos, 6 da manhã são 360 minutos ou apenas os segundos usando os colchetes. Tudo bem, vamos voltar a um dos formatos de número que temos no formato de Contabilidade e ver se agora, depois desse episódio realmente longo, entendemos o que está acontecendo.
Então, o que isso quer dizer, _ (diz “Deixe tanto espaço quanto um (no valor positivo.” “*“ Diz “Repita espaços até chegar aos números.” O #, ## 0 indica “Estamos indo para usar o separador de vírgula, não coloque caracteres à esquerda se não tivermos a precisão, sempre coloque duas casas decimais. ”E então _) diz“ Deixe tanto espaço quanto), e isso é para os números positivos. ” OK agora, para os números negativos, é realmente bizarro aqui, antes do $ eles estão deixando tanto espaço quanto um (então, em outras palavras, eles nunca vão exibir isso. Novamente “*“, mas aqui eles ' agora vamos colocar o (e o). E então, para 0, deixe tanto espaço quanto o (e tanto espaço quanto o). Não coloque nada, estamos substituindo o zero pelo “-“, mas deixe tanto espaço quanto duas casas decimais, de modo quevai se alinhar com isso. E, finalmente, na 4ª zona, se eles digitarem o texto, deixe espaço suficiente para (e a) e, em seguida, coloque o texto.
Tudo bem, isso sempre foi um monte de bobagens do que eu, mas há significados específicos para todos esses personagens. É uma loucura que todas essas informações, no valor de 18 minutos de podcast, venham de 3 páginas deste livro, imagine quanto está lá em todas as 200 páginas do livro. Compre o livro, clique no “i” no canto superior direito, você vai gostar e eu vou agradecer. Tudo bem, episódio longo, longo: a formatação de números é uma fachada, o Excel armazena uma coisa e nos mostra outra. Diminuir Decimal e Aumentar Decimal é um ótimo exemplo disso, eles ainda armazenam todos os decimais, mas simplifica a exibição. Existem 11 formatos aqui, mas realmente para chegar até eles, você vai para o inicializador de diálogo, e então você pode escolher, algo e então modificá-lo em Custom se quiser. Falei sobre todos esses códigos de formatação de números personalizados,se você tiver dicas melhores, por favor, coloque-as nos comentários do YouTube.
Zero no código de formato diz "O Excel DEVE exibir o dígito, haja precisão ou não." Sinal de hash, sinal de libra diz que eles podem exibir o dígito se houver uma precisão, mas eles não precisam. O ponto de interrogação diz “Deixe um espaço para o dígito se não houver precisão suficiente”. Pode haver até 4 formatos separados por ponto e vírgula, dessa forma temos um número diferente para positivo, negativo, zero e texto. 2 formatos, o 1º é para não negativos, o 2º é para negativo, se você tinha um 3º formato, o terceiro formato é para 0, o 4º formato é para texto, se houver. Tudo bem, podemos exibir texto com um número ou exibir texto em vez do número, “Vencedor”, “Perdedor” ou “Para cima”, “Simples”, “Para baixo”. Você pode especificar uma cor para cada zona, para alterar os códigos de cores visite Opções do Excel, Salvar, Cores.Asterisco qualquer caractere para repetir esse caractere antes dos números, como um redator de cheques faria. Coloque uma vírgula no final para dividir por mil, duas vírgulas para dividir por milhões, 3 vírgulas para dividir por bilhões e assim por diante. Podemos usar condições na zona e, em seguida, sublinhar X deixará um espaço do tamanho de X. Tudo bem, as diferentes variações de meses, se você colocar um dígito, dois dígitos, a abreviatura, soletrar ou apenas a primeira inicial . Dias, um dígito, dois dígitos, abreviar ou soletrar, não há quinto. E, finalmente, para obter tempos superiores a 24 horas, você deve colocar esse h entre colchetes e outras variações dele.Podemos usar condições na zona e, em seguida, sublinhar X deixará um espaço do tamanho de X. Tudo bem, as diferentes variações de meses, se você colocar um dígito, dois dígitos, a abreviatura, soletrar ou apenas a primeira inicial . Dias, um dígito, dois dígitos, abreviar ou soletrar, não há quinto. E, finalmente, para obter tempos superiores a 24 horas, você deve colocar esse h entre colchetes e outras variações dele.Podemos usar condições na zona e, em seguida, sublinhar X deixará um espaço do tamanho de X. Tudo bem, as diferentes variações de meses, se você colocar um dígito, dois dígitos, a abreviatura, soletrar ou apenas a primeira inicial . Dias, um dígito, dois dígitos, abreviar ou soletrar, não há quinto. E, finalmente, para obter tempos superiores a 24 horas, você deve colocar esse h entre colchetes e outras variações dele.
Obrigado por participar até o fim! Nos vemos na próxima vez em outro netcast de!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2044.xlsm
