3.5: Вступ до розв'язувача

Last updated
Save as PDF

Page ID: 66699

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $ $ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $$\newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\kernel}{\mathrm{null}\,}$ $ \newcommand{\range}{\mathrm{range}\,}$ $ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$ $ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$ $ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$ $ \newcommand{\Span}{\mathrm{span}}$ $\newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\kernel}{\mathrm{null}\,}$ $ \newcommand{\range}{\mathrm{range}\,}$ $ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$ $ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$ $ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$ $ \newcommand{\Span}{\mathrm{span}}$

Ми повинні очікувати, коли ми дивимося на операцію, яка багато використовується в діловому світі, що Excel матиме досить просту команду для досягнення результату. У розділі 1.5 ми представили Goal Seek, інструмент, який є частиною Excel, і який можна використовувати для вирішення рівняння для кореня з заданої початкової точки. В останньому розділі ми зазначили, що можна знайти кандидатів на локальний максимум або мінімум функції, знайшовши похідну та використовуючи Goal Seek, щоб знайти, де похідна дорівнює 0. У цьому розділі ми представляємо Solver, надбудову до Excel. Найпростіше думати про вирішувач як про більш потужну версію Goal Seek. Найпростіше буде пройти через використання Solver під час роботи з прикладом.

Приклад 3.5.1: Використовуйте розв'язувач для збору інформації про графік.

Малюнок$3.5.2.$ Відео презентація цього прикладу

Нехай$f(x)=9 x-x^2+7$ на проміжку$-1\le x \le 14\text{.}$

Рішення

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

$clipboard_e6d04c1db7d91ddc14146589a879f1b27.png$

Дивлячись на діаграму і картинку, ми бачимо, що вершина близька до$x=4.5\text{.}$ Ми також бачимо, що х-перехоплення близькі до$x=-0.5$ і$x=9.5\text{.}$

Далі ми хочемо переконатися, що Solver встановлений. Він повинен знаходитися в розділі Аналіз на вкладці Дані.

$clipboard_e6c78f8a89efe27013f1d681517a77bc8.png$

Якщо ви не знайдете його там, вам слід перейти до онлайн-довідки для Excel та шукати довідку щодо Solver. У розділі «Визначення та вирішення проблеми за допомогою розв'язувача» виберіть спочатку «Визначити та вирішити проблему», потім «Якщо ви не бачите розв'язувача в розділі Аналіз на вкладці Дані».

Спочатку ми хочемо використовувати Solver, щоб знайти корінь. Використовуючи той самий підхід, який ми використовували з Goal seek, ми хотіли б, щоб клітина B5 була встановлена рівною 0, змінивши значення комірки A5. (Оскільки ми встановили проблему, ми могли б використовувати розв'язувач, починаючи з будь-якої з комірок, які дають значення, бо$f(x)\text{.}$ я вибрав ту, яка має$f(x)$ найближчу до бажаного результату.)

$clipboard_ee035f84b999f39c7f5540a214141bf93.png$

Розв'язувач знаходить рішення за допомогою$x=-0.72015\text{.}$ Нам дається діалогове вікно, яке запитує, чи хочемо ми зберегти рішення рішення або відновити наше початкове значення.

$clipboard_e901b9a38bfc97ce4e7262459b00b7a1a.png$

Як і у випадку з Goal Seek, якщо ми знову використовуємо Solver, починаючи з$x$ ближче до 9, ми знайдемо рішення$x=9.72015\text{.}$

Однак причина, по якій ми представляємо Solver, полягає в тому, що він може робити речі, що було б складніше з Goal Seek. З графіка, і наших знань або парабол, ми знаємо, що графік має єдиний максимум. Щоб знайти максимум з Goal Seek, нам потрібно усвідомити, що максимум виникає, коли похідна дорівнює 0, визначити числову похідну, потім встановити похідну рівну 0. З Solver ми просто просимо його знайти максимум. Він знаходить вершину в$x=4.5\text{.}$

$clipboard_e3ba0ae909ed8261c4173c545f39cec4b.png$

Ми також хотіли б мати можливість знайти мінімум. З малюнка ми знаємо, що парабола, що вказує вниз, не має абсолютного мінімуму. Однак у бізнесі ми, як правило, стурбовані функціями, визначеними на кінцевому домені. Для цієї проблеми розглянемо лише інтервал$0\le x\le 15\text{.}$ Ми хочемо, щоб мінімум з'являвся в комірці B7, тому ми хочемо обмежити комірку A7. Якщо ми запускаємо Solver, потім натисніть кнопку додавання, ми отримаємо діалогове вікно для введення першого обмеження, що A7$\ge 0\text{.}$

$clipboard_eeafcee625f76215c0cf197f453a5e1dc.png$

Аналогічним чином додаємо обмеження, що A7 15 і запитуємо вирішувача мінімум.

$clipboard_e83e3ed3670aa6a97d128a8c538122c4a.png$

Так як ми почали пошук мінімуму на$x=0.5\text{,}$ Solver знаходить мінімум на$x=0\text{.}$ Це «локальний мінімум». Будь-яке$x$ значення в інтервалі, який знаходиться поруч, дає більш високе значення функції.

Ми також хотіли б знайти мінімум на іншому кінці інтервалу. Для цього корисно знати трохи математики за тим, що робить вирішувач. Розв'язувач використовує похідні від початкової точки, щоб визначитися з напрямком, який він повинен дивитися, і як далеко він повинен піти, щоб знайти наступне припущення для його відповіді. Це модифікація техніки під назвою метод Ньютона. Що стосується нашої картини, залежно від того, чи скажемо ми їй знайти максимальне, мінімальне або вказане значення, Solver намагається ковзати вгору або вниз по графіку, поки не знайде хорошого кандидата, який він дає нам як рішення. Він насправді шукає перший локальний максимум або мінімум, до якого він отримує. Він не шукає інших кандидатів. Так що якщо ми почали на$x=0.5$ це буде ковзати вліво, щоб знайти відповідь. Щоб знайти мінімум на іншому кінці інтервалу, нам потрібна відправна точка, де графік вже нахилений вниз вправо. Починаючи з роботи$x=8$ слід. Налаштовуємо розв'язувач.

$clipboard_e5034f2b01a571a193e90549bbf4975a1.png$

Розв'язувач виявляє, що крива мала мінімум при$x=15$ при$f(x)=-83\text{.}$

Нагадаємо, використання Solver на інтервалі, який$0\le x\le 15\text{,}$ ми знайшли, має корінь$x=9.72015\text{,}$ на максимумі at$x=4.5\text{,}$ і локальні$x=15\text{.}$ мінімуми в$x=0$ і Ми також знайшли значення$f(x)$ у всіх цих точках. Однак цей приклад був обраний тому, що ми могли отримати однакові результати з роботою, просто використовуючи властивості парабол. Таким чином, зараз ми хочемо поставити ті самі питання про проблему, яку ми не можемо вирішити алгебраїчно.

Приклад 3.5.3: Оманливий графік.

Малюнок$3.5.4.$ Відео презентація цього прикладу

Використовуйте розв'язувач для збору інформації, на інтервалі$0\le x\le 15\text{,}$ на графіку$f(x)=(x^3-4x^2+4x+3) e^{(-x/2)}\text{.}$

Рішення

Як завжди, почніть з погляду на графік.

$clipboard_e6e8e9ee09accadd37c09b51cb17357aa.png$

З графіка я очікую, що функція не має коренів на інтервалі. Він має локальні мінімуми близько 0, 2,5 та 10. Він має локальні максимуми близько 0,5, і 8. Мені потрібно буде додати обмеження, щоб знайти локальні мінімуми на кордоні. Щоб зробити мій робочий аркуш легким для читання, я додаю два додаткові стовпці для значень x та y цікавого пункту та заповніть здогадки.

$clipboard_e5ac9a70f61a20b2b5c47959de130e72f.png$

Після того, як я використовую Solver, я вважаю, що локальні мінімуми відбуваються в 0, 2.326 та 10, а локальні максимуми відбуваються на рівні 0.29115 та 7.3827. Максимальне значення для функції в інтервалі - 5,409, а мінімальне - 1,0149. Ми перевіряємо, що кінцеві точки,$x=0$ і$x=10\text{,}$ обидва локальні мінімуми.

$clipboard_e1502c9d64a8ef5fc6b877f4d8e0d79c4.png$

Цю функцію можна використовувати для ілюстрації обмеження нашого методу. Якщо ми графували функції з інтервалами з інтервалами розміру 1, а не 0.5, ми отримуємо іншу картину.

$clipboard_ef437f3df15ddcfc30e0c8927b0c8c9a4.png$

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

Попередження: У прикладі$3.5.3$ ми побачили, що покладатися на графік може нас обдурити. Зворотний бік - приклад, коли покладатися на Solver може нас обдурити. Раніше ми згадували, що Solver використовує варіант методу Ньютона для пошуку значень. У грубому вираженні він неодноразово знаходить лінійне наближення і ковзає вгору або вниз по цій лінії до бажаної відповіді. Якщо почати близько до відповіді, то це дуже ефективний метод знаходження числового рішення. Однак легко побудувати проблеми, де це призводить до тупика або до неправильної відповіді. Зокрема, метод має великі труднощі з проблемами, коли функція не диференційована або де вона має кілька вигинів. Розглянемо наступний приклад.

Приклад 3.5.5: Обман розв'язувача.

Малюнок$3.5.6.$ Відео презентація цього прикладу

Використовуйте розв'язувач для збору інформації, на інтервалі$-2\le x\le 2\text{,}$ на графіку

\[ f(x)=\begin{cases} -5x-3\amp x\lt 0\\ 4(x-1)^2 \amp x \gt 0\\ \end{cases}\text{.} \nonumber \]

Рішення

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

$clipboard_e8ad93c14939e2bbaeb177c3fe5214e32.png$

Досить легко помітити, що функція досягає максимуму 7 в$x=-2\text{,}$ і має корінь$x=-0.6\text{.}$ на цьому інтервалі функція не має мінімуму, але вона$x$ наближається до -3 при наближенні до 0 з негативної сторони. Якщо ми почнемо з$x=0.5$ і спробуємо ковзати вгору або вниз по кривій, ми йдемо в неправильному напрямку, щоб знайти корінь або мінімум. Щоб знайти максимум, нам також потрібно спуститися вниз, перш ніж ми зможемо піднятися до максимуму.

Коли ми дивимося на вирішувач, ми отримуємо неправильні, але очікувані результати. Функція не тільки не може бути диференційованою$x=0\text{,}$, у неї є стрибок там. Розв'язувач знаходить найближчий локальний максимум і мінімум. Для кореня він говорить нам, що він не може знайти здійсненне рішення.

$clipboard_e945e82e70b8ca637cb2edd4667b615f8.png$

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

Попередній перегляд речей майбутнього — Екстремума функцій двох змінних.

Є ще одна особливість Solver, до якої ми повернемося пізніше в курсі. Goal Seek запитав, яку клітинку слід змінити, щоб досягти бажаної мети. Розв'язувач дозволяє нам вказати кількість комірок, які ми можемо змінити. Це означає, що він буде працювати з функціями декількох змінних. Це дозволить нам пролити світло на один із «чорних ящиків», які ми використовували раніше в цьому курсі, здатність Excel знаходити лінію тренду або найкращу криву підгонки до набору даних.

Приклад 3.5.7: Використовуйте Solver, щоб знайти найкращу відповідну лінію до набору даних.

Малюнок$3.5.8.$ Відео презентація цього прикладу

Знайдіть найкращу відповідну лінію за наступними даними.

х	1	2	3	4	5
у	35	46	78	84	114

Рішення

Як ми вже згадували в розділі 1.4, коли нас просять знайти найкращу відповідну лінію, нас просять створити функцію прогнозування$\prediction(x)=A x+B\text{,}$ з$A$ і$B$ вибрати для мінімізації суми квадратів похибки між фактичними значеннями та прогнозованими значеннями. Ми будуємо робочий аркуш, який знаходить суму квадратних помилок. Ми починаємо з наших змінних, A і B, встановлених на 5.

$clipboard_e3aac3fb9cc6b42307f354b3009e5359c.png$

Ми просимо Solver мінімізувати D11, змінивши B2 і B3. Для порівняння ми просимо найкращу відповідну лінію Trendline за допомогою розсіювача.

$clipboard_e30b641b6ce2757deb8c034c21621ade6.png$

Ми бачимо, що отримуємо однакові відповіді, за умови дотримання правил округлення.

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

клас =»

Вправи: Введення в розв'язувач

У Вправі$3.5.1–3.5.7$ вам надається функція та інтервал, який вона визначається:

Складіть діаграму значень і побудуйте графік функції.
Знайдіть будь-які коріння для функції.
Знайти відносні максимуми і мінімуми для функції. (Не забудьте включити кінцеві точки.).
Знайти абсолютний максимум і мінімум функції на інтервалі.

Вправа 1:

$f(x)=x^2-10x+9\text{,}$на проміжку$0\le x\le 10\text{.}$

Відповідь

$clipboard_ef622367f32ad37a45737fed1ba820b6e.png$