Я пытаюсь справочные данные VLOOKUP
с 400 отдельными файлами Excel.
Возможно ли сделать это быстро, а не вручную?
Я пытаюсь справочные данные VLOOKUP
с 400 отдельными файлами Excel.
Возможно ли сделать это быстро, а не вручную?
Вот пример решения. Будет несколько трудно поддерживать, если ссылочные файлы часто меняются, но это лучше, чем ничего.
Предположим, у нас есть 3 файла:1.xslx
, 2.xslx
и 3.xslx
, и у каждого из них есть таблица Index
и Value
в столбцах A
и B
, например:
Index Value
1 a
2 b
3 c
Создайте новую книгу index.xlsx
, чтобы она выглядела примерно так:
A B ... E F
1 index: 1.xlsx
2 value: 2.xlsx
3 3.xlsx
Там вам нужно будет подготовить список всех ваших имен файлов (скажем, столбец E).
Добавьте эту формулу в ячейки F1:F3
(выберите диапазон, введите формулу и введите Ctrl+Shift+Enter):
=VLOOKUP($B$1, INDIRECT("["&$E$1:$E$3&"]Sheet1"&"!$A$2:$B$5"), 2, FALSE)
заменив 3
на количество файлов (то есть количество записей в столбце E
) и 5
на наибольшее число строк в любом из ваших файлов. Если вы решите не использовать заголовки в строке 1 ваших справочных файлов, измените $A$2
на $A$1
. Второе 2
относится к тому факту, что мы извлекаем данные из столбца B
справочных файлов.
В ячейку B2
введите формулу:
=INDEX(F1:F3, MATCH(TRUE,ISTEXT(F1:F3),0), 0)
(снова используя Ctrl+Shift+Enter). Теперь, введя некоторый индекс в B1
, вы получите его значение из всех файлов, представленных в B2
.
Примечание: это предполагает, что значения являются текстовыми (а не пустыми), хотя это не предполагает, что индексы являются числовыми. Если индексы не уникальны, вы получите первое совпадение, выполнив поиск файлов в том порядке, в котором они перечислены в столбце E
При необходимости его можно изменить для разных диапазонов в каждом файле или на разных листах, но это идея.
(Вот пример решения, представленного выше (называемого index.xlsx
выше), для справки.)