Выпадающий список с удалением использованных элементов
Я знаю, что делать, но не знаю
куда потом девать тела...
Постановка задачи
Имеем в качестве примера недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено. Идеальным вариантом было бы организовать в ячейках 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. Создаем именованный диапазон свободных сотрудников
- в Excel 2003 и старше идем в меню Вставка - Имя - Присвоить (Insert - Name - Define)
- в Excel 2007 и новее - жмем кнопку Диспетчер Имен (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 нашего графика и добавить в них выпадающий список с элементами диапазона Имена. Для этого
- в Excel 2003 и старше - откроем меню Данные - Проверка (Data - Validation),
- в Excel 2007 и новее - жмем кнопку Проверка данных (Data Validation) на вкладке Данные (Data)
В открывшемся окне выберем в списке допустимых значений вариант Список (List) и укажем Источник (Source) данных:
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))
в форуме я нашел что-то похожее: не могу осилить формулу массива:
=ИНДЕКС(A$2:A$11;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$11<>"";СТРОКА($B$2:$B$11)-1);СТРОКА(A2)))
А вообще, огромное спасибо автору. Подобные вещи в Excel, IMHO, пример "высшего пилотажа".
При создании имени можно вместо
=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))
написать так
=СМЕЩ(Лист1!$I$2,0,0,СЧЁТЕСЛИ(Лист1!$I$2:$I$11,"<>""""))
будет короче
Необходимо провести тестирование персонала.
Тест одинаковый для всех.
Выпадающий список с удалением использованных данных необходим для того, чтоб тестируемые не могли пройти тест больше чем запланировано раз. Так например, если блок-тест легкий- то необходимо его пройти с первой попытки, а если сложный, то есть 2 возможности улучшить результат. А так как тест один для всех и есть необходимость выпадающего списка в одной ячейке ( Данная ячейка находиться на листе тестирования).При этом результаты пройденного тестирования необходимо заносить в таблицу результатов.
=ЕСЛИ(F2>G2;D2;"")
=ЕСЛИОШИБКА(ВПР(НАИМЕНЬШИЙ(H$2:H$10;D2);D$2:E$10;2;ЛОЖЬ);"")
=ЕСЛИОШИБКА(ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;D2));"")
Но у меня получается какой то непонятный трабл(
В выпадающем списке отображаются пустые ячейки хотя напротив игнорировать галочка стоит. Я сделал все как в примере, разве что ячейки другие. Ну а в примере все норм.
Где может быть косяк?(
15.08.2013 16:01:31
Причём, ссылка - абсолютная, т.е. ;СТРОКА(E2)-СТРОКА($E$1)))), чтобы автозаполнение при протягивании формулы вниз выполнялось корректно. Автору огромное спасибо!
P.S. Отличный сайт и канал на YouTube!
переделала в свой
="СМЕЩ('Скрытый лист'!$F$23;0;0;СЧЁТЗ('Скрытый лист'!$F$23:$F$25)-СЧИТАТЬПУСТОТЫ('Скрытый лист'!$F$23:F$25)) "
где F столб не использованных значений
а выпадающие списки для выбора находятся на другой странице
нужно ли что-то добавлять?
Коллеги, подскажите как решить на базе этого примера следующую задачу:
на примере дежурных предположим что каждый из списка может быть ответственным за назначение дежурного на неделю, т.е. заходя в таблицу он выбирает себя как ответственного, следовательно в списках дежурных он должен пропасть, чтобы не иметь возможности самого себя назначить дежурным, а остальных назначать как и в примере с удалением уже выбранных. И это должно работать дальше для других пользователей, чтобы они, выбирая себя в качестве ответственного, не могли назначить себя же дежурным, а остальных могли.