Как разделить общую стоимость контракта на равные месячные значения, начиная с даты начала контракта, в Excel?

Question

Мне нужно создать формулу для заполнения ежемесячных столбцов ежемесячной выручкой на основе общей стоимости контракта, равномерно распределенной между продолжительностью контракта, начиная с даты его подписания. Информация, которую я в данный момент имею в своем отчете, это дата начала, общая стоимость контракта и продолжительность (в месяцах). Я знаю, что могу легко рассчитать месячную стоимость и затем вручную вводить эту сумму в каждый месяц, но у меня есть отчет с сотнями записей, поэтому мне нужна формула, которую я могу применить к каждой записи.

Формат, о котором я думаю (но я открыт для других предложений!) как показано ниже ...

Я надеюсь, что с магической формулой это будет выглядеть так (я набрал ее вручную)...

Затем я смогу создать сводную таблицу для подведения итогов ежемесячного прогноза доходов.

Мне не нужна формула для подсчета неполных месяцев, например, если контракт начинается в середине месяца, вполне нормально, чтобы он считался полным месяцем выручки. Если есть более простой способ разбить это на месячные столбцы, это не проблема - это только сводная информация, которая будет представлена в моем отчете.

Пожалуйста, дайте мне знать, если у вас есть идеи, как я могу это сделать, и дайте мне знать, если вам нужны какие-либо разъяснения.

Спасибо!

Answer 1

В ячейке D2 скопируйте / вставьте эту формулу: _{^{(возможно, вам придется изменить запятые на точки с запятой в зависимости от региональных настроек.)}}

=IF(AND(D$1>=DATE(YEAR($A2),MONTH($A2),1),D$1<=DATE(YEAR($A2),MONTH($A2)+$B2,1)),$C2/$B2,"")

Разбивая его, логика такова:

IF
   (the date in row 1 is >= the first day of the contract start date month)
   AND
   (the date in row 1 is <= the first day of the month of the contract start date plus the duration in months)
THEN
   calculate the total value divided by the number of months
ELSE
   return blank (change "" to 0 or "-" or whatever fits your need)

Ключевым моментом в этом является то, что значения в первой строке - заголовки столбцов - являются фактическими датами, а не текстом. Как написана формула, эти даты должны быть первыми каждого месяца. Это не сложно, если вы наберете первые два или три, а затем перетащите остальные; Excel будет увеличиваться автоматически. Если это проблема, дайте мне знать, и мы сможем найти другое средство.