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