Localizando colunas correspondentes no excel

Question

Eu nunca usei o excel antes, então eu preciso da solução mais simples disponível, e esta é uma tarefa de trabalho para esta semana, então não tive tempo para ler a documentação.

Basicamente, tenho duas tabelas, A e B , e ambas são milhares de linhas.

Descrição da minha tarefa: agora (já que não conheço melhor) estou fazendo isso manualmente:

1. Ir para a linha i na tabela B .
2. Selecione as entradas nas colunas B (a, b, c) dessa mesma linha.
3. Procure uma linha na tabela A onde a coluna A (b) corresponde à linha B (a) .
4. Cole as entradas das colunas B (a) da linha i no final do linha encontrada na última etapa.
5. Repita para a linha i + 1.

Exemplo: linha B(cat, dog, mouse) corresponde a A(mammal, cat, Mr. Whiskers) . Então, eu colaria B após A e teria A(mammal, cat, Mr. Whiskers, cat, dog, mouse) .

Nota: não estou me juntando a tabelas. Estou apenas estendendo a tabela A colando a linha A (b) se a linha A (b) corresponder à linha B (a).

Além disso, às vezes as entradas são escritas de forma ligeiramente diferente. Usar curingas para procurar candidatos seria útil.

Como a descrição deve ser permitida, essa tarefa é muito entediante e ineficiente se eu não souber como automatizar algumas operações (há milhares de entradas).

Todas as dicas rápidas sobre como ser mais produtivo são uma grande ajuda.

    

Answer 1

A resposta é, sem dúvida, uma das funções mais poderosas que você pode criar no excel. Se você é novo em excel, isso vai ser muito para engolir.

Dado o seu exemplo exato, talvez você consiga usar VLOOKUP . Mas estou supondo que a realidade da situação é um pouco mais complicada do que o seu exemplo, por isso estou dando a você algo que pode lidar com praticamente qualquer situação. É uma combinação da função INDEX e da função MATCH .

Eu criei duas tabelas:

TableA: Row1 (mamífero, furão, Mr. Whiskers)

TableB: Row1 (gato, cachorro, mouse)

Tabela B: Linha 2 (furão, iguana, tartaruga)

Tabela B: Linha 3 (cavalo, vaca, porco)

Na Tabela A: Coluna D, coloco a seguinte fórmula:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),1)

Primeiro, vamos ver apenas INDEX . INDEX recebe 3 argumentos, o intervalo que você deseja pesquisar, a linha do valor desejado e a coluna do valor desejado. O intervalo que você deseja pesquisar é especificado por TableB!$A$1:TableB!$C$3 .

TableB! diz ir para outra planilha chamada TableB

Os cifrões na frente da coluna e da linha dizem "estes são absolutos, não aumentam automaticamente ao preencher a fórmula (ou direita, esquerda, para cima)"

O 1 no final diz "a primeira coluna no intervalo", que no nosso caso é A e contém gato, furão e cavalo.

Agora, para obter a linha, precisamos usar outra função chamada MATCH para corresponder a um valor da linha em que estamos trabalhando atualmente. MATCH também leva 3 valores. O primeiro é o valor que você está procurando, o segundo é uma única coluna para pesquisar, e a final é como combinar (neste caso, o caminho FALSE para "correspondência exata").

Queremos corresponder o valor que está em TableA: ColumnB para a linha atual. Então o primeiro valor é $ B1. O que diz que a coluna B é absoluta, não a incrementa. Mas a linha deve ser incrementada para corresponder à linha em que estivermos quando preenchermos, para que não tenha o cifrão anterior.

A coluna a ser pesquisada é a coluna A na Tabela B e é absoluta, portanto não aumentará.

Se, em seguida, formos para TableA: ColumnE e colocarmos:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),2)

E na Tabela A: ColumnF colocamos:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),3)

Obtemos o resultado desejado. Observe que a única diferença nas fórmulas é o número final especificando a coluna a ser retornada.

Se mudarmos a Tabela A: ColunaB para "furão" então a Coluna D muda para furão, a Coluna E muda para iguana e a Coluna F muda para tartaruga.

Finalmente, selecione as células na linha 1 em D, E e F. Arraste o mouse para baixo para cobrir todas as linhas necessárias e pressione Ctrl + D, que preencherá a fórmula em todas as linhas, incrementando o termo de pesquisa em a função MATCH para procurar o valor na coluna B de cada linha.

Leitura adicional:

link