Formatação Condicional e Ref. Relativas/Absolutas

Formatação Condicional. O que é e como fazer!

Entre tantos recursos que o Excel oferece, o trabalho de análise de dados para tomada de decisão é classificada como uma das mais importantes mesmo que seja pouco explorada Poe aqueles que se utilizam do produto. Destacamos aqui, a formatação condicional.

Não raro é o fato de que, no acompanhamento de informações relativas ao andamento de uma empresa ou seja lá o que for, ocorrer uma situação onde o resultado fica abaixo daquele esperado.

Vejam o exemplo na figura abaixo, onde temos uma hipotética análise de vendas de nossos colaboradores.

Definimos como meta que cada vendedor deveria vender em cada mês, um mínimo de 9.000,00 e vejam, duas vendas ficaram abaixo e foram realçadas em vermelho. Já quem vendeu entre 9.000,00 e 10.500,00, foram destacadas em azul claro e as vendas acima de 11.500,00, foi marcado como verde.

Notem que, nas condições que definimos, não existe formatação para os valores que se sejam maior que 10.500,00 e menor que 11.500,00. Neste caso.a venda de Antonio em fevereiro, no valor de 11.000,00, não sofreu qualquer tipo de formatação. Vamos seguir o passo a passo.

Vamos criar uma planilha como mostra a figura abaixo.

Na linha 6, cada coluna tem uma fórmula ou seja:

Em B6 temos =soma(b2:b5)

Em C6 =soma(c2:c5) e assim por diante até a célula F6

Já na célula F2, a fórmula é =soma(B2:E2) e mantendo o número da linha, até a célula F5.

O primeiro passo é selecionar o intervalo de células que queremos aplicar a formatação condicional, no nosso exemplo, B2:E5. Selecionado, vamos acionar o recurso.

Pela Guia Inicio, selecione a Opção Formatação Condicional e em seguida, Nova Regra.

Na tela Seguinte....

Clique na segunda opção Formatar apenas células que contenham... e na caixa abaixo, procure Valor da Célula, selecione é menor do que e digite 9000 na respectiva caixa, como na figura acima.

No quadro seguinte, vamos formatar a cor que queremos, para isso clique no botão Formatar... e na caixa que se abre, marque a opção Negrito e em cor, escolha vermelho. Clique e OK e novamente OK. Se tudo certo, teremos já dois valores em vermelho.

Para efeito de nosso exemplo, vamos continuar. A segunda opção coloca em azul quem vendeu entre 9.000 e 10.500. Repita os passos do primeiro exemplo mas veja o detalhe quando aciono a opção esta entre.. 

Definido os parâmetros conforme figura, basta formatar a célula  para negrito e na cor azul claro. 

Viram a diferença?

Nossa última condição é colocar verde para os valores acima 11.500 e seguindo os mesmos passos, vejam a figura.

Formate a célula em negrito e na cor verde, clicando em OK em ambas as caixas.

Não se esqueçam, enquanto se utiliza a opção formatação condicional o intervalo a ser formatado deve estar sempre selecionado.

Mas existe casos em que esquecemos alguma coisa ou então tenhamos que alterar os dados definidos. Para isso existe, dentro da opção Formatação Condicional, uma outra opção que fica no final do menu suspenso, Gerenciar Regras... No nosso caso, ao clicar nessa opção teremos:

Vejam que a caixa de diálogo mostra as condições que fizemos. Basta selecionar uma delas e acionar o botão Editar Regras e executar as alterações necessárias. Não se esqueçam, o intervalo deve estar selecionado.

Para excluir toda a formatação condicional escolha a opção Limpar Regras e Limpar Regras das células Selecionadas, conforme figura.

Aqui uso a versão 2007 do Excel mas o exemplo pode ser usado também em versões anteriores, selecionando as células que se quer formatar e pelo menu, ir em Formatar -> Formatação Condicional.

O exemplo acima é pequeno mas para quem analisa dados em grandes planilhas, é um recurso muito interessante, poupa tempo e evita possíveis erros.

Referências Relativas e Absolutas, preste atenção!

Um dos recursos que usamos com frequência, é aquele de copiar e colar no Excel. Principalmente quando temos uma fórmula em uma determinada célula e queremos transferi-la para outras células. É o procedimento correto? Sim, é! 

Mas tem um porém, em vários casos a facilidade se torna uma dor de cabeça pois uma fórmula simples sem definição correta do que queremos, gera problemas, em razão daquilo que chamamos de referências Relativas e Absolutas. Vejam o modelo abaixo:

Para nosso estudo, monte uma planilha como acima lembrando que em B3 possui a fórmula =Soma(B4:B14) e em C3, temos =Média(C4:C14).

Vamos ao exemplo. Na célula D3 inserimos a fórmula =-Soma(D4:D14). Já em D4, inserimos =B4*C4 e Enter, o resultado foi 1.627,20 (Qte * Unitário).

O próximo passo é selecionar a célula D4 novamente, copiar seu conteúdo e selecionar em seguida as células abaixo, D5 até D14 e mandar Colar. O resultado é:

Tudo certo? Sim! Selecione a célula D12 e veja se a fórmula lá inserida não é =B12*C12. O que aconteceu é que o Excel, no momento, está usando o formato de Referência Relativa ou seja, conforme foi-se mudando a linha a fórmula foi automaticamente ajustada.

Vamos fazer a mesma operação na coluna E. Para eu saber quanto que cada item significa em porcentagem no estoque, devo dividir ela pelo total e obviamente, o total será 100,00% de tudo. Então, em E3, vamos colocar a fórmula para o calculo do percentual, que será =D3/D3*100 e o resultado, 100,00. Até aqui tudo bem.

Agora, vamos copiar o conteúdo da célula E3 e colar no intervalo E4 até E14, o resultado?

Tem alguma coisa errada, não? Deu tudo 100,00. É fácil ver o problema que ocorreu. Vejam na célula E10 que a fórmula é =D10/D10*100. Mas se o valor deveria ser o resultado da divisão de D10 por D3, fica claro que usando a Referência Relativa nesse caso não funciona, temos que usar uma Referência Absoluta para as linhas e como fazemos isso? Inserindo o caractere $ antes do número da linha. 

Sendo assim, a nova fórmula em E3 deve ser =D3/D$3*100. Veja que a primeira condição fica aberta para alterar quando colarmos nas células seguintes e a célula que divide fica como absoluto, não se altera no processo de copiar e colar, o resultado.

Nesse caso, vimos a questão das linhas. Com as colunas acontece a mesma coisa, vamos a um exemplo.

Para apurar o percentual de cada vendedor, devemos dividir o valor total de suas vendas pelo total vendido. Sem utilizar a Referência Absoluta, em B8 insira a fórmula =B7/H7*100, o resultado, 17,01%. Mas se a gente copiar o conteúdo de B8 e colar nas colunas seguintes, C8 até H8, veja o que ocorre:

#DIV/0! É um erro que ocorre quando tentamos dividir qualquer número por zero, impossível. Vejam na célula C8 a fórmula lá existente, =C7/I7*100. C7 é o valor a ser dividido, correto! Já I7 não tem valor algum e por isso mesmo, sendo zero, deu o erro mostrado. O que devemos fazer? Tornar a coluna como Absoluta usando o sinal $. A fórmula em B8 seria então =B7/$H7*100. Teremos então:

Vejam que, no caso da linha o sinal $ antes do número da linha e, no caso das colunas, o sinal vai antes da letra identificadora da coluna.

Acreditamos que fica aqui desmistificado a questão das células com referências relativas e absolutas e o motivo que, geralmente o Excel faz uso do símbolo $ nas fórmulas por ele criado.

Pode ocorrer de ser necessário que uma célula e apenas ela seja base para qualquer cálculo, nesse caso, usamos $ tanto nas linhas quanto nas colunas, por exemplo $B$3. Quando se copiar e colar essa referência em qualquer local da planilha, será utilizado o valor que consta dentro da célula B3.

Nota:

Considerando que um pequeno post leva horas para ficar pronto e além disso minhas atividades profissionais, quero informar que a partir da próxima semana iremos publicar dicas e truques do Office 3 vezes por semana, segundas, quartas e sextas. Os emaisl serão lidos e respondidos, se for o caso, diariamente.Para contato escreva para excelfoz@hotmail.com

Monte exemplos e estude as dicas mostradas, pode ser que não use agora mas lá na frente...

Dicas preciosas do Microsoft Word

Nosso assunto hoje será o Microsoft Word. Iremos apresentar algumas dicas úteis para seu dia a dia no uso da ferramenta.

Usando a área de trabalho no processo de copiar e colar

Um bom recurso que tanto o Word como o Excel oferece, é a possibilidade de uso da área de trabalho no processo de copiar e colar. Com certeza você deve estar pensando, Control C e Control V faz a mesma coisa. Claro que faz, mas fica uma pergunta! E se você desligar o computador?

Use o botão Maximizar/Restaurar de sua janela do Word para que uma parte da Área de Trabalho fique visível, conforme figura ao lado.

Veja a figura abaixo. O espaço à direita é a área de digitação do Word. E no lado esquerdo, uma parte da Área de Trabalho visível. Como você deve fazer. 

Selecione o texto que você quer gravar para uso posterior e com a ajuda do mouse, arraste para cima da Área de Trabalho. Veja na primeira figura que é criado um link com a denominação do texto.

Para recuperar e inserir o que você transferiu para um texto, arraste o documento da Área de Trabalho para o local onde você quiser inserir o texto e pronto. Pode desligar sua máquina à vontade. É muito útil para advogados que sempre necessitam usar um bloco predeterminado para uso em suas petições.

Padrão da fonte em seus documentos

Já vi muita gente que faz um texto usando o tipo de letra Times New Roman e depois, seleciona tudo e muda para Verdana. Será que você não é um desses? Saiba que você pode e deve padronizar o tipo de fonte e tamanho que usa com frequência. Para isso, proceda da seguinte maneira:

Microsoft Word 2007 em diante

Antes de iniciar o texto ou mesmo depois disso, acione o botão de fonte como mostra a figura. Será aberta uma tela como abaixo:

Escolha os detalhes da fonte que quiser e quando pronto, clique no botão Padrão na parte inferior da caixa de diálogo. Abre-se uma tela explicativa, basta concordar e pronto. A partir de agora, todos  os documentos que for iniciado será com a formatação escolhida.

Para versões anteriores o procedimento é o mesmo mas para isso vá, pela barra de menu em formatar -> fonte, escolha a fonte desejada e clique no botão padrão...

Maiúsculas e minúsculas rapidamente

Guarde com você essa combinação de teclas (Shift + F3). Quer saber o que faz? Selecione então uma palavra no Word ou um parágrafo. Pressione Shift+F3 e veja o resultado. Sem sair da seleção, repita o pressionamento das teclas e faça novamente.

Simples, na primeira vez, se tudo em minúsculas passa tudo para maiúsculas e repetindo a operação, volta tudo para minúscula. Na terceira vez, a primeira letra que fica em maiúscula.

Quantas letra e palavras tem meu texto?

Por curiosidade você quer saber a quantidade de palavras ou letras em seu texto. Faça assim:

No Word 2007, clique na aba Revisão e clique na opção Contar palavras.

Até o momento o resultado do presente post deu nisso!

Para o Word 2003, vá em Ferramentas -> Contar palavras.

Hoje foi isso. Não esqueça, estamos aguardando suas dúvidas, sugestões ou qualquer outro assunto que queira falar comigo pelo email excelfoz@hotmail.com.

Usando o Atingir Meta do Microsoft Excel

Otimização, que nada mais é do que a resolução de um sistema de equações, tem no Atingir Metas do Excel, mais um desses recursos obscuros, escondido em algum lugar na barra de ferramentas e a grande maioria o despreza, por não conhecer sua utilidade na busca de uma determinada resolução para um problema qualquer. Vejam a figura a seguir e crie uma igual para acompanhar nosso post.

Montamos um exemplo entre as colunas A e C e as colunas em vermelho é exatamente igual a primeira, simplesmente faremos ali os cálculos e poderemos comparar os resultados mais facilmente.

São três colunas, uma para o nome da conta, a outra para o valor apurado e uma terceira com o percentual total em razão das vendas.

A receita de vendas será sempre 100%, já temos despesas com salários, operacionais (água, aluguel, telefone, energia elétrica, etc...) e outras despesas, aquelas que não se enquadram nas duas primeiras.

Apuramos o lucro bruto (Receita – Salários – Operacionais – Outras). Em cima do lucro bruto pagamos 25% de imposto ao governo (que nunca retorna) e nos sobra o lucro líquido (Lucro Bruto – Imposto).

Vejam na coluna de percentual (coluna C), no nosso exemplo,de tudo que vendemos e tirando-se as despesas e os impostos, nos sobra míseros 21,39% de lucro (É raro empresa que tem tal margem líquida de lucro entre as pequenas).

Agora, analisando-se friamente os números, onde posso cortar despesas de tal maneira que meu lucro fique em 25%, por exemplo? Aqui entra o Atingir Metas.

Vamos ver como funciona. Na Guia Dados do Excel, temos a opção Teste de Hipóteses

Quando acionada, é apresentada a seguinte Caixa de Diálogo:

Temos 3 opções, Definir célula, Para valor e Alternando célula. Como falamos acima, como reduzir uma despesa de tal maneira que nosso lucro seja de 25% com relação as nossas vendas?

Como queremos um resultado de 25%, teremos que Definir a célula relativa à ela, no nosso caso, G11. (Estou usando o exemplo em vermelho para se comparar depois com o original mas a esquerda.)

Na opção Para Valor, insiro 25 e Alternando Célula, escolho somente uma opção, ou Despesas Operacionais ou Outras Despesas, já que em salário só posso alterar se mandar gente embora, que não é o caso.

Vou escolher Outras Despesas cujo valor se encontra em F5, insiro e clique em OK, vejam o resultado...

Notaram que nosso Lucro Líquido aumentou em relação ao exemplo anterior mas por outro lado, aumentou também o Imposto a Pagar e caiu o montante que poderíamos gastar com Outras Despesas.

Usando o mesmo procedimento, quanto deverei vender para ter um lucro de 30% nas mesmas condições?

Os passos foram os mesmos, definimos a célula G11 como partida e com uma valor de 30 só que dessa vez, variando no valor da venda ou seja, a célula F2. Olha só o resultado acima.

A solução encontrada pelo Atingir Meta foi mostrada em dois exemplos e é útil quando temos somente uma variável. Quando o assunto exigir mais de uma variável, devemos usar o Solver mas isso será assunto para um futuro bem próximo, aqui mesmo.

No mais, agora cabe à você por em pratica aquilo que aprendeu dentro daquilo que faz. Se tem números reais em sua empresa ou negócio, ótimo. Caso contrário, tente montar algo fictício e estude.

Em caso de dúvidas ou sugestões, escreva para excelfoz@hotmail.com pois estou esperando seu contato.

Configurando o Microsoft Outlook 2007

Não poderia ser diferente. Se o assunto aqui é o uso das ferramentas do Microsoft Office então para gerenciar minha conta de e-mail, calendário, tarefas, entre outras atribuições, o software em causa tem que ser o Outlook e vamos começar a usá-lo a partir de agora.

Quando abrimos o aplicativo pela primeira vez, um Assistente para Inicialização é apresentado nos guiando na tarefa de configuração do Outlook 2007 (nas versões anteriores o procedimento é a mesma coisa, o que muda são as telas de apresentação).Esse exemplo é para contas do Hotmail. Clicamos em Avançar.

Nesse momento ele nos pede se gostaríamos de configurar o programa para acesso a um servidor de e-mail, onde poderemos receber e enviar e-mail sem a necessidade de abrir qualquer outra página. Vamos clicar na opção Sim conforme figura abaixo mas lembre-se, a qualquer momento podemos excluir ou incluir novas contas como veremos durante nosso passeio pelo Outlook. Click em Avançar.

Vamos adicionar uma Nova Conta de E-mail.  Em nome, digite o que quiser. Insira logo abaixo seu endereço de e-mail e forneça e confirme a senha de acesso. Terminado, marque a opção logo abaixo Definir manualmente as configurações do servidor... Veja que os dados inseridos ficaram indisponíveis no momento. Avançar!

Conforme a figura seguinte, marque a opção Email da Internet e novamente, Avançar!

Agora devemos preencher as opções como mostra a figura abaixo usando seus dados, Nome, Endereço de Email, Nome de usuário e senha.

Se sua conta é do Hotmail, use as Informações do Servidor como acima, ou então leia o final do post onde falamos do Gmail.

Marque a opção Lembrar senha se só você utiliza a máquina, caso contrário, deixe desmarcada. Ative a opção Exigir logon usando Autenticação de Senha de Segurança. Em seguida, clique no botão Mais configurações...

Na tela que se abre, coloque seus dados como mostra a figura. 

Depois abra a Aba Servidor de saída:

Ative a opção Meu servidor de saída requer autenticação, como mostra a figura acima.

Ative agora a Aba Avançado a ative a opção Este servidor requer uma conexão criptografada e na caixa Usar o seguinte tipo de conexão, ative Automática conforme figura abaixo em seguida clique em OK

Retornaremos a tela inicial ao fechar este passo e lá existe um botão chamado Testar verificação da conta, se tudo OK, o resultado será o seguinte:

É só clicar em fechar e seu e-mail já estará configurado. Agora o envio e o recebimento de mensagens é automático, sem a necessidade de abrir o Hotmail ou Messenger para ler suas mensagens.

Depois que clicamos em Concluir vem a mensagem parabenizando pela configuração e em seguida, abre-se o Outlook para inicio dos trabalhos.

PARA CONFIGURAR O GMAIL OU OUTRAS CONTAS

Para configurar uma conta do G-mail  processo é quase a mesma coisa como veremos abaixo, mas antes devemos Ativar a  opção IMAP do Gmail. Proceda da seguinte forma: Entre na sua conta do Gmail e ao abrir, clique no botão parecendo uma engrenagem do lado superior direito da tela e no Menu suspenso, escolha a opção Configurações. Abra a Aba Encaminhamento e POP/IMAP e ative o IMAP como mostra a figura abaixo:

Não se esqueça de clicar no Botão Salvar Alterações na parte de baixo do G-mail.

Dentro do Outllook, via Barra de Menu, vá em Ferramentas -> Configurações de Conta... e chegaremos aqui:

Clique em Novo...

Mantenha a opção selecionada como acima, clique em Avançar e daqui em diante os passos são iguais a configuração do Hotmail mostrado no inicio exceto quando chegar na tela abaixo:

 No Tipo da conta selecione IMAP, Servidor de entradas de email digite imap.gmail.com e em SMTP, informe smtp.gmail.com e siga os  demais passos, teste a configuração e se tudo OK, pode começar a usá-lo. Com nosso Outlook pronto, no próximo post iremos começar a trabalhar com ele.

Quem possui outra conta que não seja as aqui citadas pode solicitar auxílio ao suporte da conta de seu email já todos eles já possuem tutoriais prontos para auxiliar nessa tarefa.

E lembre-se, a pergunta mais idiota é aquela que não foi feita. Portanto, excefoz@hotmail.com à sua disposição.

Por que usar o Microsoft Office

Na atualidade é raro aparecer um usuário de computador que não tenha trabalhado um pouco com planilhas eletrônicas, editor de textos, banco de dados (casos raros) entre outras ferramentas de produtividade.

Como também é fato que, não só a máquina em si, mas também seus aplicativos foram desenvolvidos no sentido de facilitar a vida do usuário e, além disso, tornar suas tarefas diárias eficientes.

A Microsoft, com seu ambiente para escritório denominado Office, vem quase todos os anos lançando novas versões de seus produtos e com a facilidade de integração entre eles. E é justamente tal integração que poucos usam, seja por desconhecimento ou por outro motivo qualquer. Muito se fala em Word e Excel mas será que tais usuários sabem mesmo utilizá-los de forma racional?

Existe uma concepção errada: a de que o Excel e o Word são fáceis de usar. A verdade é que ambos os aplicativos são fáceis de começar a usar, mas não são fáceis de usar. Considerando a experiência adquirida durante anos de trabalho com diversas ferramentas de produção similares, tenho encontrado pessoas que são consideradas expert no assunto. Conhecendo tais pessoas de forma mais abrangente, chega-se a conclusão que o expert em Excel não conhece  funções e muito menos ouviu falar em matrizes. Já aqueles que se acham os gurus do Word, raramente tem noção da importância dos estilos ou uso de códigos de campo.

Assim sendo, o que se busca com uma ferramenta de tal magnitude, é a obtenção de produtividade e automação de processos. Ninguém se dá conta mas a realidade da globalização só nos permite tal caminho. Ou automatizamos tudo de forma eficiente e produtivo ou seremos relegados à segundo plano já que, nunca a velha máxima que “tempo é dinheiro” se faz tão real nos dias de hoje.

Dessa forma, usaremos o presente espaço para transmitir aos poucos e a quem se interessar, nosso conhecimento à respeito do tema. Códigos de programação serão publicados mas desde já assumimos que você não seja um programador. Quando necessário publicaremos o código para uso geral e explicaremos passo à passo sua utilidade bem como a forma que foi criada já que tais exemplos serão uteis no sentido de mostrar ao usuário  a necessidade de planejamento antecipado, antes de se por a mão na massa.

Espero que gostem e repito, usem o e-mail excelfoz@hotmail.com para falar conosco.

Até a próxima!

FUNÇÃO CONCATENAR

COMEÇANDO

Dúvidas, quem não tem? Eu mesmo tenho várias e não é anormal passar horas buscando soluções para diversos problemas. Pela experiência adquirida ao longo dos anos e o gosto de escrever em blogs, resolvi fazer um apanhado de todas as dúvidas que a mim vieram e transmitir à vocês. 

Meu objetivo não é lhe dar a solução e sim dizer o por que da solução e quem sabe, ganhar uns trocados no futuro. Não, não se preocupe, para você que me  honra com a visita tudo é grátis aqui e nem cadastro é necessário mas, se quiser ajudar na divulgação, agradeço.

Por outro lado caso queira fazer alguma pergunta ou mesmo sugestão ou crítica, na logo do blog tem meu e-mail onde, na medida do possível, vou atender à todos. Vamos à primeira dúvida que até parece simples mas poucos  tem domínio à respeito do assunto.

NÃO CONSIGO COLOCAR NO RODAPÉ O NÚMERO DE PÁGINA E A QUANTIDADE DE PÁGINAS NO RELATÓRIO, TIPO 1/5

Veja na figura que temos dois botões quando vamos personalizar o cabeçalho ou rodapé da planilha. Um chama-se INSERIR NÚMERO DA PÁGINA e, logo ao seu lado, INSERIR NÚMERO DE PÁGINAS, viram a sutil diferença?

O primeiro coloca o número da página em uso, a página corrente,  já o outro, a quantidade de páginas do relatório.  Se você clicar neles vai aparecer algo como &[Página]. Viram o símbolo “&”?

Então, para inserir a numeração de página como deseja, faça o seguinte:

Escolha a posição onde deve aparecer o resultado (esquerdo, centro e a direita)

Insira o seguinte: Página &[Página] & / &[Páginas]

Depois de Página de um espaço e clique no primeiro botão, de um espaço e insira manualmente “& / “ (sem as aspas) e clique no outro botão, o resultado será Página 1/1.

Se em vez  de “&/” você usar “&de”, o resultado seria Página 1 de 1. Resolvido?

A FUNÇÃO CONCATENAR

Na questão acima falamos da Função Concatenar, vamos entender o que é isso.  Conforme a Ajuda do Excel,  a função CONCATENAR agrupa até 255 cadeias de texto em uma cadeia de texto. Os itens agrupados podem ser texto, números, referências de células ou uma combinação desses itens.

Na figura usamos um exemplo bem simples mas que nos dará uma ideia de como trabalhar com a função. Em uma célula qualquer se digitarmos:

=Concatenar(A1;A3) teremos como resultado Minhacasa mas se;

=Concatenar(A1; “ “;A3) teremos Minha casa

Podemos usar o operador “&”

=A1 & A3 que seria Minhacasa ou

=A1 & “ “&A3 seria Minha casa ou então

=”Você sabia que é “&A1 & “ “ & A3, resultado Você sabia que é Minha casa.

Como exercício, substitua os textos nas células A1 e A3 por números e veja os resultados.

Cabe agora, já sabendo como usar, testar outros exemplos e fique atento pois poderemos utilizar a dica em outras dúvidas futuras. Até a próxima!