Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: Олег Олег от 02.11.2018, 21:53

Название: Распределение по складам остатков в зависимости от потребности склада
Отправлено: Олег Олег от 02.11.2018, 21:53
Доброго вечера.Прошу подсказать/помочь/направить в решении такой задачи:
Необходимо распределить остаток вещей по складам, в зависимости от потребности склада.
Т.е. столбец Y (в наличии) необходимо распределить по столбцам "распределено" в зависимости от потребности столбец "положено".
Условия:
1.Если сумма всех столбцов "положено" равна столбцу "наличие",тогда распределение должно происходить "положено" равно "распределено".
2.Если сумма всех столбцов "положено" не равна столбцу "наличие",тогда распределение должно происходить путем вычисления среднего значения столбцов "положено" и после этого столбец "распределено" не должен быть равен нулю или быть больше столбца "положено" для каждого склада.Так же после распределения среднего значения сумма столбцов "распределено" не должна быть больше столбца "в наличии".
С помощью макросов или формул подскажите как правильно? Количество складов, потребность и остаток по наличию может меняться.
Эксель 2007.
Уже задавал вопрос на другом форуме,были предложены варианты решения,однако в ходе работы возникли дополнительные требования со стороны руководства и мне на другом форуме перестали отвечать люди.
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=110518&MID=917509#message917509 - мой вопрос на другом форуме.
Заранее благодарю за помощь.
Название: Re: Распределение по складам остатков в зависимости от потребности склада
Отправлено: Ярослав Николаев от 05.11.2018, 13:45
А чем вас не устроил вариант формулы, предложенный на другом форуме? Я сейчас попробовал, у меня получилось практически так же с минимальными изменениями. Вот основная формула:
=ЕСЛИ(СУММЕСЛИ($C$2:$X$2;"положено";C3:X3)<=Y3;C3;ОКРУГЛ(Y3*C3/СУММЕСЛИ($C$2:$X$2;"положено";C3:X3);0))

Кусок =ЕСЛИ(СУММЕСЛИ($C$2:$X$2;"положено";C3:X3)<=Y3;C3; 
- обратите внимание, что $ лучше оставить только у диапазона, где будет искаться слово "положено", в то время как диапазон суммирования C3:X3 и сумму Y3 лучше оставить без доллара, чтобы удобно было протягивать формулу по ячейкам

по сути говорит, что если в наличии (Y3) у нас больше товара, чем это требуется складам, то можно полностью заполнить каждый склад его собственным значением "положено". Это удовлетворяет вашему 1 пункту, а условие <=Y3 (делаю акцент на МЕНЬШЕ) нужно для удовлетворения куску 2ого пункта, где вы говорите, что "столбец "распределено" не должен ... быть больше столбца "положено" для каждого склада".

Если же в наличии товара недостаточно для заполнения всех складов, применяется следующий кусок формулы
ОКРУГЛ(Y3*C3/СУММЕСЛИ($C$2:$X$2;"положено";C3:X3)

Вы в описании предлагали использовать нечто "среднее по столбцу "положено"". Я об этом в заключении напишу, а пока подумайте, разве предлагаемый вариант хуже? Вместо среднего мы предлагаем использовать долю удельного веса для каждого склада.
Смотрите, вот у вас в наличии есть меньше товара, чем требуется, значит все склады заполнить не получится. Как быть? Заполнить парочку, сделав остальные полупустыми или равномерно заполнить каждый? В нашем варианте как раз каждый заполняется равномерно относительно своей доли в "общей массе".
Представьте, что у вас в наличии 10000 штук, а чтобы заполнить все склады, нужно 20000. Складу 1 положено 500, а складу 2 8000. Удельный вес говорит, что 500 от требуемых 20000 составляет 2,5%, поэтому из имеющихся в наличии 10000 правильно этому складу выделить 2,5%, т.е. 250. А если брать второй склад, то его 8000 от требуемых 20000 составляет целых 40%, поэтому разумно будет 40% от имеющихся 10000, т.е. 4000, направить именно туда. В итоге каждый склад получит кол-во штук в зависимости от своего размера/значимости на всём предприятии. Мне кажется это куда лучше, чем брать какое-то среднее.

По остальным пожеланиям:
- "столбец "распределено" не должен быть равен нулю" - он в принципе не может быть равен нулю. Он может быть равен нулю только в том случае, если "положено" будет равно нулю.
- "Так же после распределения среднего значения сумма столбцов "распределено" не должна быть больше столбца "в наличии"" - для этого колонка "распределено" в последнем складе работает чуть по-другому для случаев, если требуется больше, чем есть в наличии: вместо простого высчитывания она из общего "наличия" вычитает сумму, которую уже распределили по предыдущим складам.
p.s. Только вам нужно в столбцах "положено" использовать целые значения, а дробные, как сейчас, иначе из-за округления может потеряться или оказаться лишней 1 штука.

По-моему все пожелания, высказанные в данной теме учтены.

Что же касается темы на другом форуме, то вы там описывали алгоритм ручного расчета с использованием среднего, и его описание отличается от того, что вы расписали тут. Если пробовать реализовать тот, то у меня не получилось. Основная загвоздка в том, что вы на первой итерации берете среднее по всем значениям склада, заполняете значения в ячейках, а на второй итерации высчитываете уже новое среднее с учетом заполненных значений по первому шагу. Мне кажется формулами тут не обойтись: либо надо писать какой-то хитрый макрос, либо правее таблицы городить еще кучу вспомогательных таблиц, в каждую из которых вбивать свою логику (типа в первой считаем среднее по основной таблице и заполняем значения, вторая таблица уже будет ссылаться на первую, где будет выведен новый диапазон данных и т.д.). Но это сильно затруднит исправления формул, когда у вас будет меняться кол-во складов.
Название: Re: Распределение по складам остатков в зависимости от потребности склада
Отправлено: Олег Олег от 07.11.2018, 22:48
Доброй ночи Ярослав. Спасибо большое за столь развернутое пояснение,я действительно не смотрел на решение этой задачи по заполнению остатков в процентном соотношении от потребности. По поводу распределения имущества по складам путем нахождения среднего значения для складов я просто описал тот способ, которым пользовался я при расчете вручную на работе, поэтому и привел его в пример. Я сам достаточно долго пытался реализовать такой метод путем формул или написания макроса, но так и не смог, скорее всего из-за недостаточно большого опыта при работе с функционалом Excel.
В любом случае считаю, что этот метод расчета (распределения) будет самым оптимальным и правильным. Еще раз спасибо.