Вычислить средний расход масла за определенное количество циклов в PQ или PP, Суммирование данных в диапазоне последних 10 циклов с последующим смещением.
Пожалуйста подскажите как построить запрос в 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" и добавлен в модель данных, осталось допилить напильником
Михаил Л, в ячейке G12 - 14, потому что последние 10 циклов (D3:D12) - это сумма ячеек E3:E12 = 14 ед. Так же в ячейке F12 - 48:48, потому что последние 10 циклов (D3:D12) - это сумма ячеек C3:C12 = 48:48 часов.
Возможно не так сложно решить задачу, как объяснить
buchlotnik, интересное решение. Но есть не доработка, в диапазоне D15:D25 (10 циклов) и соответственно в диапазоне C15:C25 мы получим сумму 57:19 часов, а не 50:29. Так же Е15:Е25 получим 15 вместо 13. Сейчас считает по количеству строк, а нужно по сумме циклов. Т.е. каждая текущая ячейка должна иметь расчет диапазона за последние 10 циклов.
Михаил Л, да спасибо за Вашу версию. Как бы вычислить количество строк для суммы в 10 циклов и уже по ним пробегать получая результат?? Вопрос вопросов...
Михаил Л, да действительно работает. Внутри кода какая-то высшая математика Часы сейчас верно суммирует, а вот суммирование масла некорректно отрабатывает если в таблицу добавить несколько пустых строк.
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'
ac1-caesar, что смущает - пустые ячейки в столбце Hour. Значения в столбец Cyc можно трактовать: 1 - надо учитывать некое событие, пустая ячейка - не надо. Столбец Oil: Не пустая ячуйка - залито столько-то керосина, пустая - пролилось мимо канистры. Но в строке с пустой ячейкой Huor (в промежуток времени нулевой ! продолжительности) и Oil <> 0, это как ?
Но бегает очень медленно Хотел бы решить, используя List.Generate, но эта функция мне не подвластна Прошу решить более быстрым способом.
Логика поиска диапазона для масла. Например, ячейка G45. Почему в этой ячейке значение 16 ? Суммируется диапазон, один край которого начинается с этой же строки, а другой край ищется по колонке Cyc вверх пока сумма циклов не достигнет 10(выделено желтым), то есть до строки 27. Суммируются значения колонки Oil(выделено красным).
Я так понял что сейчас около двух тысяч строк Спасибо!
Цитата
buchlotnik написал: List.Generate тоже надо аккуратно использовать
При двух тыс строк работает резво. При 10000 строк работает медленней. При 50000 строк прям заметно что при работе запроса количество загруженных сток при изменении гораздо ниже чем при тех же 10000 строк. Как бы это объяснить?