Как сделать список в excel с другого листа?

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

Создание дополнительного списка

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

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

Выделяем данные, которые планируем занести в раскрывающийся список. Кликаем правой кнопкой мыши, и в контекстном меню выбираем пункт «Присвоить имя…».

Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».

Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.

Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».

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

Создание выпадающего списка с помощью инструментов разработчика

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

В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».

После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».

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

Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».

Открывается окно свойств элемента управления. В графе «ListFillRange» вручную через двоеточие прописываем диапазон ячеек таблицы, данные которой будут формировать пункты выпадающего списка.

• Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».
• Выпадающий список в Microsoft Excel готов.
• Чтобы сделать и другие ячейки с выпадающим списком, просто становимся на нижний правый край готовой ячейки, нажимаем кнопку мыши, и протягиваем вниз.

Связанные списки

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

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

Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).

• Как видим, список создан.
• Теперь, чтобы и нижние ячейки приобрели те же свойства, как и в предыдущий раз, выделяем верхние ячейки, и при нажатой клавише мышки «протаскиваем» вниз.
• Всё, таблица создана.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Источник: https://lumpics.ru/how-to-make-a-dropdown-list-in-excel/

Как легко создать выпадающий список в excel и облегчить заполнение таблицы?

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

Допустим, когда я создавал таблицу учета компьютерного оборудования (еще давно) у себя на работе, то чтобы сделать весь процесс работы более удобным и быстрым, я делал выпадающий список в определенных столбцах и вставлял туда определенные знaчения. И когда я заполнял столбец «Операционная система» (А ведь не на всех компах она одна и та же), то я забивал не сколько значeний (7, 8, 8.1, 10), а потом просто выбирал это всё одним нажатием кнопки мыши.

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

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

Я буду делать это в 2013 версии, но для других версий процесс идентичный, так что не переживайте.

Подготовка

1. На листе №1 создайте несколько столбцов (т.е. дайте им заголовки), например дата, отдeл, наименование проблемы, причина неисправности, фамилия специалиста техподдержки и итог устранения. Так вот, дату проблему и причину мы трогать не будем, а вот oтдел и фамилию специалиста, устранившего неисправность, мы занесем в шаблон, так как вариантов тут может быть немного.

    

2. Чтобы было удобнее создайте новый лист и обзовите его как-нибудь. Я бы назвал «Данные». Именно сюда мы и будем вносить все возможные значения, из которых потом и будем выбирать то, что нам надо. 
3. На листе 2 (Данные) начинайте записывать значения. В начале сделаем всё это для графы «Отдел». Я напишу в столбик несколько отделов, например отдeл статистики, администрация, бухгалтерия, отдел планирования, oтдел информационных технологий, ну и хватит пожалуй. Вы можете писать что-то свое.
4. Точно также напишите в столбик фамилии сотрудников, которые занимаются устранением проблем, например Иванов, Петров, Сидоров, Смирнов. Записали? Отлично. Тогда идем дальше. То же самое делаем с «Итогом устранения». Пишем данные, например устранено, на устранении, устранение невозможно. 

Основные действия

1.  Всё, подготовку мы завершили, теперь приступаем к самому действию. Переходите на главный лист и выделите весь столбец «Отдел». Для этого можете нажать на букву, соответствующую столбцу.
2. Теперь идите в меню «Данные» и нажмите на «Проверка данных». 
3. В появившемся окне в строке «Тип данных» выберите «Список» и нажмите «ОК», после чего встаньте в поле «Источник». И не закрывая это окошко переходите на второй лист «Данные» и выделите все значения, которые соответствуют отделу. Нажмите ОК. 
4. Теперь снова идите на первый (главный) лист и поставьте курсор в любую ячейку «Отдела». Заметили, что правее появилась маленькая стрелочка? Нажмите на нее и вы увидите все значения, которые вы выделили на втором листике. 

Теперь точно таким же образом поработайте с графами «Фамилия специалиста» и «Итог устранения», после чего снова вернитесь на главный лист и начинайте полноценно работать с таблицей. Вы сами увидите как это здорово и удобно, когда можно выбрать данные из доступных заранее подготовленных значений. Благодаря этому рутинное заполнение таблиц облегчается.

Кстати в таких документах для более удобного отображения лучше закрепить верхнюю строку. Тогда будет вообще всё круто.

Ну а на сегодня я свою статью заканчиваю. Я надеюсь, что то, что вы сегодня узнали пригодится вам при работе в экселе. Если статья вам понравилась, то конечно же не забудьте подписаться на обновления моего блога. Ну а вас я с нетерпением буду снова ждать на страницах моего блога. Удачи вам и пока-пока!

С уважением, Дмитрий Костин

Вам также может понравиться

Доброго всем времени суток, мои дорогие друзья и гости

Источник: https://koskomp.ru/office/microsoft-excel/kak-sozdat-vypadayushhiy-spisok-v-excel/

Выпадающий список в ячейке листа

3579 02.10.2012 Скачать пример

Видео

 У кого мало времени и нужно быстро ухватить суть — смотрим обучающее видео:

Кому интересны подробности и нюансы всех описанных способов — дальше по тексту.

Способ 1. Примитивный

Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:

Способ 2. Стандартный

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

2. Если у вас Excel 2003 или старше — выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define), если Excel 2007 или новее — откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать.

    Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.

3. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные — Проверка (Data — Validation).

   Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).

Нажмите ОК.

Все! Наслаждайтесь!

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

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

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

Способ 3. Элемент управления

Этот способ представляет собой вставку на лист нового объекта — элемента управления «поле со списком» с последующей привязкой его к диапазонам на листе. Для этого:

1. В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях — панель инструментов Формы (Forms) через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms). Если этой вкладки не видно, то нажмите кнопку Офис — Параметры Excel — флажок Отображать вкладку Разработчик на ленте (Office Button — Excel Options — Show Developer Tab in the Ribbon)
2. Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам — Поле со списком: Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник — будущий список.
3. Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте

• Формировать список по диапазону — выделите ячейки с наименованиями товаров, которые должны попасть в список
• Связь с ячейкой — укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
• Количество строк списка — сколько строк показывать в выпадающем списке. По умолчанию — 8, но можно больше, чего не позволяет предыдущий способ.

После нажатия на ОК списком можно пользоваться.

Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:

Способ 4. Элемент ActiveX

Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):

Механизм добавления тот же — выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.

Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях — режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и — режиме ввода, когда единственное, что можно — выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):

Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:

Самые нужные и полезные свойства, которые можно и нужно настроить:

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

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

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

Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:

Итоговая сравнительная таблица всех способов

Способ 1. Примитивный	Способ 2. Стандартный	Способ 3. Элемент управления	Способ 4. Элемент ActiveX
Сложность	низкая	средняя	высокая	высокая
Возможность настройки шрифта, цвета и т.д.	нет	нет	нет	да
Количество отображаемых строк	всегда 8	всегда 8	любое	любое
Быстрый поиск элемента по первым буквам	нет	нет	нет	да
Необходимость использования дополнительной функции ИНДЕКС	нет	нет	да	нет
Возможность создания связанных выпадающих списков	нет	да	нет	нет

Ссылки по теме:

Источник: https://www.planetaexcel.ru/techniques/1/40/

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

Одной из наиболее полезных функций при вводе данных в 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. Для этого:

1. Откройте вкладку Разработчик (Developer). Если этой вкладки не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры Excel – флажок Отображать вкладку Разработчик на ленте (Office Button – Excel Options – Show Developer Tab in the Ribbon) или в Excel 2010–2013 щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
2. Найдите значок выпадающего списка среди элементов управления

Вставив элемент управления «Выпадающий список» на рабочий лист, щелкните по нему правой кнопкой мышки и выберите в раскрывающемся меню пункт «Формат объекта».

Далее указываем диапазон ячеек, в котором записаны допустимые значения для ввода. В поле «Связь с ячейкой» укажем, куда именно поместить результат.

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

Чтобы все же вставить не номер, а нужное слово целиком, используем функцию ИНДЕКС (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).

Осталось создать выпадающий список, который будет ссылаться на эти данные. Для этого:

1. Выделяем ячейки, где хотим создать выпадающие списки.
2. На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
3. Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (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, называемый выпадающим списком. Рассмотрим, как сделать выпадающий список в Excel.

Составление исходных списков

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

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

Пусть у нас будет три списка, включающие слова:

• картофель, свекла, морковь, редис;
• петрушка, укроп, щавель, шпинат;
• клубника, вишня, черешня, крыжовник.

Первые четыре слова заносим в столбец, начиная с A1, вторые – с B1, третьи – с C1.

Чтобы перейти непосредственно к вопросу о том, как сделать выпадающий список в Excel, зададим имена этим спискам. Для этого выделим значения в первом столбце, выберем пункт меню: «Формулы» — «Диспетчер имен» — «Присвоить имя».

В появившейся форме в строке «Имя» заполняем «овощи», нажимаем «ОК». Аналогично повторяем для других списков, присвоив им имена «зелень» и «ягоды». В строке «Область» оставляем значение «Книга».

Это позволяет обращаться к данным спискам из других листов книги.

Создание выпадающего списка в Excel

Теперь собственно формируем выпадающий список. Выбираем, например, ячейку D1 на другом листе открытой книги, открываем пункт меню: «Данные» – «Проверка данных». В открывшейся форме «Проверка вводимых значений» во вкладке «Параметры» в поле «Тип данных» выбираем «Список». Ставим курсор в поле «Источник», набираем знак равенства и нажимаем клавишу F3. В появившемся меню «Вставка имени» выбираем имя нужного нам списка, например, «овощи», затем нажимаем «ОК». Проверяем, чтобы стояли галочки в позициях «Игнорировать пустые ячейки» и «Список допустимых значений». Это обеспечивает возможность не заполнять это поле данными и вводить только данные из списка с указанным именем. Жмем «ОК».

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

Другие варианты

При заполнении поля «Источник» формы «Проверка вводимых значений» можно применить еще два варианта.

1. Поставив курсор в это поле, выделить на данном листе книги область допустимых значений списка. Формула появится в поле после знака равенства. Как сделать выпадающий список в Excel с другого листа в этом случае? Надо аналогичный диапазон ячеек выделить на этом листе, а затем, поставив курсор в поле после знака равенства, написать наименование листа со списком и поставить восклицательный знак. Внимание: название листа не должно содержать пробелов!
2. Перечислить допустимые значения в поле, разделяя их точкой с запятой. Это самый простой способ, который не допускает обращение к спискам на другом листе книги.

Расширение диапазона допустимых значений

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

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

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

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

Таким образом, мы получили выпадающий список в Excel.

Двойная ссылка

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

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

Задаем этому списку имя, например, «Список 1».

Теперь (можно и на другом листе данной книги) выбираем, например, ячейку G1 и задаем для нее выпадающий список со ссылкой на имя «Список 1». Следующую ячейку, например, H1, заполняем с учетом выбранного значения в ячейке G1.

Для этого в ячейке H1 формируем выпадающий список, как указано ранее, в поле «Источник» выбираем ячейку G1, но дорабатываем запись в этом поле, добавляя после знака равенства ДВССЫЛ, а остальную часть формулы взяв в круглые скобки: =ДВССЫЛ($G$1).

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

Источник: https://www.syl.ru/article/168161/new_kak-sdelat-vyipadayuschiy-spisok-v-excel-s-pomoschyu-proverki-dannyih

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

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

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

Создаем простой выпадающий список

Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2.

• Переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных».
• На вкладке «Параметры» в поле «Тип данных» выбираем «Список». В поле «Источник» можно ввести значения различными способами:
• 1 – вводим значения для списка вручную, через точку с запятой;
• 2 – указываем диапазон ячеек, в которые введены данные для выпадающего списка;
• 3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя».
• Дальше впишите «Имя» для выделенных данных.
• Выделяем ячейку В2 и в поле «Источник» ставим «=», затем пишем созданное имя.
• Таким образом, мы создали простой выпадающий список в Excel.

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

Добавляем значения в выпадающий список – динамический список

1. При этом мы будем дописывать значения в нужный диапазон, а они будут автоматически добавляться в выпадающий список.
2. Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.
3. Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».

4. Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.

Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных». В следующем окне, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]»).

У меня одна таблица на листе, поэтому пишу «Таблица1», если будет вторая – «Таблица2», и так далее.

Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.

Выпадающий список со значениями с другого листа

• Если таблица с выпадающими списками находится на одном листе, а данные для этих списков – на другом, то данная функция нам очень поможет.
• На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных».
• Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.
• Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.

Создаем зависимые выпадающие списки

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

Первый называем «Имя», второй – «Фамилия», третий – «Отч».

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».

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

В поле «Тип данных» выберите «Список», в поле источник – или введите «=Сотрудники», или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

Выделяем ячейку и кликаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Здесь Е1 – это ячейка с первым выпадающим списком.

По такому принципу можно делать зависимые выпадающие списки.

Если в дальнейшем, нужно будет вписать значения в диапазон, которому задано имя, например, «Фамилия». Перейдите на вкладку «Формулы» и кликните «Диспетчер имен».

Теперь в имени диапазона выбираем «Фамилия», и внизу, вместо последней ячейки С3, напишите С10. Нажмите галочку.

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

Теперь Вы знаете, как сделать раскрывающийся список в Excel.

(3

Источник: http://comp-profi.com/kak-sdelat-vypadayushij-spisok-v-excel/