Страницы: 1
RSS
Многоуровневые выпадающие списки без пустых элементов
 
Здравствуйте, уважаемые знатоки Excel!

Прошу вашей помощи в следующей задачке: есть многоуровневый список (во вложенном файле на листе "списки"). Список не статичный, каждый уровень может меняться / дополняться с течением времени. Список организован именно таким образом (в три столбца), менять организацию списка возможности нет. Требуется во вложенном файле на листе "ЗАДАЧКА" создать три связанных выпадающих списка, которые не будут учитывать пустые элементы списков (более подробно в файле).
 
Yl, прсто нужно забыть о древообразном представлении и сделать нормальные таблицы.

А далее зависимые выпадающие списки или через имена или через динамический диапазон строятся элементарно.
Изменено: БМВ - 01.09.2021 20:46:50
По вопросам из тем форума, личку не читаю.
 
Yl,
посмотрите здесь
 
БМВ, в том-то и сложность, что список видоизменять нельзя. Максимум, что можно сделать, это заменить пустые значения дубликатами.

evgeniygeo, спасибо, я в курсе как создавать связанные списки. к сожалению, данная статья не помогает решить мою задачку.
 
Yl,
в таком случае, я думаю, что сначала нужно привести списки в порядок.
Как вариант с помощью PQ. Уверен, что на форуме найдутся те, кто сможет упростить запрос, но т.к. это другая тема, нужно создать новую тему.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="раздел"]}[Content],
    #"Повышенные заголовки" = Table.PromoteHeaders(Источник, [PromoteAllScalars=true]),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Повышенные заголовки", each ([#"Статьи уровня 1 (раздел)"] <> null)),
    #"Измененный тип" = Table.TransformColumnTypes(#"Строки с примененным фильтром",{{"Column1", type text}}),
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип", {"Column1"}, группы, {"Копия Column1.1.1"}, "группы", JoinKind.LeftOuter),
    #"Развернутый элемент группы" = Table.ExpandTableColumn(#"Объединенные запросы", "группы", {"Column1", "Статьи уровня 2 (группа)"}, {"Column1.1", "Статьи уровня 2 (группа)"}),
    #"Сортированные строки" = Table.Sort(#"Развернутый элемент группы",{{"Column1", Order.Ascending}})
in
    #"Сортированные строки"

let
    Источник = Excel.CurrentWorkbook(){[Name="группы"]}[Content],
    #"Повышенные заголовки" = Table.PromoteHeaders(Источник, [PromoteAllScalars=true]),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Повышенные заголовки", each ([#"Статьи уровня 2 (группа)"] <> null)),
    #"Измененный тип" = Table.TransformColumnTypes(#"Строки с примененным фильтром",{{"Column1", type text}}),
    #"Дублированный столбец" = Table.DuplicateColumn(#"Измененный тип", "Column1", "Копия Column1"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Дублированный столбец", "Копия Column1", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Копия Column1.1", "Копия Column1.2"}),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Разделить столбец по разделителю", "Копия Column1.1", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Копия Column1.1.1", "Копия Column1.1.2"}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Разделить столбец по разделителю1",{"Column1", "Статьи уровня 2 (группа)", "Копия Column1.1.1"}),
    #"Объединенные запросы" = Table.NestedJoin(#"Другие удаленные столбцы", {"Column1"}, статьи, {"Копия Column1.1.1"}, "статьи", JoinKind.LeftOuter),
    #"Развернутый элемент статьи" = Table.ExpandTableColumn(#"Объединенные запросы", "статьи", {"Column1", "Статьи уровня 3 (статья затрат)"}, {"Column1.1", "Статьи уровня 3 (статья затрат)"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Развернутый элемент статьи",{"Column1", "Статьи уровня 2 (группа)", "Column1.1", "Статьи уровня 3 (статья затрат)", "Копия Column1.1.1"})
in
    #"Переупорядоченные столбцы"

let
    Источник = Excel.CurrentWorkbook(){[Name="статьи"]}[Content],
    #"Повышенные заголовки" = Table.PromoteHeaders(Источник, [PromoteAllScalars=true]),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Повышенные заголовки", each ([#"Статьи уровня 3 (статья затрат)"] <> null)),
    #"Измененный тип" = Table.TransformColumnTypes(#"Строки с примененным фильтром",{{"Column1", type text}}),
    #"Дублированный столбец" = Table.DuplicateColumn(#"Измененный тип", "Column1", "Копия Column1"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Дублированный столбец", "Копия Column1", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Копия Column1.1", "Копия Column1.2"}),
    #"Разделить столбец по положению" = Table.SplitColumn(#"Разделить столбец по разделителю", "Копия Column1.1", Splitter.SplitTextByPositions({0, 1}, true), {"Копия Column1.1.1", "Копия Column1.1.2"}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Разделить столбец по положению",{"Column1", "Статьи уровня 3 (статья затрат)", "Копия Column1.1.1"})
in
    #"Другие удаленные столбцы"
Изменено: evgeniygeo - 03.09.2021 06:47:14
 
evgeniygeo, хорошо, допустим сделаем так. А дальше при добавлении новых статей как быть со списками второго и третьего уровня? Имена же списков не будут автоматически расширяться, т.к. они внутри "умной" таблицы (будут статичны и нужно будет править руками). Или есть какая-то конструкция из формул, которую можно засунуть непосредственно в форму создания списка (в диапазон)? Не можете ли показать на моем примере во вложенном файле как прописать, чтобы списки 2ого и 3его уровня менялись динамически? Что-то пока не понимаю как это сделать...
 
Доброе утро!
Yl, вам же написали приведите в порядок таблицы
Цитата
Yl написал:
(будут статичны и нужно будет править руками)
ничего править не надо если вы создадите именные диапазоны.
1. Содаете умные таблицы через PQ из одной базы, сколько уровней столько и таблиц (обязательно с сортировкой)
2. Создаете именованные диапазоны
3. Создаете выпадающие списки

Во вложении пример, но без PQ , так как код запроса вам уже выложили
 
Adamm,благодарю! Всё встало на свои места!
 
Цитата

Пример.xlsx  (11.25 КБ)
Добрый день!

у меня похожая ситуация. Пример решает задачу, только выпадающие списки должны находиться в этом документе но в другом листе.
При копировании выподающих списков на другой лист работает только список с разделами, а список групп нет. как поправить?
Изменено: Aleks2k - 24.09.2021 15:24:31
 
Aleks2k, цитата - не бездумная копия. Приведите в порядок сообщение
Страницы: 1
Читают тему (гостей: 1)
Наверх