Научно-практическая конференция

«Актуальные проблемы социально-профессионального становления будущего специалиста»

Секция

математических и естественнонаучных и информационных дисциплин

Тема: «Использование средств Excel при решении задач»

2015

Содержание

1. Введение

2. Основная часть:

   • линейная алгебра (решение систем уравнений, нахождение обратной, транспонированной матриц);

   • математический анализ (нахождение производной, вычисление определенного интеграла);

3. Заключение

4. Литература

Введение

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

Цель работы: применение средств Excel в решении.

Для реализации поставленной цели были выдвинуты следующие задачи:

• изучить возможности Excel;

• найти области применения для решения задач по алгебре, по математическому анализу, по теории вероятностей;

• создать обучающую программу с использованием Excel.

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

Практическая значимость работы заключается в использовании разработанных программ на занятиях по дисциплине "Математика и информатика".

Основная часть

Для решения задач на компьютерах чаще всего применяется метод решения «в лоб», опирающийся на основное определение и использующий самый общий подход. Снижается значение частных случаев, различных свойств описываемых математических объектов, ориентированных на облегчение решений вручную. Например, при решении вручную квадратного уравнения помимо общего решения требовалось знать решения для частных случаев: когда квадратное уравнение разлагается на множители, когда b - четное, когда а=1, по формулам Виета. При этом было принято считать, что решение «рационально», если для него используется подходящая частная формула. В настоящее время при применении компьютера, по-видимому, рациональным следует считать решение с использованием общих подходов, по общей формуле.

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

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

Приведем примеры.

1 Линейная алгебра

Пример 1.

Предположим, что в диапазон ячеек А1:Е2 введена матрица размера

Необходимо получить транспонированную матрицу.

Решение

1. Выделите блок ячеек под транспонированную матрицу (). Например, А4:В8.

2. Нажмите на панели инструментов Стандартная кнопку Вставка функции.

3. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Ссылки и массивы, а в рабочем поле Функция - имя функции ТРАНСП (рис.1). После этого щелкните на кнопке ОК.

Рис.1

4. Появившееся диалоговое окно ТРАНСП мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:Е2 в рабочее поле Массив. После чего нажмите сочетание клавиш CTRL+SHIFT+ENTER (рис.2).

Рис.2

5. Если транспонированная матрица не появилась в диапазоне А4:В8, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER. В результате в диапазоне А4:В8 появится транспонированная матрица:

Пример 2.

Предположим, что в диапазоне ячеек А1:С3 введена матрица:

.

Необходимо вычислить определитель этой матрицы.

Решение

1. Табличный курсор поставьте в ячейку, в которой требуется получить значение определителя, например, в А4.

2. Нажмите на панели инструментов Стандартная кнопку Вставка функции.

3. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция - имя функции МОПРЕД. После этого щелкните на кнопке ОК.

4. Появившееся диалоговое окно МОПРЕД мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:С3 в рабочее поле Массив. Нажмите кнопку ОК (рис.3)

Рис.3

В ячейке А4 появится значение определителя матрицы 6.

Пример 3.

Пусть в диапазоне ячеек А1:С3 введена матрица:

.

Необходимо получить обратную матрицу.

Решение

1. Выделите блок ячеек под обратную матрицу, например, блок ячеек А5:С7.

2. Нажмите на панели инструментов Стандартная кнопку Вставка функции. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция - имя функции МОБР. После этого щелкните на кнопке ОК.

3. Появившееся диалоговое окно МОБР мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:С3 в рабочее поле Массив.

рис.4

4. Нажмите сочетание клавиш CTRL+SHIFT+ENTER.

5. Если обратная матрица не появилась в диапазоне А5:С7, то следует щелкнуть указателем мыши в строке и повторить нажатие CTRL+SHIFT+ENTER. В результате в диапазоне А5:С7 появится обратная матрица:

.

Так же можно рассмотреть сложение и вычитание матриц, умножение матриц на число, умножение матриц.

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

Пример 4.

Пусть необходимо решить систему

Решение

1. Введите матрицу А в диапазон А1:В2

.

Вектор

2. Найдите обратную матрицу . Для этого:

• выделите блок ячеек под обратную матрицу. Например, выделите блок А3:В4;

• нажмите на панели инструментов Стандартная кнопку Вставка функции;

• в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция - имя функции МОБР. После этого щелкните на кнопке ОК.

• появившееся диалоговое окно МОБР мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:В2 в рабочее поле Массив. Нажмите сочетание клавиш CTRL+SHIFT+ENTER;

• если обратная матрица не появилась в диапазоне А3:В4, то следует щелкнуть указателем мыши в строке и повторить нажатие CTRL+SHIFT+ENTER. В результате в диапазоне А3:В4 появится обратная матрица:

3. Умножением обратной матрицы на вектор В найдем вектор Х. Для этого:

   • выделите блок ячеек под результирующую матрицу (под вектор Х). Ее размерность будет , в данном примере . Например, выделите блок ячеек С3:С4;

   • нажмите на панели инструментов Стандартная кнопку Вставка функции;

   • в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция - имя функции МУМНОЖ. После этого щелкните на кнопке ОК;

   • появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходной матрицы и введите диапазон обратной матрицы - А3:В4 в рабочее поле Массив1, а диапазон матрицы В - С1:С2 - в рабочее поле Массив2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER;

   • если вектор Х не появился в диапазоне С3:С4, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне С3:С4 появится вектор Х. Причем х=5 будет находиться в ячейке С3, а у=-4 - в ячейке С4.

Можно осуществить проверку найденного решения. Для этого найденный вектор Х необходимо подставить в исходное матричное уравнение

Проверка производится следующим образом.

1. Выделить блок ячеек под результирующую матрицу (под вектор В). Её размерность будет , в данном примере Например, выделите блок ячеек D1:D2.

2. Нажмите на панели инструментов Стандартная кнопку Вставка функции.

3. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция - имя функции МУМНОЖ. После этого щелкните на кнопке ОК.

4. Появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходной матрицы и введите диапазон обратной матрицы - А1:В2 в рабочее поле Массив1, а диапазон матрицы Х - С3:С4 - в рабочее поле Массив2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER.

5. Если вектор В не появился в диапазоне D1:D2, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне D1:D2 появится вектор В, и, если система решена правильно, появившийся вектор будет равен исходному .

Математический анализ

Пример 1.

Найти производную функции на промежутке при шаге дискретизации . Построить график функции и её производной.

Решение

1. Для решения задачи прежде всего необходимо ввести данные в рабочую таблицу. Вводим в ячейку А1 слово аргумент. Затем в ячейку А2 - первое значение аргумента - 0 (левую границу диапазона). Далее в ячейку А3 введем второе значение: левая граница плюс шаг дискретизации - 0,2. Теперь необходимо скопировать формулу в ячейки А4:А33. Значения аргумента введены.

2. Далее требуется вводить значения функции. В ячейку В1 заносим слово синус и устанавливаем табличный курсор аргумента в ячейке В2. Здесь должно оказаться значение синуса, соответствующее значению аргумента в ячейке А2. Для получения значения синуса воспользуемся специальной функцией: нажимаем на панели инструментов кнопку Вставка функции. В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория указываем виды функций. Выбираем Математические. Справа в поле Функция выбираем SIN. Наведя указатель мыши на серое поле окна, при нажатой левой кнопке сдвигаем поле вправо, чтобы открыть столбец данных. Указываем значение аргумента синуса щелчком мыши на ячейке А2. Нажимаем кнопку ОК. В ячейке В2 появляется 0. Теперь необходимо скопировать функцию из ячейки В2 в ячейки В3:В33. Для этого устанавливаем табличный курсор в ячейку В2, и за правый нижний угол протягиванием копируем в ячейки В3:В33. Значения синуса получены.

3. Теперь по введенным в рабочую таблицу данным необходимо найти значения производной. Для этого в ячейку С1 вводим слово производная. В ячейку С3 вводим формулу дифференцирования Протягиванием копируем ее из ячейки С3 в ячейки С4:С32 (в ячейках С2 и С33 значения производной не определены, так как не заданы значения синуса в ячейках В1 и В34). Получены значения производной.

4. Далее по полученным данным строим диаграмму. Щелчком указателя мыши на кнопке на панели инструментов вызываем Мастер диаграмм. В появившемся диалоговом окне выбираем тип диаграммы - График, вид - левый верхний. После нажатия кнопки Далее указываем диапазон данных - В1:С33. Проверяем положение переключателя Ряды в: столбцах. Выбираем вкладку Ряд и с помощью мыши вводим диапазон подписей оси Х: А2:А33. Нажав кнопку Далее вводим название осей Х и У: Аргумент и значения, соответственно. Нажимаем кнопку Готово. Появляется диаграмма, изображенная на рис.10.

Рис.10

Пример 2. Методом прямоугольников и методом трапеций найти с шагом

Решение

1. Метод прямоугольников

1. Открываем чистый рабочий лист (команда Вставка / Лист).

2. Составляем таблицу данных (х и f(x)). Пусть первый столбец будет значениями х, а второй соответствующими показателями f(x). Для этого в ячейку А1 вводим слово Аргумент, а в ячейку В1 - слово Функция. В ячейку А2 вводится первое значение аргумента - левая граница диапазона (0). В ячейку А3 вводится второе значение аргумента - левая граница диапазона плюс шаг построения (0,1). Затем, выделив блок ячеек А2:А3, автозаполняем получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки А32, до значения х=3).

3. Далее вводим значения подынтегральной функции. В ячейку В2 необходимо записать ее уравнение. Для этого табличный курсор необходимо установить в ячейку В2 и с клавиатуры ввести формулу (при английской раскладке клавиатуры). Нажимаем клавишу Enter. В ячейке В2 появится 0. Теперь необходимо скопировать функцию из ячейки В2. Автозаполнением копируем эту формулу в диапазон В2:В32. В результате должна быть получена таблица для нахождения интеграла.

4. Теперь в ячейке В33 может быть найдено приближенное значение интеграла. Для этого в ячейку В33 вводим формулу =0,1*, затем вызываем Мастер функций (нажатием на панели инструментов кнопки Вставка функции). В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория выбираем Математические. Справа в поле Функция - функцию СУММ. Нажимаем кнопку ОК. Появляется диалоговое окно СУММ. В рабочее поле мышью вводим диапазон суммирования В3:В32 (рис. 11).

Рис.11

Нажимаем кнопку ОК. В ячейке В33 появляется приближенное значение искомого интеграла (9,455).

Сравнивая полученное приближенное с истинным значением интеграла, можно видеть, что ошибка приближения метода прямоугольников в данном случае довольно значительна - 0,455.

2. Метод трапеций

Для нахождения определенного интеграла методом трапеций, как и в случае использования методом прямоугольников, значения подынтегральной функции f(x) должны быть введены в рабочую таблицу Excel в диапазоне с заданным шагом Поэтому этапы 1-3 полностью аналогичны этапам предыдущего решения. Поскольку таблица данных для нахождения интеграла уже введена, приступаем сразу к этапу 4:

Теперь в ячейке В34 может быть найдено приближенное значение интеграла по методу трапеций. Для этого в ячейку В34 вводим формулу =0,1*((В2+В32)/2+, затем вызываем Мастер функций (нажатием на панели инструментов кнопки Вставка функции). В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория выбираем Математические. Справа в поле Функция - функцию СУММ. Нажимаем кнопку ОК. Появляется диалоговое окно СУММ. В рабочее поле мышью вводим диапазон суммирования В3:В31. Нажимаем кнопку ОК. В ячейке В34 появляется приближенное значение искомого интеграла (9,005).

Сравнивая полученное приближенное с истинным значением интеграла, можно видеть, что ошибка приближения метода трапеций в данном случае в данном случае вполне приемлемая - 0,455.

Заключение

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

Существует значительное количество специализированных математических пакетов, таких как MatLab, MathCad, Math, Mathematica, Maple и др. Все они охватывают основные разделы математики и позволяют производить подавляющее большинство необходимых математических расчетов. Однако освоение этих пакетов самостоятельно - достаточно трудоемкая задача. В то же время в курс информатики включено изучение электронной таблицы Excel. Поэтому представляется оправданным реализованный в предлагаемом курсе подход, основанный на применении математических методов именно с помощью пакета Excel. Конечно, Excel сильно уступает специализированным математическим пакетам. Тем не менее, большое количество математических задач может быть решено с его помощью.

Литература

1. Додж М., Стинсон К. Эффективная работа: Microsoft Excel 2002. СПб.: Питер, 2003

2. Гельман В.Я. Практикум по математике на компьютере. СПб.: СПИГ, 2001

3. Маркович Э.С. Курс высшей математики с элементами теории вероятностей и математической статистики. М.: Высшая школа, 1987

4. Экономическая информатика/ Ред. П.В. Конюховский, Д.Н. Колесов. СПб.: Питер, 2000

5. Пикуза В., Геращенко А. Экономические и финансовые расчеты в Excel: самоучитель. СПб.: Питер, 2002