Excel - Encontre uma coluna específica em um arquivo CSV e copie para a planilha atual

Question

Eu recebo uma exportação regular de dados em um formato CSV, que gosto de incluir no Excel e gerar relatórios por meio de colunas auxiliares e tabelas dinâmicas.

Eu faço isso vinculando ao arquivo csv e copiando o conteúdo da célula das colunas de dados nas quais estou interessado.

Eu uso algo como = if ('caminho do arquivo [filename.csv] Sheet1'! A1 < > "", caminho do arquivo [filename.csv] Sheet1 '! A1, "")

Em seguida, preencho automaticamente a coluna inteira.

Isso funciona bem e puxa os dados para minha planilha atual, onde posso adicionar uma coluna auxiliar e executar uma tabela dinâmica a partir dela. Uma vez por semana, sobrescrevo o arquivo CSV com um novo e atualizo o pivô para produzir os relatórios de que preciso.

O problema que tenho é que ocasionalmente, o departamento de negócios que produz o CSV que é minha fonte, adicionará um novo campo aos dados e, como conseqüência, todas as referências de minha célula serão quebradas, e eu tenho que ir e mudar todas as minhas fórmulas para acomodar o novo campo, mesmo que eu não tenha nenhuma exigência para isso.

Todas as colunas no CSV têm cabeçalhos de coluna exclusivos.

O que eu gostaria de fazer é usar uma fórmula para encontrar a coluna específica que eu preciso no arquivo CSV e, em seguida, colocá-la na minha planilha.

Catalogue Number    Price   Purchase Date   Sales Office
1    £500.00    11-Sep  EMEA
2    £606.00    8-Aug   APAC
3    £454.00    3-Jul   NA
4    £2,132.00  29-Jan  NA
5    £548.00    30-Nov  APAC
6    £514.00    23-Apr  NA
7    £36.00     3-Feb   EMEA
8    £998.00    11-Oct  EMEA
9    £1,454.00  11-Sep  EMEA

No exemplo acima (dados fictícios), por exemplo, gostaria de identificar a coluna intitulada "Price" (D1: D10) e, em seguida, puxar toda a coluna para uma nova planilha.

Eu tentei experimentar com o HLOOKUP, INDEX e MATCH, mas não consegui descobrir como obter a coluna inteira. No exemplo abaixo, você pode ver o que eu tentei e, embora eu possa identificar a coluna da direita, não consigo fazer o preenchimento automático repetir a fórmula da maneira que eu exigiria.

Formula "=MATCH("Catalogue Number",5:5,0)"
Output  3

Formula "=MATCH("Price",5:5,0)"
Output  4

Formula "=HLOOKUP("Price",C1:F10,2)"
Output  500

Formula "=HLOOKUP("Price",$1:$1048576,2,0)"
Output  500
    500

Formula "=INDEX($1:$1048576,2,(MATCH("Price",$1:$1,0)))"
Output  500
    500
    500
    500
    500
    500

Estou usando o Excel 2010.

    

Answer 1

=INDIRECT("R"&ROW()&"C"&MATCH("Price",$A$1:$F$1,0),0)

Isso funciona criando uma referência de célula INDIRECT() R1C1 em vez da referência de célula A1 normal.

A linha é identificada usando ROW() . A coluna é preenchida usando MATCH() para localizar o número da coluna do título em questão (contando a partir da esquerda, A = 1).

Observação - Certifique-se de que os critérios de pesquisa MATCH vão até a coluna A (ou seja, $ A $ 1: $ F $ 1, mesmo se os cabeçalhos das colunas começarem na coluna B A referência R1C1 ficará ligeiramente desalinhada.)