Дейв упростил вопрос Майки, чтобы он не был таким открытым. Позвольте мне повторить это. Мы знаем, когда контракт начинается и заканчивается. Существуют правила для принятия решения о том, когда фаза дохода начинается и заканчивается. Мы хотим найти две вещи:
- Сколько месяцев в фазе дохода, и
- Сколько выручки происходит в 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 году. Cell 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для итоговых полей.