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

Автор leha218, 13.10.2023, 16:05

« назад - далее »

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;))
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

leha218

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

Serge 007

В формуле замените всеВПР(B2;Факт!B$1:D$8;3;) на ВПР(D2;Факт!C$1:D$8;2;)
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

N_Dvorets

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

Serge 007

Никита, ссылки на сторонние ресурсы недопустимы
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390