В прошлых статьях я рассказал, как сделать выпадающий список в Excel: первая, вторая и видео:
Вернемся к примеру из прошлых статей - мы готовим таблицу для отдела кадров, в которой перечислены все сотрудники, должности и отделы, и хотим, чтобы таблицу было заполнять легко, в то же время, чтобы избежать зоопарка различных названий, типа: "дир.", "директор", "дир-р", "деректор" и тому подобного - всегда удобно работать с чистыми данными.
Все достаточно просто - выбираем диапазон, где хотим сделать выпадющий список и жмем кнопку Проверка данных, указываем список значений - и готово.
Все бы хорошо, но каждый раз, когда нужно будет добавить новые должности, отделы, или что-то поменять - нужно будет редактировать настройки проверки данных, и не всегда отдел кадров сможет это сделать быстро и без ошибок.
Можно ли как-то автоматизировать добавление новых отделов не используя макросы? Да! Но сначала нужна небольшая подготовка. Воспользуемся методом, описанным в статье "Только при помощи формул: список уникальных значений и сортировка", и скопируем на лист "Списки" уникальные значения из колонки "Отделы". Чтобы узнать, как это сделать, пройдите по ссылке выше. Там же вы найдете готовую таблицу Excel для примера.
Мы ранее протягивали формулу массива на несколько десятков строк, чтобы наверняка хватило: с ячейки B3 до ячейки B33 (конечно, можно сделать и меньше, и больше). Этот диапазон можно указать в настройках проверки данных, но тогда в выпадающем списке будет достаточно много пустых строк. Неаккуратненько...
Чтобы у нашего файлы был профессиональный и законченный вид, попробуем сосчитать сколько ячеек в уникальном списке заполнены. Введем в ячейку B1 на листе "Списки" формулу:
=COUNTA($B$3:$B$33)-COUNTBLANK(B3:B33)
COUNTA (СЧЁТЗ) укажет количество ячеек в указаном диапазоне, COUNTBLANK (СЧИТАТЬПУСТОТЫ) укажет количество путых ячеек.
Таким образом мы узнали, сколько уникальных значений в списке. Теперь можно немного модифицировать формулу:
="Списки!$B$3:$B$"&TEXT(2+COUNTA($B$3:$B$33)-COUNTBLANK(B3:B33);"#")
Функция TEXT (ТЕКСТ) преображает числовое значение в текстовый вид и позволяет сцепить его с текстом. И еще немножко доделаем формулу, добавив проверку случая, если наш список пуст.
=IF($B$3="";"Списки!$D$3";"Списки!$D$3:$D$"&TEXT(2+COUNTA($B$3:$B$33)-COUNTBLANK(B3:B33);"#"))
Таким образом, у нас в ячейке B1 получился в текстовом виде указатель на диапазон, в котором размещен список уникальных значений:
Осталось теперь с помощью функции INDIRECT (ДВССЫЛ), которая преобразует текстовую ссылку в ссылку на диапазон, настроить проверку данных.
Проверим, также, чтобы нам не выдавались ошибки при вводе значения не из списка (если хотите, оставьте предупреждение или информационное сообщение):
Окей, готово. Как добавлять новые отделы? Просто ввести название отдела, не выбирая из списка, и оно автоматически добавится в список. Как переименовать? Также, просто вводим название нового отдела.
На этом все. Буду рад ответить на ваши вопросы, пишите в комментарии. Расскажите о ваших интересных кейсах!