5 способов создать раскрывающийся список в ячейке Excel
Одной из самых полезных функций при вводе данных является возможность использования выпадающего списка. Он позволяет выбрать значение из предопределенного списка и позволяет вводить только те данные, которые соответствуют вашим требованиям. Мы покажем вам несколько простых способов создания выпадающих списков в Excel. Мы также увидим более сложные методы, основанные на динамических диапазонах и использующие данные из других таблиц.
- Зачем нужен выпадающий список?
- 1 — Самый быстрый способ.
- 2 — Используйте меню.
- 3 — Создайте элемент управления.
- 4 — Активный контроль X
- 5 — Список с автозаполнением
- Способ 1. Указать заведомо крупный шрифт.
- Способ 2. Применить именованный диапазон.
- Способ 3. Список на основе «умной» таблицы.
Зачем нужен выпадающий список?
Часто бывает так, что в какой-то из столбцов вашей таблицы нужно ввести одни и те же повторяющиеся значения. Например, имена сотрудников, названия активов. Что может случиться? Конечно, в первую очередь будут ошибки ввода. Ведь человеческий фактор никто не отменял. Что нам угрожает? Например, когда мы решили посчитать, сколько заказов выполнил каждый из менеджеров, оказалось, что имён больше, чем сотрудников. Затем приходится искать ошибки, исправлять их и повторять расчет заново.
И конечно же, набирать все время одни и те же слова руками — бессмысленная работа и пустая трата времени. Здесь на помощь приходят выпадающие списки. При нажатии выпадает список предопределенных значений, только одно из которых необходимо указать.
Важно то, что теперь вы не будете входить, а будете выбирать их мышкой или клавиатурой. Это значительно ускоряет работу, а также обеспечивает защиту от случайных ошибок. Больше не нужно проверять, что мы вводим в таблицу.
1 — Самый быстрый способ.
Как проще всего добавить выпадающий список? Просто щелкните правой кнопкой мыши пустую ячейку под столбцом данных, затем выберите команду контекстного меню «Выбрать из раскрывающегося списка». Или вы можете просто встать на нужное место и нажать комбинацию клавиш Alt + стрелка вниз. Появится упорядоченный список введенных ранее уникальных значений.
Метод не работает, если нашу ячейку и столбец с записями разделяет хотя бы одна пустая строка, или если вы хотите ввести что-то, что еще не было введено выше. Это хорошо видно на нашем примере.
2 — Используем меню.
Давайте рассмотрим небольшой пример, где нам нужно постоянно вводить в таблицу одни и те же названия товаров. Напишите в графе данные, которые мы будем использовать (например, названия продуктов). В нашем примере в диапазоне G2:G7.
Выберите ячейку таблицы (у вас может быть несколько одновременно), в которой вы хотите использовать ввод из предопределенного списка. Затем в главном меню выберите вкладку Data – Validation… (Данные – Проверка). Затем нажмите «Тип данных (разрешить)» и выберите параметр «Список». Ставим курсор в поле Источник и вводим адреса с эталонными значениями элементов, в нашем случае G2:G7. Здесь также рекомендуется использовать абсолютные ссылки (чтобы установить их, нажмите клавишу F4).
Преимуществом здесь является возможность установить подсказку и сообщение об ошибке, если вы хотите вручную изменить автоматически вставленное значение. Для этого есть вкладки Входное сообщение и Предупреждение об ошибке).
Вы также можете использовать именованный диапазон в качестве источника.
Например, диапазон I2:I13, содержащий названия месяцев, можно назвать «месяцами». Затем имя можно ввести в поле «Источник».
Кроме того, как исходный, так и обычный диапазон ячеек и именованный диапазон могут находиться на других листах вашей книги.
Но вы можете не использовать диапазоны или ссылки, а просто определить параметры непосредственно в поле «Источник». Например, напишите туда —
Не совсем
Используйте точку с запятой, запятую или другой символ, который вы установили в качестве разделителя элементов, для разделения значений. (См. Панель управления — Часы и регион — Форматы — Дополнительно — Числа.)
3 — Создаем элемент управления.
Вставим в лист новый объект: элемент управления «Поле со списком» с последующей его привязкой к данным в листе Excel. Мы делаем:
- Откройте вкладку Разработчик. Если его не видно, то в Excel 2007 нужно нажать кнопку Office — Параметры — Показать вкладку разработчика на ленте флажок (Кнопка Office — Параметры — Показать вкладку разработчика на ленте) или в версии 2010 -2013 правой кнопкой мыши ленты, выберите команду «Настроить ленту» и активируйте вкладку «Разработчик», установив флажок.
- Найдите нужную иконку среди элементов управления (см рисунок ниже).
Вставив элемент управления в электронную таблицу, щелкните его правой кнопкой мыши и в появившемся меню выберите «Формат объекта». Далее указываем диапазон ячеек, в которых записаны допустимые значения для ввода. В поле «Ссылка на ячейку» укажите, куда именно поместить результат. Важно отметить, что этим результатом будет не само значение указанного нами диапазона, а только его порядковый номер.
Но нам нужно не это число, а слово, которое ему соответствует. Мы используем функцию ИНДЕКС. Он позволяет найти одно из них в списке значений по его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).
Запишем формулу в F3, как показано на рисунке:
=ИНДЕКС(F5:F11,F2)
Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.
Отметим также, что здесь мы не привязаны к какому-то конкретному месту в таблице. Такой список удобен в использовании, так как его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого во вкладке «Разработчик» нужно активировать режим конструктора.
4 — Элемент ActiveX
Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже, из раздела «Элементы управления ActiveX».
Определяем список допустимых значений (1). Обратите внимание, что здесь вы можете выбрать несколько столбцов для одновременного отображения. Затем выбираем направление, в котором будет вставлена нужная позиция списка (2), указываем количество столбцов, которые будут использоваться в качестве исходных данных (3) и номер столбца, из которого будет производиться выбор для вставки в листе списка (4). Если указать столбец номер 2, то в А5 будет вставлена не фамилия, а должность. Вы также можете указать количество строк для отображения в списке. Значение по умолчанию — 8. Остальные можно двигать мышкой (5).
Этот метод сложнее предыдущего, но он возвращает сразу значение, а не его число. Так что нет необходимости в промежуточной ячейке и обрабатывать ее с помощью ИНДЕКС -. Я нахожу этот список намного проще в использовании.
5 — Список с автозаполнением
Задача: Создать список, в который будут автоматически добавляться значения заданного динамического диапазона. Если в диапазон будут внесены изменения, сразу изменится набор значений по умолчанию. Здесь нет необходимости корректировать формулы или настройки.
Вот как будет выглядеть функция автозаполнения на простом примере:
Способ 1. Укажите заведомо большой источник.
Самый простой и незамысловатый трюк. Сначала действуем по обычному алгоритму действий: в меню выбираем на вкладке Data — Validation… (Данные — Проверка). В списке Тип данных (Разрешить) выберите параметр Список (Список). Поместите курсор в поле Origin. Мы резервируем набор с большим запасом в списке: например, до строки 55, хотя у нас занято только 7. Не забудьте поставить галочку «Игнорировать пустое…». Тогда ваш «пул» пустых значений вас не побеспокоит.
На самом деле самый простой способ, но не слишком удобный. Ведь зарезервированное место может закончиться…
Конечно, вы также можете указать весь столбец в качестве источника:
=$А:$А
Но обработка такого большого количества ячеек может немного замедлить вычисления.
Способ 2. Применяем именованный диапазон.
Именованный диапазон отличается от обычного диапазона тем, что ему присвоено конкретное имя. Работать с ним гораздо проще, так как не нужно вводить ссылку, а просто указать ее название. Давайте посмотрим на небольшой пример.
Столбец A содержит имена сотрудников, которых мы введем. Список может быть сколь угодно длинным. Мы хотим, чтобы каждая новая запись была включена в раскрывающийся список без каких-либо дополнительных действий с нашей стороны.
Выделим список имен A2:A10, который у нас есть. Затем мы присвоим ему имя, заполнив поле «Имя», расположенное слева от строки формул. Создадим список значений в C2. В качестве источника для этого укажем выражение
=имя
Недостаток работы с таким списком в том, что новые значения нельзя будет просто добавить в конец используемого списка, а они останутся за пределами упомянутого диапазона. Их нужно будет вставить в него с помощью вставки пустой строки.
Список также может быть отсортирован для удобства использования.
Основным недостатком использования такого списка является то, что используемый нами диапазон является статическим. Его размер нельзя изменить автоматически. Согласитесь, не очень удобный и технологичный способ. Слишком много ручных действий.
Теперь давайте продолжим и посмотрим, как мы можем работать с динамическим диапазоном, который автоматически подстраивается под входные значения.
Способ 3. Список на основе «умной» таблицы.
С 2007 года электронная таблица Excel перестала быть просто набором строк и столбцов. Если просто организовать показатели в привычном для нас табличном виде, то вы их не будете считать таблицей. Есть специальный формат, после которого диапазон начинает вести себя как единое целое, приобретая ряд интересных свойств. В частности, он начинает отслеживать собственные размеры, динамически изменяясь по мере соответствия данных.
Таким образом можно преобразовать любой набор значений в таблице. Например, А1:А8. Выделите их мышкой. Затем преобразовать в таблицу с помощью меню Пуск — Форматировать как таблицу (Start — Format as table). Указывает, что в первой строке у вас есть имя столбца. Это будет голова вашего стола. Внешний вид может быть любым: это всего лишь внешнее оформление и ни на что другое не влияет.
Как было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свой размер при добавлении в нее информации. Если вы введете что-то в строку под ним, он сразу прикрепит это к себе. Поэтому можно просто добавлять новые значения. Например, введите слово «кокос» в формате A9, и таблица сразу расширится до 9 строк.
Поэтому можно организовать автоматическое обновление набора информации, используемой в списке, если используется содержимое любого столбца «умной» таблицы.
Остается только обозначить его как источник. Проблема в том, что программа как источник в списке не понимает выражения вида
=Таблица1[Столбец1]
и не считает это формулой. Хотя в обычных выражениях на листе в вашей книге это будет работать нормально. Эта конструкция обозначает ссылку на первый столбец. Но почему-то игнорируется в поле «Источник.
Чтобы использовать «умную таблицу» в качестве источника, мы должны пойти на небольшой трюк и использовать функцию ДВССЫЛ. Эта функция преобразует текстовую переменную в обычную ссылку.
Теперь формула будет выглядеть так:
=ДВССЫЛ(«Таблица5[Продукт]»)
Table5 — это имя, автоматически присвоенное «умной таблице». Ваш может быть другим. Во вкладке меню «Дизайн» вы можете изменить имя по умолчанию на свое собственное (но без пробелов!). Используя его, мы можем ссылаться на нашу таблицу на любом листе рабочей книги.
«Продукт» — это название нашего первого и единственного столбца, учитывая его заголовок.
Не забудьте также заключить все выражение в кавычки, чтобы обозначить его как текстовую переменную.
Теперь, если вы добавите еще один фрукт (например, кокос) в A9, он сразу автоматически появится в нашем списке. То же самое будет, если мы что-то удалим. Исправлена задача автоматического увеличения выпадающего списка значений.
Мы надеемся, что теперь вы сможете вводить часто повторяющиеся данные в таблицу с помощью списков без ошибок.
А вот еще немного полезной информации для вас:
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, позволяющего выбрать значение из предопределенного списка. Но как только вы начнете применять это к своим электронным таблицам,…
Создать выпадающий список в Excel с помощью формул — Задача: Создать выпадающий список в Excel, чтобы все новые значения автоматически попадали в него. Сделаем это с помощью формул, чтобы этот метод можно было использовать не только в