VetCAD

Функция Excel PowerQuery для подсчета вхождений листов чертежей в другие чертежи

   1 оценка

размещено: 05 Марта 2025
обновлено: 11 Марта 2025

Сделал функцию для 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