Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Реализация модели выбора., Как можно реализовать сложную и большую модель выбора рецептур на предприятии?
 
#1
02.06.2017 10:53:49
Доброго дня!
Я работаю экономистом-аналитиком в молочной отрасли. Брали меня с главной целью - создать модель выбора рецептур продуктов. Остальные задачи второстепенны. Сложность в том, что на заводе 100 продуктов и на каждый от 1 до 5 рецептур. Ограничения - ресурсы для производства(основных 5, ост. второстепенные). Вообщем, после танцев с бубнами и формулами массива создал модель не учитывающую ограничения, т.е. самые выгодные рецептуры. Тут просто и понятно. Чтобы прикрутить ограничения, начал юзать Поиск решения. Оказалось, что вычислительных мощностей не хватает. Или выбирает первые попавшиеся или выводит ошибку или считает больше ночи. Установил пробную версию Solder Analytic 2017, но она так же сильно обрезанная и вся на английском, а платная версия стоит КРАЙНЕ ДОРОГО!
На фоне всего этого возникает вопрос. Как можно реализовать данную модель?
Ещё раз. Выбор рецептур для минимизации себестоимости за месяц и выполнении плана производства, при ограничениях на сырье.
Файл прикрепить не могу, много конфиденциальной информации.

Модераторов прошу переместить в раздел "Работа Ексель", ибо спрашиваю совета и реализовать хочу своими силами.
Изменено: Doomer Prosk - 02.06.2017 11:00:13 (прошу переместить в раздел "Работа Ексель")
 
 
 
#2
02.06.2017 11:07:05
Цитата
переместить в раздел "Работа Ексель", ибо спрашиваю совета и реализовать хочу своими силами.
Вы неправильно понимаете. Работа - раздел платных заказов.
Или в Работе на платной основе, или своими силами, отдельными непонятными вопросами, в общей ветке.
 
 
 
#3
02.06.2017 11:07:28
Да простят меня форумчане и автор темы в частности, за вопрос. А можно накидать файл-пример (с реальной структурой таблиц) на пару рецептов выдуманных продуктов и также выдуманного сырья, чтобы понимать всю сложность работы, на которую, на данный момент, не собираюсь претендовать, но интересно узнать - осилю/нет. Спасибо.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#4
02.06.2017 11:08:03
Вы хотите спросить совета и заплатить за сам совет?
 
 
 
#5
02.06.2017 11:29:06
vikttur, Ой, простите, опечатался(пару дней в голове перегруз оперативки, боюсь настанет синий экран))). Переместите в раздел Вопросы по Эксель. Думаю, там теме будет место.
 
 
 
#6
02.06.2017 11:29:35
JayBhagavan, Дайте мне часок-другой, выложу.
 
 
 
#7
02.06.2017 11:42:39
Непонятен такой момент. Вы собираетесь реализовать эту модель с помощью формул и надстроек или все-таки макросы VBA?
Никому не отвечай, кoгда ты зол, ничего не обeщай, когда ты счастлив, никогда нe решай, когда ты грустeн.
 
 
 
#8
02.06.2017 12:11:46
Цитата
vikttur написал:  или своими силами, отдельными непонятными вопросами, в общей ветке.
Комплексно решать здесь не получится.
 
 
 
#9
02.06.2017 12:34:18
Вить, я что-то не понял - и в раздел Работа перенесли и тут такая тема. Это дубль, или глюк? :)
Я сам - дурнее всякого примера! ...
 
 
 
#10
02.06.2017 12:42:19
Joiner, Формулами это сделать просто невозможно. Либо ВБА(будет наверное слишком сложный код), либо Поиск Решений(слишком много данных, думает более 10 часов для нахождения оптимального варианта), либо иной метод.
 
 
 
#11
02.06.2017 12:42:47
kuklp, По моей просьбе тема перенесена.
 
 
 
#12
02.06.2017 14:39:49
JayBhagavan, Удалил или перемешал все конфиденциальные данные. Пришлось сократить, в моей рабочей версии 99 строк заказов. Смотреть вкладку "Ручной выбор рецептуры"(Поиск Решения в ячейках Н5:Н99). И вкладка "Ручной выбор рецептуры (2)"(Поиск решения в ячейках Х5:АС99). Минимизировать необходимо значение в ячейки Е151. При ограничениях в 157 строке. Сейчас файл наверное правильно работать не будет, т.к. часть необходимых данных была изменена, но по сути, должно быть понятно. https://yadi.sk/i/KAZXNeUb3Jm8fy
 
 
 
#13
02.06.2017 17:27:27
Doomer Prosk, раздел для КОНКРЕТНЫХ ВОПРОСОВ. Что именно не получается? Вам только совет нужен - в какую сторону двигаться?
 
 
 
#14
03.06.2017 20:58:22
Бегло просмотрел файл. Насколько я понял, в модели Вы отталкиваетесь от того, что известно сколько продукции каждого вида будет продано в некий последующий период времени. Также известны входящие остатки продукции каждого вида на складе для этого периода. Соответственно можно определить количество продукции каждого вида, которое должно быть произведено в данном периоде. Т.е. доходная часть у нас известна и не требует моделирования, а затратную часть нужно минимизировать, чтобы максимизировать итоговую прибыль. Необходимо с помощью моделирования перебрать все комбинации рецептур, чтобы в итого найти комбинацию, дающую наименьшие затраты.

Возникает первый вопрос. Количество видов продукции на заводе - 100. На каждый продукт – от 1 до 5 рецептур (допустим, возьмём в среднем - 3).  Т.е. всего комбинаций 3^100. Ограничение на ресурсы пока не будем брать. Как Вы определяете оптимальную из 3^100 комбинаций рецептур без учёта ограничений на ресурсы?
 
 
 
#15
05.06.2017 08:16:16
vikttur,Именно! Каким способом можно реализовать данную задачу?
 
 
 
#16
05.06.2017 08:23:46
Цитата
Equio написал:
Необходимо с помощью моделирования перебрать все комбинации рецептур, чтобы в итого найти комбинацию, дающую наименьшие затраты.
ДА!
Цитата
Equio написал:
Количество видов продукции на заводе - 100. На каждый продукт – от 1 до 5 рецептур (допустим, возьмём в среднем - 3).  Т.е. всего комбинаций 3^100.
Так и есть!
Цитата
Equio написал:
Как Вы определяете оптимальную из 3^100 комбинаций рецептур без учёта ограничений на ресурсы?
Здесь я с помощью формул массива определяю самую выгодную по каждому виду продукции отдельно! Это оказалось не сложно. Вкладка "Оптимальные спецификации" Столбец Е.

А вот определить рецептуры на каждый товар в массиве с учетом ограничений, формулами просто не возможно. Только перебор. Или особые надстройки, помогающие высчитать(такие как Solder Analytic).

Так вот и вопрос как можно сделать этот перебор? Вариантов 100^3.
 
 
 
#17
05.06.2017 15:56:39
Перебирать 3^100 комбинаций - разумеется не вариант. Тут 10ю часами не отделаешься.

Вопрос номер два. Можно ли сейчас среди производимых ста товаров выделить допустим штук десять «лидеров» продаж, которые делают основной вклад в выруку и в расход ресурсов? (Понятно, что ситуация может меняться, но я говорю про сейчас и про несколько предыдущих периодов.)

Вопрос номер три. Когда Вы нашли оптимальную комбинацию без учёта ограничения на ресурсы, на сколько потребление ресурсов выходит за границы допустимого диапазона (на единицы процентов от пограничного значения, на десятки процентов, на сотни процентов, на тысячи процентов)? И по какому кол-ву видов ресурсов происходит выход за границы? (Опять же понятно, что ситуация может меняться, но интересует, что сейчас и в нескольких предыдущих периодах).
Изменено: Equio - 05.06.2017 18:15:14
 
 
 
#18
06.06.2017 08:35:28
Equio,
2. Выделить не получится и нет смысла. Идея и состоит в том, чтобы полностью просчитать и показать, сколько мы можем сэкономить для предприятия. А 10 позиций технологи на заводе вручную считают.
3. Выходит за границы процентов на 10. Просто допустим по некоторым позициям по технологии можно заменять сливки и молоко маслом или смесью жиров
Всё к чему вы ведете. я и сам понимаю. Мне как раз и нужен инструмент дающий возможность найти оптимальный вариант. А сейчас ещё одну группу товаров занес в таблицы. и наименований стало 139(((. Че теперь делать, вообще непонятно. Поиск Решения вообще отказывается запускаться.
 
 
 
#19
06.06.2017 16:54:24
НУ что? Неужели у меня такая уникальная задача, что никто не может подсказать в каком направлении мне двигаться???
 
 
 
#20
06.06.2017 17:06:55
Doomer Prosk, Вам дали направление на подумать, или Вы надеялись что будет пример в который нужно только подставить цифры и вуаля?
Если нужно перебрать все комбинации рецептур - перебирайте, какой тут еще совет нужен?
Загнали данные в массив и пошел перебор в цикле.
Ну а дальше с полученного ....

Правда скорее всего Excel 3^100 не вытащит...
Изменено: Ivan.kh - 06.06.2017 17:16:09
 
 
 
#21
06.06.2017 17:32:06

Да ну какой "Поиск Решения", ну что Вы.   :)

Концептуально вижу такое решение задачи. Отталкиваясь от определённой оптимальной комбинации рецептур без учёта ограничения на ресурсы, менять в ней рецептуры на требующие меньшего кол-ва тех ресурсов, в которых происходит выход за допустимые границы.

 
 
 
#22
06.06.2017 22:00:38
Добрый вечер, я так понимаю есть "дорогие" рецептуры и есть "дешевые" с заменителями, это обычная практика. Раскрутите рецептуры нормативные (дорогие) на количество готового продукта  - покажите себестоимость какая могла бы быть по ГОСТу и раскрутите самые дешевые рецептуры на то же количество и сравните эти две суммы в деньгах - покажите экономию)
Не знаю, конечно, ситуацию на Вашем заводе, почему не определиться что, к примеру, эту позицию мы делаем с "хорошего", а другие с дешевого и закупать составляющие согласно планов на производство?  
 
 
 
#23
07.06.2017 08:57:07
Equio,Это логичный вариант)) Спасибо!!! Хотя всей задачи не решает. В любом случае буду вылизывать модель и просить передать её для пользования в ПДО, пусть смотрят.

Дмитрий Тарковский, Не-не. Вы не совсем поняли))) Поясню просто так. По тому же ГОСТу допускается производство из Масла/сливок/молока. А цены на эти ресурсы постоянно меняются. Например, летом всегда молока много и оно дешевле, соответственно масло становится использовать не выгодно, а зимой наоборот. А по сливкам, цены гуляют относительно молока. Или санкции какие введут, и не будет у нас молока белорусского, дешевого.

Ivan.kh, Скорее 100^3. К сожалению, для моего уровня это пока сложновато. Буду думать, учиться, надеяться)) Спасибо!
 
 
 
#24
07.06.2017 09:12:23
Я просто хочу разобраться самому и помочь Вам) Если сделать у рецептур признак "молочные", "жир", и тд, учитывая сезонности, и потом, анализируя разницу по ценам на основные составляющие, применять те или иные рецептуры, например, если молоко дешевле жира - используем рецептуры типа 1 "молочные". Это как вариант, нужно что то брать за основу, уменьшая количество исходных данных, а потом работать по ограничениям.
 
 
 
#25
07.06.2017 10:07:24
Дмитрий Тарковский, Вручную можно добиться хорошего результата, но не идеального. А комп помог бы просмотреть абсолютно все варианты и найти идеальный. Разница между хорошим и идеальным будет составлять от 10 до 300 т.р. экономии в месяц.
 
 
 
#26
07.06.2017 11:43:53
А почему вручную? делать будете в Excel, правильно? я так понимаю есть лист с ценами на основные составляющие, вот макрос сравнивает эти 5 цен на продукцию, соответственно при использовании рецептур брать тип продукции, где цена минимальна.
Абстрагируйтесь от поиска решения (3^100 это нереально для таких, можно сказать, бытовых расчетов), посмотрите на проблему со стороны и, может быть, мою идею можно изменить, усовершенствовать но основа имеет рациональное зерно?
 
 
 
#27
07.06.2017 12:21:29
Дмитрий Тарковский, Так без учета ограничений уже сделал. А вот подобрать рецептуры так, чтоб максимально приблизиться в расходе дешевого сырья к фактически завезенному на завод..... Вот тут задача. А что не удалось по самому дешевому, сделать из чуть более дорого,а уж что совсем не вместилось, произвести из драгоценного сырья))) И надо ещё учесть, что на разную продукцию, свои нормы. Где-то выгоднее сделать из кусочка драгоценного, чем из вагона дешевого!
 
 
 
#28
07.06.2017 15:11:07
Цитата
Doomer Prosk написал:
Это логичный вариант)) Спасибо!!! Хотя всей задачи не решает.
Если под задачей понимать нахождение именно той самой единственной комбинации, которая даёт наименьшие затраты, то да, не решает. Точнее иногда он будет находить именно эту комбинацию, но далеко не во всех случаях. Но в любом случае, при правильной реализации предложенного мной варианта отклонения в затратах от оптимальной комбинации будут минимальны.
 
 
 
#29
07.06.2017 15:15:31
Equio, Минимальны..... но при учете, что затраты в месяц под сотню лямов...)) Даже пол процента это пол ляма!
 
 
 
#30
07.06.2017 16:52:47
Да, впечатляет. Но видите ли какая штука. 3^100 - это число с 47 нулями. Я сейчас запустил цикл в VBA, количество итераций которого равно числу "всего" с 10ю нулями, внутри цикла было одно арифметическое действие. Компьютер у меня перебирал этот цикл около 5 минут. Можно умножить 5 минут на число в котором 47-10=37 нулей, а можно и не умножать, и так всё понятно с временными затратами. Т.е. учитывая общее количество комбинаций мне видится, что акцент всё-таки придётся сместить с поиска точного решения на поиск решения, дающего минимальную погрешность.
 
 
 
Страницы: 1 2 След.
Наверх