Сводные таблицы Excel

Как в Excel сделать выпадающий список с удалением использованных частей

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

Недельный график дежурств, который нужно заполнить именами служащих

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

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

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

Подсчитаем, кто из наших служащих уже назначен

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

Шаг 2. Кто еще волен?

Сейчас выясним, кто из наших служащих еще волен, т.е. не исчерпал припас допустимых смен. Добавим очередной столбец и введем в него формулу, которая будет выводить номера вольных служащих: =ЕСЛИ(F2-G2 либо в английской версии =IF(F2-G2 .

Выясним, кто из наших служащих еще волен

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

Сейчас нужно сформировать непрерывный (без пустых ячеек) список вольных служащих для связи – на последующем шаге – с выпадающим перечнем. Для этого добавим очередной столбец и введем в него такую страшноватую на 1-ый взор формулу: =ЕСЛИ(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))) .

Формируем список вольных служащих

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

Интересно почитать: Как в excel обрезать лист

Шаг 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) данных.

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

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