Как сделать выпадающий список в эксель
В этой статье мы на примере покажем, как создать выпадающий список в excel, а так же как в эксель его можно изменить и удалить.
Обычно говоря про выпадающий список в excel на самом деле имеют ввиду настройку проверки данных. Именно поэтому очень часто самостоятельный поиск ниспадающего списка по меню excel не приносит результата.
Выпадающий список в excel
Создание выпадающего списка в excel
В excel выпадающий список в ячейке можно получить, настроив проверку данных. Для того чтобы сделать в экселе выпадающий список нужно:
Выбрать диапазон ячеек, в котором нужен ниспадающий список.
Выбор ячеек для ниспадающего списка
Открыть проверку данных – перейти на вкладку «Данные» в разделе «Работа с данными» кликнуть иконку «Проверка данных»
Открытие проверки данных
Настройка проверки списка – в окне «Проверка вводимых значений» на вкладке «Параметры» нужно «Тип данных» установить «Список», а в поле «Источник» записать через точку с запятой значения Вашего списка
Настройка ниспадающего списка excel
Еще один способ задать ниспадающий список – это в поле «Источник выбрать ячейки с заранее заготовленным списком
Выбор ячеек для ниспадающего списка эксель
Как изменить выпадающий список в excel
Изменение ниспадающего списка в эксель происходит примерно так же, как и его создание. Чтобы изменить выпадающий список в экселе нужно выделить ячейки со списком, потом на вкладке «Данные» открыть «Проверка данных» и в поле «Источник» переписать список или поменять диапазон ссылок.
Как убрать выпадающий список в excel
Чтобы удалить выпадающий список в excel достаточно убрать настройку проверки данных. Т.е. на вкладке «Данные» открыть «Проверка данных», затем в разделе «Тип данных» выбрать «Любое значение»
Убрать выпадающий список
Сам по себе инструмент проверки данных имеет большой и интересный функционал. Кроме выпадающих списков можно задавать проверки для типов данных, устанавливать промежутки допустимых диапазонов чисел и дат.
Устанавливать допустимое количество символов для ячейки и многое другое. Инструмент позволяет выводить любые сообщения, если данные не соответствуют проверке, а также обводить ячейки с ошибками.
У нас есть online курс Умные и сводные таблицы, пройдя который вы получите практические навыки в обработке больших массивов данных в том числе по настройке проверки данных.
Как убрать выпадающий список в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Выпадающий список с удалением использованных элементов
Имеем в качестве примера недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено. Идеальным вариантом было бы организовать в ячейках 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)
и создаем новый именованный диапазон Имена по следующей формуле:
в англоязычной версии =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) данных:
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
Ссылки по теме
- 4 способа создать выпадающий список в ячейках листа Excel
- Создание зависимых выпадающих списков
- Автоматическое создание выпадающих списков при помощи инструментов надстройки PLEX
- Выбор фото из выпадающего списка
Удаление раскрывающегося списка
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще. Меньше
Ненужный раскрывающийся список на листе можно удалить.
- Выделите ячейку, в которой есть раскрывающийся список. Если вы хотите удалить несколько таких ячеек, выделите их, удерживая нажатой клавишу CTRL.
- Щелкните Данные >Проверка данных.
- На вкладке Параметры нажмите кнопку Очистить все.
- Нажмите кнопку ОК.
Если вам нужно удалить все проверки данных с листа, включая раскрывающиеся списки, но вы не знаете, где они находятся, воспользуйтесь диалоговым окном Выделить группу ячеек. Для этого нажмите клавиши CTRL+G, в открывшемся диалоговом окне нажмите кнопку Выделить, выберите пункт Проверка данных, а затем — Всех или Этих же. Далее повторите действия, описанные выше.
Если вместо удаления раскрывающегося списка вы решили изменить параметры в нем, читайте статью Добавление и удаление элементов раскрывающегося списка.
- Выделите ячейку, в которой есть раскрывающийся список. Если вы хотите удалить несколько таких ячеек, выделите их, удерживая нажатой клавишу CTRL.
- Щелкните Данные >Проверка данных.
- На вкладке Параметры нажмите кнопку Очистить все.
- Нажмите кнопку ОК.
Если вам нужно удалить все проверки данных с листа, включая раскрывающиеся списки, но вы не знаете, где они находятся, воспользуйтесь диалоговым окном Выделить группу ячеек. Для этого нажмите клавиши CTRL+G, в открывшемся диалоговом окне нажмите кнопку Выделить, выберите пункт Проверка данных, а затем — Всех или Этих же. Далее повторите действия, описанные выше.
Если вместо удаления раскрывающегося списка вы решили изменить параметры в нем, читайте статью Добавление и удаление элементов раскрывающегося списка.
- Выделите ячейки, в которых есть раскрывающиеся списки.
- Щелкните Данные >Проверка данных.
- На вкладке Параметры нажмите кнопку Очистить все.
- Нажмите кнопку ОК.
Если вместо удаления раскрывающегося списка вы решили изменить параметры в нем, см. статью Добавление и удаление элементов раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.