Пошук рішення - це надбудова Microsoft Excel, за допомогою якої можна знайти оптимальне рішення задачі з урахуванням визначених користувачем обмежень.

Пошук рішення будемо розглядати в (ця надбудова зазнала деяких змін порівняно з попередньою версією в .
У цій статті розглянемо:

• створення оптимізаційної моделі на аркуші MS EXCEL
• налаштування пошуку рішення;
• Найпростіший приклад (лінійна модель).

Встановлення пошуку рішення

Команда Пошук рішеннязнаходиться у групі Аналізна вкладці Дані.

Якщо команда Пошук рішенняу групі Аналізнедоступна, необхідно включити однойменну надбудову.
Для цього:

• На вкладці Файлвиберіть команду Параметри, а потім – категорію Надбудови;
• У полі Управліннявиберіть значення Надбудови Excelта натисніть кнопку Перейти;
• У полі Доступні надбудовивстановіть прапорець поруч із пунктом Пошук рішеннята натисніть кнопку ОК.

Примітка. Вікно Надбудовитакож доступно на вкладці Розробник. Як увімкнути цю вкладку.

Після натискання кнопки Пошук рішенняу групі Аналіз,відкриється його діалогове вікно .

При частому використанні Пошуку рішенняйого зручніше запускати з Панелі швидкого доступу, а не вкладки Дані. Щоб помістити кнопку на Панель, клацніть на ній правою клавішею миші та виберіть пункт Додати на панель швидкого доступу.

Про моделі

Цей розділ для тих, хто тільки знайомиться з поняттям «Оптимізаційна модель».

Порада. Перед використанням Пошуку рішеннянастійно рекомендуємо вивчити літературу щодо вирішення оптимізаційних завдань та побудови моделей.

Нижче наведено невеликий лікнеп на цю тему.

Надбудова Пошук рішеннядопомагає визначити найкращий спосіб зробити щось:

• "Щось" може включати виділення грошей на інвестиції, завантаження складу, доставку товару або будь-яку іншу предметну діяльність, де потрібно знайти оптимальне рішення.
• "Найкращий спосіб" або оптимальне рішення в цьому випадку означає: максимізацію прибутку, мінімізацію витрат, досягнення найкращої якості та ін.

Ось деякі типові приклади оптимізаційних завдань:

• Визначити , у якому прибуток від реалізації виробленої продукції максимальний;
• Визначити , коли загальні витрати на перевезення були б мінімальними;
• Знайти щоб загальні витрати на виробництво продукції були б мінімальними;
• Визначити мінімальний термін виконання всіх робіт проекту (критичний шлях).

Для формалізації поставленого завдання потрібно створити модель, яка б відображала суттєві характеристики предметної області(І не включала б незначні деталі). Слід врахувати, що модель оптимізується Пошуком рішення тільки за одним показником(Цей оптимізований показник називається цільовою функцією).
У MS EXCEL модель є сукупність пов'язаних між собою формул, які як аргументи використовують змінні. Як правило, ці змінні можуть набувати лише допустимих значень з урахуванням заданих користувачем обмежень.
Пошук рішенняпідбирає такі значення цих змінних (з урахуванням заданих обмежень), щоб цільова функція була максимальною (мінімальною) або дорівнювала заданому числовому значенню.

Примітка. У найпростішому випадку модель може бути описана однією формулою. Деякі з таких моделей можна оптимізувати за допомогою інструмента . Перед першим знайомством з Пошуком рішеннямає сенс спочатку детально розібратися із спорідненим йому інструментом.
Основні відмінності Вибір параметравід Пошуку рішення:

• Вибір параметрапрацює лише з моделями з однією змінною;
• у ньому неможливо встановити обмеження для змінних;
• визначається не максимум або мінімум цільової функції, а її рівність нікому значенню;
• ефективно працює лише у разі лінійних моделей, у нелінійному випадку знаходить локальний оптимум (найближчий до первісного значення змінної).

Підготовка оптимізаційної моделі у MS EXCEL

Пошук рішенняоптимізує значення цільової функції. Під цільовою функцією мається на увазі формула, що повертає єдине значення в комірку. Результат формули повинен залежати від змінних моделі (не обов'язково безпосередньо можна через результат обчислення інших формул).
Обмеження моделі можуть бути накладені як на діапазон змін самих змінних, так і на результати обчислення інших формул моделі, що залежать від цих змінних.
Усі комірки, що містять змінні та обмеження моделі, повинні бути розташовані тільки на одному аркуші книги. Введення параметрів у діалоговому вікні Пошуку рішенняможливий лише з цього листа.
Цільова функція(комірка) також має бути розташована на цьому аркуші. Однак, проміжні обчислення (формули) можуть бути розміщені на інших аркушах.

Порада. Організуйте дані моделі так, щоб на одному аркуші MS EXCEL була лише одна модель. В іншому випадку, для виконання розрахунків доведеться постійно зберігати та завантажувати налаштування Пошуку рішення(Див. нижче).

Наведемо алгоритм роботи з Пошуком рішення, який радять самі розробники (www.solver.com):

• Визначте комірки зі змінними моделями (decision variables);
• Створіть формулу в комірці, яка розраховуватиме цільову функцію вашої моделі (objective function);
• Створіть формули в комірках, які обчислюватимуть значення, що порівнюються з обмеженнями ( ліва сторонавирази);
• За допомогою діалогового вікна Пошук рішеннявведіть посилання на комірки, що містять змінні, на цільову функцію, на формули для обмежень та самі значення обмежень;
• Запустіть Пошук рішеннядля знаходження оптимального рішення.

Зробимо всі ці кроки на простому прикладі.

Простий приклад використання Пошуку рішення

Необхідно завантажити контейнер товарами, щоб вага контейнера була максимальною. Контейнер має об'єм 32 куб. Товари містяться в коробках та ящиках. Кожна коробка з товаром важить 20 кг, її об'єм становить 0,15 м3. Ящик - 80кг та 0,5м3 відповідно. Необхідно, щоб загальна кількість тари була не менше ніж 110 штук.

Дані моделі організуємо в такий спосіб (див. файл прикладу ).

Змінні моделі (кількість кожного виду тари) виділено зеленим.
Цільова функція (загальна вага всіх коробок та ящиків) – червоною.
Обмеження моделі: за мінімальною кількістю тари (>=110) та за загальним обсягом (<=32) – синим.
Цільова функція розраховується за формулою =СУМПРОВИЗВ(B8:C8;B6:C6)– це загальна вага всіх коробок та ящиків, завантажених у контейнер.
Аналогічно розраховуємо загальний обсяг =СУМПРОВИЗВ(B7:C7;B8:C8). Ця формула потрібна, щоб задати обмеження на загальний обсяг коробок та ящиків (<=32).
Також для завдання обмеження моделі розрахуємо загальну кількість тари = СУМ (B8: C8).
Тепер за допомогою діалогового вікна Пошук рішеннявведемо посилання на комірки, що містять змінні, цільову функцію, формули для обмежень і самі значення обмежень (або посилання на відповідні комірки).
Зрозуміло, що кількість коробок та ящиків має бути цілим числом – це ще одне обмеження моделі.

Після натискання кнопки Знайти рішеннябудуть знайдені такі кількості коробок і ящиків, при яких загальна їхня вага (цільова функція) максимальна, і при цьому виконані всі задані обмеження.

Резюме

Насправді, основною проблемою при вирішенні оптимізаційних завдань за допомогою Пошуку рішенняє зовсім на тонкощі налаштування цього інструменту аналізу, а правильність побудови моделі, адекватної поставленої задачі. Тому в інших статтях сконцентруємося саме на побудові моделей, адже «крива» модель часто є причиною неможливості знайти рішення за допомогою Пошуку рішення.
Найчастіше простіше переглянути кілька типових завдань, знайти серед них схожу, а потім адаптувати цю модель під своє завдання.
Вирішення класичних оптимізаційних задач за допомогою Пошуку рішеннярозглянуто.

Пошуку рішення не вдалося знайти рішення (Solver could not find a feasible solution)

Це повідомлення з'являється, коли Пошук рішенняне зміг знайти поєднань значень змінних, які одночасно задовольняють всі обмеження.
Якщо ви використовуєте Симплекс метод вирішення лінійних завдань, то можна бути впевненим, що рішення справді не існує.
Якщо ви використовуєте метод вирішення нелінійних завдань, який завжди починається з початкових значень змінних, це може також означати, що допустиме рішення далеко від цих початкових значень. Якщо ви запустите Пошук рішенняз іншими початковими значеннями змінних, можливо, рішення буде знайдено.
Припустимо, що при розв'язанні задачі нелінійним методом, комірки зі змінними були залишені не заповненими (тобто початкові значення дорівнюють 0), і Пошук рішенняне знайшов рішення. Це не означає, що рішення справді не існує (хоча це може бути й так). Тепер, ґрунтуючись на результатах певної експертної оцінки, в комірки зі змінними введемо інший набір значень, який, на Вашу думку, близький до оптимального. У цьому випадку, Пошук рішенняможе знайти рішення (якщо воно справді існує).

Примітка. Про вплив нелінійності моделі на результати розрахунків можна прочитати в останньому розділі статті.

У будь-якому випадку (лінійному чи нелінійному), Ви повинні спочатку проаналізувати модель на несуперечність обмежень, тобто умов, які не можуть бути задоволені одночасно. Найчастіше це пов'язано з неправильним вибором співвідношення (наприклад,<= вместо >=) чи граничного значення.
Якщо, наприклад, у розглянутому вище прикладі, значення максимального обсягу встановити 16 м3 замість 32 м3, це обмеження суперечитиме обмеженню за мінімальною кількістю місць (110), т.к. мінімальній кількості місць відповідає обсяг рівний 16,5 м3 (110 * 0,15, де 0,15 - обсяг коробки, тобто найменшої тари). Встановивши як обмеження максимального обсягу 16 м3, Пошук рішенняне знайде рішення.

При обмеженні 17 м3 Пошук рішеннязнайде рішення.

Деякі налаштування Пошуку рішення

Метод вирішення
Розглянута вище модель лінійної, тобто. цільова функція (M - загальна вага, яка може бути максимальна) виражена наступним рівнянням M = a1 * x1 + a2 * x2, де x1 і x2 - це змінні моделі (кількість коробок і ящиків), а1 і а2 - їх ваги. У лінійній моделі обмеження також мають бути лінійними функціями від змінних. У разі обмеження за обсягом V=b1*x1+b2*x2 також виражається лінійної залежністю. Очевидно, що інше обмеження - Максимальна кількість тари (n) – також лінійно x1+x2 Лінійні завдання зазвичай вирішуються за допомогою методу Симплекс. Вибравши цей метод рішення у вікні Пошуку рішенняможна також перевірити на лінійність саму модель. У разі нелінійної моделі Ви отримаєте таке повідомлення:

У цьому випадку необхідно вибрати метод для вирішення нелінійного завдання. Приклади нелінійних залежностей: V = b1 * x1 * x1; V = b1 * x1 ^ 0,9; V = b1 * x1 * x2, де x - змінна, а V - цільова функція.

Кнопки Додати, Змінити, Видалити
Ці кнопки дозволяють додавати, змінювати та видаляти обмеження моделі.

Кнопка Скинути
Щоб видалити всі налаштування Пошуку рішеннянатисніть кнопку Скинути– діалогове вікно очиститься.

Ця опція зручна при використанні різних варіантів обмежень. При збереженні параметрів моделі (кнопка Завантажити/ Зберегти,далі натисніть кнопку Зберегти) пропонується вибрати верхню комірку діапазону (стовпця), в який будуть поміщені: посилання на цільову функцію, посилання на комірки зі змінними, обмеження та параметри методів вирішення (доступні через кнопку Параметри). Перед збереженням переконайтеся, що цей діапазон не містить даних моделі.
Щоб завантажити збережені параметри, натисніть спочатку кнопку Завантажити/Зберегти, потім, у діалоговому вікні, що з'явилося, кнопку Завантажити, після чого вкажіть діапазон комірок, які містять збережені раніше налаштування (не можна вказувати лише одну верхню комірку). Натисніть OK. Підтвердьте скидання поточних значень параметрів завдання та їх заміну на нові.

Точність
При створенні моделі дослідник спочатку має деяку оцінку діапазонів варіювання цільової функції та змінних. Беручи до уваги обчислень у MS EXCEL, рекомендується, щоб ці діапазони варіювання були значно вищими за точність обчислення (вона зазвичай встановлюється від 0,001 до 0,000001). Як правило, дані в моделі нормують так, щоб діапазони варіювання цільової функції та змінних були в межах 0,1 – 100 000. Звичайно, все залежить від конкретної моделі, але якщо ваші змінні змінюються більш ніж на 5-6 порядків, то, можливо, слід "загрубити" модель, наприклад, за допомогою операції логарифмування.

Користувачі Excel давно та успішно застосовують програму для вирішення різних типів завдань у різних галузях.

Excel – це найпопулярніша програма у кожному офісі у всьому світі. Її можливості дозволяють швидко знаходити ефективні рішення в різних сферах діяльності. Програма здатна вирішувати різноманітні завдання: фінансові, економічні, математичні, логічні, оптимізаційні та багато інших. Для наочності ми кожне з описаних вище розв'язків задач в Excel і приклади його виконання.

Вирішення задач оптимізації в Excel

Оптимізаційні моделі застосовуються в економічній та технічній сфері. Їхня мета – підібрати збалансоване рішення, оптимальне в конкретних умовах (кількість продажів для отримання певної виручки, найкраще меню, число рейсів тощо).

В Excel для розв'язання задач оптимізації використовуються такі команди:

Для вирішення найпростіших завдань застосовується команда "Підбір параметра". Найскладніших – «Диспетчер сценаріїв». Розглянемо приклад розв'язання оптимізаційної задачі за допомогою надбудови «Пошук розв'язків».

Умови. Фірма виготовляє кілька сортів йогурту. Умовно – «1», «2» та «3». Реалізувавши 100 баночок йогурту «1», підприємство отримує 200 рублів. "2" - 250 рублів. "3" - 300 рублів. Збут, налагоджений, але кількість наявної сировини обмежена. Потрібно знайти, який йогурт і в якому обсязі необхідно робити, щоб отримати максимальний прибуток від продажу.

Відомі дані (в т.ч. норми витрати сировини) занесемо до таблиці:

З цих даних складемо робочу таблицю:

1. Кількість виробів нам поки що невідома. Це змінні.
2. У стовпець «Прибуток» внесено формули: =200*B11, =250*В12, =300*В13.
3. Витрата сировини обмежена (це обмеження). У комірки внесено формули: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») та =0*B11+8*B12+6*B13 («цукор»). Тобто норму витрати ми помножили на кількість.
4. Ціль – знайти максимально можливий прибуток. Це осередок С14.

Активізуємо команду «Пошук рішення» та вносимо параметри.

Після натискання кнопки "Виконати" програма видає своє рішення.

Оптимальний варіант - сконцентруватися на випуску йогурту "3" та "1". Йогурт "2" виробляти не варто.



Вирішення фінансових завдань в Excel

Найчастіше для цього застосовуються фінансові функції. Розглянемо приклад.

Оформимо вихідні дані у вигляді таблиці:

Так як процентна ставка не змінюється протягом усього періоду, використовуємо функцію ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

Заповнення аргументів:

1. Ставка - 20% / 4, т.к. відсотки нараховуються щокварталу.
2. Кпер - 4 * 4 (загальний термін вкладу * Число періодів нарахування на рік).
3. Плт - 0. Нічого не пишемо, т.к. депозит не поповнюватиметься.
4. Тип – 0.
5. БС – сума, яку хочемо отримати наприкінці терміну вкладу.

Вкладнику необхідно вкласти ці гроші, тому результат є негативним.

Для перевірки правильності рішення скористаємося формулою: ПС = БС/(1+ставка) кпер. Підставимо значення: ПС = 400 000/(1 + 0,05) 16 = 183245.

Рішення економетрики в Excel

Для встановлення кількісних та якісних взаємозв'язків застосовуються математичні та статистичні методи та моделі.

Дано 2 діапазони значень:

Значення Х відіграватимуть роль факторної ознаки, Y – результативної. Завдання – визначити коефіцієнт кореляції.

Для вирішення цього завдання передбачена функція Корел (масив 1; масив 2).

Вирішення логічних завдань в Excel

У табличному процесорі є вбудовані логічні функції. Будь-яка з них повинна містити хоча б один оператор порівняння, який визначить відношення між елементами (=, >, =,

Учні складали залік. Кожен із них отримав позначку. Якщо більше 4 балів – залік зданий. Менш – не зданий.

1. Ставимо курсор у комірку С1. Натискаємо піктограму функцій. Вибираємо «ЯКЩО».
2. Заповнюємо аргументи. Логічне вираз – B1>=4. Це умова, у якому логічне значення – ІСТИНА.
3. Якщо ІСТИНА – «Залік здав». Брехня - «Залік не здав».

Вирішення математичних завдань в Excel

Засобами програми можна вирішувати як найпростіші математичні завдання, і складніші (операції з функціями, матрицями, лінійними рівняннями тощо.).

Умова навчальної задачі. Знайти зворотну матрицю для матриці А.

1. Робимо таблицю із значеннями матриці А.
2. Виділяємо цьому ж аркуші область для зворотної матриці.
3. Натискаємо кнопку "Вставити функцію". Категорія - "Математичні". Тип - "МОБР".
4. У полі аргументу "Масив" вписуємо діапазон матриці А.
5. Натискаємо одночасно Shift+Ctrl+Enter – це обов'язкова умова для введення масивів.

Можливості Excel не безмежні. Але безліч завдань програмі «під силу». Тим більше тут не описані можливості, які можна розширити за допомогою макросів і налаштувань користувача.

Значна частина завдань, які вирішуються за допомогою електронних таблиць, припускають, що для виявлення потрібного результату користувач вже має хоч якісь вихідні дані. Однак Exсel 2010 має у своєму розпорядженні необхідні інструменти, за допомогою яких можна вирішити це завдання навпаки - підібрати потрібні дані, щоб отримати необхідний результат.

«Пошук рішення» є одним з таких інструментів, максимально зручних для «завдань оптимізації». І якщо раніше вам ще не доводилося його використовувати, то зараз саме час виправити це.

Отже – починаємо з встановлення даної надбудови (оскільки самостійно вона не з'явиться). На щастя, зараз зробити це можна досить просто і швидко – відкриваємо меню «Сервіс», а вже в ньому «Надбудови»

Залишиться тільки у графі «Управління» вказати «Надбудови Excel», а потім натиснути кнопку «Перейти».

Після цього нескладного дії кнопка активації «Пошуку рішення» буде відображатися в «Дані». Як і показано на зображенні

Давайте розглянемо, як правильно використовується пошук рішень Excel 2010, на кількох простих прикладах.

Приклад перший .

Припустимо, що ви займаєте посаду начальника великого відділу виробництва та необхідно правильно розподілити премії співробітникам. Допустимо, загальна сума премій становить 100 000 рублів, і необхідно, щоб премії були пропорційні до окладів.

Тобто зараз нам необхідно підібрати правильний коефіцієнт пропорційності, щоб визначити розмір премії щодо окладу.

Насамперед необхідно швидко скласти (якщо її ще немає) таблицю, де зберігаються вихідні формули та дані, згідно з якими і можна буде отримати бажаний результат. Для нас цей результат – сумарний розмір премії. А зараз увага – цільовий осередок С8 повинен бути за допомогою формул пов'язаний з шуканим змінним осередком під адресою Е2. Це критично. У прикладі ми пов'язуємо їх, використовуючи проміжні формули, які відповідають за вирахування премії кожному співробітнику (С2:С7).

Тепер можна активувати пошук рішень. Відкриється нове віконце, в якому необхідно вказати необхідні параметри.

Під « 1 » позначено наш цільовий осередок. Вона може бути лише одна.

« 2 - це можливі варіанти оптимізації. Усього можна вибрати "Максимальне", "Мінімальне" або "Конкретне" можливі значення. І якщо вам необхідно саме конкретне значення, його потрібно вказати у відповідній графі.

« 3 » — змінних осередків може бути кілька (цілий діапазон або окремо вказані адреси). Адже саме з ними і працюватиме Excel, перебираючи варіанти так, щоб вийшло значення, задане в цільовому осередку.

« 4 » — Якщо потрібно встановити обмеження, то варто скористатися кнопкою «Додати», але ми це розглянемо трохи пізніше.

« 5 » — кнопка переходу до інтерактивних обчислень на основі заданої програми.

Але тепер повернемося до можливості змінювати завдання, скориставшись кнопкою «Додати». Даний етап є досить відповідальним (не менш ніж побудова формул), оскільки саме обмеження дають змогу отримати правильний результат на виході. Тут все зроблено максимально зручно, тому задати їх ви зможете не тільки для всього діапазону відразу, але і для певних осередків.

Для цього можна використовувати низку певних (і знайомих всім користувачам Excel 2010) знаків «=», «>=», «<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

Але у прикладі обмеження може лише одне – позитивний коефіцієнт. Задати його, звичайно, можна декількома способами - або використовуючи "Додати" (що називають "явно вказати обмеження"), або просто відзначити функцію "Зробити змінні без обмежень невід'ємними". Це можна зробити в надбудові «Пошук рішення», натиснувши кнопку «Параметри».

До речі, після підтвердження параметрів та запуску програми (кнопка «Виконати»), ви зможете у таблиці переглянути отриманий результат. Тоді програма продемонструє віконце «результатів пошуку».

Якщо продемонстрований результат повністю підходить вам, тоді залишиться тільки знову підтвердити його (кнопочка «ОК»), що зафіксує результат у вашій таблиці. Якщо ж щось у розрахунках вас не влаштовує, необхідно скасувати результат (кнопочка «Скасувати»), повернутися до попереднього стану нашої таблиці і виправити допущені помилки.

Правильне вирішення завдання прикладу має вийти таким

Дуже важливо — щоб отримати правильний результат навіть за найменшої зміни вихідних даних, необхідно перезапустити «Пошук рішень».

Щоб детальніше поглянути на те, як діє дана програма, давайте розберемо ще один приклад.

Допустимо, ви є власником великого меблевого підприємства і необхідно налагодити виробництво таким чином, щоб отримати максимально можливий прибуток. Ви робите тільки книжкові полиці, при цьому всього двох моделей – «А» та «В», виробництво яких обмежується виключно наявністю (або відсутністю) високоякісних дощок, а також машинним часом (обробка на верстаті).

Модель "А" вимагає 3 м 3 дощок, а модель "В" - на 1 м 3 більше (тобто - 4). Від своїх постачальників ви за тиждень отримуєте максимум 1700 м3 дощок. При цьому модель А створюється за 12 хвилин роботи верстата, а В - за 30 хвилин. Усього на тиждень верстат може працювати не більше 160 годин.

Питання - скільки всього виробів (і якої моделі), повинна випускати фірма за тиждень, щоб отримати максимально можливий прибуток, якщо поличка А дає 60 рублів прибутку, а В - 120?

Оскільки порядок дії відомий, то починаємо створювати необхідну нам таблицю з даними та формулами. Розташування осередків, як і раніше, ви можете встановити на власний розсуд. Або ж скористатися нашим

Будь-яким зручним способом запускаємо наш «Пошук рішень», вводимо дані, виконуємо налаштування.

Отже, розглянемо те, що маємо. У цільовому осередку F7 міститься формула, яка розрахує прибуток. Параметр оптимізації встановлюємо максимум. Серед змінних осередків у нас значиться «F3: G3». Обмеження – усі виявлені значення мають бути цілими числами, невід'ємними, загальна кількість витраченого машинного часу не перевищує позначку 160 (наша комірка D9), кількість сировини не перевищує 1700 (комірка D8).

Звичайно, в цьому випадку можна було не вказувати адреси осередків, а безпосередньо прописати необхідні цифрові значення, проте якщо використовувати адреси, то зміни обмежень можна буде проводити і в таблиці, що допоможе розраховувати прибуток цього підприємства в майбутньому, змінюючи вихідні дані.

Активуємо програму і вона готує рішення.

Втім, це не єдине рішення і у вас може вискочити інший результат. Це може статися навіть у тому випадку, якщо всі дані були вказані правильно і помилок у формулах теж не було

Так. Це може статися навіть у тому випадку, якщо ми сказали програмі шукати цілечисло. І якщо це раптом сталося, необхідно просто провести додаткове налаштування «Пошуку рішень». Відкриваємо вікно «Пошук рішень» і входимо в «Параметри».

Наш верхній параметр відповідає за точність. Чим він менший, тим вища точність і в нашому випадку це значно підвищує шанси отримати ціле число. Другий параметр («Ігнорувати цілі численні обмеження») і дає відповідь на питання, як ми змогли отримати таку відповідь з тим, що в запиті явно вказали ціле число. «Пошук рішень» просто проігнорував це обмеження через те, що так йому сказали розширені налаштування.

Тож будьте гранично уважні у майбутньому.

Третій і, мабуть, останній приклад. Спробуємо мінімізувати витрати транспортної компанії, використовуючи пошук рішень в Excel 2010.

Отже, будівельна компанія пропонує замовлення на перевезення піску, який береться від 3 постачальників (кар'єрів). Його необхідно доставити 5 різним споживачам (якими є будівельні майданчики). Вартість доставки вантажу включена в собівартість об'єкта, тому наше завдання забезпечити доставку вантажу на будмайданчики з мінімальними витратами.

Ми маємо – запас піску в кар'єрі, потребу будмайданчиків у піску, витрати на транспортування «постачальник-споживач».

Необхідно знайти схему оптимального перевезення вантажу (куди й звідки), коли загальна витрата на перевезення була б мінімальною.

Сірі осередки нашої таблиці містять формули суми по стовпцях та рядках, а цільова осередок – формула для загального підрахунку витрати на доставку вантажу. Запускаємо наш «Пошук рішення» та вносимо необхідні налаштування

Після цього приступаємо до пошуку розв'язання цього завдання

Втім, не забуватимемо, що досить часто транспортні завдання можуть бути ускладнені деякими додатковими обмежувачами. Допустимо, виникло ускладнення на дорозі і тепер з кар'єру 2 просто технічно неможливо доставити вантаж на будмайданчик 3. Щоб врахувати це, просто дописати додаткове обмеження «$D$13=0». І якщо тепер запустити програму, то результат буде іншим

Насамкінець залишилося сказати лише про вибір методу рішення. І якщо завдання дійсно дуже складне, то щоб отримати необхідний результат, швидше за все, знадобиться підібрати необхідний метод розв'язання.

Ось і все з цього питання.

Ми виконали пошук рішень в Excel 2010 для вирішення складних завдань.

Більшість завдань, розв'язуваних за допомогою електронної таблиці, передбачають знаходження шуканого результату за відомими вихідними даними. Але в Excel є інструменти, що дозволяють вирішити і обернену задачу: підібрати вихідні дані для отримання бажаного результату.

Одним із таких інструментів єПошук рішення, який особливо зручний рішення так званих " завдань оптимізації " .

Якщо Ви раніше не використовувалиПошук рішення, то Вам знадобиться встановити відповідну надбудову.

Зробити це можна так:

для версій старших за Excel 2007 через командуменю Сервіс -> Надбудови;

починаючи з Excel 2007 через діалогове вікноПараметри Excel

Починаючи з версії Excel 2007кнопка для запускуПошуку рішенняз'явиться на вкладці Дані.

У версіях до Excel 2007 аналогічна команда з'явиться у менюСервіс

Розберемо порядок роботи Пошуку рішенняна найпростішому прикладі.

Приклад 1. Розподіл премії

Припустимо, що Ви начальник виробничого відділу і Ви маєте по-чесному розподілити премію в сумі 100 000 руб. між співробітниками відділу пропорційно до їх посадових окладів. Іншими словами, Вам потрібно підібрати коефіцієнт пропорційності для обчислення розміру премії по окладу.

Насамперед створюємо таблицю з вихідними даними та формулами, за допомогою яких повинен бути отриманий результат. У разі результат - це сумарна величина премії. Дуже важливо, щоб цільова комірка (С8) за допомогою формул була пов'язана з шуканою коміркою, що змінюється (Е2). У прикладі вони пов'язані через проміжні формули, що обчислюють розмір премії кожного співробітника (С2:С7).

Тепер запускаємо Пошук рішенняі в діалоговому вікні, що відкрилося, встановлюємо необхідні параметри. Зовнішній вигляддіалогових вікон у різних версіях дещо різниться:

Починаючи з Excel 2010

До Excel 2010

Після натискання кнопкиЗнайти рішення (Виконати)Ви вже можете бачити у таблиці отриманий результат. При цьому на екрані з'являється діалогове вікноРезультати пошуку рішення.

Починаючи з Excel 2010

До Excel 2010

Якщо результат, який Ви бачите у таблиці Вас влаштовує, то у діалоговому вікні Результати пошуку рішеннянатискаєте ОКта фіксуєте результат у таблиці. Якщо ж результат Вас не влаштував, то натискаєте Скасуваннята повертаєтеся до попереднього стану таблиці.

Вирішення даної задачі виглядає так

Важливо:за будь-яких змін вихідних даних для отримання нового результату Пошук рішеннядоведеться запускати знову.

Розберемо ще одне завдання оптимізації (отримання максимального прибутку)

Приклад 2. Меблеве виробництво (максимізація прибутку)

Фірма виробляє дві моделі А і збірних книжкових полиць.

Їх виробництво обмежене наявністю сировини (високоякісних дощок) та часом машинної обробки.

Для кожного виробу моделі А потрібно 3 м ²дощок, а для виробу моделі В – 4 м². Фірма може отримати від своїх постачальників до 1700 м² дощок на тиждень.

Для кожного виробу моделі А потрібно 12 хв. машинного часу., а для виробу моделі В – 30 хв. на тиждень можна використовувати 160 годин машинного часу.

Скільки виробів кожної моделі слід випускати фірмі на тиждень задля досягнення максимального прибутку, якщо кожен виріб моделі А приносить 60 руб. прибутку, а кожен виріб моделі - 120 руб. прибутку?

Порядок дій нам уже відомий.

Спочатку створюємо таблиці з вихідними даними та формулами. Розташування осередків на аркуші може бути абсолютно довільним, таким як зручне автору. Наприклад, як на малюнку

Запускаємо Пошук рішеннята в діалоговому вікні встановлюємо необхідні параметри

1. Цільовий осередок B12 містить формулу для розрахунку прибутку
2. Параметр оптимізації – максимум
3. Змінювані осередки B9: C9
4. Обмеження: знайдені значення мають бути цілими, невід'ємними; загальна кількість машинного часу не повинна перевищувати 160 годин (посилання на комірку D16); загальна кількість сировини не повинна перевищувати 1700 м2 (посилання на комірку D15). Тут замість посилань на осередки D15 і D16 можна було вказати числа, але при використанні посилань будь-які зміни обмежень можна проводити прямо в таблиці
5. Натискаємо кнопку Знайти рішення (Виконати)і після підтвердження отримуємо результат

Але навіть якщо Ви правильно створили формули та задали обмеження, результат може виявитися несподіваним. Наприклад, при вирішенні цього завдання Ви можете побачити такий результат:

І це незважаючи на те, що було встановлено обмеження ціле. У таких випадках можна спробувати налаштувати параметри Пошуку рішення. Для цього у вікні Пошук рішеннянатискаємо кнопку Параметриі потрапляємо до однойменного діалогового вікна

Перший із виділених параметрів відповідає за точність обчислень. Зменшуючи його, можна досягти більш точного результату, у нашому випадку – цілих значень. Другий із виділених параметрів (доступний, починаючи з версії Excel 2010) дає відповідь на запитання: як взагалі могли вийти дробові результати при обмеженніціле? Виявляється Пошук рішенняце обмеження просто проігнорував відповідно до встановленого прапорця.

Приклад 3. Транспортне завдання (мінімізація витрат)

На замовлення будівельної компанії пісок перевозиться від трьох постачальників (кар'єрів) п'ятьом споживачам. будівельним майданчикам). Вартість на доставку включається до собівартості об'єкта, тому будівельна компанія зацікавлена ​​забезпечити потреби своїх будмайданчиків у піску найдешевшим способом.

Дано: запаси піску на кар'єрах; потреби у піску будмайданчиків; витрати на транспортування між кожною парою "постачальник-споживач".

Потрібно знайти схему оптимальних перевезень задоволення потреб (звідки й куди), коли загальні витрати на транспортування були б мінімальними.

Приклад розташування осередків з вихідними даними та обмеженнями, потрібних осередків та цільового осередку показаний на малюнку

У сірих осередках формули суми по рядках і стовпцях, а в цільовому осередку формула для підрахунку загальних витрат на транспортування.

Запускаємо Пошук рішення та встановлюємо необхідні параметри (див. рисунок)

Натискаємо Знайти рішення (Виконати)і отримуємо результат, зображений нижче

На закінчення пропоную спробувати свої сили у застосуванні Пошуку рішенняі вирішити з його допомогою старовинне завдання:

Селянин на ринку за 100 рублів купив 100 голів худоби. Бик коштує 10 рублів, корова 5 рублів, теля 50 копійок. Скільки биків, корів та телят купив селянин?

Уміння вирішувати системи рівнянь часто може принести користь у навчанні, а й у практиці. У той самий час, далеко ще не кожен користувач ПК знає, що у Екселе існує власні варіанти рішень лінійних рівнянь. Давайте дізнаємося, як із застосуванням інструментарію цього табличного процесора виконати це завдання різними способами.

Спосіб 1: матричний метод

Найпоширеніший спосіб розв'язання системи лінійних рівнянь інструментами Excel – це застосування матричного методу. Він полягає у побудові матриці з коефіцієнтів виразів, а потім у створенні зворотної матриці. Спробуємо використовувати цей метод для вирішення наступної системи рівнянь:

14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21

1. Заповнюємо матрицю числами, що є коефіцієнтами рівняння. Дані числа повинні розташовуватись послідовно по порядку з урахуванням розташування кожного кореня, якому вони відповідають. Якщо у якомусь вираженні одне з коренів відсутня, то цьому випадку коефіцієнт вважається рівним нулю. Якщо коефіцієнт не позначений у рівнянні, але відповідний корінь є, то вважається, що коефіцієнт дорівнює 1 . Позначаємо отриману таблицю як вектор A.



2. Окремо записуємо значення після знаку "рівно". Позначаємо їх загальним найменуванням як вектор B.



3. Тепер для знаходження коренів рівняння, перш за все, нам потрібно відшукати матрицю, обернену до існуючої. На щастя, Ексель є спеціальний оператор, який призначений для вирішення цього завдання. Називається він МОБР. Він має досить простий синтаксис:

   МОБР(масив)

   Аргумент «Масив»- Це, власне, адреса вихідної таблиці.

   Отже, виділяємо на аркуші область порожніх осередків, яка за розміром дорівнює діапазону вихідної матриці. Клацаємо по кнопці "Вставити функцію", розташовану біля рядка формул.



4. Виконується запуск Майстри функцій. Переходимо до категорії «Математичні». У списку, що представився, шукаємо найменування «МОБР». Після того, як воно знайдено, виділяємо його і тиснемо на кнопку "OK".



5. МОБР. Воно за кількістю аргументів має лише одне поле – «Масив». Тут необхідно вказати адресу нашої таблиці. Для цього встановлюємо курсор у полі. Потім затискаємо ліву кнопку миші і виділяємо область на аркуші, де знаходиться матриця. Як бачимо, дані про координати розміщення автоматично заносяться до поля вікна. Після того, як ця задача виконана, найбільш очевидним було б натиснути кнопку "OK"але не варто поспішати. Справа в тому, що натискання на цю кнопку є рівнозначним застосуванню команди Enter. Але при роботі з масивами після завершення введення формули слід не натискати на кнопку Enter, а зробити набір клавіш Ctrl+Shift+Enter. Виконуємо цю операцію.



6. Отже, після цього програма здійснює обчислення і на виході в попередньо виділеній області ми маємо матрицю, обернену до цієї.



7. Тепер нам потрібно буде помножити зворотну матрицю на матрицю Bяка складається з одного стовпця значень, розташованих після знака «рівно»у виразах. Для множення таблиць в Екселі також є окрема функція, яка називається МУМНІЖ. Цей оператор має наступний синтаксис:

   МУМНОЖ(Массив1;Массив2)

   Виділяємо діапазон, що у нашому випадку складається з чотирьох осередків. Далі знову запускаємо Майстер функцій, натиснувши значок "Вставити функцію".



8. у категорії «Математичні», що запустився Майстри функцій, виділяємо найменування «МУМНІЖ»і тиснемо на кнопку "OK".



9. Активується вікно аргументів функції МУМНІЖ. У полі «Масив1»заносимо координати нашої зворотної матриці. Для цього, як і минулого разу, встановлюємо курсор у полі та із затиснутою лівою кнопкою миші виділяємо курсором відповідну таблицю. Аналогічну дію проводимо для внесення координат у поле «Масив2», тільки цього разу виділяємо значення колонки B. Після того, як вищезазначені дії проведені, знову не поспішаємо натискати на кнопку "OK"або клавішу Enter, а набираємо комбінацію клавіш Ctrl+Shift+Enter.



10. Після цієї дії в попередньо виділеному осередку відобразяться коріння рівняння: X1, X2, X3і X4. Вони будуть розташовані послідовно. Таким чином, можна сказати, що ми вирішили цю систему. Для того, щоб перевірити правильність рішення, достатньо підставити у вихідну систему виразів дані відповіді замість відповідного коріння. Якщо рівність буде дотримано, це означає, що представлена ​​система рівнянь вирішена правильно.



Спосіб 2: вибір параметрів

Другий відомий спосіб розв'язання системи рівнянь в Екселі – це застосування методу вибору параметрів. Суть цього методу полягає у пошуку від зворотного. Тобто, ґрунтуючись на відомому результаті, ми робимо пошук невідомого аргументу. Давайте для прикладу використовуємо квадратне рівняння






Цей результат також можна перевірити, підставивши дане значення у вираз замість значення x.

Спосіб 3: метод Крамера

Тепер спробуємо розв'язати систему рівнянь методом Крамера. Для прикладу візьмемо ту саму систему, яку використовували в Способі 1:

14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21


1. Як і в першому способі, складаємо матрицю Aз коефіцієнтів рівнянь та таблицю Bіз значень, які стоять після знака «рівно».



2. Далі робимо ще чотири таблиці. Кожна з них є копією матриці AТільки у цих копій по черзі один стовпець замінений на таблицю B. У першій таблиці це перший стовпець, у другої таблиці другий і т.д.



3. Тепер нам потрібно вирахувати визначники для всіх цих таблиць. Система рівнянь матиме рішення лише тому випадку, якщо всі визначники матимуть значення, відмінне від нуля. Для розрахунку цього значення Екселе знову є окрема функція – МОПРЕД. Синтаксис цього оператора наступний:

   МОПРЕД(масив)

   Таким чином, як і у функції МОБР, єдиним аргументом є посилання на оброблювану таблицю.

   Отже, виділяємо комірку, в якій виводитиметься визначник першої матриці. Потім тиснемо на знайому за попередніми способами кнопку "Вставити функцію".



4. Активується вікно Майстри функцій. Переходимо до категорії «Математичні»і серед списку операторів виділяємо там найменування «МОПРЕД». Після цього тиснемо на кнопку "OK".



5. Запускається вікно аргументів функції МОПРЕД. Як бачимо, воно має лише одне поле – «Масив». У це поле вписуємо адресу першої перетвореної матриці. Для цього встановлюємо курсор у полі, а потім виділяємо матричний діапазон. Після цього тиснемо на кнопку "OK". Дана функція виводить результат в одну комірку, а не масивом, тому для отримання розрахунку не потрібно вдаватися до натискання клавіш. Ctrl+Shift+Enter.



6. Функція здійснює підрахунок результату і виводить його в заздалегідь виділену комірку. Як бачимо, у нашому випадку визначник дорівнює -740 , тобто, не дорівнює нулю, що нам підходить.



7. Аналогічним чином проводимо підрахунок визначників для решти трьох таблиць.



8. На завершальному етапі робимо підрахунок визначника первинної матриці. Процедура відбувається за тим самим алгоритмом. Як бачимо, визначник первинної таблиці теж відмінний від нуля, а отже, матриця вважається невиродженою, тобто система рівнянь має розв'язання.



9. Тепер настав час знайти коріння рівняння. Корінь рівняння дорівнюватиме відношенню визначника відповідної перетвореної матриці на визначник первинної таблиці. Таким чином, поділивши по черзі всі чотири визначники перетворених матриць на число -148 , Яке є визначником початкової таблиці, ми отримаємо чотири корені. Як бачимо, вони рівні значенням 5 , 14 , 8 і 15 . Таким чином, вони точно збігаються з корінням, яке ми знайшли, використовуючи зворотну матрицю в способі 1, що підтверджує правильність розв'язання системи рівнянь.



Спосіб 4: метод Гауса

Вирішити систему рівнянь можна також, застосувавши метод Гаусса. Для прикладу візьмемо більше просту системурівнянь із трьох невідомих:

14x1+2x2+8x3=110
7x1-3x2+5x3=32
5x1+x2-2x3=17


1. Знову послідовно записуємо коефіцієнти до таблиці A, а вільні члени, розташовані після знаку «рівно»- До таблиці B. Але цього разу зблизимо обидві таблиці, оскільки це знадобиться нам для роботи надалі. Важливою умовоює те, щоб у першому осередку матриці Aзначення було відмінним від нуля. Інакше слід переставити рядки місцями.



2. Копіюємо перший рядок двох з'єднаних матриць в рядок нижче (для наочності можна пропустити один рядок). У першу комірку, яка розташована в рядку ще нижче попереднього, вводимо таку формулу:

   B8:E8-$B$7:$E$7*(B8/$B$7)

   Якщо ви розташували матриці по-іншому, то й адреси комірок формули у вас будуть мати інше значення, але ви зможете вирахувати їх, зіставивши з тими формулами та зображеннями, що наводяться тут.

   Після того, як формула введена, виділіть весь ряд осередків та натисніть комбінацію клавіш Ctrl+Shift+Enter. До ряду буде застосовано формулу масиву і він буде заповнений значеннями. Таким чином ми зробили віднімання з другого рядка першого, помноженого на відношення перших коефіцієнтів двох перших виразів системи.



3. Після цього копіюємо отриманий рядок і вставляємо його в рядок нижче.



4. Виділяємо два перші рядки після пропущеного рядка. Тиснемо на кнопку «Копіювати», яка розташована на стрічці у вкладці «Головна».



5. Пропускаємо рядок після останнього запису на аркуші. Виділяємо перший осередок у наступному рядку. Клацаємо правою кнопкою миші. У контекстному меню, що відкрилося, наводимо курсор на пункт «Спеціальна вставка». У додатковому списку, що запустився, вибираємо позицію «Значення».



6. У наступний рядок вводимо формулу масиву. У ній проводиться віднімання з третього рядка попередньої групи даних другого рядка, помноженої на відношення другого коефіцієнта третього та другого рядка. У нашому випадку формула матиме такий вигляд:

   B13:E13-$B$12:$E$12*(C13/$C$12)

   Після введення формули виділяємо весь ряд та застосовуємо поєднання клавіш Ctrl+Shift+Enter.



7. Тепер слід виконати зворотну прогін за методом Гауса. Пропускаємо три рядки від останнього запису. У четвертому рядку вводимо формулу масиву:

   Таким чином, ми ділимо останній розрахований нами рядок на його третій коефіцієнт. Після того, як набрали формулу, виділяємо весь рядок і тиснемо клавіші Ctrl+Shift+Enter.



8. Піднімаємося на рядок вгору і вводимо до неї таку формулу масиву:

   =(B16:E16-B21:E21*D16)/C16

   Тиснемо звичне вже нам поєднання клавіш для застосування формули масиву.



9. Піднімаємось ще на один рядок вище. У неї вводимо формулу масиву наступного виду:

   =(B15:E15-B20:E20*C15-B21:E21*D15)/B15

   Знову виділяємо весь рядок та застосовуємо поєднання клавіш Ctrl+Shift+Enter.



10. Тепер дивимося на числа, які вийшли в останньому стовпці останнього блоку рядків, розрахованого раніше. Саме ці числа ( 4 , 7 і 5 ) будуть корінням даної системи рівнянь. Перевірити це можна, підставивши їх замість значень X1, X2і X3у вирази.



Як бачимо, в Екселі систему рівнянь можна вирішити цілим рядом способів, кожен з яких має власні переваги та недоліки. Але всі ці методи можна умовно розділити на великі групи: матричні і із застосуванням інструменту підбору параметрів. У деяких випадках не завжди матричні методипідходять для вирішення задачі. Зокрема, коли визначник матриці дорівнює нулю. В інших випадках користувач сам вільний вирішувати, який варіант він вважає більш зручним для себе.