Как создать в 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 спросит нас о том, какую именно таблицу мы хотим импортировать (если их в файле было несколько).

Теперь откроем книгу, где мы хотим создать выпадающий список

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

После нажатия на ОК появится еще одно последнее окно

Тут можно включить флажок Обновить при открытии файла (Refresh on open), чтобы каждый раз при открытии этой книги иметь последнюю версию списка.

Можно включить флажок Обновить при открытии файла

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

Excel загрузит данные из созданной таблицы

Если выделить импортированный список (диапазон А2:А7 в нашем случае), то в строке формул можно увидеть его имя, которое он автоматически получает при вставке.

В строке формул можно увидеть имя импортированного списка

Это имя также можно увидеть в Диспетчере имен на вкладке Формулы (Formulas – Name Manager).

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

1. Выделяем ячейки, где хотим создать выпадающие списки.
2. На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
3. Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу: =ДВССЫЛ("Таблица_Справочник"). В англоязычной версии Excel это будет =INDIRECT("Таблица_Справочник").

Осталось создать выпадающий список

Логично было бы ввести просто имя нашего диапазона, но, к сожалению, Microsoft Excel почему-то не воспринимает имена таблиц в поле Источник. Поэтому мы используем тактическую хитрость – функцию ДВССЫЛ (INDIRECT), которая превращает свой аргумент (имя нашей таблицы) в рабочую ссылку.

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

После нажатия на ОК список начнет работать и автоматически обновляться

Как создать выпадающий список в Excel? 2022 Руководство по созданию списка

Разглашение: Некоторые ссылки на этом сайте являются партнерскими. Это означает, что если вы нажмете на одну из ссылок и купите товар, я могу получить комиссию. Однако все мнения принадлежат мне.

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

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

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

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

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

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

Шаг № 2: Используйте проверку данных Excel.

После выбора ячеек щелкните вкладку «Данные»> группа «Инструменты для работы с данными». Появится список, выберите из списка Проверка данных.

Шаг № 3: Войдите в список предметов.

После того, как вы выберете опцию Data Validation, появится новое окно. Следуйте этим инструкциям —

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

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

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

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

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

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

Шаг № 1: Введите записи для раскрывающегося списка.

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

Шаг № 2: Создайте именованный диапазон.

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

• Выберите нужные записи в раскрывающемся списке, щелкните их правой кнопкой мыши и выберите «Определить имя» в контекстном меню. Другой способ сделать это — щелкнуть Диспетчер имен на вкладке Формулы или нажать Ctrl + F3.
• В диалоговом окне «Диспетчер имен» выберите «Создать».
•  В поле «Имя» введите имя для записей, убедитесь, что в поле «Ссылается на» отображается правильный диапазон, а затем нажмите «ОК».

Примечание: Убедитесь, что в названии диапазона нет пробелов и дефисов; вместо этого вы можете использовать подчеркивание (_).

Шаг № 3: Выберите место для раскрывающегося списка.

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

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

Шаг № 4: Примените проверку данных Excel.

После того, как вы выбрали место для своего списка, щелкните вкладку «Данные» и затем нажмите «Проверка данных».

Шаг № 5: Настройка раскрывающегося списка.

На вкладке «Проверка данных» щелкните вкладку «Настройки» и выберите следующие параметры:

•  В поле Разрешить выберите Список.
• В поле Источник введите имя, которое вы дали своему диапазону, со знаком равенства, например = ИМЯ.
•  Убедитесь, что в раскрывающемся списке в ячейке установлен флажок. Поставьте отметку в поле Игнорировать пробелы, если вашим пользователям разрешено оставлять ячейку пустой.
• Нажмите кнопку ОК.

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

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

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

Быстрые ссылки 

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

    Добрый день, уважаемый читатель!

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

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

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

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

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

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

   А делать выпадающий список в ячейке будем в несколько этапов:

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

      2. Выделяем нужный нам диапазон и в меню выбираем пункт “Данные” — “Проверка данных”, в появившемся контекстном окне выбираем из указанного выбора пункт “Список”.

     3. В разблокированной ниже строке указываем диапазон данных, которые должны быть у нас в выпадающем списке. Нажимаем “Ок” и работа сделана.

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

     А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

    Не забудьте поблагодарить автора!

Прогресс человечества основывается на желании каждого человека жить не по средствам
Сэмюэль Батлер, философ

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».

Выпадающий список в Microsoft Excel готов.

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

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

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

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

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

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

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

Как видим, список создан.

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

Всё, таблица создана.

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

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

Как сделать выпадающий список в 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.

Вебмастер. Высшее образование по специальности «Защита информации». Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности

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

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

выбрать элементы с выпадающими списками.

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

,

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

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

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

1. Создайте именованный диапазон: Этот шаг необходим для создания списка, который мы будем использовать в раскрывающемся списке.
2. Добавить проверку данных: Это не столь очевидный шаг, когда вы заполняете ячейку для отображения выпадающего списка.
3. Добавить именованный диапазон к проверке данных: Наконец, вы соберете все вместе и определите источник раскрывающегося списка (т. Е. Ранее названный диапазон).
4. Установить входное сообщение для проверки данных: Этот шаг не является обязательным. Это позволяет вам добавить всплывающее сообщение для руководства ваших пользователей электронных таблиц.

Я люблю Excel достаточно, что даже простая проверка данных + условное форматирование радует меня

— x — lily (@ungilded) 25 октября 2017 г.

Excel не легко

это мощно. Это означает, что у вас есть множество вариантов, как вы вскоре увидите.

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

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

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

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

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

2. Добавьте проверку данных

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

Убедитесь, что настройки вкладка активна на Проверка данных диалоговое окно.

3. Добавить именованный диапазон к проверке данных

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

=Food

Заменить «питаниеС любым именем, которое вы дали вашему диапазону клеток. Нажмите Хорошо.

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

Советы Тома для Excel: используйте проверку данных вместо комментария для выбранной ячейки: Alt + D + L> вкладка «Входное сообщение»> введите msg> Ck Показать входные сообщения…

— Том Уртис / Атлас (@TomUrtis) 22 января 2017 г.

4. Установите входное сообщение для проверки данных

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

Нажмите Хорошо.

Когда вы выбираете ячейку

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

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

Расширенные параметры выпадающего списка

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

Изменить или удалить именованный диапазон

Если вам нужно отредактировать или удалить именованный диапазон, вы должны использовать Имя менеджера. Нажмите на Формулы вкладка, а затем нажмите Имя менеджера в Определенные имена раздел.

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

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

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

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

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

Зависимый раскрывающийся список — это список, в котором параметры меняются в зависимости от выбора в другом раскрывающемся списке.

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

Только что узнал, как создать зависимый выпадающий список в Excel, например, cell1-state, cell2-city в этом состоянии. РЕБЯТА! Это меняет жизнь.

— Жизнь в Deloitte (@lifeatdeloitte) 4 февраля 2014 г.

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

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

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

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

Нажмите на Данные вкладка, а затем нажмите Проверка данных в Инструменты данных раздел. Выбрать Список в Разрешать выпадающий список.

Введите следующий текст в Источник коробка. Заменить «$ B $ 2»Со ссылкой на ячейку, содержащую ваш основной выпадающий список. Держите знаки доллара в ячейке ссылки. Это указывает на абсолютную ссылку на ячейку, которая не изменится, даже если вы скопируете или переместите формулу, которая ссылается на эту ячейку.

=INDIRECT($B$2)

Функция INDIRECT возвращает ссылку, указанную в текстовой строке, в данном случае текст из опции, выбранной в основном раскрывающемся списке в ячейке B2. Например, если вы выберете китайский язык из основного выпадающего списка, = ДВССЫЛ ($ B $ 2) возвращает китайский язык ссылка. В результате второй выпадающий список содержит китайский язык Предметы.

Нажмите Хорошо.

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

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

Если вам нужно продублировать раскрывающийся список с проверкой данных на другие ячейки, скопируйте и вставьте ячейку, используя Ctrl + C а также Ctrl + V. Это копирует выпадающий список и форматирование.

Если вы просто хотите скопировать выпадающий список с проверкой данных, но НЕ с форматированием, выберите ячейку и скопируйте ее обычным способом, используя Ctrl + C. Затем перейдите к Главная вкладка и нажмите Вставить в буфер обмена раздел. Выбрать Специальная паста.

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

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

Замечания: Будьте осторожны при работе с выпадающими списками в Excel. При копировании ячейки, в которой нет раскрывающегося списка, в ячейку, содержащую раскрывающийся список, раскрывающийся список теряется. Excel не предупреждает вас и не запрашивает подтверждение действия. Тем не менее, вы можете отменить действие, используя Ctrl + Z.

Выбрать все ячейки, содержащие выпадающие списки

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

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

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

Мы примем выбор по умолчанию Все потому что наши выпадающие списки имеют немного другие правила. Один использует именованный диапазон, чтобы получить его значение, а другой использует функцию INDIRECT.

Нажмите Хорошо.

Наши два выпадающих списка выбраны.

Теперь вы можете отформатировать эти ячейки

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

Сделать стрелку выпадающего списка всегда видимой

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

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

Для начала загрузите выпадающий arrow.png файл (щелкните правой кнопкой мыши по этой ссылке и выберите Сохранить ссылку как). Затем выберите ячейку справа от выпадающего списка и перейдите к Вставить Вкладка.

Затем нажмите иллюстрации и выберите Фотографий.

На Вставить картинку диалоговое окно, перейдите туда, где вы сохранили выпадающий arrow.png файл и выберите файл. Затем нажмите Вставить.

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

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

Если вы решили удалить выпадающий список из ячейки, выберите ячейку и откройте Проверка данных диалоговое окно, как описано ранее, в разделе Как создать выпадающий список (перейдите к Данные вкладка и нажмите Проверка данных в Инструменты данных раздел). Нажмите на Очистить все кнопка, которая доступна независимо от того, какая вкладка выбрана в данный момент.

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

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

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

Заставьте Dropdown Lists работать на вас

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

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

еще?

Как создать выпадающий список в Excel, чтобы ограничить данные

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; и Excel для Mac.

Создать выпадающий список

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

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

Чтобы создать раскрывающийся список:

2. Выберите Данные .

3. Выберите « Проверка данных», чтобы открыть диалоговое окно «Проверка данных».

4. Выберите вкладку « Настройки  ».

5. В разделе «Разрешить» выберите стрелку вниз.

6. Выберите  Список .

7. Поместите курсор в текстовое поле «Источник».

8. Выделите ячейки от E3 до  E10 на листе, чтобы добавить данные из этого диапазона ячеек в список.

9. Выберите ОК . За исключением Excel для Mac, где вы выбираете Готово .

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

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

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

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

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

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

2. Выберите Данные .

3. Выберите « Проверка данных», чтобы открыть диалоговое окно «Проверка данных».

4. Выберите вкладку « Настройки ».

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

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

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

Раскрывающийся список Excel — обзор, простое пошаговое руководство

Что такое раскрывающийся список Excel?

Раскрывающийся список Excel — это функция проверки данных, которая позволяет пользователям выбирать вариант из списка вариантов. Это может быть особенно полезно при выполнении финансового моделирования. Что такое финансовое моделирование? Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем строить модель. и анализ путем включения сценариев «Динамические даты», «Сумма», «Среднее значение» и «Сценарии» Узнайте, как создавать динамические даты, суммы, средние значения и сценарии в Excel.Финансовым аналитикам в области инвестиционно-банковских услуг, исследований в области капитала, планирования и анализа и корпоративного развития выгодно изучать передовые навыки работы с Excel, поскольку это выделяет вас среди конкурентов. В этой статье мы рассмотрим некоторые из способов сделать электронную таблицу более динамичной.

Научитесь встраивать раскрывающиеся списки в модели в продвинутом курсе CFI по Excel.

Как создать раскрывающийся список в Excel

Ниже приведены пошаговые инструкции по созданию раскрывающегося списка в Excel:

Шаг 1. Создайте список параметров

Создайте вертикальный список список опций, из которых вы хотите, чтобы пользователи могли выбирать.Как вы можете видеть в нашем простом примере, мы создали список городов со всего мира.

Шаг 2. Выберите ячейку и просмотрите проверку данных на ленте в Excel — это существующий и доступный инструмент для обеспечения целостности данных при построении финансовой модели. Это помогает защитить входные и выходные данные вашей модели.функция, расположенная на ленте данных. Сочетание клавиш на компьютере с Windows — Alt, A, V, V.

Шаг 3. Выберите «Список» и свяжите данные

Когда появится диалоговое окно проверки данных, вам нужно будет выбрать «Список», где он спрашивает вас, что вы хотите «Разрешить» в ячейке.

Как только это будет сделано, вы можете выбрать данные, которые хотите включить в качестве параметров, которые появятся в списке. В нашем примере варианты — это города, перечисленные в ячейках B5:B13.

Затем нажмите OK, и настройка завершена.

Узнайте больше пошаговых уроков в продвинутом курсе CFI по Excel.

Шаг 4. Форматирование раскрывающегося списка Excel

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

Когда вы закончите, начните экспериментировать с новой крутой функцией, которую вы создали!

Приложения для финансового моделирования

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

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

Узнайте больше пошаговых уроков в продвинутом курсе CFI по Excel.

Дополнительные ресурсы

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

• Рекомендации по моделированию в ExcelРекомендации по моделированию в ExcelСледующие рекомендации по моделированию в Excel позволяют пользователю обеспечить наиболее чистый и удобный процесс моделирования. Microsoft Excel — чрезвычайно надежный инструмент. Обучение опытному пользователю Excel является почти обязательным для тех, кто работает в сфере инвестиционно-банковских услуг, корпоративных финансов и прямых инвестиций.
• Ярлыки Excel для ПК и MacExcel Ярлыки ПК MacExcel Ярлыки — список наиболее важных и распространенных ярлыков MS Excel для пользователей ПК и Mac, финансовых и бухгалтерских профессий.Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.
• Руководство по функциям ExcelФункцииСписок наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel.Функция =ВПР(искомое значение, диапазон таблицы, номер столбца). Например, «ищите эту часть информации в следующей области и дайте мне соответствующие данные в другом столбце».

Добавление раскрывающегося списка с цветовым форматированием в Excel и Google Таблицах

Просмотреть все статьи с инструкциями

 =СМЕЩЕНИЕ($A$2,0,0, СЧЁТ($A$2:$A$21), 1)