Използване на булевата функция if примери. Функция на Microsoft Excel: намиране на решение

Решаване на нелинейни уравнения и системи"

Цел на работата: Проучване на възможностите на пакета Ms Excel 2007 при решаване на нелинейни уравнения и системи. Придобиване на умения за решаване на нелинейни уравнения и системи с помощта на пакета.

Упражнение 1. Намерете корените на полинома x 3 - 0,01x 2 - 0,7044x + 0,139104 = 0.

Първо, нека решим уравнението графично. Известно е, че графичното решение на уравнението f(x)=0 е пресечната точка на графиката на функцията f(x) с оста x, т.е. стойността на x, при която функцията изчезва.

Нека таблицираме нашия полином в интервала от -1 до 1 със стъпка 0,2. Резултатите от изчислението са показани на фиг., където формулата е въведена в клетка B2: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. Графиката показва, че функцията пресича оста x три пъти и тъй като полиномът от трета степен има не повече от три реални корена, е намерено графично решение на задачата. С други думи, корените бяха локализирани, т.е. определят се интервалите, на които са разположени корените на този полином: [-1,-0.8] и .

Сега можете да намерите корените на полином, като използвате метода на последователните приближения, като използвате командата Данни→Работа с данни→Анализ какво става →Избор на параметър.

След като въведете първоначалните приближения и стойностите на функцията, можете да преминете към командата Данни→Работа с данни→Анализ какво става →Избор на параметъри попълнете диалоговия прозорец по този начин.

В полето Поставете в клеткадава се препратка към клетката, в която е въведена формула, която изчислява стойността на лявата страна на уравнението (уравнението трябва да бъде написано така, че дясната му страна да не съдържа променлива). В полето Значениевъведете дясната страна на уравнението и в полето Промяна на стойностите на клеткитедава се препратка към клетката, запазена за променливата. Имайте предвид, че въвеждането на препратки към клетки в полетата на диалоговия прозорец Избор на параметрипо-удобно е не от клавиатурата, а като щракнете върху съответната клетка.

След натискане на бутона OK ще се появи диалоговият прозорец Резултат от избора на параметър със съобщение за успешното завършване на търсенето на решение, приблизителната стойност на корена ще бъде поставена в клетка A14.

Двата останали корена се намират по подобен начин. Резултатите от изчислението ще бъдат поставени в клетки A15 и A16.

Задача 2. Решете уравнението e х - (2x - 1) 2 = 0.

Нека локализираме корените на нелинейното уравнение.

За целта го представяме във формата f(x) = g(x) , т.е. e x = (2x - 1) 2 или f(x) = e x , g(x) = (2x - 1) 2 и решете графично.

Графичното решение на уравнението f(x) = g(x) ще бъде пресечната точка на правите f(x) и g(x).

Нека начертаем f(x) и g(x). За да направите това, в диапазона A3: A18 ще въведем стойностите на аргумента. В клетка B3 въвеждаме формула за изчисляване на стойностите на функцията f (x): = EXP (A3), а в C3 за изчисляване на g (x): = (2 * A3-1) ^ 2.

Резултати от изчисленията и начертаване на f(x) и g(x):

Графиката показва, че правите f(x) и g(x) се пресичат два пъти, т.е. това уравнение има две решения. Едно от тях е тривиално и може да се изчисли точно:

За втория можете да определите интервала на изолация на корена: 1,5< x < 2.

Сега можете да намерите корена на уравнението върху сегмента чрез метода на последователните приближения.

Нека въведем първоначалното приближение в клетката H17 = 1.5, а самото уравнение, по отношение на първоначалното приближение, в клетката I17 = EXP(H17) - (2*H17-1)^2.

и попълнете диалоговия прозорец Избор на параметри.

Резултатът от търсенето на решение ще бъде показан в клетка H17.

Упражнение3 . Решете системата от уравнения:

Преди да използваме описаните по-горе методи за решаване на системи от уравнения, ще намерим графично решение на тази система. Имайте предвид, че и двете уравнения на системата са дадени имплицитно и за да се начертаят графики, функции, съответстващи на тези уравнения, е необходимо да се решат дадените уравнения по отношение на променливата y.

За първото уравнение на системата имаме:

Нека намерим ODZ на получената функция:

Второто уравнение на тази система описва окръжност.

Фрагмент от работен лист на MS Excel с формули, които трябва да бъдат въведени в клетки, за да се начертаят линии, описани от уравненията на системата. Пресечните точки на изобразените прави представляват графично решение на системата от нелинейни уравнения.

Не е трудно да се види, че дадената система има две решения. Следователно процедурата за търсене на решения на системата трябва да се извърши два пъти, като предварително се определи интервалът на изолиране на корените по осите Ox и Oy. В нашия случай първият корен се намира в интервалите (-0.5;0) x и (0.5;1) y , а вторият - (0;0.5) x и (-0.5;-1) y . Да процедираме по следния начин. Въвеждаме началните стойности на променливите x и y, формулите, които показват уравненията на системата и целевата функция.

Сега ще използваме командата Data→Analysis→Search for Solutions два пъти, като попълним появилите се диалогови прозорци.

Сравнявайки полученото решение на системата с графичното, се уверяваме, че системата е решена правилно.

Задачи за самостоятелно решаване

Упражнение 1. Намерете корените на полином

Задача 2. Намерете решение на нелинейно уравнение.

Задача 3. Намерете решението на системата от нелинейни уравнения.

Както вече знаете, формулите в Microsoft Excelви позволяват да определите стойността на функция чрез нейните аргументи. Въпреки това може да възникне ситуация, когато стойността на функцията е известна и трябва да се намери аргументът (т.е. да се реши уравнението). Специална функция е предназначена за решаване на такива проблеми. Търсене на цел .

Търсене на параметри.

специална функция Търсене на цел ви позволява да определите параметъра (аргумента) на функцията, ако стойността му е известна. При избор на параметър стойността на влияещата клетка (параметър) се променя, докато формулата, която зависи от тази клетка, върне зададената стойност.

Помислете за процедурата за търсене на параметър, като използвате прост пример: решаваме уравнението 10 * x - 10 / x = 15 . Тук параметър (аргумент) - х . Нека това да е клетка A3 . Нека въведем в тази клетка всяко число, което се намира в областта на дефиницията на функцията (в нашия пример това число не може да бъде равно на нула). Тази стойност ще се използва като начална стойност. Нека бъде 3 . Нека представим формулата =10*A3-10/A3 , чрез който трябва да се получи необходимата стойност, в произволна клетка, напр. B3 . Сега можете да стартирате функцията за търсене на параметър, като изберете командата Търсене на цел в менюто Инструменти (сервиз) . Въведете параметри за търсене:

В полето Задаване на клетка (Задаване в клетка) въведете препратка към клетката, съдържаща необходимата формула.
Въведете желания резултат в полето Да ценят .
В полето Чрез промяна на клетка (промяна на стойността на клетката) въведете препратка към клетката, съдържаща стойността, която трябва да бъде съпоставена.
Кликнете върху ключа Добре .

В края на функцията на екрана ще се появи прозорец, в който ще се показват резултатите от търсенето. Намереният параметър ще се появи в клетката, която е запазена за него. Обърнете внимание на факта, че в нашия пример уравнението има две решения и е избрано само едно - това е така, защото параметърът се променя само докато се върне желаната стойност. Първият намерен аргумент по този начин се връща при нас като резултат от търсенето. Ако посочим като начална стойност в нашия пример -3 , тогава ще бъде намерено второто решение на уравнението: -0,5 .

Доста трудно е да се определи правилно най-подходящата първоначална стойност. По-често можем да направим някои предположения за желания параметър, например параметърът трябва да е цяло число (тогава получаваме първото решение на нашето уравнение) или неположително (второто решение).

Задачата за намиране на параметър при наложените гранични условия ще бъде решена от специална добавка на Microsoft Excel Решател (Търсене на решение) .

Търсене на решение.

Microsoft Excel добавка Решател (Търсене на решение) не се инсталира автоматично при нормална инсталация:

В менюто Инструменти (сервиз) изберете отбор Добавки . Ако диалоговият прозорец Добавки не съдържа команда Решател (Търсене на решение) , Натисни бутона Прегледайте и посочете устройството и папката, които съдържат файла с добавката Solver.xla (обикновено това е папката Библиотека\Солвър ) или стартирайте програмата Microsoft инсталации Office, ако файлът не може да бъде намерен.
В диалоговия прозорец Добавки поставете отметка в квадратчето Решател (Търсене на решение) .

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

В диалоговия прозорец Решател (Търсене на решение) същото като в диалоговия прозорец Търсене на цел , трябва да посочите целевата клетка, нейната стойност и клетките, които трябва да бъдат модифицирани, за да се постигне целта. За решаване на проблеми с оптимизацията целевата клетка трябва да бъде определена равна на максималната или минималната стойност.

Ако щракнете върху бутона Познай (Познай) , Excel ще се опита да намери всички клетки, които засягат формулата.

Можете да добавите гранични условия, като щракнете върху бутона Добавете .

Като щракнете върху бутона Настроики , можете да промените условията за намиране на решение: максималното време за търсене на решение, броя на итерациите, точността на решението, толеранса за отклонение от оптималното решение, метода на екстраполация (линеен или квадратичен), оптимизацията алгоритъм и др.

Да се върнем към предишния пример: за да получим второто (неположително) решение е достатъчно да добавим граничното условие A3 . Както и при избор на параметър, на екрана ще се появи прозорец, в който ще се покаже отчет за резултатите от търсенето на необходимото решение. Самото решение ще бъде показано в предназначените за него клетки (в клетката A3 ще се покаже стойност -0.50 ).

Microsoft Excel добавка Решател (Търсене на решение) позволява също да се решават системи от уравнения или неравенства. Помислете за прост пример: нека се опитаме да решим системата от уравнения
x + y = 2
x - y = 0

Една от най-интересните функции в Microsoft Excel е Solver. В същото време трябва да се отбележи, че този инструмент не може да бъде приписан на най-популярния сред потребителите в това приложение. Но напразно. В крайна сметка тази функция, използвайки първоначалните данни, чрез изброяване намира най-оптималното решение от всички налични. Нека разберем как да използваме функцията за намиране на решение в Microsoft Excel.

Можете да търсите дълго време в лентата, където се намира Solver, но все още не можете да намерите този инструмент. Просто, за да активирате тази функция, трябва да я активирате в настройките на програмата.

За да активирате Търсене на решения в Microsoft Excel 2010 и по-нови версии, отидете в раздела "Файл". За версия 2007, щракнете върху бутона Microsoft Officeв горния ляв ъгъл на прозореца. В прозореца, който се отваря, отидете в секцията "Настройки".

В прозореца с опции щракнете върху „Добавки“. След прехода, в долната част на прозореца, срещу параметъра "Управление", изберете стойността "Добавки на Excel" и кликнете върху бутона "Отиди".

Отваря се прозорецът с добавки. Поставяме отметка пред името на необходимата добавка - „Търсене на решение“. Кликнете върху бутона "OK".

След това бутонът за стартиране на функцията Find Solutions ще се появи на лентата на Excel в раздела "Данни".

Подготовка на масата

Сега, след като активирахме функцията, нека да видим как работи. Най-лесният начин да се представи това е с конкретен пример. Така че имаме маса заплатислужители на предприятието. Трябва да изчислим бонуса за всеки работник, който е произведение от заплатите, посочени в отделна колона, с определен коефициент. В същото време общият Париотпусната за наградата е 30 000 рубли. Клетката, в която се намира тази сума, се нарича цел, тъй като нашата цел е да изберем данни за това число.

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

Целевата клетка и клетката за търсене трябва да са свързани една с друга с помощта на формула. В нашия конкретен случай формулата се намира в целевата клетка и изглежда така: “=C10*$G$3”, където $G$3 е абсолютният адрес на желаната клетка, а “C10” е общата сума на заплати, от които се изчислява бонусът служители на предприятието.

Стартиране на инструмента Solver

След като таблицата е подготвена, в раздела „Данни“, щракнете върху бутона „Търсене на решение“, който се намира на лентата в блока с инструменти „Анализ“.

Отваря се прозорец с параметри, в който трябва да въведете данни. В полето „Оптимизиране на целевата функция“ трябва да въведете адреса на целевата клетка, където ще се намира общата сума на бонуса за всички служители. Това може да стане или чрез въвеждане на координатите ръчно, или чрез щракване върху бутона, разположен вляво от полето за въвеждане на данни.

След това прозорецът с опции ще се свие и можете да изберете желаната клетка от таблицата. След това трябва да кликнете отново върху същия бутон отляво на формата с въведените данни, за да разгънете отново прозореца с параметри.

Под прозореца с адреса на целевата клетка трябва да зададете параметрите на стойностите, които ще бъдат в нея. Тя може да бъде максимална, минимална или конкретна стойност. В нашия случай това ще бъде последната опция. Затова поставяме превключвателя в позиция „Стойности“ и в полето вляво от него записваме числото 30 000. Както си спомняме, именно това число, според условията, съставлява общата сума на бонус за всички служители на предприятието.

По-долу е полето „Промяна на променливи клетки“. Тук трябва да посочите адреса на желаната клетка, където, както си спомняме, се намира коефициентът, чрез умножаване на който основната заплата ще бъде изчислена сумата на бонуса. Адресът може да бъде написан по същите начини, както направихме за целевата клетка.

В полето „Съгласно ограниченията“ можете да зададете определени ограничения за данните, например да направите стойностите цели или неотрицателни. За да направите това, кликнете върху бутона "Добавяне".

След това се отваря прозорецът за добавяне на ограничение. В полето "Връзка към клетки" въведете адреса на клетките, за които е въведено ограничението. В нашия случай това е желаната клетка с коефициент. След това поставяме желания знак: „по-малко или равно“, „по-голямо или равно“, „равно“, „цяло число“, „двоично“ и т.н. В нашия случай ще изберем знака за по-голямо или равно, за да направим коефициента положително число. Съответно в полето "Ограничение" посочете числото 0. Ако искаме да настроим друго ограничение, щракнете върху бутона "Добавяне". В противен случай щракнете върху бутона "OK", за да запазите въведените ограничения.

Както можете да видите, след това ограничението се появява в съответното поле на прозореца с параметри за търсене на решение. Също така можете да направите променливите неотрицателни, като поставите отметка в квадратчето до съответния параметър малко по-надолу. Желателно е параметърът, зададен тук, да не противоречи на тези, които сте посочили в ограниченията, в противен случай може да възникне конфликт.

Допълнителни настройки можете да зададете, като щракнете върху бутона "Настройки".

Тук можете да зададете прецизността на ограничението и границите на решението. Когато необходимата информация е въведена, щракнете върху бутона "OK". Но за нашия случай не е необходимо да променяте тези параметри.

След като всички настройки са зададени, щракнете върху бутона "Намерете решение".

Освен това програмата Excel в клетките извършва необходимите изчисления. Едновременно с извеждането на резултатите се отваря прозорец, в който можете или да запазите намереното решение, или да възстановите първоначалните стойности, като преместите превключвателя в подходящата позиция. Независимо от избраната опция, като поставите отметка в квадратчето „Връщане към диалоговия прозорец с опции“, можете да се върнете към настройките за търсене на решение. След като квадратчетата за отметка и превключвателите са зададени, щракнете върху бутона „OK“.

Ако по някаква причина резултатите от търсенето на решения не ви удовлетворяват или програмата дава грешка при изчисляването им, тогава в този случай се връщаме към диалоговия прозорец с параметри по начина, описан по-горе. Преглеждаме всички въведени данни, тъй като може би някъде е допусната грешка. Ако грешката не е намерена, отидете на параметъра „Изберете метод за решение“. Тук е възможно да изберете един от трите метода на изчисление: „Търсене на решение на нелинейни проблеми по метода OPG“, „Търсене на решение на линейни проблеми по симплексния метод“ и „Еволюционно търсене на решение“. По подразбиране се използва първият метод. Опитваме се да разрешим проблема, като изберем всеки друг метод. Ако не успеете, опитайте отново, като използвате последния метод. Алгоритъмът на действията е същият, както е описано по-горе.

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

Ако в Клетка на Excelсе въвежда формула, съдържаща връзка към същата клетка (може и не директно, но индиректно чрез верига от други връзки), тогава казват, че има циклична препратка (цикъл). На практика до циклични препратки се прибягва, когато става въпрос за изпълнение на итеративен процес, изчисления по рекурентни отношения. В нормално Режим на Excelзасича цикъла и издава съобщение за възникналата ситуация, изисквайки нейното отстраняване. Excel не може да извършва изчисления, защото кръговите препратки генерират безкраен брой изчисления. Има два изхода от тази ситуация: премахване на цикличните препратки или разрешаване на изчисления с помощта на формули с циклични препратки (във последния случай броят на повторенията на цикъла трябва да е краен).

Разгледайте проблема за намиране на корена на уравнение по метода на Нютон с помощта на циклични препратки. Да вземем за пример квадратно уравнение: x 2 - 5x + 6=0, чието графично представяне е показано в . Можете да намерите корена на това (и всяко друго) уравнение, като използвате само една клетка на Excel.

За да активирате режима на циклично изчисление в Меню Инструменти/Опции/Раздел Изчислениявключете квадратчето за отметка Итерации, ако е необходимо, променете броя на повторенията на цикъла в полето Ограничете броя на повторениятаи точност на изчисленията в полето Относителна грешка(стойностите им по подразбиране са съответно 100 и 0,0001). В допълнение към тези настройки избираме опцията за извършване на изчисления: автоматичноили ръчно. При автоматиченизчисление, Excel веднага дава крайния резултат с извършени изчисления ръчно, можете да наблюдавате резултата от всяка итерация.

Ориз. 8. Функционална графика

Изберете произволна клетка, дайте й ново име, кажете - хи въведете рекурсивна формула в него, която определя изчисленията, използвайки метода на Нютон:

Където ЕИ F1задайте съответно изрази за изчисляване на стойностите на функцията и нейната производна. За нашето квадратно уравнение, след като въведете формулата, стойността ще се появи в клетката 2 съответстващ на един от корените на уравнението (). В нашия случай първоначалното приближение не беше посочено, итеративният изчислителен процес започна със стойността, съхранена по подразбиране в клетката хи равно на нула. И как да получите втория корен? Това обикновено може да се направи чрез промяна на първоначалното приближение. Проблемът с настройката на първоначалните настройки във всеки случай може да бъде решен по различни начини. Ще демонстрираме една техника, базирана на използването на функцията IF. За да се увеличи яснотата на изчисленията, на клетките бяха присвоени смислени имена ().

2.2. Избор на параметри

Когато желаният резултат от изчисление по формула е известен, но стойностите, необходими за получаване на този резултат, не са известни, можете да използвате инструмента Избор на параметрикато изберете командата Избор на параметрив менюто Обслужване. Когато изберете параметър, Excel променя стойността в една конкретна клетка, докато изчисляването на формулата, отнасяща се до тази клетка, даде желания резултат.

Да вземем за пример същото квадратно уравнение x 2 -5x+6=0. За да намерите корените на уравнението, направете следното:

При избора на параметър Excel използва итеративен (цикличен) процес. Броят на повторенията и точността се задават в менюто Раздел Инструменти/Опции/Изчисления. Ако Excel изпълнява сложната задача за избор на параметър, можете да щракнете Паузав диалоговия прозорец Резултат от избора на параметъри прекъснете изчислението, след което натиснете бутона стъпказа извършване на следващата итерация и преглед на резултата. При решаване на задача в режим стъпка по стъпка се появява бутон продължи- за връщане към нормалния режим на избор на параметри.

Да се върнем към примера. Отново възниква въпросът: как да получите втория корен? Както и в предишния случай, е необходимо да зададете първоначалното приближение. Може да се направи така ():

Ориз. 11. Търсете втория корен

Всичко това обаче може да се направи и малко по-лесно. За да намерите втория корен, достатъчно е да поставите константа като първоначално приближение () в клетка C2 5 и след това започнете процеса Избор на параметри.

2.3. Намиране на решение

Екип Избор на параметрие удобен за решаване на задачи за намиране на конкретна целева стойност в зависимост от един неизвестен параметър. За по-сложни задачи използвайте командата Намиране на решение (решаващ проблем), до която се влиза чрез елемента от менюто Сервиз/Търсене на решение.

Задачи, които се решават с Намиране на решение, в общата формулировка се формулират, както следва:

Намирам:
x 1, x 2, ..., x n
така че:
F(x 1, x 2, ..., x n) > (Макс; Мин; = Стойност)
с ограничения:
G(х 1 , х 2 , … , х n) > (Ј Стойност; і Стойност; = Стойност)

Променливи за търсене - работни клетки Excel листсе наричат регулирани клетки. целева функция F(x 1, x 2, ..., x n), понякога просто наричана цел, трябва да се посочи като формула в клетка на работен лист. Тази формула може да съдържа дефинирани от потребителя функции и трябва да зависи от (препратка към) регулируеми клетки. В момента на поставяне на задачата се определя какво да се прави с целевата функция. Можете да изберете една от опциите:

намерете максимума на целевата функция F(x 1, x 2, ..., x n);
намерете минимума на целевата функция F(x 1, x 2, ..., x n);
гарантира, че целта функция F(x 1, x 2, ..., x n)имаше фиксирана стойност: F(x 1, x 2, ..., x n) = a.

Функции G(x 1, x 2, ..., x n)се наричат ограничения. Те могат да се задават както под формата на равенства, така и под формата на неравенства. На регулираните клетки могат да бъдат наложени допълнителни ограничения: неотрицателни и/или цели числа, след което желаното решение се търси в областта на положителните и/или целите числа.

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

По-горе, за намиране на корените на квадратното уравнение, беше приложен методът на Нютон (раздел 1.4) с помощта на циклични препратки () и инструмента Избор на параметри(). Да видим как да използваме Търся решениена примера на същото квадратно уравнение.

След отваряне на диалоговия прозорец Намиране на решение() трябва да направите следното:

в полето Задайте целева клеткавъведете адреса на клетката, съдържаща формулата за изчисляване на стойностите на функцията, която се оптимизира, в нашия пример целевата клетка е C4 и формулата в нея изглежда така: = C3^2 - 5*C3 + 6;
за да увеличите максимално стойността на целевата клетка, задайте радио бутон максимална стойностдо позиция 8, превключвателят се използва за минимизиране минимална стойност, в нашия случай поставете превключвателя на позиция стойност и въведете стойността 0 ;
в полето Смяна на клеткивъведете адресите на клетките за промяна, т.е. аргументи на целевата функция (C3), като ги разделяте с ";" (или като щракнете с мишката, докато натискате Ctrlв съответните клетки), за автоматично търсене на всички клетки, които влияят на решението, използвайте бутона Познайте;
в полето Ограниченияс копче Добаветевъведете всички ограничения, на които трябва да отговаря резултатът от търсенето: за нашия пример не е необходимо да се задават ограничения;
за да започнете процеса на намиране на решение, натиснете бутона Бягай.

За да запазите полученото решение, трябва да използвате превключвателя Запазете намереното решениев отворения диалогов прозорец Резултати от търсенето на решение. След това работният лист ще приеме представената форма. Полученото решение зависи от избора на първоначалното приближение, което е посочено в клетка C4 (аргумент на функцията). Ако, като първоначално приближение, в клетка C4 въведете стойност, равна на 1,0 , след което използвате Намиране на решениенамерете втория корен равен на 2,0 .

Опции за управление на операциите Намиране на решение, поставена на прозореца Настроики(прозорецът се появява, когато щракнете върху бутона Настроикипрозорец Намиране на решение), следното ():

Максимално време- ограничава времето, отделено за процеса на намиране на решение (по подразбиране е 100 секунди, което е достатъчно за проблеми с около 10 ограничения, ако проблемът е голям, тогава времето трябва да се увеличи).
Ограничете броя на повторениятае друг начин за ограничаване на времето за търсене чрез задаване на максималния брой повторения. По подразбиране е зададено 100 и най-често, ако решението не се получи в 100 итерации, тогава с увеличаване на техния брой (можете да въведете време в полето, което не надвишава 32767 секунди), вероятността за получаване резултатът е малък. По-добре е да опитате да промените първоначалното приближение и да започнете процеса на търсене отново.
Относителна грешка- задава точността, с която се определя съответствието на клетката с целевата стойност или приближаване до зададените граници (десетична дроб от 0 до 1).
Толерантност- задава се в % само за задачи с целочислени ограничения. Намиране на решениев такива задачи той първо намира оптималното нецелочислено решение и след това се опитва да намери най-близката целочислена точка, решението в която би се различавало от оптималното с не повече от процента, определен от този параметър.
Конвергенция- когато относителната промяна в стойността в целевата клетка през последните пет итерации стане по-малка от числото (част от диапазона от 0 до 1), посочено в този параметър, търсенето спира.
Линеен модел- това квадратче за отметка трябва да бъде разрешено, когато целевата функция и ограниченията са линейни функции. Това ускорява процеса на намиране на решение.
Неотрицателни стойности- с този флаг можете да зададете ограничения върху променливите, което ще ви позволи да търсите решения в положителния диапазон от стойности, без да задавате специални ограничения върху долната им граница.
Автоматично мащабиране- това квадратче за отметка трябва да бъде активирано, когато мащабът на стойностите на входните променливи и целевата функция и ограниченията се различават, може би с порядъци. Например, променливите се задават на парчета, а целевата функция, която определя максималната печалба, се измерва в милиарди рубли.
Показване на резултатите от повторенията- това поле за отметка ви позволява да включите процеса на търсене стъпка по стъпка, показвайки резултатите от всяка итерация на екрана.
Оценки- тази група се използва за обозначаване на метода на екстраполация - линеен или квадратичен - използван за получаване на първоначални оценки на стойностите на променливите при всяко едномерно търсене. Линеенсе използва за използване на линейна екстраполация по допирателен вектор. квадратнаслужи за използване на квадратична екстраполация, която дава по-добри резултати при решаване на нелинейни проблеми.
Разлики (производни)- тази група се използва за обозначаване на метода на числено диференциране, който се използва за изчисляване на частните производни на целевата и ограничаващата функция. Параметър Директенизползва се в повечето проблеми, където скоростта на промяна на ограничението е относително бавна. Параметър Централнаизползва се за функции, които имат прекъсната производна. Този метод изисква повече изчисления, но използването му може да бъде оправдано, ако се издаде съобщение, че не може да се получи по-точно решение.
Метод на търсене- служи за избор на алгоритъм за оптимизация. Метод на Нютонбеше прегледано по-рано. IN Метод на спрегнат градиентизисква се по-малко памет, но се извършват повече итерации, отколкото в метода на Нютон. Този методтрябва да се използва, ако проблемът е достатъчно голям и трябва да спестите памет, а също и ако итерациите дават твърде малка разлика в последователните приближения.

при запазване на работна книга на Excel след намиране на решение, всички стойности, въведени в диалоговите прозорци Намиране на решение, се записват с данните от работния лист. Един набор от стойности на параметри може да се съхранява с всеки работен лист в работна книга Намиране на решение;
ако няколко оптимизационни модела трябва да бъдат разгледани в рамките на един и същ работен лист на Excel (например, намерете максимума и минимума на една функция или максимални стойностиняколко функции), по-удобно е да запазите тези модели с помощта на бутона Опции/Запазване на моделапрозорец Намиране на решение. Диапазонът за запазения модел съдържа информация за целевата клетка, клетките за промяна, за всяко от ограниченията и всички диалогови стойности. Настроики. Изборът на модел за решаване на конкретен оптимизационен проблем се извършва с помощта на бутона Опции/Модел за зарежданедиалог Намиране на решение;
Друг начин за запазване на опциите за търсене е да ги запазите като наименувани скриптове. За да направите това, щракнете върху бутона Запазване на сценариядиалогов прозорец Резултати от търсенето на решение.

В допълнение към вмъкването на оптимални стойности в променливи клетки Намиране на решениеви позволява да представите резултатите под формата на три отчета: резултати, устойчивостИ граници. За да генерирате един или повече отчети, изберете техните имена в диалоговия прозорец Резултати от търсенето на решение. Нека разгледаме всеки от тях по-подробно.

Ориз. 15. Доклад за устойчивост

) съдържа информация за това колко чувствителна е целевата клетка към промени в ограниченията и променливите. Този отчет има два раздела, един за променливи клетки и един за ограничения. Дясната колона във всеки раздел съдържа информация за чувствителност. Всяка променлива клетка и границите са изброени на отделен ред. Разделът за променливи клетки съдържа нормализирана градиентна стойност, която показва как реагира цяла клетка, когато стойността в съответната променлива клетка се увеличи с една единица. По подобен начин множителят на Лагранж в раздела за ограничения показва как реагира целевата клетка, когато съответната стойност на ограничението се увеличи с една единица. Когато използвате целочислени ограничения, Excel показва съобщение Докладите за стабилност и ограничения не са приложими за проблеми с целочислени ограничения. Ако в диалоговия прозорец Опции за търсене на решениепроверено Линеен модел, тогава докладът за стабилност съдържа няколко допълнителни колони с информация.) съдържа три таблици: първата съдържа информация за целевата функция преди началото на изчислението, втората съдържа стойностите на необходимите променливи, получени в резултат на решаването на проблем, а третият съдържа резултатите от оптималното решение за ограничения. Този отчет също така съдържа информация за параметрите на всяко ограничение, като състояние и разлика. Едно състояние може да приеме три състояния: свързано, несвързано или неуспешно. Стойността на разликата е разликата между стойността, показана в клетката за ограничение, когато се получи решението, и числото, посочено от дясната страна на формулата за ограничение. Свързано ограничение е ограничение, за което стойността на разликата е нула. Необвързано ограничение е ограничение, което е изпълнено с различна от нула делта стойност.

Отчетът за ограничения съдържа информация за границите, в които стойностите на променливите клетки могат да бъдат увеличени или намалени, без да се нарушават ограниченията на задачата. За всяка клетка, която се променя, този отчет съдържа оптималната стойност, както и най-малки стойности, които клетката може да приеме, без да нарушава ограниченията.