Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Solver и линейная модель
 
#1
02.02.2012 11:04:32
Доброе утро!  
 
Прошу помочь с подбором оптимальных значений.  
 
Например, у меня есть 4-е товара, по которым известны:  
- фактический объем поставки (C5:G5),  
- фактическая доля поставки от общего кол-ва поставок (C6:G6),  
- плановое значение доли поставки от общего кол-ва поставок (C8:G8).  
 
 
Цель: найти оптимальное кол-во каждого товара (C11:F11), к-ое необходимо грузить (поставить), чтобы выйти на плановый уровень по долям (C8:G8) с учетом фактических значений поставок.  
 
 
Если бы задача стояла подобрать оптимальное кол-во поставок каждого товара в отдельности, то я бы мог воспользоваться инструментом "Подбор параметра".  
 
Но в моем случае необходимо подобрать оптимальное значение каждого товара, которое необходимо грузить, с учетом веса их плановых долей от общего числа поставок. Т.е.:  
- оптимальная доля поставок,% (C12:F12) >= плановой доли поставок, % (C8:F8)  
- оптимальное кол-во поставок, шт. (C11:F11) >= фактический объем поставки (C5:G5)  
- оптимальное кол-во поставок, шт. (C11:F11) = целое  
- оптимальное кол-во поставок, шт. = неотрицательное значение  
 
Методом проб и ошибок, в некоторых случаях у меня получилось высчитать необходимое оптимальное кол-во поставок. Но такого результата удалось достичь нелинейным методом, к-ый конечный результат возвращает не всегда (в зависимости от факта поставок и плановых долей).  
 
Возможно ли реализовать подбор оптимального кол-ва поставок линейным методом?  
Или при указанных входных данных и минимальных ограничениях линейную модель не построить?  
 
 
Возможно, я не вижу чего-то главного здесь или упускаю какие-то взаимосвязи при построении линейной модели. В любом случае, буду признателен за любую помощь.
 
 
 
#2
02.02.2012 11:18:29
Оптимальность подразумевает -оптимальное кол-во чего либо т.е. какой-то из параметров должен быть МИН или МАКС - вопрос какой именно.  
2. Вы можете написать уравнение которое хотите решить?
 
 
 
#3
02.02.2012 11:37:37
1. Насколько я смог уловить взаимосвязь, оптимальным (точнее минимальным в данном случае) должен быть параметр G11, а именно СУММ(C11:F11)  
 
При условии:  
- оптимальная доля поставок,% (C12:F12) >= плановой доли поставок, % (C8:F8)  
- оптимальное кол-во поставок, шт. (C11:F11) >= фактический объем поставки (C5:G5)  
- оптимальное кол-во поставок, шт. (C11:F11) = целое  
- оптимальное кол-во поставок, шт. = неотрицательное значение  
 
2. А вот в уравнении затык.. Даже не представляю с какого бока подойти.. Курс моей школьной и университетской алгебры явно хромал. (
 
 
 
#4
02.02.2012 11:44:07
Очевидно, дело в построении уравнения и, как следствие, выстраивание четких взаимосвязей между фактом, планом и желаемым результатом.  
Просить составить уравнение за меня - наглость, поэтому просто прошу - подскажите хотя бы, в каком направлении мне копать или каких переменных мне не достает для возможности решения задачи линейным методом.
 
 
 
#5
02.02.2012 11:57:19
Ну минимальное кол-во поставок = 4 тут все просто ..неизвестно какой объем чего мы перевозим за одну поставку?  
 
И еще непонятно ..мы поставили 2400 всяких фруктов ..а сколько мы еще должны поставить и чего сколько ...или мы должны поставить опять 2400 но уже оптимальнее,  так как по яблокам иапельсинам у нас превышение?
 
 
 
#6
02.02.2012 12:13:57
Ну минимальное кол-во поставок = 4 тут все просто    
// сумарное минимальное кол-во поставок не может быть меньше фактического, т.е. минимальное в данном случае = 2400, т.к. это факт, ниже которого мы не можем сделать. Это одно из условий/ограничений:  
- оптимальное кол-во поставок, шт. (C11:F11) >= фактический объем поставки (C5:G5)  
 
При этом важно, что у нас есть минимальное по каждому из 4-х товаров.  
У товара №1 - минимум 400, у товара №2 - минимум 500 и тд.  
 
 
неизвестно какой объем чего мы перевозим за одну поставку?  
// Кол-во возможных будущих поставок = 1. Т.е. у нас есть факт (строка №5) и нам предстоит сделать еще одну поставку/отгрузку, чтобы выполнить план (строка №8). Т.е. по сути нам нужно расчитать то кол-во поставок по каждому товару, к-ое будет минимальным (но не ниже уже имеющегося факта) и при этом выполнять установленный план. Перевыполнение плана, по какому-либо товару также возможно, но из расчета что СУММ(C12:F12) = 100%.
 
 
 
#7
02.02.2012 12:22:59
Добавил в пример строку: Недовыполнение факта, %  
Это значение отображает, сколько процентов мы недовыполнены по какому-либо товару.  
 
Т.е. в идеале нам нужно только догрузить груши на 4,2% и персики на 0,8%.  
По яблокам и апельсинам план выполнены, поэтому в идеале их догружать не надо.  
 
Но так если мы догрузим груши и персики на соответствующий процент, то изменится значение "итого" по отгрузкам этих четырех товаров. В след за изменением итого прочитается новая доля каждого товара от этого итого (т.е. от 100%). И нам нужно будет по новой догружать недогруженные товары. И так до тех пор пока не будет выполнен план отгрузок и все всех долей не будет равен 100%.
 
 
 
#8
02.02.2012 12:24:11
Вы меня запутали окончательно.... ничего не понимаю..
 
 
 
#9
02.02.2012 12:47:42
Извиняюсь за путанность своих мыслей..  
Чтобы как-то прояснить мой вопрос прикладываю факл с уже расчитанным оптимальным значением НЕлинейным методом.  
 
Как видно из результата (строка 11):  
1. доля каждого товара (строка 12) соответствует плану (т.е. выполнено условие >= план)  
2. сумма все долей каждого товара (строка 12) = 100%  
3. оптимальное кол-во поставок по каждому товару >= фактических значений (строка 5)  
 
 
Если руководствоваться именно этим примером, то задача успешно выполнено и необходимый результат получен. Но это НЕ линейный метод и возможна ситуация, когда входные данные будут другими и оптимальное значение не будет найдено.  
 
Именно поэтому и возникла необходимость переписать даное решение под линейный метод.
 
 
 
#10
02.02.2012 12:54:04
Также прикладываю файл с нерешенным примером.  
 
В данном случае я просто изменил входные данные (факт в строке 5). Нелинейным методом решение не было найдено.  
Да, программа просчитала приближенные к оптимальным значениям показатели, но это уже не конечное решение, а лишь приближенное.
 
 
 
Страницы: 1
Читают тему
Наверх