CURSO DE EXCEL PARTE II

CURSO DE EXTENSÃO EM EXCEL
PARTE II
O Excel, como a maioria dos outros programas de escritório, tem itens
comuns como a barra de título, a barra de menus e barra de ferramentas. No
entanto o que o distingue como ferramenta de cálculo é a estrutura gradeada de
sua tela. Esta grade, ou tabela, é formada por linhas e colunas. As linhas são
identificadas por números; e as colunas, por letras. Se você clicar no canto da
tabela e segurar o botão do mouse, vai ver a capacidade total do Excel: 65536
linhas por 256 colunas. O cruzamento de uma linha com uma coluna forma uma
célula. A célula é identificada pelo nome da coluna e o nome da linha, juntos: B4,
C3, E17. Cada célula pode conter texto ou números. Mas até aí não há novidade.
Muitos programas – por exemplo, processadores de texto, como o Word –
trabalham com tabelas. O que diferencia o Excel e outros softwares do gênero é
a capacidade de calcular embutida na tabela. Em minha opinião, o Excel, é ao
mesmo tempo, a mais completa e a mais popular ferramenta de cálculo para
escritório. Poderoso e versátil, ele ajuda a desenvolver desde uma simples tabela
com totais até documentos mais complexos, como os gráficos dinâmicos.
ARREDONDAMENTO
A célula C3 corresponde a divisão da célula A3 por B3. O resultado é um
número com seis casas decimais. Vamos formatar este número para que ele
apresente duas casas decimais. Com C3 selecionada, acione Formatar>Células.
Na caixa de dialogo, veja as várias categorias de formatação. O padrão é geral ou seja, o número sem formatação. Na opção número, você pode definir o
número de casas decimais. Escolha 2. marque também o separador de milhares.
Agora, vamos multiplicar esse número por 5. Veja o resultado: outro
número com muitas casas decimais. Porque isso? Isso ocorre porque o número
na célula C3 tem duas casas decimais somente para visualização na tela. Na
memória, ele continua no mesmo formato original.
Portanto, a formatação do número é apenas um ajuste visual que não
altera o valor. A conseqüência disso é que, se você está trabalhando com valores
que precisam ser exatos, formatar os números não é a solução.
A solução é usar a função ARRED, de arredondar. Em vez de fazer uma
simples divisão, vamos arredondar o resultado. Depois do sinal de igual (=), que
inicia a fórmula escreva ARRED e abra parêntese. O Excel dá a dica sobre a
função. Você tem que fornecer o número a arredondar e o número de dígitos
desejados.
Agora, vejamos como trabalha a função ARRED. Vamos fazer
arredondamento para uma casa decimal. Para facilitar a observação, colocamos
uma coluna de números à esquerda e outra com os mesmos números,
arredondados, à direita.
O que acontece? Quando a segunda casa decimal é maior ou igual a 5, o
número é arredondado para cima. O Resto simplesmente perde a segunda casa
decimal. Até aqui, nenhuma novidade. Essa é a regra consagrada de
arredondamento.
Agora, imagine a seguinte situação. Você é um professor e quer
arredondar as médias de seus alunos da seguinte maneira. Os números com
parte decimal menor que meio ponto (0,5) devem ser ajustados para o inteiro
inferior; 0,5 permanece; e o decimal maior que meio ponto leva o número para o
inteiro superior.
Em suma, neste sistema, só podem existir notas inteiras ou com frações de
meio ponto. Portanto, nem a formatação, nem a função ARRED resolvem o
problema. Montemos uma pequena planilha de trabalho com os nomes e as
notas dos alunos. Para calcular a média aritmética simples, vamos usar a função
MÉDIA.
Na coluna verde, você vê os mesmos números da coluna amarela. A
diferença é que ela foi calculada com a função ARRED aplicada aos valores da
coluna vermelha. Como já vimos, nenhum desses esquemas resolve nosso
problema, que é arredondar as notas para múltiplos de meio ponto. Então
precisamos bolar uma fórmula.
Qual raciocínio vamos empregar nessa fórmula? Se a parte fracionária da
nota for maior do que meio ponto, então o valor final da nota será a parte inteira
mais 1. Se a fração for igual a meio, anota fica como está. Por fim, se a fração for
menor que meio ponto, o valor da nota fica sendo somente a parte inteira.
O raciocínio é fácil. Para transformá-lo numa fórmula, vamos montar um
ninho de SE, a função lógica condicional do Excel. O ninho corresponde a uma
ou mais funções SE dentro de outra. Como função auxiliar, vamos usar INT, que
fornece a parte inteira de um número. Coloquemos o resultado em uma nova
coluna na cor azul.
=SE(H4-INT(H4)>0,5;INT(H4)+1;SE(H4-INT(H4)=0,5;H4;INT(H4)))
EXERCÍCIO PROPOSTO
Se quisermos colocar mais uma coluna na minha tabela e mudássemos o
critério de avaliação, ou seja, frações menores que 0,25 são reduzidas para 0; as
menores que 0,75 e maiores ou iguais a 0,25 vão para meio ponto. A faixa
restante cresce para o próximo inteiro.
FUNÇÕES DATA/HORA
Além das funções de data e hora mais comuns no Excel, por exemplo,
DATA, DIA, MÊS, ANO, AGORA uma série de funções adicionais que fazem
parte do suplemento Ferramentas de Análise. Vamos dar um passeio por
algumas dessas funções que são muito úteis nos cálculos financeiros.
Se você nunca usou o suplemento Ferramentas de Análise vai precisar
ativá-las. Acione Ferramentas E Suplementos e marque a caixa Ferramentas de
Análise. O Excel pode pedir o CD do Office. Coloque o CD no drive e siga as
instruções.
A primeira função que vamos visitar é DIATRABALHOTOTAL. Ela
fornece o número de dias úteis entre duas datas excluindo os finais de semana. A
função também pode excluir os feriados desde que você forneça uma lista deles.
Vejamos um exemplo.
Observe a planilha. Na primeira linha temos os títulos Data Inicial. Data
Final, Dias e Feriados. Na coluna Feriados indique os feriados nacionais. Agora,
digite duas datas quaisquer nas colunas Data Inicial e Data Final. Coloque o
cursor na coluna Dias e clique no botão Inserir Função.
Na tela Inserir Função, selecione a categoria Data e Hora, escolha a
função DIATRABALHOTOTAL e dê OK. Na tela Argumentos da Função
indique os valores. Clique no botão a direita do campo Data Inicial e clique na
célula correspondente. Clique no botão da janela Argumentos da Função.
Repita o procedimento para o valor da Data Final e faça o mesmo para a
linha Feriados, mas agora selecione toda a lista de datas na coluna Feriados.
Agora, clique em OK. Na coluna Dias, o Excel retorna o número de dias úteis
entre as duas datas excluídos os feriados, se houver algum no intervalo indicado.
Numa aplicação real, você pode colocar a lista de feriados numa folha
separada da planilha. Outra jogada muito útil é dar um nome a lista de feriados.
Copie a coluna Feriados para a planilha Plan2. Selecione as datas e dê o
comando Inserir Nome, Definir. Na caixa Nomes, digite feriados e dê OK.
Agora, toda a lista de datas se chama feriados. Em vez de indicar o
intervalo de células escreva simplesmente feriados. Fica bem mais fácil. Vamos
ver agora a função DIATRABALHO. Ela calcula uma data final, partindo de
uma data inicial, o número de dias úteis e os feriados.
Na mesma planilha, criemos uma coluna chamada Data Final. Vamos
usar a mesma data inicial e o mesmo número de dias úteis. A data final é um
número estranho. O valor fornecido pela função DIATRABALHO é uma data
serial. Ou seja, o número de dias decorridos desde 1 de janeiro de 1900, o início
do calendário do Excel.
Selecione esse resultado. Agora, acione Formatar células. Escolha a
categoria Data e dê OK . Pronto, A data final, observe, não é a mesma utilizada
na outra função. É que DIATRABALHOTOTAL fornece o número de dias úteis
entre duas datas, incluindo as duas. Já DIATRABALHO retorna a data após um
período de dias úteis.
Por causa disso a data fornecida pela função DIATRABALHO cai, nos
exemplos, um dia depois. Vamos agora ver a função DATADIF. Ela retorna o
número de dias, meses ou anos entre duas datas. Mas aqui não há desconto dos
fins de semana ou dos feriados.
A função DATADIF pede três argumentos: a data inicial, a data final e
uma unidade. Essa unidade é um código para dizer se o resultado deve ser dado
em dias, meses ou anos. Para dias, o código é D, para meses é M e para anos é Y.
Na formula, o código deve ser escrito entre aspas.
Veja um exemplo em que as mesmas datas são usadas com a função
DATADIF, variando apenas a unidade de tempo. Os resultados, em meses e
anos, sempre indicam quantos meses ou anos inteiros existem entre as duas
datas.
Outra função adicional do Excel é FIMMÊS, que fornece o número de
série para o último dia do mês. Essa função é prática para calcular as datas de
vencimento que caem no último dia do mês. FIMMÊS pede dois argumentos, a
data inicial e o número de meses decorridos a partir dessa data.
Veja um exemplo. Escreva uma data na célula A1. Em A2, escreva a
fórrmula usando a função. A1 é a data inicial e o número de meses, zero. O
resultado é uma data serial. Acione Formatar células e escolha a categoria Data.
A função fornece o último dia do mesmo mês da data inicial.
Para o mês seguinte, em vez de zero, use 1. A função FIMMÊS também
pode ser usada para obter o número de dias do mês. Veja uma aplicação. Você
tem uma planilha que mostra os números de visitação do site de sua empresa.
Seu objetivo é calcular a média de visitas diárias.
Você precisa dividir o total de visitas pelo número de dias do mês
correspondente. É claro que é possível fazer as fórmulas, uma a uma. Se for
abril, dividir por 30; se for outubro, 31. E se for fevereiro? Bem, pode ser 28 ou
29. Ora, vamos procurar um jeito de fazer a fórmula apenas uma vez.
Vamos usar a função FIMMÊS, com o segundo argumento igual a zero
para obter a data serial de cada mês. Mas não queremos a data, e sim o total de
dias do mês. Então, a função DIA (que fornece o dia do mês, de 1 a 31), vai
resolver nosso problema. É só dividir o número de visitas pelo resultado da
função DIA. Estenda a fórmula para toda a coluna.
Vamos ver agora mais duas dicas importantes nas operações com data e
hora. A planilha que você é uma folha de pagamento com base em horas
trabalhadas. Cada linha representa o cartão de ponto diário de um funcionário,
com registros dos horários de entrada e saída, em dois turnos.
As duas dicas estão no cálculo das horas trabalhadas em cada turno e na
apuração do salário dia. Observe a primeira linha da coluna Total1. O
trabalhador Marcos entrou às 20:00 horas e saiu às 03:00 da manhã. Mas se
você fizer a subtração vai verificar que ele trabalhou - 17 horas.
Para evitar esse disparate, usamos a função MOD, que fornece o número
correto: 7 horas. As operações com horas se complicam quando a meia-noite
(zero hora) está entre a hora inicial e a final. O que a função MOD faz, neste
caso, é somar os intervalos antes e depois da meia-noite.
No exemplo, Marcos trabalhou quatro horas no final de um dia e três
horas no início do outro. Vamos agora ao outro truque nesta planilha. Veja a
coluna Salário/Dia. Correspondem ao total de horas trabalhadas vezes o
salário/hora. Portanto, na primeira linha K5 vezes C5.
Mas isso está errado! Dá pouco mais de 5 reais e Marcos ganha 14 reais
por hora. O problema aqui é mais uma vez, a natureza das horas, que são
números cíclicos. Para obter o valor real é preciso multiplicar o total de horas
por 24 e depois pelo salário/hora.
CÉLULAS COM NOME
As células com nome podem ajudar muito na construção de planilhas
complexas. Quando uma célula tem nome, em vez de usar referências como A4 e
C5, você pode trabalhar com itens mais amigáveis como Taxa_de_Juros ou
CustoTotal.
O caso mais comum de nomeação de células é o seguinte. Se você tem
colunas cujos cabeçalhos são meses, é possível obter o total adicionando os
nomes. Exemplo: Janeiro, Fevereiro, Março. Mas para que isso funcione você
precisa preparar o Excel.
Acione Ferramentas, Opções e clique na orelha Cálculo. A opção Aceitar
Rótulos em Fórmulas deve estar ligada. Dê OK e confirme a fórmula. Veja o
resultado. Estenda a fórmula para o resto da coluna.
Há outras formas de aproveitar esse recurso. Para obter o total do mês de
janeiro, você pode escrever assim: =SOMA(Janeiro). Como você vê, Janeiro,
nesse caso, representa toda a coluna de dados relativos a esse mês. As referências
também funcionam no sentido horizontal.
Use a fórmula =SOMA(Tecidos) para obter o total relativo ao produto
Tecidos. Também é possível referir-se a uma célula pelo nome da coluna
combinado com o da linha: Fevereiro Tecidos, Março Calçados. Essa referência
pode entrar em operações.
Quando você chama células pelo nome com base em cabeçalhos, está
usando um recurso automático do Excel. Mas você pode dar o nome que quiser a
uma ou mais células. Há pelo menos duas formas de fazer isso. Veja a primeira.
Selecione a célula desejada.
Observe que o nome da célula aparece no canto superior esquerdo da
planilha. Clique nesse campo, digite Valor e acione Enter. A célula passa a se
chamar Valor e você pode incluir esse nome em qualquer operação que envolva
essa célula. Esse procedimento vale para uma célula ou uma região delas.
Outra maneira de nomear um intervalo de células é selecioná-las e dar o
comando Inserir, Nome, Definir. Na caixa Nomes na Pasta de Trabalho, digite o
nome desejado. Atenção: o nome não pode ter espaços. Use algo como
CustoTotal ou Custo_total ou ainda Custo.Total.
Vale lembrar que os nomes podem ter até 255 caracteres. É proibido o
uso de operadores como os sinais de mais ou de menos. Prefira nomes curtos, em
vez de Banco_de_dados, BcoDados.
Até o momento, atribuímos nomes a células ou grupos de células. Mas o
recurso dos nomes também pode ser aplicado a um valor ou a uma fórmula.
Admita, por exemplo, que você trabalha numa importadora e deseja criar uma
tabela de preços indexada ao valor do dólar. É fácil.
Primeiro, vamos criar a tabela pelo método mais simples. Numa coluna, a
lista de produtos. Em outra, a lista de preços em dólar. Vamos criar também
duas células isoladas. Uma para armazenar o valor atual do dólar e a outra para
definir uma variável chamada FatorCustos.
Esse FatorCustos é um nome arbitrário. Trata-se de um número definido
por sua loja para calcular o preço dos produtos importados com base no dólar.
Ele embute os impostos de importação, custos de armazenamento, custos
financeiros, etc.
Suponha que sua empresa definiu que o FatorCustos é igual a 1,9. Ou
seja, o preço em reais equivale a esse fator multiplicado pela taxa de câmbio e
pelo preço em dólares. Nesta fórmula, usamos o cifrão ($) para fazer referência
aos endereços absolutos das células que contém o valor do dólar e do
FatorCustos.
A célula $E$2 indica a referência fixa à célula E2. Agora, vamos
trabalhar com nomes. Acione Inserir, Nome, Definir. No campo superior, digite
FatorCustos. Embaixo, escreva 1,9. Agora, em vez do valor armazenado numa
célula, use na fórmula o nome FatorCustos.
Apague a célula que armazena o valor FatorCustos. Se depois sua
empresa redefinir o valor de FatorCustos, basta atualizar o valor dessa
constante. Há uma vantagem nisso. Em vez de sair caçando numa planilha os
itens que devem ser modificados, basta alterar as referências num único lugar.
Mas há também um inconveniente. O nome da célula, do valor ou da
fórmula pode parecer um mistério para quem não elaborou a planilha. A saída,
nesses casos, é procurar na caixa Definir Nome.
PROTEÇÃO DE PLANILHAS
Você pode evitar que suas planilhas sejam alteradas por intrusos ou
simples distração do usuário. O Excel oferece meios de proteger uma ou algumas
células. Só que esse tipo de proteção não é feito de forma direta. É o que vamos
ver neste capítulo.
Admita que seu objetivo é montar uma planilha que tenha as fórmulas,
mas permita que o usuário digite dados. Para isso, selecione toda a planilha
clicando na célula de origem. Ou, no teclado, Ctrl+T. Agora, dê o comando
Formatar > Células. Na nova tela, clique em proteção.
Ative a caixa Bloqueadas. Essa caixa, ligada, impede a digitação nas
células selecionadas. Caso ela apareça na cor cinza, isso indica que algumas
células já têm essa característica. Deixe inativa a outra opção, Ocultas, e dê OK.
Até aqui você definiu que toda a planilha está protegida. Agora, selecione
somente as células que você deseja deixar livres. É o caso de células em que o
usuário precisa digitar informações. Acione outra vez Formatar > Células,
orelha Proteção. Desligue a caixa Bloqueadas e dê OK.
Se, além de evitar modificações, você quer esconder a própria fórmula dê
o comando Editar > Ir Para e, na tela aberta, clique no botão Especial. Na janela
Ir Para Especial, escolha a opção Fórmulas e deixe marcadas todas as caixas
logo abaixo. Clique em OK.
O Excel, que antes mostrava toda a planilha, vai agora destacar somente
as células que contêm fórmulas. Volte ao comando Formatar > Células, selecione
a orelha Proteção e mantenha ligadas as caixas Bloqueadas e Ocultas.
Agora, as células destinadas à digitação estão livres, enquanto as que
contêm fórmulas vão ficar travadas e sem condições de exibir sua lógica de
cálculo. Dê o comando Ferramentas > Proteger planilha. Isso conclui a
operação.
Agora, vamos fazer o teste. Você pode alterar os valores das células de
digitação. No entanto, tente mudar um dos totais ou os títulos. O Excel não
permite. Além disso, clique em qualquer célula dos totais: as fórmulas não são
exibidas.
Para editar as fórmulas ou modificar a planilha, você precisa executar o
comando Ferramentas > Proteger > Desproteger Planilha. Lembre-se: ao
proteger a planilha você pode atribuir a ela uma senha. Mas não esqueça!
Observe também que a janela Proteger Planilha oferece itens específicos
de proteção para formatação e exclusão de células, colunas, linhas e outros
objetos. Até agora, falamos somente de proteções que evitam alteração do
documento.
O Excel oferece outro tipo de proteção. É o que controla o acesso ao
próprio arquivo. Ou seja, quando alguém tenta abrir o arquivo XLS, ele pede a
senha de acesso. Para ligar esse tipo de proteção, acione Arquivo > Salvar Como.
Na janela Salvar Como, abra o menu Ferramentas e escolha Opções
Gerais. A tela Opções de Salvamento permite definir duas senhas – uma de
proteção e outra de gravação. Na primeira, você pode clicar no botão Avançada
e escolher um tipo de criptografia para guardar a senha.
A senha de proteção protege a abertura do arquivo. Já a de gravação é
pedida quando o usuário, autorizado a abrir o arquivo, tenta salvar
modificações. Apesar da possibilidade de usar tantas senhas, tenha cuidado: só a
utilize se for necessário.
GRÁFICOS DINÂMICOS
No Excel, as tabelas dinâmicas e os gráficos dinâmicos são dois recursos
poderosos para análise de dados. São ferramentas interativas que permitem
visualizar resumos de planilhas que contêm grande volume de dados. Neste
capítulo vamos tratar dos gráficos dinâmicos.
Ferramentas como gráficos dinâmicos ou tabelas dinâmicas são
especialmente úteis quando há dados que se repetem muitas vezes na tabela.
Considere, por exemplo, uma planilha na qual estão listados os clientes da
empresa, seu histórico de compras durante o ano de 2005 e as cidades desses
clientes.
Vamos montar gráficos dinâmicos com base nesta planilha. Para isso,
selecione a área útil do documento e dê o comando Dados > Relatório de Tabela
e Gráficos Dinâmicos. Surge um assistente. Primeiro, ele pergunta onde estão os
dados que você deseja analisar.
A resposta-padrão é numa lista do Excel. No entanto, você também pode
analisar informações de uma fonte de dados externa. Informe, em seguida, o tipo
de relatório que você deseja criar: relatório de gráfico dinâmico. Clique em
avançar.
Clique novamente em Avançar. No passo seguinte, escolha traçar o gráfico
em nova planilha e clique em Concluir. O Excel cria nova folha de gráfico,
Gráf1, e exibe a janela Lista de Campos da Tabela Dinâmica. Você pode
arrastar essa janela e ancorá-la no lado direito da tela.
Observe a página Gráf1. A região do gráfico exibe a mensagem “Solte
itens de dados aqui”. À direita, há um espaço para campos de série. O Excel
também ativa a barra de ferramentas Tabela Dinâmica.
Para compôr o gráfico, itens da Lista de Campos devem ser arrastados
para uma das regiões do painel principal. Tracemos o primeiro gráfico analítico.
Arraste o campo Cidade para a área de categorias; depois o campo Valor para
cada região central do gráfico.
Dessa forma, você obtém um gráfico que mostra quanto cada cidade
compra da empresa. Clique na caixa Cidade, embaixo do gráfico. O Excel exibe
uma lista de todas as cidades do banco de dados. Você pode desmarcar algumas
delas, ficando apenas com as que mais interessam.
Para reexibir todas as cidades, volte à lista de cidades e clique na opção
Mostrar Tudo. Agora admita que você deseje relacionar as cidades com os
prazos de pagamento (por exemplo, 30 dias, 60 dias, etc.). Arraste o campo
Prazo também para a área de categorias.
O gráfico mostra uma distribuição dos valores comprados em cada cidade
em cada categoria de prazo. Agora, clique com o botão direito no campo Cidade,
no gráfico, e no menu, escolha Remover campo. Pronto. Você tem outro gráfico.
Agora relacionando prazos e valores.
É fácil perceber, por exemplo, que os maiores montantes de vendas são
feitos com os prazos de 30 e 60 dias. Agora, arraste o campo Nome da lista para
a área de campos de página. Isso significa que você pode criar gráficos
separados para cada cliente. Clique na flecha do campo Nome, escolha um
cliente e dê OK.
O gráfico relaciona o cliente com os valores que ele comprou, indicando os
prazos de pagamento. Mas de qual cidade é esse cliente? Arraste o campo
Cidade para a área de Categorias. Opa, não faz sentido: como o gráfico está
centrado num cliente, a cidade se repete.
Então, arraste o campo Cidade para a área de campos de série. Agora, o
gráfico está mais correto. O cliente Marco Pólo, da cidade de Campinas,
comprou esses valores com pagamentos em três tipos de prazo. E quando ele
comprou?
Arraste o campo Data para a área dos campos dos campos de série. O
gráfico se torna mais colorido e mostra em detalhes os valores e datas de cada
compra executada pelo cliente. Com experiência, arraste o campo Data da área
dos campos de série para a dos campos de categorias. O que ocorre?
O Excel monta outro gráfico. Desta vez, ele exibe os valores comprados
pelo cliente separados pelos prazos de pagamento e indicando as datas de
compra. Observe um detalhe: a cada modificação que você faz no gráfico, o
Excel também altera uma tabela dinâmica.
É nessa tabela que ele baseia o traçado do gráfico. Agora você já sabe
como trabalhar com gráficos dinâmicos. Se você usar uma tabela com mais
informações, poderá traçar muito mais gráficos, combinando os campos de
dados.
Não esqueça: nossa análise está centrada num cliente. Portanto, você pode
ir à caixa Nome, clicar na seta e escolher na lista outro cliente. Outra
possibilidade: dê um duplo clique no campo Soma de Valor. Surge a tela Campo
da Tabela Dinâmica.
Em vez da soma dos valores comprados, você pode escolher, por exemplo,
o número de compras realizadas, o valor médio das compras e até o desviopadrão dos valores.
Ao criar diferentes visualizações com base em porções da tabela analisadas
separadamente, você pode extrair mais conclusões sobre sua atividade,
conhecendo melhor os clientes e o desempenho dos produtos em cada cidade ou
região.
VALORES MÁXIMO E MÍNIMO
Numa planilha extensa, é difícil determinar qual o maior ou o menor
número registrado numa coluna. Neste capítulo, vamos ver uma técnica para
identificar os valores extremos de um conjunto de dados. Além disso, vamos
montar um gráfico que se atualiza automaticamente.
Você tem uma planilha que faz o acompanhamento diário da variação de
um número qualquer: valor do dólar, faturamento do caixa da sua empresa,
medições num laboratório. Essencialmente, essa planilha inclui uma coluna para
as datas e outra para os valores.
Nosso problema é o seguinte. Num documento desse tipo, como localizar,
na coluna de datas, a célula correspondente ao valor máximo (mínimo)
registrado? A dificuldade, aqui, reside no fato de você não saber até onde se
estendem as colunas. Os pares data-valor vão sendo adicionados com o tempo.
Em outras palavras, você quer descobrir qual o maior valor de uma região
em aberto. Vamos desenvolver uma solução para isso. Admita que as datas
estejam na coluna A e os valores na B. A primeira linha é ocupada com títulos:
Data, Valor.
Admita, também, que os dados Data e Valor sempre formam um par: não
há data sem valor e vice-versa. O primeiro passo consiste em nomear a coluna B.
Acione Inserir/Nome/Definir e digite, em cima, o nome Dados.
Embaixo, digite a expressão mostrada na tela e dê OK. Você criou uma
fórmula chamada Dados. Nessa fórmula, A função CONT.VALORES verifica
quantas células preenchidas existem na coluna B. A subtração de 1, no final,
desconta a célula B1, que contém o título.
A função DESLOC é fundamental nessa fórmula. Ela resolve o problema
da coluna em aberto. Sempre que você inserir novos dados na coluna B,
DESLOC vai incluir esses dados na área de cálculo, que começa na célula B2.
Observe que B2 está como $B$2. Essa referência fixa indica que o início da
área de contagem dos valores é sempre a célula B2. A fórmula Dados tem apenas
um papel auxiliar: retorna uma referência geral para a coluna B. Em outras
palavras, ela se refere, genericamente, a qualquer célula da coluna B.
A fórmula Dados será usada agora em outra fórmula, que nos dará o valor
máximo na coluna B. Vamos colocá-la aqui ao lado. Essa nova fórmula fornece a
data coluna A que faz par com o valor máximo registrado na coluna B.
O núcleo dessa fórmula é a função MÁXIMO, que determina qual é o
maior valor na coluna B, referida na fórmula Dados. A função CORRESP
localiza onde está esse valor máximo. Por fim, DESLOC identifica, na coluna A,
a data que faz par com o valor máximo na coluna B.
Nesta outra célula a fórmula é idêntica. Mas, em lugar da função
MÁXIMO, usamos a função MÍNIMO. Assim, resolvemos a primeira parte do
nosso problema: temos os valores máximo e mínimo dos dados listados na
coluna B. Para testar a solução, digite novas datas e valores.
Um lembrete: naturalmente, o valor máximo ou mínimo pode ocorrer
mais de uma vez. Esta solução aponta apenas o primeiro. Você pode ampliá-la
para encontrar os demais. Uma forma é usar a função CORRESP. Para
encontrar o valor 210,17, por exemplo, use a fórmula mostrada na tela.
Ela procura na coluna inteira. O resultado é o número da linha contado
desde o início do bloco de dados. Para ajustá-lo ao número da linha na planilha,
some 1. Confira: o número 210,17 está na linha 6. Você também pode partir
para uma solução visual: crie um gráfico.
A idéia é criar um gráfico que incorpore automaticamente os novos
valores incluídos na planilha. Acione Inserir > Nome > Definir e Eixo_X no
campo Nomes da Pasta de Trabalho e, embaixo, a fórmula destacada na tela.
A estrutura da fórmula Eixo_X já é nossa conhecida. A função
CONT.VALORES conta o número de linhas ativas na coluna A, subtraindo
uma, que é a linha ocupada pelo título. A função DESLOC faz uma referência a
toda essa coluna, a partir da célula fixa A2.
Você já notou por que chamamos a fórmula de Eixo_X. É isso mesmo: os
valores X do gráfico serão formados pela coluna de datas. Agora, acione Inserir
> Gráfico e clique no botão Concluir, para criar um gráfico vazio. Reposicione o
espaço do futuro gráfico.
Clique no gráfico com o botão direito e escolha Dados de Origem. Surge
uma janela com esse nome. Escolha a orelha Série e clique no botão Adicionar.
Agora no campo Valores, digite: Plan1!Dados.
Isso diz ao Excel quais dados devem ser usados para gerar o gráfico.
Dados, você lembra, é o nome da fórmula que define a coluna B. Por fim, na
caixa Rótulo dos Eixos das Categorias (X), escreva Plan1Eixo_X. Isso diz ao
Excel que as variáveis do eixo horizontal estão na fórmula Eixo_X.
Essa fórmula – lembra? – corresponde aos valores da coluna A. Clique em
OK e o gráfico está pronto. Vamos testá-lo. Inclua nova linha de dados na
planilha e veja que o gráfico se atualiza sozinho. Problema resolvido.
Agora, vamos dar um acabamento final no gráfico. Primeiro selecione a
legenda à direita e acione a tecla Del. Assim, sobra mais espaço para o gráfico.
Clique com o botão direito no eixo das datas e escolha formatar Eixo. Na tela
Formatar Eixo, passe à orelha Fonte.
Defina uma fonte menor. Com isso, as datas, que apareciam salteadas,
agora marcam cada ponto do gráfico. Clique com o botão direito e, no menu,
selecione Opções de Gráfico. Na nova tela, passe à orelha Rótulos de Dados.
Marque a caixa Valor e dê OK.
Epa, os números estão muito grandes! Clique com o botão direito num
desses números e escolha Formatar Rótulos de Dados. Na orelha Fonte, escolha
uma fonte menor. Agora, você tem uma informação visual dos valores máximos
e mínimos.
No caso de números próximos, se houver alguma dúvida, os rótulos
ajudam a esclarecer qual valor é o maior ou o menor. Também é possível
escolher outros tipos de gráfico.
CONTROLE DE VENDAS
Nesse capítulo, nosso objetivo é montar uma planilha para controlar
diariamente os resultados de vendas de uma empresa ou departamento.
Comecemos com o esboço geral. Vamos criar uma planilha na qual serão
anotadas as vendas diárias.
A meta de vendas também se refere ao mês de janeiro de 2006. Aqui,
entende-se que a empresa opera inclusive nos sábados e domingos.
Observe a fórmula que dá a meta diária. Ela não é simplesmente a meta
dividida por 31. Por quê? Porque queremos montar uma planilha que sirva pra
qualquer mês. Em fevereiro, essa divisão simples já não serviria. Então,
montamos uma regra geral.
Você tomou contato com funções especiais de data e hora. Vamos usar
FIMMÊS, uma daquelas funções. FIMMÊS aplicada a qualquer data do mês,
fornece a data do último dia desse mês. A função DIA nos dá somente o dia
serial (de 1 a 31) dessa data.
Então, dividimos o valor da meta pelo número serial do último dia do mês.
Assim, achamos a meta média diária. Agora, vamos para a planilha. Duas
colunas são básicas para a entrada de dados: Data e Receita Diária – ou seja, as
vendas dessa data.
Todos os outros dados serão calculados em função dessas duas colunas e
da meta estabelecida. Uma coluna obrigatória é a Receita Acumulada. Ela vai
indicar, quanto já foi vendido até a data, desde o início do mês. No exemplo,
temos duas colunas de Receita Acumulada.
Só precisamos de uma, é claro. Mas com as duas vamos mostrar soluções
diferentes. Selecione a primeira célula da coluna Receita Acumulada 1. Veja a
fórmula. No primeiro dia, é a soma da célula B5 até a célula B5. Mas por quê o
primeiro B5 vem na forma $B$5?
É simples. $B$5 é uma referência absoluta. Refere-se a célula B5, e
somente a ela. Já B5, sem os cifrões, é uma referência relativa: nas próximas
linhas, vai se tornar B6, B7, B8...No entanto, a primeira célula que entra nessa
acumulação é sempre B5, fixa. Daí os cifrões.
O Excel pode exibir uma advertência, achando estranho que a soma se
refira a uma parte da coluna ao lado. Clique na célula e depois do aviso de erro.
No menu, escolha Ignorar Erro.
A fórmula foi estendida para toda a coluna. Nas linhas ainda não
preenchidas com os pares data e receita, aparece repetido o último valor
acumulado. Isso não é um erro, mas incomoda. Coloque o cursor na segunda
coluna Receita Acumulada. A fórmula é diferente.
A base é ainda a mesma SOMA usada em receita 1. Mas incluímos a
repetição SE para evitar a repetição do último valor acumulado. Essa função SE
testa se o valor das vendas na data foi preenchido. Para isso, usa a função
ÉCEL.VAZIA.
Se a célula de receita está vazia, então a receita acumulada é zero. Caso
contrário, ela assume o valor da função SOMA, como na coluna anterior. Dessa
forma, em vez de repetir o último valor, repete-se o zero. Mas é possível também
eliminar os zeros.
Para que os zeros não apareçam, acione Ferramentas > Opções e, na
orelha Exibir, desligue a caixa Valores Zero. Dê OK, e as células ficam vazias.
Observe que essa configuração é válida somente para esta folha de cálculo.
Pronto, as colunas Receitas Acumuladas estão concluídas. Na planilha
final, você pode eliminar a primeira delas. Agora, vamos criar uma coluna
indicativa da porcentagem acumulada até a data em relação à meta.
A fórmula é simples: basta dividir o acumulado até a data pela meta de
vendas. Para ter o resultado em valores percentuais, acione Formatar > Células.
Na orelha Número, escolha a categoria Porcentagem e indique uma casa
decimal. Dê OK.
Agora, vamos incluir uma coluna para indicar a porcentagem do tempo já
decorrido dentro do mês. Vejamos a fórmula. Ela divide o dia atual pelo último
dia do mês. Mais uma vez usamos as funções DIA e FIMMÊS.
Também, para evitar a repetição da última porcentagem, voltamos a
empregar a função SE e o teste de célula vazia. Agora a planilha está pronta. O
gerente de vendas pode acompanhar os resultados comparando as duas últimas
colunas.
Nos últimos registros, por exemplo, ele pode ver que as vendas estão à
frente do tempo decorrido, o que é um bom resultado. Agora, vamos dar um
último retoque para que a planilha possa ser reutilizada nos próximos meses,
sem modificação.
Na célula A1, escrevemos “Faturamento – Janeiro/2006”. Portanto, em
fevereiro, teremos que ajustar esse título. Vamos transformá-lo num título
válido em qualquer época. Para isso, vamos montar uma fórmula que concatena
textos no Excel. Coloque o cursor na célula A1.
Agora, escreva a fórmula mostrada na tela. O sinal de igual, você sabe,
inicia a fórmula. A palavra “Faturamento”, entre aspas, é um pedaço do texto
que você quer escrever na célula. O sinal de E comercial (&) é o operador de
concatenação.
Agora, vamos transformar em texto a primeira data do mês, usando a
função TEXTO. A seqüência de quatro emes entre aspas (“mmm”) indica o
nome do mês por extenso. A função MAIÚSCULA, como o nome indica, serve
apenas para colocar o nome do mês em letras maiúsculas.
Por fim, vem a função ANO, que retorna o ano da primeira data da
planilha. Assim, para usar a planilha em outro mês, basta copiar as cinco
primeiras linhas e cola-las em nova planilha. Como está tudo automatizado, você
só precisa fazer dois ajustes.
O primeiro ajuste é escrever a nova meta de vendas. E o segundo, digitar a
data do primeiro dia do mês. Pronto. Todo o resto sai automaticamente. Agora,
é só usar a planilha.
FILTROS INTERATIVOS
Neste capítulo, vamos trabalhar com filtros interativos. Trata-se de um
recurso que permite trabalhar com planilhas como se fossem bancos de dados,
identificando grupos de informações e classificando-as em diferentes categorias.
Os filtros do Excel revelam informações que estão por trás dos números,
ajudam entender o que se passa na empresa e tomar decisões. Mas a grande
vantagem deles é que tudo isso pode ser feito sem malabarismos técnicos.
Para começar, crie uma planilha de dados cuja primeira linha contenha os
títulos de cada coluna. Atenção: “primeira linha”, aqui, não significa
necessariamente a linha 1 da planilha, mas apenas a linha inicial da tabela.
No exemplo, criamos uma folha de informações funcionais do setor de
recursos humanos de uma software house. Trata-se de uma tabela simples, com
colunas com Nome, Cargo, Salário e Escolaridade dos funcionários.
Com a tabela pronta, coloque o cursor em qualquer célula útil e, no menu,
acione Dados > Filtrar > AutoFiltro. Isso ativa no menu o recurso AutoFiltro.
Agora, verifique a planilha: as células que contêm títulos se transformam em
caixas de combinação.
Clique, por exemplo, na seta da coluna Cargo. A caixa de combinação
exibe todos os cargos disponíveis na lista de empregados. Selecione a opção
“analista de sistemas”. Veja: a planilha encolheu e exibe apenas os dados dos
funcionários que são analistas de sistemas.
Outra mudança aconteceu. A seta da caixa Cargo mudou de cor, assim
como os números das linhas. Todos agora são azuis. Essa cor indica que os dados
estão filtrados e em qual campo a peneira foi aplicada. É possível fazer a
filtragem com base em mais de um campo.
Aqui, o filtro está baseado no campo Cargo, selecionando somente os
analistas de sistemas. Você pode aplicar um segundo critério. Por exemplo,
destacar apenas os que têm curso superior completo. A seta no campo
Escolaridade também assume a cor azul.
Agora o que você vê na tela corresponde ao seguinte comando: selecione
todos os empregados que têm o cargo de analistas de sistemas e curso superior
completo. Há outros itens que permitem sofisticar a análise dos dados.
Além das informações existentes na coluna, as caixas de combinação
contêm, como opções iniciais, dois itens comuns. Classificar em Ordem
Crescente e Classificar em Ordem Decrescente. Essas opções são autoexplicativas.
As caixas de combinação do sistema de filtros mostram ainda três opções
comuns: Tudo, 10 Primeiros e Personalizar. A alternativa Tudo desativa o
critério de filtragem aplicado àquele campo.
Se a filtragem envolve mais de um campo, é preciso acionar a opção Tudo
em cada um deles para que a planilha volte a mostrar todas as linhas. Um
caminho mais rápido é acionar, no menu, a opção Dados > Filtrar > Mostrar
Todos.
A opção 10 Primeiros abre uma caixa de diálogo. Nela, você que os 10
primeiros podem ser configurados para sete, vinte ou outro número qualquer.
Além disso, em vez de primeiros, podem ser os últimos. E ainda há mais opções.
Quando você pede os n primeiros, o padrão é Itens.
Ou seja, o Excel vai exibir somente os primeiros ou últimos itens ou os
maiores números ou datas. Você também pode pedir os n primeiros (ou últimos)
em porcentagem. Nesse caso, o Excel traz um número de registros
correspondente à porcentagem indicada.
Se você pedir os primeiros 5% numa tabela de 1000 registros, o Excel vai
fornecer os 100 maiores. Observe que a opção 10 primeiros funciona apenas
para campos numéricos. Quando você tenta aplicá-la a campos de texto, nada
acontece.
Ainda não falamos da opção Personalizar. Ela também abre uma caixa de
diálogo chamada Personalizar AutoFiltro. Trata-se e uma tela de busca na qual
você pode montar pesquisas muito específicas. Veja os recursos oferecidos pela
janela Personalizar AutoFiltro.
Clique, por exemplo, na opção Personalizar da caixa de combinação
associada ao campo Cidade. Surge a janela Personalizar AutoFiltro, já associada
a esse campo. Você pode procurar, por exemplo: exatamente uma cidade, todas
as cidades menos uma indicada.
Também é possível localizar as cidades cujo nome contém (ou não contém)
determinada palavra. Ou então, buscar cidade cujo nome começa com essa
palavra. As opções no primeiro campo são muito ricas.
No segundo campo, vêm os valores de comparação. Neste exemplo, são
todas as cidades disponíveis no banco de dados. Na parte inferior da janela, há
dois campos idênticos aos primeiros. Eles permitem montar buscas compostas,
usando os operadores E e Ou.
Então, você pode montar uma pesquisa assim: listar todos os empregados
que moram em São Paulo ou em Campinas. Cuidado com a lógica ao usar os
computadores E e Ou. Se, neste último exemplo, você usar E em lugar de Ou,
obtém um resultado vazio. Por quê?
É fácil perceber onde está o erro. O empregado não pode morar, ao
mesmo tempo, em São Paulo E em Campinas. Em geral, só dá para usar o
operador E em campos independentes: mora em São Paulo E é programador
júnior; mora em Campinas e ganha mais de 2000 reais. Mas a caixa personalizar
só trabalha com um campo.
O Excel também oferece outras possibilidades de trabalhar com
informações numa tabela de dados. Uma delas está no comando Dados >
Subtotais, que abre a janela Subtotais. Nessa janela você define visualizações da
planilha que mostram totais por grupos.
Veja um exemplo. Na caixa A Cada Alteração Em, escolha Cargo. Em
Usar Função, selecione ContNúm. Marque Cidade na lista Adicionar Subtotal A.
A indicação de Cidade se deve apenas ao fato de ser aqui o último campo da
planilha.
Marque também as caixas Substituir Subtotais Atuais e Resumir abaixo
dos Dados. Dê OK. O resultado é uma estrutura que totaliza os números de
registros para cada cargo. Experimente esse recurso.
No lugar da função ContNúm, que fornece a contagem de itens, coloque
Média. E em vez de Cidade, marque Salário. Dê OK. O que você vê? É a mesma
tabela, mas agora ela fornece a média dos salários de cada cargo. Para remover
os subtotais, acione Dados > Subtotais e clique no botão Remover Todos.