Я делаю некоторые пересчеты в Excel для длинного списка, чтобы подготовить его для статистического анализа в других программах, и у меня возникают проблемы с одной вещью, которую я хочу сделать.
У меня есть список некоторых переменных, среди которых идентификатор, переменная даты и числовая переменная. Мне нужно добавить переменную, которая суммирует числовую переменную из всех экземпляров, которые находятся в пределах 365 дней после экземпляра с тем же идентификатором. Это относительно легко выполнимо с помощью sumifs(). Но я также хочу добавить вес к числовому значению, чтобы экземпляры со значением даты, близким к текущему значению, имели больший вес, чем те, которые находятся в далеком прошлом (вес вчера 364/365, месяц назад 335/365, завтра год назад 1/365 и т. д.
Есть ли способ сделать это с помощью функций?
# A B C D
1 ID# datenumber value newvar
2 Ken 01-01-2015 1000 somevalue
3 Ida 01-01-2014 1000 somevalue
4 Ida 01-06-2015 1234 somevalue
5 Ida 01-10-2015 5678 somevalue
6 Ida 01-12-2015 1000 y
y = weighted sum of all values of all Ida within one year, line 3 and 4 above.
The newvar sumifs y would be (if "ID#" was in cell A1):
=SUMIFA(C:C,A:A,A6,B:B,"<"&B6,B:B,">"&B6-365) or 1234+5678 = 6912
The "weighted sumifs" would be y = (182/365*1234)+(304/365*5678) = 5344.38
Я думаю, я мог бы создать новый столбец с взвешенным значением на конкретную дату, сделать newvar для всех экземпляров с этим datenumer, скопировать значения на месте, затем переместить день вверх и повторить его с экземплярами с этим значением даты и т.д. Через VBA, но такого рода хакерство действительно ограничит возможности использования книги в других областях. Поэтому я надеюсь, что у кого-то есть решение с функциями ячеек вместо VBA.