Encontre dados parecidos em duas planilhas e coloque na 3ª planilha

3

Qual é a maneira melhor / mais fácil de combinar dados de duas planilhas diferentes do Excel em uma terceira planilha do Excel? A planilha 1 terá apenas o nome de usuário. A planilha 2 teria muitas informações, incluindo nome, sobrenome, departamento, nome de usuário, etc. O que é necessário é que a terceira planilha exiba os nomes de usuários da planilha 1 com o nome, sobrenome e departamento da planilha 2.

    
por CSF 24.01.2013 / 20:18

4 respostas

5

A função que você deseja usar é VLOOKUP . Como você faz isso vai depender um pouco de como suas planilhas estão organizadas, mas todas seguirão a mesma sintaxe:

= VLOOKUP ( lookup value , table array , column index number , range lookup )

  • lookup value são os dados que você deseja pesquisar.
  • table array define as células das quais você deseja extrair dados, incluindo a coluna que contém seu destino de pesquisa
  • column index number é o índice da coluna dentro de table array a partir do qual você deseja obter informações. (por exemplo: para uma matriz de A: E, a coluna D seria 4 .)
  • range lookup é uma opção TRUE / FALSE para especificar se uma correspondência aproximada é aceitável ou uma correspondência exata é necessária. Para manter as coisas simples, eu sempre configuro isso para FALSE . Pressione F1 no Excel, se precisar de mais detalhes.

O formato da sua fórmula será um pouco diferente, dependendo de os dados estarem todos na mesma pasta de trabalho ou não. Vou dar exemplos para cada um abaixo.

OBSERVAÇÃO IMPORTANTE: O termo de pesquisa que você está usando como lookup value deve ser encontrado na primeira coluna de table array para o VLOOKUP funcionar.

Primeiro exemplo : todos os dados estarão na mesma pasta de trabalho do Excel, mas em folhas diferentes. A primeira folha é rotulada "Usernames" e contém apenas os nomes de usuários. A segunda folha é chamada "Dados do usuário" e contém todos os detalhes do usuário. Vamos chamar a terceira folha "Resultados da pesquisa". A planilha "Dados do usuário" contém cinco colunas, A:E .

  1. Verifique se a planilha "Dados do usuário" tem todos os nomes de usuários na coluna A.
  2. Copie todos os nomes de usuários de "Usernames" para "Lookup Results".
    • Eu presumo que você esteja usando uma linha de cabeçalho, então o primeiro nome de usuário em "Resultados da pesquisa" chegará na A2.
  3. A fórmula para B2 em "Resultado de pesquisa" deve ser: =VLOOKUP(A2,'User Data'!A:B,2,FALSE)
  4. A fórmula para B3 em "Resultado da pesquisa" deve ser: =VLOOKUP(A3,'User Data'!A:B,2,FALSE)
  5. A fórmula para C2 em "Resultado da pesquisa" deve ser: =VLOOKUP(A2,'User Data'!A:C,3,FALSE)

Você deve ver o padrão aqui agora. Para cada coluna, você deve apenas escrever a fórmula VLOOKUP na primeira célula (por exemplo, B2) e preencher a fórmula no restante da planilha. No entanto, as colunas recortar e colar da fórmula em não são tão simples: você precisa atualizar os valores table array e column index number .

Segundo exemplo : cada conjunto de dados é mantido em sua própria pasta de trabalho do Excel. Os nomes das folhas dentro da pasta de trabalho são padrão (por exemplo, a primeira folha é "Folha1"). Os nomes de arquivo da pasta de trabalho são "Usernames.xlsx", "User Data.xlsx" e "Lookup Results.xlsx". Todos eles estão em uma pasta chamada "Minhas planilhas", que está na área de trabalho de um usuário chamado "Eu".

  1. Verifique se a planilha "User Data.xlsx" tem todos os nomes de usuários na coluna A.
  2. Copie todos os nomes de usuários de "Usernames.xlsx" para "Lookup Results.xlsx".
    • Novamente, presumindo que você use uma linha de cabeçalho, isso começará em A2.
  3. A fórmula para B2 em "Resultado da pesquisa.xlsx" deve ser =VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  4. A fórmula para B3 em "Resultado da consulta.xlsx" deve ser =VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  5. A fórmula para C2 em "Resultado da consulta.xlsx" deve ser =VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)

Mais uma vez, você deve poder ver o padrão aqui agora. Recorte / cole / ajuste conforme necessário nas linhas e nas colunas, e você está definido.

Uma outra coisa a ter em mente aqui é que essa planilha não será atualizada automaticamente para alterações nos dados de "nomes de usuários". Alterações na planilha "Dados do usuário" podem ser obtidas com este método, mas técnicas mais avançadas serão necessárias se você quiser seguir as alterações em "Nomes de usuário" também.

    
por 24.01.2013 / 22:05
1

Vlookup e similares só funcionarão se os dados em ambas as planilhas forem idênticos.
Eu acredito que o que você precisa é o complemento de lógica difusa para o Excel. Isso permitirá que você encontre resultados semelhantes com base em vários parâmetros diferentes. Verifique a página de download .

    
por 25.05.2015 / 13:47
0

HLOOKUP / VLOOKUP - use uma fórmula para buscar o nome de usuário da planilha 1 e use o nome de usuário como chave e planilha 2 como uma matriz de pesquisa para uma instância de HLOOKUP / VLOOKUP (não sei qual é qual, como estou usando uma versão do Excel não inglesa).

    
por 24.01.2013 / 21:21
0

Você pode usar o Query from Excel Files:

  • Defina o nome do conjunto de dados na Planilha 1 (guia Fórmulas - > Definir nome)
  • Defina o nome do conjunto de dados na planilha 2
  • Na planilha 1, vá para a guia Dados, selecione "De outras fontes" e, na lista suspensa, selecione "Do Microsoft Query"
  • Selecione o outro arquivo de planilha e confirme que você deseja mesclar as colunas manualmente
  • Na janela seguinte "Consulta de arquivos do Excel", arraste e solte a coluna "nome de usuário" do primeiro conjunto de dados na coluna "nome de usuário" do segundo conjunto de dados - será criado um link entre essas colunas
  • Vá para o menu Arquivo, clique em "Retornar dados para o MS Office Excel", uma caixa de diálogo Importar dados será exibida
  • Selecione a folha na qual você deseja que os dados correspondentes sejam importados
  • Clique em OK - você verá dados correspondentes com colunas de ambas as planilhas

Ou se você não se importar de enviar seus arquivos para um serviço on-line, poderá usar, por exemplo, link e mesclar as folhas usando drag & drop (Disclaimer: Eu sou autor da ferramenta).

Espero que isso ajude.

    
por 11.11.2014 / 10:21