Svinkovod.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Решение уравнений в среде MS EXCEL

Решение уравнений в среде MS EXCEL

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

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

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

Нахождение корней уравнения с помощью подбора параметра Пример 1.

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

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

Построим модель решения этой задачи. За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: Ai*С+Вi, где С – оклад санитарки; Аi и Вi – коэффициенты, которые для каждой должности определяют следующим образом:

– медсестра получает в 1,5 раза больше санитарки (А2=1,5; В2=0);

– врач – в 3 раза больше санитарки (А3=3; В3=0);

– заведующий отделением – на 30 y. e. больше, чем врач (А4=3; B4=30);

– заведующий аптекой – в 2 раза больше санитарки (А5=2; В5=0);

– заведующий хозяйством – на 40 y. e. больше медсестры (А6=1,5; В6=40);

– заведующий больницей – на 20 y. e. больше главного врача (А8=4; В8=20);

– главный врач – в 4 раза больше санитарки (А7=4; В7=0);

Зная количество человек на каждой должности, нашу модель можно

где N1 – число санитарок, N2 – число медсестер и т. д.

В этом уравнении нам известны A1. . . A8, B1. . . B8 и N1. . . N8, а С неизвестно. Анализ уравнения показывает, что задача вычисления заработной платы свелась к решению линейного уравнения относительно С. Предположим, что зарплата у санитарки 150,00 y. e.

Введите исходные данные в рабочий лист электронной таблицы, как показано ниже.

Читайте так же:
Можно ли в повер поинте вставить видео

В столбце D вычислите заработную плату для каждой должности. Например, для ячейки D4 формула расчета имеет вид =B4*$D$3+C4.

В столбце F вычислите заработную плату всех работников данной должности. Например, для ячейки F3 формула расчета имеет вид =D3*E3.

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

Чтобы определите оклад санитарки так, чтобы расчетный фонд был равен заданному надо:

1. Активизировать команду Подбор параметра во вкладке Данные / Работа с данными /Анализ «Что, если»;

2. В поле «Установить в ячейке» появившегося окна ввести ссылку на ячейку F11, содержащую формулу;

3. В поле «Значение» набрать искомый результат 1000000;

4. В поле «Изменяя значение ячейки» ввести ссылку на изменяемую ячейку D3 и щелкните на кнопке ОК.

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

Приложение 1

Задание для учащихся:

Составить несколько вариантов штатного расписания с использованием функции Подбор параметра и оформить их в виде таблицы:

– Изменить количество сотрудников на различных должностях;

– Подобрать зарплату санитарки в новых условиях;

– Составить таблицу нескольких вариантов штатного расписания.

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

Пусть дано уравнение, записанное в виде x=F(x). Выбирают некоторое начальное приближение x1 и подставляют его вместо x в F(x). Полученное значение x2=F(x1) этой функции считают вторым приближением. Далее находят третье приближение по формуле x3=F(x2) и так далее. Таким образом, получаем последовательность x1, x2, x3,…, xn,… чисел, имеющая предел α. Тогда если функция F(x) непрерывна, из равенства xn+1=F(xn) получаем α=F(α). Это означает, что α является решением уравнения x=F(x).

Пример 2.

Пусть нам дан многочлен третьей степени:

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

Читайте так же:
Как включить защиту листа в excel

Метод деления отрезка пополам в excel

Создаем макрос:Сервис – Макрос – макросы. Задвем имя(решение). Вводим программу:

For X = Xmin To Xmax Step h

ElseIf X <= 3.5 Then

Запускаем программу: кнопка F5.

Сворачиваем программу и на листе MS Excel нам выдает данные:

Это и есть решение нашей функции.

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

Тема: Поиск экстремумов функции.

Цель. Рассмотреть методику нахождения экстремумов функции, составить блок-схему и программу вычисления в VBA.

Найти максимум на промежутке 0,2;1,5 с шагом 0,3. точность поиска экстремумов 10-5

Максимум функции будет, когда она начинает убывать. Если она начала убывать, то на этом промежутке находится максимальное значение У. для достижения заданной точности пользуемся правилом: если модуль(у-у1)> желаемой точности, то возвращаемся на 2 шага назад, уменьшаем шаг в 10 раз и опять повторяем до тех пор, пока условие не будет выполняться.

Мы водим в лист MS Excel начальные данные: Xmin, Xmax и h:

Создаем макрос:Сервис – Макрос – макросы. Задаем имя(экстремуму). Вводим программу:

For X = Xmin To Xmax Step h

Y = X ^ 3 — 6 * X ^ 2 + 9 * X + 4

Y = X ^ 3 — 6 * X ^ 2 + 9 * X + 4

Y1 = (X + h) ^ 3 — 6 * (X + h) ^ 2 + 9 * (X + h) + 4

Loop While Y1 > Y

Loop While Abs(Y — Y1) > eps

Cells(i + 2, 6) = XYmax

Cells(i + 2, 7) = Ymax

Запускаем программу: кнопка F5.

Сворачиваем программу и на листе MS Excel нам выдает данные:

Вывод. В ходе работы мы рассмотрели методику нахождения экстремума функции, составили блок-схему и программу вычисления в VBA.

Тема 3: Решение нелинейных уравнений.

Цель. Рассмотреть методы решения нелинейных уравнений : метод деления отрезка пополам. Составить алгоритм решения и реализовать программы в VBA.

Интервал 1;2 . точность 10-5

1) Метод деления отрезка пополам.

Делим отрезок пополам:

Определяют знак функции f(x) и выбирают ту половину отрезка, на концах которого функция принимает значение разных знаков и деление повторяется.

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

Мы водим в лист MS Excel начальные данные: a, b,f(x), eps:

Создаем макрос: Сервис – Макрос – макросы. Задаем имя(нелинейное). Вводим программу:

If f(a) * f(X) > 0 Then

Cells(i + 3, 5) = f(X)

Loop While Abs(X — X0) > eps

f = 0.8 * X ^ 4 — 2 * X ^ 2

Читайте так же:
Как войти в аккаунт яндекс браузера

Запускаем программу: кнопка F5.

Сворачиваем программу и на листе MS Excel нам выдает данные:

Основан на предположении, что на маленьком отрезке функция изменяется линейно. Тогда кривую можно заменить хордой и в качестве приближенного значения принять точку пересечения хорды с осью абсцисс. Точка Х находиться по формуле Х=a-((b-a)*f(a))/(f(b)-f(a)). Определяют знак функции f(x) и выбирают ту половину отрезка, на концах которого функция принимает значение разных знаков и деление повторяется.

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

Эти два метода очень похожи и различаются только способом нахождения Х. все остальное то же самое.

Мы водим в лист MS Excel начальные данные: a, b,f(x), eps:

Создаем макрос: Сервис – Макрос – макросы. Задаем имя(нелинейное). Вводим программу:

X = a — ((b — a) * f(a)) / (f(b) — f(a))

If f(a) * f(X) > 0 Then

Cells(i + 3, 5) = f(X)

Loop While Abs(X — X0) > eps

f = 0.8 * X ^ 4 — 2 * X ^ 2

Вывод. В ходе работы мы рассмотрели методы решения нелинейных уравнений: метод деления отрезка пополам и метод хорд. Составили алгоритм решения и реализовали программу в VBA.

Построение графиков функции и решение нелинейных уравнений в Excel

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

Подобные документы

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

контрольная работа, добавлен 20.11.2013

Численное решение нелинейных уравнений. Этапы решения уравнений. Уточнение корней методом половинного деления, хорд, касательных, простой итерации. Решение уравнений средствами Excel. Циклические ссылки и надстройка «Подбор параметра» и «Поиск решения».

реферат, добавлен 07.09.2009

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

методичка, добавлен 25.03.2014

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

реферат, добавлен 22.12.2011

Создание, форматирование электронной таблицы, выполнение вычислений в электронной таблице. Вставка функции в математической нотации как объект MS Equation. Создание столбца номеров точек. Построение быстрых, качественных графиков в программе Excel.

Читайте так же:
Как восстановить в excel несохраненные данные

лабораторная работа, добавлен 23.01.2015

Назначение электронных таблиц Excel для Windows. Работа с меню. Строки, столбцы, ячейки рабочего поля. Особенности ввода формул. Абсолютная, относительная, смешанная адресация ячеек и блоков. Решение нелинейных уравнений методом деления отрезка пополам.

контрольная работа, добавлен 08.12.2013

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

курсовая работа, добавлен 15.01.2016

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

курсовая работа, добавлен 21.04.2014

Классификация уравнений, основные признаки нелинейных уравнений, описание методов их решения. Способы и средства для решения уравнений в Mathcad. Алгоритм нахождения корня уравнения с помощью встроенной функции root. Решение системы нелинейных уравнений.

презентация, добавлен 11.05.2015

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

Метод деления отрезка пополам в excel

Постановка задачи

Дано нелинейное алгебраическое уравнение

f(x)=0 (1)

Нелинейность уравнения означает, что график функции не есть прямая линия, т.е. в f(x) входит x в некоторой степени или под знаком функции.

Решить уравнение – это найти такое x* ∈ R: f(x*)=0. Значение x* называют корнем уравнения. Нелинейное уравнение может иметь несколько корней. Геометрическая интерпретация корней уравнения представлена на рис. 1. Корнями уравнения (1) являются точки x1*, x2*, x3*, в которых функция f(x) пересекает ось x.

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

В приближенных методах процесс нахождения решения, вообще говоря, бесконечен. Решение получается в виде бесконечной последовательности <xn>, такой, что . По определению предела, для любого (сколь угодно малого) ε, найдется такое N, что при n>N, |xn x*|< ε. Члены этой последовательности xn называются последовательными приближениями к решению, или итерациями. Наперёд заданное число ε называют точностью метода, а N это количество итераций, которое необходимо выполнить, чтобы получить решение с точностью ε.

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

Читайте так же:
Можно ли залить тирет в посудомоечную машину

Наиболее часто используется следующий критерий остановки итерационного процесса: |xn+1xn|<ε, т.е. разница между соседними итерациями становится малой. Также для окончания итерационного процесса используется условие |f(xn)|<ε , где f(xn) – невязка метода.

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

Необходимое условие существования корня уравнения на отрезке [a,b]: Пусть f(x) непрерывна и f(a)f(b)<0 (т.е., на концах интервала функция имеет разные знаки). Тогда внутри отрезка [a, b] существует хотя бы один корень уравнения f(x)=0.

Достаточное условие единственности корня на отрезке [a,b]:

Корень будет единственным, если f(a)f(b)<0 и f / (x) не меняет знак на отрезке [a, b], т.е. f(x) – монотонная функция, в этом случае отрезок [a,b] будет интервалом изоляции.

Если корней несколько, то для каждого нужно найти интервал изоляции.

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

Аналитический способ состоит в нахождении экстремумов функции f(x), исследование ее поведения при и нахождение участков возрастания и убывания функции.

Графический способ – это построение графика функции f(x) и определение числа корней по количеству пересечений графика с осью x.

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

Решить уравнение x 3 ‑ 6x 2 +3x+11=0, т.е. f(x)= x 3 ‑ 6x 2 +3x+11.

Найдем производную f / (x)=3x 2 -12x+3.

Найдем нули производной f / (x)=3x 2 -12x+3=0; D=144-4*3*3=108;

Так как f / ( )>0, то f / (x)>0 при , f / (x)<0 при и f / (x)>0 при . Кроме того, f( )= <0, f( )= >0. Следовательно, на интервале возрастает от до f(x1)= 3x1 2 -12x1+3=11.39; на интервале — убывает до f(x2)= 3x2 2 -12x2+3=-9.39 и на интервале возрастает до , т.е. уравнение имеет три корня.

Найдем интервалы изоляции для каждого из корней.

Рассмотрим для первого корня отрезок [-2, -1]:

f(-2)= -27<0, f(-1)= 1>0, f / (x)>0 при т.е. этот отрезок является интервалом изоляции корня.

Рассмотрим для второго корня отрезок [1, 3]:

f(1)= 9>0, f(3)= -7<0, f / (x)<0 при т.е. этот отрезок является интервалом изоляции корня.

Рассмотрим для третьего корня отрезок [4, 5]:

f(4)= -9<0, f(5)=1>0, f / (x)>0 при т.е. этот отрезок является интервалом изоляции корня.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector