Как создать раскрывающийся список в Excel

Выпадающий список Excel — отличная функция, облегчающая безошибочный ввод данных. Получите четкое представление о процессе реализации раскрывающихся списков в книге Excel с помощью инструмента проверки данных Excel.

Раскрывающиеся списки в ячейках — отличные варианты для ускорения задач ввода данных. Оператор ввода данных может ввести необходимые данные, просто щелкнув раскрывающийся список, а затем выбрав правильный элемент списка. В большинстве процессов ввода данных используются раскрывающиеся списки, чтобы свести к минимуму человеческие ошибки и двусмысленность при вводе данных.

Продолжайте читать, чтобы узнать об основах раскрывающихся списков, инструменте проверки данных Excel и пошаговом процессе добавления раскрывающегося списка в книгу Excel.

Что такое раскрывающийся список Excel?

Раскрывающийся список в приложениях для работы с электронными таблицами, таких как Excel, является мощным инструментом для уменьшения количества ошибок и повышения производительности.

Такие функции инструмента проверки данных Excel уменьшают количество ошибок, предлагая пользователю рабочей книги Excel некоторые ограниченные элементы списка для конкретной ячейки. Когда вы используете Excel для сбора данных и обрабатываете данные с помощью инструмента базы данных, раскрывающийся список также помогает вам собирать унифицированные данные.

Например, если ячейка принимает только названия фруктов, оператор ввода данных не сможет добавить названия животных, рыб, специй и т. д. Причина в том, что в раскрывающемся меню будут отображаться только те фрукты, которые продает ваша компания. .

Преимущества раскрывающегося списка

Опять же, эта функция также повышает производительность, автоматически предлагая элементы ячейки. Например, если в раскрывающемся списке есть имена 100 сотрудников и тип пользователя «А», в раскрывающемся списке Excel автоматически будут отображаться имена, начинающиеся с «А».

Таким образом, пользователь может просто выбрать имя с помощью мыши и сэкономить время. Если вы заметили здесь, вы также можете уберечь себя от орфографических ошибок.

Раскрывающиеся списки еще больше расширяют функциональные возможности вашего рабочего листа, предлагая динамическое создание отчетов. Например, ваша компания работает в 150 странах, у вас 200 офисов, в офисах по десять менеджеров, и каждый менеджер контролирует 20 менеджеров по продажам.

Когда вам нужно сделать отчет о продажах, вы можете сделать зависимые выпадающие списки Excel. Выпадающий список стран приведет вас к нужной стране. Затем, выбрав офис, вы увидите десять менеджеров, которые работают в этом офисе. И, наконец, когда вы выбираете менеджера, вы увидите только торгового представителя, который работает под этим менеджером.

Следовательно, вам не нужно запоминать имена всех. Просто выберите страну и следуйте многоуровневым раскрывающимся спискам.

Что такое проверка данных в Excel?

Ячейки Excel могут принимать любые входные данные, такие как символы, числа, специальные символы и т. д. Однако большинство процессов создают специально разработанный рабочий лист с ограниченными данными для каждой ячейки ввода данных.

Например, в одной ячейке разрешены числа, в следующей — символы, в другой — имена сотрудников и т. д. Но как Excel обеспечивает соблюдение всех этих ограничений? Ответ — инструмент проверки данных Excel.

Большинство пользователей Excel знают этот инструмент как средство быстрого создания раскрывающихся меню. Однако он имеет гораздо большее применение, чем раскрывающиеся списки Excel. Короче говоря, всякий раз, когда администратор рабочего листа или книги Excel хочет заставить пользователей вводить данные в определенном формате, он обращается за помощью к средству проверки данных.

Ознакомьтесь с инструментом проверки данных

Вы можете найти инструмент проверки данных в меню ленты Excel. Находясь на открытом листе Excel, щелкните вкладку «Данные» в меню ленты. В разделе «Инструменты данных» вы должны найти инструмент «Проверка данных». Посмотрите на изображение ниже, чтобы получить четкое представление о расположении инструмента.

Когда вы нажмете кнопку «Проверка данных», вы увидите диалоговое окно «Проверка данных» с тремя вкладками: «Настройки», «Вводное сообщение» и «Предупреждение об ошибке».

Настройки

Настройки позволяют настраивать такие параметры, как разрешенные форматы данных и устанавливать правила ввода данных.

Последняя версия Microsoft Excel для настольных компьютеров или ноутбуков позволяет добавлять следующие форматы:

• Любое значение без проверки.
• Целое число позволяет пользователям вводить в целевую ячейку только целые числа.
• Десятичный позволяет отформатировать ячейку так, чтобы она принимала только десятичные числа.
• Список позволяет создавать раскрывающиеся списки с предопределенными элементами списка.
• Дата позволяет вам ограничить определенные ячейки только для ввода значений даты.
• Проверка времени форматирует целевую ячейку так, чтобы она принимала только значения времени.
• Длина текста — идеальная функция для ограничения данных ячейки до определенного количества символов или цифр.
• Пользовательское форматирование позволяет пользователю использовать формулы в проверенных ячейках.

Приведенные выше функции форматирования или проверки работают с использованием следующих критериев проверки. И хорошо то, что вы можете настроить эти критерии проверки в нескольких случаях:

• « Между » позволяет использовать числа, которые находятся в пределах минимального и максимального диапазона.
• « Не между » полезно, когда вам нужно указать, что введенные данные не должны находиться в пределах минимального или максимального значения.
• « Равно » полезно при фиксировании ввода на определенное значение.
• «Не равно» означает, что введенные данные не должны совпадать с указанным значением ячейки.
• «Больше чем» используется, когда ввод должен быть больше определенного значения.
• « Меньше чем » означает, что введенное значение должно быть меньше указанной цифры.
• « Больше или равно » означает, что входное значение должно быть равно или больше заданного значения.
• « Меньше или равно » означает, что введенное значение меньше или равно заданному значению.

Входное сообщение

Правила проверки данных полезны только тогда, когда вы сообщаете о них операторам ввода данных. Чтобы сделать это правильно, вы можете использовать функцию входного сообщения проверки данных в Excel.

При использовании этой функции вы можете ввести заголовок и описательное сообщение о правилах проверки данных. Когда пользователи наводят курсор на отформатированную ячейку, появляется контекстное меню с входным сообщением . Стоит отметить, что это сообщение не будет видно до тех пор, пока вы не наведете указатель мыши на целевую ячейку.

Предупреждение об ошибке

Когда пользователь делает ошибки при вводе данных в проверенную ячейку, вы можете отобразить предупреждения об ошибках, чтобы проинформировать пользователя. Вы можете использовать любое из следующих оповещений:

• Останавливаться
• Предупреждение
• Информация

Опять же, вы можете ввести собственный заголовок и сообщение об ошибке для вышеуказанных предупреждений. Таким образом, пользователь будет проинформирован и не повторит ошибок.

Использование проверки данных в Excel

• Создавайте полезные раскрывающиеся списки, чтобы уменьшить количество ошибок и быстрее вводить данные.
• Предотвратите ввод неправильных дат из-за опечатки или человеческой ошибки.
• Разрешайте ввод в верхнем регистре только для определенных ситуаций, таких как почтовые индексы Великобритании, сокращения и т. д.
• Вы можете свести количество повторяющихся значений к нулю.
• Создавайте зависимые раскрывающиеся списки для фильтрации данных в соответствии с основным списком.
• Вы можете использовать функции инструмента проверки данных, когда вам нужны только текстовые записи для определенных ячеек.
• Вы можете заставить пользователя вводить только числа, а не какие-либо тексты или символы для числовых значений.
• Создавайте проектные или офисные графики только для будних дней.
• Проверка записи пользователя на основе другой записи, сделанной в другой ячейке того же рабочего листа.
• Принудительное ограничение на длину цифры или символа любой ячейки.
• Сделайте записи данных, содержащие заранее определенные или определенные наборы текстов для целей поиска.
• Сообщите оператору ввода данных, что могло пойти не так, создав собственные сообщения об ошибках.

Создайте раскрывающийся список с помощью проверки данных Excel

Раскрывающийся список предоставляет контекстное меню с используемыми значениями или текстом для проверенной ячейки. Перед созданием такого списка соберите значения или тексты в одном месте. Это может быть та же книга, рабочий лист или даже совершенно другой файл Excel. Вот что вам нужно сделать:

• Откройте приложение Excel на своем ПК и введите список элементов, которые вы хотите в раскрывающемся списке.
• Это также возможно, если вы хотите перенести элементы из другого листа, книги или файла. Вам просто нужно держать эти ресурсы доступными.

После того, как вы нашли источник перечисленных элементов, вы можете перейти к собственно части создания выпадающего списка.

• Выберите ячейку, в которой вам нужно создать раскрывающийся список Excel.
• Нажмите на вкладку « Данные » в меню ленты Excel.
• Теперь вы должны найти раздел « Инструменты данных » в середине меню ленты. Он предлагает несколько инструментов данных, таких как «Текст в столбцы», «Быстрое заполнение», «Управление моделью данных» и «Проверка данных».
• Вы можете напрямую щелкнуть значок « Проверка данных », чтобы открыть диалоговое окно «Проверка данных».

• Кроме того, вы можете щелкнуть текст « Проверка данных» , чтобы открыть раскрывающееся меню, и снова выбрать «Проверка данных», чтобы открыть диалоговое окно.

До сих пор вы успешно открывали диалоговое окно проверки данных. Он предлагает все функции, необходимые для создания простого раскрывающегося списка. Вот как:

• По умолчанию вы окажетесь на вкладке « Настройки » окна « Проверка данных ».
• Щелкните раскрывающееся меню под текстом « Разрешить ».
• Должно открыться контекстное меню, содержащее несколько форматов проверки. Выберите формат списка из этого контекстного меню.

• Вы не можете вносить какие-либо изменения под текстом Data .
• Под текстом Source необходимо ввести источник элементов списка, которые будут отображаться в раскрывающемся списке.
• Введите адрес при выборе источника на другом листе, в книге или в файле Excel.
• В качестве альтернативы, чтобы выбрать источники данных из того же рабочего листа, просто щелкните один раз пустое поле источника. Затем определите диапазон ячеек с помощью курсора.

• Установите флажок Игнорировать пустые параметры и параметры раскрывающегося списка в ячейке . Вы можете не увидеть значок раскрывающейся стрелки в проверенных ячейках, если вы снимите флажок с раскрывающегося списка «В ячейке».
• Нажмите OK , и ваш раскрывающийся список готов.

Создание динамического раскрывающегося списка Excel

В таком выпадающем списке при выборе ввода в одном выпадающем списке автоматически меняются доступные данные для второго выпадающего списка. Вот шаги, чтобы попробовать:

• Соберите список предметов, как показано на изображении ниже. Вы также можете импортировать элементы списка из другой книги или файла.

• Преобразуйте названия городов в именованные диапазоны . Для этого выберите города под страной.

• Нажмите на поле « Имя» и введите точное название страны, как оно показано в заголовках.
• Если вы пытаетесь использовать свои данные, вы можете выполнить тот же шаг, чтобы преобразовать список элементов под заголовком в именованные диапазоны .
• Создайте основной раскрывающийся список, используя название страны из диапазона ячеек от D1 до G1 .

• Для столбца « Выберите город » начните создавать раскрывающийся список, как обычно. В источнике введите следующую формулу. Идея состоит в том, чтобы использовать формулу ДВССЫЛ со смешанной ссылкой для раскрывающегося списка стран, которая равна $A2 .

 =INDIRECT($A2)

• Нажмите « ОК» , чтобы завершить шаг. Ваш двухуровневый раскрывающийся список готов.

В этом раскрывающемся списке, если вы выберете США в качестве страны, вы получите только города США под заголовком столбца « Выбрать город ».

Заключительные слова

Excel — отличный инструмент для ввода данных и управления числовыми данными. Однако опечатки могут сильно затруднить ввод данных в Excel. Следовательно, вам необходимо использовать любой из вышеперечисленных методов, чтобы исключить человеческие ошибки или опечатки при вводе данных.

Вы можете следовать простому методу Excel или зависимому раскрывающемуся списку. Просто посмотрите на свой процесс ввода данных или план сбора данных, чтобы узнать, какой подход использовать. Вы можете использовать эту функцию практически в любой ячейке.

Вас также могут заинтересовать некоторые бесплатные шаблоны Excel для личного бюджета.