Използването на Solver за определяне на оптимален продукт mix

Забележка:  Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

В тази статия се обсъжда използването на Solver, Добавяне на програма на Microsoft Excel можете да използвате за условен анализ, за определяне на оптимална продукти.

Как да определите месечен mix продукт, който увеличава рентабилност?

Фирми често трябва да определи количеството на всеки продукт се получава на месечна база. В простия си вид продукт Смесете проблем включва как да определите размера на всеки продукт, които трябва да бъдат представени по време на месец, за да увеличите приходите. Продукт mix обикновено трябва да отговарят на следните ограничения:

  • Продукти можете да използвате повече ресурси, отколкото са налични.

  • Има ограничен търсене за всеки продукт. Ние не може да произвежда повече от един продукт по време на месеца от търсенето диктува, защото над производството се губи (например, нетрайни наркотици).

Нека сега реши следния пример на mix проблема. Можете да намерите решение на този проблем във файла Prodmix.xlsx, показани в фигура 27-1.

Book image
Фигура 27-1 продукт mix

Да речем, работим за наркотици компания, която поражда шест различни продукти по техните растение. Представяне на всеки продукт изисква трудов и материал. Ред 4 в фигура 27-1 показва часа на раждане, необходими за производството фунт на всеки продукт, и ред 5 показва фунт на суров материал, необходим за производството на един паунд за всеки продукт. Например производството на продукт 1 изисква шест часа на раждане и 3,2 фунт на суров материал. За всеки наркотици цената за фунт е даден в ред 6, Единична цена на фунт е даден в ред 7 и печалба приноса на фунт е даден в ред 9. Например продукт 2 се продава за $11,00 на фунт, направила с единична цена от $5,70 на фунт и допринася $5,30 печалба на фунт. Месеца търсенето на всеки наркотици фигурира в ред 8. Например търсенето на продукт 3 е 1041 фунт. Този месец, 4500 часа на раждане и 1 600 фунт на суров материал са налични. Как да тази фирма увеличите си месечен печалба?

Ако ние не знае нищо за Excel Solver, ние ще атака този проблем с изграждането на работен лист за проследяване на печалбата и използването на ресурсите, свързани с продукта mix. След това ще използваме и грешката да се различават продукт mix да оптимизирате печалба, без да се използва повече трудов или суров материал, отколкото е налично и без да се получава всички наркотици повече от търсенето. Ние използваме Solver в този процес само на етап опити и грешки. В основата Solver е оптимизация машина, която качество извършва търсене опити и грешки.

Ключ за решаване на проблема с mix е да се изчисли ефективно използване на ресурси и печалба, свързани с всяка комбинация от даден продукт. SUMPRODUCT функция е важен инструмент, който може да използваме да направите този изчисление. SUMPRODUCT функция умножава съответните стойности в диапазони от клетки и връща сумата на тези стойности. Всеки диапазон от клетки, използвани в SUMPRODUCT оценка трябва да имат еднакви размери, което означава, че можете да използвате SUMPRODUCT с два реда или две колони, но не и с една колона и един ред.

Като един пример за това как можем да използваме функцията SUMPRODUCT в нашия продукт Смесете пример, нека опитаме да се изчисли нашите използване на ресурси. Използване на нашите раждане се пресмята посредством

*(Drug 1 pounds produced) (трудов използва по фунт на наркотици 1) +
(трудов използва по фунт на наркотици 2) * (наркотици 2 фунт произведени) +...
(Трудов използва по фунт на наркотици 6) * (наркотици 6 фунт произведени)

Може да се изчисли трудов употребата в по-досаден модата като D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. По същия начин, може да се изчисли материал употребата като D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * Н5 + I2 * I5. Въвеждане на тези формули в работен лист за шест продукти е отнема много време. Представете си колко време ще отнеме, ако сте работили с фирма, която произвежда, например 50 продукти по техните растение. Много по-лесен начин да се изчисли трудов и употребата на материал е да копирате от D14 D15 формулата SUMPRODUCT($D$2:$I$2,D4:I4). Тази формула се изчислява D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (което е използване на нашите раждане), но е много по-лесно да въведете! Обърнете внимание, че използвам символът $ с диапазона D2:I2, така че когато копирате формулата все още заснемане на продукта комбинация от ред 2. Формулата в клетката е D15 се изчислява материал употребата.

По подобен начин нашата печалба се определя от

(Наркотици 1 печалба на фунт) * (наркотици 1 фунт произведени) +
(наркотици 2 печалба на фунт) * (наркотици 2 фунт произведени) +...
(Наркотици 6 печалба на фунт) * (наркотици 6 фунт произведени)

Печалба лесно се изчислява в клетка D12 с формула SUMPRODUCT(D9:I9,$D$2:$I$2).

Да определяме сега трите компонента на нашите продукти mix модел на Solver.

  • Целевата клетка. Нашата цел е да увеличаване на печалбата (изчислен в клетка D12).

  • Променливи клетки. Броят на фунт произведени от всеки продукт (изброени в диапазона от клетки D2:I2)

  • Ограниченията. В момента има следните ограничения:

    • Не използвайте повече трудов или материал не е налична. Това означава стойностите в клетки D14:D15 (ресурсите, използвани) трябва да бъде по-малка или равна на стойностите в клетките F14:F15 (наличните ресурси).

    • Да създадат повече от наркотици, отколкото е в търсенето. Това означава стойностите в клетки D2:I2 (фунт, получени от всяка наркотици) трябва да бъде по-малка или равна на търсенето на всеки наркотици (изброени в клетки D8:I8).

    • Ние не може да произвежда отрицателна стойност на наркотици.

Ще ви покажа как да въведете целевата клетка, променя клетки и ограниченията в Solver. Тогава всичко, което трябва да направите е да щракнете върху Решавай бутона, за да намерите увеличаване на печалбата продукт комбинация!

За да започнете, щракнете върху раздела "данни" и в групата анализ щракнете върху Solver.

Забележка: Както е обяснено в глава 26, "Въведение към оптимизация с Excel" Решател "," Solver е инсталиран чрез щракване върху бутона Microsoft Office, след което Опции на Excel, последвано от добавки. В списъка управление щракнете върху добавки на Excel, отметнете квадратчето добавка Solver и след това щракнете върху OK.

Ще се покаже диалоговият прозорец параметри на Solver, както е показано в фигура 27-2.

Book image
Фигура 27-2 диалоговия прозорец параметри на Solver

Щракнете върху полето за задаване на целевата клетка и след това изберете нашата печалба клетка (клетка D12). Щракнете върху полето чрез променяне на клетките и след това посочете диапазона D2:I2, който съдържа фунт произведени на всеки наркотици. Диалоговият прозорец сега би трябвало да изглежда фигура 27-3.

Book image
Фигура 27-3 диалоговия прозорец параметри на Solver с целевата клетка и променящи се клетки, които са дефинирани

Сега сме готови да добавите ограничения на модела. Щракнете върху бутона Добави. Ще видите диалоговия прозорец Добавяне на ограничение, показани в фигура 27-4.

Book image
Фигура 27-4 диалоговия прозорец Добавяне на ограничение

За да добавите ограничения за употребата на ресурсите, щракнете върху полето препратка към клетка и след това изберете диапазона D14:D15. Изберете < = от средния списък. Щракнете върху полето ограничение и след това изберете диапазона от клетки F14:F15. Диалоговия прозорец Добавяне на ограничение трябва да изглежда като фигура 27-5.

Book image
Фигура 27-5 диалоговия прозорец Добавяне на ограничение с ресурс употребата ограниченията въведени

Ние сега да се гарантира, че когато Solver се опитва различни стойности за променяне на клетки, само комбинации, които удовлетворяват и двете D14 < = F14 (трудов използва е по-малко или равно на трудов наличен) и D15 < = F15 (суров продукт е по-малко или равно на ще се счита суров материал наличен). Щракнете върху Добави, за да въведете търсенето ограничения. Попълнете диалоговия прозорец Добавяне на ограничение, както е показано на илюстрацията 27-6.

Book image
Фигура 27-6 диалоговия прозорец Добавяне на ограничение с търсенето ограниченията въведени

Добавянето на тези ограничения, гарантира, че когато Solver се опита различни комбинации за промяната стойностите на клетките, ще се счита само комбинации, които отговарят на следните параметри:

  • D2 < = D8 (сумата, получена на наркотици 1 е по-малка или равна на търсенето на наркотици 1)

  • E2 < = E8 (сумата на произведени от наркотици 2 е по-малка или равна на търсенето на наркотици 2)

  • F2 < = F8 (сумата, получена от наркотици 3 направени е по-малка или равна на търсенето на наркотици 3)

  • G2 < = G8 (сумата, получена от наркотици 4 направени е по-малка или равна на търсенето на наркотици 4)

  • H2 < = H8 (сумата, получена на наркотици 5 направени е по-малка или равна на търсенето на наркотици 5)

  • I2 < = I8 (сумата, получена от наркотици 6 направени е по-малка или равна на търсенето на наркотици 6)

Щракнете върху OK в диалоговия прозорец Добавяне на ограничение. Прозорец на Solver трябва да изглежда като фигура 27-7.

Book image
Фигура 27-7 окончателен диалоговия прозорец параметри на Solver за проблема с mix

Ние въведете ограничението, променящи се клетки трябва да бъде неотрицателното в диалоговия прозорец Опции за Solver. Щракнете върху бутона "Опции" в диалоговия прозорец параметри на Solver. Отметнете полетата Приеми линеен модел и поеме Non-отрицателни, както е показано в цифра 27-8 на следващата страница. Щракнете върху OK.

Book image
Фигура 27-8 Solver опции за настройки

Проверка на поеме Non-отрицателни полето гарантира, че Solver смята, че само комбинации от променливи клетки, в които всяка клетка, промяната предполага-отрицателна стойност. Ние отметнато квадратчето Приеми линеен модел, тъй като продукта Смесете проблем е специфичен тип Solver проблем, наречен линеен модел. В основата на модел на Solver е линейна при следните условия:

  • Целевата клетка се изчислява като сбор условията на формуляра за (промяната cell)*(constant).

  • Всяко ограничение отговаря на "линеен модел изискването." Това означава, че всеки ограничението се оценява като сбор условията на формуляра за (промяната cell)*(constant) и сравняване на сумите, които ще константа.

Защо е този проблем Solver линейна? Нашите целевата клетка (печалба) се изчислява като

(Наркотици 1 печалба на фунт) * (наркотици 1 фунт произведени) +
(наркотици 2 печалба на фунт) * (наркотици 2 фунт произведени) +...
(Наркотици 6 печалба на фунт) * (наркотици 6 фунт произведени)

Този изчисление следва модел, в която се получава стойността на целевата клетка като сбор условия на формуляра за (промяната cell)*(constant).

Нашите трудов ограничението се оценява чрез сравняване на стойност, получени от (трудов използвани на фунт наркотици 1) * (наркотици 1 фунт произведени) ++ (трудов използвани на фунт на наркотици 2) *(Drug 2 pounds produced)... (Трудов ниЕд на фунт от наркотици 6) * (наркотици 6 фунт произведени) към налични трудов.

Следователно, трудов ограничението се оценява като сбор условията на формуляра за (промяната cell)*(constant) и сравняване на сумите, които ще константа. Ограничение на раждане и материал ограничението отговарят на изискването за линеен модел.

Нашите поискване ограниченията под формата

(Наркотици 1 произведени) < = (1 търсенето)
(наркотици 2 произведени) < = (2 търсенето)
член
(наркотици 6 произведени) < = (6 търсенето)

Всяко поискване ограничение също отговаря на изискването за линеен модел, защото всеки се оценява като сбор условията на формуляра за (промяната cell)*(constant) и сравняване на сумите, които ще константа.

Като показва, че нашите продукти mix модел е линеен модел, защо трябва да интересува?

  • Ако модел на Solver е линейна и изберете Приеми линеен модел, Solver е сигурност ще намерите оптимално решение на модел на Solver. Ако модел на Solver не е линейна, Solver може или не може да намери оптимално решение.

  • Ако модел на Solver е линейна и изберете Приеми линеен модел, Solver използва много ефективен алгоритъм (simplex метод), за да намери оптимално решение на модела. Ако модел на Solver е линейна и ние не избирайте Приеми линеен модел, Solver използва много неефективни алгоритъм (GRG2 метод) и може да имат затруднения при намирането на модела на оптимални решения.

След като щракнете върху OK в диалоговия прозорец Опции за Solver, ние се върнете към основния Solver диалоговия прозорец е показано по-рано в фигура 27-7. Когато ние щракнете върху Решавай, Solver изчислява оптимално решение (ако има такава) за mix модел нашите продукти. Както е посочено в глава 26, оптимално решение на модел на продукта mix ще бъде набор от промяна на стойностите на клетките (фунт, получени от всяка наркотици), който увеличава печалбата набор от всички възможни решения. Отново е възможно решение е набор от промяната на стойностите на клетките, които отговарят на всички ограничения. Променя стойностите на клетките, показани във фигура 27-9 са допустимо решение, защото всички нива на производство са неотрицателното производствените нива не надвишават търсене и използване на ресурси не надвишава наличните ресурси.

Book image
Фигура 27-9 A възможно решение с продукта Смесете проблем става в рамките на ограниченията.

Променя стойностите на клетките, показани във фигура 27-10 на следващата страница представлява възможно решение поради следните причини:

  • Ние произвежда повече от наркотици 5 от търсенето за нея.

  • Ние използваме повече трудов от това, което е на разположение.

  • Ние използваме повече суров материал от това, което е на разположение.

Book image
Фигура 27-10 възможно решение на проблема за mix на продукта не се побира в рамките на определени ограничения.

След като щракнете върху Решавай, който Solver намира бързо оптимално решение, показани във фигура 27-11. Трябва да изберете запази решението от Solver да запазите оптимално решение стойности в работния лист.

Book image
Фигура 27-11 на оптимално решение на проблема с mix

Фирмата наркотици да увеличите си месечен печалба на ниво от $6,625.20 чрез представяне 596.67 фунт на наркотици 4 1084 фунт на наркотици 5 и нито една от други наркотици! Ние не може да определи дали може да се постигне максималната печалба от $6,625.20 по други начини. Всички можем да сте сигурни за е, че с нашите ограничени ресурси и търсене, няма начин да направите повече от $6,627.20 този месец.

Да предположим, че търсенето на всеки продукт, трябва да бъдат изпълнени. (Вижте таблицата за Възможно решение № във файла Prodmix.xlsx.) След това трябва да променим нашите поискване ограничени от D2:I2 < = D8:I8 да D2:I2 > = D8:I8. За да направите това, отворете Solver, изберете D2:I2 < = D8:I8 ограничение и след това щракнете върху промяна. Появява се диалоговият прозорец промяна на ограничение, показани в фигура 27-12.

Book image
Фигура 27-12 диалоговия прозорец промяна на ограничение

Изберете > = и след това щракнете върху OK. Ние сега сте гарантира, че Solver ще Променете само стойностите на клетките, които отговарят на всички изисквания. Когато щракнете върху Решавай, ще видите съобщението "Solver не може да намери допустимо решение." Това съобщение не означава, че ще направи грешка в нашия модел, но по-скоро, с ограничени ресурси, ние не може да отговори на търсене на всички продукти. Solver е просто ни казва, че ако искаме да отговори на търсене за всеки продукт, трябва да добавите още трудов, повече материали или повече от двете.

Нека видим какво се случва, ако разрешаваме неограничено търсене за всеки продукт и разрешаваме отрицателни количества да бъдат изпълнени от всеки наркотици. (Можете да видите този проблем Solver в Задаване на стойности не съвпадат лист във файла Prodmix.xlsx.) За да намерите оптимално решение за тази ситуация, отворете "Решател", щракнете върху бутона Опции и изчистете полето поеме Non-отрицателни. В диалоговия прозорец параметри на Solver изберете поискване ограничението D2:I2 < = D8:I8 и след това щракнете върху Изтрий, за да премахнете ограничението. Когато щракнете върху Решавай, Solver връща съобщението "Задаване на стойности на клетки не съвпадат." Това съобщение означава, че ако целевата клетка трябва да бъде увеличена (като в нашия пример), са допустими решения с произволно големи целеви стойности на клетки. (Ако е на целевата клетка да бъде намалявана, съобщението "Задаване на клетката стойности да не се събират" означава, че са допустими решения с произволно малки целеви стойности на клетки.) В нашата ситуация като позволява отрицателни производството на наркотици в сила "създаваме" ресурси, които могат да бъдат използвани за производството произволно големи количества на други лекарства. Поради търсенето ни неограничено, това ни позволява да направите неограничен печалби. В реална ситуация ние не може да се безкраен сума. Накратко Ако виждате "Задаване на стойности да не се събират", своя модел има грешка.

  1. Да предположим, че фирмата наркотици могат да закупят до 500 часа на раждане $ 1 още един час от текущия трудов разходи. Как да увеличаване на печалбата?

  2. При си производството четири техника (A, B, C и D) произвежда три продукти (1, 2 и 3). Този месец, производителя си да продавате 80 единици на продукта 1, 50 единици на продукт 2 и най-много 50 единици от 3 продукта. Техника A, за да направите само продукти 1 и 3. Техника B може да направи само продукти 1 и 2. Техника C, за да направите само продукт 3. Техника D може да направи само продукт 2. За всяка единица, произведени, продуктите допринасят следните печалба: продукт 1, $6; Продукт 2, $7; и продукт 3, $10. Време (в часове) всеки специалист трябва да произвежда продукт както следва:

    Продукт

    Техника A

    Техника B

    Техника C

    Техника D

    1

    2

    2,5

    Не можете да правите

    Не можете да правите

    2

    Не можете да правите

    3

    Не можете да правите

    3,5

    3

    3

    Не можете да правите

    4

    Не можете да правите

  3. Всеки специалист може да работи до 120 часа на месец. Как да си производителя увеличите си месечен печалба? Предполагат дробно брой единици могат да бъдат произведени.

  4. Компютър производството произвежда мишка, клавиатури и видео играта джойстици. Единица печалба единица трудов употребата, месечен търсенето и единица време на машина употребата са дадени в таблицата по-долу:

    Мишка

    Клавиатури

    Джойстици

    Печалба/единица

    $8

    $11

    $9

    Трудова употребата/единица

    .2 час

    .3 час

    .24 час

    Машина/единица за време

    .04 час

    .055 час

    .04 час

    Месечен търсене

    15 000

    27 000

    11 000

  5. Всеки месец, общо 13000 трудов часове и 3000 часа машина време са налични. Как да производителя увеличите вноската месечен отчет за печалби от растение?

  6. Разрешаване на нашите наркотици пример предполагаемо минималната търсенето на 200 единици за всяка наркотици трябва да бъдат изпълнени.

  7. София прави диамант гривни, колиета и обеци. Иска да работи максимум 160 часа на месец. Той има 800 унция на ромбчета. Печалба трудов време и унция на ромбчета, необходими за производството на всеки продукт са дадени по-долу. Ако търсенето за всеки продукт е неограничено, как да София увеличаване на печалбата си?

    Продукт

    Единица печалба

    Трудова часа на единица

    Унция на ромбчета за бройка

    Гривна

    300 лв.

    .35

    1.2

    Колие

    200 лв.

    .15

    .75

    Обеци

    100 лв.

    0,05

    ,5

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×