Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query аналог формулы СУММЕСЛИМН
 
#1
16.05.2017 12:28:52
Уважаемые, а кто подскажет возможно ли в PQ сделать конструкцию по аналогии с формулой из Excel СУММЕСЛИМН? Чтобы при добавлении столбца можно было бы прописать суммирование по выборке?
Изменено: PooHkrd - 03.02.2022 12:27:10
Вот горшок пустой, он предмет простой...
 
 
 
#2
16.05.2017 12:41:26
В PQ есть функции "Колонка по условию"(Conditoinal column) и "Пользовательская колонка" (Custom column). В первой можно использовать механизм "что...если...", а во второй писать пользовательские формулы.

Формулы на языке M для PQ можно найти здесь https://msdn.microsoft.com/library/1ed840b1-7e20-4419-ad2f-d82054c9b2ab
 
 
 
#3
16.05.2017 12:52:39
Denis87,это мне известно. А как имея таблицу:
Товар / Город / Продажи
дыня / Тамбов / 5
дыня / Липецк / 15
тыква / Тамбов / 10
тыква / Липецк / 19

получить на выходе таблицу
Товар / Город / Продажи / Сумма продаж
дыня / Тамбов / 5 / 20
дыня / Липецк / 15 / 20
тыква / Тамбов / 10 / 29
тыква / Липецк / 19 / 29
Где в столбце сумма продаж должна посчитаться сумма продаж в разрезе товаров по всем городам? В екселе это 1 минута, а в PQ я такое могу сделать только через создание отдельного запроса с группировкой по товару исходного массива и потом добавлением к исходному массиву столбца через слияние из запроса с группировкой.

А мне вот хочется прописать формулой, но не очень понимаю как. А прописывать условия для каждого товара слишком жирно, у меня ассортимент 15 тыщ СКЮ. Причем есть сильное подозрение, что это можно реализовать через перемножение записей с функцией Record.FieldValues(_) (слава товарищу Зеленскому за её для меня открытие). но голова что-то не варит как.
Изменено: PooHkrd - 08.12.2021 08:55:48
Вот горшок пустой, он предмет простой...
 
 
 
#4
16.05.2017 12:58:25
PooHkrd, выложите файл-пример чтобы можно было экспериментировать, иначе трудно что-то предлагать.
 
 
 
#5
16.05.2017 13:06:24
Да не вопрос. Выложил.
Изменено: PooHkrd - 25.01.2021 17:18:56
Вот горшок пустой, он предмет простой...
 
 
 
#6
16.05.2017 13:42:04
Нашел такой код, который, если я правильно понимаю, как раз делает то, что вам нужно, но пока сам разбираюсь как его под ваши переменные переписать:
Код
Table.AggregateTableColumn(  
  
    Table.FromRecords(  
  
    {[t = Table.FromRecords({[a=1, b=2, c=3], [a=2,b=4,c=6]}), b = 2]}, type table [t = table [a=number, b=number, c=number], b = number]  
  
    ), "t",  
  
    {{"a", List.Sum, "sum of t.a"},  
  
     {"b", List.Min, "min of t.b"},  
  
     {"b", List.Max, "max of t.b"},  
  
     {"a", List.Count, "count of t.a"}})  
 
 
 
#7
16.05.2017 15:23:28
Основной вопрос - зачем заставлять PQ считать формулы, которые потом можно выполнить в DAX (в разы быстрее). Уверен, сам по себе такой столбец вам не нужен, потом все равно будете делить одно на другое и так далее, считать разные ABC и т.п.
Задача PQ - подготовить данные для дальнейших расчетов.

Если всё же надо именно так, то три варианта одной формулой:
1. Почти СУММЕСЛИМН
Код
= Table.AddColumn(Источник, "Сумма Продаж по товару", each List.Sum(Table.SelectRows(Источник, (r)=>r[Код]=_[Код] and r[Товар]=_[Товар])[Продажи]))

2. Можно так (две группировки - сумма и "все строки"), потом разворачиваем:
Код
    #"Grouped Rows" = Table.Group(Источник, {"Код", "Товар"}, {{"tab", each _, type table}, {"Сумма продаж по товару", each List.Sum([Продажи]), type number}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "tab", {"Город", "Продажи"}, {"Город", "Продажи"})

3. Но лучше и гораздо быстрее на вашем объеме данных - через джойн (слияние) с группировкой. Группировки и джойн в PQ оптимизированы, и выполняется быстрее, чем группировка и разворачивание.
Код
= Table.Join(Источник, {"Код", "Товар"}, Table.Group(Источник, {"Код", "Товар"}, {{"Сумма Продаж по товару 2", each List.Sum([Продажи])}}), {"Код", "Товар"})

Не стоит в PQ пытаться всегда соблюсти логику формул Excel, это не всегда лучший путь.
F1 творит чудеса
 
 
 
#8
16.05.2017 15:23:44
Слегка обновил пример, оказывается группировать и объединять можно прямо в одном запросе. Это уже интереснее и многое упрощает, плюс уменьшает время на вычисления в больших массивах.
Denis87, большое вам спасибо за попытку помочь. Если не сможете найти решение, то и фиг с ним, буду делать как в приложенном примере.
Изменено: PooHkrd - 16.05.2017 15:24:05
Вот горшок пустой, он предмет простой...
 
 
 
#9
16.05.2017 15:36:22
Максим Зеленский, ну до второго примера уже сам до копался на просторах интернетов. А вот третий просто прекрасен.
На счет логики формул Ексель - очень трудно её выковыривать из головы, когда за 4 года решение всех задач делал через формулы, а потом автоматизацию заказывал у ВБАшников. С PQ знаком полтора месяца - меня ВБАшники наши забыли уже как выгляжу. И все задачи решаются в 2-3 раза быстрее. Но голову потихоньку переформатирую, за это в том числе и вам спасибо.
Вопрос закрыт, всем еще раз спасибо!.
Вот горшок пустой, он предмет простой...
 
 
 
#10
20.06.2017 13:11:11
Добрый день Максим Зеленский,
Цитата
Максим Зеленский написал:
зачем заставлять PQ считать формулы, которые потом можно выполнить в DAX (в разы быстрее)
DAX это уже Power BI   или в Power Pivot тоже он?

выше в теме мы предложили несколько вариантов решения задачи с суммированием
с точки зрения оптимизации кода и ускорения обновления запросов какой вариант будет быстрее там где меньше строк кода или где меньше промежуточных вычислений? к чему нужно стремиться при работе запросами?
Изменено: Blood81 - 20.06.2017 13:11:24
 
 
 
#11
20.06.2017 13:54:48
Цитата
Blood81 написал:
DAX это уже Power BI   или в Power Pivot тоже он?
Тоже он. Еще и в SSAS Tabular тоже он.
Цитата
Blood81 написал:
какой вариант будет быстрее там где меньше строк кода или где меньше промежуточных вычислений?
Количество строк кода вряд ли сильно влияет :) но абстрактно - сложно давать совет. Код быстрее, когда он не создает сущности сверх необходимого, и когда использует оптимальный набор действий с точки зрения алгоритма.
Конечно, чем меньше вычислений, тем быстрее - меньше загружаем память, меньше дергаем разные источники данных, и т.п. Изучайте встроенные функции, чтобы не делать лишние телодвижения. Их много - почти 600, для большинства простых и не очень операций. Но только практика и анализ готовых решений даст результат. Тут, как и в Excel, одну задачу можно решить несколькими способами, и постепенно научитесь не делать лишние вычисления и операции.

Вообще оптимизация кода в PQ - задача нетривиальная и иногда неочевидная, к тому же различающаяся немного в Power BI и в Excel.
F1 творит чудеса
 
 
 
#12
21.06.2017 16:34:13
Blood81, для себя вывел такое правило: через PQ тащу все что нужно из источников и через джойны формирую столбцы таблицы, которая будет основой для строк итогового отчета, потом все остальные таблицы со значениями также затаскиваю в модель, и настраиваю связи от них к основной. А уже в модели на DAX  добавляю новые столбцы с нужными вычислениями. Ну и результаты в сводную.
Так работает на порядок быстрее если нужно переженить друг с другом порядка 20-ти таблиц по 20к-100к записей в каждой.

Изначально пытался делать всё через PQ в результате оно или зависало или рассчитывалось по 3-5 часов (я так понял это из-за "ленивых" вычислений в нем).

Для меня только одна проблема: в PQ разобраться значительно проще, т.к. конструктор запросов в нем сделан так, что при наличии справки не разберется разве что только полный имбецил. А вот с РР все не так привычно из-за чего кажется, что сложнее. Но это ничего - освоим.
Вот горшок пустой, он предмет простой...
 
 
 
#13
23.06.2017 12:35:57
Совет 1: изучайте правила построения аналитической модели данных. Неправильная модель данных приводит к тормозам и неудобству и сложности
Совет 2: делайте прототип модели данных, готовьте исходные таблицы в PQ, анализируте данные в DAX. Некоторые вычисления можно и нужно делать в PQ (группировки, например), но большая часть вычислений быстрее делается в DAX. В общем, задача PQ - подготовить модель данных, а анализ - DAX
Совет 3: Поменьше расчетных доп.столбцов (в 90% случаев нужен не столбец, а мера).
F1 творит чудеса
 
 
 
#14
22.01.2025 14:17:27
Цитата
написал:
3. Но лучше и гораздо быстрее на вашем объеме данных - через джойн (слияние) с группировкой. Группировки и джойн в PQ оптимизированы, и выполняется быстрее, чем группировка и разворачивание.
Добрый день. Спасибо. Повторил, все отлично работает, сумма по каждому клиенту отдельно считается:
Код
= Table.Join(РасчетыСКлиентами, "Код", Table.Group(РасчетыСКлиентами, "Код", {{"Сумма3", each List.Sum([Сумма])}}), "Код")
Но мне потребовалось сложить не все числа, а только положительные, и пока писал вопрос как это сделать, нашелся ответ:
Код
= Table.Join(РасчетыСКлиентами, "Код", Table.Group(Table.SelectRows(РасчетыСКлиентами, each [Сумма] > 0), {"Код"}, {{"СуммаПоложительных", each List.Sum([Сумма]), type number}}), "Код")
Все получилось. Может кому-то поможет. Задачу можно было решить иначе, но мне хотелось одной строкой.
 
 
 
#15
22.01.2025 15:00:09
Цитата
Андрей Михайлов написал:
Задачу можно было решить иначе, но мне хотелось одной строкой.
M - функциональный язык. Вы вообще весь запрос можете сделать одной строкой (функция от функции от функции от...).
1. Удобно ли это поддерживать? То есть не нужно прям пытаться все написать одним "шагом", но что-то реально можно объединить.
2. Главный вопрос - это все-таки производительность. Тут без вашего примера не скажешь.
Но я бы делал по второму варианту Максима Зеленского из #7. Предположение, что это гораздо быстрее будет, чем лишний джоин. Но нужно тестить.
И его тоже можно сделать одним шагом:
Код
  #"Grouped Rows" = Table.ExpandTableColumn (
    Table.Group (
      Источник, 
      { "Код", "Товар" }, 
      {
        { "tab", ( t ) => t, type table }, 
        { "Сумма продаж по товару", ( t ) => List.Sum ( t[Продажи] ), type number }
      }
    ), 
    "tab", 
    { "Город", "Продажи" }
  )
Изменено: surkenny - 22.01.2025 15:02:27
 
 
 
Страницы: 1
Наверх