Как настроить раскрывающийся список в Excel?
Раскрывающиеся списки в Excel позволяют вам выбирать из предопределенного списка вариантов. Они полезны для ограничения ввода данных определенными вариантами и обеспечения согласованности в ваших электронных таблицах. Excel предоставляет некоторые базовые параметры настройки для раскрывающихся списков, но вы также можете изменять их в дальнейшем, чтобы они соответствовали вашим конкретным потребностям. В этом подробном руководстве мы рассмотрим все, что вам нужно знать о настройке раскрывающихся списков в Excel.
Зачем использовать раскрывающиеся списки
Раскрывающиеся списки предлагают несколько ключевых преимуществ:
- Обеспечение проверки данных — пользователи могут выбирать только из предоставленных вами вариантов
- Улучшение согласованности данных — стандартные параметры предотвращают опечатки/неправильные записи
- Упрощение ввода данных — пользователям не нужно вводить или запоминать параметры
- Экономия времени — ввод данных из списка выполняется быстрее, чем при наборе
- Уменьшение количества ошибок — за счет устранения неправильных или повторяющихся записей
- Улучшение организации электронных таблиц — записи выглядят более аккуратно в однородных раскрывающихся списках
По этим причинам настраиваемые раскрывающиеся списки идеально подходят для управления информацией во всех видах рабочих книг и шаблонов Excel.
Раскрывающийся список Основы
Прежде чем изучать методы настройки, давайте рассмотрим основы работы со стандартными раскрывающимися списками в Excel:
- Перейдите на вкладку Данные и нажмите Проверка данных
- В поле Разрешить выберите Список и настройте входные данные Источник
- Записи можно вводить с запятыми, перечислять или помещать на другой лист
- Установите флажок «Раскрывающийся список в ячейке», чтобы добавить стрелку раскрывающегося списка
- Раскрывающийся список появится в выбранных ячейках
Это вставит базовый предустановленный раскрывающийся список. Теперь давайте рассмотрим способы их дальнейшей настройки.
Изменение цвета раскрывающегося списка
Чтобы сделать раскрывающиеся списки выделяющимися, вы можете применить пользовательское форматирование, например заливку цветом. Вот как это сделать:
- Выберите ячейки с раскрывающимся списком
- Перейдите на вкладку Главная и откройте раскрывающийся список Цвет заливки
- Выберите нужный цвет заливки
Это применит выбранный цвет только к ячейкам, содержащим раскрывающийся список. Вы также можете использовать условное форматирование для изменения цвета заливки на основе значений ячеек.
Изменение шрифтов раскрывающегося списка
Улучшение шрифтов раскрывающегося списка — еще один простой способ настроить их внешний вид:
- Выберите ячейки раскрывающегося списка
- Перейдите на Главная > Шрифт и настройте шрифт, размер, цвет и т. д.
- Убедитесь, что шрифты читаются вашими пользователями
Используйте жирные или цветные шрифты, чтобы выделить раскрывающиеся списки на ваших рабочих листах. Не забудьте также сохранить легко читаемый размер.
Сортировка раскрывающихся списков
Параметры списка будут отображаться в том порядке, в котором вы их вводите по умолчанию. Чтобы переупорядочить варианты в алфавитном порядке:
- Перейдите в раздел «Проверка данных» и отредактируйте поле «Источник»
- Добавьте эту формулу перед списком: =SORT(
- Закройте этим после списка: )
Теперь ваши параметры раскрывающегося списка всегда будут оставаться отсортированными, даже при добавлении/удалении вариантов.
Создание зависимых раскрывающихся списков
Зависимые раскрывающиеся списки имеют отфильтрованные варианты, которые изменяются в зависимости от выбора в другой ячейке. Вот как их настроить:
- Введите основные категории в один раскрывающийся список (A2)
- Во втором раскрывающемся списке (B2) используйте эту исходную формулу:
- =INDIRECT(A2)
- Назовите списки на другом листе для динамической ссылки
Это заставит варианты выбора B2 фильтроваться в соответствии с категорией, выбранной в A2.
Отключить сортировку раскрывающихся списков
По умолчанию поведение Excel пересортирует раскрывающиеся списки в алфавитном порядке. Чтобы предотвратить это:
- Выберите ячейку проверки данных
- Перейдите в раздел «Проверка данных» и введите эту формулу для источника:
- =$A$1:$A$100
- Замените диапазон фактическими ячейками
Теперь вы можете полностью контролировать порядок раскрывающихся списков.
Ограничьте длину раскрывающегося списка
Длинные раскрывающиеся списки со слишком большим количеством вариантов выбора могут быть сложными для навигации. Чтобы задать максимальную видимую длину:
- Выберите ячейки проверки
- Перейдите в раздел «Проверка данных» и щелкните вкладку «Оповещение об ошибке».
- В поле «Стиль» выберите «Остановить» и введите максимальную длину.
Это ограничит раскрывающийся список указанным вами количеством видимых вариантов.
Длина раскрывающегося списка с автоматическим раскрытием
Кроме того, вы можете настроить раскрывающиеся списки так, чтобы они автоматически раскрывались, чтобы отображать все варианты при прокрутке. Вот как это сделать:
- Выберите ячейки проверки данных
- Перейдите в раздел «Проверка данных» и щелкните вкладку «Оповещение об ошибке»
- В поле «Стиль» выберите «Предупреждение» и введите следующую формулу:
- =IF(ROW(INDIRECT(“A1:A”&COUNTA(A:A)))
Теперь ваши раскрывающиеся списки будут динамически увеличиваться в длине по мере прокрутки списка.
Требовать выбор раскрывающегося списка
Чтобы предотвратить появление пустых записей, вы можете сделать выбор обязательным:
- Выделив ячейки, перейдите в раздел «Данные» > Проверка данных
- На вкладке «Настройки» установите флажок «Игнорировать пустые ячейки».
- На вкладке «Вводное сообщение» введите пользовательскую ошибку, если ячейка пуста.
Это гарантирует, что пользователи выберут вариант вместо того, чтобы принимать пустую ячейку.
Создание раскрывающихся списков с одним выбором
По умолчанию разрешено несколько вариантов выбора. Чтобы ограничиться одним выбором:
- Выбрав ячейки, перейдите в раздел Данные & gt; Проверка данных
- На вкладке Настройки измените «Разрешить» на Список и установите флажок «Раскрывающийся список в ячейке»
- Установите «Показывать сообщение ввода при выборе ячейки» на Нет
Теперь можно выбрать только один вариант за раз, что улучшает согласованность данных.
Создание нередактируемых раскрывающихся списков
Вы можете запретить ручное редактирование текста раскрывающегося списка:
- Выберите ячейки проверки
- Перейдите в раздел Данные & gt; Проверка данных & gt Вкладка «Настройки»
- Установите флажок «Отключить ввод данных»
Это заблокирует ячейки, чтобы принимать только варианты из раскрывающегося списка.
Создание раскрывающихся списков
Для раскрывающегося списка с множественным выбором в стиле списка:
- Перейдите на вкладку «Разработчик» и нажмите «Вставить» > Список
- Щелкните правой кнопкой мыши по списку и выберите «Управление форматом».
- В поле «Диапазон ввода» добавьте исходные ячейки.
- Установите связанную ячейку для хранения выбранных значений.
Списки поддерживают нажатие клавиши Shift для множественного выбора и идеально подходят для длинных списков.
Изменение ссылок на ячейки раскрывающегося списка
Чтобы создать раскрывающийся список на основе другого диапазона ячеек:
- Выберите ячейки проверки.
- Перейдите в меню Данные > Проверка данных > Вкладка «Настройки»
- Измените поле «Источник» на новую ссылку на ячейку
Используйте это для динамического изменения вариантов раскрывающегося списка на основе именованных диапазонов или таблиц.
Каскадные раскрывающиеся списки
Каскадные раскрывающиеся списки фильтруют на основе выбора в другой раскрывающейся ячейке. Чтобы создать их:
- Введите основные категории в ячейку A2
- В ячейку B2 добавьте формулу проверки данных:
- =INDIRECT(A2)
- Назовите списки подкатегорий на другом листе
Теперь в ячейке B2 будут отображаться подкатегории, соответствующие выбору из раскрывающегося списка A2.
Ограничить варианты раскрывающегося списка
Чтобы ограничить выбор подмножеством всех вариантов:
- Добавьте список в Источник, как обычно
- На вкладке Настройки установите флажок «Показать список в»
- Введите отфильтрованный диапазон строк, например A1:A5
Теперь будут отображаться только эти пять вариантов, даже при более длинном списке Источник.
Начальное значение раскрывающегося списка по умолчанию
Чтобы предварительно выберите вариант по умолчанию вместо первого варианта:
- В первой строке данных введите свой вариант по умолчанию
- Выберите ячейку и перейдите в раздел Данные > Проверка данных
- На вкладке Настройки установите флажок «Игнорировать пустые»
Теперь для новых записей по умолчанию будет использоваться ваш предустановленный вариант, а не первый.
Очистить дубликаты раскрывающихся списков
Чтобы удалить лишние копии одного и того же варианта:
- Выберите ячейки с проверкой данных
- Перейдите в раздел Данные > Инструменты для данных > Удалить дубликаты
- Отметьте «Мои данные имеют заголовки», если список имеет заголовки
Это аккуратно удалит все дублирующиеся параметры проверки.
Динамические диапазоны проверки данных
Для раскрывающихся списков, которые автоматически обновляются новыми данными:
- Добавьте свой список в таблицу с заголовками столбцов
- Назовите диапазон таблицы (Table1)
- В исходных ячейках используйте именованный диапазон: =Table1
Теперь, когда вы добавляете данные в таблицу, раскрывающийся список обновит свой выбор.
Проверка данных с помощью формул
Более сложные правила проверки данных можно создавать с помощью формул. Например:
- Выберите ячейку(и) для проверки
- Перейдите в Data > Проверка данных > Вкладка «Настройки»
- Введите пользовательскую формулу в поле «Формула»
Вы можете ссылаться на другие ячейки, ограничивать числа или даты и многое другое.
Устранение неполадок
Если у вас возникли проблемы с раскрывающимися списками, проверьте:
- Ошибки #VALUE — Имя исходного списка написано с ошибкой или отсутствует
- Циклические ссылки — Формула ссылается на собственную ячейку
- Ошибки #REF — Удаленные ячейки ссылок все еще используются в формуле
- Ошибки синтаксиса формулы — Исправьте недопустимую логику формулы
- Отключенный список — «Игнорировать пустые» отмечен, но ячейка пустая
Исправление таких небольших ошибок часто решает проблемы проверки.
Заключение
В этой статье мы рассмотрели многочисленные способы настройки и улучшения раскрывающихся списков в Excel. Ключевые выводы включают:
- Изменения цвета, шрифта и форматирования
- Сортировка, ограничение и расширение списков
- Зависимые, каскадные и динамические раскрывающиеся списки
- Ограничение и требование выбора
- Раскрывающиеся списки с одним или несколькими вариантами
- Списки для длинных списков
- Формулы проверки данных для пользовательских правил
Раскрывающиеся списки очень полезны для управления данными электронных таблиц. С помощью этих советов вы сможете создавать идеально адаптированные раскрывающиеся списки, которые будут соответствовать всем вашим потребностям Excel.