Има много проблеми, които могат да бъдат значително улеснени с помощта на инструмента Find Solutions. Но за да направите това, трябва да започнете с организиране на работния лист според модел, подходящ за намиране на решения, за което трябва да имате добро разбиране на връзките между променливи и формули. Въпреки че изложението на проблема обикновено е основната трудност, времето и усилията, изразходвани за изготвяне на модела, са напълно оправдани, тъй като получените резултати могат да ви спестят загуба на ресурси, ако не са планирани правилно, да помогнат за увеличаване на печалбите чрез оптимално финансово управление или да идентифицирате най-добрите съотношение на производствените обеми, наличностите и наименованията на продуктите.
Зад вашата същност проблем с оптимизациятае математически модел на определен производствен процес, неговото разпространение, съхранение, преработка, транспортиране, покупка или продажба, извършване на набор от услуги и др. Това е често срещан математически проблем като: дадено/намери/предполагаемо, но който има много възможни решения. По този начин оптимизационният проблем е проблемът за избора от множеството възможни опции на най-добрия, оптимален. Решението на този проблем се нарича планили програма, например, казват - производствен план или програма за реконструкция. С други думи, това са неизвестните, които трябва да намерим, например, количеството производство, което ще даде максимална печалба. Проблемът за оптимизация е търсенето на екстремум, тоест максимална или минимална стойност на определена функция, която се нарича целева функция, например, може да бъде функция на печалба - приходи минус разходи. Тъй като всичко в света е ограничено (време, пари, природни и човешки ресурси), при оптимизацията проблеми винаги има ограничения, например, количеството метал, работници и металорежещи машини в предприятието за производство на части. Следва пример за проектиране на много прост оптимизационен проблем, но с помощта на него можете лесно да разберете организацията за изграждане на таблица за ефективността на решаването на практически оптимизационни проблеми.
Имаме класически проблем, когато една фирма произвежда два вида продукти (стока А и стока Б) на определена цена, тяхното производство изисква 4 вида ресурси (ресурс 1, ресурс 2, ресурс 3, ресурс 4), които са налични на компанията в определено количество (Наличност), има и информация за това колко всеки ресурс е необходим за производството на единица продукция, съответно стоки А и стоки Б. Трябва да намерите количеството стоки А и стоки В, което максимизира доходи (приходи) (виж фиг.).
След това трябва да направим връзките между ограниченията, плана и целевата функция. За да направите това, изграждаме допълнителна колона (Used), в която въвеждаме формулата СУМПРОИЗВОД(Норма; План). Нормата е цената на определен ресурс за производството на единица продукция от стоки А и Б, а планът е количеството продукция, което търсим. В клетките за доходи въведете формулата СУМПРОИЗВОД(Цена; План). По този начин попълнихме колоната Използвани и клетката Доход с формули. Тъй като планът е променлива, от която зависи количеството на използваните ресурси и приходите, клетките с формули директно зависят от данните, които се появяват там в резултат на търсенето на решения. От гореизложеното можем да направим следните изводи, че всеки оптимизационен проблем трябва задължително да има три компонента:
неизвестен(това, което търсим, тоест план);
ограничениедо неизвестни (област на търсене);
целева функция(целта, за която търсим екстремум).
Мощен инструмент за анализ на данни превъзхождаме добавка Solver (Търсене на решение). С негова помощ можете да определите при какви стойности на посочените влияещи клетки формулата в целевата клетка приема желаната стойност (минимална, максимална или равна на някаква стойност). Можете да зададете ограничения за процедурата за търсене на решение и не е необходимо да се използват същите влияещи клетки. За изчисляване на зададената стойност се използват различни математически методи за търсене. Можете да зададете режима, в който получените стойности на променливите се въвеждат автоматично в таблицата. Освен това резултатите от програмата могат да бъдат представени под формата на доклад. Програмата Search for Solutions (в оригиналния Excel Solver) е добавка за процесора за електронни таблици MS Excel, която е предназначена за решаване на определени системи от уравнения, линейни и нелинейни оптимизационни проблеми, се използва от 1991 г. Размерът на проблема, който може да бъде решен с основната версия на тази програма, е ограничен от следните ограничения:
броят на неизвестните (променлива за решение) - 200;
броят на формулните ограничения (експлицитно ограничение) за неизвестни - 100;
броят на ограничаващите условия (просто ограничение) за неизвестни е 400.
Разработчикът на програмата Solver, Frontline System, отдавна се специализира в разработването на мощни и удобни методи за оптимизация, вградени в средата на популярни процесори за електронни таблици от различни производители (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Високата ефективност на тяхното приложение се обяснява с интегрирането на оптимизационната програма и табличния бизнес документ. Поради глобалната популярност на електронната таблица MS Excel, вградената в нейната среда програма Solver е най-разпространеният инструмент за намиране на оптимални решения в областта на съвременния бизнес. По подразбиране добавката Solver е деактивирана в Excel. За да го активирате в Excel 2007, щракнете върху иконата Бутон за офис, щракнете Опции на Excelи след това изберете категория добавки. На полето Контролизберете стойност Добавки в Excelи натиснете бутона Отивам. На полето Налични добавкипоставете отметка в квадратчето до Намиране на решениеи натиснете бутона Добре.
AT Excel 2003и по-долу изберете команда Услуга/Добавки , в диалоговия прозорец Добавки, който се показва, поставете отметка в квадратчето Намиране на решениеи щракнете върху бутона OK. Ако на екрана се появи диалогов прозорец с молба да потвърдите намеренията си, щракнете върху бутона Да. (Може да се нуждаете от инсталационен компактдиск на Office.)
Процедура за търсене на решение 1. Създайте таблица с формули, които установяват връзки между клетките.
2. Изберете целевата клетка, която трябва да приеме желаната стойност, и изберете командата: - In Excel 2007 Анализ на данни/Намиране на решение;
AT Excel 2003и по-долу Инструменти > Solver (Инструменти > Търсене на решение). Полето Set Target Cell на диалоговия прозорец на добавката Solver, който се отваря, ще съдържа адреса на целевата клетка. 3. Задайте превключвателите Equal To (Equal), като зададете стойността на целевата клетка, - Max (максимална стойност), Min (минимална стойност) или Value of (стойност). В последния случай въведете стойност в полето отдясно. 4. Посочете в полето Чрез промяна на клетки (Промяна на клетки), в кои клетки програмата трябва да промени стойностите в търсене на оптималния резултат. 5. Създайте ограничения в списъка Подлежащи на ограниченията (Ограничения). За да направите това, щракнете върху бутона Добавяне и в диалоговия прозорец Добавяне на ограничение дефинирайте ограничението.
6. Щракнете върху бутона на бутона Опции (Параметри) и в прозореца, който се появява, изберете превключвателя Неотрицателни стойности (ако променливите трябва да са положителни числа), Линеен модел (ако проблемът, който решавате се отнася към линейни модели)
7. Щракнете върху бутона Solver, за да стартирате процеса на търсене на решение.
8. Когато се появи диалоговият прозорец Резултати от решаването, изберете радио бутона Запазване на решението или Възстановяване на оригиналните стойности. 9. Щракнете върху бутона OK.
Опции за намиране на решение Максимално време- служи за ограничаване на времето, определено за търсене на решение на проблема. В това поле можете да въведете време в секунди до 32 767 (приблизително девет часа); стойността по подразбиране от 100 е добра за повечето прости задачи.
Ограничаване на броя на повторенията- контролира времето за решаване на задачата чрез ограничаване на броя на изчислителните цикли (итерации). Относителна грешка- определя точността на изчисленията. Колкото по-малка е стойността на този параметър, толкова по-висока е точността на изчисленията. Толерантност- предназначен да зададе толеранса за отклонение от оптималното решение, ако наборът от стойности на влияещата клетка е ограничен от набора от цели числа. Колкото по-голяма е стойността на толеранса, толкова по-малко време е необходимо за намиране на решение. Конвергенция- важи само за нелинейни проблеми. Когато относителната промяна в стойността в целевата клетка през последните пет итерации е по-малка от числото, посочено в полето Convergence, търсенето спира. Линеен модел- служи за ускоряване на търсенето на решение чрез прилагане на линеен модел към оптимизационната задача. Нелинейните модели включват използването на нелинейни функции, фактор на растеж и експоненциално изглаждане, което забавя изчисленията. Неотрицателни стойности- ви позволява да зададете нулева долна граница за онези влияещи клетки, за които не е зададено съответно ограничение в диалоговия прозорец Добавяне на ограничение. Автоматично мащабиране- се използва, когато числата в клетките, които се променят, и в целевата клетка са значително различни. Показване на резултатите от итерациите- поставя на пауза търсенето на решение, за да видите резултатите от отделните итерации. Изтеглете модел- след натискане на този бутон се отваря диалогов прозорец със същото име, в който можете да въведете връзка към диапазон от клетки, съдържащи оптимизационния модел. Запазете модела- служи за показване на едноименния диалогов прозорец на екрана, в който можете да въведете връзка към диапазон от клетки, предназначени за съхранение на оптимизационния модел. Оценката е линейна- изберете този бутон за избор, за да работите с линеен модел. Оценка квадратична- изберете този бутон за избор, за да работите с нелинеен модел. Директни различия- използва се в повечето задачи, при които скоростта на промяна на ограниченията е относително ниска. Увеличава скоростта на инструмента Find Solution. Разлики централни- използва се за функции, които имат прекъсната производна. Този метод изисква повече изчисления, но използването му може да бъде оправдано, ако се издаде съобщение, че не може да се получи по-точно решение. Методът за търсене на Нютон - изисква повече памет, но извършва по-малко итерации, отколкото при метода на конюгирания градиент. Метод за търсене на конюгирани градиенти- прилага метода на конюгирания градиент, който изисква по-малко памет, но извършва повече итерации от метода на Нютон. Този метод трябва да се използва, ако проблемът е достатъчно голям и е необходимо да се спести памет или ако итерациите дават твърде малка разлика в последователните приближения.
Значителна част от задачите, които се решават с помощта на електронни таблици, предполагат, че потребителят вече има поне някои първоначални данни, за да намери желания резултат. Въпреки това Excel 2010 разполага с необходимите инструменти, с които можете да решите този проблем напротив – да изберете правилните данни, за да получите желания резултат.
„Търсене на решение“ е един от тези инструменти, който е най-удобен за „проблеми с оптимизация“. И ако не ви се е налагало да го използвате преди, сега е моментът да го поправите.
И така - започваме с инсталирането на тази добавка (тъй като тя няма да се появи сама). За щастие, сега това може да се направи доста просто и бързо - отворете менюто "Услуга" и вече в него "Добавки"
Остава само в колоната "Управление" да посочите "Добавки на Excel" и след това да кликнете върху бутона "Отиди".
След това просто действие, бутонът за активиране на "Търсене на решение" ще се покаже в "Данни". Както е показано на снимката
Нека да разгледаме как Excel 2010 намери решения се използва правилно с няколко прости примера.
Пример първи .
Да кажем, че сте ръководител на голям производствен отдел и трябва правилно да разпределите бонусите на служителите. Да кажем, че общият размер на бонусите е 100 000 рубли и е необходимо бонусите да са пропорционални на заплатите.
Тоест, сега трябва да изберем правилния коефициент на пропорционалност, за да определим размера на бонуса спрямо заплатата.
На първо място, е необходимо бързо да се състави (ако вече не съществува) таблица, където ще се съхраняват първоначалните формули и данни, според които ще бъде възможно да се получи желания резултат. За нас този резултат е общата стойност на премията. И сега внимание - целевата клетка C8 трябва да бъде свързана с помощта на формули към желаната променлива клетка на адрес E2. Това е критично. В примера ги свързваме с помощта на междинни формули, които са отговорни за изчисляването на бонуса за всеки служител (С2:С7).
Сега можете да активирате „Търсене на решения“. Ще се отвори нов прозорец, в който трябва да посочим необходимите параметри.
Под " 1 » е нашата целева клетка. Тя може да бъде само една.
« 2 ' са възможни опции за оптимизация. Като цяло можете да изберете възможни стойности "Максимални", "Минимални" или "Специфични". И ако имате нужда от конкретна стойност, тогава трябва да я посочите в съответната колона.
« 3 » - може да има няколко клетки за промяна (цял диапазон или отделно посочени адреси). В крайна сметка Excel ще работи с тях, като сортира опциите, така че да се получи стойността, посочена в целевата клетка.
« 4 - Ако трябва да зададете ограничения, тогава трябва да използвате бутона „Добавяне“, но ще разгледаме това малко по-късно.
« 5 » - бутон за превключване към интерактивни изчисления въз основа на програмата, която посочихме.
Но сега нека се върнем към възможността да променим нашата задача с помощта на бутона „Добавяне“. Този етап е доста отговорен (не по-малко от изграждането на формули), тъй като това е ограничението, което ви позволява да получите правилния резултат на изхода. Тук всичко е направено възможно най-удобно, така че можете да ги зададете не само за целия диапазон наведнъж, но и за определени клетки.
За да направите това, можете да използвате редица специфични (и познати на всички потребители на Excel 2010) знаци "=", ">=", "<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).
Но в нашия пример може да има само едно ограничение - положителен коефициент. Разбира се, можете да го зададете по няколко начина - или като използвате "Добавяне" (което се нарича "изрично посочете ограничение"), или просто проверете действащата функция "Направете променливи без ограничения неотрицателни". Това може да стане в добавката "Търсене на решение", като щракнете върху бутона "Опции".
Между другото, след като потвърдите параметрите и стартирате програмата (бутонът "Изпълни"), можете да видите резултата в таблицата. След това програмата ще покаже прозореца "резултати от търсенето".
Ако демонстрираният резултат ви устройва напълно, остава само да го потвърдите отново (бутонът „OK“), което ще фиксира резултата във вашата таблица. Ако нещо в изчисленията не ви устройва, тогава трябва да отмените резултата (бутонът „Отказ“), да се върнете към предишното състояние на нашата таблица и да коригирате допуснатите грешки.
Правилното решение на примерния проблем трябва да изглежда така
Много е важно, за да получите правилния резултат дори при най-малката промяна в изходните данни, трябва да рестартирате „Търсене на решения“.
За да разгледаме по-отблизо как работи тази програма, нека разгледаме друг пример.
Да приемем, че сте собственик на голяма мебелна компания и трябва да настроите производството по такъв начин, че да получите максимална възможна печалба. Произвеждате само рафтове за книги, само с два модела - "А" и "В", чието производство е ограничено единствено от наличието (или липсата) на висококачествени дъски, както и машинното време (обработка на машината).
Модел "А" изисква 3 m 3 дъски, а модел "B" изисква още 1 m 3 (тоест 4). От вашите доставчици получавате максимум 1700 m 3 дъски на седмица. В този случай модел "А" се създава за 12 минути работа на машината, а "В" - за 30 минути. Общо машината може да работи не повече от 160 часа седмично.
Въпросът е - колко продукта (и какъв модел) трябва да произвежда компанията на седмица, за да получи максималната възможна печалба, ако рафтът "A" дава 60 рубли печалба, а "B" - 120?
Тъй като процедурата е известна, започваме да създаваме необходимата ни таблица с данни и формули. Местоположението на клетките, както и преди, можете да зададете по ваша преценка. Или използвайте нашите
По всеки удобен начин стартираме нашето „Търсене на решения“, въвеждаме данни и правим настройки.
Така че нека да разгледаме какво имаме. Целевата клетка F7 съдържа формула, която ще изчисли печалбата. Параметърът за оптимизация е зададен на максимум. Сред клетките, които трябва да бъдат променени, имаме "F3: G3". Ограничения - всички открити стойности трябва да са цели числа, неотрицателни, общото прекарано време на машината не надвишава 160 (нашата клетка D9), количеството на суровините не надвишава 1700 (клетка D8).
Разбира се, в този случай беше възможно да не се посочват адресите на клетките, а директно да се регистрират необходимите цифрови стойности, но ако се използват адреси, тогава могат да се направят и промени в ограниченията в таблицата, което ще помогнете да се изчисли печалбата на това предприятие в бъдеще, когато се променят първоначалните данни.
Активираме програмата и тя подготвя решение.
Това обаче не е единственото решение и може да получите различен резултат. Това може да се случи дори ако всички данни са правилни и няма грешки във формулите.
да. Това може да се случи дори ако сме казали на програмата да търси цяланомер. И ако това изведнъж се случи, тогава просто трябва да направите допълнителна настройка на „Търсене на решения“. Отворете прозореца "Търсене на решения" и въведете "Параметри".
Нашият горен параметър е отговорен за точността. Колкото по-малък е, толкова по-висока е точността, а в нашия случай това значително увеличава шансовете за получаване на цяло число. Вторият параметър („Ignore integer restrictions”) дава отговор на въпроса как бихме могли да получим такъв отговор с факта, че в заявката е изрично посочено цяло число. Търсенето на решения просто игнорира това ограничение поради факта, че разширените настройки го казваха.
Така че бъдете много внимателни в бъдеще.
Третият и може би последният пример. Нека се опитаме да сведем до минимум разходите на транспортна компания, използвайки търсенето на решения в Excel 2010.
И така, строителната фирма дава поръчка за транспортиране на пясък, който се взема от 3 доставчици (кариери). Тя трябва да бъде доставена на 5 различни потребители (които са строителни обекти). Цената на доставката на товара е включена в цената на обекта, така че нашата задача е да осигурим доставката на товари до строителните обекти с минимални разходи.
Имаме - запасите от пясък в кариерата, необходимостта от строителни площадки в пясък, разходите за транспорт "доставчик-потребител".
Необходимо е да се намери схема за оптимално транспортиране на стоки (къде и откъде), при която общата цена на транспорта да бъде минимална.
Сивите клетки на нашата таблица съдържат формулите за сумата на колоните и редовете, а целевата клетка е формулата за общото изчисляване на разходите за доставка на стоките. Стартираме нашето "Търсене на решение" и правим необходимите настройки
След това започваме да търсим решение на този проблем.
Нека обаче не забравяме, че доста често транспортните задачи могат да бъдат усложнени от някои допълнителни ограничения. Да предположим, че е имало усложнение на пътя и сега е просто технически невъзможно да се достави товар от кариера 2 до строителна площадка 3. За да вземете това предвид, просто трябва да добавите допълнително ограничение "$D$13=0". И ако стартирате програмата сега, резултатът ще бъде различен
И накрая, остава да се каже само за избора на метода на решение. И ако проблемът наистина е много труден, тогава, за да получите желания резултат, най-вероятно ще трябва да изберете необходимия метод за решение.
Това е всичко за този въпрос.
Търсихме решения в Excel 2010 – за решаване на сложни проблеми
Solution Finder е добавка на Microsoft Excel, която може да се използва за намиране на най-доброто решение на проблем, като се имат предвид зададени от потребителя ограничения.
Ще разгледаме търсенето на решение в (тази добавка е претърпяла някои промени в сравнение с предишната версия в .
В тази статия ще разгледаме:
- създаване на оптимизационен модел на MS EXCEL лист
- настройка Намиране на решение;
- прост пример (линеен модел).
Инсталиране на Solver
Екип Намиране на решениее в група Анализраздел Данни.
Ако екипът Намиране на решениев група Анализне е налично, трябва да активирате добавката със същото име.
За това:
- В раздела Файлизберете отбор Настроикии след това категорията добавки;
- На полето Контролизберете стойност Добавки в Excelи натиснете бутона скок;
- На полето Налични добавкипоставете отметка в квадратчето до Намиране на решениеи щракнете върху OK.
Забележка. Прозорец добавкисъщо наличен в раздел Разработчик. Как да активирате този раздел.
След натискане на бутона Намиране на решениев група анализ,диалоговият му прозорец ще се отвори .
При честа употреба Намиране на решениепо-удобно е да го стартирате от лентата с инструменти за бърз достъп, а не от раздела Данни. За да поставите бутон на панел, щракнете с десния бутон върху него и изберете Добавяне към лентата с инструменти за бърз достъп.
Относно моделите
Този раздел е за тези, които тепърва се запознават с концепцията за оптимизационен модел.
Съвет. Преди употреба Намиране на решениеСилно препоръчваме да проучите литературата за решаване на оптимизационни проблеми и изграждане на модели.
По-долу е дадена малка образователна програма по тази тема.
надстройка Намиране на решениепомага да се определи По най-добрия начиннаправи нещо:
- „Нещо“ може да включва разпределяне на пари за инвестиции, товарене на склад, доставка на стоки или всякаква друга съществена дейност, където се изисква оптимално решение.
- „Най-добрият начин“ или оптималното решение в този случай означава: максимизиране на печалбите, минимизиране на разходите, постигане на най-добро качество и т.н.
Ето някои типични примери за оптимизационни проблеми:
- Определете кой е максимален доходът от продажба на произведени продукти;
- Определете при които общата цена на транспорта ще бъде минимална;
- Намерете така, че общите производствени разходи да бъдат минимални;
- Определете минималното време за завършване на всички дейности по проекта (критичния път).
За формализиране на задачата е необходимо да се създаде модел, който да отразява съществените характеристики на предметната област (и да не включва незначителни детайли). Имайте предвид, че моделът се оптимизира Търся решение само един индикатор(този оптимизиран показател се нарича целева функция).
В MS EXCEL моделът е набор от взаимосвързани формули, които използват променливи като аргументи. Обикновено тези променливи могат да приемат само валидни стойности, предмет на определени от потребителя ограничения.
Намиране на решениеизбира такива стойности на тези променливи (като се вземат предвид дадените ограничения), че целевата функция да е максимална (минимална) или равна на дадената числова стойност.
Забележка. В най-простия случай моделът може да бъде описан с една формула. Някои от тези модели могат да бъдат оптимизирани с помощта на . Преди първата среща Търся решениеима смисъл първо да се занимаваме подробно със свързан инструмент.
Основни разлики Избор на параметърот Намиране на решение:
- Избор на параметърработи само с модели с една променлива;
- невъзможно е да се зададат ограничения за променливи в него;
- не се определя максимумът или минимумът на целевата функция, а нейното равенство на определена стойност;
- работи ефективно само в случай на линейни модели, в нелинейния случай намира локален оптимум (най-близък до първоначалната стойност на променливата).
Изготвяне на оптимизационен модел в MS EXCEL
Намиране на решениеоптимизира стойността на целевата функция. Целевата функция е формула, която връща една стойност в клетка. Резултатът от формулата трябва да зависи от променливите на модела (не непременно директно, възможно е чрез резултата от изчисляване на други формули).
Моделните ограничения могат да бъдат наложени както върху обхвата на вариация на самите променливи, така и върху резултатите от изчисляването на други формули на модела, които зависят от тези променливи.
Всички клетки, съдържащи променливи на модела и ограничения, трябва да бъдат разположени само на един лист от работната книга. Въвеждане на параметри в диалоговия прозорец Намиране на решениевъзможно само от този лист.
Целевата функция (клетка) също трябва да бъде разположена на този лист. Но междинните изчисления (формули) могат да бъдат поставени на други листове.
Съвет. Организирайте данните на модела така, че само един модел да се намира на един лист на MS EXCEL. В противен случай, за да извършите изчисления, ще трябва постоянно да записвате и зареждате настройки. Намиране на решение(виж отдолу).
Представяме алгоритъм за работа с Търся решение, което се препоръчва от самите разработчици (www.solver.com):
- Дефиниране на клетки с моделни променливи (променливи за решение);
- Създайте формула в клетка, която ще изчисли целевата функция на вашия модел (целева функция);
- Създайте формули в клетки, които ще изчислят стойностите, които трябва да се сравняват спрямо границите (лявата страна на израза);
- Използване на диалоговия прозорец Намиране на решениевъведете връзки към клетки, съдържащи променливи, към целевата функция, към формули за ограничения и стойностите на самите ограничения;
- Бягай Намиране на решениеза намиране на оптималното решение.
Нека направим всички тези стъпки с прост пример.
Прост случай на употреба Намиране на решение
Необходимо е да заредите контейнера със стоки, така че теглото на контейнера да е максимално. Контейнерът е с обем 32 куб.м. Стоките се съхраняват в кутии и каси. Всяка кутия със стоки тежи 20 кг, обемът й е 0,15 м3. Кутия - съответно 80кг и 0,5м3. Необходимо е общият брой на контейнерите да е не по-малко от 110 броя.
Ние организираме данните за модела, както следва (вижте примерния файл).
Променливите на модела (броя на всеки тип контейнер) са маркирани в зелено.
Целевата функция (общото тегло на всички кутии и щайги) е в червено.
Ограничения на модела: от минималния брой контейнери (>=110) и от общия обем (<=32) – синим.
Целевата функция се изчислява по формулата =SUMPRODUCT(B8:C8,B6:C6)е общото тегло на всички кутии и каси, заредени в контейнера.
По същия начин изчисляваме общия обем - =SUMPRODUCT(B7:C7,B8:C8). Тази формула е необходима, за да зададете ограничение на общия обем на кутиите и кутиите (<=32).
Също така, за да зададем ограничението на модела, ние изчисляваме общия брой на контейнерите = SUM (B8: C8) .
Сега с диалоговия прозорец Намиране на решениенека да въведем връзки към клетки, съдържащи променливи, целева функция, формули за ограничения и стойностите на самите ограничения (или връзки към съответните клетки).
Ясно е, че броят на кутиите и кутиите трябва да е цяло число - това е още едно ограничение на модела.
След натискане на бутона Намери решениеще се намерят такъв брой кутии и кутии, при които общото им тегло (целева функция) е максимално, като в същото време са изпълнени всички посочени ограничения.
Резюме
Всъщност основният проблем при решаването на оптимизационни проблеми се използва Намиране на решениев никакъв случай не са тънкостите на настройката на този инструмент за анализ, а коректността на изграждане на модел, който е адекватен на задачата. Ето защо в други статии ще се фокусираме върху изграждането на модели, тъй като „извитият“ модел често е причината за невъзможността да се намери решение с помощта на Намиране на решение.
Често е по-лесно да разгледате няколко типични задачи, да намерите подобна сред тях и след това да адаптирате този модел към вашата задача.
Решаване на класически оптимизационни задачи с Намиране на решениеразглеждан .
Solver не можа да намери осъществимо решение
Това съобщение се появява, когато Намиране на решениене можа да намери комбинации от променливи стойности, които удовлетворяват всички ограничения едновременно.
Ако използвате Симплексен метод за решаване на линейни задачи, тогава можете да сте сигурни, че решението наистина не съществува.
Ако използвате нелинеен метод за решаване на проблеми, който винаги започва с началните стойности на променливите, това може също да означава, че възможното решение е далеч от тези начални стойности. Ако тичаш Намиране на решениес други начални стойности на променливите, тогава може би ще бъде намерено решение.
Представете си, че при решаване на проблема по нелинеен метод клетките с променливи са оставени празни (т.е. първоначалните стойности са 0) и Намиране на решениене намери решение. Това не означава, че наистина няма решение (въпреки че може да има). Сега, въз основа на резултатите от някаква експертна оценка, нека да въведете друг набор от стойности в клетките с променливи, който според вас е близък до оптималния (желан). В такъв случай, Намиране на решениеможе да намери решение (ако наистина съществува).
Забележка. Можете да прочетете за влиянието на нелинейността на модела върху резултатите от изчисленията в последния раздел на статията.
И в двата случая (линеен или нелинеен) първо трябва да анализирате модела за ограничения на последователността, тоест условия, които не могат да бъдат изпълнени едновременно. Най-често това се дължи на грешен избор на съотношение (напр.<= вместо >=) или гранична стойност.
Ако например в разгледания по-горе пример стойността на максималния обем е зададена на 16 m3 вместо 32 m3, тогава това ограничение ще противоречи на ограничението за минималния брой места (110), т.к. минималният брой места съответства на обем, равен на 16,5 m3 (110 * 0,15, където 0,15 е обемът на кутията, т.е. най-малкият контейнер). Като зададете максималното ограничение на обема на 16 m3, Намиране на решениеняма да намери решение.
С ограничение от 17 m3 Намиране на решениеще намери решение.
Някои настройки Намиране на решение
Метод на решение
Разгледаният по-горе модел е линеен, т.е. целевата функция (M е общото тегло, което може да бъде максимално) се изразява със следното уравнение M=a1*x1+a2*x2, където x1 и x2 са променливите на модела (броя на кутиите и кутиите), a1 и a2 са техните тежести. В линеен модел ограниченията също трябва да бъдат линейни функции на променливите. В нашия случай ограничението за обем 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 порядъка, тогава може би трябва да "загрубите" модела, например, като използвате логаритъмната операция.
Текстът на творбата е поставен без изображения и формули.
Пълната версия на произведението е достъпна в раздела „Данни файлове“ в PDF формат
ВЪВЕДЕНИЕ
Постановка на проблема и уместността на изследването. Училищният курс по математика, от начално училище до 11 клас, включва голям брой начини за решаване на различни видове уравнения и системи от уравнения. Някои уравнения се решават по нестандартни методи, които могат да се прилагат от малка част от завършилите училище. Анализът на проучената литература показа, че уравнения и системи от уравнения се срещат в различни отрасли и икономика. И като правило тези уравнения не изглеждат толкова привлекателни като училищните и имат нецелочислени решения. За да автоматизираме процеса на решаване на уравнения и системи от уравнения, решихме да намерим начини с помощта на електронни таблици. Електронните таблици намират широко приложение в професионалната дейност на специалисти в различни области на науката, производството и услугите, в различни държавни и търговски организации и фирми. Освен това електронните таблици могат да се използват за решаване на ежедневни задачи, като например създаване на домашен архив с книги или компактдискове, водене на записи на сметки за комунални услуги или домакински бюджет и др.
Към днешна дата има достатъчен брой различни образователни материали, които подробно описват как да се решават производствени проблеми с помощта на уравнения и системи от уравнения, както и методи за решаването им с помощта на електронни таблици.
В хода на изследването обаче беше установено, че методите за решаване на уравнения от по-високи степени, както и уравнения, които имат безкраен брой решения (например тригонометрични), не са достатъчно проучени.
Актуалността на посочения проблем предопредели избора на тема на изследването: „Решение на уравнения с помощта на Microsoft Excel“.
Обективен: Разгледайте инструментите на Microsoft Excel за решаване на уравнения от различни порядки.
Обект на изследване: Приложение на Microsoft Excel.
Предмет на изследване: използвайте инструментите ИЗБОР НА ПАРАМЕТР и ТЪРСЕНЕ НА РЕШЕНИЕ в Microsoft Excel, когато решавате уравнения.
Изследователска хипотеза:използването на инструментите на приложението MS Excel ИЗБОР НА ПАРАМЕТР и ТЪРСЕНЕ НА РЕШЕНИЕ значително опростява процеса на решаване на уравнения от различен тип.
Цели на изследването:
Да се проучи литературата за прилагането на уравнения при решаване на производствени задачи.
Да се проучи литературата за използването на Microsoft Excel на практика.
Помислете за начини за решаване на уравнения с помощта на инструментите ИЗБОР НА ПАРАМЕТР и ТЪРСЕНЕ НА РЕШЕНИЕ в Microsoft Excel.
Създайте видео курсове за решаване на различни видове уравнения.
Теоретично значение: беше извършен анализ на редица източници относно възможностите на приложението Microsoft Excel при решаване на уравнения от различен порядък.
Практическо значение: предложени са методи за решаване на уравнения от по-висок порядък и тригонометрични уравнения с помощта на MS Excel, материалът е систематизиран и обобщен под формата на видео курсове.
Изследователски методи: теоретичен анализ и обобщение на научна литература и интернет материали; провеждане на експерименти за решаване на различни видове уравнения с помощта на инструментите за избор на параметри и за търсене на решаване; създаване на видео курсове за използване на инструментите за избор на параметри и за търсене на решения при решаване на различни уравнения.
УРАВНЕНИЯ В РАЗЛИЧНИ ИНДУСТРИИ
В съвременното общество уравненията са намерили своето приложение в много сектори на икономиката и производството, както и в почти всички най-нови технологии. Разбира се, математиката, както всяка друга наука, не стои на едно място. Вече са разработени достатъчно методи за решаване на различни видове уравнения от различни степени. Появата на компютрите и бързото развитие на информационните технологии направиха възможно многократно да се опрости задачата за намиране на корените на различни уравнения. В тази глава като примери представяме видовете уравнения, които се решават в някои сектори на икономиката и производството.
1.1. Уравнения за решаване на икономически задачи
Пример 1.1.1.Изчислете на каква възраст е необходимо да платите по 1000 рубли като допълнителни застрахователни премии, за да получите увеличение на пенсия от 2000 рубли чрез участие в държавната програма за съфинансиране?
Входни данни:
месечно удръжки- 1000 рубли;
месечен цикълплащане на допълнителни застрахователни премии - прогнозната стойност (възраст за пенсиониране (в примера - за мъж) минус възрастта на участника в програмата към момента на влизане);
пенсионни спестявания- прогнозната стойност (сумата, натрупана от участника за периода, увеличена от държавата 2 пъти;
очакван период на изплащане на трудова пенсия- 228 месеца (19 години);
желан нарастваза пенсиониране - 2000 рубли.
пенсионни спестявания- калкулирана стойност (сумата, натрупана от участника за периода, удвоена от държавата).
Позволявам х- възрастта, от която трябва да се правят удръжки. Тогава увеличението на пенсията (в размер на 2000 рубли) ще бъде изчислено по формулата:
Получихме линейно уравнение, в което трябва да намерите параметъра х.
Пример 1.1.2.Нека е дадена структурата на договорната цена: собствени разходи, печалба, ДДС. Известно е, че собствените разходи възлизат на 150 000,00 рубли, ДДС 18%, а целевата стойност на договора е 200 000,00 рубли. Необходимо е да се избере такава стойност на печалбата, при която стойността на договора е равна на целевата стойност (тоест несъответствието трябва да бъде равно на нула).
Нека x е печалба. След това ще изчислим цената на продукцията като сбор от собствени разходи и печалба: 150 000 + x. ДДС върху цената на продуктите ще бъде равен на (150 000 + x) * 0,18. Изчисляваме стойността на договора като сума от Цена на продукта и ДДС: (150 000+х)+ (150 000+х)*0,18=(150 000+х)*1,18.
И така, получихме уравнението (150000 + x) * 1,18 = 2000.
Пример 1.1.3., чието решение също се свежда до линейно уравнение. Определете максималния размер на заема, който можем да си позволим да вземем от банката, ако е известно, че можем да плащаме сума от 1800,00 рубли на месец. Знаем и лихвения процент по кредита и периода, за който искаме да вземем заем (брой месеци).
Пример 1.1.4, чието решение се свежда до система от линейни уравнения. За производството на комплекти украса за коледно дърво, предприятието трябва да произведе техните компоненти - топка, камбана, сърма.
От своя страна, за производството на тези компоненти са необходими три вида суровини - стъкло (в g), папие-маше (в g), фолио (в g), нуждите от които са отразени в таблицата.
Задължително:
1) определя необходимостта от суровини за изпълнение на плана за производство на комплекти от първи, втори, трети и четвърти вид в размер съответно х 1, х 2, х 3 и х 4 броя;
2) направете изчисления за стойностите x 1 = 500, x 2 = 400, x 3 = 300 и x 4 = 200.
За да се реши този проблем, е необходимо да се намерят корените на системата от линейни уравнения:
y 1 = 5 (5x 1 + 6x 2 + 8x 3 + 10x 4) = 25x 1 + 30x 2 + 40x 3 + 50x 4
y 2 = 4 (3x 1 + 4x 2 + 6x 3) = 12x 1 + 16x 2 + 24x 3
y 3 = 3 (5x 1 + 6x 2 + 8x 3 + 10x 4) + 75 (3x 2 + 5x 3 + 8x 4) = 15x 1 + 243x 2 + 399x 3 + 630x 4
Уравнения в електроенергетиката
Помислете за приложението на уравненията в електроенергийната индустрия.
Пример 1.2.1.Дадена е схема на DC електрическа верига. Намерете токове в клоните на веригата.
За да се реши този проблем, е необходимо да се състави и реши система от линейни уравнения въз основа на законите на Кирхоф (тук не се разглежда процесът на съставяне на система от уравнения):
Уравнения в транспортната индустрия
Пример 1.3.1.За решаване на проблемите при проектирането на транспортни съоръжения и вземането на разумни решения при планирането, наблюдението и управлението на технологичните процеси на пътното строителство е необходимо да се идентифицира връзката между параметрите, които определят хода на тези процеси, и да се представят в количествен вид - под формата на математически модели. В тази връзка регресионният анализ често се използва на практика.
Регресионен анализ - метод за моделиране на измерени данни и изследване на техните свойства чрез идентифициране на връзката между зависимата променлива г и една или повече независими променливи х 1, х 2, ..., xn
Независимите променливи са известни още като фактори, аргументи,или регресори, а зависимпроменливи - функции, отговори, резултат, обяснено.
На практика регресионното уравнение най-често се избира под формата на линейна и нелинейна функция (най-простите са хипербола, експоненциална и парабола).
Пример 1.3.2.Транспортна задача
Необходимо е да се изготви транспортен план, в който всички запаси (строителни материали или конструкции) на доставчиците (строителен завод, целулозно-хартиен завод, кариери) ще бъдат изнесени, потребителското търсене (пътни работи, обекти) ще бъде напълно задоволено , като в същото време общите транспортни разходи ще бъдат минимални (транспортни разходи, срокове, други ресурси).
При решаването на тази задача се съставя система от линейни уравнения по отношение на xij- количеството товар (материали), транспортиран от пункта икъм параграф j.
Уравнения в строителната индустрия
Пример 1.4.1.Изчислете отклонението (в средата) на правоъгълна плоча. Правоъгълна плоча е натоварена с равномерно разпределен товар с интензитет q. Плочата е прищипана по контура, ръбовете са неподвижни.
Провисването се изчислява като корен на нелинейно уравнение на интервала:
Пример 1.4.2.Определете критичната сила за стоманена колона с I-сечение, ако са известни дължината на колоната L, модулът на еластичност на стоманата E, коефициентът на твърдост на еластичната опора C и инерционният момент I.
Критичната сила се изчислява по формулата:
където е коефициентът на намаляване на дължината на колоната, който се определя по формулата
Параметърът се намира от решението на уравнението
на интервала.
ИЗПОЛЗВАНЕ НА ИНСТРУМЕНТАИЗБОР НА ПАРАМЕТР ПРИ РЕШАВАНЕ НА УРАВНЕНИЯ
При решаване на производствени проблеми често възниква проблемът с избора на параметри. Например, в икономическите изчисления се използват алгоритми за изчисляване на цената на стоките, изчисляване на фонда на заплатите, печалбата от дейността на предприятието, които от своя страна зависят от много променливи и неизменни фактори.
Пример 2.1.Така че, първо, за да проучите принципа на действие на добавката ИЗБОР НА ПАРАМЕТРИ, помислете за решението линейно уравнение Ax+B=C с помощта на Microsoft Excel.
В клетка B3 въведете произволна начална стойност на променливата х, например, 0 и в клетка C1 въвеждаме лявата страна на уравнението под формата на формула: =B1*B3+B2. Нека извикаме диалоговия прозорец ИЗБОР НА ПАРАМЕТРс помощта на команди Данни - Анализ "Какво-ако" - Напасване. В този прозорец в полето Задайте в клеткавъведете препратка към клетката с формулата в полето смисъл- очакван резултат (т.е. 7), в полето Промяна на стойността в клетка- връзка към клетка, която ще съхранява стойността на избрания параметър (съдържанието на тази клетка не може да бъде формула).
Фигура 1 - Диалогов прозорец ИЗБОР НА ПАРАМЕТР
След натискане на бутона Добре, получаваме резултата.
Фигура 2 - Решаване на линейно уравнение с помощта на диалогов прозорец ИЗБОР НА ПАРАМЕТР
Известно е, че инструментът Избор на параметъризползва се главно при решаване на линейно уравнение. Ако се опитате например да решите с Избор на параметър квадратно уравнение(което има два корена), тогава инструментът ще намери решение, но само едно, това, което е по-близо до първоначалната стойност.
Пример 2.2.Помислете за пример за решаване на квадратно уравнение. Нека намерим корените на квадратното уравнение. Нека първо създадем първоначалната таблица.
Фигура 3 - Изходни данни на квадратното уравнение
Задайте произволна начална стойност на x, например 0. След това използвайте инструмента ИЗБОР НА ПАРАМЕТР.
Получих резултат: 2.
Ще намерим втория корен, като зададем различна начална стойност, например 5. И ще направим същите стъпки.
ИЗПОЛЗВАНЕ НА ДОБАВКАТАТЪРСЕТЕ РЕШЕНИЕ ПРИ РЕШАВАНЕ НА УРАВНЕНИЯ
Пример 3.1.Помислете за решаване на квадратно уравнение (от предишната глава) с помощта на инструмента SOLUTION SEARCH.
Нека въведем изходните данни
Фигура 4 - Изходни данни на квадратното уравнение
Извикайте инструмента SOLUTION SEARCH, като изберете командата DATA.
Фигура 5 - Добавка ТЪРСЕНЕ НА РЕШЕНИЕ при решаване на квадратно уравнение
В полето „Задаване на целева клетка“ изберете клетката с формулата на квадратното уравнение C1. След това поставете превключвателя в положение "Равно на 0". В полето „Промяна на клетки“ добавете клетка B4. Нека натиснем бутона "Изпълни". Взехме решение.
Фигура 6 - Решението на квадратното уравнение, намерено с помощта на добавката ТЪРСЕНЕ НА РЕШЕНИЕ
При решаването по този начин също се получава само един корен.
За да намерим втория корен, нека зададем друга начална стойност на променливата x, например, равна на 1.
Въпреки това, във всяко производство най-често трябва да се справите с уравненията по-високи степени.
Пример 3.2.Обмисли уравнение от пета степен-3x 5 +x 3 +2x 2 -3x-3=0.
Преди да намерим корените на уравнението (а това уравнение трябва да има максимум 5 корена), установяваме в кои интервали се съдържат тези корени. Нека използваме графиката на функцията, с помощта на която можем ясно да видим пропуските в местоположението на корените на уравнението.
Нека построим графика на функцията. За да направите това, в клетка A1 въведете "x", в клетка B1 въведете "y". Стойности хвъведете в клетки A2: A22, стойностите прище изчислим в клетки B2: B22, съответно.
Фигура 7 - Формула на уравнение от пета степен
Известно е, че коренът на уравнението (уравнението се записва като f(x)=0) е стойността на аргумента, при която стойността на функцията е равна на нула. При графично представяне това може да бъде точката на пресичане или докосване на графиката на функцията с оста x.
Нека построим графика на функцията.
Фигура 8 - Графика на функцията на интервала [-10; 10] на стъпки от 1
Графиката на функцията показва, че уравнението има един реален корен (останалите са комплексни), който е в интервала [-1; 0].
Нека го намерим с помощта на инструмента SOLUTION SEARCH. За да направите това, в таблицата изберете точка, близка до решението на уравнението, например -0,7.
Фигура 9 - Намиране на корена на уравнението с помощта на добавката
ТЪРСЕТЕ РЕШЕНИЕ
Задайте относителната грешка на 0,0001 с помощта на командата Format Cells.
И така, решението на уравнението е x ≈ -0,668.
Така получихме алгоритъм за решаване на уравнението от най-висока степен:
търсене на интервали, които съдържат само един корен;
прецизиране на корена в избрания интервал (чрез определяне на стойността на корена с дадена точност).
Тригонометрични уравнения
Характеристика на тригонометричните уравнения е, че те имат безкрайно много решения и всички решения се различават едно от друго с определен период.
Пример за решаване на едно от тригонометричните уравнения е разгледан подробно в Приложение 1.
Приложение 2 съдържа и пример за намиране на решения на система от линейни уравнения.
ЗАКЛЮЧЕНИЕ
В резултат на изследователската работа беше разкрито, че решението на различни уравнения и системи от уравнения се използва в много сектори на икономиката и индустрията.
В хода на изследването се научихме как да намираме корените на уравнения и системи от линейни уравнения с помощта на инструментите ТЪРСЕНЕ НА РЕШЕНИЕ и ИЗБОР НА ПАРАМЕТР в Microsoft Excel, създадохме видео курсове за решаване на уравнения с Microsoft Excel.
Така целите и задачите на това изследване бяха изпълнени.
Освен това експериментално беше установено, че използването на ТЪРСЕНЕ НА РЕШЕНИЕ и ИЗБОР НА ПАРАМЕТЪРА на приложението Microsoft Excel значително опростява процеса на намиране на корените на уравненията и системите от уравнения. Така хипотезата, поставена в началото на изследването, беше потвърдена.
Резултатите от извършената работа ще позволят използването на възможностите на изучаваните инструменти в бъдещи професионални дейности, особено ако задачата ще съдържа сложни изчисления.
Изследването може да бъде полезно не само за студенти в образователни дейности, но и за специалисти от различни сектори на икономиката и индустрията, занимаващи се с проектиране на обекти.
Резултатите от тази работа могат да се използват за изследване на други функции на приложението Microsoft Excel.
Това проучване не е завършено. Планираме да продължим да разглеждаме начини за решаване на системи от нелинейни уравнения с помощта на Microsoft Excel.
СПИСЪК НА ИЗПОЛЗВАНИ ИЗТОЧНИЦИ И ЛИТЕРАТУРА:
Богомолов, С.В. Икономико-математически методи за проектиране на транспортни съоръжения [Електронен ресурс]: насоки за практически упражнения и самостоятелна работа за студенти от специалност 270205 „Автомобилни пътища и летища“ от всички форми на обучение / С.В. Богомолов. - Електрон. Дан. - Кемерово: КуГТУ, 2013. - 30 с.
Компютърни науки за икономисти. Практикум: учебник за бакалаври / изд. В.П. Полякова, В.П. Косарев. - М.: Издателство Юрайт, 2013. - 343 с.
Митрофанов, С.В. Използването на системата MathCAD при решаване на задачи на електротехниката и електромеханиката: насоки за прилагане на WGD в дисциплината „Задачи за приложно програмиране“ / С.В. Митрофанов, А.С. Падеев. - Оренбург: ГОУ ОГУ, 2005. - 40 с.
Репкин, Д.А. Използването на MS EXCEL за решаване на приложни задачи по икономика: учебник за студенти от направление 080100 „Икономика“ от всички профили на обучение, всички форми на обучение / Д.А. Репкин. - Киров: ПРИП ФГБОУ ВПО "ВятГУ", 2012. [Електронен ресурс]
Федулов, С.В. Използване на MS Excel при финансови изчисления: учеб.-метод. надбавка / С.В. Федулов. - Екатеринбург: Издателство на УрГУПС, 2013. - 94 с.
Числени методи. Част 1: Насоки за лабораторна и самостоятелна работа по дисциплините "Информатика" и "Изчислителна математика" / Комп. Ф.Г. Ахмадиев, Ф.Г. Габасов, Р.Ф. Гизяятов, И.В. Маланичев. - Казан: Издателство Казан. състояние архитект-строи. ун-та, 2013 - 34 с.
Решение на нелинейни уравнения в Excel https://www.altstu.ru/media/f/lr3nelin-uravn.pdf - уебсайт на Алтайския държавен технически университет I.I. Ползунова
http://excel2.ru/articles/podbor-parametra-v-ms-excel - сайт Excel2.ru
https://knowledge.allbest.ru/mathematics/3c0b65625b3ad68b4c43a89421306d37_0.html - сайт allbest
Приложение 1
Решаване на тригонометрично уравнение с помощта на инструмента SOLUTION SEARCH
Нека намерим решения на уравнението.
Ще решим това уравнение по същия начин като пример 3.1. Това е:
Нека да таблираме функцията и да начертаем нейната графика;
Нека изясним корените на уравнението.
Нека да таблираме функцията на интервала [-10; десет]. Първо, в клетки A2: A22 задаваме стойностите на аргумента x и намираме стойностите на функцията в тези точки, които записваме в клетки B2: B22.
В клетка B2 въведете формулата: =A2*TAN(A2)-1
Фигура 1 - Таблица със стойности на аргументи и функции
на интервала [-10; 10] на стъпки от 1
Нека построим графика на функцията върху този сегмент.
Фигура 2 - Графика на дадена тригонометрична функция
След анализ на графиката и таблицата със стойности на функциите виждаме, че корените на уравнението са разположени в интервалите (-10; -9), (-7; -6); (-4; -3) и т.н., тоест на онези интервали, където функцията променя знака и пресича оста Ox.
Нека прецизираме първия корен на уравнението. За да направите това, поставете курсора в клетка B2 и извикайте инструмента SOLUTION SEARCH.
Фигура 3 - Добавка ТЪРСЕНЕ НА РЕШЕНИЕ
И така, първият корен се получава.
Фигура 4 - Решение на тригонометричното уравнение
По същия начин намираме корена на уравнението, като задаваме началната стойност x=-7 и x=-4.
Фигура 5 - Три корена на тригонометричното уравнение
Като се има предвид, че периодът на допирателната функция е π, намираме разликата между корените на уравнението: получаваме 3.04 и 3.01. И така, разликата между корените е приблизително 3. Следователно, следните корени на уравнението: - 0,4; 2.6; и т.н.
По този начин, за да се намерят корените на тригонометрично уравнение, е необходимо да се направят същите стъпки, както при решаване на уравнения от по-високи степени.
Приложение 2
Използване на инструментТЪРСЕТЕ РЕШЕНИЕ при решаване на системи от линейни уравнения
С помощта на инструмента SOLUTION SEARCH можете също да решите система от линейни уравнения.
Пример 4.1.Решаваме следната система от линейни уравнения
За да направите това, ще зададем клетките, в които ще бъдат записани решенията на системата от уравнения. Нека това са клетки A2:D2.
Фигура 1 – Създаване на таблица за решаване на система от линейни уравнения
Нека въведем в клетките, предназначени за решаване (А2:D2) произволни стойности, лежащи в областта на дефиниция (начални стойности).
В клетки (A3:D3) ще въведем формулите, по които трябва да се изчисляват правилните части на уравненията: (=8*A2+4*B2-6*C2; =-2*A2-4*C2-6* D2; =6*A2 +4*B2+4*C2+6*D2 = 4*A2+6*B2+8*C2+8*D2)
Фигура 2 - Изходна таблица за решаване на система от линейни уравнения
Да започнем ТЪРСЕНЕТО НА РЕШЕНИЕ от меню ДАННИ. Нека изберем една от клетките, съдържащи формули, като целева клетка (например A3), да я направим равна на -18.
В полето CHANGE CELLS поставете клетки A2:D2. Нека добавим ограничения, като щракнем върху бутона ДОБАВИ: В3=-2; С3=-14; D3=-6.
Фигура 3 - Добавка за диалогов прозорец ТЪРСЕНЕ НА РЕШЕНИЕ
Фигура 4 - Диалогов прозорец ДОБАВЯНЕ НА ОГРАНИЧЕНИЯ
Щракнете върху бутона EXECUTE. Получаваме решение:
Фигура 5 - Решаване на система от линейни уравнения
Така е намерено решението на системата от линейни уравнения. Ако проверим решението (x1=-5, x2=1, x3=-3, x4=4) чрез заместване, тогава получаваме правилните равенства.
Целта на урока: продължете да изграждате умения за електронни таблици.
- образователен: за формиране на умения за създаване, редактиране, форматиране и извършване на прости изчисления в електронни таблици.
- развиващи се: разширяване на разбирането на учениците за възможните области на приложение на електронните таблици; развиват аналитично мислене, реч и умения за внимание.
- образователен: да формира и подхранва познавателен интерес; да възпитава умения за самостоятелност в работата.
План на урока.
- Организиране на времето.
- Актуализиране на знанията на учениците.
- Проверка на домашните.
- Разрешаване на проблем.
- Независимо решаване на проблеми.
- Обобщавайки. Оценки.
- Домашна работа.
По време на занятията
1. Организационен момент.
Информирайте темата на урока, формулирайте целите и задачите на урока.
Днес отново ще посетим малкия великан Вася в Приказната страна. Той, както винаги, се нуждае от вашата помощ, момчета.
Можете ли да помогнете на Вася? Сега да проверим!
2. Актуализация на знанията на учениците.
1) Отговорете устно на въпросите.
| А | Б | ° С | д | |
| 1 | 2 | 1 | =A1+3*B1 | =A1^2+B1 |
| 2 | 4 | 6 | =A2+3*B2 | =A2^2+B2 |
- Какво е електронна таблица?
- Кои са основните елементи на електронната таблица?
- Как е името на клетка (ред, колона) в електронна таблица?
- Какво може да бъде съдържанието на клетка?
- Числото 1 е в колоната..., в реда..., в клетката с адреса...
- Числото 4 е в клетката с адреса...
- Какви са правилата за писане на формули в клетки?
- Каква е стойността, изчислена по формулата в клетка C1?
- Каква е стойността, изчислена по формулата в клетка D2?
2) Какъв резултат ще се получи в клетки с формули?
| НО | AT | |
| 1 | 25 | 4 |
| 2 | 2 | =A1*B1/2 |
| 3 |
Отговор: 25*4/2=50
| А | Б | ° С | д | |
| 1 | 5 | 2 | 1 | |
| 2 | 6 | 8 | 3 | |
| 3 | 8 | 3 | 4 | |
| 4 | =SUM(B1:D3) |
- Какво означава записът =SUM(B1:D3)?
- Колко елемента съдържа блок B1:D3? Отговор: 9.
- Съдържанието на клетка D3? Отговор: 5+2+1+6+8+3+8+3+4= 40
3) Проверка на домашната работа
Резултати от плуване
Един ученик разказва как е направил домашното си (чрез проектор).
| № | ПЪЛНО ИМЕ. | 1 | 2 | 3 | Най-доброто време | Средно време | Отклонение | |
| 1 | Лягушкин | 3.23 | 3.44 | 3.30 | ||||
| 2 | Моржов | 3.21 | 3.22 | 3.24 | ||||
| 3 | акули | 3.17 | 3.16 | 3.18 | ||||
| 4 | Рибин | 3.24 | 3.20 | 3.18 | ||||
| 5 | Черепахин | 3.56 | 3.44 | 3.52 | ||||
| Максимално отклонение | ||||||||
- Средното време за всеки състезател се намира като средноаритметично за трите му манша.
- Минималният резултат от 3 маншове се записва в клетката "Най-добро време".
- В клетката "Най-добър резултат от състезанието" се записва минималното време от колоната.
- Разликата между най-доброто време на спортиста и най-добрия резултат от състезанието се записва в колоната „Отклонение“.
- Клетката за максимално отклонение съдържа максималната стойност на колоната.
| Резултати от плуване | |||||||
| № | ПЪЛНО ИМЕ. | 1 | 2 | 3 | Най-доброто време | Средно време | Отклонение |
| 1 | Лягушкин | 3,23 | 3,44 | 3,30 | 3,23 | 3,32 | 0,07 |
| 2 | Моржов | 3,21 | 3,22 | 3,24 | 3,21 | 3,22 | 0,05 |
| 3 | акули | 3,17 | 3,16 | 3,18 | 3,16 | 3,17 | 0,00 |
| 4 | Рибин | 3,24 | 3,20 | 3,18 | 3,18 | 3,21 | 0,02 |
| 5 | Черепахин | 3,56 | 3,44 | 3,52 | 3,44 | 3,51 | 0,28 |
| Най-добрият резултат от състезанието | 3,16 | ||||||
| Средно време на състезателите | 3,29 | ||||||
| Максимално отклонение | 0,28 | ||||||
4) Решаване на прости проблеми.
Малкият гигант Вася реши да поправи оградата около градината си и да я изкопае за засаждане на зеленчуци (дошла е друга пролет), да маркира правоъгълни лехи. За работа той трябваше да намери дължината на оградата и площта на обекта. Но той никога не е ходил на училище. Нека помогнем на Вася.
№ 1. Изчислете периметъра и площта на правоъгълник със страни:
а) 3 и 5; б) 6 и 8; в) 10 и 7.
Обсъждаме този проблем заедно с децата:
- Как да си направим маса?
- Какви формули да използвате?
- Как да използваме вече написани формули за следващия правоъгълник?
Дизайн на маса - на дъската и в тетрадките.
В същото време друг ученик решава самостоятелно следната задача и представя своето решение на учениците (чрез проектора).
След като обсъдихме решението на задача номер 2, преминаваме към решението на следващия.
Единият ученик показва как се работи с формули, другият показва как се използва функцията за сумиране, числовия формат (общ, валутен) и т.н. (Таблицата вече е готова, учениците ще трябва да въведат формулите, да използват сумирането и да получат отговора).
№ 3. Изчислете, като използвате ET, 150 рубли ще бъдат ли достатъчни на Вася да купи всички продукти, които майка му е поръчала за него, и дали ще са достатъчни за чипове за 10 рубли? Мамо ми позволи да сложа рестото в касичката. Колко рубли ще отидат в касичката?
Предложено решение:
| № | име | Цена в рубли | количество | Цена |
| 1 | Хляб | 9,6 | 2 | =C2*D2 |
| 2 | кафе | 2,5 | 5 | =C3*D3 |
| 3 | Мляко | 13,8 | 2 | =C4*D4 |
| 4 | кнедли | 51,3 | 1 | =C5*D5 |
| =SUM(E2:E5) | ||||
| След пазаруване ще остане | =150-E6 | |||
| След закупуване чиповете ще останат | =D7-10 | |||
5) Самостоятелно решение на проблема.
Малкият гигант Вася често посещаваше жителите на Цветния град.
Отивайки на плажа, веселите човечета решиха да се запасят с безалкохолни напитки. Незнайката взе със себе си 2 л квас, 1 л сода и 1 л малинов сироп, Поничка - 3 л сода и 2 л малинов сироп, Торопижка - 2 л сода, д-р Пилюлкин - 1 л квас и 1 бр. литър рициново масло.
- Колко литра напитки от всеки вид взеха заедно всички човечета?
- Колко литра напитки е взел със себе си всеки от човечетата?
- Колко литра напитки взеха всички човечета заедно?
Проектирайте таблицата както желаете и я запазете в личната си папка.
Резултатът от работата.
| Весели хора. Напитки. | |||||
| Пийте | Не знам | Поничка | прибързано | Пилюлкин | Обща сума |
| Квас, л | 2 | 0 | 0 | 1 | 3 |
| Сода, л | 1 | 3 | 2 | 0 | 6 |
| сироп, л | 1 | 2 | 0 | 0 | 9 |
| Рициново масло, л | 0 | 0 | 0 | 1 | 1 |
| ОБЩА СУМА: | 4 | 5 | 2 | 2 | 13 |
7) Обобщаване. Оценки.
8) Домашна работа.
Помислете и решете този проблем, ако са известни следните количества.
Как ще се промени масата? Какви формули ще се появят?
Известно е, че 1 литър квас в Цветния град струва 1 монета, 1 литър сода - 3 монети, 1 литър малинов сироп - 6 монети, 1 литър рициново масло - 2 монети.
- Колко монети е похарчил всеки човек за закупуване на напитки?
- Колко монети се изразходват за закупуване на напитки от всеки тип?
- Колко пари са похарчени от всички човечета заедно?
литература
- Информатика. Учебник-работилница в 2 тома / Изд. I.G.Semakina, E.K.Khenner - M.: Лаборатория за основни знания, 2010.
- Ефимова О. Курс по компютърни технологии с основи на информатиката. - М .: LLC "Издателство AST"; АБФ, 2005 г.