Как сделать список данных в excel. Рекомендации как быстро сделать выпадающей список в MS Excel

Очень часто тем, кто набивает какие-то данные или просто заполняет таблицу, нужно забивать данные, которые повторяются. Чтобы не забивать одно и то же, для этих случаев у Excel есть такой инструмент – выпадающий список. Смотрим как его сделать

Делаем пошагово

Как он работает - вы заранее формируете список, а затем в определенных полях выбираете нужное значение из списка, мышкой. Экономит время и нервы.

Реализуется это просто:

• Сначала формируете сам список данных.

• Выделяете те поля, которые планируете заполнять данными.

• Затем заходите во вкладку Данные, команда Проверка данных – Тип данных (Список), в поле Источник ставите диапазон.

Если вы планируете дополнять список новыми данными, пусть диапазон включает в себя еще и энное количество ячеек ниже списка.

Альтернативный вариант – помечаете не диапазон, а имя диапазона. В этом случае избавляетесь от вероятности увеличения списка больше, чем задали в списке. Достаточно только убедиться, что новые значения, которые вы забиваете в диапазон, действительно в него включены.

• Отмечаете галочкой поле «Игнорировать пустые ячейки».

Теперь при выделении ячейки из тех, к которым была применена команда, справа будет появляться кнопка со стрелкой.

Нюанс – при таком методе в ячейку можно ввести значения, ТОЛЬКО содержащиеся в списке.

Нюансы

Выпадающий список – это свойство ячейки. И поэтому, если вы скопируете другую ячейку и вставите в ту, где есть выпадающий список, свойства ячейки заместятся и выпадающий список пропадет. Соответственно, если вам понадобится тот же выпадающий список в другой ячейку, можете просто скопировать ячейку и вставить по нужному адресу (или через специальную вставку, нужно скопировать «условия на значения»).

После заполнения полей, если вы больше не планируете ничего делать, можно удалить функцию выпадающего списка (бывает, что визуально мешает стрелка при работе с ячейками). Делается это так – выделяется тот диапазон, где выпадающий список уже не нужен, та же команда, но нажимаете уже кнопку «Очистить все». Данные при этом все сохранятся.

Второй способ

Если вы не заполняете повторящиеся данные, а хотите сделать что-то типа анкеты, т.к. таблицу, где произвольно расставлены поля со списками, то лучше воспользоваться другим способом создания выпадающего списка.

Для этого надо зайти на вкладку Разработчик (Excel 2007-2010). По умолчанию эта вкладка не показывается. Чтобы ее открыть надо зайти в Параметры (Офис/Параметры Excel/Основные/Показывать вкладку "Разработчик" на ленте).

• На вкладке "Разработчик" есть кнопка "Вставить", где есть элементы формы. Нам нужно "Поле со списком".

• Обведите мышкой контур кнопки, затем правой кнопкой мыши нажимаете на получившемся поле и выбираете "Формат объекта".

• В первом поле выбираете диапазон.
• Во втором поле можете поставить ячейку, куда будет проставляться порядковый номер элемента. Это может быть полезно, когда вы потом будете обрабатывать данные.
• Также можете поставить список элементов, которые будут показаны. В предыдущем способе автоматически дается только 8 элементов выбора, здесь хотя настройка сложнее, зато и выгод больше.

Потом нажимаете "Ок" и пользуетесь.

«Глаза боятся, а руки делают»

Элемент списка знаком нам по формам на сайтах. Удобно выбирать уже готовые значения. Например, никто не вводит месяц вручную, его берут из такого перечня. Заполнить выпадающий список в Excel можно с использованием различных инструментов. В статье рассмотрим каждый из них.

Как сделать выпадающий список в Excel

Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.

Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».


Снизу появится строка для указания источников.


Указывать информацию можно по-разному.

Сначала назначим имя. Для этого создайте на любом листе такую таблицу.

Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».

Введите имя в строку сверху.

Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним знак «=».


В любом из трех случаев Вы увидите нужный элемент. Выбор значения из выпадающего списка Excel происходит с помощью мыши. Нажмите на него и появится перечень указанных данных.

Вы узнали, как создать выпадающий список в ячейке Excel. Но можно сделать и больше.

Подстановка динамических данных Excel

Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив.

Выделите его и на вкладке «Главная» выберите любой стиль таблицы.


Обязательно поставьте галочку внизу.

Вы получите такое оформление.

Создайте активный элемент, как было описано выше. В качестве источника введите формулу

Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое.


Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.

Попробуем увеличить количество городов.


Обратная процедура - подстановка данных из выпадающего списка в таблицу Excel, работает очень просто. В ячейку, куда надо вставить выбранное значение из таблицы, введите формулу:

Адрес_ячейки

Например, если перечень данных находится в ячейке D1, то в ячейке, куда будут выведены выбранные результаты введите формулу

Как убрать (удалить) выпадающий список в Excel

Откройте окно настройки выпадающего списка и выберите «Любое значение» в разделе «Тип данных».

Ненужный элемент исчезнет.

Зависимые элементы

Иногда в Excel встречается необходимость создания нескольких перечней, когда один зависит от другого. Например, каждый город имеет несколько адресов. При выборе в первом мы должны получить только адреса выбранного населенного пункта.


В этом случае дайте имя каждому столбцу. Выделите без первой ячейки (названия) и нажмите правую кнопку мыши. Выберите «Присвоить имя».

Это будет название города.


При именовании Санкт-Петербурга и Нижнего Новгорода Вы получите ошибку, так как имя не может содержать пробелов, символов подчеркивания, специальных символов и т.д.


Поэтому переименуем эти города, поставив нижнее подчеркивание.


Первый элемент в ячейке A9 создаем обычным образом.


А во втором пропишем формулу:

ДВССЫЛ(A9)

Сначала Вы увидите сообщение об ошибке. Соглашайтесь.

Проблема в отсутствии выбранного значения. Как только в первом перечне будет выбран город, второй заработает.

Как настроить зависимые выпадающие списки в Excel с поиском

Можно использовать динамический диапазон данных для второго элемента. Это удобнее, если количество адресов будет расти.
Создадим выпадающий перечень городов. Оранжевым выделен именованный диапазон.


Для второго перечня нужно ввести формулу:

СМЕЩ($A$1;ПОИСКПОЗ($E$6;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$E$6);1)

ПОИСКПОЗ возвращает номер ячейки с выбранным в первом списке (E6) городом в указанной области SA:$A.
СЧЕТЕСЛИ считает количество совпадений в диапазоне со значением в указанной ячейке (E6).

Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском диапазона для него.

Мультивыбор

Часто нам необходимо получить несколько значений из набора данных. Можно вывести их в разные ячейки, а можно объединить в одну. В любом случае необходим макрос.
Нажмите на ярлыке листа внизу правую кнопку мыши и выберите команду «Просмотреть код».


Откроется окно разработчика. В него надо вставить следующий алгоритм.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Обратите внимание, что в строке

If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then

Следует проставить адрес ячейки со списком. У нас это будет E7.

Вернитесь на лист Excel и создайте в ячейке E7 список.

При выборе значения будут появляться под ним.

Следующий код позволит накапливать значения в ячейке.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) <> 0 And oldval <> newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

Как только Вы переведете указатель на другую ячейку, Вы увидите перечень выбранных городов. Для прочитайте эту статью.


Мы рассказали, как добавить и изменить выпадающий список в ячейку Excel. Надеемся, эта информация поможет вам.

Отличного Вам дня!

Чтобы не набирать по буквам и цифрам уже набранные ранее текстовые и числовые значения ячеек, для ускорения процесса заполнения информацией ячеек листа MS Excel и для минимизации ошибок, в том числе опечаток и орфографических, удобно применять раскрывающийся список.

Из раскрывающегося списка несколькими кликами мыши можно ввести в назначенные ячейки нужную информацию. Широко применяются раскрывающиеся списки при написании расчетных программ в Excel.

Программа MS Excel, имея очень дружественный интерфейс, предлагает пользователю несколько разных вариантов помощи при вводе в ячейки рабочего листа повторяющейся информации.

Предположим, что мы ведем базу данных поступления металлопроката на склад. В первом столбце мы указываем вид прокатного профиля.

Вариант №0 — «Элементарный».

Делая очередную запись в ячейку А9, при наборе первой буквы наименования профиля, например «Ш», Excel предлагает заполнить ячейку словом «Швеллер». После набора «Ш» достаточно нажать кнопку «Ввод» на клавиатуре – и слово будет введено в ячейку.

«Минусом» данного варианта является необходимость ввода иногда нескольких букв и невозможность заранее создать справочник наименований, ограничивающий свободу деятельности пользователя.

Переходим непосредственно к вариантам создания раскрывающихся списков.

Вариант №1 — «Простейший».

Если активировать мышью ячейку А9, нажать сочетание клавиш «Alt» «↓», то появится раскрывающийся список, содержащий все ранее введенные в этом столбце значения. Остается лишь выбрать мышью нужную запись. Вместо набора вышеуказанного сочетания клавиш можно щелчком правой кнопки мыши вызвать контекстное меню и выбрать в нем пункт «Выбрать из раскрывающегося списка…». В результате увидим тот же выпадающий список.

В данном варианте активная ячейка обязательно должна примыкать снизу к диапазону значений, а сам диапазон не должен содержать пустых ячеек!

Вариант №2 — «Простой».

Этот вариант позволяет заранее создать список (справочник) значений из которых пользователь в дальнейшем сможет выбирать необходимые записи. При этом список может быть размещен где угодно на листе (или даже на другом листе) и может быть при необходимости скрыт от пользователя.

Для того чтобы создать раскрывающийся список в этом варианте необходимо выполнить ряд последовательных шагов.

1. Создаем список возможных значений, записав их в столбец по одному в ячейку. Допустим это перечень в ячейках А2…А8.

2. Активируем ячейку, в которой необходимо поместить раскрывающийся список путем установки в нее курсора. Пусть это будет та же ячейка А9.

3. Выбираем в главном меню кнопку «Данные» – «Проверка…».

4. В выпавшем окне «Проверка вводимых значений» выбираем вкладку «Параметры».

5. В поле «Тип данных:» из раскрывающегося списка (подобного тому, который мы создаем) выбираем значение «Список».

6. В появившемся поле «Источник:» указываем диапазон, содержащий список возможных значений.

7. Устанавливаем (если он не установлен по умолчанию) флажок «Список допустимых значений» и нажимаем кнопку «ОК».

Раскрывающийся список готов. Его можно скопировать как формулы в любое количество ячеек!

Вариант №3 — «Сложный».

Этот вариант создания раскрывающегося списка, не смотря на свое название «Сложный», по сути таковым не является. Для создания выпадающего списка в нем используется элемент «Поле со списком» панели инструментов «Формы».

Создадим раскрывающийся список этим способом.

1. Создаем список-справочник в ячейках А2…А8.

2. Выбираем в главном меню кнопку «Вид» – «Панели инструментов» – «Формы».

3. В появившейся панели «Формы» выбираем «Поле со списком» и рисуем его, например, в ячейке А9.

Элемент «Поле со списком» размещается не в самой ячейке, а, как бы, над ней!!! Элемент может быть большим и находиться над несколькими ячейками.

4. Щелкаем правой кнопкой мыши по нарисованному элементу и в появившемся контекстном меню выбираем «Формат объекта».

5. В выпавшем окне «Форматирование объекта» на вкладке «Элемент управления» заполняем поля в соответствии с рисунком, расположенном ниже и нажимаем «ОК».

6. Раскрывающийся список готов. Он выводит порядковый номер элемента списка в связанную ячейку В9. (Можете назначить любую удобную вам ячейку, не обязательно В9!)

Для вывода в какую-либо ячейку самого значения из списка-справочника применим функцию ИНДЕКС. Допустим, нам необходимо вывести значение в ячейку А9, расположенную под элементом «Поле со списком».

Для этого в ячейку А9 запишем формулу: =ИНДЕКС(A2:A8;В9)

Наглядный пример подробно рассмотрен в статье « ». Можно перейти по ссылке и ознакомиться.

Раскрывающийся список, созданный этим способом плюс применение функций ИНДЕКС и/или ВПР предоставляют безграничные возможности пользователю при извлечении данных из различных базовых справочных таблиц.

Вариант №4 — «Самый сложный».

Для создания выпадающего списка в этом случае используется также элемент «Поле со списком», но панели инструментов «Элементы управления» (в MS Excel 2003). Это так называемые элементы ActiveX. Здесь все очень похоже внешне на вариант №3, но значительно шире возможности настройки и форматирования элемента.

1. Выбираем в главном меню кнопку «Вид» – «Панели инструментов» – «Элементы управления».

2. В появившейся панели «Элементы управления» выбираем «Поле со списком» и рисуем его в ячейке А9. Элемент ActiveX «Поле со списком» размещается не в самой ячейке, а сверху, накрывая ее!!!

3. Нажимаем кнопку «Свойства» на панели «Элементы управления» и в выпавшем окне «Properties» («Свойства») вручную вписываем диапазон исходных данных, адрес связанной ячейки (ячейки, куда будет введено выбранное значение) и количество отображаемых строк.

4. Далее при желании можно изменить шрифт, его цвет, цвет фона, и еще ряд параметров… Ничего сложного нет в использовании «Самого сложного» варианта – убедитесь сами. Все интуитивно понятно, хотя базовые знания английского языка не помешают!

5. Отжимаем кнопку «Выход из режима конструктора» на панели «Элементы управления» и проверяем работу раскрывающегося списка. Все работает! Выбранное значение записано в ячейку А9, в нашем примере — под элементом «Поле со списком». Вообще связанной ячейкой может быть абсолютно любая кроме ячеек, где располагается базовый список.

Итоги.

Вариант №0 автоматизирует в некоторой степени заполнение ячеек, но к раскрывающимся спискам, конечно, отношения не имеет и приведен здесь под соответствующим номером, как элементарный вариант автоматизации ввода повторяющихся данных.

На практике я чаще всего создаю раскрывающиеся списки в Excel, используя варианты №1 и №3, реже — вариант №2 и совсем редко — вариант №4, хотя именно он является, безусловно, самым гибким, предоставляющим самые широкие возможности.

Но часто наш выбор в жизни определяют вкусы, стереотипы и привычки! В зависимости от задачи, которую необходимо решить при работе в Excel, следует выбирать наиболее приемлемый и удобный для каждого конкретного случая вариант создания раскрывающихся списков.

Подписывайтесь на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы и не забывайте подтверждать подписку кликом по ссылке в письме, которое придет к вам на указанную почту (может прийти в папку «Спам» — все зависит от настроек вашей почты)!!!

Полезной или нет, оказалась эта статья для вас, уважаемые читатели? Напишите об этом в комментариях.

При создании таблиц порой очень удобно использовать выпадающие (иначе говоря — раскрывающиеся) списки. Excel 2010 позволяет это делать несколькими способами. Рассмотрим их.

Способ 1. Создаем раскрывающийся список в Excel 2010, используя инструмент «Проверка данных»

Этот способ считается стандартным, поскольку он прост и удобен.

1. На свободном месте листа запишите все элементы раскрывающегося списка в столбик, каждый элемент — в своей ячейке.

2. Задайте имя диапазону ячеек. Для этого:

• кликните верхнюю ячейку списка и, зажав левую кнопку мыши, тяните курсор вниз, пока весь список не будет выделен.
• установите курсор в поле «Имя», слева от строки формул;
• введите имя списка и нажмите Enter.

Обратите внимание, имя списка всегда должно начинаться с буквы и не содержать пробелов.

3. Выделите ячейку в таблице, где должен будет размещаться выпадающий список.

4. Откройте вкладку «Данные» и нажмите кнопку «Проверка данных». В открывшемся окне перейдите на вкладку «Параметры». В выпадающем списке «Тип данных» выберите пункт «Список».

5. В строке «Источник» нужно указать адрес, откуда будет взяты элементы создаваемого списка. Адресом будет имя, которое вы присвоили диапазону ячеек. Задать адрес можно несколькими способами.

• Вписать вручную, поставив перед ним знак «равно», например, «=месяца». Регистр букв не важен.
• Щелкнув мышью в строке «Источник» (для активации), выделить курсором все элементы списка в таблице.

6. Если нужно создать сообщение для ввода, откройте одноименную вкладку. Напишите текст, который будет появляться рядом с ячейкой с раскрывающимся списком при ее выделении. На соседней вкладке — «Сообщение об ошибке», таким же образом можно написать текст, уведомляющий об ошибках.

7. Подтвердите ввод нажатием «ОК», и раскрывающийся список готов. Для его открытия нажмите на кнопку со стрелкой вниз, которая появится рядом с ячейкой, содержащей список.

Способ 2. Быстрое создание выпадающего списка

Выпадающий список в Excel 2010 можно создавать одним нажатием сочетания клавиш, но он может быть расположен только в одном месте — в ячейке под элементами списка.

1. Перечислите в столбик все элементы будущего выпадающего списка.

2. Выделите ячейку, находящуюся под последним элементом и нажмите сочетание клавиш «Alt» + «стрелка вниз» — список будет создан. Этот метод позволит задать ячейке значение одного из элементов.

Способ 3. Создание выпадающего списка как элемента управления

Для применения этого метода включите отображение вкладки «Разработчик»: откройте меню «Файл» — «Параметры» — «Настройка ленты». В столбце «Основные вкладки» отметьте галочкой пункт «Разработчик». Подтвердите действие нажатием «ОК» — вкладка будет создана.

1. Перечислите элементы будущего списка в столбик.

2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы управления формы» — «Поле со списком».

3. Нарисуйте на листе курсором ваш будущий раскрывающийся список. Кликните по нему правой кнопкой мыши и выберите из меню пункт «Формат объекта».

4. Значением поля «Формировать список по диапазону» должен быть список элементов — выделите его курсором, и поле будет заполнено автоматически. В поле «Связь с ячейкой» указывается адрес ячейки, где будет показан порядковый номер выделенного элемента. Выберите ячейку и кликните по ней. Поле «Количество строк списка» позволяет настроить, сколько элементов будет отображено при раскрытии списка.

5. Подтвердите ввод и нажмите «ОК». Список будет создан.

Способ 4. Создание выпадающего списка как элемента ActiveX

Самый сложный метод, но обладающий максимально гибкими настройками.

1. Создайте список вышеописанным способом.

2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы ActiveX» — «Поле со списком».

3. Нарисуйте на листе будущий раскрывающийся список.

4. Опция, которая позволяет редактировать выпадающий список называется «Режим конструктора». Если данный режим активен — будет выделена одноименная кнопка в разделе «Элементы управления», рядом с кнопкой «Вставить». Если кнопка не выделена — режим редактирования отключен.

5. Для задания параметров списка нажмите кнопку «Свойства» того же раздела. Откроется окно настроек «Properties». Обе вкладки этого окна содержат одни и те же настройки, рассортированные в первом случае — по алфавиту, во втором — по категориям.

6. Большинство настроек можно оставить по умолчанию, а самое необходимое перечислено ниже.

• ListRows — аналог значения «Количество строк в списке», покажет, сколько строк будет отображаться.
• Font — настройки шрифта. Позволяет выбрать шрифт и его начертание.
• ForeColor — выбор из таблицы цвета шрифта.
• BackColor — цвет заднего фона.
• ListFillRange — расположение списка элементов в формате: лист(«!» — разделитель) и диапазон ячеек. Например: Лист2!D2:D6. Прописывается вручную.
• LinkedCell — связь с ячейкой. Вручную указывается адрес ячейки, где будет показываться порядковый номер выделенного элемента списка.

7. Сохраните настройки и деактивируйте режим конструктора нажатием на одноименную кнопку. Выпадающий список будет создан, а вы сможете проверить, как он работает.

Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки, в которых можно выбрать нужный элемент.

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel . Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T .



Примечания:

1. Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.

   На ленте откройте вкладку Данные и нажмите кнопку Проверка данных .

   Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.

   На вкладке Параметры в поле Тип данных выберите пункт Список .

   Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

   

   Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки .

   Установите флажок Список допустимых значений

   Откройте вкладку Сообщение для ввода .

   
   

2. Откройте вкладку Сообщение об ошибке .

   
   

3. Не знаете, какой параметр выбрать в поле Вид ?

Работа с раскрывающимся списком

После того как вы создадите раскрывающийся список, убедитесь, что он работает так, как вам нужно. Например, может потребоваться проверить, нужно ли изменять ширину столбцов и высоту строк , чтобы отображались все записи.

Скачивание примеров

Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачать примеры проверки данных Excel

Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.

Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.