Чтобы создать раскрывающийся список в Microsoft Excel, выделите ячейки, откройте Данные > Проверка данных, выберите Список в поле Тип данных и введите варианты через запятую или выделите диапазон ячеек. Нажмите ОК — при выделении каждой ячейки будет появляться стрелка. Изменить или удалить список можно в том же диалоговом окне.
Раскрывающиеся списки помогают вводить данные в таблицу единообразно: вы выбираете из заранее заданных вариантов, а не вводите их вручную. Они удобны для отслеживания статусов проектов, распределения задач, учета информации о клиентах и упорядочивания других данных с повторяющимися значениями. В этом руководстве вы узнаете, как создать раскрывающийся список, изменить его варианты, удалить его без потери данных и сделать один список зависимым от другого.
Как создать раскрывающийся список в Excel
Чтобы создать раскрывающийся список в Excel, добавьте к нужным ячейкам правило проверки данных с типом «Список». Вся настройка выполняется в одном диалоговом окне. Для примера возьмем столбец «Статус» в таблице задач на 200 строк:
1. Выделите ячейки, в которых должен быть список, например B2:B200.
2. Откройте вкладку «Данные» и нажмите «Проверка данных».
3. Перейдите на вкладку «Параметры».
4. В поле «Тип данных» выберите «Список».
5. Заполните поле «Источник»: введите значения через запятую (Открыта,В работе,Готово) или щелкните в поле и выделите ячейки со списком.
6. Оставьте флажок «Список допустимых значений». Флажок «Игнорировать пустые ячейки» тоже оставьте, если пустые ячейки допустимы.
7. Нажмите «ОК».
8. Выделите одну из ячеек и нажмите стрелку, чтобы проверить список.
Рядом с вкладкой «Параметры» есть еще две необязательные вкладки. «Подсказка по вводу» показывает подсказку, когда кто-то выделяет ячейку. «Сообщение об ошибке» определяет, что произойдет, если кто-то введет значение не из списка.
Совет: храните длинные списки на отдельном листе. Поместите значения на отдельный лист (например, «Списки») и укажите этот диапазон в поле «Источник». Список не будет мешать, а редактировать его можно в одном месте, а не в диалоговом окне. Microsoft рекомендует отформатировать источник как таблицу Excel: выделите в нем ячейку и нажмите Ctrl+T.
В длинной таблице закрепление строки заголовков позволяет видеть названия столбцов, пока вы заполняете ячейки со списком ниже.
Хотите выполнить ту же задачу в альтернативном офисном пакете? В MobiSheets раскрывающийся список добавляется прямо из меню «Вставка».
Откуда брать значения для списка
Раскрывающиеся списки в Excel могут использовать значения, введенные вручную, диапазон ячеек или источник, который обновляется по мере роста данных. Подходящий вариант зависит от того, как часто вам нужно будет менять доступные варианты.
Для коротких неизменных списков проще всего ввести значения напрямую. Для длинных списков удобнее ссылаться на ячейки — так их проще менять в будущем.
Перед созданием списка стоит удалить повторяющиеся значения из исходных данных, чтобы один и тот же вариант не появлялся несколько раз.
Как изменить раскрывающийся список
Чтобы изменить раскрывающийся список в Excel, откройте Проверку данных и обновите поле Источник. Порядок действий зависит от того, ввели ли вы варианты вручную или выбрали их из диапазона ячеек.
Если вы ввели значения вручную:
1. Выделите ячейку с раскрывающимся списком.
2. Откройте Данные > Проверка данных.
3. Перейдите на вкладку Параметры.
4. Измените значения в поле Источник, сохранив запятые между ними.
5. Нажмите ОК, чтобы сохранить изменения.
Например, можно заменить Ожидает,Одобрено,Отклонено на Ожидает,На проверке,Одобрено,Отклонено без создания нового списка.
Если список использует диапазон ячеек:
1. Найдите ячейки с исходными вариантами.
2. Измените, замените или удалите значения прямо в этих ячейках.
3. Если вы добавили значения за пределами исходного диапазона, откройте Проверку данных и обновите поле Источник, чтобы включить их.
4. Нажмите ОК.
Если одни и те же параметры проверки используются в нескольких ячейках, установите флажок Распространить изменения на другие ячейки с тем же условием в диалоговом окне «Проверка данных».
Так все соответствующие раскрывающиеся списки обновятся одновременно, и вам не придется менять каждый по отдельности.
Для списков, которые регулярно меняются, таблица Excel упрощает поддержку: новые строки могут автоматически становиться частью источника списка.
Как удалить раскрывающийся список
Раскрывающийся список в Excel можно удалить, не стирая значения, уже выбранные в его ячейках. Для этого очистите правило проверки данных, а не содержимое ячеек.
Выполните следующие действия:
1. Выделите ячейку или диапазон с раскрывающимся списком.
2. Откройте Данные > Проверка данных.
3. Перейдите на вкладку Параметры.
4. Нажмите Очистить все.
5. Нажмите ОК для подтверждения.
Стрелка раскрывающегося списка исчезнет, но значения в ячейках останутся без изменений. После этого в эти ячейки можно вводить новые значения без ограничений прежнего списка.
Это удобно, когда таблице больше не нужны ограничения на ввод, но вы хотите сохранить уже внесенную информацию.
Как создать зависимый раскрывающийся список
Зависимый раскрывающийся список меняет доступные варианты в зависимости от значения, выбранного в другой ячейке. Например, выбор страны в первом списке может определять, какие города появятся во втором.
Такой список создается с помощью именованных диапазонов и функции ДВССЫЛ.
Пример: раскрывающиеся списки стран и городов
Предположим, вы создаете таблицу, в которой пользователи сначала выбирают страну, а затем город.
Шаг 1. Подготовьте исходные данные.
Создайте два столбца с вариантами городов:
Шаг 2. Присвойте имя каждому диапазону.
Выделите болгарские города без заголовка и введите Болгария в поле «Имя». Повторите то же для французских городов и назовите диапазон Франция.
Имена диапазонов должны совпадать с вариантами в первом раскрывающемся списке.
Шаг 3. Создайте раскрывающийся список стран.
Выделите ячейку A2 и откройте Данные > Проверка данных.
В поле Тип данных выберите Список и введите в поле Источник:
Болгария,Франция
Нажмите ОК.
Шаг 4. Создайте зависимый список городов.
Выделите ячейку B2 и снова откройте Проверку данных.
Выберите Список и введите эту формулу в поле Источник:
=ДВССЫЛ(A2)
Нажмите ОК.
Теперь, если выбрать Болгария в ячейке A2, список в B2 будет содержать София, Пловдив и Варна. Если изменить A2 на Франция, в списке городов появятся Париж, Лион и Марсель.
Функция ДВССЫЛ преобразует текст из ячейки A2 в ссылку на соответствующий именованный диапазон.
Этот способ подходит для категорий товаров, отделов, местоположений и других связанных данных. Для работы со значениями, выбранными из списков, можно также использовать формулы и функции электронных таблиц.
Примечание. При изменении первого выбора варианты во втором списке обновляются, но ранее выбранное значение во второй ячейке автоматически не очищается.
Проблемы с раскрывающимися списками и их решение
Большинство проблем с раскрывающимися списками в Excel связано с неправильными параметрами проверки данных или с диапазоном-источником, который больше не соответствует данным. Вот четыре распространенные проблемы и способы их решения.
Стрелка раскрывающегося списка не появляется. Обычно Excel показывает стрелку, только когда выделена ячейка со списком. Если стрелки по-прежнему нет, откройте Данные > Проверка данных > Параметры и убедитесь, что флажок Список допустимых значений установлен.
Также проверьте, что в поле Тип данных выбрано значение Список.
В списке нет новых элементов. Если вы добавили значения ниже исходного диапазона-источника, Excel может не включить их автоматически.
Откройте Проверку данных и расширьте диапазон в поле Источник, чтобы он охватывал новые значения. Таблица Excel помогает поддерживать растущие списки в актуальном состоянии.
Excel не принимает значения, которые вы вводите в ячейку. Возможно, параметры на вкладке Сообщение об ошибке запрещают ввод значений не из списка. Откройте Проверка данных > Сообщение об ошибке, чтобы проверить параметры. Вы можете изменить реакцию Excel на недопустимые значения или дополнить исходный список недостающим значением.
Раскрывающийся список исчезает после вставки данных. Если вставить целую ячейку поверх ячейки с проверкой, ее параметры проверки данных могут быть заменены.
Чтобы этого избежать, вставляйте только значения при обновлении содержимого ячейки. Если правило уже перезаписано, придется заново настроить параметры проверки данных.
Альтернатива: MobiSheets
MobiSheets — это редактор таблиц из пакета MobiOffice, доступной альтернативы Microsoft Office. В нем можно создавать раскрывающиеся списки и управлять ими с помощью специального инструмента, редактировать записи и применять правила к нескольким ячейкам. Раскрывающиеся списки можно использовать вместе с другими параметрами форматирования ячеек и ввода данных, чтобы упорядочить содержимое таблицы.
В MobiSheets для Windows выполните следующие действия:
1. Выделите ячейку, в которую нужно вставить список.
2. Откройте Вставка > Раскрывающийся список….
3. Справа откроется панель Изменить проверку данных, где в поле Условия проверки уже выбран вариант Раскрывающийся список.
4. Появятся две начальные записи — Вариант 1 и Вариант 2, у каждой есть своя цветная метка.
5. Переименуйте записи в соответствии с нужными вариантами. Записи можно перетаскивать, чтобы изменить порядок, удалять с помощью значка корзины или добавлять новые кнопкой Добавить запись.
6. Проверьте поле Применяется к, чтобы увидеть выбранный диапазон, например Sheet1!A1. Нажмите Добавить диапазон, чтобы распространить правило на другие ячейки.
7. Нажмите Готово, чтобы сохранить раскрывающийся список.
Параметры Игнорировать пустые ячейки и Список допустимых значений включены по умолчанию. На панели также есть вкладки Подсказка по вводу и Сообщение об ошибке. Удалить существующее правило можно кнопкой Удалить правило.
Другие правила проверки доступны в меню Данные > Проверка данных.
Проверка данных — это функция Premium в MobiSheets. Если вы ищете альтернативу Microsoft Excel, MobiSheets позволяет работать с таблицами привычным способом.
Чтобы использовать MobiSheets вместе с другими приложениями MobiOffice, скачайте пакет и выберите подходящий тарифный план.
Часто задаваемые вопросы
Выделите ячейки, в которых нужен список, и откройте Данные > Проверка данных > Параметры. В поле Тип данных выберите Список и введите варианты в поле Источник через запятую или выделите диапазон, который их содержит. Оставьте флажок Список допустимых значений и нажмите ОК.
Выделите ячейку со списком и откройте Данные > Проверка данных. Если вы вводили варианты вручную, измените их в поле Источник. Если список ссылается на ячейки, обновите значения в них или расширьте диапазон. Чтобы обновить все соответствующие списки, используйте флажок Распространить изменения на другие ячейки с тем же условием.
Выделите ячейки со списком, откройте Данные > Проверка данных и нажмите Очистить все на вкладке Параметры. Подтвердите кнопкой ОК. Excel удалит правило проверки и стрелку раскрывающегося списка, но сохранит уже введенные в ячейки значения.
Обычно стрелка появляется, только когда вы выделяете ячейку с раскрывающимся списком. Если вы ее по-прежнему не видите, откройте Данные > Проверка данных > Параметры и убедитесь, что флажок Список допустимых значений установлен. Также проверьте, что у ячейки есть правило проверки, в котором в поле Тип данных выбрано значение Список.
Да. Зависимый раскрывающийся список можно создать с помощью именованных диапазонов и функции ДВССЫЛ. Назовите исходные диапазоны так же, как варианты в первом списке, а затем введите в поле Источник второго списка формулу, например =ДВССЫЛ(A2), чтобы отображались соответствующие варианты.
В Excel можно использовать условное форматирование, чтобы менять оформление ячейки в зависимости от выбранного значения. При этом окрашивается сама ячейка, а не отдельные варианты в стандартном списке проверки данных. В MobiSheets у каждой записи есть своя цветная метка на панели Изменить проверку данных.
Заключение
Раскрывающийся список в Excel помогает вводить данные единообразно и меньше печатать вручную. Для коротких списков обычно достаточно ввести варианты прямо в окне «Проверка данных». Для часто меняющихся данных удобнее использовать диапазон ячеек или таблицу Excel — так их проще обновлять.
Если один выбор должен определять следующий, используйте зависимые списки. А если ваши задачи изменятся, правило проверки можно отредактировать или удалить, не начиная работу заново.
Если вам нужен другой способ работы со списками в таблицах, попробуйте MobiSheets в составе пакета MobiOffice.
