Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query. Рассчитать нарастающий итог с условием и создание групп
 
#1
08.10.2021 12:40:56
Добрый день.

Подскажите, как в Power Query сделать нарастающий итог с условием для суммирования, в файл исходник и ожидаемый результат.
Пробовал с помощью функции List.Generate не получилось.

Попытки увенчались успехом лишь в расчете нарастающего итога с помощью функции List.Generate, но не получилось ее доработать для расчета с учетом ограничения 20%.

Пробовал решить математически, без цикла, но тоже не получилось, пример расчета в файле.
Изменено: vikttur - 08.10.2021 14:47:42
 
 
 
#2
08.10.2021 13:11:21
Marioma,
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content], 
  addPrice = Table.AddColumn ( src, "Цена", each [Руб] / [ШТ], type number ), 
  tbl = Table.Sort ( addPrice, { { "Цена", Order.Ascending } } ), 
  items = List.Buffer ( Table.ToRecords ( tbl ) ), 
  allAmount = List.Accumulate ( items, 0, ( s, c ) => s + c[Руб] ), 
  count = List.Count ( items ), 
  gen = List.Generate (
    () =>  [
      i         = 0, 
      rec       = items{i}, 
      cumAmount = rec[Руб], 
      Группа    = 1, 
      cumPerc   = cumAmount / allAmount, 
      Доля      = rec[Руб] / allAmount
    ], 
    each [i] < count, 
    each [
      i         = [i] + 1, 
      rec       = items{i}, 
      cumAmount = [cumAmount] + rec[Руб], 
      Группа    = Number.IntegerDivide ( [cumPerc], 0.2 ) + 1, 
      cumPerc   = cumAmount / allAmount, 
      Доля      = rec[Руб] / allAmount
    ], 
    each [[rec], [Доля], [Группа]]
  ),
  toTbl = Table.FromRecords ( gen ), 
  expandTbl = Table.ExpandRecordColumn ( toTbl, "rec", Table.ColumnNames ( tbl ) ), 
  typed = Table.TransformColumnTypes (
    expandTbl, 
    { { "Руб", type number }, { "ШТ", Int64.Type }, { "Цена", type number }, { "Доля", Percentage.Type }, { "Группа", Int64.Type } }
  )
in
  typed
Изменено: surkenny - 08.10.2021 13:28:35
 
 
 
#3
08.10.2021 17:37:43
surkenny, спасибо
Не могу понять почему у Вас получилось 3 группы с долей около 20%?
Так по условию "не менее 20%" получится как минимум 4 группы с долей?  как я понял у Вас через деление считается, думаю тут только с циклом полчится, который будет суммировать нарастающим итогом и как дойдет до первого значения >20% отсекать группу и снова со следующего значения начнет суммировать.

Файл с проверкой приложил.
 
 
 
#4
08.10.2021 17:56:01
Marioma, ну чутка-то подправьте:)
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content], 
  addPrice = Table.AddColumn ( src, "Цена", each [Руб] / [ШТ], type number ), 
  tbl = Table.Sort ( addPrice, { { "Цена", Order.Ascending } } ), 
  items = List.Buffer ( Table.ToRecords ( tbl ) ), 
  allAmount = List.Accumulate ( items, 0, ( s, c ) => s + c[Руб] ), 
  count = List.Count ( items ), 
  gen = List.Generate (
    () =>  [
      i         = 0, 
      rec       = items{i}, 
      cumAmount = rec[Руб], 
      Группа    = 1, 
      cumPerc   = cumAmount / allAmount, 
      Доля      = rec[Руб] / allAmount
    ], 
    each [i] < count, 
    each [
      i         = [i] + 1, 
      rec       = items{i}, 
      cumAmount = if [cumPerc] < 0.2 then [cumAmount] + rec[Руб] else rec[Руб], 
      Группа    = if [cumPerc] < 0.2 then [Группа] else [Группа] + 1, 
      cumPerc   = cumAmount / allAmount, 
      Доля      = rec[Руб] / allAmount
    ], 
    each [[rec], [Доля], [Группа]]
  ),
  toTbl = Table.FromRecords ( gen ), 
  expandTbl = Table.ExpandRecordColumn ( toTbl, "rec", Table.ColumnNames ( tbl ) ), 
  typed = Table.TransformColumnTypes (
    expandTbl, 
    { { "Руб", type number }, { "ШТ", Int64.Type }, { "Цена", type number }, { "Доля", Percentage.Type }, { "Группа", Int64.Type } }
  )
in
  typed
Изменено: surkenny - 09.10.2021 13:13:55
 
 
 
#5
08.10.2021 18:24:34
surkenny, спасибо, получилось, надеюсь другим тоже поможет данный код
 
 
 
#6
18.08.2022 15:38:47
Добрый день.
Подскажите, как в Power Query сделать нарастающий итог в группе и с условием. Файл с данными и нужным результатом во вложении.
Необходимо по каждому контрагенту считать нарастающий итог <= 100000 руб., как только нарастающий итог будет >100000, то счет нужно начать заново.
Я в Power Query делаю сводную таблицу, её копирую и вставляю значения в новую умную таблицу, где добавила два столбца "нарастающий итог" и "дата договора" с формулами "Если".
Может можно эти два столбца вычислять в Power Query. Пыталась что-то сделать, максимум дошла до нужной группировки (во вложении подключение называется нараст итоги).  
 
 
 
#7
18.08.2022 16:15:30
Наталья Яшина, можно:) Что именно у Вас не получилось реализовать в PQ (что пробовали прописывать внутри List.Generate)?:)
Изменено: surkenny - 18.08.2022 16:16:10
 
 
 
#8
18.08.2022 22:08:55
У меня получилось только рассчитать нарастающий итог. Как его сделать по 100000 руб и в разрезе каждого контрагента я не знаю. В интернете искала решение и набрела на ваш форум. Вроде бы задача от Marioma чем-то похожа, но другая. С функцией List.Generate я не знакома. Пыталась разобраться, но она для меня оказал
ась сложной. В Power Query не смогла реализовать столбец с нарастающим итогом до 100000 и выводом в соседней колонке даты. В надежде на помощь написала Вам.
Изменено: Наталья Яшина - 18.08.2022 22:14:40
 
 
 
#9
19.08.2022 12:50:05
Вот, что у меня получается с нарастающим итогом.  
 
 
 
#10
19.08.2022 19:22:17
Наталья Яшина, так что ли?
Код
let
  data = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  typed = Table.TransformColumnTypes (
    data,
    {
      { "№ п/п", Int64.Type },
      { "Дата", type date },
      { "Документ", type text },
      { "Номер", Int64.Type },
      { "Сумма", type number },
      { "Валюта", type text },
      { "Информация", type text }
    }
  ),
  rename = Table.RenameColumns ( typed, { { "Информация", "Контрагент" } } ),
  delClmn = Table.RemoveColumns ( rename, { "№ п/п", "Валюта" } ),
  sort = Table.Sort ( delClmn, { { "Контрагент", Order.Ascending }, { "Дата", Order.Ascending } } ),
  group = Table.Group ( sort, { "Контрагент", "Дата" }, { { "Сумма", each List.Sum ( [Сумма] ), type nullable number } }, GroupKind.Local ),
  group2 = Table.Group (
    group,
    { "Контрагент" },
    {
      {
        "tbl",
        ( t ) =>
          [
            recs = List.Buffer ( Table.ToRecords ( t ) ),
            generate = List.Generate (
              () => [ i = 0, newRec = [ Нарастающий итог = recs{i}[Сумма], Дата договора = null ], result = Record.Combine ( { recs{i}, newRec } ) ],
              each [i] < List.Count ( recs ),
              each [
                i = [i] + 1,
                newGroup = [newRec][Нарастающий итог] + recs{i}[Сумма] > 100000,
                newRec = [
                  Нарастающий итог = if newGroup then recs{i}[Сумма] else [newRec][Нарастающий итог] + recs{i}[Сумма],
                  Дата договора    = if newGroup then recs{i}[Дата] else null
                ],
                result = Record.Combine ( { recs{i}, newRec } )
              ],
              each [result]
            ),
            toTable = Table.FromRecords ( generate )
          ][toTable],
        type table
      }
    },
    GroupKind.Local
  ),
  combine = Table.Combine ( group2[tbl] ),
  typed2 = Table.TransformColumnTypes (
    combine,
    { { "Дата", type date }, { "Дата договора", type date }, { "Сумма", type number }, { "Нарастающий итог", type number } }
  )
in
  typed2
 
 
 
#11
20.08.2022 18:49:16
Идеально. Все работает. Огромное спасибо!!!!!!!  
 
 
 
Страницы: 1
Наверх