- •Microsoft Excel: краткая информация о программе
- •Практическое занятие № 1 Анализ рядов данных. Вычисление линии тренда для случаев однофакторной и многофакторной зависимостей.
- •Задачи для самостоятельного решения
- •Практическое занятие № 2 Решение классической транспортной задачи с помощью Excel
- •Задачи для самостоятельного решения
- •Практическое занятие № 3 Решение многоэтапной транспортной задачи с помощью Excel
- •Задачи для самостоятельного решения
- •Практическое занятие № 4 Решение транспортной задачи с выбором вида транспорта с помощью Excel
- •Задачи для самостоятельного решения
- •Практическое занятие № 5 Решение задачи о назначениях с помощью Excel
- •Задачи для самостоятельного решения
Microsoft Excel: краткая информация о программе
Microsoft Excel (также иногда называется Microsoft Office Excel) -программа для работы с электронными таблицами, созданная корпорацией Microsoft. Она предоставляет возможности экономико-статистических расчетов, графические инструменты. Microsoft Excel входит в состав Microsoft Office и на сегодняшний день Excel является одним из наиболее популярных приложений в мире.
Области применения электронных таблиц:
бухгалтерский и банковский учет;
планирование распределение ресурсов;
проектно-сметные работы;
инженерно-технические расчеты;
обработка больших массивов информации;
исследование динамических процессов.
Основные возможности электронных таблиц:
анализ и моделирование на основе выполнения вычислений и обработки данных;
оформление таблиц, отчетов;
форматирование содержащихся в таблице данных;
построение диаграмм требуемого вида;
создание и ведение баз данных с возможностью выбора записей по заданному критерию и сортировки по любому параметру;
перенесение (вставка) в таблицу информации из документов, созданных в других приложениях, работающих в среде Windows;
печать итогового документа целиком или частично.
Преимущества использования ЭТ при решении задач:
- Решение задач с помощью электронных таблиц освобождает от составления алгоритма и отладки программы. Нужно только определенным образом записать в таблицу исходные данные и математические соотношения, входящие в модель.
- При использовании однотипных формул нет необходимости вводить их многократно, можно скопировать формулу в нужную ячейку. При этом произойдет автоматический пересчет относительных адресов, встречающихся в формуле. Если же необходимо, чтобы при копировании формулы ссылка на какую-то ячейку не изменилась, то существует возможность задания абсолютного (неизменяемого) адреса ячейки.
Практическое занятие № 1 Анализ рядов данных. Вычисление линии тренда для случаев однофакторной и многофакторной зависимостей.
Постановка задачи: необходимо проанализировать представленные ряды данных и найти статистические зависимости между ними.
Исходные данные:
Месяц |
Фрахтовая ставка, $/сут |
Спрос, млн.т дедвейта |
Предложение, млн. т дедвейта |
1 |
2000 |
20 |
27 |
2 |
2150 |
18 |
25 |
3 |
2100 |
19 |
26 |
4 |
2050 |
22 |
29 |
5 |
2100 |
25 |
27 |
6 |
2170 |
27 |
28 |
7 |
2250 |
24 |
27 |
8 |
2280 |
23 |
29 |
9 |
2290 |
26 |
30 |
10 |
3000 |
28 |
29 |
11 |
2800 |
21 |
27 |
12 |
2650 |
22 |
26 |
13 |
2700 |
25 |
30 |
14 |
2800 |
27 |
31 |
15 |
2950 |
29 |
30 |
А. Найти уравнение однофакторной зависимости изменения фрахтовой ставки во времени путем построения линии тренда.
1. Для анализа, из представленных данных возьмем столбцы «Месяц» и «Фрахтовая ставка». Построить график, представив данные в виде точечной диаграммы. При этом, по оси х располагается месяц, по оси у – фрахтовая ставка (рис.1).
2. Добавить на график линию тренда (рис. 2-4):
- правой кнопкой выделить точки графика и в появившемся меню выбрать «Добавить линию тренда» (тип линии тренда выбрать линейный);
- в том же меню, в последней закладке, выделить позиции «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации».
- появившуюся на графике линию тренда можно редактировать, вызвав правой кнопкой меню.
! Тип линии тренда подбирается, в зависимости от анализируемых данных, по показателю достоверности аппроксимации R2. Чем ближе R2 к единице, тем лучше линия тренда описывает данные.
3. Добавить в таблицу дополнительный столбец «Фрахтовая ставка теоретическая 1». Используя формулу линейного тренда, представленную на графике (y=70,536х+1855), рассчитать теоретические значения фрахтовой ставки.
Б. Найти уравнение многофакторной зависимости фрахтовой ставки от спроса и предложения на тоннаж путем выполнения регрессионного анализа.
1. В меню «Сервис» выбрать «Анализ данных». В появившемся меню выбрать инструмент анализа «Регрессия».
2. В открывшемся меню в активные строки входных интервалов ввести: входной интервал у – всю совокупность фактических значений фрахтовой ставки; входной интервал х – всю совокупность показателей, от которых ищется зависимость (а именно – спрос и предложение) (рис. 6).
3. Результат регрессионного анализа представлен на рис. 7. Для уравнения регрессии используются коэффициенты из нижней таблицы.
Уравнение регрессии для данного примера:
, (1)
где
Y-пересечение |
693,43606= |
b |
Переменная X 1 |
50,53489= |
a1 |
Переменная X 2 |
18,76018= |
a2 |
Таким образом,
4. Формируем в таблице еще один столбец «Фрахтовая ставка теоретическая 2». В столбце, используя полученное уравнение регрессии, рассчитываем значения фрахтовой ставки.
Результат расчетов изображаем графически (рис. 9).