MS_EXCEL

Решение уравнений с помощью MS EXCEL.

Построение графиков функций.

Графиком функции является наглядное отображение зависимости изменения одной величины, называемой функцией, от другой, называемой аргументом. Традиционно на двумерной координатной плоскости по горизонтальной оси (оси абсцисс) откладывается значение аргумента x, а по вертикальной оси (оси ординат) – значение функции y=f(x). Следует отметить, что современные программные средства позволяют строить график в трёх измерениях и отображать на экране монитора его двумерную проекцию. В табличном процессоре MS Excel график функции строится с помощью Мастера диаграмм, который вызывается командой Вставка/Диаграмма.

Выражение вида f(x)=0 называется уравнением, а значения x, при которых функция f(x) обращается в ноль, называются корнями уравнения.

Доказано, что аналитическое решение имеют алгебраические уравнения до третьего порядка включительно (кубические). Графический же метод позволяет найти решения большинства уравнений, которые не могут быть решены аналитически. Например, корнями трансцендентного уравнения sinx – lnx = 0, не умеющего аналитического решения, являются проекции точек пересечения синусоиды y=sinx и логарифмической кривой y=lnx на ось x, которые можно определить, построив графики обеих функций на одной координатной плоскости. В нашем случае можно также построить график функции y= sinx – lnx и показать, что точка его пересечения с осью абсцисс совпадёт с вышеназванной проекцией.

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

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

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

Табличный процессор MS Excel имеет встроенные программы, так называемые надстройки Подбор параметра и Поиск решения, производящие автоматизированный поиск уточнённого значения выделенного корня.

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

Для начала введём ряд определений.

Квадратным уравнением называется уравнение вида ax2 + bx + c = 0, где a,b.c – коэффициенты квадратного уравнения, на которые накладываются следующие условия:

a ≠ 0, a ≠ 1, то есть уравнение должно быть не вырожденным и не приведённым a:b:c m, где m – любое целое число, то есть коэффициенты квадратного уравнения не должны делиться на одно и то же число, или, что то же самое, они не должны быть кратны друг другу.

Дискриминантом квадратного уравнения называется выражение вида D = b2-4ac.

Если:

D < 0, уравнение действительных корней не имеет;

D = 0, уравнение имеет два действительных корня, равных друг другу:

x1 = x2 = (- b)/(2a);

D > 0, уравнение имеет два разных действительных корня, которые находятся по формулам:

x1 = (- b + √`D)/(2a), x2 = (- b – √`D)/(2a).

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

Для этого заполним ячейки B4:D6 данными в соответствии с приведённой таблицей, а в ячейки E4, F4 и G4 введём следующие формулы:

E4: C4*C4-4*B4*D4

F4: ЕСЛИ($E4>=0;ЕСЛИ($E4=0;-$C4/(2*$B4);-$C4/(2*$B4)+КОРЕНЬ($E4)/(2*$B4));”корней нет”);

G4: ЕСЛИ($E4>=0;ЕСЛИ($E4=0;-$C4/(2*$B4);-$C4/(2*$B4)-КОРЕНЬ($E4)/(2*$B4));”корней нет”).

В ячейках F4 и G4 для анализа дискриминанта D используется логическая функция ЕСЛИ. Работу формул в ячейках E4, F4 и G4 необходимо разобрать самостоятельно.

После копирования формул в ячейки E5, E6; F5, F6; G5, G6 соответственно, в этих ячейках появятся значения дискриминанта D и корней квадратного уравнения, или сообщение об отсутствии корней.

Для графического решения квадратного уравнения табличного процессора MS Excel необходимо выполнить следующие действия:

в ячейки H2:J2 коэффициенты квадратного уравнения;

в ячейку A2 ввести значение шага изменения аргумента X, в ячейку A3 ввести начальное значение аргумента X. Начальное значение выбирается на одно значение шага левее левого корня, предварительно полученного аналитическим путём, а значение шага выбирается так, чтобы между левым и правым корнем уравнения уложилось примерно десять значений аргумента (подбирается опытным путём);

в ячейку A4 ввести формулу A3+$A$2. Копируя её, заполняют столбец А значениями аргумента до конечного значения, которое должно быть на одно значение шага правее правого корня, предварительно полученного аналитическим путём;

в ячейку В3 ввести формулу ax2 + bx + c, которая в адресах соответствующих ячеек примет вид $H$2*A3*A3+$I$2*A3+$J$2, и размножить её копированием вниз по столбцу В до уровня последней заполненной ячейки столбца А;

выделить заполненные ячейки столбца В, и, после выполнения команды Формат/Ячейки/вкладка Число/формат Числовой в шаблоне справа выбрать параметр отображения отрицательных чисел красным цветом. Это позволяет легко определить значения, между которыми квадратичная функция меняет знак (называемые ограничениями), то есть, пересекает ось абсцисс. Следовательно, в соответствующих ячейках в столбце А находятся значения аргумента, между которыми лежит значение корня;

найденные ограничения для первого корня копируются в ячейки E5 и E6, а для второго корня – в ячейки E7 и E8 командами Правка/Копировать, затем Правка/Специальная вставка/параметр Значения;

в ячейки F1 и F2 необходимо скопировать, предварительно выделив их, значения из ячеек А2 и А3;

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

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

восстановить первоначальный вид таблицы (и, соответственно, графика), скопировав значения из ячеек F1 и F2 в ячейки А2 и А3.

Для осуществления автоматизированного процесса нахождения корней необходимо задать целевые и изменяемые ячейки. Целевые ячейки должны содержать формулу. В нашем случае это будет выражение ax2 + bx + c, скопированное из ячейки В3 посредством команд Правка/Копировать, затем Правка/Специальная вставка/параметр Формула в ячейки H5 и H7, J5 и J7.

Для случая подбора параметра необходимо в ячейки G5 и G7 предварительно ввести любые значения, близкие к соответствующим значениям корней, например, ограничения из ячеек Е5,Е6 и Е7,Е8 соответственно. Запуск надстройки Подбор параметра осуществляется с помощью команды Сервис/Подбора параметра. В открывшемся диалоговом окне необходимо в соответствующих полях задать адреса целевой (Установить в ячейке…) и изменяемой (Изменяя значение в ячейке…) ячеек, а также искомое значение (Значение, в нашем случае это ноль), и кнопкой ОК запустить подбор параметра. Через небольшой промежуток времени программа выдаст сообщение о результатах подбора и предложит сохранить их. В случае, если корень найден, следует сохранить результат и просмотреть его в изменяемой ячейке.

Для случая поиска решения необходимо в ячейки I5 и I7 предварительно ввести значения 0 (ноль). Запуск надстройки Поиск решения осуществляется с помощью команды Сервис/Поиск решения. В открывшемся диалоговом окне необходимо в соответствующих полях задать адреса целевой (Установить целевую ячейку…) и изменяемой (Изменяемая ячейка…) ячеек, а также искомое значение (РавнойЗначению, в нашем случае это ноль), а также добавить Ограничения. Ограничения устанавливаются с помощью специальных диалоговых окон, открывающихся по команде Добавить, в виде двух неравенств, операндами которых являются адреса ячеек. Для нашего примера это будет I5>=E5, I5<=E6 для первого корня и I7<=E8, I7>=E7 для второго корня. После добавления ограничений необходимо нажать кнопку Выполнить. Через небольшой промежуток времени программа выдаст сообщение о результатах поиска и предложит либо сохранить их, либо вернуться к первоначальным значениям. Если исходные данные введены правильно и решение найдено, его необходимо сохранить.

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

КОММЕНТАРИИ