Разбиение суммы платежа по месяцам в зависимости от даты и процента оплаты

[8bplol]

Здравствуйте!

Прошу помощи в следующей задаче.
В столбцах K и L находятся даты оплаты аванса и расчета, в столбцах I и J - проценты аванса и расчета, в столбце Е сумма сделки.
Необходимо любыми средствами (встроенными в Excel формулами и функциями или средствами макросов) добиться разбиения суммы из столбца Е по столбцам аванса и расчета в части таблицы с месяцами, начиная со столбца M по столбец AG, а также в столбец AI в зависимости от дат, указанных в столбцах K и L.

В приложении показан конечный желаемый результат. Используется Excel 2010.

[AlexM12]

Четыре формулы

=ЕСЛИ(K5<M$3;E5*I5;"")
=ЕСЛИ(ТЕКСТ($K5;"МГГГ")=ТЕКСТ(N$3;"МГГГ");$E5*$I5;"")
=ЕСЛИ(ТЕКСТ($L5;"МГГГ")=ТЕКСТ(N$3;"МГГГ");$E5*$J5;"")
=ЕСЛИ(L5>=AI$3;E5*J5;"")

Первая и последняя протягиваются вниз.
Вторая и третья выделяются вместе и тянуться вправо, а затем вниз.

[IgorGO]

в М5 формула
=СУММПРОИЗВ(RC5*RC9:RC10*(RC11:RC12 <R3C))

в N5 формула
=СУММПРОИЗВ(RC5*RC9:RC10*(ТЕКСТ(RC1 1:RC12;"ММ!ГГ")=ТЕКСТ(ИНДЕКС(R3;СТО ЛБЕЦ()-ОСТАТ(СТОЛБЕЦ();2));"ММ!ГГ"))*(R4C1 1:R4C12=R4C))

М5 тянете за уголок вниз
N5 за уголок вниз, потом отмечаете ячейки колонки N и за уголок тянете вправо, до декабря.

[8bplol]

Большое спасибо! Оба варианта решают поставленную задачу.

Однако способ IgorGO более универсален, так как позволяет учитывать такие варианты, как когда и аванс (К), и расчет (L) меньше даты в М3 или больше даты в AI3 (чего в примере не было, но позже оказалось необходимым). При этом если в таблицу добавить нечетное число новых столбцов, то этот вариант перестает работать, если добавить четное число столбцов, то все нормально работает.

Вариант AlexM12 считает верно только когда аванс (К) меньше даты в МЗ, или только когда расчет (L) больше или равен дате в AI3 (строго по приведенному мной примеру). Добавление столбцов в таблицу не влияет на расчеты.

Но, повторюсь, в рамках приложенной мной таблицы оба варианта позволяют решить задачу.

[AlexM12]

8bplol, в ячейке K6 дата больше чем в М3, а в L6 меньше даты в AI3 и расчет в этой строке правильный.
Покажите пример неправильной работы формул в файле.

[8bplol]

AlexM12, в приведенном мной файле расчет Вашей формулы правильный. Изначально я описал задачу не совсем точно.
С датой в ячейке МЗ должна сравниваться дата как из столбца K так и из столбца L. Если даты в них меньше, чем в МЗ, то в М5 результат должен суммироваться, так как и аванс и расчет оплачиваются до 01.03.2017. Соответственно с датой в AI3 должна сравниваться дата не только из L, но и из K, так как оба платежа могут произойти после 01.01.2018.
В приведенном мной файле таких условий нет, но они, как оказалось, не исключены.

[AlexM12]

поправил формулу для столбца М и AI

=ЕСЛИ(И(K5<M$3;L5<M$3);E5;ЕСЛИ(K5<M$3;E5*I5;""))
=ЕСЛИ(И(K5>=AI$3;L5>=AI$3);E5;ЕСЛИ(L5>=AI$3;E5*J5;""))

Добавил условия для проверки работы формул.

[8bplol]

AlexM12, спасибо. Теперь всё работает как надо.

[AlexM12]

Вариант формул покороче

=ЕСЛИ(K5<M$3;E5*I5^(L5>=M$3);"")
=ЕСЛИ(L5>=AI$3;E5*J5^(K5<AI$3);"")