Дейв упростил вопрос Майки, чтобы он не был таким открытым. Позвольте мне повторить это. Мы знаем, когда контракт начинается и заканчивается. Существуют правила для принятия решения о том, когда фаза дохода начинается и заканчивается. Мы хотим найти две вещи:
- Сколько месяцев в фазе дохода, и
- Сколько выручки происходит в 2016 году.
Я собираюсь найти доход, который происходит в тот же год, что и в начале года, в каком бы году это ни было.
Я определил имена для следующих ячеек. Contract.value определяется как 1000.
Start $C$3
Stop $D$3
Months $E$3
Start и Stop рассчитываются по датам начала и окончания контракта. Нам нужно количество месяцев от Start до Stop чтобы мы могли найти «фазу равного дохода». Это действительно легко. Используйте DATEDIF или установите флажок Как рассчитать количество месяцев между двумя датами в Excel для альтернативного метода. Вот формула для ячейки E3 .
=DATEDIF(Start,Stop,"M")+1
+1 означает количество месяцев, включающих первый и последний месяцы. В этом примере есть 9 месяцев дохода с июля 2016 года по март 2017 года.
Вот как найти доход в 2016 году, в котором начинается этап доходов.
Сначала найдите количество месяцев в 2016 году. Ячейка C2 вычисляет это:
=IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1
Вычислите количество месяцев, умноженное на доход за один месяц в фазе дохода. Cell D2 делает это:
=C2*Contract.value/Months
Объединение C2 и D2 в одну формулу показывает тот же результат в ячейке E2 .
=(IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1)*Contract.value/Months
У этого подхода есть некоторые недостатки. Что если вы хотите найти доход в 2017 году? Формула для 2017 года следует той же схеме, что и C2 , но это не то же самое. Вещи становятся беспорядочными, если этап доходов охватывает более двух лет.
Проблему можно обобщить, составив упрощенную таблицу амортизации и используя сводную таблицу для ее суммирования. Start , Stop и Months - все, что нужно для составления списка месяцев и дохода. Сводная таблица находит доход за каждый год в фазе дохода.
Ряд 2 особенный.
Вот формулы для A2:C2 .
=Start
=YEAR(A2)
=Contract.value/Months
Используйте формулы для A3:C3 Fill Down , чтобы заполнить список. Заполните количество строк, чтобы учесть как можно больше месяцев в фазе дохода. Легко добавить больше.
=IF(A2<Stop,DATE(YEAR(A2),MONTH(A2)+1,1),"")
=IF(A3<>"",YEAR(A3),"")
=IF(A3<>"",Contract.value/Months,"")
"Трюк" в A3 . Когда дата в столбце A для предыдущей строки находится после « Stop , ячейка в столбце A пуста, а каждая следующая строка пуста, поэтому в фазе дохода будет по одной строке на каждый месяц - не больше, не меньше. Трюк полезен, когда вы хотите составить список, но заранее не знаете количество строк.
Чтобы создать сводную таблицу, выберите весь список, включая пустые строки. При выборе всего списка сводная таблица будет работать, даже если вы измените даты контракта. Используйте поле « Year для строки сводной таблицы, а также « Sum of Revenue и « Count of Year для итоговых полей.
