Информатика (2 курс)

1

Тема 1.4 Использование программы Excel в линейной алгебре

Цель занятия: Научиться производить операции с матрицами в Excel.

Матрицей размера m*n называется прямоугольная таблица чисел, содержащая m строк и n столбцов. Числа, составляющие матрицу, называются элементами матрицы и обозначаются строчными буквами с двойной индексацией:

aij , где i – номер строки, j – номер столбца. Например,

Операции с матрицами.

Транспонирование

Транспонированной (обратной) называется матрица, в которой столбцы исходной матрицы заменяются строками с

Для осуществления транспонирования в Excel используется функция ТРАНСП в категории Ссылки и массивы, которая позволяет менять ориентацию массива. Функция имеет вид ТРАНСП(массив). Массив – это диапазон ячеек на рабочем листе, в котором записаны элементы матрицы.

Пример 1. Дана матрица А, получить транспонированную матрицу Ат.

Решение.

1.Введите на рабочий лист Excel предложенную матрицу (в ячейку число). Выделите указателем мыши блок ячеек под транспонированную матрицу (размер транспонированной матрицы: количество строк = количеству столбцов в исходной матрице). В данном случае размер исходной матрицы 2*5, транспонированной – 5*2 .

2.Меню Вставка – Функция, в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Ссылки и массивы, в рабочем поле функции – имя функции ТРАНСП.

3.В окне ТРАНСП укажите диапазон ячеек исходной матрицы. После чего нажмите сочетание клавиш

CTRL+SHIFT+ENTER.

4.В указанном вами диапазоне должна появиться транспонированная матрица.

Вычисление определителя матрицы.

Важной характеристикой квадратных матриц является их определитель. Определитель матрицы – это число, вычисляемое на основе значений элементов массива. Определить матрицы обозначается как À или . Определителем матрицы второго порядка, или определителем второго порядка, называется число, которое вычисляется по

а21 а22 а11а22 а12а21.

ВExcel для вычисления определителя матрицы используют функцию МОПРЕД в категории Математические. Функция имеет вид МОПРЕД (массив). Здесь массив – это числовой массив, в котором хранится матрица с равным количеством строк и столбцов.

Пример 2.

Решение.

1.На лист рабочей книги введите исходные данные в ячейки А1:С3.

2.Поставьте табличный курсор в ячейку А4.

3.Меню Вставка – Функция. В поле Категория выберите Математические, в рабочем поле Функция – имя функции МОПРЕД. Нажмите ОК.

4.В появившееся диалоговое окно МОПРЕД введите диапазон исходной матрицы. Нажмите ОК.

4

5.В ячейке А4 появится значение определителя (6).

Умножение матриц.

Произведение матриц определено, если число столбцов первой матрицы равно числу строк второй. Для нахождения произведения двух матриц в Excel используется функция МУМНОЖ, которая вычисляет произведение матриц (матрицы хранятся в массивах). Функция имеет вид МУМНОЖ(массив1; массив 2). Здесь массив 1 и массив 2 – это перемножаемые матрицы. При этом количество столбцов аргумента массив 1 должно быть таким же, как количество строк аргумента массив 2, оба массива должны содержать только числа. Результатом

(т.е. матрицей-произведением) является массив с таким же числом строк, как массив 1, и таким же числом столбцов, как массив 2.

Пример 3.

Решение:

1.Введите матрицу А в диапазон А1:D3, а матрицу В в диапазон А4:В7.

2.Выделите блок ячеек под результирующую матрицу. Для этого необходимо найти размер матрицыпроизведения. Ее размерность будет в данном примере 3*2. Выделите блок ячеек F1:G3.

3.Вызовите Мастер функций. В поле Категория выберите Математические, в поле Функции – имя функции МУМНОЖ. Нажмите ОК.

В появившемся диалоговом окне введите диапазоны массива 1 и массива 2 (соответственно А1:D3 и А4:В7). После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER.

4.После этого в выделенном вами диапазоне должна появится матрица, которая равна произведению исходных матриц.

Упражнения.

Раздел 2. Элементы математического анализа

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

Производной функции y f (x) называется предел отношения приращения функции к приращению независимой переменной при стремлении последнего к нулю (если этот предел существует):

Геометрический смысл производной заключается в том, что производная f (x0 ) есть угловой коэффициент каса-

тельной, проведенной к кривой y f (x) в точке х0.

Обычно производная характеризует скорость изменения функции. Например, скорость движения – это производная от пути по времени st .

Продифференцировать функцию – это значит найти ее производную (можно использовать таблицы производных).

Тема 2.1. Определенный интеграл

5

Упражнения.

Найти при помощи метода прямоугольника и трапеции определенные интегралы:

3.1

1. sin xdx с шагом х=0,1.

0

2

2. xdx с шагом х=0,1.

0

1.51

3.x dx с шагом х=0,1.

1

Раздел 3. Задачи оптимизации.

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

Тема 3.1 Решение уравнения с одним неизвестным

Одним из приложений задач оптимизации является численное решение уравнения вида f(x)=0. Для решения подобных уравнений в программе Excel; используется удобный и простой инструмент Подбор параметров. Процесс решения распадается на два этапа:

1.Задание на рабочем листе ячейки, содержащей переменную решаемого уравнения (влияющую ячейку), и ячейки, содержащей формулу уравнения ( зависящей или целевой ячейки).

2.Ввод адресов влияющей и целевой ячеек в диалоговом окне Подбор параметра и получение ответа (или сообщение и его отсутствии).

Пример 1. Найти решение уравнения lnx=0.

Решение. Этап 1.

1.В ячейку А1 вводим – корень, в ячейку В1 – функция.

2.В ячейку А2 вводим ориентировочное значение корня, например, 3.

3.Заносим в ячейку В2 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А2. В ячейке В2 появляется число 1,098612.

Этап 2.

1. Вызываем процедуру Подбор параметров (Сервис – Подбор параметра).

2.В поле Установить в ячейке мышью указываем В2, в поле Значение с клавиатуры задаем 0 (правая часть уравнения), в поле Изменяя значение ячейки мышью указываем А2 (см. рис).

3.Щелкаем на кнопке ОК и получаем результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкаем на ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции. При этом существует погрешность решения (вместо 0 в правой части уравнения получаем -0,00013).

Пример 2. Найти решение уравнения x2 3x 2 0.

Решение. Уравнение имеет два корня. Решение начинаем с нахождения первого корня.

1.В ячейку А4 вводим заголовок корни, в ячейку В4 – функция.

2.В ячейку А5 вводим ориентировочное значение корня, например, 3.

3.Заносим в ячейку В5 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А5.

4.Производим подбор параметров. В результате получаем значение первого корня х1=2,000019, значение функции при этом получаем 1,94Е-05 (0,000019).

5.Повторяем расчет для второго корня. Для этого в ячейку А6 вводим значение -3, в ячейку В6 копируем формулу функции. Производим подбор параметров. Значение второго корня х2=0,99960.

Упражнения.

1.Решить уравнение cos(x)=0 в диапазоне х[0;2].

2.Решить уравнение 2x2 3x 1 0

7

3.Решить уравнение x3 3x2 х 0

Тема 3.2 Аппроксимация экспериментальных данных.

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

Z n f (xi ) (xi ) 2 min

i 0

ВExcel аппроксимация экспериментальных данных осуществляется путем построения их графика с последующим подбором подходящей аппроксимирующей функции (линии тренда). Возможны следующие варианты функций:

1.Линейная: y=ax+b. Обычно применяется в простейших случаях, когда экспериментальные данные убывают или возрастают с постоянной скоростью.

2.Полиномиальная: y=а0+ a1 x + a2 x2 +… an xn, (п), аi– константы. Используется для описания экспериментальных данных, попеременно возрастающих и убывающих. Степень полинома определяется количеством экстремумов кривой. Так, например, полином второй степени может описать только один максимум или минимум, полином третьей степени – не более двух экстремумов.

3.Логарифмическая: y a lnx b , где а и b константы, ln – функция натурального логарифма. Функция

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

4.Степенная: y = bxa, где а и b – константы. Аппроксимация степенной функции используется для экспериментальных данных с постоянно увеличивающейся (убывающей) скоростью роста. Данные не должны иметь нулевых или отрицательных значений.

5.Экспоненциальная: y = bеaх, где а и b константы, е – основание натурального логарифма. Применятся для описания экспериментальных данных, которые быстро растут или убывают, а затем постепенно стабилизируется. Часто ее использование вытекает из теоретических соображений.

Степень близости аппроксимации экспериментальных данных выбранной функции оценивается коэффициентом детерминации (R2). Чем больше коэффициент детерминации (стремится к единице), тем лучше.

Для осуществления аппроксимации на диаграмме экспериментальных данных необходимо щелчком правой кнопки мыши вызвать контекстное меню и выбрать пункт Добавить линию тренда. В появившемся диалоговом окне Линия тренда, на вкладке Тип выбрать вид аппроксимирующей функции, на вкладке Параметры задаются дополнительные параметры, влияющие на отображение аппроксимирующей кривой, в частности можно установить флажки в поля Показывать уравнения на диаграмме и Поместить на диаграмму величину достоверности аппроксимации. Можно сделать прогноз о том, как будет вести себя исследуемая функция, для этого надо на вкладке Параметры указать количество периодов (от 0,5) для которых будет сделан прогноз.

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

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

8

4.Постройте по исходным данным еще одну гистограмму и произведите те же действия, выбрав тип линии тренда Полиномиальный, степень 2, параметры те же.

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

Самостоятельное задание.

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

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

2. Количество вложенных в производство средств и полученная в результате прибыль соотносятся следующим образом

Запишите аналитическую зависимость между x и y. Проанализируйте полученный ответ. Какова будет прибыль предприятия, если вложить 10,0 единиц средств?

9