Выпадающий список с мультивыбором

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

Давайте рассмотрим несколько типовых вариантов реализации такого списка с мультивыбором.

Вариант 1. Горизонтальный

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

Выпадающие списки в ячейках С2:С5 в данном примере создаются стандартным образом, т.е.

выделить ячейки С2:С5
на вкладке или в меню Данные (Data) выбрать команду Проверка данных (Data Validation)
в открывшемся окне выбрать вариант Список (List) и указать в качестве диапазона Источник (Source) ячейки с исходными данными для списка A1:A8

Затем в модуль листа нужно добавить макрос, который и будет делать всю основную работу, т.е. добавлять выбранные значения справа от зеленых ячеек. Для этого щелкните правой кнопкой мыши по ярлычку листа с выпадающими списками и выберите команду Исходный текст (Source code). В открывшееся окно редактора Visual Basic нужно вставить следующий код:

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

Вариант 2. Вертикальный

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

Делается совершенно аналогично, но немного меняется код макроса обработчика:

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

Вариант 3. С накоплением в той же ячейке

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

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

При желании, можно заменить символ-разделитель (запятую) в 9-й строке кода на свой (например, пробел или точку с запятой).

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

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

заранее благодарю, уже даже за выпадающие списки

Доброго дня!Николай, очень обрадовалась когда нашла именно то, что мне было нужно - выбор с накоплением в той же ячейке . Спасибо Вам - все работает.

Маленький, но важный вопрос - есть ли возможность фильтрации по столбцу в котором данные идут с накоплением по первичным значениям? Excel воспринимает строки с данными полученными накоплением как новые полные данные, а мне необходимо из множествастрок в столбце выбирать те которые содержат первичное значения.Из доступных способов фижу только фильт->текстовый фильтр->содержит. Но хотелось бы иметь более простой инструмент, либо сводную таблицу.

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

Первая запись выводится правильно, в нужной мне ячейке, а вот вторая куда то пропадает (((

Помогите пожалуйста, никак не могу додуматься.

p.s. отличный сайт! открыл для себя новую америку)))

Николай, огромное спасибо за ваш прекрасный и познавательный сайт. У меня вопрос по третьему варианту. Как сделать так чтобы список работал на нескольких листах одной книги? Опыт в работе с макросами = новичок. Спасибо!

УРА ПОБЕДИЛ, ПРОСТО ДОБАВИЛ МАКРОС В МОДУЛЬ КАЖДОЙ СТРАНИЦИ КНИГИ!

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

PS Может кому пригодится: вместо разделителя можно использовать chr(10), тогда новое значение будет переноситься на новую строку в данной ячейке

Подскажите, пожалуйста, есть ли возможность объединить 3-й вариант с вот этим примером . Заранее благодарю!

Николай, добрый день.

Нет ли у вас случаем в запасе такого макроса? сейчас он крайне необходим.

Извиняюсь за вопрос. Есть список из 10 строк, он заполнен наполовину. Другие ячейки ссылаются на этот список, но при выборе значений из списка отображаются все 10 значений, из которых 5 названных, а 5 пустые. Как сделать так, чтобы в раскрывающимся списке отображались только указанные 5 позиций, а не все 10 (с пятью пустыми)?

Добрый вечер. Мое обращение адресовано Николаю Павлову. Такой вопрос:

1. как в макросе (Вариант 3. С накоплением в той же ячейке) исправить такую ошибку: при удалении одного из элементов в одной ячейке и нажатии на Enter этот макрос не удаляет, а наоборот, клонирует и добавляет элементы в ту же строку после запятой.

2. как применить каждый из вариантов только к одному столбцу в одном листе? 3. Как правильно вставить в окно VBA одновременно несколько макросов по всем 3-м вариантам? Я пробовал, но пишет, что нужны какие-то разделительные кавычки между разными макросами

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

"Вариант 3. С накоплением в той же ячейке"-используется для составления списка запчастей в ячейке I3, выпадающий список берет названия из определенного прайс листа ( сделанном на отдельном листе EXEL).В прайс листе есть соответственно ячейка с названием, артикулом и ценойКак теперь сделать чтобы в ячейке К3 производилось суммирование цен на эти запчасти взятые из прайс листа.Буду очень благодарен совету и помощи в реализации.

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

Николай, добрый день! Подскажите пожалуйста, чем вызвана следующая проблема: использую вариант 3 (накопление в одной ячейке), в трех столбцах использую макрос, как и рекомендовалось выше дописала инфо в коде на несколько диапазонов, но как только вручную указываю какие-либо данные в других строках таблицы макрос перестает работать в заданном диапазоне, Excel на долго зависает и в ячейке в которой внесла какие-либо данные дублируется текст через запятую несколько раз, такое ощущение, что макрос работает за пределом указанного диапазона в коде

Заранее большое спасибо!

Николай, добрый день!

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

Как это реализовать?

Здравствуйте. Office Excel 2007. Применил в файле решение по Варианту 3. "С накоплением в той же ячейке". Дополнительно в 3 строке кода указал через запятую второй диапазон (Range("F:F;I:I";)). Стал наблюдать следующее:- в таблице файла несколько столбцов со списками и все они стали работать по варианту "С накоплением в той же ячейке", не только "F" и "I";- в случае, если что-то забыл набрать и требуется вписать строку таблицы между двумя существующими строками, то чистая строка не вставляется (как это работает без кода VBA);- при попытке скопировать строку таблицы, чтобы её затем вставить ниже, скопированная строка таблицы не вставляется;- при попытке вырезать строку таблицы, строка принимает очертания скопированной области (но остаётся видимой), а при операции "вставить" просто удаляется из таблицы (информация, содержащаяся в строке удаляется полностью);- стрелка "Отменить ввод" после каждого ввода становиться неактивной (отменить операцию невозможно).

Вопросы.1. Как сделать так, чтобы файл позволял вставлять строку между уже набранными строками таблицы, не удаляя существующую в таблице информацию?2. Как сделать так, чтобы файл позволял копировать (вырезать), а затем вставлять скопированную строку в нужном месте таблицы без потери информации?3. Как сделать так, чтобы файл позволял отменять операции?4. Как сделать так, чтобы файл распространял код VBA только на указанные столбцы, а не на всю таблицу?

Приобрела вашу книгу, пока тестирую).Пытаюсь создать связанные выпадающие списки с помощью формулы СМЕЩ(..) .В вашем примере на стр.22 поняла как вы исключаете дублирование наименований для марки авто (1уровень), то есть диапазон списка составлен из уникальных наименований марок. А что если уровней несколько и для последующих необходимо сделать то же самое.Как сделать так, чтобы в выпадающем списке наименования не дублировались: например, выбрав toyota в следующем уровне чтобы corolla camry carina, а не corolla corolla corolla camry camry camry и т.д. Спасибо заранее

Спасибо за информативный и очень полезный материал!

Возникли вопросы:1. Можно ли использовать данный прием (в частности по 1-му способу) с выпадающим списком ActiveX ?. Я попробовал, но в указанную в поле LincedCell ячейку просто записывается значение из списка. Обработчик не "срабатывает", вероятно событие не происходит? Это удобно, например, если использовать в отображении выпадающего списка несколько столбцов (чтобы правильно выбрать), а выводить в ячейку - результирующее (нужное) значение. Как пример - сотрудник и его табельный номер. В выпадающем списке отображается Фамилия и Табельный номер, а в ячейку заносится табельный номер. Это нельзя реализовать с помощью списка из Проверки данных, но из элемента ActiveX - можно.

2. Как можно сделать (в том же 1-м способе - горизонтальном), чтобы значение добавлялось не в ячейку, что идет сразу за "целевой", а например, через заданное кол-во ячеек? Т.е. например, список находится в ячейке С1, а мне нужно, чтобы значения выводились в строке не начиная со следующей ячейки D1, а с ячейки F1, и т.д.

3. Тут, если можно так сделать: хотелось бы, чтобы ячейка для введения значений из списка находилась на одном листе, а макрос с формирующимися значениями - на другом листе. Это, например, нужно, чтобы на "рабочем" листе не формировать таких списков. Т.е. для формирования "диапазона данных" как бы использовать "промежуточный лист", чтобы не "захламлять" рабочий лист вычислениями и данными. Просто выбор производится на одном листе, а "вычисления" - на другом.Буду благодарен за ответы.

📎📎📎📎📎📎📎📎📎📎