Примітка.: Ми хочемо надавати найновіший вміст довідки рідною мовою користувачів якомога швидше. Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була інформація корисною, унизу цієї сторінки. Для зручності цю статтю можна переглянути англійською мовою.
У цій статті описано за допомогою надбудови "розв'язувач", надбудова програми Microsoft Excel можна використовувати для аналізу what-if, для визначення mix на оптимальні продукту.
Як можна визначити щомісячний поєднання продукту, що прибутковості за допомогою?
Компанії, які часто потрібно визначити кількість кожного продукту створює щомісяця. Найпростіший форма цього продукту змішувати включає в себе визначення кількості кожного продукту, які повинні бути зроблені за місяць, щоб збільшити прибуток. Поєднання продукту зазвичай мають дотримуватися такі обмеження:
-
Поєднання продукту не можна використовувати більше ресурсів, ніж доступні.
-
Існує обмежений вимогу для кожного продукту. Ми не овочі більше продукту протягом місяця за вимогою диктує, оскільки надлишкового виробництва втрачається (наприклад, швидкопсувні наркотиків).
Давайте тепер вирішити у наведеному нижче прикладі поєднання товарів. Вирішення цієї проблеми можна знайти у файлі Prodmix.xlsx, показано на малюнку 27-1.
Припустимо, що ми працюємо наркотиків компанію, яка повертає шість різних продуктів на їх підприємства. Виробництво кожного продукту потрібна праці та сировини. 4 рядка в рисунку 27-1 відображає години трудові витрати, необхідні для створення фунт кожного продукту, а рядок 5 показано фунтах сировини, необхідні для створення фунт кожного продукту. Наприклад, виробництва товару 1 фунт вимагає 6 годин праці та 3,2 фунта сировини. Для кожного наркотиків вартість фунт надається у рядку 6, Вартість одиниці фунта буде надано в рядку 7, а прибуток внеску кожного фунт – це у рядку 9. Наприклад, продукт 2 продає за $11,00 за фунт бере на себе вартість одиниці $5.70 за фунт та сприяє $5,30 прибуток за фунт. Вимога на місяць для кожного наркотиків буде надано в рядку 8. Наприклад, потреба продукт 3 – це 1041 фунта. У цьому місяці 4500 годин праці та 1600 фунтах сировини доступні. Як розгорнути її Щомісячний звіт про прибутки цієї компанії?
Якщо ми знали, що нічого про Пошук розв'язання Excel, ми б атак цієї проблеми побудови аркуша для відстеження використання звіт про прибутки та ресурсів, пов'язані з поєднання продукту. Потім ми використовуємо ознайомлювальну версію і помилок змінюватися продукту поєднання оптимізувати прибуток без за допомогою кількох праці або сировини не доступна і будь-який наркотиків понад вимогу виробництва. Ми використовуємо розв'язання в цей процес тільки на етапі ознайомлювальну версію та помилки. По суті, Пошук розв'язання – це оптимізації модуля, який бездоганно виконує пошук ознайомлювальну версію та помилки.
Клавіші для вирішення цієї проблеми поєднання продукту – це ефективно обчислення використання ресурсів і прибутку, що пов'язані з будь-які поєднання заданого продукту. Важливим інструментом, можна використати, щоб зробити цей обчислень – це функції SUMPRODUCT. Функція SUMPRODUCT Перемножує відповідні значення в діапазоні клітинок і Повертає суму основі цих значень. Кожний діапазон клітинок, які використовуються в аналізу за допомогою функції SUMPRODUCT має бути однакові розміри, яка передбачає, які можна використовувати функцію SUMPRODUCT із двома рядками та двома стовпцями, а не з один стовпець та один рядок.
Як приклад того, як можна використовувати функції SUMPRODUCT у продукту mix приклад, спробуємо обчислення наші використання ресурсів. Використання наших праці обчислюється за
*(Drug 1 pounds produced) (праці використовуються за фунт наркотиків 1) +
(праці використовуються за фунт наркотиків 2) * (наркотиків 2 фунта виробництва) +...
(Праці використовуються за фунт наркотиків 6) * (наркотиків 6 фунтах виробництва)
Ми може обчислити праці використання більше втомлює моди як D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * Р4. Так само сировини використання можна обчислити як D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Однак, введення цих формул на аркуші для шість продуктів є багато часу. Уявіть, скільки буде потрібно якщо ви працювали компанію, яка виробництва, наприклад, 50 продукти в їх підприємства. Про набагато простіший спосіб обчислення праці та використання сировини – це можна копіювати з D14 D15 формула SUMPRODUCT($D$2:$I$2,D4:I4). Ця формула обчислює D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * Р4 (тобто використання наших праці), але набагато легше ввести! Зверніть увагу, що можна використовувати знак долара із діапазону D2:I2, щоб під час копіювання формули можна й надалі фіксування продукту поєднання із 2 рядка. Формула у клітинці D15 обчислює сировини використання.
Таким же чином прибуток визначається
(Наркотиків 1 прибуток за фунт) * (1 наркотиків фунтах виробництва) +
(наркотиків 2 прибуток за фунт) * (наркотиків 2 фунта виробництва) +...
(6 наркотиків прибуток за фунт) * (наркотиків 6 фунтах виробництва)
Звіт про прибутки легко обчислити D12 клітинку з формулою SUMPRODUCT(D9:I9,$D$2:$I$2).
Тепер ми можемо визначити три компоненти нашого продукту поєднання розв'язання моделі.
-
Цільову клітинку.Наша мета – це максимальний прибуток (обчислити у клітинці D12).
-
Кількість змінюваних клітинок.Кількість фунтах виробництва кожного продукту (у списку в діапазоні клітинок D2:I2)
-
Обмеження. У нас є такі обмеження:
-
Не використовуйте більше праці та сировини не доступна. Тобто значення клітинки D14:D15 (ресурси, використані) має бути менше або дорівнює до значень у клітинках F14:F15 (доступні ресурси).
-
Не створює більше ніж попиту наркотиків. Тобто значень у клітинках D2:I2 (фунта з кожного наркотиків) має бути менше або дорівнює потреба в кожному наркотиків (відображаються у клітинках D8:I8).
-
Ми не овочі від'ємне кількість будь-який наркотиків.
-
Мені потрібно показати вам, як введення цільову клітинку, змінення клітинок і обмеження на Пошук розв'язання. Виберіть все, що потрібно зробити це, натисніть кнопку розв'язати для пошуку максимального прибутку продукту поєднання!
Щоб почати, перейдіть на вкладку дані та у групі Аналіз натисніть кнопку розв'язувач.
Примітка.: Як описано в розділі 26, "Загальні відомості про для оптимізації з Excel надбудови" розв'язувач"," Пошук розв'язання інстальовано, натиснувши кнопку Microsoft Office, виберіть Параметри Excel, слідує надбудови. У списку керування виберіть пункт Надбудови Excel, установіть прапорець надбудови Пошук розв'язання та натисніть кнопку OK.
Відкриється діалогове вікно параметри розв'язувача, як показано на рисунку 27-2.
Клацніть у полі Установити цільову клітинку а потім виберіть наших прибуток клітинки (клітинки D12). Клацніть поле шляхом змінення клітинок і наведіть вказівник миші на діапазон D2:I2, який містить фунтах виробництва кожного наркотиків. Діалогове вікно Тепер має виглядати рисунок 27 – 3.
Ми зараз готове для додавання обмежень моделі. Натисніть кнопку Додати. Ви побачите діалоговому вікні Додавання обмеження, показано на малюнку 27 – 4.
Щоб додати обмеження використання ресурсів, клацніть клітинку поле та виберіть діапазон D14:D15. Виберіть пункт < = середній списку. Клацніть у полі обмеження а потім виберіть діапазон клітинок F14:F15. Діалогове вікно Додавання обмеження тепер має виглядати як рисунок 27 – 5.
Ми зараз забезпечує, що під час розв'язання намагається різні значення для зміни клітинок, тільки комбінації, які задовольняють обидві D14< = F14 (праці використовуються є менше або дорівнює праці доступно) і D15< = F15 (сировини використовується менший або дорівнює буде вважатися сировини доступні). Натисніть кнопку Додати, щоб ввести вимогу обмежень. Введіть у діалоговому вікні Додавання обмеження, як показано на рисунку 27 – 6.
Додавання цих обмежень гарантує, що під час розв'язання різних комбінацій для змінення значень клітинок, комбінацій, задовольнити нижченаведених параметрів буде вважатися:
-
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 у діалоговому вікні Додавання обмеження. Пошук розв'язання вікно має виглядати як рисунок 27-7.
Ми введіть обмеження, кількість змінюваних клітинок має бути додатне в діалоговому вікні параметри розв'язувача. Натисніть кнопку Параметри в діалоговому вікні параметри розв'язувача. Установіть у лінійній моделі і вважати не від'ємні вікно, як показано на рисунку 27-8 на наступній сторінці. Натисніть кнопку OK.
Установіть прапорець вважати не від'ємні гарантує, що Пошук розв'язання вважає лише комбінацій змінюваних клітинок, у якому кожної зміни клітинки передбачає додатне значення. Ми перевірили полі лінійній моделі через продукт змішувати проблема – це спеціальний тип розв'язання задачі називається лінійній моделі. По суті, модель розв'язання є лінійною за таких обставин:
-
Цільову клітинку обчислюється додаванням разом з умовами у формі (змінення cell)*(constant).
-
Кожен обмеження задовольняє "лінійної моделі вимоги". Це означає, що кожен обмеження обчислюється додаванням разом з умовами у формі (змінення cell)*(constant) та порівняння суми для константи.
Чому – це неполадка Пошук розв'язання лінійних? Наша цільову клітинку (прибуток) обчислюється як
(Наркотиків 1 прибуток за фунт) * (1 наркотиків фунтах виробництва) +
(наркотиків 2 прибуток за фунт) * (наркотиків 2 фунта виробництва) +...
(6 наркотиків прибуток за фунт) * (наркотиків 6 фунтах виробництва)
Цей обчислень відповідають певній структурі значення клітинки цільової обчислюється додаванням разом умов у формі (змінення cell)*(constant).
Наша праці обмеження обчислюється, порівнявши значення, отримані з (праці використовуються за фунт наркотиків 1) * (1 наркотиків фунтах виробництва) + (праці використовуються за фунт наркотиків 2) + *(Drug 2 pounds produced)... (Праці намЕд за фунт наркотиків 6) * (наркотиків 6 фунтах виробництва) праці доступні.
Таким чином, обмеження праці обчислюється додаванням разом з умовами у формі (змінення cell)*(constant) та порівняння суми для константи. Праці обмеження, так і обмеження сировини задовольнити вимоги лінійній моделі.
Наша вимогу обмежень виконати у формі
_LT_ (наркотиків 1 виробництва) = (1 наркотиків попиту)
< (наркотиків 2 виробництва) = (наркотиків 2 попиту)
§
< (наркотиків 6 виробництва) = (наркотиків 6 попиту)
Кожен вимогу обмеження також задовольняє вимоги лінійної моделі, оскільки кожен обчислюється додаванням разом з умовами у формі (змінення cell)*(constant) та порівняння суми для константи.
Маючи показано, що наша продукту поєднання модель лінійного моделі, чому ми це потрібно?
-
Якщо модель розв'язання лінійних і ми вибрати лінійній моделі, розв'язувач гарантує знайти оптимальні рішення для розв'язання моделі. Якщо модель розв'язання лінійних, Пошук розв'язання може або не може знайти оптимальні рішення.
-
Якщо модель розв'язання лінійних і ми виберіть лінійній моделі, розв'язувач використовує ефективний алгоритм (симплекс-метод метод) знайти оптимальні рішення в моделі. Якщо модель Пошук розв'язання лінійних, і ми не встановлюйте лінійній моделі, Пошук розв'язання використовує дуже неефективні алгоритм (GRG2 метод) і може бути важко знайти оптимальні рішення в моделі.
Після натискання кнопки OK у діалоговому вікні параметри розв'язувача, ми повернутися до головного розв'язання діалоговому вікні показано раніше у рисунок 27-7. При натисканні вирішити, розв'язувач обчислює оптимального рішення (якщо таке існує) Наша модель поєднання продукту. Як я зазначено в розділі 26, оптимальні рішення продукту поєднання модель буде набір змінення значень клітинок (фунта з кожного наркотиків), які прибуток за допомогою набору всіх рішень, якщо це можливо. Знову ж таки якщо це можливо рішення – це набір змінення значень клітинок, які задовольняють всі обмеження. Змінення значень клітинок, показано на малюнку 27 – 9, якщо це можливо рішення, тому, що всі рівні додатне, рівні перевищувати вимогу, і використання ресурсів не перевищує доступні ресурси.
Змінення значень клітинок, показано на малюнку 27-10 на наступній сторінці представляють є неможливим рішення з таких причин:
-
Ми овочі з наркотиків 5 більше потреба його.
-
Ми використовуємо праці більше ніж доступні.
-
Ми використовуємо більше сировини за те, що є.
Після натискання кнопки вирішити, розв'язувач швидко знаходить оптимальні розв'язання, показано на малюнку 27-11. Вам потрібно виберіть елемент зберегти розв'язання, щоб зберігати значення оптимальні розв'язання на аркуші.
Компанія наркотиків можна розгорнути Щомісячний звіт про прибутки на рівні $6,625.20 з виробництва 596.67 фунтах наркотиків 4, 1084 фунтах наркотиків 5 і немає інших засобів! Ми не можна визначити, якщо ми можемо досягти максимальний прибуток $6,625.20 в інший спосіб. Все, що ми можемо бути впевнені, що з обмеженою ресурси та вимогу спосіб зробити більше $6,627.20 цього місяця.
Припустімо, що вимогу для кожного продукту має бути виконано. ( Можливо рішення без аркуші у файлі Prodmix.xlsx див.) Тоді доведеться змінити наші вимогу обмежень з D2:I2< = D8:I8 до D2:I2> = D8:I8. Для цього відкрийте розв'язання, виберіть на D2:I2< = D8:I8 обмеження а потім натисніть кнопку Змінити. Діалогове вікно змінення обмеження, показано в рисунок 27-12, відображається.
Виберіть пункт > = а потім натисніть кнопку OK. Ми зараз забезпечує, що розв'язання буде змініть лише значення клітинки, які відповідають всі вимоги. Якщо натиснути кнопку розв'язати, відобразиться повідомлення «Пошук розв'язання не вдалося знайти можливо рішення». Це повідомлення не означає, що ми зробили помилку в нашій моделі, а які наші обмежені ресурси, ми не відповідають вимогу для всіх продуктів. Пошук розв'язання просто говорить нам, якщо ми хочемо, щоб задовольнити вимоги до кожного продукту, яку необхідно додати більше праці, додаткові матеріали або більше обох.
Подивимося, що відбувається, якщо ми дозволити необмежений вимогу для кожного продукту, і ми дозволити від'ємних величин до здійснюватися кожного наркотиків. (Можна побачити цю проблему розв'язання на аркуші у файлі Prodmix.xlsx Встановити значення не сходяться .) Щоб знайти оптимальні рішення для цієї ситуації, відкрийте розв'язання, натисніть кнопку Параметри та зніміть прапорець вважати не від'ємні. У діалоговому вікні параметри розв'язувача виберіть обмеження вимогу D2:I2< = D8:I8 а потім натисніть кнопку Видалити, щоб видалити обмеження. Якщо натиснути кнопку розв'язати, розв'язувач повертає повідомлення «Установити значення клітинок не сходяться». Це повідомлення означає, що якщо цільову клітинку розгортати (як у нашому прикладі), можливо рішення з довільно великих цільового значення клітинок. (Якщо цільову клітинку звести до мінімуму, повідомлення «Настроювання клітинку значення не сходяться» означає, що допустимих розв'язань з довільно малого цільового значення клітинок.) В нашій ситуації дає змогу від'ємне виробництва наркотиків, ми по суті "створити" ресурсів, які можна використовувати для створення довільно великих обсягів інших засобів. Огляду наші необмежений вимогу, це дозволяє прибуток не обмежено. У реальному ситуації ми не можна вносити нескінченну кількість грошей. Одним словом, якщо ви бачите «Установити значення не сходяться», моделі є повідомлення про помилку.
-
Припустімо, що компанії наркотиків можна придбати до 500 годин праці на 1 більше за годину поточного трудові витрати. Як ми максимальний прибуток?
-
У чіп завод чотири фахівці (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
Не можна робити
-
Кожен техніка, можна звернутися до 120 годин на місяць. Як розгорнути її Щомісячний звіт про прибутки чіп виробника? Припустимо, що можуть створюватися дробова кількість одиниць.
-
Завод комп'ютер створює миші розкладок клавіатури та відео гра джойстики. Прибуток за одиницю, одиницю праці використання щомісячний вимогу та використання машинного часу за одиницю наведені в таблиці нижче:
Миші
Розкладку клавіатури
Джойстики
Звіт про прибутки та одиниці
$8
$11
$9
Використання праці та одиниці
,2 години
.3 години
.24 години
Машинного часу та одиниці
.04 години
.055 години
.04 години
Щомісячний вимогу
15 000
27000
11000
-
Кожного місяця, 13 000 годин праці та 3000 годин машинного часу доступні. Як розгорнути її Щомісячний звіт про прибутки внесок від заводу виробника?
-
Усунення наші наркотиків приклад припускається, що Мінімальне замовлення 200 одиниць для кожного наркотиків має бути виконано.
-
Подбайте про використання робить ромб браслети, намиста і сережки. Хоче працювати більше 160 годин на місяць. Він має 800 унцій з ромбів. Звіт про прибутки, робочий час і унцій ромби, необхідні для створення кожного продукту наведені нижче. Якщо не обмежено вимогу для кожного продукту, як можна подбайте про використання максимальний його прибуток?
Продукт
Звіт про прибутки одиниці
Трудові годин за одиницю
Унцій ромби за одиницю
Браслет
300 грн.
.35
1,2
Намисто
200₴
.15
.75
Сережки
100 грн.
0,05
.5
