Одной из наиболее полезных функций при вводе данных в Excel является возможность создания выпадающего списка. Он позволяет выбирать значение из предварительно определенного перечня. Раскрывающиеся списки позволяют пользователям вводить только те данные, которые соответствуют вашим требованиям.
Мы рассмотрим 4 самых простых способа, как сделать выпадающий список в Excel. Более сложные способы, основанные на динамических диапазонах и использовании данных из других таблиц Excel, мы рассмотрим отдельно. Ссылки на описание этих способов вы сможете найти в конце этой статьи (контент будет обновляться).
Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся данные. К примеру, это могут быть фамилии сотрудников, наименования товаров.
Это может быть статус выполнения заказа – к примеру, отправлено коммерческое предложение, выставлен счет на оплату, заказ выполнен.
Иначе говоря, нам необходимо выбрать один из нескольких заранее определённых вариантов и вставить его в ячейку нашей таблицы.
Какие здесь могут быть проблемы? Конечно, в первую очередь это ошибки при вводе. К примеру, можно перепутать букву в фамилии, сделать грамматическую ошибку в слове.
Чем нам грозят такие ошибки? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из наших сотрудников, то окажется, что фамилий больше, чем людей.
Потом придётся искать неправильно записанные фамилии, исправлять их и вновь повторять расчет.
Ну и конечно же, постоянно вручную вводить одни и те же данные – это просто потеря времени. Поэтому нам и нужны выпадающие списки.
Мы будем использовать перечень определённых значений, из которых при вводе необходимо выбрать только одно.
Важно то, что вы будете не вводить, а выбирать их при помощи мыши или клавиатуры. Это значительно ускоряет процесс заполнения таблицы, а также гарантирует нам защиту от случайных ошибок.
4 способа, как сделать выпадающий список в Excel
Способ 1 — самый быстрый
Как проще всего сделать раскрывающийся список в ячейке Excel? Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню «Выбрать из раскрывающегося списка» (Choose from drop-down list).
А можно просто стать в нужную ячейку и нажать сочетание клавиш Alt+стрелка вниз. Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.
Способ 2 — как сделать выпадающий список в ячейке при помощи меню
Давайте рассмотрим небольшой пример, в котором нам нужно постоянно вводить в таблицу одни и те же наименования. Запишите данные, которые мы будем использовать (например, с наименованиями товаров). В нашем примере — G2:G7.
Выделите ячейки таблицы (можно сразу несколько), в которых хотите использовать раскрывающийся список. Далее в меню выберите на вкладке Данные – Проверка данных (Data – Validation).
Из выпадающего перечня Тип данных (Allow) выберите вариант Список (List). Поставьте курсор в поле Источник (Source) и укажите адреса с эталонными значениями элементов — в нашем случае G2:G7.
Рекомендуется также использовать при этом абсолютные ссылки.
Бонусом к этому способу идет возможность задать подсказку при вводе данных и сообщение об ошибке при некорректном вводе на вкладках Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert)
В качестве источника можно использовать также и именованный диапазон.
К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя диапазона можно ввести в поле «Источник».
- Кроме того, и источник данных в виде диапазона значений, и именованный диапазон могут находиться на других листах вашей рабочей книги.
- Вы можете не использовать диапазон значений, а просто определить их прямо в поле «Источник». К примеру, записать там —
- Да,Нет
Используйте для разделения значений точку с запятой, запятую, либо другой символ, установленный у вас в качестве разделителя элементов. (Смотрите Панель управления — Часы и регион — Форматы — Дополнительно — Числа.)
Способ 3 — как создать выпадающий список при помощи элемента управления
Этот способ представляет собой вставку на лист нового объекта – элемента управления «Поле со списком» с последующей привязкой его к диапазонам на листе. А выбранная позиция будет вставлена в ячейку Excel. Для этого:
- Откройте вкладку Разработчик (Developer). Если этой вкладки не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры Excel – флажок Отображать вкладку Разработчик на ленте (Office Button – Excel Options – Show Developer Tab in the Ribbon) или в Excel 2010–2013 щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
- Найдите значок выпадающего списка среди элементов управления
Вставив элемент управления «Выпадающий список» на рабочий лист, щелкните по нему правой кнопкой мышки и выберите в раскрывающемся меню пункт «Формат объекта».
Далее указываем диапазон ячеек, в котором записаны допустимые значения для ввода. В поле «Связь с ячейкой» укажем, куда именно поместить результат.
Важно помнить, что этим результатом будет не само значение из указанного нами диапазона, а только его порядковый номер.
Чтобы все же вставить не номер, а нужное слово целиком, используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет выбрать из диапазона значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон значений (F5:F11) и адрес с полученным порядковым номером (F2).
Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.
Способ 4 — используем элемент ActiveX
Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже — из раздела «Элементы ActiveX».
Определяем диапазон допустимых значений (1). Обратите внимание, что здесь для показа можно выбирать сразу несколько колонок. Затем выбираем адрес, по которому будет вставлена выбранная позиция из перечня (2).
Указываем количество столбцов, которые будут использованы как исходные данные (3), и номер столбца, из которого будет происходить выбор для вставки на лист (4). Если укажете номер столбца 2, то в А5 будет вставлена не фамилия, а должность.
Можно также указать количество строк, которое будет выведено при развертывании списка. По умолчанию — 8. Остальные можно прокручивать мышкой (5).
Надеемся, теперь вам будет гораздо проще сделать выпадающий список в ячейке таблицы Excel.
А вот еще по той же теме:
Создаем выпадающий список в Exel при помощи формул — Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения. Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в… 
Автозаполнение выпадающего списка в Excel 2010-2013 — Задача: Создать в ячейке Excel выпадающий список, в который будут автоматически добавляться значения из заданного динамического диапазона. Если в этот диапазон будут внесены изменения, то сразу же изменится и набор…
Источник: https://mister-office.ru/formuly-excel/dropdown-list-excel.html
Как создать в Excel выпадающий список с данными из другого файла | Сводные таблицы Excel 2010
Создать простой выпадающий список в ячейке с данными из этого же файла – просто. Все усложняется, когда сам список и данные для него находятся в разных файлах. Для решения этой проблемы есть несколько способов.
Способ 1. Функция ДВССЫЛ
В простом случае можно использовать функцию ДВССЫЛ (INDIRECT), чтобы сформировать правильную ссылку на внешний файл.
Например, если необходимо создать выпадающий список с содержимым ячеек А1:А10 с листа Список из файла Товары.
xls, нужно открыть окно проверки данных через вкладку Данные – Проверка данных (Data – Validation) и в поле Источник (Source) ввести следующую конструкцию: =ДВССЫЛ(«[Товары.xls]Список!$A$1:$A$10»).
Чтобы сформировать правильную ссылку на внешний файл можно использовать функцию ДВССЫЛ
Функция ДВССЫЛ (INDIRECT) преобразует текстовую строку аргумента в реальный адрес, используемый для ссылки на данные. Обратите внимание, что имя файла заключается в квадратные скобки, а восклицательный знак служит разделителем имени листа и адреса диапазона ячеек. Если имя файла содержит пробелы, то его надо заключить в апострофы.
Если файл с исходными данными для списка лежит в другой папке, необходимо указать полный путь к файлу, например, следующим образом: =ДВССЫЛ(«'C:Поставщики[Товары.xls]Список'!$A$1:$A$10»).
В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа. Минус этого способа только один – выпадающий список будет корректно работать только в том случае, если файл Товары.
xls открыт.
Способ 2. Импорт данных
Суть этого способа в том, что данные для выпадающего списка буду импортироваться из другого файла в текущий. Для этого необходимо создать обновляемую связь между двумя файлами.
Сначала откройте файл-источник, где находятся эталонные значения для выпадающего списка (назовем его, допустим, Справочник.xlsx).
Выделите диапазон с данными для списка и отформатируйте его как таблицу с помощью кнопки Форматировать как таблицу на вкладке Главная (Home – Format as Table).
Обратите внимание, что у такой таблицы предварительно должна быть сделана «шапка» – строка заголовка. После этого файл Справочник можно сохранить и закрыть.
Теперь откроем книгу, где мы хотим создать выпадающий список (условно назовем ее Бланк.xlsx).
Вставим чистый лист (Alt+F11), выберем на вкладке Данные – Существующие подключения – Найти другие (Data – Existing Connections – Browse for more) и укажем наш файл Справочник.xlsx.
Появится диалоговое окно, в котором Excel спросит нас о том, какую именно таблицу мы хотим импортировать (если их в файле было несколько).
Теперь откроем книгу, где мы хотим создать выпадающий список
После нажатия на ОК появится еще одно последнее окно, где можно указать удобную ячейку для импорта и, нажав на кнопку Свойства (Properties), задать частоту обновления информации.
После нажатия на ОК появится еще одно последнее окно
Тут можно включить флажок Обновить при открытии файла (Refresh on open), чтобы каждый раз при открытии этой книги иметь последнюю версию списка.
Можно включить флажок Обновить при открытии файла
После нажатия на кнопку ОК, Excel загрузит данные из созданной таблицы из файла Справочник в наш текущий файл Бланк и отформатирует их в виде таблицы.
Excel загрузит данные из созданной таблицы
Если выделить импортированный список (диапазон А2:А7 в нашем случае), то в строке формул можно увидеть его имя, которое он автоматически получает при вставке.
В строке формул можно увидеть имя импортированного списка
Это имя также можно увидеть в Диспетчере имен на вкладке Формулы (Formulas – Name Manager).
Осталось создать выпадающий список, который будет ссылаться на эти данные. Для этого:
- Выделяем ячейки, где хотим создать выпадающие списки.
- На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
- Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу: =ДВССЫЛ(«Таблица_Справочник»). В англоязычной версии Excel это будет =INDIRECT(«Таблица_Справочник»).
Осталось создать выпадающий список
Логично было бы ввести просто имя нашего диапазона, но, к сожалению, Microsoft Excel почему-то не воспринимает имена таблиц в поле Источник. Поэтому мы используем тактическую хитрость – функцию ДВССЫЛ (INDIRECT), которая превращает свой аргумент (имя нашей таблицы) в рабочую ссылку.
Все. После нажатия на ОК наш список начнет работать и будет в дальнейшем автоматически обновляться, подгружая данные из другого файла.
После нажатия на ОК список начнет работать и автоматически обновляться
Источник: http://pivot-table.ru/kak-sozdat-v-excel-vypadayushhij-spisok-s-dannymi-iz-drugogo-fajla.html
Зависимые выпадающие списки в Excel
Изучим простой способ создания зависимых (также называют связанных) выпадающих списков в Excel.
Для начала поясним, что же такое связанные выпадающие списки.
Это 2 выпадающих списка, при этом список значений одного из выпадающих списков зависит от выбора значения в другом выпадающем списке. Создать по отдельности выпадающие списки не представляет сложностей, но и связать выпадающие списки можно достаточно просто.
Для наглядности рассмотрим следующую ситуацию: у нас есть несколько категорий блюд, например, пицца, суши и паста, а также виды блюд в каждой категории (пицца Пепперони, суши Филадельфия, паста Феттучини и т.д.):
В результате мы хотим получить своеобразный двухуровневый зависимый выпадающий список: на первом уровне (списке) — категории блюд, на втором — блюда из выбранной категории.
Как сделать зависимые выпадающие списки?
В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:
Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):
- В том случае если в названии категории все же содержится пробел (например, Японская кухня), то одним из вариантов решения является использование нижнего подчеркивания вместо пробела в имени диапазона (Японская_кухня).
- Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:
Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;» «;»_»)).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.
Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.
Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!
- Точечная диаграмма в Excel
- Всплывающие подсказки в Excel
Источник: https://tutorexcel.ru/priemy/zavisimye-vypadayushhie-spiski-v-excel/
Как быстро создать многоуровневые (каскадные) выпадающие списки в Excel
Мир вокруг нас полон иерархических структур. Зачастую при разработке форм в Microsoft Excel мы сталкиваемся с задачей организации выбора показателей, имеющих некую иерархию, например, адреса (страна-город-улица-дом), организационная структура предприятия (департамент – управление – отдел-сотрудник) или же номенклатура товаров магазина.
Почти каждый пользователь Excel умеет создавать в ячейке выпадающий список, более продвинутые могут создать второй, связанный с первым выпадающий список.
А можно ли создавать более сложные системы списков в Excel? На самом деле да, причем количество уровней таких списков ничем не ограничено и определяется только вашей потребностью. Рассмотрим методику, которая позволяет быстро и без особого труда строить многоуровневые выпадающие списки.
Основа методики – организация исходной информации в «Умных таблицах», т.е. специально структурированных объектах Excel. «Умную таблицу» можно создать клавишами Ctrl+T или кнопкой Таблица на вкладке Вставка, после чего таблице необходимо задать имя (во всплывающей при её выделении вкладки Конструктор).
Основное преимущество данной методики состоит в том, что полученная система является полностью динамической, т.е. в неё можно добавить любые данные, даже целые ветви иерархии, и они автоматически попадут в выпадающие списки.
Особенность заключается в том, что названия нижестоящих таблиц должны соответствовать элементам вышестоящих. В качестве примера рассмотрим фрагмент 6-уровневой иерархии товаров магазина, оформленной в «Умных таблицах»:
Первая таблица Группы_товаров связывает группы товаров и категории: в магазине 2 группы товаров – поля Продукты_питания и Одежда, каждая из которых включает по 2 категории товаров: продукты питания состоят из элементов Молочные_продукты и Мясо, одежда – Верхняя_одежда и Спортивные_товары.
Обратите внимание! Элементы таблицы будут являться одновременно названием для нижестоящих таблиц. К именам таблиц предъявляются специальные требования: имя должно начинаться с буквы, не должно содержать пробелов и специальных символов.
На следующем уровне создаются таблицы, связывающие категории и виды продукции: в приведенном примере создана таблица с именем Молочные_продукты, содержащая поля Молоко (подразделяется на козье и коровье) и Сыр (подразделяется на твердый и полутвердый), а также таблица с именем Мясо, содержащая поля Красное_мясо (подразделяется на говядина и свинина) и Птица (подразделяется на курицу и индейку).
Остальные данные организуются по такому же принципу.
Список первого уровня является первичным, т.е. задается абсолютно и не является зависимым, это может быть список таблиц книги, список полей конкретной таблицы или список элементов конкретного поля конкретной таблицы, в зависимости от ваших потребностей.
Следующие списки будут связанными, т.е. зависеть от выбранного значения вышестоящего списка. Список может быть 2 уровня, т.е. ссылаться на один вышестоящий список, или 3 уровня, т.е. ссылаться на два вышестоящих списка (например, на список с именем таблицы и список с именем поля таблицы).
Источник: http://xn—-btbktgundek1hji.xn--p1ai/connected-drop-down-lists-excel.html
Как сделать выпадающий список в excel 2019 – Как сделать выпадающий список в Excel? Инструкция и видеоурок — MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение
Если Вы заполняете таблицу в Excel, и данные в столбце могут иногда повторяться, например, название товара, или имя сотрудника, то, чтобы не вводить нужный параметр каждый раз, проще и легче один раз создать выпадающий список и выбирать из него значение.
В статье мы рассмотрим, как сделать выпадающие списки различного вида в таблице Эксель.
Создаем простой выпадающий список
Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2.
- Переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных».
- На вкладке «Параметры» в поле «Тип данных» выбираем «Список». В поле «Источник» можно ввести значения различными способами:
- 1 – вводим значения для списка вручную, через точку с запятой;
- 2 – указываем диапазон ячеек, в которые введены данные для выпадающего списка;
- 3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя».
- Дальше впишите «Имя» для выделенных данных.
- Выделяем ячейку В2 и в поле «Источник» ставим «=», затем пишем созданное имя.
- Таким образом, мы создали простой выпадающий список в Excel.
Если у Вас есть заголовок для столбца, и значениями нужно заполнять каждую строку, то выделите не одну ячейку, а диапазон ячеек – В2:В9. Тогда можно будет выбирать из выпадающего списка нужное значение в каждой ячейке.
Добавляем значения в выпадающий список – динамический список
- При этом мы будем дописывать значения в нужный диапазон, а они будут автоматически добавляться в выпадающий список.
- Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.
- Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».
- Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.
Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных». В следующем окне, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]»).
У меня одна таблица на листе, поэтому пишу «Таблица1», если будет вторая – «Таблица2», и так далее.
Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.
Выпадающий список со значениями с другого листа
- Если таблица с выпадающими списками находится на одном листе, а данные для этих списков – на другом, то данная функция нам очень поможет.
- На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных».
- Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.
- Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.
Создаем зависимые выпадающие списки
Предположим, у нас есть три диапазона: имена, фамилии и отчества сотрудников. Для каждого, нужно присвоить имя. Выделяем ячейки оного диапазона, можно и пустые – в них со временем можно будет добавлять данные, которые будут появляться в выпадающем списке. Кликаем по ним правой кнопкой мыши и выбираем из списка «Присвоить имя».
Первый называем «Имя», второй – «Фамилия», третий – «Отч».
Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».
Делаем первый выпадающий список, который будет состоять из названия диапазонов. Выделяем ячейку Е1 и на вкладке «Данные» выбираем «Проверка данных».
В поле «Тип данных» выберите «Список», в поле источник – или введите «=Сотрудники», или выделите диапазон ячеек, которому присвоено имя.
Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.
Выделяем ячейку и кликаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Здесь Е1 – это ячейка с первым выпадающим списком.
По такому принципу можно делать зависимые выпадающие списки.
Если в дальнейшем, нужно будет вписать значения в диапазон, которому задано имя, например, «Фамилия». Перейдите на вкладку «Формулы» и кликните «Диспетчер имен».
Теперь в имени диапазона выбираем «Фамилия», и внизу, вместо последней ячейки С3, напишите С10. Нажмите галочку.
После этого диапазон увеличится, и в него можно будет дописывать данные, которые автоматически будут появляться в выпадающем списке.
- Теперь Вы знаете, как сделать раскрывающийся список в Excel.
Источник: https://toto-school.ru/excel/kak-sdelat-vypadayushhij-spisok-v-excel-2019-kak-sdelat-vypadayushhij-spisok-v-excel-instrukciya-i-videourok-ms-office-excel-rabota-na-kompyutere-instrukcii-i-sovety-obrazovanie-vos.html
Загрузка...
