Создать список в ячейке Excel
Постановка задачи
Рассмотрим для определенности следующий пример. Имеем недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено:
Идеальным вариантом было бы организовать в ячейках B2:B8 выпадающий список, но при этом сделать так, чтобы уже занятые сотрудники автоматически убирались из выпадающего списка, оставляя только свободных.
Шаг 1. Кто сколько работает?
Сначала давайте подсчитаем кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу:
=СЧЁТЕСЛИ($B$2:$B$8;E2)
Шаг 2. Кто еще свободен?
Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников:
=ЕСЛИ(F2-G2<=0;»»;СТРОКА(E2)-1)
Шаг 3. Формируем список
Теперь надо сформировать непрерывный (без пустых ячеек) список свободных сотрудников для связи — на следующем шаге — с выпадающим списком. Для этого добавим еще один столбец и введем в него такую страшноватую на первый взгляд формулу:
=ЕСЛИ(СТРОКА(E2)-СТРОКА(E$2)+1>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;1+СТРОКА(E2)-СТРОКА(E$2));1))
При всей внешней жуткости вида, эта формула делает одну простую вещь — выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.
Шаг 4. Создаем именованный диапазон свободных сотрудников
Теперь идем в меню Вставка — Имя — Присвоить (Insert — Name — Define) и создаем новый именованный диапазон Имена по следующей формуле:
=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))
Фактически, мы просто даем диапазону занятых ячеек в синем столбце собственное название Имена.
Шаг 5. Создаем выпадающий список в ячейках
Осталось выделить ячейки B2:B8 нашего графика и добавить в них выпадающий список с элементами диапазона Имена. Для этого откроем меню Данные — Проверка (Data — Validation), выберем в списке допустимых значений вариант Список и укажем Источник данных:
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
Добрый день. Подскажите, пожалуйста, решение проблемы. Воспользовался формулой «=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))» относительно своего диапазона. Но в случае если в исходной таблице диапазон начинается с пустой ячейки, то в выпадающий список попадает только первые 2 строки и далее список уникальных значений не подтягивается. Если же первая ячейка содержит значение, а затем, к примеру, 2 пустых строки, то подтягивается только 3 строки (первая со значением и 2 пустых). Помогите, пожалуйста, с проблемой. Заранее спасибо.
Моя формула в списке =СМЕЩ($M$33;0;0;СЧЁТЗ($M$33:$M$42)-СЧИТАТЬПУСТОТЫ($M$33:$M$42))
Моя формула исходных данных для списка =ЕСЛИ(СТРОКА(L33)-СТРОКА(L$33)+1>СЧЁТ($K$33:$K$42);»»»»;ИНДЕКС($L$33:$L$42;НАИМЕНЬШИЙ($K$33:$K$42;1+СТРОКА(L33)-СТРОКА(L$33));1))