Como mover sinal na posição errada (Excel)

Uma aluna apareceu com o seguinte problema, que pode ser comum a outras pessoas: “quando importo uma tabela de um sistema contábil e colo no Excel, os números ficam com sinais de positivo ou negativo à direita e não à esquerda como é o normal e tenho de deletar um por um, às vezes são dezenas”

Isso pode ser resolvido facilmente usando funções de texto (ESQUERDA, DIREITA, NÚM.CARACT) e VALOR (função para conversão de texto para valor numérico) combinadas na função lógica SE.

Exel sinal trocado

  Suponha que na célula A2 tenha o primeiro número a ser “convertido”, a fórmula (em B2) ficará:

=SE(DIREITA(A2)=”+”;VALOR(ESQUERDA(A2;NÚM.CARACT(A2)-1));VALOR(“-“&ESQUERDA(A2;NÚM.CARACT(A2)-1)))

Depois disso basta “arrastar” a fórmula (ou copiar a célula e colar nas demais) para as próximas linhas…

Histograma no Excel 2003

 Como criar um gráfico de Histograma no Excel?   
   
A ferramenta Histograma fica no menu Ferramentas, opção Analise de Dados…
menu Ferramentas, opção Analise de dados...

Menu Ferramentas, opção Analise de dados...

(Observação: estou usando a versão 2003 do Excel, mas deve ser parecido para outras versões) Porém, se a opção Análise de Dados não tiver sido anteriormente usada, talvez não esteja instalada. Nesse caso, faça assim, vá no menu Ferramentas e procure a opção Suplementos…

menu suplementos

Menu Ferramentas opção Suplementos...

Marque a opção Ferramentas de análisepara deixá-la disponível no Excel:

janela Suplementos opção Análise de Dados marcada

Janela Suplementos, opção Ferramentas de análise marcada

Após instalada, a opção Análise de Dados será visível no Menu Ferramentas.

Exemplo de Criação de Histograma

Eu tenho uma lista de notas de alunos que gostaria de criar um histograma. São 12 notas:

notas de provas de alunos

notas de provas de alunos

A primeira coisa que faço é ordenar estas notas selecionando toda a coluna e clicando no ícone Classificação Crescente:

ícone classificacao crescente

ícone classificacao crescente no Excel 2003

(Observação: posso fazer o mesmo selecionando a coluna e indo no menu Dados, opção Classificar) Assim eu sei qual é o limite inferior e superior dos meus dados:

notas ordenadas

Com as notas ordenadas tenho os limites dos meus dados

Agora que eu tenho os limites inferior e superior que desejo usar no meu histograma e coloco eles em um bloco, no meu caso fiz (manualmente) 5 faixas de valores:

faixas de valores

Faixas de valores que desejo agrupar no histograma

A faixa de valores possui um valor “de” (0,11 é o primeiro) e um valor “até” (0,2 é o valor “até” da primeira faixa). Na verdade só precisamos da segunda coluna com os valores “até”, mas coloquei aqui as duas colunas para ficar mais “didático”. Tendo estas faixas de valores, vou no menu Ferramentas e seleciono Análise de Dados:

menu Ferramentas, opção Analise de dados...

Menu Ferramentas, opção Analise de dados...

Na janela Análise de Dados seleciono a opção Histograma:

janela analise de dados - histograma

Na janela Analise de Dados selecione a opção Histograma

Preciso selecionar meus dados. Primeiro o Intervalo de entrada:

janela histograma

Janela Histograma - selecionar intervalo de entrada

Isso fará uma mini-janela (onde o intervalo será entrado) surgir:

Selecione o intervalo de entrada

Aqui ficará o intervalo de entrada

É hora de selecionar com o mouse o intervalo de meus dados na planilha (ou escrever o intervalo na janela acima, se preferir):

selecao do intervalo do histograma

Selecione o intervalo de dados para o Histograma

Depois faça o mesmo para selecionar as faixas de dados que escolhi, o Intervalo do bloco:

janela de Histograma - seleção intervalo bloco

Janela de Histograma - Selecionar bloco

Seleciono na planilha a faixa do meu bloco que está na segunda coluna (valores “até”):

seleção intervalo bloco

Seleção de intervalo do bloco

E obtenho em outra planilha o seguinte resultado:

Resultado Histograma

Resultado da Ferramenta Histograma

Observe que como selecionei como bloco os valores até a última linha do meu bloco, o Excel criou uma opção “mais” (com zero valores). O melhor seria ter selecionado até a penúltima linha. Com estes dados ainda selecionados, faço um gráfico de colunas através do assitente de gráfico:

Ícone do Assistente de Gráfico

Ícone do "Assistente de Gráfico"

Também posso abrir a janela do Assistente de Gráfico indo no menu Inserir, opção Gráfico. Selecione o Gráfico de colunas (ou outro, se quiser) e clique em Concluir:

janela assistente de grafico

Janela Assistente de Gráfico

O resultado será este:

Resultado do Gráfico

Resultado do Gráfico de Colunas para a tabela de Histograma

Novamente, o melhor seria não ter incluído no gráfico a opção “Mais”, já que isso não nos traz nenhuma informação, mas deixei aqui para mostrar o exemplo mais simples. Pratique com outros dados! Dúvidas? Entre em contato!

Precisa de Consultoria em Excel em Macros? Olhe este link: http://prog.naotemnome.com/excel/index.html

Vencimento – Formatação Condicional

Em minhas aulas de informática tenho algumas dicas interessantes, pouco valorizadas pelos alunos, uma delas é sobre o seguinte tema: formatação condicional – Hoje, amanhã e depois de amanhã: como fazer para formatar datas de contas a vencer hoje ou dentro dos próximos dias?

O link é:

http://www.naotemnome.com/fapa/_lista16/_completa.html#12

Basicamente queremos alterar a cor da célula que possui datas iguais a hoje ou dos próximos dias. Supondo que a data a formatar esteja na célula C2, na formatação condicional, a “condição 1” deve mostrar “A FÓRMULA É” e a fórmula usada dentro da formatação condicional é:

=E(C2<(HOJE()+3);C2>=HOJE())

Esta é uma função lógica, a função E( ). Ela resulta no valor VERDADEIRO caso as sejam verdadeiras as duas condições (ou mais) separadas por ponto-e-vírgula e FALSO se uma das condições for falsa.

Se eu quiser ao invés desses 3 dias, mostrar as datas de amanhã e mais 6 dias (7 dias no total), mudaria a fórmula para:

=E(C2<=(HOJE()+7);C2>HOJE())

vencimento - formatação condicional

resultado da formatação condicional de datas a vencer

Dúvidas? Deixe um comentário com sua pergunta ou escreva diretamente para o meu e-mail!

Como não exibir certos valores em planilhas

É possível criar uma fórmula ÉERROS dentro de uma função SE para evitar nas células aqueles #N/D (dado não disponível ) #DIV/0! (divisão por zero) ou #NÚM! (argumento não é um número), conforme mostro na minha dica “Planilha sem códigos de erros” em http://www.naotemnome.com/fapa/_lista28/_completa.html#dica

De modo geral o truque é usar, no lugar da sua fórmula, a seguinte fórmula:

=SE(ÉERROS([suafórmula]); “DEU ERRO”; [suafórmula])
(você pode usar aspas com espaços para não mostrar nada quando surgir um erro)

onde lemos [suafórmula] deve ser colada a fórmula original que estava na célula (sem os colchetes).

Entretanto, frequentemente queremos coisas mais simples, como, por exemplo, evitar que os valores zero de uma planilha apareçam, para facilitar a vizualização.

Como fazer para que os valores iguais a zero, seja por fórmula ou inseridos manualmente, sejam vistos como espaços em branco sem a utilização de uma fórmula? Simples, usamos a formatação condicional!

tabela de valores que desejo fazer os zeros "desaparecer"

Selecione todo intervalo que tem números que deseja desaparecer os zeros e, no menu “Formatar“, selecione “Formatação Condicional“. Na janela que surge, troque as opções mostradas para “o valor da célula é”, “igual a” e digite o valor 0 (zero):

janela de formatação condicional

Feito isso, ainda nesta janela, clique no botão “Formatar…” e troque a cor para a cor branca:

formate a cor da fonte para branco

Claro que se suas células tiverem um fundo de outra cor, a cor da fonte deve ser trocada para a mesma cor do fundo da célula. O resultado da formatação condicional será este:

valores iguais zero não aparecem

valores iguais zero não aparecem mais

Formato de fonte “subscrito” no EXCEL

A maioria das pessoas sabe como formatar as letras do Excel, basta clicar na célula que desejamos formatar e ir no menu “Formatar“, escolher a opção “Células…” (ou teclar CTRL+1) que a janela Formatar Células abrirá. Devemos clicar na guia (ou “aba”) “Fonte” e se quisermos deixar o texto em formato subscrito basta deixar marcada esta opção, veja:

janela formatar célula

janela formatar célula

O que algumas pessoas desconhecem, é que o Excel (similar ao Word dentro de um parágrafo) permite que parte de um texto seja formatado dentro de uma célula. Para isto basta selecionar a parte do texto a ser formatada. Por exemplo, quero escrever a fórmula química H2SO4 dentro de uma célula com os números formatados como subscrito:

selecione um algarismo

selecione o texto a ser formatado

Basta selecionar o texto (na figura estou selecionando o algarismo 2 a partir da barra de fórmulas) e usar o atalho CTRL+1 para chamar a janela “Formatar células”, marcando em subscrito como foi observado na figura anterior. O resultado pode ser visto na célula (embora não seja visto no texto da barra de fórmulas) Depois faça o mesmo para o algarismo 4 e pronto!

SOMASE duplo no Excel

Para quem tem o EXCEL 2007, existe uma nova função o SOMASES (com uma letra “s” no fim) que permite mais de uma condição.

Por exemplo, quero somar a coluna “total” (de C3 a C12) apenas para o “joão” e apenas para a data “14/01/2010”. No Excel a partir da versão 2007, isso é possível com esta nova função SOMASES.

tabela excel com 3 colunas

Somase com 2 condições: como fazer?

Para o Excel 2007 ou posterior, teremos:

=SOMASES(C3:C12;A3:A12;”joão”;B3:B12;DATA(2010;1;14))

(Observação: não pude testar, não tenho o Excel 2007)

Bom… Para a maioria dos coitados que como eu só tem o 2003, é possível resolver o problema adicionando uma coluna extra onde os critérios “joão” e data(2010;1;14) sejam usados na fórmula lógica

=E(A3=”joão”;B3=DATA(2010;1;14))

para mostrar os resultados FALSO ou VERDADEIRO. Abaixo, coloquei a fórmula acima na célula E3 e arrastei-a para as outras células abaixo:

coluna extra para o somase Agora posso fazer um SOMASE normal para obter o resultado desejado: =SOMASE(E3:E12;VERDADEIRO;C3:C12).

Outra maneira de resolver o problema (minha solução favorita) é, ao invés de escrever FALSO ou VERDADEIRO, concatenar os nomes e as datas em uma única célula. Fica esquisito, porque para o Excel, uma data é o número de dias a partir de primeiro de janeiro de 1900, ao usar em E3 a fórmula

=A3&B3

ou

=CONCATENAR(A3;B3)

ficaremos com a planilha assim:

planilha com fórmula somaseA fórmula SOMASE usada na planilha acima é:

=SOMASE(E3:E12;”joão40192″;C3:C12)

o número 40192 é o número de dias existentes entre 1/1/1900 e 14/1/2010 incluindo ambos.

Por fim, uma solução mais esquisita ainda, mas que dispensa o uso de uma coluna extra: também é possível resolver o problema por uma fórmula menos conhecida, a fórmula SOMARPRODUTO. Neste caso, basta usar a fórmula:

=SOMARPRODUTO(- -(A3:A12=”joão”);- -(B3:B12=DATA(2010;1;14));- -(C3:C12))

Observe que a função DATA requer que ano, mês e dia sejam digitadas exatamente nesta ordem. Os dois sinais negativos na fórmula são uma maneira de converter resultados numérico para valores lógicos e “enganar” a função SOMARPRODUTO.

Bom, depois de toda essa trabalheira, cada vez acho mais conveniente migrar para o Office 2007…

Dúvidas? é só mandar um e-mail, visite minha página de Informática: www.naotemnome.com/fapa lá tem o meu e-mail… E dicas para Excel, basta olhar no índice!