Microsoft Excel

Содержание 

 Введение 3

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

2. Выбор метода  решения системы линейных уравнений 8

2.1 Использование  статистических функций для решения  системы линейных уравнений 10

3. Решение  Задач оптимизации с помощь  надстройки Поиск решения 12

 3.1 Построение  математической модели задач 14

 3.2 Использование  команды Поиск решения в решении  задания 3 17

 3.3 Анализ  отчета по результатам, устойчивости, пределам 20

4. Листы Microsoft Excel с исходными данными, формулами,  диаграммами и полученными результатами решения 21

 Заключение 30

 Литература 30 

 

Введение 

      Microsoft Excel – ведущая программа обработки электронных таблиц. Первая версия MS Excel появилась в 1985 году и обеспечивала только простые арифметические операции в строку или в столбец. В 1993 году вышла пятая версия Excel, ставшая первым приложением Microsoft Office. Приложение включало язык Visual Basic for Application (VBA), основанный на языке Microsoft Visual Basic. Начиная с Office 97 Microsoft включает VBA во все приложения Microsoft Office.

      В настоящее время MS Excel представляет собой мощное и гибкое средство разработки информационных систем, содержащее в  себя как электронные таблицы (со средствами финансового и статистического  анализа, набором стандартных математических функций, доступными в компьютерных языках высокого уровня, рядом дополнительных функций, встречающихся только в библиотеках дорогостоящих инженерных программ), так и средства визуального программирования (Visual Basic for Applications). Электронные таблицы позволяют производить обработку чисел и текста, задать формулы и функции для автоматического выполнения, прогнозировать бюджет на основе сценария, представлять данные в виде диаграмм, публиковать рабочие листы и диаграммы в Web. С помощью VBA можно автоматизировать всю работу, начиная от сбора информации, ее обработки до создания игровой документации, как офисного пользования, так и на Web-узле.

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

 

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

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

      При построении графиков функций предполагается следующие действия:

1. В ячейки А5,А44:А45 введем названия тех функций, графики которых будем строить. В дальнейшем они составят текст легенды.
2. В строки 4 и 43 введем значения независимой переменной t с шагом 0.2 в промежутке [-2, 2]. Если ввести в две соседние ячейки значения -2 и -1.8, затем выделив их в правом нижнем углу появится крестик. Наведем на него курсор мыши который в результате примет указатель в виде крестика. Нажав левую кнопку мыши и удерживая тянем в правую сторону. В результате получается интервал с шагом 0.2.
3. В 5, 44 и 45 строках введите значения функций в соответствующих точках. Технически это делается следующим образом: в ячейку B5 вводится формула =LN(1/(2+2*B4+B4*B4)), в ячейку B5 вводится формула =$B$42*КОРЕНЬ(B43*B43+4), в ячейку B5 вводится формула =($B$42*SIN(R43))/LN(1+R43*R43). После этого этого формулы переносятся из данных ячеек в остальные.
4. После этого выполняем команду Вставка ׀ Диаграмма. В результате на данном листе появляется Мастер диаграмм Шаг1 из 4 который имеет вид как показано на рисунке 1. Выбираем тип диаграммы, вид и нажимаем кнопку Далее.
5. На втором шаге выделяем диапазон ячеек на основании которых будет строится график. Вид Шага 2 показан на рисунке 2.

Рисунок 1. Диалоговое окно «Мастер диаграмм Шаг 1 из 4» 

Рисунок 2. Диалоговое окно «Мастер диаграмм Шаг 2 из 4» 

       Как только буден введен диапазон Диалоговое окно «Мастер диаграмм Шаг 2 из 4»  примет вид как показано на рисунке 3

Рисунок 3 Диалоговое окно «Мастер диаграмм Шаг 2 из 4»

      На  данном этапе будет показан предварительный  вид диаграммы. После этого нажимаем кнопку Далее.

6. На 3 шаге как показано на Рисунке 4 предлагается ввести названия

Рисунок 4 Диалоговое окно «Мастер диаграмм Шаг 3 из 4»

диаграмм, название осей координат. Так же на этом шаге предлагается возможность изменения линий сетки, легенды, таблицы данных, подписи данных и оси координат. После ввода всех корректировок нажимаем кнопку Далее.

7. На 4 шаге предлагается выбрать где будет размещаться данная диаграмма: на отдельном листе или на новом. Данное диалоговое окно 4 шага представлена на Рисунке 5. После этого нажимаем кнопку Готово.

Рисунок 5 Диалоговое окно «Мастер диаграмм Шаг 4 из 4» 

      В результате, если было выбрано размещение диаграммы на имеющемся листе, то на данном листе будет вставлена  диаграмма.

      Если  диаграмма построена, это не значит что ее нельзя редактировать. При двойном нажатии кнопки мыши на тексте названия откроется окно диалога форматирования название диаграммы. В результате на вкладке шрифт можно установить вид шрифта, стиль и размер. Также двойным щелчком можно редактировать легенду, оси координат, линии графика.

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

 

2. Выбор метода решения системы линейных уравнений 

      Уравнение с одной независимой переменной, описывающее прямую линию, имеет вид

y=mx+b,

      где x – независимая переменная;

          y – зависимая переменная;

          m – характеристика наклона прямой;

          b – точка пересечения прямой с осью Y.

      В случае множественной регрессии  имеется несколько независимых  переменных и уравнение прямой становится многомерным:

y=m₁x₁ + m₂x₂ + … m_nx_n + b,

      где x₁, x₂, …, x_n – независимые переменные;

          y – зависимая переменная;

          m₁, m₂, …, m_n – соответствующие коэффициенты;

          b – константа.

      Функция

= ЛИНЕЙН(известные_x; известные_y; конст; стат)

вычисляет коэффициенты m₁, m₂, …, m_n и значение b  по известным множествам значений y и соответствующим значениям каждой независимой переменной.

      Аргумент  известные_y задает множество значений y, которые могут располагаться в одном столбце, в одной строке или в прямоугольном интервале ячеек. Если известные_y заданы в виде одного столбца (одной строки), то каждый столбец (соответственно, каждая строка) массива известные_x задает значения независимой переменной. Если же аргумент известные_y задает прямоугольный интервал, то предпологается, что имеется только одна независимая переменная, при этом значения аргумента известные_x должны быть организованы тоже в виде интервала того же размера и формы, что и известные_y.

      При опущенном аргументе известные_x Excel использует последовательность 1; 2; 3; 4 и т.д.

      Аргумент  конст и стат необязательны и по умолчанию считаются равными логическим значениям ИСТИНА и ЛОЖЬ соответственно. Вместо логических значений ИСТИНА и ЛОЖЬ можно использовать числа 1 и 0. Если аргумент конст имеет значение ЛОЖЬ, то пологается, что параметр b=0. При значении аргумента стат, равном ИСТИНА, возвращаемый массив включает следующие контрольные статистические параметры:

          se₁, …, se_n – значения стандартной погрешности для каждого коэффициента уравнения

          se_b – значения стандартной погрешности для константы b

          r² – коэффициент детерминирования;

          b – константа.

          se_y – значения стандартной погрешности для y

          F –F-статистика

          df – число степеней свободы

          ss_reg – сумма квадратов регрессии

          se_resid – остаточная сумма квадратов

      Перед вводом формулы с функцией ЛИНЕЙН() нужно выделить интервал ячеек, соответствующий размеру возвращаемого ею массива.

      Если  аргумент стат опущен (или имеет значение ЛОЖЬ), то результирующий интервал массива содержит по одной ячейке для каждой независимой переменной и одну ячейку для b. Если же включены контрольные статистические параметры, то результирующий интервал массива будет выглядеть следующим образом:

      Это прямоугольный интервал из n+1 столбцов и 5 строк, в котором блок в правом нижнем углу не используется и заполняется значениями ошибки ^#Н/Д.

      Выделив нужный интервал, следует напечатать формулу обращения к функции  и нажать Ctrl+Shift+Enter, чтобы ввести ее как табличную формулу. 
 

2.1 Использование статистических функций для решения системы линейных уравнений 

      Для решение системы уравнения в  Задании 2 будем использовать ЛИНЕЙН функцию так как она наиболее подходит для решения уравнений с несколькими независимыми переменными. Перед вводом формулы с функцией ЛИНЕЙН () нужно выделить интервал ячеек, соответствующий размеру возвращаемого ею массива. Затем выполняем команду Вставка ׀ Функция. В результате появляется диалоговое окно «Мастер функций Шаг 1 из 2», который имеет вид на рисунке 6.

Рисунок 6 Диалоговое окно «Мастер функций Шаг 1 из 2»

      В окне «категория» выбираем Статистические, а в окне «выберите функцию» выбираем ЛИНЕЙН. Затем нажимаем ОК. В результате появляется новое диалоговое окно «Аргументы функции» которое имеет вид на рисунке 7. В этом диалоговом окне нам необходимо заполнить следующие поля: «известные_значения_y», «известные_значения_x», «конст», «статистика». В поле известные_значения_y вводим диапазон ячеек которые соответствуют значениям y, это диапазон ячеек D12:D14. В поле известные_значения_x вводим диапазон ячеек которые соответствуют значениям m, это диапазон ячеек A12:C14. В поле конст устанавливаем значение ЛОЖЬ так, как у нас аргумент b=0 и нам надо найти не контрольные статистические параметры а значения x1, x2, x3.