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

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Автор Тема: Распределение по складам остатков в зависимости от потребности склада  (Прочитано 118 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Олег Олег

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 2

Доброго вечера.Прошу подсказать/помочь/направить в решении такой задачи:
Необходимо распределить остаток вещей по складам, в зависимости от потребности склада.
Т.е. столбец Y (в наличии) необходимо распределить по столбцам "распределено" в зависимости от потребности столбец "положено".
Условия:
1.Если сумма всех столбцов "положено" равна столбцу "наличие",тогда распределение должно происходить "положено" равно "распределено".
2.Если сумма всех столбцов "положено" не равна столбцу "наличие",тогда распределение должно происходить путем вычисления среднего значения столбцов "положено" и после этого столбец "распределено" не должен быть равен нулю или быть больше столбца "положено" для каждого склада.Так же после распределения среднего значения сумма столбцов "распределено" не должна быть больше столбца "в наличии".
С помощью макросов или формул подскажите как правильно? Количество складов, потребность и остаток по наличию может меняться.
Эксель 2007.
Уже задавал вопрос на другом форуме,были предложены варианты решения,однако в ходе работы возникли дополнительные требования со стороны руководства и мне на другом форуме перестали отвечать люди.
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=110518&MID=917509#message917509 - мой вопрос на другом форуме.
Заранее благодарю за помощь.
Записан

Ярослав Николаев

  • Новичок
  • *
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 5

А чем вас не устроил вариант формулы, предложенный на другом форуме? Я сейчас попробовал, у меня получилось практически так же с минимальными изменениями. Вот основная формула:
=ЕСЛИ(СУММЕСЛИ($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 штука.

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

Что же касается темы на другом форуме, то вы там описывали алгоритм ручного расчета с использованием среднего, и его описание отличается от того, что вы расписали тут. Если пробовать реализовать тот, то у меня не получилось. Основная загвоздка в том, что вы на первой итерации берете среднее по всем значениям склада, заполняете значения в ячейках, а на второй итерации высчитываете уже новое среднее с учетом заполненных значений по первому шагу. Мне кажется формулами тут не обойтись: либо надо писать какой-то хитрый макрос, либо правее таблицы городить еще кучу вспомогательных таблиц, в каждую из которых вбивать свою логику (типа в первой считаем среднее по основной таблице и заполняем значения, вторая таблица уже будет ссылаться на первую, где будет выведен новый диапазон данных и т.д.). Но это сильно затруднит исправления формул, когда у вас будет меняться кол-во складов.
Записан

Олег Олег

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 2

Доброй ночи Ярослав. Спасибо большое за столь развернутое пояснение,я действительно не смотрел на решение этой задачи по заполнению остатков в процентном соотношении от потребности. По поводу распределения имущества по складам путем нахождения среднего значения для складов я просто описал тот способ, которым пользовался я при расчете вручную на работе, поэтому и привел его в пример. Я сам достаточно долго пытался реализовать такой метод путем формул или написания макроса, но так и не смог, скорее всего из-за недостаточно большого опыта при работе с функционалом Excel.
В любом случае считаю, что этот метод расчета (распределения) будет самым оптимальным и правильным. Еще раз спасибо.
Записан
 



Темы без ответов

30.09.2018 10:24 Расчет процентов за определенный период (месяц) с учетом изменений и платежей 380
22.05.2018 11:38 Скрипт написать который допишет данные в файл 1041
03.03.2018 00:00 Подсчет отработанного времени, за исключением заранее определенных перерывов 1176
14.02.2018 10:11 Подготовить читабельную отчетность по платежам 1168
23.01.2018 13:46 Найти вероятность повторной покупки 1061
12.01.2018 23:56 Сделать отчет на Power BI (Dashboard) 1496
06.09.2017 10:43 Solver VBA не решает гиперболическое уравнение, но при этом решает гармоническое 1357
17.08.2017 12:15 Гиперссылка и фильтр одновременно макрос 1703
23.05.2017 11:20 Копирование данных из одной таблицы в умную таблицу по условию 3440
15.03.2017 15:45 автозамена картинок PowerPoint 1954





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.144 секунд. Запросов: 109.