Распределить количество факта на плановые значения

[leha218]

Добрый день!
Имеется 2 файла: "файл1" с договорами Файл1 договоры.xlsx  и "файл2" с планом Файл2 план.xlsx .
В договорах позиции указаны 1 строкой без повторов в одном договоре (но Одна и та же позиция может быть в разных договорах)
В плане позиции указаны несколькими строками по одному договору.
Необходимо распределить количество факта в договорах на количество по плану в соответствии с договорами.
Вариант 1:
Условия:
если количество факта превышает значения плана - проставить значение плана (и так же далее по остальным строкам). Если количество факта меньше, чем план - поставить количество факта, (далее в остальных строках поставить 0).
если в последней строке количество факта все равно превышает значение плана - поставить значение факта.
То есть распределяется все количество факта в соответствии с планом, излишки - в последнюю строку (как воду из ведра разливаем по кастрюлям).
Вариант 2: (на мой взгляд более сложный)
Условия:
если общее количество факта превышает общее значение плана - распределить значение факта пропорционально значениям плана. если общее количество факта меньше общего значения плана - распределить значение факта как в 1 варианте (построчно с остатком в последней строке).

Желательно формула, но можно и макрос. Дополнительные столбцы допускаются, строки не желательны.

Есть нюанс: договоры в файле2 с планом могут идти не по порядку. если при этом не возможно будет распределить, будем пользоваться сортировкой. 

[Serge 007]

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

Наверное, можно оптимизировать, но если "в лоб" - то так:

Код Выделить Развернуть

=ЕСЛИ(ВПР(B2;Факт!B$1:D$8;3;)>СУММЕСЛИ(B$1:B$38;B2;E$1:E$38);(СЧЁТЕСЛИ(B$1:B$38;B2)<>СЧЁТЕСЛИ(B$1:B2;B2))*E2+(СЧЁТЕСЛИ(B$1:B$38;B2)=СЧЁТЕСЛИ(B$1:B2;B2))*(ВПР(B2;Факт!B$1:D$8;3;)-СУММЕСЛИ(B$1:B1;B1;E$1:E1));(СЧЁТЕСЛИ(B$1:B$38;B2)=СЧЁТЕСЛИ(B$1:B2;B2))*ВПР(B2;Факт!B$1:D$8;3;))

[leha218]

Добрый день. Спасибо. Но не учитывает номер договора, а распределяет первое найденное значение.

[Serge 007]

В формуле замените все

Код Выделить Развернуть

ВПР(B2;Факт!B$1:D$8;3;) на

Код Выделить Развернуть

ВПР(D2;Факт!C$1:D$8;2;)

[N_Dvorets]

Добрый день.
Вариант распределения макросом (проект).

[Serge 007]

Никита, ссылки на сторонние ресурсы недопустимы