Создать простой выпадающий список в ячейке с данными из этого же файла — просто. Все усложняется, когда сам список и данные для него находятся в разных файлах. Для решения этой проблемы есть несколько способов.
Способ 1. Функция ДВССЫЛ
В простом случае можно использовать функцию ДВССЫЛ (INDIRECT), чтобы сформировать правильную ссылку на внешний файл. Например, если необходимо создать выпадающий список с содержимым ячеек А1:А10 с листа Список из файла Товары.xls, нужно открыть окно проверки данных через вкладку Данные — Проверка данных (Data — Validation) и в поле Источник (Source) ввести следующую конструкцию: =ДВССЫЛ("[Товары.xls]Список!$A$1:$A$10")
.
Функция ДВССЫЛ (INDIRECT) преобразует текстовую строку аргумента в реальный адрес, используемый для ссылки на данные. Обратите внимание, что имя файла заключается в квадратные скобки, а восклицательный знак служит разделителем имени листа и адреса диапазона ячеек. Если имя файла содержит пробелы, то его надо заключить в апострофы.
Если файл с исходными данными для списка лежит в другой папке, необходимо указать полный путь к файлу, например, следующим образом: =ДВССЫЛ("'C:\Поставщики\[Товары.xls]Список'!$A$1:$A$10")
. В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа. Минус этого способа только один — выпадающий список будет корректно работать только в том случае, если файл Товары.xls открыт.
Способ 2. Импорт данных
Суть этого способа в том, что данные для выпадающего списка буду импортироваться из другого файла в текущий. Для этого необходимо создать обновляемую связь между двумя файлами.
Сначала откройте файл-источник, где находятся эталонные значения для выпадающего списка (назовем его, допустим, Справочник.xlsx). Выделите диапазон с данными для списка и отформатируйте его как таблицу с помощью кнопки Форматировать как таблицу на вкладке Главная (Home — Format as Table). Обратите внимание, что у такой таблицы предварительно должна быть сделана «шапка» — строка заголовка. После этого файл Справочник можно сохранить и закрыть.
Теперь откроем книгу, где мы хотим создать выпадающий список (условно назовем ее Бланк.xlsx). Вставим чистый лист (Alt+F11), выберем на вкладке Данные — Существующие подключения — Найти другие (Data — Existing Connections — Browse for more) и укажем наш файл Справочник.xlsx. Появится диалоговое окно, в котором Excel спросит нас о том, какую именно таблицу мы хотим импортировать (если их в файле было несколько).
После нажатия на ОК появится еще одно последнее окно, где можно указать удобную ячейку для импорта и, нажав на кнопку Свойства (Properties), задать частоту обновления информации.
Тут можно включить флажок Обновить при открытии файла (Refresh on open), чтобы каждый раз при открытии этой книги иметь последнюю версию списка.
После нажатия на кнопку ОК, Excel загрузит данные из созданной таблицы из файла Справочник в наш текущий файл Бланк и отформатирует их в виде таблицы.
Если выделить импортированный список (диапазон А2:А7 в нашем случае), то в строке формул можно увидеть его имя, которое он автоматически получает при вставке.
Это имя также можно увидеть в Диспетчере имен на вкладке Формулы (Formulas — Name Manager).
Осталось создать выпадающий список, который будет ссылаться на эти данные. Для этого:
- Выделяем ячейки, где хотим создать выпадающие списки.
- На вкладке Данные жмем на кнопку Проверка данных (Data — Validation).
- Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу:
=ДВССЫЛ("Таблица_Справочник")
. В англоязычной версии Excel это будет=INDIRECT("Таблица_Справочник")
.
Логично было бы ввести просто имя нашего диапазона, но, к сожалению, Microsoft Excel почему-то не воспринимает имена таблиц в поле Источник. Поэтому мы используем тактическую хитрость — функцию ДВССЫЛ (INDIRECT), которая превращает свой аргумент (имя нашей таблицы) в рабочую ссылку.
Все. После нажатия на ОК наш список начнет работать и будет в дальнейшем автоматически обновляться, подгружая данные из другого файла.