Уважаемые модераторы, не спешите удалять или игнорировать этот пост - он является НЕ тысячным клоном себе подобных, НО попыткой и просьбой о реализации ДЕЙСТВИТЕЛЬНО УНИВЕРСАЛЬНОГО, решающего БОЛЬШИНСТВО вопросов по данной популярной теме (кроме того сюда всегда можно будет отправить по этой теме). Начну с формы ведения. Предлагаю в качестве платформы создания таких списков - "умные" таблицы, как учит Николай Павлов, т.к. они сразу решают множество проблем по работе с базой данных и являются универсальными практически для любых потребностей (сами растягиваются, сами формируют именованые диапазоны полей и т.д.). Наиболее близкие к желаемому примеры найдены здесь, за авторством ber$erk аж в начале 2013 года: http://goo.gl/i39PAE и здесь http://goo.gl/mT3jcm (первый вариант очень узко заточен, а второй отказывается работать с большим количеством данных (хотя очень крутой и универсальный)...
Короче говоря, создавать 2х уровневые выпадающие списки мы умеем вот по этой теме http://planetaexcel.ru/techniques/1/38/#6067, а сделать так, чтобы в каком-либо диапазоне НА ЛИСТЕ отсутствовали дубликаты можно, предварительно сделав сводную на основе необходимого списка и ссылаться уже на неё (автообновлять можно простым макросом) (найдено здесь http://goo.gl/TosXzm)
Для удобства объяснения введу такие понятия для примера: 1 (первый) уровень списков - исходный начальный диапазон (овощи и фрукты); 2 уровень (помидоры и огрурцы - овощи, а апельсины и мандарины -фрукты; 3 уровень - соответственно дальше по уточнениям и т.д.)
С первой и ОСНОВНОЙ проблемой я столкнулся, когда искал способ удалять дубликаты ИЗ ФОРМИРУЕМОГО (СМЕЩ+ИНДЕКС+ПОИСКПОЗ) выпадающего списка, то есть НА ЛЕТУ (в то же время есть способ удаления дубликатов из списка НА ЛИСТЕ (но хотя бы НЕ формулой массива) здесь http://www.planetaexcel.ru/techniques/2/103/).
Вторая проблема возникает, когда внутри уровня частично или полностью пересекаются формируемые списки (например овощи-красные-помидоры и фрукты-красные-яблоки) - здесь признак 2 уровня дублируется для уровня выше (первого), поэтому вновь сформированный зависимый выпадающий список по критерию "красные" выдаст либо И помидоры, И яблоки (если сортировка будет произведена по 2 уровню), либо ТОЛЬКО "помидоры" (т.к. овощи первые по алфавиту), если будет проведена необходимая сортировка справа-налево. Решением будет являться создание дополнительного столбца после каждого критерия, кроме первого (причём и в справочнике, и в листе выбора), в котором будут сцепляться все предыдущие критерии. Соответственно, выбор следующего уровня будет осуществляться по сцепке всех предыдущих.
Это, на мой взгляд, основные проблемы, которые возникают при формировании ЗНАЧИТЕЛЬНЫХ каскадных выпадающих списков. Решение вижу только макросами и UDF, т.к. набор и сложность формул и списков, чтобы ЭТО реализовать, отобьёт любое желание. Чтобы не требовалось сортировки, сводных (желательно даже обойтись без создания вручную формульных диапазонов). Как вариант файл-шаблон с таблицами и макросами (заполнил таблицы на нужное количество уровней и вперёд) Файл с примерами прикреплён. Слежу за темой...
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Юрий. не понял, что ваша форма делает... Мечта хозяйки это просто название листа со сводной таблицей, которая формирует ПЕРВЫЙ диапазон уникальных значений (выпадающий список для первого столбца) - не зацикливайтесь на нём, - это моя вина (не мог удержаться от примитивного юморка). Самое первоочередное, что я не могу сделать сам - это УДАЛИТЬ ПОВТОРЫ (ДУБЛИКАТЫ) В ДИНАМИЧЕСКИ СОЗДАВАЕМОМ ДИАПАЗОНЕ для выпадающего списка. То есть я создаю зависимый выпадающий список через СМЕЩ+ИНДЕКС+ПОИСКПОЗ из приёмов http://planetaexcel.ru/techniques/1/38/#6067. Но если во втором (условно) столбце значения повторяются несколько раз (потому что служат источником для выпадающего списка следующего уровня), то и выпадающем списке их будет несколько. Вот не знаю, как это убрать... Лучше, конечно, что-то дописать в формулу, по которой он формируется, но сойдёт и вариант с созданием временного столбца, где будет происходить удаление дубликатов... Перезалил файл-пример... Дублирую здесь
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Файл не смотрел. Я делаю следующим образом: в коллекцию отбираю уникальные, затем в укромном местечке (можно на скрытом листе) наполняю ими именованный динамический диапазон, который и использую для выпадающих списков. Можно было бы и без промежуточного диапазона, но есть ограничение на длину строки.
Юрий М, вот вы всё это говорите, а я понял только, что макросом наполняете заранее созданный именованный диапазон. Тут главное слово - макросом, потому что я вне игры)))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
а какой-нибудь примерчик с таким макросом не найдётся?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Все, кто пытался прикрутить данную реализацию каскадных списков к более-менее обширным данным, сталкивался с одной печальной ситуацией: всё вроде бы работает, но после переоткрытия файла Excel вам с прискорбием сообщает, что ваш файл повреждён и предлагает его восстановить, что успешно и делает. Возникает это из-за того, что существует ограничение в 255 символов на строку, которую мы прописываем в правило проверки данных для реализации выпадающего списка. Когда мы создаём этот список программно, то Excel на это не ругается (в отличие от попытки это сделать через стандартное диалоговое окно). Не ругается он на это и при сохранении файла, а вот при открытии - ой. В связи с этим у меня возникла идея не хранить в ячейке правило валидации вообще. То есть на событие SelectionChange мы налету формируем выпадающий список, а на событие Change мы этот список удаляем, так как пользователь уже выбрал интересовавшее его значение. Однако, если пользователь не осуществляет выбор значения из списка, то событие Change не возникает и правило контроля данных остаётся, что опять несёт риск "повреждения" файла. Поэтому приходится идти на такой извращенческий приём, как перебор всех правил контроля данных на листе и удаление "наших" (те, которые коррелируют по расположению с листов BACK). Сейчас я прикрутил это к событиям листа Activate / Deactivate. Если у вас на листе много ячеек, использующих правила валидации данных, но которые не связаны с каскадными выпадающими списками, то, данное решение я, пожалуй, вам НЕ порекомендую использовать
Короче, когда книгу открываю в следующий раз - всё слетает (((
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack_Famous написал: существует ограничение в 255 символов
А я об этом говорил в #4 - сам в своё время столкнулся этой проблемой )) См. пример. Заполнение первого списка уникальными происходит при активации первого листа. Второй список будет заполняться при выборе данных в первом выпадающем списке. Столбцы (листы) с динамическими диапазонами можно скрыть. Комментарии писать жутко лень )
Юрий М, разберусь (во всяком случае, надеюсь на это) - спасибо большое! Завтра отпишусь
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Юрий М, посмотрел, проверил. Штука чудная! Дубликаты удаляет, ячейки очищает, списки формирует и, самое главное, очень шустро работает (увеличил диапазоны в 20 раз - время работы также незаметно)! К сожалению, как и в 90% всех подобных случаев, изменить под универсальные нужды самостоятельно не смогу . Дело в том, что источник для всех выпадающих должен быть один, в одной "умной" таблице, на одном листе и иметь классическую древовидную структуру - это делается для удобства редактирования и анализа всех имеющихся данных для выпадающих списков. Есть идея, но, опять же, реализация зависит только от участников форума... У Nerv есть надстройка, которой я часто пользуюсь - http://excelvba.ru/code/DropDownList. В ней есть файл шаблон, в котором всё настраивается. Связи осуществляются через указанные диапазоны. Может быть что-то подобное можно было бы сделать и для выпадающих (связь: столбец источник - столбец выбора; зависимый столбец выбора).
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Я тоже пару лет назад споткнулся на этих 255 символах. Выкручивался убиением списков при закрытии книги, и созданием заново при открытии. Но думаю лучше вариант как делает Юрий (правда я его файл не смотрел) - использовать диапазоны.
Универсальные списки с зависимостями я дела в свое время, но через форму пользователя. Выдернуть из проекта возможности нет - слишком большой. Стандартные списки - это самоубийство, по-любому где-то да произойдет казус.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Hugo, The_Prist, спасибо вам большое...видимо, буду обходить все списки, что выше 2х уровней))) В ближайшем будущем займусь изучением VBA, так что своё видение решения этой (и не только) проблемы, естественно выложу на Планете P.S.: неужели дубликаты в списке не убить на лету? )))))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack_Famous написал: Дело в том, что источник для всех выпадающих должен быть один, в одной "умной" таблице, на одном листе и иметь классическую древовидную структуру
Покажите небольшой файл пример.
Цитата
Jack_Famous написал: Может быть что-то подобное можно было бы сделать и для выпадающих (связь: столбец источник - столбец выбора; зависимый столбец выбора).
И уточните этот момент - в каком виде пользователь должен делать выбор.
Hugo, вообще конечно можно (фантазия человека безгранична). Но как по мне, так эти связаные списки для того и нужны, чтобы с каждым этапом сужать область поиска. В случае с длинными именами можно просто пользовать форму от Nerv - в ней отличный контекстный поиск ))) но я не вижу смысла такие делать. Сейчас изучаю нормализацию данных, реляционные базы данных и т.д. так вот там, (как и Николай Павлов) рекомендуется дробить таблицу по смысловым группам, чтобы впоследствии с каждой такой группой (поле, столбец) можно было работать (фильтровать, сортировать и т.д.) Юрий М, сейчас сделаю...
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Вообще ничего ил шаблон подстановки...? Я создал 6 именованных диапазонов - по 3 на таблицу
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Именованные диапазоны INPUT_lvl_1, INPUT_lvl_2 и INPUT_lvl_3 - это как раз и есть ОБЛАСТИ на листе ввода, в которых будут создаваться выпадающие списки. А диапазоны LIST_lvl_1, LIST_lvl_2 и LIST_lvl_3 - области на листе СПИСКОВ, ИЗ КОТОРЫХ формируются выпадающие списки.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Вот с формой. Дальше (по кнопке) что должно происходить? P.S. Столбец-сцепка (D) в коде не задействован, но если его удалить физически, то нужно будет немного подправить код.
Я ,короче, офигел... Браво, Юрий, браво!!! А то,что столбец-сцепка не задействован это вообще супер (то есть код ищет сам не по ОДНОМУ, а по ВСЕМ предыдущим). Нет, правда - я прям поражён. И это полчаса... Однако, VBA определённо стоит изучать
1. Я добавил ещё 2 уровня - не могли бы вы распространить код на новые условия, т.к. я не совсем понял, как он меняется в каждом блоке (смог только удалить столбец сцепки и заменить 5 на 4 в коде ) 2. Можно ли вставлять значения из формы в, новую строку в конце таблицы в соответствующие поля?
Если сможете, оставьте в коде комментарии... И буду я на выходных потихоньку вникать и тестить))))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Да как скажете, Юрий! Мне тут кода разбирать до Рождества)))) Спасибо ОГРОМНОЕ за помощь!
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄