Fórmula de matriz

Outro dia recebi a seguinte mensagem:

"Encontrei a planilha anexa e não entendi nada, mas funciona...
Você saberia o que é? O mais engraçado: Se substituo qualquer letra ou símbolo, pela mesma letra ou símbolo na função, aparece erro de #Valor#"

Não precisa ver a planilha para entender a resposta (ainda que algumas coisas se refiram a ela):

Sua dúvida é muito boa. Não são muitas pessoas que conhecem essa fórmula de matriz.

Já explico:

1) Entender a fórmula de matriz.

- Primeiro repare que as fórmulas na aba "Histograma" de C3 a Z6 estão entre chaves. Isso faz com que a fórmula seja inserida como fórmula matriz.

Um pouco de Help do Excel não faz mal a ninguém (aperte F1 no Excel e busque por “Adicionar números”). Você lerá o seguinte:

Adicionar números com base em condições múltiplas

Para executar essa tarefa, use as funções SE e SOMA.

Exemplo

Talvez seja mais fácil de compreender o exemplo se você copiá-lo para uma planilha em branco. (não copiei o exemplo aqui)

Observação As fórmulas do exemplo precisam ser inseridas como fórmulas de matriz (fórmula de matriz: uma fórmula que executa vários cálculos em um ou mais conjuntos de valores e retorna um único resultado ou vários resultados. As fórmulas de matriz ficam entre chaves { } e são inseridas pressionando-se CTRL+SHIFT+ENTER.). Após copiar o exemplo para uma planilha em branco, selecione a célula da fórmula. Pressione F2 e, em seguida, pressione CTRL+SHIFT+ENTER. Se a fórmula não for inserida como uma fórmula de matriz, será retornado o erro #VALOR!

Ou seja, as fórmulas inseridas dessa forma fazem com que o Excel some com condições.

2) Entender a planilha.

Vamos pegar a célula Z6 como exemplo:

{=SOMA((Evol.Equipe!$C$5:$G$71="CSTC")*(Evol.Equipe!$E$5:$E$71<=Histograma!Z$2)*(Evol.Equipe!$F$5:$F$71>=Histograma!Z$2))}

Agora vamos dividir:

1) A primeira condição é encontrar as pessoas com “Coord” = “CSTC”.

Isso está na parte (Evol.Equipe!$C$5:$G$71="CSTC"). Como os “Coord” estão todos descritos na coluna G da Aba “Evol.Equipe”, a fórmula aqui poderia ser:

(Evol.Equipe!$G$5:$G$71="CSTC")

2) as demais condições são para ver se a pessoa está na empresa no mês indicado.

(Evol.Equipe!$E$5:$E$71<=Histograma!Z$2) >> se já foi admitido

(Evol.Equipe!$F$5:$F$71>=Histograma!Z$2) >> se ainda não foi demitido

Repare que aqui a fórmula indica >= (maior ou igual)! Talvez considere como aviso prévio, mas isso é só uma suposição minha...

Juntando tudo: Em Z6 teremos os funcionários já admitidos em AGO-09, ainda não demitidos e que são “CSTC”.

O resto é moleza.

- A fórmula em C2 da Aba “Histograma” =MÍNIMO(Evol.Equipe!E5:E68) >> pega a menor data de Admissão;

- As fórmulas de D2 a Z2 da Aba “Histograma” >> pegam a data anterior e somam 1 no mês;

- Fazer o gráfico.

Por fim, vale dizer que o mesmo resultado (da fórmula de matriz) poderia ser obtido de outras formas, mas fica para uma próxima vez.

Classificar por cor

Situação normal para uma pessoa que começa a usar o Excel no trabalho:

- Recebe uma tabela grande e o chefe pede para marcar, por exemplo, os valores duvidosos para ele conferir depois.
- Feliz e contente o novato vai colorindo as células da coluna em questão para depois mostrar para o chefe.
- Após apresentar o fruto de muitas horas de trabalho, o chefe mostra um sorriso de canto de boca, indicando a satisfação, ao analisar uns três ou quatro números, e faz um último pedido. Separe para mim apenas os que você marcou de amarelo.

E agora?

Para quem usa versões antigas do Excel, a sugestão é, sempre que tiver um trabalho desses, utilizar uma coluna alternativa para realizar as marcações com "x", assim pode usar "filtro" ao final do trabalho.

Com o Excel 2007 já é possível filtrar ou classificar por cores.

Simples, mas funcional

Aí vai uma dica interessante.

A função 'Texto' serve para escrever o nome do mês e do dia da semana.

Converte um valor para texto em um formato de número específico.
TEXTO(valor;format_texto)

Exemplo: tenho na célula A1 a data: 30/05/2009
A fórmula =TEXTO(A1;"mmmm") retorna o texto "maio"

Trocando "mmmm" por:
- "dddd" >resultado> "sábado"
- "ddd" >resultado> "sáb"
- "mmm" >resultado> "mai"

Texto para colunas

Uma leitora fanática do meu blog me deu uma dica de post interessante.

"eu tenho um arquivo deste tipo"
E me mandou o xls.
"quero separar os números numa coluna e as palavras em outra"

A resposta é: Dados > Texto para colunas (para versões anteriores ao Excel 2007 tem que ver onde isso ficava... Estranho! Já estou me acostumando com a nova versão onde tudo fica escondido).

Para algumas tabelas, basta usar diretamente. Escolher o separador (vírgula, cifrão, dois pontos etc) e ser feliz! Outras tabelas exigem um pouco mais de habilidade para não gastar horas separando 495 linhas de um arquivo...

Dica de mestre: para formulários com campos de tamanho fixo, use a fonte 'Courier New' e depois faça a transformação de texto para coluna com "Largura fixa"

Veja na página da Microsoft (Portugal) um tutorial fácil para isso.

Yes, weekend

Meus milhares de leitores gostam quando chega um fim de semana, pois na segunda-feira podem aparecer com algo novo para mostrar ao chefe...

Meu último relatório de visitas aponta a seguinte divisão de visitas por dia da semana:
Monday 16.75%
Tuesday 17.51%
Wednesday 17.39%
Thursday 15.19%
Friday 15.19%
Saturday 9.90%
Sunday 8.08%

Somando Sábado (9.90%), Domingo (8.08%) e Segunda (16.75%), temos 34,73%. Isso comprova que muita gente só quer se mostrar para o chefe!

Mesmo assim, continuarei na minha incansável batalha de DIE (Diminuição da Ignorância em Excel).

Como este não é um blog sobre política, não poderia falar aqui que o título deste post é também uma forma de reprovação das primeiras besteiras do senhor Yes-We-Can (levantar o veto ao financiamento dos grupos pró-aborto). Tanta coisa boa para fazer... Mas como diz o velho ditado: "quem nunca comeu melado, quando come se lambuza".

Porém o que falei acima não é sobre política, mas bom senso. Portanto vou deixar escrito.

Chega de enrolar os meus leitores.

Você já deve ter ouvido de alguém que trabalha com banco de dados a seguinte afirmação:
- Vou te mandar um csv com os dados e aí você se vira...
Daí você recebe aquele arquivo com todas as colunas agrupadas em uma só...

Parece que a pessoa está de má vontade, mas na verdade essa pessoa está te ajudando.
Basta você pegar o arquivo e mandar transformar texto em colunas.

Se quiserem, podemos falar mais sobre isso depois.

Obter dados externos - WEB

Função muito interessante para quem tem que buscar dados muitas vezes em uma mesma página.

Exemplo: todos os dias tenho que inserir na minha planilha a ptax do dia anterior.

Vou ao site do bcb, vejo lá e digito na minha planilha.

Posso fazer isso automaticamente com o link de dados externos do Excel.

Passo 1: Veja na figura abaixo.


Passo 2: Entrar com o endereço do site que tem os dados e escolher a área que me interessa. Na figura abaixo selecionei apenas a parte da cotação.


Passo 3: Apontar onde desejo colar os dados.


Passo 4: Aguardar...

Pronto!
Veja que agora o Excel já tem os seus dados. E não é ctrl+c ctrl+v...


Para ajustar os parâmetros da sua busca (como frequência de atualização), entre em "propriedades da conexão".

Atalho para dependente

Outro dia uma pessoa me perguntou: "Tem um atalho para ir ao dependente?"

Para dependentes na mesma aba: F5 + (Alt+E) + N (para Excel 2007 em português)

Ou, se preferir, com uma macro básica (e associar uma tecla de atalho):

Sub D1()
Selection.Dependents.Select
End Sub

ou (para dependente direto)...

Sub D2()
Selection.DirectDependents.Select
End Sub

Para dependentes em outras planilhas também é possível mas acho que o crime não compensa...

Contas com "texto"

Ainda não é para pagar a dívida, mas serve por enquanto...

Veja o exemplo abaixo:

Estou calculando um aumento usando como base as linhas 3 e 5. No primeiro caso não é possível calcular, mas no segundo, sim.

Repare que, na linha 3, "Crescimento de 5% a.a." é um texto, enquanto que na linha 5 ele é um valor disfarçado de texto.

Ele é 5%, com a formatação personalizada:

Entre em "formatar células" (ctrl+1)

Escolha tipo "personalizado"

E faça o formato de número que você desejar:

Soma 3D

Tenho várias abas com o mesmo modelo e desejo ter na primeira aba os valores consolidados.

Posso fazer:
=F9+Plan2!F9+Plan3!F8+Plan4!F8+Plan5!F8+Plan6!F8+Plan7!F8+Plan8!F8+Plan9!F8

Ou simplesmente:
=SOMA(Plan1:Plan9!F8)

Largue o mouse

Para quem quer ser mais rápido no Excel, precisa aprender a largar o mouse e usar mais os atalhos.
OK... Desta vez a Microsoft vacilou e trocou diversas combinações de atalho na versão 2007.

Mas vale a pena aprender novamente. Aos poucos vou publicando alguns atalhos aqui.

ctrl + barra de espaço >> seleciona coluna inteira
shift + barra de espaço >> seleciona linha inteira

alt + c + v + v >> colar especial valores (2007)
alt + e + a + v >> colar especial valores (versões anteriores)

Copiando uma fórmula com o mouse

Talvez essa seja a operação mais usada no Excel. Você tem uma célula com uma fórmula e precisa repeti-la mais vezes.

Essa operação pode ser feita puxando o quadrado preto que fica embaixo de cada célula quando ela está selecionada. Isto serve não só para fórmulas, mas para seqüências de números também. Você coloca dois ou três números da sua seqüência, que o Excel preenche o resto para você. Se ele não descobrir a seqüência, ele repete os números N vezes para preencher a seleção.

Na imagem abaixo, fiz o exemplo mais simples de seqüência.



Tendo uma seqüência de dados, por exemplo é possível definir uma fórmula e replicá-la para toda seqüência usando o mesmo quadrado preto no canto inferior direito da célula:




Note que a fórmula foi mudando de acordo com a célula. Caso se queira fixar uma célula na fórmula, coloque "$" antes da coluna e/ou da linha:




Dessa forma, onde houver um "$" a coluna ou linha não será alterada ao se arrastar a fórmula.

Repetir operação

Caso típico:
Estou percorrendo uma lista e quero colorir de amarelo as células com um determinado valor (quando não posso usar formatação condicional).
Selecionei a primeira célula e, com o mouse, cliquei na ferramenta de preenchimento amarelo.
Vou com a seta para baixo percorrendo a minha lista e encontro uma nova célula a ser preenchida de amarelo. F4 resolve!
Essa tecla repete a última operação realizada (que foi colorir a célula).
Só para lembrar: F4 = ctrl + Y.