Выпадающий список в ячейке с удалением использованных элементов
Категория: Выпадающие списки, просмотров: 31057, опубликовано: 31.03.2007 Скачать пример |
Я знаю, что делать,
но не знаю куда
девать тела...
Постановка задачи
Рассмотрим для определенности следующий пример. Имеем недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено:
Идеальным вариантом было бы организовать в ячейках 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), выберем в списке допустимых значений вариант Список и укажем Источник данных:
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
Ссылки по теме
Создание выпадающего списка в ячейках листа
Создание зависимых выпадающих списков
Автоматическое создание выпадающих списков при помощи инструментов надстройки PLEX
Выбор фото из выпадающего списка
Комментарии:
| Олеся 06.06.2008 |
Довольно громоздкие формулы. На мой взгляд проще вместо функции СТРОКА сразу использовать ссылку на ячейку с номером сотрудника: Шаг 2 - =ЕСЛИ(F2-G2СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;D2);1)) А идея хорошая :). |
| Денис 04.02.2009 |
А как сделать чтобы вообще не использовать оставшиеся смены. Т.е. убрать их вовсе и оставить только выбран или не выбран?. |
| Алекс 09.08.2009 |
Формула в столбце H не работает, если список начинается не с первой строки листа. Надо изменить на =ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА($E$2)+1) За идею спасибо!. |
| Алекс 09.08.2009 |
to Олеся НАИМЕНЬШИЙ($H$2:$H$10;D2); У автора поста ячейка D2 не используется в принципе. Если её использовать, то, вы правы, формулы в H и I станут более читаемыми, т.к. не придется каждый раз рассчитывать номер строки от начала списка.. |
| Михаил 09.09.2009 |
У автора статьи не используются значения в ячейках D2:D10, их можно полностью исключить либо задать произвольные номера, при этом все будет работать. Как указал Алекс если в ячейки H заменить формулу на =ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА(E$2)+1) то пример будет универсальным и его можно размещать в любом месте листа (не только в 1 строке). |
| Олег 19.09.2009 |
Всё это хорошо, но только при условии не большого количества определяемых значений. А вот если их хотя бы будет 101, то что же будет с нашей присловутой рабочей таблицей, которую к стати 99% пользователей не нужно видеть, а ингода необходимо что бы не видели (в том числе и человеческий фактор)?!. |
| Rafael 09.11.2009 |
На втором шаеге вот эту часть СТРОКА(E2)-СТРОКА(E$2)+1 можно заменить так СТРОКА(E2)-1. |
| Rafael 09.11.2009 |
Короче переписал второй шаг так =ЕСЛИ(D2>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1))). |
| Луиза 16.02.2010 |
не в тему конечно, но подскажите, можно ли сделать так, чтобы в списке при наборе повторные имена и фамилии высвечивались. |