segunda-feira, 18 de março de 2013

RESOLVENDO DUVIDAS DA NET : Busca no Excel - Fórmula?

RESOLVENDO DUVIDAS DA NET

Boa tarde! Galera hoje postarei uma solução para a duvida de um internauta que postou no Yahoo Respostas a seguinte  dúvida...



Busca no Excel - Fórmula?

Olá. Tenho essa matriz no Excel e preciso realizar uma busca nela.

NOME MOTIVO ANO

Antonio Jogando bola 2010
Bruno Faltou aula 2010
Antonio Jogando bola 2010
Tiago Briga 2011
Antonio Faltou aula 2012

Quero que ele me diga quantas vezes o Antônio aparece na lista, mas sem contar registros repetidos..
Nesse exemplo, seria duas vezes (1 em 2010 e 1 em 2012).
Obs.: Não posso fazer um filtro prévio porque são mais de 30 mil registros e eu preciso saber do antonio, do bruno, etc..
Quero que ele não conte nos casos em que tiver mais de um registro igual.
Obrigado.


RESOLUÇÃO

Ele me mandou uma planilha onde fiz um esboço para ele de como poderíamos fazer isso com macros e aqui vai um  passo a passo de como o fiz...


Autor:
O que eu preciso é de uma fórmula ou macro que me retorne o número de visitas que foram realizadas naquela empresa. Por exemplo, no caso da empresa "D" (na tabela 1 mais a baixo) ela teve dois problemas ambientais, mas no mesmo dia. Então ele teria que registrar que só houve 1 visita. Já no caso da empresa "H"
ela teve 8 problemas ambientais, no entanto em 4 dias diferentes. 


A planilha que nosso colega me passou por e-mail tem 2 folhas... veja:




TABELA 1 ONDE BUSCARÁ AS INFORMAÇÕES



TABELA 2 ONDE IRÁ APARECER O RESULTADO DA BUSCA

(o autor da pergunta preencheu manualmente o resultado esperado para analisarmos)

Então o desafio é percorrer a tabela 1, e verificar a quantidades de visitas que a empresa recebeu mas em dias diferentes, e não em quantidades de visitas, como citado pelo próprio autor da pergunta...


Criei uma terceira tabela para o código extrair as informações para ela.... empresa e data...

PASSO A PASSO


Primeiro devemos entrar no Editor Visual Basic... Faremos isso através do atalho "Alt + F11".

Veja imagem do editor já aberto...



Agora vamos inserir um módulo que será onde iremos escrever nosso código...


Para inserirmos o módulos clicamos no canto direito do botão inserir userform 





e um submenu será aberto,
e devemos escolher a
opção Módulo







Veja onde iremos escrever o nosso código...



O código que fará a busca é o seguinte:

Sub empresas() 
Dim linha As Integer
Dim lin As Integer

Dim limpar As Integer
limpar = 2

Do Until Plan2.Range("d" & limpar).Value = ""
limpar = limpar + 1
Loop
Plan2.Range("d2:e" & limpar).ClearContents
linha = 2
lin = 2
Do Until Plan1.Range("c" & linha).Value = ""
ENCONTRADO = "NAO"
Do Until Plan2.Range("d" & lin).Value = ""
If Plan1.Range("c" & linha).Value & Plan1.Range("b" & linha).Value = Plan2.Range("d" & lin).Value & Plan2.Range("e" & lin).Value Then
ENCONTRADO = "SIM"
Exit Do
Else
lin = lin + 1
End If
Loop
If ENCONTRADO = "SIM" Then
Else
Plan2.Range("d" & lin).Value = Plan1.Range("c" & linha).Value
Plan2.Range("e" & lin).Value = Plan1.Range("b" & linha).Value

End If
linha = linha + 1
lin = 2
Loop
End Sub


EXECUTANDO O CÓDIGO

Insira um botão (forma) na plan2...

Clique com o botão direito do mouse sobre a forma criada...





escolha a opção Atribuir macro...








escolha a macro que criamos (empresa), e pronto vamos executar clicando no botão criado... vejamos o resultado...

veja que a lista de empresas
aparece uma unica vez em cada dia que foi visitada
mesmo que tenha sido mais de uma vez por dia...













agora vou apagar as informações digitadas pelo autor da pergunta na coluna B da plan2...















vou colocar uma fórmula cont.se, para contar quantas vezes a empresa X aparece na coluna D...





agora é só copiar a fórmula para as células abaixo...


COMO ASSOCIAR A SUA NECESSIDADE

Bom repare que no código em alguns trechos diz o seguinte

plan1.range(......      que é referencia das planilhas
plan2.range(.......     então terá que adequar as suas.




então eu vou marcar o que você terá que mudar e o que você pode só copiar e colar no editor de códigos



Sub empresas() 
Dim linha As IntegerDim lin As Integer

Dim limpar As Integer
limpar = 2

Do Until Plan2.Range("d" & limpar).Value = ""
limpar = limpar + 1
Loop
Plan2.Range("d2:e" & limpar).ClearContents

essa parte do código é referente a tabela extra que criei, então Plan2.Range("d" & limpar) quer dizer na planilha 2, célula D2,3,4,5,6 depende do valor da variável limpar.... nesse caso ela vai ter o valor 26, pois começou como 2 e percorreu 25 linhas então 
Plan2.Range("d2:e" & limpar).ClearContents
irá limpar o intervalo de D2 até E26, assim efetua uma nova busca a partir do zero...

linha = 2
lin = 2

Do Until Plan1.Range("c" & linha).Value = ""
essa linha é referente a coluna C da planilha 1 (1. Matriz de dados)
ENCONTRADO = "NAO"

Do Until Plan2.Range("d" & lin).Value = ""
referente a coluna D na plan2 (2. O QUE EU PRECISO)
If Plan1.Range("c" & linha).Value & Plan1.Range("b" & linha).Value = Plan2.Range("d" & lin).Value & Plan2.Range("e" & lin).Value Then
aqui é onde o código ira juntar o que tem nas colunas "c" e "b" da plan1, e verá se já estão na tabela extra nas colunas "d" e "e"da plan2.
ENCONTRADO = "SIM"se já já tiver vai marcar a varaivel encontrado com "sim" e vai sair dessa linha já passando para outra e verificando tudo de novo...
Exit Do
Else
lin = lin + 1
End If
Loop
If ENCONTRADO = "SIM" Then'aqui se encontrado for igual a sim
Else 'fazer
Plan2.Range("d" & lin).Value = Plan1.Range("c" & linha).Value
Plan2.Range("e" & lin).Value = Plan1.Range("b" & linha).Value
vai pegar o valor que estiver na plan1 e jogar para a plan2, sendo o nome da empresa e a data.
End If
linha = linha + 1
lin = 2
Loop
End Sub


é isso aí se tiver alguma dúvida meu e-mail é


 edivan.cabral@yahoo.com.br










2 comentários: