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

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

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

Пример создания зависимого выпадающего списка в ячейке Excel

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

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

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

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

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



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

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

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

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

Для того чтобы этого достичь, необходимо сделать немного другую таблицу данных, чем если бы мы создавали один раскрывающийся список. Таблица должна выглядеть так (диапазон G2:h25):

Рабочая исходная таблица Excel

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

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

Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.

1. Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

1. Выберите диапазон A3:A5.
2. В поле имени (поле слева от строки формулы) введите название «Категория».
3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.

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

Это будет просто:

1. Выберите ячейку, в которую вы хотите поместить список. В моем случае это A12.
2. В меню «ДАННЫЕ» выберите инструмент «Проверка данных». Появится окно «Проверка вводимых значений».
3. В качестве типа данных выберите «Список».
4. В качестве источника введите: =Категория (рисунок ниже).
5. Подтвердите с помощью OK.

Проверка вводимых значений – Категория.

Результат следующий:

Раскрывающийся список для категории.

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

Сейчас будет весело. Создавать списки мы умеем — только что это сделали для категории. Только единственный вопрос: «Как сказать Excelю выбрать только те значения, которые предназначены для конкретной категории?» Как вы, наверное, догадываетесь, я буду использовать здесь рабочую таблицу и, конечно же, формулы.

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

В источник списка введите следующую формулу:

Вид окна «Проверка вводимых значений»:

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

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

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

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:h21, для Транспорта — диапазон h22: h25 и т. д. Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке).

Начало диапазона будет перемещено относительно ячейки h3 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки h3 (начинается с 4 ячейки от h3). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне — это его высота. Вот функция:

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $h4: $h25. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Вот и все:

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

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

Читайте также: Связанные выпадающие списки и формула массива в Excel

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?

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

Содержание

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

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

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

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

Посмотреть видео — Создание раскрывающегося списка в Excel

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

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

В этом разделе вы узнаете, как создать раскрывающийся список Excel:

1. Использование данных из ячеек.
2. Ввод данных вручную.
3. Используя формулу СМЕЩЕНИЕ.

# 1 Использование данных из ячеек

Допустим, у вас есть список предметов, как показано ниже:

Вот шаги для создания раскрывающегося списка Excel:

1. Выберите ячейку, в которой вы хотите создать раскрывающийся список.
2. Перейдите в Data -> Data Tools -> Data Validation.
3. В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
   • Как только вы выбираете Список, появляется исходное поле.
4. В поле «Источник» введите = $ A $ 2: $ A $ 6 или просто щелкните поле «Источник», выберите ячейки с помощью мыши и нажмите «ОК». Это вставит раскрывающийся список в ячейку C2.
   • Убедитесь, что в раскрывающемся списке установлен флажок (по умолчанию). Если этот параметр не установлен, в ячейке не отображается раскрывающийся список, однако вы можете вручную ввести значения в список.

Примечание: Если вы хотите создать раскрывающиеся списки в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните указанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2, или A $ 2, или $ A2).

# 2 Ввод данных вручную

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

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

• Выберите ячейку, в которой вы хотите создать раскрывающийся список (в этом примере ячейка C2).
• Перейдите в Data -> Data Tools -> Data Validation.
• В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
  • Как только вы выбираете Список, появляется исходное поле.
• В поле источника введите Да, Нет
  • Убедитесь, что в раскрывающемся списке установлен флажок.
• Щелкните ОК.

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

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

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

# 3 Использование формул Excel

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

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

Например, предположим, что у вас есть набор данных, как показано ниже:

Вот шаги для создания раскрывающегося списка Excel с помощью функции СМЕЩЕНИЕ:

• Выберите ячейку, в которой вы хотите создать раскрывающийся список (в этом примере ячейка C2).
• Перейдите в Data -> Data Tools -> Data Validation.
• В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
  • Как только вы выбираете Список, появляется исходное поле.
• В поле Источник введите следующую формулу: = СМЕЩЕНИЕ ($ A $ 2,0,0,5)
  • Убедитесь, что в раскрывающемся списке установлен флажок.
• Щелкните ОК.

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

Примечание: Если вы хотите создать раскрывающийся список из нескольких ячеек за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните указанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2, или A $ 2, или $ A2).

Как работает эта формула ??

В приведенном выше случае мы использовали функцию OFFSET для создания раскрывающегося списка. Он возвращает список элементов из ра

Он возвращает список элементов из диапазона A2: A6.

Вот синтаксис функции СМЕЩЕНИЕ: = СМЕЩЕНИЕ (ссылка, строки, столбцы, [высота], [ширина])

Он принимает пять аргументов, где мы указали ссылку как A2 (начальная точка списка). Строки / столбцы указаны как 0, поскольку мы не хотим смещать ссылочную ячейку. Высота указана как 5, так как в списке пять элементов.

Теперь, когда вы используете эту формулу, она возвращает массив со списком пяти фруктов в A2: A6. Обратите внимание: если вы введете формулу в ячейку, выделите ее и нажмете F9, вы увидите, что она возвращает массив названий фруктов.

Создание динамического раскрывающегося списка в Excel (с помощью OFFSET)

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

Вот способ сделать его динамичным (и это всего лишь небольшая поправка в формуле):

• Выберите ячейку, в которой вы хотите создать раскрывающийся список (в этом примере ячейка C2).
• Перейдите в Data -> Data Tools -> Data Validation.
• В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки. Как только вы выбираете Список, появляется исходное поле.
• В поле источника введите следующую формулу: = СМЕЩЕНИЕ ($ A $ 2,0,0, СЧЁТЕСЛИ ($ A $ 2: $ A $ 100, ””))
• Убедитесь, что в раскрывающемся списке установлен флажок.
• Щелкните ОК.

В этой формуле я заменил аргумент 5 на СЧЁТЕСЛИ ($ A $ 2: $ A $ 100, ””).

Функция СЧЁТЕСЛИ подсчитывает непустые ячейки в диапазоне A2: A100. Следовательно, функция СМЕЩЕНИЕ настраивается для включения всех непустых ячеек.

Примечание:

• Для этого НЕ ДОЛЖНО быть пустых ячеек между заполненными ячейками.
• Если вы хотите создать раскрывающийся список из нескольких ячеек за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните указанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2, или A $ 2, или $ A2).

Копировать вставку раскрывающихся списков в Excel

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

Например, если у вас есть раскрывающийся список в ячейке C2, и вы хотите применить его также к C3: C6, просто скопируйте ячейку C2 и вставьте ее в C3: C6. Это скопирует раскрывающийся список и сделает его доступным в C3: C6 (вместе с раскрывающимся списком он также скопирует форматирование).

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

• Скопируйте ячейку с раскрывающимся списком.
• Выберите ячейки, в которые вы хотите скопировать раскрывающийся список.
• Перейдите на главную -> Вставить -> Специальная вставка.
• В диалоговом окне «Специальная вставка» выберите «Проверка в параметрах вставки».
• Щелкните ОК.

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

Осторожно при работе с раскрывающимся списком Excel

Будьте осторожны при работе с раскрывающимися списками в Excel.

Когда вы копируете ячейку (не содержащую раскрывающегося списка) поверх ячейки, содержащей раскрывающийся список, раскрывающийся список теряется.

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

Как выбрать все ячейки, в которых есть раскрывающийся список

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

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

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

• Перейдите на главную -> Найти и выбрать -> Перейти к специальному.
• В диалоговом окне «Перейти к специальному» выберите «Проверка данных».
  • У проверки данных есть два варианта: Все и Одинаково. All выберет все ячейки, к которым применено правило проверки данных. То же самое будет выбирать только те ячейки, которые имеют то же правило проверки данных, что и для активной ячейки.
• Щелкните ОК.

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

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

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

Создание зависимого / условного раскрывающегося списка Excel

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

Если вы предпочитаете читать, а не смотреть видео, продолжайте читать.

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

Они называются зависимыми или условными раскрывающимися списками.

Ниже приведен пример условного / зависимого раскрывающегося списка:

В приведенном выше примере, когда элементы, перечисленные в «Выпадающем списке 2», зависят от выбора, сделанного в «Выпадающем списке 1».

Теперь давайте посмотрим, как это создать.

Вот шаги для создания зависимого / условного раскрывающегося списка в Excel:

• Выберите ячейку, в которой вы хотите отобразить первый (основной) раскрывающийся список.
• Перейдите в Data -> Data Validation. Откроется диалоговое окно проверки данных.
• В диалоговом окне проверки данных на вкладке настроек выберите Список.
• В поле «Источник» укажите диапазон, содержащий элементы, которые должны отображаться в первом раскрывающемся списке.
• Щелкните ОК. Это создаст выпадающий список 1.
• Выберите весь набор данных (A1: B6 в этом примере).
• Перейдите в Формулы -> Определенные имена -> Создать из выделенного (или вы можете использовать сочетание клавиш Control + Shift + F3).
• В диалоговом окне «Создать имя из выделенного» установите флажок «Верхняя строка» и снимите все остальные флажки. В результате будут созданы 2 диапазона имен («Фрукты» и «Овощи»). Именованный диапазон фруктов относится ко всем фруктам в списке, а именованный диапазон овощей относится ко всем овощам в списке.
• Щелкните ОК.
• Выберите ячейку, в которой вы хотите создать раскрывающийся список «Зависимые / условные» (в данном примере — E3).
• Перейдите в Data -> Data Validation.
• Убедитесь, что в диалоговом окне «Проверка данных» на вкладке настроек установлен флажок «Список».
• В поле Источник введите формулу = КОСВЕННО (D3). Здесь D3 — это ячейка, содержащая главное раскрывающееся меню.
• Щелкните ОК.

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

Загрузите файл примера

Как это работает? — Условный раскрывающийся список (в ячейке E3) относится к = КОСВЕННО (D3). Это означает, что когда вы выбираете «Фрукты» в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон «Фрукты» (с помощью функции КОСВЕННО) и, следовательно, перечисляет все элементы в этой категории.

Важное примечание при работе с условными раскрывающимися списками в Excel:

• Когда вы сделали выбор, а затем изменили родительский раскрывающийся список, зависимый раскрывающийся список не изменится и, следовательно, будет неправильной записью. Например, если вы выберете США в качестве страны, а затем выберите Флориду в качестве штата, а затем вернетесь и измените страну на Индию, штат останется как Флорида. Вот отличный учебник от Дебры по очистке зависимых (условных) раскрывающихся списков в Excel при изменении выбора.
• Если основная категория состоит из более чем одного слова (например, «Сезонные фрукты» вместо «Фрукты»), тогда вам необходимо использовать формулу = КОСВЕННО (ПОДСТАВИТЬ (D3, ”“, ”_”)) вместо простая функция КОСВЕННО, показанная выше. Причина этого в том, что Excel не допускает пробелов в именованных диапазонах. Поэтому, когда вы создаете именованный диапазон, используя более одного слова, Excel автоматически вставляет подчеркивание между словами. Таким образом, диапазон с названием «Сезонные фрукты» будет называться «Сезонные_фрукты». Использование функции ПОДСТАВИТЬ в функции ДВССЫЛ позволяет убедиться, что пробелы являются преобразованы в символы подчеркивания.

Как в Excel сделать выпадающий список (простой или зависимый) с выбором нескольких значений?

Содержание

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

Доброго времени на fast-wolker. ru! В процессе рутинной работы с таблицами всегда возникает желание ускорить ввод значений. Особенно это касается наименований позиций. Зачастую мы имеем один и тот же список,  с которыми постоянно приходится работать, а новые добавляются постепенно.

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

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

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

Ставим курсор на  следующую пустую ячейку под списком кликаем правой кнопкой мыши;  из контекстного меню выбираем команду. Или используем сочетание клавиш «Alt+ Стрелка вниз». Такой способ выбора из списка использовать приходиться не слишком часто. Наименования ведь редко повторяются.

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

 Как в эксель сделать выпадающий список с другого листа?

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

Выделяем нужную область и в верхней левом верхнем углу присваиваем  ей имя «Список_Кондит». У нас есть теперь первый ИМЕНОВАННЫЙ диапазон в нашей книге эксель. Чтобы  потом диапазонами управлять,  нужно открыть вкладку «Формулы» и найти там  «Диспетчер имен»:

Следующий шаг — идем обратно в наш лист где создана  основная таблица. Выделяем в графе нужные строки.  Идем во вкладку «Данные»- «Проверка данных». В строке «тип данных» выбираем «Список»….

А в строке «Источник»  копируем адрес нашего диапазона из «диспетчера имен»…

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

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

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

Сначала сделаем в листе «база» наши  будущие списки. У нас  будет три категории товара — полуфабрикаты, рыба и хлебобулочные. И мы заполним ими   три графы.

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

Идем в «Данные -«Проверка данных». Укажем в качестве диапазона только строку с названиями категорий.

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

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

Ставим курсор в строку из  «Полуфабрикаты,  затем открываем вкладку «Главная» и жмем на  кнопку «Форматировать как таблицу». Дизайн выбираем любой.  Задаем по столбцу нужный вертикальный диапазон, включая  только строки с наименованиями;  ничего кроме диапазона не меняем.

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

Повторяем действо для граф «Рыба» и «Хлебобулочные».

Завешающий этап.  Встаем в  нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на  соседнюю ячейку , т.е на ранее созданный список, но…

… с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки.  Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»). Чтобы распространить в таком виде на остальные строки, нужно будет указывать для каждой строки свой источник данных. Это долго. Можно аккуратно убрать абсолютную адресацию руками из «источника» и после скопировать ячейку на остальные строки ниже. Тогда «формула»  распространится корректно.

Попробуйте добавить в базу новые позиции; проверьте — появляются ли они теперь автоматически в списке после указании категорий? Должно получиться.

 

Делаем выпадающий список в ячейке  эксель с выбором по первой букве

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

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

Курсор мыши  приобретет  форму крестика. Побудем немного программистами — нарисуем этот элемент в нужной ячейке — так и делаются формы и и окошки которые привычны нам уже давно.

В свойствах этого элемента делаем настройки — указываем наш именованный диапазон,  цвет фона, шрифта, есть и другие настройки.

• ListFillRange —  сюда вставляем наш именованный диапазон из «диспетчера имен»;
• LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
• ListRows — количество отображаемых строк в списке;
• Font —  выбираем шрифт, размер, стиль;
• ForeColor и BackColor — цвет текста и фона;
• ListRow —  количество возвращаемых  строк в списке;
• PrintObject —  определяет — выводить элемент управления на печать или нет (истина или ложь)

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

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

Автор публикации

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

Проверка > Раскрывающиеся списки > Зависимый

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

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

Видео: Зависимые раскрывающиеся списки

Начало работы: Зависимые раскрывающиеся списки

• Настройка 2 листов в рабочей книге
• Создание списков для раскрывающихся списков
• Настроить основной выпадающий список
• Добавить зависимые выпадающие списки
• Проверка раскрывающихся списков

Расширенные зависимые раскрывающиеся списки

• Использовать элементы из двух слов
• Использовать недопустимые символы
• Добавить третий зависимый раскрывающийся список
• Использовать динамические списки источников
• Зависимое раскрывающееся меню IF Formula
• Показать длинный или короткий список

Предотвратить неверный выбор

Загрузить образцы файлов

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

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

Например:

• Фрукты были выбраны в качестве типа продукта в ячейке B3, и в раскрывающемся списке был показан список фруктов в ячейке D3
• Затем в ячейке B4 в качестве типа продукта было выбрано Овощи . В ячейке C4 зависимые выпадающие списки изменились автоматически, и теперь он показывает список овощей
.

Инструкции по установке и видео в разделах ниже.

Видео: Зависимые раскрывающиеся списки

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

Начало работы: зависимые раскрывающиеся списки

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

На приведенном ниже снимке экрана показан заполненный лист ввода данных.

• Выберите тип продукта (фрукты или овощи) в столбце B из первого раскрывающегося списка
• Затем в столбце C зависимый раскрывающийся список показывает элементы только для выбранного типа продукта.

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

• Настройка 2 листов в книге
• Создание списков для раскрывающихся списков
• Настроить основной выпадающий список
• Добавить зависимые выпадающие списки
• Проверка раскрывающихся списков

Настройка рабочих листов

В этом примере будет рабочая книга с двумя листами — лист ввода данных и лист со списками.

1. Создать новую книгу
2. Изменить имя для Sheet1 на «DataEntry»
3. Вставьте новый лист в рабочую книгу и назовите этот лист «Списки»

Создание списков

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

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

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

1. Введите элементы на листе списков
2. Форматировать списки как таблицу Excel
3. Назовите списки.

1. Введите списки

Чтобы ввести списки, перейдите на лист Списки. Начните с основного списка — Типы продуктов.

1. В ячейке B2 введите заголовок основного списка, Список продуктов
2. В ячейках B3 и B4 введите Фрукты и овощи.

ПРИМЕЧАНИЕ : В списке продуктов однословных элемента в нем — Фрукты и овощи. Это важно, потому что эти слова будут использоваться в качестве имен Excel, а имена из двух слов не допускаются в качестве имен . Если вам нужно используйте элементы из нескольких слов в основном списке, см. : Использование элементов из двух слов

Добавление зависимых списков

Затем введите зависимые списки с заголовками «Список фруктов» (в ячейке D2) и «Список овощей» (в ячейке F2). Эти списки могут содержать элементы из одного слова (яблоко) или из нескольких слов (зеленая фасоль).

2. Форматирование списков в виде таблиц

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

Выполните следующие действия для каждого из 3 списков:

1. Выберите ячейку в списке и на вкладке Главная щелкните Форматировать как таблицу
2. Нажмите на один из стилей таблицы
3. Добавить галочку для «Моя таблица имеет заголовки»
4. Нажмите ОК

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

Название списка продуктов

1. Выберите элементы в таблице «Производство» (ячейки B3:B4), но не заголовок таблицы.
2. Щелкните в поле Имя слева от строки формул
3. Введите название списка, состоящее из одного слова — Произвести
4. Нажмите клавишу Enter, чтобы завершить ввод имени.

Назовите списки фруктов и овощей

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

1. Выберите названия фруктов в ячейках D3:D6
2. Щелкните в поле Имя слева от строки формул
3. Введите название списка из одного слова — Fruit .
4. Нажмите клавишу Enter, чтобы завершить ввод имени.
5. Выберите названия овощей в ячейках F3:F6.
6. Щелкните в поле Имя слева от строки формул
7. Введите имя списка из одного слова — Овощи .
8. Нажмите клавишу Enter, чтобы завершить ввод имени.

Добавить основной раскрывающийся список

Далее вы добавите основной раскрывающийся список (Тип продукта) на лист ввода данных. Это обычный выпадающий список проверки данных.

Начните лист ввода данных

Сначала вы введете заголовки в лист ввода данных и настроите именованную таблицу.

1. На листе ввода данных введите заголовки в B2:C2 — Тип продукта и Артикул
2. Выберите ячейку B2 и на вкладке Главная щелкните Форматировать как таблицу
3. Нажмите на один из стилей таблицы
4. Добавить галочку для «Моя таблица имеет заголовки»
5. Нажмите OK

Добавить основной раскрывающийся список

Далее вы увидите основной раскрывающийся список в столбце Тип продукта

1. На листе ввода данных выберите ячейку B3
2. На ленте щелкните вкладку «Данные», затем щелкните «Проверка данных».
3. В раскрывающемся списке Разрешить выберите Список
.
4. В поле Источник введите знак равенства и имя списка: = Произвести
5. Нажмите OK, чтобы завершить настройку проверки данных.
6. Чтобы протестировать раскрывающийся список, щелкните стрелку в ячейке B3 и выберите Fruit из списка

Добавить зависимый раскрывающийся список

Далее вы создадите зависимый раскрывающийся список в столбце Элемент. В этой ячейке будет формула для создания раскрывающегося списка проверки данных. Подробнее о функции ДВССЫЛ читайте здесь.

1. На листе ввода данных выберите ячейку C3
2. На ленте щелкните вкладку «Данные», затем щелкните «Проверка данных».
3. В раскрывающемся списке Разрешить выберите Список
.
4. В поле Источник введите знак равенства и функция ДВССЫЛ , ссылаясь на первую ячейку данных в столбце «Тип продукта»:
   . В этом примере активна ячейка C3, поэтому формула будет ссылаться на Ячейка типа продукта в строке 3: = ДВССЫЛ (B3)
   Примечание . Если щелкнуть ячейку B3, Excel добавит абсолютную ссылку — $B$3 . Удалите знаки $, потому что нам нужна относительная ссылка в этой формуле
5. Нажмите OK.

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

Проверка раскрывающихся списков

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

1. Фрукты должны быть выбраны в ячейке B3. Если нет, выберите их сейчас.
2. Выберите ячейку C3, затем щелкните стрелку и выберите один из фруктов из списка.

3. Затем нажмите клавишу Tab, чтобы начать новую строку в таблице ввода данных.
4. В столбце «Тип продукта» выберите «Овощи» в раскрывающемся списке «Продукт».
5. Перейти к столбцу Элемент, в том же ряду.
6. В столбце «Элемент» щелкните стрелку раскрывающегося списка и выберите элемент из списка «Овощи»

ПРИМЕЧАНИЕ . Если тип продукта не выбран, раскрывающийся список «Элемент» в этом ряду работать не будет.

Дополнительные зависимые раскрывающиеся списки

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

• Использовать элементы из двух слов
• Использовать недопустимые символы
• Добавить третий зависимый раскрывающийся список
• Использовать динамические списки источников
• Зависимая раскрывающаяся формула IF
• Показать длинный или короткий список

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

В некоторых рабочих книгах может потребоваться наличие элементов из двух слов в первом Раскрывающийся список проверки данных Excel. Например, ваш выбор: «Красный Фрукты», «Зеленые фрукты» и «Желтые фрукты»

1. Создайте первый именованный диапазон и раскрывающийся список, как описано выше.
2. Создайте вспомогательные именованные списки, используя имена из одного слова, например. красныйфрукт, Зеленые фрукты, желтые фрукты
3. Во втором раскрывающемся списке выберите Разрешить: Список и используйте формулу который удаляет пробелы из имен. Например:

   =ДВССЫЛ(ЗАМЕНА(A2,» «,»»))

Основные элементы с недопустимыми символами

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

Например, вы можете выбрать «Красные фрукты», «Зеленые фрукты» и «Желтые фрукты». & Апельсин’. Для зависимых списков вы можете создавать диапазоны с однословными именами, такими как YOФрукты . Затем создайте таблицу поиска, в котором перечислены все элементы в первом раскрывающемся списке проверки данных Excel, и диапазон, в котором будут храниться его зависимые элементы.

Для начала создайте списки элементов и первую проверку данных Excel раскрывающийся список:

1. Создайте первый именованный диапазон и раскрывающийся список, как описано выше. В этом примере диапазон называется ProductList со значениями в ячейках A6:A8. Раскрывающийся список в ячейке A2 использует ProductList как его источник.
2. Создайте зависимые списки и назовите их, используя имена из одного слова, со «Списком» в конце, например. RedFruitList , GreenFruitList , YOFruitList . В этом примере RedFruitList находится в A11:A12, YOFruitList находится в формате A15:A16, а GreenFruitList — в формате A19:20.
3. Выберите элемент из раскрывающегося списка в ячейке A2.

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

1. В столбце справа от диапазона FruitList введите код имя зависимого списка каждого элемента. Например, YOFruit вводится как кодовое название Yellow & Orange Fruit.
2. Назовите таблицу поиска. В этом примере диапазон A6:B8 называется Поиск продукта .
3. Выберите ячейку B2 и в меню «Данные» выберите «Проверка».
4. Выберите Разрешить: Список.
5. В поле Источник введите формулу, которая использует формулу ВПР для поиска имя диапазона зависимого списка — комбинация кодового имени и «Список».
   Например:    =ДВССЫЛ(ВПР(A2,Просмотр продукта,2,0) &»Список»)

Если в ячейке A2 выбрано значение Red Fruit , формула VLookup будет вернуть RedFruitList в качестве имени диапазона для зависимого списка. Элементы RedFruitList будут отображаться в раскрывающемся списке ячейки B2.

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

На приведенном ниже снимке экрана показаны таблицы поиска с именем RedFruitLookup, YOFruitLookup и GreenFruitLookup. Списки предметов с кодовыми названиями и «Список» были добавлены на лист.

В окне проверки данных используйте следующую формулу для третьего раскрывающегося списка:

= ДВССЫЛ(ВПР(B2, ДВССЫЛ(ВПР(A2,Просмотр продукта,2,0)&»Поиск»)),2,0 ) &»Список»)

Формула находит справочную таблицу на основе выбранного типа продукта в ячейке A2, например. RedFruitLook и получает код выбранного товара — Мак. Он добавляет «Список» в код и показывает элементы в ассортимент Маклист.

Добавить третий зависимый раскрывающийся список

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

Обратите внимание, что названия регионов не уникальны — Западный регион есть и в Канаде, и в США — поэтому мы не можем отобразить список городов, основанный только на названиях регионов.

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

ПРИМЕЧАНИЕ . Если вам нужно больше нескольких именованных диапазонов для списков 3-го уровня, попробуйте метод зависимых списков с таблицами — его будет проще настроить и поддерживать.

Для настройки 3-х уровневых зависимых списков:

1. Создайте основные именованные диапазоны — Страна, Канада и США, как описано выше.
2. Создайте еще один набор именованных диапазонов, назвав их для доступных комбинации из первых двух списков. За Например, создайте диапазоны с именами CanadaWest и USAWest.

3. На листе ввода данных настройте раскрывающиеся списки в столбцах Страна и Регион.
4. В раскрывающемся списке Город добавьте проверку данных и выберите Разрешить: Список
5. Для источника используйте формулу который объединяет записи в первых двух столбцах.
   Например, в ячейке D2 проверка данных формула использует ДВССЫЛ и ЗАМЕНУ, чтобы объединить значения в B2 и C2 и удалить все символы пробела:

  = НЕПРЯМОЙ(ЗАМЕНИТЬ(B2&C2,» «,»»))

Использовать динамические списки

Поскольку функция ДВССЫЛ работает только со ссылками, а не с формулами, предыдущий метод зависимой проверки данных не будет работать со списками которые используют динамические именованные диапазоны, такие как диапазоны OFFSET. Используйте одно из следующих решений, когда требуется динамический список:

• Именованные таблицы Excel (лучший вариант)
• Обходной путь динамического имени (если вы не можете использовать таблицы)

Именованные таблицы Excel

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

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

Затем используйте один из показанных выше примеров функции ДВССЫЛ, чтобы создать зависимый раскрывающийся список.

Обход динамического имени

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

1. Создайте первый именованный диапазон и раскрывающийся список, как описано выше.
2. Создайте поддерживающие именованные списки и назовите первую ячейку в каждом диапазон, например ячейка B1 называется «Фрукты», а ячейка C1 — «Овощи».

3. Назовите столбец, в котором находится каждый список, например. столбец Б с именем FruitCol, а столбец C — с именем VegetablesCol 9.0014
4. Во втором раскрывающемся списке выберите Разрешить: Список и используйте формулу который вычисляет диапазон поиска. Например, если первое раскрывающееся меню список находится в ячейке E2:

=СМЕЩ(ДВССЫЛ($E2),0,0, СЧЕТЧ(ДВССЫЛ(E2&»Col»)),1)

если будут использоваться элементы из двух слов, вы можете включить функцию ПОДСТАВИТЬ в формуле:

=СМЕЩ(ДВССЫЛ(ПОДСТАВИТЬ($F2,» «,»»)),0,0, СЧЕТЧ(ДВССЫЛ(ПОДСТАВИТЬ($F2, » «,»») &»Столб»)),1)

Зависимый раскрывающийся список с формулой ЕСЛИ

Это еще один пример зависимых раскрывающихся списков в Excel с функцией ЕСЛИ, используемой с ДВССЫЛ, для более гибкого раскрывающегося списка. В этом примере:

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

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

Выберите страны и города

В этом примере есть раскрывающийся список для выбора страны в столбце A. Если вы выберете США в качестве страны, города из США появятся в зависимом раскрывающемся списке в столбце B.

В книге есть списки городов, и каждый список назван в соответствии с названием страны.

• США : ячейки C2:C5
• Канада : ячейки E2:E4

Формула простого зависимого выпадающего списка

Изначально в столбце B была простая формула для списка проверки данных:

= ДВССЫЛ(A2)

Итак, если вы выбрали USA3 A2, зависимый раскрывающийся список показывает города из диапазона с названием USA .

Страна не выбрана

При использовании этой простой формулы ДВССЫЛ раскрывающийся список в столбце B НЕ работает, если в столбце A не выбрана страна.

• При нажатии на стрелку раскрывающегося списка в ячейке B3 ничего не происходит.
• Поскольку ячейка A3 пуста, формула ДВССЫЛ возвращает ошибку, поэтому раскрывающийся список не работает.

Добавить функцию ЕСЛИ в формулу

Вместо неработающей стрелки раскрывающегося списка мы изменим формулу зависимого раскрывающегося списка и добавим функцию ЕСЛИ. Пересмотренная формула даст пользователям возможность выбрать Мир город, если они не выбрали страну в столбце A.

На листе списков есть еще один диапазон с именем Мир, выделенный на снимке экрана ниже.

Так же, как вы можете использовать функцию ЕСЛИ на листе, вы можете использовать ее в формуле проверки данных.

Для зависимых раскрывающихся ячеек в столбце B мы изменим формулу на следующую:

• =ЕСЛИ(A2=»»,Мир,ДВССЫЛ(A2))

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

Как работает формула ЕСЛИ

Вот что делает измененная формула:

• Сначала функция ЕСЛИ проверяет ячейку в столбце А, чтобы увидеть, является ли она пустой — равной пустой строке «»
• ЕСЛИ ячейка пуста, в раскрывающемся списке проверки данных будет показан список из диапазона с именем World
• В противном случае показать список, названный в честь страны, выбранной в ячейке A2, с помощью функции ДВССЫЛ

Показать длинный или короткий раскрывающийся список

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

В этом коротком видео показаны шаги, а под видео есть записанные шаги.

Настройка списков

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

1. Введите полный список клиентов
2. Выберите все ячейки в этом списке и назовите диапазон: FullList
3. В другом столбце введите «Полный список» в строке 2.
4. Начиная со строки 3, введите краткий список основных клиентов
5. Выберите все ячейки в этом списке, включая ячейку «Полный список»
6. Назовите этот диапазон: ShortList

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

Далее вы создадите раскрывающийся список, используя проверку данных с помощью формулы ЕСЛИ:

1. Выберите ячейки, в которых вы хотите разместить раскрывающийся список клиентов
2. На ленте щелкните вкладку Данные, затем щелкните Проверка данных
3. В диалоговом окне «Проверка данных» на вкладке «Параметры» в разделе «Разрешить» выберите «Список»
.
4. В поле Источник введите формулу ЕСЛИ, которая ссылается на активную ячейку и именованные списки.
   • Если вы используете список в одной ячейке, используйте абсолютную ссылку
     • =ЕСЛИ($E$3=»Полный список», Полный список, Краткий список)
   • Для нескольких ячеек используйте относительную ссылку
     • =ЕСЛИ(E3=»Полный список», Полный список, Короткий список)
5. Нажмите OK, чтобы закрыть диалоговое окно.

Использование раскрывающегося списка

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

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

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

Предотвратить неверный выбор

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

Например, «Фрукты» можно выбрать в основном раскрывающемся списке, а затем «Лимон» в зависимом раскрывающемся списке. Если позже Фрукты изменить на Овощи, выбор Лимона будет неправильным — это не овощ.

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

• Блокировать изменения в главном раскрывающемся списке (формула)
• Очистить зависимую ячейку, если основной раскрывающийся список изменен (макрос)

Блокировать изменения в первом Выпадающий список

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

Блокировать изменения в первом раскрывающемся списке

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

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

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

• Если ячейка «Элемент» пуста, в раскрывающемся списке «Тип продукта» отобразится Список товаров
• Если элемент был выбран, раскрывающийся список попытается отобразить диапазон, созданный функцией ДВССЫЛ — «FakeRange». Потому что нет диапазона с таким именем, результат — ошибка, и выпадающий не получится.

Чтобы изменить формулу:

1. Выберите ячейки проверки данных в первом столбце
2. На вкладке «Данные ленты» нажмите «Проверка данных».
3. Для Разрешить выберите Список
4. В поле Формула введите:

=ЕСЛИ(C2=»»,Производить, ДВССЫЛ(«ПоддельныйДиапазон»))

5. Нажмите OK

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

Очистить зависимую ячейку после выбора

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

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

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

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

Добавить код события для очистки ячейки клиента

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

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

ПРИМЕЧАНИЕ . Образец рабочей книги также содержит код для очистки нескольких зависимых ячеек .

 Private Sub Worksheet_Change 
 _ (ByVal Target As Range)
При ошибке Возобновить Далее
Если Цель.Колонка = 2 Тогда
  Если Target.Validation.Type = 3 Тогда
   Приложение.EnableEvents = Ложь
   Цель.Смещение(0, 1).ClearContents
  Конец, если
Конец, если
обработчик выхода:
  Приложение.EnableEvents = Истина
  Выйти из подпрограммы
End Sub 

Загрузить образцы файлов

1. Загрузить образец файла для зависимых проверка данных. Введение. Фрукты/овощи, пример 9.0014
2. Загрузите образец файла для зависимого раскрывающегося списка с IF — пример города/страны/мира
3. Загрузите образец файла для зависимых проверка данных Advanced — 3-й список; 2-слово/недопустимый
4. Загрузите образец файла для зависимых проверка данных с помощью динамических списков — пример фруктов/других продуктов/овощей
5. Загрузите образец файла для Clear Зависимая ячейка после выбора
6. Загрузите образец файла для короткого или длинного раскрывающегося списка клиентов.

ПРИМЕЧАНИЕ. Существуют другие методы настройки зависимых раскрывающихся списков, поэтому посмотрите, какой метод настройки лучше всего подходит для ваших нужд.

Дополнительные руководства

Основы проверки данных

Индексная страница проверки данных

Изменить название продукта на код

Выбор зависимого раскрывающегося списка

Зависимый раскрывающийся список из динамических массивов

Зависимые выпадающие списки Видео

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

Зависимые списки с ИНДЕКСОМ

Скрыть ранее использовавшиеся элементы в раскрывающемся списке

Использовать список проверки данных из другой рабочей книги Советы

Проверка данных с помощью поля со списком

 

Создание зависимого раскрывающегося списка в Excel [Пошаговое руководство]

Смотреть видео — Создание зависимого раскрывающегося списка в Excel

Раскрывающийся список Excel — полезная функция при создании форм ввода данных или информационных панелей Excel.

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

Ниже приведен пример раскрывающегося списка Excel:

В приведенном выше примере я использовал элементы в A2:A6 для создания раскрывающегося списка в C3.

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

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

В Excel они называются зависимыми раскрывающимися списками.

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

Вы можете видеть, что параметры в раскрывающемся списке 2 зависят от выбора, сделанного в раскрывающемся списке 1. Если я выберу «Фрукты», в раскрывающемся списке 1 мне показаны названия фруктов, но если я выберу овощи в раскрывающемся списке 1, мне будут показаны названия овощей в раскрывающемся списке 2.

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

Вот шаги для создания зависимого раскрывающегося списка в Excel:

• Выберите ячейку, в которой вы хотите разместить первый (основной) раскрывающийся список.
• Перейдите в раздел «Данные» -> «Проверка данных». Это откроет диалоговое окно проверки данных.
• В диалоговом окне проверки данных на вкладке настроек выберите Список.
• В поле Источник укажите диапазон, содержащий элементы, которые должны отображаться в первом раскрывающемся списке.
• Нажмите OK. Это создаст раскрывающийся список 1.
• Выберите весь набор данных (A1:B6 в этом примере).
• Перейдите в Формулы -> Определенные имена -> Создать из выделения (или вы можете использовать сочетание клавиш Control + Shift + F3).
• В диалоговом окне «Создать имя из выбранного» установите флажок «Верхняя строка» и снимите все остальные. При этом создается 2 диапазона имен («Фрукты» и «Овощи»). Именованный диапазон фруктов относится ко всем фруктам в списке, а названный диапазон овощей относится ко всем овощам в списке.
• Нажмите OK.
• Выберите ячейку, в которой вы хотите разместить зависимый/условный раскрывающийся список (E3 в этом примере).
• Перейдите в раздел «Данные» -> «Проверка данных».
• В диалоговом окне «Проверка данных» на вкладке настроек убедитесь, что выбран список.
• В поле Источник введите формулу =ДВССЫЛ(D3). Здесь D3 — это ячейка, содержащая основной раскрывающийся список.
• Нажмите OK.

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

Загрузить файл примера

Как это работает?  – Условный раскрывающийся список (в ячейке E3) ссылается на =ДВССЫЛ(D3). Это означает, что когда вы выбираете «Фрукты» в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон «Фрукты» (через функцию ДВССЫЛ) и, следовательно, перечисляет все элементы в этой категории.

Важное примечание: Если основная категория состоит из более чем одного слова (например, «Сезонные фрукты» вместо «Фрукты»), необходимо использовать формулу =ДВССЫЛ(ПОДСТАВИТЬ(D3″, «,» _»)) вместо простой функции ДВССЫЛ, показанной выше.

• Причина этого в том, что Excel не допускает пробелов в именованных диапазонах. Поэтому, когда вы создаете именованный диапазон, используя более одного слова, Excel автоматически вставляет знак подчеркивания между словами. Например, когда вы создаете именованный диапазон с «Сезонные фрукты», он будет называться Season_Fruits в бэкэнде. Использование функции ПОДСТАВИТЬ в функции ДВССЫЛ обеспечивает преобразование пробелов в символы подчеркивания .

Автоматический сброс/очистка содержимого зависимого выпадающего списка

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

Например, если вы выберете «Фрукты» в качестве категории, а затем выберите «Яблоко» в качестве элемента, а затем вернетесь и измените категорию на «Овощи», в зависимом раскрывающемся списке будет по-прежнему отображаться яблоко в качестве элемента.

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

Вот код VBA для очистки содержимого зависимого раскрывающегося списка:

 Private Sub Worksheet_Change (ByVal Target As Range)
При ошибке Возобновить Далее
Если Цель.Колонка = 4 Тогда
 Если Target.Validation.Type = 3 Тогда
 Приложение.EnableEvents = Ложь
 Цель.Смещение(0, 1).ClearContents
 Конец, если
Конец, если
обработчик выхода:
 Приложение.EnableEvents = Истина
 Выйти из подпрограммы
End Sub 

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

Вот как заставить этот код работать:

• Скопируйте код VBA.
• В рабочей книге Excel, где у вас есть зависимый раскрывающийся список, перейдите на вкладку «Разработчик» и в группе «Код» нажмите Visual Basic (вы также можете использовать сочетание клавиш — ALT + F11).
• В окне редактора VB слева в обозревателе проектов вы увидите все имена рабочих листов. Дважды щелкните тот, у которого есть раскрывающийся список.
• Вставьте код в окно кода справа.
• Закройте редактор VB.

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

Загрузите файл примера

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

Вот шаги t0 для выделения несоответствий в зависимых раскрывающихся списках:

• Выберите ячейку с зависимыми раскрывающимися списками.
• Перейти на главную -> Условное форматирование -> Новое правило.
• В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения форматируемых ячеек».
• В поле формулы введите следующую формулу: =ЕОШИБКА(ВПР(E3,ИНДЕКС($A$2:$B$6,ПОИСКПОЗ(D3,$A$1:$B$1)),1,0))
• Установите формат.
• Нажмите OK.

Формула использует функцию ВПР, чтобы проверить, является ли элемент в зависимом раскрывающемся списке элементом из основной категории или нет. Если это не так, формула возвращает ошибку. Это используется функцией ЕОШИБКА для возврата ИСТИНА, которая указывает условному форматированию выделить ячейку.

Загрузите пример файла

Вам также могут понравиться следующие учебные пособия по Excel :

• Извлечение данных на основе выбора из раскрывающегося списка.
• Создание выпадающего списка с поисковыми подсказками.
• Выберите несколько элементов из раскрывающегося списка.
• Создание нескольких раскрывающихся списков без повторения.
• Экономьте время с помощью форм ввода данных в Excel.

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

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

Что такое выпадающий список?

Раскрывающиеся списки позволяют пользователям выбирать значение из предопределенного списка. Это позволяет пользователям легко вводить только те данные, которые соответствуют требованиям. Выпадающие списки реализованы как особый вид проверки данных. На приведенном ниже экране показан простой пример. В столбце E доступны варианты «Завершено», «Ожидание» или «Отменено». Эти значения автоматически извлекаются из диапазона G5:G7:

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

Вот несколько примеров:

• список городов, который зависит от выбранной страны
• список вкусов в зависимости от типа мороженого
• список моделей зависит от производителя
• список продуктов, который зависит от категории

Такие списки называются зависимыми выпадающими списками , поскольку список зависит от другого значения. Они создаются с проверкой данных с использованием пользовательской формулы, основанной на функции ДВССЫЛ и именованных диапазонах. Это может показаться сложным, но на самом деле это очень просто и отличный пример того, как можно использовать INDIRECT.

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

Пример зависимого раскрывающегося списка

В приведенном ниже примере столбец B содержит раскрывающееся меню для категории продуктов питания, а столбец C содержит параметры выбранной категории. Если пользователь выберет «Фрукты», он увидит список фруктов, если выберет «Орехи», то увидит список орехов, а если выберет «Овощи», то увидит список овощей.

Проверка данных в столбце B использует эту пользовательскую формулу:

 
 = категория 

И проверка данных в столбце C использует эту пользовательскую формулу:

 
 =ДВССЫЛ(B5) 

Где рабочий лист содержит следующие именованные диапазоны:

категория = E4:G4
овощи = F5:F10
орехи = G5:G9
фрукты = E5:E11

3 Как это работает

Ключом к этому методу являются именованные диапазоны + функция ДВССЫЛ. ДВССЫЛ принимает текстовые значения и пытается оценить их как ссылки на ячейки. Например, ДВССЫЛ возьмет текст «A1» и превратит его в реальную ссылку:

 
 = ДВССЫЛ ("A1")
=A1 

Аналогично, ДВССЫЛ преобразует текст «A1:A10» в диапазон A1:A10 внутри другой функции:  

 
 =СУММ(ДВССЫЛ("A1:A10")
=СУММ(A1:A10) 

На первый взгляд эта конструкция может показаться вам раздражающей или даже бессмысленной. Зачем усложнять красивую простую формулу ДВССЫЛОЙ?

Будьте уверены, сумасшествие имеет свой метод 🙂

Прелесть ДВССЫЛЫ в том, что вы можете использовать текст точно так же, как ссылка на ячейку . Это дает два ключевых преимущества:

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

В примере на этой странице мы комбинируем последнюю идею с именованными диапазонами для создания зависимых выпадающих списков. ДВССЫЛ сопоставляет текст с именованным диапазоном, который затем преобразуется в допустимую ссылку. Итак, в этом примере мы берем текстовые значения в столбце B и используем ДВССЫЛ, чтобы преобразовать их в ссылки на ячейки путем сопоставления существующих именованных диапазонов, например:

 
 = НЕПРЯМОЙ (B6)
= ДВССЫЛ ("гайка")
=G5:G9 

B6 преобразуется в текст «орех», который преобразуется в диапазон G5:G9.

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

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

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

2. Создайте именованные диапазоны для каждого списка: категория = E4:G4, овощи = F5:F10, орехи = G5:G9., а фрукты = E5:E11.

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

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

 
 = категория 

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

4. Создайте правило проверки данных для зависимого выпадающего списка с пользовательской формулой на основе функции ДВССЫЛ:

 
 =ДВССЫЛ(B5) 

В этой формуле ДВССЫЛ просто оценивает значения в столбце B как ссылки, которые связывают их с ранее определенными именованными диапазонами.

5. Протестируйте раскрывающиеся списки, чтобы убедиться, что они динамически реагируют на значения в столбце B. Именованный диапазон называется «гайка», а значение в ячейке B6 — «орех», но функция ДВССЫЛ правильно разрешает именованный диапазон, даже если регистр различается.

Работа с пробелами

В именованных диапазонах пробелы недопустимы, поэтому обычно вместо них используются символы подчеркивания. Так, например, если вы хотите создать именованный диапазон для мороженое , вы бы использовали ice_cream. Это работает нормально, но зависимые выпадающие списки будут ломаться, если они попытаются сопоставить «мороженое» с «ice_cream». Чтобы решить эту проблему, вы можете использовать более надежную пользовательскую формулу для проверки данных:

.
 
 =ДВССЫЛ(ПОДСТАВИТЬ(A1," ","_")) 

В этой формуле по-прежнему используется ДВССЫЛ, чтобы связать текстовое значение в A1 с именованным диапазоном, но перед запуском ДВССЫЛ функция ПОДСТАВИТЬ заменяет все пробелы символами подчеркивания.