РЕШЕНИЕ МАТЕМАТИЧЕСКИХ ЗАДАЧ СРЕДСТВАМИ MS EXCEL

Интенсификация

научных

исследований

и

инженерных

разработок

в

современном

мире

обусловила необходимость в программном обеспечении, позволяющем получать результат

сложных

математических

задач

в

приемлемые

сроки.

В

настоящее

время

программные

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

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

относящихся к свободному программному обеспечению. К их числу относятся, например,

MathLab, Math, Mathematica, Maple, Scilab, Maxima и др. Математические пакеты охватывают

основные

разделы

математики

и

позволяют

производить

большинство

необходимых

математических расчетов. Однако изучение математических пакетов – это дополнительная,

трудоемкая

задача.

В

то

же

время

в

курсе

информатики

обычно

включено

изучение

электронных

таблиц

MS

Excel.

MS

Excel

уступает

специализированным

математическим

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

Решение систем уравнений с двумя неизвестными графическим способом

Многие прикладные задачи в технике, экономике и других областях сводятся к решению

системы уравнений.

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

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

системы.

Графический

способ

зачастую

дает

только

приближенное

решение.

Точность

решения зависит от шага табуляции – чем меньше шаг, тем точнее решение.

Для решения системы уравнений необходимо:

1. определить диапазон аргумента х, на котором линии пересекутся;

2. привести (если это необходимо) уравнения системы к виду:

;

3. построить графики линий в одной плоскости;

4. определить координаты точек пересечения.

Рассмотрим пример решения системы уравнений.

Задача. Найти приближенное решение системы уравнений:













)

sin(

*

3

9

2

2

x

y

y

x

Этап 1. Создание рабочей таблицы с данными

Введем значения аргумента х в диапазоне [-2,5;2,5] с шагом 0.1.

В ячейку A2 введем первое значение аргумента из диапазона: -2,5 (в ячейке А1 - заголовок), в

ячейку А3 – второе значение аргумента: -2,4 (первое значение плюс шаг). Выделив диапазон

А2:А3, автозаполнением получим все значения аргумента (за правый нижний угол блока

протянем до ячейки А42). В последней ячейке должно быть последнее значение аргумента из

диапазона: 2

Вычислим значения для окружности. В ячейку В2 введем формулу: =КОРЕНЬ(9-А2^2).

Автозаполнением скопируем эту формулу в диапазон В3:В42.

Функция КОРЕНЬ возвращает только положительное значение, поэтому вычисленные

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

полуокружности. Они будут отличаться от вычисленных только знаком. Введем в ячейку С2

формулу: =-КОРЕНЬ(9-А2) и скопируем ее в диапазон С3:С42.

Вычислим значения для второй кривой. Введем в ячейку D2 формулу: =3*SIN(А2). Скопируем

формулу в диапазон D3:D42.

Этап 2. Построение графиков

Вызовем Мастер диаграмм (кнопка

на панели инструментов Стандартная). В

появившемся диалоговом окне выберем тип диаграммы гладкие графики.

Нажатием кнопки Далее перейдем к шагу 2 Мастера диаграмм. На первой вкладке Диапазон

данных указываем диапазон (выделяем с помощью мыши диапазон B11:D61). Необходимо

удостовериться, что выбрано Ряды в: столбцах.

Перейдем на вкладку Ряд и введем с помощью мыши диапазон подписей оси х: А11:А61

Можно пропустить шаг 3 и сразу нажать на кнопку Готово.

4.

На построенной диаграмме видно, что линии пересекаются в двух точках.

Следовательно, данная система имеет два решения (координаты точек пересечения).

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

левой кнопкой. Появится надпись с указанием искомых координат: Ряд «y=3sinx».

Точка «1». Значение: 2,79. (В надписи будет написано Ряд 3 вместо Ряд «y=3sinx»,

если на шаге 2 Мастера диаграмм не были введены названия рядов). Точка «1»

соответствует значению х, а Значение: 1, – соответствует у (если щелчок мыши был по

точке на верхней полуокружности, то значение для той же точки будет отличаться и

равняться 1,, поскольку метод дает только приближенные решения). Таким образом,

одно приближенное решение системы:

Х1=1,2 у1=2,79

Аналогично найдем второе приближенное решение системы:

Х2=-1,2 у2=-2,789

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

Графический метод дает лишь приближенное решение. Для решения систем линейных

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

линейных уравнений с использованием матриц и операций над ними.

Матрица размера

- прямоугольная таблица чисел, содержащая n строк и m

столбцов.

Элементы матрицы – числа, составляющие матрицу.

Матрицы обозначаются заглавными буквами латинского алфавита, а элементы матрицы –

строчными буквами с двойной индексацией:

- матрица А размера

Матрица-строка – матрица, состоящая из одной строки:

Матрица-столбец – матрица, состоящая из одного столбца:

Квадратная матрица – матрица, число строк у которой равно числу столбцов.

Для каждого числа

существует обратное число

и для квадратных матриц вводится

аналогичное понятие. Матрица, обратная по отношению к квадратной матрице А,

обозначается А-1 .

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

МОБР(массив),

где массив – это числовой массив с равным количеством строк и столбцов.

Как и над числами, над матрицами можно производить ряд арифметических операций. В

частности, можно вычислять произведение матриц. Но перемножать можно не любые

матрицы, а только те, у которых число столбцов первой матрицы равно числу строк второй.

Также нужно помнить, что умножение матриц некоммутативно, то есть АВ≠ВА.

Для нахождения произведения двух матриц используется функция из

категории Математические

МУМНОЖ(массив1; массив2),

где массив1 и массив2 – перемножаемые массивы

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

называется такая совокупность n чисел x1, x2, …, xn, при подстановке которых каждое

уравнение системы обращается в верное равенство.

Систему можно записать в виде матричного уравнения:

,

где А – матрица коэффициентов при переменных:

;

Х – матрица-столбец неизвестных:

;

В – матрица-столбец свободных членов:

.

Существует ряд методов решения систем, ориентированных на вычисления вручную.

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

целесообразно рассмотреть решение системы такого вида в общем виде (метод обратной

матрицы).

Решением системы таким методом будет матрица-столбец

.

Рассмотрим пример решения системы уравнений методом обратной матрицы.

Задача. Решить систему линейных уравнений

Решение

1. Введем матрицу А – значения коэффициентов при неизвестных. В нашем случае это будет

матрица, состоящая из 3 строк и 3 столбцов. Введем элементы матрицы в диапазон А1:С3

2. Введем вектор В – значения свободных членов в диапазон Е1:Е3

3. Найдем обратную матрицу А-1. Для этого необходимо:

выделить блок ячеек для результата такого же размера, что и матрица А. Например, А5:С7;

запустить Мастер функций (кнопка Вставка функций на панели

инструментов Стандартная) и найти в категории Математические функцию МОБР.

в поле Массив ввести диапазон матрицы А (А1:С3).

нажать сочетание клавиш CTRL+SHIFT+ENTER (иногда обратная матрица не появляется в

выделенном диапазоне, тогда необходимо повторить нажатие клавиш при выделенном

диапазоне). В результате в выделенном диапазоне А5:С7 должна появиться обратная

матрица:

4. Найдем решение системы уравнений – вектор Х. Для этого умножим обратную матрицу А-1

на вектор В. Необходимо:

выделить блок под результирующую матрицу (в нашем случае - вектор). Его размерность

будет n x m, где n – количество строк у матрицы А-1, а m –количество столбцов у

матрицы В. В нашем случае размерность будет 3х1. Выделим, например, диапазон Е5:Е7

выбрать функцию МУМНОЖ с помощью Мастера функций. В поле Массив1 ввести диапазон

матрицы А-1 (А5:С7), в поле Массив2 – диапазон матрицы-вектора В (Е1:Е3).

нажать сочетание клавиш CTRL+SHIFT+ENTER. В результате в выделенном диапазоне

появится вектор Х:

где х1=4, х2=2, х3=1 – решение системы уравнений

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

в исходное матричное уравнение

. То есть при умножении матрицы А на матрицу-

вектор Х должен получиться вектор В.

Числовые последовательности

При решении многих прикладных математических задач приходится рассматривать суммы,

составленные из большого количества слагаемых. Широко распространенными числовыми

последовательностями являются арифметическая и геометрическая прогрессии.

Для нахождения членов прогрессии в Excel можно использовать стандартные средства, а

также специальную процедуру Прогрессия (Правка → Заполнить → Прогрессия).

Рассмотрим примеры задач.

Задача. Найти сумму первых 8 членов арифметической прогрессии с а1=2 и разностью

прогрессии d=4.

Решение.

1. Найдем члены прогрессии. Можно это сделать двумя способами.

1 способ. Введем в ячейку А1 первый член прогрессии - 2, в ячейку А2 введем значение

второго с учетом разности – 6 (2+4). Выделим блок А1:А2 и автозаполнением найдем

остальные члены прогрессии (протянем до ячейки А8);

2 способ. Введем в ячейку А1 первый член прогрессии – 2. Выделим блок А1:А8. Запустим

процедуру (Правка → Заполнить → Прогрессия). В диалоговом окне в поле Шаг введем

значение разности прогрессии – 4 (необходимо удостовериться, что выставлены

значения: Расположение – по столбцам, Тип - арифметическая)

2 Найдем сумму полученных членов прогрессии. Для этого установим табличный курсор в

ячейку А9 и нажмем кнопку Автосумма

на панели инструментов Стандартная и вводим

диапазон суммирования А1:А8. Результат – сумма первых 8 членов арифметической

прогрессии – 128.

Задача. Вычислить все члены геометрической последовательности с первым членом b1=2 и

знаменателем q=2,5, не превосходящие 500.

Решение.

1. Введем в ячейку А1 значение первого члена прогрессии – 2.

2. Выполним команду Правка → Заполнить → Прогрессия.

3. В диалоговом окне Прогрессия переключатель Расположение поставим в положение по

столбцам, Тип – в положение геометрическая, в поле Шаг введем значение знаменателя

прогрессии – 2,5, в поле Предельное значение – 500.

Задания для самостоятельного решения

Графически решить систему:

Зависимость спроса на некоторый товар у от его цены х выражается уравнением

,

а зависимость предложения z от цены товара – уравнением

.

Найти точку равновесия - точку пересечения кривых спроса и предложения (решить систему

уравнений графическим способом).

Вычислите:

Найдите обратную матрицу для матрицы:

Решите систему уравнений:

Вычислите все члены арифметической прогрессии с а1=4 и разностью прогрессии d=3, не

превосходящие 17