Часто при создании таблицы приходится много раз вводить повторяющиеся данные. Например, фамилии сотрудников или наименования товаров. Если мы создадим такой список заранее то опция «Выпадающий список» в Excel позволит выбрать значение ячейки таблицы из этого списка. Используя опцию «Выпадающий список» мы убиваем двух зайцев. Во-первых, выпадающий список позволит не вводить одни и те же данные несколько раз. Во-вторых, уменьшится вероятность опечаток.
По окончании этого урока вы сможете:
- Создавать выпадающие списки в ячейке
Чтобы добавить для проверки вводимых данных выпадающий список элементов в ячейку или группу ячеек, существует несколько способов.
1. Первый способ
Шаг 1. Выделяем диапазон ячеек
Шаг 2. Лента Данные → группа команд Работа с данным → команда Проверка данных (так как эта статья продолжение двух предыдущих, то я не обошлась без скриншотов).
Шаг 3. Закладка «Параметры» → поле «Тип данных» выбираем «Список»
Шаг 4. В поле Источник введите элементы списка проверки Excel «Иванов Иван;Сидоров Сидор; Петров Петя; Котов Котя», разделенные точкой с запятой:
Обратите внимание, что в районе точки с запятой никаких пробелов!
| Совет | Если у вас список большой, то имеет смысл сделать его в Word, заодно проверите орфографию. Потом скопировать в буфер обмена и вставить в поле «Источник» Ctrl+V. Я так и делаю. |
Убедитесь, что выбрана опция «Список допустимых значений», чтобы стрелка раскрывающегося списка отображалась рядом с ячейкой. А теперь ОК.
А вот и результат:
| Понять и запомнить. | Будьте осторожны с опцией «Игнорировать пустые ячейки», которая активна по умолчанию. Если вы создаете раскрывающийся список на основе именованного диапазона, в котором есть хотя бы одна пустая ячейка, установка этого флажка позволит ввести любое значение в проверенную ячейку. Во многих случаях это справедливо и для формул проверки вводимых значений: если ячейка, указанная в формуле, пуста, любое значение будет разрешено в проверяемой ячейке. |
2. Второй способ
Шаг 1. На листе в свободном месте создаем перечень, который будет являться списком и активизируем ячейку А1.
Шаг 2. Лента Данные → группа команд Работа с данным → команда Проверка → данных →команда список из выпадающего меню:
В данной ситуации диалоговое окно «Проверка вводимых данных» не перекрывает список, но в других случаях может и перекрыть. Поэтому…
Шаг 3. Нажимаем на кнопку напротив поля «Источник» → выделяем диапазон будущего выпадающего списка → и еще раз нажимаем на кнопку напротив поля «Источник»:
В поле «Источник» появилась запись – «=$F$1:$F$4». Если вы делали заготовку для выпадающего списка в другом места, то запись будет соответствующая. Да, и обратите внимание, что адреса ячеек абсолютные (статья «Excel 10. Диапазон и вычисления в нем»).
После чего ОК:
Все в порядке: появилась кнопка выпадающего списка. Но вы не забыли, что мы сделали активной только одну ячейку? Значит для других ячеек проверка вводимых значений не будет работать.
Шаг 4. Курсором заполнения ячеек (статья «Excel 3. Введение в Excel») протягиваем выделение на нужный диапазон:
Можете проверить – все работает.
| Совет | Если вы сделаете большой список в Word, то сделайте каждый пункт отдельным абзацем. После копирования списка в Word смело вставляйте содержимое буфера обмена в Excel – каждый пункт списка вставиться в отдельную ячейку: |
3. Третий способ
Вспомним статью «Excel 10. Диапазон и вычисления в нем». В ней я рассказала, как создавать именованный диапазон.
Шаг 1. Создаем именованный диапазон (выделяем диапазон с данными → лента Формулы → группа команд Определенные имена → команда Присвоение имени → диалоговое окно «создание имени»):
Заносим имя в поле «Имя» (например, «ФИО») и ОК.
Шаг 2. Делаем активной ячейку А1 (или любую другую по вашему выбору)
Шаг 3. Лента Данные → группа команд Работа с данным → команда Проверка данных → диалоговое окно «Проверка вводимых значений:
- Тип данных – Список
- Источник – «=ФИО» (модно записать вручную) и ОК
Проверяем:
Распространяем:
Чем этот способ отличается от второго способа? Да тем, что мы можем добавлять в наш исходный именованный диапазон новые элементы. Правда есть небольшой нюанс. Я заполнила диапазон А1:А2 выпадающим списком. Теперь мне надо добавить еще один элемент в список-исходник (будем называть его динамическим списком).
| Понять и запомнить! | Если мы просто добавим элемент после последнего элемента динамического списка, то этот добавленный элемент будет за пределами динамического списка и никоим образом не повлияет на диапазон А1:А4! |
То есть новый элемент не добавиться в динамический список!
| Понять и запомнить! | Чтобы новый элемент добавился в динамический список, необходимо создать его внутри динамического списка |
Если ваш динамический список находится где-то очень далеко на листе и никоим образом не задевает основной список, то можете смело добавлять новую строчку внутри именованного (динамического) диапазона любым известным вам способом. Но я смоделировала ситуацию, когда динамический список находится на одних строчках с основным списком. Поэтому…
Шаг 4. Выделяем ячейку внутри именованного диапазона → лента Главная → группа команд Ячейки → команда Вставить → команда Вставить ячейки из выпадающего меню → диалоговое окно Добавить ячейки → ячейки со сдвигом вниз:
Проверяем:
| Совет | Отсортируйте динамический список – и вам легче будет ориентироваться в выпадающем списке, особенно, когда количество элементов именованного списка перевалит через десяток |
4. Выпадающий список на другом листе
Если вам необходимо создать несколько выпадающих списков, то имеет смысл выделить отдельный лист под эти списки. На небольшом видео вы можете посмотреть, как делается выпадающий список на отдельном листе:
В этом случае адрес выпадающего списка будет выглядеть так: «=Лист2!$A$1:$A$5».
Существуют еще два способа создания выпадающих списков в ячейке, но об этом попозже.
Во всех этих случаях вы просто записываете соответствующую ссылку на диапазон либо элемент таблицы в поле «Источник».
Теперь вы сможете:
- Создавать выпадающие списки в ячейке
Комментировать
Для отправки комментария вам необходимо авторизоваться.