Создание общей таблицы (по составам/по изделиям)

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Создание общей таблицы (по составам/по изделиям) (Формулы/Formulas)

4step

Дата: Среда, 20.07.2022, 14:43 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 85

Репутация: 0 ±

Замечаний: 40% ±

Добрый день! Возможно ли создать с помощью Power Query общую таблицу как указанно в примере? Загрузка информации проходит на листе "По изделиям", вывод информации "Общая". Попытаюсь раскрыть задачу: Столбец "В составе" - это изделие/сборка/контейнер/папка, где в сам состав входят комплектующее/файлы (РАЗДЕЛ;Код оборудования;Наименование - (3 уникальных столбца). Вывод столбцов должны располагаться слева направо в алфавитном порядке, где количество "В составе" может быть неограниченное. Столбцы "Кол.Х" выполнены сборкой с привязкой к обозначению "В составе", а именно "Раздел" + "-" + "Кол.Х". Итоговое количество необходимо просуммировать по позициям комплектующих (перед столбцом "Примечание").

К сообщению приложен файл: 8548165.xlsx (17.2 Kb)

Ответить

msi2102

Дата: Четверг, 21.07.2022, 14:32 | Сообщение № 2

Группа: Проверенные

Ранг: Обитатель

Сообщений: 415

Репутация: 129 ±

Замечаний: 0% ±

Excel 2007

Уж больно мудрённое объяснение. В примере тоже не ясно почему количество швеллера разбито на 2 строки. Добавил ещё одну строку на лист "Общая", чтобы понятно было как работает. Если правильно понял, то вот кнопочный вариант:
[vba]

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="Общая"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"РАЗДЕЛ", type text}, {"Код оборудования", Int64.Type}, {"Наименование", type text}, {"КШМ-Кол.1", type any}, {"РШМ-Кол.1", type number}, {"ШК2-Кол.1", type number}, {"ЦКЛ-Кол.1", type number}, {"КШМ-Кол.2", type number}, {"РШМ-Кол.2", type number}, {"ЦКЛ-Кол.2", type number}, {"ШК2-Кол.2", type number}, {"СУММА-Кол.1", type number}, {"Ед. изм.1", type text}, {"СУММА-Кол.2", type number}, {"Ед. изм.2", type text}, {"Примечание", type any}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"СУММА-Кол.1", "СУММА-Кол.2"}),
    #"Другие столбцы с отмененным свертыванием1" = Table.UnpivotOtherColumns(#"Удаленные столбцы", {"РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2", "Примечание"}, "Атрибут", "Значение"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Другие столбцы с отмененным свертыванием1", "Атрибут", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"В составе", "Атрибут"}),
    #"Сведенный столбец" = Table.Pivot(#"Разделить столбец по разделителю", List.Distinct(#"Разделить столбец по разделителю"[Атрибут]), "Атрибут", "Значение", List.Sum),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Сведенный столбец",{"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Кол.1", "Ед. изм.1", "Кол.2", "Ед. изм.2", "Примечание"}),
    #"Сортированные строки" = Table.Sort(#"Переупорядоченные столбцы",{{"Наименование", Order.Ascending}})
in
    #"Сортированные строки"

[/vba]

Код

[/vba]msi2102

К сообщению приложен файл: 7505129.xlsx (28.8 Kb)

Сообщение отредактировал msi2102 - Четверг, 21.07.2022, 14:36

Ответить

СообщениеУж больно мудрённое объяснение. В примере тоже не ясно почему количество швеллера разбито на 2 строки. Добавил ещё одну строку на лист "Общая", чтобы понятно было как работает. Если правильно понял, то вот кнопочный вариант:
[vba]

Код

[/vba]

Автор - msi2102
Дата добавления - 21.07.2022 в 14:32

4step

Дата: Четверг, 21.07.2022, 14:51 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 85

Репутация: 0 ±

Замечаний: 40% ±

msi2102, Добрый день! Загрузка информации должна вноситься из "По изделиям", а вывод в "Общая". Попробовал сделать через "Сводная таблица", но не знаю как вставить "Ед.изм.Х" перед каждым "Итог...". Также не знаю как удалить кнопки сворачивания, так как хочется выполнить просто таблицей.

К сообщению приложен файл: 9283128.xlsx (40.7 Kb)

Ответить

msi2102

Дата: Пятница, 22.07.2022, 13:08 | Сообщение № 4

Группа: Проверенные

Ранг: Обитатель

Сообщений: 415

Репутация: 129 ±

Замечаний: 0% ±

Excel 2007

Попробуйте так:
[vba]

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="По_изделиям"]}[Content],
    Удаленные_столбцы = Table.SelectColumns(Источник,{"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Кол.1", "Ед. изм.1", "Кол.2", "Ед. изм.2"}),
    Несвернутые_столбцы = Table.UnpivotOtherColumns(Удаленные_столбцы, {"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2"}, "Атрибут", "Значение"),
    Объединенные_столбцы = Table.CombineColumns(Несвернутые_столбцы,{"Атрибут", "В составе"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Сведено"),
    Сведенный_столбец = Table.Pivot(Объединенные_столбцы, List.Distinct(Объединенные_столбцы[Сведено]), "Сведено", "Значение", List.Sum),
    Список_полный = List.Buffer(Table.ColumnNames(Сведенный_столбец)),
    Список_кол = List.Buffer(List.FindText(Список_полный, "Кол")),
    Список_без_кол = List.Buffer(List.Difference(Список_полный, Список_кол)),
    Список_кол_1 = List.Buffer(List.FindText(Список_кол, "Кол.1")),
    Список_кол_2 = List.Buffer(List.FindText(Список_кол, "Кол.2")),
    Список_ед_1 = List.Buffer(List.FindText(Список_без_кол, "изм.1")),
    Список_ед_2 = List.Buffer(List.FindText(Список_без_кол, "изм.2")),
    Список_порядок = List.Buffer(List.Difference(Список_без_кол, Список_ед_1 & Список_ед_2) & Список_кол_1 & {"Сумма_Кол_1"} & Список_ед_1 & Список_кол_2 & {"Сумма_Кол_2"} & Список_ед_2),
    Группа1 = Table.Group(
      Сведенный_столбец,
      {"РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2"},
      {{
        "Таблица2",
        (t) =>
        [
          d5 = List.Transform(List.Range(Table.ToColumns(t), 0, List.Count(Список_полный) - List.Count(Список_кол)), each _{0}) &
               List.Transform(List.Range(Table.ToColumns(t), List.Count(Список_полный) - List.Count(Список_кол)), each List.Sum(_)),
          r1 = Record.FromList(d5, Список_полный),
          rs1 = Record.FromList({List.Sum(Record.ToList(Record.SelectFields(r1, Список_кол_1)))},{"Сумма_Кол_1"}),
          rs2 = Record.FromList({List.Sum(Record.ToList(Record.SelectFields(r1, Список_кол_2)))},{"Сумма_Кол_2"}),
          r2 = r1 & rs1 & rs2,
          r3 = Record.ReorderFields(r2,Список_порядок)
        ][r3]
      }}),
    Результат = Table.FromRecords (Группа1[Таблица2] )
in
    Результат

[/vba]

Попробуйте так:
[vba]

Код

[/vba]msi2102

К сообщению приложен файл: 7505129_3.xlsx (36.1 Kb)

Ответить

СообщениеПопробуйте так:
[vba]

Код

[/vba]

Автор - msi2102
Дата добавления - 22.07.2022 в 13:08

4step

Дата: Пятница, 22.07.2022, 14:48 | Сообщение № 5

Группа: Пользователи

Ранг: Участник

Сообщений: 85

Репутация: 0 ±

Замечаний: 40% ±

msi2102, на процентов 95% - то что нужно, единственное хотелось бы оставить столбец "Примечание". Например: болт такой та - нужен "для каркаса"; винт такой та - нужен "для корпуса".
а) Если 2 строки или более по столбцам совпадают, а именно "В составе" + "РАЗДЕЛ" + "Код оборудования" + "Наименование", то "Примечание" сцепляется через ";".
б) Если имеется повторяющаяся номенклатура, но привязана также к другому изделию "В составе", то "Примечание" будут как уникальным (новая строка).
Есть ещё особенность с оформлением столбцов для удобства чтения (но это не обязательно). Пример прилагаю.

К сообщению приложен файл: ____.xlsx (28.5 Kb)

Ответить

msi2102

Дата: Пятница, 22.07.2022, 17:52 | Сообщение № 6

Группа: Проверенные

Ранг: Обитатель

Сообщений: 415

Репутация: 129 ±

Замечаний: 0% ±

Excel 2007

Добавил "Примечание", добавил сумму по "РАЗДЕЛУ", остальное доделывайте сами
[vba]

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="По_изделиям"]}[Content],
    Удаленные_столбцы = Table.SelectColumns(Источник,{"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Кол.1", "Ед. изм.1", "Кол.2", "Ед. изм.2", "Примечание"}),
    Несвернутые_столбцы = Table.UnpivotOtherColumns(Удаленные_столбцы, {"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2", "Примечание"}, "Атрибут", "Значение"),
    Объединенные_столбцы = Table.CombineColumns(Несвернутые_столбцы,{"Атрибут", "В составе"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Сведено"),
    Сведенный_столбец = Table.Pivot(Объединенные_столбцы, List.Distinct(Объединенные_столбцы[Сведено]), "Сведено", "Значение", List.Sum),
    Список_полный = Table.ColumnNames(Сведенный_столбец),
    Список_кол = List.Buffer(List.FindText(Список_полный, "Кол")),
    Список_без_кол = List.Buffer(List.Difference(Список_полный, Список_кол)),
    Список_кол_1 = List.Buffer(List.FindText(Список_кол, "Кол.1")),
    Список_кол_2 = List.Buffer(List.FindText(Список_кол, "Кол.2")),
    Список_порядок = List.Buffer(List.Difference(Список_без_кол, {"Ед. изм.1", "Ед. изм.2", "Примечание"}) & Список_кол_1 & {"Сумма_Кол_1", "Ед. изм.1"} & Список_кол_2 & {"Сумма_Кол_2", "Ед. изм.2", "Примечание"}),
    Тип = Table.Buffer(Table.TransformColumnTypes(Сведенный_столбец, List.Transform(Список_полный, each  if List.Contains(Список_без_кол, _) then {_, type text}  else {_, type number}))),
    Группа1 = Table.Group(
      Тип,
      {"РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2"},
      {{
        "Таблица2",
        (t) =>
        [
          d5 = List.Transform(List.Range(Table.ToColumns(t), 0, List.Count(Список_полный) - List.Count(Список_кол)-1), each _{0}) &
               List.Transform(List.Range(Table.ToColumns(t), List.Count(Список_полный) - List.Count(Список_кол)-1, 1), each Text.Combine(_, ", ")) &
               List.Transform(List.Range(Table.ToColumns(t), List.Count(Список_полный) - List.Count(Список_кол)), each List.Sum(_)),
          r1 = Record.FromList(d5, Список_полный),
          rs1 = Record.FromList({List.Sum(Record.ToList(Record.SelectFields(r1, Список_кол_1)))},{"Сумма_Кол_1"}),
          rs2 = Record.FromList({List.Sum(Record.ToList(Record.SelectFields(r1, Список_кол_2)))},{"Сумма_Кол_2"}),
          r2 = r1 & rs1 & rs2,
          r3 = Record.ReorderFields(r2,Список_порядок)
        ][r3]
      }}),
    Результат = Table.FromRecords (Группа1[Таблица2] ),
    Группа2 = Table.Group(
      Результат,
      {"РАЗДЕЛ"},
      {{
        "Таблица3",
        (t) =>
        [
          d5 = {"Итого по " & t[РАЗДЕЛ]{0}} & {null, null} &
               List.Transform(List.Range(Table.ToColumns(t), 3, List.Count(Список_кол_1) + 1), each List.Sum(_)) & {null} &
               List.Transform(List.Range(Table.ToColumns(t), 3 + List.Count(Список_кол_1)+2, List.Count(Список_кол_2) + 1), each List.Sum(_)) & {null, null},
          r1 = t & Table.PromoteHeaders(Table.Transpose(Record.ToTable(Record.FromList(d5, Список_порядок))),[PromoteAllScalars=true])
        ][r1]
      }}),
Результат1 = Table.Combine(Группа2[Таблица3])
in
    Результат1

[/vba]

Добавил "Примечание", добавил сумму по "РАЗДЕЛУ", остальное доделывайте сами
[vba]

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="По_изделиям"]}[Content],
    Удаленные_столбцы = Table.SelectColumns(Источник,{"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Кол.1", "Ед. изм.1", "Кол.2", "Ед. изм.2", "Примечание"}),
    Несвернутые_столбцы = Table.UnpivotOtherColumns(Удаленные_столбцы, {"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2", "Примечание"}, "Атрибут", "Значение"),
    Объединенные_столбцы = Table.CombineColumns(Несвернутые_столбцы,{"Атрибут", "В составе"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Сведено"),
    Сведенный_столбец = Table.Pivot(Объединенные_столбцы, List.Distinct(Объединенные_столбцы[Сведено]), "Сведено", "Значение", List.Sum),
    Список_полный = Table.ColumnNames(Сведенный_столбец),
    Список_кол = List.Buffer(List.FindText(Список_полный, "Кол")),
    Список_без_кол = List.Buffer(List.Difference(Список_полный, Список_кол)),
    Список_кол_1 = List.Buffer(List.FindText(Список_кол, "Кол.1")),
    Список_кол_2 = List.Buffer(List.FindText(Список_кол, "Кол.2")),
    Список_порядок = List.Buffer(List.Difference(Список_без_кол, {"Ед. изм.1", "Ед. изм.2", "Примечание"}) & Список_кол_1 & {"Сумма_Кол_1", "Ед. изм.1"} & Список_кол_2 & {"Сумма_Кол_2", "Ед. изм.2", "Примечание"}),
    Тип = Table.Buffer(Table.TransformColumnTypes(Сведенный_столбец, List.Transform(Список_полный, each  if List.Contains(Список_без_кол, _) then {_, type text}  else {_, type number}))),
    Группа1 = Table.Group(
      Тип,
      {"РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2"},
      {{
        "Таблица2",
        (t) =>
        [
          d5 = List.Transform(List.Range(Table.ToColumns(t), 0, List.Count(Список_полный) - List.Count(Список_кол)-1), each _{0}) &
               List.Transform(List.Range(Table.ToColumns(t), List.Count(Список_полный) - List.Count(Список_кол)-1, 1), each Text.Combine(_, ", ")) &
               List.Transform(List.Range(Table.ToColumns(t), List.Count(Список_полный) - List.Count(Список_кол)), each List.Sum(_)),
          r1 = Record.FromList(d5, Список_полный),
          rs1 = Record.FromList({List.Sum(Record.ToList(Record.SelectFields(r1, Список_кол_1)))},{"Сумма_Кол_1"}),
          rs2 = Record.FromList({List.Sum(Record.ToList(Record.SelectFields(r1, Список_кол_2)))},{"Сумма_Кол_2"}),
          r2 = r1 & rs1 & rs2,
          r3 = Record.ReorderFields(r2,Список_порядок)
        ][r3]
      }}),
    Результат = Table.FromRecords (Группа1[Таблица2] ),
    Группа2 = Table.Group(
      Результат,
      {"РАЗДЕЛ"},
      {{
        "Таблица3",
        (t) =>
        [
          d5 = {"Итого по " & t[РАЗДЕЛ]{0}} & {null, null} &
               List.Transform(List.Range(Table.ToColumns(t), 3, List.Count(Список_кол_1) + 1), each List.Sum(_)) & {null} &
               List.Transform(List.Range(Table.ToColumns(t), 3 + List.Count(Список_кол_1)+2, List.Count(Список_кол_2) + 1), each List.Sum(_)) & {null, null},
          r1 = t & Table.PromoteHeaders(Table.Transpose(Record.ToTable(Record.FromList(d5, Список_порядок))),[PromoteAllScalars=true])
        ][r1]
      }}),
Результат1 = Table.Combine(Группа2[Таблица3])
in
    Результат1

[/vba]msi2102

К сообщению приложен файл: 7505129_5.xlsx (37.5 Kb)

Ответить

СообщениеДобавил "Примечание", добавил сумму по "РАЗДЕЛУ", остальное доделывайте сами
[vba]

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="По_изделиям"]}[Content],
    Удаленные_столбцы = Table.SelectColumns(Источник,{"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Кол.1", "Ед. изм.1", "Кол.2", "Ед. изм.2", "Примечание"}),
    Несвернутые_столбцы = Table.UnpivotOtherColumns(Удаленные_столбцы, {"В составе", "РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2", "Примечание"}, "Атрибут", "Значение"),
    Объединенные_столбцы = Table.CombineColumns(Несвернутые_столбцы,{"Атрибут", "В составе"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Сведено"),
    Сведенный_столбец = Table.Pivot(Объединенные_столбцы, List.Distinct(Объединенные_столбцы[Сведено]), "Сведено", "Значение", List.Sum),
    Список_полный = Table.ColumnNames(Сведенный_столбец),
    Список_кол = List.Buffer(List.FindText(Список_полный, "Кол")),
    Список_без_кол = List.Buffer(List.Difference(Список_полный, Список_кол)),
    Список_кол_1 = List.Buffer(List.FindText(Список_кол, "Кол.1")),
    Список_кол_2 = List.Buffer(List.FindText(Список_кол, "Кол.2")),
    Список_порядок = List.Buffer(List.Difference(Список_без_кол, {"Ед. изм.1", "Ед. изм.2", "Примечание"}) & Список_кол_1 & {"Сумма_Кол_1", "Ед. изм.1"} & Список_кол_2 & {"Сумма_Кол_2", "Ед. изм.2", "Примечание"}),
    Тип = Table.Buffer(Table.TransformColumnTypes(Сведенный_столбец, List.Transform(Список_полный, each  if List.Contains(Список_без_кол, _) then {_, type text}  else {_, type number}))),
    Группа1 = Table.Group(
      Тип,
      {"РАЗДЕЛ", "Код оборудования", "Наименование", "Ед. изм.1", "Ед. изм.2"},
      {{
        "Таблица2",
        (t) =>
        [
          d5 = List.Transform(List.Range(Table.ToColumns(t), 0, List.Count(Список_полный) - List.Count(Список_кол)-1), each _{0}) &
               List.Transform(List.Range(Table.ToColumns(t), List.Count(Список_полный) - List.Count(Список_кол)-1, 1), each Text.Combine(_, ", ")) &
               List.Transform(List.Range(Table.ToColumns(t), List.Count(Список_полный) - List.Count(Список_кол)), each List.Sum(_)),
          r1 = Record.FromList(d5, Список_полный),
          rs1 = Record.FromList({List.Sum(Record.ToList(Record.SelectFields(r1, Список_кол_1)))},{"Сумма_Кол_1"}),
          rs2 = Record.FromList({List.Sum(Record.ToList(Record.SelectFields(r1, Список_кол_2)))},{"Сумма_Кол_2"}),
          r2 = r1 & rs1 & rs2,
          r3 = Record.ReorderFields(r2,Список_порядок)
        ][r3]
      }}),
    Результат = Table.FromRecords (Группа1[Таблица2] ),
    Группа2 = Table.Group(
      Результат,
      {"РАЗДЕЛ"},
      {{
        "Таблица3",
        (t) =>
        [
          d5 = {"Итого по " & t[РАЗДЕЛ]{0}} & {null, null} &
               List.Transform(List.Range(Table.ToColumns(t), 3, List.Count(Список_кол_1) + 1), each List.Sum(_)) & {null} &
               List.Transform(List.Range(Table.ToColumns(t), 3 + List.Count(Список_кол_1)+2, List.Count(Список_кол_2) + 1), each List.Sum(_)) & {null, null},
          r1 = t & Table.PromoteHeaders(Table.Transpose(Record.ToTable(Record.FromList(d5, Список_порядок))),[PromoteAllScalars=true])
        ][r1]
      }}),
Результат1 = Table.Combine(Группа2[Таблица3])
in
    Результат1

[/vba]

Автор - msi2102
Дата добавления - 22.07.2022 в 17:52

4step

Дата: Суббота, 23.07.2022, 03:40 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 85

Репутация: 0 ±

Замечаний: 40% ±

msi2102, Благодарю Вас за проделанную работу! Работа уникальная.