РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ и систем уравнений ПРОГРАММНЫМИ СРЕДСТВАМИ Excel
Цели работы
1. Научиться использовать инструмент Подбор параметра для решения нелинейных уравнений вида f (x) = 0.
2. Освоить программный инструмент Поиск решения для решения систем нелинейных уравнений в среде Excel .
3. Освоить программные средства для поиска координат и значений экстремумов функции одной переменной с помощью инструмента Поиск решения.
Задание 1
Найти все корни уравнения .
Порядок выполнения работы
1. Дать рабочему листу название «Уравнение».
2. Настроить текущий документ Еxcel на вычисления с заданной точностью, открыв Настройку панели быстрого доступаи выбравДругие команды.На вкладке Формулы задать предельное число итераций, равное 10000, и относительную погрешность, равную 0,000001.
3. Найти решение нелинейного уравнения в два этапа.
Этап – ЛОКАЛИЗАЦИЯ КОРНЕЙ
4. Создать таблицу значений функции f (x) для х Î [‑2,5; 2,5], шаг изменения 0,1.
5. Построить график функции f (x) (тип – График).
6. Основываясь на данных таблицы и графика, выделить интервалы, на которых функция меняет знак (существует корень).
Этап – УТОЧНЕНИЕ КОРНЕЙ
7. Задать начальные приближения к корням – точки из отрезков локализации корней, например: ‑2,2; 0 и 1,5. Ввести эти значения в ячейки F16, F17 и F18 соответственно.
8. В ячейку G16 ввести формулу =(F16^3-4*F16+1)/(ABS(F16)+1) и с помощью маркера заполнения размножить эту формулу в ячейки G17 и G18.
9. На вкладке ДанныевыбратьАнализ «что-если»,открыть диалоговое окноПодбор параметра и заполнить его поля: в поле Установить в ячейке ввести G16(в этом поле дана ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения); в поле Значениеввести значение 0 (здесь указывается правая часть уравнения); в поле Изменяя значение ячейки ввести F16 (в этом поле дана ссылка на ячейку, отведенную под переменную). После нажатия кнопки OK средство подбора параметра находит приближенное значение корня с заданной точностью.
10. Повторить указанные в п. 9 действия для ячеек G17, F17 и G18, F18 соответственно. Ячейки F16:F18 содержат значения корней уравнения, а ячейки G16:G18 –значения функции в этих точках, близкие к нулю.
11. Оформить созданный лист заголовками и комментариями, как в образце (рис. 13).
K 
Рис. 13. Лист «Уравнение»
Задание 2
Найти решение системы нелинейных уравнений на интервале [– 1,7; 1,7] с помощью Excel, используя инструментПоиск решения.
Порядок выполнения работы
1. Дать рабочему листу название «Система».
2. Преобразовать уравнения системы относительно переменной у, получив две полуокружности в положительной и отрицательной полуплоскостях, пересекающих прямую.
3. Столбец А(аргумент x) заполнить с помощью арифметической прогрессии на интервале [– 1,7; 1,7] с шагом 0,1.
4. Столбцы B, C, D озаглавить как y1, у2, у3 и заполнить формулами, соответствующими полуокружностям и прямой, используя относительную адресацию и маркер заполнения.
5. Построить графики функций системы уравнений для диапазона А2:D36в одной системе координат и определить интервалы локализации корней.
6. Отредактировать диаграмму, согласно образцу (рис. 15).
7. На втором этапе– УТОЧНЕНИЕ КОРНЕЙ – найти корни системы уравнений с помощью инструмента Поиск решения.
8. Для нахождения первого корня в ячейку F25 ввести начальное приближение для первого корня по х. В ячейку G25 – начальное приближение для первого корня по у. В ячейку H25 ввести формулу = (F25^2+G25^2–3)^2+(2*F25+3*G25–1)^2
9. Чтобы получить уточненное значение первого корня, открыть диалоговое окно Поиск решения вкладки Данные. В поле Изменяя ячейки ввести диапазон ячеек F25:G25. В группе Равной установить переключатель в положение Значению, в поле ввода которого ввести 0. Убедитесь, что в диалоговом окне Параметры поиска решения снят флажок Линейная модель. Затем нажать кнопку Выполнить(рис. 14).
10. Вы получили приближенное решение в ячейках F25 и G25, а в ячейке H25 достаточно близкое к нулю значение и с заданной точностью приблизились к точке пересечения окружности с прямой линией. Если в ячейке H25 будет большое число, то решение найдено неверно.
Рис. 14. Инструмент «Поиск решения»
11. Аналогично находят второе решение. Но в качестве начального приближения принимают точку, близкую по координатам ко второму корню.
12. Проверить пару значений (1,5; 0). Область притяжения какого корня вы получаете?
13. Оформить созданный документ заголовками и комментариями (рис. 15).
Задание 3
Найти экстремумы функции на интервале [‑ 2; 2] с шагом 0,1. Точность поиска e = 10 -6
Порядок выполнения работы:
1. Дать рабочему листу название «Экстремум».
2. Задав значения аргумента (столбец А) и рассчитав значения функции (столбец В), построить график функции и определить интервалы локализации экстремумов.
3. На этапе– УТОЧНЕНИЕ ЭКСТРЕМУМОВ –найти уточненные значения координат экстремумов и значения функции в этих точках с помощью инструмента Поиск решения.
Рис. 15. Лист «Система»
4. Для нахождения первого экстремума (максимума) в ячейку F17 ввести начальное приближение, в ячейку G17 ввести формулу = F17^3 – F17^2 + 4.
5. Чтобы получить уточненное значение максимума, открыть диалоговое окно Поиск решения вкладки Данные.В поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу – G17. В группе Равной установить переключатель в положение Максимальному значению.В поле Изменяя ячейки ввести адрес ячейки F17. Затем нажать кнопку Выполнить.
6. Получено приближенное значение координаты экстремума и максимальное значение функции в ячейках F17 и G17.
7. Аналогично находят второй экстремум. Но при настройке диалогового окна Поиск решенияв группе Равной установить переключатель в положение Минимальному значению.
8. Оформить созданный документ заголовками и комментариями (рис. 16).
Отчет о лабораторной работе должен содержатьэлектронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненные задания описательной части работы и задания по предложенному преподавателем варианту.
Примечание. Если на вкладке Данныене оказалось Поиска решения, то его нужно подгрузить, используя Настройку панели быстрого доступа.
Решение систем уравнений в среде Microsoft Excel
обучающие:
- повторение и закрепление знаний учащихся правил записи арифметических выражений и формул в электронных таблицах;
- повторение алгоритма решения систем уравнений;
- формирование знаний и умений в решении систем уравнений, используя возможности электронных таблиц;
развивающие:
- формирование умений анализировать, выделять главное, сравнивать, строить аналогии;
воспитывающие:
- осуществление эстетического воспитания;
- воспитание аккуратности, добросовестности.
Тип урока: урок закрепления изученного материала и объяснения нового.
ХОД УРОКА
I. Организационная часть.
Здравствуйте! Все мы знаем, что одну и ту же информацию можно закодировать любым способом. Перед вами набор чисел. Известно, что каждому числу ставится в соответствие буква в русском алфавите. Расшифруйте эту информацию, кто быстрее!
Ответ: “Знание – сила!”
Молодцы! А знаете, кому принадлежит это выражение? (Если нет, то один ученик ищет ответ в Интернете. Остальные отвечают на вопросы: Для чего предназначена программа Excel? (Программа Excel предназначена для хранения и обработки данных, представленных в табличном виде) Что собой представляет документ в Excel? (Каждый документ в Excel представляет собой набор таблиц – рабочую книгу, которая состоит из одного или многих рабочих листов) Какая функция используется для подсчета суммы чисел? (Функция СУММ). Как определить адрес ячейки? (Excel вводит номера ячеек автоматически. Адрес ячейки составляется как объединение номеров столбца и строки без пробела между ними)
Выражение английского философа Френсиса Бэкона “Знание – сила!” и будет эпиграфом к нашему уроку. ("Нравственные и политические очерки", 1597).
II. Повторение пройденного материала.
Мы уже знакомы с программой Microsoft Excel, умеем записывать арифметические выражения и различные формулы, находить значения арифметических выражений и построить графики функций. Чтобы проверить выполнение домашнего задания, предлагаю каждому пройти тестирование. (Приложение 1)
Хорошо, все справились и каждому поставим соответствующие оценки в журнал. А давайте устроим путешествие в математику и вспомним, что мы понимаем под понятием: “Решить систему уравнений”? (Найти такие значения х и у, которые будут удовлетворять и первое уравнение и второе). Какие способы существуют для решения систем уравнений (метод подстановки, метод сложения и графический способ). Сегодня мы с вами научимся решать системы уравнений, используя возможности электронных таблиц.
III. Объяснение нового.
А. Решим систему графическим способом. Преобразуем данную систему . Для решения воспользуемся диаграммой, на которой отобразим графики обеих функций. Заполняем столбец А: заполняем ячейки А2:А22 числами от -5 до 5 с шагом 0,5. (в ячейку А2 заносим число -5, в ячейку А3 – число -4,5, выделяем ячейки А2 и А3, установим курсор мыши на правый нижний угол рамки (указатель примет форму черного крестика) и растягиваем рамку вниз, пока последнее значение не станет равным 5). При заполнении столбца В в ячейку В2 заносим формулу =А2*А2, которую затем копируем до ячейки В22. (протянем формулу за правый нижний угол). При заполнении столбца С в ячейку С2 заносим формулу =1-2*А2, копируем ее до ячейки С22. Выделим блок с данными, с помощью Мастера диаграмм выберем тип диаграммы Точечная и построим графики функций. Координаты точек пересечения графиков – решения системы.
Получены приближенные значения решений. Чем меньше шаг, тем точнее значение координат точек пересечения.
Запишем алгоритм решения систем уравнений графическим способом:
1. Преобразовать систему уравнений, если это необходимо.
2. Задать начальные значения для Х.
3. Найти значение первой функции при заданных Х.
4. Найти значение второй функции при тех же Х.
5. Выделить блок с данными и построить графики функций, используя точечный тип диаграммы.
6. Решение системы — точка пересечения графиков функций.
7. Для нахождения координат точек пересечения с заданной точностью построить новый график на том отрезке, где находится решение, с шагом, равным значению точности.
Б. Решить систему уравнений . Занесем в электронную таблицу исходные данные и расчетные формулы следующим образом:.
Для решения системы уравнений воспользуемся надстройкой Поиск решения, которая запускается через Сервис (-Надстройки) и заполним диалоговое окно следующим образом:
При нажатии на кнопку Выполнить происходит решение системы уравнений и в ячейках B3 и B4 высвечивается результат.
Запишем примерный алгоритм решения системы уравнений, используя Поиск решения
1. Преобразовать систему уравнений, если это необходимо
2. Записать исходные данные (в ячейку А1 ввести текст “Решите уравнение”, в ячейку В1 записать первое уравнение, в ячейку В2 второе уравнение, в ячейку А3 ввести текст “Х=”, в ячейку А4 “Y=”, в ячейку А5 “уравнение 1”, в ячейку А6 “уравнение 2”. В ячейке B3 хотим получить значение Х, в ячейке В4 – значение Y, их оставляем пустыми.
3. В ячейку В5 переписать уравнение 1, используя правило записи арифметических выражений, следующим образом: в левой части вместо Х указывать ячейку В3, вместо Y ячейку В4, правую часть отбросить. Таким же образом переписать левую часть второго уравнения в ячейку В6.
4. Выбрать команду Сервис – Поиск решения.
5. Установить целевую ячейку — ту ячейку, в которой содержится формула, например, В5 и задать значение, равное значению правой части первого уравнения
6. В поле “изменяя ячейки” указать ячейки, в которых хотим увидеть ответ (В3 и В4)
7. Вести ограничение $B$6 = -3. Для этого щелкнуть на кнопке Добавить и в полученном окне установить реквизиты следующим образом: в поле Ссылка на ячейку указать ячейку, в которой записана левая часть другого уравнения, в другом поле выбрать знак “=”, в третьем ввести число, равное значению правой части. Закрыть окно Добавить ограничение, щелкнув кнопкой ОК
8. Решить систему уравнений, щелкнув кнопкой Выполнить
IV. Практическая работа на компьютере.
А. Решите систему уравнений графическим способом
Б. Решите систему уравнения, воспользовавшись командой Поиск решения:
А. Решите систему уравнений графическим способом
Б. Решите систему уравнения, воспользовавшись командой Поиск решения:
V. Подведение итогов.
Повторить алгоритмы решения систем уравнений
Выставить оценки за тестирование в журнал
VI. Домашнее задание.
Решить рациональным способом системы уравнений: