Конспект занятия Численные методы решения задач (средствами MS Excel)

ТЕМА: «Численные методы решения математических задач средствами Microsoft Excel»

ФИО ПРЕПОДАВАТЕЛЯ: Шашкова Юлия Николаевна, преподаватель информатики

ТИП УРОКА: Занятие комплексного применения знаний и умений

ВИД УРОКА: Практическая работа

ДИСЦИПЛИНА: «Компьютерное моделирование»

ЦЕЛЬ и ЗАДАЧИ:

- формирование навыков решения системы линейных уравнений методом Крамера средствами Microsoft Excel;

- усвоение приемов работы с матрицами, и матричного способа решения систем линейных уравнений средствами Microsoft Excel;

- формирование навыков решения уравнения методом половинного деления с точностью ε и методом оптимизации.

- развитие навыков мыслительной деятельности, включая каждого студента в учебно-познавательный процесс и создавая условия для работы каждого в индивидуальном темпе;

- воспитание положительной мотивации к изучению темы занятия, необходимой для будущей специальности;

- воспитание внимательности, аккуратности, добросовестности.

ЛИТЕРАТУРА

1) Могилев, А.В. Информатика: учебное пособие для студ. Пед.вузов/ А.В. Могилев, Н.И. Пак, Е.К. Хеннер. – М.: Академия, 2008. – 848 с

2) Гельман В.Я. Решение математических задач средствами Excel: Практикум. М.: Питер, 2003. – 240с

2. Инструктаж по выполнению практического задания:

- цель работы;

- комментарии по программному обеспечению;

- порядок выполнения заданий;

10

3. Демонстрация выполнения задания преподавателем с устными по-шаговыми инструкциями

10

4. Самостоятельная работа студентов за компьютером

25

5. Физкультминутка для глаз, рук и тела

5

6. Самостоятельная работа студентов за компьютером

30

7. Анализ и оценка выполненных работ и степени овладения студентами запланированными умениями

5

ПРАКТИЧЕСКАЯ РАБОТА

«Численные методы решения математических задач средствами Microsoft Excel»

Цель: формирование навыков решения систем линейных уравнений и уравнений с одной неизвестной численными методами в табличном редакторе Microsoft Excel.

Численные методы решения различных видов уравнений – это алгоритмы нахождения приближённых (а иногда и точных) значений искомого решения. Решение алгебраических уравнений при этом получаются в виде значений аргументов, вычисленных с определённой степенью точности. Численные методы можно применять только к корректно поставленным задачам.

Упражнение 1. РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ УРАВНЕНИЙ
МАТРИЧНЫМ МЕТОДОМ

Цель упражнения: сформировать навыки решения систем линейных уравнений средствами Microsoft Excel матричным способом.

Многие прикладные задачи в технике, экономике и других областях сводятся к решению системы линейных уравнений, поэтому особенно важно уметь их решать.

Пусть дана линейная система n уравнений с n неизвестными, где - произвольные числа, называемые коэффициентами при переменных и свободными членами уравнений.

Решением системы называется такая совокупность n чисел, при подстановке которых каждое уравнение системы обращается в верное равенство. Система уравнений совместна, если она имеет хотя бы одно решение.

Данную систему уравнений можно записать в виде матричного уравнения:

,

где А – матрица коэффициентов при переменных;

Х – матрица-столбец (вектор) неизвестных;

В – матрица-столбец (вектор) свободных членов.

В развернутом виде систему можно представить следующим образом:

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

Таким образом, для решения системы необходимо найти обратную матрицу коэффициентов и умножить ее справа на вектор свободных членов.

Задание 1. Необходимо решить систему уравнений

Решение.

1. На Лист1 введите матрицу А в диапазон А1:В2. И вектор В=(7 40) в диапазон С1:С2.

2. Найдите обратную матрицу А^-1. Для этого:

   • Выделите блок А3:В4 под обратную матрицу;

   • На закладке ФОРМУЛЫ выберите пункт МАТЕМАТИЧЕСКИЕ. В выпадающем списке выберите функцию МОБР;

   • Появившееся диалоговое окно МОБР мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:В2 в рабочее поле Массив (указателем мыши принажатой левой кнопке). После чего нажмите сочетание CTRL+SHIFT+ENTER

   • В результате в диапазоне А3:В4 появиться обратная матрица

3. Умножением обратной матрицы А^-1 на вектор В найдите вектор Х. Для этого:

   • Выделите блок С3:С4 под результирующую матрицу (вектор Х);

   • На закладке ФОРМУЛЫ выберите пункт МАТЕМАТИЧЕСКИЕ. В выпадающем списке выберите функцию МУМНОЖ;

   • Появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходной матрицы и введите диапазон обратной матрицы А3:В4 в поле Массив1 и диапазон С1:С2 в рабочее поле Массив2. После чего нажмите сочетание CTRL+SHIFT+ENTER;

   • В результате в диапазоне С3:С4 появился вектор Х. Причем х=5, у=-4.

4. Можно осуществить проверку найденного решения. Для этого найденный вектор Х необходимо подставить в исходное матричное уравнение .

Задание 2. Самостоятельно решите следующие системы уравнений

Упражнение 2. РЕШЕНИЕ СИСТЕМЫ УРАВНЕНИЙ МЕТОДОМ КРАМЕРА

Цель упражнения: сформировать навыки решения системы линейных уравнений методом Крамера средствами Microsoft Excel.

Метод Крамера (правило Крамера) — способ решения квадратных систем линейных алгебраических уравнений с ненулевым определителем основной матрицы (причём для таких уравнений решение существует и единственно)

Задание 3. Решить систему линейных уравнений методом Крамера.

Решение:

1. Введите на Листе2 матрицу А (размером 3х3) в диапазон ячеек В1:D3, столбец свободных членов b в диапазон B5:B7.

[pic]

2. В ячейку В9 посчитайте определитель основной матрицы с помощью функции МОПРЕД(массив). После чего нажмите сочетание CTRL+SHIFT+ENTER.

3. Введите матрицу D1 (размером 3х3) в диапазон ячеек В11:D13, в ячейку G12 введите определитель матрицы D1 с помощью функции МОПРЕД(массив), где матрица D1составлена из элементов матрицы А, в которой первый столбец заменен столбцом свободных членов.

4. Введите матрицу D2 (размером 3х3) в диапазон ячеек В15:D17, в ячейку G16 введите определитель матрицы D2 с помощью функции МОПРЕД(массив), где матрица D2 составлена из элементов матрицы А, в которой второй столбец заменен столбцом свободных членов.

5. Введите матрицу D3 (размером 3х3) в диапазон ячеек В19:D21, в ячейку G20 введите определитель матрицы D2 с помощью функции МОПРЕД(массив), где матрица D2 составлена из элементов матрицы А, в которой третий столбец заменен столбцом свободных членов.

6. Вычислить искомые неизвестные, в диапазоне В23:В25

[pic]

Задание 4. Решить систему линейных уравнений методом Крамера.

4. Упражнение 3. РЕШЕНИЕ УРАВНЕНИЯ

5. МЕТОДОМ ПОЛОВИННОГО ДЕЛЕНИЯ

6. Цель упражнения: сформировать навыки решения уравнения методом половинного деления с точностью ε.

   Пусть на отрезке [a,b] расположен один корень уравнения , который необходимо уточнить с погрешностью ε.

7. Процедура уточнения положения корня заключается в построении последовательности вложенных друг в друга отрезков, каждый из которых содержит корень уравнения. Для этого находится середина текущего интервала неопределенности:

8. [pic]

10. В качестве следующего интервала неопределенности из двух возможных выбирается тот, на концах которых функция имеет разные знаки. Точность будет достигнута, если:

11. [pic]

12. Корень уравнения вычисляется по формуле

15. Задание 5. Уточнить корни уравнения методом половинного деления с точностью до 0,00001.

16. Решение.

1. На Лист3 заполнить ячейки А1:H1 последовательностью следующим образом: a, b, c=(a+b)/2, f(a), f(b), f(c), |b-a|<=2*e, e

2. Ввести в ячейку A2 число 5, в ячейку B2 - число 6.

3. В ячейку B2 ввести формулу: =(A2+B2)/2.

4. В ячейку D2 ввести формулу: =cos(2*A2)+A2-5, скопировать эту формулу в ячейки E2:F2.

5. Ввести в ячейку G2 формулу: =ЕСЛИ(ABS(B2-A2)<=2*$H$2;C2;"-").

6. Ввести в ячейку H2 число 0,00001.

7. В ячейку A3 ввести формулу: =ЕСЛИ(D2*F2<0;A2;C2).

8. В ячейку B3 ввести формулу: =ЕСЛИ(D2*F2<0;C2;B2).

9. Диапазон ячеек C2:G2 скопировать в диапазон ячеек C3:G3.

10. Выделить диапазон ячеек A3:G3 и с помощью маркера заполнения заполнить все нижестоящие ячейки до получения результата в одной из ячеек столбца G (это ячейки A3:G23).

20. [pic]

21. Ответ: Корень уравнения cos(2x)+x-5=0 равен 5,32977.

24. Упражнение 4. РЕШЕНИЕ УРАВНЕНИЙ С ОДНИМ НЕИЗВЕСТНЫМ
    СРЕДСТВАМИ ОПТИМИЗАЦИИ

26. Цель упражнения: сформировать навыки решения уравнения методом оптимизации.

27. Одним из приложений задач оптимизации является численные решение системы уравнений с одни неизвестным или несколькими неизвестными вида .

28. Нахождение корней уравнений данного вида даже в случае алгебраических уравнений выше третьей степени представляется достаточно сложную задачу. В этих случаях единственным путем является получение приближенных решений, выбором неизвестных значений так, чтобы они давали минимум ошибки некоторой целевой функции (как правило, квадратичной). Обычно используются итерационные методы, когда вначале выбирают некоторое начальное приближение, а затем вычисляют последовательность приближений.

29. Итерационные методы обеспечивают сходимость таких приближений к искомому значению х.

30. Задание 5. Найти решение уравнения

31. Решение. Уравнение имеет два корня. Решение начинаем с нахождения первого корня.

1. На Листе4 заносим в ячейку А1 ориентировочное значение первого корня, например, 3.

2. Заносим в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1. Соответствующая формула будет иметь вид =A1^2-3*A1+2

3. Вызываем функцию ПОДБОР ПАРАМЕТРОВ (Закладка Данные, Кнопка Анализ «что-если»)

32. [pic]

4. В поле Установить в ячейке указываем В1, в поле Значение задаем 0 (правая часть уравнения), в поле Изменяя значение ячейки указываем А1.

5. Щелкаем по кнопке ОК и получаем результат подбора, таким образом получаем в ячейке А1 приближенное значение х1=2,000048. При этом точность решения указана в ячейке В1, получаем 2е-5.

6. Повторяем расчет для второго корня, задавая в ячейке А1 другое начальное значение, например, -3. Аналогично получаем значение второго корн уравнения х2=0,9996.

7. Подставляя значения (округленные) в ячейку А1 –выполнить проверку.

8. Записываем ответ

34. Вопросы:

1. Назовите функции Microsoft Excel, которые можно использовать при выполнении операций с матрицами.

2. Укажите способ решения уравнения, систем линейных уравнений, реализуемый в Excel.