Страницы: 1
RSS
Каскадные выпадающие списки. Список в Power Query, Нужно настроить сложную связку выпадающих списков у двух книг или через запрос создать именованный диапазон.
 
Здравствуйте, пересмотрел много информации.

У меня есть две книги. Книга_1 и Книга_2.

В Книге_1 у меня есть несколько листов (Лист_1, Лист_2 и т.д.) во всех есть данные организованные схожим образом.
На каждом листе у меня есть таблица с данными.(смотри картинку во вложении)

Книга_1 - является базой материалов на своем листе (Арматура, Бетон, Пиломатериал и т.д.) сформированы позиции и цены по ним.
Книга_2 - базой работ. На Лист_1, сформирован общий список с работами у каждой есть цена за материалы и сами работы. (см. Список работ)
Цену за материалы для данной работы необходимо посчитать.

В Книге_2 создан Лист_2 (см. картинку).
Список листов из Книги_1 я более менее придумал подходящий для меня способ. (см. Список листов в Книге_2 из Книги_1)
Далее (см. Лист_2) Через проверку данных в ячейках столбца А я спокойно могу выбрать нужный мне вид материала. (см. Выпад. список. стол. А)

Затем мне нужно получить в соседней правой ячейке в зависимости от выбранного "Листа из Книги_1" список конкретных материалов. (см. https://yadi.sk/i/8vy_BwgELFCbWA (по ссылке картинка, надеюсь ссылку на яндекс диск тут можно оставлять))
Я это могу делать по прямому созданию ИД, как показал на последнем скриншоте (с Я.диска)
Но я хотел заавтоматизировать этот процесс.

1) Если держать открытой обе книги.

То получилось использовать вот такую формулу в столбце B.

=ДВССЫЛ("'[База расценок.xlsm]"&$A4&"'!$B$2:$B$8")

Вроде все работает. И не нужно создавать лишние именованные диапазоны. Плюс наверно можно усовершенствовать правую часть формулы, что бы этот список был динамическим.

2) Если не держать открытой Книга_1.

Я думал что можно в Книге_2 сделать запрос к Книге_1 и через Power Query создавать динамические именованные диапазоны. Имя диапазонов должно было совпадать с названиями листов из которых они (именованные диапазоны) создавались и просто использовать их в столбцах B.

Но теоретических знаний оказалось не достаточно.

Мне хотелось бы что бы дали совет, что лучше идти по варианту 1 или как то можно организовать 2 вариант?
Есть ли еще какие-то варианты получить то что я хочу? Я пытался найти информацию разными путями, но пока не знаю куда копать.

Спасибо.
 
А файл-пример где?

Power Query не может создавать именованные диапазоны, за этим вам к VBS. Но связанные выпадающие списки на PQ решаются другим способом - делаете на PQ таблицы, по одной для каждого уровня вложенных списков. Все они вида: "Имя параметра в выпадающем списке" - "Диапазон значений, соответствующих данному параметру, в таблице следущего уровня":

Значения в списке уровня 1: =INDIRECT(INDEX(Table_Level_1;;1)

Table_Level_1:
A: Выбранное в списке 1 значениеB: Ссылка для уровня 2
Группа товаров 1C2:C3
Группа товаров 2C4:C5
Значения в списке уровня 2: =INDIRECT(VLOOKUP(Table_Level_1;(введенное пользователем значение на уровне 1);2))

Table_Level_2:
C:  Выбранное в списке 2 значение D: Ссылка для уровня 3
Товар A в группе 1 E1:E2
Товар B в группе 1 E3:E3
Товар C в группе 2 E4:E4
Товар D в группе 2 E5:E8
Значения в списке уровня 3: =INDIRECT(VLOOKUP(Table_Level_2;(введенное пользователем значение на уровне 2);2))

Table_Level_3:
E: КомпонентыF: Не используется
Компонент 1 товара A
Компонент 2 товара А
Компонент товара B
Компонент товара C
Компонент 1 товара D
Компонент 2 товара D
Компонент 3 товара D
Компонент 4 товара D
Идея в том что диапазон из первой таблицы ссылается на диапазон во второй таблице, в выпадающем списке вы используете его через ДВССЫЛ(). В этом диапазоне как раз будут данные из выпадающего списка. Может, гуру экселя вам подскажут более изящный способ. PQ в данном случае используется чтобы построить данные справочные таблицы со ссылками и значениям
Изменено: Alexey_Spb - 21.05.2019 12:07:04
Страницы: 1
Наверх