Выпадающий список с удалением использованных элементов в эксель
Обновлено: 06.07.2024
Ещё вариант: вообще не изменять список, а использовать Условное форматирование для проверки на неуникальность. Если вы введете повторяющееся значение - то ячейки с повторами подсветятся.
P.S. В формуле с ВПР() диапазон исходной таблицы надо задавать абсолютными ссылками, а то адреса у вас "поплывут" при протягивании формулы
Ещё вариант: вообще не изменять список, а использовать Условное форматирование для проверки на неуникальность. Если вы введете повторяющееся значение - то ячейки с повторами подсветятся.
P.S. В формуле с ВПР() диапазон исходной таблицы надо задавать абсолютными ссылками, а то адреса у вас "поплывут" при протягивании формулы AndreTM
P.S. В формуле с ВПР() диапазон исходной таблицы надо задавать абсолютными ссылками, а то адреса у вас "поплывут" при протягивании формулы Автор - AndreTM
Дата добавления - 08.06.2017 в 21:16
Подразумевалось: "Нет, не надо ничего больше делать, если Именованный диапазон "Код" задать/задан динамическим".
Подразумевалось: "Нет, не надо ничего больше делать, если Именованный диапазон "Код" задать/задан динамическим".
Skype: andre.tm.007
Donate: Q iwi: 9517375010
Автор - AndreTM
Дата добавления - 08.06.2017 в 21:50
Имеем в качестве примера недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено. Идеальным вариантом было бы организовать в ячейках 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) данных.
Создаем выпадающий список в ячейках
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
После того, как вы создали раскрывающийся список, вам может понадобиться добавлять в него дополнительные элементы или удалять имеющиеся. В этой статье мы покажем, как изменять списки, созданные разными способами.
Изменение раскрывающегося списка, основанного на таблице Excel
Если источником вашего списка является таблица Excel, достаточно просто добавить элементы в список или удалить их из него, а Excel автоматически обновит все связанные раскрывающиеся списки.
Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.
Чтобы удалить элемент, нажмите кнопку Удалить.
Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх.
Изменение раскрывающегося списка, основанного на именованном диапазонеОткройте лист, содержащий именованный диапазон для раскрывающегося списка.
Выполните одно из указанных ниже действий.
Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.
Чтобы удалить элемент, нажмите кнопку Удалить.
Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх.
На вкладке Формулы нажмите кнопку Диспетчер имен.
В поле Диспетчер имен выберите именованный диапазон, который требуется обновить.
Щелкните поле Диапазон, а затем на листе выберите все ячейки, содержащие записи для раскрывающегося списка.
Совет: Чтобы определить именованный диапазон, выделите его и найдите его имя в поле Имя. Сведения о поиске именованных диапазонов см. в статье Поиск именованных диапазонов.
Откройте лист, содержащий данные для раскрывающегося списка.
Выполните одно из указанных ниже действий.
Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.
Чтобы удалить элемент, нажмите кнопку Удалить.
Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх.
На листе с раскрывающимся списком выделите содержащую список ячейку.
На вкладке Данные нажмите кнопку Проверка данных.
В диалоговом окне на вкладке Параметры щелкните поле Источник, а затем на листе с записями для раскрывающегося списка выберите все ячейки, содержащие эти записи. После выделения ячеек вы увидите, как изменится диапазон списка в поле "Источник".
Чтобы обновить все ячейки, к которым применен один и тот же раскрывающийся список, установите флажок Распространить изменения на другие ячейки с тем же условием.
Изменение раскрывающегося списка с введенными вручную элементамиНа листе с раскрывающимся списком выделите содержащую список ячейку.
На вкладке Данные нажмите кнопку Проверка данных.
На вкладке Параметры щелкните поле Источник и измените нужные элементы списка. Элементы должны быть разделены точкой с запятой, без пробелов между ними следующим образом: Да;Нет;Возможно
Чтобы обновить все ячейки, к которым применен один и тот же раскрывающийся список, установите флажок Распространить изменения на другие ячейки с тем же условием.
После обновления раскрывающегося списка убедитесь, что он работает так, как нужно. Например, проверьте, достаточно ли ширины ячеек для отображения ваших обновленных записей.
Если список записей для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов читайте в статье Блокировка ячеек.
Если вам нужно удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Чтобы просмотреть видео о том, как работать с раскрывающимися списками, см. статью Создание раскрывающихся списков и управление ими.
Изменение раскрывающегося списка, основанного на таблице Excel
Если источником вашего списка является таблица Excel, достаточно просто добавить элементы в список или удалить их из него, а Excel автоматически обновит все связанные раскрывающиеся списки.
Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.
Чтобы удалить элемент, нажмите кнопку Удалить.
Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх.
Изменение раскрывающегося списка, основанного на именованном диапазонеОткройте лист, содержащий именованный диапазон для раскрывающегося списка.
Выполните одно из указанных ниже действий.
Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.
Чтобы удалить элемент, нажмите кнопку Удалить.
Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх.
На вкладке Формулы нажмите кнопку Диспетчер имен.
В поле Диспетчер имен выберите именованный диапазон, который требуется обновить.
Щелкните поле Диапазон, а затем на листе выберите все ячейки, содержащие записи для раскрывающегося списка.
Совет: Чтобы определить именованный диапазон, выделите его и найдите его имя в поле Имя. Сведения о поиске именованных диапазонов см. в статье Поиск именованных диапазонов.
Откройте лист, содержащий данные для раскрывающегося списка.
Выполните одно из указанных ниже действий.
Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.
Чтобы удалить элемент, нажмите кнопку Удалить.
Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх.
На листе с раскрывающимся списком выделите содержащую список ячейку.
На вкладке Данные нажмите кнопку Проверка данных.
В диалоговом окне на вкладке Параметры щелкните поле Источник, а затем на листе с записями для раскрывающегося списка выделите содержимое ячеек в Excel, в которых находятся эти записи. После выделения ячеек вы увидите, как изменится диапазон списка в поле "Источник".
Чтобы обновить все ячейки, к которым применен один и тот же раскрывающийся список, установите флажок Распространить изменения на другие ячейки с тем же условием.
Изменение раскрывающегося списка с введенными вручную элементамиНа листе с раскрывающимся списком выделите содержащую список ячейку.
На вкладке Данные нажмите кнопку Проверка данных.
На вкладке Параметры щелкните поле Источник и измените нужные элементы списка. Элементы должны быть разделены точкой с запятой, без пробелов между ними следующим образом: Да;Нет;Возможно
Чтобы обновить все ячейки, к которым применен один и тот же раскрывающийся список, установите флажок Распространить изменения на другие ячейки с тем же условием.
После обновления раскрывающегося списка убедитесь, что он работает так, как нужно. Например, проверьте, как изменяется ширина столбцов и высота строк для отображения ваших обновленных записей.
Если список записей для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов читайте в статье Блокировка ячеек.
Если вам нужно удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Чтобы просмотреть видео о том, как работать с раскрывающимися списками, см. статью Создание раскрывающихся списков и управление ими.
В Excel в Интернете можно изменять только тот раскрывающийся список, в котором исходные данные введены вручную.
Выделите ячейки, в которых расположен раскрывающийся список.
Выберите пункт Данные > Проверка данных.
На вкладке Параметры щелкните в поле Источник. Затем выполните одно из указанных ниже действий.
Если поле "Источник" содержит записи раскрывающегося списка, разделенные запятыми, введите новые записи или удалите ненужные. После завершения записи должны быть разделены запятыми без пробелов. Например: Фрукты,Овощи,Мясо,Закуски.
Если поле "Источник" содержит ссылку на диапазон ячеек (например, =$A$2:$A$5), нажмите кнопку Отмена, а затем добавьте или удалите записи из этих ячеек. В этом примере можно добавить или удалить записи в ячейках А2–А5. Если окончательный список записей оказался больше или меньше исходного диапазона, вернитесь на вкладку Параметры и удалите содержимое поля Источник. Затем щелкните и перетащите указатель, чтобы выделить новый диапазон, содержащий записи.
Если поле "Источник" содержит именованный диапазон, например, "Отделы", необходимо изменить сам диапазон с помощью классической версии Excel.
После обновления раскрывающегося списка убедитесь, что он работает так, как нужно. Например, проверьте, достаточно ли ширины ячеек для отображения ваших обновленных записей. Если вам нужно удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Раскрывающийся список – удобный визуальный компонент таблиц Microsoft Excel, который часто оказывается полезным. Однако в некоторых случаях потребность в нем отпадает, и юзеры задаются вопросом об удалении списка из листа. Иногда с этим возникают проблемы, поскольку непонятно, как сразу убрать все содержимое.
В этой статье я покажу три подходящих метода, а вы сможете выбрать любой из них.
Вариант 1: Контекстное меню ячейки
Элементы управления ячейками отображаются в контекстном меню, которое можно вызвать в любое время. В нем есть пункт, отвечающий за удаление содержимого клетки, который и пригодится в тех ситуациях, когда нужно избавиться от раскрывающегося списка в Эксель. Однако учитывайте, что будет удалено даже текущее выбранное значение, поэтому запомните его или скопируйте заранее.
Выделите ячейку с раскрывающимся списком и кликните по ней правой кнопкой мыши.
Из контекстного меню выберите пункт «Удалить».
Программа предлагает несколько вариантов удаления со сдвигом. В этом случае не так важно, где ставить маркер, поэтому выберите любой параметр и подтвердите удаление.
Далее вы увидите, что содержимое ячейки полностью очищено и в ней можно написать любую функцию, число или текст. Предварительно рекомендую убедиться в том, что формат ячейки вас устраивает, поскольку математические вычисления в ее текстовом представлении невозможны.
Вариант 2: Меню «Проверка вводимых значений»
Если ранее вы самостоятельно создавали раскрывающийся список, то уже знаете, через какое именно меню это делается. Его можно использовать и для очистки данных, удалив все значения кроме выбранного. Это отличный вариант в тех случаях, когда нужно сохранить текущее число или текст в ячейке, но удалить все остальные варианты списка.
Выделите список левой кнопкой мыши, перейдите на вкладку «Данные» и откройте окно «Проверка данных».
Слева внизу вы увидите кнопку «Очистить все», которая и удаляет все выставленные условия проверки, то есть в данном случае – выпадающий список.
Теперь вы увидите, что тип данных изменился на «Любое значение» и пропала строка со значениями списка.
Вернитесь к таблице и проверьте результативность выполненных действий.
Вариант 3: Изменение типа данных в ячейке
В завершение быстро разберу вариант, связанный с предыдущим, но выполняющийся немного другим образом. Он заключается в изменении типа данных в ячейке, что автоматически удаляет выпадающий список и применяет другие необходимые значения для клетки. Используйте этот вариант, если уже знаете, какое форматирование ячейки хотите произвести.
Выделите ячейку и перейдите в «Проверка данных» точно так же, как это было показано выше.
На этот раз разверните меню «Тип данных» и установите параметр «Любое значение» или другой, в зависимости от ваших личных предпочтений.
Вернитесь к ячейке и убедитесь в том, что теперь вы можете редактировать ее содержимое требуемым вам образом.
Все действия в Microsoft Excel, как и в практически любой другой программе, можно отменить, используя комбинацию клавиш Ctrl + Z. Нажмите ее один раз, если после удаления списка вы решили, что хотите его вернуть, или забыли сохранить конкретные значения.
Детальную информацию о том, как производится создание раскрывающегося списка, вы найдете в другой статье. Она пригодится, если вдруг после удаления одного перечня вы задумали создать другой, с отличающимися или такими же значениями.
Читайте также: