Encontre dados semelhantes em duas planilhas e coloque na terceira planilha

Question

Qual é a maneira melhor/mais fácil de combinar dados de duas planilhas Excel diferentes em uma terceira planilha 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ário da planilha 1 com o nome, sobrenome e departamento da planilha 2.

Answer 1

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

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

• lookup valuesão os dados que você deseja pesquisar.
• table arraydefine as células das quais você deseja extrair dados, incluindo a coluna que contém seu alvo de pesquisa
• column index numberé o índice da coluna table arrayda qual você deseja extrair 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 se uma correspondência exata é necessária. Para manter as coisas simples, sempre defino isso como FALSE. Pressione F1 no Excel se precisar de mais detalhes.

O formato da sua fórmula será um pouco diferente dependendo se todos os seus dados estão na mesma pasta de trabalho ou não. Darei exemplos para cada um abaixo.

NOTA IMPORTANTE:O termo de pesquisa que você está usando comolookup value deveser localizável na primeira coluna table arraypara que VLOOKUP funcione.

---

Primeiro exemplo: todos os dados estarão na mesma pasta de trabalho do Excel, mas em planilhas diferentes. A primeira folha é chamada de “Nomes de usuário” e contém apenas os nomes de usuário. A segunda planilha é chamada de “Dados do Usuário” e contém todos os detalhes do usuário. Chamaremos a terceira planilha de "Resultados da pesquisa". A planilha "Dados do usuário" contém cinco colunas, A:E.

1. Certifique-se de que a planilha "Dados do usuário" contenha todos os nomes de usuário na coluna A.
2. Copie todos os nomes de usuário de "Nomes de usuário" para "Resultados de pesquisa".
   • Presumo que você esteja usando uma linha de cabeçalho, então o primeiro nome de usuário em "Resultados da pesquisa" será A2.
3. A fórmula para B2 em “Resultado da 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ê já deve ver o padrão aqui. Para cada coluna, você poderá apenas escrever a fórmula VLOOKUP na primeira célula (por exemplo: B2) e, em seguida, preencher a fórmula no restante da planilha. No entanto, recortar e colar a fórmulaentrecolunas não é tão simples - você precisa atualizar os valores table arraye column index number.

---

Segundo exemplo: Cada conjunto de dados é mantido em sua própria pasta de trabalho do Excel. Os nomes das planilhas na pasta de trabalho são padrão (ou seja: a primeira planilha é "Planilha1"). Os nomes dos arquivos da pasta de trabalho são "Usernames.xlsx", "User Data.xlsx" e "Lookup Results.xlsx". Tudo isso está em uma pasta chamada “Minhas Planilhas”, que fica na Área de Trabalho de um usuário chamado “Eu”.

1. Certifique-se de que a planilha "User Data.xlsx" tenha todos os nomes de usuário na coluna A.
2. Copie todos os nomes de usuário 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 "Lookup Result.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 "Lookup Result.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 "Lookup Result.xlsx" deve ser=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)

Novamente, você já deve conseguir ver o padrão aqui. Recorte/cole/ajuste conforme necessário nas linhas e nas colunas e pronto.

---

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

Answer 2

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

Answer 3

HLOOKUP/VLOOKUP - use uma fórmula para buscar o nome de usuário da planilha 1 e, em seguida, use o nome de usuário como chave e a planilha 2 como matriz de pesquisa para uma instância de HLOOKUP/VLOOKUP (não sei qual é qual, pois estou usando uma versão Excel que não seja em inglês).

Answer 4

Você pode usar a consulta de arquivos do Excel:

• 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
• Enquanto estiver na planilha 1, vá para a guia Dados, selecione "De outras fontes" e, no menu suspenso, selecione "Do Microsoft Query"
• Selecione o outro arquivo de planilha e confirme que deseja mesclar as colunas manualmente
• Na janela seguinte "Consulta de arquivos 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 - um link entre essas colunas será criado
• Vá para o menu Arquivo, clique em "Retornar dados para o MS Office Excel", uma caixa de diálogo Importar dados aparecerá
• Selecione a planilha para a qual você deseja que os dados correspondentes sejam importados
• Clique em OK - você deverá ver os dados correspondentes com as colunas de ambas as planilhas