Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Распределитель товара по фурам с условиями
 
#1
16.01.2023 06:14:35
Добрый день, друзья. Прошу помочь с написанием распределителя товаров по фургонам при помощи кода VBA. Казалось бы задача простая, но имеются условия, которые ее значительно осложняют.
Пример задачи следующий: на складе имеются 3 вида товаров: картошка, морковь и огурцы с весами 50 кг., 100 кг. и 150 кг. Их необходимо разгрузить по 3-ем фурам с желаемой загруженностью в 100 кг., 200 кг. и 300 кг.  Причем картошку нельзя грузить на 3юю фуру. Казалось можно было просто товары просто равномерно распределить по 3-ем фурам (1/6 всех товаров в 1ую фуру, 2/6 всех товаров во 2ую фуру и 3/6 всех товаров в 3юю фуру) однако имеется ограничение по картошке, из-за чего в 3юю фуру картошка не будет грузиться, из-за ее загруженность будет на 25 кг. (3/6 картошки) меньше предполагаемого уровня, а в 1ые 2 фуры будет загружено больше на 8,3 кг. и 16,67 кг. картошки соответственно. Однако, теперь необходимо, чтобы эти недостающие 25 кг. в 3ей фуре были компенсированы за счет других товаров. Цель - достигнуть максимально равномерного уровня загруженности во всех 3-ех фурах (загруженность = вес всех товаров в фуре / желаемый уровень загрузки). Кстати, почему пишу желаемый уровень загрузки - потому что в фуры можно грузить сколько угодно товаров если в другие фуры больше нельзя (например если бы в нашей задачи стояло условие - что во 2ую и 3юю фуру можно грузить только картошку, то все огурцы и морковь пришлось бы грузить в 1ую фуру, что явно выше 100 кг.).
Сразу замечу, что количество товаров, их веса, количество фур и их желаемые уровни загрузки могут постоянно меняться, ровно как и ограничения по загрузке. Попытался сделать через solver, вкладываю документ. Он почему-то в 1 момент в целом начал путаться в арифметике, ну и ограничение в 200 переменных сильно мешает.
Друзья, посмотрите пожалуйста, может чем помочь сможете
Спасибо!
 
 
 
#2
16.01.2023 07:38:55
Thomas Cook,
не совсем понимаю расчет "Желаемой загрузки"?
 
 
 
#3
16.01.2023 08:01:11
Если бы я написал вместимость фуры, то это бы означало, например 1ой фуры в 100 кг., то сложилось бы впечатление, что 1ую фуру можно максимум до 100 кг. загружать. По факту у фуры отсутствует максимальная вместимость, она «бесконечна». Желаемая загрузка скорее нужна чтобы понять какую долю товаров нужно распределить именно в 1ую фуру, то есть желаемая загрузка 1ой фуры в 100 кг. означает, что в 1ую фуру нужно по возможности 1/6 всех товаров распределить. Почему я пишу именно желаемая? Представьте ситуацию, когда во 2ую и 3юю фуры можно грузить только картошку, т.е. распределить 50кг. между ними. В таком случае объем во 2ой фуре составит 20кг. картошки, а в 3ей 30 кг. (Исходя из логики, что общая вместимость 2ой и 3ей фур 500 кг., то желаемая вместимость 2ой фуры 200 кг. или 40%, а 3ей 300 кг. или 60%). В таком случае морковь и огурцы с общим весом в 250 кг. уйдут в 1ую фуру.
То есть прошу не зацикливаться на том, чтобы вес товар в 1ой фуре превысит ее вместимость, если других вариантов расклада нет, то можно и превысить. В задаче, же которая была указана изначально картошку нельзя грузить в 3юю фуру, соответственно она получит на 25 кг. меньше товара, чем могла бы получить если бы туда загрузили картошку. Соответственно 1ые 2 фуры получат на 25кг. больше товаров, но теперь в них нужно загрузить меньше огурцов и моркови, чтобы эти 25 кг. перераспределились в 3юю фуру и совокупный уровень загруженности каждой фуры составлял 50%.
 
 
 
#4
16.01.2023 11:01:33
Thomas Cook,
у меня получается добиться результаты, но не с первого поиска решения, а раза так с 4-го:
 
 
 
#5
16.01.2023 11:21:35
Да, поэтому «Поиск решения» не подходит :( может знаете метод, чтобы он сразу нужный результат давал? Через VBA допустим
 
 
 
#6
16.01.2023 12:57:33
Thomas Cook,
из глупых вариантов, можно раз 10 запускать Поиск решения через VBA чтобы сразу получить ответ
Изменено: evgeniygeo - 16.01.2023 13:42:32
 
 
 
#7
17.01.2023 10:20:34
Посмотрите такое решение:
Сводим задачу к линейной модели, оптимизируем к минимуму процент заполняемости фур.
Если в MS Solver упираемся в ограничение в 200 изменяемых ячеек, то используем для решения OpenSolver
 
 
 
#8
23.01.2023 13:46:04
Вот здесь через Поиск решения все с первого раза работает.
В качестве целевой функции использовал сумму отношений квадрата отклонения загрузки от грузоподъемности по всем видам овощей (квадрат - чтобы убрать проблему ухода в минус при минимизации отклонения, нам нужно стремиться к нулю).
 
 
 
#9
23.01.2023 14:00:52
Дубль темы в платном разделе. Где Где оставляем?
 
 
 
#10
24.01.2023 12:22:16

Идея для решения макросом

Для частного случая можно перебор вариантов через вложенные циклы. Там 9 переменных – количество видов овощей умножить на количество фур. Но из них 4 переменных производные от других. Т.е. морковь в третьей фуре – морковь всего минус морковь в первых двух фурах. Огурцы аналогично. Картошка в третье фуре равна нулю. Значит, остается картошка в первой фуре, а во второй = картошка всего минус картошка в третьей фуре.

Итого получается 5 вложенных циклов. Для каждой переменной перебираем все варианты других переменных. И проверяем целевую функцию. Где она максимальная, запоминаем.

 
 
 
#11
24.01.2023 12:22:42

Но для случая, когда количество переменных может быть любое, этот вариант не подходит. Так как количество циклов может быть любым и мы не можем их прямо в макросе написать. Там, как я подумал и пришел к выводу, надо использовать рекурсивную функцию, т.е. которая ссылается сама на себя. Количество переменных – например, N. Для каждой переменной свои ограничения сверху и снижу. Запускаем функцию сначала с переданной ей переменной x=1. Т.е. это первая из N переменных. Внутри функции запускаем цикл по значениям этой переменной от минимальной границы до максимальной. А внутри каждого витка цикла запускаем эту же функцию, но с переданной ей переменной уже x+1. И т.д.

То есть, получается для каждого значения переменной x1 у нас есть дерево, где данному значению x1 соответствует множество значений x2, каждому из этих значений x2 в свою очередь множество значений x3 и т.д. Рекурсивные функции как раз используются, чтобы пробежать все это дерево до каждой последней ветки, из которой больше других веток нет. Эти пути от вершины до каждой последней ветки – и есть все возможные комбинации наших переменных. Соответственно, для каждой такой комбинации надо высчитать проверку на условия и значение целевой функции, а потом выбрать наилучшую. Я так вижу возможность. Но это реально сложный расчет. И еще вопрос быстродействия. Если число возможных вариантов будет исчисляться сотнями тысяч, то не очень хорошо получится.
 
 
 
#12
24.01.2023 12:26:33
Это общая идея. Для того, чтобы не перебирать ненужные варианты, лучше считать не один одномерный массив из N переменных, а как-то попытаться использовать двумерный массив - вид овощей/номер фуры. Чтобы если по столбцу, например, моркови, не получается нужного общего веса, то огурцы и картошку по фурам не делить, а сразу пресекать эту ветку.
Но лучше, конечно, использовать Поиск решения, чем заново его писать на vba :)
 
 
 
Страницы: 1
Читают тему
Наверх