Excel: решение уравнений, систем уравнений и задач оптимизации

Министерство  образования и науки

Федеральное государственное бюджетное образовательное  учреждение высшего профессионального  образования

«Тульский государственный университет» 
 

РЦПК 

Курс  «Информационные технологии управления» 
 
 
 

Контрольно-курсовая работа на тему:  

«Excel: решение уравнений, систем уравнений и задач оптимизации» 
 
 
 

ВЫПОЛНИЛ  студент группы 760381в                                 Литвина М.В.

                                                                                                                  (Фамилия И.О.)                                                                    

ПРОВЕРИЛ                                                                                доц. Огнянович А.В.                                                                                                                         

                                                                                                                   (Фамилия И.О.)                                                                    
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Тула   2011 

Задание 1 

     Решить  уравнение f(x)=0 в табличном процессоре Excel

• используя инструмент Excel Подбор параметра;
• используя инструмент Excel Поиск решения, 
  где  
  целевая функция: f(x)=0, 
  два ограничения задают интервал изменения x: .

     

     Решение 

1. Запустить табличный процессор Excel
2. Ввести заголовок задания «подбор параметра» и уравнение.
3. В ячейки A4:B5 ввести «заданное значение функции» и «изменение графика», x, f(x)
4. Задаем значение ячейки А6=0 исходя из области определения данной функции, в ячейку В6 вводим функцию =A6+15/(A6^2+3)

    

5. В меню «сервис» выбираем  «подбор параметра» и вводим соответствующие значения и номера ячеек:

 

    

6. Нажимаем  «ок» и получаем приближенное решение уравнения:

    

7. Теперь  построим график функции в окрестности  решения уравнения с помощью мастера диаграмм:

    

8. Получим тоже решение с помощью меню «сервис» - «поиск решения», для этого задаем целевую ячейку:

В ячейку А4 заносим =C4+15/(C4^2+3)

В ячейку С4 заносим =0

Добавляем ограничения исходя из области определения функции (-3,2):

9. Выбираем  в меню «сервис» «поиск решения», задаем целевую ячейку и значение равное нулю и два ограничения:

       

10. Нажимаем  «Выполнить» и получаем решение  уравнения:

    Найденное решение вторым способом белее точное, т.к. целевая ячейка принимает нулевое значение до 6 знака после запятой. Погрешность можно уменьшить еще с помощью параметров поиска решения. 

Задание 2

Решить систему n уравнений с n неизвестными, где 
целевая функция: , ограничения: , используя инструмент Excel Поиск решения

     

 

Решение

1. Запустить табличный процессор Excel
2. Ввести переменные (нулевые значения). Ввести ограничение в соответствии с системой:

      В ячейку Е3 вводим: =A3*SIN(A10)+B3*B10

      В ячейку Е4 вводим: =СУММПРОИЗВ(A4:B4;A$10:B$10)

3. Задаем целевую ячейку с помощью формулы

 

4. В меню «сервис» выбираем «поиск решения» и задаем необходимые значения: целевую ячейку, ее равенство нулю, переменные, ограничения:

   

5. Нажимаем  «Выполнить» и получаем решение  уравнения:

    

6. Получаем решение системы уравнений x₁=1,262476, x₂=0,643714. погрешность равна 10^-5.

 

Задание 3

Решить  оптимизационную задачу, используя инструмент Excel Поиск решения

 

Решение

1. Запустить табличный процессор Excel
2. Ввести значения коэффициентов системы уравнений. Ввести значения x₁, x₂, x₃, x₄, x₅, x₆ ограничения и задать целевую ячейку с помощью функции, определенной в задании:

    

3. В меню «сервис» выбираем «поиск решения» и задаем необходимые значения: целевую ячейку, максимальное значение, переменные, ограничения:

4.  Нажимаем  «Выполнить» и получаем оптимальное  решение:

    

       Решение найдено. Максимальное значение 1040,0001 целевая функция принимает при x₁=360, x₂=0, x₃=80,00001, x₄=x₅=x₆=0.