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

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

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

Два связанных выпадающих списка с формулой массива

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

• тип автомобиля: Легковой, Фургон и Внедорожник (Категория)
• производитель: Fiat, Volkswagen i Suzuki (Подкатегория) и
• модель: . немножечко их есть 🙂 (Подподкатегория)

В то же время мы имеем следующие данные:

Этот список должен быть отсортирован в следующей очередности:

Он может быть любой длины. Что еще важно: стоит добавить к нему еще два меньших списка, необходимых для Типа и Производителя, то есть к категории (первый список) и подкатегории (второй список). Эти дополнительные списки списки выглядят следующим образом:

Дело в том, что эти списки не должны иметь дубликатов записей по Типу и Производителю, находящихся в списке Моделей. Вы можете создать их с помощью инструмента «Удалить дубликаты» (например, это показано в этом видео продолжительностью около 2 минут). Когда мы это сделали, тогда .

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

Для ячеек, которые должны стать раскрывающимися списками в меню «Данные» выбираем «Проверка данных» и как тип данных выбираем «Список».

Для Типа как источник данных мы просто указываем диапазон B7:B9.

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

Модель — описание для этой записи сделаем таким же самым образом.

Третий связывающий выпадающий список: Модель

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

Мы будем перемещать ячейку H4 на столько строк, пока не найдем позицию первого легкового Fiatа. Поэтому в колонке Тип мы должны иметь значение Легковой, а в колонке Производитель должен быть Fiat. Если бы мы использовали промежуточный столбец (это было бы отличным решением, но хотели бы показать вам что-то более крутое ;-), то мы бы искали комбинацию этих данных: Легковой Fiat. Однако у нас нет такого столбца, но мы можем создать его «на лету», другими словами, используя формулу. Набирая эту формулу, вы можете себе представить, что такой промежуточный столбец существует, и вы увидите, что будет проще 😉

Для определения положения Легковой Fiat, мы, конечно, будем использовать функцию ПОИСКПОЗ. Смотрите:

Вышеописанное означает, что мы хотим знать позицию Легкового Fiatа (отсюда и связь B4&C4). Где? В нашем воображаемом вспомогательном столбце, то есть: F5:F39&G5:G39. И здесь самая большая сложность всей формулы.

Остальное уже проще, а наибольшего внимания требует функция СЧЁТЕСЛИМН, которая проверяет, сколько есть Легковых Fiatов. В частности, она проверяет, сколько раз в списке встречаются такие записи, которые в столбце F5:F39 имеют значение Легковой, а в столбце G5:G39 — Fiat. Функция выглядит так:

А вся формула для именного диапазона раскрывающегося списка это:

Если вы планируете использовать эту формулу в нескольких ячейках — не забудьте обозначить ячейки как абсолютные ссылки!

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

1. Создаем новое имя. Для этого выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен»-«Создать».
2. При создании имени в поле «Имя:» вводим слово – модель, а в поле «Диапазон:» вводим выше указанную формулу и нажимаем на всех открытых диалоговых окнах ОК:
3. Перейдите на ячейку D4 чтобы там создать выпадающий список, в котором на этот раз в поле ввода «Источник:» следует указать ссылку на выше созданное имя с формулой =модель.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

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

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

Вот примеры таких задач:

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

Выглядеть это может примерно так:

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

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

1. Используем именованные диапазоны + функция ДВССЫЛ.

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

Рассмотрим небольшой пример. У нас есть перечень автомобилей различных марок. Расположим их каждый в отдельном столбце. В первой ячейке каждого столбца запишем производителя — Toyota, Ford, Nissan. Необходимо, чтобы после того, как первоначально мы выберем, например, Toyota, далее мы видели бы только модели этой марки, и ничего более. То есть, нам нужен двухуровневый связанный список.

Для начала создадим именованные диапазоны с моделями автомашин. Имя каждому из них присвоим в соответствии с маркой авто. Важно, чтобы имя каждого из них точно соответствовало значению, записанному в первой строке соответствующего столбца. Иными словами, если мы создаем именованный диапазон из ячеек A2:A100, то имя его должно совпадать со значением в A1 (регистр символов значения не имеет). Посмотрите на рисунке, как это выглядит.

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

Итак, у нас получилось 3 именованных диапазона — «toyota», «ford», «nissan». Делать их статическими (фиксированными) или динамически (автоматически пополняемыми) — решайте сами. О том, как создать автоматически пополняемый список, смотрите ссылку в конце этой статьи.

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

И далее выбираем того производителя, который нас интересует. К примеру, «Ford».

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

В этом нам поможет функция ДВССЫЛ. Функция ДВССЫЛ (INDIRECT в английском варианте) преобразует текст в стандартную ссылку Excel.

Если мы запишем

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

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

«Фишка» функции ДВССЫЛ (или INDIRECT) в том, что она позволяет использовать текст точно так же, как обычную ссылку на ячейку . Это обеспечивает нам два ключевых преимущества:

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

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

Итак, в этом примере мы берем текстовые значения из А1:С1, выбираем из них какое-то одно. К примеру, «Ford». Поскольку такое же название у нас имеет один из именованных диапазонов, то и применяем ДВССЫЛ, чтобы преобразовать текст «Ford» в ссылку =ford. И вот уже ее мы употребляем как источник для связанного выпадающего списка.

Итак, в качестве источника значений применяем формулу

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

В результате функция возвращает в нашу таблицу Excel ссылку

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

Изменяя значения в F3, мы автоматически изменяем и ссылку-источник для списка в F6. В результате источник данных для зависимого выпадающего списка в F6 динамически меняется в зависимости от того, что было выбрано в F3. Если выбираем Ford, то видим только каталог машин этой марки. Аналогично, если выбираем Toyota либо Nissan.

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

А как быть, если в наименованиях есть пробелы?

Может случиться так, что название вашей группы товаров или категории будет содержать пробелы. А именованные диапазоны не позволяют, чтобы в их названии встречался пробел. Принято заменять их символом нижнего подчеркивания «_». Как же нам быть в этом случае? Ведь в таблице названия товарных категорий с символом нижнего подчеркивания будут смотреться несколько непривычно. Например, «Косметические_товары». С непривычки можно и просто забыть ввести нужный символ. И тогда наши формулы работать не будут.

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

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

То есть, мы проведем предварительную обработку значений, чтобы они соответствовали правилам написания имён. Вместо =ДВССЫЛ($F$3) запишем

Кавычки здесь не нужны, поскольку ПОДСТАВИТЬ возвращает текстовую строку. Если же в нашем тексте нет пробелов и он состоит из одного слова, то он будет возвращен «как есть». Следите только за тем, чтобы в начале и в конце обрабатываемой текстовой переменной у вас случайно не оказались пробелы. Ведь они тоже будут заменены на нижнее подчеркивание. Ну а чтобы не заниматься этим ручным контролем, усложните еще немного свою формулу при помощи функции СЖПРОБЕЛЫ. Она автоматически уберет начальные и конечные пробелы из текста. В итоге получим:

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

2. Комбинация СМЕЩ + ПОИСКПОЗ

Итак, у нас снова есть перечень марок и моделей автомобилей. Только записан он немного по-другому.

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

Первое условие — исходные данные должны быть отсортированы по маркам, а внутри марок — по моделям. То есть, нужно отсортировать по столбцу А, а затем — по В.

Начнем с простого. В ячейке D1 создадим выпадающий список из марок автомобилей. Для этого в F1:F3 запишем их названия и затем употребим их в качестве источника. Напомню, что нужно нажать Меню — Данные — Проверка данных.

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

• координаты верхней левой ячейки,
• на сколько строк нужно сместиться вниз — A,
• на сколько столбцов нужно перейти вправо — B,
• высота массива (строк) — C,
• ширина массива (столбцов) D.

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

Традиционно точкой отсчета для функции СМЕЩ возьмем ячейку A1. Теперь нам нужно решить, на сколько позиций вниз и вправо нужно перейти, чтобы указать левый верхний угол нового перечня с моделями. Предположим, первоначально мы выбрали Ford.

На сколько шагов сместиться вниз? Применим функцию ПОИСКПОЗ, которая возвратит нам номер позиции первого вхождения «Ford».

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

А теперь объединяем все это в СМЕЩ:

Последняя единичка означает, что массив состоит из одной колонки.

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

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

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

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

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

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

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

Создание выпадающего списка с множественным выбором в Excel состоит из двух шагов:

1. Во-первых, вы создаете на рабочем листе стандартный список проверки данных в одной или в нескольких ячейках. Подробно эти действия описаны в этой статье: 5 способов создать выпадающий список в ячейке Excel.
2. А затем вставьте код VBA на этот лист.

Шаги эти можно выполнить и в обратном порядке 🙂

Как создать обычный выпадающий список.

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

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

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

1. Выберите одну или несколько ячеек для создания в них выпадающего списка (D3:D7 в нашем случае).
2. На вкладке «Данные» в группе «Работа с данными» нажмите кнопку «Проверка данных«.
3. В раскрывающемся списке «Проверка вводимых значений» выберите «Список«.
4. В поле «Источник» введите формулу, которая косвенно ссылается на столбец Table1 с именем Продукты.

1. Когда закончите, нажмите OK.

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

Для получения дополнительной информации о сделанных нами шагах см. Как создать список проверки данных Excel.

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

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

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

Чтобы добавить код VBA в свой рабочий лист, выполните следующие действия:

1. Откройте редактор Visual Basic, нажав ALT + F11 .
2. На панели VBA Project слева дважды щелкните имя рабочего листа, содержащего ваш раскрывающийся список. Это откроет окно «Код» для этого листа.

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

1. В окне «Код» вставьте код VBA.
2. Закройте редактор VBA и сохраните файл как рабочую книгу с поддержкой макросов (.xlsm).

Вот и все! Когда вы вернетесь к рабочему листу, ваш выпадающий список позволит вам выбрать несколько элементов:

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

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

Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim DelimiterType As String Dim rngDropdown As Range Dim oldValue As String Dim newValue As String DelimiterType = ", " If Destination.Count > 1 Then Exit Sub On Error Resume Next Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitError If rngDropdown Is Nothing Then GoTo exitError If Intersect(Destination, rngDropdown) Is Nothing Then 'do nothing Else Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value  Destination.Value = newValue If oldValue = "" Then 'do nothing Else If newValue = "" Then 'do nothing Else  Destination.Value = oldValue & DelimiterType & newValue ' add new value with delimiter End If End If End If exitError: Application.EnableEvents = True End Sub

Как работает этот код:

• Код позволяет выполнять многократный выбор во всех выпадающих списках на определенном листе.
• Код работает только на текущем листе, поэтому обязательно добавьте его на каждый лист, где вы хотите разрешить несколько вариантов выбора.
• Этот код позволяет дублировать, то есть выбирать один и тот же элемент несколько раз.
• Выбранные элементы разделены запятой и пробелом. Чтобы изменить разделитель, замените «, » на нужный символ в переменной DelimiterType = «, » (строка 7 в коде).

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

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

Код VBA для создания выпадающего спуска с несколькими выборами без дубликатов:

Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim rngDropdown As Range Dim oldValue As String Dim newValue As String Dim DelimiterType As String DelimiterType = ", " If Destination.Count > 1 Then Exit Sub On Error Resume Next Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitError If rngDropdown Is Nothing Then GoTo exitError If Intersect(Destination, rngDropdown) Is Nothing Then 'do nothing Else Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value  Destination.Value = newValue If oldValue <> "" Then If newValue <> "" Then If oldValue = newValue Or _ InStr(1, oldValue, DelimiterType & newValue) Or _ InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then  Destination.Value = oldValue Else  Destination.Value = oldValue & DelimiterType & newValue End If End If End If End If exitError: Application.EnableEvents = True End Sub

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

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

Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim rngDropdown As Range Dim oldValue As String Dim newValue As String Dim DelimiterType As String DelimiterType = ", " Dim DelimiterCount As Integer Dim TargetType As Integer Dim i As Integer Dim arr() As String If Destination.Count > 1 Then Exit Sub On Error Resume Next Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitError If rngDropdown Is Nothing Then GoTo exitError TargetType = 0 TargetType = Destination.Validation.Type If TargetType = 3 Then ' is validation type is "list" Application.ScreenUpdating = False Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value  Destination.Value = newValue If oldValue <> "" Then If newValue <> "" Then If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list oldValue = Replace(oldValue, DelimiterType, "") oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "")  Destination.Value = oldValue ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, " " & newValue & DelimiterType) Then arr = Split(oldValue, DelimiterType) If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then  Destination.Value = oldValue & DelimiterType & newValue Else:  Destination.Value = "" For i = 0 To UBound(arr) If arr(i) <> newValue Then  Destination.Value = Destination.Value & arr(i) & DelimiterType End If Next i  Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType)) End If ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then oldValue = Replace(oldValue, newValue, "")  Destination.Value = oldValue Else  Destination.Value = oldValue & DelimiterType & newValue End If  Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces  Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) If Destination.Value <> "" Then If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end  Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2) End If End If If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters  Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1) End If If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then  Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1) End If DelimiterCount = 0 For i = 1 To Len(Destination.Value) If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then DelimiterCount = DelimiterCount + 1 End If Next i If DelimiterCount = 1 Then ' remove delimiter if last character  Destination.Value = Replace(Destination.Value, DelimiterType, "")  Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "") End If End If End If Application.EnableEvents = True Application.ScreenUpdating = True End If exitError: Application.EnableEvents = True End Sub

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

Как изменить вид разделителя в списке.

Символ, который разделяет элементы в выделении, установлен в параметре DelimiterType. Во всех вариантах макроса значением по умолчанию этого параметра является «, » (запятая и пробел). Онрасположен в строке 7. Чтобы использовать другой разделитель, вы можете заменить «, » на нужный вам символ или группу символов. Например:

• Чтобы отделить выбранные элементы пробелом, используйте DelimiterType = » «.
• Чтобы отделить точкой с запятой, используйте DelimiterType = «; » или DelimiterType = «;» (с пробелом или без него, соответственно).
• Чтобы отделить вертикальной линией, используйте DelimiterType = » | «.

Например, если вы измените разделитель на вертикальную линию, ваш список будет выглядеть следующим образом:

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

Чтобы записать каждое выбранное значение в отдельной строке внутри одной и той же ячейки, установите значение DelimiterType – Vbcrlf. В VBA это означает возврат каретки и перевод строки.

Итак, вы меняете эту строку кода:

DelimiterType = «,»

DelimiterType = vbCrLf

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

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

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

Для этого найдите эту строку кода:

If rngDropdown Is Nothing Then GoTo exitError

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

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

Чтобы разрешить выбор нескольких элементов в определенном столбце, добавьте этот код:

If Not Destination.Column = 4 Then GoTo exitError

Где «4» — это номер целевого столбца. В данном случае раскрывающийся список с возможностьювыбора нескольких значений будет активен только в столбце D (четвертый по счёту столбец). Во всех других столбцах выпадающий список будет стандартно ограничен одним выбором.

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

If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError

В этом случае раскрывающийся список множественного выбора будет доступен в столбцах D (4) и F (6).

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

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

If Not Destination.Row = 3 Then GoTo exitError

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

Чтобы разрешить сразу несколько строк, измените код следующим образом:

If Destination.Row <> 3 And Destination.Row <> 6 Then GoTo exitError

Где «3» и «6» — это строки, в которых разрешено выбирать несколько элементов.

Выбор нескольких значений только в конкретных ячейках.

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

Для одной ячейки:

If Not Destination.Address = «$D$3» Then GoTo exitError

Для нескольких ячеек:

If Destination.Address <> «$D$3» And Destination.Address <> «$F$6» Then GoTo exitError

Конечно, не забудьте заменить «$D$3» и «$F$6» реальными адресами ваших ячеек.

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

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Unprotect password:="password" On Error GoTo exitError2 If Target.Validation.Type = 3 Then Else  ActiveSheet.Protect password:="password" End If Done: Exit Sub exitError2:  ActiveSheet.Protect password:="password" End Sub

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

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

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

Есть ли возможность поиска в выпадающем списке?

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

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

Как это сделать? Часто рекомендуют использовать код VBA, но можно прекрасно обойтись и без этого.

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

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

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

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

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

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

4. В диалоговом окне «Проверка данных» выберите «Список» в качестве критериев проверки. В поле «Источник» введите именованный диапазон, созданный на шаге 2.

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

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

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

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

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

Рассмотрим особенности создания выпадающих списков на примере:

Исходные данные:

• Список адресов в разных городах

Задача:

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

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

Скачать файлы из этой статьи

Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!

Выпадающий список в Excel

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

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

Выпадающий список готов!

Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.

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

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

Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

Способ 1: именованные диапазоны + функция ДВССЫЛ

Для начала создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Алгоритм создания именованного диапазона: выделяем диапазон, далее «Формулы» – «Задать имя».

У нас получится 5 именованных диапазона: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

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

Поэтому, вместо дефисов в названии города Ростов-на-Дону мы укажем допустимый символ – нижнее подчеркивание.

Именованные диапазоны готовы.

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

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

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

Меняя значения в ячейке D2, меняются списки в ячейке E2. За исключением города Ростов-на-Дону. В выпадающем списке городов (ячейка D2), в названии используется дефис, а в именованном диапазоне – нижнее подчеркивание.

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

Функция ПОДСТАВИТЬ заменяет определенный текст в текстовой строке на новое значение. Вместо: =ДВССЫЛ(D2) укажем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;»-«;»_»))

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

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

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

Для начала создадим из диапазона данных «умную» таблицу Excel. Сделать это можно сочетанием клавиш Ctrl+T .

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

Как сделать выпадающий список уникальных значений в Excel?

Надоело смотреть на повторяющиеся названия городов в выпадающем списке. Реализуем выпадающий список так, чтобы названия городов в нем не повторялись. Для этого, добавим слева вспомогательный столбец. Мы дали ему название – «Уникальные».

И включим новый столбец в диапазон «умной» таблицы. «Конструктор» – «Размер таблицы». Вместо =$B$1:$C$17 указываем: =$A$1:$C$17

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

В ячейку А2 добавим формулу массива, которая будет формировать список уникальных городов:

Чтобы Excel воспринял нашу формулу, как формулу массива, жмем Ctrl + Shift + Enter .

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

Из списка уникальных городов создадим именованный диапазон (мы назвали его — «Уникальные»), который затем используем в качестве источника для выпадающего списка городов.

«Проверка данных» – «Список». В источнике данных, вместо предыдущего диапазона с названиями городов =$B$2:$B$18 , задаем имя – =Уникальные

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

Чтобы их убрать, доработаем именованный диапазон «Уникальные». В диспетчере имен, вместо диапазона =Таблица1[Уникальные] используем: =СМЕЩ(Лист1!$A$2;0;0;СЧЁТЗ(Таблица1[Уникальные])-СЧИТАТЬПУСТОТЫ(Таблица1[Уникальные]))

где: Лист1!$A$2 – ячейка со значением первого пункта списка уникальных значений

Таблица1[Уникальные] – столбец с перечнем всех пунктов списка

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

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

Как сделать автоматически обновляемый зависимый список? Способ 2: СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ

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

В ячейку F2 (зависимый выпадающий список адресов) вместо: =ДВССЫЛ(ПОДСТАВИТЬ(E2;»-«;»_»)) вставляем: =СМЕЩ($B$2;ПОИСКПОЗ(E2;$B$2:$B$18;0)-1;1;СЧЁТЕСЛИ($B$2:$B$18;E2);1)

Для корректной работы этого способа, данные в столбце с городом должны быть отсортированы. Функция СМЕЩ будет динамически ссылаться только на ячейки адресов определенного города.

Аргументы функции:

Ссылка – берем первую ячейку нашего списка, т.е. $B$2

Смещение по строкам – считает функция ПОИСКПОЗ , которая выдает порядковый номер ячейки с выбранным городом (E2) в заданном диапазоне ( $B$2:$B$18 )

Смещение по столбцам = 1, т.к. мы хотим сослаться на адреса в соседнем столбце (С)

Высота – вычисляем с помощью функции СЧЁТЕСЛИ , которая подсчитывает количество встретившихся в диапазоне ( $B$2:$B$18 ) нужных нам значений – названий городов (E2)

Ширина = 1, т.к. нам нужен один столбец с адресами

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

С выпадающими списками в Google таблицах все немного иначе.

Выпадающий список в Google таблицах

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

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

«Данные» – «Настроить проверку данных» – «Значение из диапазона»

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

Зависимый выпадающий список в Google таблицах

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

Способ 1: именованные диапазоны + ДВССЫЛ

Создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Выделяем ячейки – «Данные» – «Настроить именованные диапазоны»

Указываем имя и жмем готово. У нас получится 5 именованных диапазонов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

Также, как и в Excel, в Google таблицах к именам диапазонов есть список требований.

Поэтому, вместо дефисов в названии города Ростов-на-Дону укажем допустимый символ – нижнее подчеркивание.

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

В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;»_»;»-«))

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

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

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

В выпадающем списке городов, достаточно расширить диапазон и вместо =$A$2:$A$16 указать: =$A$2:$A . Теперь при добавлении нового города он автоматически появляется в выпадающем списке.

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

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

В ячейке G6 укажем:

Важно: для корректной работы этого способа, данные в столбце с городом должны быть отсортированы от А до Я, или от Я до А. Подробнее о том, как в данном случае работает функция СМЕЩ читайте выше в примере с Excel.

Заключительным этапом поместим результат функции СМЕЩ в диапазон выпадающего списка.

Скроем вспомогательные столбцы для удобства.

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

Заключение

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

Изучить работу в программе Excel Вы можете на наших курсах: бесплатные онлайн-курсы по Excel

Пройдите бесплатный тест на нашем сайте, чтобы объективно оценить свой уровень владения инструментами и функциями программы Excel: пройти бесплатный тест

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

• 20 июля 2021
• 41998
• MS Excel Video File Google Sheets