Выпадающий список с удалением использованных элементов в эксель
Добрый день!
Подскажите пожалуйста как быть. Есть таблица, где находится база данных. Есть другая таблица, куда переносятся данные из первой таблицы. Перенос осуществляется по средствам выпадающего списка по коду. НО необходимо иметь список только уникальных (еще не выбранных значений).. Т.е. если данные находятся под кодом АА11 и этот код уже выбран во второй таблице, то при следующем желании выбрать код, код (АА11) уже не должен выпадать ибо тогда данные с этим кодом будут задвоены. Подскажите пожалуйста как быть.Спасибо
Добрый день!
Подскажите пожалуйста как быть. Есть таблица, где находится база данных. Есть другая таблица, куда переносятся данные из первой таблицы. Перенос осуществляется по средствам выпадающего списка по коду. НО необходимо иметь список только уникальных (еще не выбранных значений).. Т.е. если данные находятся под кодом АА11 и этот код уже выбран во второй таблице, то при следующем желании выбрать код, код (АА11) уже не должен выпадать ибо тогда данные с этим кодом будут задвоены. Подскажите пожалуйста как быть.Спасибо alex808
Так нужно?
Там отдельный столбец для списка и динамический именованный диапазон для вып. списка
==========
Кстати да, Андрей как всегда супервнимателен
Так нужно?
Там отдельный столбец для списка и динамический именованный диапазон для вып. списка
==========
Кстати да, Андрей как всегда супервнимателен
Ещё вариант: вообще не изменять список, а использовать Условное форматирование для проверки на неуникальность. Если вы введете повторяющееся значение - то ячейки с повторами подсветятся.
P.S. В формуле с ВПР() диапазон исходной таблицы надо задавать абсолютными ссылками, а то адреса у вас "поплывут" при протягивании формулы
Ещё вариант: вообще не изменять список, а использовать Условное форматирование для проверки на неуникальность. Если вы введете повторяющееся значение - то ячейки с повторами подсветятся.
P.S. В формуле с ВПР() диапазон исходной таблицы надо задавать абсолютными ссылками, а то адреса у вас "поплывут" при протягивании формулы AndreTM
P.S. В формуле с ВПР() диапазон исходной таблицы надо задавать абсолютными ссылками, а то адреса у вас "поплывут" при протягивании формулы Автор - AndreTM
Дата добавления - 08.06.2017 в 21:16
Спасибо большое Вам! Условное форматирование имеет значительный минус.. Допустим из 1000 строк уже выбраны 950, с вероятностью 95% мне будет светить красный цвет, а это дополнительный поиск..Путь Бороды на мой взгляд более интересен, поскольку не предполагает дополнительного поиска в случае осечки. Правда возник вопрос в продолжении. Если таблица №1 постоянно увеличивается, и если ее отметить как таблицу для последующего создания сводных таблиц, наверное уникальный список я должен внедрить непосредственно в таблицу №1 дабы она расширялась соразмерно ?
Спасибо большое Вам! Условное форматирование имеет значительный минус.. Допустим из 1000 строк уже выбраны 950, с вероятностью 95% мне будет светить красный цвет, а это дополнительный поиск..Путь Бороды на мой взгляд более интересен, поскольку не предполагает дополнительного поиска в случае осечки. Правда возник вопрос в продолжении. Если таблица №1 постоянно увеличивается, и если ее отметить как таблицу для последующего создания сводных таблиц, наверное уникальный список я должен внедрить непосредственно в таблицу №1 дабы она расширялась соразмерно ? alex808
Подразумевалось: "Нет, не надо ничего больше делать, если Именованный диапазон "Код" задать/задан динамическим".
Подразумевалось: "Нет, не надо ничего больше делать, если Именованный диапазон "Код" задать/задан динамическим".
Skype: andre.tm.007
Donate: Q iwi: 9517375010
Автор - AndreTM
Дата добавления - 08.06.2017 в 21:50
"Код" у меня и так динамический (я его перекинул со столбца А на новый уменьшающийся столбец). Он для выпадающего списка. Добавил еще 2 динамических диапазона - "Дано" и "Есть"
"Код" у меня и так динамический (я его перекинул со столбца А на новый уменьшающийся столбец). Он для выпадающего списка. Добавил еще 2 динамических диапазона - "Дано" и "Есть" _Boroda_
Имеем в качестве примера недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено. Идеальным вариантом было бы организовать в ячейках B2:B8 выпадающий список, но при этом сделать так, чтобы уже занятые сотрудники автоматически убирались из выпадающего списка, оставляя только свободных:
Чтобы реализовать подобный вариант выпадающего списка выполним несколько простых шагов.
Шаг 1. Кто сколько работает?
Сначала давайте подсчитаем кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу:
=СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной версии =COUNTIF($B$2:$B$8;E2)
Фактически, формула просто вычисляет сколько раз имя сотрудника встречалось в диапазоне с именами.
Шаг 2. Кто еще свободен?
Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников:
Шаг 3. Формируем список
Теперь надо сформировать непрерывный (без пустых ячеек) список свободных сотрудников для связи - на следующем шаге - с выпадающим списком. Для этого добавим еще один столбец и введем в него такую страшноватую на первый взгляд формулу:
При всей внешней жуткости вида, эта формула делает одну простую вещь - выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.
Шаг 4. Создаем именованный диапазон свободных сотрудников
- в Excel 2003 и старше идем в меню Вставка - Имя - Присвоить (Insert - Name - Define)
- в Excel 2007 и новее - жмем кнопку Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas)
и создаем новый именованный диапазон Имена по следующей формуле:
в англоязычной версии =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) данных:
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
Имеем в качестве примера недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено. Идеальным вариантом было бы организовать в ячейках B2:B8 выпадающий список, но при этом сделать так, чтобы уже занятые сотрудники автоматически убирались из выпадающего списка, оставляя только свободных:
Чтобы реализовать подобный вариант выпадающего списка выполним несколько простых шагов.
Шаг 1. Кто сколько работает?
Сначала давайте подсчитаем кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу:
=СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной версии =COUNTIF($B$2:$B$8;E2)
Фактически, формула просто вычисляет сколько раз имя сотрудника встречалось в диапазоне с именами.
Шаг 2. Кто еще свободен?
Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников:
Шаг 3. Формируем список
Теперь надо сформировать непрерывный (без пустых ячеек) список свободных сотрудников для связи - на следующем шаге - с выпадающим списком. Для этого добавим еще один столбец и введем в него такую страшноватую на первый взгляд формулу:
При всей внешней жуткости вида, эта формула делает одну простую вещь - выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.
Шаг 4. Создаем именованный диапазон свободных сотрудников
- в Excel 2003 и старше идем в меню Вставка - Имя - Присвоить (Insert - Name - Define)
- в Excel 2007 и новее - жмем кнопку Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas)
и создаем новый именованный диапазон Имена по следующей формуле:
в англоязычной версии =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) данных:
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
Имеем в качестве примера недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено. Идеальным вариантом было бы организовать в ячейках B2:B8 выпадающий список, но при этом сделать так, чтобы уже занятые сотрудники автоматически убирались из выпадающего списка, оставляя только свободных.
Недельный график дежурств, который надо заполнить именами сотрудников
Чтобы реализовать подобный вариант выпадающего списка, выполним несколько простых шагов.
Шаг 1. Кто сколько работает?
Сначала давайте подсчитаем, кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу: =СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной версии =COUNTIF($B$2:$B$8;E2) .
Подсчитаем, кто из наших сотрудников уже назначен
Фактически формула просто вычисляет, сколько раз имя сотрудника встречалось в диапазоне с именами.
Шаг 2. Кто еще свободен?
Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников: =ЕСЛИ(F2-G2 или в англоязычной версии =IF(F2-G2 .
Выясним, кто из наших сотрудников еще свободен
Шаг 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) данных.
Создаем выпадающий список в ячейках
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
Задача: сделать в ячейке D2 выпадающий список, чтобы пользователь мог выбирать имена из списка (столбец А). Если нужного имени нет в списке, то пользователь может ввести новое имя прямо в ячейку D2 – оно автоматически добавится к столбцу А и начнет отображаться в выпадающем списке в будущем.
Выпадающий список с автоматическим добавлением отсутствующих элементов
Шаг 1. Создаем именованный диапазон
Сначала создадим именованный диапазон, указывающий на заполненные именами ячейки в столбце А – сколько бы имен в списке ни находилось. Для этого жмем на вкладке Формулы (Formulas) кнопку Диспетчер имен (Name Manager) и затем Создать (New).
Вводим имя диапазона (допустим, People) и в строку Ссылка (Reference) вводим следующую формулу: =СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A$1:$A$24);1) . в английской версии Excel это будет: =OFFSET(Лист1!$A$1;0;0;COUNTA(Лист1!$A$1:$A$24);1) . Эта формула ссылается на все заполненные ячейки в столбце А, начиная с А1 и вниз до конца – до последнего имени.
Шаг 2. Создаем выпадающий список в ячейке
Выделяем ячейку D2 и жмем на вкладке Данные (Data) кнопку Проверка данных (Data Validation). Далее выбираем из выпадающего списка Тип данных (Allow) позицию Список (List) и вводим в строку Источник (Source) ссылку на созданный на шаге 1 именованный диапазон (не забудьте перед именем диапазона поставить знак равенства!):
Не забудьте перед именем диапазона поставить знак равенства!
Шаг 3. Добавляем простой макрос
Щелкаем правой кнопкой мыши по ярлычку нашего листа и выбираем Исходный текст (View Source). Откроется модуль листа в редакторе Visual Basic, куда надо ввести такой код:
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$D$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("People"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("People").Cells(Range("People").Rows.Count + 1, 1) = Target End If End If End If End Sub
Если ваш выпадающий список находится не в ячейке D2 или вы назвали диапазон с именами не People, а как-то еще, то подправьте эти параметры в макросе на свои. Всё! Теперь при попытке ввести новое имя в ячейку D2 Excel будет спрашивать и при утвердительном ответе пользователя автоматически добавлять новое имя к списку в столбце А и в выпадающий список в ячейку D2.
Excel будет спрашивать и при утвердительном ответе пользователя автоматически добавлять новое имя
Читайте также: