Страницы: 1
RSS
Создание выпадающего списка с данными из несмежных ячеек + связанные с ними списки
 
Здравствуйте. После долгих безуспешных попыток по поиску информации для решения имеющейся проблемы решил обратиться на форум.
Нужен макрос для получения выпадающего списка с данными, расположенными в несмежных ячейках   другого листа. В зависимости от выбранного значения формируется другой выпадающий список.
Суть проблемы:
1) Имеется Лист1, на котором в ячейках А2, А5, А8 ... находятся данные для выпадющего списка №1. Выпадающий список №1 должен появляться при выделении ячейки В3, В4,.. В9 на Листе2. Причём, если данные в какой-либо из ячеек А2, А5, А8 ... отсутствуют, то она присутствовать в выпадающем списке №1 не должна (т.е., чтобы в этом списке не было пустых строк)
2) На Листе1 имеются данные для выпадающего списка №2 в ячейках В2,С2,D2...;  В5,С5,D5...; В8,С8,D8 .... Выпадающий список №2 должен появляться при выделении ячейки С3, С4, С5 ... на Листе2. Причём, данные для списка №2 должны браться из соответствующей строки в зависимости от значения выбранного в ячейке слева.
Конкретный пример находится в прилагаемом файле.
Версия  Excel - 2003!

Буду очень признателен за помощь в написании данного макроса, либо за информацию с решениями похожих задач.
Изменено: jurij271 - 11.08.2014 00:27:06
 
Цитата
jurij271 пишет: помощь в написании данного макроса
и в чём именно Вам требуется "помощь"? я в Вашем файле даже заготовки макроса не нашёл.
или под этим словом Вы подразумеваете "напишите вместо меня"?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
jurij271, http  :/  /www.planetaexcel.ru/techniques/2/80/ - сортировка формулой;
http :/ /www.planetaexcel.ru/techniques/1/38/ - зависимы выпадающие списки
 
Евгений, спасибо за информацию. Возможно это то, что мне нужно. Буду разбираться.

ikki, спасибо за внимание к поставленной мной задаче. Извиняюсь за отсутствие конкретных проблемных вопросов - попробую их конкретизировать. Решение первой части задачи сначала пытался делать стандартным способом (создавая именованный диапазон из несмежных ячеек). Но при попытке сделать выпадающий список через проверку данных выдаётся сообщение об ошибке. Эту проблему я частично решил через промежуточный лист, на котором формируется нужный список из данных с Листа1, располагаемых в соседних ячейках. Но в данном случае в выпадающем списке появляются пусты строки, если не все ячейки с исходными данными с Листа1 заполнены. (этот способ - в файле Пример1)
Потому пришёл к выводу, что без макроса в этой задаче не обойтись. Так как опыт написания программ на VBA у меня невелик (делаю свой первый проект) и в синтаксисе данного языка я не силён, то при необходимости написания программы запускаю макрорекордер и пытаюсь разобраться в полученном коде. Так вот в коде выпадающего списка нужно вместо непрерывного диапазона =$A$2:$A$23 указать несмежные ячейки с даными из Листа1.
Код
 Sub Макрос2()
    Range("B4".Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$A$2:$A$23"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
 
один доп.столбец, один доп.диапазон, два имени
без макросов
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
ikki, большое Вам спасибо за помощь. Буду "пристраивать" Ваше решение к моему проекту. Можно ли ещё Вас побеспокоить своими вопросами, если возникнут некоторые "подводные камни" в процессе "пристройки"?
Ещё раз спасибо.
 
ikki, "пристроил" Ваше решение к своему проекту. Поскольку в качестве образца прикладывал примерный файл, то в процессе пристройки пришлось немного изменить условие (в зависимости от содержимого ячеек). Всё работает.
Осталось несколько "шероховатостей", которые хотелось бы устранить:
1) При выборе нового предмета в соседней ячейке справа остаётся прежний класс, даже если такого класса в данном предмете не существует. Логичным была бы очистка ячейки с классом при выборе нового предмета. Решение аналогичной проблемы рассматривалось на страничке с созданием связанных списков http://www.planetaexcel.ru/techniques/1/38/ и Николай Павлов предложил для её решения следующий макрос:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "C4" Then Range("D4").ClearContents
End Sub
Для одной пары ячеек макрос работает, если скопиравать его для другой пары ячеек (например, расположенных снизу, естественно, с указанием их имён), то класс при выборе нового предмета не удаляется. Даже если данный способ заработает, то решение этой задачи, конечно, будет некрасивое (48 однотипных макросов!). Полагаю, что логичным было бы решение через массив - при изменении элемента, соответствующего предмету, удалять содержимое ячейки справа. Хотя, может есть решение более простое?
2) Выпадающий список с классами содержал в том числе и пустые ячейки, в которых классов не было. Для списка, в котором для предметов отведено 5 ячеек этот недостаток несущественнен. В реализуемом же проекте на классы отводится 10 ячеек и выпадающий список уже некрасив (много пустых строк) и неудобен (появляется полоса прокрутки, случается что список пуст, так как предметы располагаются в его верхней (невидимой) части). Возможно ли модернизировать формулу для второго списка, с тем, чтобы пустые строки в нём отсутствовали?
3) И ещё один момент, который в общем-то несущественнен, но возможно упростит решение задачи: На листе "Предмет-Классы" у каждого класса уже предполагается номер. Для реализации этого столбец В с номерами для формирования списка скрывается. Может, ввиду имеющейся фиксированной нумерации предметов можно упростить создание первого выпадающего списка?
Спасибо.
Изменено: jurij271 - 31.07.2014 10:34:28 (Опечатка)
 
посмотрите вариант.
изменения коснулись формулы для имени "Классы" и макроса
п.3 я, честно говоря, не понял.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Уважаемый, ikki, большое Вам спасибо за решение имевшейся проблемы.
Имеется небольшой нюанс, который был замечен в ходе тестирования файла с решением:
При расположении классов не подряд, в выпадающем списке классов могут присутствовать пустые строки, либо отсутствовать классы (пример такой ситуации - в приложенном файле). Понимаю, что появляется эта проблема из-за принципа формирования списка классов (подсчитывается количество непустых ячеек и тем самым определяется количество строк, которые нужно выводить в списке) и решить её можно таком же способом, что и способ формирования списка с предметами (осуществлять нумерацию ячеек, содержащих классы и "вытаскивать" их в список по  наличию возле них номера). Но не лучше ли было бы решение через макрос? Ведь формирование обоих списков однотипное и, внеся список в массив, обращаться к его элементам и осуществлять выборку по определённым признакам было бы проще чем через встроенные функции? Кроме того, так, наверное, проще  будет  реализовать при необходимости и вложенные списки следующих уровней (т.е. решение будет универсальным, да и макрос уже задействован при очистке ячеек :).
Проблему с пустыми строками я решил принудительным переносом классов в начало списка. Хотя это, конечно, "половинчатое" решение, т.е. пользователю не будет предоставлено возможности произвольного размещения элементов списка в заданном диапазоне.
PS: 1) Массив взят из проекта, для данного примера можно использовать и меньше элементов.
2) Возможно перенос классов реализован не самым оптимальным способом :)
 
Цитата
jurij271 пишет: не лучше ли было бы решение через макрос?
Вы знаете, как это делать через макрос?
Вот и я не знаю  :)
Изменено: ikki - 11.08.2014 00:26:37
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Нет. В силу ничтожно малого опыта работы с VBA реализовать выпадающие списки через макрос я не могу. Предполагаю, что это возможно, хотя я (опять же, по причине малого опыта) могу ошибаться - ведь даже у Вас эта задача вызывает затруднение. Но в любом случае уже предложенное  Вами решение можно использовать в аналогичных задачах.
Честно говоря, когда начинал поиск решения проблемы со связанными выпадающими списками, то полагал, что эта задача уже решена (задача казалась мне достаточно распространённой). Оказалось, что нет. В любом случае, пусть найденное решение поможет тем, кто столкнётся с аналогичной проблемой.
Ещё раз выражаю Вам свою благодарность за помощь.
 
Цитата
jurij271 пишет: Предполагаю, что это возможно
я тоже.
но "в лоб" не получилось.
"в лоб" - это написать пользовательскую функцию, которая возвращала бы массив.
почему-то этот массив "не цепляется" у меня к проверке данных.

т.к. списки короткие - можно пробовать иначе.
при активации ячейки проверять принадлежность нужному диапазону и создавать для текущей ячейки список в виде константы.
но пока не хочется.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
ikki пишет: при активации ячейки проверять принадлежность нужному диапазону и создавать для текущей ячейки список в виде константы.
Что ж, будем надеяться, что у Вас появится желание реализовать имеющиеся идеи   :)
 
Цитата
ikki пишет: почему-то этот массив "не цепляется" у меня к проверке данных.
Может массив не тот? ))
 
недавно кто-то такое решение показывал
если уже есть массив, содержащий именно нужный перечень строк с названиями классов или предметов, то его (если правильно помню) можно присвоить проверке данных через макрос так:
Код
Range("A1").Validation.Add xlValidateList, xlValidAlertStop, xlBetween, Join(massiv,",") 
где massiv - это собственно одномерный массив нужных строк.
а уж по какому событию это делать, или по кнопке, или одноразово - смотрите сами
Изменено: Максим Зеленский - 08.08.2014 08:01:39
F1 творит чудеса
 
Я делаю так:
Код
[A1].Validation.Add Type:=xlValidateList, Formula1:=Join(arr, ",") 
Arr - одномерный массив
 
Уважаемые старожилы форума Максим Зеленский иЮрий М, спасибо за подсказку с выпадающим списком. Попробовал адаптировать к своему проекту - получилось. Выпадающий список предметов работает. В этой части есть следующая проблема - выпадающий список не обновляется. Первым запуском макрос нормально отрабатывается, а при следующем обращении к нему выдаётся ошибка. Причина - уже имеющийся выпадающий список в ячейке. (если его удалить, макрос опять срабатывает). Пробовал перед формирующимся выпадающим списком выполнять его удаление (макросом, записанным через макрорекордер). В этом случае выпадающий список даже не формируется. Восстанавливается работоспособность удалением списка "вручную".
Так что в этой части задачи стоит проблема с обновлением списка.

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