Имеем в качестве примера недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено. Идеальным вариантом было бы организовать в ячейках B2:B8 выпадающий список, но при этом сделать так, чтобы уже занятые сотрудники автоматически убирались из выпадающего списка, оставляя только свободных.

Чтобы реализовать подобный вариант выпадающего списка, выполним несколько простых шагов.

Шаг 1. Кто сколько работает?

Сначала давайте подсчитаем, кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу: =СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной версии =COUNTIF($B$2:$B$8;E2).

Фактически формула просто вычисляет, сколько раз имя сотрудника встречалось в диапазоне с именами.

Шаг 2. Кто еще свободен?

Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников: =ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА($E$2)+1) или в англоязычной версии =IF(F2-G2<=0;"";ROW(E2)-ROW($E$2)+1).

Шаг 3. Формируем список

Теперь надо сформировать непрерывный (без пустых ячеек) список свободных сотрудников для связи – на следующем шаге – с выпадающим списком. Для этого добавим еще один столбец и введем в него такую страшноватую на первый взгляд формулу: =ЕСЛИ(D2>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1))) или соответственно, =IF(D2>COUNT($H$2:$H$10);"";INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1))).

При всей внешней жуткости вида эта формула делает одну простую вещь – выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

Шаг 4. Создаем именованный диапазон свободных сотрудников

Жмем кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas) и создаем новый именованный диапазон Имена по следующей формуле: =СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10)) или в англоязычной версии: =OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-COUNTBLANK(Лист1!I$2:I$10)).

Фактически мы просто даем диапазону занятых ячеек в синем столбце собственное название Имена.

Шаг 5. Создаем выпадающий список в ячейках

Осталось выделить ячейки B2:B8 нашего графика и добавить в них выпадающий список с элементами диапазона Имена. Для этого жмем кнопку Проверка данных (Data Validation) на вкладке Данные (Data) и в открывшемся окне выберем в списке допустимых значений вариант Список (List) и укажем Источник (Source) данных.

Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.