Решение задач линейной алгебры с помощью пакета ms excel реферат

Обновлено: 02.07.2024

Введение 3 1. Сложение, вычитание и умножение матриц на число 4 2. Умножение матриц 9 3. Нахождение обратных матриц и транспонирование матриц 12 4. Нахождение определителей квадратных матриц 14 Заключение 16

Программный пакет MS Excel представляет собой набор инструментов, предназначенных для обработки данных, например, числовых. Основой данной прикладной программы являются различные функции MS Excel, такие как финансовые, математические, статистические, баз данных и т.д., каждая из которых используется в определенной тематике.

Реферат по БЖД на заказ не будет содержать "воды". Рефераты, которые пишут наши авторы, получают высокие оценки! Обращайтесь в Work5!

. В этом реферате будем использовать средства Excel для выполнения различных действий над матрицами, что, возможно, упростит выполнение различных заданий линейной алгебры [ , ] Программный пакет MS Excel включает в себя следующие функции, предназначенные для действий над матрицами: МУМНОЖ – возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив 1 и с таким же числом столбцов, как массив 2. МОПРЕД – возвращает определитель матрицы (матрица хранится в массиве). ТРАНСП – транспонирование матрицы (замена строк на столбцы или наоборот). МОБР – возвращает обратную матрицу для матрицы, хранящейся в массиве. Для выполнения простейших арифметических действий над матрицами, таких как: - сложение или вычитание двух матриц; - умножение матрицы на произвольное число; не требуется использование встроенных функций MS Excel [ , ].

Microsoft Excel – является одной из популярных математических программ вычислений электронных таблиц [ ]. Главной особенностью её применения является выполнение сложных расчётов. Если в своей работе мы часто используем разного рода таблицы, списки, бланки, при заполнении которых производятся вычисления по формулам, то эта программа для нас. С её помощью можно проанализировать полученные числовые данные, применяя удобные встроенные инструменты, а также представить результаты в графическом виде. В расчётах можно использовать более 400 математических, статистических, финансовых и других специализированных функций, связывать различные таблицы между собой, выбирать произвольные форматы представления данных, создавать иерархические структуры и т.д [ ].

. Глушаков, С.В. Microsoft Excel 2007. Краткий курс / С.В. Глушаков, А.С. Сурядный. - М.: АСТ, АСТ Москва, Харвест, 2014. - 352 c. . В. Т. Безручко. Информатика курс лекций: Учебное пособие. - М.: ИД. "Форум": ИНФРА - М. 2013-432.с . О Сергеев, А. П. Использование Microsoft Office Excel 2007 / А.П. Сергеев. - Москва: СПб. [и др.] : Питер, 2017. - 288 c. . Зелинский, С. Э. Microsoft Office Excel 2007. Настоящий самоучитель. / С.Э. Зелинский. - М.: Век +, Корона-Век, 2015. - 320 c. . Гельман В.Я. Решение математических задач средствами Excel.-Москва- Санкт-Петербург : Питер,2003. . Лабораторный практикум по информатике [Текст]: учебное пособие мо/ под ред. В. А. Острейковского.- 2-е изд., стер..- М.: Высшая школа, 2006.- 376 с. . .М. Абдиксева. Интернет технологии в экономике знаний. Н: - М.: ИД. "Форум" : ИНФРА - М. 2012-448с. . Райс Дж. Матричные вычисления и математическое обеспечение.- М.: Мир, 1984. . Кашаев, С. М. Работа в Excel 2007/ С. М. Кашаев.- СПб.: Питер, 2009.- 144 с.: ил. . Серогодский, В.В. Excel 2007. Эффективный самоучитель + справочник пользователя / В.В. Серогодский. - М.: СПб: Наука и Техника, 2015. - 400 c.

Цель работы: познакомиться с приемами решения задач линейной алгебры.

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

МОПРЕД(массив) — функция для нахождения определителя квадратной матрицы. Здесь массив — числовой массив с равным количеством строк и столбцов;
МОБР(массив) — функция для нахождения обратной матрицы. Здесь массив — числовой массив с равным количеством строк и столбцов;
МУМНОЖ(массив1;массив2) — функция для нахождения произведения массива1на массив2.Результатом является массив с таким же числом строк, как массив1, и с таким же числом столбцов, как массив2. При этом если обозначить буквой С результат произведения двух массивов А и В, то элементы массива С определяются по формуле , где i — номер строки, а j — номер столбца;
ТРАНСП(массив) — функция для транспонирования массива ячеек на рабочем листе. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вторым столбцом нового массива и т.д.

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

Шаг 1. Сначала формула вводится в первую ячейку результирующего массива.

Шаг 2.Выделяется весь блок ячеек результирующего массива.

Шаг 3.Нажимается клавишаклавиатуры.

Шаг 4.Нажимается комбинация клавиш Ctrl+Shift+Enter. При этом формула распознается как формула массива и заключается в фигурные скобки < >.

При работе с массивами чисел всегда контролируйте размерность результирующего массива!

ЗАДАНИЕ 1. Решение задач линейной алгебры

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

Найдите матрицу А-1, обратную к данной матрице А.
Найдите определитель матрицы А.
Проверьте, что найденная матрица А-1 действительно является обратной для матрицы А.

1. Откройте чистый рабочий лист. Переименуйте его в Обратная матрица.

2. Заполните рабочий лист исходными данными, как показано на рис. 14:

3. Установите курсор в ячейку Е2 и введите формулу =МОБР(А2:С4). После нажатия клавиши Enter в ячейке Е2 появится число 0,4.

4. Для получения обратной матрицы формулу в этом примере необходимо ввести как формулу массива. Для этого выделите диапазон ячеек Е2:G4, соответствующий обратной матрице (размерность матрицы А-1 очевидно будет такая же, как и у матрицы А). Нажмите клавишу , а затем комбинацию клавиш Ctrl+Shift+Enter. В результате ячейки Е2:G4 будут заполнены элементами обратной матрицы.

5. В ячейку А6 введите текст Определитель(A).

6. Установите курсор в ячейку В6 и введите формулу = МОПРЕД(А2:С4). Нажмите клавишу Enter. В ячейке должно получиться значение определителя матрицы А, равное 5. Внимание!В этом случае формулу для расчета определителя не нужно вводить как формулу массива, так как определитель является не массивом, а одним числом.

7. Для проверки правильности нахождения обратной матрицы вспомним, что должно выполняться условие: А А-1=Е, где Е — единичная матрица того же порядка, что и матрица А. Установите курсор в ячейку Е6 и введите текст Произведение матриц.

8. В ячейку Е7 введите формулу = МУМНОЖ(А2:С4; Е2:G4). Нажмите клавишу Enter.

9. Формулу в этом случае также вводим как формулу массива. В результате ячейки Е7:G9 будут заполнены элементами единичной матрицы (по главной диагонали будут записаны 1, остальные элементы будут равны 0). Если числа имеют много десятичных знаков, то выделите ячейки Е7:G9 и уменьшите разрядность либо при помощи команды меню Формат/Ячейки/закладка Число, либо при помощи кнопки Уменьшить разрядность .

ТЕСТОВОЕ ЗАДАНИЕ 1. Решение систем линейных уравнений

Вариант вашего задания задает преподаватель!

Откройте чистый рабочий лист. Переименуйте его в Тест 1.Решите систему линейных уравнений (см. табл. 5) по формулам Крамера.

Формулы Крамера имеют вид:

Здесь D- определитель системы, а Di — вспомогательный определитель, который получается из исходного определителя D путем замены i-го столбца на столбец свободных членов уравнений системы.

№	Решить систему	№	Решить систему

ТЕСТОВОЕ ЗАДАНИЕ 2. Решение уравнений, записанных в матричной форме

Вариант вашего задания задает преподаватель!

Откройте чистый рабочий лист. Переименуйте его в Тест 2.Решите матричные уравнения, выбрав свой вариант из таблицы 6.

Примечание. Заданное уравнение нужно свести к одному из двух видов:

1) А Х=В, здесь решение находится по формуле Х=А-1 В

2) Х А =В, в этом случае решение находится по формуле Х=В А-1.

Если исходное уравнение имеет вид С Х А =В, то его предварительно приведите к виду: Х А = С-1 В, рассчитайте правую часть системы и затем воспользуйтесь предложенными формулами для нахождения решения системы.

№	Уравнение	№	Уравнение

Статьи к прочтению:

Курс Excel_Базовый — Урок №8. Абсолютные и относительные ссылки

Похожие статьи:

Цель работы: познакомиться с возможностями организации циклических вычислений на примере расчетов сумм и произведений. Обычно при расчетах сумм или…

Загрузить презентацию (764 кБ)

Тема “Решение математических задач средствами EXCEL”, является значимой в курсе “Информатика и информационные технологии”, которая возникает на различных этапах изучения предмета. Например, вычисления алгебраических выражений, решения квадратных уравнений в различных средах, построение графиков функций и т.д.

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

Данный урок можно отнести к интегрированным урокам, построенным на деятельной основе с применением проблемно-исследовательской технологии. Ценность урока заключается в том, что ученики решают стандартные математические задачи нестандартным способом – применяя современные компьютерные технологии. Этим достигается мотивационная цель – побуждение интереса, показ необходимости знаний по математике и информатики в реальной жизни. На уроке ученики покажут владение компьютером, умение работать с пакетом программ Microsoft Office, знания, умения и навыки, полученные на уроках математики. В результате будет достигнута образовательная цель урока: по математики обобщение знаний по темам: “Матрицы. Действия с матрицами. Решение систем линейных уравнений методом Крамера, Гаусса”, по информатике у учащихся формируется навык работы с табличными формулами, познакомятся с возможностями Excel для решения различных уравнений и систем уравнений.

11 класс, информатика.

Тема: “Применение табличного процессора MS Excel для решения систем линейных алгебраических уравнений”.

Тема рассчитана на два урока.

Тип урока: комбинированный урок, совершенствование знаний, умений и навыков.

Вид урока: интегрированный.

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

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

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

Формы организации познавательной деятельности: фронтальная, индивидуальная, групповая, коллективная.

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

Оборудование: доска, компьютеры, мультимедийный проектор и экран, презентация, карточки с индивидуальным заданием, папка с электронным материалом для урока.

Средства обучения: презентация учителя MS PowerPoint “Решение математических задач средствами Excel”, ресурсы Интернет.

Компьютерное программное обеспечение: пакет программ Microsoft Office 2007.

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

2. Актуализация опорных знаний.

Учитель. Для успешного проведения занятия по теме нам необходимо будет вспомнить и повторить материал из уроков математики “Методы решения линейных систем уравнений” и из информатики “Работа с формулами в Excel. Логические формулы. Относительные и абсолютные ссылки”.

Откройте файл D://Уроки_11/Решение СЛАУ/Приложение 2. У учащихся файл без листа Решение.

Заполните все поля таблицы.

Фронтальная работа с учащимися по проверки знаний и умений работы с формулами и функциями в Excel. На экране демонстрируется пример таблицы,

в которой необходимо заполнить все поля. Учащиеся предлагают алгоритмы заполнения полей. В тетради выписывают формулу для заполнения столбца K (победители, призеры), далее сравнивают свое решение с решением, представленным на экране (лист Решение, Приложение 2).

3. Изучение нового материала.

Какие методы решения линейных уравнений вы знаете? Если не просмотрели файл выложенный в домашнее задание предыдущего урока, то можете открыть файл D://Уроки_11/Решение СЛАУ/Приложение 1.

Метод последовательного исключения неизвестных, метод Крамера.

Посмотрите описание метода Крамера, с какими элементами нужно уметь работать при применении этого метода?

Т.е. с матрицами, на экране демонстрируется пример матрицы . Откройте файл D://Уроки_11/Решение СЛАУ/Приложение 3, лист Пример и выполните задание.

Учащиеся открывают документ Приложение 3 (лист Пример 1).

Выполняются задания, представленные на экране.

Для работы с матрицами в Excel существуют специальные формулы, формулы для работы с массивом или их ещё называют табличные формулы.

Презентация. Слайд 3, 4. Учащиеся записывают понятие табличной формулы и особенности её ввода.

4. Подготовка к осмысление и применение изученного материала. Практическая работа.

1. Для решения, каких задач можно применять табличные формулы?

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

2. Дайте понятие матрицы? Может ли сказать, что любая прямоугольная таблица, заполненная числовыми значениями, есть матрица?

Ответ утвердительный. Слайд 5

3. Какие виды матриц вы знаете, чем они отличаются друг от друга? (заполнение, размерность и т.д.)

После обсуждения представить Слайд 6.

4. Можно ли с матрицами производить какие-либо действия?

Учащиеся могу перечислить некоторые действия с матрицами, сложение, умножение на число и т.д. Слайд 7.

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

Ученики записывают тему пункта темы Слайд8.

Повторение, обобщение математических знаний, дополненных демонстрацией новых функций Excel.

Презентация Слайды 9-14.

Демонстрация каждого слайда предопределяется вопросами по теме слайда.

В тетрадь учащиеся записывают только функции Excel для работы с матрицами и одновременно выполняют тренировочные практические задания из Приложение 3 Листы: пример 2, пример 3, пример 4. Подробно остановиться на примере 5, Приложение 3, Слайд 14.

Теперь непосредственно перейдем к решению СЛАУ и познакомимся с методом, который вы рассматривали на уроках математики, это матричный метод. Слайд 16. Как вы думаете почему вы не решали системы матричным методом?

Сложность вычисления обратной матрицы

Запишите в тетрадь алгоритм решения системы матричным способом.

Учитель открывает файл – заготовку упражнения и вместе с учащимися решает упражнение.

Рассмотрим теперь решение СЛАУ методом Крамера, этот метод вам знаком, но на уроках математики вы решали, в основном, системы из двух уравнений с двумя неизвестными, почему? Слайд 22.

Нужно много времени для вычисления определителей.

Возможности Excel решают эту проблему. Откройте новый лист в книге и вместе решим систему уравнений представленную на экране.

Свои решения учащиеся сравнивают с решением, представленным в презентации. Слайды 23-25.

5. Закрепление (эвристическая беседа, тренировка, отработка умений).

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

Домашнее задание. На выбор:

1. (Приложение 4) Выполнить один из вариантов из карточки , разобрать программы решения систем уравнений на языке Паскаль из теоретического материала (Приложение 1)

2. Выполнить один из вариантов из карточки. Создать отдельно программу для решения систем методом Гаусса или матричным методом, группе программистов доработать программу метод Крамера.

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

Предложенное занятие по содержанию и выполнению заданий, кажется, насыщенным и перегруженным теорией и практическими упражнениями, но применение презентации, заготовок файлов (приложение 3) помогает выполнить все запланированные действия. Такое занятие рекомендуется проводить в математических классах, когда учащиеся уже изучили методы решения СЛАУ. За неделю до изучения этой темы выложить в эл. дневник, для ознакомления, информационный материал по методам решения систем уравнений и описание создания программ для решения систем уравнений на языке программирования.

1. Воронина Т.П. Образование в эпоху новых информационных технологий / Т.П. Воронина.- М.: АМО, 2008. -147 с.

2. Глинская Е. А. Межпредметные связи в обучении / Е.А. Глинская, С.В. Титова. – 3-е изд. – Тула: Инфо, 2007. - 44 с.

3. Данилюк Д. Я. Учебный предмет как интегрированная система /Д.Я. Данилюк //Педагогика. - 2007. - № 4. - С. 24-28.

4. Иванова М.А. Межпредметные связи на уроках информатики / М.А. Иванова, И.Л. Карева // Информатика и образование. – 2005. - №5. – С. 17-20.

5. А.В. Могилев, Н.И. Пак, Е.К. Хеннер "Информатика", Москва, ACADEMA, 2000 г.

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

Д
ля вычисления производной в Excel будем использовать приведенную зависимость.

Рассмотрим методику вычисления производной на примере упражнения.

Допустим требуется найти производную функции Y= 2x 3 + x 2 в точке x=3. Производная, вычисленная аналитическим методом, равна 60.

Для вычисления производной выполните следующие действия:

т
абулируйте заданную функцию в окрестности точки х=3 с достаточно малым шагом, например 0,001 (см рис.)

в ячейку С2 введите формулу вычисления производной. Здесь ячейка В2 содержит значение х к+1 , ячейка А2 - х к .

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

Для значения х =3 производная функции равна значению 60,019, что близко к значению, вычисленному аналитически.

1.2.Численное вычисление определенных интегралов

Д
ля численного вычисления определенного интеграла методом трапеций используется формула:

Методику вычисления определенного интеграла в Excel с использованием приведенной формулы рассмотрим на примере.

Пусть требуется вычислить определенный интеграл

Величина интеграла, вычисленная аналитически равна 9. Для численного вычисления величины интеграла с использованием приведенной формулы выполните следующие действия:

табулируйте подинтегральную функцию в диапазоне изменения значений аргумента 0 – 3 (см. рис.).

в ячейку С3 введите формулу =(A3-A2)*B2+(A3-A2)*(B3-B2)/2+C2, которая реализует подинтегральную функцию.

С
копируйте буксировкой формулу, записанную в ячейке С3 до значения аргумента х = 3. Вычисленное значение в ячейке С17 и будет величиной заданного интеграла - 9.

1.3.Нахождение экстремумов функций с помощью инструмента Поиск решения

Если функция F(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезка локальный экстремум, то его можно найти используя надстройку Excel Поиск решения .

Рассмотрим последовательность нахождения экстремума функции на примере следующего упражнения.

Пусть задана неразрывная функция Y= X 2 +X +2. Требуется найти ее экстремум (минимальное значение).

Для решения задачи выполните действия:

В ячейку А2 рабочего листа введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение Х;

В ячейку В2 введите формулу, определяющую заданную функцию. Вместо переменной Х в этой формуле должна быть ссылка на ячейку А2: =A2^2 + A2 +2

Выполните команду меню Сервис/Поиск решения;

Настройте параметры инструмента Поиск решения : число итераций – 1000, относительная погрешность 0,00001.

в поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу ( А2), установите переключатель Минимальному значению, в поле Изменяя ячейки введите адрес ячейки, содержащей Х (А2);

Щелкните на кнопке Выполнить. В ячейке А2 будет помещено значение Х функции, при котором она имеет минимальное значение, а в ячейке В2 – минимальное значение функции.

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

1.4.Решение систем линейных уравнений

1.4.1.Встроенные функции для работы с матрицами

В библиотеке Excel в разделе математических функций есть функции для выполнения операций над матрицами (табл.1.1).

Русифицированное имя функции

Англоязычное имя функции

вычисление определителя матрицы

МУМНОЖ (список параметров)

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

МОБР (А1: B2) или МОПР (матрица_1).

1.4.2.Решение систем линейных уравнений

Известно, что система линейных уравнений в матричном представлении записывается в виде:

Решение такой системы записывается в виде

Где A-1 –матрица, обратная по отношению к А.

1.4.3.Пример решения системы линейных уравнений:

П
усть система уравнений задана матрицами:

Для решения задачи выполните действия:

Выделите диапазон размерностью 2 х 2 и присвойте ему имя А ;

Выделите диапазон размерностью 1 х 2 и присвойте ему имя В ;

Выделите диапазон размерностью 1 х 2 и присвойте ему имя Х ;

Используя список имен выделите диапазон А и введите в него значения элементов матрицы А;

Используя список имен выделите диапазон В и введите в него значения элементов вектора В;

Используя список имен выделите диапазон Х для помещения результата решения системы;

В выделенный диапазон Х введите формулу

Укажите Excel, что выполняется операция над массивами, для этого нажмите комбинацию клавиш + + , в ячейках диапазона Х будет получен результат: х1=2,16667, х2= - 1,33333

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

Решите систему уравнений вида AX=B и выполните проверку решения

1.5.Решение нелинейных уравнений методом подбора параметра

Используя возможности Excel можно находить корни нелинейного уравнения в допустимой области определения переменной. Последовательность операций нахождения корней следующая:

Уравнение представляется в виде функции одной переменной;

Производится табулирование функции в диапазоне вероятного существования корней;

По таблице фиксируются ближайшие приближения к значениям корней;

Используя средство Excel Подбор параметра, вычисляются корни уравнения с заданной точностью.

Рассмотрим последовательность отыскания корней нелинейного уравнения на примере.

Требуется найти все корни уравнения X 3 -0,01X 2 -0,7044X+0,139104=0 на отрезке [-1 ; 1]. Правая часть уравнения представлена полиномом третьей степени, следовательно, уравнение может иметь не более трех корней.

представим уравнение в виде функции

Y = X 3 -0,01X 2 -0,7044X+0,139104

Известно, что корни исходного уравнения находятся в точках пересечения графика функции с осью Х.

Для локализации начальных приближений необходимо определить интервалы значений Х, внутри которых значение функции пересекает ось абсцисс, т.е. функция меняет знак. С этой целью табулируем функцию на отрезке [–1;+1] с шагом 0,2, получим табличные значения функции. Из полученной таблицы находим, что значение функции трижды пересекает ось Х, следовательно, исходное уравнение имеет на заданном отрезке все три корня.

Анализ таблицы показывает, что функция меняет знак в следующих интервалах значений аргумента Х: (-1;-0,8), (-0,2;0,4) и (0,6;0,8). Поэтому в качестве начальных приближений возьмем значения Х: -0,8; -0,2 и 0,6 .

На свободном участке рабочего листа, как показано на рисунке, в ячейки А15: A17 введите начальные приближения, а соответствующие ячейки столбца В скопируйте формулу.

Выполните команду меню Сервис/Параметры, во вкладке Вычисления установите относительную погрешность вычислений E=0,00001, а число итераций N=1000, установите флажок Итерации.

Выполните команду меню Сервис/Подбор параметра . В диалоговом окне заполните следующие поля:

Установить в ячейке : в поле указывается адрес ячейки, в которой записана формула правой части функции;

Значение : в поле указывается значение, которое должен получить полином в результате вычислений, т.е. правая часть уравнения (в нашем случае 0);

Изменяя значение : в поле указывается адрес ячейки (где записано начальное приближение), в которой будет вычисляться корень уравнения и на которую ссылается формула.

После щелчка на ОК получим значение первого корня: -0,92.

Выполняя последовательно операции аналогичные предыдущим, вычислим значения остальных корней: -0,209991 и 0,720002.

1.6.Решение систем нелинейных уравнений

Применяя надстройку Excel Поиск решения можно решать системы нелинейных уравнений. Предварительно система уравнений должна быть приведена к одному уравнению. Рассмотрим последовательность решения на примере упражнения.

Д
ана система двух уравнений:

Требуется найти все корни приведенного уравнения для диапазона значений х и y [-3; 3].

Шаг 1 . Приведем систему к одному уравнению. Пара (x, y) является решением системы тогда и только тогда, когда она является решением следующего уравнения с двумя неизвестными:

(x 2 + y 2 – 3) 2 + (2x + 3y – 1) 2 = 0

Шаг 2. Для решения последнего уравнения необходимо найти начальные приближения, для этого табулируем выражение, стоящее в левой части как функцию по двум переменным x и y. Для табуляции функции выполните следующие действия:

В столбец А введите последовательность значений Х с шагом 0,5, а строку 3 – последовательность значений У также с шагом 0,5.

Присвойте диапазонам значений Х и У имена Х и У, соответственно.

Выделите диапазон ячеек, в котором будут вычисляться значения функции (B4:N16).

В выделенный диапазон введите формулу

Нажав комбинацию клавиш [Ctrl]+[Shift]+[Enter] выполните операцию над выделенным массивом. В выделенном диапазоне появятся вычисленные значения функции.

Шаг 3. Найдем начальные приближения. Поскольку табулируемая функция задает поверхность, то начальные приближения следует искать во впадинах, т.е. в точках, где функция принимает наименьшие значения. На рисунке эти точки затемнены. Начальными приближениями являются пары (-1;1) и (1,5; -0,5).

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

Шаг 4 . В ячейку строки, в которой записана первая пара Х и У введите формулу, вычисляющую значение функции:

и скопируйте ее в следующую строку.

Шаг 4 . Установите курсор на ячейку, в которой записана формула и выполните команду меню Сервис/Поиск решения . Выполните настройку параметров инструмента Поиск решения: Предельное число итераций – 1000, относительная погрешность 0,000001.

В окне Поиск решения в качестве целевой ячейки установите адрес ячейки, содержащей формулу, взведите переключатель Минимальному значению, в поле Изменяя ячейки укажите адрес диапазона, содержащего начальные приближения и щелкните на ОК. В ячейках, где хранились начальные приближения будет получена первая пара корней.

Повторите такие же операции для второй пары приближений.

Р
ешением системы являются пары (-1,269; 1,1791) и (1,5764; -0,718).

Читайте также: