Как сделать выпадающий список в excel на английском
Обновлено: 06.07.2024
Выпадающие списки могут значительно облегчить ввод данных. Ниже показано, как использовать функцию проверки данных Microsoft Excel для создания удобных списков на рабочих листах.
Раскрывающийся список или раскрывающееся меню Excel может упростить обычному пользователю Microsoft Excel ввод данных на листе или книге. Использование раскрывающегося меню в веб-формах, опросах или опросах может ограничить варианты ввода для выбранной ячейки, ускорить ввод данных и уменьшить количество ошибок при вводе данных. В этом совете по Excel мы покажем вам, как быстро и легко создать раскрывающийся список или раскрывающееся меню с помощью функции проверки данных приложения для работы с электронными таблицами.
Чтобы создать раскрывающийся список в Excel, вам потребуются две вещи: список значений (содержащихся в диапазоне ячеек) и пустая ячейка для использования в качестве ячейки ввода данных.
На рис. A показан простой раскрывающийся список на листе Excel. Чтобы использовать раскрывающееся меню, показанное здесь, кто-то должен навести курсор на пустую ячейку для ввода данных (в этом примере E4) и щелкнуть стрелку раскрывающегося списка, чтобы отобразить список значений, показанных в диапазоне ячеек A1: A4. Если пользователь пытается ввести что-то, что не является элементом в этом списке значений, Excel отклоняет ввод.
Вы можете следовать шагам, описанным в этом совете, создав новый лист с данными, подобными показанному на рис. A, загрузив наши демонстрационные файлы .xlsx и .xls или используя свой собственный лист и данные.
Рисунок А
Чтобы добавить раскрывающийся список из нашего примера на лист Excel, сделайте следующее:
- Создайте список проверки данных в ячейках A1:A4. Точно так же вы можете вводить элементы в одну строку, например A1:D1.
- Выберите ячейку E4. (Вы можете разместить раскрывающийся список практически в любой ячейке или даже в нескольких ячейках.)
- Выберите «Проверка данных» в меню ленты «Данные».
- Выберите «Список» в раскрывающемся списке параметра «Разрешить». (Смотрите, они везде.)
- Нажмите на поле управления источником и перетащите курсор, чтобы выделить ячейки A1:A4. Или просто введите ссылку (=$A$1:$A$4).
- Убедитесь, что установлен флажок в раскрывающемся списке "В ячейке". Если снять этот флажок, Excel по-прежнему будет заставлять пользователей вводить только значения списка (A1:A4), но не будет отображать раскрывающийся список.
- Нажмите "ОК".
Вы можете добавить раскрывающийся список в несколько ячеек Excel. Выберите диапазон ячеек ввода данных (шаг 2) вместо одной ячейки Excel. Он работает даже для несмежных ячеек Excel. Удерживая нажатой клавишу Shift, щелкните соответствующие ячейки Excel.
Несколько заметок:
- Выпадающий список можно увидеть, только если щелкнуть ячейку Excel, используемую для ввода данных.
- Теперь ваши пользователи могут выбрать только один из вариантов в раскрывающемся списке. Если они попытаются ввести свои данные, они получат сообщение об ошибке.
- Вы можете скопировать и вставить эту ячейку раскрывающегося списка в любые другие ячейки Excel в электронной таблице, а также создать столько различных раскрывающихся списков, как это, сколько хотите.
Бонусный совет по Microsoft Excel
Этот совет по Excel содержится в бесплатном PDF-файле "30 вещей, которые никогда не следует делать в Microsoft Office".
Использовать несколько ссылок
Ссылки между двумя книгами Excel распространены и полезны. Но множественные ссылки, в которых значения в workbook1 зависят от значений в workbook2, которые ссылаются на workbook3 и т. д., трудноуправляемы и нестабильны. Пользователи забывают закрывать файлы, а иногда даже перемещают их. Если вы единственный человек, работающий с этими связанными книгами Excel, у вас может не возникнуть проблем, но если другие пользователи просматривают и изменяют их, вы напрашиваетесь на неприятности. Если вам действительно нужно столько ссылок, вы можете подумать о новом дизайне.
Дополнительные советы по Excel
Примечание. Если вы не хотите, чтобы пользователи имели доступ к элементам на Листе2, вы можете скрыть Лист2. Для этого щелкните правой кнопкой мыши вкладку листа Sheet2 и выберите "Скрыть".
<р>2. На первом листе выберите ячейку B1.
Откроется диалоговое окно "Проверка данных".
<р>4. В поле Разрешить нажмите Список. <р>5. Нажмите в поле Источник и выберите диапазон A1:A3 на Листе2.
Примечание: чтобы скопировать/вставить раскрывающийся список, выберите ячейку с раскрывающимся списком и нажмите CTRL + c, выберите другую ячейку и нажмите CTRL + v.
<р>7. Вы также можете вводить элементы непосредственно в поле Источник вместо использования ссылки на диапазон.
Примечание: это делает ваш раскрывающийся список чувствительным к регистру. Например, если пользователь введет "да", появится предупреждение об ошибке.
Разрешить другие записи
Вы также можете создать раскрывающийся список в Excel, который позволяет вводить другие данные.
<р>1. Во-первых, если вы введете значение, которого нет в списке, Excel покажет предупреждение об ошибке. Ширина
Чтобы разрешить другие записи, выполните следующие действия.
<р>2. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".
Откроется диалоговое окно "Проверка данных".
<р>3. На вкладке Оповещение об ошибке снимите флажок "Показывать оповещение об ошибке после ввода неверных данных".
Добавить/удалить элементы
Вы можете добавлять или удалять элементы из раскрывающегося списка в Excel, не открывая диалоговое окно "Проверка данных" и не изменяя ссылку на диапазон. Это экономит время.
<р>1. Чтобы добавить элемент в раскрывающийся список, перейдите к элементам и выберите элемент.
Примечание. Excel автоматически изменил ссылку на диапазон с Sheet2!$A$1:$A$3 на Sheet2!$A$1:$A$4. Вы можете проверить это, открыв диалоговое окно "Проверка данных".
<р>4. Введите новый элемент.
Динамический раскрывающийся список
Вы также можете использовать формулу, которая автоматически обновляет раскрывающийся список при добавлении элемента в конец списка.
<р>1. На первом листе выберите ячейку B1.
Откроется диалоговое окно "Проверка данных".
<р>3. В поле Разрешить нажмите Список. <р>4. Щелкните поле Источник и введите формулу: =СМЕЩ(Лист2!$A$1,0,0,СЧЁТ(Лист2!$A:$A),1)
Объяснение: функция OFFSET принимает 5 аргументов. Ссылка: Sheet2!$A$1, строки для смещения: 0, столбцы для смещения: 0, высота: COUNTA(Sheet2!$A:$A) и ширина: 1. COUNTA(Sheet2!$A:$A) подсчитывает число значений в столбце A на Sheet2, которые не пусты. Когда вы добавляете элемент в список на Листе2, СЧЕТЧИК(Лист2!$A:$A) увеличивается. В результате диапазон, возвращаемый функцией OFFSET, расширяется, а раскрывающийся список обновляется.
<р>6. На втором листе просто добавьте новый элемент в конец списка.
Удалить раскрывающийся список
Чтобы удалить раскрывающийся список в Excel, выполните следующие действия.
<р>1. Выберите ячейку с раскрывающимся списком.
Откроется диалоговое окно "Проверка данных".
<р>3. Нажмите «Очистить все».
Примечание. Чтобы удалить все другие раскрывающиеся списки с такими же настройками, установите флажок "Применить эти изменения ко всем другим ячейкам с такими же настройками", прежде чем нажимать "Очистить все".
Зависимые раскрывающиеся списки
Хотите узнать больше о раскрывающихся списках в Excel? Узнайте, как создавать зависимые раскрывающиеся списки.
<р>1. Например, если пользователь выбирает Пиццу из первого раскрывающегося списка.
Магия стола
Вы также можете хранить элементы в таблице Excel, чтобы создать динамический раскрывающийся список.
<р>1. На втором листе выберите элемент списка.
Объяснение: функция ДВССЫЛ в Excel преобразует текстовую строку в допустимую ссылку.
<р>6. На втором листе просто добавьте новый элемент в конец списка.
Примечание: попробуйте сами. Загрузите файл Excel и создайте раскрывающийся список.
<р>7. При использовании таблиц используйте функцию UNIQUE в Excel 365/2021 для извлечения уникальных элементов списка.
Примечание: эта функция динамического массива, введенная в ячейку F1, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365/2021 называется сбросом.
<р>8. Используйте этот диапазон для создания волшебного раскрывающегося списка.
Объяснение: всегда используйте первую ячейку (F1) и символ решетки для обозначения диапазона разлива.
Примечание. Когда вы добавляете новые записи, функция UNIQUE автоматически извлекает новые уникальные элементы списка, а Excel автоматически обновляет раскрывающийся список.
< /p>
Задайте вопрос или присоединитесь к обсуждению всего, что связано с Excel, на нашем канале Slack.
Раскрывающиеся списки Excel — это простой способ управления значениями, которые вводятся в ячейку. Они очень удобны в использовании и позволяют уменьшить количество ошибок при вводе.
Вы можете создать раскрывающийся список в Excel различными способами. Мы рассмотрим, как создать раскрывающийся список в Excel, используя три метода.
Способ 1 — вручную
Если вам нужен простой вариант (например, Черный/белый; Да/Нет/Не знаю и т. д.), то самым быстрым способом может быть сделать это вручную.
- Выберите ячейку или диапазон ячеек, в которых вы хотите создать раскрывающийся список.
- Перейдите на вкладку Данные. В группе команд "Инструменты данных" выберите значок "Проверка данных".
- В диалоговом окне "Проверка данных" на вкладке "Настройки" выберите Список в качестве критерия проверки.
- В поле Источник введите параметры, разделенные запятыми. Убедитесь, что установлен флажок Раскрывающийся список в ячейке.
Это создаст раскрывающийся список в выбранных ячейках. Все элементы, перечисленные в исходном поле, перечислены в разных строках раскрывающегося меню.
Загрузите бесплатный обучающий файл с раскрывающимся списком!
Используйте этот бесплатный файл раскрывающегося списка Excel, чтобы практиковаться вместе с учебным пособием.
Способ 2. Ссылки на данные из других ячеек
Есть также возможность создать раскрывающийся список в Excel, используя диапазон ячеек в качестве исходных данных для вашего списка проверки.
- Конечно, сначала вам нужно настроить источник или указать его в другом месте. Мы ввели исходные данные в ячейки с A2 по A7 на другом листе с именем Источник в той же книге.
- Далее выберите ячейку или диапазон ячеек, в которых вы хотите создать раскрывающийся список. В нашем примере мы выбрали B2:C5.
- Перейдите на вкладку Данные. В группе команд "Инструменты данных" выберите значок "Проверка данных".
- Выберите «Список» в качестве критерия проверки.
- В поле "Источник" введите диапазон, содержащий список значений, которые будут использоваться в качестве раскрывающегося списка, или вы можете просто щелкнуть внутри поля "Источник" и выбрать ячейки в Источнике< /em> рабочий лист.
Это создаст раскрывающийся список в выбранных ячейках. Каждый элемент в списке отображается в отдельной строке раскрывающегося меню.
Способ 3 – формула OFFSET (динамические раскрывающиеся списки)
Вы также можете использовать формулу OFFSET для создания динамических раскрывающихся списков, которые автоматически обновляются при добавлении элементов в конец списка.
Синтаксис функции СМЕЩ:
Первые три аргумента являются обязательными, а последние два — необязательными.
- Ссылка. Это ячейка или диапазон ячеек (смежных), на основе которых можно определить смещение. Ссылка является отправной точкой.
- Строки – это количество строк (вниз или вверх), на которое нужно перейти от начальной точки. Если rows является положительным числом, формула перемещается вниз от начальной ссылки. В случае отрицательного числа оно идет вверх от начальной ссылки.
- Количество столбцов – количество столбцов, на которое формула должна переместиться от начальной точки.Как и строки, столбцы могут быть положительными (справа от начальной ссылки) или отрицательными (слева от начальной ссылки).
- Высота – количество строк, которые должен содержать результат. Если он опущен, используется высота ссылки.
- Ширина – количество столбцов, которые должен содержать результат. Если он опущен, используется ширина ссылки.
Чтобы упростить понимание, мы воспользуемся функцией OFFSET для создания динамического раскрывающегося списка в следующем примере.
Мы хотим, чтобы столбцы B и C на листе Игры отображали раскрывающийся список всех клубов лиги. Исходные данные названий клубов будут взяты из другого листа (Источник) в этой книге.
- На листе Игры выберите ячейки с B2 по C5, так как мы хотим, чтобы раскрывающийся список был создан для всех ячеек в этом диапазоне.
- На вкладке "Данные" на ленте в группе "Инструменты данных" нажмите "Проверка данных". Появится диалоговое окно "Проверка данных".
- В поле "Разрешить" нажмите "Список".
- Нажмите в поле "Источник" и введите формулу:
Приведенная выше формула указывает Excel использовать ячейку A2 на исходном листе в качестве отправной точки (обратите внимание на абсолютную ссылку на ячейку A2). Поскольку желаемый результат должен начинаться со ссылочной ячейки, смещение должно оставаться равным нулю строк и нулю столбцов (от начальной точки).
Высота нашего результата должна быть равна высоте списка, поэтому мы просим Excel подсчитать количество значений в списке, используя формулу СЧЁТЗ, ссылаясь на весь столбец A.
Результат будет иметь ширину всего в один столбец, поэтому 1 — последний аргумент в нашей формуле.
Раскрывающийся список работает так же, как и в двух предыдущих примерах, но имеет дополнительное преимущество: он обновляется всякий раз, когда варианты добавляются в конец списка на листе Источник.
Просто добавьте новый элемент в конец исходного списка, и варианты выбора в раскрывающемся списке будут немедленно обновлены.
Разрешить другие записи
Пока что мы не изменили Предупреждение об ошибке по умолчанию для созданных нами раскрывающихся списков. Это означает, что после настройки списка Excel будет принимать только те значения, которые являются частью списка. Если мы попытаемся ввести значение, не входящее в список источников, мы получим сообщение об ошибке.
При желании вы можете создать раскрывающийся список в Excel, в который можно добавить другие записи, не включенные в исходный список.
- Выберите ячейку или ячейки с раскрывающимися списками, в которых вы хотите разрешить запись «на лету».
- Перейдите на вкладку "Данные", затем в группе команд "Инструменты данных" нажмите "Проверка данных".
- Появится диалоговое окно проверки данных. Перейдите на вкладку Оповещение об ошибке и снимите флажок Показывать оповещение об ошибке после ввода неверных данных. Нажмите "ОК".
Теперь вы сможете ввести значение, которого нет в раскрывающемся списке.
Копировать правило проверки данных из другой ячейки
Если есть ячейка с правилом проверки данных, которое вы хотите скопировать в другую ячейку:
- Перейдите к ячейке, содержащей правило, и скопируйте ее.
- Перейдите к целевой ячейке и на вкладке "Главная" нажмите стрелку раскрывающегося списка "Вставить".
- Выберите «Специальная вставка».
- В диалоговом окне "Специальная вставка" выберите переключатель Проверка и нажмите "ОК".
Добавить элемент в раскрывающийся список Excel
Даже если вы не используете динамический раскрывающийся список, вот небольшая полезная хитрость, позволяющая быстро добавить элемент в раскрывающийся список:
- Перейдите к списку и щелкните правой кнопкой мыши одно из значений.
- В контекстном меню нажмите "Вставить".
- Выберите "Сдвинуть ячейки вниз" и нажмите "ОК".
- Введите новый элемент.
В результате Excel автоматически расширяет исходный диапазон, включая первое и последнее значения в списке, а также все промежуточные значения.
Удалить элемент из раскрывающегося списка
Чтобы быстро удалить элемент из раскрывающегося списка:
- Перейдите к списку и щелкните правой кнопкой мыши значение, которое нужно удалить.
- В контекстном меню нажмите "Удалить".
- Выберите "Сдвинуть ячейки вверх" и нажмите "ОК".
В результате Excel корректирует диапазон источников таким образом, чтобы список источников проверки данных начинался со ссылки на первое значение и заканчивался ссылкой на последнее значение.
Удалить раскрывающийся список
Если вы хотите удалить все значения из ячейки, включая раскрывающиеся списки, форматирование и т. д., просто перейдите на вкладку "Главная", щелкните раскрывающийся список "Очистить" и выберите "Очистить все".
Однако, если вы хотите удалить только раскрывающийся список, но сохранить выбранные значения, сделайте следующее:
- Выберите ячейки в раскрывающемся списке.
- На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".
- В разделе Разрешить измените значение списка на любое.
Если вы также хотите удалить все другие раскрывающиеся списки с теми же настройками, установите флажок «Применить эти изменения ко всем другим ячейкам с такими же настройками».
Создать зависимые раскрывающиеся списки
Неожиданный поворот. Возможно, вы захотите узнать, как создать раскрывающийся список в Excel, в котором параметры меняются в зависимости от того, что было выбрано в предыдущем раскрывающемся списке.
Например, в столбце B мы выберем название страны, чья лига будет отображаться.
Тогда столбцы C и D будут предлагать только те клубы, которые принадлежат стране, выбранной в столбце B этой строки.
Настройка выполняется следующим образом:
Шаг 1. Создайте список стран, которые станут вашим «родительским» списком проверки данных. Мы указали Англию, Испанию и Италию в качестве родительского списка.
Шаг 2. Перечислите названия клубов по местонахождению в отдельных столбцах.
Шаг 3. Создайте именованный диапазон для списка каждой страны, где имена идентичны именам вашего родительского списка проверки данных.
Чтобы отобразить параметры родительского списка в раскрывающемся меню в Excel:
- Выберите ячейки, в которых мы хотим, чтобы родительский список отображался в раскрывающемся списке. В нашем примере мы выбрали ячейки B2:B5.
- Перейдите на вкладку "Данные" > щелкните значок "Проверка данных" > щелкните "Список" в раскрывающемся списке Разрешить > перейдите в поле Источник и выберите $A$2:$ Диапазон A$4 на исходном листе.
Чтобы отобразить параметры зависимого списка:
- Выберите ячейки, в которых мы хотим, чтобы зависимые значения отображались в раскрывающемся списке. Мы выбрали C2:D5.
- Перейдите на вкладку "Данные" > щелкните значок "Проверка данных" > щелкните "Список" в раскрывающемся списке Разрешить > перейдите в поле "Источник" и введите =ДВССЫЛ(B2).
В нашем случае мы также хотим использовать ту же лигу страны, что и родительский список для столбца D. Поэтому мы должны выделить диапазон от C2 до D6 и использовать смешанную ссылку при вводе формулы для проверки.
Поскольку мы всегда будем искать название страны в столбце B, ссылка на столбец B будет фиксированной, но номер строки будет относиться к строке, для которой мы делаем выборку.
Поэтому мы будем вводить =ДВССЫЛ($B2)
Теперь наши зависимые списки в столбцах C и D работают прекрасно, а параметры меняются в зависимости от того, что было выбрано в столбце B.
Подробнее
Посмотрите, вы профессионально создаете раскрывающиеся списки в Excel! Узнайте больше об Excel на курсах GoSkills! Вы можете попробовать курс Microsoft Excel — базовый и продвинутый. Или вы можете начать с бесплатного ускоренного курса Excel за час сегодня.
Изучите Excel бесплатно
Начните изучать формулы, функции и экономящие время лайфхаки уже сегодня с помощью этого бесплатного курса!
В этой статье вы узнаете, как создавать раскрывающиеся списки в Microsoft Excel с помощью функции под названием "Проверка данных". При применении функция проверки данных служит для ограничения данных, которые могут быть введены в целевую ячейку, требуя от пользователей выбора значения из списка заранее определенных входных данных. Эта функция очень полезна в ряде случаев, от планирования графиков отпусков сотрудников, до контроля расходов, до общего управления проектами. Вот как начать.
Создать раскрывающийся список в Microsoft Excel
Для выполнения этой операции вам потребуется открыть как минимум два листа в рабочей книге: рабочий лист и чистый лист, на котором вы сможете составлять свои списки.
Первый шаг — создать список. Для этого перейдите на чистый лист и перечислите элементы, которые вы хотите включить в раскрывающийся список в столбце А. Список может быть любой длины и может включать любой тип информации — какой-то общий список. элементы: Да/Нет (часто сокращенно Y/N), Пройдено/Не пройдено, нумерованные списки и даты.
Теперь вернитесь к своему рабочему листу и щелкните ячейку или ячейки, которые вы хотите проверить. Затем перейдите на вкладку «Данные» и найдите параметр «Проверка данных» в разделе «Группы данных»:
Далее перейдите на вкладку «Настройки» и найдите поле «Разрешить». В раскрывающемся меню выберите Список:
Заполнение выпадающего меню в Excel
< бр />
Перейдите к рабочему листу со списком и выберите все элементы, которые вы хотите включить в свой список. Поле Источник в диалоговом окне автоматически регистрирует поля по мере их выбора.Обратите внимание, что если вы решите добавить или удалить элемент из своего списка, Excel автоматически обновит проверку данных:
Убедитесь, что выбран раскрывающийся список в ячейке, чтобы вы обязательно см. стрелку раскрывающегося списка рядом с ячейкой. Закончив, нажмите OK.
Теперь ваш раскрывающийся список должен отображаться в ячейках, указанных на вашем рабочем листе. Чтобы проверить проверку данных, попробуйте ввести в ячейки как действительные, так и недопустимые данные. Действительные данные должны регистрироваться правильно, а недействительные данные (информация, не включенная в ваш список) должны возвращать сообщение об ошибке.
Создание длинного раскрывающегося списка в Excel
Если вы хотите добавить элементы в раскрывающийся список, но обнаружите, что ваше пространство ограничено, попробуйте вставить ячейки сразу над последней ячейкой, а не под ней.
Читайте также: