Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Вычислить средний расход масла за определенное количество циклов в PQ или PP, Суммирование данных в диапазоне последних 10 циклов с последующим смещением.
 
#1
06.07.2021 16:19:15
Уважаемые форумчане!

Пожалуйста подскажите как построить запрос в PQ или создать меру в PP для расчета среднего расхода масла за последние 10 циклов.
Имеется таблица с данными/колонки: дата (Date), количество часов работы (Hour), циклы (Cyc), количество залитого масла (Oil).
Формула расчета следующая Oil consumption = total oil in 10 cycles / total hours in 10 cycles.

Пример вложен:
В примере видно что за первые 10 циклов (с 1-ой по 10-ю строку) мы имеем 49:49 часов и 12 ед. масла, где получаем средний расход = 0,24 ед. масла.
Далее в следующие 10 циклов имеем 54:05 часов и 15 ед. масла, где получаем средний расход = 0,28 ед. масла.
И т.д.

Задача - сделать эти расчеты в Pover Query или Pover Pivot откуда и будет построен график.
Сделан запрос в PQ "OilConsTable" и добавлен в модель данных, осталось допилить напильником :)
Изменено: ac1-caesar - 06.07.2021 21:59:17
 
 
 
#2
06.07.2021 22:03:59
ac1-caesar, почему в ячейке G12 - 14 , а не 3   ?
И в ячейке F12 не 4:16
НА МЯСО В ПРОФИЛЕ
 
 
 
#3
06.07.2021 23:11:50
Михаил Л, в ячейке G12 - 14, потому что последние 10 циклов (D3:D12) - это сумма ячеек E3:E12 = 14 ед.
Так же в ячейке F12 - 48:48, потому что последние 10 циклов (D3:D12) - это сумма ячеек C3:C12 = 48:48 часов.

Возможно не так сложно решить задачу, как объяснить  :)  
Изменено: ac1-caesar - 06.07.2021 23:21:49
 
 
 
#4
06.07.2021 23:57:12
del
Изменено: buchlotnik - 22.08.2021 00:20:58
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#5
07.07.2021 10:48:41
buchlotnik, интересное решение. Но есть не доработка, в диапазоне D15:D25 (10 циклов) и соответственно в диапазоне C15:C25 мы получим сумму 57:19 часов, а не 50:29. Так же Е15:Е25 получим 15 вместо 13.
Сейчас считает по количеству строк, а нужно по сумме циклов. Т.е. каждая текущая ячейка должна иметь расчет диапазона за последние 10 циклов.
Изменено: ac1-caesar - 07.07.2021 10:53:16
 
 
 
#6
07.07.2021 10:55:19
Цитата
ac1-caesar написал:
есть не доработка
доработайте
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#7
07.07.2021 11:03:39
buchlotnik, к сожалению не имею достаточных знаний. Изучаю Ваш код, и пока не разобрался  :D .
Если Вам не трудно, пожалуйста посодействуйте.
Спасибо.
 
 
 
#8
07.07.2021 14:03:51
Цитата
ac1-caesar написал:
считает по количеству строк
Так же считает
Небыстрый вариант
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="OilConsTable"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"id", Int64.Type}, {"Date", type date}, {"Hour", type number}, {"Cyc", Int64.Type}, {"Oil", Int64.Type}, {"Hrs in 10 cyc", type number}, {"Oil in 10 cyc", Int64.Type}, {"Oil consumption", type number}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Hrs in 10 cyc", "Oil in 10 cyc", "Oil consumption", "id"}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Удаленные столбцы", "Индекс", 1, 1, Int64.Type),
    #"Переименованные столбцы" = Table.RenameColumns(#"Добавлен индекс",{{"Индекс", "id"}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"id", "Date", "Hour", "Cyc", "Oil"}),
    Custom1 = Table.Buffer(Table.FromColumns({{-9..0}},{"id"})&#"Переупорядоченные столбцы"),
    #"Added Custom" = Table.AddColumn(Custom1, "a", each let  a=Table.Range(Custom1,[id],10), b =List.Sum(a[Hour]), b2 =List.Sum(a[Oil]), b3 = b2/(b*24)
in #table(3,{{b,b2,b3}})
),
    #"Removed Top Rows" = Table.Skip(#"Added Custom",10),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Top Rows", "a", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
    #"Expanded {0}"
НА МЯСО В ПРОФИЛЕ
 
 
 
#9
07.07.2021 14:38:37
Михаил Л, да спасибо за Вашу версию. Как бы вычислить количество строк для суммы в 10 циклов и уже по ним пробегать получая результат?? Вопрос вопросов...
 
 
 
#10
07.07.2021 16:37:52
Цитата
ac1-caesar написал:
Вопрос вопросов...
это как были получены значения в примере - например F24:H24 - по строке цикл не обозначен, масло почему-то суммируется.
Цитата
ac1-caesar написал:
Возможно не так сложно решить задачу, как объяснить  
возможно, только вы не объяснили никак;
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#11
07.07.2021 17:56:38
Подогнал под пример
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="OilConsTable"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"id", Int64.Type}, {"Date", type date}, {"Hour", type number}, {"Cyc", Int64.Type},
    {"Oil", Int64.Type}, {"Hrs in 10 cyc", type number}, {"Oil in 10 cyc", Int64.Type}, {"Oil consumption", type number}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Hrs in 10 cyc", "Oil in 10 cyc", "Oil consumption", "id"}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Удаленные столбцы", "Индекс", 1, 1, Int64.Type),
    #"Переименованные столбцы" = Table.RenameColumns(#"Добавлен индекс",{{"Индекс", "id"}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"id", "Date", "Hour", "Cyc", "Oil"}),
    Custom1 = Table.Buffer(#"Переупорядоченные столбцы"),
    #"Added Custom" = Table.AddColumn(Custom1, "a", each let i=[id], a2= Table.RemoveLastN(Custom1, each [id] <> i), a=Table.LastN(a2, 10),
    a3=10-List.Sum(a[Cyc]), a4=Table.LastN(a2, 10+a3),a5=Table.LastN(Table.SelectRows(a2, each [Hour] <> null),10), b =List.Sum(a5[Hour]), 
    b2 =List.Sum(a4[Oil]), b3 = b2/(b*24) in #table(3,{{b,b2,b3}})),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom", "a", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
    #"Expanded {0}"
НА МЯСО В ПРОФИЛЕ
 
 
 
#12
07.07.2021 21:21:41
Михаил Л, да действительно работает. Внутри кода какая-то высшая математика  :D
Часы сейчас верно суммирует, а вот суммирование масла некорректно отрабатывает если в таблицу добавить несколько пустых строк.
 
 
 
#13
07.07.2021 21:30:18
Цитата
buchlotnik написал:
это как были получены значения в примере - например F24:H24 - по строке цикл не обозначен, масло почему-то суммируется
В принципе да, по идее для ячейки F24, G24, H24 вообще расчет не нужен т.к. цикл не обозначен, просто не знал как лучше показать суть вычисления.

Цитата
buchlotnik написал:
возможно, только вы не объяснили никак
Не спорю, задача не тривиальная  :)  
Изменено: ac1-caesar - 07.07.2021 22:00:54
 
 
 
#14
08.07.2021 07:34:22
Цитата
ac1-caesar написал:
математика
Там все просто. Просто вы не объясняете как считать, вот и приходиться додумывать
Цитата
ac1-caesar написал:
Часы сейчас верно суммирует, а вот суммирование масла
Для часов и для масла по разному считает. Потому и написал что подогнал под пример)
Цитата
ac1-caesar написал:
если в таблицу добавить несколько пустых строк
В примере покажите. Желательно максимум вариантов ситуаций
НА МЯСО В ПРОФИЛЕ
 
 
 
#15
08.07.2021 12:25:06
Старый добрый VBA
Код
Function StartPos(ColumnCycl As Range)
Rem Для текущей ячейки находим позицию верхней ячейки диапазонов суммирования
    Dim L1 As Long, S As Long
    L1 = Application.Caller.Row - ColumnCycl.Row + 1
    Do While L1 > 1
        S = S + ColumnCycl.Cells(L1, 1)
        If S = 10 Then Exit Do
        L1 = L1 - 1
    Loop
    StartPos = L1
End Function 'StartPos'
 
 
 
#16
08.07.2021 13:29:07
Цитата
Михаил Л написал:
В примере покажите
Ваш запрос вывел в таблицу рядом и подсветил несоответствия. Добавил вариантов. Пустых ячеек С и D может быть конечно гораздо больше.
 
 
 
#17
08.07.2021 13:35:17
С.М., спасибо. Элегантное решение. Но в моей ситуации происходит сборка со многих книг и листов, поэтому рассматриваю PQ, PP.
 
 
 
#18
08.07.2021 18:06:24
Цитата
ac1-caesar написал:
подсветил несоответствия
Насчет остального не спорю, а вот в ячейках G17 и G18 должно быть значение 13.
Если не так, то почему?
Уже устал догадываться)
НА МЯСО В ПРОФИЛЕ
 
 
 
#19
08.07.2021 20:06:29
Цитата
Михаил Л написал:
а вот в ячейках G17 и G18 должно быть значение 13
Верно подметили - моя ошибка. Спасибо за корректировку.
 
 
 
#20
09.07.2021 13:28:54
ac1-caesar,
что смущает - пустые ячейки в столбце Hour.
Значения в столбец Cyc  можно трактовать:
1 - надо учитывать некое событие,  пустая ячейка - не надо.
Столбец Oil:
Не пустая ячуйка - залито столько-то керосина,  пустая - пролилось мимо канистры.
Но в строке с пустой ячейкой Huor (в промежуток  времени нулевой ! продолжительности) и Oil <> 0,
это как ?
 
 
 
#21
09.07.2021 13:38:10
Цитата
С.М. написал:
это как ?
поверьте, не только вас мучает данный вопрос  ;)  поскольку логика не озвучена и нужно только догадываться по примерам - оставил попытки решения
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#22
09.07.2021 13:51:44
В личке задачка решилась  
НА МЯСО В ПРОФИЛЕ
 
 
 
#23
09.07.2021 14:03:21
И что теперь? Удалить тему или покажете решение?
 
 
 
#24
09.07.2021 14:28:20
vikttur, покажу.
С работы до дома доберусь..
ps:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="OilConsTable"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"id", Int64.Type}, {"Date", type date}, {"Hour", type number}, {"Cyc", Int64.Type},
    {"Oil", Int64.Type}, {"Hrs in 10 cyc", type number}, {"Oil in 10 cyc", Int64.Type}, {"Oil consumption", type number}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Hrs in 10 cyc", "Oil in 10 cyc", "Oil consumption", "id"}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Удаленные столбцы", "Индекс", 1, 1, Int64.Type),
    #"Переименованные столбцы" = Table.RenameColumns(#"Добавлен индекс",{{"Индекс", "id"}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"id", "Date", "Hour", "Cyc", "Oil"}),
    Custom1 = Table.Buffer(#"Переупорядоченные столбцы"),
    #"Added Custom" = Table.AddColumn(Custom1, "a", each 
                                                         let 
                                                            i = [id], 
                                                           a2 = Table.RemoveLastN(Custom1, each [id] <> i), 
                                                            s = i-Table.LastN(Table.SelectRows(a2, each ([Cyc] = 1)), 10){0}[id], 
                                                            a = Table.LastN(a2, s+1), 
                                                            b1 = List.Sum(a[Hour]),  
                                                            b2 = List.Sum(a[Oil]), 
                                                            b3 = b2/(b1*24) 
                                                        in 
                                                            #table({"Hrs in 10 cyc","Oil in 10 cyc","Oil consumption"},{{b1,b2,b3}})),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom", "a", {"Hrs in 10 cyc", "Oil in 10 cyc", "Oil consumption"})
in
    #"Expanded {0}"
Изменено: Михаил Л - 09.07.2021 16:38:37
НА МЯСО В ПРОФИЛЕ
 
 
 
#25
12.07.2021 19:24:11
Цитата
Михаил Л написал:
задачка решилась
Но бегает очень медленно
Хотел бы решить, используя List.Generate, но эта функция мне не подвластна :(
Прошу решить более быстрым способом.

Логика поиска диапазона для масла. Например, ячейка G45. Почему в этой ячейке значение 16 ?  Суммируется диапазон, один край которого начинается с этой же строки, а другой край ищется по колонке Cyc вверх пока сумма циклов не достигнет 10(выделено желтым), то есть до строки 27. Суммируются  значения колонки Oil(выделено красным).
НА МЯСО В ПРОФИЛЕ
 
 
 
#26
12.07.2021 20:22:09
Цитата
Михаил Л написал:
Но бегает очень медленно
а зачем запрос грузится в модель данных? грузите просто на лист - не менял ни строчки кода:
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#27
12.07.2021 20:51:35
buchlotnik, даже на двух тысячах строк медленно. В модель или на лист - нет разницы
НА МЯСО В ПРОФИЛЕ
 
 
 
#28
12.07.2021 21:10:57
del
Изменено: buchlotnik - 21.08.2021 23:23:22
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#29
13.07.2021 11:59:09
Цитата
buchlotnik написал:
сколько в реальности?
Я так понял что сейчас около двух тысяч строк
Спасибо!
Цитата
buchlotnik написал:
List.Generate тоже надо аккуратно использовать
При двух тыс строк работает резво. При 10000 строк работает медленней. При 50000 строк прям заметно что при работе запроса количество загруженных сток при изменении гораздо ниже чем при тех же 10000 строк. Как бы это объяснить?
НА МЯСО В ПРОФИЛЕ
 
 
 
#30
13.07.2021 12:18:44
Цитата
Михаил Л написал:
Как бы это объяснить?
Цитата
buchlotnik написал:
оператива не резиновая
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
Страницы: 1
Наверх