Доклад на тему «Использование средств Excel при решении задач». Научно-практическая конференция «Актуальные проблемы социально-профессионального становления будущего специалиста»

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

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


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

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


















2015
Содержание
Введение
Основная часть:
линейная алгебра (решение систем уравнений, нахождение обратной, транспонированной матриц);
математический анализ (нахождение производной, вычисление определенного интеграла);
Заключение
Литература


Введение
Любому специалисту в ходе практической деятельности приходится совершать операции над количественными данными, которые осуществляются в соответствии с математическими законами. Поэтому для специалиста-нематематика наиболее важным является практический аспект математики. Для него это прикладная наука, близкая к технологии. Здесь наиболее важным является умение провести необходимые вычисления. Математическая теория изменяется сравнительно медленно, однако технология применения математических методов претерпела значительно более существенные изменения. Буквально за последние десятилетия пройден путь от расчетов в уме и на бумаге к применению счетов, арифмометров, калькуляторов и далее – к расчетам на компьютере. Поэтому в настоящее время специалист, даже хорошо знающий математику, но не умеющий применять математические методы на компьютере, не может считаться специалистом современного уровня.
Цель работы: применение средств Excel в решении.
Для реализации поставленной цели были выдвинуты следующие задачи:
изучить возможности Excel;
найти области применения для решения задач по алгебре, по математическому анализу, по теории вероятностей;
создать обучающую программу с использованием Excel.
Использование компьютера при проведении расчетов сдвигает акценты в математической подготовке специалиста. Если раньше основное внимание было сосредоточено на математических методах, которые предусматривали проведение расчетов вручную, то теперь, с появлением специализированных математических программ, необходимо научиться проводить требуемые вычисления на компьютере.
Практическая значимость работы заключается в использовании разработанных программ на занятиях по дисциплине “Математика и информатика”.
Основная часть
Для решения задач на компьютерах чаще всего применяется метод решения «в лоб», опирающийся на основное определение и использующий самый общий подход. Снижается значение частных случаев, различных свойств описываемых математических объектов, ориентированных на облегчение решений вручную. Например, при решении вручную квадратного уравнения 13 EMBED Equation.3 1415 помимо общего решения 13 EMBED Equation.3 1415 требовалось знать решения для частных случаев: когда квадратное уравнение разлагается на множители, когда b – четное, когда а=1, по формулам Виета. При этом было принято считать, что решение «рационально», если для него используется подходящая частная формула. В настоящее время при применении компьютера, по-видимому, рациональным следует считать решение с использованием общих подходов, по общей формуле.
В тоже время традиционное преподавание классической математики все еще ориентировано на дальнейшую работу с карандашом и бумагой. Наиболее важной отличительной особенностью предлагаемого материала является рассмотрение основных разделов курса математики не в традиционном изложении, а с перспективой дальнейшего применения компьютера. Причем, в отличие от курсов информатики, изложение материала ведется не «от пакетов программ и их возможностей», а «математических задач к способам их решения на компьютере». При этом основное внимание сосредоточено на реализации способов решения математических задач, на том, как решать типовые задачи.
Компьютерный математический анализ данных предлагает некоторое математическое преобразование данных с помощью определенных программных средств. Следовательно, необходимо иметь представление как о математических методах обработки данных, так и о соответствующих программных средствах, то есть необходимо опираться на определенный программный пакет.

Приведем примеры.
1 Линейная алгебра
Пример 1.
Предположим, что в диапазон ячеек А1:Е2 введена матрица размера 13 EMBED Equation.3 1415
13 EMBED Equation.3 1415
Необходимо получить транспонированную матрицу.
Решение
Выделите блок ячеек под транспонированную матрицу (13 EMBED Equation.3 1415). Например, А4:В8.
Нажмите на панели инструментов Стандартная кнопку Вставка функции.
В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Ссылки и массивы, а в рабочем поле Функция – имя функции ТРАНСП (рис.1). После этого щелкните на кнопке ОК.
13 EMBED PBrush 1415
Рис.1
Появившееся диалоговое окно ТРАНСП мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:Е2 в рабочее поле Массив. После чего нажмите сочетание клавиш CTRL+SHIFT+ENTER (рис.2).
13 EMBED PBrush 1415
Рис.2
Если транспонированная матрица не появилась в диапазоне А4:В8, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER. В результате в диапазоне А4:В8 появится транспонированная матрица:
13 EMBED Equation.3 1415
Пример 2.
Предположим, что в диапазоне ячеек А1:С3 введена матрица:
13 EMBED Equation.3 1415.
Необходимо вычислить определитель этой матрицы.
Решение
Табличный курсор поставьте в ячейку, в которой требуется получить значение определителя, например, в А4.
Нажмите на панели инструментов Стандартная кнопку Вставка функции.
В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция – имя функции МОПРЕД. После этого щелкните на кнопке ОК.
Появившееся диалоговое окно МОПРЕД мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:С3 в рабочее поле Массив. Нажмите кнопку ОК (рис.3)
13 EMBED PBrush 1415
Рис.3
В ячейке А4 появится значение определителя матрицы 6.
Пример 3.
Пусть в диапазоне ячеек А1:С3 введена матрица:
13 EMBED Equation.3 1415.
Необходимо получить обратную матрицу.
Решение
Выделите блок ячеек под обратную матрицу, например, блок ячеек А5:С7.
Нажмите на панели инструментов Стандартная кнопку Вставка функции. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция – имя функции МОБР. После этого щелкните на кнопке ОК.
Появившееся диалоговое окно МОБР мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:С3 в рабочее поле Массив.
13 EMBED PBrush 1415
рис.4

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

13 EMBED Equation.3 1415.

Так же можно рассмотреть сложение и вычитание матриц, умножение матриц на число, умножение матриц.
Рассмотрим решение систем линейных уравнений.
Пример 4.
Пусть необходимо решить систему 13 EMBED Equation.3 1415
Решение
Введите матрицу А в диапазон А1:В2
13 EMBED Equation.3 1415.
Вектор 13 EMBED Equation.3 1415
Найдите обратную матрицу 13 EMBED Equation.3 1415. Для этого:
выделите блок ячеек под обратную матрицу. Например, выделите блок А3:В4;
нажмите на панели инструментов Стандартная кнопку Вставка функции;
в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция – имя функции МОБР. После этого щелкните на кнопке ОК.
появившееся диалоговое окно МОБР мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:В2 в рабочее поле Массив. Нажмите сочетание клавиш CTRL+SHIFT+ENTER;
если обратная матрица не появилась в диапазоне А3:В4, то следует щелкнуть указателем мыши в строке и повторить нажатие CTRL+SHIFT+ENTER. В результате в диапазоне А3:В4 появится обратная матрица:
13 EMBED Equation.3 1415
Умножением обратной матрицы 13 EMBED Equation.3 1415 на вектор В найдем вектор Х. Для этого:
выделите блок ячеек под результирующую матрицу (под вектор Х). Ее размерность будет 13 EMBED Equation.3 1415, в данном примере 13 EMBED Equation.3 1415. Например, выделите блок ячеек С3:С4;
нажмите на панели инструментов Стандартная кнопку Вставка функции;
в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция – имя функции МУМНОЖ. После этого щелкните на кнопке ОК;
появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходной матрицы и введите диапазон обратной матрицы 13 EMBED Equation.3 1415 – А3:В4 в рабочее поле Массив1, а диапазон матрицы В – С1:С2 – в рабочее поле Массив2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER;
если вектор Х не появился в диапазоне С3:С4, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне С3:С4 появится вектор Х. Причем х=5 будет находиться в ячейке С3, а у=-4 – в ячейке С4.
Можно осуществить проверку найденного решения. Для этого найденный вектор Х необходимо подставить в исходное матричное уравнение 13 EMBED Equation.3 1415
Проверка производится следующим образом.
Выделить блок ячеек под результирующую матрицу (под вектор В). Её размерность будет 13 EMBED Equation.3 1415, в данном примере 13 EMBED Equation.3 1415 Например, выделите блок ячеек D1:D2.
Нажмите на панели инструментов Стандартная кнопку Вставка функции.
В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция – имя функции МУМНОЖ. После этого щелкните на кнопке ОК.
Появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходной матрицы и введите диапазон обратной матрицы 13 EMBED Equation.3 1415 – А1:В2 в рабочее поле Массив1, а диапазон матрицы Х – С3:С4 – в рабочее поле Массив2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER.
Если вектор В не появился в диапазоне D1:D2, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне D1:D2 появится вектор В, и, если система решена правильно, появившийся вектор будет равен исходному 13 EMBED Equation.3 1415.
Математический анализ
Пример 1.
Найти производную функции 13 EMBED Equation.3 1415 на промежутке 13 EMBED Equation.3 1415 при шаге дискретизации 13 EMBED Equation.3 1415. Построить график функции и её производной.
Решение
Для решения задачи прежде всего необходимо ввести данные в рабочую таблицу. Вводим в ячейку А1 слово аргумент. Затем в ячейку А2 – первое значение аргумента – 0 (левую границу диапазона). Далее в ячейку А3 введем второе значение: левая граница плюс шаг дискретизации – 0,2. Теперь необходимо скопировать формулу в ячейки А4:А33. Значения аргумента введены.
Далее требуется вводить значения функции. В ячейку В1 заносим слово синус и устанавливаем табличный курсор аргумента в ячейке В2. Здесь должно оказаться значение синуса, соответствующее значению аргумента в ячейке А2. Для получения значения синуса воспользуемся специальной функцией: нажимаем на панели инструментов кнопку Вставка функции. В появившемся диалоговом окне Мастер функций – шаг 1 из 2 слева в поле Категория указываем виды функций. Выбираем Математические. Справа в поле Функция выбираем SIN. Наведя указатель мыши на серое поле окна, при нажатой левой кнопке сдвигаем поле вправо, чтобы открыть столбец данных. Указываем значение аргумента синуса щелчком мыши на ячейке А2. Нажимаем кнопку ОК. В ячейке В2 появляется 0. Теперь необходимо скопировать функцию из ячейки В2 в ячейки В3:В33. Для этого устанавливаем табличный курсор в ячейку В2, и за правый нижний угол протягиванием копируем в ячейки В3:В33. Значения синуса получены.
Теперь по введенным в рабочую таблицу данным необходимо найти значения производной. Для этого в ячейку С1 вводим слово производная. В ячейку С3 вводим формулу дифференцирования 13 EMBED Equation.3 1415 Протягиванием копируем ее из ячейки С3 в ячейки С4:С32 (в ячейках С2 и С33 значения производной не определены, так как не заданы значения синуса в ячейках В1 и В34). Получены значения производной.
Далее по полученным данным строим диаграмму. Щелчком указателя мыши на кнопке на панели инструментов вызываем Мастер диаграмм. В появившемся диалоговом окне выбираем тип диаграммы – График, вид – левый верхний. После нажатия кнопки Далее указываем диапазон данных – В1:С33. Проверяем положение переключателя Ряды в: столбцах. Выбираем вкладку Ряд и с помощью мыши вводим диапазон подписей оси Х: А2:А33. Нажав кнопку Далее вводим название осей Х и У: Аргумент и значения, соответственно. Нажимаем кнопку Готово. Появляется диаграмма, изображенная на рис.10.
13 EMBED PBrush 1415
Рис.10
Пример 2. Методом прямоугольников и методом трапеций найти 13 EMBED Equation.3 1415 с шагом 13 EMBED Equation.3 1415
Решение
Метод прямоугольников
Открываем чистый рабочий лист (команда Вставка / Лист).
Составляем таблицу данных (х и f(x)). Пусть первый столбец будет значениями х, а второй соответствующими показателями f(x). Для этого в ячейку А1 вводим слово Аргумент, а в ячейку В1 – слово Функция. В ячейку А2 вводится первое значение аргумента – левая граница диапазона (0). В ячейку А3 вводится второе значение аргумента – левая граница диапазона плюс шаг построения (0,1). Затем, выделив блок ячеек А2:А3, автозаполняем получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки А32, до значения х=3).
Далее вводим значения подынтегральной функции. В ячейку В2 необходимо записать ее уравнение. Для этого табличный курсор необходимо установить в ячейку В2 и с клавиатуры ввести формулу13 EMBED Equation.3 1415 (при английской раскладке клавиатуры). Нажимаем клавишу Enter. В ячейке В2 появится 0. Теперь необходимо скопировать функцию из ячейки В2. Автозаполнением копируем эту формулу в диапазон В2:В32. В результате должна быть получена таблица для нахождения интеграла.
Теперь в ячейке В33 может быть найдено приближенное значение интеграла. Для этого в ячейку В33 вводим формулу =0,1*, затем вызываем Мастер функций (нажатием на панели инструментов кнопки Вставка функции). В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория выбираем Математические. Справа в поле Функция – функцию СУММ. Нажимаем кнопку ОК. Появляется диалоговое окно СУММ. В рабочее поле мышью вводим диапазон суммирования В3:В32 (рис. 11).
13 EMBED PBrush 1415
Рис.11
Нажимаем кнопку ОК. В ячейке В33 появляется приближенное значение искомого интеграла (9,455).
Сравнивая полученное приближенное с истинным значением интеграла, можно видеть, что ошибка приближения метода прямоугольников в данном случае довольно значительна – 0,455.

Метод трапеций
Для нахождения определенного интеграла методом трапеций, как и в случае использования методом прямоугольников, значения подынтегральной функции f(x) должны быть введены в рабочую таблицу Excel в диапазоне 13 EMBED Equation.3 1415 с заданным шагом 13 EMBED Equation.3 1415 Поэтому этапы 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 сильно уступает специализированным математическим пакетам. Тем не менее, большое количество математических задач может быть решено с его помощью.

Литература


Додж М., Стинсон К. Эффективная работа: Microsoft Excel 2002. СПб.: Питер, 2003
Гельман В.Я. Практикум по математике на компьютере. СПб.: СПИГ, 2001
Маркович Э.С. Курс высшей математики с элементами теории вероятностей и математической статистики. М.: Высшая школа, 1987
Экономическая информатика/ Ред. П.В. Конюховский, Д.Н. Колесов. СПб.: Питер, 2000
Пикуза В., Геращенко А. Экономические и финансовые расчеты в Excel: самоучитель. СПб.: Питер, 2002