Formate um número com casas decimais opcionais no Excel

Question

Eu tenho números em células no Excel. Eu quero os números formatados de modo que, se eles tiverem casas decimais, eles mostrem um máximo de dois, e se eles não tiverem casas decimais, ele não mostrará nenhum.

Por exemplo.

• 15 deve ser formatado como 15, NÃO 15.00
• 14.3453453 deve ser formatado como 14.35
• 12.1 deve ser formatado como 12.1
• 0 deve ser formatado como 0

O código de formato personalizado mais próximo que eu encontrei é 0.## . Infelizmente, isso formata 15.00 como 15. (observe o ponto decimal extra).

Para complicar ainda mais o problema, a planilha é resultado de uma exportação do SQL Server Reporting Services. Portanto, não são possíveis macros. Oh, bem, parece que 0.## é minha melhor aposta, e eles podem apenas viver com o período extra.

    

Answer 1

Como alternativa, você pode resolver o problema do ponto decimal "opcional" usando a seguinte solução:

Formato numérico: General;[Red](General)

Isso adicionará a casa decimal e o valor fracionário de acordo, enquanto formata os números negativos de maneira mais legível.

Quanto à pergunta original do pôster, você ainda precisa arredondar / truncar os pontos decimais "extras" usando uma fórmula. No entanto, esta é uma fórmula simples de =ROUND(<value>,<desired decimal places>) que não é extremamente computacionalmente cara.

Exemplos:

2500 -> 2500
0.25 -> 0.25
-2500 -> (2500)
-0.25 -> (0.25)

    

Answer 2

Aplique Formatação Condicional para números não decimais.

Por exemplo, A1 é a célula de destino.

1. Formatar A1 como "###, ###. 00". Isso será usado para o número decimal.

2. Defina a formatação condicional para números não decimais.

   • Condição: valor da célula igual a = TRUNC (A1).
   • Formato: ###,###

Abaixo está o resultado:

12       =>  12
14.231   => 14.23
15.00000 => 15
17.3     => 17.30

    

Answer 3

Os formatos personalizados do Excel podem fornecer uma resposta parcial

Formatos personalizados para números no Excel são inseridos neste formato:

• formato numérico positivo, formato numérico negativo, formato zero, formato de texto

Um formato que se aproxima de sua exigência, mas que fica na casa decimal para números sem decimais, é:

• , ##. ??; (#, ##. ??); 0

Exemplo:

• 15 é exibido como 15.
• 14.3453453 é exibido como 14.35
• 12.1 é exibido como 12.1
• 0 é exibido como 0

Answer 4

Eu encontrei isso recentemente no Excel 2007 e acabei usando a função 'TRUNC' nas células de valor:

  value  =TRUNC(B5,1)
      0      0
      5      5
    5.4    5.4
  65.43   65.4
765.432  765.4

Trabalhei exatamente do jeito que eu queria ...

    

Answer 5

Aqui está um método usando formatação condicional.

1. Clique com o botão direito no seu celular, escolha "Formatar célula"
2. Selecione "Personalizado"
3. Insira "0. ####" (adicione mais ou menos #s para controlar o número máximo de casas decimais)
4. Clique em OK
5. Com a célula ainda selecionada, use "Formatação condicional" (pode ser um item de menu em Formatar ou botão em Início, dependendo da sua versão do Excel)

6. Adicione uma nova regra, com base na fórmula "= MOD (H32,1) = 0"

   edit - melhor fórmula é "= MOD (ROUND (H32,2), 1) = 0" com o '2' sendo o número desejado de casas decimais. a fórmula anterior deixa o ponto decimal à direita se o número for arredondado para um inteiro.

   Substitua H32 na fórmula com o ID do seu celular, mas CERTIFIQUE-SE DE NÃO HÁ SINAIS DE $! (Nenhum sinal $ garante que você copie o formato para outras células)

7. Para o formato dessa regra, selecione a guia numérica, escolha "Personalizada"

8. Desta vez, insira a fórmula "0"
9. Clique em OK o suficiente para retornar à planilha (varia entre as versões do Excel)

Lá vai você, aproveite com responsabilidade! Se você quiser copiar o formato para outras células, lembre-se de que você pode copiar a célula e usar "Colar especial" com a opção "Formatar".

    

Answer 6

Lembre-se de que, no Reporting Services, você também pode escrever uma expressão para a formatação de números. Na minha situação, eu usei

iif(floor(Fields!numericFieldName.Value)=Fields!numericFieldName.Value,"0","0.##")

como a expressão de formato numérico. Isso produziu um arquivo do Excel com dois formatos de células, selecionado pelo fato de o valor ser ou não inteiro.

    

Answer 7

Você / seus usuários estão inserindo valores diretamente nas células ou estão sendo preenchidos por uma fórmula ou macro?

Se as células não estiverem sendo preenchidas diretamente por um ser humano, você poderá armazenar os valores calculados em um intervalo oculto e exibir o texto formatado para o usuário com uma fórmula como esta:

=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#"))

(onde 'A1' é a célula que está sendo referenciada)

A fórmula exibirá valores como este:

 -------------------------
 |Original     |Formatted|
 |-------------+---------|
 |         15  |       15|
 | 14.3453453  |    14.35|
 |       12.1  |     12.1|
 |          0  |        0|
 |    -15.123  |   -15.12|
 |      1.004  |        1|
 -------------------------

NB: A saída da fórmula é uma cadeia de texto, não um numérico, por isso:

• A saída padrão é alinhada à esquerda.
• Você não pode usar a saída em nenhum outro cálculo (em vez disso, você deve usar a célula original sendo referenciada)

Answer 8

Formate a célula como 'Geral' e depois, na validação de dados, restrinja os valores a decimais

    

Answer 9

Isso não precisa ser tão complicado - pode ser feito exclusivamente com a formatação condicional. Eu tenho o Excel 2010, então isso pode não funcionar para versões anteriores.

1. Formate as células para serem gerais. O número de casas decimais para alguns números pode ser muito alto.
2. Realce uma célula e clique em Formatação condicional. Clique em Nova Regra e depois em "Usar uma Fórmula".
3. Use a fórmula = (B1-INT (B1)) > 0 (onde B1 é a célula que você está formatando)
4. Clique no botão Formato e escolha Personalizar. Digite [= 0] 0;. ## no campo Tipo.
5. Pressione Enter várias vezes.
6. Clique na formatação condicional novamente, clique em Gerenciar regras e edite a regra que você acabou de criar. Altere o intervalo no campo "Aplica-se a" para cobrir o intervalo que você deseja cobrir.

Tudo pronto.

    

Answer 10

Minha resposta remove o ponto decimal AND Remove 0 valor

na gravação de formatação condicional:

General;#;;@

Isso deve funcionar.

    

Answer 11

Além da ótima resposta de Lucas acima, há uma variação baseada em seu trabalho. Eu não queria ter um zero na célula se não houvesse nenhum valor. Deixe seu formato de célula como geral e use esta fórmula:

=IF((A1=""), "",IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#")))

Novamente, onde A1 é a célula que está sendo referenciada.

    

Answer 12

Usando a função Round para fornecer valores significativos. Isso não altera o número de casas decimais exibidas no entanto. Apenas formate a célula com o formato de número general .

ROUND(MyValue,Sig.figs - 1 - INT(LOG10(ABS(MyValue)))

    

Answer 13

B1=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.##"))
C1=IF(ROUND(B1,2)=INT(B1),TEXT(B1,"0"),TEXT(B1,"0.##"))

Não há mais pontos decimais órfãos.

    

Answer 14

Esta parece ser a maneira mais simples de ter um Decimal mostrado quando há números fracionários - e nenhum decimal é mostrado para números inteiros:

=TEXT(ROUND(Base_Number,Max_Decimal_Places),"general")

ou com referências de células:

=TEXT(ROUND(A1,2),"general")

Dá

15 = 15
9.23432234 = 9.23