Скользящая средняя $/час по колонкам время и прибыль

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

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

Cage

Дата: Пятница, 25.01.2013, 20:26 | Сообщение № 1

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

Ранг: Новичок

Сообщений: 18

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

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

Задача

В таблице 2 колонки:
A - время в часах
B - прибыль в $

Требуется с помощью формул вычислить и заполнить колонку
C - $/час за последние n часов (например n=10)

По колонке C легко построить скользящую среднюю показателя $/час со сглаживающим интервалом n.

Пример вычисления

Чтобы вычислить значение в ячейке С10 при n=10, нужно отобрать те строки, сумма времени в которых будет не менее 10 часов. Это три строки: 8, 9 и 10. Время = 5 + 3 + 7 = 15, что больше 10. Поэтому из строки 8 нужно взять только 2 часа из 7.

Прибыль = 70 + 20 + 2/7*150
Время = 5 + 3 + 2 = 10
$/час = Прибыль/Время = 13.29

Для С9 $/час = (20 + 150)/10 = 17
Для С8 $/час = (150 + 3/25*25)/10 = 15.3
И т.д.

Не получается решить задачу в общем виде. Помогите, пожалуйста.

Ответить

ikki

Дата: Пятница, 25.01.2013, 20:32 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1906

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

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

Excel 2003, 2010

решение с помощью udf (функции, определенной пользователем, написанной на VBA) интересует?

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki

Ответить

Cage

Дата: Пятница, 25.01.2013, 20:36 | Сообщение № 3

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

Ранг: Новичок

Сообщений: 18

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

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

Очень хотелось бы обойтись стандартными функциями Excel.

Ответить

ikki

Дата: Пятница, 25.01.2013, 20:39 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1906

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

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

Excel 2003, 2010

ок, я понял. ждём формулистов.

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki

Ответить

Cage

Дата: Суббота, 26.01.2013, 04:34 | Сообщение № 5

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

Ранг: Новичок

Сообщений: 18

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

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

На всякий случай прикрепляю файл с исходными данными из первого поста.

К сообщению приложен файл: moving_average.xls (24.0 Kb)

Ответить

Serge_007

Дата: Суббота, 26.01.2013, 10:39 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

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

Замечаний: ±

Excel 2016

Цитата (Cage)

хотелось бы обойтись стандартными функциями Excel

Не думаю что это хорошая мысль...

ЮMoney:41001419691823 | WMR:126292472390

Ответить

ikki

Дата: Суббота, 26.01.2013, 10:53 | Сообщение № 7

Группа: Друзья

Ранг: Старожил

Сообщений: 1906

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

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

Excel 2003, 2010

вообще не вижу смысла в данном случае ломать голову со встроенными функциями wink

udf очень простая
[vba]

Код

Function f(v, hRng As Range, pRng As Range)
      For i = hRng.Rows.Count To 1 Step -1
          If (s + hRng.Cells(i, 1)) >= v Then
              f = (t + pRng.Cells(i, 1) / hRng.Cells(i, 1) * (v - s)) / v
              Exit Function
          Else
              s = s + hRng.Cells(i, 1)
              t = t + pRng.Cells(i, 1)
          End If
      Next
End Function

[/vba]

пример использования в вашем файле - в яч.C3 формула

Код

=f($E$1;$A$1:$A3;$B$1:$B3)

и тянем вниз.

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki

Сообщение отредактировал ikki - Суббота, 26.01.2013, 10:57

Ответить

Cage

Дата: Суббота, 26.01.2013, 19:17 | Сообщение № 8

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

Ранг: Новичок

Сообщений: 18

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

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

Цитата (Serge_007)

Не думаю что это хорошая мысль...

Конечно, не хочется использовать громоздкое решение со множеством "ЕСЛИ()".
Но если кто-то смог бы предложить более изящный вариант, то почему бы и нет?

Цитата (ikki)

вообще не вижу смысла в данном случае ломать голову со встроенными функциями

Спасибо за предложенный вариант.
Но я все еще надеюсь, что кто-нибудь из формулистов, которых мы ждем, заглянет в эту тему и предложит свой вариант :).
Все-таки не хочется связываться с макросами, какими бы простыми они ни были.

Ответить

MCH

Дата: Суббота, 26.01.2013, 20:17 | Сообщение № 9

Группа: Админы

Ранг: Старожил

Сообщений: 2004

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

Замечаний: ±

Задача близка к задаче списания по методу LIFO, формульное решение получится громозким, придется использовать доп столбцы

Ответить

Cage

Дата: Суббота, 26.01.2013, 21:16 | Сообщение № 10

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

Ранг: Новичок

Сообщений: 18

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

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

Цитата (MCH)

формульное решение получится громозким, придется использовать доп столбцы

Вариант с дополнительными столбцами (желательно не более одного) меня вполне устраивает.
Пока удалось составить формулы для вычисления $/час (столбец D), используя дополнительный столбец с количеством используемых для вычисления строк (столбец C).
Но вычислить формулами количество используемых строк пока не удалось.