O Melhor do Excel

Pergunta:
Olá, Gostaria de tirar uma dúvida básica em relação à tabela dinâmica. Eu tenho uma base de dados, que vai por exemplo até a linha 1000. Em cima dessa bse de dados, eu gero uma tabela dinâmica. Entretanto, se por exemplo eu tiver que acrescentar dados nessa base, para digamos, 2000 linhas, eu não consigo atualizar a minha tabela dinâmica para que ela puxe esses novos dados. No excel 2003, eu conseguia editar a minha base de dados, mas no excel 2007 não consigo fazer isso. Se eu acrescento dados à minha base, eu tenho que gerar outra tabela dinâmica. Como faço para editar minha fonte de informações da tabela dinâmica do excel 2007? Existe essa opção, ou eu tenho mesmo que selecionar os dados novamente e gerar outra tabela? Muito obrigado pela ajuda. ps: Descobri hoje o site de vocês, e estão de parabéns pelo trabalho!

Resposta direta:
Agradecemos pelos parabéns. Isso incentiva nosso trabalho.
Sobre a sua dúvida...
Quando gerar a tabela dinâmica siga os passos abaixo (Excel 2007)
• Clique dentro da Tabela Dinâmica
• Na barra de menus vai aparecer um ítem "Ferramentas de Tabela Dinâmica" com Opções e Designer
• Clique em Opções
• Clique em Alterar Fonte de Dados
Pronto, dai pra frente você vai reconhecer...

Agora a Mágica:
Selecione a sua tabela e crie um nome para ela:

Agora torne esse nome dinâmico.
Vá em Gerenciador de Nomes

E altere de acordo com a fórmula (faça as alterações da fórmula de acordo com a posição da sua tabela).
=Plan1!$A$1:DESLOC(Plan1!$C$1;CONT.VALORES(Plan1!$C$1:$C$2000)-1;0)

Repare que o fim está marcado para C2000. Se você preferir, pode usar um valor de linha mais alto.
Agora crie a sua tabela dinâmica, com o intervalo de entrada sendo “TABELA”.

Pronto! Sempre que você acrescentar um nome na lista, é só mandar atualizar a tabela dinâmica que ela está preparada para o intervalo todo.

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.

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"

Uma pessoa escreveu no orkut:

mudar a cor dos algarismos
Planilha de controle de estoque com a segunte formação
a1 - data ent/saida
b1 - Nota fiscal/Req. Material
c1 - entrada
d1 - saída
e1 - saldo
gostaria de saber se existe alguma fórmula que, quando eu lançar a entrada (c2) ou a saída (d2) automaticamente as células no caso da entrada (a2;b2, c2 e e2) ficassem por exemplo com as letras ou números em azul e quando eu lançar a saída(d2) as células (a2;b2, d2 e e2) ficassem com as letras vermelhas. Eu procurei em função mas não consegui

Isso se resolve facilmente com Formatação Condicional.

Selecione a célula A2.
Formatação Condicional > Nova Regra


Usar fórmula...


Para a parte azul, use a fórmula:
=E($A2<>"";$B2<>"";$C2<>"";$D2="";$E2<>"")

Para a parte azul, crie nova regra e use a fórmula:
=E($A2<>"";$B2<>"";$C2<>"";$D2<>"";$E2="")

Agora é só copiar a formatação para as outras células...


Por isso tem o "$" na coluna e não na linha.

Outro dia, caçando algum post na Internet para este blog, encontrei algo interessante:

Como fazer uma busca com correspondência em duas colunas, usando somarproduto.

Veja na figura abaixo que quero descobrir onde está (na minha planilha) o Daniel (E2).
Mas não serve o Daniel da Itália. Tem que ser o do Chile (E3).

A resposta dada em (E4) é o resultado da fórmula:
=SOMARPRODUTO((B2:B13=E2)*(C2:C13=E3)*(A2:A13))

Isso apenas elimina uma coluna...

Para sobreviver a este período de muitas frentes ao mesmo tempo, só mesmo postando uns vídeos...

Este vídeo é bem básico para quem ainda não sabe usar o "$".

Pergunta de um leitor deste blog.

Bom dia,

Tenho a seguinte sequência numérica no excel na posição horizontal

..A B C D E F G H
1 1 2 3 4 5 6 7 8
2

Quando eu excluir uma coluna, quero que a sequência continue, e não fique faltando nenhum número, como a figura abaixo que, ao excluir a coluna E, mudou para o numero 6.

..A B C D E F G H
1 1 2 3 4 6 7 8 9
2

Resposta:

Que tal usar a fórmula COL no lugar dos números?

......A........B........C........D
1 =COL(A1) =COL(B1) =COL(C1) =COL(D1)

Bom dia, 2009!

Aqui vai uma dica interessante.
Já tive que criar uma planilha para escrever por extenso vários números.
Agora encontrei uma pessoa bacana que resolveu ajudar quem tem esse tipo de problema (e que já era um dos itens da minha wishlist).

Você pode baixar de graça o VExtenso e seguir as instruções para instalar. Encontrei uns pequenos bugs (poderia ser discutido em outro blog), mas nada que afete substantivamente a ferramenta.

Básico sobre referências em Excel.

Excelente aula sobre a função banco de dados do Excel.
Quem nunca usou esta função, vale a pena conferir.

Tenho uma lista que mistura números com texto e quero fazer uma soma dos números.

Todos sabemos que o Excel oferece diversos modos para resolver um problema.

Veja uma solução:



Na coluna "B" tenho o texto com números.

Na coluna "C" usei a fórmula "=ESQUERDA(B3;2)" para aparecer só os números. Porém repare que a fórmula está fixa para mostrar apenas os dois primeiros algarismos da esquerda. Além disso, o que aparece na coluna "C" ainda é interpretado como texto.

Na coluna "D" resolvi o problema de interpretação como valor, com a fórmula: "=VALOR(ESQUERDA(B3;2))"

Na coluna "E" resolvi todos os problemas com: "=VALOR(ESQUERDA(B3;NÚM.CARACT(B3)-6))"

E se não for tudo com "Reais"? Fica para um outro dia...

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

Veja o exemplo abaixo:

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:

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)

Leia até o fim...

Aposto que muita gente já sabe criar uma lista suspensa a partir de um intervalo de células.

Exemplo

Temos uma relação de países e capitais:

Basta escolher validação de dados:


Depois selecionar "Permitir Lista" e escolher o intervalo (no exemplo os dados estão entre A2 e A201):


Pronto! Com um "procv" podemos fazer algo interessante. Você escolhe o país e a célula ao lado apresenta a capital.



Porém o que muitos não sabem é como fazer uma lista que se ajuste automaticamente ao tamanho da tabela de dados...

As funções "desloc" e "corresp" podem ajudar. Veja abaixo e tente fazer.


OBS: Para entender as funções citadas, basta ver o help do Excel (F1).

Para quem quer fazer um resumo de dados sem usar tabela dinâmica, a ferramenta remover duplicatas pode ser muito útil.

Por exemplo, tenho uma lista de pessoas com seus gastos e desejo saber quanto cada pessoa gastou no total.


Separe os nomes em uma outra coluna. No menu Dados você encontrará "Remover Duplicatas".


A coluna H, após aplicar a ferramenta, terá apenas os nomes das pessoas sem repetição:


Agora ficou fácil. Basta usar a fórmula somase que o seu trabalho terminou.