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

132608 02.10.2012 Скачать пример

Я знаю, что делать, но не знаю
куда потом девать тела...

Постановка задачи

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

remove-used-dropdown1.gif

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

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

Сначала давайте подсчитаем кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу:

=СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной версии =COUNTIF($B$2:$B$8;E2)

remove-used-dropdown2.png

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

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

Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников:

=ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА($E$2)+1) или в англоязычной версии =IF(F2-G2<=0;"";ROW(E2)-ROW($E$2)+1)

remove-used-dropdown3.png

Шаг 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)))

remove-used-dropdown4.png

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

Шаг 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))

remove-used-dropdown5.png

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

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

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

  • в Excel 2003 и старше - откроем меню Данные - Проверка (Data - Validation),
  • в Excel 2007 и новее - жмем кнопку Проверка данных (Data Validation) на вкладке Данные (Data)

В открывшемся окне выберем в списке допустимых значений вариант Список (List) и укажем Источник (Source) данных:

remove-used-dropdown6.png

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

Ссылки по теме


13.02.2013 15:37:47
Пожалуйста объясните поподробнее как работает эта формула:

=ЕСЛИ(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)))
22.03.2013 19:43:13
В шаге 3 конец формулы ...;СТРОКА(E2)-1))) , на мой взгляд, лучше было бы представить ;СТРОКА(E2)-СТРОКА(E1)))). Где E1 - ссылка на заголовок строки с именами сотрудников. Мало ли кому вздумается сдвинуть вниз всю конструкцию, а найти возникшую ошибку без детального понимания происходящего будет весьма сложно.
А вообще, огромное спасибо автору. Подобные вещи в Excel, IMHO, пример "высшего пилотажа".
16.04.2013 00:37:13
Согласен, так нагляднее. Спасибо! :)
15.08.2013 16:01:31
Причём, ссылка - абсолютная, т.е. ;СТРОКА(E2)-СТРОКА($E$1)))), чтобы автозаполнение при протягивании формулы вниз выполнялось корректно. Автору огромное спасибо!
26.01.2014 23:13:07
Подскажите, как сделать выпадающий список с удалением использованных элементов,если выпадающий список должен быть только в одной ячейке?
27.01.2014 16:46:01
Это как? А зачем он тогда вообще? Весь смысл в том, чтобы в других ячейках со списком использованные элементы убирались.
27.01.2014 15:18:35
Круто! Высший пилотаж.
При создании имени можно вместо
=СМЕЩ(Лист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,"<>""""))
будет короче :)
03.02.2014 14:06:54
Возможно есть другой вариант решения, но вот пример в котором хотел это применить:
Необходимо провести тестирование персонала.
Тест одинаковый для всех.
Выпадающий список с удалением использованных данных необходим для того, чтоб  тестируемые не могли пройти тест больше чем запланировано раз. Так например, если блок-тест легкий- то необходимо его пройти с первой попытки, а если сложный, то есть 2 возможности улучшить результат. А так как тест один для всех  и есть необходимость выпадающего списка  в одной ячейке ( Данная ячейка находиться на листе тестирования).При этом результаты пройденного тестирования необходимо заносить в таблицу результатов.
04.06.2014 16:27:12
Шаг 2.

=ЕСЛИ(F2>G2;D2;"")
04.06.2014 16:31:32
Шаг 3.
=ЕСЛИОШИБКА(ВПР(НАИМЕНЬШИЙ(H$2:H$10;D2);D$2:E$10;2;ЛОЖЬ);"")
10.10.2015 01:06:06
Присоединюсь к стройным рядам людей благодарным автору за такое творение)
Но у меня получается какой то непонятный трабл(
В выпадающем списке отображаются пустые ячейки хотя напротив игнорировать галочка стоит. Я сделал все как в примере, разве что ячейки другие. Ну а в примере все норм.
Где может быть косяк?(
10.10.2015 22:41:22
Сам морожу сам катаюсь)
Илья Искрин
15.08.2013 16:01:31
Причём, ссылка - абсолютная, т.е. ;СТРОКА(E2)-СТРОКА($E$1)))), чтобы автозаполнение при протягивании формулы вниз выполнялось корректно. Автору огромное спасибо!
08.11.2015 05:19:49
извиняюсь, понял.
07.11.2015 17:02:46
Прошу автора урока подсобить по вопросу  :!::oops:
03.03.2016 19:38:00
Добрый день.а можно ли сделать так, чтобы в выпадающем списке можно было выбирать в порядке номеров т.е. если в понедельник дежурит Мария то назначить Александра на вторник уже нельзя и так далее

P.S. Отличный сайт и канал на YouTube!
10.06.2016 08:23:14
Добрый день, в данном примере представлена одна неделя, при прочих равных график обычно делаю на месяц, то есть несколько недель,  а как сделать что бы было не сколько недель ? не как у меня что то не получается
10.06.2016 08:57:23
получилось!
03.08.2016 06:30:20
Добрый день. Подскажите пожалуйста,  как упростить формулу. Мне просто необходимо, чтобы при выборе в выпадающем списке удалялось значение из списка имена без подсчёта смен и прочего.
=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))
переделала в свой
="СМЕЩ('Скрытый лист'!$F$23;0;0;СЧЁТЗ('Скрытый лист'!$F$23:$F$25)-СЧИТАТЬПУСТОТЫ('Скрытый лист'!$F$23:F$25)) "

где F столб не использованных значений

а выпадающие списки для выбора находятся на другой странице
нужно ли что-то добавлять?
18.11.2016 19:48:43
Спасибо за статью. Очень полезно и наглядно.


Коллеги, подскажите как решить на базе этого примера следующую задачу:
на примере дежурных предположим что каждый из списка может быть ответственным за назначение дежурного на неделю, т.е. заходя в таблицу он выбирает себя как ответственного, следовательно в списках дежурных он должен пропасть, чтобы не иметь возможности самого себя назначить дежурным, а остальных назначать как и в примере с удалением уже выбранных. И это должно работать дальше для других пользователей, чтобы они, выбирая себя в качестве ответственного, не могли назначить себя же дежурным, а остальных могли.
11.12.2016 10:57:46
Подскажите, а можно сделать так, чтобы можно было выбирать ФИО не только из выпадающего списка, но и при вводе ФИО в ячейках и он как в комбоксе предлагал подходящую из списка? Когда ФИО много листа замучаешься..
16.12.2016 00:58:06
а в чиом может быть проблема что дает ошибку #число