Лабораторная работа №3. Анализ данных в Excel

Задание 1: Найти корни уравнения (использовать инструмент Подбор параметра):

Задание 2: Решить систему нелинейных уравнений (использовать инструмент Поиск решения):

Ход работы:

Задание1:

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

1) Локализация корней. Находим отрезки, на которых находится один корень. Для этого протабулируем функцию на отрезке [-2;1]. Построим график функции, чтобы наглядно видеть отрезки, на которых функция меняет знак. В нашем случае находим следующие отрезки: [-1,4;-1,2], [0;0,2], [0,2;0,4]. В ячейки С5, С12 и С13 поместим начальные приближения корней на соответствующих отрезках (рис.3.1.):

Рис.3.1. Пример выполнения Задания1.

2) Уточнение корней. Вычислим значение функции в точках, которые приняли за приближение корней. В ячейках D5, D12 и D13разместим формулы, вычисляющие значения функции. Затем применяем средство «Подбор параметра» для уточнения корней на этих отрезках. Выделяем ячейку D5, вызываем команду Данные/Работа с данными/Анализ «что-если»/Подбор параметра. В поле Установить в ячейке указываем ссылку на ячейку D5, в поле Значение вводим значение 0, в поле Изменяя значение ячейки указываем ссылку на ячейку С5. Excelнайдет решение ипоместите его в ячейку C5. Аналогично находим для второго и третьего отрезков. В итоге получаем решение на рис. 3.2.:

Рис. 3.2. Результат выполнения Задания1.

Задание 2: Необходимо решить нелинейную систему уравнений:

Система содержит уравнение окружности и прямой. Пара (x,y) является решением системы уравнений в том случае, если она является решением уравнения с двумя неизвестными:

Чтобы решить это уравнение нужно протабулировать функцию двух переменных. За начальные приближения к корням уравнения выбрать пары (x,y), в которых функция ближе всего к нулю.

В столбец А поместим значения переменной x с шагом 0,2, в строку 2 поместим значения переменной y с шагом 0,2. После табулирования заметим, что наиболее близкие к нулю значения функция принимает в точках x=-0,4, y=-1,2, f(x,y)=0,55 иx=1, y=0,8, f(x,y)=0,35. Их и возьмем за начальные приближения. В диапазон С17:D17 поместим первую пару, в диапазон С18:D18 – вторую пару. В диапазон Е17:Е18 введем формулу, реализующую левую часть уравнения с двумя неизвестными.

Для нахождения первого корня выделяем ячейку Е17, вызываем команду Данные/Анализ/Поиск решения, вводим необходимые данные и нажимаем выполнить (рис 3.).

Рис. 3.3. Параметры Поиска решений

Аналогично находим решение для второй пары чисел. Получаем решение на рис. 4.:

Рис. 3.4. Результат выполнения Задания 2