Como levar dados selecionados de uma planilha para outra

Uma das técnicas mais úteis do Excel são os autofiltros. Nesse vídeo abaixo … tem uma dica de como funcionam os autofiltros. A vantagem dessa técnica é que é fácil e rápido de usar. Basta marcar a base de dados e selecionar o filtro desejado. Em conjunto com a função subtotal() pode-se facilmente obter somas, contagens, maiores e menores valores apenas dos registros que atenderam aquele filtro.

Apesar de muito útil, prática e rápida, essa técnica tem algumas desvantagens. Se for necessário usar o valor de uma célula para filtrar os dados, essa técnica já não funciona. Outra desvantagem é que a manipulação de múltiplos filtros ao mesmo tempo pode confundir o usuário e dar resultados inesperados.

Existe uma alternativa a essa técnica simples. Essa alternativa requer um pouco mais de fórmulas mas pode ser útil em alguns casos. Para ilustrar essa técnica, eu fiz uma planilha que pode ser baixada aqui

Essa pasta é simples. Na plan2 existe uma relação de vendas feitas por cliente relacionando os produtos, preços, quantidade, etc. Os dados são inseridos nas colunas de B a N na plan2, registrando as vendas.

O que se quer é … ao digitar na plan1 o nome de um cliente na célula E4 e o código do produto em E5, listar as vendas realizadas desse cliente+produto. Ou seja, vamos usar um filtro com dois critérios, o cliente e o produto. A relação aparecerá na plan1 mesmo, mantendo plan2 intacta.

Como fazer ?

Criamos em plan2 3 colunas auxiliares que serão calculadas automaticamente. A primeira coluna é a coluna O que indicará se o cliente daquela linha é o mesmo cliente colocado em E4 (da plan1). A coluna P indicará se o produto daquela linha é o mesmo produto colocado em E5 (da plan1). Essas duas colunas terão apenas os valores 1 caso seja verdadeiro e 0 caso seja falso. Ou seja, a coluna O terá um 1 para cada linha que tiver o mesmo cliente que desejamos. A coluna P terá um 1 em cada linha que tiver o produto que desejamos.

A coluna Q irá fazer a combinação dos dois critérios, ou seja, ela só terá 1 se O e P tiverem ambas 1. Isso é feito com uma simples multiplicação O8 * P8 só dará 1 se ambos forem 1. Ou seja, só dará 1 se o cliente for o desejado e o produto também. Além de calcularmos se a combinação cliente * produto é a desejada somamos esse resultado ao resultado da linha anterior. No caso da primeira linha (a linha 8) não temos linha anterior. A soma com a linha anterior só acontece a partir da linha 9 em diante.

Esse truque de somar a linha anterior serve para marcar quando acharmos mais um novo cliente * produto que atende ao filtro especificado em E4 e E5. A coluna Q então terá uma “contagem” que muda de número apenas quando encontra uma nova linha que atendeu ao critério.

Muito bem, plan2 agora tem as 3 colunas auxiliares que fizeram o filtro. Toda vez que o valor de Q mudar é porque naquela linha tem um novo cliente * produto que atende ao critério. O que temos que fazer é levar essas linhas, e apenas essas linhas, para plan1. Como fazer isso ?

Em plan1 a primeira coluna contém uma simples relação crescente de números. Para cada linha dessa, vamos procurar na lista de plan2 qual o ítem que corresponde a ela na coluna Q. Ou seja, em que linha da faixa que começa em Q8 aparece o número que está na coluna A ? Ou … perguntando pelo significado, em que linha da coluna Q da plan2 aparece o primeiro cliente * produto que atende o critério desejado ? A coluna B de plan1 contém essa resposta a essa pergunta. Essa resposta é obtida com a função corresp() assim

=CORRESP(A27;Plan2!$Q$8:$Q$17;0)

A27 é o número crescente do itens que atendem ao critério, 1 o primeiro ítem, 2 o segundo ítem e assim por diante, entre A27 e A36

Plan2!$Q$8:$Q$17 é a coluna de plan2, calculada, que nos informa a cada novo número que achamos um cliente * produto desejado

o 0 (zero) usado na corresp() é para garantir que a busca que corresp() irá realizar é “exata”, ou seja, queremos exatamente o primeiro, depois o segundo, depois o terceiro, etc. Quando o item não é encontrado, corresp() retorna #N/D. No exemplo, não existe 3º item que atenda ao critério Mateus e Painço.

Na coluna B de Plan1 teremos então a lista de números de linha de Q8:Q17 da plan2 onde tem os itens desejados. Agora é só trazer os dados da plan2 para plan1. Para fazer isso, usamos a função INDICE() que nada mais faz do que pegar o elemento desejado de uma matriz. INDICE() funciona assim

indice( matriz original ; número da linha ; número da coluna )

índice() retorna o elemento da matriz original que estão na linha x coluna informada.

Queremos o peso bruto, a tara , o liquido bruto, o %l/kg, o %u/kg, e o total liquido. Essas colunas em plan2 são E, F, G, H, I e J respectivamente.

O número da linha foi calculado e está em B, é só aplicar então
=ÍNDICE(Plan2!E$8:E$17;B27;1) para obtermos o peso bruto, ou seja, queremos o elemento cujo número está em b27 da lista de pesos brutos que está na plan2 entre E8:E17

para não encher a planilha com erros #N/D fazemos um pequeno SE() antes assim

=SE(É.NÃO.DISP($B27);””;ÍNDICE(Plan2!E$8:E$17;Plan1!$B27;1))

Ou seja, se b27 for #N/D exibimos um nada “”. Se for um número, exibimos o valor buscado na matriz original.

Nesse exemplo, usamos 2 critérios. Através de adição de mais colunas auxiliares em plan2 a partir da coluna P podemos colocar quantos critérios quisermos. Podemos usar um critério só. É só modificar a coluna Q para se adequar a fórmula.

Anúncios

Comente aqui ou faça uma pergunta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s