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