Вирішення системи рівнянь у Microsoft Excel. Рішення рівнянь в Excel Як знайти рішення в 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 можна використовувати різні способи виконання цього завдання. Про спосіб рішення шляхом підбору параметра йтиметься у цій статті.
Знаходження коріння нелінійного рівняння з використанням засобу "Підбір параметра"зводиться у двох етапах:
- визначення приблизних меж відрізків та кількості коренів графічним методом;
- підбір кожному відрізку значення кореня, що задовольняє заданої точності обчислень.
Щоб оцінити зразкові межі відрізків і кількість коренів, можна використовувати табличне завдання значень функції, тобто. задати кілька значень змінної та обчислити відповідні значення функції. Знову ж таки, для того, щоб можна було моделювати розрахунки для квадратних рівнянь із різними коефіцієнтами, крок табулювання краще задати в окремому осередку. Початкове значення змінної можна буде змінювати шляхом введення в комірку « А6». Для обчислення наступного значення в комірку "А7"введено формулу « = А6 + $B$4», тобто. використано абсолютне посилання на комірку з кроком табулювання.
Далі за допомогою маркера заповненняформується ряд формул для обчислення наступних значень змінної, наведеному прикладі використовується 20 значень. Вводиться формула для обчислення значення функції (для прикладу, що розглядається, в комірку « О 6") і формується ряд аналогічних формул для інших осередків. У формулі використані абсолютні посилання на комірки з коефіцієнтами рівняння.
По побудованій таблиці будується точкова діаграма.
Якщо початкове значення Х і крок обрані невдало, і діаграмі немає перетинів з віссю абсцис, можна ввести інші значення і досягти потрібного результату. Можна було б знайти рішення вже на цьому кроці, але для цього знадобилося б набагато більше осередків і крок, що дорівнює заданій точності обчислень (0,001). Щоб не створювати громіздких таблиць, далі використовується "Підбір параметра"із групи "Прогноз"на вкладці «Дані». Попередньо необхідно виділити місце під початкові значення змінної (коренів у прикладі два) та відповідні значення функції. В якості " х1»вибирається перше із значень, що дають найближче до нуля значення функції (у прикладі 0,5). У осередокL6введено формулу для обчислення функції. У вікні підбору параметра необхідно вказати для якого осередку ( L6), яке значення ( 0 ) потрібно отримати, і в якому осередку для цього змінювати значення ( К6).
Для пошуку другого кореня необхідно ввести друге з значень, що дають найближче до нуля значення функції (у прикладі 9,5), і повторити вибір параметра для комірки L9(В комірку скопійовано формулу з комірки L6). 
Запропоноване оформлення коефіцієнтів функції окремі осередки дозволяє без зміни формул вирішувати інші подібні рівняння. 
Підбір параметра є й у попередніх версіях програми. Надбудова Excel «Пошук рішення» – це аналітичний інструмент, який дозволяє нам швидко та легко визначити, коли та який результат ми отримаємо за певних умов. Можливості інструменту пошуку рішення набагато вищі, ніж може надати "підбір параметра" в Excel.
Основні відмінності між пошуком рішення та підбором параметра:
- Вибір декількох параметрів в Excel.
- Накладення умов, що обмежують зміни в комірках, які містять змінні значення.
- Можливість використання у випадках, коли може бути багато рішень однієї задачи.
Приклади та завдання на пошук рішення в Excel
Розглянемо аналітичні можливості надбудови. Наприклад, Вам потрібно накопичити 14 000 $ за 10 років. Протягом 10-ти років ви хочете щороку відкладати на депозитний рахунок у банку по 1000 $ під 5% річних. Нижче на малюнку побудовано таблицю в Excel, за якою добре видно залишок накопичених коштів на кожен рік. Як видно за таких умов депозитного рахунку та внесків накопичення мети не буде досягнуто навіть через 10 років. При вирішенні цього завдання можна йти двома шляхами:- Знайти банк, який пропонує вищу відсоткову ставку за депозитами.
- Збільшити обсяг щорічних накопичувальних внесків на банківський рахунок.
Ми можемо змінювати змінні значення у осередках B1 і B2 так, щоб підібрати необхідні умови для накопичення необхідної суми грошей.
Надбудова «Пошук рішення» - дозволяє нам одночасно використовувати 2 ці варіанти, щоб швидко змоделювати найбільш оптимальні умови для досягнення поставленої мети. Для цього:
Як бачимо програма трохи збільшила відсоткову ставку та суму щорічних внесків.
Обмеження параметрів під час пошуку рішень
Припустимо, ви пішли в банк із цією таблицею, але банк відмовляється підняти Вам процентну ставку. У такому випадку нам потрібно дізнатися, наскільки нам доведеться збільшити суму щорічних вкладень. Ми повинні встановити обмеження на комірку з одним змінним значенням. Але перед початком зміните значення змінних осередках на вихідні: в B1 на 5%, а B2 на -1000$. А тепер робимо таке.
Користувачі Excel давно та успішно застосовують програму для вирішення різних типів завдань у різних галузях.
Excel – це найпопулярніша програма у кожному офісі у всьому світі. Її можливості дозволяють швидко знаходити ефективні рішення в різних сферах діяльності. Програма здатна вирішувати різноманітні завдання: фінансові, економічні, математичні, логічні, оптимізаційні та багато інших. Для наочності ми кожне з описаних вище розв'язків задач в Excel і приклади його виконання.
Вирішення задач оптимізації в Excel
Оптимізаційні моделі застосовуються в економічній та технічній сфері. Їхня мета – підібрати збалансоване рішення, оптимальне в конкретних умовах (кількість продажів для отримання певної виручки, найкраще меню, число рейсів тощо).
В Excel для розв'язання задач оптимізації використовуються такі команди:
Для вирішення найпростіших завдань застосовується команда "Підбір параметра". Найскладніших – «Диспетчер сценаріїв». Розглянемо приклад розв'язання оптимізаційної задачі за допомогою надбудови «Пошук розв'язків».
Умови. Фірма виготовляє кілька сортів йогурту. Умовно – «1», «2» та «3». Реалізувавши 100 баночок йогурту «1», підприємство отримує 200 рублів. "2" - 250 рублів. "3" - 300 рублів. Збут, налагоджений, але кількість наявної сировини обмежена. Потрібно знайти, який йогурт і в якому обсязі необхідно робити, щоб отримати максимальний прибуток від продажу.
Відомі дані (в т.ч. норми витрати сировини) занесемо до таблиці:
З цих даних складемо робочу таблицю:
- Кількість виробів нам поки що невідома. Це змінні.
- У стовпець «Прибуток» внесено формули: =200*B11, =250*В12, =300*В13.
- Витрата сировини обмежена (це обмеження). У комірки внесено формули: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») та =0*B11+8*B12+6*B13 («цукор»). Тобто норму витрати ми помножили на кількість.
- Ціль – знайти максимально можливий прибуток. Це осередок С14.
Активізуємо команду «Пошук рішення» та вносимо параметри.
Після натискання кнопки "Виконати" програма видає своє рішення.
Оптимальний варіант - сконцентруватися на випуску йогурту "3" та "1". Йогурт "2" виробляти не варто.
Вирішення фінансових завдань в Excel
Найчастіше для цього застосовуються фінансові функції. Розглянемо приклад.
Оформимо вихідні дані у вигляді таблиці:
Так як процентна ставка не змінюється протягом усього періоду, використовуємо функцію ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).
Заповнення аргументів:
- Ставка - 20% / 4, т.к. відсотки нараховуються щокварталу.
- Кпер - 4 * 4 (загальний термін вкладу * Число періодів нарахування на рік).
- Плт - 0. Нічого не пишемо, т.к. депозит не поповнюватиметься.
- Тип – 0.
- БС – сума, яку хочемо отримати наприкінці терміну вкладу.
Вкладнику необхідно вкласти ці гроші, тому результат є негативним.
Для перевірки правильності рішення скористаємося формулою: ПС = БС/(1+ставка) кпер. Підставимо значення: ПС = 400 000/(1 + 0,05) 16 = 183245.
Рішення економетрики в Excel
Для встановлення кількісних та якісних взаємозв'язків застосовуються математичні та статистичні методи та моделі.
Дано 2 діапазони значень:
Значення Х відіграватимуть роль факторної ознаки, Y – результативної. Завдання – визначити коефіцієнт кореляції.
Для вирішення цього завдання передбачена функція Корел (масив 1; масив 2).
Вирішення логічних завдань в Excel
У табличному процесорі є вбудовані логічні функції. Будь-яка з них повинна містити хоча б один оператор порівняння, який визначить відношення між елементами (=, >, =,
Учні складали залік. Кожен із них отримав позначку. Якщо більше 4 балів – залік зданий. Менш – не зданий.
- Ставимо курсор у комірку С1. Натискаємо піктограму функцій. Вибираємо «ЯКЩО».
- Заповнюємо аргументи. Логічне вираз – B1>=4. Це умова, у якому логічне значення – ІСТИНА.
- Якщо ІСТИНА – «Залік здав». Брехня - «Залік не здав».
Вирішення математичних завдань в Excel
Засобами програми можна вирішувати як найпростіші математичні завдання, і складніші (операції з функціями, матрицями, лінійними рівняннями тощо.).
Умова навчальної задачі. Знайти зворотну матрицю для матриці А.
- Робимо таблицю із значеннями матриці А.
- Виділяємо цьому ж аркуші область для зворотної матриці.
- Натискаємо кнопку "Вставити функцію". Категорія - "Математичні". Тип - "МОБР".
- У полі аргументу "Масив" вписуємо діапазон матриці А.
- Натискаємо одночасно Shift+Ctrl+Enter – це обов'язкова умова для введення масивів.
Можливості Excel не безмежні. Але безліч завдань програмі «під силу». Тим більше тут не описані можливості, які можна розширити за допомогою макросів і налаштувань користувача.
