Сделал функцию для Excel PowerQuery которая считает количество вхождений листов в другие листы.
Входным аргументом для функции является умная таблица вида:
В этой таблице в первом столбце и в шапке перечислены все листы чертежа. На пересечении рядов и колонок записано количество вхождений листов из шапки таблицы в листы из соответствующего ряда (например, в состав листа 1 входят 2 листа 2 и 10 листов 3).
Функция выдает результат в виде таблицы:
Зная общее количество листов и заведя спецификации по каждом листу можно сосчитать материалы для спецификации всего комплекта:
Код функции:
let GetAssemblyCount = (table as table) as table => let #"Исходная таблица" = table, Источник = let Источник = #"Исходная таблица", #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(Источник, {"Сборка"}, "Атрибут", "Значение"), #"Переименованные столбцы" = Table.RenameColumns(#"Другие столбцы с отмененным свертыванием",{{"Атрибут", "Компонент"}, {"Значение", "Количество"}}), Result = #"Переименованные столбцы" in Result, // Таблица всех сборок AllAssemblies = Table.SelectColumns(#"Исходная таблица",{"Сборка"}), // Исходная таблица со списком всех сборок. Если в сборке нет других сборок - то Компонент и Количество равно null Source = let #"Объединенные запросы" = Table.NestedJoin(AllAssemblies ,{"Сборка"}, Источник,{"Сборка"},"NewColumn"), #"Развернутый элемент NewColumn" = Table.ExpandTableColumn(#"Объединенные запросы", "NewColumn", {"Компонент", "Количество"}, {"Компонент", "Количество"}), #"Дублированный столбец" = Table.DuplicateColumn(#"Развернутый элемент NewColumn", "Сборка", "Родитель"), res = #"Дублированный столбец" in res, //Объединяем таблицу всех сборок с полученной таблице fnNestedJoin = (table2 as table) as table => let temp1 = Table.NestedJoin(AllAssemblies ,{"Сборка"}, table2 , {"Родитель_prev"}, "NewColumn"), temp2 = Table.ExpandTableColumn(temp1, "NewColumn", {"Количество"}, {"Количество"}), temp3 = Table.DuplicateColumn(temp2, "Сборка", "Родитель"), temp4 = Table.SelectRows(temp3, each ([Количество] <> null)), res = Table.ReorderColumns(temp4,{"Родитель", "Количество"}), res1 = Table.SelectColumns(res, {"Родитель", "Количество"}) in res1, //Функция формирования таблицы сборок, для которых исходная сборка является компонентом fnSub = (table as table, filterValue as text, quantity as number) as table => let temp0 = Table.RenameColumns(table ,{{"Родитель", "Родитель_prev"},{"Количество", "Количество_prev"}}), temp1 = Table.SelectRows(temp0, each [Компонент]=filterValue), temp2 = Table.AddColumn(temp1 , "Количество", each [Количество_prev]*quantity, type number), temp3 = Table.RemoveColumns(temp2 , {"Количество_prev"}), res1 = fnNestedJoin (temp3), res2 = Table.AddColumn(res1, "Предки", each @fnSub(table, [Родитель], [Количество])) in res2 , // Рекурсивная функция для разворачивания таблиц RecursiveUnfoldTables = (inputTable as table, column as text) as any => let ExpandedTable = Table.ExpandTableColumn(inputTable , column , {"Количество", "Предки"}, {"Количество.OLD", "Предки"}), t1 = Table.AddColumn(ExpandedTable , "Количество", each if [Количество.OLD]=null then [Count.tmp] else [Количество.OLD], type number), t2 = Table.RemoveColumns(t1, {"Count.tmp", "Количество.OLD"}), t3 = Table.RenameColumns(t2, {"Количество", "Count.tmp"}), q2 = List.Sum (Table.Column(Table.AddColumn(t3, "Custom", each if [Предки]<>null then 1 else null), "Custom")), t4 = if q2 = null then t3 else @RecursiveUnfoldTables(t3,column) in t4, //Создаем таблицу Компонент-Родитель-Количество из Таблицы всех сборок t1 = Table.AddColumn(AllAssemblies , "Count.tmp", each 1), t2 = Table.AddColumn(t1, "Предки", each fnSub(Source, [Сборка], 1)), t3 = RecursiveUnfoldTables (t2, "Предки"), t4 = Table.Group(t3, {"Сборка"}, {{"Количество", each List.Sum([Count.tmp]), type number}}) in t4 in GetAssemblyCount