EXCEL: фишки с выпадающим списком

Вы знали, что в Excel можно задать выпадающий список значений для любой ячейки? Например, для определенного столбца нужно в ячейке выбирать только позиции да/нет. Или постоянно указывать одно значение из ограниченного перечня.

Выбираем на вкладке Данные – Проверка данных – пункт Проверка данных – тип данных: Список.

В строке Источник указываем диапазон. Почему-то по умолчанию Excel дает возможность выбрать только диапазон на том же листе, что и ячейка со списком. Но если нужно указать другой лист, можно просто ручками написать точный адрес. На скриншоте ссылка на Лист 2.

На втором листе, соответственно сам диапазон значений:

Результат:

Также без проблем можно сослаться на диапазон в другом файле. Удобно, если один и тот же перечень используется в различных отчетах. Формула в источнике будет:

=ДВССЫЛ(«[Таблица.xls]Список!$A$1:$A$2»)

Функция ДВССЫЛ (или в англ. варианте INDIRECT) и источник — файл Таблица (где, допустим, у нас хранится нужный диапазон данных). Можно использовать один и тот же файл постоянно. Но если таблица лежит не в той же папке, что и документ со списком, надо кроме названия файла указать и полный путь к нему, типа: =ДВССЫЛ(«[C:\Docs\Таблица.xls и т.д.

Динамический список

Еще хорошей идеей будет сделать диапазон возможных данных динамическим. Не будешь же постоянно править количество ячеек?

Пусть наш список: столбец ФИО и перечень фамилий.

• Вариант первый, просто указываем в качестве диапазона столбец целиком, т.е. пишем в источнике:

=Лист2!$A:$A

Единственный минус, если у колонки есть название, то оно тоже будет в списке

• Вариант второй, превращаем список в таблицу: Главная – Стили – Форматировать как таблицу (оформление роли не играет). Первая строка (ФИО) становится названием таблицы.

В ячейке с выпадающим списком пишем:

=ДВССЫЛ(«Таблица1[ФИО]»)

Если потом мы добавим или уберем какие-то имена из таблицы, соответственно изменятся и данные в выпадающем списке: