Я пытаюсь справочные данные VLOOKUP с 400 отдельными файлами Excel.
Возможно ли сделать это быстро, а не вручную?
3
1 ответ
2
Вот пример решения. Будет несколько трудно поддерживать, если ссылочные файлы часто меняются, но это лучше, чем ничего.
Предположим, у нас есть 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 выше), для справки.)