Excel - найти конкретный столбец в файле CSV и скопировать на текущий лист

Question

Я получаю регулярный экспорт данных в формате CSV, который мне нравится использовать в Excel и создавать отчеты с помощью вспомогательных столбцов и сводных таблиц.

Я делаю это, связываясь с файлом CSV и копируя содержимое ячейки из столбцов данных, которые меня интересуют.

Я использую что-то вроде = if('путь к файлу [filename.csv] Sheet1'!A1 <> "", 'путь к файлу [filename.csv] Sheet1'!A1 "")

Затем я автоматически заполняю весь столбец.

Это хорошо работает, и данные перетаскиваются в мой текущий лист, где я могу добавить вспомогательный столбец и запустить из него сводную таблицу. Раз в неделю я перезаписываю CSV-файл новым и обновляю сводную таблицу, чтобы получить нужные мне отчеты.

У меня проблема в том, что иногда бизнес-отдел, который создает CSV, который является моим источником, добавляет новое поле к данным, и, как следствие, все ссылки на ячейки меняются, и мне приходится идти и менять все мои формулы, чтобы приспособить новое поле, хотя у меня нет требования к нему вообще.

Все столбцы в CSV имеют уникальные заголовки столбцов.

То, что я хотел бы сделать, это использовать формулу, чтобы найти конкретный столбец, который мне требуется в CSV, а затем перетащить его в свой рабочий лист.

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

Например, в приведенном выше примере (фиктивные данные) я хотел бы указать столбец с заголовком «Цена» (D1:D10), а затем перетащить весь столбец на новый лист.

Я пытался поэкспериментировать с HLOOKUP, INDEX и MATCH, но не могу понять, как получить весь столбец. В приведенном ниже примере вы можете увидеть, что я пытался, и хотя я могу определить правильный столбец, я не могу получить автозаполнение для успешной итерации формулы так, как мне бы того хотелось.

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

Я использую Excel 2010.

Answer 1

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

Это работает путем создания ссылки на ячейку INDIRECT INDIRECT() R1C1 вместо обычной ссылки на ячейку A1.

Строка идентифицируется с помощью ROW() . Столбец заполняется с помощью MATCH() чтобы найти номер столбца рассматриваемого заголовка (считая слева, A = 1).

Примечание. Убедитесь, что критерии поиска MATCH полностью соответствуют столбцу A (т. Е. $ A $ 1:$ F $ 1, даже если заголовки столбцов начинаются с столбца B - в противном случае ссылка на R1C1 будет слегка смещена).